Mercurial > gemma

annotate pkg/imports/wg.go @ 3460:50d40ed6e3da

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