Mercurial > gemma
comparison schema/gemma.sql @ 4067:0ba3fc89b499 historization_ng
Adapted references to bottlenecks from fairway_availability in DB schema.
| author | Sascha Wilde <wilde@intevation.de> |
|---|---|
| date | Thu, 25 Jul 2019 12:33:19 +0200 |
| parents | 8c6bc85db711 |
| children | 76482935b6e5 |
comparison
equal
deleted
inserted
replaced
| 4066:6bfe42f88638 | 4067:0ba3fc89b499 |
|---|---|
| 722 -- Fairway availability | 722 -- Fairway availability |
| 723 -- | 723 -- |
| 724 CREATE TABLE fairway_availability ( | 724 CREATE TABLE fairway_availability ( |
| 725 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, | 725 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
| 726 position_code char(2) REFERENCES position_codes, | 726 position_code char(2) REFERENCES position_codes, |
| 727 bottleneck_id int NOT NULL REFERENCES bottlenecks(id), | 727 bottleneck_id varchar NOT NULL, |
| 728 surdat date NOT NULL, | 728 surdat date NOT NULL, |
| 729 UNIQUE (bottleneck_id, surdat), | 729 UNIQUE (bottleneck_id, surdat), |
| 730 -- additional_data xml -- Currently not relevant for GEMMA | 730 -- additional_data xml -- Currently not relevant for GEMMA |
| 731 critical boolean, | 731 critical boolean, |
| 732 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, | 732 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
| 733 source_organization varchar NOT NULL | 733 source_organization varchar NOT NULL |
| 734 ) | 734 ) |
| 735 CREATE TRIGGER fairway_availability_date_info | 735 CREATE TRIGGER fairway_availability_date_info |
| 736 BEFORE UPDATE ON fairway_availability | 736 BEFORE UPDATE ON fairway_availability |
| 737 FOR EACH ROW EXECUTE PROCEDURE update_date_info() | 737 FOR EACH ROW EXECUTE PROCEDURE update_date_info() |
| 738 -- FIXME: From the DRC it is unclear what the exact semantics of | |
| 739 -- surdat and Date_Info ar unclear. Currently we assume that | |
| 740 -- (fk_bn_fid,surdat) has to be unique, but that might be false. | |
| 741 -- Anyway, I will date_info here to check for an matching | |
| 742 -- reference gauge at the bottleneck. The reason for this | |
| 743 -- decision is purely practical (and might be semantically | |
| 744 -- disputable: the bottleneck data in the demo system is not old | |
| 745 -- enough to cover rthe surdat times... | |
| 746 CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck | |
| 747 AFTER INSERT OR UPDATE OF bottleneck_id ON fairway_availability | |
| 748 FOR EACH ROW | |
| 749 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') | |
| 738 | 750 |
| 739 CREATE TABLE fa_reference_values ( | 751 CREATE TABLE fa_reference_values ( |
| 740 fairway_availability_id int NOT NULL REFERENCES fairway_availability, | 752 fairway_availability_id int NOT NULL REFERENCES fairway_availability, |
| 741 level_of_service smallint NOT NULL REFERENCES levels_of_service, | 753 level_of_service smallint NOT NULL REFERENCES levels_of_service, |
| 742 PRIMARY KEY (fairway_availability_id, level_of_service), | 754 PRIMARY KEY (fairway_availability_id, level_of_service), |