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