Mercurial > dive4elements > river

comparison flys-backend/doc/schema/postgresql.sql @ 4991:b79eb203032d dami

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
SCHEMA CHANGE: added 'ON DELETE CASCADE' where necessary to delete complete rivers (only in Postgres-Schema so far)
author Tom Gottfried <tom.gottfried@intevation.de>
date Thu, 14 Feb 2013 17:36:10 +0100
parents 20b6ebf23916
children 646c154477fe
comparison
equal deleted inserted replaced
4989:f4d61ac84b76 4991:b79eb203032d
29 -- segments from/to at a river 29 -- segments from/to at a river
30 CREATE SEQUENCE RANGES_ID_SEQ; 30 CREATE SEQUENCE RANGES_ID_SEQ;
31 31
32 CREATE TABLE ranges ( 32 CREATE TABLE ranges (
33 id int PRIMARY KEY NOT NULL, 33 id int PRIMARY KEY NOT NULL,
34 river_id int NOT NULL REFERENCES rivers(id), 34 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
35 a NUMERIC NOT NULL, 35 a NUMERIC NOT NULL,
36 b NUMERIC, 36 b NUMERIC,
37 UNIQUE (river_id, a, b) 37 UNIQUE (river_id, a, b)
38 ); 38 );
39 39
66 -- plus its position. 66 -- plus its position.
67 CREATE SEQUENCE ANNOTATIONS_ID_SEQ; 67 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
68 68
69 CREATE TABLE annotations ( 69 CREATE TABLE annotations (
70 id int PRIMARY KEY NOT NULL, 70 id int PRIMARY KEY NOT NULL,
71 range_id int NOT NULL REFERENCES ranges(id), 71 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
72 attribute_id int NOT NULL REFERENCES attributes(id), 72 attribute_id int NOT NULL REFERENCES attributes(id),
73 position_id int REFERENCES positions(id), 73 position_id int REFERENCES positions(id),
74 edge_id int REFERENCES edges(id), 74 edge_id int REFERENCES edges(id),
75 type_id int REFERENCES annotation_types(id) 75 type_id int REFERENCES annotation_types(id)
76 ); 76 );
79 CREATE SEQUENCE GAUGES_ID_SEQ; 79 CREATE SEQUENCE GAUGES_ID_SEQ;
80 80
81 CREATE TABLE gauges ( 81 CREATE TABLE gauges (
82 id int PRIMARY KEY NOT NULL, 82 id int PRIMARY KEY NOT NULL,
83 name VARCHAR(256) NOT NULL, 83 name VARCHAR(256) NOT NULL,
84 river_id int NOT NULL REFERENCES rivers(id), 84 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
85 station NUMERIC NOT NULL UNIQUE, 85 station NUMERIC NOT NULL UNIQUE,
86 aeo NUMERIC NOT NULL, 86 aeo NUMERIC NOT NULL,
87 official_number int8 UNIQUE, 87 official_number int8 UNIQUE,
88 88
89 -- Pegelnullpunkt 89 -- Pegelnullpunkt
90 datum NUMERIC NOT NULL, 90 datum NUMERIC NOT NULL,
91 -- Streckengueltigkeit 91 -- Streckengueltigkeit
92 range_id int REFERENCES ranges (id), 92 range_id int REFERENCES ranges (id) ON DELETE CASCADE,
93 93
94 UNIQUE (name, river_id), 94 UNIQUE (name, river_id),
95 UNIQUE (river_id, station) 95 UNIQUE (river_id, station)
96 ); 96 );
97 97
126 -- Stammdaten 126 -- Stammdaten
127 CREATE SEQUENCE MAIN_VALUES_ID_SEQ; 127 CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
128 128
129 CREATE TABLE main_values ( 129 CREATE TABLE main_values (
130 id int PRIMARY KEY NOT NULL, 130 id int PRIMARY KEY NOT NULL,
131 gauge_id int NOT NULL REFERENCES gauges(id), 131 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
132 named_value_id int NOT NULL REFERENCES named_main_values(id), 132 named_value_id int NOT NULL REFERENCES named_main_values(id),
133 value NUMERIC NOT NULL, 133 value NUMERIC NOT NULL,
134 134
135 time_interval_id int REFERENCES time_intervals(id), 135 time_interval_id int REFERENCES time_intervals(id),
136 136
141 -- Abflusstafeln 141 -- Abflusstafeln
142 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ; 142 CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
143 143
144 CREATE TABLE discharge_tables ( 144 CREATE TABLE discharge_tables (
145 id int PRIMARY KEY NOT NULL, 145 id int PRIMARY KEY NOT NULL,
146 gauge_id int NOT NULL REFERENCES gauges(id), 146 gauge_id int NOT NULL REFERENCES gauges(id) ON DELETE CASCADE,
147 description VARCHAR(256) NOT NULL, 147 description VARCHAR(256) NOT NULL,
148 bfg_id VARCHAR(50), 148 bfg_id VARCHAR(50),
149 kind int NOT NULL DEFAULT 0, 149 kind int NOT NULL DEFAULT 0,
150 time_interval_id int REFERENCES time_intervals(id) 150 time_interval_id int REFERENCES time_intervals(id)
151 151
156 -- Values of the Abflusstafeln 156 -- Values of the Abflusstafeln
157 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ; 157 CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
158 158
159 CREATE TABLE discharge_table_values ( 159 CREATE TABLE discharge_table_values (
160 id int PRIMARY KEY NOT NULL, 160 id int PRIMARY KEY NOT NULL,
161 table_id int NOT NULL REFERENCES discharge_tables(id), 161 table_id int NOT NULL REFERENCES discharge_tables(id) ON DELETE CASCADE,
162 q NUMERIC NOT NULL, 162 q NUMERIC NOT NULL,
163 w NUMERIC NOT NULL, 163 w NUMERIC NOT NULL,
164 164
165 UNIQUE (table_id, q, w) 165 UNIQUE (table_id, q, w)
166 ); 166 );
168 -- WST files 168 -- WST files
169 CREATE SEQUENCE WSTS_ID_SEQ; 169 CREATE SEQUENCE WSTS_ID_SEQ;
170 170
171 CREATE TABLE wsts ( 171 CREATE TABLE wsts (
172 id int PRIMARY KEY NOT NULL, 172 id int PRIMARY KEY NOT NULL,
173 river_id int NOT NULL REFERENCES rivers(id), 173 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
174 description VARCHAR(256) NOT NULL, 174 description VARCHAR(256) NOT NULL,
175 kind int NOT NULL DEFAULT 0, 175 kind int NOT NULL DEFAULT 0,
176 -- TODO: more meta infos 176 -- TODO: more meta infos
177 UNIQUE (river_id, description) 177 UNIQUE (river_id, description)
178 ); 178 );
180 -- columns of WST files 180 -- columns of WST files
181 CREATE SEQUENCE WST_COLUMNS_ID_SEQ; 181 CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
182 182
183 CREATE TABLE wst_columns ( 183 CREATE TABLE wst_columns (
184 id int PRIMARY KEY NOT NULL, 184 id int PRIMARY KEY NOT NULL,
185 wst_id int NOT NULL REFERENCES wsts(id), 185 wst_id int NOT NULL REFERENCES wsts(id) ON DELETE CASCADE,
186 name VARCHAR(256) NOT NULL, 186 name VARCHAR(256) NOT NULL,
187 description VARCHAR(256), 187 description VARCHAR(256),
188 position int NOT NULL DEFAULT 0, 188 position int NOT NULL DEFAULT 0,
189 189
190 time_interval_id int REFERENCES time_intervals(id), 190 time_interval_id int REFERENCES time_intervals(id),
196 -- w values in WST file column 196 -- w values in WST file column
197 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ; 197 CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
198 198
199 CREATE TABLE wst_column_values ( 199 CREATE TABLE wst_column_values (
200 id int PRIMARY KEY NOT NULL, 200 id int PRIMARY KEY NOT NULL,
201 wst_column_id int NOT NULL REFERENCES wst_columns(id), 201 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
202 position NUMERIC NOT NULL, 202 position NUMERIC NOT NULL,
203 w NUMERIC NOT NULL, 203 w NUMERIC NOT NULL,
204 204
205 UNIQUE (position, wst_column_id), 205 UNIQUE (position, wst_column_id),
206 UNIQUE (position, wst_column_id, w) 206 UNIQUE (position, wst_column_id, w)
209 -- bind q values to range 209 -- bind q values to range
210 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ; 210 CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
211 211
212 CREATE TABLE wst_q_ranges ( 212 CREATE TABLE wst_q_ranges (
213 id int PRIMARY KEY NOT NULL, 213 id int PRIMARY KEY NOT NULL,
214 range_id int NOT NULL REFERENCES ranges(id), 214 range_id int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
215 q NUMERIC NOT NULL 215 q NUMERIC NOT NULL
216 ); 216 );
217 217
218 -- bind q ranges to wst columns 218 -- bind q ranges to wst columns
219 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ; 219 CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
220 220
221 CREATE TABLE wst_column_q_ranges ( 221 CREATE TABLE wst_column_q_ranges (
222 id int PRIMARY KEY NOT NULL, 222 id int PRIMARY KEY NOT NULL,
223 wst_column_id int NOT NULL REFERENCES wst_columns(id), 223 wst_column_id int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
224 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id), 224 wst_q_range_id int NOT NULL REFERENCES wst_q_ranges(id) ON DELETE CASCADE,
225 225
226 UNIQUE (wst_column_id, wst_q_range_id) 226 UNIQUE (wst_column_id, wst_q_range_id)
227 ); 227 );
228 228
229 CREATE VIEW wst_value_table AS 229 CREATE VIEW wst_value_table AS
275 275
276 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ; 276 CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
277 277
278 CREATE TABLE cross_sections ( 278 CREATE TABLE cross_sections (
279 id int PRIMARY KEY NOT NULL, 279 id int PRIMARY KEY NOT NULL,
280 river_id int NOT NULL REFERENCES rivers(id), 280 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
281 time_interval_id int REFERENCES time_intervals(id), 281 time_interval_id int REFERENCES time_intervals(id),
282 description VARCHAR(256) 282 description VARCHAR(256)
283 ); 283 );
284 284
285 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ; 285 CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
286 286
287 CREATE TABLE cross_section_lines ( 287 CREATE TABLE cross_section_lines (
288 id int PRIMARY KEY NOT NULL, 288 id int PRIMARY KEY NOT NULL,
289 km NUMERIC NOT NULL, 289 km NUMERIC NOT NULL,
290 cross_section_id int NOT NULL REFERENCES cross_sections(id), 290 cross_section_id int NOT NULL REFERENCES cross_sections(id) ON DELETE CASCADE,
291 UNIQUE (km, cross_section_id) 291 UNIQUE (km, cross_section_id)
292 ); 292 );
293 293
294 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ; 294 CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
295 295
296 CREATE TABLE cross_section_points ( 296 CREATE TABLE cross_section_points (
297 id int PRIMARY KEY NOT NULL, 297 id int PRIMARY KEY NOT NULL,
298 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id), 298 cross_section_line_id int NOT NULL REFERENCES cross_section_lines(id) ON DELETE CASCADE,
299 col_pos int NOT NULL, 299 col_pos int NOT NULL,
300 x NUMERIC NOT NULL, 300 x NUMERIC NOT NULL,
301 y NUMERIC NOT NULL, 301 y NUMERIC NOT NULL,
302 UNIQUE (cross_section_line_id, col_pos) 302 UNIQUE (cross_section_line_id, col_pos)
303 ); 303 );
312 312
313 CREATE SEQUENCE HYKS_ID_SEQ; 313 CREATE SEQUENCE HYKS_ID_SEQ;
314 314
315 CREATE TABLE hyks ( 315 CREATE TABLE hyks (
316 id int PRIMARY KEY NOT NULL, 316 id int PRIMARY KEY NOT NULL,
317 river_id int NOT NULL REFERENCES rivers(id), 317 river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
318 description VARCHAR(256) NOT NULL 318 description VARCHAR(256) NOT NULL
319 ); 319 );
320 320
321 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ; 321 CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
322 322
323 CREATE TABLE hyk_entries ( 323 CREATE TABLE hyk_entries (
324 id int PRIMARY KEY NOT NULL, 324 id int PRIMARY KEY NOT NULL,
325 hyk_id int NOT NULL REFERENCES hyks(id), 325 hyk_id int NOT NULL REFERENCES hyks(id) ON DELETE CASCADE,
326 km NUMERIC NOT NULL, 326 km NUMERIC NOT NULL,
327 measure TIMESTAMP, 327 measure TIMESTAMP,
328 UNIQUE (hyk_id, km) 328 UNIQUE (hyk_id, km)
329 ); 329 );
330 330
331 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ; 331 CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
332 332
333 CREATE TABLE hyk_formations ( 333 CREATE TABLE hyk_formations (
334 id int PRIMARY KEY NOT NULL, 334 id int PRIMARY KEY NOT NULL,
335 formation_num int NOT NULL DEFAULT 0, 335 formation_num int NOT NULL DEFAULT 0,
336 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id), 336 hyk_entry_id int NOT NULL REFERENCES hyk_entries(id) ON DELETE CASCADE,
337 top NUMERIC NOT NULL, 337 top NUMERIC NOT NULL,
338 bottom NUMERIC NOT NULL, 338 bottom NUMERIC NOT NULL,
339 distance_vl NUMERIC NOT NULL, 339 distance_vl NUMERIC NOT NULL,
340 distance_hf NUMERIC NOT NULL, 340 distance_hf NUMERIC NOT NULL,
341 distance_vr NUMERIC NOT NULL, 341 distance_vr NUMERIC NOT NULL,
352 352
353 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ; 353 CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
354 354
355 CREATE TABLE hyk_flow_zones ( 355 CREATE TABLE hyk_flow_zones (
356 id int PRIMARY KEY NOT NULL, 356 id int PRIMARY KEY NOT NULL,
357 formation_id int NOT NULL REFERENCES hyk_formations(id), 357 formation_id int NOT NULL REFERENCES hyk_formations(id) ON DELETE CASCADE,
358 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id), 358 type_id int NOT NULL REFERENCES hyk_flow_zone_types(id),
359 a NUMERIC NOT NULL, 359 a NUMERIC NOT NULL,
360 b NUMERIC NOT NULL, 360 b NUMERIC NOT NULL,
361 CHECK (a <= b) 361 CHECK (a <= b)
362 ); 362 );
http://dive4elements.wald.intevation.org