Mercurial > gemma
annotate schema/updates/1100/02.remove_bottleneck_validity_refs.sql @ 5736:55892008ec96 default tip
Fixed a bunch of corner cases in WG import.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Wed, 29 May 2024 19:02:42 +0200 |
parents | 30a567b1cc2e |
children |
rev | line source |
---|---|
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
3 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
6 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
7 -- Copyright (C) 2019 by via donau |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
10 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
11 -- Author(s): |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
12 -- * Sascha Wilde <sascha.wilde@intevation.de> |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
13 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
14 -- |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
15 -- CONSTRAINT FUNCTIONS |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
16 -- |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
17 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
18 -- We still want to ensure, that there is at least a valid bottleneck |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
19 -- at any time of the referencing objects validity. To ensure this we |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
20 -- need a trigger constraint: |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
21 CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
22 $$ |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
23 DECLARE |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
24 referenced_bottleneck_id text; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
25 new_tstz timestamptz; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
26 BEGIN |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
27 EXECUTE format('SELECT $1.%I', TG_ARGV[0]) |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
28 INTO referenced_bottleneck_id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
29 USING NEW; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
30 EXECUTE format('SELECT $1.%I', TG_ARGV[1]) |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
31 INTO new_tstz |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
32 USING NEW; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
33 IF EXISTS ( SELECT * FROM waterway.bottlenecks |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
34 WHERE bottleneck_id = referenced_bottleneck_id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
35 AND validity @> new_tstz ) |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
36 THEN |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
37 RETURN NEW; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
38 ELSE |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
39 RAISE EXCEPTION |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
40 'new row for relation "%" violates constraint trigger "%"', |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
41 TG_TABLE_NAME, TG_NAME |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
42 USING |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
43 DETAIL = format('No matching bottleneck %s for %s found.', |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
44 referenced_bottleneck_id, new_tstz), |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
45 ERRCODE = 23505, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
46 SCHEMA = TG_TABLE_SCHEMA, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
47 TABLE = TG_TABLE_NAME, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
48 COLUMN = TG_ARGV[0], |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
49 CONSTRAINT = TG_NAME; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
50 END IF; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
51 END; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
52 $$ |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
53 LANGUAGE plpgsql; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
54 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
55 -- Redifine constraint trigger: sounding Results must intersect with |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
56 -- the area of the bottleneck they belong to. Bottleneck is |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
57 -- determined dynamically via date_info. |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
58 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
59 LANGUAGE plpgsql |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
60 AS $$ |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
61 BEGIN |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
62 IF NOT st_intersects((SELECT area |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
63 FROM waterway.bottlenecks |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
64 WHERE bottleneck_id = NEW.bottleneck_id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
65 AND validity @> NEW.date_info::timestamptz), |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
66 NEW.area) |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
67 THEN |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
68 RAISE EXCEPTION |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
69 'new row for relation "%" violates constraint trigger "%"', |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
70 TG_TABLE_NAME, TG_NAME |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
71 USING |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
72 DETAIL = 'Failing row area has no intersection with bottleneck.', |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
73 ERRCODE = 23514, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
74 SCHEMA = TG_TABLE_SCHEMA, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
75 TABLE = TG_TABLE_NAME, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
76 CONSTRAINT = TG_NAME; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
77 END IF; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
78 RETURN NEW; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
79 END; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
80 $$; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
81 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
82 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
83 -- |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
84 -- SOUNDING RESULTS |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
85 -- |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
86 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
87 -- Dynamic version of sounding_differences geoserver view: Instead of |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
88 -- a static reference to a specific bottleneck data set via primary |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
89 -- key (id, validity) we check for a bottleneck valid at the time of |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
90 -- the survey at executiuon time. |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
91 CREATE OR REPLACE VIEW waterway.sounding_differences AS |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
92 SELECT |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
93 sd.id AS id, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
94 bn.objnam AS objnam, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
95 srm.date_info AS minuend, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
96 srs.date_info AS subtrahend, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
97 sdcl.height AS height, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
98 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
99 FROM caching.sounding_differences sd |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
100 JOIN caching.sounding_differences_contour_lines sdcl |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
101 ON sd.id = sdcl.sounding_differences_id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
102 JOIN waterway.sounding_results srm |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
103 ON sd.minuend = srm.id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
104 JOIN waterway.sounding_results srs |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
105 ON sd.subtrahend = srs.id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
106 JOIN waterway.bottlenecks bn |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
107 ON srm.bottleneck_id = bn.bottleneck_id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
108 AND srm.date_info::timestamptz <@ bn.validity; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
109 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
110 -- As we resolve the correct gauge data to use on runtime, we drop the |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
111 -- hard reference to the bottlenecks vaidity: |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
112 ALTER TABLE waterway.sounding_results DROP IF EXISTS bottleneck_validity; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
113 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
114 -- Note, we now use prefixed names, to ensure correct execution order |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
115 -- for the triggers... |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
116 DROP TRIGGER IF EXISTS a_sounding_results_reference_bottleneck |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
117 ON waterway.sounding_results; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
118 CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
119 AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
120 FOR EACH ROW |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
121 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info'); |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
122 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
123 DROP TRIGGER IF EXISTS sounding_results_in_bn_area |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
124 ON waterway.sounding_results; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
125 DROP TRIGGER IF EXISTS b_sounding_results_in_bn_area |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
126 ON waterway.sounding_results; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
127 CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
128 AFTER INSERT OR UPDATE ON waterway.sounding_results |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
129 FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area(); |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
130 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
131 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
132 -- |
4067
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
133 -- FAIRWAY AVAILABILITY |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
134 -- |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
135 |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
136 -- The current implementation associates fairway_availability data |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
137 -- hard with the internal id of an bottleneck. Lets use the |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
138 -- bottleneck_id (official unique id) instead and to the lookup of an |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
139 -- matching bottleneck for a specific date on demand. |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
140 |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
141 -- Dear reader: The gemma schema update scripts are intended to be run |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
142 -- only once, which is implemented via schema verioning and the |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
143 -- update-db.sh script. None the less it is very helpful to be able |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
144 -- to run scripts more than once during development and testing, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
145 -- without harm being done. Thats the reason for the following code |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
146 -- to be, like it is: |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
147 -- |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
148 -- This migration can only be done once, thanks to psql magic we are |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
149 -- able to guard it approprieatly. |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
150 |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
151 SELECT data_type='integer' AS old_fwa_bnid |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
152 FROM information_schema.columns |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
153 WHERE table_schema = 'waterway' |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
154 AND table_name='fairway_availability' |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
155 AND column_name='bottleneck_id'; |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
156 \gset |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
157 \if :old_fwa_bnid |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
158 \qecho 'Migrating bottleneck_id column in fairway_availability to text id.' |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
159 -- We temporarily keep the old if field for the migration of existing data: |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
160 ALTER TABLE waterway.fairway_availability |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
161 RENAME COLUMN bottleneck_id TO old_bnid; |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
162 ALTER TABLE waterway.fairway_availability |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
163 ADD COLUMN bottleneck_id varchar; |
4085
c0642c1c0ff8
Be more permissive during afd data migration.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
164 |
c0642c1c0ff8
Be more permissive during afd data migration.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
165 -- Migrate existing data: Let's be ultra permissive and make the |
c0642c1c0ff8
Be more permissive during afd data migration.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
166 -- migration _before adding the constraint triggger. |
c0642c1c0ff8
Be more permissive during afd data migration.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
167 UPDATE waterway.fairway_availability AS fwa |
c0642c1c0ff8
Be more permissive during afd data migration.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
168 SET bottleneck_id = b.bottleneck_id |
c0642c1c0ff8
Be more permissive during afd data migration.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
169 FROM waterway.bottlenecks b |
c0642c1c0ff8
Be more permissive during afd data migration.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
170 WHERE b.id = fwa.old_bnid; |
4092
30a567b1cc2e
Re-added uniq constraint after waterway.fairway_availability update.
Sascha Wilde <wilde@intevation.de>
parents:
4085
diff
changeset
|
171 ALTER TABLE waterway.fairway_availability |
30a567b1cc2e
Re-added uniq constraint after waterway.fairway_availability update.
Sascha Wilde <wilde@intevation.de>
parents:
4085
diff
changeset
|
172 ALTER COLUMN bottleneck_id SET NOT NULL; |
4085
c0642c1c0ff8
Be more permissive during afd data migration.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
173 |
4067
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
174 -- Set constraint trigger to make sure a matching BN exists: |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
175 -- |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
176 -- FIXME: From the DRC it is unclear what the exact semantics of |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
177 -- surdat and Date_Info ar unclear. Currently we assume that |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
178 -- (fk_bn_fid,surdat) has to be unique, but that might be false. |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
179 -- Anyway, I will date_info here to check for an matching |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
180 -- reference gauge at the bottleneck. The reason for this |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
181 -- decision is purely practical (and might be semantically |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
182 -- disputable: the bottleneck data in the demo system is not old |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
183 -- enough to cover rthe surdat times... |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
184 CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
185 AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.fairway_availability |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
186 FOR EACH ROW |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
187 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info'); |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
188 -- Set NOT NULL constraint for new column |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
189 ALTER TABLE waterway.fairway_availability |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
190 ALTER COLUMN bottleneck_id SET NOT NULL; |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
191 |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
192 -- The change also effects the geoserver bottlenecks view, which |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
193 -- joined in fairway_availability. We leave the rather fuzzy match |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
194 -- (not using any validity time match), as it is unclear what the |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
195 -- validity period of fwa data is. |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
196 CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
197 SELECT |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
198 b.id, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
199 b.bottleneck_id, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
200 b.objnam, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
201 b.nobjnm, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
202 b.stretch, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
203 b.area, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
204 b.rb, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
205 b.lb, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
206 b.responsible_country, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
207 b.revisiting_time, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
208 b.limiting, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
209 b.date_info, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
210 b.source_organization, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
211 g.location AS gauge_isrs_code, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
212 g.objname AS gauge_objname, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
213 g.reference_water_levels, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
214 fal.date_info AS fa_date_info, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
215 fal.critical AS fa_critical, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
216 g.gm_measuredate, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
217 g.gm_waterlevel, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
218 g.gm_n_14d, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
219 srl.date_max, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
220 g.forecast_accuracy_3d, |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
221 g.forecast_accuracy_1d |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
222 FROM waterway.bottlenecks b |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
223 LEFT JOIN waterway.gauges_base_view g |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
224 ON b.gauge_location = g.location AND g.validity @> current_timestamp |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
225 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
226 bottleneck_id, date_info, critical |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
227 FROM waterway.fairway_availability |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
228 ORDER BY bottleneck_id, date_info DESC) AS fal |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
229 ON b.bottleneck_id = fal.bottleneck_id |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
230 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
231 bottleneck_id, max(date_info) AS date_max |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
232 FROM waterway.sounding_results |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
233 GROUP BY bottleneck_id |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
234 ORDER BY bottleneck_id DESC) AS srl |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
235 ON b.bottleneck_id = srl.bottleneck_id |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
236 WHERE b.validity @> current_timestamp; |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
237 |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
238 -- Finally dropt the old column |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
239 ALTER TABLE waterway.fairway_availability |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
240 DROP COLUMN old_bnid; |
4092
30a567b1cc2e
Re-added uniq constraint after waterway.fairway_availability update.
Sascha Wilde <wilde@intevation.de>
parents:
4085
diff
changeset
|
241 ALTER TABLE waterway.fairway_availability |
30a567b1cc2e
Re-added uniq constraint after waterway.fairway_availability update.
Sascha Wilde <wilde@intevation.de>
parents:
4085
diff
changeset
|
242 ADD CONSTRAINT fairway_availability_bottleneck_id_surdat_key |
30a567b1cc2e
Re-added uniq constraint after waterway.fairway_availability update.
Sascha Wilde <wilde@intevation.de>
parents:
4085
diff
changeset
|
243 UNIQUE (bottleneck_id, surdat); |
4067
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
244 \else |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
245 \qecho 'NOTICE: bottleneck_id column in fairway_availability alread migrated.' |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
246 \endif |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
247 |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
248 |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
249 -- |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
250 -- DROP NO LONGER NEEDED TRIGGER FUNCTION |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
251 -- |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
252 |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
253 -- This used to update foreign key references. As these references no |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
254 -- longer exist we dont need this magic any more... |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
255 DROP TRIGGER IF EXISTS move_referencing ON waterway.bottlenecks; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
256 DROP FUNCTION IF EXISTS move_bottleneck_referencing(); |