Mercurial > gemma
changeset 4047:8c6bc85db711 historization_ng
WIP: Remove references to bottleneck validity in DB schema.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 24 Jul 2019 16:15:21 +0200 |
parents | 12e3933b2050 |
children | 9072584b205f |
files | schema/gemma.sql schema/geoserver_views.sql schema/updates/1100/02.remove_bottleneck_validity_refs.sql |
diffstat | 3 files changed, 182 insertions(+), 40 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Jul 24 13:29:31 2019 +0200 +++ b/schema/gemma.sql Wed Jul 24 16:15:21 2019 +0200 @@ -151,45 +151,55 @@ $$ LANGUAGE plpgsql; --- REMOVE ME: -CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS +-- Trigger function to be used as a constraint trigger to enforce +-- existance of a referenced bottleneck with validity at a given time. +-- The columns with the referenced bottleneck id and the timestamp are +-- given as arguments to the trigger function. +CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS $$ +DECLARE + referenced_bottleneck_id text; + new_tstz timestamptz; BEGIN - -- Avoid unnecessary execution ON UPDATE if validity did not change - IF OLD IS NULL OR NEW.validity <> OLD.validity THEN - UPDATE waterway.sounding_results - SET bottleneck_validity = NEW.validity - WHERE bottleneck_id = NEW.bottleneck_id - AND CAST(date_info AS timestamptz) <@ NEW.validity; - - -- Always associate fairway availability data to newest bottleneck - -- version to prevent problems in analysis over longer time periods - WITH - bn AS (SELECT id, validity FROM waterway.bottlenecks - WHERE bottleneck_id = NEW.bottleneck_id), - latest AS (SELECT id FROM bn - -- Candidates are past new validity or just inserted/updated - WHERE NOT validity &< NEW.validity OR id = NEW.id - ORDER BY upper(validity) DESC FETCH FIRST ROW ONLY) - UPDATE waterway.fairway_availability - SET bottleneck_id = (SELECT id FROM latest) - WHERE bottleneck_id IN(SELECT id FROM bn EXCEPT SELECT id FROM latest); + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_bottleneck_id + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_tstz + USING NEW; + IF EXISTS ( SELECT * FROM waterway.bottlenecks + WHERE bottleneck_id = referenced_bottleneck_id + 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 bottleneck %s for %s found.', + referenced_bottleneck_id, new_tstz), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; END IF; - RETURN NULL; -- ignored END; $$ LANGUAGE plpgsql; -- Constraint trigger: sounding Results must intersect with the area --- of the bottleneck they belong to. +-- of the bottleneck they belong to. The "xx" at the beginning of the +-- name is to ensure, it is fired last after other triggers. CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NOT st_intersects((SELECT area FROM waterway.bottlenecks - WHERE (bottleneck_id, validity) - =(NEW.bottleneck_id, NEW.bottleneck_validity)), + WHERE bottleneck_id = NEW.bottleneck_id + AND validity @> NEW.date_info::timestamptz), NEW.area) THEN RAISE EXCEPTION @@ -668,10 +678,6 @@ 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 - EXECUTE FUNCTION move_bottleneck_referencing() CREATE TABLE bottlenecks_riverbed_materials ( bottleneck_id int NOT NULL REFERENCES bottlenecks(id) @@ -684,14 +690,7 @@ CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar NOT NULL, - bottleneck_validity tstzrange NOT NULL, - CONSTRAINT bottleneck_key - FOREIGN KEY (bottleneck_id, bottleneck_validity) - REFERENCES bottlenecks (bottleneck_id, validity) - ON UPDATE CASCADE, date_info date NOT NULL, - CHECK (tstzrange(date_info::timestamptz, - date_info::timestamptz + '1 d'::interval) && bottleneck_validity), UNIQUE (bottleneck_id, date_info), area geography(POLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), @@ -702,9 +701,13 @@ octree_index bytea, staging_done boolean NOT NULL DEFAULT false ) - CREATE CONSTRAINT TRIGGER sounding_results_in_bn_area - AFTER INSERT OR UPDATE ON sounding_results - FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area() + CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck + AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results + FOR EACH ROW + EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info'); + CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area + AFTER INSERT OR UPDATE ON waterway.sounding_results + FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area(); CREATE TABLE sounding_results_contour_lines ( sounding_result_id int NOT NULL REFERENCES sounding_results
--- a/schema/geoserver_views.sql Wed Jul 24 13:29:31 2019 +0200 +++ b/schema/geoserver_views.sql Wed Jul 24 16:15:21 2019 +0200 @@ -218,4 +218,4 @@ ON sd.subtrahend = srs.id JOIN waterway.bottlenecks bn ON srm.bottleneck_id = bn.bottleneck_id - AND srm.bottleneck_validity = bn.validity; + AND srm.date_info::timestamptz <@ bn.validity;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1100/02.remove_bottleneck_validity_refs.sql Wed Jul 24 16:15:21 2019 +0200 @@ -0,0 +1,139 @@ +-- 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 bottleneck +-- at any time of the referencing objects validity. To ensure this we +-- need a trigger constraint: +CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS +$$ +DECLARE + referenced_bottleneck_id text; + new_tstz timestamptz; +BEGIN + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_bottleneck_id + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_tstz + USING NEW; + IF EXISTS ( SELECT * FROM waterway.bottlenecks + WHERE bottleneck_id = referenced_bottleneck_id + 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 bottleneck %s for %s found.', + referenced_bottleneck_id, new_tstz), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; + +-- Redifine constraint trigger: sounding Results must intersect with +-- the area of the bottleneck they belong to. Bottleneck is +-- determined dynamically via date_info. +CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger +LANGUAGE plpgsql +AS $$ +BEGIN + IF NOT st_intersects((SELECT area + FROM waterway.bottlenecks + WHERE bottleneck_id = NEW.bottleneck_id + AND validity @> NEW.date_info::timestamptz), + NEW.area) + THEN + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = 'Failing row area has no intersection with bottleneck.', + ERRCODE = 23514, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + CONSTRAINT = TG_NAME; + END IF; + RETURN NEW; +END; +$$; + + +-- +-- SOUNDING RESULTS +-- + +-- Dynamic version of sounding_differences geoserver view: Instead of +-- a static reference to a specific bottleneck data set via primary +-- key (id, validity) we check for a bottleneck valid at the time of +-- the survey at executiuon time. +CREATE OR REPLACE VIEW waterway.sounding_differences AS + SELECT + sd.id AS id, + bn.objnam AS objnam, + srm.date_info AS minuend, + srs.date_info AS subtrahend, + sdcl.height AS height, + CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines + FROM caching.sounding_differences sd + JOIN caching.sounding_differences_contour_lines sdcl + ON sd.id = sdcl.sounding_differences_id + JOIN waterway.sounding_results srm + ON sd.minuend = srm.id + JOIN waterway.sounding_results srs + ON sd.subtrahend = srs.id + JOIN waterway.bottlenecks bn + ON srm.bottleneck_id = bn.bottleneck_id + AND srm.date_info::timestamptz <@ bn.validity; + +-- As we resolve the correct gauge data to use on runtime, we drop the +-- hard reference to the bottlenecks vaidity: +ALTER TABLE waterway.sounding_results DROP IF EXISTS bottleneck_validity; + +-- Note, we now use prefixed names, to ensure correct execution order +-- for the triggers... +DROP TRIGGER IF EXISTS a_sounding_results_reference_bottleneck + ON waterway.sounding_results; +CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck + AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results + FOR EACH ROW + EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info'); + +DROP TRIGGER IF EXISTS sounding_results_in_bn_area + ON waterway.sounding_results; +DROP TRIGGER IF EXISTS b_sounding_results_in_bn_area + ON waterway.sounding_results; +CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area + AFTER INSERT OR UPDATE ON waterway.sounding_results + FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area(); + + +-- +-- 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.bottlenecks; +DROP FUNCTION IF EXISTS move_bottleneck_referencing();