Mercurial > gemma
diff schema/updates/1100/01.remove_gauge_validity_refs.sql @ 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 | |
children | f42f7f7eb81f |
line wrap: on
line diff
--- /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')