Mercurial > gemma

changeset 81:acaa485c0c1e

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use different schemas for different diagrams. Add TODOs.
author Tom Gottfried <tom@intevation.de>
date Thu, 31 May 2018 17:54:32 +0200
parents 004198a3cbc0
children c71e43f88ae0
files wamos.sql
diffstat 1 files changed, 48 insertions(+), 29 deletions(-) [+]
line wrap: on
line diff
--- 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,