sascha@2823: BEGIN; sascha@2823: sascha@2823: CREATE SEQUENCE LOCATION_SYSTEM_SEQ; sascha@2823: sascha@2823: CREATE TABLE location_system ( sascha@2823: id int NOT NULL, sascha@2823: name VARCHAR(32) NOT NULL, sascha@2823: description VARCHAR(255), sascha@2823: PRIMARY KEY(id) sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE ELEVATION_MODEL_SEQ; sascha@2823: sascha@2823: CREATE TABLE elevation_model ( sascha@2823: id int NOT NULL, sascha@2823: name VARCHAR(32) NOT NULL, sascha@2823: unit_id int NOT NULL, sascha@2823: PRIMARY KEY(id), sascha@2823: CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) sascha@2823: ); sascha@2823: sascha@2823: tom@5202: -- lookup table for bedheight types sascha@2823: CREATE TABLE bed_height_type ( sascha@2823: id int NOT NULL, teichmann@5273: name VARCHAR(64) NOT NULL, sascha@2823: PRIMARY KEY(id) sascha@2823: ); tom@5276: 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'); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE bed_height_single ( sascha@2823: id int NOT NULL, sascha@2823: river_id int NOT NULL, tom@5448: year int, tom@5448: sounding_width int, sascha@2823: type_id int NOT NULL, sascha@2823: location_system_id int NOT NULL, sascha@2823: cur_elevation_model_id int NOT NULL, sascha@2823: old_elevation_model_id int, tom@5448: range_id int, sascha@2823: evaluation_by VARCHAR(255), sascha@2823: description VARCHAR(255), sascha@2823: PRIMARY KEY(id), tom@4991: CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, sascha@2823: CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), sascha@2823: CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), sascha@2823: CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), sascha@2823: CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), tom@4991: CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE bed_height_epoch ( sascha@2823: id int NOT NULL, sascha@2823: river_id int NOT NULL, sascha@2823: time_interval_id int NOT NULL, tom@5894: -- sounding_width int, sascha@2823: -- type_id int NOT NULL, sascha@2823: cur_elevation_model_id int NOT NULL, sascha@2823: old_elevation_model_id int, tom@5448: range_id int, sascha@2823: evaluation_by VARCHAR(255), sascha@2823: description VARCHAR(255), sascha@2823: PRIMARY KEY(id), sascha@2823: CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), sascha@2823: CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), sascha@2823: CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), tom@4991: CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE bed_height_single_values ( sascha@2823: id int NOT NULL, sascha@2823: bed_height_single_id int NOT NULL, sascha@2823: station NUMERIC NOT NULL, sascha@2823: height NUMERIC, sascha@2823: uncertainty NUMERIC, felix@3953: data_gap NUMERIC, felix@3953: sounding_width NUMERIC, felix@3953: width NUMERIC, sascha@2823: PRIMARY KEY(id), tom@4991: CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE bed_height_epoch_values ( sascha@2823: id int NOT NULL, sascha@2823: bed_height_epoch_id int NOT NULL, sascha@2823: station NUMERIC NOT NULL, sascha@2823: height NUMERIC, sascha@2823: PRIMARY KEY(id), tom@4991: CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE DEPTHS_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE depths ( sascha@2823: id int NOT NULL, sascha@2823: lower NUMERIC NOT NULL, sascha@2823: upper NUMERIC NOT NULL, tom@5441: PRIMARY KEY(id) sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE sediment_density ( sascha@2823: id int NOT NULL, sascha@2823: river_id int NOT NULL, sascha@2823: depth_id int NOT NULL, sascha@2823: description VARCHAR(256), sascha@2823: PRIMARY KEY(id), tom@4991: 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) sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE sediment_density_values ( sascha@2823: id int NOT NULL, sascha@2823: sediment_density_id int NOT NULL, sascha@2823: station NUMERIC NOT NULL, tom@5507: shore_offset NUMERIC, sascha@2823: density NUMERIC NOT NULL, sascha@2823: description VARCHAR(256), rrenkert@4523: year int, sascha@2823: PRIMARY KEY(id), tom@4991: CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE morphologic_width ( sascha@2823: id int NOT NULL, sascha@2823: river_id int NOT NULL, sascha@2823: unit_id int NOT NULL, sascha@2823: PRIMARY KEY(id), tom@4991: CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE, sascha@2823: CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) sascha@2823: ); sascha@2823: sascha@2823: sascha@2823: CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; sascha@2823: sascha@2823: CREATE TABLE morphologic_width_values ( sascha@2823: id int NOT NULL, sascha@2823: morphologic_width_id int NOT NULL, sascha@2823: station NUMERIC NOT NULL, sascha@2823: width NUMERIC NOT NULL, sascha@2823: description VARCHAR(256), sascha@2823: PRIMARY KEY(id), tom@4991: CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE sascha@2823: ); sascha@2823: sascha@2858: sascha@2858: CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; sascha@2858: sascha@2858: CREATE TABLE discharge_zone ( sascha@2858: id int NOT NULL, sascha@2858: river_id int NOT NULL, sascha@2858: gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance sascha@2858: value NUMERIC NOT NULL, sascha@2858: lower_discharge VARCHAR(16) NOT NULL, sascha@2858: upper_discharge VARCHAR(16), sascha@2858: PRIMARY KEY(id), tom@4991: CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE sascha@2858: ); sascha@2858: sascha@2858: sascha@2858: CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; sascha@2858: sascha@2858: CREATE TABLE flow_velocity_model ( sascha@2858: id int NOT NULL, sascha@2858: discharge_zone_id int NOT NULL, sascha@2858: description VARCHAR(256), sascha@2858: PRIMARY KEY (id), tom@4991: CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE sascha@2858: ); sascha@2858: sascha@2858: sascha@2858: CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; sascha@2858: sascha@2858: CREATE TABLE flow_velocity_model_values ( sascha@2858: id int NOT NULL, sascha@2858: flow_velocity_model_id int NOT NULL, sascha@2858: station NUMERIC NOT NULL, sascha@2858: q NUMERIC NOT NULL, sascha@2858: total_channel NUMERIC NOT NULL, sascha@2858: main_channel NUMERIC NOT NULL, sascha@2858: shear_stress NUMERIC NOT NULL, sascha@2858: PRIMARY KEY(id), tom@5709: UNIQUE (station, flow_velocity_model_id), tom@4991: CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE sascha@2858: ); sascha@2858: sascha@2858: sascha@2858: sascha@2858: CREATE SEQUENCE FV_MEASURE_ID_SEQ; sascha@2858: sascha@2858: CREATE TABLE flow_velocity_measurements ( sascha@2858: id int NOT NULL, sascha@2858: river_id int NOT NULL, sascha@2858: description VARCHAR(256), sascha@2858: PRIMARY KEY (id), tom@4991: CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE sascha@2858: ); sascha@2858: sascha@2858: CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; sascha@2858: sascha@2858: CREATE TABLE flow_velocity_measure_values ( sascha@2858: id int NOT NULL, sascha@2858: measurements_id int NOT NULL, sascha@2858: station NUMERIC NOT NULL, sascha@2858: datetime TIMESTAMP, sascha@2858: w NUMERIC NOT NULL, sascha@2858: q NUMERIC NOT NULL, sascha@2858: v NUMERIC NOT NULL, sascha@2858: description VARCHAR(256), sascha@2858: PRIMARY KEY (id), tom@4991: CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE sascha@2858: ); sascha@2858: sascha@2858: sascha@2858: CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; sascha@2858: sascha@2858: CREATE TABLE grain_fraction ( sascha@2858: id int NOT NULL, sascha@2858: name VARCHAR(64) NOT NULL, sascha@2858: lower NUMERIC, sascha@2858: upper NUMERIC, sascha@2858: PRIMARY KEY (id), sascha@2858: ); sascha@2858: sascha@2858: sascha@2858: CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; sascha@2858: sascha@2858: CREATE TABLE sediment_yield ( sascha@2858: id int NOT NULL, sascha@2858: river_id int NOT NULL, sascha@2858: grain_fraction_id int, sascha@2858: unit_id int NOT NULL, sascha@2858: time_interval_id int NOT NULL, sascha@2858: description VARCHAR(256), sascha@2858: PRIMARY KEY (id), tom@4991: CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, sascha@2858: CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), sascha@2858: CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), sascha@2858: CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) sascha@2858: ); sascha@2858: sascha@2858: sascha@2858: CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ; sascha@2858: sascha@2858: CREATE TABLE sediment_yield_values ( sascha@2858: id int NOT NULL, sascha@2858: sediment_yield_id int NOT NULL, sascha@2858: station NUMERIC NOT NULL, sascha@2858: value NUMERIC NOT NULL, sascha@2858: PRIMARY KEY (id), tom@4991: CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE sascha@2858: ); sascha@2858: sascha@2858: ingo@4193: CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; ingo@4193: CREATE TABLE measurement_station ( aheinecke@5206: id int NOT NULL, aheinecke@5206: name VARCHAR(256) NOT NULL, aheinecke@5206: river_id int NOT NULL, aheinecke@5206: station NUMERIC NOT NULL, tom@5448: range_id int, aheinecke@5206: measurement_type VARCHAR(64) NOT NULL, aheinecke@5206: riverside VARCHAR(16), aheinecke@5206: reference_gauge_id int, tom@5456: -- store name of reference gauges here too, as not all are in gauges tom@5453: reference_gauge_name VARCHAR(64), aheinecke@5206: observation_timerange_id int, aheinecke@5206: operator VARCHAR(64), tom@5475: description VARCHAR(512), aheinecke@5206: PRIMARY KEY (id), aheinecke@5206: CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, aheinecke@5206: CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE, aheinecke@5206: CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, tom@5478: CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id) ingo@4193: ); ingo@4193: ingo@4193: sascha@3793: CREATE SEQUENCE SQ_RELATION_ID_SEQ; sascha@3793: sascha@3793: CREATE TABLE sq_relation ( sascha@3793: id int NOT NULL, sascha@3793: river_id int NOT NULL, sascha@3793: time_interval_id int NOT NULL, sascha@3793: description VARCHAR(256), sascha@3793: PRIMARY KEY (id), tom@4991: CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, sascha@3793: CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) sascha@3793: ); sascha@3793: sascha@3793: sascha@3793: CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; sascha@3793: sascha@3793: CREATE TABLE sq_relation_value ( rrenkert@5426: id int NOT NULL, rrenkert@5426: sq_relation_id int NOT NULL, rrenkert@5426: measurement_station_id int NOT NULL, tom@5455: parameter VARCHAR(1) NOT NULL, rrenkert@5426: a NUMERIC NOT NULL, rrenkert@5426: b NUMERIC NOT NULL, tom@5498: qmax NUMERIC NOT NULL, rrenkert@5426: rsq NUMERIC, rrenkert@5426: ntot int, rrenkert@5426: noutl int, rrenkert@5426: cferguson NUMERIC, rrenkert@5426: cduan NUMERIC, sascha@3793: PRIMARY KEY (id), tom@5406: 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 sascha@3793: ); sascha@2823: COMMIT;