
bjoern@2351: -- ANNOTATION_TYPES
bjoern@2351: CREATE SEQUENCE ANNOTATION_TYPES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE annotation_types (
bjoern@2351:     id              NUMBER(38,0) NOT NULL, 
tom@5783:     name            VARCHAR2(255) NOT NULL UNIQUE,
bjoern@2351:     PRIMARY KEY     (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- ANNOTATIONS
bjoern@2351: CREATE SEQUENCE ANNOTATIONS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE annotations (
tom@5783:     id              NUMBER(38,0) NOT NULL,
tom@5783:     attribute_id    NUMBER(38,0) NOT NULL,
bjoern@2351:     edge_id         NUMBER(38,0),
bjoern@2351:     position_id     NUMBER(38,0),
bjoern@2351:     range_id        NUMBER(38,0),
bjoern@2351:     type_id         NUMBER(38,0),
bjoern@2351:     PRIMARY KEY     (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- ATTRIBUTES 
bjoern@2351: CREATE SEQUENCE ATTRIBUTES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE attributes (
bjoern@2351:     id              NUMBER(38,0) NOT NULL, 
tom@5783:     value           VARCHAR2(255) NOT NULL UNIQUE, 
bjoern@2351:     primary key     (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- CROSS_SECTION_LINES
bjoern@2351: CREATE SEQUENCE CROSS_SECTION_LINES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE cross_section_lines (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     km                  NUMBER(38,2),
bjoern@2351:     cross_section_id    NUMBER(38,0), 
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- CROSS_SECTION_POINTS
bjoern@2351: CREATE SEQUENCE CROSS_SECTION_POINTS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE cross_section_points (
bjoern@2351:     id                      NUMBER(38,0) NOT NULL,
bjoern@2351:     col_pos                 NUMBER(38,0),
bjoern@2351:     x                       NUMBER(38,2),
bjoern@2351:     y                       NUMBER(38,2),
bjoern@2353:     cross_section_line_id   NUMBER(38,0),
bjoern@2351:     PRIMARY KEY             (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- CROSS_SECTIONS
ingo@2855: CREATE SEQUENCE CROSS_SECTIONS_ID_SEQ;
bjoern@2351: 
ingo@2855: CREATE TABLE cross_sections (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     description         VARCHAR2(255),
bjoern@2351:     river_id            NUMBER(38,0),
bjoern@2351:     time_interval_id    NUMBER(38,0),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
sascha@3339: -- Indices for faster access of the points
sascha@3339: CREATE INDEX cross_section_lines_km_idx
sascha@3339:     ON cross_section_lines(km);
sascha@3339: CREATE INDEX cross_section_points_line_idx
sascha@3339:     ON cross_section_points(cross_section_line_id);
bjoern@2351: 
bjoern@2351: -- DISCHARGE_TABLE_VALUES
bjoern@2351: CREATE SEQUENCE DISCHARGE_TABLE_VALUES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE discharge_table_values (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5897:     q                   NUMBER(38,4) NOT NULL,
tom@5783:     w                   NUMBER(38,2) NOT NULL,
tom@5783:     table_id            NUMBER(38,0) NOT NULL,
tom@5783:     UNIQUE (table_id, q, w),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- DISCHARGE_TABLES
bjoern@2351: CREATE SEQUENCE DISCHARGE_TABLES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE discharge_tables (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     description         VARCHAR2(255) NOT NULL,
teichmann@4776:     bfg_id              VARCHAR2(50),
tom@5783:     kind                NUMBER(38,0) NOT NULL DEFAULT 0,
tom@5783:     gauge_id            NUMBER(38,0) NOT NULL,
bjoern@2351:     time_interval_id    NUMBER(38,0),
tom@5883:     PRIMARY KEY         (id),
tom@5883:     UNIQUE(gauge_id, time_interval_id, kind)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- EDGES
bjoern@2351: CREATE SEQUENCE EDGES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE edges (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     bottom              NUMBER(38,2),
bjoern@2351:     top                 NUMBER(38,2),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- GAUGES
bjoern@2351: CREATE SEQUENCE GAUGES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE gauges (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5207:     aeo                 NUMBER(38,2) NOT NULL,
tom@5207:     datum               NUMBER(38,2) NOT NULL, 
tom@5207:     name                VARCHAR2(255) NOT NULL,
tom@5207:     station             NUMBER(38,2) NOT NULL,
tom@5894:     official_number     NUMBER(38,0),
tom@5207:     range_id            NUMBER(38,0) NOT NULL,
tom@5894:     -- TODO: remove river id here because range_id references river already
tom@5783:     river_id            NUMBER(38,0) NOT NULL,
tom@5207:     PRIMARY KEY         (id),
tom@5207:     UNIQUE (name, river_id),
tom@5894:     UNIQUE (official_number, river_id),
tom@5207:     UNIQUE (river_id, station)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- HYK_ENTRIES
bjoern@2351: CREATE SEQUENCE HYK_ENTRIES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE hyk_entries (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     km                  NUMBER(38,2),
bjoern@2351:     measure             TIMESTAMP,
bjoern@2351:     hyk_id              NUMBER(38,0),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- HYK_FLOW_ZONE_TYPES
bjoern@2351: CREATE SEQUENCE HYK_FLOW_ZONE_TYPES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE hyk_flow_zone_types (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     description         VARCHAR2(255),
bjoern@2351:     name                VARCHAR2(255),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- HYK_FLOW_ZONES
bjoern@2351: CREATE SEQUENCE HYK_FLOW_ZONES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE hyk_flow_zones (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     a                   NUMBER(38,2),
bjoern@2351:     b                   NUMBER(38,2),
bjoern@2351:     formation_id        NUMBER(38,0),
bjoern@2351:     type_id             NUMBER(38,0),
bjoern@2351:     primary key         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- HYK_FORMATIONS
bjoern@2351: CREATE SEQUENCE HYK_FORMATIONS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE hyk_formations (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     bottom              NUMBER(38,2),
bjoern@2351:     distance_hf         NUMBER(38,2),
bjoern@2351:     distance_vl         NUMBER(38,2),
bjoern@2351:     distance_vr         NUMBER(38,2),
bjoern@2351:     formation_num       NUMBER(38,0),
bjoern@2351:     top                 NUMBER(38,2),
bjoern@2351:     hyk_entry_id        NUMBER(38,0),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- HYKS
bjoern@2351: CREATE SEQUENCE HYKS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE hyks (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     description         VARCHAR2(255),
bjoern@2351:     river_id            NUMBER(38,0),
bjoern@2351:     primary key         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- MAIN_VALUE_TYPES
bjoern@2351: CREATE SEQUENCE MAIN_VALUE_TYPES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE main_value_types (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     name                VARCHAR2(255) NOT NULL UNIQUE,
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- MAIN_VALUES
bjoern@2351: CREATE SEQUENCE MAIN_VALUES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE main_values (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     value               NUMBER(38,2) NOT NULL,
tom@5783:     gauge_id            NUMBER(38,0) NOT NULL,
tom@5783:     named_value_id      NUMBER(38,0) NOT NULL,
bjoern@2351:     time_interval_id    NUMBER(38,0),
tom@5783: 
tom@5783:     -- TODO: better checks
tom@5783:     UNIQUE (gauge_id, named_value_id, time_interval_id),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- NAMED_MAIN_VALUES
bjoern@2351: CREATE SEQUENCE NAMED_MAIN_VALUES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE named_main_values (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5684:     name                VARCHAR2(256) NOT NULL UNIQUE,
tom@5783:     type_id             NUMBER(38,0) NOT NULL,
bjoern@2351:     PRIMARY KEY (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- POSITIONS
bjoern@2351: CREATE SEQUENCE POSITIONS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE positions (
bjoern@2353:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     value               VARCHAR2(255 char) NOT NULL UNIQUE,
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: --- RANGES
bjoern@2351: CREATE SEQUENCE RANGES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE ranges (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5158:     a                   NUMBER(38,10) NOT NULL,
sascha@2367:     b                   NUMBER(38,10),
bjoern@2351:     river_id            NUMBER(38,0),
tom@5783:     UNIQUE (river_id, a, b),
tom@5783:     PRIMARY KEY (id)
bjoern@2351: );
tom@5321: 
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- RIVERS
bjoern@2351: CREATE SEQUENCE RIVERS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE rivers (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5893:     official_number     NUMBER(38,0),
tom@5783:     km_up               NUMBER(38,0) NOT NULL DEFAULT 0,
tom@5783:     name                VARCHAR2(255) NOT NULL UNIQUE,
tom@5783:     wst_unit_id         NUMBER(38,0) NOT NULL,
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- TIME_INTERVALS
bjoern@2351: CREATE SEQUENCE TIME_INTERVALS_ID_SEQ;
bjoern@2351: 
aheinecke@5206: CREATE TABLE time_intervals (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL, 
tom@5158:     start_time          TIMESTAMP NOT NULL,
bjoern@2351:     stop_time           TIMESTAMP,
tom@5158:     PRIMARY KEY         (id),
tom@5158:     CHECK (start_time <= stop_time)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: --- UNITS
bjoern@2351: CREATE SEQUENCE UNITS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE units (
bjoern@2353:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     name                VARCHAR2(255) NOT NULL UNIQUE,
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- WST_COLUMN_Q_RANGES
bjoern@2351: CREATE SEQUENCE WST_COLUMN_Q_RANGES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE wst_column_q_ranges (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     wst_column_id       NUMBER(38,0) NOT NULL,
tom@5783:     wst_q_range_id      NUMBER(38,0) NOT NULL,
tom@5783:     UNIQUE (wst_column_id, wst_q_range_id),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- WST_COLUMN_VALUES
bjoern@2351: CREATE SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE wst_column_values (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     position            NUMBER(38,5) NOT NULL,
tom@5783:     w                   NUMBER(38,5) NOT NULL,
tom@5783:     wst_column_id       NUMBER(38,0) NOT NULL,
tom@5783:     UNIQUE (position, wst_column_id),
tom@5783:     UNIQUE (position, wst_column_id, w),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- WST_COLUMNS
bjoern@2351: CREATE SEQUENCE WST_COLUMNS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE wst_columns (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
bjoern@2351:     description         VARCHAR2(255),
tom@5783:     name                VARCHAR2(255) NOT NULL,
tom@5783:     position            NUMBER(38,0) NOT NULL DEFAULT 0,
bjoern@2351:     time_interval_id    NUMBER(38,0),
tom@5783:     wst_id              NUMBER(38,0) NOT NULL,
tom@5783:     UNIQUE (wst_id, name),
tom@5783:     UNIQUE (wst_id, position),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- WST_Q_RANGES
bjoern@2351: CREATE SEQUENCE WST_Q_RANGES_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE wst_q_ranges (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     q                   NUMBER(38,5) NOT NULL,
tom@5783:     range_id            NUMBER(38,0) NOT NULL,
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2351: -- WSTS
tom@5224: --lookup table for wst kinds
tom@5224: CREATE TABLE wst_kinds (
tom@5224:     id 	     NUMBER PRIMARY KEY NOT NULL,
tom@5224:     kind     VARCHAR(64) NOT NULL
tom@5224: );
tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (0, 'basedata');
tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (1, 'basedata_additionals_marks');
tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (2, 'basedata_fixations_wst');
tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (3, 'basedata_officials');
tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (4, 'basedata_heightmarks-points-relative_points');
tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (5, 'basedata_flood-protections_relative_points');
tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (6, 'morpho_waterlevel-differences');
tom@5224: INSERT INTO wst_kinds (id, kind) VALUES (7, 'morpho_waterlevels');
tom@5224: 
tom@5224: 
bjoern@2351: CREATE SEQUENCE WSTS_ID_SEQ;
bjoern@2351: 
bjoern@2351: CREATE TABLE wsts (
bjoern@2351:     id                  NUMBER(38,0) NOT NULL,
tom@5783:     description         VARCHAR2(255) NOT NULL,
tom@5783:     kind                NUMBER(38,0) NOT NULL,
tom@5783:     river_id            NUMBER(38,0) NOT NULL,
tom@5783:     UNIQUE (river_id, description),
bjoern@2351:     PRIMARY KEY         (id)
bjoern@2351: );
bjoern@2351: 
bjoern@2351: 
bjoern@2352: -- ADD CONSTRAINTs
aheinecke@5113: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsAttributes FOREIGN KEY (attribute_id) REFERENCES attributes;
bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsEdges FOREIGN KEY (edge_id) REFERENCES edges;
bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsPositions FOREIGN KEY (position_id) REFERENCES positions;
bjoern@2352: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsTypes FOREIGN KEY (type_id) REFERENCES annotation_types;
bjoern@2352: ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
bjoern@2352: ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
bjoern@2352: ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFlowZoneTypes FOREIGN KEY (type_id) REFERENCES hyk_flow_zone_types;
aheinecke@5113: ALTER TABLE main_values ADD CONSTRAINT cMainValuesNamedMainValues FOREIGN KEY (named_value_id) REFERENCES named_main_values;
bjoern@2352: ALTER TABLE main_values ADD CONSTRAINT cMainValuesTimeIntervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
bjoern@2352: ALTER TABLE named_main_values ADD CONSTRAINT cNamedMainValuesMainValueTypes FOREIGN KEY (type_id) REFERENCES main_value_types;
bjoern@2352: ALTER TABLE rivers ADD CONSTRAINT cRiversUnits FOREIGN KEY (wst_unit_id) REFERENCES units;
bjoern@2352: ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsTime_intervals FOREIGN KEY (time_interval_id) REFERENCES time_intervals;
aheinecke@5113: 
aheinecke@5113: -- Cascading references
aheinecke@5113: ALTER TABLE annotations ADD CONSTRAINT cAnnotationsRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE cross_section_lines ADD CONSTRAINT cQPSLinesCrossSections FOREIGN KEY (cross_section_id) REFERENCES cross_sections ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE cross_section_points ADD CONSTRAINT cQPSPointsCrossSectionLines FOREIGN KEY (cross_section_line_id) REFERENCES cross_section_lines ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE cross_sections ADD CONSTRAINT cCrossSectionsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE discharge_tables ADD CONSTRAINT cDischargeTablesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE discharge_table_values ADD CONSTRAINT cTableValuesDischargeTables FOREIGN KEY (table_id) REFERENCES discharge_tables ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE gauges ADD CONSTRAINT cGaugesRanges FOREIGN KEY (range_id) REFERENCES ranges ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE gauges ADD CONSTRAINT cGaugesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE hyk_entries ADD CONSTRAINT cHykEntriesHyks FOREIGN KEY (hyk_id) REFERENCES hyks ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE hyk_flow_zones ADD CONSTRAINT cHykFlowZonesHykFormations FOREIGN KEY (formation_id) REFERENCES hyk_formations ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE hyk_formations ADD CONSTRAINT cHykFormationsHykEntries FOREIGN KEY (hyk_entry_id) REFERENCES hyk_entries ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE hyks ADD CONSTRAINT cHyksRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE main_values ADD CONSTRAINT cMainValuesGauges FOREIGN KEY (gauge_id) REFERENCES gauges ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE ranges ADD CONSTRAINT cRangesRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstColums FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE wst_column_q_ranges ADD CONSTRAINT cWstColumnQRangesWstQRanges FOREIGN KEY (wst_q_range_id) REFERENCES wst_q_ranges ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE wst_columns ADD CONSTRAINT cWstColumnsWsts FOREIGN KEY (wst_id) REFERENCES wsts ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE wst_column_values ADD CONSTRAINT cWstColumnValuesWstColumns FOREIGN KEY (wst_column_id) REFERENCES wst_columns ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE wst_q_ranges ADD CONSTRAINT cWstQRangesRanges FOREIGN KEY (range_id) REFERENCES RANGES ON DELETE CASCADE;
aheinecke@5113: ALTER TABLE wsts ADD CONSTRAINT cWstsRivers FOREIGN KEY (river_id) REFERENCES rivers ON DELETE CASCADE;
tom@5224: ALTER TABLE wsts ADD CONSTRAINT cWstsWstKinds FOREIGN KEY (kind) REFERENCES wst_kinds;
bjoern@2355: 
bjoern@2355: -- VIEWS
bjoern@2355: 
bjoern@2355: CREATE VIEW wst_value_table AS
bjoern@2355:     SELECT wcv.position AS position,
bjoern@2355:            w,
bjoern@2355:            (SELECT q
bjoern@2355:             FROM   wst_column_q_ranges wcqr
bjoern@2355:                    JOIN wst_q_ranges wqr
bjoern@2355:                      ON wcqr.wst_q_range_id = wqr.id
bjoern@2355:                    JOIN ranges r
bjoern@2355:                      ON r.id = wqr.range_id
bjoern@2355:             WHERE  wcqr.wst_column_id = wc.id
bjoern@2355:                    AND wcv.position BETWEEN r.a AND r.b) AS q,
bjoern@2355:            wc.position                                   AS column_pos,
bjoern@2355:            w.id                                          AS wst_id
bjoern@2355:     FROM   wst_column_values wcv
bjoern@2355:            JOIN wst_columns wc
bjoern@2355:              ON wcv.wst_column_id = wc.id
bjoern@2355:            JOIN wsts w
bjoern@2355:              ON wc.wst_id = w.id
bjoern@2355:     ORDER  BY wcv.position ASC,
bjoern@2355:           wc.position DESC;
bjoern@2355: 
bjoern@2355: -- view to select the w values of a WST
bjoern@2355: CREATE VIEW wst_w_values  AS
bjoern@2355:     SELECT wcv.position   AS km, 
bjoern@2355:            wcv.w          AS w,  
bjoern@2355:            wc.position    AS column_pos, 
bjoern@2355:            w.id           AS wst_id
bjoern@2355:         FROM wst_column_values wcv
bjoern@2355:         JOIN wst_columns wc ON wcv.wst_column_id = wc.id
bjoern@2355:         JOIN wsts w         ON wc.wst_id = w.id
bjoern@2355:     ORDER BY wcv.position, wc.position;
bjoern@2355: 
bjoern@2355: -- view to select the q values of a WST
bjoern@2355: CREATE VIEW wst_q_values AS
bjoern@2355:     SELECT wc.position AS column_pos,
bjoern@2355:            wqr.q       AS q, 
bjoern@2355:            r.a         AS a, 
bjoern@2355:            r.b         AS b,
bjoern@2355:            wc.wst_id   AS wst_id
bjoern@2355:     FROM wst_column_q_ranges wcqr
bjoern@2355:     JOIN wst_q_ranges wqr ON wcqr.wst_q_range_id = wqr.id
bjoern@2355:     JOIN ranges r         ON wqr.range_id        = r.id
bjoern@2355:     JOIN wst_columns wc   ON wcqr.wst_column_id  = wc.id
bjoern@2355:     ORDER BY wc.position, wcqr.wst_column_id, r.a;
sascha@3335: 
sascha@3335: -- Views to make the 'Amtlichen Linien' easier to access.
sascha@3335: 
sascha@3335: CREATE VIEW official_lines
sascha@3335: AS
sascha@3335:   SELECT w.river_id AS river_id,
sascha@3335:          w.id       AS wst_id,
sascha@3335:          wc.id      AS wst_column_id,
felix@3470:          wc.name    AS name,
felix@3470:          wc.position AS wst_column_pos
sascha@3335:   FROM   wsts w
sascha@3335:          JOIN wst_columns wc
sascha@3335:            ON wc.wst_id = w.id
sascha@3335:   WHERE  w.kind = 3;
sascha@3335: 
sascha@3335: CREATE VIEW q_main_values
sascha@3335: AS
sascha@3335:   SELECT riv.id AS river_id,
sascha@3335:          g.id   AS gauge_id,
sascha@3335:          g.name AS gauge_name,
sascha@3335:          r.a    AS a,
sascha@3335:          r.b    AS b,
sascha@3335:          REGEXP_REPLACE(
sascha@3335:             nmv.name, '[:space:]*\(.*\)[:space:]*', '') AS name,
sascha@3338:          CAST(mv.value AS NUMERIC(38, 5)) AS value
sascha@3335:   FROM   main_values mv
sascha@3335:          JOIN named_main_values nmv
sascha@3335:            ON mv.named_value_id = nmv.id
sascha@3335:          JOIN main_value_types mvt
sascha@3335:            ON nmv.type_id = mvt.id
sascha@3335:          JOIN gauges g
sascha@3335:            ON mv.gauge_id = g.id
sascha@3335:          JOIN ranges r
sascha@3335:            ON g.range_id = r.id
sascha@3335:          JOIN rivers riv
sascha@3335:            ON g.river_id = riv.id
sascha@3335:   WHERE  mvt.name = 'Q'
sascha@3338:   ORDER  BY g.id, CAST(mv.value AS NUMERIC(38,5));
sascha@3335: 
sascha@3335: CREATE VIEW official_q_values
sascha@3335: AS
sascha@3335:   SELECT ol.river_id AS river_id,
sascha@3335:          wst_id,
sascha@3335:          wst_column_id,
sascha@3335:          gauge_id,
sascha@3335:          gauge_name,
sascha@3335:          a,
sascha@3335:          b,
sascha@3335:          ol.name,
felix@3470:          value,
felix@3470:          wst_column_pos
sascha@3335:   FROM   official_lines ol
sascha@3335:          JOIN q_main_values qmv
sascha@3335:            ON ol.river_id = qmv.river_id
sascha@3335:               AND ol.name = qmv.name;
teichmann@4651: 
teichmann@4651: CREATE VIEW wst_ranges
teichmann@4651: AS
teichmann@4651:   SELECT wc.id             AS wst_column_id,
teichmann@4651:          wc.wst_id         AS wst_id,
teichmann@4651:          Min(wcv.position) AS a,
teichmann@4651:          Max(wcv.position) AS b
teichmann@4651:   FROM   wst_columns wc
teichmann@4651:          JOIN wst_column_values wcv
teichmann@4651:            ON wc.id = wcv.wst_column_id
teichmann@4651:   GROUP  BY wc.id,
teichmann@4651:             wc.wst_id;




