tom@8775: -- SEDDB_NAME
tom@8775: -- Lookup table for optional matching with differing river names in SedDB
tom@8775: -- Add name here and set rivers.seddb_name_id to id
tom@8775: CREATE TABLE seddb_name (
tom@8775:     id                  NUMBER(38,0) NOT NULL,
tom@8775:     name                VARCHAR2(255) NOT NULL,
tom@8775:     PRIMARY KEY         (id)
tom@8775: );
tom@8775: 
tom@8775: ALTER TABLE rivers ADD seddb_name_id NUMBER(38,0);
tom@8775: 
tom@8775: ALTER TABLE rivers ADD CONSTRAINT cRiversSeddbNames
tom@8775:       FOREIGN KEY (seddb_name_id) REFERENCES seddb_name;
tom@8775: 
tom@8775: 
tom@8775: -- bed heights
tom@8775: ALTER TABLE bed_height_single DROP CONSTRAINT fk_bed_single_river_id;
tom@8775: ALTER TABLE bed_height_single DROP CONSTRAINT fk_type;
tom@8775: ALTER TABLE bed_height_single DROP CONSTRAINT fk_location_system;
tom@8775: ALTER TABLE bed_height_single DROP CONSTRAINT fk_cur_elevation_model;
tom@8775: ALTER TABLE bed_height_single DROP CONSTRAINT fk_old_elevation_model;
tom@8775: ALTER TABLE bed_height_single DROP CONSTRAINT fk_range;
tom@8775: 
tom@8775: ALTER TABLE bed_height_single DROP COLUMN sounding_width;
tom@8775: 
tom@8775: ALTER TABLE bed_height_single RENAME TO bed_height;
tom@8775: 
tom@8775: ALTER TABLE bed_height ADD CONSTRAINT fk_bh_river_id
tom@8775:       FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE;
tom@8775: ALTER TABLE bed_height ADD CONSTRAINT fk_bh_type
tom@8775:       FOREIGN KEY (type_id) REFERENCES bed_height_type(id);
tom@8775: ALTER TABLE bed_height ADD CONSTRAINT fk_bh_location_system
tom@8775:       FOREIGN KEY (location_system_id) REFERENCES location_system(id);
tom@8775: ALTER TABLE bed_height ADD CONSTRAINT fk_bh_cur_elevation_model
tom@8775:       FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id);
tom@8775: ALTER TABLE bed_height ADD CONSTRAINT fk_bh_old_elevation_model
tom@8775:       FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id);
tom@8775: ALTER TABLE bed_height ADD CONSTRAINT fk_bh_range
tom@8775:       FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE;
tom@8775: 
tom@8775: -- the following is needed because Oracle is not able to mix DDL with
tom@8775: -- DML in a subselect
tom@8775: VARIABLE seqval NUMBER
tom@8775: BEGIN
tom@8775:     SELECT BED_HEIGHT_SINGLE_ID_SEQ.NEXTVAL INTO :seqval FROM DUAL;
tom@8775:     execute immediate('CREATE SEQUENCE BED_HEIGHT_ID_SEQ START WITH '
tom@8775:                       || :seqval);
tom@8775: END;
tom@8775: /
tom@8775: DROP SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ;
tom@8775: 
tom@8775: 
tom@8775: -- bed height values
tom@8775: ALTER TABLE bed_height_single_values
tom@8775:       DROP CONSTRAINT fk_bed_single_values_parent;
tom@8775: 
tom@8775: ALTER TABLE bed_height_single_values
tom@8775:       RENAME COLUMN bed_height_single_id TO bed_height_id;
tom@8775: ALTER TABLE bed_height_single_values DROP COLUMN width;
tom@8775: 
tom@8775: ALTER TABLE bed_height_single_values RENAME TO bed_height_values;
tom@8775: 
tom@8775: ALTER TABLE bed_height_values ADD CONSTRAINT fk_bed_values_parent
tom@8775:       FOREIGN KEY (bed_height_id) REFERENCES bed_height(id) ON DELETE CASCADE;
tom@8775: 
tom@8775: BEGIN
tom@8775:     SELECT BED_SINGLE_VALUES_ID_SEQ.NEXTVAL INTO :seqval FROM DUAL;
tom@8775:     execute immediate('CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ START WITH '
tom@8775:                       || :seqval);
tom@8775: END;
tom@8775: /
tom@8775: DROP SEQUENCE BED_SINGLE_VALUES_ID_SEQ;
tom@8775: 
tom@8775: 
tom@8775: -- measurement stations
tom@8775: ALTER TABLE measurement_station ADD CONSTRAINT check_m_type
tom@8775:       CHECK(measurement_type IN ('Geschiebe', 'Schwebstoff'));
tom@8775: 
tom@8775: 
tom@8775: -- SQ relations
tom@8775: ALTER TABLE sq_relation DROP CONSTRAINT fk_sqr_river_id;
tom@8775: 
tom@8775: ALTER TABLE sq_relation DROP COLUMN river_id;
tom@8775: 
tom@8775: ALTER TABLE sq_relation_value ADD CONSTRAINT sq_mstation_param_key
tom@8775:       UNIQUE(sq_relation_id, measurement_station_id, parameter);