Mercurial > gemma

comparison 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
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')