Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql.sql @ 4991:b79eb203032d dami
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 ); |