gemma: pkg/imports/wg.go annotate

annotate pkg/imports/wg.go @ 5591:0011f50cf216 surveysperbottleneckid

Removed no longer used alternative api for surveys/ endpoint. As bottlenecks in the summary for SR imports are now identified by their id and no longer by the (not guarantied to be unique!) name, there is no longer the need to request survey data by the name+date tuple (which isn't reliable anyway). So the workaround was now reversed.

author	Sascha Wilde <wilde@sha-bang.de>
date	Wed, 06 Apr 2022 13:30:29 +0200
parents	ade07a3f2cfd
children	e1936db6db8e

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

Mercurial > gemma

annotate pkg/imports/wg.go @ 5591:0011f50cf216 surveysperbottleneckid