Mercurial > dive4elements > river
changeset 3335:c3e049961685
Backend: Added views to access the 'Amtlichen Linien'.
flys-backend/trunk@4834 c6561f87-3c4e-4783-a992-168aeb5c3f6f
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Fri, 29 Jun 2012 11:24:50 +0000 |
| parents | 2ae732e2c65c |
| children | 0af4c8386ed0 |
| files | flys-backend/ChangeLog flys-backend/doc/schema/oracle.sql flys-backend/doc/schema/postgresql.sql |
| diffstat | 3 files changed, 109 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- 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.
--- 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;
--- 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;