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 (
+--
+--);

http://dive4elements.wald.intevation.org