Mercurial > gemma

changeset 3645:02951a62e8c6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
'Historicise' bottlenecks on import
author Tom Gottfried <tom@intevation.de>
date Wed, 12 Jun 2019 17:11:15 +0200
parents 9e91b416d5bb
children 123b9341408e
files pkg/controllers/bottlenecks.go pkg/controllers/search.go pkg/controllers/stretches.go pkg/controllers/surveys.go pkg/imports/bn.go pkg/imports/fa.go pkg/imports/sr.go pkg/imports/wg.go pkg/models/sr.go schema/auth_tests.sql schema/gemma.sql schema/geoserver_views.sql schema/tap_tests_data.sql
diffstat 13 files changed, 330 insertions(+), 106 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/controllers/bottlenecks.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/controllers/bottlenecks.go	Wed Jun 12 17:11:15 2019 +0200
@@ -33,7 +33,7 @@
 
 const (
 	selectLimitingSQL = `
-SELECT limiting from waterway.bottlenecks WHERE objnam = $1`
+SELECT limiting from waterway.bottlenecks WHERE NOT erased AND objnam = $1`
 
 	selectAvailableDepthSQL = `
 WITH data AS (
@@ -48,6 +48,7 @@
   JOIN waterway.bottlenecks bn
     ON fa.bottleneck_id = bn.id
   WHERE
+    NOT bn.erased AND
     bn.objnam = $1 AND
     efa.level_of_service = $2 AND
     efa.measure_type = 'Measured' AND
@@ -74,20 +75,6 @@
 ORDER BY measure_date
 `
 
-	selectGaugeLevelsSQL = `
-SELECT
-  grwl.depth_reference,
-  grwl.value
-FROM waterway.gauges_reference_water_levels grwl
-  JOIN waterway.bottlenecks bns
-    ON grwl.location = bns.gauge_location
-      AND grwl.validity = bns.gauge_validity
-WHERE bns.objnam = $1 AND (
-  grwl.depth_reference like 'HDC%' OR
-  grwl.depth_reference like 'LDC%' OR
-  grwl.depth_reference like 'MW%'
-)
-`
 	selectGaugeLDCSQL = `
 SELECT
   grwl.value
@@ -95,7 +82,7 @@
   JOIN waterway.bottlenecks bns
     ON grwl.location = bns.gauge_location
       AND grwl.validity = bns.gauge_validity
-WHERE bns.objnam = $1 AND grwl.depth_reference like 'LDC%'
+WHERE NOT bns.erased AND bns.objnam = $1 AND grwl.depth_reference like 'LDC%'
 `
 )
 
--- a/pkg/controllers/search.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/controllers/search.go	Wed Jun 12 17:11:15 2019 +0200
@@ -42,6 +42,7 @@
     ST_AsGeoJSON(ST_Centroid(area))::json AS geom,
     'bottleneck' AS type
   FROM waterway.bottlenecks
+  WHERE NOT erased
 ORDER BY objnam) r
 `
 )
--- a/pkg/controllers/stretches.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/controllers/stretches.go	Wed Jun 12 17:11:15 2019 +0200
@@ -35,14 +35,14 @@
   distinct(b.objnam),
   b.limiting
 FROM waterway.sections s, waterway.bottlenecks b
-WHERE ST_Intersects(b.area, s.area) AND s.name = $1`
+WHERE NOT b.erased AND ST_Intersects(b.area, s.area) AND s.name = $1`
 
 	selectStretchBottlenecks = `
 SELECT
   distinct(b.objnam),
   b.limiting
 FROM waterway.stretches s, waterway.bottlenecks b
-WHERE ST_Intersects(b.area, s.area) AND s.name = $1`
+WHERE NOT b.erased AND ST_Intersects(b.area, s.area) AND s.name = $1`
 )
 
 type (
--- a/pkg/controllers/surveys.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/controllers/surveys.go	Wed Jun 12 17:11:15 2019 +0200
@@ -36,7 +36,8 @@
     ON b.gauge_location = g.location AND b.gauge_validity = g.validity
   JOIN waterway.sounding_results AS s ON b.id = s.bottleneck_id
   LEFT JOIN waterway.gauges_reference_water_levels AS r
-    USING (depth_reference, location, validity)
+    ON s.depth_reference = r.depth_reference
+      AND g.location = r.location AND g.validity = r.validity
 WHERE b.objnam = $1`
 )
 
