Mercurial > dive4elements > river
view backend/doc/schema/oracle_migrations/from3.1.9to3.1.10.sql @ 9405:34cd4faf43f4
minMax queries
author | gernotbelger |
---|---|
date | Wed, 15 Aug 2018 14:34:23 +0200 (2018-08-15) |
parents | 76c84294c1d3 |
children |
line wrap: on
line source
-- SEDDB_NAME -- Lookup table for optional matching with differing river names in SedDB -- Add name here and set rivers.seddb_name_id to id CREATE TABLE seddb_name ( id NUMBER(38,0) NOT NULL, name VARCHAR2(255) NOT NULL, PRIMARY KEY (id) ); ALTER TABLE rivers ADD seddb_name_id NUMBER(38,0); ALTER TABLE rivers ADD CONSTRAINT cRiversSeddbNames FOREIGN KEY (seddb_name_id) REFERENCES seddb_name; -- bed heights ALTER TABLE bed_height_single DROP CONSTRAINT fk_bed_single_river_id; ALTER TABLE bed_height_single DROP CONSTRAINT fk_type; ALTER TABLE bed_height_single DROP CONSTRAINT fk_location_system; ALTER TABLE bed_height_single DROP CONSTRAINT fk_cur_elevation_model; ALTER TABLE bed_height_single DROP CONSTRAINT fk_old_elevation_model; ALTER TABLE bed_height_single DROP CONSTRAINT fk_range; ALTER TABLE bed_height_single DROP COLUMN sounding_width; ALTER TABLE bed_height_single RENAME TO bed_height; ALTER TABLE bed_height ADD CONSTRAINT fk_bh_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE; ALTER TABLE bed_height ADD CONSTRAINT fk_bh_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id); ALTER TABLE bed_height ADD CONSTRAINT fk_bh_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id); ALTER TABLE bed_height ADD CONSTRAINT fk_bh_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id); ALTER TABLE bed_height ADD CONSTRAINT fk_bh_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id); ALTER TABLE bed_height ADD CONSTRAINT fk_bh_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE; -- the following is needed because Oracle is not able to mix DDL with -- DML in a subselect VARIABLE seqval NUMBER BEGIN SELECT BED_HEIGHT_SINGLE_ID_SEQ.NEXTVAL INTO :seqval FROM DUAL; execute immediate('CREATE SEQUENCE BED_HEIGHT_ID_SEQ START WITH ' || :seqval); END; / DROP SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; -- bed height values ALTER TABLE bed_height_single_values DROP CONSTRAINT fk_bed_single_values_parent; ALTER TABLE bed_height_single_values RENAME COLUMN bed_height_single_id TO bed_height_id; ALTER TABLE bed_height_single_values DROP COLUMN width; ALTER TABLE bed_height_single_values RENAME TO bed_height_values; ALTER TABLE bed_height_values ADD CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id) REFERENCES bed_height(id) ON DELETE CASCADE; BEGIN SELECT BED_SINGLE_VALUES_ID_SEQ.NEXTVAL INTO :seqval FROM DUAL; execute immediate('CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ START WITH ' || :seqval); END; / DROP SEQUENCE BED_SINGLE_VALUES_ID_SEQ; -- measurement stations ALTER TABLE measurement_station ADD CONSTRAINT check_m_type CHECK(measurement_type IN ('Geschiebe', 'Schwebstoff')); -- SQ relations ALTER TABLE sq_relation DROP CONSTRAINT fk_sqr_river_id; ALTER TABLE sq_relation DROP COLUMN river_id; ALTER TABLE sq_relation_value ADD CONSTRAINT sq_mstation_param_key UNIQUE(sq_relation_id, measurement_station_id, parameter);