# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1560936047 -7200
# Node ID 063a1883b5cb2d9310a2e94e570dbfee74a399cf
# Parent  9ef98342ffe78e19c80ef5d3af681e2939049c71
Detect and handle unchanged BN during import.

diff -r 9ef98342ffe7 -r 063a1883b5cb pkg/imports/bn.go
--- a/pkg/imports/bn.go	Wed Jun 19 11:18:26 2019 +0200
+++ b/pkg/imports/bn.go	Wed Jun 19 11:20:47 2019 +0200
@@ -104,6 +104,56 @@
 RETURNING id
 `
 
+	findExactMatchBottleneckSQL = `
+WITH
+bounds (b) AS (VALUES (isrs_fromText($6)), (isrs_fromText($7))),
+r AS (SELECT isrsrange(
+    (SELECT b FROM bounds ORDER BY b USING <~ FETCH FIRST ROW ONLY),
+    (SELECT b FROM bounds ORDER BY b USING >~ FETCH FIRST ROW ONLY)) AS r)
+SELECT id FROM waterway.bottlenecks
+WHERE (
+  bottleneck_id,
+  validity,
+  gauge_location,
+  gauge_validity,
+  objnam,
+  nobjnm,
+  stretch,
+  area,
+  rb,
+  lb,
+  responsible_country,
+  revisiting_time,
+  limiting,
+  date_info,
+  source_organization,
+  staging_done
+) = ( SELECT
+  $1,
+  validity * $2, -- intersections with gauge validity ranges
+  location,
+  validity,
+  $4,
+  $5,
+  (SELECT r FROM r),
+  ISRSrange_area(
+    ISRSrange_axis((SELECT r FROM r),
+                   $15),
+    (SELECT ST_Collect(CAST(area AS geometry))
+        FROM waterway.waterway_area)),
+  $8,
+  $9,
+  $10,
+  $11::smallint,
+  $12,
+  $13::timestamptz,
+  $14,
+  true
+  FROM waterway.gauges
+  WHERE location = isrs_fromText($3) AND validity && $2
+)
+`
+
 	// Alignment with gauge validity might have generated new entries
 	// for the same time range. Thus, remove the old ones
 	deleteObsoleteBNSQL = `
@@ -236,14 +286,16 @@
 
 	feedback.Info("Found %d bottlenecks for import", len(bns))
 
-	var insertStmt, deleteObsoleteBNStmt, fixValidityStmt,
-		deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt
+	var insertStmt, findExactMatchingBNStmt, deleteObsoleteBNStmt,
+		fixValidityStmt, deleteMaterialStmt, insertMaterialStmt,
+		trackStmt *sql.Stmt
 
 	for _, x := range []struct {
 		sql  string
 		stmt **sql.Stmt
 	}{
 		{insertBottleneckSQL, &insertStmt},
+		{findExactMatchBottleneckSQL, &findExactMatchingBNStmt},
 		{deleteObsoleteBNSQL, &deleteObsoleteBNStmt},
 		{fixBNValiditySQL, &fixValidityStmt},
 		{deleteBottleneckMaterialSQL, &deleteMaterialStmt},
@@ -264,8 +316,9 @@
 	for _, bn := range bns {
 		if err := storeBottleneck(
 			ctx, importID, conn, feedback, bn, &nids, tolerance,
-			insertStmt, deleteObsoleteBNStmt, fixValidityStmt,
-			deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil {
+			insertStmt, findExactMatchingBNStmt, deleteObsoleteBNStmt,
+			fixValidityStmt, deleteMaterialStmt, insertMaterialStmt,
+			trackStmt); err != nil {
 			return nil, err
 		}
 	}
@@ -291,8 +344,9 @@
 	bn *ifbn.BottleNeckType,
 	nids *[]string,
 	tolerance float64,
-	insertStmt, deleteObsoleteBNStmt, fixValidityStmt,
-	deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt,
+	insertStmt, findExactMatchingBNStmt, deleteObsoleteBNStmt,
+	fixValidityStmt, deleteMaterialStmt, insertMaterialStmt,
+	trackStmt *sql.Stmt,
 ) error {
 	feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id)
 
@@ -389,6 +443,34 @@
 		}
 	}
 
+	// Check if an bottleneck identical to the one we would insert already
+	// exists:
+	bns, err := findExactMatchingBNStmt.QueryContext(ctx,
+		bn.Bottleneck_id,
+		&validity,
+		bn.Fk_g_fid,
+		bn.OBJNAM,
+		bn.NOBJNM,
+		bn.From_ISRS, bn.To_ISRS,
+		rb,
+		lb,
+		country,
+		revisitingTime,
+		limiting,
+		bn.Date_Info,
+		bn.Source,
+		tolerance,
+	)
+
+	if err != nil {
+		return err
+	}
+	defer bns.Close()
+	if bns.Next() {
+		feedback.Info("unchanged")
+		return nil
+	}
+
 	tx, err := conn.BeginTx(ctx, nil)
 	if err != nil {
 		return err
@@ -396,7 +478,7 @@
 	defer tx.Rollback()
 
 	var bnIds []int64
-	bns, err := tx.StmtContext(ctx, insertStmt).QueryContext(ctx,
+	bns, err = tx.StmtContext(ctx, insertStmt).QueryContext(ctx,
 		bn.Bottleneck_id,
 		&validity,
 		bn.Fk_g_fid,