Mercurial > dive4elements > river

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