Mercurial > gemma

diff schema/gemma.sql @ 3656:2a079d0a71c1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Ensure sounding results are associated to matching bottleneck version
author Tom Gottfried <tom@intevation.de>
date Thu, 13 Jun 2019 19:13:42 +0200
parents 0ec5c8ec1e44
children 29ef6d41e4af
line wrap: on
line diff
--- a/schema/gemma.sql	Thu Jun 13 11:20:17 2019 +0200
+++ b/schema/gemma.sql	Thu Jun 13 19:13:42 2019 +0200
@@ -489,6 +489,7 @@
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
         bottleneck_id varchar NOT NULL,
         validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
+        UNIQUE (bottleneck_id, validity),
         EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
             DEFERRABLE INITIALLY DEFERRED,
         gauge_location isrs NOT NULL,
@@ -535,8 +536,15 @@
 
     CREATE TABLE sounding_results (
         id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-        bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
+        bottleneck_id varchar NOT NULL,
+        bottleneck_validity tstzrange NOT NULL,
+        CONSTRAINT bottleneck_key
+            FOREIGN KEY (bottleneck_id, bottleneck_validity)
+                REFERENCES bottlenecks (bottleneck_id, validity)
+                ON UPDATE CASCADE,
         date_info date NOT NULL,
+        CHECK (tstzrange(date_info::timestamptz,
+            date_info::timestamptz + '1 d'::interval) && bottleneck_validity),
         UNIQUE (bottleneck_id, date_info),
         area geography(POLYGON, 4326) NOT NULL
             CHECK(ST_IsValid(CAST(area AS geometry))),