Mercurial > dive4elements > river
diff flys-backend/doc/schema/oracle-spatial.sql @ 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 |
line wrap: on
line diff
--- 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);