--- a/pkg/imports/bn.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/imports/bn.go	Wed Jun 12 17:11:15 2019 +0200
@@ -24,6 +24,7 @@
 	"time"
 
 	"gemma.intevation.de/gemma/pkg/soap/ifbn"
+	"github.com/jackc/pgx/pgtype"
 )
 
 // Bottleneck is an import job to import
@@ -43,16 +44,29 @@
 
 const (
 	hasBottleneckSQL = `
-SELECT true FROM waterway.bottlenecks WHERE bottleneck_id = $1`
+WITH upd AS (
+  UPDATE waterway.bottlenecks SET
+    erased = true
+  WHERE bottleneck_id = $1
+    AND NOT erased
+    -- Don't touch old entry if validity did not change: will be updated
+    AND validity <> $2
+  RETURNING 1
+)
+-- Decide whether a new version will be INSERTed
+SELECT EXISTS(SELECT 1 FROM upd)
+  OR NOT EXISTS(SELECT 1 FROM waterway.bottlenecks WHERE bottleneck_id = $1)
+`
 
 	insertBottleneckSQL = `
 WITH
-bounds (b) AS (VALUES (isrs_fromText($5)), (isrs_fromText($6))),
+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)
 INSERT INTO waterway.bottlenecks (
   bottleneck_id,
+  validity,
   gauge_location,
   gauge_validity,
   objnam,
@@ -68,29 +82,80 @@
   source_organization
 ) VALUES (
   $1,
-  isrs_fromText($2),
+  $2,
+  isrs_fromText($3),
   COALESCE(
     (SELECT validity FROM waterway.gauges
-       WHERE location = isrs_fromText($2) AND NOT erased),
+       WHERE location = isrs_fromText($3)
+         AND validity @> lower(CAST($2 AS tstzrange))),
     tstzrange(NULL, NULL)),
-  $3,
   $4,
+  $5,
   (SELECT r FROM r),
   ISRSrange_area(
     ISRSrange_axis((SELECT r FROM r),
-                   $14),
+                   $15),
     (SELECT ST_Collect(CAST(area AS geometry))
         FROM waterway.waterway_area)),
-  $7,
   $8,
   $9,
   $10,
   $11,
   $12,
-  $13
+  $13,
+  $14
 )
 RETURNING id`
 
+	fixBNValiditySQL = `
+UPDATE waterway.bottlenecks SET
+   -- Set enddate of old entry to new startdate in case of overlap:
+  validity = validity - $2
+WHERE bottleneck_id = $1
+  AND validity && $2
+  AND erased
+`
+
+	updateBottleneckSQL = `
+WITH
+bounds (b) AS (VALUES (isrs_fromText($5)), (isrs_fromText($6))),
+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)
+UPDATE waterway.bottlenecks b SET
+  gauge_location = isrs_fromtext($2),
+  gauge_validity = COALESCE(
+    (SELECT validity FROM waterway.gauges g
+       WHERE g.location = isrs_fromText($2)
+         AND g.validity @> lower(b.validity)),
+    tstzrange(NULL, NULL)),
+  objnam = $3,
+  nobjnm = $4,
+  stretch = (SELECT r FROM r),
+  area = ISRSrange_area(
+    ISRSrange_axis((SELECT r FROM r), $14),
+    (SELECT ST_Collect(CAST(area AS geometry))
+        FROM waterway.waterway_area)),
+  rb = $7,
+  lb = $8,
+  responsible_country = $9,
+  revisiting_time = $10,
+  limiting = $11,
+  date_info = $12,
+  source_organization = $13
+WHERE bottleneck_id = $1
+  AND NOT erased
+  AND $12 > date_info
+RETURNING id
+`
+
+	deleteBottleneckMaterialSQL = `
+DELETE FROM waterway.bottlenecks_riverbed_materials
+WHERE bottleneck_id = $1
+  AND riverbed <> ALL($2)
+RETURNING riverbed
+`
+
 	insertBottleneckMaterialSQL = `
 INSERT INTO waterway.bottlenecks_riverbed_materials (
    bottleneck_id,
@@ -98,7 +163,8 @@
 ) VALUES (
    $1,
    $2
-)`
+) ON CONFLICT (bottleneck_id, riverbed) DO NOTHING
+`
 )
 
 type bnJobCreator struct{}
