Mercurial > gemma
changeset 5736:55892008ec96 default tip
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(