Mercurial > gemma
annotate schema/updates/1100/01.remove_gauge_validity_refs.sql @ 4671:0cc339c4c356
fairway profile line use correct delta
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 15 Oct 2019 15:44:07 +0200 |
parents | 12e3933b2050 |
children |
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 |
4044
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
14 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
15 -- CONSTRAINT FUNCTIONS |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
16 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
17 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
18 -- We still want to ensure, that there is at least a valid gauge at |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
19 -- any time of the referencing objects validity. To ensure this we |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
20 -- need a trigger constraint: |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
21 CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
22 $$ |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
23 DECLARE |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
24 -- FIXME: I'm using text for the isrs code and cast it on demand. |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
25 -- If someone is able to get it to work with isrs or isrs_base as |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
26 -- type, feel free to show me how its done... ;-) [sw] |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
27 referenced_gauge text; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
28 new_validity tstzrange; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
29 BEGIN |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
30 EXECUTE format('SELECT $1.%I', TG_ARGV[0]) |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
31 INTO referenced_gauge |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
32 USING NEW; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
33 EXECUTE format('SELECT $1.%I', TG_ARGV[1]) |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
34 INTO new_validity |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
35 USING NEW; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
36 IF EXISTS ( SELECT * FROM waterway.gauges |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
37 WHERE location = referenced_gauge::isrs |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
38 AND validity && new_validity ) |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
39 THEN |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
40 RETURN NEW; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
41 ELSE |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
42 RAISE EXCEPTION |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
43 'new row for relation "%" violates constraint trigger "%"', |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
44 TG_TABLE_NAME, TG_NAME |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
45 USING |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
46 DETAIL = format('No matching gauge %s found.', |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
47 isrs_AsText(referenced_gauge::isrs)), |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
48 ERRCODE = 23505, |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
49 SCHEMA = TG_TABLE_SCHEMA, |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
50 TABLE = TG_TABLE_NAME, |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
51 COLUMN = TG_ARGV[0], |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
52 CONSTRAINT = TG_NAME; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
53 END IF; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
54 END; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
55 $$ |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
56 LANGUAGE plpgsql; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
57 |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
58 -- The same for objects with a timestamp instead of a validity range. |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
59 CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
60 $$ |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
61 DECLARE |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
62 -- FIXME: I'm using text for the isrs code and cast it on demand. |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
63 -- If someone is able to get it to work with isrs or isrs_base as |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
64 -- type, feel free to show me how its done... ;-) [sw] |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
65 referenced_gauge text; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
66 new_tstz timestamptz; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
67 BEGIN |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
68 EXECUTE format('SELECT $1.%I', TG_ARGV[0]) |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
69 INTO referenced_gauge |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
70 USING NEW; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
71 EXECUTE format('SELECT $1.%I', TG_ARGV[1]) |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
72 INTO new_tstz |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
73 USING NEW; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
74 IF EXISTS ( SELECT * FROM waterway.gauges |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
75 WHERE location = referenced_gauge::isrs |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
76 AND validity @> new_tstz ) |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
77 THEN |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
78 RETURN NEW; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
79 ELSE |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
80 RAISE EXCEPTION |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
81 'new row for relation "%" violates constraint trigger "%"', |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
82 TG_TABLE_NAME, TG_NAME |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
83 USING |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
84 DETAIL = format('No matching gauge %s for %s found.', |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
85 (isrs_AsText(referenced_gauge::isrs)), new_tstz), |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
86 ERRCODE = 23505, |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
87 SCHEMA = TG_TABLE_SCHEMA, |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
88 TABLE = TG_TABLE_NAME, |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
89 COLUMN = TG_ARGV[0], |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
90 CONSTRAINT = TG_NAME; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
91 END IF; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
92 END; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
93 $$ |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
94 LANGUAGE plpgsql; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
95 |
4044
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
96 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
97 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
98 -- BOTTLENECKS |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
99 -- |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
100 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
101 -- Dynamic version of bottlenecks_geoserver view: |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
102 -- 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
|
103 -- 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
|
104 -- 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
|
105 -- time. |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
106 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
|
107 SELECT |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
108 b.id, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
109 b.bottleneck_id, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
110 b.objnam, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
111 b.nobjnm, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
112 b.stretch, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
113 b.area, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
114 b.rb, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
115 b.lb, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
116 b.responsible_country, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
117 b.revisiting_time, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
118 b.limiting, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
119 b.date_info, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
120 b.source_organization, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
121 g.location AS gauge_isrs_code, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
122 g.objname AS gauge_objname, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
123 g.reference_water_levels, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
124 fal.date_info AS fa_date_info, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
125 fal.critical AS fa_critical, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
126 g.gm_measuredate, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
127 g.gm_waterlevel, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
128 g.gm_n_14d, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
129 srl.date_max, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
130 g.forecast_accuracy_3d, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
131 g.forecast_accuracy_1d |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
132 FROM waterway.bottlenecks b |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
133 LEFT JOIN waterway.gauges_base_view g |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
134 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
|
135 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
136 bottleneck_id, date_info, critical |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
137 FROM waterway.fairway_availability |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
138 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
|
139 ON b.id = fal.bottleneck_id |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
140 LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
141 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
|
142 FROM waterway.sounding_results |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
143 GROUP BY bottleneck_id |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
144 ORDER BY bottleneck_id DESC) AS srl |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
145 ON b.bottleneck_id = srl.bottleneck_id |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
146 WHERE b.validity @> current_timestamp; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
147 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
148 -- 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
|
149 -- hard reference to the gauges vaidity: |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
150 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
|
151 |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
152 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
|
153 ON waterway.bottlenecks; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
154 CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
diff
changeset
|
155 AFTER INSERT OR UPDATE OF gauge_location ON waterway.bottlenecks |
4044
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
156 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity'); |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
157 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
158 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
159 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
160 -- GAUGE MEASUREMENTS |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
161 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
162 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
163 -- As we resolve the correct gauge data to use on runtime, we drop the |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
164 -- hard reference to the gauges vaidity: |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
165 ALTER TABLE waterway.gauge_measurements DROP IF EXISTS validity; |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
166 |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
167 DROP TRIGGER IF EXISTS waterway_gauge_measurements_reference_gauge |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
168 ON waterway.gauge_measurements; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
169 CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
170 AFTER INSERT OR UPDATE OF location ON waterway.gauge_measurements |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
171 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
172 |
4044
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
173 ALTER TABLE waterway.gauge_predictions DROP IF EXISTS validity; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
174 |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
175 DROP TRIGGER IF EXISTS waterway_gauge_predictions_reference_gauge |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
176 ON waterway.gauge_predictions; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
177 CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
178 AFTER INSERT OR UPDATE OF location ON waterway.gauge_predictions |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
179 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); |
4044
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
180 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
181 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
182 -- DROP NO LONGER NEEDED TRIGGER FUNCTION |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
183 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
184 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
185 -- This used to update foreign key references. As these references no |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
186 -- longer exist we dont need this magic any more... |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
187 DROP TRIGGER IF EXISTS move_referencing ON waterway.gauges; |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
188 DROP FUNCTION IF EXISTS move_gauge_referencing(); |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
189 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
190 |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
191 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
192 -- ADDITIONAL NOTES |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
193 -- |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
194 -- waterway.gauges_reference_water_levels still has a hard foreign key |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
195 -- reference to waterway.gauges. As this data actually is part of the |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
196 -- gauges data and the seperation in two tables is exclusively due to |
f42f7f7eb81f
WIP: Finished remove references to gauge validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4041
diff
changeset
|
197 -- schema modeling we leave it alone! |