# HG changeset patch
# User Ingo Weinzierl <ingo.weinzierl@intevation.de>
# Date 1331882591 0
# Node ID efb10ed2af96599a7c832bd138fd8b0d7ca75281
# Parent  501f00e355ebca88774843fe1300af8c44e0d139
Modified the oracle db schema (relation 'floodmaps').

flys-backend/trunk@4153 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 501f00e355eb -r efb10ed2af96 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Thu Mar 08 13:32:45 2012 +0000
+++ b/flys-backend/ChangeLog	Fri Mar 16 07:23:11 2012 +0000
@@ -1,3 +1,8 @@
+2012-03-16  Ingo Weinzierl <ingo@intevation.de>
+
+	* doc/schema/oracle-spatial.sql: Repaired broken oracle db schema for
+	  relation 'floodmaps'.
+
 2012-03-08  Ingo Weinzierl <ingo@intevation.de>
 
 	* src/main/java/de/intevation/flys/model/Floodmaps.java: New model class for
diff -r 501f00e355eb -r efb10ed2af96 flys-backend/doc/schema/oracle-spatial.sql
--- a/flys-backend/doc/schema/oracle-spatial.sql	Thu Mar 08 13:32:45 2012 +0000
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Fri Mar 16 07:23:11 2012 +0000
@@ -195,43 +195,21 @@
 --Hydrologie/UeSG
 CREATE SEQUENCE FLOODMAPS_ID_SEQ;
 CREATE TABLE floodmaps (
-    id NUMBER PRIMARY KEY NOT NULL,
+    OGR_FID NUMBER(38),
+    GEOM MDSYS.SDO_GEOMETRY,
     river_id NUMBER(38),
+    name VARCHAR(255),
     kind NUMBER(38),
     diff NUMBER(19,5),
     count NUMBER(38),
     area NUMBER(19,5),
-    perimeter NUMBER(19,5)
+    perimeter NUMBER(19,5),
+    id NUMBER PRIMARY KEY NOT NULL
 );
+INSERT INTO USER_SDO_GEOM_METADATA VALUES ('floodmaps', '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 OR REPLACE TRIGGER floodmaps_trigger BEFORE INSERT ON floodmaps FOR each ROW
     BEGIN
         SELECT FLOODMAPS_ID_SEQ.nextval INTO :new.id FROM dual;
     END;
-/
-
-
-CREATE SEQUENCE FLOODMAP_GEOMS_SEQ;
-CREATE TABLE floodmap_geoms (
-    id           NUMBER(38) PRIMARY KEY NOT NULL,
-    floodmaps_id NUMBER(38) NOT NULL,
-    polygons_id  NUMBER(38) NOT NULL
-);
-CREATE OR REPLACE TRIGGER floodmap_geoms_trigger BEFORE INSERT ON floodmap_geoms FOR each ROW
-    BEGIN
-        SELECT FLOODMAP_GEOMS_SEQ.nextval INTO :new.id FROM dual;
-    END;
+CREATE INDEX floodmaps_spatial_idx ON floodmaps(GEOM) indextype IS MDSYS.SPATIAL_INDEX parameters ('LAYER_GTYPE=MULTIPOLYGON');
 /
-
-
-CREATE SEQUENCE POLYGONS_SEQ;
-CREATE TABLE polygons (
-    OGR_FID NUMBER(38),
-    GEOM MDSYS.SDO_GEOMETRY,
-    id NUMBER(38) PRIMARY KEY NOT NULL
-);
-INSERT INTO USER_SDO_GEOM_METADATA VALUES ('polygons', '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 OR REPLACE TRIGGER polygons_trigger BEFORE INSERT ON polygons FOR each ROW
-    BEGIN
-        SELECT POLYGONS_SEQ.nextval INTO :new.id FROM dual;
-    END;
-/