Mercurial > gemma
changeset 5186:1c5c9fdaf730 new-fwa
Merged default into 'new-fwa' branch.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 31 Mar 2020 20:03:05 +0200 |
| parents | 3c748b2b4de6 (current diff) f11b9b50fcc9 (diff) |
| children | 5f4d054fea31 |
| files | |
| diffstat | 13 files changed, 386 insertions(+), 55 deletions(-) [+] |
line wrap: on
line diff
--- a/client/src/components/importoverview/ImportOverview.vue Fri Mar 27 15:57:40 2020 +0100 +++ b/client/src/components/importoverview/ImportOverview.vue Tue Mar 31 20:03:05 2020 +0200 @@ -482,6 +482,7 @@ id: r.id, state: r.status })); + this.loading = true; this.$store .dispatch("imports/confirmReview", data) .then(response => { @@ -522,6 +523,9 @@ title: this.$gettext("Backend Error"), message: message }); + }) + .finally(() => { + this.loading = false; }); } },
--- a/client/src/components/layers/layers.js Fri Mar 27 15:57:40 2020 +0100 +++ b/client/src/components/layers/layers.js Tue Mar 31 20:03:05 2020 +0200 @@ -5,7 +5,12 @@ Tile as TileLayer, Vector as VectorLayer } from "ol/layer"; -import { and as andFilter, equalTo } from "ol/format/filter"; +import { + and as andFilter, + equalTo, + lessThanOrEqualTo, + greaterThanOrEqualTo +} from "ol/format/filter"; import { HTTP } from "@/lib/http"; import { ImageWMS as ImageSource } from "ol/source"; @@ -39,7 +44,21 @@ HTTP.post( "/internal/wfs", new XMLSerializer().serializeToString( - new WFS().writeGetFeature(featureRequestOptions) + new WFS().writeGetFeature( + (function() { + if (featureRequestOptions.filter) { + for (let condition of featureRequestOptions.filter.conditions) { + if (condition.propertyName == "valid_from") { + condition.expression = store.state.application.refreshLayersTime.toISOString(); + } + if (condition.propertyName == "valid_to") { + condition.expression = store.state.application.refreshLayersTime.toISOString(); + } + } + } + return featureRequestOptions; + })() + ) ), { headers: { @@ -49,15 +68,20 @@ } ) .then(response => { - const features = new GeoJSON().readFeatures( - JSON.stringify(response.data) - ); - if (featurePostProcessor) { - features.map(f => featurePostProcessor(f, store, features)); + try { + const features = new GeoJSON().readFeatures( + JSON.stringify(response.data) + ); + if (featurePostProcessor) { + features.map(f => featurePostProcessor(f, store, features)); + } + vectorSource.addFeatures(features); + } catch (error) { + console.log(error); } - vectorSource.addFeatures(features); }) - .catch(() => { + .catch(error => { + console.log(error); vectorSource.removeLoadedExtent(extent); store.dispatch("application/reportBackendError"); }); @@ -194,7 +218,17 @@ buildVectorLoader( { featureTypes: ["bottlenecks_geoserver"], - geometryName: "area" + geometryName: "area", + filter: andFilter( + lessThanOrEqualTo( + "valid_from", + store.state.application.refreshLayersTime.toISOString() + ), + greaterThanOrEqualTo( + "valid_to", + store.state.application.refreshLayersTime.toISOString() + ) + ) }, bottlenecksSource, false, @@ -362,7 +396,8 @@ .then(response => { tile.getImage().src = URL.createObjectURL(response.data); }) - .catch(() => { + .catch(error => { + console.log(error); store.dispatch("application/reportBackendError"); }); } // TODO tile.setState(TileState.ERROR); @@ -392,7 +427,8 @@ .then(response => { tile.getImage().src = URL.createObjectURL(response.data); }) - .catch(() => { + .catch(error => { + console.log(error); store.dispatch("application/reportBackendError"); }); } // TODO tile.setState(TileState.ERROR); @@ -497,7 +533,8 @@ .then(response => { tile.getImage().src = URL.createObjectURL(response.data); }) - .catch(() => { + .catch(error => { + console.log(error); store.dispatch("application/reportBackendError"); }); } // TODO tile.setState(TileState.ERROR); @@ -628,7 +665,8 @@ .then(response => { tile.getImage().src = URL.createObjectURL(response.data); }) - .catch(() => { + .catch(error => { + console.log(error); store.dispatch("application/reportBackendError"); }); } // TODO tile.setState(TileState.ERROR); @@ -702,7 +740,8 @@ .then(response => { tile.getImage().src = URL.createObjectURL(response.data); }) - .catch(() => { + .catch(error => { + console.log(error); store.dispatch("application/reportBackendError"); }); } // TODO tile.setState(TileState.ERROR);
--- a/pkg/imports/fd.go Fri Mar 27 15:57:40 2020 +0100 +++ b/pkg/imports/fd.go Tue Mar 31 20:03:05 2020 +0200 @@ -136,21 +136,54 @@ relation = 'waterway.fairway_dimensions'::regclass)` deleteFairwayDimensionSQL = ` +-- Delete entries to be replaced by those in staging area DELETE FROM waterway.fairway_dimensions -WHERE staging_done - AND level_of_service = ( - SELECT DISTINCT level_of_service FROM waterway.fairway_dimensions - WHERE id IN ( - SELECT key FROM import.track_imports - WHERE import_id = $1 - AND relation = 'waterway.fairway_dimensions'::regclass)) + WHERE id IN ( + SELECT key FROM import.track_imports + WHERE import_id = $1 + AND relation = 'waterway.fairway_dimensions'::regclass + AND deletion) ` - + // Temporary table to collect IDs of unchanged entries + tmpTableSQL = ` +CREATE TEMP TABLE unchanged (id int PRIMARY KEY) ON COMMIT DROP +` // The ST_MakeValid and ST_Buffer below are a workarround to // avoid errors due to reprojection. insertFairwayDimensionSQL = ` WITH resp AS ( SELECT users.current_user_area_utm() AS a +), +g AS ( + SELECT ST_Multi(ST_CollectionExtract(ST_MakeValid(ST_Transform( + CASE WHEN pg_has_role('sys_admin', 'MEMBER') + OR ST_Covers((SELECT a FROM resp), + ST_Transform(new_fd, (SELECT ST_SRID(a) FROM resp))) + THEN new_fd + ELSE ST_Intersection( + (SELECT ST_Buffer(a, -0.0001) FROM resp), + ST_MakeValid(ST_Transform(new_fd, (SELECT ST_SRID(a) FROM resp)))) + END, + 4326)), 3)) AS new_fd + FROM ST_GeomFromWKB($1, $2::integer) AS new_fd (new_fd) + WHERE pg_has_role('sys_admin', 'MEMBER') + OR ST_Intersects((SELECT a FROM resp), + ST_MakeValid(ST_Transform(new_fd, (SELECT ST_SRID(a) FROM resp)))) +), +not_new AS ( + -- Collect IDs of unchanged entries in temp table + INSERT INTO unchanged + SELECT id + FROM g, waterway.fairway_dimensions + WHERE staging_done + AND validity @> current_timestamp + AND (area, level_of_service, + min_width, max_width, min_depth, source_organization + ) IS NOT DISTINCT FROM ( + new_fd, $3, $4, $5, $6, $8) + -- Return something if a duplicate in the data source is encountered + ON CONFLICT (id) DO UPDATE SET id = EXCLUDED.id + RETURNING 1 ) INSERT INTO waterway.fairway_dimensions ( area, @@ -161,25 +194,58 @@ date_info, source_organization) SELECT - ST_Multi(ST_CollectionExtract(ST_MakeValid(ST_Transform( - CASE WHEN pg_has_role('sys_admin', 'MEMBER') - OR ST_Covers((SELECT a FROM resp), - ST_Transform(new_fd, (SELECT ST_SRID(a) FROM resp))) - THEN new_fd - ELSE ST_Intersection( - (SELECT ST_Buffer(a, -0.0001) FROM resp), - ST_MakeValid(ST_Transform(new_fd, (SELECT ST_SRID(a) FROM resp)))) - END, - 4326)), 3)), - $3, $4, $5, $6, $7, $8 - FROM ST_GeomFromWKB($1, $2::integer) AS new_fd (new_fd) - WHERE pg_has_role('sys_admin', 'MEMBER') - OR ST_Intersects((SELECT a FROM resp), - ST_MakeValid(ST_Transform(new_fd, (SELECT ST_SRID(a) FROM resp)))) + new_fd, $3, $4, $5, $6, $7, $8 + FROM g + WHERE NOT EXISTS(SELECT 1 FROM not_new) RETURNING id, ST_X(ST_Centroid(area::geometry)), ST_Y(ST_Centroid(area::geometry)) ` + // Fetch IDs of entries removed from data source + selectOldSQL = ` +WITH resp AS ( + SELECT users.current_user_area_utm() AS a +) +SELECT id FROM waterway.fairway_dimensions + WHERE staging_done + AND validity @> current_timestamp + AND level_of_service = $1 + AND (pg_has_role('sys_admin', 'MEMBER') + OR ST_Covers((SELECT a FROM resp), + ST_Transform(CAST(area AS geometry), (SELECT ST_SRID(a) FROM resp)))) + AND id NOT IN (SELECT id FROM unchanged) +` + invalidateFairwayDimensionSQL = ` +WITH track AS ( + -- Mark entry for deletion that has been removed from the data source + INSERT INTO import.track_imports (import_id, deletion, relation, key) + VALUES($1, true, 'waterway.fairway_dimensions', $2) +) +-- Insert historic version with respective validity +INSERT INTO waterway.fairway_dimensions ( + area, + validity, + level_of_service, + min_width, + max_width, + min_depth, + date_info, + source_organization) +SELECT + area, + tstzrange(lower(validity), current_timestamp), + level_of_service, + min_width, + max_width, + min_depth, + date_info, + source_organization + FROM waterway.fairway_dimensions + WHERE id = $2 +RETURNING id, + ST_X(ST_Centroid(area::geometry)), + ST_Y(ST_Centroid(area::geometry)) +` ) // Do executes the actual fairway dimension import. @@ -226,12 +292,25 @@ } defer tx.Rollback() + if _, err := tx.ExecContext(ctx, tmpTableSQL); err != nil { + return nil, err + } + insertStmt, err := tx.PrepareContext(ctx, insertFairwayDimensionSQL) if err != nil { return nil, err } defer insertStmt.Close() + invalidateStmt, err := tx.PrepareContext( + ctx, invalidateFairwayDimensionSQL) + if err != nil { + return nil, err + } + defer invalidateStmt.Close() + + savepoint := Savepoint(ctx, tx, "feature") + var ( unsupported = stringCounter{} missingProperties int @@ -265,8 +344,6 @@ feedback.Info( "Found %d features in data source", len(rfc.Features)) - savepoint := Savepoint(ctx, tx, "feature") - features: for _, feature := range rfc.Features { if feature.Geometry.Coordinates == nil { @@ -347,11 +424,73 @@ } if outside > 0 { - feedback.Info("Features outside responsibility area: %d", outside) + feedback.Info( + "Features outside responsibility area or unchanged: %d", outside) } if features == 0 { - return nil, UnchangedError("No features found") + feedback.Info("No new features found") + } + + // Invalidate features that have been removed from data source + res, err := tx.QueryContext(ctx, selectOldSQL, fd.LOS) + if err != nil { + return nil, err + } + defer res.Close() + var oldIDs []int64 + for res.Next() { + var oldID int64 + if err := res.Scan(&oldID); err != nil { + return nil, err + } + oldIDs = append(oldIDs, oldID) + } + if err := res.Err(); err != nil { + return nil, err + } + + if features == 0 && len(oldIDs) == 0 { + return nil, UnchangedError("Nothing changed") + } + + if len(oldIDs) > 0 { + feedback.Info( + "Number of features removed from data source: %d", len(oldIDs)) + + var old int + for _, oldID := range oldIDs { + var fdid int64 + var lat, lon float64 + if err := savepoint(func() error { + return invalidateStmt.QueryRowContext( + ctx, + importID, + oldID, + ).Scan(&fdid, &lat, &lon) + }); err != nil { + feedback.Error(pgxutils.ReadableError{Err: err}.Error()) + continue + } + fds = append(fds, fdSummary{ID: fdid, Lat: lat, Lon: lon}) + + if err := track( + ctx, tx, importID, "waterway.fairway_dimensions", fdid, + ); err != nil { + return nil, err + } + + old++ + } + + // Do not fail if features > 0 because otherwise new features are lost + if features == 0 && old == 0 { + return nil, fmt.Errorf("Invalidating features failed") + } + + if old > 0 { + feedback.Info("Number of features invalidated: %d", old) + } } if err = tx.Commit(); err == nil {
--- a/pkg/imports/wfsjob.go Fri Mar 27 15:57:40 2020 +0100 +++ b/pkg/imports/wfsjob.go Tue Mar 31 20:03:05 2020 +0200 @@ -311,8 +311,10 @@ sgc.releaseStmts() sgc.tx = nil sgc.ctx = nil - if err2 := tx.Commit(); err2 != nil { - // A real error on commit overrules the first. + if err2 := tx.Commit(); err2 != nil && + (err == nil || err == ErrFeaturesUnmodified) { + // An error on commit that is not induced by the first + // overrules the first. err = err2 } }
--- a/schema/default_sysconfig.sql Fri Mar 27 15:57:40 2020 +0100 +++ b/schema/default_sysconfig.sql Tue Mar 31 20:03:05 2020 +0200 @@ -112,8 +112,7 @@ -- Directly accessed tables INSERT INTO sys_admin.published_services (schema, name) VALUES ('waterway', 'waterway_area'), - ('waterway', 'waterway_profiles'), - ('waterway', 'fairway_dimensions'); + ('waterway', 'waterway_profiles'); -- GeoServer SQL views without time support INSERT INTO sys_admin.published_services ( @@ -306,6 +305,21 @@ ORDER BY bottleneck_id DESC) AS srl ON b.bottleneck_id = srl.bottleneck_id $$), + ('waterway', 'fairway_dimensions', 4326, 'id', + 'valid_from', 'valid_to', $$ + SELECT id, + lower(validity) AS valid_from, + COALESCE(upper(validity), current_timestamp) AS valid_to, + area, + level_of_service, + min_width, + max_width, + min_depth, + date_info, + source_organization, + staging_done + FROM waterway.fairway_dimensions + $$), ('waterway', 'waterway_axis', 4326, 'id', 'valid_from', 'valid_to', $$ SELECT id,
--- a/schema/gemma.sql Fri Mar 27 15:57:40 2020 +0100 +++ b/schema/gemma.sql Tue Mar 31 20:03:05 2020 +0200 @@ -71,13 +71,17 @@ -- Trigger function to be used as a constraint trigger to enforce uniqueness -- of geometries in the column with its name given as an argument to the --- trigger function. If additional column names are given, --- the group of given columns is tested for equality. +-- trigger function. If additional column names are given as trigger arguments, +-- the group of given columns is tested for equality, if no operators are +-- given. Optionally, a column can be compared with a different operator +-- given after the keyword "WITH" in the trigger argument. CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS $$ DECLARE new_geom geometry; tg_arg text; + col varchar; + opr varchar; filters text; has_equal boolean; BEGIN @@ -86,7 +90,11 @@ USING NEW; FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP -- Test each additional argument for equality - filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg); + -- or with operator given after keyword "WITH" + tg_arg = lower(tg_arg); + col = split_part(tg_arg, ' with ', 1); + opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '='); + filters = format('%s AND %I %s $2.%2$I', filters, col, opr); END LOOP; EXECUTE format( 'SELECT EXISTS(SELECT 1 FROM %I.%I ' @@ -632,7 +640,9 @@ ) CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis - FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity') + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW + EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&') CREATE INDEX waterway_axis_validity ON waterway_axis USING GiST (validity) @@ -716,14 +726,20 @@ min_depth smallint NOT NULL, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, - staging_done boolean NOT NULL DEFAULT false + staging_done boolean NOT NULL DEFAULT false, + validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) + CHECK (NOT isempty(validity)) ) CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique - AFTER INSERT OR UPDATE OF area, staging_done ON fairway_dimensions - FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area', 'staging_done') + AFTER INSERT OR UPDATE OF area, validity, staging_done + ON fairway_dimensions + FOR EACH ROW EXECUTE FUNCTION prevent_st_equals( + 'area', 'validity WITH &&', 'staging_done') + CREATE INDEX fairway_dimensions_validity + ON fairway_dimensions USING GiST (validity) -- -- Bottlenecks
--- a/schema/gemma_tests.sql Fri Mar 27 15:57:40 2020 +0100 +++ b/schema/gemma_tests.sql Tue Mar 31 20:03:05 2020 +0200 @@ -25,6 +25,7 @@ 'NULL value is not checked'); SELECT throws_ok($$ + SET CONSTRAINTS waterway.waterway_axis_wtwaxs_unique IMMEDIATE; INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test'), (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test') @@ -32,6 +33,21 @@ 23505, NULL, 'No duplicate geometries can be inserted into waterway_axis'); +SELECT throws_ok($$ + SET CONSTRAINTS waterway.waterway_axis_wtwaxs_unique IMMEDIATE; + INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES ( + ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), + 'test', + tstzrange(NULL, current_timestamp) + ), ( + ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), + 'test', + tstzrange(current_timestamp - interval '1d', NULL) + ) + $$, + 23505, NULL, + 'Duplicate axis geometries cannot be inserted if validities intersect'); + SELECT lives_ok($$ INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES ( ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),
--- a/schema/run_tests.sh Fri Mar 27 15:57:40 2020 +0100 +++ b/schema/run_tests.sh Tue Mar 31 20:03:05 2020 +0200 @@ -80,7 +80,7 @@ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ -f "$BASEDIR"/tap_tests_data.sql \ - -c "SELECT plan(88 + ( + -c "SELECT plan(89 + ( SELECT count(*)::int FROM information_schema.tables WHERE table_schema = 'waterway'))" \
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1436/01.amend_prevent_st_equals.sql Tue Mar 31 20:03:05 2020 +0200 @@ -0,0 +1,43 @@ +CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS +$$ +DECLARE + new_geom geometry; + tg_arg text; + col varchar; + opr varchar; + filters text; + has_equal boolean; +BEGIN + EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0]) + INTO new_geom + USING NEW; + FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP + tg_arg = lower(tg_arg); + col = split_part(tg_arg, ' with ', 1); + opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '='); + filters = format('%s AND %I %s $2.%2$I', filters, col, opr); + END LOOP; + EXECUTE format( + 'SELECT EXISTS(SELECT 1 FROM %I.%I ' + 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))' + '%s)', + TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters) + INTO has_equal + USING new_geom, NEW; + IF has_equal THEN + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('Failing row contains geometry in %s', + Box2D(new_geom)), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; + RETURN NEW; +END; +$$ +LANGUAGE plpgsql;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1436/02.axis_no_intersecting_validity.sql Tue Mar 31 20:03:05 2020 +0200 @@ -0,0 +1,7 @@ +DROP TRIGGER waterway_axis_wtwaxs_unique ON waterway.waterway_axis; + +CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique + AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway.waterway_axis + DEFERRABLE INITIALLY DEFERRED + FOR EACH ROW + EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&');
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1437/01.historicise_fairway_dimensions.sql Tue Mar 31 20:03:05 2020 +0200 @@ -0,0 +1,30 @@ +ALTER TABLE waterway.fairway_dimensions + ADD validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) + CHECK (NOT isempty(validity)); + +-- Assume existing entries have been valid since last accepted import +WITH imps AS ( + SELECT changed, CAST(summary AS jsonb)->'fd-area' AS fd_area + FROM import.imports + WHERE kind = 'fd' AND state = 'accepted' +) +UPDATE waterway.fairway_dimensions fd SET validity = tstzrange( + COALESCE( + (SELECT max(changed) + FROM imps + WHERE fd.id IN( + SELECT id + FROM jsonb_to_recordset(fd_area) + AS fd_area (id bigint, lat numeric, lon numeric))), + current_timestamp), + NULL); + +DROP TRIGGER fairway_dimensions_area_unique ON waterway.fairway_dimensions; +CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique + AFTER INSERT OR UPDATE OF area, validity, staging_done + ON waterway.fairway_dimensions + FOR EACH ROW EXECUTE FUNCTION prevent_st_equals( + 'area', 'validity WITH &&', 'staging_done'); + +CREATE INDEX fairway_dimensions_validity + ON waterway.fairway_dimensions USING GiST (validity);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1437/02.configure_wms-t.sql Tue Mar 31 20:03:05 2020 +0200 @@ -0,0 +1,21 @@ +UPDATE sys_admin.published_services + SET + srid = 4326, + key_column = 'id', + wmst_attribute = 'valid_from', + wmst_end_attribute = 'valid_to', + view_def = $$ + SELECT id, + lower(validity) AS valid_from, + COALESCE(upper(validity), current_timestamp) AS valid_to, + area, + level_of_service, + min_width, + max_width, + min_depth, + date_info, + source_organization, + staging_done + FROM waterway.fairway_dimensions + $$ + WHERE schema = 'waterway' AND name = 'fairway_dimensions';