Mercurial > gemma
diff schema/gemma.sql @ 3666:db87f34805fb
Align bottleneck validity at gauges
Ensuring the validity of a bottleneck version is always contained
by the validity of the referenced gauge version allows to reliably
determine matching reference values of the gauge at a point in time.
Since this implies that a bottleneck version might be cut into more
than one time ranges, the concept of having only one non-erased
version is no longer applicable and replaced by using the 'current'
version of a bottleneck.
Fairway availability data are always kept with the 'current'
bottleneck version to have them at hand alltogether for analyses
over longer time ranges.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Sat, 15 Jun 2019 14:36:50 +0200 |
parents | 29ef6d41e4af |
children | 433bad131e5c |
line wrap: on
line diff
--- a/schema/gemma.sql Sat Jun 15 09:24:28 2019 +0200 +++ b/schema/gemma.sql Sat Jun 15 14:36:50 2019 +0200 @@ -76,6 +76,9 @@ -- associating time-based referencing objects to matching version CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS $$ +DECLARE + new_bn int; + new_bns int[]; BEGIN -- Avoid unnecessary execution ON UPDATE if validity did not change IF OLD IS NULL OR NEW.validity <> OLD.validity THEN @@ -84,10 +87,63 @@ WHERE location = NEW.location AND measure_date <@ NEW.validity; - UPDATE waterway.bottlenecks - SET gauge_validity = NEW.validity + -- build bottleneck validities from intersections with gauge validities + FOR new_bn IN + INSERT INTO waterway.bottlenecks ( + bottleneck_id, + validity, + gauge_location, + gauge_validity, + objnam, + nobjnm, + stretch, + area, + rb, + lb, + responsible_country, + revisiting_time, + limiting, + date_info, + source_organization, + staging_done + ) SELECT + b.bottleneck_id, + -- Anticipate non-intersecting gauge validities: + b.validity * CASE WHEN g.validity = NEW.validity + THEN NEW.validity ELSE g.validity - NEW.validity END, + b.gauge_location, + g.validity, + 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, + b.staging_done + FROM waterway.bottlenecks b JOIN waterway.gauges g + ON b.gauge_location = g.location + WHERE b.gauge_location = NEW.location + AND b.validity && NEW.validity + -- Avoid duplicate intersection results: + AND NOT (b.validity <@ NEW.validity + AND g.validity <> NEW.validity) + ON CONFLICT (bottleneck_id, validity) DO UPDATE SET + -- Associate to new matching gauge version + gauge_validity = EXCLUDED.gauge_validity + RETURNING id + LOOP + new_bns = new_bns || new_bn; + END LOOP; + -- Delete bottleneck versions superseded by new intersections: + DELETE FROM waterway.bottlenecks WHERE gauge_location = NEW.location - AND lower(validity) <@ NEW.validity; + AND validity && NEW.validity + AND id <> ALL(new_bns); END IF; RETURN NULL; -- ignored END; @@ -103,6 +159,19 @@ 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); END IF; RETURN NULL; -- ignored END; @@ -534,7 +603,7 @@ DEFERRABLE INITIALLY DEFERRED, gauge_location isrs NOT NULL, gauge_validity tstzrange NOT NULL, - CHECK(lower(validity) <@ gauge_validity), + CHECK(validity <@ gauge_validity), CONSTRAINT gauge_key FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges ON UPDATE CASCADE, @@ -558,13 +627,8 @@ -- XXX: Also an attribut of sounding result? date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL, - erased boolean NOT NULL DEFAULT false, staging_done boolean NOT NULL DEFAULT false ) - -- Allow only one non-erased entry per bottleneck - CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint - ON bottlenecks (bottleneck_id) - WHERE NOT erased -- Associate referencing objects to matching bottleneck version CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity ON bottlenecks FOR EACH ROW