changeset 5783:153456f84602

add missing constraints to Oracle Schema (issue426) and some cosmetics
author Tom Gottfried <tom@intevation.de>
date Mon, 22 Apr 2013 13:00:13 +0200
parents 5ad389bc20f0
children efbbfe32e9fe
files flys-backend/doc/schema/oracle-spatial.sql flys-backend/doc/schema/oracle.sql flys-backend/doc/schema/postgresql-spatial.sql flys-backend/doc/schema/postgresql.sql
diffstat 4 files changed, 52 insertions(+), 41 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-spatial.sql	Mon Apr 22 12:11:55 2013 +0200
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Mon Apr 22 13:00:13 2013 +0200
@@ -34,7 +34,7 @@
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
-    km NUMBER(7,3),
+    km NUMBER(7,3) NOT NULL,
     name     VARCHAR(64),
     path     VARCHAR(256),
     ID NUMBER PRIMARY KEY NOT NULL
@@ -313,7 +313,7 @@
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
-    name VARCHAR(255),
+    name VARCHAR(255) NOT NULL,
     kind NUMBER NOT NULL REFERENCES floodmap_kinds(id),
     diff NUMBER(19,5),
     count NUMBER(38),
--- a/flys-backend/doc/schema/oracle.sql	Mon Apr 22 12:11:55 2013 +0200
+++ b/flys-backend/doc/schema/oracle.sql	Mon Apr 22 13:00:13 2013 +0200
@@ -3,7 +3,7 @@
 
 CREATE TABLE annotation_types (
     id              NUMBER(38,0) NOT NULL, 
-    name            VARCHAR2(255),
+    name            VARCHAR2(255) NOT NULL UNIQUE,
     PRIMARY KEY     (id)
 );
 
@@ -12,8 +12,8 @@
 CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
 
 CREATE TABLE annotations (
-    id              NUMBER(38,0) NOT NULL, 
-    attribute_id    NUMBER(38,0),
+    id              NUMBER(38,0) NOT NULL,
+    attribute_id    NUMBER(38,0) NOT NULL,
     edge_id         NUMBER(38,0),
     position_id     NUMBER(38,0),
     range_id        NUMBER(38,0),
@@ -27,7 +27,7 @@
 
 CREATE TABLE attributes (
     id              NUMBER(38,0) NOT NULL, 
-    value           VARCHAR2(255), 
+    value           VARCHAR2(255) NOT NULL UNIQUE, 
     primary key     (id)
 );
 
@@ -78,9 +78,10 @@
 
 CREATE TABLE discharge_table_values (
     id                  NUMBER(38,0) NOT NULL,
-    q                   NUMBER(38,2),
-    w                   NUMBER(38,2),
-    table_id            NUMBER(38,0),
+    q                   NUMBER(38,2) NOT NULL,
+    w                   NUMBER(38,2) NOT NULL,
+    table_id            NUMBER(38,0) NOT NULL,
+    UNIQUE (table_id, q, w),
     PRIMARY KEY         (id)
 );
 
@@ -90,10 +91,10 @@
 
 CREATE TABLE discharge_tables (
     id                  NUMBER(38,0) NOT NULL,
-    description         VARCHAR2(255),
+    description         VARCHAR2(255) NOT NULL,
     bfg_id              VARCHAR2(50),
-    kind                NUMBER(38,0),
-    gauge_id            NUMBER(38,0),
+    kind                NUMBER(38,0) NOT NULL DEFAULT 0,
+    gauge_id            NUMBER(38,0) NOT NULL,
     time_interval_id    NUMBER(38,0),
     PRIMARY KEY         (id)
 );
@@ -122,7 +123,7 @@
     official_number     NUMBER(38,0) UNIQUE,
     range_id            NUMBER(38,0) NOT NULL,
     -- remove river id here because range_id references river already
-    river_id            NUMBER(38,0),
+    river_id            NUMBER(38,0) NOT NULL,
     PRIMARY KEY         (id),
     UNIQUE (name, river_id),
     UNIQUE (river_id, station)
@@ -197,7 +198,7 @@
 
 CREATE TABLE main_value_types (
     id                  NUMBER(38,0) NOT NULL,
-    name                VARCHAR2(255),
+    name                VARCHAR2(255) NOT NULL UNIQUE,
     PRIMARY KEY         (id)
 );
 
@@ -207,10 +208,13 @@
 
 CREATE TABLE main_values (
     id                  NUMBER(38,0) NOT NULL,
-    value               NUMBER(38,2),
-    gauge_id            NUMBER(38,0),
-    named_value_id      NUMBER(38,0),
+    value               NUMBER(38,2) NOT NULL,
+    gauge_id            NUMBER(38,0) NOT NULL,
+    named_value_id      NUMBER(38,0) NOT NULL,
     time_interval_id    NUMBER(38,0),
+
+    -- TODO: better checks
+    UNIQUE (gauge_id, named_value_id, time_interval_id),
     PRIMARY KEY         (id)
 );
 
@@ -221,7 +225,7 @@
 CREATE TABLE named_main_values (
     id                  NUMBER(38,0) NOT NULL,
     name                VARCHAR2(256) NOT NULL UNIQUE,
-    type_id             NUMBER(38,0),
+    type_id             NUMBER(38,0) NOT NULL,
     PRIMARY KEY (id)
 );
 
@@ -231,7 +235,7 @@
 
 CREATE TABLE positions (
     id                  NUMBER(38,0) NOT NULL,
-    value               VARCHAR2(255 char),
+    value               VARCHAR2(255 char) NOT NULL UNIQUE,
     PRIMARY KEY         (id)
 );
 
@@ -244,7 +248,8 @@
     a                   NUMBER(38,10) NOT NULL,
     b                   NUMBER(38,10),
     river_id            NUMBER(38,0),
-    PRIMARY KEY         (id)
+    UNIQUE (river_id, a, b),
+    PRIMARY KEY (id)
 );
 
 
