Mercurial > gemma
view schema/updates/1100/02.remove_bottleneck_validity_refs.sql @ 5721:0500d76e074b uploadwg
Fixed eraseObsoleteGaugesSQL by reintroducing matching on users CC.
The condition was removed when introducing file upload, but that leads
to too many gauges being removed.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Fri, 19 Apr 2024 16:52:14 +0200 |
parents | 30a567b1cc2e |
children |
line wrap: on
line source
-- 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();