Mercurial > gemma

changeset 3648:0ec5c8ec1e44

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid empty validity time ranges An entry which is not valid at any point in time makes no sense. Further, multiple of such entries would violate a UNIQUE constraint. Since an UPDATE now can change validity time ranges, do all the adjustments for that regardles of whether an INSERT or UPDATE happens.
author Tom Gottfried <tom@intevation.de>
date Wed, 12 Jun 2019 18:26:26 +0200
parents 123b9341408e
children fb8a53c7c6d3
files pkg/imports/bn.go pkg/imports/wg.go schema/gemma.sql
diffstat 3 files changed, 66 insertions(+), 61 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/bn.go	Wed Jun 12 18:21:14 2019 +0200
+++ b/pkg/imports/bn.go	Wed Jun 12 18:26:26 2019 +0200
@@ -49,8 +49,8 @@
     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
+    -- Don't touch old entry if new validity contains old: will be updated
+    AND NOT validity <@ $2
   RETURNING 1
 )
 -- Decide whether a new version will be INSERTed
@@ -142,7 +142,8 @@
   revisiting_time = $10,
   limiting = $11,
   date_info = $12,
-  source_organization = $13
+  source_organization = $13,
+  validity = $15
 WHERE bottleneck_id = $1
   AND NOT erased
   AND $12 > date_info
@@ -442,19 +443,6 @@
 			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
@@ -472,6 +460,7 @@
 			bn.Date_Info,
 			bn.Source,
 			tolerance,
+			&validity,
 		).Scan(&nid)
 		switch {
 		case err == sql.ErrNoRows:
@@ -514,6 +503,20 @@
 		}
 	}
 
+	// 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
+	}
+
+	// Insert riverbed materials
 	if materials != nil {
 		for _, mat := range materials {
 			if _, err := tx.StmtContext(ctx,
--- a/pkg/imports/wg.go	Wed Jun 12 18:21:14 2019 +0200
+++ b/pkg/imports/wg.go	Wed Jun 12 18:26:26 2019 +0200
@@ -79,8 +79,8 @@
     erased = true
   WHERE isrs_astext(location) = $1
     AND NOT erased
-    -- Don't touch old entry if validity did not change: will be updated
-    AND validity <> $2
+    -- Don't touch old entry if new validity contains old: will be updated
+    AND NOT validity <@ $2
   RETURNING 1
 )
 -- Decide whether a new version will be INSERTed
@@ -151,7 +151,8 @@
   geodref = $12,
   date_info = $13,
   source_organization = $14,
-  lastupdate = $15
+  lastupdate = $15,
+  validity = $16
 WHERE location = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int)
   AND NOT erased
   AND $15 > lastupdate
@@ -375,44 +376,6 @@
 					unchanged++
 					continue
 				}
-				// Move gauge measurements and bottlenecks to new matching
-				// gauge version, if applicable
-				if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx,
-					code.String(),
-					&validity,
-				); err != nil {
-					feedback.Warn(handleError(err).Error())
-					if err2 := tx.Rollback(); err2 != nil {
-						return nil, err2
-					}
-					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(
-					ctx,
-					code.String(),
-					&validity,
-				); err != nil {
-					feedback.Warn(handleError(err).Error())
-					if err2 := tx.Rollback(); err2 != nil {
-						return nil, err2
-					}
-					unchanged++
-					continue
-				}
 				feedback.Info("insert new version")
 			case !isNew:
 				// try to update
@@ -432,6 +395,7 @@
 					&dateInfo,
 					source,
 					time.Time(*dr.Lastupdate),
+					&validity,
 				).Scan(&dummy)
 				switch {
 				case err2 == sql.ErrNoRows:
@@ -482,6 +446,46 @@
 				}
 			}
 
+			// Move gauge measurements and bottlenecks to new matching
+			// gauge version, if applicable
+			if _, err = tx.StmtContext(ctx, moveGMStmt).ExecContext(ctx,
+				code.String(),
+				&validity,
+			); err != nil {
+				feedback.Warn(handleError(err).Error())
+				if err2 := tx.Rollback(); err2 != nil {
+					return nil, err2
+				}
+				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(
+				ctx,
+				code.String(),
+				&validity,
+			); err != nil {
+				feedback.Warn(handleError(err).Error())
+				if err2 := tx.Rollback(); err2 != nil {
+					return nil, err2
+				}
+				unchanged++
+				continue
+			}
+
 			// "Upsert" reference water levels
 			for _, wl := range []struct {
 				level **erdms.RisreflevelcodeType
--- a/schema/gemma.sql	Wed Jun 12 18:21:14 2019 +0200
+++ b/schema/gemma.sql	Wed Jun 12 18:26:26 2019 +0200
@@ -280,7 +280,7 @@
         geom geography(POINT, 4326) NOT NULL,
         applicability_from_km int8,
         applicability_to_km int8,
-        validity tstzrange NOT NULL,
+        validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
         zero_point double precision NOT NULL,
         geodref varchar,
         date_info timestamp with time zone NOT NULL,
@@ -288,7 +288,6 @@
         lastupdate timestamp with time zone NOT NULL,
         -- entry removed from external data source (RIS-Index)/historicised:
         erased boolean NOT NULL DEFAULT false,
-        CHECK (erased OR NOT isempty(validity)),
         PRIMARY KEY (location, validity),
         EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
             DEFERRABLE INITIALLY DEFERRED
@@ -489,7 +488,7 @@
     CREATE TABLE bottlenecks (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         bottleneck_id varchar NOT NULL,
-        validity tstzrange NOT NULL,
+        validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
         EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
             DEFERRABLE INITIALLY DEFERRED,
         gauge_location isrs NOT NULL,
@@ -519,7 +518,6 @@
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL,
         erased boolean NOT NULL DEFAULT false,
-        CHECK (erased OR NOT isempty(validity)),
         staging_done boolean NOT NULL DEFAULT false
     )
     -- Allow only one non-erased entry per bottleneck