# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1334569729 0
# Node ID 85b2b5e7377f0436629efa84f5eb75f93b624217
# Parent  13b3fcaa1b3448f094b6d4a7389da38de574bd7a
Improved the oracle db schema for MINFO specific flow velocity values.

flys-backend/trunk@4241 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 13b3fcaa1b34 -r 85b2b5e7377f flys-backend/ChangeLog
--- 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
diff -r 13b3fcaa1b34 -r 85b2b5e7377f flys-backend/doc/schema/oracle-drop-minfo.sql
--- 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;
 
diff -r 13b3fcaa1b34 -r 85b2b5e7377f flys-backend/doc/schema/oracle-minfo.sql
--- 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 (
+--
+--);