Mercurial > gemma

comparison schema/updates/1100/02.remove_bottleneck_validity_refs.sql @ 4047:8c6bc85db711 historization_ng

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WIP: Remove references to bottleneck validity in DB schema.
author Sascha Wilde <wilde@intevation.de>
date Wed, 24 Jul 2019 16:15:21 +0200
parents
children 0ba3fc89b499
comparison
equal deleted inserted replaced
4045:12e3933b2050 4047:8c6bc85db711
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 -- CONSTRAINT FUNCTIONS
16 --
17
18 -- We still want to ensure, that there is at least a valid bottleneck
19 -- at any time of the referencing objects validity. To ensure this we
20 -- need a trigger constraint:
21 CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
22 $$
23 DECLARE
24 referenced_bottleneck_id text;
25 new_tstz timestamptz;
26 BEGIN
27 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
28 INTO referenced_bottleneck_id
29 USING NEW;
30 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
31 INTO new_tstz
32 USING NEW;
33 IF EXISTS ( SELECT * FROM waterway.bottlenecks
34 WHERE bottleneck_id = referenced_bottleneck_id
35 AND validity @> new_tstz )
36 THEN
37 RETURN NEW;
38 ELSE
39 RAISE EXCEPTION
40 'new row for relation "%" violates constraint trigger "%"',
41 TG_TABLE_NAME, TG_NAME
42 USING
43 DETAIL = format('No matching bottleneck %s for %s found.',
44 referenced_bottleneck_id, new_tstz),
45 ERRCODE = 23505,
46 SCHEMA = TG_TABLE_SCHEMA,
47 TABLE = TG_TABLE_NAME,
48 COLUMN = TG_ARGV[0],
49 CONSTRAINT = TG_NAME;
50 END IF;
51 END;
52 $$
53 LANGUAGE plpgsql;
54
55 -- Redifine constraint trigger: sounding Results must intersect with
56 -- the area of the bottleneck they belong to. Bottleneck is
57 -- determined dynamically via date_info.
58 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
59 LANGUAGE plpgsql
60 AS $$
61 BEGIN
62 IF NOT st_intersects((SELECT area
63 FROM waterway.bottlenecks
64 WHERE bottleneck_id = NEW.bottleneck_id
65 AND validity @> NEW.date_info::timestamptz),
66 NEW.area)
67 THEN
68 RAISE EXCEPTION
69 'new row for relation "%" violates constraint trigger "%"',
70 TG_TABLE_NAME, TG_NAME
71 USING
72 DETAIL = 'Failing row area has no intersection with bottleneck.',
73 ERRCODE = 23514,
74 SCHEMA = TG_TABLE_SCHEMA,
75 TABLE = TG_TABLE_NAME,
76 CONSTRAINT = TG_NAME;
77 END IF;
78 RETURN NEW;
79 END;
80 $$;
81
82
83 --
84 -- SOUNDING RESULTS
85 --
86
87 -- Dynamic version of sounding_differences geoserver view: Instead of
88 -- a static reference to a specific bottleneck data set via primary
89 -- key (id, validity) we check for a bottleneck valid at the time of
90 -- the survey at executiuon time.
91 CREATE OR REPLACE VIEW waterway.sounding_differences AS
92 SELECT
93 sd.id AS id,
94 bn.objnam AS objnam,
95 srm.date_info AS minuend,
96 srs.date_info AS subtrahend,
97 sdcl.height AS height,
98 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
99 FROM caching.sounding_differences sd
100 JOIN caching.sounding_differences_contour_lines sdcl
101 ON sd.id = sdcl.sounding_differences_id
102 JOIN waterway.sounding_results srm
103 ON sd.minuend = srm.id
104 JOIN waterway.sounding_results srs
105 ON sd.subtrahend = srs.id
106 JOIN waterway.bottlenecks bn
107 ON srm.bottleneck_id = bn.bottleneck_id
108 AND srm.date_info::timestamptz <@ bn.validity;
109
110 -- As we resolve the correct gauge data to use on runtime, we drop the
111 -- hard reference to the bottlenecks vaidity:
112 ALTER TABLE waterway.sounding_results DROP IF EXISTS bottleneck_validity;
113
114 -- Note, we now use prefixed names, to ensure correct execution order
115 -- for the triggers...
116 DROP TRIGGER IF EXISTS a_sounding_results_reference_bottleneck
117 ON waterway.sounding_results;
118 CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
119 AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results
120 FOR EACH ROW
121 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info');
122
123 DROP TRIGGER IF EXISTS sounding_results_in_bn_area
124 ON waterway.sounding_results;
125 DROP TRIGGER IF EXISTS b_sounding_results_in_bn_area
126 ON waterway.sounding_results;
127 CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
128 AFTER INSERT OR UPDATE ON waterway.sounding_results
129 FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area();
130
131
132 --
133 -- DROP NO LONGER NEEDED TRIGGER FUNCTION
134 --
135
136 -- This used to update foreign key references. As these references no
137 -- longer exist we dont need this magic any more...
138 DROP TRIGGER IF EXISTS move_referencing ON waterway.bottlenecks;
139 DROP FUNCTION IF EXISTS move_bottleneck_referencing();