Mercurial > gemma
changeset 3533:8e083b271fca
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,