Mercurial > gemma
changeset 56:f378959820be
Started to draft schema for fairway availability.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 23 May 2018 18:24:02 +0200 |
parents | ecb9bb0591c9 |
children | 353f804e86ae |
files | wamos.sql |
diffstat | 1 files changed, 60 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/wamos.sql Wed May 23 17:37:19 2018 +0200 +++ b/wamos.sql Wed May 23 18:24:02 2018 +0200 @@ -56,6 +56,9 @@ -- factor when there are many FKs pointing here. ); +-- +-- Bottlenecks +-- CREATE TABLE riverbed_materials ( material varchar PRIMARY KEY -- XXX: Should this table contain choices from DRC 2.2.3 or @@ -134,4 +137,61 @@ sounding_data raster NOT NULL ); +-- +-- Fairway availability +-- +CREATE TABLE spot_marks ( + mark_name varchar PRIMARY KEY + -- Use smallint because of fairway availability provided on daily basis? + ); + +CREATE TABLE levels_of_service ( + level_of_service smallint PRIMARY KEY + ); + +CREATE TABLE fairway_availability ( + bottleneck_id varchar NOT NULL REFERENCES bottlenecks, + surdat date NOT NULL, + PRIMARY KEY (bottleneck_id, surdat), + position varchar REFERENCES spot_marks, + -- additional_data xml -- Currently not relevant for WAMOS + critical boolean, + date_info timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, + source_organization varchar NOT NULL + ); +CREATE TRIGGER fairway_availability_date_info + BEFORE UPDATE ON fairway_availability + FOR EACH ROW EXECUTE PROCEDURE update_date_info(); + +CREATE TABLE fa_reference_values ( + bottleneck_id varchar NOT NULL REFERENCES bottlenecks, + surdat date NOT NULL, + FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, + level_of_service smallint NOT NULL REFERENCES levels_of_service, + PRIMARY KEY (bottleneck_id, surdat, level_of_service), + fairway_depth smallint, + fairway_width smallint, + fairway_radius int, + CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), + shallowest_spot geometry(POINT, 3146) + ); + +CREATE TABLE bottleneck_pdfs ( + bottleneck_id varchar NOT NULL REFERENCES bottlenecks, + surdat date NOT NULL, + FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, + profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL + profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? + PRIMARY KEY (bottleneck_id, surdat, profile_pdf_url), + pdf_generation_date timestamp NOT NULL, + source_organization varchar NOT NULL + ); + +CREATE TABLE effective_fairway_availability ( + bottleneck_id varchar NOT NULL REFERENCES bottlenecks, + surdat date NOT NULL, + FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability + -- TODO + ); + COMMIT;