Mercurial > gemma
annotate schema/updates/1100/01.remove_gauge_validity_refs.sql @ 4041:3fcb95a07948 historization_ng
WIP: Started to remove references to gauge validity.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Tue, 23 Jul 2019 13:10:51 +0200 |
parents | |
children | f42f7f7eb81f |
rev | line source |
---|---|
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 -- Copyright (C) 2019 by via donau |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 -- Author(s): |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 -- * Sascha Wilde <sascha.wilde@intevation.de> |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 -- Dynamic version of bottlenecks_geoserver view: |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 -- Instead of a static reference to a specific gauge data set via |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 -- primary key (location, validity) we check for a currently |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 -- valid gauge (for the currently valid bottleneck) at executiuon |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 -- time. |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 SELECT |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 b.id, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 b.bottleneck_id, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 b.objnam, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 b.nobjnm, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 b.stretch, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 b.area, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 b.rb, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 b.lb, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 b.responsible_country, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 b.revisiting_time, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 b.limiting, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 b.date_info, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 b.source_organization, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 g.location AS gauge_isrs_code, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 g.objname AS gauge_objname, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 g.reference_water_levels, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 fal.date_info AS fa_date_info, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 fal.critical AS fa_critical, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 g.gm_measuredate, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
41 g.gm_waterlevel, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 g.gm_n_14d, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 srl.date_max, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 g.forecast_accuracy_3d, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 g.forecast_accuracy_1d |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 FROM waterway.bottlenecks b |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 LEFT JOIN waterway.gauges_base_view g |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 ON b.gauge_location = g.location AND g.validity @> current_timestamp |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 bottleneck_id, date_info, critical |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 FROM waterway.fairway_availability |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 ORDER BY bottleneck_id, date_info DESC) AS fal |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 ON b.id = fal.bottleneck_id |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 bottleneck_id, max(date_info) AS date_max |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 FROM waterway.sounding_results |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 GROUP BY bottleneck_id |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 ORDER BY bottleneck_id DESC) AS srl |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
59 ON b.bottleneck_id = srl.bottleneck_id |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
60 WHERE b.validity @> current_timestamp; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
61 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
62 -- As we resolve the correct gauge data to use on runtime, we drop the |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
63 -- hard reference to the gauges vaidity: |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
64 ALTER TABLE waterway.bottlenecks DROP IF EXISTS gauge_validity; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
65 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
66 -- We still want to ensure, that there is at least a valid gauge at |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
67 -- any time of the bottleneck validity. To ensure this we need a |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
68 -- trigger constraint: |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
69 CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
70 $$ |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
71 DECLARE |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
72 -- FIXME: I'm using text for the isrs code and cast it on demand. |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
73 -- If someone is able to get it to work with isrs or isrs_base as |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
74 -- type, feel free to show me how its done... ;-) [sw] |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
75 referenced_gauge text; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
76 new_validity tstzrange; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
77 BEGIN |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
78 EXECUTE format('SELECT $1.%I', TG_ARGV[0]) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
79 INTO referenced_gauge |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
80 USING NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
81 EXECUTE format('SELECT $1.%I', TG_ARGV[1]) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
82 INTO new_validity |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
83 USING NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
84 IF EXISTS ( SELECT * FROM waterway.gauges |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
85 WHERE location = referenced_gauge::isrs |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
86 AND validity && new_validity ) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
87 THEN |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
88 RETURN NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
89 ELSE |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
90 RAISE EXCEPTION |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
91 'new row for relation "%" violates constraint trigger "%"', |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
92 TG_TABLE_NAME, TG_NAME |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
93 USING |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
94 DETAIL = format('No matching gauge %s found.', |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
95 isrs_AsText(referenced_gauge::isrs)), |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
96 ERRCODE = 23505, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
97 SCHEMA = TG_TABLE_SCHEMA, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
98 TABLE = TG_TABLE_NAME, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
99 COLUMN = TG_ARGV[0], |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
100 CONSTRAINT = TG_NAME; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
101 END IF; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
102 END; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
103 $$ |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
104 LANGUAGE plpgsql; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
105 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
106 DROP TRIGGER IF EXISTS waterway_bottlenecks_reference_gauge |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
107 ON waterway.bottlenecks; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
108 CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
109 AFTER INSERT OR UPDATE OF gauge_location ON waterway.bottlenecks |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
110 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity') |