Mercurial > gemma

comparison schema/gemma.sql @ 3665:29ef6d41e4af

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use database triggers to move referencing objects to new versions Needs fewer database round-trips and is more convenient especially if more than two levels in the object hierarchy have to be handled.
author Tom Gottfried <tom@intevation.de>
date Sat, 15 Jun 2019 09:24:28 +0200
parents 2a079d0a71c1
children db87f34805fb
comparison
equal deleted inserted replaced
3664:58508f50d192 3665:29ef6d41e4af
70 RETURN NEW; 70 RETURN NEW;
71 END; 71 END;
72 $$ 72 $$
73 LANGUAGE plpgsql; 73 LANGUAGE plpgsql;
74 74
75 -- Trigger functions to be used as statement-level AFTER triggers,
76 -- associating time-based referencing objects to matching version
77 CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS
78 $$
79 BEGIN
80 -- Avoid unnecessary execution ON UPDATE if validity did not change
81 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
82 UPDATE waterway.gauge_measurements
83 SET validity = NEW.validity
84 WHERE location = NEW.location
85 AND measure_date <@ NEW.validity;
86
87 UPDATE waterway.bottlenecks
88 SET gauge_validity = NEW.validity
89 WHERE gauge_location = NEW.location
90 AND lower(validity) <@ NEW.validity;
91 END IF;
92 RETURN NULL; -- ignored
93 END;
94 $$
95 LANGUAGE plpgsql;
96
97 CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS
98 $$
99 BEGIN
100 -- Avoid unnecessary execution ON UPDATE if validity did not change
101 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
102 UPDATE waterway.sounding_results
103 SET bottleneck_validity = NEW.validity
104 WHERE bottleneck_id = NEW.bottleneck_id
105 AND CAST(date_info AS timestamptz) <@ NEW.validity;
106 END IF;
107 RETURN NULL; -- ignored
108 END;
109 $$
110 LANGUAGE plpgsql;
111
75 -- 112 --
76 -- GEMMA data 113 -- GEMMA data
77 -- 114 --
78 115
79 -- Namespace not to be accessed directly by any user 116 -- Namespace not to be accessed directly by any user
294 ) 331 )
295 -- Allow only one non-erased entry per location 332 -- Allow only one non-erased entry per location
296 CREATE UNIQUE INDEX gauges_erased_unique_constraint 333 CREATE UNIQUE INDEX gauges_erased_unique_constraint
297 ON gauges (location) 334 ON gauges (location)
298 WHERE NOT erased 335 WHERE NOT erased
336 -- Associate referencing objects to matching gauge version
337 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
338 ON gauges FOR EACH ROW EXECUTE FUNCTION move_gauge_referencing()
299 339
300 CREATE TABLE gauges_reference_water_levels ( 340 CREATE TABLE gauges_reference_water_levels (
301 location isrs NOT NULL, 341 location isrs NOT NULL,
302 validity tstzrange NOT NULL, 342 validity tstzrange NOT NULL,
303 FOREIGN KEY (location, validity) REFERENCES gauges ON UPDATE CASCADE, 343 FOREIGN KEY (location, validity) REFERENCES gauges ON UPDATE CASCADE,
523 ) 563 )
524 -- Allow only one non-erased entry per bottleneck 564 -- Allow only one non-erased entry per bottleneck
525 CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint 565 CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint
526 ON bottlenecks (bottleneck_id) 566 ON bottlenecks (bottleneck_id)
527 WHERE NOT erased 567 WHERE NOT erased
568 -- Associate referencing objects to matching bottleneck version
569 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
570 ON bottlenecks FOR EACH ROW
571 EXECUTE FUNCTION move_bottleneck_referencing()
528 572
529 CREATE TABLE bottlenecks_riverbed_materials ( 573 CREATE TABLE bottlenecks_riverbed_materials (
530 bottleneck_id int NOT NULL REFERENCES bottlenecks(id) 574 bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
531 ON DELETE CASCADE, 575 ON DELETE CASCADE,
532 riverbed varchar NOT NULL REFERENCES riverbed_materials, 576 riverbed varchar NOT NULL REFERENCES riverbed_materials,