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