changeset 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 42bb6ff78d1b
children d4c276bbbc6c
files flys-backend/doc/schema/postgresql-minfo.sql flys-backend/doc/schema/postgresql.sql flys-backend/src/main/java/de/intevation/flys/backend/FLYSCredentials.java
diffstat 3 files changed, 22 insertions(+), 89 deletions(-) [+]
line wrap: on
line diff
--- 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,
--- 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)
 );
--- 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,

http://dive4elements.wald.intevation.org