changeset 5894:62e6598a2c4b

Schema change: make gauges.official_number unique per river as we can have multiple identical gauges for different representations of the same river. Comments and TODOs
author Tom Gottfried <tom@intevation.de>
date Fri, 03 May 2013 10:31:34 +0200
parents c1abd48a03e2
children 4110565baff8
files backend/doc/schema/oracle.sql backend/doc/schema/postgresql-minfo.sql backend/doc/schema/postgresql.sql
diffstat 3 files changed, 6 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/backend/doc/schema/oracle.sql	Fri May 03 07:47:15 2013 +0200
+++ b/backend/doc/schema/oracle.sql	Fri May 03 10:31:34 2013 +0200
@@ -121,12 +121,13 @@
     datum               NUMBER(38,2) NOT NULL, 
     name                VARCHAR2(255) NOT NULL,
     station             NUMBER(38,2) NOT NULL,
-    official_number     NUMBER(38,0) UNIQUE,
+    official_number     NUMBER(38,0),
     range_id            NUMBER(38,0) NOT NULL,
-    -- remove river id here because range_id references river already
+    -- TODO: remove river id here because range_id references river already
     river_id            NUMBER(38,0) NOT NULL,
     PRIMARY KEY         (id),
     UNIQUE (name, river_id),
+    UNIQUE (official_number, river_id),
     UNIQUE (river_id, station)
 );
 
--- a/backend/doc/schema/postgresql-minfo.sql	Fri May 03 07:47:15 2013 +0200
+++ b/backend/doc/schema/postgresql-minfo.sql	Fri May 03 10:31:34 2013 +0200
@@ -65,7 +65,7 @@
     id                      int NOT NULL,
     river_id                int NOT NULL,
     time_interval_id        int NOT NULL,
-    -- sounding_with           int NOT NULL,
+    -- sounding_width           int,
     -- type_id                 int NOT NULL,
     cur_elevation_model_id  int NOT NULL,
     old_elevation_model_id  int,
--- a/backend/doc/schema/postgresql.sql	Fri May 03 07:47:15 2013 +0200
+++ b/backend/doc/schema/postgresql.sql	Fri May 03 10:31:34 2013 +0200
@@ -86,7 +86,7 @@
     river_id        int             NOT NULL REFERENCES rivers(id) ON DELETE CASCADE,
     station         NUMERIC         NOT NULL,
     aeo             NUMERIC         NOT NULL,
-    official_number int8            UNIQUE,
+    official_number int8,
 
     -- Pegelnullpunkt
     datum    NUMERIC NOT NULL,
@@ -94,6 +94,7 @@
     range_id int NOT NULL REFERENCES ranges (id) ON DELETE CASCADE,
 
     UNIQUE (name, river_id),
+    UNIQUE (official_number, river_id),
     UNIQUE (river_id, station)
 );
 

http://dive4elements.wald.intevation.org