Mercurial > dive4elements > river
diff flys-backend/doc/schema/oracle-spatial-migrate-dami.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 | 0fd7720e5c7f |
| children |
line wrap: on
line diff
--- 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