Mercurial > dive4elements > river
changeset 476:77f337650211
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
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Mon, 18 Apr 2011 15:52:53 +0000 |
| parents | 9aa0eddc5221 |
| children | 0addc2663a4d |
| files | flys-backend/ChangeLog flys-backend/doc/schema/postgresql-cleanup.sql flys-backend/doc/schema/postgresql.sql |
| diffstat | 3 files changed, 29 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- 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:
--- 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;
--- 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;