Mercurial > dive4elements > river

changeset 2858:c3b2673eafbf

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Adjusted PostgreSQL port of MINFO schema to match Oracle one. flys-backend/trunk@4322 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 27 Apr 2012 15:47:00 +0000
parents 5dfe06f149f3
children ed13816047b3
files flys-backend/ChangeLog flys-backend/doc/schema/postgresql-minfo.sql
diffstat 2 files changed, 189 insertions(+), 1 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/ChangeLog	Fri Apr 27 08:35:50 2012 +0000
+++ b/flys-backend/ChangeLog	Fri Apr 27 15:47:00 2012 +0000
@@ -1,3 +1,8 @@
+2012-02-27	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* doc/schema/postgresql-minfo.sql: Adjusted PostgreSQL port
+	  to match the Oracle schema.
+
 2012-04-27  Ingo Weinzierl <ingo@intevation.de>
 
 	* doc/annotation-types.xml: Adapted annotation typed based on BfG wishes.
@@ -70,7 +75,7 @@
 
 	  * src/main/java/de/intevation/flys/model/WaterlevelDifferenceValue.java:
 	    (setValue, setValues): Change setter name to allow hibernate to recognize
-            it.
+	    it.
 
 2012-04-24  Ingo Weinzierl <ingo@intevation.de>
 
--- a/flys-backend/doc/schema/postgresql-minfo.sql	Fri Apr 27 08:35:50 2012 +0000
+++ b/flys-backend/doc/schema/postgresql-minfo.sql	Fri Apr 27 15:47:00 2012 +0000
@@ -168,4 +168,187 @@
     CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id)
 );
 
+
+CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ;
+
+CREATE TABLE discharge_zone (
+    id                      int NOT NULL,
+    river_id                int NOT NULL,
+    gauge_name              VARCHAR(64)  NOT NULL, -- this is not very proper, but there are gauges with no db instance
+    value                   NUMERIC NOT NULL,
+    lower_discharge         VARCHAR(16)  NOT NULL,
+    upper_discharge         VARCHAR(16),
+    PRIMARY KEY(id),
+    CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id)
+);
+
+
+CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ;
+
+CREATE TABLE flow_velocity_model (
+    id                  int NOT NULL,
+    river_id            int NOT NULL,
+    discharge_zone_id   int NOT NULL,
+    description         VARCHAR(256),
+    PRIMARY KEY (id),
+    CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id)
+);
+
+
+CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
+
+CREATE TABLE flow_velocity_model_values (
+    id                      int NOT NULL,
+    flow_velocity_model_id  int NOT NULL,
+    station                 NUMERIC NOT NULL,
+    q                       NUMERIC NOT NULL,
+    total_channel           NUMERIC NOT NULL,
+    main_channel            NUMERIC NOT NULL,
+    shear_stress            NUMERIC NOT NULL,
+    PRIMARY KEY(id),
+    CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id)
+);
+
+
+
+CREATE SEQUENCE FV_MEASURE_ID_SEQ;
+
+CREATE TABLE flow_velocity_measurements (
+    id          int NOT NULL,
+    river_id    int NOT NULL,
+    description VARCHAR(256),
+    PRIMARY KEY (id),
+    CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id)
+);
+
+CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ;
+
+CREATE TABLE flow_velocity_measure_values (
+    id              int NOT NULL,
+    measurements_id int NOT NULL,
+    station         NUMERIC NOT NULL,
+    datetime        TIMESTAMP,
+    w               NUMERIC NOT NULL,
+    q               NUMERIC NOT NULL,
+    v               NUMERIC NOT NULL,
+    description     VARCHAR(256),
+    PRIMARY KEY (id),
+    CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id)
+);
+
+
+CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ;
+
+CREATE TABLE grain_fraction (
+    id      int   NOT NULL,
+    name    VARCHAR(64)    NOT NULL,
+    lower   NUMERIC,
+    upper   NUMERIC,
+    unit_id int,
+    PRIMARY KEY (id),
+    CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id)
+);
+
+
+CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;
+
+CREATE TABLE sediment_yield (
+    id                  int NOT NULL,
+    river_id            int NOT NULL,
+    grain_fraction_id   int,
+    unit_id             int NOT NULL,
+    time_interval_id    int NOT NULL,
+    description         VARCHAR(256),
+    PRIMARY KEY (id),
+    CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
+    CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id),
+    CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id),
+    CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id)
+);
+
+
+CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
+
+CREATE TABLE sediment_yield_values (
+    id                  int NOT NULL,
+    sediment_yield_id   int NOT NULL,
+    station             NUMERIC NOT NULL,
+    value               NUMERIC NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id)
+);
+
+
+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),
+    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)
+);
+
+
+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)
+);
+
+
+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),
+    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)
+);
+
+
+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)
+);
+
 COMMIT;
http://dive4elements.wald.intevation.org