ingo@2805: SET AUTOCOMMIT ON; ingo@2805: ingo@2805: CREATE SEQUENCE LOCATION_SYSTEM_SEQ; ingo@2805: ingo@2805: CREATE TABLE location_system ( ingo@2805: id NUMBER(38,0) NOT NULL, ingo@2805: name VARCHAR(32) NOT NULL, ingo@2805: description VARCHAR(255), ingo@2805: PRIMARY KEY(id) ingo@2805: ); ingo@2805: ingo@2805: ingo@2805: CREATE SEQUENCE ELEVATION_MODEL_SEQ; ingo@2805: ingo@2805: CREATE TABLE elevation_model ( ingo@2805: id NUMBER(38,0) NOT NULL, ingo@2805: name VARCHAR(32) NOT NULL, ingo@2805: unit_id NUMBER(38,0) NOT NULL, ingo@2805: PRIMARY KEY(id), ingo@2805: CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) ingo@2805: ); ingo@2805: ingo@2805: CREATE SEQUENCE BED_HEIGHT_TYPE_SEQ; ingo@2805: ingo@2805: CREATE TABLE bed_height_type ( ingo@2805: id NUMBER(38,0) NOT NULL, ingo@2805: name VARCHAR(16) NOT NULL, ingo@2805: description VARCHAR(255), ingo@2805: PRIMARY KEY(id) ingo@2805: ); ingo@2805: ingo@2805: ingo@2805: ingo@2805: CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; ingo@2805: ingo@2805: CREATE TABLE bed_height_single ( ingo@2805: id NUMBER(38,0) NOT NULL, ingo@2809: river_id NUMBER(38,0) NOT NULL, ingo@2805: year NUMBER(38,0) NOT NULL, ingo@2807: sounding_width NUMBER(38,0) NOT NULL, ingo@2805: type_id NUMBER(38,0) NOT NULL, ingo@2807: location_system_id NUMBER(38,0) NOT NULL, ingo@2805: cur_elevation_model_id NUMBER(38,0) NOT NULL, ingo@2805: old_elevation_model_id NUMBER(38,0), ingo@2805: range_id NUMBER(38,0) NOT NULL, ingo@2805: evaluation_by VARCHAR(255), ingo@2805: description VARCHAR(255), ingo@2805: PRIMARY KEY(id), ingo@2809: CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), ingo@2805: CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), ingo@2807: CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), ingo@2805: CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), ingo@2805: CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), ingo@2805: CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ingo@2805: ); ingo@2805: ingo@2805: ingo@2805: CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; ingo@2805: ingo@2805: CREATE TABLE bed_height_epoch ( ingo@2805: id NUMBER(38,0) NOT NULL, ingo@2810: river_id NUMBER(38,0) NOT NULL, ingo@2805: time_interval_id NUMBER(38,0) NOT NULL, ingo@2805: -- sounding_with NUMBER(38,0) NOT NULL, ingo@2805: -- type_id NUMBER(38,0) NOT NULL, ingo@2805: cur_elevation_model_id NUMBER(38,0) NOT NULL, ingo@2805: old_elevation_model_id NUMBER(38,0), ingo@2805: range_id NUMBER(38,0) NOT NULL, ingo@2805: evaluation_by VARCHAR(255), ingo@2805: description VARCHAR(255), ingo@2805: PRIMARY KEY(id), ingo@2805: CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), ingo@2805: CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), ingo@2805: CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), ingo@2805: CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ingo@2805: ); ingo@2805: ingo@2805: ingo@2805: CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; ingo@2805: ingo@2805: CREATE TABLE bed_height_single_values ( ingo@2805: id NUMBER(38,0) NOT NULL, ingo@2805: bed_height_single_id NUMBER(38,0) NOT NULL, ingo@2805: station NUMBER(38,2) NOT NULL, ingo@2805: height NUMBER(38,2), ingo@2805: uncertainty NUMBER(38,2), ingo@2805: data_gap NUMBER(38,2) NOT NULL, ingo@2807: sounding_width NUMBER(38,2) NOT NULL, ingo@2805: width NUMBER(38,2) NOT NULL, ingo@2805: PRIMARY KEY(id), ingo@2805: CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ingo@2805: ); ingo@2805: ingo@2805: ingo@2805: CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; ingo@2805: ingo@2805: CREATE TABLE bed_height_epoch_values ( ingo@2805: id NUMBER(38,0) NOT NULL, ingo@2805: bed_height_epoch_id NUMBER(38,0) NOT NULL, ingo@2805: station NUMBER(38,2) NOT NULL, ingo@2805: height NUMBER(38,2), ingo@2805: PRIMARY KEY(id), ingo@2805: CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ingo@2805: ); ingo@2805: ingo@2812: ingo@2812: CREATE SEQUENCE DEPTHS_ID_SEQ; ingo@2812: ingo@2812: CREATE TABLE depths ( ingo@2812: id NUMBER(38,0) NOT NULL, ingo@2812: lower NUMBER(38,2) NOT NULL, ingo@2812: upper NUMBER(38,2) NOT NULL, sascha@2823: unit_id NUMBER(38,0) NOT NULL, ingo@2812: PRIMARY KEY(id), ingo@2812: CONSTRAINT fk_depths_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ingo@2812: ); ingo@2812: ingo@2812: ingo@2812: CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; ingo@2812: ingo@2812: CREATE TABLE sediment_density ( ingo@2812: id NUMBER(38,0) NOT NULL, ingo@2812: river_id NUMBER(38,0) NOT NULL, ingo@2812: depth_id NUMBER(38,0) NOT NULL, ingo@2816: unit_id NUMBER(38,0) NOT NULL, ingo@2817: description VARCHAR(256), ingo@2812: PRIMARY KEY(id), ingo@2812: CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), ingo@2816: CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), ingo@2816: CONSTRAINT fk_sd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ingo@2812: ); ingo@2812: ingo@2812: ingo@2812: CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; ingo@2812: ingo@2812: CREATE TABLE sediment_density_values ( ingo@2812: id NUMBER(38,0) NOT NULL, ingo@2812: sediment_density_id NUMBER(38,0) NOT NULL, ingo@2816: station NUMBER(38,2) NOT NULL, ingo@2812: density NUMBER(38,2) NOT NULL, ingo@2812: description VARCHAR(256), ingo@2812: PRIMARY KEY(id), ingo@2816: CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ingo@2812: ); ingo@2818: ingo@2818: ingo@2818: CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; ingo@2818: ingo@2818: CREATE TABLE morphologic_width ( ingo@2818: id NUMBER(38,0) NOT NULL, ingo@2818: river_id NUMBER(38,0) NOT NULL, ingo@2818: unit_id NUMBER(38,0) NOT NULL, ingo@2818: PRIMARY KEY(id), ingo@2818: CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id), ingo@2818: CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) ingo@2818: ); ingo@2818: ingo@2818: ingo@2818: CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; ingo@2818: ingo@2818: CREATE TABLE morphologic_width_values ( ingo@2818: id NUMBER(38,0) NOT NULL, ingo@2818: morphologic_width_id NUMBER(38,0) NOT NULL, ingo@2818: station NUMBER(38,3) NOT NULL, ingo@2818: width NUMBER(38,3) NOT NULL, ingo@2819: description VARCHAR(256), ingo@2818: PRIMARY KEY(id), ingo@2818: CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ingo@2818: ); ingo@2824: ingo@2824: ingo@2824: CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; ingo@2824: ingo@2824: CREATE TABLE discharge_zone ( ingo@2824: id NUMBER(38,0) NOT NULL, ingo@2824: river_id NUMBER(38,0) NOT NULL, ingo@2824: gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance ingo@2833: value NUMBER(38,3) NOT NULL, ingo@2833: lower_discharge VARCHAR(16) NOT NULL, ingo@2833: upper_discharge VARCHAR(16), ingo@2824: PRIMARY KEY(id), ingo@2833: CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ingo@2824: ); ingo@2824: ingo@2824: ingo@2824: CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; ingo@2824: ingo@2824: CREATE TABLE flow_velocity_model ( ingo@2824: id NUMBER(38,0) NOT NULL, ingo@2824: river_id NUMBER(38,0) NOT NULL, ingo@2824: discharge_zone_id NUMBER(38,0) NOT NULL, ingo@2824: description VARCHAR(256), ingo@2824: PRIMARY KEY (id), ingo@2824: CONSTRAINT fk_fvm_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), ingo@2824: CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ingo@2824: ); ingo@2824: ingo@2824: ingo@2824: CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; ingo@2824: ingo@2824: CREATE TABLE flow_velocity_model_values ( ingo@2824: id NUMBER(38,0) NOT NULL, ingo@2824: flow_velocity_model_id NUMBER(38,0) NOT NULL, ingo@2824: station NUMBER(38,3) NOT NULL, ingo@2828: q NUMBER(38,3) NOT NULL, ingo@2824: total_channel NUMBER(38,3) NOT NULL, ingo@2824: main_channel NUMBER(38,3) NOT NULL, ingo@2824: shear_stress NUMBER(38,3) NOT NULL, ingo@2824: PRIMARY KEY(id), ingo@2824: CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ingo@2824: ); ingo@2824: ingo@2824: ingo@2824: ingo@2829: CREATE SEQUENCE FV_MEASURE_ID_SEQ; ingo@2829: ingo@2829: CREATE TABLE flow_velocity_measurements ( ingo@2829: id NUMBER(38,0) NOT NULL, ingo@2829: river_id NUMBER(38,0) NOT NULL, ingo@2829: description VARCHAR(256), ingo@2829: PRIMARY KEY (id), ingo@2829: CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ingo@2829: ); ingo@2829: ingo@2829: CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; ingo@2829: ingo@2829: CREATE TABLE flow_velocity_measure_values ( ingo@2829: id NUMBER(38,0) NOT NULL, ingo@2829: measurements_id NUMBER(38,0) NOT NULL, ingo@2829: station NUMBER(38,3) NOT NULL, ingo@2829: datetime TIMESTAMP, ingo@2829: w NUMBER(38,3) NOT NULL, ingo@2829: q NUMBER(38,3) NOT NULL, ingo@2829: v NUMBER(38,3) NOT NULL, ingo@2829: description VARCHAR(256), ingo@2829: PRIMARY KEY (id), ingo@2829: CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ingo@2829: ); ingo@2834: ingo@2834: ingo@2834: CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; ingo@2834: ingo@2834: CREATE TABLE grain_fraction ( ingo@2834: id NUMBER(38,0) NOT NULL, ingo@2834: name VARCHAR(64) NOT NULL, ingo@2834: lower NUMBER(38,3), ingo@2834: upper NUMBER(38,3), ingo@2834: unit_id NUMBER (38,0), ingo@2834: PRIMARY KEY (id), ingo@2834: CONSTRAINT fk_gf_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ingo@2834: ); ingo@2834: ingo@2834: ingo@2834: CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; ingo@2834: ingo@2834: CREATE TABLE sediment_yield ( ingo@2834: id NUMBER(38,0) NOT NULL, ingo@2836: river_id NUMBER(38,0) NOT NULL, ingo@2834: grain_fraction_id NUMBER(38,0), ingo@2834: unit_id NUMBER(38,0) NOT NULL, ingo@2834: time_interval_id NUMBER(38,0) NOT NULL, ingo@2840: description VARCHAR(256), ingo@2834: PRIMARY KEY (id), ingo@2836: CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), ingo@2834: CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), ingo@2834: CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), ingo@2834: CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) ingo@2834: ); ingo@2834: ingo@2834: ingo@2834: CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ; ingo@2834: ingo@2834: CREATE TABLE sediment_yield_values ( ingo@2834: id NUMBER(38,0) NOT NULL, ingo@2834: sediment_yield_id NUMBER(38,0) NOT NULL, ingo@2834: station NUMBER(38,3) NOT NULL, ingo@2834: value NUMBER(38,3) NOT NULL, ingo@2834: PRIMARY KEY (id), ingo@2834: CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ingo@2834: ); ingo@2841: ingo@2841: ingo@2841: CREATE SEQUENCE WATERLEVEL_ID_SEQ; ingo@2841: ingo@2841: CREATE TABLE waterlevel ( ingo@2841: id NUMBER(38,0) NOT NULL, ingo@2841: river_id NUMBER(38,0) NOT NULL, ingo@2841: unit_id NUMBER(38,0) NOT NULL, ingo@2841: description VARCHAR(256), ingo@2841: PRIMARY KEY (id), ingo@2841: CONSTRAINT fk_w_river_id FOREIGN KEY (river_id) REFERENCES rivers(id), ingo@2841: CONSTRAINT fk_w_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ingo@2841: ); ingo@2841: ingo@2841: ingo@2841: CREATE SEQUENCE WATERLEVEL_Q_RANGES_ID_SEQ; ingo@2841: ingo@2841: CREATE TABLE waterlevel_q_range ( ingo@2841: id NUMBER(38,0) NOT NULL, ingo@2841: waterlevel_id NUMBER(38,0) NOT NULL, ingo@2841: q NUMBER(38,2) NOT NULL, ingo@2841: PRIMARY KEY (id), ingo@2841: CONSTRAINT fk_wqr_waterlevel_id FOREIGN KEY (waterlevel_id) REFERENCES waterlevel(id) ingo@2841: ); ingo@2841: ingo@2841: ingo@2841: CREATE SEQUENCE WATERLEVEL_VALUES_ID_SEQ; ingo@2841: ingo@2841: CREATE TABLE waterlevel_values( ingo@2841: id NUMBER(38,0) NOT NULL, ingo@2841: waterlevel_q_range_id NUMBER(38,0) NOT NULL, ingo@2844: station NUMBER(38,3) NOT NULL, ingo@2844: w NUMBER(38,2) NOT NULL, ingo@2841: PRIMARY KEY (id), ingo@2841: CONSTRAINT fk_wv_waterlevel_q_range_id FOREIGN KEY (waterlevel_q_range_id) REFERENCES waterlevel_q_range(id) ingo@2841: ); ingo@2841: ingo@2846: ingo@2846: CREATE SEQUENCE WATERLEVEL_DIFFERENCE_ID_SEQ; ingo@2846: ingo@2846: CREATE TABLE waterlevel_difference ( ingo@2846: id NUMBER(38,0) NOT NULL, ingo@2846: river_id NUMBER(38,0) NOT NULL, ingo@2849: unit_id NUMBER(38,0) NOT NULL, ingo@2846: description VARCHAR(256), ingo@2846: PRIMARY KEY (id), ingo@2849: CONSTRAINT fk_wd_river_id FOREIGN KEY (river_id) REFERENCES rivers (id), ingo@2849: CONSTRAINT fk_wd_unit_id FOREIGN KEY (unit_id) REFERENCES units(id) ingo@2846: ); ingo@2846: ingo@2846: ingo@2846: CREATE SEQUENCE WATERLEVEL_DIFF_COLUMN_ID_SEQ; ingo@2846: ingo@2846: CREATE TABLE waterlevel_difference_column ( ingo@2846: id NUMBER(38,0) NOT NULL, ingo@2846: difference_id NUMBER(38,0) NOT NULL, ingo@2846: description VARCHAR(256), ingo@2846: PRIMARY KEY (id), ingo@2846: CONSTRAINT fk_wdc_difference_id FOREIGN KEY (difference_id) REFERENCES waterlevel_difference (id) ingo@2846: ); ingo@2846: ingo@2846: ingo@2846: CREATE SEQUENCE WATERLEVEL_DIFF_VALUES_ID_SEQ; ingo@2846: ingo@2846: CREATE TABLE waterlevel_difference_values ( ingo@2846: id NUMBER(38,0) NOT NULL, ingo@2846: column_id NUMBER(38,0) NOT NULL, ingo@2846: station NUMBER(38,3) NOT NULL, ingo@2846: value NUMBER(38,2) NOT NULL, ingo@2846: PRIMARY KEY (id), ingo@2846: CONSTRAINT fk_wdv_column_id FOREIGN KEY (column_id) REFERENCES waterlevel_difference_column (id) ingo@2846: ); ingo@2846: