Mercurial > gemma
comparison schema/gemma.sql @ 3666:db87f34805fb
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 |