gemma: pkg/imports/wg.go annotate

annotate pkg/imports/wg.go @ 3302:ec6163c6687d

'Historicise' gauges on import Gauge data sets will be updated or a new version will be inserted depending on temporal validity and a timestamp marking the last update in the RIS-Index of a data set. The trigger on date_info is removed because the value is actually an attribut coming from the RIS-Index. Gauge measurements and predictions are associated to the version with matching temporal validity. Bottlenecks are always associated to the actual version of the gauge, although this might change as soon as bottlenecks are 'historicised', too.

author	Tom Gottfried <tom@intevation.de>
date	Thu, 16 May 2019 18:41:43 +0200
parents	e640f51b5a4e
children	5932f9574493

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 (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	68 eraseGaugeSQL = `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	69 UPDATE waterway.gauges SET
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	70 erased = true,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	71 -- Set enddate of old entry to new startdate in case of overlap:
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	72 validity = validity - $2
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	73 WHERE isrs_astext(location) = $1
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	74 AND NOT erased
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	75 -- Don't touch old entry if validity did not change: will be updated
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	76 AND validity <> $2
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	77 `
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	78
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	79 insertGaugeSQL = `
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	80 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	81 location,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	82 objname,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	83 geom,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	84 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	85 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	86 validity,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	87 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	88 geodref,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	89 date_info,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	90 source_organization,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	91 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	92 ) VALUES (
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	93 ($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	94 $6,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	95 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	96 $9,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	97 $10,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	98 $11,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	99 $12,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	100 $13,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	101 $14,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	102 $15,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	103 $16
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	104 -- Exclusion constraints are not supported as arbiters.
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	105 -- Thus we need to DO NOTHING here and use an extra UPDATE statement
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	106 ) ON CONFLICT DO NOTHING
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	107 RETURNING 1
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	108 `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	109 updateGaugeSQL = `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	110 UPDATE waterway.gauges SET
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	111 objname = $6,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	112 geom = ST_SetSRID(ST_MakePoint($7, $8), 4326),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	113 applicability_from_km = $9,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	114 applicability_to_km = $10,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	115 zero_point = $11,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	116 geodref = $12,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	117 date_info = $13,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	118 source_organization = $14,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	119 lastupdate = $15
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	120 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	121 AND NOT erased
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	122 AND $15 > lastupdate
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	123 RETURNING 1
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	124 `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	125
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	126 deleteReferenceWaterLevelsSQL = `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	127 DELETE FROM waterway.gauges_reference_water_levels
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	128 WHERE isrs_astext(location) = $1
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	129 AND validity = $2
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	130 AND depth_reference <> ALL($3)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	131 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	132 `
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	133
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	134 isNtSDepthRefSQL = `
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	135 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	136
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	137 insertReferenceWaterLevelsSQL = `
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	138 INSERT INTO waterway.gauges_reference_water_levels (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	139 location,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	140 validity,
2317 8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2187 diff changeset	141 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	142 value
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	143 ) VALUES (
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	144 ($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	145 $6,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	146 $7,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	147 $8
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	148 ) 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	149 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	150 `
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	151 )
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	152
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	153 func (wg *WaterwayGauge) Do(
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	154 ctx context.Context,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	155 importID int64,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	156 conn *sql.Conn,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	157 feedback Feedback,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	158 ) (interface{}, error) {
1810 7ee9bdaac336 Waterway gauge import: Added support for username/password. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1808 diff changeset	159
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	160 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	161
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	162 responseData, err := getRisData(
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	163 ctx,
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	164 conn,
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	165 feedback,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	166 wg.Username,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	167 wg.Password,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	168 wg.URL,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	169 wg.Insecure,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	170 "wtwgag")
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	171 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	172 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	173 }
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	174
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	175 var ignored int
b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	176
b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	177 type idxCode struct {
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	178 jdx int
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	179 idx int
b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	180 code *models.Isrs
b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	181 }
1810 7ee9bdaac336 Waterway gauge import: Added support for username/password. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1808 diff changeset	182
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	183 var gauges []idxCode
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	184
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	185 for j, data := range responseData {
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	186 for i, dr := range data.RisdataReturn {
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	187 if dr.RisidxCode == nil {
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	188 ignored++
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	189 continue
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	190 }
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	191 code, err := models.IsrsFromString(string(*dr.RisidxCode))
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	192 if err != nil {
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	193 feedback.Warn("invalid ISRS code %v", err)
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	194 ignored++
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	195 continue
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	196 }
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	197
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	198 if dr.Objname.Loc == nil {
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	199 feedback.Warn("missing objname: %s", code)
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	200 ignored++
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	201 continue
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	202 }
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	203
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	204 if dr.Lat == nil \|\| dr.Lon == nil {
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	205 feedback.Warn("missing lat/lon: %s", code)
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	206 ignored++
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	207 continue
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	208 }
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	209
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	210 if dr.Zeropoint == nil {
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	211 feedback.Warn("missing zeropoint: %s", code)
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	212 ignored++
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	213 continue
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	214 }
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	215
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	216 gauges = append(gauges, idxCode{jdx: j, idx: i, code: code})
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	217 }
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	218 }
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	219 feedback.Info("Ignored gauges: %d", ignored)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	220 feedback.Info("Further process %d gauges", len(gauges))
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	221
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	222 if len(gauges) == 0 {
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	223 return nil, UnchangedError("Nothing to do")
1829 b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	224 }
b4b9089c2d79 Waterway gauges: Started with deleting old gauges to be overwritten. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1812 diff changeset	225
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	226 // insert/update the gauges
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	227 var eraseGaugeStmt, insertStmt, updateStmt,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	228 deleteReferenceWaterLevelsStmt,
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	229 isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	230 for _, x := range []struct {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	231 sql string
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	232 stmt **sql.Stmt
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	233 }{
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	234 {eraseGaugeSQL, &eraseGaugeStmt},
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	235 {insertGaugeSQL, &insertStmt},
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	236 {updateGaugeSQL, &updateStmt},
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	237 {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	238 {isNtSDepthRefSQL, &isNtSDepthRefStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	239 {insertReferenceWaterLevelsSQL, &insertWaterLevelStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	240 } {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	241 var err error
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	242 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	243 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	244 }
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	245 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	246 }
1810 7ee9bdaac336 Waterway gauge import: Added support for username/password. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1808 diff changeset	247
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	248 var unchanged int
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	249
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	250 for i := range gauges {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	251 ic := &gauges[i]
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	252 dr := responseData[ic.jdx].RisdataReturn[ic.idx]
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	253
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	254 feedback.Info("Processing %s", ic.code)
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	255
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	256 var from, to sql.NullInt64
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	257
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	258 if dr.Applicabilityfromkm != nil {
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	259 from = sql.NullInt64{
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	260 Int64: int64(*dr.Applicabilityfromkm),
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	261 Valid: true,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	262 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	263 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	264 if dr.Applicabilitytokm != nil {
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	265 to = sql.NullInt64{
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	266 Int64: int64(*dr.Applicabilitytokm),
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	267 Valid: true,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	268 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	269 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	270
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	271 var tfrom, tto, dateInfo pgtype.Timestamptz
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	272
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	273 if dr.Startdate != nil {
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	274 tfrom = pgtype.Timestamptz{
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	275 Time: time.Time(*dr.Startdate),
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	276 Status: pgtype.Present,
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	277 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	278 } else {
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	279 tfrom = pgtype.Timestamptz{
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	280 Status: pgtype.Null,
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	281 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	282 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	283
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	284 if dr.Enddate != nil {
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	285 tto = pgtype.Timestamptz{
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 Time: time.Time(*dr.Enddate),
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	287 Status: pgtype.Present,
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	288 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	289 } else {
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	290 tto = pgtype.Timestamptz{
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	291 Status: pgtype.Null,
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	292 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	293 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	294
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	295 validity := pgtype.Tstzrange{
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	296 Lower: tfrom,
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	297 Upper: tto,
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	298 LowerType: pgtype.Inclusive,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	299 UpperType: pgtype.Exclusive,
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	300 Status: pgtype.Present,
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	301 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	302
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	303 if dr.Infodate != nil {
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	304 dateInfo = pgtype.Timestamptz{
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	305 Time: time.Time(*dr.Infodate),
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	306 Status: pgtype.Present,
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	307 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	308 } else {
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	309 dateInfo = pgtype.Timestamptz{
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	310 Status: pgtype.Null,
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	311 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	312 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	313
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	314 var geodref sql.NullString
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	315 if dr.Geodref != nil {
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	316 geodref = sql.NullString{
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	317 String: string(*dr.Geodref),
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	318 Valid: true,
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	319 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	320 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	321
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	322 var source sql.NullString
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	323 if dr.Source != nil {
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	324 source = sql.NullString{
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	325 String: string(*dr.Source),
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	326 Valid: true,
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	327 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	328 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	329
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	330 tx, err := conn.BeginTx(ctx, nil)
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	331 if err != nil {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	332 return nil, err
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	333 }
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	334 defer tx.Rollback()
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	335
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	336 // Mark old entries of gauge as erased, if applicable
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	337 if _, err := tx.StmtContext(ctx, eraseGaugeStmt).ExecContext(ctx,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	338 ic.code.String(),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	339 validity,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	340 ); err != nil {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	341 feedback.Warn(handleError(err).Error())
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	342 if err2 := tx.Rollback(); err2 != nil {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	343 return nil, err2
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	344 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	345 unchanged++
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	346 continue
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	347 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	348
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	349 // Try to insert gauge entry
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	350 var dummy int
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	351 err = tx.StmtContext(ctx, insertStmt).QueryRowContext(ctx,
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	352 ic.code.CountryCode,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	353 ic.code.LoCode,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	354 ic.code.FairwaySection,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	355 ic.code.Orc,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	356 ic.code.Hectometre,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	357 string(*dr.Objname.Loc),
2518 e199655809c1 Dont cast lat/lon values from ERDMS service to ints. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2318 diff changeset	358 float64(dr.Lon), float64(dr.Lat),
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	359 from,
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	360 to,
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	361 &validity,
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	362 float64(*dr.Zeropoint),
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	363 geodref,
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	364 &dateInfo,
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	365 source,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	366 time.Time(*dr.Lastupdate),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	367 ).Scan(&dummy)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	368 switch {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	369 case err == sql.ErrNoRows:
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	370 // Assume constraint conflict, try to update
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	371 err2 := tx.StmtContext(ctx, updateStmt).QueryRowContext(ctx,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	372 ic.code.CountryCode,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	373 ic.code.LoCode,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	374 ic.code.FairwaySection,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	375 ic.code.Orc,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	376 ic.code.Hectometre,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	377 string(*dr.Objname.Loc),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	378 float64(dr.Lon), float64(dr.Lat),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	379 from,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	380 to,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	381 float64(*dr.Zeropoint),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	382 geodref,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	383 &dateInfo,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	384 source,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	385 time.Time(*dr.Lastupdate),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	386 ).Scan(&dummy)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	387 switch {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	388 case err2 == sql.ErrNoRows:
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	389 feedback.Info("unchanged")
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	390 if err3 := tx.Rollback(); err3 != nil {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	391 return nil, err3
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	392 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	393 unchanged++
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	394 continue
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	395 case err2 != nil:
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	396 feedback.Warn(handleError(err2).Error())
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	397 if err3 := tx.Rollback(); err3 != nil {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	398 return nil, err3
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	399 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	400 unchanged++
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	401 continue
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	402 default:
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	403 feedback.Info("update")
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	404 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	405
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	406 // Remove obsolete reference water levels
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	407 var currLevels pgtype.VarcharArray
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	408 currLevels.Set([]string{
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	409 string(*dr.Reflevel1code),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	410 string(*dr.Reflevel2code),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	411 string(*dr.Reflevel3code),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	412 })
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	413 rwls, err2 := tx.StmtContext(ctx,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	414 deleteReferenceWaterLevelsStmt).QueryContext(ctx,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	415 ic.code.String(),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	416 &validity,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	417 &currLevels,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	418 )
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	419 if err2 != nil {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	420 return nil, err2
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	421 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	422 defer rwls.Close()
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	423 for rwls.Next() {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	424 var delRef string
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	425 if err2 = rwls.Scan(&delRef); err2 != nil {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	426 return nil, err2
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	427 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	428 feedback.Warn("Removed reference water level %s from %s",
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	429 delRef, ic.code)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	430 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	431 case err != nil:
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	432 feedback.Warn(handleError(err).Error())
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	433 if err2 := tx.Rollback(); err2 != nil {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	434 return nil, err2
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	435 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	436 unchanged++
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	437 continue
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	438 default:
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	439 feedback.Info("insert new version")
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	440 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	441
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	442 // "Upsert" reference water levels
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	443 for _, wl := range []struct {
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	444 level **erdms.RisreflevelcodeType
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	445 value **erdms.RisreflevelvalueType
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	446 }{
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	447 {&dr.Reflevel1code, &dr.Reflevel1value},
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	448 {&dr.Reflevel2code, &dr.Reflevel2value},
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	449 {&dr.Reflevel3code, &dr.Reflevel3value},
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	450 } {
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	451 if wl.level == nil \|\| wl.value == nil {
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	452 continue
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	453 }
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	454
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	455 var isNtSDepthRef bool
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	456 if err := tx.StmtContext(ctx, isNtSDepthRefStmt).QueryRowContext(
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	457 ctx,
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	458 string(**wl.level),
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	459 ).Scan(
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	460 &isNtSDepthRef,
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	461 ); err != nil {
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	462 return nil, err
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	463 }
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	464 if !isNtSDepthRef {
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	465 feedback.Warn(
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	466 "Reference level code '%s' is not in line "+
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	467 "with the NtS reference_code table",
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	468 string(**wl.level))
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	469 }
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	470
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	471 if _, err := tx.StmtContext(ctx, insertWaterLevelStmt).ExecContext(
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	472 ctx,
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	473 ic.code.CountryCode,
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	474 ic.code.LoCode,
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	475 ic.code.FairwaySection,
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	476 ic.code.Orc,
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	477 ic.code.Hectometre,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	478 &validity,
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	479 string(**wl.level),
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	480 int64(**wl.value),
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	481 ); err != nil {
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	482 feedback.Warn(handleError(err).Error())
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	483 tx.Rollback()
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	484 continue
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	485 }
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	486 }
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	487
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	488 if err = tx.Commit(); err != nil {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	489 return nil, err
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	490 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	491 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	492
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	493 feedback.Info("Importing gauges took %s",
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	494 time.Since(start))
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	495
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	496 if unchanged == len(gauges) {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	497 return nil, UnchangedError("All gauges unchanged")
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	498 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	499
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	500 return nil, err
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	501 }

Mercurial > gemma

annotate pkg/imports/wg.go @ 3302:ec6163c6687d