Mercurial > gemma

diff schema/gemma.sql @ 3389:45a629a3a8b8

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix constraints on relationship between gauges and measurements/predictions measure_date is the time of measurement, not date_issue. There can be only one measurement at a time for a gauge location, independently of gauge versions with differing validity. The 'measure_date' of a prediction can well be beyond the validity of the gauge, since a new gauge version might appear within the time range of the forecast. There can be only one prediction at a time per date of issue, independently of gauge versions with differing validity.
author Tom Gottfried <tom@intevation.de>
date Wed, 22 May 2019 16:44:13 +0200
parents d9eda49a52f4
children c04b1409a596
line wrap: on
line diff
--- a/schema/gemma.sql	Wed May 22 16:30:43 2019 +0200
+++ b/schema/gemma.sql	Wed May 22 16:44:13 2019 +0200
@@ -314,17 +314,17 @@
         CONSTRAINT gauge_key
             FOREIGN KEY (location, validity) REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
+        CHECK (measure_date <@ validity),
         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 CHECK (date_issue <@ validity),
+        date_issue timestamp with time zone NOT NULL,
         reference_code varchar(4) NOT NULL REFERENCES depth_references,
         water_level double precision NOT NULL,
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL, -- "originator" from NtS response
         staging_done boolean NOT NULL DEFAULT false,
-        UNIQUE (location, validity, measure_date, staging_done)
+        UNIQUE (location, measure_date, staging_done)
     )
 
     CREATE TABLE gauge_predictions (
@@ -333,18 +333,18 @@
         CONSTRAINT gauge_key
             FOREIGN KEY (location, validity) REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
+        CHECK (measure_date >= lower(validity)),
         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 CHECK (date_issue <@ validity),
+        date_issue timestamp with time zone NOT NULL,
         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,
         source_organization varchar NOT NULL, -- "originator" from NtS response
-        PRIMARY KEY (location, validity, measure_date, date_issue)
+        PRIMARY KEY (location, measure_date, date_issue)
     )
 
     CREATE TABLE waterway_axis (