Mercurial > gemma
changeset 4111:692aba3e8b85 request_hist_bns
Change constraints for bottlenecks to include staging_done flag.
This will allow us to stage not only new bottlenecks but also updates.
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Tue, 30 Jul 2019 13:14:47 +0200 |
| parents | 861760675497 |
| children | f39d20427e89 |
| files | schema/gemma.sql schema/updates/1101/01.bn_constraint.sql schema/version.sql |
| diffstat | 3 files changed, 21 insertions(+), 5 deletions(-) [+] |
line wrap: on
line diff
--- 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
--- /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;