Mercurial > gemma

changeset 3536:1422ca0e6a51 waterlevel-in-crossprofile

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged default into waterlevel-in-crossprofile branch
author Markus Kottlaender <markus@intevation.de>
date Thu, 30 May 2019 10:37:22 +0200
parents 034657d6604f (current diff) 337e9f85f84c (diff)
children cbf883596e4e
files
diffstat 5 files changed, 28 insertions(+), 19 deletions(-) [+]
line wrap: on
line diff
--- 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,
--- 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)),
--- 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"
 				}
 			}
 		}
--- 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,
--- 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,