gemma: pkg/imports/bn.go annotate

annotate pkg/imports/bn.go @ 3665:29ef6d41e4af

Use database triggers to move referencing objects to new versions Needs fewer database round-trips and is more convenient especially if more than two levels in the object hierarchy have to be handled.

author	Tom Gottfried <tom@intevation.de>
date	Sat, 15 Jun 2019 09:24:28 +0200
parents	2a079d0a71c1
children	db87f34805fb

rev	line source
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	1 // This is Free Software under GNU Affero General Public License v >= 3.0
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	2 // without warranty, see README.md and license for details.
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	3 //
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	4 // SPDX-License-Identifier: AGPL-3.0-or-later
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	5 // License-Filename: LICENSES/AGPL-3.0.txt
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	6 //
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	7 // Copyright (C) 2018, 2019 by via donau
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	8 // – Österreichische Wasserstraßen-Gesellschaft mbH
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	9 // Software engineering by Intevation GmbH
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	10 //
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	11 // Author(s):
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	12 // * Sascha L. Teichmann <sascha.teichmann@intevation.de>
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	13 // * Tom Gottfried <tom.gottfried@intevation.de>
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	14
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	15 package imports
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	16
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	17 import (
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	18 "context"
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	19 "database/sql"
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	20 "errors"
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	21 "regexp"
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	22 "strconv"
3528 b9e331c1e616 Ignore zero length and whitespace-only values Tom Gottfried <tom@intevation.de> parents: 3423 diff changeset	23 "strings"
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	24 "time"
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	25
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	26 "gemma.intevation.de/gemma/pkg/soap/ifbn"
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	27 "github.com/jackc/pgx/pgtype"
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	28 )
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	29
1695 be78b429ef6e Bottleneck import: Resolved remaining golint issues. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1676 diff changeset	30 // Bottleneck is an import job to import
be78b429ef6e Bottleneck import: Resolved remaining golint issues. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1676 diff changeset	31 // bottlenecks from an IFBN SOAP service.
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	32 type Bottleneck struct {
1695 be78b429ef6e Bottleneck import: Resolved remaining golint issues. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1676 diff changeset	33 // URL is the URL of the SOAP service.
be78b429ef6e Bottleneck import: Resolved remaining golint issues. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1676 diff changeset	34 URL string `json:"url"`
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2548 diff changeset	35 // Tolerance used for axis snapping
dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2548 diff changeset	36 Tolerance float64 `json:"tolerance"`
1695 be78b429ef6e Bottleneck import: Resolved remaining golint issues. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1676 diff changeset	37 // Insecure indicates if HTTPS traffic
be78b429ef6e Bottleneck import: Resolved remaining golint issues. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1676 diff changeset	38 // should validate certificates or not.
be78b429ef6e Bottleneck import: Resolved remaining golint issues. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1676 diff changeset	39 Insecure bool `json:"insecure"`
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	40 }
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	41
1696 ad5e1cddaa09 Imports: Resolved the remaining golint issues with this package. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1695 diff changeset	42 // BNJobKind is the import queue type identifier.
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	43 const BNJobKind JobKind = "bn"
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	44
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	45 const (
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	46 hasBottleneckSQL = `
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	47 WITH upd AS (
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	48 UPDATE waterway.bottlenecks SET
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	49 erased = true
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	50 WHERE bottleneck_id = $1
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	51 AND NOT erased
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	52 -- 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	53 AND NOT validity <@ $2
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	54 RETURNING 1
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	55 )
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	56 -- Decide whether a new version will be INSERTed
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	57 SELECT EXISTS(SELECT 1 FROM upd)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	58 OR NOT EXISTS(SELECT 1 FROM waterway.bottlenecks WHERE bottleneck_id = $1)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	59 `
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	60
1658 07f996b0e14a Bottleneck import: Cleaned up source a bit (removed outdated TODOs). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1646 diff changeset	61 insertBottleneckSQL = `
3566 4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 3562 diff changeset	62 WITH
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	63 bounds (b) AS (VALUES (isrs_fromText($6)), (isrs_fromText($7))),
3566 4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 3562 diff changeset	64 r AS (SELECT isrsrange(
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 3562 diff changeset	65 (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY),
4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 3562 diff changeset	66 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r)
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	67 INSERT INTO waterway.bottlenecks (
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	68 bottleneck_id,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	69 validity,
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3224 diff changeset	70 gauge_location,
ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3224 diff changeset	71 gauge_validity,
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	72 objnam,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	73 nobjnm,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	74 stretch,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	75 area,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	76 rb,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	77 lb,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	78 responsible_country,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	79 revisiting_time,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	80 limiting,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	81 date_info,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	82 source_organization
3302 ec6163c6687d 'Historicise' gauges on import Tom Gottfried <tom@intevation.de> parents: 3224 diff changeset	83 ) VALUES (
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	84 $1,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	85 $2,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	86 isrs_fromText($3),
3533 8e083b271fca Improve error messages if no matching gauge version found Tom Gottfried <tom@intevation.de> parents: 3530 diff changeset	87 COALESCE(
8e083b271fca Improve error messages if no matching gauge version found Tom Gottfried <tom@intevation.de> parents: 3530 diff changeset	88 (SELECT validity FROM waterway.gauges
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	89 WHERE location = isrs_fromText($3)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	90 AND validity @> lower(CAST($2 AS tstzrange))),
3533 8e083b271fca Improve error messages if no matching gauge version found Tom Gottfried <tom@intevation.de> parents: 3530 diff changeset	91 tstzrange(NULL, NULL)),
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	92 $4,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	93 $5,
3566 4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 3562 diff changeset	94 (SELECT r FROM r),
1631 6ee06eb37fa9 Use new function to calculate bn area. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1611 diff changeset	95 ISRSrange_area(
3566 4c585b5d4fe8 Introduce hectometer based order for ranges of ISRS location codes Tom Gottfried <tom@intevation.de> parents: 3562 diff changeset	96 ISRSrange_axis((SELECT r FROM r),
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	97 $15),
2248 cce158db02b0 Input area as multipolygons to generate area from stretch Tom Gottfried <tom@intevation.de> parents: 2195 diff changeset	98 (SELECT ST_Collect(CAST(area AS geometry))
1902 c4af342be999 Use waterway area as basis for bottleneck area generation. Sascha Wilde <wilde@intevation.de> parents: 1754 diff changeset	99 FROM waterway.waterway_area)),
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	100 $8,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	101 $9,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	102 $10,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	103 $11,
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	104 $12,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	105 $13,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	106 $14
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	107 )
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	108 RETURNING id`
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	109
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	110 fixBNValiditySQL = `
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	111 UPDATE waterway.bottlenecks SET
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	112 -- Set enddate of old entry to new startdate in case of overlap:
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	113 validity = validity - $2
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	114 WHERE bottleneck_id = $1
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	115 AND validity && $2
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	116 AND erased
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	117 `
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	118
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	119 updateBottleneckSQL = `
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	120 WITH
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	121 bounds (b) AS (VALUES (isrs_fromText($5)), (isrs_fromText($6))),
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	122 r AS (SELECT isrsrange(
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	123 (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY),
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	124 (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	125 UPDATE waterway.bottlenecks b SET
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	126 gauge_location = isrs_fromtext($2),
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	127 gauge_validity = COALESCE(
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	128 (SELECT validity FROM waterway.gauges g
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	129 WHERE g.location = isrs_fromText($2)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	130 AND g.validity @> lower(b.validity)),
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	131 tstzrange(NULL, NULL)),
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	132 objnam = $3,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	133 nobjnm = $4,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	134 stretch = (SELECT r FROM r),
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	135 area = ISRSrange_area(
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	136 ISRSrange_axis((SELECT r FROM r), $14),
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	137 (SELECT ST_Collect(CAST(area AS geometry))
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	138 FROM waterway.waterway_area)),
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	139 rb = $7,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	140 lb = $8,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	141 responsible_country = $9,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	142 revisiting_time = $10,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	143 limiting = $11,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	144 date_info = $12,
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	145 source_organization = $13,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	146 validity = $15
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	147 WHERE bottleneck_id = $1
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	148 AND NOT erased
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	149 AND $12 > date_info
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	150 RETURNING id
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	151 `
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	152
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	153 deleteBottleneckMaterialSQL = `
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	154 DELETE FROM waterway.bottlenecks_riverbed_materials
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	155 WHERE bottleneck_id = $1
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	156 AND riverbed <> ALL($2)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	157 RETURNING riverbed
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	158 `
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	159
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	160 insertBottleneckMaterialSQL = `
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	161 INSERT INTO waterway.bottlenecks_riverbed_materials (
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	162 bottleneck_id,
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	163 riverbed
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	164 ) VALUES (
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	165 $1,
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	166 $2
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	167 ) ON CONFLICT (bottleneck_id, riverbed) DO NOTHING
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	168 `
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	169 )
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	170
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	171 type bnJobCreator struct{}
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	172
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	173 func init() {
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	174 RegisterJobCreator(BNJobKind, bnJobCreator{})
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	175 }
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	176
1754 807569b08513 Import queue: Auto acceptance is now a property of the import kind itself and is not configurable any more. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1696 diff changeset	177 func (bnJobCreator) Description() string { return "bottlenecks" }
807569b08513 Import queue: Auto acceptance is now a property of the import kind itself and is not configurable any more. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1696 diff changeset	178
807569b08513 Import queue: Auto acceptance is now a property of the import kind itself and is not configurable any more. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1696 diff changeset	179 func (bnJobCreator) AutoAccept() bool { return false }
1646 a0982c38eac0 Import queue: Implemented email notifications. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1631 diff changeset	180
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	181 func (bnJobCreator) Create() Job { return new(Bottleneck) }
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	182
3219 4acbee65275d Import queue: Split locked dependencies in exclusively and multiple uses. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3164 diff changeset	183 func (bnJobCreator) Depends() [2][]string {
4acbee65275d Import queue: Split locked dependencies in exclusively and multiple uses. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3164 diff changeset	184 return [2][]string{
4acbee65275d Import queue: Split locked dependencies in exclusively and multiple uses. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 3164 diff changeset	185 {"bottlenecks", "bottlenecks_riverbed_materials"},
3224 bfde4f8dd323 Add missing table dependencies of imports with area generation Tom Gottfried <tom@intevation.de> parents: 3219 diff changeset	186 {"gauges", "distance_marks_virtual", "waterway_axis", "waterway_area"},
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	187 }
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	188 }
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	189
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	190 const (
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	191 bnStageDoneSQL = `
1533 8fc1a2298acb Pseudo fixed unstaging of bottlenecks. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1531 diff changeset	192 UPDATE waterway.bottlenecks SET staging_done = true
8fc1a2298acb Pseudo fixed unstaging of bottlenecks. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1531 diff changeset	193 WHERE id IN (
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1975 diff changeset	194 SELECT key from import.track_imports
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	195 WHERE import_id = $1 AND
1533 8fc1a2298acb Pseudo fixed unstaging of bottlenecks. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1531 diff changeset	196 relation = 'waterway.bottlenecks'::regclass)`
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	197 )
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	198
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	199 // StageDone moves the imported bottleneck out of the staging area.
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	200 func (bnJobCreator) StageDone(
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	201 ctx context.Context,
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	202 tx *sql.Tx,
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	203 id int64,
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	204 ) error {
1594 e3abd3b3ebc6 Fixed stage done SQL for bottleneck imports. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1580 diff changeset	205 _, err := tx.ExecContext(ctx, bnStageDoneSQL, id)
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	206 return err
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	207 }
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	208
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	209 // CleanUp of a bottleneck import is a NOP.
1676 4407ecaa2192 Imports: Cosmetics. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1664 diff changeset	210 func (*Bottleneck) CleanUp() error { return nil }
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	211
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	212 var rblbRe = regexp.MustCompile(`(..)_(..)`)
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	213
2765 41f5ac76d642 BT import: allow for rb and lb to be null. Sascha Wilde <wilde@intevation.de> parents: 2752 diff changeset	214 func splitRBLB(s string) (string, string) {
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	215 m := rblbRe.FindStringSubmatch(s)
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	216 if len(m) == 0 {
2765 41f5ac76d642 BT import: allow for rb and lb to be null. Sascha Wilde <wilde@intevation.de> parents: 2752 diff changeset	217 return nil, nil
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	218 }
2765 41f5ac76d642 BT import: allow for rb and lb to be null. Sascha Wilde <wilde@intevation.de> parents: 2752 diff changeset	219 return &m[1], &m[2]
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	220 }
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	221
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	222 // Do executes the actual bottleneck import.
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	223 func (bn *Bottleneck) Do(
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	224 ctx context.Context,
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	225 importID int64,
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	226 conn *sql.Conn,
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	227 feedback Feedback,
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	228 ) (interface{}, error) {
2195 3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	229
2253 cfc523c70e90 Uploaded imports: Small code clean-ups. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2248 diff changeset	230 fetch := func() ([]*ifbn.BottleNeckType, error) {
2195 3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	231 client := ifbn.NewIBottleneckService(bn.URL, bn.Insecure, nil)
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	232
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	233 req := &ifbn.Export_bn_by_isrs{}
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	234
2195 3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	235 resp, err := client.Export_bn_by_isrs(req)
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	236 if err != nil {
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	237 return nil, err
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	238 }
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	239
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	240 if resp.Export_bn_by_isrsResult == nil {
3562 c03224d33e78 Improve error message Tom Gottfried <tom@intevation.de> parents: 3533 diff changeset	241 return nil, errors.New(
c03224d33e78 Improve error message Tom Gottfried <tom@intevation.de> parents: 3533 diff changeset	242 "The requested service returned no bottlenecks")
2195 3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	243 }
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	244
2195 3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	245 return resp.Export_bn_by_isrsResult.BottleNeckType, nil
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	246 }
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	247
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2548 diff changeset	248 return storeBottlenecks(ctx, fetch, importID, conn, feedback, bn.Tolerance)
2195 3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	249 }
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	250
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	251 func storeBottlenecks(
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	252 ctx context.Context,
2253 cfc523c70e90 Uploaded imports: Small code clean-ups. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2248 diff changeset	253 fetch func() ([]*ifbn.BottleNeckType, error),
2195 3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	254 importID int64,
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	255 conn *sql.Conn,
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	256 feedback Feedback,
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2548 diff changeset	257 tolerance float64,
2195 3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	258 ) (interface{}, error) {
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	259 start := time.Now()
3437ceee1529 Uploaded bottleneck import: Implemented in terms of normal bottleneck import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2187 diff changeset	260
2253 cfc523c70e90 Uploaded imports: Small code clean-ups. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2248 diff changeset	261 bns, err := fetch()
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	262 if err != nil {
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	263 return nil, err
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	264 }
24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	265
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	266 feedback.Info("Found %d bottlenecks for import", len(bns))
cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	267
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	268 var hasStmt, insertStmt, fixValidityStmt, updateStmt,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	269 deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt
1580 c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	270
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	271 for _, x := range []struct {
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	272 sql string
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	273 stmt **sql.Stmt
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	274 }{
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	275 {hasBottleneckSQL, &hasStmt},
1658 07f996b0e14a Bottleneck import: Cleaned up source a bit (removed outdated TODOs). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1646 diff changeset	276 {insertBottleneckSQL, &insertStmt},
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	277 {fixBNValiditySQL, &fixValidityStmt},
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	278 {updateBottleneckSQL, &updateStmt},
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	279 {deleteBottleneckMaterialSQL, &deleteMaterialStmt},
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	280 {insertBottleneckMaterialSQL, &insertMaterialStmt},
1580 c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	281 {trackImportSQL, &trackStmt},
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	282 } {
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	283 var err error
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	284 if *x.stmt, err = conn.PrepareContext(ctx, x.sql); err != nil {
1580 c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	285 return nil, err
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	286 }
c503d2fa9776 Bottleneck import: Use a prepared statement for import tracking, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1572 diff changeset	287 defer (*x.stmt).Close()
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	288 }
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	289
1611 1835d1c9eb9b Store bottleneck_id's instead of id's in bottleneck import summary. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1595 diff changeset	290 var nids []string
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	291
2693 02d012272dd4 Log snapping tolerance Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	292 feedback.Info("Tolerance used to snap waterway axis: %g", tolerance)
02d012272dd4 Log snapping tolerance Tom Gottfried <tom@intevation.de> parents: 2563 diff changeset	293
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	294 for _, bn := range bns {
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	295 if err := storeBottleneck(
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2548 diff changeset	296 ctx, importID, conn, feedback, bn, &nids, tolerance,
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	297 hasStmt, insertStmt, fixValidityStmt, updateStmt,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	298 deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil {
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	299 return nil, err
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	300 }
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	301 }
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	302 if len(nids) == 0 {
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	303 return nil, UnchangedError("No new bottlenecks inserted")
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	304 }
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	305
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	306 feedback.Info("Storing %d bottlenecks took %s", len(nids), time.Since(start))
1906 32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1902 diff changeset	307 feedback.Info("Import of bottlenecks was successful")
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	308 summary := struct {
1611 1835d1c9eb9b Store bottleneck_id's instead of id's in bottleneck import summary. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1595 diff changeset	309 Bottlenecks []string `json:"bottlenecks"`
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	310 }{
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1569 diff changeset	311 Bottlenecks: nids,
1569 cf0ca4a9812a Bottleneck import: Implemented backend to store bottlenecks in database. Raimund Renkert <raimund.renkert@intevation.de> parents: 1533 diff changeset	312 }
1906 32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1902 diff changeset	313 return &summary, nil
1531 24445a618513 Added stub for bottleneck importer. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: diff changeset	314 }
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	315
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	316 func storeBottleneck(
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	317 ctx context.Context,
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	318 importID int64,
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	319 conn *sql.Conn,
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	320 feedback Feedback,
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	321 bn *ifbn.BottleNeckType,
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	322 nids *[]string,
2563 dc4fae4bdb8f Expose axis snapping tolerance to users Tom Gottfried <tom@intevation.de> parents: 2548 diff changeset	323 tolerance float64,
3665 29ef6d41e4af Use database triggers to move referencing objects to new versions Tom Gottfried <tom@intevation.de> parents: 3656 diff changeset	324 hasStmt, insertStmt, fixValidityStmt, updateStmt,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	325 deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt,
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	326 ) error {
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	327 feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id)
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	328
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	329 if bn.AdditionalData == nil \|\| bn.AdditionalData.KeyValuePair == nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	330 feedback.Warn("Missing validity information")
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	331 return nil
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	332 }
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	333 const (
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	334 fromKey = "Valid_from_date"
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	335 toKey = "Valid_to_date"
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	336 )
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	337 fromTo := make(map[string]time.Time)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	338 for _, kv := range bn.AdditionalData.KeyValuePair {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	339 k := string(kv.Key)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	340 if k == fromKey \|\| k == toKey {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	341 if t, err := time.Parse(time.RFC3339, kv.Value); err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	342 return err
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	343 } else {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	344 fromTo[k] = t
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	345 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	346 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	347 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	348
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	349 var tfrom, tto pgtype.Timestamptz
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	350 if t, ok := fromTo[fromKey]; ok {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	351 tfrom.Set(t)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	352 } else {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	353 feedback.Warn("Missing start date")
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	354 return nil
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	355 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	356 var uBound pgtype.BoundType
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	357 if t, ok := fromTo[toKey]; ok {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	358 tto.Set(t)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	359 uBound = pgtype.Exclusive
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	360 } else {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	361 uBound = pgtype.Unbounded
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	362 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	363 validity := pgtype.Tstzrange{
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	364 Lower: tfrom,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	365 Upper: tto,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	366 LowerType: pgtype.Inclusive,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	367 UpperType: uBound,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	368 Status: pgtype.Present,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	369 }
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	370
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	371 rb, lb := splitRBLB(bn.Rb_lb)
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	372
3423 6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	373 var revisitingTime *int
3528 b9e331c1e616 Ignore zero length and whitespace-only values Tom Gottfried <tom@intevation.de> parents: 3423 diff changeset	374 if bn.Revisiting_time != nil &&
b9e331c1e616 Ignore zero length and whitespace-only values Tom Gottfried <tom@intevation.de> parents: 3423 diff changeset	375 len(strings.TrimSpace(*bn.Revisiting_time)) > 0 {
3423 6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	376 i, err := strconv.Atoi(*bn.Revisiting_time)
6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	377 if err != nil {
3530 e98ea184538e Improve warning message Tom Gottfried <tom@intevation.de> parents: 3528 diff changeset	378 feedback.Warn(
e98ea184538e Improve warning message Tom Gottfried <tom@intevation.de> parents: 3528 diff changeset	379 "Cannot convert given revisiting time '%s' to number of months",
3423 6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	380 *bn.Revisiting_time)
6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	381 } else {
6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	382 revisitingTime = &i
6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	383 }
6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	384 }
6592396f5061 Make revisiting time of a bottleneck optional Tom Gottfried <tom@intevation.de> parents: 3302 diff changeset	385
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	386 var limiting, country string
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	387
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	388 if bn.Limiting_factor != nil {
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	389 limiting = string(*bn.Limiting_factor)
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	390 }
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	391
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	392 if bn.Responsible_country != nil {
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	393 country = string(*bn.Responsible_country)
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	394 }
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	395
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	396 var materials []string
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	397 if bn.Riverbed != nil {
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	398 for _, material := range bn.Riverbed.Material {
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	399 if material != nil {
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	400 materials = append(materials, string(*material))
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	401 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	402 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	403 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	404
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	405 tx, err := conn.BeginTx(ctx, nil)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	406 if err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	407 return err
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	408 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	409 defer tx.Rollback()
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	410
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	411 var isNew bool
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	412 var nid int64
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	413 err = tx.StmtContext(ctx, hasStmt).QueryRowContext(ctx,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	414 bn.Bottleneck_id,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	415 validity,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	416 ).Scan(&isNew)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	417 switch {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	418 case err != nil:
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	419 feedback.Warn(handleError(err).Error())
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	420 if err2 := tx.Rollback(); err2 != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	421 return err2
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	422 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	423 return nil
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	424 case isNew:
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	425 err = tx.StmtContext(ctx, insertStmt).QueryRowContext(
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	426 ctx,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	427 bn.Bottleneck_id,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	428 &validity,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	429 bn.Fk_g_fid,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	430 bn.OBJNAM,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	431 bn.NOBJNM,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	432 bn.From_ISRS, bn.To_ISRS,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	433 rb,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	434 lb,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	435 country,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	436 revisitingTime,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	437 limiting,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	438 bn.Date_Info,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	439 bn.Source,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	440 tolerance,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	441 ).Scan(&nid)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	442 if err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	443 feedback.Warn(handleError(err).Error())
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	444 return nil
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	445 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	446 feedback.Info("insert new version")
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	447 case !isNew:
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	448 // try to update
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	449 err := tx.StmtContext(ctx, updateStmt).QueryRowContext(ctx,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	450 bn.Bottleneck_id,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	451 bn.Fk_g_fid,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	452 bn.OBJNAM,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	453 bn.NOBJNM,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	454 bn.From_ISRS, bn.To_ISRS,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	455 rb,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	456 lb,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	457 country,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	458 revisitingTime,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	459 limiting,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	460 bn.Date_Info,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	461 bn.Source,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	462 tolerance,
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	463 &validity,
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	464 ).Scan(&nid)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	465 switch {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	466 case err == sql.ErrNoRows:
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	467 feedback.Info("unchanged")
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	468 if err := tx.Rollback(); err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	469 return err
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	470 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	471 return nil
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	472 case err != nil:
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	473 feedback.Warn(handleError(err).Error())
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	474 if err := tx.Rollback(); err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	475 return err
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	476 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	477 return nil
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	478 default:
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	479 feedback.Info("update")
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	480
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	481 // Remove obsolete riverbed materials
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	482 var pgMaterials pgtype.VarcharArray
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	483 pgMaterials.Set(materials)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	484 mtls, err := tx.StmtContext(ctx,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	485 deleteMaterialStmt).QueryContext(ctx,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	486 nid,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	487 &pgMaterials,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	488 )
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	489 if err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	490 return err
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	491 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	492 defer mtls.Close()
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	493 for mtls.Next() {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	494 var delMat string
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	495 if err := mtls.Scan(&delMat); err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	496 return err
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	497 }
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	498 feedback.Warn("Removed riverbed material %s", delMat)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	499 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	500 if err := mtls.Err(); err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	501 return err
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	502 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	503 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	504 }
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	505
3648 0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	506 // 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	507 // in case of overlap
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	508 if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(ctx,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	509 bn.Bottleneck_id,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	510 validity,
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	511 ); err != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	512 feedback.Warn(handleError(err).Error())
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	513 if err2 := tx.Rollback(); err2 != nil {
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	514 return err2
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	515 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	516 return nil
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	517 }
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	518
0ec5c8ec1e44 Avoid empty validity time ranges Tom Gottfried <tom@intevation.de> parents: 3645 diff changeset	519 // Insert riverbed materials
3645 02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	520 if materials != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	521 for _, mat := range materials {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	522 if _, err := tx.StmtContext(ctx,
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	523 insertMaterialStmt).ExecContext(
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	524 ctx, nid, mat); err != nil {
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	525 feedback.Warn("Failed to insert riverbed material '%s'", mat)
02951a62e8c6 'Historicise' bottlenecks on import Tom Gottfried <tom@intevation.de> parents: 3566 diff changeset	526 feedback.Warn(handleError(err).Error())
2999 b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	527 }
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	528 }
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	529 }
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2765 diff changeset	530
3164 1de0764f6af4 Use context for preparation of transaction-specific statements Tom Gottfried <tom@intevation.de> parents: 2999 diff changeset	531 if _, err := tx.StmtContext(ctx, trackStmt).ExecContext(
2532 452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	532 ctx, importID, "waterway.bottlenecks", nid,
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	533 ); err != nil {
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	534 return err
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	535 }
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	536 if err = tx.Commit(); err != nil {
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	537 return err
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	538 }
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	539 nids = append(nids, bn.Bottleneck_id)
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	540 return nil
452bc714bfd9 Continue importing bottlenecks if one item fails Tom Gottfried <tom@intevation.de> parents: 2253 diff changeset	541 }

Mercurial > gemma

annotate pkg/imports/bn.go @ 3665:29ef6d41e4af