--- /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();