Mercurial > gemma

comparison schema/gemma.sql @ 3666:db87f34805fb

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Align bottleneck validity at gauges Ensuring the validity of a bottleneck version is always contained by the validity of the referenced gauge version allows to reliably determine matching reference values of the gauge at a point in time. Since this implies that a bottleneck version might be cut into more than one time ranges, the concept of having only one non-erased version is no longer applicable and replaced by using the 'current' version of a bottleneck. Fairway availability data are always kept with the 'current' bottleneck version to have them at hand alltogether for analyses over longer time ranges.
author Tom Gottfried <tom@intevation.de>
date Sat, 15 Jun 2019 14:36:50 +0200
parents 29ef6d41e4af
children 433bad131e5c
comparison
equal deleted inserted replaced
3665:29ef6d41e4af 3666:db87f34805fb
74 74
75 -- Trigger functions to be used as statement-level AFTER triggers, 75 -- Trigger functions to be used as statement-level AFTER triggers,
76 -- associating time-based referencing objects to matching version 76 -- associating time-based referencing objects to matching version
77 CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS 77 CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS
78 $$ 78 $$
79 DECLARE
80 new_bn int;
81 new_bns int[];
79 BEGIN 82 BEGIN
80 -- Avoid unnecessary execution ON UPDATE if validity did not change 83 -- Avoid unnecessary execution ON UPDATE if validity did not change
81 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN 84 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
82 UPDATE waterway.gauge_measurements 85 UPDATE waterway.gauge_measurements
83 SET validity = NEW.validity 86 SET validity = NEW.validity
84 WHERE location = NEW.location 87 WHERE location = NEW.location
85 AND measure_date <@ NEW.validity; 88 AND measure_date <@ NEW.validity;
86 89
87 UPDATE waterway.bottlenecks 90 -- build bottleneck validities from intersections with gauge validities
88 SET gauge_validity = NEW.validity 91 FOR new_bn IN
92 INSERT INTO waterway.bottlenecks (
93 bottleneck_id,
94 validity,
95 gauge_location,
96 gauge_validity,
97 objnam,
98 nobjnm,
99 stretch,
100 area,
101 rb,
102 lb,
103 responsible_country,
104 revisiting_time,
105 limiting,
106 date_info,
107 source_organization,
108 staging_done
109 ) SELECT
110 b.bottleneck_id,
111 -- Anticipate non-intersecting gauge validities:
112 b.validity * CASE WHEN g.validity = NEW.validity
113 THEN NEW.validity ELSE g.validity - NEW.validity END,
114 b.gauge_location,
115 g.validity,
116 b.objnam,
117 b.nobjnm,
118 b.stretch,
119 b.area,
120 b.rb,
121 b.lb,
122 b.responsible_country,
123 b.revisiting_time,
124 b.limiting,
125 b.date_info,
126 b.source_organization,
127 b.staging_done
128 FROM waterway.bottlenecks b JOIN waterway.gauges g
129 ON b.gauge_location = g.location
130 WHERE b.gauge_location = NEW.location
131 AND b.validity && NEW.validity
132 -- Avoid duplicate intersection results:
133 AND NOT (b.validity <@ NEW.validity
134 AND g.validity <> NEW.validity)
135 ON CONFLICT (bottleneck_id, validity) DO UPDATE SET
136 -- Associate to new matching gauge version
137 gauge_validity = EXCLUDED.gauge_validity
138 RETURNING id
139 LOOP
140 new_bns = new_bns || new_bn;
141 END LOOP;
142 -- Delete bottleneck versions superseded by new intersections:
143 DELETE FROM waterway.bottlenecks
89 WHERE gauge_location = NEW.location 144 WHERE gauge_location = NEW.location
90 AND lower(validity) <@ NEW.validity; 145 AND validity && NEW.validity
146 AND id <> ALL(new_bns);
91 END IF; 147 END IF;
92 RETURN NULL; -- ignored 148 RETURN NULL; -- ignored
93 END; 149 END;
94 $$ 150 $$
95 LANGUAGE plpgsql; 151 LANGUAGE plpgsql;
101 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN 157 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN
102 UPDATE waterway.sounding_results 158 UPDATE waterway.sounding_results
103 SET bottleneck_validity = NEW.validity 159 SET bottleneck_validity = NEW.validity
104 WHERE bottleneck_id = NEW.bottleneck_id 160 WHERE bottleneck_id = NEW.bottleneck_id
105 AND CAST(date_info AS timestamptz) <@ NEW.validity; 161 AND CAST(date_info AS timestamptz) <@ NEW.validity;
162
163 -- Always associate fairway availability data to newest bottleneck
164 -- version to prevent problems in analysis over longer time periods
165 WITH
166 bn AS (SELECT id, validity FROM waterway.bottlenecks
167 WHERE bottleneck_id = NEW.bottleneck_id),
168 latest AS (SELECT id FROM bn
169 -- Candidates are past new validity or just inserted/updated
170 WHERE NOT validity &< NEW.validity OR id = NEW.id
171 ORDER BY upper(validity) DESC FETCH FIRST ROW ONLY)
172 UPDATE waterway.fairway_availability
173 SET bottleneck_id = (SELECT id FROM latest)
174 WHERE bottleneck_id IN(SELECT id FROM bn EXCEPT SELECT id FROM latest);
106 END IF; 175 END IF;
107 RETURN NULL; -- ignored 176 RETURN NULL; -- ignored
108 END; 177 END;
109 $$ 178 $$
110 LANGUAGE plpgsql; 179 LANGUAGE plpgsql;
532 UNIQUE (bottleneck_id, validity), 601 UNIQUE (bottleneck_id, validity),
533 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) 602 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
534 DEFERRABLE INITIALLY DEFERRED, 603 DEFERRABLE INITIALLY DEFERRED,
535 gauge_location isrs NOT NULL, 604 gauge_location isrs NOT NULL,
536 gauge_validity tstzrange NOT NULL, 605 gauge_validity tstzrange NOT NULL,
537 CHECK(lower(validity) <@ gauge_validity), 606 CHECK(validity <@ gauge_validity),
538 CONSTRAINT gauge_key 607 CONSTRAINT gauge_key
539 FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges 608 FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
540 ON UPDATE CASCADE, 609 ON UPDATE CASCADE,
541 objnam varchar, 610 objnam varchar,
542 nobjnm varchar, 611 nobjnm varchar,
556 -- different model approach? 625 -- different model approach?
557 -- depth_reference char(3) NOT NULL REFERENCES depth_references, 626 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
558 -- XXX: Also an attribut of sounding result? 627 -- XXX: Also an attribut of sounding result?
559 date_info timestamp with time zone NOT NULL, 628 date_info timestamp with time zone NOT NULL,
560 source_organization varchar NOT NULL, 629 source_organization varchar NOT NULL,
561 erased boolean NOT NULL DEFAULT false,
562 staging_done boolean NOT NULL DEFAULT false 630 staging_done boolean NOT NULL DEFAULT false
563 ) 631 )
564 -- Allow only one non-erased entry per bottleneck
565 CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint
566 ON bottlenecks (bottleneck_id)
567 WHERE NOT erased
568 -- Associate referencing objects to matching bottleneck version 632 -- Associate referencing objects to matching bottleneck version
569 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity 633 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
570 ON bottlenecks FOR EACH ROW 634 ON bottlenecks FOR EACH ROW
571 EXECUTE FUNCTION move_bottleneck_referencing() 635 EXECUTE FUNCTION move_bottleneck_referencing()
572 636