Mercurial > dive4elements > river
comparison flys-backend/doc/schema/postgresql-minfo.sql @ 5202:646c154477fe
SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Fri, 08 Mar 2013 12:45:48 +0100 |
| parents | b79eb203032d |
| children | 8667f629d238 |
comparison
equal
deleted
inserted
replaced
| 5200:42bb6ff78d1b | 5202:646c154477fe |
|---|---|
| 18 unit_id int NOT NULL, | 18 unit_id int NOT NULL, |
| 19 PRIMARY KEY(id), | 19 PRIMARY KEY(id), |
| 20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) | 20 CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) |
| 21 ); | 21 ); |
| 22 | 22 |
| 23 CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ; | 23 |
| 24 | 24 -- lookup table for bedheight types |
| 25 CREATE TABLE bed_height_type ( | 25 CREATE TABLE bed_height_type ( |
| 26 id int NOT NULL, | 26 id int NOT NULL, |
| 27 name VARCHAR(16) NOT NULL, | 27 type VARCHAR(64) NOT NULL, |
| 28 description VARCHAR(255), | |
| 29 PRIMARY KEY(id) | 28 PRIMARY KEY(id) |
| 30 ); | 29 ); |
| 31 | 30 INSERT INTO bed_height_type VALUES (1, 'Querprofil') |
| 31 INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung') | |
| 32 INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen') | |
| 33 INSERT INTO bed_height_type VALUES (4, 'DGM') | |
| 32 | 34 |
| 33 | 35 |
| 34 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; | 36 CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; |
| 35 | 37 |
| 36 CREATE TABLE bed_height_single ( | 38 CREATE TABLE bed_height_single ( |
| 276 sediment_yield_id int NOT NULL, | 278 sediment_yield_id int NOT NULL, |
| 277 station NUMERIC NOT NULL, | 279 station NUMERIC NOT NULL, |
| 278 value NUMERIC NOT NULL, | 280 value NUMERIC NOT NULL, |
| 279 PRIMARY KEY (id), | 281 PRIMARY KEY (id), |
| 280 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE | 282 CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE |
| 281 ); | |
| 282 | |
| 283 | |
| 284 CREATE SEQUENCE WATERLEVEL_ID_SEQ; | |
| 285 | |
| 286 CREATE TABLE waterlevel ( | |
| 287 id int NOT NULL, | |
| 288 river_id int NOT NULL, | |
| 289 unit_id int NOT NULL, | |
| 290 description VARCHAR(256), | |
| 291 PRIMARY KEY (id), | |
| 292 CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, | |
| 293 CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) | |
| 294 ); | |
| 295 | |
| 296 | |
| 297 CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ; | |
| 298 | |
| 299 CREATE TABLE waterlevel_q_range ( | |
| 300 id int NOT NULL, | |
| 301 waterlevel_id int NOT NULL, | |
| 302 q NUMERIC NOT NULL, | |
| 303 PRIMARY KEY (id), | |
| 304 CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id) ON DELETE CASCADE | |
| 305 ); | |
| 306 | |
| 307 | |
| 308 CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ; | |
| 309 | |
| 310 CREATE TABLE waterlevel_values ( | |
| 311 id int NOT NULL, | |
| 312 waterlevel_q_range_id int NOT NULL, | |
| 313 station NUMERIC NOT NULL, | |
| 314 w NUMERIC NOT NULL, | |
| 315 PRIMARY KEY (id), | |
| 316 CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id) ON DELETE CASCADE | |
| 317 ); | |
| 318 | |
| 319 | |
| 320 CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ; | |
| 321 | |
| 322 CREATE TABLE waterlevel_difference ( | |
| 323 id int NOT NULL, | |
| 324 river_id int NOT NULL, | |
| 325 unit_id int NOT NULL, | |
| 326 description VARCHAR(256), | |
| 327 PRIMARY KEY (id), | |
| 328 CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id) ON DELETE CASCADE, | |
| 329 CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) | |
| 330 ); | |
| 331 | |
| 332 | |
| 333 CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ; | |
| 334 | |
| 335 CREATE TABLE waterlevel_difference_column ( | |
| 336 id int NOT NULL, | |
| 337 difference_id int NOT NULL, | |
| 338 description VARCHAR(256), | |
| 339 PRIMARY KEY (id), | |
| 340 CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id) ON DELETE CASCADE | |
| 341 ); | |
| 342 | |
| 343 | |
| 344 CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ; | |
| 345 | |
| 346 CREATE TABLE waterlevel_difference_values ( | |
| 347 id int NOT NULL, | |
| 348 column_id int NOT NULL, | |
| 349 station NUMERIC NOT NULL, | |
| 350 value NUMERIC NOT NULL, | |
| 351 PRIMARY KEY (id), | |
| 352 CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id) ON DELETE CASCADE | |
| 353 ); | 283 ); |
| 354 | 284 |
| 355 | 285 |
| 356 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; | 286 CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; |
| 357 CREATE TABLE measurement_station ( | 287 CREATE TABLE measurement_station ( |