# HG changeset patch
# User Sascha Wilde <wilde@intevation.de>
# Date 1564485287 -7200
# Node ID 692aba3e8b85ee4df72b11ce8ab45eae393479ea
# Parent  861760675497e196d69742c2e7604a7e7da03704
Change constraints for bottlenecks to include staging_done flag.

This will allow us to stage not only new bottlenecks but also updates.

diff -r 861760675497 -r 692aba3e8b85 schema/gemma.sql
--- a/schema/gemma.sql	Tue Jul 30 11:30:31 2019 +0200
+++ b/schema/gemma.sql	Tue Jul 30 13:14:47 2019 +0200
@@ -649,9 +649,6 @@
         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,
         objnam varchar,
         nobjnm varchar,
@@ -673,7 +670,12 @@
         -- XXX: Also an attribut of sounding result?
         date_info timestamp with time zone NOT NULL,
         source_organization varchar NOT NULL,
-        staging_done boolean NOT NULL DEFAULT false
+        staging_done boolean NOT NULL DEFAULT false,
+        UNIQUE (bottleneck_id, validity, staging_done),
+        EXCLUDE USING GiST (bottleneck_id WITH =,
+                            validity WITH &&,
+                            CAST(staging_done AS int) WITH =)
+            DEFERRABLE INITIALLY DEFERRED
     )
     CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
         AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
diff -r 861760675497 -r 692aba3e8b85 schema/updates/1101/01.bn_constraint.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1101/01.bn_constraint.sql	Tue Jul 30 13:14:47 2019 +0200
@@ -0,0 +1,14 @@
+ALTER TABLE waterway.bottlenecks
+    DROP CONSTRAINT IF EXISTS bottlenecks_bottleneck_id_validity_key,
+    DROP CONSTRAINT IF EXISTS bottlenecks_bottleneck_id_validity_excl,
+    DROP CONSTRAINT IF EXISTS bottlenecks_bottleneck_id_validity_staging_done_key,
+    DROP CONSTRAINT IF EXISTS bottlenecks_bottleneck_id_validity_staging_done_excl;
+
+ALTER TABLE waterway.bottlenecks
+    ADD CONSTRAINT bottlenecks_bottleneck_id_validity_staging_done_key
+        UNIQUE (bottleneck_id, validity, staging_done),
+    ADD CONSTRAINT bottlenecks_bottleneck_id_validity_staging_done_excl
+        EXCLUDE USING GiST (bottleneck_id WITH =,
+                            validity WITH &&,
+                            CAST(staging_done AS int) WITH =)
+        DEFERRABLE INITIALLY DEFERRED;
diff -r 861760675497 -r 692aba3e8b85 schema/version.sql
--- a/schema/version.sql	Tue Jul 30 11:30:31 2019 +0200
+++ b/schema/version.sql	Tue Jul 30 13:14:47 2019 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1100);
+INSERT INTO gemma_schema_version(version) VALUES (1101);