Mercurial > gemma
changeset 3645:02951a62e8c6
'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 (