@@ -198,7 +264,8 @@
 
 	feedback.Info("Found %d bottlenecks for import", len(bns))
 
-	var hasStmt, insertStmt, insertMaterialStmt, trackStmt *sql.Stmt
+	var hasStmt, insertStmt, fixValidityStmt, updateStmt,
+		deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt
 
 	for _, x := range []struct {
 		sql  string
@@ -206,6 +273,9 @@
 	}{
 		{hasBottleneckSQL, &hasStmt},
 		{insertBottleneckSQL, &insertStmt},
+		{fixBNValiditySQL, &fixValidityStmt},
+		{updateBottleneckSQL, &updateStmt},
+		{deleteBottleneckMaterialSQL, &deleteMaterialStmt},
 		{insertBottleneckMaterialSQL, &insertMaterialStmt},
 		{trackImportSQL, &trackStmt},
 	} {
@@ -223,7 +293,8 @@
 	for _, bn := range bns {
 		if err := storeBottleneck(
 			ctx, importID, conn, feedback, bn, &nids, tolerance,
-			hasStmt, insertStmt, insertMaterialStmt, trackStmt); err != nil {
+			hasStmt, insertStmt, fixValidityStmt, updateStmt,
+			deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil {
 			return nil, err
 		}
 	}
@@ -249,28 +320,52 @@
 	bn *ifbn.BottleNeckType,
 	nids *[]string,
 	tolerance float64,
-	hasStmt, insertStmt, insertMaterialStmt, trackStmt *sql.Stmt,
+	hasStmt, insertStmt, fixValidityStmt, updateStmt,
+	deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt,
 ) error {
-
-	tx, err := conn.BeginTx(ctx, nil)
-	if err != nil {
-		return err
-	}
-	defer tx.Rollback()
+	feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id)
 
-	var found bool
-	err = tx.StmtContext(ctx, hasStmt).QueryRowContext(ctx,
-		bn.Bottleneck_id).Scan(&found)
-	switch {
-	case err == sql.ErrNoRows:
-		// This is good.
-	case err != nil:
-		return err
-	case found:
-		feedback.Info("'%s' already in database. Skip", bn.OBJNAM)
-		// TODO: Deep comparison database vs. SOAP.
+	if bn.AdditionalData == nil || bn.AdditionalData.KeyValuePair == nil {
+		feedback.Warn("Missing validity information")
 		return nil
 	}
+	const (
+		fromKey = "Valid_from_date"
+		toKey   = "Valid_to_date"
+	)
+	fromTo := make(map[string]time.Time)
+	for _, kv := range bn.AdditionalData.KeyValuePair {
+		k := string(kv.Key)
+		if k == fromKey || k == toKey {
+			if t, err := time.Parse(time.RFC3339, kv.Value); err != nil {
+				return err
+			} else {
+				fromTo[k] = t
+			}
+		}
+	}
+
+	var tfrom, tto pgtype.Timestamptz
+	if t, ok := fromTo[fromKey]; ok {
+		tfrom.Set(t)
+	} else {
+		feedback.Warn("Missing start date")
+		return nil
+	}
+	var uBound pgtype.BoundType
+	if t, ok := fromTo[toKey]; ok {
+		tto.Set(t)
+		uBound = pgtype.Exclusive
+	} else {
+		uBound = pgtype.Unbounded
+	}
+	validity := pgtype.Tstzrange{
+		Lower:     tfrom,
+		Upper:     tto,
+		LowerType: pgtype.Inclusive,
+		UpperType: uBound,
+		Status:    pgtype.Present,
+	}
 
 	rb, lb := splitRBLB(bn.Rb_lb)
 
@@ -297,42 +392,135 @@
 		country = string(*bn.Responsible_country)
 	}
 
-	var nid int64
-
-	err = tx.StmtContext(ctx, insertStmt).QueryRowContext(
-		ctx,
-		bn.Bottleneck_id,
-		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,
-	).Scan(&nid)
-	if err != nil {
-		feedback.Warn("Failed to insert '%s' into database", bn.OBJNAM)
-		feedback.Warn(handleError(err).Error())
-		return nil
-	}
-
+	var materials []string
 	if bn.Riverbed != nil {
 		for _, material := range bn.Riverbed.Material {
 			if material != nil {
-				mat := string(*material)
-				if _, err := tx.StmtContext(ctx,
-					insertMaterialStmt).ExecContext(
-					ctx, nid, material); err != nil {
-					feedback.Warn(
-						"Failed to insert riverbed material '%s' for bottleneck '%s'.",
-						mat, bn.OBJNAM)
-					feedback.Warn(handleError(err).Error())
+				materials = append(materials, string(*material))
+			}
+		}
+	}
+
+	tx, err := conn.BeginTx(ctx, nil)
+	if err != nil {
+		return err
+	}
+	defer tx.Rollback()
+
+	var isNew bool
+	var nid int64
+	err = tx.StmtContext(ctx, hasStmt).QueryRowContext(ctx,
+		bn.Bottleneck_id,
+		validity,
+	).Scan(&isNew)
+	switch {
+	case err != nil:
+		feedback.Warn(handleError(err).Error())
+		if err2 := tx.Rollback(); err2 != nil {
+			return err2
+		}
+		return nil
+	case isNew:
+		err = tx.StmtContext(ctx, insertStmt).QueryRowContext(
+			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,
+		).Scan(&nid)
+		if err != nil {
+			feedback.Warn(handleError(err).Error())
+			return nil
+		}
+
+		// Set end of validity of old version to start of new version
+		// in case of overlap
+		if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(ctx,
+			bn.Bottleneck_id,
+			validity,
+		); err != nil {
+			feedback.Warn(handleError(err).Error())
+			if err2 := tx.Rollback(); err2 != nil {
+				return err2
+			}
+			return nil
+		}
+		feedback.Info("insert new version")
+	case !isNew:
+		// try to update
+		err := tx.StmtContext(ctx, updateStmt).QueryRowContext(ctx,
+			bn.Bottleneck_id,
+			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,
+		).Scan(&nid)
+		switch {
+		case err == sql.ErrNoRows:
+			feedback.Info("unchanged")
+			if err := tx.Rollback(); err != nil {
+				return err
+			}
+			return nil
+		case err != nil:
+			feedback.Warn(handleError(err).Error())
+			if err := tx.Rollback(); err != nil {
+				return err
+			}
+			return nil
+		default:
+			feedback.Info("update")
+
+			// Remove obsolete riverbed materials
+			var pgMaterials pgtype.VarcharArray
+			pgMaterials.Set(materials)
+			mtls, err := tx.StmtContext(ctx,
+				deleteMaterialStmt).QueryContext(ctx,
+				nid,
+				&pgMaterials,
+			)
+			if err != nil {
+				return err
+			}
+			defer mtls.Close()
+			for mtls.Next() {
+				var delMat string
+				if err := mtls.Scan(&delMat); err != nil {
+					return err
 				}
+				feedback.Warn("Removed riverbed material %s", delMat)
+			}
+			if err := mtls.Err(); err != nil {
+				return err
+			}
+		}
+	}
+
+	if materials != nil {
+		for _, mat := range materials {
+			if _, err := tx.StmtContext(ctx,
+				insertMaterialStmt).ExecContext(
+				ctx, nid, mat); err != nil {
+				feedback.Warn("Failed to insert riverbed material '%s'", mat)
+				feedback.Warn(handleError(err).Error())
 			}
 		}
 	}
@@ -345,7 +533,6 @@
 	if err = tx.Commit(); err != nil {
 		return err
 	}
-	feedback.Info("Inserted '%s' into database", bn.OBJNAM)
 	*nids = append(*nids, bn.Bottleneck_id)
 	return nil
 }
--- a/pkg/imports/fa.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/imports/fa.go	Wed Jun 12 17:11:15 2019 +0200
@@ -47,7 +47,7 @@
 
 const (
 	listBottlenecksSQL = `
-SELECT
+SELECT DISTINCT
   bottleneck_id
 FROM waterway.bottlenecks
 WHERE responsible_country = users.current_user_country()
@@ -79,7 +79,8 @@
   source_organization
 ) VALUES (
   $1,
-  (SELECT id FROM waterway.bottlenecks WHERE bottleneck_id = $2),
+  (SELECT id FROM waterway.bottlenecks
+     WHERE NOT erased AND bottleneck_id = $2),
   $3,
   $4,
   $5,
--- a/pkg/imports/sr.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/imports/sr.go	Wed Jun 12 17:11:15 2019 +0200
@@ -32,6 +32,7 @@
 	"strings"
 	"time"
 
+	"github.com/jackc/pgx"
 	shp "github.com/jonas-p/go-shp"
 
 	"gemma.intevation.de/gemma/pkg/common"
@@ -113,7 +114,8 @@
   depth_reference,
   area
 ) SELECT
-  (SELECT id from waterway.bottlenecks where objnam = $1),
+  (SELECT id FROM waterway.bottlenecks WHERE objnam = $1
+     AND validity @> CAST($2 AS timestamptz)),
   $2::date,
   $3,
   (SELECT
@@ -172,7 +174,9 @@
   JOIN waterway.bottlenecks bns
     ON grwl.location = bns.gauge_location
       AND grwl.validity = bns.gauge_validity
-WHERE bns.objnam = $1 AND grwl.depth_reference like 'LDC%'
+WHERE bns.objnam = $1
+  AND bns.validity @> CAST($2 AS timestamptz)
+  AND grwl.depth_reference like 'LDC%'
 `
 
 	reprojectPointsSingleBeamSQL = `
@@ -226,7 +230,11 @@
 		feedback.Info("Found ZPG as reference system -> translating Z values to LDC")
 		var ldc float64
 		var depthReference string
-		err := conn.QueryRowContext(ctx, selectGaugeLDCSQL, m.Bottleneck).Scan(
+		err := conn.QueryRowContext(ctx,
+			selectGaugeLDCSQL,
+			m.Bottleneck,
+			m.Date.Time,
+		).Scan(
 			&ldc,
 			&depthReference,
 		)
@@ -412,6 +420,14 @@
 	xyz, boundary = nil, nil // not need from now on.
 	feedback.Info("Calculating hull took %s.", time.Since(start))
 	if err != nil {
+		if e, isPgErr := err.(pgx.PgError); isPgErr &&
+			e.Code == notNullViolation &&
+			e.SchemaName == "waterway" &&
+			e.TableName == "sounding_results" &&
+			e.ColumnName == "bottleneck_id" {
+			return nil, fmt.Errorf(
+				"No bottleneck matching given name and time available")
+		}
 		return nil, err
 	}
 	feedback.Info("Best suited UTM EPSG: %d", epsg)
--- a/pkg/imports/wg.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/imports/wg.go	Wed Jun 12 17:11:15 2019 +0200
@@ -124,6 +124,14 @@
   AND measure_date <@ CAST($2 AS tstzrange)
 `
 
+	moveBNSQL = `
+UPDATE waterway.bottlenecks
+-- Associate bottlenecks to matching gauge version
+SET gauge_validity = $2
+WHERE isrs_astext(gauge_location) = $1
+  AND lower(validity) <@ CAST($2 AS tstzrange)
+`
+
 	fixValiditySQL = `
 UPDATE waterway.gauges SET
    -- Set enddate of old entry to new startdate in case of overlap:
@@ -199,7 +207,8 @@
 		return nil, err
 	}
 
-	var eraseGaugeStmt, insertStmt, moveGMStmt, fixValidityStmt, updateStmt,
+	var eraseGaugeStmt, insertStmt, moveGMStmt, moveBNStmt,
+		fixValidityStmt, updateStmt,
 		deleteReferenceWaterLevelsStmt,
 		isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt
 	for _, x := range []struct {
@@ -209,6 +218,7 @@
 		{eraseGaugeSQL, &eraseGaugeStmt},
 		{insertGaugeSQL, &insertStmt},
 		{moveGMSQL, &moveGMStmt},
+		{moveBNSQL, &moveBNStmt},
 		{fixValiditySQL, &fixValidityStmt},
 		{updateGaugeSQL, &updateStmt},
 		{deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt},
@@ -365,8 +375,8 @@
 					unchanged++
 					continue
 				}
-				// Move gauge measurements to new matching gauge version,
-				// if applicable
+				// Move gauge measurements and bottlenecks to new matching
+				// gauge version, if applicable
 				if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx,
 					code.String(),
 					&validity,
@@ -378,6 +388,17 @@
 					unchanged++
 					continue
 				}
+				if _, err = tx.StmtContext(ctx, moveBNStmt).ExecContext(ctx,
+					code.String(),
+					&validity,
+				); err != nil {
+					feedback.Warn(handleError(err).Error())
+					if err2 := tx.Rollback(); err2 != nil {
+						return nil, err2
+					}
+					unchanged++
+					continue
+				}
 				// Set end of validity of old version to start of new version
 				// in case of overlap
 				if _, err = tx.StmtContext(ctx, fixValidityStmt).ExecContext(
--- a/pkg/models/sr.go	Wed Jun 12 17:10:49 2019 +0200
+++ b/pkg/models/sr.go	Wed Jun 12 17:11:15 2019 +0200
@@ -41,7 +41,8 @@
   FROM waterway.bottlenecks bn
     JOIN waterway.gauges g
       ON bn.gauge_location = g.location AND bn.gauge_validity = g.validity
-    JOIN waterway.gauges_reference_water_levels rl USING (location, validity)
+    JOIN waterway.gauges_reference_water_levels rl
+      ON g.location = rl.location AND g.validity = rl.validity
   WHERE bn.objnam = $1
     AND rl.depth_reference = $2)`
 
--- a/schema/auth_tests.sql	Wed Jun 12 17:10:49 2019 +0200
+++ b/schema/auth_tests.sql	Wed Jun 12 17:11:15 2019 +0200
@@ -75,16 +75,16 @@
 
 PREPARE bn_insert (varchar, geometry(MULTIPOLYGON, 4326)) AS
     INSERT INTO waterway.bottlenecks (
-        gauge_location, gauge_validity,
+        gauge_location, gauge_validity, validity,
         bottleneck_id, stretch, area, rb, lb, responsible_country,
-        revisiting_time, limiting, source_organization)
+        revisiting_time, limiting, date_info, source_organization)
         SELECT
-            location, validity,
+            location, validity, validity,
             $1,
             isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
                 ('AT', 'XXX', '00001', '00000', 2)::isrs),
             $2, 'AT', 'AT', 'AT',
