Mercurial > gemma

changeset 3665:29ef6d41e4af

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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 58508f50d192
children db87f34805fb
files pkg/imports/bn.go pkg/imports/wg.go schema/auth_tests.sql schema/gemma.sql schema/gemma_tests.sql schema/run_tests.sh
diffstat 6 files changed, 89 insertions(+), 70 deletions(-) [+]
line wrap: on
line diff
--- a/pkg/imports/bn.go	Fri Jun 14 17:56:42 2019 +0200
+++ b/pkg/imports/bn.go	Sat Jun 15 09:24:28 2019 +0200
@@ -107,14 +107,6 @@
 )
 RETURNING id`
 
-	moveSRSQL = `
-UPDATE waterway.sounding_results
--- Associate measurements to matching bottleneck version
-SET bottleneck_validity = $2
-WHERE bottleneck_id = $1
-  AND CAST(date_info AS timestamptz) <@ CAST($2 AS tstzrange)
-`
-
 	fixBNValiditySQL = `
 UPDATE waterway.bottlenecks SET
    -- Set enddate of old entry to new startdate in case of overlap:
@@ -273,7 +265,7 @@
 
 	feedback.Info("Found %d bottlenecks for import", len(bns))
 
-	var hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt,
+	var hasStmt, insertStmt, fixValidityStmt, updateStmt,
 		deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt
 
 	for _, x := range []struct {
@@ -282,7 +274,6 @@
 	}{
 		{hasBottleneckSQL, &hasStmt},
 		{insertBottleneckSQL, &insertStmt},
-		{moveSRSQL, &moveSRStmt},
 		{fixBNValiditySQL, &fixValidityStmt},
 		{updateBottleneckSQL, &updateStmt},
 		{deleteBottleneckMaterialSQL, &deleteMaterialStmt},
@@ -303,7 +294,7 @@
 	for _, bn := range bns {
 		if err := storeBottleneck(
 			ctx, importID, conn, feedback, bn, &nids, tolerance,
-			hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt,
+			hasStmt, insertStmt, fixValidityStmt, updateStmt,
 			deleteMaterialStmt, insertMaterialStmt, trackStmt); err != nil {
 			return nil, err
 		}
@@ -330,7 +321,7 @@
 	bn *ifbn.BottleNeckType,
 	nids *[]string,
 	tolerance float64,
-	hasStmt, insertStmt, moveSRStmt, fixValidityStmt, updateStmt,
+	hasStmt, insertStmt, fixValidityStmt, updateStmt,
 	deleteMaterialStmt, insertMaterialStmt, trackStmt *sql.Stmt,
 ) error {
 	feedback.Info("Processing %s (%s)", bn.OBJNAM, bn.Bottleneck_id)
@@ -512,18 +503,6 @@
 		}
 	}
 
-	// Move sounding results to new matching bottleneck version, if applicable
-	if _, err = tx.StmtContext(ctx, moveSRStmt).ExecContext(ctx,
-		bn.Bottleneck_id,
-		&validity,
-	); err != nil {
-		feedback.Warn(handleError(err).Error())
-		if err2 := tx.Rollback(); err2 != nil {
-			return err2
-		}
-		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,
--- a/pkg/imports/wg.go	Fri Jun 14 17:56:42 2019 +0200
+++ b/pkg/imports/wg.go	Sat Jun 15 09:24:28 2019 +0200
@@ -116,22 +116,6 @@
 )
 `
 
-	moveGMSQL = `
-UPDATE waterway.gauge_measurements
--- Associate measurements to matching gauge version
-SET validity = $2
-WHERE isrs_astext(location) = $1
-  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:
@@ -208,7 +192,7 @@
 		return nil, err
 	}
 
-	var eraseGaugeStmt, insertStmt, moveGMStmt, moveBNStmt,
+	var eraseGaugeStmt, insertStmt,
 		fixValidityStmt, updateStmt,
 		deleteReferenceWaterLevelsStmt,
 		isNtSDepthRefStmt, insertWaterLevelStmt *sql.Stmt
@@ -218,8 +202,6 @@
 	}{
 		{eraseGaugeSQL, &eraseGaugeStmt},
 		{insertGaugeSQL, &insertStmt},
-		{moveGMSQL, &moveGMStmt},
-		{moveBNSQL, &moveBNStmt},
 		{fixValiditySQL, &fixValidityStmt},
 		{updateGaugeSQL, &updateStmt},
 		{deleteReferenceWaterLevelsSQL, &deleteReferenceWaterLevelsStmt},
@@ -446,31 +428,6 @@
 				}
 			}
 
