# HG changeset patch # User Sascha Wilde # Date 1564050799 -7200 # Node ID 0ba3fc89b499233efa3d2d1fe4434dd1e199444c # Parent 6bfe42f886385bc7e967d57d97fb70521aa54a70 Adapted references to bottlenecks from fairway_availability in DB schema. diff -r 6bfe42f88638 -r 0ba3fc89b499 schema/gemma.sql --- a/schema/gemma.sql Wed Jul 24 18:55:09 2019 +0200 +++ b/schema/gemma.sql Thu Jul 25 12:33:19 2019 +0200 @@ -724,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 @@ -735,6 +735,18 @@ CREATE TRIGGER fairway_availability_date_info BEFORE UPDATE ON fairway_availability FOR EACH ROW EXECUTE PROCEDURE update_date_info() + -- 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, diff -r 6bfe42f88638 -r 0ba3fc89b499 schema/geoserver_views.sql --- a/schema/geoserver_views.sql Wed Jul 24 18:55:09 2019 +0200 +++ b/schema/geoserver_views.sql Thu Jul 25 12:33:19 2019 +0200 @@ -126,7 +126,7 @@ 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 diff -r 6bfe42f88638 -r 0ba3fc89b499 schema/updates/1100/02.remove_bottleneck_validity_refs.sql --- a/schema/updates/1100/02.remove_bottleneck_validity_refs.sql Wed Jul 24 18:55:09 2019 +0200 +++ b/schema/updates/1100/02.remove_bottleneck_validity_refs.sql Thu Jul 25 12:33:19 2019 +0200 @@ -130,6 +130,116 @@ -- +-- 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; + -- 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'); + + -- Migrate existing data: + UPDATE waterway.fairway_availability AS fwa + SET bottleneck_id = b.bottleneck_id + FROM waterway.bottlenecks b + WHERE b.id = fwa.old_bnid; + -- 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; +\else + \qecho 'NOTICE: bottleneck_id column in fairway_availability alread migrated.' +\endif + + +-- -- DROP NO LONGER NEEDED TRIGGER FUNCTION --