view flys-backend/doc/schema/oracle.sql @ 4269:0c766c475805

Add Panel and Tree UI classes for dispayling the measurement station info The new ui classes are using the new extracted base class InfoPanel and InfoTree which are in common with the gauge info.
author Björn Ricks <bjoern.ricks@intevation.de>
date Fri, 26 Oct 2012 12:22:06 +0200
parents 948c7289fc42
children 83a42e6a562d
line wrap: on
line source
-- ANNOTATION_TYPES
CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;

CREATE TABLE annotation_types (
    id              NUMBER(38,0) NOT NULL, 
    name            VARCHAR2(255),
    PRIMARY KEY     (id)
);


-- ANNOTATIONS
CREATE SEQUENCE ANNOTATIONS_ID_SEQ;

CREATE TABLE annotations (
    id              NUMBER(38,0) NOT NULL, 
    attribute_id    NUMBER(38,0),
    edge_id         NUMBER(38,0),
    position_id     NUMBER(38,0),
    range_id        NUMBER(38,0),
    type_id         NUMBER(38,0),
    PRIMARY KEY     (id)
);


-- ATTRIBUTES 
CREATE SEQUENCE ATTRIBUTES_ID_SEQ;

CREATE TABLE attributes (
    id              NUMBER(38,0) NOT NULL, 
    value           VARCHAR2(255), 
    primary key     (id)
);


-- CROSS_SECTION_LINES
CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;

CREATE TABLE cross_section_lines (
    id                  NUMBER(38,0) NOT NULL,
    km                  NUMBER(38,2),
    cross_section_id    NUMBER(38,0), 
    PRIMARY KEY         (id)
);


-- CROSS_SECTION_POINTS
CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;

CREATE TABLE cross_section_points (
    id                      NUMBER(38,0) NOT NULL,
    col_pos                 NUMBER(38,0),
    x                       NUMBER(38,2),
    y                       NUMBER(38,2),
    cross_section_line_id   NUMBER(38,0),
    PRIMARY KEY             (id)
);


-- CROSS_SECTIONS
CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;

CREATE TABLE cross_sections (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    river_id            NUMBER(38,0),
    time_interval_id    NUMBER(38,0),
    PRIMARY KEY         (id)
);

-- Indices for faster access of the points
CREATE INDEX cross_section_lines_km_idx
    ON cross_section_lines(km);
CREATE INDEX cross_section_points_line_idx
    ON cross_section_points(cross_section_line_id);

-- DISCHARGE_TABLE_VALUES
CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;

