gemma: schema/updates/1100/01.remove_gauge_validity

annotate schema/updates/1100/01.remove_gauge_validity_refs.sql @ 4045:12e3933b2050 historization_ng

Added trigger constraints to ensure matching gauges for gauge measurements. 1. Remind me to never write "Finished" in commit messages... ;-) 2. This might be bad for performance, and might be of limited use anyway, so the triggers might be changed or removed later...

author	Sascha Wilde <wilde@intevation.de>
date	Wed, 24 Jul 2019 13:29:31 +0200
parents	f42f7f7eb81f
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!

Mercurial > gemma

annotate schema/updates/1100/01.remove_gauge_validity_refs.sql @ 4045:12e3933b2050 historization_ng