view backend/doc/schema/oracle.sql @ 7300:83bb52fa0c32

(issue1529) Be more tolerant in the fitting. The invalid value warning is removed because invalid data is expected there when datapoints are not valid for this KM
author Andre Heinecke <aheinecke@intevation.de>
date Fri, 11 Oct 2013 18:40:33 +0200
parents a8b065038c0c
children 3b93f439e954
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) NOT NULL UNIQUE,
    PRIMARY KEY     (id)
);


-- ANNOTATIONS
CREATE SEQUENCE ANNOTATIONS_ID_SEQ;

CREATE TABLE annotations (
    id              NUMBER(38,0) NOT NULL,
    attribute_id    NUMBER(38,0) NOT NULL,
    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) NOT NULL UNIQUE, 
    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) NOT NULL,
    cross_section_id    NUMBER(38,0) NOT NULL, 
    PRIMARY KEY         (id),
    UNIQUE (km, cross_section_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) NOT NULL,
    x                       NUMBER(38,2) NOT NULL,
    y                       NUMBER(38,2) NOT NULL,
    cross_section_line_id   NUMBER(38,0) NOT NULL,
    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) NOT NULL,
    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,4) NOT NULL,
    w                   NUMBER(38,2) NOT NULL,
    table_id            NUMBER(38,0) NOT NULL,
    UNIQUE (table_id, q, w),
    PRIMARY KEY         (id)
);


-- DISCHARGE_TABLES
CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;

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


-- 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) NOT NULL,
    datum               NUMBER(38,2) NOT NULL, 
    name                VARCHAR2(255) NOT NULL,
    station             NUMBER(38,2) NOT NULL,
    official_number     NUMBER(38,0),
    range_id            NUMBER(38,0) NOT NULL,
    -- TODO: remove river id here because range_id references river already
    river_id            NUMBER(38,0) NOT NULL,
    PRIMARY KEY         (id),
    UNIQUE (name, river_id),
    UNIQUE (official_number, river_id),
    UNIQUE (river_id, station)
);


-- 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) NOT NULL UNIQUE,
    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) NOT NULL,
    gauge_id            NUMBER(38,0) NOT NULL,
    named_value_id      NUMBER(38,0) NOT NULL,
    time_interval_id    NUMBER(38,0),

    -- TODO: better checks
    UNIQUE (gauge_id, named_value_id, time_interval_id),
    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(256) NOT NULL,
    type_id             NUMBER(38,0) NOT NULL,
    PRIMARY KEY (id)
);


-- POSITIONS
CREATE SEQUENCE POSITIONS_ID_SEQ;

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


--- RANGES
CREATE SEQUENCE RANGES_ID_SEQ;

CREATE TABLE ranges (
    id                  NUMBER(38,0) NOT NULL,
    a                   NUMBER(38,10) NOT NULL,
    b                   NUMBER(38,10),
    river_id            NUMBER(38,0),
    UNIQUE (river_id, a, b),
    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) NOT NULL DEFAULT 0,
    name                VARCHAR2(255) NOT NULL UNIQUE,
    wst_unit_id         NUMBER(38,0) NOT NULL,
    PRIMARY KEY         (id)
);


-- TIME_INTERVALS
CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;

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


--- UNITS
CREATE SEQUENCE UNITS_ID_SEQ;

CREATE TABLE units (
    id                  NUMBER(38,0) NOT NULL,
    name                VARCHAR2(255) NOT NULL UNIQUE,
    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) NOT NULL,
    wst_q_range_id      NUMBER(38,0) NOT NULL,
    UNIQUE (wst_column_id, wst_q_range_id),
    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) NOT NULL,
    w                   NUMBER(38,5) NOT NULL,
    wst_column_id       NUMBER(38,0) NOT NULL,
    UNIQUE (position, wst_column_id),
    UNIQUE (position, wst_column_id, w),
    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) NOT NULL,
    position            NUMBER(38,0) NOT NULL DEFAULT 0,
    time_interval_id    NUMBER(38,0),
    wst_id              NUMBER(38,0) NOT NULL,
    UNIQUE (wst_id, name),
    UNIQUE (wst_id, position),
    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) NOT NULL,
    range_id            NUMBER(38,0) NOT NULL,
    PRIMARY KEY         (id)
);

-- OFFICIAL_LINES
CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ;

CREATE TABLE official_lines (
    id                  NUMBER(38,0) NOT NULL,
    wst_column_id       NUMBER(38,0) NOT NULL,
    named_main_value_id NUMBER(38,0) NOT NULL,
    UNIQUE (wst_column_id, named_main_value_id),
    PRIMARY KEY (id)
);

-- WSTS
--lookup table for wst kinds
CREATE TABLE wst_kinds (
    id 	     NUMBER PRIMARY KEY NOT NULL,
    kind     VARCHAR(64) NOT NULL
);
INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');


CREATE SEQUENCE WSTS_ID_SEQ;

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


-- ADD CONSTRAINTs
ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
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 cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;

-- Cascading references
ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE;
ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE;
ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE;
ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE;
ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE;
ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE;
ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON LETE CASCADE;
ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;

ALTER TABLE official_lines ADD CONSTRAINT cOffLinesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
ALTER TABLE official_lines ADD CONSTRAINT cOffLinesNamedMainValues FOREIGN KEY (named_main_value_id) REFERENCES named_main_values ON DELETE CASCADE;

-- VIEWS

CREATE VIEW wst_value_table AS
    SELECT 
           wcv.position AS position,
           w, 
           q,
           wc.position AS column_pos,
           w.id AS wst_id
        FROM wsts w
        JOIN wst_columns wc
             ON wc.wst_id=w.id
        JOIN wst_column_q_ranges wcqr
             ON wcqr.wst_column_id=wc.id
        JOIN wst_q_ranges wqr
             ON wcqr.wst_q_range_id=wqr.id
        JOIN ranges r
             ON wqr.range_id=r.id
        JOIN wst_column_values wcv
             ON wcv.wst_column_id=wc.id AND wcv.position between r.a and r.b
    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;

CREATE VIEW wst_ranges
AS
  SELECT wc.id             AS wst_column_id,
         wc.wst_id         AS wst_id,
         Min(wcv.position) AS a,
         Max(wcv.position) AS b
  FROM   wst_columns wc
         JOIN wst_column_values wcv
           ON wc.id = wcv.wst_column_id
  GROUP  BY wc.id,
            wc.wst_id;

http://dive4elements.wald.intevation.org