Mercurial > gemma

changeset 1783:8fd132b9cdbd

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Fri, 11 Jan 2019 18:32:59 +0100
parents 30c76168e6d0
children 724758455a4e
files schema/gemma.sql
diffstat 1 files changed, 4 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- 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