Mercurial > gemma
changeset 4044:f42f7f7eb81f historization_ng
WIP: Finished remove references to gauge validity in DB schema.
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Wed, 24 Jul 2019 13:00:37 +0200 |
| parents | fbd7c012f10c |
| children | 12e3933b2050 |
| files | schema/gemma.sql schema/updates/1100/01.remove_gauge_validity_refs.sql |
| diffstat | 2 files changed, 79 insertions(+), 132 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Jul 24 11:16:38 2019 +0200 +++ b/schema/gemma.sql Wed Jul 24 13:00:37 2019 +0200 @@ -113,84 +113,7 @@ $$ 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 -$$ -DECLARE - new_bn int; - new_bns int[]; -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); - END IF; - RETURN NULL; -- ignored -END; -$$ -LANGUAGE plpgsql; - +-- REMOVE ME: CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS $$ BEGIN @@ -485,9 +408,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, @@ -503,12 +423,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, @@ -526,12 +441,7 @@ 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,
--- a/schema/updates/1100/01.remove_gauge_validity_refs.sql Wed Jul 24 11:16:38 2019 +0200 +++ b/schema/updates/1100/01.remove_gauge_validity_refs.sql Wed Jul 24 13:00:37 2019 +0200 @@ -11,6 +11,54 @@ -- 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; + + +-- +-- BOTTLENECKS +-- -- Dynamic version of bottlenecks_geoserver view: -- Instead of a static reference to a specific gauge data set via @@ -63,48 +111,37 @@ -- hard reference to the gauges vaidity: ALTER TABLE waterway.bottlenecks DROP IF EXISTS gauge_validity; --- We still want to ensure, that there is at least a valid gauge at --- any time of the bottleneck 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; - 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') + 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; +ALTER TABLE waterway.gauge_predictions DROP IF EXISTS validity; + + +-- +-- 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!