@@ -254,10 +259,10 @@
 
 CREATE TABLE rivers (
     id                  NUMBER(38,0) NOT NULL,
-    official_number     NUMBER(38,0),
-    km_up               NUMBER(38,0),
-    name                VARCHAR2(255),
-    wst_unit_id         NUMBER(38,0),
+    official_number     NUMBER(38,0) UNIQUE,
+    km_up               NUMBER(38,0) NOT NULL DEFAULT 0,
+    name                VARCHAR2(255) NOT NULL UNIQUE,
+    wst_unit_id         NUMBER(38,0) NOT NULL,
     PRIMARY KEY         (id)
 );
 
@@ -279,7 +284,7 @@
 
 CREATE TABLE units (
     id                  NUMBER(38,0) NOT NULL,
-    name                VARCHAR2(255),
+    name                VARCHAR2(255) NOT NULL UNIQUE,
     PRIMARY KEY         (id)
 );
 
@@ -289,8 +294,9 @@
 
 CREATE TABLE wst_column_q_ranges (
     id                  NUMBER(38,0) NOT NULL,
-    wst_column_id       NUMBER(38,0),
-    wst_q_range_id      NUMBER(38,0),
+    wst_column_id       NUMBER(38,0) NOT NULL,
+    wst_q_range_id      NUMBER(38,0) NOT NULL,
+    UNIQUE (wst_column_id, wst_q_range_id),
     PRIMARY KEY         (id)
 );
 
@@ -300,9 +306,11 @@
 
 CREATE TABLE wst_column_values (
     id                  NUMBER(38,0) NOT NULL,
-    position            NUMBER(38,5),
-    w                   NUMBER(38,5),
-    wst_column_id       NUMBER(38,0),
+    position            NUMBER(38,5) NOT NULL,
+    w                   NUMBER(38,5) NOT NULL,
+    wst_column_id       NUMBER(38,0) NOT NULL,
+    UNIQUE (position, wst_column_id),
+    UNIQUE (position, wst_column_id, w),
     PRIMARY KEY         (id)
 );
 
@@ -313,10 +321,12 @@
 CREATE TABLE wst_columns (
     id                  NUMBER(38,0) NOT NULL,
     description         VARCHAR2(255),
-    name                VARCHAR2(255),
-    position            NUMBER(38,0),
+    name                VARCHAR2(255) NOT NULL,
+    position            NUMBER(38,0) NOT NULL DEFAULT 0,
     time_interval_id    NUMBER(38,0),
-    wst_id              NUMBER(38,0),
+    wst_id              NUMBER(38,0) NOT NULL,
+    UNIQUE (wst_id, name),
+    UNIQUE (wst_id, position),
     PRIMARY KEY         (id)
 );
 
@@ -326,8 +336,8 @@
 
 CREATE TABLE wst_q_ranges (
     id                  NUMBER(38,0) NOT NULL,
-    q                   NUMBER(38,5),
-    range_id            NUMBER(38,0),
+    q                   NUMBER(38,5) NOT NULL,
+    range_id            NUMBER(38,0) NOT NULL,
     PRIMARY KEY         (id)
 );
 
@@ -352,9 +362,10 @@
 
 CREATE TABLE wsts (
     id                  NUMBER(38,0) NOT NULL,
-    description         VARCHAR2(255),
-    kind                NUMBER(38,0),
-    river_id            NUMBER(38,0),
+    description         VARCHAR2(255) NOT NULL,
+    kind                NUMBER(38,0) NOT NULL,
+    river_id            NUMBER(38,0) NOT NULL,
+    UNIQUE (river_id, description),
     PRIMARY KEY         (id)
 );
 
--- a/flys-backend/doc/schema/postgresql-spatial.sql	Mon Apr 22 12:11:55 2013 +0200
+++ b/flys-backend/doc/schema/postgresql-spatial.sql	Mon Apr 22 13:00:13 2013 +0200
@@ -125,7 +125,7 @@
     range_id         INT REFERENCES ranges(id),
     time_interval_id INT REFERENCES time_intervals(id),
     projection       VARCHAR(32),
-    srid	    int  NOT NULL,
+    srid	     int NOT NULL,
     elevation_state  VARCHAR(32),
     format           VARCHAR(32),
     border_break     BOOLEAN NOT NULL DEFAULT FALSE,
--- a/flys-backend/doc/schema/postgresql.sql	Mon Apr 22 12:11:55 2013 +0200
+++ b/flys-backend/doc/schema/postgresql.sql	Mon Apr 22 13:00:13 2013 +0200
@@ -69,8 +69,8 @@
 
 CREATE TABLE annotations (
     id           int PRIMARY KEY NOT NULL,
-    range_id     int             NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
-    attribute_id int             NOT NULL REFERENCES attributes(id),
+    range_id     int NOT NULL REFERENCES ranges(id) ON DELETE CASCADE,
+    attribute_id int NOT NULL REFERENCES attributes(id),
     position_id  int REFERENCES positions(id),
     edge_id      int REFERENCES edges(id),
     type_id      int REFERENCES annotation_types(id)

http://dive4elements.wald.intevation.org