Mercurial > dive4elements > river
changeset 2824:85b2b5e7377f
Improved the oracle db schema for MINFO specific flow velocity values.
flys-backend/trunk@4241 c6561f87-3c4e-4783-a992-168aeb5c3f6f
author | Ingo Weinzierl <ingo.weinzierl@intevation.de> |
---|---|
date | Mon, 16 Apr 2012 09:48:49 +0000 |
parents | 13b3fcaa1b34 |
children | a948366d8ac5 |
files | flys-backend/ChangeLog flys-backend/doc/schema/oracle-drop-minfo.sql flys-backend/doc/schema/oracle-minfo.sql |
diffstat | 3 files changed, 73 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/ChangeLog Fri Apr 13 16:15:23 2012 +0000 +++ b/flys-backend/ChangeLog Mon Apr 16 09:48:49 2012 +0000 @@ -1,3 +1,9 @@ +2012-04-16 Ingo Weinzierl <ingo@intevation.de> + + * doc/schema/oracle-minfo.sql, + doc/schema/oracle-drop-minfo.sql: Added new relations for MINFO specific + flow velocity values. + 2012-02-14 Sascha L. Teichmann <sascha.teichmann@intevation.de> * doc/schema/oracle-minfo.sql: Fixed column unit_id in table depths
--- a/flys-backend/doc/schema/oracle-drop-minfo.sql Fri Apr 13 16:15:23 2012 +0000 +++ b/flys-backend/doc/schema/oracle-drop-minfo.sql Mon Apr 16 09:48:49 2012 +0000 @@ -19,6 +19,11 @@ ALTER TABLE morphologic_width DROP CONSTRAINT fk_mw_river_id; ALTER TABLE morphologic_width DROP CONSTRAINT fk_mw_unit_id; ALTER TABLE morphologic_width_values DROP CONSTRAINT fk_mwv_morphologic_width_id; +ALTER TABLE flow_velocity_model_values DROP CONSTRAINT fk_fvv_flow_velocity_model_id; +ALTER TABLE flow_velocity_model DROP CONSTRAINT fk_fvm_river_id; +ALTER TABLE flow_velocity_model DROP CONSTRAINT fk_fvm_discharge_zone_id; +ALTER TABLE discharge_zone DROP CONSTRAINT fk_dz_river_id; +ALTER TABLE discharge_zone DROP CONSTRAINT fk_dz_nmv_id; DROP TABLE bed_height_type; DROP TABLE location_system; @@ -32,6 +37,9 @@ DROP TABLE sediment_density_values; DROP TABLE morphologic_width; DROP TABLE morphologic_width_values; +DROP TABLE discharge_zone; +DROP TABLE flow_velocity_model; +DROP TABLE flow_velocity_model_values; DROP SEQUENCE BED_HEIGHT_TYPE_SEQ; DROP SEQUENCE LOCATION_SYSTEM_SEQ; @@ -45,4 +53,7 @@ DROP SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; DROP SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; DROP SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; +DROP SEQUENCE DISCHARGE_ZONE_ID_SEQ; +DROP SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; +DROP SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ;
--- a/flys-backend/doc/schema/oracle-minfo.sql Fri Apr 13 16:15:23 2012 +0000 +++ b/flys-backend/doc/schema/oracle-minfo.sql Mon Apr 16 09:48:49 2012 +0000 @@ -167,3 +167,59 @@ PRIMARY KEY(id), 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 NUMBER(38,0) NOT NULL, + river_id NUMBER(38,0) NOT NULL, + named_main_value_id NUMBER(38,0) NOT NULL, + gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance + lower_factor NUMBER(38,2) NOT NULL, + upper_factor NUMBER(38,2) NOT NULL, + PRIMARY KEY(id), + CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), + CONSTRAINT fk_dz_nmv_id FOREIGN KEY (named_main_value_id) REFERENCES named_main_values(id) +); + + +CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; + +CREATE TABLE flow_velocity_model ( + id NUMBER(38,0) NOT NULL, + river_id NUMBER(38,0) NOT NULL, + discharge_zone_id NUMBER(38,0) 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 NUMBER(38,0) NOT NULL, + flow_velocity_model_id NUMBER(38,0) NOT NULL, + station NUMBER(38,3) NOT NULL, + total_channel NUMBER(38,3) NOT NULL, + main_channel NUMBER(38,3) NOT NULL, + shear_stress NUMBER(38,3) 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 +-- +--CREATE TABLE flow_velocity_measurements ( +-- +--); +-- +--CREATE SEQUENCE +-- +--CREATE TABLE flow_velocity_measurements_values ( +-- +--);