--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/flys-backend/doc/schema/oracle.sql	Fri Sep 28 12:14:36 2012 +0200
@@ -0,0 +1,472 @@
+-- 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,
+    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
+  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
+  FROM   official_lines ol
+         JOIN q_main_values qmv
+           ON ol.river_id = qmv.river_id
+              AND ol.name = qmv.name;