# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1340969090 0
# Node ID c3e04996168537785fabb535935cf2e46bc1b5ce
# Parent  2ae732e2c65c819f8b50148e29aad6dedbb7e9d6
Backend: Added views to access the 'Amtlichen Linien'.

flys-backend/trunk@4834 c6561f87-3c4e-4783-a992-168aeb5c3f6f

diff -r 2ae732e2c65c -r c3e049961685 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Fri Jun 15 09:20:49 2012 +0000
+++ b/flys-backend/ChangeLog	Fri Jun 29 11:24:50 2012 +0000
@@ -1,3 +1,8 @@
+2012-06-15	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* doc/schema/postgresql.sql, doc/schema/oracle.sql:
+	  Added views to access the 'Amtlichen Linien'.
+
 2012-06-15	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* src/main/java/de/intevation/**/*.java: Removed trailing whitespace.
diff -r 2ae732e2c65c -r c3e049961685 flys-backend/doc/schema/oracle.sql
--- a/flys-backend/doc/schema/oracle.sql	Fri Jun 15 09:20:49 2012 +0000
+++ b/flys-backend/doc/schema/oracle.sql	Fri Jun 29 11:24:50 2012 +0000
@@ -412,3 +412,56 @@
     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, 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
+  FROM   official_lines ol
+         JOIN q_main_values qmv
+           ON ol.river_id = qmv.river_id
+              AND ol.name = qmv.name;
diff -r 2ae732e2c65c -r c3e049961685 flys-backend/doc/schema/postgresql.sql
--- a/flys-backend/doc/schema/postgresql.sql	Fri Jun 15 09:20:49 2012 +0000
+++ b/flys-backend/doc/schema/postgresql.sql	Fri Jun 29 11:24:50 2012 +0000
@@ -353,4 +353,55 @@
     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
+  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
+  FROM   official_lines ol
+         JOIN q_main_values qmv
+           ON ol.river_id = qmv.river_id
+              AND ol.name = qmv.name;
+
 COMMIT;