Mercurial > dive4elements > river
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)