# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1558536253 -7200
# Node ID 45a629a3a8b8e389201de8d54a14bdc25f1187b6
# Parent  1876b204b004ad57fa6c9e5fc7e7ca9bc63d5c08
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.

diff -r 1876b204b004 -r 45a629a3a8b8 pkg/imports/agm.go
--- a/pkg/imports/agm.go	Wed May 22 16:30:43 2019 +0200
+++ b/pkg/imports/agm.go	Wed May 22 16:44:13 2019 +0200
@@ -75,7 +75,7 @@
   SELECT o.id AS id
   FROM waterway.gauge_measurements o
   JOIN waterway.gauge_measurements n
-    USING (location, validity, measure_date)
+    USING (location, measure_date)
     WHERE n.id IN (SELECT key FROM staged)
 	  AND o.id NOT IN (SELECT key FROM staged)
 )
diff -r 1876b204b004 -r 45a629a3a8b8 schema/gemma.sql
--- 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 (