# HG changeset patch
# User Sascha L. Teichmann <sascha.teichmann@intevation.de>
# Date 1547227979 -3600
# Node ID 8fd132b9cdbd59223ce89ec4760bddf02a62aa4d
# Parent  30c76168e6d0297d178b4b1ad788ed0abeb78547
Gauge measurements: Add an unique constraint so there could only
two pairs of fk_gauge_id/date in the table. One staged one not staged.
TODO: Simplify stage done SQL in approved import.

diff -r 30c76168e6d0 -r 8fd132b9cdbd schema/gemma.sql
--- a/schema/gemma.sql	Fri Jan 11 17:43:26 2019 +0100
+++ b/schema/gemma.sql	Fri Jan 11 18:32:59 2019 +0100
@@ -268,7 +268,6 @@
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         fk_gauge_id isrs NOT NULL REFERENCES gauges,
         measure_date timestamp with time zone NOT NULL,
-        -- PRIMARY KEY (fk_gauge_id, measure_date),
         country_code char(2) NOT NULL REFERENCES countries,
         -- TODO: add relations to stuff provided as enumerations
         sender varchar NOT NULL, -- "from" attribute from DRC
@@ -285,7 +284,10 @@
         --- TODO: Add a double range type for checking?
         date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
         source_organization varchar NOT NULL, -- "originator"
-        staging_done boolean NOT NULL DEFAULT false
+        staging_done boolean NOT NULL DEFAULT false,
+        -- So we can have a staged and
+        -- a non-staged fk_gauge_id/measure_date pair.
+        UNIQUE (fk_gauge_id, measure_date, staging_done)
     )
     CREATE TRIGGER gauge_measurements_date_info
         BEFORE UPDATE ON gauge_measurements