CREATE TABLE discharge_table_values (
    id                  NUMBER(38,0) NOT NULL,
    q                   NUMBER(38,2),
    w                   NUMBER(38,2),
    table_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- DISCHARGE_TABLES
CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;

CREATE TABLE discharge_tables (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    kind                NUMBER(38,0),
    gauge_id            NUMBER(38,0),
    time_interval_id    NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- EDGES
CREATE SEQUENCE EDGES_ID_SEQ;

CREATE TABLE edges (
    id                  NUMBER(38,0) NOT NULL,
    bottom              NUMBER(38,2),
    top                 NUMBER(38,2),
    PRIMARY KEY         (id)
);


-- GAUGES
CREATE SEQUENCE GAUGES_ID_SEQ;

CREATE TABLE gauges (
    id                  NUMBER(38,0) NOT NULL,
    aeo                 NUMBER(38,2),
    datum               NUMBER(38,2), 
    name                VARCHAR2(255),
    station             NUMBER(38,2),
    official_number     NUMBER(38,0),
    range_id            NUMBER(38,0),
    river_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- HYK_ENTRIES
CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;

CREATE TABLE hyk_entries (
    id                  NUMBER(38,0) NOT NULL,
    km                  NUMBER(38,2),
    measure             TIMESTAMP,
    hyk_id              NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- HYK_FLOW_ZONE_TYPES
CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;

CREATE TABLE hyk_flow_zone_types (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    name                VARCHAR2(255),
    PRIMARY KEY         (id)
);


-- HYK_FLOW_ZONES
CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;

CREATE TABLE hyk_flow_zones (
    id                  NUMBER(38,0) NOT NULL,
    a                   NUMBER(38,2),
    b                   NUMBER(38,2),
    formation_id        NUMBER(38,0),
    type_id             NUMBER(38,0),
    primary key         (id)
);


-- HYK_FORMATIONS
CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;

CREATE TABLE hyk_formations (
    id                  NUMBER(38,0) NOT NULL,
    bottom              NUMBER(38,2),
    distance_hf         NUMBER(38,2),
    distance_vl         NUMBER(38,2),
    distance_vr         NUMBER(38,2),
    formation_num       NUMBER(38,0),
    top                 NUMBER(38,2),
    hyk_entry_id        NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- HYKS
CREATE SEQUENCE HYKS_ID_SEQ;

CREATE TABLE hyks (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    river_id            NUMBER(38,0),
    primary key         (id)
);


-- MAIN_VALUE_TYPES
CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;

CREATE TABLE main_value_types (
    id                  NUMBER(38,0) NOT NULL,
    name                VARCHAR2(255),
    PRIMARY KEY         (id)
);


-- MAIN_VALUES
CREATE SEQUENCE MAIN_VALUES_ID_SEQ;

CREATE TABLE main_values (
    id                  NUMBER(38,0) NOT NULL,
    value               NUMBER(38,2),
    gauge_id            NUMBER(38,0),
    named_value_id      NUMBER(38,0),
    time_interval_id    NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- NAMED_MAIN_VALUES
CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;

CREATE TABLE named_main_values (
    id                  NUMBER(38,0) NOT NULL,
    name                VARCHAR2(255),
    type_id             NUMBER(38,0),
    PRIMARY KEY (id)
);


-- POSITIONS
CREATE SEQUENCE POSITIONS_ID_SEQ;

CREATE TABLE positions (
    id                  NUMBER(38,0) NOT NULL,
    value               VARCHAR2(255 char),
    PRIMARY KEY         (id)
);


--- RANGES
CREATE SEQUENCE RANGES_ID_SEQ;

CREATE TABLE ranges (
    id                  NUMBER(38,0) NOT NULL,
    a                   NUMBER(38,10),
    b                   NUMBER(38,10),
    river_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- RIVERS
CREATE SEQUENCE RIVERS_ID_SEQ;

CREATE TABLE rivers (
    id                  NUMBER(38,0) NOT NULL,
    official_number     NUMBER(38,0),
    km_up               NUMBER(38,0),
    name                VARCHAR2(255),
    wst_unit_id         NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- TIME_INTERVALS
CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;

CREATE TABLE time_intervals (
    id                  NUMBER(38,0) NOT NULL, 
    start_time          TIMESTAMP,
    stop_time           TIMESTAMP,
    PRIMARY KEY         (id)
);


--- UNITS
CREATE SEQUENCE UNITS_ID_SEQ;

CREATE TABLE units (
    id                  NUMBER(38,0) NOT NULL,
    name                VARCHAR2(255),
    PRIMARY KEY         (id)
);


-- WST_COLUMN_Q_RANGES
CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;

CREATE TABLE wst_column_q_ranges (
    id                  NUMBER(38,0) NOT NULL,
    wst_column_id       NUMBER(38,0),
    wst_q_range_id      NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- WST_COLUMN_VALUES
CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;

CREATE TABLE wst_column_values (
    id                  NUMBER(38,0) NOT NULL,
    position            NUMBER(38,5),
    w                   NUMBER(38,5),
    wst_column_id       NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- WST_COLUMNS
CREATE SEQUENCE WST_COLUMNS_ID_SEQ;

CREATE TABLE wst_columns (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    name                VARCHAR2(255),
    position            NUMBER(38,0),
    time_interval_id    NUMBER(38,0),
    wst_id              NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- WST_Q_RANGES
CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;

CREATE TABLE wst_q_ranges (
    id                  NUMBER(38,0) NOT NULL,
    q                   NUMBER(38,5),
    range_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- WSTS
CREATE SEQUENCE WSTS_ID_SEQ;

CREATE TABLE wsts (
    id                  NUMBER(38,0) NOT NULL,
    description         VARCHAR2(255),
    kind                NUMBER(38,0),
    river_id            NUMBER(38,0),
    PRIMARY KEY         (id)
);


-- ADD CONSTRAINTs
ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges;
ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections;
ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines;
ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers;
ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables foreign key (table_id) REFERENCES discharge_tables;
ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers;
ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges;
ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks;
ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations;
ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers;
ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries;
ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges;
ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers;
ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges;
ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns;
ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts;
ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES;
ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers;

-- VIEWS

CREATE VIEW wst_value_table AS
    SELECT wcv.position AS position,
           w,
           (SELECT q
            FROM   wst_column_q_ranges wcqr
                   JOIN wst_q_ranges wqr
                     ON wcqr.wst_q_range_id = wqr.id
                   JOIN ranges r
                     ON r.id = wqr.range_id
            WHERE  wcqr.wst_column_id = wc.id
                   AND wcv.position BETWEEN r.a AND r.b) AS q,
           wc.position                                   AS column_pos,
           w.id                                          AS wst_id
    FROM   wst_column_values wcv
           JOIN wst_columns wc
             ON wcv.wst_column_id = wc.id
           JOIN wsts w
             ON wc.wst_id = w.id
    ORDER  BY wcv.position ASC,
          wc.position DESC;

-- view to select the w values of a WST
CREATE VIEW wst_w_values  AS
    SELECT wcv.position   AS km, 
           wcv.w          AS w,  
           wc.position    AS column_pos, 
           w.id           AS wst_id
        FROM wst_column_values wcv
        JOIN wst_columns wc ON wcv.wst_column_id = wc.id
        JOIN wsts w         ON wc.wst_id = w.id
    ORDER BY wcv.position, wc.position;

-- view to select the q values of a WST
CREATE VIEW wst_q_values AS
    SELECT wc.position AS column_pos,
           wqr.q       AS q, 
           r.a         AS a, 
           r.b         AS b,
           wc.wst_id   AS wst_id
    FROM wst_column_q_ranges wcqr
    JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
    JOIN ranges r         ON wqr.range_id        = r.id
    JOIN wst_columns wc   ON wcqr.wst_column_id  = wc.id
    ORDER BY wc.position, wcqr.wst_column_id, r.a;

-- Views to make the 'Amtlichen Linien' easier to access.

CREATE VIEW official_lines
AS
  SELECT w.river_id AS river_id,
         w.id       AS wst_id,
         wc.id      AS wst_column_id,
         wc.name    AS name,
         wc.position AS wst_column_pos
  FROM   wsts w
         JOIN wst_columns wc
           ON wc.wst_id = w.id
  WHERE  w.kind = 3;

CREATE VIEW q_main_values
AS
  SELECT riv.id AS river_id,
         g.id   AS gauge_id,
         g.name AS gauge_name,
         r.a    AS a,
         r.b    AS b,
         REGEXP_REPLACE(
            nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name,
         CAST(mv.value AS NUMERIC(38, 5)) AS value
  FROM   main_values mv
         JOIN named_main_values nmv
           ON mv.named_value_id = nmv.id
         JOIN main_value_types mvt
           ON nmv.type_id = mvt.id
         JOIN gauges g
           ON mv.gauge_id = g.id
         JOIN ranges r
           ON g.range_id = r.id
         JOIN rivers riv
           ON g.river_id = riv.id
  WHERE  mvt.name = 'Q'
  ORDER  BY g.id, CAST(mv.value AS NUMERIC(38,5));

CREATE VIEW official_q_values
AS
  SELECT ol.river_id AS river_id,
         wst_id,
         wst_column_id,
         gauge_id,
         gauge_name,
         a,
         b,
         ol.name,
         value,
         wst_column_pos
  FROM   official_lines ol
         JOIN q_main_values qmv
           ON ol.river_id = qmv.river_id
              AND ol.name = qmv.name;

http://dive4elements.wald.intevation.org