--- 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;