Mercurial > gemma

comparison pkg/imports/bn.go @ 3665:29ef6d41e4af

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use database triggers to move referencing objects to new versions Needs fewer database round-trips and is more convenient especially if more than two levels in the object hierarchy have to be handled.
author Tom Gottfried <tom@intevation.de>
date Sat, 15 Jun 2019 09:24:28 +0200
parents 2a079d0a71c1
children db87f34805fb
comparison
equal deleted inserted replaced
3664:58508f50d192 3665:29ef6d41e4af
105 $13, 105 $13,
106 $14 106 $14
107 ) 107 )
108 RETURNING id` 108 RETURNING id`
109 109
110 moveSRSQL = `
111 UPDATE waterway.sounding_results
112 -- Associate measurements to matching bottleneck version
113 SET bottleneck_validity = $2
114 WHERE bottleneck_id = $1
115 AND CAST(date_info AS timestamptz) <@ CAST($2 AS tstzrange)
116 `
117
118 fixBNValiditySQL = ` 110 fixBNValiditySQL = `
119 UPDATE waterway.bottlenecks SET 111 UPDATE waterway.bottlenecks SET
120 -- Set enddate of old entry to new startdate in case of overlap: 112 -- Set enddate of old entry to new startdate in case of overlap:
121 validity = validity - $2 113 validity = validity - $2
122 WHERE bottleneck_id = $1 114 WHERE bottleneck_id = $1
271 return nil, err 263 return nil, err
272 } 264 }
273 265
274 feedback.Info("Found %d bottlenecks for import", len(bns)) 266 feedback.Info("Found %d bottlenecks for import", len(bns))
275 267
276 var hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, 268 var hasStmt, insertStmt, fixValidityStmt, updateStmt,
277 deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt 269 deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt
278 270
279 for _, x := range []struct { 271 for _, x := range []struct {
280 sql string 272 sql string
281 stmt **sql.Stmt 273 stmt **sql.Stmt
282 }{ 274 }{
283 {hasBottleneckSQL, &hasStmt}, 275 {hasBottleneckSQL, &hasStmt},
284 {insertBottleneckSQL, &insertStmt}, 276 {insertBottleneckSQL, &insertStmt},
285 {moveSRSQL, &moveSRStmt},
286 {fixBNValiditySQL, &fixValidityStmt}, 277 {fixBNValiditySQL, &fixValidityStmt},
287 {updateBottleneckSQL, &updateStmt}, 278 {updateBottleneckSQL, &updateStmt},
288 {deleteBottleneckMaterialSQL, &deleteMaterialStmt}, 279 {deleteBottleneckMaterialSQL, &deleteMaterialStmt},
289 {insertBottleneckMaterialSQL, &insertMaterialStmt}, 280 {insertBottleneckMaterialSQL, &insertMaterialStmt},
290 {trackImportSQL, &trackStmt}, 281 {trackImportSQL, &trackStmt},
301 feedback.Info("Tolerance used to snap waterway axis: %g", tolerance) 292 feedback.Info("Tolerance used to snap waterway axis: %g", tolerance)
302 293
303 for _, bn := range bns { 294 for _, bn := range bns {
304 if err := storeBottleneck( 295 if err := storeBottleneck(
305 ctx, importID, conn, feedback, bn, &nids, tolerance, 296 ctx, importID, conn, feedback, bn, &nids, tolerance,
306 hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, 297 hasStmt, insertStmt, fixValidityStmt, updateStmt,
307 deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil { 298 deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil {
308 return nil, err 299 return nil, err
309 } 300 }
310 } 301 }
311 if len(nids) == 0 { 302 if len(nids) == 0 {
328 conn *sql.Conn, 319 conn *sql.Conn,
329 feedback Feedback, 320 feedback Feedback,
330 bn *ifbn.BottleNeckType, 321 bn *ifbn.BottleNeckType,
331 nids *[]string, 322 nids *[]string,
332 tolerance float64, 323 tolerance float64,
333 hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt, 324 hasStmt, insertStmt, fixValidityStmt, updateStmt,
334 deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt, 325 deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt,
335 ) error { 326 ) error {
336 feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id) 327 feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id)
337 328
338 if bn.AdditionalData == nil || bn.AdditionalData.KeyValuePair == nil { 329 if bn.AdditionalData == nil || bn.AdditionalData.KeyValuePair == nil {
510 return err 501 return err
511 } 502 }
512 } 503 }
513 } 504 }
514 505
515 // Move sounding results to new matching bottleneck version, if applicable
516 if _, err = tx.StmtContext(ctx, moveSRStmt).ExecContext(ctx,
517 bn.Bottleneck_id,
518 &validity,
519 ); err != nil {
520 feedback.Warn(handleError(err).Error())
521 if err2 := tx.Rollback(); err2 != nil {
522 return err2
523 }
524 return nil
525 }
526
527 // Set end of validity of old version to start of new version 506 // Set end of validity of old version to start of new version
528 // in case of overlap 507 // in case of overlap
529 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(ctx, 508 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(ctx,
530 bn.Bottleneck_id, 509 bn.Bottleneck_id,
531 validity, 510 validity,