Mercurial > dive4elements > river
changeset 5409:8b29a94ebdd8
SCHEMA CHANGE: add table for flood marks and missing DROPs for jetties
| author | Tom Gottfried <tom@intevation.de> |
|---|---|
| date | Mon, 25 Mar 2013 17:51:33 +0100 |
| parents | 91f5908c472a |
| children | 8650c0700bfb |
| files | flys-backend/doc/schema/oracle-drop-spatial.sql flys-backend/doc/schema/oracle-spatial.sql flys-backend/doc/schema/oracle-spatial_idx.sql flys-backend/doc/schema/postgresql-spatial.sql |
| diffstat | 4 files changed, 42 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/flys-backend/doc/schema/oracle-drop-spatial.sql Mon Mar 25 17:01:07 2013 +0100 +++ b/flys-backend/doc/schema/oracle-drop-spatial.sql Mon Mar 25 17:51:33 2013 +0100 @@ -67,6 +67,16 @@ DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'GAUGE_LOCATION'; DROP SEQUENCE GAUGE_LOCATION_ID_SEQ; +DROP TRIGGER jetties_trigger; +DROP TABLE jetties; +DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'JETTIES'; +DROP SEQUENCE JETTIES_ID_SEQ; + +DROP TRIGGER flood_marks_trigger; +DROP TABLE flood_marks; +DELETE FROM USER_SDO_GEOM_METADATA WHERE TABLE_NAME = 'FLOOD_MARKS'; +DROP SEQUENCE FLOOD_MARKS_ID_SEQ; + DROP TABLE hws_kinds; DROP TABLE sectie_kinds; DROP TABLE sobek_kinds;
--- a/flys-backend/doc/schema/oracle-spatial.sql Mon Mar 25 17:01:07 2013 +0100 +++ b/flys-backend/doc/schema/oracle-spatial.sql Mon Mar 25 17:51:33 2013 +0100 @@ -412,3 +412,22 @@ SELECT JETTIES_ID_SEQ.nextval INTO :new.id FROM dual; END; / + +CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; +CREATE TABLE flood_marks ( + OGR_FID NUMBER(38), + GEOM MDSYS.SDO_GEOMETRY, + id NUMBER PRIMARY KEY NOT NULL, + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, + path VARCHAR(256), + km NUMBER(7,3), + z NUMBER(38,12), + location VARCHAR(64), + year NUMBER(38,0) +); +INSERT INTO USER_SDO_GEOM_METADATA VALUES ('flood_marks', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001)), 31467); +CREATE OR REPLACE TRIGGER flood_marks_trigger BEFORE INSERT ON flood_marks FOR EACH ROW + BEGIN + SELECT FLOOD_MARKS_ID_SEQ.nextval INTO :new.id FROM dual; + END; +/
--- a/flys-backend/doc/schema/oracle-spatial_idx.sql Mon Mar 25 17:01:07 2013 +0100 +++ b/flys-backend/doc/schema/oracle-spatial_idx.sql Mon Mar 25 17:51:33 2013 +0100 @@ -22,3 +22,4 @@ CREATE INDEX jetties_idx ON jetties(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT'); +CREATE INDEX flood_marks_idx ON flood_marks(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=POINT');
--- a/flys-backend/doc/schema/postgresql-spatial.sql Mon Mar 25 17:01:07 2013 +0100 +++ b/flys-backend/doc/schema/postgresql-spatial.sql Mon Mar 25 17:51:33 2013 +0100 @@ -331,5 +331,17 @@ SELECT AddGeometryColumn('jetties','geom',31467,'POINT',2); ALTER TABLE jetties ALTER COLUMN id SET DEFAULT NEXTVAL('JETTIES_ID_SEQ'); +CREATE SEQUENCE FLOOD_MARKS_ID_SEQ; +CREATE TABLE flood_marks ( + id int PRIMARY KEY NOT NULL, + river_id int REFERENCES rivers(id) ON DELETE CASCADE, + path VARCHAR(256), + km FLOAT8, + z FLOAT8, + location VARCHAR(64), + year int +); +SELECT AddGeometryColumn('flood_marks','geom',31467,'POINT',2); +ALTER TABLE flood_marks ALTER COLUMN id SET DEFAULT NEXTVAL('FLOOD_MARKS_ID_SEQ'); COMMIT;