# HG changeset patch # User Tom Gottfried # Date 1527782072 -7200 # Node ID acaa485c0c1eda45cb76a91727537362ba621253 # Parent 004198a3cbc0f890ccd69966ab7a5c17c45a2487 Use different schemas for different diagrams. Add TODOs. diff -r 004198a3cbc0 -r acaa485c0c1e wamos.sql --- a/wamos.sql Thu May 31 16:30:20 2018 +0200 +++ b/wamos.sql Thu May 31 17:54:32 2018 +0200 @@ -1,10 +1,10 @@ BEGIN; +-- +-- Infrastructure +-- CREATE EXTENSION postgis; -CREATE SCHEMA wamos; -SET search_path TO public, wamos; - CREATE FUNCTION update_date_info() RETURNS trigger LANGUAGE plpgsql AS $$ @@ -30,6 +30,25 @@ subtype = isrs ); +-- +-- WAMOS data +-- +CREATE SCHEMA wamos; +CREATE SCHEMA wamos_waterway; +CREATE SCHEMA wamos_fairway; +SET search_path TO public, wamos, wamos_waterway, wamos_fairway; + +-- +-- Auxilliary tables +-- + +-- TODO: Add table to manage users e-mail adresses (APUC9) +-- TODO: Add table for responsibility areas and link to users +-- TODO: Store reports/statistics templates per user (APUC6, SPUC13) + +-- TODO: Do we need to store system config (APUC10) in database? +-- TODO: Will we store any pre-calculated cross sections in database (SPUC3)? + -- Eventually obsolete. -- See https://roundup-intern.intevation.de/wamos/issue5 -- CREATE TABLE rwdrs ( @@ -55,24 +74,24 @@ -- -- General river information -- -CREATE TABLE wamos.waterway_area ( +CREATE TABLE wamos_waterway.waterway_area ( dummy_attrib varchar, "..." varchar -- TODO: add real waterway area attributes (DRC 2.1.3) ); -CREATE TABLE wamos.reference_water_levels ( +CREATE TABLE wamos_fairway.reference_water_levels ( reference_water_level varchar(20) PRIMARY KEY ); -CREATE TABLE wamos.gauges ( +CREATE TABLE wamos_fairway.gauges ( location isrs PRIMARY KEY, dummy_attrib varchar, "..." varchar -- TODO: add real gauge attributes (DRC 2.1.4) ); -CREATE TABLE wamos.gauges_reference_water_levels ( +CREATE TABLE wamos_fairway.gauges_reference_water_levels ( gauge_id isrs NOT NULL REFERENCES gauges, reference_water_level varchar(20) NOT NULL REFERENCES reference_water_levels, @@ -80,7 +99,7 @@ value int NOT NULL ); -CREATE TABLE wamos.gauge_measurements ( +CREATE TABLE wamos_fairway.gauge_measurements ( fk_gauge_id isrs NOT NULL REFERENCES gauges, -- XXX: Is country_code really relevant for WAMOS or just NtS? -- country_code char(2) NOT NULL REFERENCES countries, @@ -90,13 +109,13 @@ -- TODO: add real gauge measurement attributes (DRC 2.1.5) ); -CREATE TABLE wamos.waterway_axis ( +CREATE TABLE wamos_waterway.waterway_axis ( dummy_attrib varchar, "..." varchar -- TODO: add real waterway area attributes (DRC 2.1.3) ); -CREATE TABLE wamos.distance_mark_functions ( +CREATE TABLE wamos_waterway.distance_mark_functions ( -- XXX: Redundant information to object code in isrs code of dist. mark distance_mark_function varchar(8) PRIMARY KEY ); @@ -104,7 +123,7 @@ -- This table allows linkage between the 1D ISRS location codes and 2D space -- e.g. for cutting bottleneck area out of waterway area based on virtual -- distance marks along waterway axis (see SUC7). -CREATE TABLE wamos.distance_marks ( +CREATE TABLE wamos_waterway.distance_marks ( location_code isrs PRIMARY KEY, geom geometry(POINT, 4326), distance_mark_function varchar(8) REFERENCES distance_mark_functions, @@ -114,7 +133,7 @@ -- TODO: add real distance mark attributes (DRC 2.1.7) ); -CREATE TABLE wamos.sections_stretches ( +CREATE TABLE wamos_waterway.sections_stretches ( id varchar PRIMARY KEY, is_section boolean NOT NULL, -- maps 'function' from interface stretch isrsrange, @@ -123,7 +142,7 @@ -- TODO: add real section/stretch attributes (DRC 2.2.1) ); -CREATE TABLE wamos.waterway_profiles ( +CREATE TABLE wamos_waterway.waterway_profiles ( dummy_attrib varchar, "..." varchar -- TODO: add real profile attributes (DRC 2.2.3) @@ -132,25 +151,25 @@ -- -- Bottlenecks -- -CREATE TABLE wamos.riverbed_materials ( +CREATE TABLE wamos_fairway.riverbed_materials ( material varchar PRIMARY KEY -- XXX: Should this table contain choices from DRC 2.2.3 or -- from IENC Encoding Guide M.4.3, attribute NATSUR? ); -CREATE TABLE wamos.survey_types ( +CREATE TABLE wamos_fairway.survey_types ( survey_type varchar PRIMARY KEY ); -CREATE TABLE wamos.coverage_types ( +CREATE TABLE wamos_fairway.coverage_types ( coverage_type varchar PRIMARY KEY ); -CREATE TABLE wamos.limiting_factors ( +CREATE TABLE wamos_fairway.limiting_factors ( limiting_factor varchar PRIMARY KEY ); -CREATE TABLE wamos.depth_references ( +CREATE TABLE wamos_fairway.depth_references ( depth_reference varchar(4) PRIMARY KEY -- See col. AB and AI RIS-Index Encoding Guide -- XXX: We need a way to distinguish between geodetic (eg. col. AP @@ -164,7 +183,7 @@ -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) -CREATE TABLE wamos.bottlenecks ( +CREATE TABLE wamos_fairway.bottlenecks ( bottleneck_id varchar PRIMARY KEY, fk_g_fid isrs NOT NULL REFERENCES gauges, -- XXX: DRC references "ch. 3.1.1", which does not exist in document. @@ -193,14 +212,14 @@ CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks FOR EACH ROW EXECUTE PROCEDURE update_date_info(); -CREATE TABLE wamos.bottlenecks_riverbed_materials ( +CREATE TABLE wamos_fairway.bottlenecks_riverbed_materials ( bottleneck_id varchar REFERENCES bottlenecks, riverbed varchar REFERENCES riverbed_materials, -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 PRIMARY KEY (bottleneck_id, riverbed) ); -CREATE TABLE wamos.sounding_results ( +CREATE TABLE wamos_fairway.sounding_results ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, date_info date NOT NULL, PRIMARY KEY (bottleneck_id, date_info), @@ -215,27 +234,27 @@ -- -- Fairway availability -- -CREATE TABLE wamos.spot_marks ( +CREATE TABLE wamos_fairway.spot_marks ( mark_name varchar PRIMARY KEY -- Use smallint because of fairway availability provided on daily basis? ); -CREATE TABLE wamos.levels_of_service ( +CREATE TABLE wamos_fairway.levels_of_service ( level_of_service smallint PRIMARY KEY ); -CREATE TABLE wamos.measure_types ( +CREATE TABLE wamos_fairway.measure_types ( measure_type varchar PRIMARY KEY ); -CREATE TABLE wamos.fairway_dimensions ( +CREATE TABLE wamos_fairway.fairway_dimensions ( level_of_service smallint REFERENCES levels_of_service, dummy_attrib varchar, "..." varchar -- TODO: add real fairway dimension attributes (DRC 2.2.2) ); -CREATE TABLE wamos.fairway_availability ( +CREATE TABLE wamos_fairway.fairway_availability ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, position varchar REFERENCES spot_marks, bottleneck_id varchar NOT NULL REFERENCES bottlenecks, @@ -250,7 +269,7 @@ BEFORE UPDATE ON fairway_availability FOR EACH ROW EXECUTE PROCEDURE update_date_info(); -CREATE TABLE wamos.fa_reference_values ( +CREATE TABLE wamos_fairway.fa_reference_values ( fairway_availability_id int NOT NULL REFERENCES fairway_availability, level_of_service smallint NOT NULL REFERENCES levels_of_service, PRIMARY KEY (fairway_availability_id, level_of_service), @@ -261,7 +280,7 @@ shallowest_spot geometry(POINT, 4326) ); -CREATE TABLE wamos.bottleneck_pdfs ( +CREATE TABLE wamos_fairway.bottleneck_pdfs ( fairway_availability_id int NOT NULL 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? @@ -270,7 +289,7 @@ source_organization varchar NOT NULL ); -CREATE TABLE wamos.effective_fairway_availability ( +CREATE TABLE wamos_fairway.effective_fairway_availability ( fairway_availability_id int NOT NULL REFERENCES fairway_availability, measure_date timestamp with time zone NOT NULL, level_of_service smallint NOT NULL REFERENCES levels_of_service,