# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1303141973 0
# Node ID 77f3376502110cea1100d34f727aefb0eaa97c63
# Parent  9aa0eddc52218e6f83bd55c78e02484de6a2e273
Added a view 'wst_value_table' which aggregates the data to build w/q value tables.



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

diff -r 9aa0eddc5221 -r 77f337650211 flys-backend/ChangeLog
--- a/flys-backend/ChangeLog	Mon Apr 18 13:34:07 2011 +0000
+++ b/flys-backend/ChangeLog	Mon Apr 18 15:52:53 2011 +0000
@@ -1,3 +1,9 @@
+2011-04-18	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
+
+	* doc/schema/postgresql.sql, doc/schema/postgresql-cleanup.sql:
+	  Added a view 'wst_value_table' which aggregates the data
+	  to build w/q value tables.
+
 2011-04-18	Sascha L. Teichmann	<sascha.teichmann@intevation.de>
 
 	* src/main/java/de/intevation/flys/model/Wst.java:
diff -r 9aa0eddc5221 -r 77f337650211 flys-backend/doc/schema/postgresql-cleanup.sql
--- a/flys-backend/doc/schema/postgresql-cleanup.sql	Mon Apr 18 13:34:07 2011 +0000
+++ b/flys-backend/doc/schema/postgresql-cleanup.sql	Mon Apr 18 15:52:53 2011 +0000
@@ -1,5 +1,7 @@
 BEGIN;
 
+DROP VIEW wst_value_table;
+
 DROP TABLE wst_column_q_ranges;
 DROP TABLE wst_q_ranges;
 DROP TABLE wst_column_values;
diff -r 9aa0eddc5221 -r 77f337650211 flys-backend/doc/schema/postgresql.sql
--- a/flys-backend/doc/schema/postgresql.sql	Mon Apr 18 13:34:07 2011 +0000
+++ b/flys-backend/doc/schema/postgresql.sql	Mon Apr 18 15:52:53 2011 +0000
@@ -194,4 +194,25 @@
     UNIQUE (wst_column_id, wst_q_range_id)
 );
 
+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;
+
 COMMIT;