
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),
sascha@3793:     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: 
sascha@3793: 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: 
ingo@3321: 
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),
ingo@3329:     CONSTRAINT fk_sqr_river_id FOREIGN KEY (river_id) REFERENCES rivers(id),
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 (
ingo@3321:     id             NUMBER(38,0) NOT NULL,
ingo@3321:     sq_relation_id NUMBER(38,0) NOT NULL,
ingo@3321:     parameter      VARCHAR(16)  NOT NULL,
ingo@3321:     fraction       VARCHAR(32)  NOT NULL,
ingo@3321:     function       VARCHAR(32)  NOT NULL,
ingo@3321:     km             NUMBER(38,3) NOT NULL,
sascha@3793:     a              NUMBER(38, 3) NOT NULL,
ingo@3321:     b              NUMBER(38,3) NOT NULL,
ingo@3321:     PRIMARY KEY (id),
ingo@3321:     CONSTRAINT fk_sqr_id FOREIGN KEY (sq_relation_id) REFERENCES sq_relation(id)
ingo@3321: );




