# HG changeset patch
# User Markus Kottlaender <markus@intevation.de>
# Date 1559205442 -7200
# Node ID 1422ca0e6a51c201d0231a0f9b106acf016ffa10
# Parent  034657d6604fa96dd1b3e5ce6ca6c57a45cf0c7d# Parent  337e9f85f84caa2c99410e559eca7bff814d0b1f
merged default into waterlevel-in-crossprofile branch

diff -r 034657d6604f -r 1422ca0e6a51 pkg/imports/agm.go
--- a/pkg/imports/agm.go	Wed May 29 18:20:53 2019 +0200
+++ b/pkg/imports/agm.go	Thu May 30 10:37:22 2019 +0200
@@ -182,10 +182,12 @@
   staging_done
 ) VALUES (
   ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int),
-  (SELECT validity FROM waterway.gauges
-     WHERE location
-          = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
-       AND validity @> CAST($6 AS timestamp with time zone)),
+  COALESCE(
+    (SELECT validity FROM waterway.gauges
+       WHERE location
+            = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
+         AND validity @> CAST($6 AS timestamp with time zone)),
+    tstzrange(NULL, NULL)),
   $6,
   $7,
   $8,
diff -r 034657d6604f -r 1422ca0e6a51 pkg/imports/bn.go
--- a/pkg/imports/bn.go	Wed May 29 18:20:53 2019 +0200
+++ b/pkg/imports/bn.go	Thu May 30 10:37:22 2019 +0200
@@ -64,8 +64,10 @@
 ) VALUES (
   $1,
   isrs_fromText($2),
-  (SELECT validity FROM waterway.gauges
-     WHERE location = isrs_fromText($2) AND NOT erased),
+  COALESCE(
+    (SELECT validity FROM waterway.gauges
+       WHERE location = isrs_fromText($2) AND NOT erased),
+    tstzrange(NULL, NULL)),
   $3,
   $4,
   isrsrange(least(isrs_fromText($5), isrs_fromText($6)),
diff -r 034657d6604f -r 1422ca0e6a51 pkg/imports/errors.go
--- a/pkg/imports/errors.go	Wed May 29 18:20:53 2019 +0200
+++ b/pkg/imports/errors.go	Thu May 30 10:37:22 2019 +0200
@@ -57,10 +57,10 @@
 		switch err.SchemaName {
 		case "waterway":
 			switch err.TableName {
-			case "gauge_measurements":
+			case "gauge_measurements", "gauge_predictions", "bottlenecks":
 				switch err.ConstraintName {
 				case "gauge_key":
-					return "Referenced gauge is not in database"
+					return "Referenced gauge with matching temporal validity not available"
 				}
 			}
 		}
diff -r 034657d6604f -r 1422ca0e6a51 pkg/imports/gm.go
--- a/pkg/imports/gm.go	Wed May 29 18:20:53 2019 +0200
+++ b/pkg/imports/gm.go	Thu May 30 10:37:22 2019 +0200
@@ -73,10 +73,12 @@
   staging_done
 ) VALUES (
   ($1, $2, $3, $4, $5),
-  (SELECT validity FROM waterway.gauges
-     WHERE location
-          = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
-       AND validity @> CAST($6 AS timestamp with time zone)),
+  COALESCE(
+    (SELECT validity FROM waterway.gauges
+       WHERE location
+            = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
+         AND validity @> CAST($6 AS timestamp with time zone)),
+    tstzrange(NULL, NULL)),
   $6,
   $7,
   $8,
@@ -108,10 +110,12 @@
   source_organization
 ) VALUES(
   ($1, $2, $3, $4, $5),
-  (SELECT validity FROM waterway.gauges
-     WHERE location
-          = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
-       AND validity @> CAST($6 AS timestamp with time zone)),
+  COALESCE(
+    (SELECT validity FROM waterway.gauges
+       WHERE location
+            = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
+         AND validity @> CAST($6 AS timestamp with time zone)),
+    tstzrange(NULL, NULL)),
   $6,
   $7,
   $8,
diff -r 034657d6604f -r 1422ca0e6a51 schema/gemma.sql
--- a/schema/gemma.sql	Wed May 29 18:20:53 2019 +0200
+++ b/schema/gemma.sql	Thu May 30 10:37:22 2019 +0200
@@ -288,6 +288,7 @@
         lastupdate timestamp with time zone NOT NULL,
         -- entry removed from external data source (RIS-Index)/historicised:
         erased boolean NOT NULL DEFAULT false,
+        CHECK (erased OR NOT isempty(validity)),
         PRIMARY KEY (location, validity),
         EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
             DEFERRABLE INITIALLY DEFERRED
@@ -490,9 +491,9 @@
         bottleneck_id varchar UNIQUE NOT NULL,
         gauge_location isrs NOT NULL,
         gauge_validity tstzrange NOT NULL,
-        FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
-            ON UPDATE CASCADE,
-        -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
+        CONSTRAINT gauge_key
+            FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
+                ON UPDATE CASCADE,
         objnam varchar,
         nobjnm varchar,
         stretch isrsrange NOT NULL,