Mercurial > dive4elements > river

view flys-backend/doc/schema/postgresql-minfo.sql @ 5779:ebec12def170

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Datacage: Add a pool of builders to make it multi threadable. XML DOM is not thread safe. Therefore the old implementation only allowed one thread to use the builder at a time. As the complexity of the configuration has increased over time this has become a bottleneck of the whole application because it took quiet some time to build a result. Furthermore the builder code path is visited very frequent. So many concurrent requests were piled up resulting in long waits for the users. To mitigate this problem a round robin pool of builders is used now. Each of the pooled builders has an independent copy of the XML template and can be run in parallel. The number of builders is determined by the system property 'flys.datacage.pool.size'. It defaults to 4.
author Sascha L. Teichmann <teichmann@intevation.de>
date Sun, 21 Apr 2013 12:48:09 +0200
parents e89d922df8ae
children
line wrap: on
line source
BEGIN;

CREATE SEQUENCE LOCATION_SYSTEM_SEQ;

CREATE TABLE location_system (
    id          int NOT NULL,
    name        VARCHAR(32)  NOT NULL,
    description VARCHAR(255),
    PRIMARY KEY(id)
);


CREATE SEQUENCE ELEVATION_MODEL_SEQ;

CREATE TABLE elevation_model (
    id          int NOT NULL,
    name        VARCHAR(32)  NOT NULL,
    unit_id     int 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          int NOT NULL,
    name        VARCHAR(64) 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                      int NOT NULL,
    river_id                int NOT NULL,
    year                    int,
    sounding_width          int,
    type_id                 int NOT NULL,
    location_system_id      int NOT NULL,
    cur_elevation_model_id  int NOT NULL,
    old_elevation_model_id  int,
    range_id                int,
    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                      int NOT NULL,
    river_id                int NOT NULL,
    time_interval_id        int NOT NULL,
    -- sounding_with           int NOT NULL,
    -- type_id                 int NOT NULL,
    cur_elevation_model_id  int NOT NULL,
    old_elevation_model_id  int,
    range_id                int,
    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                      int NOT NULL,
    bed_height_single_id    int NOT NULL,
    station                 NUMERIC NOT NULL,
    height                  NUMERIC,
    uncertainty             NUMERIC,
    data_gap                NUMERIC,
    sounding_width          NUMERIC,
    width                   NUMERIC,
    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                      int NOT NULL,
    bed_height_epoch_id     int NOT NULL,
    station                 NUMERIC NOT NULL,
    height                  NUMERIC,
    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      int NOT NULL,
    lower   NUMERIC NOT NULL,
    upper   NUMERIC NOT NULL,
    PRIMARY KEY(id)
);


CREATE SEQUENCE SEDIMENT_DENSITY_ID_SEQ;

CREATE TABLE sediment_density (
    id          int NOT NULL,
    river_id    int NOT NULL,
    depth_id    int 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                  int NOT NULL,
    sediment_density_id int NOT NULL,
    station             NUMERIC NOT NULL,
    shore_offset	NUMERIC,
    density             NUMERIC NOT NULL,
    description         VARCHAR(256),
    year                int,
    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          int NOT NULL,
    river_id    int NOT NULL,
    unit_id     int 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                      int NOT NULL,
    morphologic_width_id    int NOT NULL,
    station                 NUMERIC NOT NULL,
    width                   NUMERIC 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                      int NOT NULL,
    river_id                int NOT NULL,
    gauge_name              VARCHAR(64)  NOT NULL, -- this is not very proper, but there are gauges with no db instance
    value                   NUMERIC 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                  int NOT NULL,
    discharge_zone_id   int 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                      int NOT NULL,
    flow_velocity_model_id  int NOT NULL,
    station                 NUMERIC NOT NULL,
    q                       NUMERIC NOT NULL,
    total_channel           NUMERIC NOT NULL,
    main_channel            NUMERIC NOT NULL,
    shear_stress            NUMERIC 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          int NOT NULL,
    river_id    int 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              int NOT NULL,
    measurements_id int NOT NULL,
    station         NUMERIC NOT NULL,
    datetime        TIMESTAMP,
    w               NUMERIC NOT NULL,
    q               NUMERIC NOT NULL,
    v               NUMERIC 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      int   NOT NULL,
    name    VARCHAR(64)    NOT NULL,
    lower   NUMERIC,
    upper   NUMERIC,
    PRIMARY KEY (id),
);


CREATE SEQUENCE SEDIMENT_YIELD_ID_SEQ;

CREATE TABLE sediment_yield (
    id                  int NOT NULL,
    river_id            int NOT NULL,
    grain_fraction_id   int,
    unit_id             int NOT NULL,
    time_interval_id    int 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                  int NOT NULL,
    sediment_yield_id   int NOT NULL,
    station             NUMERIC NOT NULL,
    value               NUMERIC 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                       int          NOT NULL,
    name                     VARCHAR(256) NOT NULL,
    river_id                 int          NOT NULL,
    station                  NUMERIC      NOT NULL,
    range_id                 int,
    measurement_type         VARCHAR(64)  NOT NULL,
    riverside                VARCHAR(16),
    reference_gauge_id       int,
    -- store name of reference gauges here too, as not all are in gauges
    reference_gauge_name     VARCHAR(64),
    observation_timerange_id int,
    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               int NOT NULL,
    river_id         int NOT NULL,
    time_interval_id int 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                     int NOT NULL,
    sq_relation_id         int NOT NULL,
    measurement_station_id int NOT NULL,
    parameter              VARCHAR(1) NOT NULL,
    a                      NUMERIC NOT NULL,
    b                      NUMERIC NOT NULL,
    qmax                   NUMERIC NOT NULL,
    rsq                    NUMERIC,
    ntot                   int,
    noutl                  int,
    cferguson              NUMERIC,
    cduan                  NUMERIC,
    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
);
COMMIT;
http://dive4elements.wald.intevation.org