Mercurial > gemma
changeset 4123:209bdd361615
Merged v4-preview20190726-fixes
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Thu, 01 Aug 2019 14:22:24 +0200 |
| parents | b785b6bef578 (diff) 9f03eb3817d6 (current diff) |
| children | acd87a3d246b |
| files | |
| diffstat | 25 files changed, 790 insertions(+), 271 deletions(-) [+] |
line wrap: on
line diff
--- a/.hgtags Thu Aug 01 14:10:25 2019 +0200 +++ b/.hgtags Thu Aug 01 14:22:24 2019 +0200 @@ -12,3 +12,4 @@ b166cb97b98a40f33e977c96e65e79bf22e92fca v3.1 d78af8354b95cea86744459f350edb16662dadd0 v3.1 5396581cf20334cbc5e69280e5d9b192640d96b9 v4-preview20190717 +aececbc3d04798d905e65196ac0870d081776ca2 v4-preview20190726
--- a/README.md Thu Aug 01 14:10:25 2019 +0200 +++ b/README.md Thu Aug 01 14:22:24 2019 +0200 @@ -21,6 +21,21 @@ For further details see [docs/DEVELOPMENT](docs/DEVELOPMENT.md), +## Running Tests + +- Running database tests: + + * You will need a PostgreSQL cluster with PostGIS and pgTAP. + * To run the tests use the script `./schema/run_tests.sh`. + * `./schema/run_tests.sh --help` shows you available options. + Per default the script will create (and drop if it already exists) + a database named "gemma_test" and all necessary roles in the postgres + default cluster (listening on port 5432) and run the tests + in that database. + * The script must be run as a user with PostgreSQL super user rights. + By convention this is "postgres" on most systems. + + ## Setup Database - You will need a PostgreSQL cluster with PostGIS.
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/TODOs-historization_ng.md Thu Aug 01 14:22:24 2019 +0200 @@ -0,0 +1,51 @@ +# TODOs for HNG (Historization NextGen) + +## gauges-reference-water-levels + +Could be seen as direct part of the gauge data and therefore kept with +the foll primary key reference... + +## Water level diagrams: + +One time range might reference different verions of one gauge: +- How is that hanedled currently? +- How to handle it in HNG? + +## Map display: + +Ensure only current gauges are shown in all situations +- Gauges layer +- Search +- more? + +## Bottlenecks: + +Always associate the correct gauge, when: +- Uploading Sounding results (how is that currently handled?) +- Displaying Sounding data +- Creating/showing X-Cuts +- Unclear: what constraints shall we chekc for on bottleneck imports? + +Cut end of validit _after_ accepting the new one. + +## Sounding Results: + +- Maybe add a constraint to check, that a ref gauge is available? + Maybe not neccessary as import isn't possible anyway, as the + reference gauge is needed for the import process... (So the + constraint would never catch anything, as no data could be produced + without ref gauge) Only upside: we would triger if there is an error + in the backend generating SR with invalid ref data. +- Check during upload that matching bottleneck and reference gauge + data is available (and use ist correctly) +- Use correct reference gauge for X-Cuts +- (Maybe later) use matching BN data for display + +## EVERYTHING ELSE + +Not being mentioned here, doesn't mean something is done... + + +# DONE + +Stuff done will bemoved moved here for reference:
--- a/client/src/components/gauge/HydrologicalConditions.vue Thu Aug 01 14:10:25 2019 +0200 +++ b/client/src/components/gauge/HydrologicalConditions.vue Thu Aug 01 14:22:24 2019 +0200 @@ -241,38 +241,41 @@ }, // Diagram legend addDiagramLegend(position, offset, color) { - let x = offset.x, - y = offset.y; - let width = - (this.pdf.doc.getStringUnitWidth("Long-term Amplitude") * 10) / - (72 / 25.6) + - 5; + let x = offset.x + 2, // 2 is the radius of the circle + y = offset.y, + padding = 3; + this.pdf.doc.setFontStyle("normal"); + this.pdf.doc.setFontSize(10); + let width = this.pdf.doc.getTextWidth("Long-term Amplitude") + padding; // if position is on the right, x needs to be calculate with pdf width and // the size of the element if (["topright", "bottomright"].indexOf(position) !== -1) { x = this.pdf.width - offset.x - width; } if (["bottomright", "bottomleft"].indexOf(position) !== -1) { - y = this.pdf.height - offset.y - this.getTextHeight(4); + y = this.pdf.height - offset.y - this.getTextHeight(5) - 2; } - this.pdf.doc.setFontSize(10); + if (y < this.getTextHeight(1)) { + y = y + this.getTextHeight(1) / 2; + } + console.log(y); this.pdf.doc.setTextColor(color); this.pdf.doc.setDrawColor("white"); this.pdf.doc.setFillColor("red"); this.pdf.doc.circle(x, y, 2, "FD"); - this.pdf.doc.text(x + 3, y + 1, "" + this.yearCompare); + this.pdf.doc.text(x + padding, y + 1, "" + this.yearCompare); this.pdf.doc.setFillColor("orange"); this.pdf.doc.circle(x, y + 5, 2, "FD"); - this.pdf.doc.text(x + 3, y + 6, "Q25%"); + this.pdf.doc.text(x + padding, y + 6, "Q25%"); this.pdf.doc.setFillColor("black"); this.pdf.doc.circle(x, y + 10, 2, "FD"); this.pdf.doc.text(x + 3, y + 11, "Median "); this.pdf.doc.setFillColor("purple"); this.pdf.doc.circle(x, y + 15, 2, "FD"); - this.pdf.doc.text(x + 3, y + 16, "Q75%"); + this.pdf.doc.text(x + padding, y + 16, "Q75%"); this.pdf.doc.setFillColor("lightsteelblue"); this.pdf.doc.circle(x, y + 20, 2, "FD"); - this.pdf.doc.text(x + 3, y + 21, "Long-term Amplitude"); + this.pdf.doc.text(x + padding, y + 21, "Long-term Amplitude"); }, getPrintLayout(svgHeight) { return {
--- a/client/src/components/gauge/Waterlevel.vue Thu Aug 01 14:10:25 2019 +0200 +++ b/client/src/components/gauge/Waterlevel.vue Thu Aug 01 14:22:24 2019 +0200 @@ -209,12 +209,10 @@ this.dateFrom.toLocaleDateString() + " - " + this.dateTo.toLocaleDateString(); - this.generatePDF({ templateData: this.templateData, diagramTitle: diagramTitle }); - this.pdf.doc.save( this.selectedGauge.properties.objname + " Waterlevel-Diagram.pdf" ); @@ -247,33 +245,35 @@ }, // Diagram legend addDiagramLegend(position, offset, color) { - let x = offset.x; - let y = offset.y; + let x = offset.x + 2, // 2 is the radius of the circle + y = offset.y, + padding = 3; + this.pdf.doc.setFontStyle("normal"); this.pdf.doc.setFontSize(10); - let width = - (this.pdf.doc.getStringUnitWidth("Navigable Range") * 10) / - (72 / 25.6) + - 5; + let width = this.pdf.doc.getTextWidth("Navigable Range") + padding; if (["topright", "bottomright"].indexOf(position) !== -1) { x = this.pdf.width - offset.x - width; } if (["bottomright", "bottomleft"].indexOf(position) !== -1) { - y = this.pdf.height - offset.y - this.getTextHeight(4); + y = this.pdf.height - offset.y - this.getTextHeight(3); + } + if (y < this.getTextHeight(1)) { + y = y + this.getTextHeight(1) / 2; } this.pdf.doc.setTextColor(color); this.pdf.doc.setDrawColor("white"); this.pdf.doc.setFillColor("steelblue"); this.pdf.doc.circle(x, y, 2, "FD"); - this.pdf.doc.text(x + 3, y + 1, "Waterlevel"); + this.pdf.doc.text(x + padding, y + 1, "Waterlevel"); this.pdf.doc.setFillColor("#dae6f0"); this.pdf.doc.circle(x, y + 5, 2, "FD"); this.pdf.doc.setFillColor("#e5ffe5"); this.pdf.doc.circle(x, y + 10, 2, "FD"); - this.pdf.doc.text(x + 3, y + 11, "Navigable Range"); + this.pdf.doc.text(x + padding, y + 11, "Navigable Range"); this.pdf.doc.setDrawColor("#90b4d2"); this.pdf.doc.setFillColor("#90b4d2"); this.pdf.doc.circle(x, y + 5, 0.6, "FD"); - this.pdf.doc.text(x + 3, y + 6, "Prediction"); + this.pdf.doc.text(x + padding, y + 6, "Prediction"); }, getPrintLayout(svgHeight) { return {
--- a/client/src/lib/mixins.js Thu Aug 01 14:10:25 2019 +0200 +++ b/client/src/lib/mixins.js Thu Aug 01 14:22:24 2019 +0200 @@ -174,7 +174,7 @@ svg2pdf(svg, this.pdf.doc, { xOffset: x, yOffset: y, - scale: 0.354 + scale: this.pixel2millimeter(1, 80) }); offScreen.removeChild(svg); },
--- a/pkg/controllers/bottlenecks.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/controllers/bottlenecks.go Thu Aug 01 14:22:24 2019 +0200 @@ -48,7 +48,7 @@ JOIN waterway.fairway_availability fa ON efa.fairway_availability_id = fa.id JOIN waterway.bottlenecks bn - ON fa.bottleneck_id = bn.id + ON fa.bottleneck_id = bn.bottleneck_id WHERE bn.validity @> current_timestamp AND bn.objnam = $1 AND @@ -83,7 +83,7 @@ FROM waterway.gauges_reference_water_levels grwl JOIN waterway.bottlenecks bns ON grwl.location = bns.gauge_location - AND grwl.validity = bns.gauge_validity + AND grwl.validity @> current_timestamp WHERE bns.validity @> current_timestamp AND bns.objnam = $1 AND grwl.depth_reference like 'LDC%'
--- a/pkg/controllers/surveys.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/controllers/surveys.go Thu Aug 01 14:22:24 2019 +0200 @@ -32,9 +32,9 @@ g.objname AS gauge_objname, r.value AS waterlevel_value FROM waterway.bottlenecks AS b + JOIN waterway.sounding_results AS s ON b.bottleneck_id = s.bottleneck_id JOIN waterway.gauges AS g - ON b.gauge_location = g.location AND b.gauge_validity = g.validity - JOIN waterway.sounding_results AS s ON b.bottleneck_id = s.bottleneck_id + ON b.gauge_location = g.location AND s.date_info::timestamptz <@ g.validity LEFT JOIN waterway.gauges_reference_water_levels AS r ON s.depth_reference = r.depth_reference AND g.location = r.location AND g.validity = r.validity
--- a/pkg/imports/agm.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/imports/agm.go Thu Aug 01 14:22:24 2019 +0200 @@ -169,7 +169,6 @@ agmInsertSQL = ` INSERT INTO waterway.gauge_measurements ( location, - validity, measure_date, country_code, sender, @@ -182,12 +181,6 @@ staging_done ) VALUES ( ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int), - COALESCE( - (SELECT validity FROM waterway.gauges - WHERE location - = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) - AND validity @> CAST($6 AS timestamp with time zone)), - tstzrange(NULL, NULL)), $6, $7, $8,
--- a/pkg/imports/bn.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/imports/bn.go Thu Aug 01 14:22:24 2019 +0200 @@ -55,7 +55,6 @@ bottleneck_id, validity, gauge_location, - gauge_validity, objnam, nobjnm, stretch, @@ -67,11 +66,10 @@ limiting, date_info, source_organization -) SELECT +) VALUES ( $1, - validity * $2, -- intersections with gauge validity ranges - location, - validity, + $2::tstzrange, + isrs_fromText($3), $4, $5, (SELECT r FROM r), @@ -87,8 +85,7 @@ $12, $13, $14 - FROM waterway.gauges - WHERE location = isrs_fromText($3) AND validity && $2 +) RETURNING id ` @@ -102,7 +99,6 @@ bottleneck_id, validity, gauge_location, - gauge_validity, objnam, nobjnm, stretch, @@ -114,11 +110,10 @@ limiting, date_info, source_organization -) = ( SELECT +) = ( $2, - validity * $3, -- intersections with gauge validity ranges - location, - validity, + $3::tstzrange, + isrs_fromText($4), $5, $6, (SELECT r FROM r), @@ -134,8 +129,7 @@ $13, $14::timestamptz, $15 - FROM waterway.gauges - WHERE location = isrs_fromText($4) AND validity && $3 ) +) WHERE id=$1 RETURNING id ` @@ -151,7 +145,6 @@ bottleneck_id, validity, gauge_location, - gauge_validity, objnam, nobjnm, stretch, @@ -165,9 +158,8 @@ staging_done ) = ( SELECT $1, - validity * $2, -- intersections with gauge validity ranges - location, - validity, + $2::tstzrange, + isrs_fromText($3), $4, $5, (SELECT r FROM r), @@ -179,8 +171,6 @@ $13::timestamptz, $14, true - FROM waterway.gauges - WHERE location = isrs_fromText($3) AND validity && $2 ) ` @@ -190,14 +180,16 @@ bottleneck_id, validity, staging_done -) = ( SELECT +) = ( $1, - validity * $2, -- intersections with gauge validity ranges + $2::tstzrange, true - FROM waterway.gauges - WHERE location = isrs_fromText($3) AND validity && $2 ) ` + // FIXME: Is this still neede wtih the new simplified historization + // model? My intuition is: no it isn't and should be removed, but we + // should double check before doing so... [sw] + // // Alignment with gauge validity might have generated new entries // for the same time range. Thus, remove the old ones deleteObsoleteBNSQL = ` @@ -532,7 +524,6 @@ err = findMatchingBNStmt.QueryRowContext(ctx, bn.Bottleneck_id, &validity, - bn.Fk_g_fid, ).Scan(&existing_bn_id) switch { case err == sql.ErrNoRows:
--- a/pkg/imports/fa.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/imports/fa.go Thu Aug 01 14:22:24 2019 +0200 @@ -4,13 +4,14 @@ // SPDX-License-Identifier: AGPL-3.0-or-later // License-Filename: LICENSES/AGPL-3.0.txt // -// Copyright (C) 2018 by via donau +// Copyright (C) 2018,2019 by via donau // – Österreichische Wasserstraßen-Gesellschaft mbH // Software engineering by Intevation GmbH // // Author(s): // * Raimund Renkert <raimund.renkert@intevation.de> // * Sascha L. Teichmann <sascha.teichmann@intevation.de> +// * Sascha Wilde <wilde@intevation.de> package imports @@ -67,10 +68,7 @@ source_organization ) VALUES ( $1, - -- Always associate fairway availability data to newest bottleneck - -- version to prevent problems in analysis over longer time periods - (SELECT id FROM waterway.bottlenecks WHERE bottleneck_id = $2 - ORDER BY validity DESC FETCH FIRST ROW ONLY), + $2, $3, $4, $5, @@ -309,6 +307,8 @@ var faID int64 feedback.Info("Found %d fairway availabilities", len(fas)) for _, faRes := range fas { + // FIXME: The following test is propably unneccessary as already + // done by DB constraints... [sw] if !bnIds.contains(faRes.Bottleneck_id) { feedback.Warn("Bottleneck %s not found in database.", faRes.Bottleneck_id) continue
--- a/pkg/imports/gm.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/imports/gm.go Thu Aug 01 14:22:24 2019 +0200 @@ -62,7 +62,6 @@ insertGMSQL = ` INSERT INTO waterway.gauge_measurements ( location, - validity, measure_date, sender, language_code, @@ -75,12 +74,6 @@ staging_done ) VALUES ( ($1, $2, $3, $4, $5), - COALESCE( - (SELECT validity FROM waterway.gauges - WHERE location - = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) - AND validity @> CAST($6 AS timestamp with time zone)), - tstzrange(NULL, NULL)), $6, $7, $8, @@ -99,7 +92,6 @@ insertGPSQL = ` INSERT INTO waterway.gauge_predictions ( location, - validity, measure_date, sender, language_code, @@ -112,12 +104,6 @@ source_organization ) VALUES( ($1, $2, $3, $4, $5), - COALESCE( - (SELECT validity FROM waterway.gauges - WHERE location - = ($1::char(2), $2::char(3), $3::char(5), $4::char(5), $5::int) - AND validity @> CAST($6 AS timestamp with time zone)), - tstzrange(NULL, NULL)), $6, $7, $8,
--- a/pkg/imports/sr.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/imports/sr.go Thu Aug 01 14:22:24 2019 +0200 @@ -116,13 +116,11 @@ insertHullSQL = ` INSERT INTO waterway.sounding_results ( bottleneck_id, - bottleneck_validity, date_info, depth_reference, area ) SELECT bottleneck_id, - validity, $2::date, $3, (SELECT @@ -190,7 +188,7 @@ FROM waterway.gauges_reference_water_levels grwl JOIN waterway.bottlenecks bns ON grwl.location = bns.gauge_location - AND grwl.validity = bns.gauge_validity + AND grwl.validity @> CAST($2 AS timestamptz) WHERE bns.objnam = $1 AND bns.validity @> CAST($2 AS timestamptz) AND grwl.depth_reference like 'LDC%'
--- a/pkg/imports/wg.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/imports/wg.go Thu Aug 01 14:22:24 2019 +0200 @@ -67,7 +67,7 @@ const ( eraseObsoleteGaugesSQL = ` -UPDATE waterway.gauges SET erased = true +UPDATE waterway.gauges SET erased = true, validity = validity - '[now,)' WHERE NOT erased AND (location).country_code = ANY($1) AND isrs_astext(location) <> ALL($2)
--- a/pkg/models/sr.go Thu Aug 01 14:10:25 2019 +0200 +++ b/pkg/models/sr.go Thu Aug 01 14:22:24 2019 +0200 @@ -41,7 +41,7 @@ SELECT EXISTS(SELECT 1 FROM waterway.bottlenecks bn JOIN waterway.gauges g - ON bn.gauge_location = g.location AND bn.gauge_validity = g.validity + ON bn.gauge_location = g.location AND $3::timestamptz <@ g.validity JOIN waterway.gauges_reference_water_levels rl ON g.location = rl.location AND g.validity = rl.validity WHERE bn.objnam = $1 @@ -85,7 +85,8 @@ err = conn.QueryRowContext(ctx, checkDepthReferenceSQL, m.Bottleneck, - m.DepthReference).Scan(&b) + m.DepthReference, + m.Date.Time).Scan(&b) switch { case !b: errs = append(errs,
--- a/schema/auth_tests.sql Thu Aug 01 14:10:25 2019 +0200 +++ b/schema/auth_tests.sql Thu Aug 01 14:22:24 2019 +0200 @@ -75,11 +75,11 @@ PREPARE bn_insert (varchar, geometry(MULTIPOLYGON, 4326)) AS INSERT INTO waterway.bottlenecks ( - gauge_location, gauge_validity, validity, + gauge_location, validity, bottleneck_id, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, date_info, source_organization) SELECT - location, validity, validity, + location, validity, $1, isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, ('AT', 'XXX', '00001', '00000', 2)::isrs),
--- a/schema/gemma.sql Thu Aug 01 14:10:25 2019 +0200 +++ b/schema/gemma.sql Thu Aug 01 14:22:24 2019 +0200 @@ -72,122 +72,134 @@ $$ 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 +-- Trigger function to be used as a constraint trigger to enforce +-- existance of a referenced gauge with intersecting validity. The +-- columns with the referenced gauge isrs code an the validity are +-- given as arguments to the trigger function. +CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS $$ DECLARE - new_bn int; - new_bns int[]; + -- FIXME: I'm using text for the isrs code and cast it on demand. + -- If someone is able to get it to work with isrs or isrs_base as + -- type, feel free to show me how its done... ;-) [sw] + referenced_gauge text; + new_validity tstzrange; 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; - - -- build bottleneck validities from intersections with gauge validities - FOR new_bn IN - INSERT INTO waterway.bottlenecks ( - bottleneck_id, - validity, - gauge_location, - gauge_validity, - objnam, - nobjnm, - stretch, - area, - rb, - lb, - responsible_country, - revisiting_time, - limiting, - date_info, - source_organization, - staging_done - ) SELECT - b.bottleneck_id, - -- Anticipate non-intersecting gauge validities: - b.validity * CASE WHEN g.validity = NEW.validity - THEN NEW.validity ELSE g.validity - NEW.validity END, - b.gauge_location, - g.validity, - b.objnam, - b.nobjnm, - b.stretch, - b.area, - b.rb, - b.lb, - b.responsible_country, - b.revisiting_time, - b.limiting, - b.date_info, - b.source_organization, - b.staging_done - FROM waterway.bottlenecks b JOIN waterway.gauges g - ON b.gauge_location = g.location - WHERE b.gauge_location = NEW.location - AND b.validity && NEW.validity - -- Avoid duplicate intersection results: - AND NOT (b.validity <@ NEW.validity - AND g.validity <> NEW.validity) - ON CONFLICT (bottleneck_id, validity) DO UPDATE SET - -- Associate to new matching gauge version - gauge_validity = EXCLUDED.gauge_validity - RETURNING id - LOOP - new_bns = new_bns || new_bn; - END LOOP; - -- Delete bottleneck versions superseded by new intersections: - DELETE FROM waterway.bottlenecks - WHERE gauge_location = NEW.location - AND validity && NEW.validity - AND id <> ALL(new_bns); + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_gauge + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_validity + USING NEW; + IF EXISTS ( SELECT * FROM waterway.gauges + WHERE location = referenced_gauge::isrs + AND validity && new_validity ) + THEN + RETURN NEW; + ELSE + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('No matching gauge %s found.', + isrs_AsText(referenced_gauge::isrs)), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; END IF; - RETURN NULL; -- ignored END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS +-- The same for objects with a timestamp instead of a validity range. +CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS $$ +DECLARE + -- FIXME: I'm using text for the isrs code and cast it on demand. + -- If someone is able to get it to work with isrs or isrs_base as + -- type, feel free to show me how its done... ;-) [sw] + referenced_gauge text; + new_tstz timestamptz; 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; + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_gauge + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_tstz + USING NEW; + IF EXISTS ( SELECT * FROM waterway.gauges + WHERE location = referenced_gauge::isrs + AND validity @> new_tstz ) + THEN + RETURN NEW; + ELSE + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('No matching gauge %s found.', + isrs_AsText(referenced_gauge::isrs)), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; - -- Always associate fairway availability data to newest bottleneck - -- version to prevent problems in analysis over longer time periods - WITH - bn AS (SELECT id, validity FROM waterway.bottlenecks - WHERE bottleneck_id = NEW.bottleneck_id), - latest AS (SELECT id FROM bn - -- Candidates are past new validity or just inserted/updated - WHERE NOT validity &< NEW.validity OR id = NEW.id - ORDER BY upper(validity) DESC FETCH FIRST ROW ONLY) - UPDATE waterway.fairway_availability - SET bottleneck_id = (SELECT id FROM latest) - WHERE bottleneck_id IN(SELECT id FROM bn EXCEPT SELECT id FROM latest); +-- Trigger function to be used as a constraint trigger to enforce +-- existance of a referenced bottleneck with validity at a given time. +-- The columns with the referenced bottleneck id and the timestamp are +-- given as arguments to the trigger function. +CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS +$$ +DECLARE + referenced_bottleneck_id text; + new_tstz timestamptz; +BEGIN + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_bottleneck_id + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_tstz + USING NEW; + IF EXISTS ( SELECT * FROM waterway.bottlenecks + WHERE bottleneck_id = referenced_bottleneck_id + AND validity @> new_tstz ) + THEN + RETURN NEW; + ELSE + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('No matching bottleneck %s for %s found.', + referenced_bottleneck_id, new_tstz), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; END IF; - RETURN NULL; -- ignored END; $$ LANGUAGE plpgsql; -- Constraint trigger: sounding Results must intersect with the area --- of the bottleneck they belong to. +-- of the bottleneck they belong to. The "xx" at the beginning of the +-- name is to ensure, it is fired last after other triggers. CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NOT st_intersects((SELECT area FROM waterway.bottlenecks - WHERE (bottleneck_id, validity) - =(NEW.bottleneck_id, NEW.bottleneck_validity)), + WHERE bottleneck_id = NEW.bottleneck_id + AND validity @> NEW.date_info::timestamptz), NEW.area) THEN RAISE EXCEPTION @@ -444,9 +456,6 @@ 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, @@ -462,12 +471,7 @@ CREATE TABLE gauge_measurements ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, location isrs NOT NULL, - validity tstzrange NOT NULL, - CONSTRAINT gauge_key - FOREIGN KEY (location, validity) REFERENCES gauges - ON UPDATE CASCADE, measure_date timestamp with time zone NOT NULL, - CHECK (measure_date <@ validity), country_code char(2) NOT NULL REFERENCES countries, sender varchar NOT NULL, -- "from" element from NtS response language_code varchar NOT NULL REFERENCES language_codes, @@ -479,18 +483,16 @@ staging_done boolean NOT NULL DEFAULT false, UNIQUE (measure_date, location, staging_done) ) + CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge + AFTER INSERT OR UPDATE OF location ON gauge_measurements + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') -- For fast retrieval of newest measurement per location: CREATE INDEX gauge_measurements_location_measure_date_desc ON waterway.gauge_measurements (location, measure_date DESC) CREATE TABLE gauge_predictions ( location isrs NOT NULL, - validity tstzrange NOT NULL, - CONSTRAINT gauge_key - FOREIGN KEY (location, validity) REFERENCES gauges - ON UPDATE CASCADE, measure_date timestamp with time zone NOT NULL, - CHECK (measure_date >= lower(validity)), country_code char(2) NOT NULL REFERENCES countries, sender varchar NOT NULL, -- "from" element from NtS response language_code varchar NOT NULL REFERENCES language_codes, @@ -503,6 +505,9 @@ source_organization varchar NOT NULL, -- "originator" from NtS response PRIMARY KEY (measure_date, location, date_issue) ) + CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge + AFTER INSERT OR UPDATE OF location ON gauge_predictions + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') CREATE TABLE waterway_axis ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, @@ -648,11 +653,6 @@ EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) DEFERRABLE INITIALLY DEFERRED, gauge_location isrs NOT NULL, - gauge_validity tstzrange NOT NULL, - CHECK(validity <@ gauge_validity), - CONSTRAINT gauge_key - FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges - ON UPDATE CASCADE, objnam varchar, nobjnm varchar, stretch isrsrange NOT NULL, @@ -675,10 +675,9 @@ source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false ) - -- 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 CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge + AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity') CREATE TABLE bottlenecks_riverbed_materials ( bottleneck_id int NOT NULL REFERENCES bottlenecks(id) @@ -691,14 +690,7 @@ CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar NOT NULL, - bottleneck_validity tstzrange NOT NULL, - CONSTRAINT bottleneck_key - FOREIGN KEY (bottleneck_id, bottleneck_validity) - REFERENCES bottlenecks (bottleneck_id, validity) - ON UPDATE CASCADE, date_info date NOT NULL, - CHECK (tstzrange(date_info::timestamptz, - date_info::timestamptz + '1 d'::interval) && bottleneck_validity), UNIQUE (bottleneck_id, date_info), area geography(POLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), @@ -709,7 +701,11 @@ octree_index bytea, staging_done boolean NOT NULL DEFAULT false ) - CREATE CONSTRAINT TRIGGER sounding_results_in_bn_area + CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck + AFTER INSERT OR UPDATE OF bottleneck_id ON sounding_results + FOR EACH ROW + EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') + CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area AFTER INSERT OR UPDATE ON sounding_results FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area() @@ -728,7 +724,7 @@ CREATE TABLE fairway_availability ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, position_code char(2) REFERENCES position_codes, - bottleneck_id int NOT NULL REFERENCES bottlenecks(id), + bottleneck_id varchar NOT NULL, surdat date NOT NULL, UNIQUE (bottleneck_id, surdat), -- additional_data xml -- Currently not relevant for GEMMA @@ -736,6 +732,18 @@ date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL ) + -- FIXME: From the DRC it is unclear what the exact semantics of + -- surdat and Date_Info ar unclear. Currently we assume that + -- (fk_bn_fid,surdat) has to be unique, but that might be false. + -- Anyway, I will date_info here to check for an matching + -- reference gauge at the bottleneck. The reason for this + -- decision is purely practical (and might be semantically + -- disputable: the bottleneck data in the demo system is not old + -- enough to cover rthe surdat times... + CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck + AFTER INSERT OR UPDATE OF bottleneck_id ON fairway_availability + FOR EACH ROW + EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') CREATE TABLE fa_reference_values ( fairway_availability_id int NOT NULL REFERENCES fairway_availability,
--- a/schema/gemma_tests.sql Thu Aug 01 14:10:25 2019 +0200 +++ b/schema/gemma_tests.sql Thu Aug 01 14:22:24 2019 +0200 @@ -30,40 +30,3 @@ $$, 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 count(*) FROM waterway.bottlenecks GROUP BY bottleneck_id; - $$, - CAST(ARRAY[2,2] AS bigint[]), - 'Bottlenecks have been split to two new matching gauge versions');
--- a/schema/geoserver_views.sql Thu Aug 01 14:10:25 2019 +0200 +++ b/schema/geoserver_views.sql Thu Aug 01 14:22:24 2019 +0200 @@ -121,12 +121,12 @@ g.forecast_accuracy_1d FROM waterway.bottlenecks b LEFT JOIN waterway.gauges_base_view g - ON b.gauge_location = g.location AND b.gauge_validity = g.validity + ON b.gauge_location = g.location AND g.validity @> current_timestamp LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) bottleneck_id, date_info, critical FROM waterway.fairway_availability ORDER BY bottleneck_id, date_info DESC) AS fal - ON b.id = fal.bottleneck_id + ON b.bottleneck_id = fal.bottleneck_id LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) bottleneck_id, max(date_info) AS date_max FROM waterway.sounding_results @@ -218,4 +218,4 @@ ON sd.subtrahend = srs.id JOIN waterway.bottlenecks bn ON srm.bottleneck_id = bn.bottleneck_id - AND srm.bottleneck_validity = bn.validity; + AND srm.date_info::timestamptz <@ bn.validity;
--- a/schema/run_tests.sh Thu Aug 01 14:10:25 2019 +0200 +++ b/schema/run_tests.sh Thu Aug 01 14:22:24 2019 +0200 @@ -5,35 +5,87 @@ # SPDX-License-Identifier: AGPL-3.0-or-later # License-Filename: LICENSES/AGPL-3.0.txt # -# Copyright (C) 2018 by via donau +# Copyright (C) 2018, 2019 by via donau # – Österreichische Wasserstraßen-Gesellschaft mbH # Software engineering by Intevation GmbH # # Author(s): # * Tom Gottfried <tom@intevation.de> -dropdb --if-exists gemma_test +ME=`basename "$0"` +BASEDIR=`dirname "$0"` + +usage() +{ + cat <<EOF +$ME [OPTION]... + +Options: + -d, --db=NAME create (and drop if exists) the database NAME. + Default: "gemma_test" + -p, --port=PORT connect do the postgresql cluster at PORT. + Default is the postgresql standard port 5432 + --help display this help and exit + +EOF +} + +# Defaults: + +db=gemma_test +port=5432 + +# Parse options: -./install-db.sh -d gemma_test -psql -qv ON_ERROR_STOP= -c 'CREATE EXTENSION pgtap' -d gemma_test +OPTS=`getopt \ + -l help,db:,port: \ + -o d:p: -n "$ME" -- "$@"` +[ $? -eq 0 ] || { usage ; exit 1 ; } + +eval set -- "$OPTS" + +while true ; do + case "$1" in + --db|-d) + db="$2" + shift 2 + ;; + --port|-p) + port="$2" + shift 2 + ;; + --help) + { usage ; exit 0 ; } + ;; + --) + shift + break + ;; + esac +done + +dropdb --if-exists -p "$port" "$db" + +./install-db.sh -d "$db" -p "$port" +psql -qv ON_ERROR_STOP= -c 'CREATE EXTENSION pgtap' -d "$db" -p "$port" # Collect test roles to be dropped # Concatenate with dummy role to prevent syntax error if there is no test role -TEST_ROLES=$(psql -qtc \ +TEST_ROLES=$(psql -d "$db" -p "$port" -qtc \ "SELECT concat_ws(',', 'test', string_agg(rolname, ',')) FROM pg_roles WHERE rolname LIKE 'test%'") # Drop test roles, add test data and run tests -psql -qXv ON_ERROR_STOP= -v -d gemma_test \ +psql -qXv ON_ERROR_STOP= -v -d "$db" -p "$port" \ -c 'SET client_min_messages TO WARNING' \ -c "DROP ROLE IF EXISTS $TEST_ROLES" \ - -f tap_tests_data.sql \ - -c "SELECT plan(71 + ( + -f "$BASEDIR"/tap_tests_data.sql \ + -c "SELECT plan(70 + ( SELECT count(*)::int FROM information_schema.tables WHERE table_schema = 'waterway'))" \ - -f gemma_tests.sql \ - -f isrs_tests.sql \ - -f auth_tests.sql \ - -f manage_users_tests.sql \ + -f "$BASEDIR"/gemma_tests.sql \ + -f "$BASEDIR"/isrs_tests.sql \ + -f "$BASEDIR"/auth_tests.sql \ + -f "$BASEDIR"/manage_users_tests.sql \ -c 'SELECT * FROM finish()'
--- a/schema/tap_tests_data.sql Thu Aug 01 14:10:25 2019 +0200 +++ b/schema/tap_tests_data.sql Thu Aug 01 14:22:24 2019 +0200 @@ -57,7 +57,7 @@ current_timestamp, 'testorganization', current_timestamp) - RETURNING location, validity, validity), + RETURNING location, validity), bns AS ( VALUES ( 'testbottleneck1', @@ -75,7 +75,7 @@ 1, 'depth', current_timestamp, 'testorganization', true )) INSERT INTO waterway.bottlenecks ( - gauge_location, gauge_validity, validity, + gauge_location, validity, bottleneck_id, stretch, area, rb, lb, responsible_country, revisiting_time, limiting, date_info, source_organization, staging_done) SELECT * FROM gs, bns;
--- a/schema/update-db.sh Thu Aug 01 14:10:25 2019 +0200 +++ b/schema/update-db.sh Thu Aug 01 14:22:24 2019 +0200 @@ -21,7 +21,7 @@ $ME [OPTION]... Options: - -d, --db=NAME create the database NAME. Default: "gemma" + -d, --db=NAME update the database NAME. Default: "gemma" -p, --port=PORT connect do the postgresql cluster at PORT. Default is the postgresql standard port 5432 --help display this help and exit @@ -98,13 +98,17 @@ current_ver=$( get_version ) -for d in $BASEDIR/updates/* ; do +for d in "$BASEDIR"/updates/* ; do new_ver=$( basename $d ) if [ -d "$d" ] && [ "$new_ver" -gt $current_ver ] ; then echo "Running updates for $new_ver ..." - psql -1qv ON_ERROR_STOP= -p "$port" -d "$db" \ - $(find "$d" -type f -printf ' -f %p') \ + file_args="" + for f in "$d"/* ; do + file_args+=" -f $f" + done + + psql -1qv ON_ERROR_STOP= -p "$port" -d "$db" $file_args \ -c "INSERT INTO gemma_schema_version(version) VALUES ($new_ver)" fi
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1100/01.remove_gauge_validity_refs.sql Thu Aug 01 14:22:24 2019 +0200 @@ -0,0 +1,197 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2019 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha Wilde <sascha.wilde@intevation.de> + +-- +-- CONSTRAINT FUNCTIONS +-- + +-- We still want to ensure, that there is at least a valid gauge at +-- any time of the referencing objects validity. To ensure this we +-- need a trigger constraint: +CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS +$$ +DECLARE + -- FIXME: I'm using text for the isrs code and cast it on demand. + -- If someone is able to get it to work with isrs or isrs_base as + -- type, feel free to show me how its done... ;-) [sw] + referenced_gauge text; + new_validity tstzrange; +BEGIN + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_gauge + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_validity + USING NEW; + IF EXISTS ( SELECT * FROM waterway.gauges + WHERE location = referenced_gauge::isrs + AND validity && new_validity ) + THEN + RETURN NEW; + ELSE + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('No matching gauge %s found.', + isrs_AsText(referenced_gauge::isrs)), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; + +-- The same for objects with a timestamp instead of a validity range. +CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS +$$ +DECLARE + -- FIXME: I'm using text for the isrs code and cast it on demand. + -- If someone is able to get it to work with isrs or isrs_base as + -- type, feel free to show me how its done... ;-) [sw] + referenced_gauge text; + new_tstz timestamptz; +BEGIN + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_gauge + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_tstz + USING NEW; + IF EXISTS ( SELECT * FROM waterway.gauges + WHERE location = referenced_gauge::isrs + AND validity @> new_tstz ) + THEN + RETURN NEW; + ELSE + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('No matching gauge %s for %s found.', + (isrs_AsText(referenced_gauge::isrs)), new_tstz), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; + + +-- +-- BOTTLENECKS +-- + +-- Dynamic version of bottlenecks_geoserver view: +-- Instead of a static reference to a specific gauge data set via +-- primary key (location, validity) we check for a currently +-- valid gauge (for the currently valid bottleneck) at executiuon +-- time. +CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS + SELECT + b.id, + b.bottleneck_id, + b.objnam, + b.nobjnm, + b.stretch, + b.area, + b.rb, + b.lb, + b.responsible_country, + b.revisiting_time, + b.limiting, + b.date_info, + b.source_organization, + g.location AS gauge_isrs_code, + g.objname AS gauge_objname, + g.reference_water_levels, + fal.date_info AS fa_date_info, + fal.critical AS fa_critical, + g.gm_measuredate, + g.gm_waterlevel, + g.gm_n_14d, + srl.date_max, + g.forecast_accuracy_3d, + g.forecast_accuracy_1d + FROM waterway.bottlenecks b + LEFT JOIN waterway.gauges_base_view g + ON b.gauge_location = g.location AND g.validity @> current_timestamp + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, date_info, critical + FROM waterway.fairway_availability + ORDER BY bottleneck_id, date_info DESC) AS fal + ON b.id = fal.bottleneck_id + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, max(date_info) AS date_max + FROM waterway.sounding_results + GROUP BY bottleneck_id + ORDER BY bottleneck_id DESC) AS srl + ON b.bottleneck_id = srl.bottleneck_id + WHERE b.validity @> current_timestamp; + +-- As we resolve the correct gauge data to use on runtime, we drop the +-- hard reference to the gauges vaidity: +ALTER TABLE waterway.bottlenecks DROP IF EXISTS gauge_validity; + +DROP TRIGGER IF EXISTS waterway_bottlenecks_reference_gauge + ON waterway.bottlenecks; +CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge + AFTER INSERT OR UPDATE OF gauge_location ON waterway.bottlenecks + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity'); + + +-- +-- GAUGE MEASUREMENTS +-- + +-- As we resolve the correct gauge data to use on runtime, we drop the +-- hard reference to the gauges vaidity: +ALTER TABLE waterway.gauge_measurements DROP IF EXISTS validity; + +DROP TRIGGER IF EXISTS waterway_gauge_measurements_reference_gauge + ON waterway.gauge_measurements; +CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge + AFTER INSERT OR UPDATE OF location ON waterway.gauge_measurements + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); + +ALTER TABLE waterway.gauge_predictions DROP IF EXISTS validity; + +DROP TRIGGER IF EXISTS waterway_gauge_predictions_reference_gauge + ON waterway.gauge_predictions; +CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge + AFTER INSERT OR UPDATE OF location ON waterway.gauge_predictions + FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date'); + +-- +-- DROP NO LONGER NEEDED TRIGGER FUNCTION +-- + +-- This used to update foreign key references. As these references no +-- longer exist we dont need this magic any more... +DROP TRIGGER IF EXISTS move_referencing ON waterway.gauges; +DROP FUNCTION IF EXISTS move_gauge_referencing(); + + +-- +-- ADDITIONAL NOTES +-- +-- waterway.gauges_reference_water_levels still has a hard foreign key +-- reference to waterway.gauges. As this data actually is part of the +-- gauges data and the seperation in two tables is exclusively due to +-- schema modeling we leave it alone!
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1100/02.remove_bottleneck_validity_refs.sql Thu Aug 01 14:22:24 2019 +0200 @@ -0,0 +1,256 @@ +-- This is Free Software under GNU Affero General Public License v >= 3.0 +-- without warranty, see README.md and license for details. + +-- SPDX-License-Identifier: AGPL-3.0-or-later +-- License-Filename: LICENSES/AGPL-3.0.txt + +-- Copyright (C) 2019 by via donau +-- – Österreichische Wasserstraßen-Gesellschaft mbH +-- Software engineering by Intevation GmbH + +-- Author(s): +-- * Sascha Wilde <sascha.wilde@intevation.de> + +-- +-- CONSTRAINT FUNCTIONS +-- + +-- We still want to ensure, that there is at least a valid bottleneck +-- at any time of the referencing objects validity. To ensure this we +-- need a trigger constraint: +CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS +$$ +DECLARE + referenced_bottleneck_id text; + new_tstz timestamptz; +BEGIN + EXECUTE format('SELECT $1.%I', TG_ARGV[0]) + INTO referenced_bottleneck_id + USING NEW; + EXECUTE format('SELECT $1.%I', TG_ARGV[1]) + INTO new_tstz + USING NEW; + IF EXISTS ( SELECT * FROM waterway.bottlenecks + WHERE bottleneck_id = referenced_bottleneck_id + AND validity @> new_tstz ) + THEN + RETURN NEW; + ELSE + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = format('No matching bottleneck %s for %s found.', + referenced_bottleneck_id, new_tstz), + ERRCODE = 23505, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + COLUMN = TG_ARGV[0], + CONSTRAINT = TG_NAME; + END IF; +END; +$$ +LANGUAGE plpgsql; + +-- Redifine constraint trigger: sounding Results must intersect with +-- the area of the bottleneck they belong to. Bottleneck is +-- determined dynamically via date_info. +CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger +LANGUAGE plpgsql +AS $$ +BEGIN + IF NOT st_intersects((SELECT area + FROM waterway.bottlenecks + WHERE bottleneck_id = NEW.bottleneck_id + AND validity @> NEW.date_info::timestamptz), + NEW.area) + THEN + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = 'Failing row area has no intersection with bottleneck.', + ERRCODE = 23514, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + CONSTRAINT = TG_NAME; + END IF; + RETURN NEW; +END; +$$; + + +-- +-- SOUNDING RESULTS +-- + +-- Dynamic version of sounding_differences geoserver view: Instead of +-- a static reference to a specific bottleneck data set via primary +-- key (id, validity) we check for a bottleneck valid at the time of +-- the survey at executiuon time. +CREATE OR REPLACE VIEW waterway.sounding_differences AS + SELECT + sd.id AS id, + bn.objnam AS objnam, + srm.date_info AS minuend, + srs.date_info AS subtrahend, + sdcl.height AS height, + CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines + FROM caching.sounding_differences sd + JOIN caching.sounding_differences_contour_lines sdcl + ON sd.id = sdcl.sounding_differences_id + JOIN waterway.sounding_results srm + ON sd.minuend = srm.id + JOIN waterway.sounding_results srs + ON sd.subtrahend = srs.id + JOIN waterway.bottlenecks bn + ON srm.bottleneck_id = bn.bottleneck_id + AND srm.date_info::timestamptz <@ bn.validity; + +-- As we resolve the correct gauge data to use on runtime, we drop the +-- hard reference to the bottlenecks vaidity: +ALTER TABLE waterway.sounding_results DROP IF EXISTS bottleneck_validity; + +-- Note, we now use prefixed names, to ensure correct execution order +-- for the triggers... +DROP TRIGGER IF EXISTS a_sounding_results_reference_bottleneck + ON waterway.sounding_results; +CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck + AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results + FOR EACH ROW + EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info'); + +DROP TRIGGER IF EXISTS sounding_results_in_bn_area + ON waterway.sounding_results; +DROP TRIGGER IF EXISTS b_sounding_results_in_bn_area + ON waterway.sounding_results; +CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area + AFTER INSERT OR UPDATE ON waterway.sounding_results + FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area(); + + +-- +-- FAIRWAY AVAILABILITY +-- + +-- The current implementation associates fairway_availability data +-- hard with the internal id of an bottleneck. Lets use the +-- bottleneck_id (official unique id) instead and to the lookup of an +-- matching bottleneck for a specific date on demand. + +-- Dear reader: The gemma schema update scripts are intended to be run +-- only once, which is implemented via schema verioning and the +-- update-db.sh script. None the less it is very helpful to be able +-- to run scripts more than once during development and testing, +-- without harm being done. Thats the reason for the following code +-- to be, like it is: +-- +-- This migration can only be done once, thanks to psql magic we are +-- able to guard it approprieatly. + +SELECT data_type='integer' AS old_fwa_bnid + FROM information_schema.columns + WHERE table_schema = 'waterway' + AND table_name='fairway_availability' + AND column_name='bottleneck_id'; +\gset +\if :old_fwa_bnid + \qecho 'Migrating bottleneck_id column in fairway_availability to text id.' + -- We temporarily keep the old if field for the migration of existing data: + ALTER TABLE waterway.fairway_availability + RENAME COLUMN bottleneck_id TO old_bnid; + ALTER TABLE waterway.fairway_availability + ADD COLUMN bottleneck_id varchar; + + -- Migrate existing data: Let's be ultra permissive and make the + -- migration _before adding the constraint triggger. + UPDATE waterway.fairway_availability AS fwa + SET bottleneck_id = b.bottleneck_id + FROM waterway.bottlenecks b + WHERE b.id = fwa.old_bnid; + ALTER TABLE waterway.fairway_availability + ALTER COLUMN bottleneck_id SET NOT NULL; + + -- Set constraint trigger to make sure a matching BN exists: + -- + -- FIXME: From the DRC it is unclear what the exact semantics of + -- surdat and Date_Info ar unclear. Currently we assume that + -- (fk_bn_fid,surdat) has to be unique, but that might be false. + -- Anyway, I will date_info here to check for an matching + -- reference gauge at the bottleneck. The reason for this + -- decision is purely practical (and might be semantically + -- disputable: the bottleneck data in the demo system is not old + -- enough to cover rthe surdat times... + CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck + AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.fairway_availability + FOR EACH ROW + EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info'); + -- Set NOT NULL constraint for new column + ALTER TABLE waterway.fairway_availability + ALTER COLUMN bottleneck_id SET NOT NULL; + + -- The change also effects the geoserver bottlenecks view, which + -- joined in fairway_availability. We leave the rather fuzzy match + -- (not using any validity time match), as it is unclear what the + -- validity period of fwa data is. + CREATE OR REPLACE VIEW waterway.bottlenecks_geoserver AS + SELECT + b.id, + b.bottleneck_id, + b.objnam, + b.nobjnm, + b.stretch, + b.area, + b.rb, + b.lb, + b.responsible_country, + b.revisiting_time, + b.limiting, + b.date_info, + b.source_organization, + g.location AS gauge_isrs_code, + g.objname AS gauge_objname, + g.reference_water_levels, + fal.date_info AS fa_date_info, + fal.critical AS fa_critical, + g.gm_measuredate, + g.gm_waterlevel, + g.gm_n_14d, + srl.date_max, + g.forecast_accuracy_3d, + g.forecast_accuracy_1d + FROM waterway.bottlenecks b + LEFT JOIN waterway.gauges_base_view g + ON b.gauge_location = g.location AND g.validity @> current_timestamp + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, date_info, critical + FROM waterway.fairway_availability + ORDER BY bottleneck_id, date_info DESC) AS fal + ON b.bottleneck_id = fal.bottleneck_id + LEFT JOIN (SELECT DISTINCT ON (bottleneck_id) + bottleneck_id, max(date_info) AS date_max + FROM waterway.sounding_results + GROUP BY bottleneck_id + ORDER BY bottleneck_id DESC) AS srl + ON b.bottleneck_id = srl.bottleneck_id + WHERE b.validity @> current_timestamp; + + -- Finally dropt the old column + ALTER TABLE waterway.fairway_availability + DROP COLUMN old_bnid; + ALTER TABLE waterway.fairway_availability + ADD CONSTRAINT fairway_availability_bottleneck_id_surdat_key + UNIQUE (bottleneck_id, surdat); +\else + \qecho 'NOTICE: bottleneck_id column in fairway_availability alread migrated.' +\endif + + +-- +-- DROP NO LONGER NEEDED TRIGGER FUNCTION +-- + +-- This used to update foreign key references. As these references no +-- longer exist we dont need this magic any more... +DROP TRIGGER IF EXISTS move_referencing ON waterway.bottlenecks; +DROP FUNCTION IF EXISTS move_bottleneck_referencing();