changeset 6632:97145ec4e33c double-precision

Schema changes: fixed typos, syntax errors and missing or obsolete statements in Oracle schema
author Tom Gottfried <tom@intevation.de>
date Mon, 22 Jul 2013 18:06:23 +0200 (2013-07-22)
parents 9aa1c946b87d
children e7d058a2e3b2
files backend/doc/schema/oracle-drop-minfo.sql backend/doc/schema/oracle-drop-spatial.sql backend/doc/schema/oracle-drop.sql backend/doc/schema/oracle-minfo.sql backend/doc/schema/oracle-spatial.sql backend/doc/schema/oracle.sql
diffstat 6 files changed, 15 insertions(+), 38 deletions(-) [+]
line wrap: on
line diff
--- a/backend/doc/schema/oracle-drop-minfo.sql	Mon Jul 22 16:57:56 2013 +0200
+++ b/backend/doc/schema/oracle-drop-minfo.sql	Mon Jul 22 18:06:23 2013 +0200
@@ -27,14 +27,6 @@
 ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_time_interval_id;
 ALTER TABLE sediment_yield DROP CONSTRAINT fk_sy_river_id;
 ALTER TABLE sediment_yield_values DROP CONSTRAINT fk_syv_sediment_yield_id;
-ALTER TABLE waterlevel DROP CONSTRAINT fk_w_river_id;
-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 DROP CONSTRAINT fk_wd_unit_id;
-ALTER TABLE waterlevel_difference_column DROP CONSTRAINT fk_wdc_difference_id;
-ALTER TABLE waterlevel_difference_values DROP CONSTRAINT fk_wdv_column_id;
 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;
@@ -64,17 +56,10 @@
 DROP TABLE grain_fraction;
 DROP TABLE sediment_yield;
 DROP TABLE sediment_yield_values;
-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 TABLE measurement_station;
 DROP TABLE sq_relation_value;
 DROP TABLE sq_relation;
 
-DROP SEQUENCE BED_HEIGHT_TYPE_SEQ;
 DROP SEQUENCE LOCATION_SYSTEM_SEQ;
 DROP SEQUENCE ELEVATION_MODEL_SEQ;
 DROP SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
@@ -94,11 +79,6 @@
 DROP SEQUENCE GRAIN_FRACTION_ID_SEQ;
 DROP SEQUENCE SEDIMENT_YIELD_ID_SEQ;
 DROP SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ;
-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;
+DROP SEQUENCE MEASUREMENT_STATION_ID_SEQ;
 DROP SEQUENCE SQ_RELATION_ID_SEQ;
 DROP SEQUENCE SQ_RELATION_VALUES_ID_SEQ;
--- a/backend/doc/schema/oracle-drop-spatial.sql	Mon Jul 22 16:57:56 2013 +0200
+++ b/backend/doc/schema/oracle-drop-spatial.sql	Mon Jul 22 18:06:23 2013 +0200
@@ -32,11 +32,6 @@
 DROP TABLE dem;
 DROP SEQUENCE DEM_ID_SEQ;
 
-DROP TRIGGER catchment_trigger;
-DROP TABLE catchment;
-DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'CATCHMENT';
-DROP SEQUENCE CATCHMENT_ID_SEQ;
-
 DROP TRIGGER hws_lines_trigger;
 DROP TABLE hws_lines;
 DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'HWS_LINES';
@@ -85,4 +80,6 @@
 DROP TABLE boundary_kinds;
 DROP TABLE cross_section_track_kinds;
 DROP TABLE floodplain_kinds;
+DROP TABLE floodmap_kinds;
 DROP TABLE building_kinds;
+DROP TABLE jetty_kinds;
--- a/backend/doc/schema/oracle-drop.sql	Mon Jul 22 16:57:56 2013 +0200
+++ b/backend/doc/schema/oracle-drop.sql	Mon Jul 22 18:06:23 2013 +0200
@@ -20,6 +20,8 @@
 ALTER TABLE main_values DROP CONSTRAINT cMainValuesGauges;
 ALTER TABLE main_values DROP CONSTRAINT cMainValuesNamedMainValues;
 ALTER TABLE named_main_values DROP CONSTRAINT cNamedMainValuesMainValueTypes;
+ALTER TABLE official_lines DROP CONSTRAINT cOffLinesNamedMainValues;
+ALTER TABLE official_lines DROP CONSTRAINT cOffLinesWstColumns;
 ALTER TABLE ranges DROP CONSTRAINT cRangesRivers;
 ALTER TABLE rivers DROP CONSTRAINT cRiversUnits;
 ALTER TABLE wst_column_q_ranges DROP CONSTRAINT cWstColumnQRangesWstColums;
