# HG changeset patch # User Andre Heinecke # Date 1361282384 -3600 # Node ID bdef4c74d675a7a682af5b9f594b02db5269a5fb # Parent 9ef49f3b030465f55d674cb7212ca496881864ad Update migrators for changes made in the dami branch and add oracle migrator. Both are currently untested! diff -r 9ef49f3b0304 -r bdef4c74d675 flys-backend/doc/schema/oracle-spatial-migrate-dami.sql --- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/flys-backend/doc/schema/oracle-spatial-migrate-dami.sql Tue Feb 19 14:59:44 2013 +0100 @@ -0,0 +1,120 @@ +DROP TRIGGER hws_trigger; +DROP TABLE hws; +DROP SEQUENCE HWS_ID_SEQ; + +--Static lookup tables for Hochwasserschutzanlagen +CREATE TABLE hws_kinds ( + id NUMBER PRIMARY KEY NOT NULL, + kind VARCHAR(64) NOT NULL +); +INSERT INTO hws_kinds (id, kind) VALUES (1, 'Durchlass'); +INSERT INTO hws_kinds (id, kind) VALUES (2, 'Damm'); +INSERT INTO hws_kinds (id, kind) VALUES (3, 'Graben'); + +CREATE TABLE fed_states ( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(23) NOT NULL +); +INSERT INTO fed_states (id, name) VALUES (1, 'Bayern'); +INSERT INTO fed_states (id, name) VALUES (2, 'Hessen'); +INSERT INTO fed_states (id, name) VALUES (3, 'Niedersachsen'); +INSERT INTO fed_states (id, name) VALUES (4, 'Nordrhein-Westfalen'); +INSERT INTO fed_states (id, name) VALUES (5, 'Rheinland-Pfalz'); +INSERT INTO fed_states (id, name) VALUES (6, 'Saarland'); +INSERT INTO fed_states (id, name) VALUES (7, 'Schleswig-Holstein'); +INSERT INTO fed_states (id, name) VALUES (8, 'Brandenburg'); +INSERT INTO fed_states (id, name) VALUES (9, 'Mecklenburg-Vorpommern'); +INSERT INTO fed_states (id, name) VALUES (10, 'Thüringen'); +INSERT INTO fed_states (id, name) VALUES (11, 'Baden-Württemberg'); +INSERT INTO fed_states (id, name) VALUES (12, 'Sachsen-Anhalt'); +INSERT INTO fed_states (id, name) VALUES (13, 'Sachsen'); +INSERT INTO fed_states (id, name) VALUES (14, 'Berlin'); +INSERT INTO fed_states (id, name) VALUES (15, 'Bremen'); +INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); + +-- HWS-Lines +CREATE SEQUENCE HWS_LINES_ID_SEQ; +CREATE TABLE hws_lines ( + id NUMBER PRIMARY KEY NOT NULL, + GEOM MDSYS.SDO_GEOMETRY, + ogr_fid NUMBER, + kind_id NUMBER REFERENCES hws_kinds(id) DEFAULT 2, + fed_state_id NUMBER REFERENCES fed_states(id), + river_id NUMBER REFERENCES rivers(id), + name VARCHAR(256), + path VARCHAR(256), + official NUMBER DEFAULT 0, + agency VARCHAR(256), + range VARCHAR(256), + shore_side NUMBER DEFAULT 0, + source VARCHAR(256), + status_date TIMESTAMP, + description VARCHAR(256) +); +INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_lines', 'GEOM', MDSYS.SDO_DIM_ARRAY(MDSYS.SDO_DIM_ELEMENT('X',3282450,3912240,0.001),MDSYS.SDO_DIM_ELEMENT('Y',5248260,6100130,0.001),MDSYS.SDO_DIM_ELEMENT('Z',-100000,100000,0.002)), 31467); +CREATE INDEX hws_lines_spatial_idx ON hws_lines(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=LINE'); + +CREATE OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW + BEGIN + SELECT HWS_LINES_ID_SEQ.nextval INTO :new.id FROM dual; + END; + +-- HWS Points lookup tables +CREATE TABLE sectie_kinds ( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); +INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); +INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); + +CREATE TABLE sobek_kinds ( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); +INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); + +CREATE TABLE boundary_kinds ( + id NUMBER PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); +INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); +INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); + +-- HWS Points +CREATE SEQUENCE HWS_POINTS_ID_SEQ; +CREATE TABLE hws_points ( + id NUMBER PRIMARY KEY NOT NULL, + GEOM MDSYS.SDO_GEOMETRY, + ogr_fid NUMBER(2), + kind_id NUMBER(2) DEFAULT 2 REFERENCES hws_kinds(id), + fed_state_id NUMBER(2) REFERENCES fed_states(id), + river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE, + name VARCHAR(256), + path VARCHAR(256), + official NUMBER DEFAULT 0, + agency VARCHAR(256), + range VARCHAR(256), + shore_side NUMBER DEFAULT 0, + source VARCHAR(256), + status_date VARCHAR(256), + description VARCHAR(256), + freeboard NUMBER(19,5), + dike_km NUMBER(19,5), + z NUMBER(19,5), + z_target NUMBER(19,5), + rated_level NUMBER(19,5) +); + +-- Altrications +ALTER TABLE dem ADD COLUMN srid NUMBER NOT NULL; +ALTER TABLE hydr_boundaries_poly ADD COLUMN sectie INT REFERENCES sectie_kinds(id); +ALTER TABLE hydr_boundaries_poly ADD COLUMN sobek INT REFERENCES sobek_kinds(id); +ALTER TABLE hydr_boundaries ADD COLUMN sectie INT REFERENCES sectie_kinds(id); +ALTER TABLE hydr_boundaries ADD COLUMN sobek INT REFERENCES sobek_kinds(id); +ALTER TABLE hydr_boundaries ADD COLUMN kind INT REFERENCES boundary_kinds(id); diff -r 9ef49f3b0304 -r bdef4c74d675 flys-backend/doc/schema/postgresql-migrate-dami.sql --- a/flys-backend/doc/schema/postgresql-migrate-dami.sql Tue Feb 19 14:58:33 2013 +0100 +++ b/flys-backend/doc/schema/postgresql-migrate-dami.sql Tue Feb 19 14:59:44 2013 +0100 @@ -1,4 +1,5 @@ DROP table hws; +DROP sequence HWS_ID_SEQ; -- Static lookup tables for Hochwasserschutzanlagen CREATE TABLE hws_kinds ( id int PRIMARY KEY NOT NULL, @@ -29,6 +30,32 @@ INSERT INTO fed_states (id, name) VALUES (15, 'Bremen'); INSERT INTO fed_states (id, name) VALUES (16, 'Hamburg'); +CREATE TABLE sectie_kinds ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO sectie_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sectie_kinds (id, name) VALUES (1, 'Flussschlauch'); +INSERT INTO sectie_kinds (id, name) VALUES (2, 'Uferbank'); +INSERT INTO sectie_kinds (id, name) VALUES (3, 'Überflutungsbereich'); + +CREATE TABLE sobek_kinds ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO sobek_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO sobek_kinds (id, name) VALUES (1, 'Stromführend'); +INSERT INTO sobek_kinds (id, name) VALUES (2, 'Stromspeichernd'); + +CREATE TABLE boundary_kinds ( + id int PRIMARY KEY NOT NULL, + name VARCHAR(64) NOT NULL +); +INSERT INTO boundary_kinds (id, name) VALUES (0, 'Unbekannt'); +INSERT INTO boundary_kinds (id, name) VALUES (1, 'BfG'); +INSERT INTO boundary_kinds (id, name) VALUES (2, 'Land'); +INSERT INTO boundary_kinds (id, name) VALUES (3, 'Sonstige'); + --Hydrologie/HW-Schutzanlagen/*Linien.shp CREATE SEQUENCE HWS_LINES_ID_SEQ; CREATE TABLE hws_lines ( @@ -48,8 +75,6 @@ description VARCHAR(256) ); SELECT AddGeometryColumn('hws_lines', 'geom', 31467, 'LINESTRING', 2); -SELECT AddGeometryColumn('hws_lines', 'geom_target', 31467, 'LINESTRING', 2); -- ? -SELECT AddGeometryColumn('hws_lines', 'geom_rated_level', 31467, 'LINESTRING', 2); -- ? -- TODO: dike_km_from dike_km_to, are they geometries? ALTER TABLE hws_lines ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_LINES_ID_SEQ'); @@ -81,8 +106,12 @@ ALTER TABLE hws_points ALTER COLUMN id SET DEFAULT NEXTVAL('HWS_POINTS_ID_SEQ'); -ALTER TABLE hydr_boundaries_poly ADD column sectie int; -ALTER TABLE hydr_boundaries_poly ADD column sobek int; -ALTER TABLE hydr_boundaries ADD column sectie int; -ALTER TABLE hydr_boundaries ADD column sobek int; +ALTER TABLE hydr_boundaries_poly ADD COLUMN sectie INT REFERENCES sectie_kinds(id); +ALTER TABLE hydr_boundaries_poly ADD COLUMN sobek INT REFERENCES sobek_kinds(id); +ALTER TABLE hydr_boundaries ADD COLUMN sectie INT REFERENCES sectie_kinds(id); +ALTER TABLE hydr_boundaries ADD COLUMN sobek INT REFERENCES sobek_kinds(id); +ALTER TABLE hydr_boundaries ADD FOREIGN KEY (kind) REFERENCES boundary_kinds(id); +ALTER TABLE dem ADD COLUMN srid INT NOT NULL; +COMMIT; +