Mercurial > dive4elements > river

annotate backend/doc/schema/oracle_migrations/from3.1.9to3.1.10.sql @ 9234:2d4750874c57

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Cleanup and more fail safe
author gernotbelger
date Fri, 06 Jul 2018 13:12:04 +0200
parents 76c84294c1d3
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