Mercurial > gemma

comparison schema/gemma.sql @ 3645:02951a62e8c6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
'Historicise' bottlenecks on import
author Tom Gottfried <tom@intevation.de>
date Wed, 12 Jun 2019 17:11:15 +0200
parents 3012d0b3badc
children 123b9341408e
comparison
equal deleted inserted replaced
3644:9e91b416d5bb 3645:02951a62e8c6
486 -- 486 --
487 -- Bottlenecks 487 -- Bottlenecks
488 -- 488 --
489 CREATE TABLE bottlenecks ( 489 CREATE TABLE bottlenecks (
490 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 490 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
491 bottleneck_id varchar UNIQUE NOT NULL, 491 bottleneck_id varchar NOT NULL,
492 validity tstzrange NOT NULL,
493 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
494 DEFERRABLE INITIALLY DEFERRED,
492 gauge_location isrs NOT NULL, 495 gauge_location isrs NOT NULL,
493 gauge_validity tstzrange NOT NULL, 496 gauge_validity tstzrange NOT NULL,
497 CHECK(lower(validity) <@ gauge_validity),
494 CONSTRAINT gauge_key 498 CONSTRAINT gauge_key
495 FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges 499 FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
496 ON UPDATE CASCADE, 500 ON UPDATE CASCADE,
497 objnam varchar, 501 objnam varchar,
498 nobjnm varchar, 502 nobjnm varchar,
510 -- XXX: Also an attribut of sounding result? 514 -- XXX: Also an attribut of sounding result?
511 -- CHECK allowed combinations of surtyp and coverage or 515 -- CHECK allowed combinations of surtyp and coverage or
512 -- different model approach? 516 -- different model approach?
513 -- depth_reference char(3) NOT NULL REFERENCES depth_references, 517 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
514 -- XXX: Also an attribut of sounding result? 518 -- XXX: Also an attribut of sounding result?
515 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 519 date_info timestamp with time zone NOT NULL,
516 source_organization varchar NOT NULL, 520 source_organization varchar NOT NULL,
517 -- additional_data xml -- Currently not relevant for GEMMA 521 erased boolean NOT NULL DEFAULT false,
522 CHECK (erased OR NOT isempty(validity)),
518 staging_done boolean NOT NULL DEFAULT false 523 staging_done boolean NOT NULL DEFAULT false
519 ) 524 )
520 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks 525 -- Allow only one non-erased entry per bottleneck
521 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 526 CREATE UNIQUE INDEX bottlenecks_erased_unique_constraint
527 ON bottlenecks (bottleneck_id)
528 WHERE NOT erased
522 529
523 CREATE TABLE bottlenecks_riverbed_materials ( 530 CREATE TABLE bottlenecks_riverbed_materials (
524 bottleneck_id int NOT NULL REFERENCES bottlenecks(id) 531 bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
525 ON DELETE CASCADE, 532 ON DELETE CASCADE,
526 riverbed varchar NOT NULL REFERENCES riverbed_materials, 533 riverbed varchar NOT NULL REFERENCES riverbed_materials,
561 bottleneck_id int NOT NULL REFERENCES bottlenecks(id), 568 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
562 surdat date NOT NULL, 569 surdat date NOT NULL,
563 UNIQUE (bottleneck_id, surdat), 570 UNIQUE (bottleneck_id, surdat),
564 -- additional_data xml -- Currently not relevant for GEMMA 571 -- additional_data xml -- Currently not relevant for GEMMA
565 critical boolean, 572 critical boolean,
566 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 573 date_info timestamp with time zone NOT NULL CURRENT_TIMESTAMP,
567 source_organization varchar NOT NULL 574 source_organization varchar NOT NULL
568 ) 575 )
569 CREATE TRIGGER fairway_availability_date_info 576 CREATE TRIGGER fairway_availability_date_info
570 BEFORE UPDATE ON fairway_availability 577 BEFORE UPDATE ON fairway_availability
571 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 578 FOR EACH ROW EXECUTE PROCEDURE update_date_info()