Mercurial > gemma

changeset 3533:8e083b271fca

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Improve error messages if no matching gauge version found Avoid hitting the NOT NULL constraint of the referencing validity column in order to hit the foreign key constraint instead and emit an appropriate error message in all such cases.
author Tom Gottfried <tom@intevation.de>
date Wed, 29 May 2019 18:14:20 +0200
parents e98ea184538e
children 337e9f85f84c
files pkg/imports/agm.go pkg/imports/bn.go pkg/imports/errors.go pkg/imports/gm.go schema/gemma.sql
diffstat 5 files changed, 27 insertions(+), 19 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/agm.go	Wed May 29 16:56:56 2019 +0200
+++ b/pkg/imports/agm.go	Wed May 29 18:14:20 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,
--- a/pkg/imports/bn.go	Wed May 29 16:56:56 2019 +0200
+++ b/pkg/imports/bn.go	Wed May 29 18:14:20 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)),
--- a/pkg/imports/errors.go	Wed May 29 16:56:56 2019 +0200
+++ b/pkg/imports/errors.go	Wed May 29 18:14:20 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"
 				}
 			}
 		}
--- a/pkg/imports/gm.go	Wed May 29 16:56:56 2019 +0200
+++ b/pkg/imports/gm.go	Wed May 29 18:14:20 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,
--- a/schema/gemma.sql	Wed May 29 16:56:56 2019 +0200
+++ b/schema/gemma.sql	Wed May 29 18:14:20 2019 +0200
@@ -490,9 +490,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,