Mercurial > gemma
changeset 4041:3fcb95a07948 historization_ng
WIP: Started to remove references to gauge validity.
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Tue, 23 Jul 2019 13:10:51 +0200 |
| parents | 39441cdc5021 |
| children | 9f6a6b8ad965 |
| files | schema/gemma.sql schema/geoserver_views.sql schema/updates/1100/01.remove_gauge_validity_refs.sql schema/version.sql |
| diffstat | 4 files changed, 156 insertions(+), 7 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Tue Jul 23 12:37:35 2019 +0200 +++ b/schema/gemma.sql Tue Jul 23 13:10:51 2019 +0200 @@ -72,6 +72,47 @@ $$ LANGUAGE plpgsql; +-- Trigger function to be used as a constraint trigger to enforce +-- existance of a referenced gauge with intersecting validity. The +-- columns with the referenced gauge isrs code an the validity are +-- given as arguments to the trigger function. +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; + -- Trigger functions to be used as statement-level AFTER triggers, -- associating time-based referencing objects to matching version CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS @@ -648,11 +689,6 @@ EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) DEFERRABLE INITIALLY DEFERRED, gauge_location isrs NOT NULL, - gauge_validity tstzrange NOT NULL, - CHECK(validity <@ gauge_validity), - CONSTRAINT gauge_key - FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges - ON UPDATE CASCADE, objnam varchar, nobjnm varchar, stretch isrsrange NOT NULL, @@ -675,6 +711,9 @@ source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false ) + CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge + AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity') -- Associate referencing objects to matching bottleneck version CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity ON bottlenecks FOR EACH ROW
--- a/schema/geoserver_views.sql Tue Jul 23 12:37:35 2019 +0200 +++ b/schema/geoserver_views.sql Tue Jul 23 13:10:51 2019 +0200 @@ -121,7 +121,7 @@ g.forecast_accuracy_1d FROM waterway.bottlenecks b LEFT JOIN waterway.gauges_base_view g - ON b.gauge_location = g.location AND b.gauge_validity = g.validity + 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
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1100/01.remove_gauge_validity_refs.sql Tue Jul 23 13:10:51 2019 +0200 @@ -0,0 +1,110 @@ +-- 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> + + +-- 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; + +-- We still want to ensure, that there is at least a valid gauge at +-- any time of the bottleneck 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; + +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')