# HG changeset patch # User Tom Gottfried # Date 1362743148 -3600 # Node ID 646c154477fead9b5936ca06544ffdcf3f3abc5f # Parent 42bb6ff78d1b734341732772ab24db2a913311b0 SCHEMA CHANGE: removed Waterlevel and WaterleveDifferences, added lookup table for wst diff -r 42bb6ff78d1b -r 646c154477fe flys-backend/doc/schema/postgresql-minfo.sql --- a/flys-backend/doc/schema/postgresql-minfo.sql Fri Mar 08 11:48:33 2013 +0100 +++ b/flys-backend/doc/schema/postgresql-minfo.sql Fri Mar 08 12:45:48 2013 +0100 @@ -20,15 +20,17 @@ CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) ); -CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ; +-- lookup table for bedheight types CREATE TABLE bed_height_type ( id int NOT NULL, - name VARCHAR(16) NOT NULL, - description VARCHAR(255), + type VARCHAR(64) NOT NULL, PRIMARY KEY(id) ); - +INSERT INTO bed_height_type VALUES (1, 'Querprofil') +INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung') +INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen') +INSERT INTO bed_height_type VALUES (4, 'DGM') CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; @@ -281,78 +283,6 @@ ); -CREATE SEQUENCE WATERLEVEL_ID_SEQ; - -CREATE TABLE waterlevel ( - id int NOT NULL, - river_id int NOT NULL, - unit_id int NOT NULL, - description VARCHAR(256), - PRIMARY KEY (id), - CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, - CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) -); - - -CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ; - -CREATE TABLE waterlevel_q_range ( - id int NOT NULL, - waterlevel_id int NOT NULL, - q NUMERIC NOT NULL, - PRIMARY KEY (id), - CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id) ON DELETE CASCADE -); - - -CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ; - -CREATE TABLE waterlevel_values ( - id int NOT NULL, - waterlevel_q_range_id int NOT NULL, - station NUMERIC NOT NULL, - w NUMERIC NOT NULL, - PRIMARY KEY (id), - CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id) ON DELETE CASCADE -); - - -CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ; - -CREATE TABLE waterlevel_difference ( - id int NOT NULL, - river_id int NOT NULL, - unit_id int NOT NULL, - description VARCHAR(256), - PRIMARY KEY (id), - CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id) ON DELETE CASCADE, - CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) -); - - -CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ; - -CREATE TABLE waterlevel_difference_column ( - id int NOT NULL, - difference_id int NOT NULL, - description VARCHAR(256), - PRIMARY KEY (id), - CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id) ON DELETE CASCADE -); - - -CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ; - -CREATE TABLE waterlevel_difference_values ( - id int NOT NULL, - column_id int NOT NULL, - station NUMERIC NOT NULL, - value NUMERIC NOT NULL, - PRIMARY KEY (id), - CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id) ON DELETE CASCADE -); - - CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; CREATE TABLE measurement_station ( id int NOT NULL, diff -r 42bb6ff78d1b -r 646c154477fe flys-backend/doc/schema/postgresql.sql --- a/flys-backend/doc/schema/postgresql.sql Fri Mar 08 11:48:33 2013 +0100 +++ b/flys-backend/doc/schema/postgresql.sql Fri Mar 08 12:45:48 2013 +0100 @@ -166,13 +166,28 @@ ); -- WST files +--lookup table for wst kinds +CREATE TABLE wst_kinds ( + id int PRIMARY KEY NOT NULL, + kind VARCHAR(64) NOT NULL +); +INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata'); +INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks'); +INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst'); +INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials'); +INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points'); +INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points'); +INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences'); +INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels'); + + CREATE SEQUENCE WSTS_ID_SEQ; CREATE TABLE wsts ( id int PRIMARY KEY NOT NULL, river_id int NOT NULL REFERENCES rivers(id) ON DELETE CASCADE, description VARCHAR(256) NOT NULL, - kind int NOT NULL DEFAULT 0, + kind int NOT NULL REFERENCES wst_kinds(id) DEFAULT 0, -- TODO: more meta infos UNIQUE (river_id, description) ); diff -r 42bb6ff78d1b -r 646c154477fe flys-backend/src/main/java/de/intevation/flys/backend/FLYSCredentials.java --- a/flys-backend/src/main/java/de/intevation/flys/backend/FLYSCredentials.java Fri Mar 08 11:48:33 2013 +0100 +++ b/flys-backend/src/main/java/de/intevation/flys/backend/FLYSCredentials.java Fri Mar 08 12:45:48 2013 +0100 @@ -66,12 +66,6 @@ import de.intevation.flys.model.SedimentYieldValue; import de.intevation.flys.model.TimeInterval; import de.intevation.flys.model.Unit; -import de.intevation.flys.model.Waterlevel; -import de.intevation.flys.model.WaterlevelDifference; -import de.intevation.flys.model.WaterlevelDifferenceColumn; -import de.intevation.flys.model.WaterlevelDifferenceValue; -import de.intevation.flys.model.WaterlevelQRange; -import de.intevation.flys.model.WaterlevelValue; import de.intevation.flys.model.Wst; import de.intevation.flys.model.WstColumn; import de.intevation.flys.model.WstColumnQRange; @@ -189,12 +183,6 @@ SQRelationValue.class, TimeInterval.class, Unit.class, - Waterlevel.class, - WaterlevelDifference.class, - WaterlevelDifferenceColumn.class, - WaterlevelDifferenceValue.class, - WaterlevelQRange.class, - WaterlevelValue.class, WstColumn.class, WstColumnQRange.class, WstColumnValue.class,