Mercurial > dive4elements > river
view backend/doc/schema/oracle-minfo.sql @ 6601:5ecc6d4d73f2
Add official fixings to Waterlevel CSV Export (issue1384)
This searches the collection for staticwqkms artifacts that
contain official data and adds that data to the export.
The data is filtered by the calculation range and sorted by the
calculation direction.
author | Andre Heinecke <aheinecke@intevation.de> |
---|---|
date | Thu, 18 Jul 2013 13:16:33 +0200 |
parents | dc166b225775 |
children | a3fb76cf79b6 df402b3e0281 |
line wrap: on
line source
SET AUTOCOMMIT ON; CREATE SEQUENCE LOCATION_SYSTEM_SEQ; CREATE TABLE location_system ( id NUMBER(38,0) NOT NULL, name VARCHAR(32) NOT NULL, description VARCHAR(255), PRIMARY KEY(id) ); CREATE SEQUENCE ELEVATION_MODEL_SEQ; CREATE TABLE elevation_model ( id NUMBER(38,0) NOT NULL, name VARCHAR(32) NOT NULL, unit_id NUMBER(38,0) NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_unit FOREIGN KEY (unit_id) REFERENCES units(id) ); -- lookup table for bedheight types CREATE TABLE bed_height_type ( id NUMBER(38,0) NOT NULL, name VARCHAR(65) NOT NULL, PRIMARY KEY(id) ); INSERT INTO bed_height_type VALUES (1, 'Querprofile'); INSERT INTO bed_height_type VALUES (2, 'Flächenpeilung'); INSERT INTO bed_height_type VALUES (3, 'Flächen- u. Querprofilpeilungen'); INSERT INTO bed_height_type VALUES (4, 'DGM'); INSERT INTO bed_height_type VALUES (5, 'TIN'); INSERT INTO bed_height_type VALUES (6, 'Modell'); CREATE SEQUENCE BED_HEIGHT_SINGLE_ID_SEQ; CREATE TABLE bed_height_single ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, year NUMBER(38,0), sounding_width NUMBER(38,0), type_id NUMBER(38,0) NOT NULL, location_system_id NUMBER(38,0) NOT NULL, cur_elevation_model_id NUMBER(38,0) NOT NULL, old_elevation_model_id NUMBER(38,0), range_id NUMBER(38,0), evaluation_by VARCHAR(255), description VARCHAR(255), PRIMARY KEY(id), CONSTRAINT fk_bed_single_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, CONSTRAINT fk_type FOREIGN KEY (type_id) REFERENCES bed_height_type(id), CONSTRAINT fk_location_system FOREIGN KEY (location_system_id) REFERENCES location_system(id), CONSTRAINT fk_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE ); CREATE SEQUENCE BED_HEIGHT_EPOCH_ID_SEQ; CREATE TABLE bed_height_epoch ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0) NOT NULL, -- sounding_with NUMBER(38,0) NOT NULL, -- type_id NUMBER(38,0) NOT NULL, cur_elevation_model_id NUMBER(38,0) NOT NULL, old_elevation_model_id NUMBER(38,0), range_id NUMBER(38,0), evaluation_by VARCHAR(255), description VARCHAR(255), PRIMARY KEY(id), CONSTRAINT fk_time_interval FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id), CONSTRAINT fk_epoch_cur_elevation_model FOREIGN KEY (cur_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_epoch_old_elevation_model FOREIGN KEY (old_elevation_model_id) REFERENCES elevation_model(id), CONSTRAINT fk_epoch_range FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE ); CREATE SEQUENCE BED_SINGLE_VALUES_ID_SEQ; CREATE TABLE bed_height_single_values ( id NUMBER(38,0) NOT NULL, bed_height_single_id NUMBER(38,0) NOT NULL, station NUMBER(38,2) NOT NULL, height NUMBER(38,2), uncertainty NUMBER(38,2), data_gap NUMBER(38,2), sounding_width NUMBER(38,2), width NUMBER(38,2), PRIMARY KEY(id), UNIQUE (station, bed_height_single_id), CONSTRAINT fk_bed_single_values_parent FOREIGN KEY (bed_height_single_id) REFERENCES bed_height_single(id) ON DELETE CASCADE ); CREATE SEQUENCE BED_EPOCH_VALUES_ID_SEQ; CREATE TABLE bed_height_epoch_values ( id NUMBER(38,0) NOT NULL, bed_height_epoch_id NUMBER(38,0) NOT NULL, station NUMBER(38,2) NOT NULL, height NUMBER(38,2), PRIMARY KEY(id), CONSTRAINT fk_bed_epoch_values_parent FOREIGN KEY (bed_height_epoch_id) REFERENCES bed_height_epoch(id) ON DELETE CASCADE ); CREATE SEQUENCE DEPTHS_ID_SEQ; CREATE TABLE depths ( id NUMBER(38,0) NOT NULL, lower NUMBER(38,2) NOT NULL, upper NUMBER(38,2) NOT NULL, PRIMARY KEY(id) ); CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ; CREATE TABLE sediment_density ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, depth_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_sd_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, CONSTRAINT fk_sd_depth_id FOREIGN KEY (depth_id) REFERENCES depths(id) ); CREATE SEQUENCE SEDIMENT_DENSITY_VALUES_ID_SEQ; CREATE TABLE sediment_density_values ( id NUMBER(38,0) NOT NULL, sediment_density_id NUMBER(38,0) NOT NULL, station NUMBER(38,2) NOT NULL, shore_offset NUMBER(38,2), density NUMBER(38,2) NOT NULL, description VARCHAR(256), year NUMBER(38,0), PRIMARY KEY(id), CONSTRAINT fk_sdv_sediment_density_id FOREIGN KEY(sediment_density_id) REFERENCES sediment_density(id) ON DELETE CASCADE ); CREATE SEQUENCE MORPHOLOGIC_WIDTH_ID_SEQ; CREATE TABLE morphologic_width ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, unit_id NUMBER(38,0) NOT NULL, PRIMARY KEY(id), CONSTRAINT fk_mw_river_id FOREIGN KEY(river_id) REFERENCES rivers(id) ON DELETE CASCADE, CONSTRAINT fk_mw_unit_id FOREIGN KEY(unit_id) REFERENCES units(id) ); CREATE SEQUENCE MORPH_WIDTH_VALUES_ID_SEQ; CREATE TABLE morphologic_width_values ( id NUMBER(38,0) NOT NULL, morphologic_width_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, width NUMBER(38,3) NOT NULL, description VARCHAR(256), PRIMARY KEY(id), CONSTRAINT fk_mwv_morphologic_width_id FOREIGN KEY (morphologic_width_id) REFERENCES morphologic_width(id) ON DELETE CASCADE ); CREATE SEQUENCE DISCHARGE_ZONE_ID_SEQ; CREATE TABLE discharge_zone ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, gauge_name VARCHAR(64) NOT NULL, -- this is not very proper, but there are gauges with no db instance value NUMBER(38,3) NOT NULL, lower_discharge VARCHAR(16) NOT NULL, upper_discharge VARCHAR(16), PRIMARY KEY(id), CONSTRAINT fk_dz_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE ); CREATE SEQUENCE FLOW_VELOCITY_MODEL_ID_SEQ; CREATE TABLE flow_velocity_model ( id NUMBER(38,0) NOT NULL, discharge_zone_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvm_discharge_zone_id FOREIGN KEY (discharge_zone_id) REFERENCES discharge_zone (id) ON DELETE CASCADE ); CREATE SEQUENCE FLOW_VELOCITY_M_VALUES_ID_SEQ; CREATE TABLE flow_velocity_model_values ( id NUMBER(38,0) NOT NULL, flow_velocity_model_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, q NUMBER(38,3) NOT NULL, total_channel NUMBER(38,3) NOT NULL, main_channel NUMBER(38,3) NOT NULL, shear_stress NUMBER(38,3) NOT NULL, PRIMARY KEY(id), UNIQUE (station, flow_velocity_model_id), CONSTRAINT fk_fvv_flow_velocity_model_id FOREIGN KEY (flow_velocity_model_id) REFERENCES flow_velocity_model(id) ON DELETE CASCADE ); CREATE SEQUENCE FV_MEASURE_ID_SEQ; CREATE TABLE flow_velocity_measurements ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvm_rivers_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE ); CREATE SEQUENCE FV_MEASURE_VALUES_ID_SEQ; CREATE TABLE flow_velocity_measure_values ( id NUMBER(38,0) NOT NULL, measurements_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, datetime TIMESTAMP, w NUMBER(38,3) NOT NULL, q NUMBER(38,3) NOT NULL, v NUMBER(38,3) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_fvmv_measurements_id FOREIGN KEY (measurements_id) REFERENCES flow_velocity_measurements (id) ON DELETE CASCADE ); CREATE SEQUENCE GRAIN_FRACTION_ID_SEQ; CREATE TABLE grain_fraction ( id NUMBER(38,0) NOT NULL, name VARCHAR(64) NOT NULL, lower NUMBER(38,3), upper NUMBER(38,3), PRIMARY KEY (id), ); CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ; CREATE TABLE sediment_yield ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, grain_fraction_id NUMBER(38,0), unit_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_sy_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, CONSTRAINT fk_sy_grain_fraction_id FOREIGN KEY (grain_fraction_id) REFERENCES grain_fraction(id), CONSTRAINT fk_sy_unit_id FOREIGN KEY (unit_id) REFERENCES units(id), CONSTRAINT fk_sy_time_interval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) ); CREATE SEQUENCE SEDIMENT_YIELD_VALUES_ID_SEQ; CREATE TABLE sediment_yield_values ( id NUMBER(38,0) NOT NULL, sediment_yield_id NUMBER(38,0) NOT NULL, station NUMBER(38,3) NOT NULL, value NUMBER(38,3) NOT NULL, PRIMARY KEY (id), CONSTRAINT fk_syv_sediment_yield_id FOREIGN KEY (sediment_yield_id) REFERENCES sediment_yield(id) ON DELETE CASCADE ); CREATE SEQUENCE MEASUREMENT_STATION_ID_SEQ; CREATE TABLE measurement_station ( id NUMBER(38) NOT NULL, name VARCHAR(256) NOT NULL, river_id NUMBER(38) NOT NULL, station NUMBER(38,3) NOT NULL, range_id NUMBER(38), measurement_type VARCHAR(64) NOT NULL, riverside VARCHAR(16), reference_gauge_id NUMBER(38), -- store name of reference gauges here too, as not all are in gauges reference_gauge_name VARCHAR(64), observation_timerange_id NUMBER(38), operator VARCHAR(64), description VARCHAR(512), PRIMARY KEY (id), CONSTRAINT fk_ms_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, CONSTRAINT fk_ms_range_id FOREIGN KEY (range_id) REFERENCES ranges(id) ON DELETE CASCADE, CONSTRAINT fk_ms_reference_gauge_id FOREIGN KEY (reference_gauge_id) REFERENCES gauges(id) ON DELETE CASCADE, CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id) ); CREATE SEQUENCE SQ_RELATION_ID_SEQ; CREATE TABLE sq_relation ( id NUMBER(38,0) NOT NULL, river_id NUMBER(38,0) NOT NULL, time_interval_id NUMBER(38,0) NOT NULL, description VARCHAR(256), PRIMARY KEY (id), CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id) ON DELETE CASCADE, CONSTRAINT fk_sqr_tinterval_id FOREIGN KEY (time_interval_id) REFERENCES time_intervals(id) ); CREATE SEQUENCE SQ_RELATION_VALUES_ID_SEQ; CREATE TABLE sq_relation_value ( id NUMBER(38,0) NOT NULL, sq_relation_id NUMBER(38,0) NOT NULL, measurement_station_id NUMBER(38,0) NOT NULL, parameter VARCHAR(1) NOT NULL, a NUMBER(38,20) NOT NULL, b NUMBER(38,20) NOT NULL, qmax NUMBER(38,20) NOT NULL, rsq NUMBER(38,3), ntot NUMBER(38,0), noutl NUMBER(38,0), cferguson NUMBER(38,20), cduan NUMBER(38,20), PRIMARY KEY (id), CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id) ON DELETE CASCADE, CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id) ON DELETE CASCADE );