Mercurial > gemma

changeset 4045:12e3933b2050 historization_ng

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Added trigger constraints to ensure matching gauges for gauge measurements. 1. Remind me to never write "Finished" in commit messages... ;-) 2. This might be bad for performance, and might be of limited use anyway, so the triggers might be changed or removed later...
author Sascha Wilde <wilde@intevation.de>
date Wed, 24 Jul 2019 13:29:31 +0200
parents f42f7f7eb81f
children 8c6bc85db711
files schema/gemma.sql schema/updates/1100/01.remove_gauge_validity_refs.sql
diffstat 2 files changed, 94 insertions(+), 0 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Wed Jul 24 13:00:37 2019 +0200
+++ b/schema/gemma.sql	Wed Jul 24 13:29:31 2019 +0200
@@ -113,6 +113,44 @@
 $$
 LANGUAGE plpgsql;
 
+-- The same for objects with a timestamp instead of a validity range.
+CREATE OR REPLACE FUNCTION check_valid_gauge_ts() 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_tstz timestamptz;
+BEGIN
+    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
+        INTO referenced_gauge
+        USING NEW;
+    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
+        INTO new_tstz
+        USING NEW;
+    IF EXISTS ( SELECT * FROM waterway.gauges
+                  WHERE location = referenced_gauge::isrs
+                  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 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;
+
 -- REMOVE ME:
 CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS
 $$
@@ -435,6 +473,9 @@
         staging_done boolean NOT NULL DEFAULT false,
         UNIQUE (measure_date, location, staging_done)
     )
+    CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge
+        AFTER INSERT OR UPDATE OF location ON waterway.gauge_measurements
+        FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date');
     -- For fast retrieval of newest measurement per location:
     CREATE INDEX gauge_measurements_location_measure_date_desc
         ON waterway.gauge_measurements (location, measure_date DESC)
@@ -454,6 +495,9 @@
         source_organization varchar NOT NULL, -- "originator" from NtS response
         PRIMARY KEY (measure_date, location, date_issue)
     )
+    CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge
+        AFTER INSERT OR UPDATE OF location ON waterway.gauge_predictions
+        FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date');
 
     CREATE TABLE waterway_axis (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
--- a/schema/updates/1100/01.remove_gauge_validity_refs.sql	Wed Jul 24 13:00:37 2019 +0200
+++ b/schema/updates/1100/01.remove_gauge_validity_refs.sql	Wed Jul 24 13:29:31 2019 +0200
@@ -55,6 +55,44 @@
 $$
 LANGUAGE plpgsql;
 
+-- The same for objects with a timestamp instead of a validity range.
+CREATE OR REPLACE FUNCTION check_valid_gauge_ts() 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_tstz timestamptz;
+BEGIN
+    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
+        INTO referenced_gauge
+        USING NEW;
+    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
+        INTO new_tstz
+        USING NEW;
+    IF EXISTS ( SELECT * FROM waterway.gauges
+                  WHERE location = referenced_gauge::isrs
+                  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 gauge %s for %s found.',
+                    (isrs_AsText(referenced_gauge::isrs)), new_tstz),
+                ERRCODE = 23505,
+                SCHEMA = TG_TABLE_SCHEMA,
+                TABLE = TG_TABLE_NAME,
+                COLUMN = TG_ARGV[0],
+                CONSTRAINT = TG_NAME;
+    END IF;
+END;
+$$
+LANGUAGE plpgsql;
+
 
 --
 -- BOTTLENECKS
@@ -125,8 +163,20 @@
 -- As we resolve the correct gauge data to use on runtime, we drop the
 -- hard reference to the gauges vaidity:
 ALTER TABLE waterway.gauge_measurements DROP IF EXISTS validity;
+
+DROP TRIGGER IF EXISTS waterway_gauge_measurements_reference_gauge
+    ON waterway.gauge_measurements;
+CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge
+    AFTER INSERT OR UPDATE OF location ON waterway.gauge_measurements
+    FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date');
+
 ALTER TABLE waterway.gauge_predictions DROP IF EXISTS validity;
 
+DROP TRIGGER IF EXISTS waterway_gauge_predictions_reference_gauge
+    ON waterway.gauge_predictions;
+CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge
+    AFTER INSERT OR UPDATE OF location ON waterway.gauge_predictions
+    FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date');
 
 --
 -- DROP NO LONGER NEEDED TRIGGER FUNCTION