# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1335272670 0
# Node ID c1c6c91906363db0f648794e85e446675c7f921c
# Parent  a65b5b0ade6f247148f256b963a0bf1273a30b65
Improved the db schema to store MINFO specific waterlevel differences.

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

diff -r a65b5b0ade6f -r c1c6c9190636 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Tue Apr 24 12:46:26 2012 +0000
+++ b/flys-backend/ChangeLog	Tue Apr 24 13:04:30 2012 +0000
@@ -1,3 +1,9 @@
+2012-04-24  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/oracle-minfo.sql,
+	  doc/schema/oracle-drop-minfo.sql: Improved the schema to store MINFO
+	  specific waterlevel differences.
+
 2012-04-24  Ingo Weinzierl <ingo@intevation.de>
 
 	* src/main/java/de/intevation/flys/model/WaterlevelValue.java: Renamed a
diff -r a65b5b0ade6f -r c1c6c9190636 flys-backend/doc/schema/oracle-drop-minfo.sql
--- a/flys-backend/doc/schema/oracle-drop-minfo.sql	Tue Apr 24 12:46:26 2012 +0000
+++ b/flys-backend/doc/schema/oracle-drop-minfo.sql	Tue Apr 24 13:04:30 2012 +0000
@@ -35,6 +35,9 @@
 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;
+ALTER TABLE waterlevel_difference DROP CONSTRAINT fk_wd_river_id;
+ALTER TABLE waterlevel_difference_column DROP CONSTRAINT fk_wdc_difference_id;
+ALTER TABLE waterlevel_difference_values DROP CONSTRAINT fk_wdv_column_id;
 
 DROP TABLE bed_height_type;
 DROP TABLE location_system;
@@ -59,6 +62,9 @@
 DROP TABLE waterlevel;
 DROP TABLE waterlevel_q_range;
 DROP TABLE waterlevel_values;
+DROP TABLE waterlevel_difference;
+DROP TABLE waterlevel_difference_column;
+DROP TABLE waterlevel_difference_values;
 
 DROP SEQUENCE BED_HEIGHT_TYPE_SEQ;
 DROP SEQUENCE LOCATION_SYSTEM_SEQ;
@@ -83,3 +89,7 @@
 DROP SEQUENCE WATERLEVEL_ID_SEQ;
 DROP SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ;
 DROP SEQUENCE WATERLEVEL_VALUES_ID_SEQ;
+DROP SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ;
+DROP SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;
+DROP SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ;
+
diff -r a65b5b0ade6f -r c1c6c9190636 flys-backend/doc/schema/oracle-minfo.sql
--- a/flys-backend/doc/schema/oracle-minfo.sql	Tue Apr 24 12:46:26 2012 +0000
+++ b/flys-backend/doc/schema/oracle-minfo.sql	Tue Apr 24 13:04:30 2012 +0000
@@ -315,3 +315,38 @@
     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          NUMBER(38,0) NOT NULL,
+    river_id    NUMBER(38,0) NOT NULL,
+    unit        NUMBER(38,0) NOT NULL,
+    description VARCHAR(256),
+    PRIMARY KEY (id),
+    CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id)
+);
+
+
+CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ;
+
+CREATE TABLE waterlevel_difference_column (
+    id              NUMBER(38,0) NOT NULL,
+    difference_id   NUMBER(38,0) 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          NUMBER(38,0) NOT NULL,
+    column_id   NUMBER(38,0) NOT NULL,
+    station     NUMBER(38,3) NOT NULL,
+    value       NUMBER(38,2) NOT NULL,
+    PRIMARY KEY (id),
+    CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id)
+);
+