# HG changeset patch # User Sascha L. Teichmann # Date 1335541620 0 # Node ID c3b2673eafbfff5e3b1576d5a5de700b8ea903a2 # Parent 5dfe06f149f342282f81bf1130345dcc34d63418 Adjusted PostgreSQL port of MINFO schema to match Oracle one. flys-backend/trunk@4322 c6561f87-3c4e-4783-a992-168aeb5c3f6f diff -r 5dfe06f149f3 -r c3b2673eafbf flys-backend/ChangeLog --- 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 + + * doc/schema/postgresql-minfo.sql: Adjusted PostgreSQL port + to match the Oracle schema. + 2012-04-27 Ingo Weinzierl * 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 diff -r 5dfe06f149f3 -r c3b2673eafbf flys-backend/doc/schema/postgresql-minfo.sql --- 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;