Mercurial > gemma
diff schema/gemma.sql @ 3302:ec6163c6687d
'Historicise' gauges on import
Gauge data sets will be updated or a new version will be inserted
depending on temporal validity and a timestamp marking the last
update in the RIS-Index of a data set. The trigger on date_info is
removed because the value is actually an attribut coming from the
RIS-Index.
Gauge measurements and predictions are associated to the version with
matching temporal validity. Bottlenecks are always associated to the
actual version of the gauge, although this might change as soon as
bottlenecks are 'historicised', too.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 16 May 2019 18:41:43 +0200 |
parents | 831193935739 |
children | b90b17d0b5a9 |
line wrap: on
line diff
--- a/schema/gemma.sql Thu May 16 17:22:33 2019 +0200 +++ b/schema/gemma.sql Thu May 16 18:41:43 2019 +0200 @@ -273,7 +273,7 @@ CREATE TABLE gauges ( - location isrs PRIMARY KEY CHECK( + location isrs CHECK( (location).orc SIMILAR TO 'G[[:digit:]]{4}' AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), objname varchar NOT NULL, @@ -281,56 +281,70 @@ applicability_from_km int8, applicability_to_km int8, validity tstzrange, - -- pasted text from a more general specification is given - -- (a gauge is not a berth!) - -- TODO: Ranges need a joint exclusion constaint to prevent overlaps? zero_point double precision NOT NULL, geodref varchar, - date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, - source_organization varchar + date_info timestamp with time zone NOT NULL, + source_organization varchar, + lastupdate timestamp with time zone NOT NULL, + -- entry removed from external data source (RIS-Index)/historicised: + erased boolean NOT NULL DEFAULT false, + PRIMARY KEY (location, validity), + EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&) ) - CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges - FOR EACH ROW EXECUTE PROCEDURE update_date_info() + -- Allow only one non-erased entry per location + CREATE UNIQUE INDEX gauges_erased_unique_constraint + ON gauges (location) + WHERE NOT erased CREATE TABLE gauges_reference_water_levels ( - gauge_id isrs NOT NULL REFERENCES gauges, + location isrs NOT NULL, + validity tstzrange NOT NULL, + FOREIGN KEY (location, validity) REFERENCES gauges, -- Omit foreign key constraint to be able to store not NtS-compliant -- names, too: depth_reference varchar NOT NULL, -- REFERENCES depth_references, - PRIMARY KEY (gauge_id, depth_reference), + PRIMARY KEY (location, validity, depth_reference), value int NOT NULL ) CREATE TABLE gauge_measurements ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, - fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges, + location isrs NOT NULL, + validity tstzrange NOT NULL, + CONSTRAINT gauge_key + FOREIGN KEY (location, validity) REFERENCES gauges, measure_date timestamp with time zone NOT NULL, 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, - date_issue timestamp with time zone NOT NULL, + date_issue timestamp with time zone + NOT NULL CHECK (date_issue <@ validity), reference_code varchar(4) NOT NULL REFERENCES depth_references, water_level double precision NOT NULL, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, -- "originator" from NtS response staging_done boolean NOT NULL DEFAULT false, - UNIQUE (fk_gauge_id, measure_date, staging_done) + UNIQUE (location, validity, measure_date, staging_done) ) CREATE TABLE gauge_predictions ( - fk_gauge_id isrs NOT NULL CONSTRAINT gauge_key REFERENCES gauges, + location isrs NOT NULL, + validity tstzrange NOT NULL, + CONSTRAINT gauge_key + FOREIGN KEY (location, validity) REFERENCES gauges, measure_date timestamp with time zone NOT NULL, 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, - date_issue timestamp with time zone NOT NULL, + date_issue timestamp with time zone + NOT NULL CHECK (date_issue <@ validity), reference_code varchar(4) NOT NULL REFERENCES depth_references, water_level double precision NOT NULL, conf_interval numrange CHECK (conf_interval @> CAST(water_level AS numeric)), date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, -- "originator" from NtS response - PRIMARY KEY (fk_gauge_id, measure_date, date_issue) + PRIMARY KEY (location, validity, measure_date, date_issue) ) CREATE TABLE waterway_axis ( @@ -474,7 +488,9 @@ CREATE TABLE bottlenecks ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar UNIQUE NOT NULL, - fk_g_fid isrs NOT NULL REFERENCES gauges, + gauge_location isrs NOT NULL, + gauge_validity tstzrange NOT NULL, + FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges, -- XXX: DRC references "ch. 3.1.1", which does not exist in document. objnam varchar, nobjnm varchar,