gemma: pkg/imports/wg.go annotate

annotate pkg/imports/wg.go @ 4497:ca7f9c56697a

WG import: "End date not after start date" is an hard error. As this leads to data not being imported it should be marked as Error (not Warning) in the Logs).

author	Sascha Wilde <wilde@intevation.de>
date	Mon, 30 Sep 2019 13:41:39 +0200
parents	5d7ce7f926eb
children	ca6a5f722471

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

Mercurial > gemma

annotate pkg/imports/wg.go @ 4497:ca7f9c56697a