Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 168:86a1bd9cc50e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
More Hibernate/JPA stuff flys-backend/trunk@1458 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 11 Mar 2011 14:41:18 +0000
parents 15d515fe15f5
children 88c14d5d45be
comparison
equal deleted inserted replaced
167:15d515fe15f5 168:86a1bd9cc50e
1 BEGIN; 1 BEGIN;
2 2
3 -- Gewaesser 3 -- Gewaesser
4 CREATE SEQUENCE RIVERS_ID_SEQ;
5
4 CREATE TABLE rivers ( 6 CREATE TABLE rivers (
5 id int PRIMARY KEY NOT NULL, 7 id int PRIMARY KEY NOT NULL,
6 name VARCHAR(256) NOT NULL UNIQUE 8 name VARCHAR(256) NOT NULL UNIQUE
7 ); 9 );
8 10
9 -- Bruecke, Haefen, etc. 11 -- Bruecke, Haefen, etc.
10 CREATE TABLE attributes ( 12 CREATE TABLE attributes (
11 id int PRIMARY KEY NOT NULL, 13 id int PRIMARY KEY NOT NULL,
12 value VARCHAR(256) NOT NULL UNIQUE 14 value VARCHAR(256) NOT NULL UNIQUE
13 ); 15 );
14 16
15 -- segments from/to at a river 17 -- segments from/to at a river
16 CREATE TABLE ranges ( 18 CREATE TABLE ranges (
17 id int PRIMARY KEY NOT NULL, 19 id int PRIMARY KEY NOT NULL,
18 river_id int NOT NULL REFERENCES rivers(id), 20 river_id int NOT NULL REFERENCES rivers(id),
19 a NUMERIC NOT NULL, 21 a NUMERIC NOT NULL,
20 b NUMERIC, 22 b NUMERIC,
21 UNIQUE (river_id, a, b) 23 UNIQUE (river_id, a, b)
22 ); 24 );
23 25
24 -- Lage 'links', 'rechts', etc. 26 -- Lage 'links', 'rechts', etc.
25 CREATE TABLE positions ( 27 CREATE TABLE positions (
26 id int PRIMARY KEY NOT NULL, 28 id int PRIMARY KEY NOT NULL,
27 value VARCHAR(256) NOT NULL UNIQUE 29 value VARCHAR(256) NOT NULL UNIQUE
28 ); 30 );
29 31
30 -- Some object (eg. Hafen) at a segment of river 32 -- Some object (eg. Hafen) at a segment of river
31 -- plus its position. 33 -- plus its position.
32 CREATE TABLE annotations ( 34 CREATE TABLE annotations (
33 id int PRIMARY KEY NOT NULL, 35 id int PRIMARY KEY NOT NULL,
34 range_id int NOT NULL REFERENCES ranges(id), 36 range_id int NOT NULL REFERENCES ranges(id),
35 attribute_id int NOT NULL REFERENCES attributes(id), 37 attribute_id int NOT NULL REFERENCES attributes(id),
36 position_id int REFERENCES positions(id) 38 position_id int REFERENCES positions(id)
37 ); 39 );
38 40
39 -- Pegel 41 -- Pegel
40 CREATE TABLE gauges ( 42 CREATE TABLE gauges (
41 id int PRIMARY KEY NOT NULL, 43 id int PRIMARY KEY NOT NULL,
42 name VARCHAR(256) NOT NULL, 44 name VARCHAR(256) NOT NULL,
43 river_id int NOT NULL REFERENCES rivers(id), 45 river_id int NOT NULL REFERENCES rivers(id),
44 station NUMERIC NOT NULL UNIQUE, 46 station NUMERIC NOT NULL UNIQUE,
45 aeo NUMERIC NOT NULL, 47 aeo NUMERIC NOT NULL,
46 48
47 -- Pegelnullpunkt 49 -- Pegelnullpunkt
48 datum NUMERIC NOT NULL, 50 datum NUMERIC NOT NULL,
49 -- Streckengueltigkeit 51 -- Streckengueltigkeit
50 range_id int NOT NULL REFERENCES ranges (id), 52 range_id int NOT NULL REFERENCES ranges (id),
51 53
52 UNIQUE (name, river_id), 54 UNIQUE (name, river_id),
53 UNIQUE (river_id, datum) 55 UNIQUE (river_id, datum)
54 ); 56 );
55 57
56 -- Type of a Hauptwert 'W', 'Q', 'D', etc. 58 -- Type of a Hauptwert 'W', 'Q', 'D', etc.
57 CREATE TABLE main_value_types ( 59 CREATE TABLE main_value_types (
58 id int PRIMARY KEY NOT NULL, 60 id int PRIMARY KEY NOT NULL,
59 name VARCHAR(256) NOT NULL UNIQUE 61 name VARCHAR(256) NOT NULL UNIQUE
60 ); 62 );
61 63
62 -- Named type of a Hauptwert (eg. HQ100) 64 -- Named type of a Hauptwert (eg. HQ100)
63 CREATE TABLE named_main_values ( 65 CREATE TABLE named_main_values (
64 id int PRIMARY KEY NOT NULL, 66 id int PRIMARY KEY NOT NULL,
65 name VARCHAR(256) NOT NULL UNIQUE, 67 name VARCHAR(256) NOT NULL UNIQUE,
66 type_id int NOT NULL REFERENCES main_value_types(id), 68 type_id int NOT NULL REFERENCES main_value_types(id),
67 UNIQUE (name, type_id) 69 UNIQUE (name, type_id)
68 ); 70 );
69 71
70 -- Table for time intervals 72 -- Table for time intervals
71 CREATE TABLE time_intervals ( 73 CREATE TABLE time_intervals (
72 id int PRIMARY KEY NOT NULL, 74 id int PRIMARY KEY NOT NULL,
73 start_time TIMESTAMP NOT NULL, 75 start_time TIMESTAMP NOT NULL,
74 stop_time TIMESTAMP, 76 stop_time TIMESTAMP,
75 CHECK (start_time <= stop_time) 77 CHECK (start_time <= stop_time)
76 ); 78 );
77 79
78 -- Stammdaten 80 -- Stammdaten
79 CREATE TABLE main_values ( 81 CREATE TABLE main_values (
80 id int PRIMARY KEY NOT NULL, 82 id int PRIMARY KEY NOT NULL,
81 gauge_id int NOT NULL REFERENCES gauges(id), 83 gauge_id int NOT NULL REFERENCES gauges(id),
82 named_value_id int NOT NULL REFERENCES named_main_values(id), 84 named_value_id int NOT NULL REFERENCES named_main_values(id),
83 value NUMERIC NOT NULL, 85 value NUMERIC NOT NULL,
84 86
85 time_interval_id int REFERENCES time_intervals(id), 87 time_interval_id int REFERENCES time_intervals(id),
86 88
87 -- TODO: better checks 89 -- TODO: better checks
88 UNIQUE (gauge_id, named_value_id, time_interval_id) 90 UNIQUE (gauge_id, named_value_id, time_interval_id)
99 UNIQUE (gauge_id, time_interval_id) 101 UNIQUE (gauge_id, time_interval_id)
100 ); 102 );
101 103
102 -- Values of the Abflusstafeln 104 -- Values of the Abflusstafeln
103 CREATE TABLE discharge_table_values ( 105 CREATE TABLE discharge_table_values (
104 id int PRIMARY KEY NOT NULL, 106 id int PRIMARY KEY NOT NULL,
105 table_id int NOT NULL REFERENCES discharge_tables(id), 107 table_id int NOT NULL REFERENCES discharge_tables(id),
106 q NUMERIC NOT NULL, 108 q NUMERIC NOT NULL,
107 w NUMERIC NOT NULL, 109 w NUMERIC NOT NULL,
108 110
109 UNIQUE (table_id, q, w) 111 UNIQUE (table_id, q, w)
110 ); 112 );
111 113
112 -- WST files 114 -- WST files
113 CREATE TABLE wst ( 115 CREATE TABLE wst (
114 id int PRIMARY KEY NOT NULL, 116 id int PRIMARY KEY NOT NULL,
115 river_id int NOT NULL REFERENCES rivers(id), 117 river_id int NOT NULL REFERENCES rivers(id),
116 description VARCHAR(256) NOT NULL, 118 description VARCHAR(256) NOT NULL,
117 -- TODO: more meta infos 119 -- TODO: more meta infos
118 UNIQUE (river_id, description) 120 UNIQUE (river_id, description)
119 ); 121 );
120 122
121 -- columns of WST files 123 -- columns of WST files
122 CREATE TABLE wst_column ( 124 CREATE TABLE wst_column (
123 id int PRIMARY KEY NOT NULL, 125 id int PRIMARY KEY NOT NULL,
124 wst_id int NOT NULL REFERENCES wst(id), 126 wst_id int NOT NULL REFERENCES wst(id),
125 name VARCHAR(256) NOT NULL, 127 name VARCHAR(256) NOT NULL,
126 description VARCHAR, 128 description VARCHAR,
127 129
128 time_interval_id int REFERENCES time_intervals(id), 130 time_interval_id int REFERENCES time_intervals(id),
129 131
130 UNIQUE (wst_id, name) 132 UNIQUE (wst_id, name)
131 ); 133 );
132 134
133 -- w values in WST file column 135 -- w values in WST file column
134 CREATE TABLE wst_column_values ( 136 CREATE TABLE wst_column_values (
135 id int PRIMARY KEY NOT NULL, 137 id int PRIMARY KEY NOT NULL,
136 wst_column_id int NOT NULL REFERENCES wst_column(id), 138 wst_column_id int NOT NULL REFERENCES wst_column(id),
137 position NUMERIC NOT NULL, 139 position NUMERIC NOT NULL,
138 w NUMERIC NOT NULL, 140 w NUMERIC NOT NULL,
139 141
140 UNIQUE (position, wst_column_id), 142 UNIQUE (position, wst_column_id),
141 UNIQUE (position, wst_column_id, w) 143 UNIQUE (position, wst_column_id, w)
142 ); 144 );
143 145
144 -- bind q values to range 146 -- bind q values to range
145 CREATE TABLE wst_q_ranges ( 147 CREATE TABLE wst_q_ranges (
146 id int PRIMARY KEY NOT NULL, 148 id int PRIMARY KEY NOT NULL,
147 range_id int NOT NULL REFERENCES ranges(id), 149 range_id int NOT NULL REFERENCES ranges(id),
148 q NUMERIC NOT NULL 150 q NUMERIC NOT NULL
149 ); 151 );
150 152
151 -- bind q ranges to wst columns 153 -- bind q ranges to wst columns
152 CREATE TABLE wst_column_q_ranges ( 154 CREATE TABLE wst_column_q_ranges (
153 id int PRIMARY KEY NOT NULL, 155 id int PRIMARY KEY NOT NULL,
http://dive4elements.wald.intevation.org