-            1, 'depth', 'testorganization'
+            1, 'depth', current_timestamp, 'testorganization'
         FROM waterway.gauges
         WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs;
 SELECT lives_ok($$
--- a/schema/gemma.sql	Wed Jun 12 17:10:49 2019 +0200
+++ b/schema/gemma.sql	Wed Jun 12 17:11:15 2019 +0200
@@ -488,9 +488,13 @@
     --
     CREATE TABLE bottlenecks (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-        bottleneck_id varchar UNIQUE NOT NULL,
+        bottleneck_id varchar NOT NULL,
+        validity tstzrange NOT NULL,
+        EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
+            DEFERRABLE INITIALLY DEFERRED,
         gauge_location isrs NOT NULL,
         gauge_validity tstzrange NOT NULL,
+        CHECK(lower(validity) <@ gauge_validity),
         CONSTRAINT gauge_key
             FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
                 ON UPDATE CASCADE,
@@ -512,13 +516,16 @@
         -- different model approach?
         -- depth_reference char(3) NOT NULL REFERENCES depth_references,
         -- XXX: Also an attribut of sounding result?
-        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL,
-        -- additional_data xml -- Currently not relevant for GEMMA
+        erased boolean NOT NULL DEFAULT false,
+        CHECK (erased OR NOT isempty(validity)),
         staging_done boolean NOT NULL DEFAULT false
     )
-    CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
-        FOR EACH ROW EXECUTE PROCEDURE update_date_info()
+    -- Allow only one non-erased entry per bottleneck
+    CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint
+        ON bottlenecks (bottleneck_id)
+        WHERE NOT erased
 
     CREATE TABLE bottlenecks_riverbed_materials (
         bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
@@ -563,7 +570,7 @@
         UNIQUE (bottleneck_id, surdat),
         -- additional_data xml -- Currently not relevant for GEMMA
         critical boolean,
-        date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+        date_info timestamp with time zone NOT NULL CURRENT_TIMESTAMP,
         source_organization varchar NOT NULL
     )
     CREATE TRIGGER fairway_availability_date_info
--- a/schema/geoserver_views.sql	Wed Jun 12 17:10:49 2019 +0200
+++ b/schema/geoserver_views.sql	Wed Jun 12 17:11:15 2019 +0200
@@ -135,7 +135,8 @@
         LEFT JOIN fairway_availability_latest fal
             ON b.id = fal.bottleneck_id
         LEFT JOIN sounding_result_latest srl
-            ON b.id = srl.bottleneck_id;
+            ON b.id = srl.bottleneck_id
+    WHERE NOT b.erased;
 
 CREATE OR REPLACE VIEW waterway.stretches_geoserver AS
     SELECT
@@ -200,6 +201,7 @@
         SELECT bottleneck_id, max(date_info) AS current
             FROM waterway.sounding_results
             GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
+    WHERE NOT bn.erased
     ORDER BY objnam;
 
 CREATE OR REPLACE VIEW waterway.sounding_differences AS
--- a/schema/tap_tests_data.sql	Wed Jun 12 17:10:49 2019 +0200
+++ b/schema/tap_tests_data.sql	Wed Jun 12 17:11:15 2019 +0200
@@ -57,7 +57,7 @@
         current_timestamp,
         'testorganization',
         current_timestamp)
-    RETURNING location, validity),
+    RETURNING location, validity, validity),
 bns AS (
     VALUES (
         'testbottleneck1',
@@ -65,19 +65,19 @@
             ('AT', 'XXX', '00001', '00000', 2)::isrs),
         ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
         'AT', 'AT', 'AT',
-        1, 'depth', 'testorganization', false
+        1, 'depth', current_timestamp, 'testorganization', false
     ), (
         'testbottleneck2',
         isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs,
             ('AT', 'XXX', '00001', '00000', 2)::isrs),
         ST_geomfromtext('MULTIPOLYGON(((0 0, 0 1, 1 1, 1 0, 0 0)))', 4326),
         'AT', 'AT', 'AT',
-        1, 'depth', 'testorganization', true
+        1, 'depth', current_timestamp, 'testorganization', true
     ))
 INSERT INTO waterway.bottlenecks (
-    gauge_location, gauge_validity,
+    gauge_location, gauge_validity, validity,
     bottleneck_id, stretch, area, rb, lb, responsible_country,
-    revisiting_time, limiting, source_organization, staging_done)
+    revisiting_time, limiting, date_info, source_organization, staging_done)
     SELECT * FROM gs, bns;
 
 INSERT INTO waterway.distance_marks_virtual VALUES (