# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1334905143 0
# Node ID 6be2bf2492f936a028774c8693e5b70e54ace288
# Parent  71175502d868f8921326bcbf2e3942ab218ca2aa
Added MINFO specific waterlevel relations.

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

diff -r 71175502d868 -r 6be2bf2492f9 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Thu Apr 19 12:48:53 2012 +0000
+++ b/flys-backend/ChangeLog	Fri Apr 20 06:59:03 2012 +0000
@@ -1,3 +1,10 @@
+2012-04-20  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/oracle-minfo.sql,
+	  doc/schema/oracle-drop-minfo.sql: Added relations for waterlevels
+	  specific to MINFO. Note: those waterlevel values are not stored in the
+	  WINFO specific relations!
+
 2012-04-19  Ingo Weinzierl <ingo@intevation.de>
 
 	* doc/schema/oracle-minfo.sql,
diff -r 71175502d868 -r 6be2bf2492f9 flys-backend/doc/schema/oracle-drop-minfo.sql
--- a/flys-backend/doc/schema/oracle-drop-minfo.sql	Thu Apr 19 12:48:53 2012 +0000
+++ b/flys-backend/doc/schema/oracle-drop-minfo.sql	Fri Apr 20 06:59:03 2012 +0000
@@ -31,6 +31,10 @@
 ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_time_interval_id;
 ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_river_id;
 ALTER TABLE sediment_yield_values DROP CONSTRAINT fk_syv_sediment_yield_id;
+ALTER TABLE waterlevel DROP CONSTRAINT fk_w_river_id;
+ALTER TABLE waterlevel DROP CONSTRAINT fk_w_unit_id;
+ALTER TABLE waterlevel_q_range DROP CONSTRAINT fk_wqr_waterlevel_id;
+ALTER TABLE waterlevel_values DROP CONSTRAINT fk_wv_waterlevel_q_range_id;
 
 DROP TABLE bed_height_type;
 DROP TABLE location_system;
@@ -52,6 +56,9 @@
 DROP TABLE grain_fraction;
 DROP TABLE sediment_yield;
 DROP TABLE sediment_yield_values;
+DROP TABLE waterlevel;
+DROP TABLE waterlevel_q_range;
+DROP TABLE waterlevel_values;
 
 DROP SEQUENCE BED_HEIGHT_TYPE_SEQ;
 DROP SEQUENCE LOCATION_SYSTEM_SEQ;
@@ -73,4 +80,6 @@
 DROP SEQUENCE GRAIN_FRACTION_ID_SEQ;
 DROP SEQUENCE SEDIMENT_YIELD_ID_SEQ;
 DROP SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
-
+DROP SEQUENCE WATERLEVEL_ID_SEQ;
+DROP SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;
+DROP SEQUENCE WATERLEVEL_VALUES_ID_SEQ;
diff -r 71175502d868 -r 6be2bf2492f9 flys-backend/doc/schema/oracle-minfo.sql
--- a/flys-backend/doc/schema/oracle-minfo.sql	Thu Apr 19 12:48:53 2012 +0000
+++ b/flys-backend/doc/schema/oracle-minfo.sql	Fri Apr 20 06:59:03 2012 +0000
@@ -278,3 +278,40 @@
     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          NUMBER(38,0) NOT NULL,
+    river_id    NUMBER(38,0) NOT NULL,
+    unit_id     NUMBER(38,0) 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              NUMBER(38,0) NOT NULL,
+    waterlevel_id   NUMBER(38,0) NOT NULL,
+    q               NUMBER(38,2) 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                      NUMBER(38,0) NOT NULL,
+    waterlevel_q_range_id   NUMBER(38,0) NOT NULL,
+    station                 NUMBER(38,0) NOT NULL,
+    w                       NUMBER(38,0) NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id)
+);
+