Mercurial > gemma

comparison 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
comparison
equal deleted inserted replaced
3649:fb8a53c7c6d3 3656:2a079d0a71c1
487 -- 487 --
488 CREATE TABLE bottlenecks ( 488 CREATE TABLE bottlenecks (
489 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 489 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
490 bottleneck_id varchar NOT NULL, 490 bottleneck_id varchar NOT NULL,
491 validity tstzrange NOT NULL CHECK (NOT isempty(validity)), 491 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
492 UNIQUE (bottleneck_id, validity),
492 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) 493 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
493 DEFERRABLE INITIALLY DEFERRED, 494 DEFERRABLE INITIALLY DEFERRED,
494 gauge_location isrs NOT NULL, 495 gauge_location isrs NOT NULL,
495 gauge_validity tstzrange NOT NULL, 496 gauge_validity tstzrange NOT NULL,
496 CHECK(lower(validity) <@ gauge_validity), 497 CHECK(lower(validity) <@ gauge_validity),
533 PRIMARY KEY (bottleneck_id, riverbed) 534 PRIMARY KEY (bottleneck_id, riverbed)
534 ) 535 )
535 536
536 CREATE TABLE sounding_results ( 537 CREATE TABLE sounding_results (
537 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 538 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
538 bottleneck_id int NOT NULL REFERENCES bottlenecks(id), 539 bottleneck_id varchar NOT NULL,
540 bottleneck_validity tstzrange NOT NULL,
541 CONSTRAINT bottleneck_key
542 FOREIGN KEY (bottleneck_id, bottleneck_validity)
543 REFERENCES bottlenecks (bottleneck_id, validity)
544 ON UPDATE CASCADE,
539 date_info date NOT NULL, 545 date_info date NOT NULL,
546 CHECK (tstzrange(date_info::timestamptz,
547 date_info::timestamptz + '1 d'::interval) && bottleneck_validity),
540 UNIQUE (bottleneck_id, date_info), 548 UNIQUE (bottleneck_id, date_info),
541 area geography(POLYGON, 4326) NOT NULL 549 area geography(POLYGON, 4326) NOT NULL
542 CHECK(ST_IsValid(CAST(area AS geometry))), 550 CHECK(ST_IsValid(CAST(area AS geometry))),
543 surtyp varchar REFERENCES survey_types, 551 surtyp varchar REFERENCES survey_types,
544 coverage varchar REFERENCES coverage_types, 552 coverage varchar REFERENCES coverage_types,