Mercurial > gemma

comparison pkg/imports/wg.go @ 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 0500d76e074b
children
comparison
equal deleted inserted replaced
5735:8cbe0a873e32 5736:55892008ec96
92 -- Don't touch old entry if new validity contains old: will be updated 92 -- Don't touch old entry if new validity contains old: will be updated
93 AND NOT validity <@ $2 93 AND NOT validity <@ $2
94 RETURNING 1 94 RETURNING 1
95 ) 95 )
96 -- Decide whether a new version will be INSERTed 96 -- Decide whether a new version will be INSERTed
97 SELECT EXISTS(SELECT 1 FROM upd) 97 SELECT (EXISTS(SELECT 1 FROM upd)
98 AND NOT EXISTS(SELECT 1 FROM waterway.gauges
99 WHERE isrs_astext(location) = $1
100 AND validity <@ $2))
98 OR NOT EXISTS(SELECT 1 FROM waterway.gauges WHERE isrs_astext(location) = $1) 101 OR NOT EXISTS(SELECT 1 FROM waterway.gauges WHERE isrs_astext(location) = $1)
99 ` 102 `
100 103
101 insertGaugeSQL = ` 104 insertGaugeSQL = `
102 INSERT INTO waterway.gauges ( 105 INSERT INTO waterway.gauges (
133 WHERE isrs_astext(location) = $1 136 WHERE isrs_astext(location) = $1
134 AND validity && $2 137 AND validity && $2
135 AND erased 138 AND erased
136 ` 139 `
137 140
141 deleteObsoleteSQL = `
142 WITH dummy AS (
143 DELETE FROM waterway.gauges_reference_water_levels
144 WHERE isrs_astext(location) = $1
145 AND validity <@ $2 AND validity != $2
146 )
147 DELETE FROM waterway.gauges
148 WHERE isrs_astext(location) = $1
149 AND validity <@ $2 AND validity != $2
150 AND erased
151 `
152
138 updateGaugeSQL = ` 153 updateGaugeSQL = `
139 UPDATE waterway.gauges SET 154 UPDATE waterway.gauges SET
140 objname = $2, 155 objname = $2,
141 geom = ST_SetSRID(ST_MakePoint($3, $4), 4326), 156 geom = ST_SetSRID(ST_MakePoint($3, $4), 4326),
142 applicability_from_km = $5, 157 applicability_from_km = $5,
147 source_organization = $10, 162 source_organization = $10,
148 lastupdate = $11, 163 lastupdate = $11,
149 validity = $12, 164 validity = $12,
150 erased = false 165 erased = false
151 WHERE isrs_astext(location) = $1 166 WHERE isrs_astext(location) = $1
152 AND validity <@ $12 167 AND validity = (SELECT validity FROM waterway.gauges
153 AND $11 > lastupdate 168 WHERE isrs_astext(location) = $1 AND validity <@ $12
169 AND $11 > lastupdate ORDER BY validity DESC LIMIT 1)
154 RETURNING 1 170 RETURNING 1
155 ` 171 `
156 172
157 deleteReferenceWaterLevelsSQL = ` 173 deleteReferenceWaterLevelsSQL = `
158 DELETE FROM waterway.gauges_reference_water_levels 174 DELETE FROM waterway.gauges_reference_water_levels
196 return nil, err 212 return nil, err
197 } 213 }
198 defer tx.Rollback() 214 defer tx.Rollback()
199 215
200 var eraseGaugeStmt, insertStmt, 216 var eraseGaugeStmt, insertStmt,
201 fixValidityStmt, updateStmt, 217 fixValidityStmt, deleteObsoleteStmt, updateStmt,
202 deleteReferenceWaterLevelsStmt, 218 deleteReferenceWaterLevelsStmt,
203 isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt 219 isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt
204 for _, x := range []struct { 220 for _, x := range []struct {
205 sql string 221 sql string
206 stmt **sql.Stmt 222 stmt **sql.Stmt
207 }{ 223 }{
208 {eraseGaugeSQL, &eraseGaugeStmt}, 224 {eraseGaugeSQL, &eraseGaugeStmt},
209 {insertGaugeSQL, &insertStmt}, 225 {insertGaugeSQL, &insertStmt},
210 {fixValiditySQL, &fixValidityStmt}, 226 {fixValiditySQL, &fixValidityStmt},
227 {deleteObsoleteSQL, &deleteObsoleteStmt},
211 {updateGaugeSQL, &updateStmt}, 228 {updateGaugeSQL, &updateStmt},
212 {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt}, 229 {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt},
213 {isNtSDepthRefSQL, &isNtSDepthRefStmt}, 230 {isNtSDepthRefSQL, &isNtSDepthRefStmt},
214 {insertReferenceWaterLevelsSQL, &insertWaterLevelStmt}, 231 {insertReferenceWaterLevelsSQL, &insertWaterLevelStmt},
215 } { 232 } {
462 if err := rwls.Err(); err != nil { 479 if err := rwls.Err(); err != nil {
463 return err 480 return err
464 } 481 }
465 } 482 }
466 483
484 // Delete old versions if they are completely replaced by the new entrie
485 // (old validity is completely in new validity) an were not updated.
486 if _, err = tx.StmtContext(ctx, deleteObsoleteStmt).ExecContext(
487 ctx,
488 code.String(),
489 &validity,
490 ); err != nil {
491 key := pgxutils.ReadableError{Err: err}.Error()
492 databaseErrors[key] = append(databaseErrors[key], isrs)
493 unchanged++
494 return errContinue
495 }
496
467 // Set end of validity of old version to start of new version 497 // Set end of validity of old version to start of new version
468 // in case of overlap 498 // in case of overlap
469 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext( 499 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(
470 ctx, 500 ctx,
471 code.String(), 501 code.String(),