Mercurial > gemma

comparison pkg/imports/wg.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 0ec5c8ec1e44
children 5fed2f5bc104 6c5c15b2fb64
comparison
equal deleted inserted replaced
3664:58508f50d192 3665:29ef6d41e4af
114 $15, 114 $15,
115 $16 115 $16
116 ) 116 )
117 ` 117 `
118 118
119 moveGMSQL = `
120 UPDATE waterway.gauge_measurements
121 -- Associate measurements to matching gauge version
122 SET validity = $2
123 WHERE isrs_astext(location) = $1
124 AND measure_date <@ CAST($2 AS tstzrange)
125 `
126
127 moveBNSQL = `
128 UPDATE waterway.bottlenecks
129 -- Associate bottlenecks to matching gauge version
130 SET gauge_validity = $2
131 WHERE isrs_astext(gauge_location) = $1
132 AND lower(validity) <@ CAST($2 AS tstzrange)
133 `
134
135 fixValiditySQL = ` 119 fixValiditySQL = `
136 UPDATE waterway.gauges SET 120 UPDATE waterway.gauges SET
137 -- Set enddate of old entry to new startdate in case of overlap: 121 -- Set enddate of old entry to new startdate in case of overlap:
138 validity = validity - $2 122 validity = validity - $2
139 WHERE isrs_astext(location) = $1 123 WHERE isrs_astext(location) = $1
206 "wtwgag") 190 "wtwgag")
207 if err != nil { 191 if err != nil {
208 return nil, err 192 return nil, err
209 } 193 }
210 194
211 var eraseGaugeStmt, insertStmt, moveGMStmt, moveBNStmt, 195 var eraseGaugeStmt, insertStmt,
212 fixValidityStmt, updateStmt, 196 fixValidityStmt, updateStmt,
213 deleteReferenceWaterLevelsStmt, 197 deleteReferenceWaterLevelsStmt,
214 isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt 198 isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt
215 for _, x := range []struct { 199 for _, x := range []struct {
216 sql string 200 sql string
217 stmt **sql.Stmt 201 stmt **sql.Stmt
218 }{ 202 }{
219 {eraseGaugeSQL, &eraseGaugeStmt}, 203 {eraseGaugeSQL, &eraseGaugeStmt},
220 {insertGaugeSQL, &insertStmt}, 204 {insertGaugeSQL, &insertStmt},
221 {moveGMSQL, &moveGMStmt},
222 {moveBNSQL, &moveBNStmt},
223 {fixValiditySQL, &fixValidityStmt}, 205 {fixValiditySQL, &fixValidityStmt},
224 {updateGaugeSQL, &updateStmt}, 206 {updateGaugeSQL, &updateStmt},
225 {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt}, 207 {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt},
226 {isNtSDepthRefSQL, &isNtSDepthRefStmt}, 208 {isNtSDepthRefSQL, &isNtSDepthRefStmt},
227 {insertReferenceWaterLevelsSQL, &insertWaterLevelStmt}, 209 {insertReferenceWaterLevelsSQL, &insertWaterLevelStmt},
444 if err := rwls.Err(); err != nil { 426 if err := rwls.Err(); err != nil {
445 return nil, err 427 return nil, err
446 } 428 }
447 } 429 }
448 430
449 // Move gauge measurements and bottlenecks to new matching
450 // gauge version, if applicable
451 if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx,
452 code.String(),
453 &validity,
454 ); err != nil {
455 feedback.Warn(handleError(err).Error())
456 if err2 := tx.Rollback(); err2 != nil {
457 return nil, err2
458 }
459 unchanged++
460 continue
461 }
462 if _, err = tx.StmtContext(ctx, moveBNStmt).ExecContext(ctx,
463 code.String(),
464 &validity,
465 ); err != nil {
466 feedback.Warn(handleError(err).Error())
467 if err2 := tx.Rollback(); err2 != nil {
468 return nil, err2
469 }
470 unchanged++
471 continue
472 }
473
474 // Set end of validity of old version to start of new version 431 // Set end of validity of old version to start of new version
475 // in case of overlap 432 // in case of overlap
476 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext( 433 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(
477 ctx, 434 ctx,
478 code.String(), 435 code.String(),