Mercurial > dive4elements > river

changeset 6300:0711ce5ca701

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 16182a08ccf7
children 20a32dbdbb59
files backend/doc/schema/oracle-drop.sql backend/doc/schema/oracle.sql backend/doc/schema/postgresql.sql
diffstat 3 files changed, 27 insertions(+), 111 deletions(-) [+]
line wrap: on
line diff
--- a/backend/doc/schema/oracle-drop.sql	Wed Jun 12 14:28:37 2013 +0200
+++ b/backend/doc/schema/oracle-drop.sql	Wed Jun 12 16:36:34 2013 +0200
@@ -57,6 +57,7 @@
 DROP TABLE wst_column_values;
 DROP TABLE wst_columns;
 DROP TABLE wst_q_ranges;
+DROP TABLE official_lines;
 DROP TABLE wsts;
 DROP TABLE wst_kinds;
 DROP SEQUENCE ANNOTATION_TYPES_ID_SEQ;
@@ -86,11 +87,9 @@
 DROP SEQUENCE WST_COLUMN_VALUES_ID_SEQ;
 DROP SEQUENCE WST_COLUMNS_ID_SEQ;
 DROP SEQUENCE WST_Q_RANGES_ID_SEQ;
+DROP SEQUENCE OFFICIAL_LINES_ID_SEQ;
 DROP SEQUENCE WSTS_ID_SEQ;
 DROP VIEW wst_value_table;
 DROP VIEW wst_w_values ;
 DROP VIEW wst_q_values;
-DROP VIEW official_lines;
-DROP VIEW q_main_values;
-DROP VIEW official_q_values;
 DROP VIEW wst_ranges;
--- 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,
--- a/backend/doc/schema/postgresql.sql	Wed Jun 12 14:28:37 2013 +0200
+++ b/backend/doc/schema/postgresql.sql	Wed Jun 12 16:36:34 2013 +0200
@@ -242,6 +242,16 @@
     UNIQUE (wst_column_id, wst_q_range_id)
 );
 
+CREATE SEQUENCE OFFICIAL_LINES_ID_SEQ;
+
+CREATE TABLE official_lines (
+    id                  int PRIMARY KEY NOT NULL,
+    wst_column_id       int NOT NULL REFERENCES wst_columns(id) ON DELETE CASCADE,
+    named_main_value_id int NOT NULL REFERENCES named_main_values(id) ON DELETE CASCADE,
+
+    UNIQUE (wst_column_id, named_main_value_id)
+);
+
 CREATE VIEW wst_value_table AS
     SELECT 
            wcv.position AS position,
@@ -376,59 +386,6 @@
     CHECK (a <= b)
 );
 
-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, E'[:space:]*\\(.*\\)[:space:]*', '') AS name,
-         CAST(mv.value AS NUMERIC(38, 2)) 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,2));
-
-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