
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,
sascha@2823:     -- sounding_with           int NOT NULL,
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,
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@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@5453:     -- store name and km of reference gauges here too, as not all are in gauges
tom@5453:     reference_gauge_name     VARCHAR(64),
tom@5453:     reference_gauge_station  NUMERIC,
aheinecke@5206:     observation_timerange_id int,
aheinecke@5206:     operator                 VARCHAR(64),
aheinecke@5206:     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,
aheinecke@5206:     CONSTRAINT fk_ms_observation_timerange_id FOREIGN KEY (observation_timerange_id) REFERENCES time_intervals(id),
aheinecke@5206:     UNIQUE (river_id, station)
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,
rrenkert@5426:     qmax                   NUMERIC,
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@5448:     CONSTRAINT fk_mstation_id FOREIGN KEY (measurement_station_id) REFERENCES measurement_station(id)
sascha@3793: );
sascha@2823: COMMIT;




