Mercurial > gemma

changeset 5736:55892008ec96 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed a bunch of corner cases in WG import.
author Sascha Wilde <wilde@sha-bang.de>
date Wed, 29 May 2024 19:02:42 +0200
parents 8cbe0a873e32
children
files pkg/imports/wg.go
diffstat 1 files changed, 34 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/wg.go	Wed May 29 16:55:22 2024 +0200
+++ b/pkg/imports/wg.go	Wed May 29 19:02:42 2024 +0200
@@ -94,7 +94,10 @@
   RETURNING 1
 )
 -- Decide whether a new version will be INSERTed
-SELECT EXISTS(SELECT 1 FROM upd)
+SELECT (EXISTS(SELECT 1 FROM upd)
+        AND NOT EXISTS(SELECT 1 FROM waterway.gauges
+                         WHERE isrs_astext(location) = $1
+                           AND validity <@ $2))
   OR NOT EXISTS(SELECT 1 FROM waterway.gauges WHERE isrs_astext(location) = $1)
 `
 
@@ -135,6 +138,18 @@
   AND erased
 `
 
+	deleteObsoleteSQL = `
+WITH dummy AS (
+  DELETE FROM waterway.gauges_reference_water_levels
+    WHERE isrs_astext(location) = $1
+      AND validity <@ $2 AND validity != $2
+)
+DELETE FROM waterway.gauges
+WHERE isrs_astext(location) = $1
+  AND validity <@ $2 AND validity != $2
+  AND erased
+`
+
 	updateGaugeSQL = `
 UPDATE waterway.gauges SET
   objname = $2,
@@ -149,8 +164,9 @@
   validity = $12,
   erased = false
 WHERE isrs_astext(location) = $1
-  AND validity <@ $12
-  AND $11 > lastupdate
+  AND validity = (SELECT validity FROM waterway.gauges
+                   WHERE isrs_astext(location) = $1 AND validity <@ $12
+		     AND $11 > lastupdate ORDER BY validity DESC LIMIT 1)
 RETURNING 1
 `
 
@@ -198,7 +214,7 @@
 	defer tx.Rollback()
 
 	var eraseGaugeStmt, insertStmt,
-		fixValidityStmt, updateStmt,
+		fixValidityStmt, deleteObsoleteStmt, updateStmt,
 		deleteReferenceWaterLevelsStmt,
 		isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt
 	for _, x := range []struct {
@@ -208,6 +224,7 @@
 		{eraseGaugeSQL, &eraseGaugeStmt},
 		{insertGaugeSQL, &insertStmt},
 		{fixValiditySQL, &fixValidityStmt},
+		{deleteObsoleteSQL, &deleteObsoleteStmt},
 		{updateGaugeSQL, &updateStmt},
 		{deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt},
 		{isNtSDepthRefSQL, &isNtSDepthRefStmt},
@@ -464,6 +481,19 @@
 					}
 				}
 
+				// Delete old versions if they are completely replaced by the new entrie
+				// (old validity is completely in new validity) an were not updated.
+				if _, err = tx.StmtContext(ctx, deleteObsoleteStmt).ExecContext(
+					ctx,
+					code.String(),
+					&validity,
+				); err != nil {
+					key := pgxutils.ReadableError{Err: err}.Error()
+					databaseErrors[key] = append(databaseErrors[key], isrs)
+					unchanged++
+					return errContinue
+				}
+
 				// Set end of validity of old version to start of new version
 				// in case of overlap
 				if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(