changeset 5406:e88d55d01cf5

SCHEMA CHANGE: additional columns and FK constraint for SQ-relation
author Tom Gottfried <tom@intevation.de>
date Mon, 25 Mar 2013 15:46:21 +0100
parents 5ca06120b918
children db5def5456ce
files flys-backend/doc/schema/oracle-drop-minfo.sql flys-backend/doc/schema/oracle-minfo.sql flys-backend/doc/schema/postgresql-minfo.sql
diffstat 3 files changed, 21 insertions(+), 8 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-drop-minfo.sql	Mon Mar 25 15:38:54 2013 +0100
+++ b/flys-backend/doc/schema/oracle-drop-minfo.sql	Mon Mar 25 15:46:21 2013 +0100
@@ -40,6 +40,7 @@
 ALTER TABLE sq_relation DROP CONSTRAINT fk_sqr_tinterval_id;
 ALTER TABLE sq_relation DROP CONSTRAINT fk_sqr_river_id;
 ALTER TABLE sq_relation_value DROP CONSTRAINT fk_sqr_id;
+ALTER TABLE sq_relation_value DROP CONSTRAINT fk_mstation_id;
 ALTER TABLE measurement_station DROP CONSTRAINT fk_ms_river_id;
 ALTER TABLE measurement_station DROP CONSTRAINT fk_ms_range_id;
 ALTER TABLE measurement_station DROP CONSTRAINT fk_ms_reference_gauge_id;
--- a/flys-backend/doc/schema/oracle-minfo.sql	Mon Mar 25 15:38:54 2013 +0100
+++ b/flys-backend/doc/schema/oracle-minfo.sql	Mon Mar 25 15:46:21 2013 +0100
@@ -321,12 +321,18 @@
 CREATE TABLE sq_relation_value (
     id             NUMBER(38,0) NOT NULL,
     sq_relation_id NUMBER(38,0) NOT NULL,
+    m_station_id   NUMBER(38,0) NOT NULL,
     parameter      VARCHAR(16)  NOT NULL,
     fraction       VARCHAR(32)  NOT NULL,
-    function       VARCHAR(32)  NOT NULL,
-    km             NUMBER(38,3) NOT NULL,
-    a              NUMBER(38, 3) NOT NULL,
-    b              NUMBER(38,3) NOT NULL,
+    a              NUMBER(38,20) NOT NULL,
+    b              NUMBER(38,20) NOT NULL,
+    q_max          NUMBER(38,20),
+    r_sq           NUMBER(38,3),
+    n_tot          NUMBER(38,0),
+    n_outl         NUMBER(38,0),
+    cferguson      NUMBER(38,20),
+    cduan          NUMBER(38,20),
     PRIMARY KEY (id),
-    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id)
+    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id),
+    CONSTRAINT fk_mstation_id FOREIGN KEY (m_station_id) REFERENCES measurement_station(id)
 );
--- a/flys-backend/doc/schema/postgresql-minfo.sql	Mon Mar 25 15:38:54 2013 +0100
+++ b/flys-backend/doc/schema/postgresql-minfo.sql	Mon Mar 25 15:46:21 2013 +0100
@@ -321,13 +321,19 @@
 CREATE TABLE sq_relation_value (
     id             int NOT NULL,
     sq_relation_id int NOT NULL,
+    m_station_id   int NOT NULL,
     parameter      VARCHAR(16)  NOT NULL,
     fraction       VARCHAR(32)  NOT NULL,
-    function       VARCHAR(32)  NOT NULL,
-    km             NUMERIC NOT NULL,
     a              NUMERIC NOT NULL,
     b              NUMERIC NOT NULL,
+    q_max          NUMERIC,
+    r_sq           NUMERIC,
+    n_tot          int,
+    n_outl         int,
+    cferguson      NUMERIC,
+    cduan          NUMERIC,
     PRIMARY KEY (id),
-    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE
+    CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE,
+    CONSTRAINT fk_mstation_id FOREIGN KEY (m_station_id) REFERENCES measurement_station(id)
 );
 COMMIT;

http://dive4elements.wald.intevation.org