--- a/backend/doc/schema/oracle-minfo.sql	Mon Jul 22 16:57:56 2013 +0200
+++ b/backend/doc/schema/oracle-minfo.sql	Mon Jul 22 18:06:23 2013 +0200
@@ -247,7 +247,7 @@
     name    VARCHAR(64)    NOT NULL,
     lower   NUMBER(38,3),
     upper   NUMBER(38,3),
-    PRIMARY KEY (id),
+    PRIMARY KEY (id)
 );
 
 
--- a/backend/doc/schema/oracle-spatial.sql	Mon Jul 22 16:57:56 2013 +0200
+++ b/backend/doc/schema/oracle-spatial.sql	Mon Jul 22 18:06:23 2013 +0200
@@ -1,4 +1,4 @@
-WHENEVER SQLERROR EXIT;
+--WHENEVER SQLERROR EXIT;
 
 CREATE TABLE axis_kinds(
     id NUMBER PRIMARY KEY NOT NULL,
@@ -14,7 +14,7 @@
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
-    kind_id  NUMBER(38) REFERENCES axis_kinds(id) NOT NULL DEFAULT 0,
+    kind_id  NUMBER(38) DEFAULT 0 NOT NULL REFERENCES axis_kinds(id),
     name     VARCHAR(64),
     path     VARCHAR(256),
     ID NUMBER PRIMARY KEY NOT NULL
@@ -62,7 +62,7 @@
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
-    kind_id  NUMBER(38) REFERENCES cross_section_track_kinds(id) NOT NULL DEFAULT 0,
+    kind_id  NUMBER(38) DEFAULT 0 NOT NULL REFERENCES cross_section_track_kinds(id),
     km       NUMBER(38,12) NOT NULL,
     z        NUMBER(38,12) DEFAULT 0 NOT NULL,
     name     VARCHAR(64),
@@ -145,7 +145,7 @@
     OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
-    kind_id  NUMBER(38) REFERENCES floodplain_kinds(id) NOT NULL DEFAULT 0,
+    kind_id  NUMBER(38) DEFAULT 0 NOT NULL REFERENCES floodplain_kinds(id),
     name     VARCHAR(64),
     path     VARCHAR(256),
     ID NUMBER PRIMARY KEY NOT NULL
--- a/backend/doc/schema/oracle.sql	Mon Jul 22 16:57:56 2013 +0200
+++ b/backend/doc/schema/oracle.sql	Mon Jul 22 18:06:23 2013 +0200
@@ -53,7 +53,7 @@
     x                       NUMBER(38,2) NOT NULL,
     y                       NUMBER(38,2) NOT NULL,
     cross_section_line_id   NUMBER(38,0) NOT NULL,
-    PRIMARY KEY             (id),
+    PRIMARY KEY             (id)
 );
 
 
@@ -94,7 +94,7 @@
     id                  NUMBER(38,0) NOT NULL,
     description         VARCHAR2(255) NOT NULL,
     bfg_id              VARCHAR2(50),
-    kind                NUMBER(38,0) NOT NULL DEFAULT 0,
+    kind                NUMBER(38,0) DEFAULT 0 NOT NULL,
     gauge_id            NUMBER(38,0) NOT NULL,
     time_interval_id    NUMBER(38,0),
     PRIMARY KEY         (id),
@@ -215,8 +215,6 @@
     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)
 );
@@ -263,7 +261,7 @@
 CREATE TABLE rivers (
     id                  NUMBER(38,0) NOT NULL,
     official_number     NUMBER(38,0),
-    km_up               NUMBER(38,0) NOT NULL DEFAULT 0,
+    km_up               NUMBER(38,0) DEFAULT 0 NOT NULL,
     name                VARCHAR2(255) NOT NULL UNIQUE,
     wst_unit_id         NUMBER(38,0) NOT NULL,
     PRIMARY KEY         (id)
@@ -326,7 +324,7 @@
     description         VARCHAR2(255),
     name                VARCHAR2(255) NOT NULL,
     source              VARCHAR(256),
-    position            NUMBER(38,0) NOT NULL DEFAULT 0,
+    position            NUMBER(38,0) DEFAULT 0 NOT NULL,
     time_interval_id    NUMBER(38,0),
     wst_id              NUMBER(38,0) NOT NULL,
     UNIQUE (wst_id, name),
@@ -417,7 +415,7 @@
 ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
 ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
 ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
-ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON LETE CASCADE;
+ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE;
 ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
 ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;
 

http://dive4elements.wald.intevation.org