Mercurial > dive4elements > river

annotate backend/doc/schema/oracle_migrations/from3.1.9to3.1.10.sql @ 8775:76c84294c1d3

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
author Tom Gottfried <tom@intevation.de>
date Tue, 24 Nov 2015 18:52:24 +0100
parents
children
rev   line source
8775
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- SEDDB_NAME
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- Lookup table for optional matching with differing river names in SedDB
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 -- Add name here and set rivers.seddb_name_id to id
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 CREATE TABLE seddb_name (
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 id NUMBER(38,0) NOT NULL,
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 name VARCHAR2(255) NOT NULL,
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 PRIMARY KEY (id)
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 );
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 ALTER TABLE rivers ADD seddb_name_id NUMBER(38,0);
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 ALTER TABLE rivers ADD CONSTRAINT cRiversSeddbNames
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 FOREIGN KEY (seddb_name_id) REFERENCES seddb_name;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 -- bed heights
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 ALTER TABLE bed_height_single DROP CONSTRAINT fk_bed_single_river_id;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 ALTER TABLE bed_height_single DROP CONSTRAINT fk_type;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 ALTER TABLE bed_height_single DROP CONSTRAINT fk_location_system;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 ALTER TABLE bed_height_single DROP CONSTRAINT fk_cur_elevation_model;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 ALTER TABLE bed_height_single DROP CONSTRAINT fk_old_elevation_model;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 ALTER TABLE bed_height_single DROP CONSTRAINT fk_range;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 ALTER TABLE bed_height_single DROP COLUMN sounding_width;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 ALTER TABLE bed_height_single RENAME TO bed_height;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 ALTER TABLE bed_height ADD CONSTRAINT fk_bh_river_id
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 ALTER TABLE bed_height ADD CONSTRAINT fk_bh_type
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 FOREIGN KEY (type_id) REFERENCES bed_height_type(id);
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 ALTER TABLE bed_height ADD CONSTRAINT fk_bh_location_system
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 FOREIGN KEY (location_system_id) REFERENCES location_system(id);
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 ALTER TABLE bed_height ADD CONSTRAINT fk_bh_cur_elevation_model
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id);
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 ALTER TABLE bed_height ADD CONSTRAINT fk_bh_old_elevation_model
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id);
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 ALTER TABLE bed_height ADD CONSTRAINT fk_bh_range
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 -- the following is needed because Oracle is not able to mix DDL with
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 -- DML in a subselect
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 VARIABLE seqval NUMBER
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 BEGIN
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 SELECT BED_HEIGHT_SINGLE_ID_SEQ.NEXTVAL INTO :seqval FROM DUAL;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 execute immediate('CREATE SEQUENCE BED_HEIGHT_ID_SEQ START WITH '
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 || :seqval);
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 END;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 /
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 DROP SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 -- bed height values
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 ALTER TABLE bed_height_single_values
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 DROP CONSTRAINT fk_bed_single_values_parent;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 ALTER TABLE bed_height_single_values
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58 RENAME COLUMN bed_height_single_id TO bed_height_id;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 ALTER TABLE bed_height_single_values DROP COLUMN width;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 ALTER TABLE bed_height_single_values RENAME TO bed_height_values;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 ALTER TABLE bed_height_values ADD CONSTRAINT fk_bed_values_parent
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 FOREIGN KEY (bed_height_id) REFERENCES bed_height(id) ON DELETE CASCADE;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 BEGIN
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 SELECT BED_SINGLE_VALUES_ID_SEQ.NEXTVAL INTO :seqval FROM DUAL;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 execute immediate('CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ START WITH '
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 || :seqval);
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 END;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 /
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 DROP SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 -- measurement stations
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 ALTER TABLE measurement_station ADD CONSTRAINT check_m_type
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 CHECK(measurement_type IN ('Geschiebe', 'Schwebstoff'));
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 -- SQ relations
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 ALTER TABLE sq_relation DROP CONSTRAINT fk_sqr_river_id;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
82
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
83 ALTER TABLE sq_relation DROP COLUMN river_id;
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
84
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
85 ALTER TABLE sq_relation_value ADD CONSTRAINT sq_mstation_param_key
76c84294c1d3 Add script to migrate an Oracle DB from schema 3.1.9 to 3.1.10.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
86 UNIQUE(sq_relation_id, measurement_station_id, parameter);
http://dive4elements.wald.intevation.org