diff backend/doc/schema/oracle.sql @ 6300:0711ce5ca701

Backend: Modified schema for a better model of official lines.
author Sascha L. Teichmann <teichmann@intevation.de>
date Wed, 12 Jun 2013 16:36:34 +0200
parents 573112007ec7
children a8b065038c0c
line wrap: on
line diff
--- a/backend/doc/schema/oracle.sql	Wed Jun 12 14:28:37 2013 +0200
+++ b/backend/doc/schema/oracle.sql	Wed Jun 12 16:36:34 2013 +0200
@@ -344,6 +344,17 @@
     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,
+
+    PRIMARY KEY (id),
+    UNIQUE (wst_column_id, named_main_value_id)
+);
 
 -- WSTS
 --lookup table for wst kinds
@@ -406,10 +417,13 @@
 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 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
@@ -457,61 +471,6 @@
     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;
-
 CREATE VIEW wst_ranges
 AS
   SELECT wc.id             AS wst_column_id,

http://dive4elements.wald.intevation.org