Mercurial > gemma

annotate schema/updates/1100/01.remove_gauge_validity_refs.sql @ 4041:3fcb95a07948 historization_ng

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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')