Mercurial > gemma
view schema/updates/1100/01.remove_gauge_validity_refs.sql @ 5650:cb3c4ba0f052 DQR adjustments
Merged default branch
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 29 Jun 2023 13:18:54 +0200 |
parents | 12e3933b2050 |
children |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Sascha Wilde <sascha.wilde@intevation.de> -- -- CONSTRAINT FUNCTIONS -- -- We still want to ensure, that there is at least a valid gauge at -- any time of the referencing objects validity. To ensure this we -- need a trigger constraint: CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS $$ DECLARE -- FIXME: I'm using text for the isrs code and cast it on demand. -- If someone is able to get it to work with isrs or isrs_base as -- type, feel free to show me how its done... ;-) [sw] referenced_gauge text; new_validity tstzrange; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_gauge USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_validity USING NEW; IF EXISTS ( SELECT * FROM waterway.gauges WHERE location = referenced_gauge::isrs AND validity && new_validity ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching gauge %s found.', isrs_AsText(referenced_gauge::isrs)), ERRCODE = 23505, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; -- The same for objects with a timestamp instead of a validity range. CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS $$ DECLARE -- FIXME: I'm using text for the isrs code and cast it on demand. -- If someone is able to get it to work with isrs or isrs_base as -- type, feel free to show me how its done... ;-) [sw] referenced_gauge text; new_tstz timestamptz; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_gauge USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_tstz USING NEW; IF EXISTS ( SELECT * FROM waterway.gauges WHERE location = referenced_gauge::isrs AND validity @> new_tstz ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching gauge %s for %s found.', (isrs_AsText(referenced_gauge::isrs)), new_tstz), ERRCODE = 23505, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; -- -- BOTTLENECKS -- -- Dynamic version of bottlenecks_geoserver view: -- Instead of a static reference to a specific gauge data set via -- primary key (location, validity) we check for a currently -- valid gauge (for the currently valid bottleneck) at executiuon -- time. CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS SELECT b.id, b.bottleneck_id, b.objnam, b.nobjnm, b.stretch, b.area, b.rb, b.lb, b.responsible_country, b.revisiting_time, b.limiting, b.date_info, b.source_organization, g.location AS gauge_isrs_code, g.objname AS gauge_objname, g.reference_water_levels, fal.date_info AS fa_date_info, fal.critical AS fa_critical, g.gm_measuredate, g.gm_waterlevel, g.gm_n_14d, srl.date_max, g.forecast_accuracy_3d, g.forecast_accuracy_1d FROM waterway.bottlenecks b LEFT JOIN waterway.gauges_base_view g ON b.gauge_location = g.location AND g.validity @> current_timestamp LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical FROM waterway.fairway_availability ORDER BY bottleneck_id, date_info DESC) AS fal ON b.id = fal.bottleneck_id LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max FROM waterway.sounding_results GROUP BY bottleneck_id ORDER BY bottleneck_id DESC) AS srl ON b.bottleneck_id = srl.bottleneck_id WHERE b.validity @> current_timestamp; -- As we resolve the correct gauge data to use on runtime, we drop the -- hard reference to the gauges vaidity: ALTER TABLE waterway.bottlenecks DROP IF EXISTS gauge_validity; DROP TRIGGER IF EXISTS waterway_bottlenecks_reference_gauge ON waterway.bottlenecks; CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge AFTER INSERT OR UPDATE OF gauge_location ON waterway.bottlenecks FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity'); -- -- GAUGE MEASUREMENTS -- -- As we resolve the correct gauge data to use on runtime, we drop the -- hard reference to the gauges vaidity: ALTER TABLE waterway.gauge_measurements DROP IF EXISTS validity; DROP TRIGGER IF EXISTS waterway_gauge_measurements_reference_gauge ON waterway.gauge_measurements; CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge AFTER INSERT OR UPDATE OF location ON waterway.gauge_measurements FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); ALTER TABLE waterway.gauge_predictions DROP IF EXISTS validity; DROP TRIGGER IF EXISTS waterway_gauge_predictions_reference_gauge ON waterway.gauge_predictions; CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge AFTER INSERT OR UPDATE OF location ON waterway.gauge_predictions FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); -- -- DROP NO LONGER NEEDED TRIGGER FUNCTION -- -- This used to update foreign key references. As these references no -- longer exist we dont need this magic any more... DROP TRIGGER IF EXISTS move_referencing ON waterway.gauges; DROP FUNCTION IF EXISTS move_gauge_referencing(); -- -- ADDITIONAL NOTES -- -- waterway.gauges_reference_water_levels still has a hard foreign key -- reference to waterway.gauges. As this data actually is part of the -- gauges data and the seperation in two tables is exclusively due to -- schema modeling we leave it alone!