Mercurial > gemma

changeset 4047:8c6bc85db711 historization_ng

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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();