gemma: pkg/imports/wg.go annotate

annotate pkg/imports/wg.go @ 3678:8f58851927c0

client: make layer factory only return new layer config for individual maps instead of each time it is invoked. The purpose of the factory was to support multiple maps with individual layers. But returning a new config each time it is invoked leads to bugs that rely on the layer's state. Now this factory reuses the same objects it created before, per map.

author	Markus Kottlaender <markus@intevation.de>
date	Mon, 17 Jun 2019 17:31:35 +0200
parents	29ef6d41e4af
children	5fed2f5bc104 6c5c15b2fb64

rev	line source
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 // This is Free Software under GNU Affero General Public License v >= 3.0
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2 // without warranty, see README.md and license for details.
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	3 //
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	4 // SPDX-License-Identifier: AGPL-3.0-or-later
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	5 // License-Filename: LICENSES/AGPL-3.0.txt
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6 //
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	7 // Copyright (C) 2018, 2019 by via donau
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	8 // – Österreichische Wasserstraßen-Gesellschaft mbH
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	9 // Software engineering by Intevation GmbH
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	10 //
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	11 // Author(s):
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	12 // * Sascha L. Teichmann <sascha.teichmann@intevation.de>
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	13 // * Tom Gottfried <tom.gottfried@intevation.de>
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	14
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	15 package imports
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	16
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	17 import (
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	18 "context"
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19 "database/sql"
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	20 "time"
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	21
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	22 "github.com/jackc/pgx/pgtype"
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	23
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	24 "gemma.intevation.de/gemma/pkg/models"
1810 7ee9bdaac336 Waterway gauge import: Added support for username/password. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1808 diff changeset	25 "gemma.intevation.de/gemma/pkg/soap/erdms"
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	26 )
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	27
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	28 type WaterwayGauge struct {
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	29 // URL is the URL of the SOAP service.
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	30 URL string `json:"url"`
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	31 // Username is the username used to authenticate.
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	32 Username string `json:"username"`
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	33 // Passwort is the password to authenticate.
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	34 Password string `json:"password"`
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	35 // Insecure indicates if HTTPS traffic
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	36 // should validate certificates or not.
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	37 Insecure bool `json:"insecure"`
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	38 }
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	39
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	40 const WGJobKind JobKind = "wg"
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	41
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	42 type wgJobCreator struct{}
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	43
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	44 func init() {
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	45 RegisterJobCreator(WGJobKind, wgJobCreator{})
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	46 }
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	47
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	48 func (wgJobCreator) Description() string { return "waterway gauges" }
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	49
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	50 func (wgJobCreator) AutoAccept() bool { return true }
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	51
2187 7c83b5277c1c Import queue: Removed boilerplate code to deserialize jobs from JSON by making it part of the import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2168 diff changeset	52 func (wgJobCreator) Create() Job { return new(WaterwayGauge) }
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	53
3219 4acbee65275d Import queue: Split locked dependencies in exclusively and multiple uses. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3163 diff changeset	54 func (wgJobCreator) Depends() [2][]string {
4acbee65275d Import queue: Split locked dependencies in exclusively and multiple uses. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3163 diff changeset	55 return [2][]string{
4acbee65275d Import queue: Split locked dependencies in exclusively and multiple uses. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3163 diff changeset	56 {"gauges_reference_water_levels", "gauges"},
4acbee65275d Import queue: Split locked dependencies in exclusively and multiple uses. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3163 diff changeset	57 {"depth_references"},
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	58 }
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	59 }
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	60
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	61 // StageDone does nothing as there is no staging for gauges.
b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	62 func (wgJobCreator) StageDone(context.Context, *sql.Tx, int64) error { return nil }
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	63
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	64 // CleanUp does nothing as there is nothing to cleanup with gauges.
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	65 func (*WaterwayGauge) CleanUp() error { return nil }
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	66
1812 4910bcfab319 Waterway gauge import: Work around the expected (but nonetheless silly) namespace issues with the SOAP interface. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1810 diff changeset	67 const (
3311 0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	68 eraseObsoleteGaugesSQL = `
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	69 UPDATE waterway.gauges SET erased = true
3551 a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	70 WHERE NOT erased
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	71 AND (location).country_code = ANY($1)
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	72 AND isrs_astext(location) <> ALL($2)
3311 0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	73 RETURNING isrs_astext(location)
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	74 `
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	75
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	76 eraseGaugeSQL = `
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	77 WITH upd AS (
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	78 UPDATE waterway.gauges SET
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	79 erased = true
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	80 WHERE isrs_astext(location) = $1
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	81 AND NOT erased
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	82 -- Don't touch old entry if new validity contains old: will be updated
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	83 AND NOT validity <@ $2
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	84 RETURNING 1
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	85 )
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	86 -- Decide whether a new version will be INSERTed
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	87 SELECT EXISTS(SELECT 1 FROM upd)
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	88 OR NOT EXISTS(SELECT 1 FROM waterway.gauges WHERE isrs_astext(location) = $1)
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	89 `
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	90
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	91 insertGaugeSQL = `
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	92 INSERT INTO waterway.gauges (
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	93 location,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	94 objname,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	95 geom,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	96 applicability_from_km,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	97 applicability_to_km,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	98 validity,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	99 zero_point,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	100 geodref,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	101 date_info,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	102 source_organization,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	103 lastupdate
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	104 ) VALUES (
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	105 ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int),
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	106 $6,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	107 ST_SetSRID(ST_MakePoint($7, $8), 4326)::geography,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	108 $9,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	109 $10,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	110 $11,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	111 $12,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	112 $13,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	113 $14,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	114 $15,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	115 $16
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	116 )
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	117 `
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	118
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	119 fixValiditySQL = `
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	120 UPDATE waterway.gauges SET
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	121 -- Set enddate of old entry to new startdate in case of overlap:
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	122 validity = validity - $2
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	123 WHERE isrs_astext(location) = $1
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	124 AND validity && $2
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	125 AND erased
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	126 `
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	127
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	128 updateGaugeSQL = `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	129 UPDATE waterway.gauges SET
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	130 objname = $6,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	131 geom = ST_SetSRID(ST_MakePoint($7, $8), 4326),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	132 applicability_from_km = $9,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	133 applicability_to_km = $10,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	134 zero_point = $11,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	135 geodref = $12,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	136 date_info = $13,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	137 source_organization = $14,
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	138 lastupdate = $15,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	139 validity = $16
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	140 WHERE location = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	141 AND NOT erased
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	142 AND $15 > lastupdate
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	143 RETURNING 1
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	144 `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	145
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	146 deleteReferenceWaterLevelsSQL = `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	147 DELETE FROM waterway.gauges_reference_water_levels
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	148 WHERE isrs_astext(location) = $1
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	149 AND validity = $2
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	150 AND depth_reference <> ALL($3)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	151 RETURNING depth_reference
1836 4dcdd8891770 Waterway gauges import: Fixed insert/update of gauges. TODO: Re-insert reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1835 diff changeset	152 `
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	153
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	154 isNtSDepthRefSQL = `
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	155 SELECT EXISTS(SELECT 1 FROM depth_references WHERE depth_reference = $1)`
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	156
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	157 insertReferenceWaterLevelsSQL = `
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	158 INSERT INTO waterway.gauges_reference_water_levels (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	159 location,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	160 validity,
2317 8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2187 diff changeset	161 depth_reference,
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	162 value
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	163 ) VALUES (
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	164 ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int),
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	165 $6,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	166 $7,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	167 $8
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	168 ) ON CONFLICT (location, validity, depth_reference) DO UPDATE SET
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	169 value = EXCLUDED.value
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	170 `
1812 4910bcfab319 Waterway gauge import: Work around the expected (but nonetheless silly) namespace issues with the SOAP interface. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1810 diff changeset	171 )
4910bcfab319 Waterway gauge import: Work around the expected (but nonetheless silly) namespace issues with the SOAP interface. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1810 diff changeset	172
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	173 func (wg *WaterwayGauge) Do(
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	174 ctx context.Context,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	175 importID int64,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	176 conn *sql.Conn,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	177 feedback Feedback,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	178 ) (interface{}, error) {
1810 7ee9bdaac336 Waterway gauge import: Added support for username/password. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1808 diff changeset	179
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	180 start := time.Now()
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	181
3551 a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	182 responseData, countries, err := getRisData(
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	183 ctx,
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	184 conn,
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	185 feedback,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	186 wg.Username,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	187 wg.Password,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	188 wg.URL,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	189 wg.Insecure,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	190 "wtwgag")
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	191 if err != nil {
1812 4910bcfab319 Waterway gauge import: Work around the expected (but nonetheless silly) namespace issues with the SOAP interface. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1810 diff changeset	192 return nil, err
4910bcfab319 Waterway gauge import: Work around the expected (but nonetheless silly) namespace issues with the SOAP interface. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1810 diff changeset	193 }
4910bcfab319 Waterway gauge import: Work around the expected (but nonetheless silly) namespace issues with the SOAP interface. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1810 diff changeset	194
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3648 diff changeset	195 var eraseGaugeStmt, insertStmt,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	196 fixValidityStmt, updateStmt,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	197 deleteReferenceWaterLevelsStmt,
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	198 isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	199 for _, x := range []struct {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	200 sql string
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	201 stmt **sql.Stmt
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	202 }{
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	203 {eraseGaugeSQL, &eraseGaugeStmt},
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	204 {insertGaugeSQL, &insertStmt},
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	205 {fixValiditySQL, &fixValidityStmt},
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	206 {updateGaugeSQL, &updateStmt},
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	207 {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	208 {isNtSDepthRefSQL, &isNtSDepthRefStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	209 {insertReferenceWaterLevelsSQL, &insertWaterLevelStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	210 } {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	211 var err error
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	212 if *x.stmt, err = conn.PrepareContext(ctx, x.sql); err != nil {
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	213 return nil, err
b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	214 }
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	215 defer (*x.stmt).Close()
1812 4910bcfab319 Waterway gauge import: Work around the expected (but nonetheless silly) namespace issues with the SOAP interface. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1810 diff changeset	216 }
1810 7ee9bdaac336 Waterway gauge import: Added support for username/password. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1808 diff changeset	217
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	218 var gauges []string
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	219 var unchanged int
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	220
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	221 for _, data := range responseData {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	222 for _, dr := range data.RisdataReturn {
1836 4dcdd8891770 Waterway gauges import: Fixed insert/update of gauges. TODO: Re-insert reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1835 diff changeset	223
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	224 isrs := string(*dr.RisidxCode)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	225 code, err := models.IsrsFromString(isrs)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	226 if err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	227 feedback.Warn("Invalid ISRS code '%s': %v", isrs, err)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	228 continue
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	229 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	230 gauges = append(gauges, isrs)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	231 feedback.Info("Processing %s", code)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	232
3529 ba0339118d9c Improve validation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3460 diff changeset	233 // We need a valid, non-empty time range to identify gauge versions
3460 50d40ed6e3da Don't dereference pointers before checking them not to be nil. Sascha L. Teichmann <teichmann@intevation.de> parents: 3459 diff changeset	234 if dr.Enddate != nil && dr.Startdate != nil &&
3529 ba0339118d9c Improve validation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3460 diff changeset	235 !time.Time(dr.Enddate).After(time.Time(dr.Startdate)) {
ba0339118d9c Improve validation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3460 diff changeset	236 feedback.Warn("End date not after start date")
3458 aa3c83fb7018 Validate temporal validity of gauges on import Tom Gottfried <tom@intevation.de> parents: 3402 diff changeset	237 unchanged++
aa3c83fb7018 Validate temporal validity of gauges on import Tom Gottfried <tom@intevation.de> parents: 3402 diff changeset	238 continue
aa3c83fb7018 Validate temporal validity of gauges on import Tom Gottfried <tom@intevation.de> parents: 3402 diff changeset	239 }
aa3c83fb7018 Validate temporal validity of gauges on import Tom Gottfried <tom@intevation.de> parents: 3402 diff changeset	240
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	241 var from, to sql.NullInt64
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	242
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	243 if dr.Applicabilityfromkm != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	244 from = sql.NullInt64{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	245 Int64: int64(*dr.Applicabilityfromkm),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	246 Valid: true,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	247 }
1836 4dcdd8891770 Waterway gauges import: Fixed insert/update of gauges. TODO: Re-insert reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1835 diff changeset	248 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	249 if dr.Applicabilitytokm != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	250 to = sql.NullInt64{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	251 Int64: int64(*dr.Applicabilitytokm),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	252 Valid: true,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	253 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	254 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	255
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	256 var tfrom, tto, dateInfo pgtype.Timestamptz
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	257
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	258 if dr.Startdate != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	259 tfrom = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	260 Time: time.Time(*dr.Startdate),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	261 Status: pgtype.Present,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	262 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	263 } else {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	264 tfrom = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	265 Status: pgtype.Null,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	266 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	267 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	268
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	269 if dr.Enddate != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	270 tto = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	271 Time: time.Time(*dr.Enddate),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	272 Status: pgtype.Present,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	273 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	274 } else {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	275 tto = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	276 Status: pgtype.Null,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	277 }
1836 4dcdd8891770 Waterway gauges import: Fixed insert/update of gauges. TODO: Re-insert reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1835 diff changeset	278 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	279
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	280 validity := pgtype.Tstzrange{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	281 Lower: tfrom,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	282 Upper: tto,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	283 LowerType: pgtype.Inclusive,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	284 UpperType: pgtype.Exclusive,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	285 Status: pgtype.Present,
1836 4dcdd8891770 Waterway gauges import: Fixed insert/update of gauges. TODO: Re-insert reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1835 diff changeset	286 }
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	287
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	288 if dr.Infodate != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	289 dateInfo = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	290 Time: time.Time(*dr.Infodate),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	291 Status: pgtype.Present,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	292 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	293 } else {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	294 dateInfo = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	295 Status: pgtype.Null,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	296 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	297 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	298
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	299 var geodref sql.NullString
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	300 if dr.Geodref != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	301 geodref = sql.NullString{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	302 String: string(*dr.Geodref),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	303 Valid: true,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	304 }
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	305 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	306
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	307 var source sql.NullString
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	308 if dr.Source != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	309 source = sql.NullString{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	310 String: string(*dr.Source),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	311 Valid: true,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	312 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	313 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	314
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	315 tx, err := conn.BeginTx(ctx, nil)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	316 if err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	317 return nil, err
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	318 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	319 defer tx.Rollback()
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	320
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	321 // Mark old entry of gauge as erased, if applicable
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	322 var isNew bool
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	323 err = tx.StmtContext(ctx, eraseGaugeStmt).QueryRowContext(ctx,
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	324 code.String(),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	325 validity,
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	326 ).Scan(&isNew)
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	327 switch {
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	328 case err != nil:
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	329 feedback.Warn(handleError(err).Error())
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	330 if err2 := tx.Rollback(); err2 != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	331 return nil, err2
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	332 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	333 unchanged++
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	334 continue
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	335 case isNew:
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	336 // insert gauge version entry
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	337 if _, err = tx.StmtContext(ctx, insertStmt).ExecContext(ctx,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	338 code.CountryCode,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	339 code.LoCode,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	340 code.FairwaySection,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	341 code.Orc,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	342 code.Hectometre,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	343 dr.Objname.Loc,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	344 dr.Lon, dr.Lat,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	345 from,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	346 to,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	347 &validity,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	348 dr.Zeropoint,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	349 geodref,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	350 &dateInfo,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	351 source,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	352 time.Time(*dr.Lastupdate),
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	353 ); err != nil {
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	354 feedback.Warn(handleError(err).Error())
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	355 if err2 := tx.Rollback(); err2 != nil {
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	356 return nil, err2
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	357 }
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	358 unchanged++
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	359 continue
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	360 }
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	361 feedback.Info("insert new version")
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	362 case !isNew:
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	363 // try to update
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	364 var dummy int
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	365 err2 := tx.StmtContext(ctx, updateStmt).QueryRowContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	366 code.CountryCode,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	367 code.LoCode,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	368 code.FairwaySection,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	369 code.Orc,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	370 code.Hectometre,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	371 dr.Objname.Loc,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	372 dr.Lon, dr.Lat,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	373 from,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	374 to,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	375 dr.Zeropoint,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	376 geodref,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	377 &dateInfo,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	378 source,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	379 time.Time(*dr.Lastupdate),
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	380 &validity,
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	381 ).Scan(&dummy)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	382 switch {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	383 case err2 == sql.ErrNoRows:
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	384 feedback.Info("unchanged")
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	385 if err3 := tx.Rollback(); err3 != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	386 return nil, err3
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	387 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	388 unchanged++
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	389 continue
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	390 case err2 != nil:
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	391 feedback.Warn(handleError(err2).Error())
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	392 if err3 := tx.Rollback(); err3 != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	393 return nil, err3
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	394 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	395 unchanged++
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	396 continue
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	397 default:
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	398 feedback.Info("update")
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	399 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	400
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	401 // Remove obsolete reference water levels
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	402 var currLevels pgtype.VarcharArray
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	403 currLevels.Set([]string{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	404 string(*dr.Reflevel1code),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	405 string(*dr.Reflevel2code),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	406 string(*dr.Reflevel3code),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	407 })
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	408 rwls, err := tx.StmtContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	409 deleteReferenceWaterLevelsStmt).QueryContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	410 code.String(),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	411 &validity,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	412 &currLevels,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	413 )
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	414 if err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	415 return nil, err
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	416 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	417 defer rwls.Close()
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	418 for rwls.Next() {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	419 var delRef string
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	420 if err := rwls.Scan(&delRef); err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	421 return nil, err
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	422 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	423 feedback.Warn("Removed reference water level %s from %s",
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	424 delRef, code)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	425 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	426 if err := rwls.Err(); err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	427 return nil, err
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	428 }
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	429 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	430
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	431 // Set end of validity of old version to start of new version
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	432 // in case of overlap
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	433 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	434 ctx,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	435 code.String(),
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	436 &validity,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	437 ); err != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	438 feedback.Warn(handleError(err).Error())
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	439 if err2 := tx.Rollback(); err2 != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	440 return nil, err2
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	441 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	442 unchanged++
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	443 continue
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	444 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	445
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	446 // "Upsert" reference water levels
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	447 for _, wl := range []struct {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	448 level **erdms.RisreflevelcodeType
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	449 value **erdms.RisreflevelvalueType
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	450 }{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	451 {&dr.Reflevel1code, &dr.Reflevel1value},
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	452 {&dr.Reflevel2code, &dr.Reflevel2value},
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	453 {&dr.Reflevel3code, &dr.Reflevel3value},
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	454 } {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	455 if wl.level == nil \|\| wl.value == nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	456 continue
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	457 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	458
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	459 var isNtSDepthRef bool
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	460 if err := tx.StmtContext(
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	461 ctx, isNtSDepthRefStmt).QueryRowContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	462 string(**wl.level),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	463 ).Scan(
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	464 &isNtSDepthRef,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	465 ); err != nil {
3305 5932f9574493 Follow-up for 3350:e640f51b5a4e (Fix reporting of removed reference water levels). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3302 diff changeset	466 return nil, err
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	467 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	468 if !isNtSDepthRef {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	469 feedback.Warn(
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	470 "Reference level code '%s' is not in line "+
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	471 "with the NtS reference_code table",
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	472 string(**wl.level))
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	473 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	474
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	475 if _, err := tx.StmtContext(
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	476 ctx, insertWaterLevelStmt).ExecContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	477 code.CountryCode,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	478 code.LoCode,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	479 code.FairwaySection,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	480 code.Orc,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	481 code.Hectometre,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	482 &validity,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	483 string(**wl.level),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	484 int64(**wl.value),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	485 ); err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	486 feedback.Warn(handleError(err).Error())
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	487 tx.Rollback()
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	488 continue
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	489 }
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	490 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	491
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	492 if err = tx.Commit(); err != nil {
3305 5932f9574493 Follow-up for 3350:e640f51b5a4e (Fix reporting of removed reference water levels). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3302 diff changeset	493 return nil, err
5932f9574493 Follow-up for 3350:e640f51b5a4e (Fix reporting of removed reference water levels). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3302 diff changeset	494 }
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	495 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	496 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	497
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	498 if len(gauges) == 0 {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	499 return nil, UnchangedError("No gauges returned from ERDMS")
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	500 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	501
3551 a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	502 var pgCountries, pgGauges pgtype.VarcharArray
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	503 pgCountries.Set(countries)
3311 0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	504 pgGauges.Set(gauges)
3551 a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	505 obsGauges, err := conn.QueryContext(ctx,
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	506 eraseObsoleteGaugesSQL,
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	507 &pgCountries,
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	508 &pgGauges)
3311 0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	509 if err != nil {
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	510 return nil, err
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	511 }
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	512 defer obsGauges.Close()
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	513 for obsGauges.Next() {
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	514 var isrs string
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	515 if err := obsGauges.Scan(&isrs); err != nil {
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	516 return nil, err
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	517 }
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	518 feedback.Info("Erased %s", isrs)
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	519 unchanged--
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	520 }
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	521 if err := obsGauges.Err(); err != nil {
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	522 return nil, err
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	523 }
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	524
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	525 if unchanged == len(gauges) {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	526 return nil, UnchangedError("All gauges unchanged")
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	527 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	528
3311 0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	529 feedback.Info("Importing gauges took %s",
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	530 time.Since(start))
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	531
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	532 return nil, err
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	533 }

Mercurial > gemma

annotate pkg/imports/wg.go @ 3678:8f58851927c0