gemma: pkg/imports/wg.go annotate

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

Avoid empty validity time ranges An entry which is not valid at any point in time makes no sense. Further, multiple of such entries would violate a UNIQUE constraint. Since an UPDATE now can change validity time ranges, do all the adjustments for that regardles of whether an INSERT or UPDATE happens.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 12 Jun 2019 18:26:26 +0200
parents	02951a62e8c6
children	29ef6d41e4af

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 moveGMSQL = `
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	120 UPDATE waterway.gauge_measurements
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	121 -- Associate measurements to matching gauge version
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	122 SET 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 measure_date <@ CAST($2 AS tstzrange)
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	125 `
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	126
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	127 moveBNSQL = `
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	128 UPDATE waterway.bottlenecks
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	129 -- Associate bottlenecks to matching gauge version
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	130 SET gauge_validity = $2
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	131 WHERE isrs_astext(gauge_location) = $1
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	132 AND lower(validity) <@ CAST($2 AS tstzrange)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	133 `
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	134
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	135 fixValiditySQL = `
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	136 UPDATE waterway.gauges SET
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	137 -- 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	138 validity = validity - $2
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	139 WHERE isrs_astext(location) = $1
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	140 AND validity && $2
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	141 AND erased
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	142 `
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	143
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	144 updateGaugeSQL = `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	145 UPDATE waterway.gauges SET
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	146 objname = $6,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	147 geom = ST_SetSRID(ST_MakePoint($7, $8), 4326),
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	148 applicability_from_km = $9,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	149 applicability_to_km = $10,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	150 zero_point = $11,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	151 geodref = $12,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	152 date_info = $13,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	153 source_organization = $14,
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	154 lastupdate = $15,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	155 validity = $16
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	156 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	157 AND NOT erased
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	158 AND $15 > lastupdate
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	159 RETURNING 1
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	160 `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	161
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	162 deleteReferenceWaterLevelsSQL = `
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	163 DELETE FROM waterway.gauges_reference_water_levels
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	164 WHERE isrs_astext(location) = $1
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	165 AND validity = $2
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	166 AND depth_reference <> ALL($3)
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	167 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	168 `
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	169
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	170 isNtSDepthRefSQL = `
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	171 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	172
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	173 insertReferenceWaterLevelsSQL = `
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	174 INSERT INTO waterway.gauges_reference_water_levels (
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	175 location,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	176 validity,
2317 8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2187 diff changeset	177 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	178 value
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	179 ) VALUES (
00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	180 ($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	181 $6,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	182 $7,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	183 $8
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	184 ) 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	185 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	186 `
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	187 )
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	188
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	189 func (wg *WaterwayGauge) Do(
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	190 ctx context.Context,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	191 importID int64,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	192 conn *sql.Conn,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	193 feedback Feedback,
77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	194 ) (interface{}, error) {
1810 7ee9bdaac336 Waterway gauge import: Added support for username/password. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1808 diff changeset	195
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	196 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	197
3551 a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	198 responseData, countries, err := getRisData(
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	199 ctx,
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	200 conn,
2633 eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	201 feedback,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	202 wg.Username,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	203 wg.Password,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	204 wg.URL,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	205 wg.Insecure,
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	206 "wtwgag")
eb1d119f253f Fetch data from ERDMS for all allowed countries Tom Gottfried <tom@intevation.de> parents: 2518 diff changeset	207 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	208 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	209 }
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	210
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	211 var eraseGaugeStmt, insertStmt, moveGMStmt, moveBNStmt,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	212 fixValidityStmt, updateStmt,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	213 deleteReferenceWaterLevelsStmt,
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	214 isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	215 for _, x := range []struct {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	216 sql string
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	217 stmt **sql.Stmt
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	218 }{
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	219 {eraseGaugeSQL, &eraseGaugeStmt},
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	220 {insertGaugeSQL, &insertStmt},
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	221 {moveGMSQL, &moveGMStmt},
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3551 diff changeset	222 {moveBNSQL, &moveBNStmt},
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	223 {fixValiditySQL, &fixValidityStmt},
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	224 {updateGaugeSQL, &updateStmt},
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	225 {deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	226 {isNtSDepthRefSQL, &isNtSDepthRefStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	227 {insertReferenceWaterLevelsSQL, &insertWaterLevelStmt},
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	228 } {
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	229 var err error
d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	230 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	231 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	232 }
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	233 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	234 }
1810 7ee9bdaac336 Waterway gauge import: Added support for username/password. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1808 diff changeset	235
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	236 var gauges []string
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	237 var unchanged int
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	238
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	239 for _, data := range responseData {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	240 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	241
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	242 isrs := string(*dr.RisidxCode)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	243 code, err := models.IsrsFromString(isrs)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	244 if err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	245 feedback.Warn("Invalid ISRS code '%s': %v", isrs, err)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	246 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	247 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	248 gauges = append(gauges, isrs)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	249 feedback.Info("Processing %s", code)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	250
3529 ba0339118d9c Improve validation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3460 diff changeset	251 // 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	252 if dr.Enddate != nil && dr.Startdate != nil &&
3529 ba0339118d9c Improve validation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3460 diff changeset	253 !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	254 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	255 unchanged++
aa3c83fb7018 Validate temporal validity of gauges on import Tom Gottfried <tom@intevation.de> parents: 3402 diff changeset	256 continue
aa3c83fb7018 Validate temporal validity of gauges on import Tom Gottfried <tom@intevation.de> parents: 3402 diff changeset	257 }
aa3c83fb7018 Validate temporal validity of gauges on import Tom Gottfried <tom@intevation.de> parents: 3402 diff changeset	258
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	259 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	260
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	261 if dr.Applicabilityfromkm != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	262 from = sql.NullInt64{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	263 Int64: int64(*dr.Applicabilityfromkm),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	264 Valid: true,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	265 }
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	266 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	267 if dr.Applicabilitytokm != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	268 to = sql.NullInt64{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	269 Int64: int64(*dr.Applicabilitytokm),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	270 Valid: true,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	271 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	272 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	273
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	274 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	275
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	276 if dr.Startdate != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	277 tfrom = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	278 Time: time.Time(*dr.Startdate),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	279 Status: pgtype.Present,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	280 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	281 } else {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	282 tfrom = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	283 Status: pgtype.Null,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	284 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	285 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	286
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	287 if dr.Enddate != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	288 tto = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	289 Time: time.Time(*dr.Enddate),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	290 Status: pgtype.Present,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	291 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	292 } else {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	293 tto = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	294 Status: pgtype.Null,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	295 }
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 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	297
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	298 validity := pgtype.Tstzrange{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	299 Lower: tfrom,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	300 Upper: tto,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	301 LowerType: pgtype.Inclusive,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	302 UpperType: pgtype.Exclusive,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	303 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	304 }
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	305
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	306 if dr.Infodate != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	307 dateInfo = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	308 Time: time.Time(*dr.Infodate),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	309 Status: pgtype.Present,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	310 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	311 } else {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	312 dateInfo = pgtype.Timestamptz{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	313 Status: pgtype.Null,
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 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	316
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	317 var geodref sql.NullString
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	318 if dr.Geodref != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	319 geodref = sql.NullString{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	320 String: string(*dr.Geodref),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	321 Valid: true,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	322 }
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	323 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	324
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	325 var source sql.NullString
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	326 if dr.Source != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	327 source = sql.NullString{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	328 String: string(*dr.Source),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	329 Valid: true,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	330 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	331 }
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 tx, err := conn.BeginTx(ctx, nil)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	334 if err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	335 return nil, err
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	336 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	337 defer tx.Rollback()
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	338
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	339 // 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	340 var isNew bool
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	341 err = tx.StmtContext(ctx, eraseGaugeStmt).QueryRowContext(ctx,
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	342 code.String(),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	343 validity,
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	344 ).Scan(&isNew)
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	345 switch {
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	346 case err != nil:
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	347 feedback.Warn(handleError(err).Error())
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	348 if err2 := tx.Rollback(); err2 != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	349 return nil, err2
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	350 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	351 unchanged++
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	352 continue
3402 c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	353 case isNew:
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	354 // insert gauge version entry
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	355 if _, err = tx.StmtContext(ctx, insertStmt).ExecContext(ctx,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	356 code.CountryCode,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	357 code.LoCode,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	358 code.FairwaySection,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	359 code.Orc,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	360 code.Hectometre,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	361 dr.Objname.Loc,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	362 dr.Lon, dr.Lat,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	363 from,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	364 to,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	365 &validity,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	366 dr.Zeropoint,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	367 geodref,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	368 &dateInfo,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	369 source,
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	370 time.Time(*dr.Lastupdate),
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	371 ); err != nil {
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	372 feedback.Warn(handleError(err).Error())
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	373 if err2 := tx.Rollback(); err2 != nil {
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	374 return nil, err2
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	375 }
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	376 unchanged++
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	377 continue
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	378 }
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	379 feedback.Info("insert new version")
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	380 case !isNew:
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	381 // try to update
c04b1409a596 Fix adaptation of gauge temporal validity Tom Gottfried <tom@intevation.de> parents: 3311 diff changeset	382 var dummy int
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	383 err2 := tx.StmtContext(ctx, updateStmt).QueryRowContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	384 code.CountryCode,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	385 code.LoCode,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	386 code.FairwaySection,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	387 code.Orc,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	388 code.Hectometre,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	389 dr.Objname.Loc,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	390 dr.Lon, dr.Lat,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	391 from,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	392 to,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	393 dr.Zeropoint,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	394 geodref,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	395 &dateInfo,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	396 source,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	397 time.Time(*dr.Lastupdate),
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	398 &validity,
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	399 ).Scan(&dummy)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	400 switch {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	401 case err2 == sql.ErrNoRows:
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	402 feedback.Info("unchanged")
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	403 if err3 := tx.Rollback(); err3 != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	404 return nil, err3
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	405 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	406 unchanged++
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	407 continue
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	408 case err2 != nil:
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	409 feedback.Warn(handleError(err2).Error())
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	410 if err3 := tx.Rollback(); err3 != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	411 return nil, err3
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	412 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	413 unchanged++
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	414 continue
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	415 default:
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	416 feedback.Info("update")
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	417 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	418
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	419 // Remove obsolete reference water levels
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	420 var currLevels pgtype.VarcharArray
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	421 currLevels.Set([]string{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	422 string(*dr.Reflevel1code),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	423 string(*dr.Reflevel2code),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	424 string(*dr.Reflevel3code),
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 rwls, err := tx.StmtContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	427 deleteReferenceWaterLevelsStmt).QueryContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	428 code.String(),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	429 &validity,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	430 &currLevels,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	431 )
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	432 if err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	433 return nil, err
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	434 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	435 defer rwls.Close()
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	436 for rwls.Next() {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	437 var delRef string
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	438 if err := rwls.Scan(&delRef); err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	439 return nil, err
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	440 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	441 feedback.Warn("Removed reference water level %s from %s",
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	442 delRef, code)
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	443 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	444 if err := rwls.Err(); err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	445 return nil, err
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	446 }
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	447 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	448
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	449 // Move gauge measurements and bottlenecks to new matching
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	450 // gauge version, if applicable
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	451 if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	452 code.String(),
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	453 &validity,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	454 ); err != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	455 feedback.Warn(handleError(err).Error())
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	456 if err2 := tx.Rollback(); err2 != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	457 return nil, err2
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	458 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	459 unchanged++
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	460 continue
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	461 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	462 if _, err = tx.StmtContext(ctx, moveBNStmt).ExecContext(ctx,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	463 code.String(),
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	464 &validity,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	465 ); err != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	466 feedback.Warn(handleError(err).Error())
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	467 if err2 := tx.Rollback(); err2 != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	468 return nil, err2
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	469 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	470 unchanged++
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	471 continue
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	472 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	473
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	474 // 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	475 // in case of overlap
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	476 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	477 ctx,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	478 code.String(),
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	479 &validity,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	480 ); err != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	481 feedback.Warn(handleError(err).Error())
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	482 if err2 := tx.Rollback(); err2 != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	483 return nil, err2
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	484 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	485 unchanged++
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	486 continue
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	487 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	488
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	489 // "Upsert" reference water levels
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	490 for _, wl := range []struct {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	491 level **erdms.RisreflevelcodeType
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	492 value **erdms.RisreflevelvalueType
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	493 }{
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	494 {&dr.Reflevel1code, &dr.Reflevel1value},
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	495 {&dr.Reflevel2code, &dr.Reflevel2value},
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	496 {&dr.Reflevel3code, &dr.Reflevel3value},
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	497 } {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	498 if wl.level == nil \|\| wl.value == nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	499 continue
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
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	502 var isNtSDepthRef bool
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	503 if err := tx.StmtContext(
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	504 ctx, isNtSDepthRefStmt).QueryRowContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	505 string(**wl.level),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	506 ).Scan(
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	507 &isNtSDepthRef,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	508 ); 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	509 return nil, err
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	510 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	511 if !isNtSDepthRef {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	512 feedback.Warn(
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	513 "Reference level code '%s' is not in line "+
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	514 "with the NtS reference_code table",
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	515 string(**wl.level))
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	516 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	517
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	518 if _, err := tx.StmtContext(
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	519 ctx, insertWaterLevelStmt).ExecContext(ctx,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	520 code.CountryCode,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	521 code.LoCode,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	522 code.FairwaySection,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	523 code.Orc,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	524 code.Hectometre,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	525 &validity,
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	526 string(**wl.level),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	527 int64(**wl.value),
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	528 ); err != nil {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	529 feedback.Warn(handleError(err).Error())
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	530 tx.Rollback()
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	531 continue
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	532 }
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	533 }
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	534
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	535 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	536 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	537 }
3163 d9903cb34842 Handle failing INSERTs gracefully during gauges import Tom Gottfried <tom@intevation.de> parents: 2633 diff changeset	538 }
1835 f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	539 }
f7b926440449 Waterway gauge import: More stuggling with inserting gauges. Not working, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1829 diff changeset	540
3310 e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	541 if len(gauges) == 0 {
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	542 return nil, UnchangedError("No gauges returned from ERDMS")
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	543 }
e0dabe7b2fcf Simplify gauges import Tom Gottfried <tom@intevation.de> parents: 3305 diff changeset	544
3551 a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	545 var pgCountries, pgGauges pgtype.VarcharArray
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	546 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	547 pgGauges.Set(gauges)
3551 a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	548 obsGauges, err := conn.QueryContext(ctx,
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	549 eraseObsoleteGaugesSQL,
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	550 &pgCountries,
a5448426e4e2 Only erase gauges that have been requested from ERDMS Tom Gottfried <tom@intevation.de> parents: 3529 diff changeset	551 &pgGauges)
3311 0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	552 if err != nil {
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	553 return nil, err
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	554 }
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	555 defer obsGauges.Close()
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	556 for obsGauges.Next() {
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	557 var isrs string
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	558 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	559 return nil, err
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	560 }
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	561 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	562 unchanged--
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	563 }
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	564 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	565 return nil, err
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	566 }
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	567
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	568 if unchanged == len(gauges) {
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	569 return nil, UnchangedError("All gauges unchanged")
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	570 }
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3286 diff changeset	571
3311 0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	572 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	573 time.Since(start))
0f6b156cff55 Mark gauges as erased if they are missing in ERDMS response Tom Gottfried <tom@intevation.de> parents: 3310 diff changeset	574
1837 00d63eb9306a Waterway gauges import: Finalized with re-inserting the reference water levels. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1836 diff changeset	575 return nil, err
1808 77582da3adb0 Waterway gauges import: Added stub. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	576 }

Mercurial > gemma

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