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: tom@5224: -- lookup table for bedheight types ingo@2805: CREATE TABLE bed_height_type ( ingo@2805: id NUMBER(38,0) NOT NULL, teichmann@5273: name VARCHAR(65) NOT NULL, ingo@2805: PRIMARY KEY(id) ingo@2805: ); tom@5275: INSERT INTO bed_height_type VALUES (1, 'Querprofile'); tom@5275: INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung'); tom@5275: INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen'); tom@5275: INSERT INTO bed_height_type VALUES (4, 'DGM'); tom@5275: INSERT INTO bed_height_type VALUES (5, 'TIN'); tom@5275: INSERT INTO bed_height_type VALUES (6, 'Modell'); ingo@2805: ingo@2805: tom@8559: CREATE SEQUENCE BED_HEIGHT_ID_SEQ; ingo@2805: tom@8559: CREATE TABLE bed_height ( ingo@2805: id NUMBER(38,0) NOT NULL, ingo@2809: river_id NUMBER(38,0) NOT NULL, tom@5448: year NUMBER(38,0), 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), tom@5448: range_id NUMBER(38,0), ingo@2805: evaluation_by VARCHAR(255), ingo@2805: description VARCHAR(255), ingo@2805: PRIMARY KEY(id), tom@8559: CONSTRAINT fk_bed_river_id FOREIGN KEY (river_id) tom@8559: REFERENCES rivers(id) ON DELETE CASCADE, ingo@2805: CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), tom@8559: CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) tom@8559: REFERENCES location_system(id), tom@8559: CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) tom@8559: REFERENCES elevation_model(id), tom@8559: CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) tom@8559: REFERENCES elevation_model(id), tom@8559: CONSTRAINT fk_range FOREIGN KEY (range_id) tom@8559: REFERENCES ranges(id) ON DELETE CASCADE ingo@2805: ); ingo@2805: ingo@2805: tom@8559: CREATE SEQUENCE BED_HEIGHT_VALUES_ID_SEQ; ingo@2805: tom@8559: CREATE TABLE bed_height_values ( ingo@2805: id NUMBER(38,0) NOT NULL, tom@8559: bed_height_id NUMBER(38,0) NOT NULL, tom@6203: station DOUBLE PRECISION NOT NULL, tom@6203: height DOUBLE PRECISION, tom@6203: uncertainty DOUBLE PRECISION, tom@6203: data_gap DOUBLE PRECISION, tom@6203: sounding_width DOUBLE PRECISION, ingo@2805: PRIMARY KEY(id), tom@8559: UNIQUE (station, bed_height_id), tom@8559: CONSTRAINT fk_bed_values_parent FOREIGN KEY (bed_height_id) tom@8559: REFERENCES bed_height(id) ON DELETE CASCADE ingo@2805: ); ingo@2805: ingo@2805: 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, tom@5441: PRIMARY KEY(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@2817: description VARCHAR(256), ingo@2812: PRIMARY KEY(id), tom@5577: CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, tom@5441: CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(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, tom@5507: shore_offset NUMBER(38,2), ingo@2812: density NUMBER(38,2) NOT NULL, ingo@2812: description VARCHAR(256), rrenkert@4523: year NUMBER(38,0), ingo@2812: PRIMARY KEY(id), tom@5577: CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE ingo@2812: ); ingo@2818: ingo@2818: rrenkert@7863: CREATE SEQUENCE POROSITY_ID_SEQ; rrenkert@7863: rrenkert@7863: CREATE TABLE porosity ( tom@7866: id NUMBER(38,0) NOT NULL, tom@7866: river_id NUMBER(38,0) NOT NULL, tom@7866: depth_id NUMBER(38,0) NOT NULL, rrenkert@7863: description VARCHAR(256), tom@7866: time_interval_id NUMBER(38,0) NOT NULL, rrenkert@7863: PRIMARY KEY(id), rrenkert@7863: CONSTRAINT fk_p_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, rrenkert@7863: CONSTRAINT fk_p_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id), rrenkert@7863: CONSTRAINT fk_p_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) rrenkert@7863: ); rrenkert@7863: rrenkert@7863: rrenkert@7863: CREATE SEQUENCE POROSITY_VALUES_ID_SEQ; rrenkert@7863: rrenkert@7863: CREATE TABLE porosity_values ( tom@7866: id NUMBER(38,0) NOT NULL, tom@7866: porosity_id NUMBER(38,0) NOT NULL, tom@7866: station DOUBLE PRECISION NOT NULL, tom@7866: shore_offset DOUBLE PRECISION, tom@7866: porosity DOUBLE PRECISION NOT NULL, rrenkert@7863: description VARCHAR(256), rrenkert@7863: PRIMARY KEY(id), rrenkert@7863: CONSTRAINT fk_pv_porosity_id FOREIGN KEY(porosity_id) REFERENCES porosity(id) ON DELETE CASCADE rrenkert@7863: ); rrenkert@7863: rrenkert@7863: 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), tom@5577: CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE, 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), tom@5577: CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE 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, tom@6369: lower_discharge VARCHAR(64) NOT NULL, tom@6369: upper_discharge VARCHAR(64), ingo@2824: PRIMARY KEY(id), tom@5577: CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE 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: discharge_zone_id NUMBER(38,0) NOT NULL, ingo@2824: description VARCHAR(256), ingo@2824: PRIMARY KEY (id), tom@5577: CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE 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), tom@5709: UNIQUE (station, flow_velocity_model_id), tom@5577: CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE 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), tom@5577: CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE 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), tom@5577: CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE 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), tom@8032: PRIMARY KEY (id), tom@8032: UNIQUE(name, lower, upper) ingo@2834: ); tom@8032: -- single fractions tom@8032: INSERT INTO grain_fraction VALUES (1, 'coarse', 16, 200); tom@8032: INSERT INTO grain_fraction VALUES (2, 'fine_middle', 2, 16); tom@8032: INSERT INTO grain_fraction VALUES (3, 'sand', 0.063, 2); tom@8032: INSERT INTO grain_fraction VALUES (4, 'susp_sand', 0.063, 2); tom@8032: INSERT INTO grain_fraction VALUES (5, 'susp_sand_bed', 0.063, 2); tom@8032: INSERT INTO grain_fraction VALUES (6, 'suspended_sediment', 0, 0.063); tom@8032: -- aggregations of fractions tom@8032: INSERT INTO grain_fraction VALUES (7, 'total', 0, 200); tom@8032: INSERT INTO grain_fraction VALUES (8, 'bed_load', 0.063, 200); tom@8032: INSERT INTO grain_fraction VALUES (9, 'suspended_load', 0, 2); tom@8032: ingo@2834: tom@7461: --lookup table for sediment yield kinds tom@8072: CREATE TABLE sediment_load_kinds ( tom@7461: id int PRIMARY KEY NOT NULL, tom@7461: kind VARCHAR(64) NOT NULL tom@7461: ); tom@8072: INSERT INTO sediment_load_kinds (id, kind) VALUES (0, 'non-official'); tom@8072: INSERT INTO sediment_load_kinds (id, kind) VALUES (1, 'official'); ingo@2834: tom@8072: CREATE SEQUENCE SEDIMENT_LOAD_LS_ID_SEQ; ingo@2834: tom@8072: CREATE TABLE sediment_load_ls ( 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, tom@8059: sq_time_interval_id NUMBER(38,0), ingo@2840: description VARCHAR(256), felix@6721: kind NUMBER(38,0), ingo@2834: PRIMARY KEY (id), tom@8072: CONSTRAINT fk_slls_river_id FOREIGN KEY (river_id) tom@8072: REFERENCES rivers(id) ON DELETE CASCADE, tom@8072: CONSTRAINT fk_slls_kind_id FOREIGN KEY (kind) tom@8072: REFERENCES sediment_load_kinds(id), tom@8072: CONSTRAINT fk_slls_grain_fraction_id FOREIGN KEY (grain_fraction_id) tom@8072: REFERENCES grain_fraction(id), tom@8072: CONSTRAINT fk_slls_unit_id FOREIGN KEY (unit_id) tom@8072: REFERENCES units(id), tom@8072: CONSTRAINT fk_slls_time_interval_id FOREIGN KEY (time_interval_id) tom@8072: REFERENCES time_intervals(id), tom@8072: CONSTRAINT fk_slls_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) tom@8059: REFERENCES time_intervals(id) ingo@2834: ); ingo@2834: ingo@2834: tom@8072: CREATE SEQUENCE SEDIMENT_LOAD_LS_VALUES_ID_SEQ; ingo@2834: tom@8072: CREATE TABLE sediment_load_ls_values ( ingo@2834: id NUMBER(38,0) NOT NULL, tom@8072: sediment_load_ls_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), tom@8072: CONSTRAINT fk_sllsv_sediment_load_ls_id FOREIGN KEY (sediment_load_ls_id) tom@8072: REFERENCES sediment_load_ls(id) ON DELETE CASCADE ingo@2834: ); ingo@2841: ingo@2841: aheinecke@5206: CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; aheinecke@5206: CREATE TABLE measurement_station ( tom@8412: id int NOT NULL, tom@8412: range_id int NOT NULL, tom@8412: reference_gauge_id int, tom@8412: time_interval_id int, tom@8412: name VARCHAR2(256 CHAR) NOT NULL, tom@8412: measurement_type VARCHAR2(64 CHAR) NOT NULL, tom@8412: riverside VARCHAR2(16 CHAR), tom@5456: -- store name of reference gauges here too, as not all are in gauges tom@8412: reference_gauge_name VARCHAR2(64 CHAR), tom@8412: operator VARCHAR2(64 CHAR), tom@8415: commentary VARCHAR2(512 CHAR), tom@8412: PRIMARY KEY (id), tom@8412: CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) tom@8412: REFERENCES ranges(id) ON DELETE CASCADE, tom@8412: CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) tom@8412: REFERENCES gauges(id) ON DELETE CASCADE, tom@8412: CONSTRAINT fk_time_interval_id FOREIGN KEY (time_interval_id) tom@8412: REFERENCES time_intervals(id) aheinecke@5206: ); aheinecke@5206: aheinecke@5206: tom@8017: CREATE SEQUENCE SEDIMENT_LOAD_ID_SEQ; tom@8017: tom@8018: CREATE TABLE sediment_load ( tom@8017: id int NOT NULL, tom@8017: grain_fraction_id int NOT NULL, tom@8017: time_interval_id int NOT NULL, tom@8017: sq_time_interval_id int, tom@8017: description VARCHAR(256), tom@8017: kind int, tom@8017: PRIMARY KEY (id), tom@8017: CONSTRAINT fk_sl_kind_id FOREIGN KEY (kind) tom@8072: REFERENCES sediment_load_kinds(id), tom@8017: CONSTRAINT fk_sl_grain_fraction_id FOREIGN KEY (grain_fraction_id) tom@8017: REFERENCES grain_fraction(id), tom@8017: CONSTRAINT fk_sl_time_interval_id FOREIGN KEY (time_interval_id) tom@8017: REFERENCES time_intervals(id), tom@8017: CONSTRAINT fk_sl_sq_time_interval_id FOREIGN KEY (sq_time_interval_id) tom@8017: REFERENCES time_intervals(id) tom@8017: ); tom@8017: tom@8017: tom@8017: CREATE SEQUENCE SEDIMENT_LOAD_VALUES_ID_SEQ; tom@8017: tom@8017: CREATE TABLE sediment_load_values ( tom@8017: id int NOT NULL, tom@8017: sediment_load_id int NOT NULL, tom@8017: measurement_station_id int NOT NULL, tom@8017: value DOUBLE PRECISION NOT NULL, tom@8017: PRIMARY KEY (id), tom@8017: CONSTRAINT fk_slv_sediment_load_id FOREIGN KEY (sediment_load_id) tom@8019: REFERENCES sediment_load(id) ON DELETE CASCADE, tom@8017: CONSTRAINT fk_slv_m_station_id FOREIGN KEY (measurement_station_id) tom@8017: REFERENCES measurement_station(id) ON DELETE CASCADE tom@8017: ); tom@8017: tom@8017: ingo@3321: CREATE SEQUENCE SQ_RELATION_ID_SEQ; ingo@3321: ingo@3321: CREATE TABLE sq_relation ( ingo@3321: id NUMBER(38,0) NOT NULL, ingo@3329: river_id NUMBER(38,0) NOT NULL, ingo@3321: time_interval_id NUMBER(38,0) NOT NULL, ingo@3321: description VARCHAR(256), ingo@3321: PRIMARY KEY (id), tom@5577: CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, ingo@3321: CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) ingo@3321: ); ingo@3321: ingo@3321: ingo@3321: CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; ingo@3321: ingo@3321: CREATE TABLE sq_relation_value ( rrenkert@5426: id NUMBER(38,0) NOT NULL, rrenkert@5426: sq_relation_id NUMBER(38,0) NOT NULL, rrenkert@5426: measurement_station_id NUMBER(38,0) NOT NULL, tom@5455: parameter VARCHAR(1) NOT NULL, rrenkert@5426: a NUMBER(38,20) NOT NULL, rrenkert@5426: b NUMBER(38,20) NOT NULL, tom@5498: qmax NUMBER(38,20) NOT NULL, rrenkert@5426: rsq NUMBER(38,3), rrenkert@5426: ntot NUMBER(38,0), rrenkert@5426: noutl NUMBER(38,0), rrenkert@5426: cferguson NUMBER(38,20), rrenkert@5426: cduan NUMBER(38,20), ingo@3321: PRIMARY KEY (id), tom@5577: CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE, tom@5684: CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) ON DELETE CASCADE ingo@3321: );