Mercurial > gemma
changeset 1783:8fd132b9cdbd
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