changeset 5122:5ba502e78e05 dami

Fix import of hws_* on Oracle The OCI driver needs a field named OGR_FID as the first element of a table, otherwise it gets confused and tries to add the fid twice in the insert statement leading to the "too many values" error.
author Andre Heinecke <aheinecke@intevation.de>
date Thu, 28 Feb 2013 10:09:31 +0100
parents e4cc9aebfcf1
children 3d8000616ed5
files flys-backend/contrib/shpimporter/hws.py flys-backend/doc/schema/oracle-spatial-migrate-dami.sql flys-backend/doc/schema/oracle-spatial.sql flys-backend/pom.xml
diffstat 4 files changed, 18 insertions(+), 22 deletions(-) [+]
line wrap: on
line diff
--- a/flys-backend/contrib/shpimporter/hws.py	Wed Feb 27 19:44:08 2013 +0100
+++ b/flys-backend/contrib/shpimporter/hws.py	Thu Feb 28 10:09:31 2013 +0100
@@ -90,7 +90,6 @@
 
         newFeat.SetGeometry(geometry)
 
-        newFeat.SetField("ogr_fid", feat.GetFID())
         artname = self.searchField("art$")
         if self.IsFieldSet(feat, artname):
             self.handled(artname)
--- a/flys-backend/doc/schema/oracle-spatial-migrate-dami.sql	Wed Feb 27 19:44:08 2013 +0100
+++ b/flys-backend/doc/schema/oracle-spatial-migrate-dami.sql	Thu Feb 28 10:09:31 2013 +0100
@@ -35,12 +35,11 @@
 -- HWS-Lines
 CREATE SEQUENCE HWS_LINES_ID_SEQ;
 CREATE TABLE hws_lines (
-    id NUMBER PRIMARY KEY NOT NULL,
+    OGR_FID NUMBER(38),
     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),
+    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),
     name VARCHAR(256),
     path VARCHAR(256),
     official NUMBER DEFAULT 0,
@@ -49,7 +48,8 @@
     shore_side NUMBER DEFAULT 0,
     source VARCHAR(256),
     status_date TIMESTAMP,
-    description VARCHAR(256)
+    description VARCHAR(256),
+    id NUMBER PRIMARY KEY NOT NULL
 );
 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');
@@ -89,11 +89,11 @@
 -- HWS Points
 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
 CREATE TABLE hws_points (
-    id NUMBER PRIMARY KEY NOT NULL,
+    OGR_FID NUMBER(38),
     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),
+    ogr_fid NUMBER,
+    kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
+    fed_state_id NUMBER REFERENCES fed_states(id),
     river_id NUMBER(38) REFERENCES rivers(id) ON DELETE CASCADE,
     name VARCHAR(256),
     path VARCHAR(256),
@@ -108,7 +108,8 @@
     dike_km NUMBER(19,5),
     z NUMBER(19,5),
     z_target NUMBER(19,5),
-    rated_level NUMBER(19,5)
+    rated_level NUMBER(19,5),
+    id NUMBER PRIMARY KEY NOT NULL
 );
 
 -- Altrications
--- a/flys-backend/doc/schema/oracle-spatial.sql	Wed Feb 27 19:44:08 2013 +0100
+++ b/flys-backend/doc/schema/oracle-spatial.sql	Thu Feb 28 10:09:31 2013 +0100
@@ -183,9 +183,8 @@
 -- HWS-Lines
 CREATE SEQUENCE HWS_LINES_ID_SEQ;
 CREATE TABLE hws_lines (
-    id NUMBER PRIMARY KEY NOT NULL,
+    OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
-    ogr_fid NUMBER,
     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),
@@ -197,7 +196,8 @@
     shore_side NUMBER DEFAULT 0,
     source VARCHAR(256),
     status_date TIMESTAMP,
-    description VARCHAR(256)
+    description VARCHAR(256),
+    id NUMBER PRIMARY KEY NOT NULL
 );
 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 OR REPLACE TRIGGER hws_lines_trigger BEFORE INSERT ON hws_lines FOR each ROW
@@ -235,7 +235,7 @@
 -- HWS Points
 CREATE SEQUENCE HWS_POINTS_ID_SEQ;
 CREATE TABLE hws_points (
-    id NUMBER PRIMARY KEY NOT NULL,
+    OGR_FID NUMBER(38),
     GEOM MDSYS.SDO_GEOMETRY,
     ogr_fid NUMBER,
     kind_id NUMBER DEFAULT 2 REFERENCES hws_kinds(id),
@@ -254,7 +254,8 @@
     dike_km NUMBER(19,5),
     z NUMBER(19,5),
     z_target NUMBER(19,5),
-    rated_level NUMBER(19,5)
+    rated_level NUMBER(19,5),
+    id NUMBER PRIMARY KEY NOT NULL
 );
 
 INSERT INTO USER_SDO_GEOM_METADATA VALUES ('hws_points', '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);
--- a/flys-backend/pom.xml	Wed Feb 27 19:44:08 2013 +0100
+++ b/flys-backend/pom.xml	Thu Feb 28 10:09:31 2013 +0100
@@ -114,11 +114,6 @@
         <version>1.1</version>
     </dependency>
     <dependency>
-        <groupId>org.hibernatespatial</groupId>
-        <artifactId>hibernate-spatial-oracle</artifactId>
-        <version>1.1</version>
-    </dependency>
-    <dependency>
         <groupId>org.postgis</groupId>
         <artifactId>postgis-jdbc</artifactId>
         <version>1.3.3</version>

http://dive4elements.wald.intevation.org