--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/backend/doc/schema/oracle-minfo.sql	Thu Apr 25 15:23:37 2013 +0200
@@ -0,0 +1,336 @@
+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),
+    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
+);