-			// 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(
--- a/schema/auth_tests.sql	Fri Jun 14 17:56:42 2019 +0200
+++ b/schema/auth_tests.sql	Sat Jun 15 09:24:28 2019 +0200
@@ -86,7 +86,8 @@
             $2, 'AT', 'AT', 'AT',
             1, 'depth', current_timestamp, 'testorganization'
         FROM waterway.gauges
-        WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs;
+        WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs
+            AND NOT erased;
 SELECT lives_ok($$
     EXECUTE bn_insert(
         'test1',
--- a/schema/gemma.sql	Fri Jun 14 17:56:42 2019 +0200
+++ b/schema/gemma.sql	Sat Jun 15 09:24:28 2019 +0200
@@ -72,6 +72,43 @@
 $$
 LANGUAGE plpgsql;
 
+-- Trigger functions to be used as statement-level AFTER triggers,
+-- associating time-based referencing objects to matching version
+CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS
+$$
+BEGIN
+    -- Avoid unnecessary execution ON UPDATE if validity did not change
+    IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
+        UPDATE waterway.gauge_measurements
+        SET validity = NEW.validity
+        WHERE location = NEW.location
+            AND measure_date <@ NEW.validity;
+
+        UPDATE waterway.bottlenecks
+        SET gauge_validity = NEW.validity
+        WHERE gauge_location = NEW.location
+            AND lower(validity) <@ NEW.validity;
+    END IF;
+    RETURN NULL; -- ignored
+END;
+$$
+LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS
+$$
+BEGIN
+    -- Avoid unnecessary execution ON UPDATE if validity did not change
+    IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
+        UPDATE waterway.sounding_results
+        SET bottleneck_validity = NEW.validity
+        WHERE bottleneck_id = NEW.bottleneck_id
+          AND CAST(date_info AS timestamptz) <@ NEW.validity;
+    END IF;
+    RETURN NULL; -- ignored
+END;
+$$
+LANGUAGE plpgsql;
+
 --
 -- GEMMA data
 --
@@ -296,6 +333,9 @@
     CREATE UNIQUE INDEX gauges_erased_unique_constraint
         ON gauges (location)
         WHERE NOT erased
+    -- Associate referencing objects to matching gauge version
+    CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
+        ON gauges FOR EACH ROW EXECUTE FUNCTION move_gauge_referencing()
 
     CREATE TABLE gauges_reference_water_levels (
         location isrs NOT NULL,
@@ -525,6 +565,10 @@
     CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint
         ON bottlenecks (bottleneck_id)
         WHERE NOT erased
+    -- Associate referencing objects to matching bottleneck version
+    CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
+        ON bottlenecks FOR EACH ROW
+        EXECUTE FUNCTION move_bottleneck_referencing()
 
     CREATE TABLE bottlenecks_riverbed_materials (
         bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
--- a/schema/gemma_tests.sql	Fri Jun 14 17:56:42 2019 +0200
+++ b/schema/gemma_tests.sql	Sat Jun 15 09:24:28 2019 +0200
@@ -31,3 +31,41 @@
     23505, NULL,
     'No duplicate geometries can be inserted into waterway_area');
 
+START TRANSACTION;
+CREATE TEMP TABLE new_v (v) AS
+    SELECT tstzrange(current_timestamp - '2 d'::interval,
+        current_timestamp - '12 h'::interval);
+INSERT INTO waterway.gauges (
+    location,
+    validity,
+    objname,
+    geom,
+    zero_point,
+    date_info,
+    source_organization,
+    lastupdate,
+    erased)
+VALUES (
+    ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
+    (SELECT v FROM new_v),
+    'testgauge',
+    ST_geomfromtext('POINT(0 0)', 4326),
+    0,
+    current_timestamp,
+    'testorganization',
+    current_timestamp,
+    true);
+-- Fix validity of old entry to match exclusion constraint
+UPDATE waterway.gauges SET
+    validity = validity - (SELECT v FROM new_v)
+WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs
+    AND validity && (SELECT v FROM new_v)
+    AND NOT erased;
+COMMIT;
+SELECT results_eq($$
+    SELECT DISTINCT gauge_validity FROM waterway.bottlenecks
+    $$,
+    $$
+    SELECT v FROM new_v
+    $$,
+    'Bottlenecks have been associated to new matching gauge version');
--- a/schema/run_tests.sh	Fri Jun 14 17:56:42 2019 +0200
+++ b/schema/run_tests.sh	Sat Jun 15 09:24:28 2019 +0200
@@ -28,7 +28,7 @@
     -c 'SET client_min_messages TO WARNING' \
     -c "DROP ROLE IF EXISTS $TEST_ROLES" \
     -f tap_tests_data.sql \
-    -c "SELECT plan(70 + (
+    -c "SELECT plan(71 + (
             SELECT count(*)::int
                 FROM information_schema.tables
                 WHERE table_schema = 'waterway'))" \