Mercurial > gemma

comparison schema/gemma.sql @ 3648:0ec5c8ec1e44

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid empty validity time ranges An entry which is not valid at any point in time makes no sense. Further, multiple of such entries would violate a UNIQUE constraint. Since an UPDATE now can change validity time ranges, do all the adjustments for that regardles of whether an INSERT or UPDATE happens.
author Tom Gottfried <tom@intevation.de>
date Wed, 12 Jun 2019 18:26:26 +0200
parents 123b9341408e
children 2a079d0a71c1
comparison
equal deleted inserted replaced
3647:123b9341408e 3648:0ec5c8ec1e44
278 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), 278 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
279 objname varchar NOT NULL, 279 objname varchar NOT NULL,
280 geom geography(POINT, 4326) NOT NULL, 280 geom geography(POINT, 4326) NOT NULL,
281 applicability_from_km int8, 281 applicability_from_km int8,
282 applicability_to_km int8, 282 applicability_to_km int8,
283 validity tstzrange NOT NULL, 283 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
284 zero_point double precision NOT NULL, 284 zero_point double precision NOT NULL,
285 geodref varchar, 285 geodref varchar,
286 date_info timestamp with time zone NOT NULL, 286 date_info timestamp with time zone NOT NULL,
287 source_organization varchar, 287 source_organization varchar,
288 lastupdate timestamp with time zone NOT NULL, 288 lastupdate timestamp with time zone NOT NULL,
289 -- entry removed from external data source (RIS-Index)/historicised: 289 -- entry removed from external data source (RIS-Index)/historicised:
290 erased boolean NOT NULL DEFAULT false, 290 erased boolean NOT NULL DEFAULT false,
291 CHECK (erased OR NOT isempty(validity)),
292 PRIMARY KEY (location, validity), 291 PRIMARY KEY (location, validity),
293 EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&) 292 EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
294 DEFERRABLE INITIALLY DEFERRED 293 DEFERRABLE INITIALLY DEFERRED
295 ) 294 )
296 -- Allow only one non-erased entry per location 295 -- Allow only one non-erased entry per location
487 -- Bottlenecks 486 -- Bottlenecks
488 -- 487 --
489 CREATE TABLE bottlenecks ( 488 CREATE TABLE bottlenecks (
490 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 489 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
491 bottleneck_id varchar NOT NULL, 490 bottleneck_id varchar NOT NULL,
492 validity tstzrange NOT NULL, 491 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
493 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) 492 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
494 DEFERRABLE INITIALLY DEFERRED, 493 DEFERRABLE INITIALLY DEFERRED,
495 gauge_location isrs NOT NULL, 494 gauge_location isrs NOT NULL,
496 gauge_validity tstzrange NOT NULL, 495 gauge_validity tstzrange NOT NULL,
497 CHECK(lower(validity) <@ gauge_validity), 496 CHECK(lower(validity) <@ gauge_validity),
517 -- depth_reference char(3) NOT NULL REFERENCES depth_references, 516 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
518 -- XXX: Also an attribut of sounding result? 517 -- XXX: Also an attribut of sounding result?
519 date_info timestamp with time zone NOT NULL, 518 date_info timestamp with time zone NOT NULL,
520 source_organization varchar NOT NULL, 519 source_organization varchar NOT NULL,
521 erased boolean NOT NULL DEFAULT false, 520 erased boolean NOT NULL DEFAULT false,
522 CHECK (erased OR NOT isempty(validity)),
523 staging_done boolean NOT NULL DEFAULT false 521 staging_done boolean NOT NULL DEFAULT false
524 ) 522 )
525 -- Allow only one non-erased entry per bottleneck 523 -- Allow only one non-erased entry per bottleneck
526 CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint 524 CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint
527 ON bottlenecks (bottleneck_id) 525 ON bottlenecks (bottleneck_id)