Mercurial > gemma

comparison pkg/imports/wg.go @ 3648:0ec5c8ec1e44

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid empty validity time ranges An entry which is not valid at any point in time makes no sense. Further, multiple of such entries would violate a UNIQUE constraint. Since an UPDATE now can change validity time ranges, do all the adjustments for that regardles of whether an INSERT or UPDATE happens.
author Tom Gottfried <tom@intevation.de>
date Wed, 12 Jun 2019 18:26:26 +0200
parents 02951a62e8c6
children 29ef6d41e4af
comparison
equal deleted inserted replaced
3647:123b9341408e 3648:0ec5c8ec1e44
77 WITH upd AS ( 77 WITH upd AS (
78 UPDATE waterway.gauges SET 78 UPDATE waterway.gauges SET
79 erased = true 79 erased = true
80 WHERE isrs_astext(location) = $1 80 WHERE isrs_astext(location) = $1
81 AND NOT erased 81 AND NOT erased
82 -- Don't touch old entry if validity did not change: will be updated 82 -- Don't touch old entry if new validity contains old: will be updated
83 AND validity <> $2 83 AND NOT validity <@ $2
84 RETURNING 1 84 RETURNING 1
85 ) 85 )
86 -- Decide whether a new version will be INSERTed 86 -- Decide whether a new version will be INSERTed
87 SELECT EXISTS(SELECT 1 FROM upd) 87 SELECT EXISTS(SELECT 1 FROM upd)
88 OR NOT EXISTS(SELECT 1 FROM waterway.gauges WHERE isrs_astext(location) = $1) 88 OR NOT EXISTS(SELECT 1 FROM waterway.gauges WHERE isrs_astext(location) = $1)
149 applicability_to_km = $10, 149 applicability_to_km = $10,
150 zero_point = $11, 150 zero_point = $11,
151 geodref = $12, 151 geodref = $12,
152 date_info = $13, 152 date_info = $13,
153 source_organization = $14, 153 source_organization = $14,
154 lastupdate = $15 154 lastupdate = $15,
155 validity = $16
155 WHERE location = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) 156 WHERE location = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
156 AND NOT erased 157 AND NOT erased
157 AND $15 > lastupdate 158 AND $15 > lastupdate
158 RETURNING 1 159 RETURNING 1
159 ` 160 `
373 return nil, err2 374 return nil, err2
374 } 375 }
375 unchanged++ 376 unchanged++
376 continue 377 continue
377 } 378 }
378 // Move gauge measurements and bottlenecks to new matching
379 // gauge version, if applicable
380 if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx,
381 code.String(),
382 &validity,
383 ); err != nil {
384 feedback.Warn(handleError(err).Error())
385 if err2 := tx.Rollback(); err2 != nil {
386 return nil, err2
387 }
388 unchanged++
389 continue
390 }
391 if _, err = tx.StmtContext(ctx, moveBNStmt).ExecContext(ctx,
392 code.String(),
393 &validity,
394 ); err != nil {
395 feedback.Warn(handleError(err).Error())
396 if err2 := tx.Rollback(); err2 != nil {
397 return nil, err2
398 }
399 unchanged++
400 continue
401 }
402 // Set end of validity of old version to start of new version
403 // in case of overlap
404 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(
405 ctx,
406 code.String(),
407 &validity,
408 ); err != nil {
409 feedback.Warn(handleError(err).Error())
410 if err2 := tx.Rollback(); err2 != nil {
411 return nil, err2
412 }
413 unchanged++
414 continue
415 }
416 feedback.Info("insert new version") 379 feedback.Info("insert new version")
417 case !isNew: 380 case !isNew:
418 // try to update 381 // try to update
419 var dummy int 382 var dummy int
420 err2 := tx.StmtContext(ctx, updateStmt).QueryRowContext(ctx, 383 err2 := tx.StmtContext(ctx, updateStmt).QueryRowContext(ctx,
430 dr.Zeropoint, 393 dr.Zeropoint,
431 geodref, 394 geodref,
432 &dateInfo, 395 &dateInfo,
433 source, 396 source,
434 time.Time(*dr.Lastupdate), 397 time.Time(*dr.Lastupdate),
398 &validity,
435 ).Scan(&dummy) 399 ).Scan(&dummy)
436 switch { 400 switch {
437 case err2 == sql.ErrNoRows: 401 case err2 == sql.ErrNoRows:
438 feedback.Info("unchanged") 402 feedback.Info("unchanged")
439 if err3 := tx.Rollback(); err3 != nil { 403 if err3 := tx.Rollback(); err3 != nil {
480 if err := rwls.Err(); err != nil { 444 if err := rwls.Err(); err != nil {
481 return nil, err 445 return nil, err
482 } 446 }
483 } 447 }
484 448
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
475 // in case of overlap
476 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(
477 ctx,
478 code.String(),
479 &validity,
480 ); err != nil {
481 feedback.Warn(handleError(err).Error())
482 if err2 := tx.Rollback(); err2 != nil {
483 return nil, err2
484 }
485 unchanged++
486 continue
487 }
488
485 // "Upsert" reference water levels 489 // "Upsert" reference water levels
486 for _, wl := range []struct { 490 for _, wl := range []struct {
487 level **erdms.RisreflevelcodeType 491 level **erdms.RisreflevelcodeType
488 value **erdms.RisreflevelvalueType 492 value **erdms.RisreflevelvalueType
489 }{ 493 }{