Mercurial > gemma

comparison schema/gemma.sql @ 4047:8c6bc85db711 historization_ng

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WIP: Remove references to bottleneck validity in DB schema.
author Sascha Wilde <wilde@intevation.de>
date Wed, 24 Jul 2019 16:15:21 +0200
parents 12e3933b2050
children 0ba3fc89b499
comparison
equal deleted inserted replaced
4045:12e3933b2050 4047:8c6bc85db711
149 END IF; 149 END IF;
150 END; 150 END;
151 $$ 151 $$
152 LANGUAGE plpgsql; 152 LANGUAGE plpgsql;
153 153
154 -- REMOVE ME: 154 -- Trigger function to be used as a constraint trigger to enforce
155 CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS 155 -- existance of a referenced bottleneck with validity at a given time.
156 -- The columns with the referenced bottleneck id and the timestamp are
157 -- given as arguments to the trigger function.
158 CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
156 $$ 159 $$
160 DECLARE
161 referenced_bottleneck_id text;
162 new_tstz timestamptz;
157 BEGIN 163 BEGIN
158 -- Avoid unnecessary execution ON UPDATE if validity did not change 164 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
159 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN 165 INTO referenced_bottleneck_id
160 UPDATE waterway.sounding_results 166 USING NEW;
161 SET bottleneck_validity = NEW.validity 167 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
162 WHERE bottleneck_id = NEW.bottleneck_id 168 INTO new_tstz
163 AND CAST(date_info AS timestamptz) <@ NEW.validity; 169 USING NEW;
164 170 IF EXISTS ( SELECT * FROM waterway.bottlenecks
165 -- Always associate fairway availability data to newest bottleneck 171 WHERE bottleneck_id = referenced_bottleneck_id
166 -- version to prevent problems in analysis over longer time periods 172 AND validity @> new_tstz )
167 WITH 173 THEN
168 bn AS (SELECT id, validity FROM waterway.bottlenecks 174 RETURN NEW;
169 WHERE bottleneck_id = NEW.bottleneck_id), 175 ELSE
170 latest AS (SELECT id FROM bn 176 RAISE EXCEPTION
171 -- Candidates are past new validity or just inserted/updated 177 'new row for relation "%" violates constraint trigger "%"',
172 WHERE NOT validity &< NEW.validity OR id = NEW.id 178 TG_TABLE_NAME, TG_NAME
173 ORDER BY upper(validity) DESC FETCH FIRST ROW ONLY) 179 USING
174 UPDATE waterway.fairway_availability 180 DETAIL = format('No matching bottleneck %s for %s found.',
175 SET bottleneck_id = (SELECT id FROM latest) 181 referenced_bottleneck_id, new_tstz),
176 WHERE bottleneck_id IN(SELECT id FROM bn EXCEPT SELECT id FROM latest); 182 ERRCODE = 23505,
183 SCHEMA = TG_TABLE_SCHEMA,
184 TABLE = TG_TABLE_NAME,
185 COLUMN = TG_ARGV[0],
186 CONSTRAINT = TG_NAME;
177 END IF; 187 END IF;
178 RETURN NULL; -- ignored
179 END; 188 END;
180 $$ 189 $$
181 LANGUAGE plpgsql; 190 LANGUAGE plpgsql;
182 191
183 -- Constraint trigger: sounding Results must intersect with the area 192 -- Constraint trigger: sounding Results must intersect with the area
184 -- of the bottleneck they belong to. 193 -- of the bottleneck they belong to. The "xx" at the beginning of the
194 -- name is to ensure, it is fired last after other triggers.
185 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger 195 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
186 LANGUAGE plpgsql 196 LANGUAGE plpgsql
187 AS $$ 197 AS $$
188 BEGIN 198 BEGIN
189 IF NOT st_intersects((SELECT area 199 IF NOT st_intersects((SELECT area
190 FROM waterway.bottlenecks 200 FROM waterway.bottlenecks
191 WHERE (bottleneck_id, validity) 201 WHERE bottleneck_id = NEW.bottleneck_id
192 =(NEW.bottleneck_id, NEW.bottleneck_validity)), 202 AND validity @> NEW.date_info::timestamptz),
193 NEW.area) 203 NEW.area)
194 THEN 204 THEN
195 RAISE EXCEPTION 205 RAISE EXCEPTION
196 'new row for relation "%" violates constraint trigger "%"', 206 'new row for relation "%" violates constraint trigger "%"',
197 TG_TABLE_NAME, TG_NAME 207 TG_TABLE_NAME, TG_NAME
666 staging_done boolean NOT NULL DEFAULT false 676 staging_done boolean NOT NULL DEFAULT false
667 ) 677 )
668 CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge 678 CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
669 AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks 679 AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
670 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity') 680 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity')
671 -- Associate referencing objects to matching bottleneck version
672 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
673 ON bottlenecks FOR EACH ROW
674 EXECUTE FUNCTION move_bottleneck_referencing()
675 681
676 CREATE TABLE bottlenecks_riverbed_materials ( 682 CREATE TABLE bottlenecks_riverbed_materials (
677 bottleneck_id int NOT NULL REFERENCES bottlenecks(id) 683 bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
678 ON DELETE CASCADE, 684 ON DELETE CASCADE,
679 riverbed varchar NOT NULL REFERENCES riverbed_materials, 685 riverbed varchar NOT NULL REFERENCES riverbed_materials,
682 ) 688 )
683 689
684 CREATE TABLE sounding_results ( 690 CREATE TABLE sounding_results (
685 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 691 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
686 bottleneck_id varchar NOT NULL, 692 bottleneck_id varchar NOT NULL,
687 bottleneck_validity tstzrange NOT NULL,
688 CONSTRAINT bottleneck_key
689 FOREIGN KEY (bottleneck_id, bottleneck_validity)
690 REFERENCES bottlenecks (bottleneck_id, validity)
691 ON UPDATE CASCADE,
692 date_info date NOT NULL, 693 date_info date NOT NULL,
693 CHECK (tstzrange(date_info::timestamptz,
694 date_info::timestamptz + '1 d'::interval) && bottleneck_validity),
695 UNIQUE (bottleneck_id, date_info), 694 UNIQUE (bottleneck_id, date_info),
696 area geography(POLYGON, 4326) NOT NULL 695 area geography(POLYGON, 4326) NOT NULL
697 CHECK(ST_IsValid(CAST(area AS geometry))), 696 CHECK(ST_IsValid(CAST(area AS geometry))),
698 surtyp varchar REFERENCES survey_types, 697 surtyp varchar REFERENCES survey_types,
699 coverage varchar REFERENCES coverage_types, 698 coverage varchar REFERENCES coverage_types,
700 depth_reference varchar NOT NULL, -- REFERENCES depth_references, 699 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
701 octree_checksum varchar, 700 octree_checksum varchar,
702 octree_index bytea, 701 octree_index bytea,
703 staging_done boolean NOT NULL DEFAULT false 702 staging_done boolean NOT NULL DEFAULT false
704 ) 703 )
705 CREATE CONSTRAINT TRIGGER sounding_results_in_bn_area 704 CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
706 AFTER INSERT OR UPDATE ON sounding_results 705 AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results
707 FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area() 706 FOR EACH ROW
707 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info');
708 CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
709 AFTER INSERT OR UPDATE ON waterway.sounding_results
710 FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area();
708 711
709 CREATE TABLE sounding_results_contour_lines ( 712 CREATE TABLE sounding_results_contour_lines (
710 sounding_result_id int NOT NULL REFERENCES sounding_results 713 sounding_result_id int NOT NULL REFERENCES sounding_results
711 ON DELETE CASCADE, 714 ON DELETE CASCADE,
712 height numeric NOT NULL, 715 height numeric NOT NULL,