--- a/schema/auth.sql	Tue Jun 26 16:16:05 2018 +0200
+++ b/schema/auth.sql	Tue Jun 26 16:20:04 2018 +0200
@@ -1,15 +1,15 @@
 BEGIN;
 
 --
--- Roles, privileges and policies for the WAMOS database
+-- Roles, privileges and policies for the GEMMA database
 --
-SET search_path TO public, wamos, wamos_waterway, wamos_fairway;
+SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
 
 -- We do not want any users to be able to create any objects
 REVOKE CREATE ON SCHEMA public FROM PUBLIC;
 
 --
--- Primary WAMOS roles (SRS table 3)
+-- Primary GEMMA roles (SRS table 3)
 --
 CREATE ROLE waterway_user;
 CREATE ROLE waterway_admin IN ROLE waterway_user;
@@ -18,15 +18,15 @@
 --
 -- Privileges for waterway_user
 --
-GRANT USAGE ON SCHEMA wamos, wamos_waterway, wamos_fairway TO waterway_user;
-GRANT SELECT ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
+GRANT USAGE ON SCHEMA gemma, gemma_waterway, gemma_fairway TO waterway_user;
+GRANT SELECT ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway
       TO waterway_user;
 GRANT SELECT ON templates, user_templates, user_profiles TO waterway_user;
 
 --
 -- Extended privileges for waterway_admin
 --
-GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA wamos_waterway, wamos_fairway
+GRANT INSERT, UPDATE ON ALL TABLES IN SCHEMA gemma_waterway, gemma_fairway
       TO waterway_admin;
 -- TODO: will there ever be UPDATEs or can we drop that due to historicisation?
 GRANT INSERT, UPDATE ON templates, user_templates TO waterway_admin;
@@ -35,7 +35,7 @@
 --
 -- Extended privileges for sys_admin
 --
-GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA wamos
+GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA gemma
       TO sys_admin;
 
 --

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/gemma.sql	Tue Jun 26 16:20:04 2018 +0200
@@ -0,0 +1,409 @@
+BEGIN;
+
+--
+-- Infrastructure
+--
+CREATE EXTENSION postgis;
+
+-- TODO: will there ever be UPDATEs or can we drop that function due to
+-- historicisation?
+CREATE FUNCTION update_date_info() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+    BEGIN
+        NEW.date_info = CURRENT_TIMESTAMP;
+        RETURN NEW;
+    END;
+$$;
+
+-- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
+-- See RIS-Index Encoding Guide
+CREATE TYPE isrs AS (
+       country_code char(2), -- ISO 3166 country code
+       -- could be validated against countries table.
+       locode char(3), -- without the country code:
+       -- http://www.unece.org/cefact/locode/welcome.html
+       fairway_section char(5),
+       object_reference char(5),
+       hectometre int -- should be constrained to five digits
+);
+
+CREATE TYPE isrsrange AS RANGE (
+       subtype = isrs
+       );
+
+--
+-- GEMMA data
+--
+CREATE SCHEMA gemma;
+CREATE SCHEMA gemma_waterway;
+CREATE SCHEMA gemma_fairway;
+SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
+-- TODO: consolidate schemas. The current distribution of tables is mainly
+-- for diagram generation. A privilege based distribution might ease
+-- privilege management.
+
+--
+-- Auxiliary tables
+--
+CREATE TABLE gemma.system_config (
+       config_key varchar PRIMARY KEY,
+       config_val varchar
+       );
+
+CREATE TABLE gemma.countries (
+       country_code char(2) PRIMARY KEY -- ISO 3166 country code
+       -- A smallint PK would require even less disk space i.e. on the FK side.
+       -- This might be an issue in case cache space becomes a limiting
+       -- factor when there are many FKs pointing here.
+       );
+
+CREATE TABLE gemma.responsibility_areas (
+       country char(2) PRIMARY KEY REFERENCES countries,
+       area geometry(POLYGON, 4326) --XXX: Should be geography (elsewhere too)
+       );
+
+CREATE TABLE gemma.language_codes (
+       language_code varchar PRIMARY KEY
+       );
+
+CREATE TABLE gemma.user_profiles (
+       username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
+       country char(2) NOT NULL REFERENCES countries,
+       language_code varchar REFERENCES language_codes,
+       map_extent box2d,
+       email_adress varchar NOT NULL UNIQUE
+       );
+
+CREATE TABLE gemma.templates (
+       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+       dummy_attrib varchar,
+       "..." varchar
+       -- TODO: template attributes tbd.
+       );
+
+CREATE TABLE gemma.user_templates (
+       username varchar NOT NULL REFERENCES user_profiles,
+       template_id int NOT NULL REFERENCES templates,
+       PRIMARY KEY (username, template_id)
+       );
+
+--
+-- General river information
+--
+
+-- Eventually obsolete.
+-- See https://roundup-intern.intevation.de/gemma/issue5
+-- CREATE TABLE rwdrs (
+--        stretch isrsrange PRIMARY KEY,
+--        -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
+--        -- Only B-tree supports UNIQUE indexes, but we need the GIST index
+--        -- below anyhow.
+--        -- Is it a good idea to build B-tree indexes on relatively large
+--        -- composites of string values or should we use inter PKs?
+--        -- => In case the index is used and cache space becomes a limiting
+--        -- factor, this might be an issue.
+--        rwdr double precision NOT NULL,
+--        EXCLUDE USING GIST (stretch WITH &&)
+--        );
+
+CREATE TABLE gemma_waterway.catccls (
+       catccl smallint PRIMARY KEY
+       -- TODO: Do we need name and/or definition from IENC feature catalogue?
+       );
+
+CREATE TABLE gemma_waterway.dirimps (
+       dirimp smallint PRIMARY KEY
+       -- TODO: Do we need name and/or definition from IENC feature catalogue?
+       );
+
+CREATE TABLE gemma_waterway.waterway_area (
+       area geometry(POLYGON, 4326) PRIMARY KEY,
+       catccl smallint REFERENCES catccls,
+       dirimp smallint REFERENCES dirimps
+       );
+
+CREATE TABLE gemma_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
+       -- RIS-Index) and other references (e.g. col. AB and AI):
+       -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
+       --   i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
+       -- _ Do not mixup things with different meanings in one table at all
+       --   (which would mean a model differing a bit from RIS-Index ideas)
+       );
+
+CREATE TABLE gemma_fairway.reference_water_levels (
+       reference_water_level varchar(20) PRIMARY KEY
+       );
+
+CREATE TABLE gemma_fairway.gauges (
+       location isrs PRIMARY KEY,
+       function_code varchar(10) NOT NULL, -- XXX: What is this really for?
+       objname varchar NOT NULL,
+       is_left boolean, -- XXX: Or reference position_codes?
+       geom geometry(POINT, 4326) NOT NULL,
+       applicability isrsrange,
+       validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy
+       -- pasted text from a more general specification is given
+       -- (a gauge is not a berth!)
+       -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
+       zero_point double precision NOT NULL,
+       geodref varchar(4) REFERENCES depth_references,
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL
+       );
+CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+
+CREATE TABLE gemma_fairway.gauges_reference_water_levels (
+       gauge_id isrs NOT NULL REFERENCES gauges,
+       reference_water_level varchar(20)
+           NOT NULL REFERENCES reference_water_levels,
+       PRIMARY KEY (gauge_id, reference_water_level),
+       value int NOT NULL
+       );
+
+CREATE TABLE gemma_fairway.gauge_measurements (
+       fk_gauge_id isrs NOT NULL REFERENCES gauges,
+       measure_date timestamp with time zone NOT NULL,
+       PRIMARY KEY (fk_gauge_id, measure_date),
+       -- XXX: Is country_code really relevant for GEMMA or just NtS?
+       -- country_code char(2) NOT NULL REFERENCES countries,
+       -- TODO: add relations to stuff provided as enumerations
+       sender varchar NOT NULL, -- "from" attribute from DRC
+       language_code varchar NOT NULL REFERENCES language_codes,
+       date_issue timestamp with time zone NOT NULL,
+       -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
+       -- XXX: Always ZPG?
+       water_level double precision NOT NULL,
+       predicted boolean NOT NULL,
+       is_waterlevel boolean NOT NULL,
+       -- XXX: "measure_code" if really only W or Q
+       -- XXX: Do we need "unit" attribute or can we normalise on import?
+       value_min double precision, -- XXX: NOT NULL if predicted?
+       value_max double precision, -- XXX: NOT NULL if predicted?
+       --- TODO: Add a double range type for checking?
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL -- "originator"
+       );
+CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+
+CREATE TABLE gemma_waterway.waterway_axis (
+       wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
+       -- TODO: Do we need to check data set quality as described in DRC 2.1.6?
+       objnam varchar NOT NULL,
+       nobjnam varchar
+       );
+
+CREATE TABLE gemma_waterway.distance_mark_functions (
+       -- XXX: Redundant information to object code in isrs code of dist. mark
+       distance_mark_function varchar(8) PRIMARY KEY
+       );
+
+CREATE TABLE gemma_waterway.position_codes (
+       position_code char(2) PRIMARY KEY
+       -- Use smallint because of fairway availability provided on daily basis?
+       -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
+       -- sheet "Position_code" or RIS-Index encoding guide?
+       -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
+       -- Clarify!
+       -- TODO: Do we need an attribute "meaning" or so?
+       );
+
+-- 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 gemma_waterway.distance_marks (
+       location_code isrs PRIMARY KEY,
+       geom geometry(POINT, 4326) NOT NULL,
+       distance_mark_function varchar(8)
+           NOT NULL REFERENCES distance_mark_functions,
+       -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
+       -- to encode the same thing as the object code in ISRS location code.
+       position_code char(2) NOT NULL REFERENCES position_codes
+       );
+
+CREATE TABLE gemma_waterway.sections_stretches (
+       id varchar PRIMARY KEY,
+       is_section boolean NOT NULL, -- maps 'function' from interface
+       stretch isrsrange,
+       objnam varchar NOT NULL,
+       nobjnam varchar,
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL
+       );
+CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+
+CREATE TABLE gemma_waterway.waterway_profiles (
+       location isrs NOT NULL,
+       validity tstzrange,
+       EXCLUDE USING GIST (validity WITH &&),
+       PRIMARY KEY (location, validity),
+       lnwl smallint,
+       mwl smallint,
+       hnwl smallint,
+       fe30 smallint,
+       fe100 smallint,
+       -- XXX: further normalise using reference_water_levels?
+       CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
+             OR validity IS NOT NULL),
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL
+       );
+CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+
+CREATE TABLE gemma_waterway.levels_of_service (
+       level_of_service smallint PRIMARY KEY
+       );
+
+CREATE TABLE gemma_waterway.fairway_dimensions (
+       area geometry(POLYGON, 4326) PRIMARY KEY,
+       level_of_service smallint NOT NULL REFERENCES levels_of_service,
+       min_width smallint NOT NULL,
+       max_width smallint NOT NULL,
+       min_depth smallint NOT NULL,
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL
+       );
+CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+
+--
+-- Bottlenecks
+--
+CREATE TABLE gemma_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 gemma_fairway.survey_types (
+       survey_type varchar PRIMARY KEY
+       );
+
+CREATE TABLE gemma_fairway.coverage_types (
+       coverage_type varchar PRIMARY KEY
+       );
+
+CREATE TABLE gemma_fairway.limiting_factors (
+       limiting_factor varchar PRIMARY KEY
+       );
+
+-- 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 gemma_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.
+       objnam varchar,
+       nobjnm varchar,
+       stretch isrsrange NOT NULL,
+       area geometry(POLYGON, 4326) NOT NULL,
+       rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
+       lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
+       responsible_country char(2) NOT NULL REFERENCES countries,
+       revisiting_time smallint NOT NULL,
+       limiting varchar NOT NULL REFERENCES limiting_factors,
+       -- surtyp varchar NOT NULL REFERENCES survey_types,
+       -- XXX: Also an attribut of sounding result?
+       -- coverage varchar REFERENCES coverage_types,
+       -- XXX: Also an attribut of sounding result?
+       -- CHECK allowed combinations of surtyp and coverage or
+       -- different model approach?
+       -- depth_reference char(3) NOT NULL REFERENCES depth_references,
+       -- XXX: Also an attribut of sounding result?
+       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
+       source_organization varchar NOT NULL,
+       -- additional_data xml -- Currently not relevant for GEMMA
+       staging_done boolean NOT NULL DEFAULT false
+       );
+CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
+       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
+
+CREATE TABLE gemma_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 gemma_fairway.sounding_results (
+       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+       date_info date NOT NULL,
+       PRIMARY KEY (bottleneck_id, date_info),
+       area geometry(POLYGON, 4326) NOT NULL,
+       surtyp varchar NOT NULL REFERENCES survey_types,
+       coverage varchar REFERENCES coverage_types,
+       depth_reference char(3) NOT NULL REFERENCES depth_references,
+       sounding_data raster NOT NULL,
+       staging_done boolean NOT NULL DEFAULT false
+       );
+
+--
+-- Fairway availability
+--
+CREATE TABLE gemma_fairway.measure_types (
+       measure_type varchar PRIMARY KEY
+       );
+
+CREATE TABLE gemma_fairway.fairway_availability (
+       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
+       position_code char(2) REFERENCES position_codes,
+       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
+       surdat date NOT NULL,
+       UNIQUE (bottleneck_id, surdat),
+       -- additional_data xml -- Currently not relevant for GEMMA
+       critical boolean,
+       date_info timestamp with time zone 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 gemma_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),
+       fairway_depth smallint,
+       fairway_width smallint,
+       fairway_radius int,
+       CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
+       shallowest_spot geometry(POINT, 4326)
+       );
+
+CREATE TABLE gemma_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?
+       PRIMARY KEY (fairway_availability_id, profile_pdf_url),
+       pdf_generation_date timestamp with time zone NOT NULL,
+       source_organization varchar NOT NULL
+       );
+
+CREATE TABLE gemma_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,
+       PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
+       available_depth_value smallint,
+       available_width_value smallint,
+       water_level_value smallint,
+       CHECK(COALESCE(available_depth_value, available_width_value,
+           water_level_value) IS NOT NULL),
+       measure_type varchar NOT NULL REFERENCES measure_types,
+       source_organization varchar NOT NULL,
+       forecast_generation_time timestamp with time zone,
+       CHECK(measure_type <> 'forecasted'
+             OR forecast_generation_time IS NOT NULL),
+       value_lifetime timestamp with time zone,
+       CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
+       );
+
+COMMIT;

--- a/schema/tap_tests.sql	Tue Jun 26 16:16:05 2018 +0200
+++ b/schema/tap_tests.sql	Tue Jun 26 16:20:04 2018 +0200
@@ -7,7 +7,7 @@
 
 SELECT plan(10); -- Give number of tests that have to be run
 
-SET search_path TO public, wamos, wamos_waterway, wamos_fairway;
+SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
 
 \i tap_tests_data.sql
 
@@ -24,7 +24,7 @@
 SELECT is_empty('SELECT * FROM bottlenecks WHERE NOT staging_done',
                 'Only staged data should be visible');
 
-SELECT set_eq('SELECT count(*) FROM wamos.user_profiles', ARRAY[1],
+SELECT set_eq('SELECT count(*) FROM gemma.user_profiles', ARRAY[1],
               'User should only see his own profile');
 SELECT results_eq('SELECT username FROM user_profiles',
                   'SELECT CAST(current_user AS varchar)',

--- a/schema/tap_tests_data.sql	Tue Jun 26 16:16:05 2018 +0200
+++ b/schema/tap_tests_data.sql	Tue Jun 26 16:20:04 2018 +0200
@@ -1,4 +1,4 @@
-SET search_path TO public, wamos, wamos_waterway, wamos_fairway;
+SET search_path TO public, gemma, gemma_waterway, gemma_fairway;
 
 INSERT INTO countries VALUES ('AT');
 

--- a/schema/wamos.sql	Tue Jun 26 16:16:05 2018 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,409 +0,0 @@
-BEGIN;
-
---
--- Infrastructure
---
-CREATE EXTENSION postgis;
-
--- TODO: will there ever be UPDATEs or can we drop that function due to
--- historicisation?
-CREATE FUNCTION update_date_info() RETURNS trigger
-    LANGUAGE plpgsql
-    AS $$
-    BEGIN
-        NEW.date_info = CURRENT_TIMESTAMP;
-        RETURN NEW;
-    END;
-$$;
-
--- Composite type: UN/LOCODE, fairway section, object reference, hectometre.
--- See RIS-Index Encoding Guide
-CREATE TYPE isrs AS (
-       country_code char(2), -- ISO 3166 country code
-       -- could be validated against countries table.
-       locode char(3), -- without the country code:
-       -- http://www.unece.org/cefact/locode/welcome.html
-       fairway_section char(5),
-       object_reference char(5),
-       hectometre int -- should be constrained to five digits
-);
-
-CREATE TYPE isrsrange AS RANGE (
-       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;
--- TODO: consolidate schemas. The current distribution of tables is mainly
--- for diagram generation. A privilege based distribution might ease
--- privilege management.
-
---
--- Auxiliary tables
---
-CREATE TABLE wamos.system_config (
-       config_key varchar PRIMARY KEY,
-       config_val varchar
-       );
-
-CREATE TABLE wamos.countries (
-       country_code char(2) PRIMARY KEY -- ISO 3166 country code
-       -- A smallint PK would require even less disk space i.e. on the FK side.
-       -- This might be an issue in case cache space becomes a limiting
-       -- factor when there are many FKs pointing here.
-       );
-
-CREATE TABLE wamos.responsibility_areas (
-       country char(2) PRIMARY KEY REFERENCES countries,
-       area geometry(POLYGON, 4326) --XXX: Should be geography (elsewhere too)
-       );
-
-CREATE TABLE wamos.language_codes (
-       language_code varchar PRIMARY KEY
-       );
-
-CREATE TABLE wamos.user_profiles (
-       username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
-       country char(2) NOT NULL REFERENCES countries,
-       language_code varchar REFERENCES language_codes,
-       map_extent box2d,
-       email_adress varchar NOT NULL UNIQUE
-       );
-
-CREATE TABLE wamos.templates (
-       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-       dummy_attrib varchar,
-       "..." varchar
-       -- TODO: template attributes tbd.
-       );
-
-CREATE TABLE wamos.user_templates (
-       username varchar NOT NULL REFERENCES user_profiles,
-       template_id int NOT NULL REFERENCES templates,
-       PRIMARY KEY (username, template_id)
-       );
-
---
--- General river information
---
-
--- Eventually obsolete.
--- See https://roundup-intern.intevation.de/wamos/issue5
--- CREATE TABLE rwdrs (
---        stretch isrsrange PRIMARY KEY,
---        -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
---        -- Only B-tree supports UNIQUE indexes, but we need the GIST index
---        -- below anyhow.
---        -- Is it a good idea to build B-tree indexes on relatively large
---        -- composites of string values or should we use inter PKs?
---        -- => In case the index is used and cache space becomes a limiting
---        -- factor, this might be an issue.
---        rwdr double precision NOT NULL,
---        EXCLUDE USING GIST (stretch WITH &&)
---        );
-
-CREATE TABLE wamos_waterway.catccls (
-       catccl smallint PRIMARY KEY
-       -- TODO: Do we need name and/or definition from IENC feature catalogue?
-       );
-
-CREATE TABLE wamos_waterway.dirimps (
-       dirimp smallint PRIMARY KEY
-       -- TODO: Do we need name and/or definition from IENC feature catalogue?
-       );
-
-CREATE TABLE wamos_waterway.waterway_area (
-       area geometry(POLYGON, 4326) PRIMARY KEY,
-       catccl smallint REFERENCES catccls,
-       dirimp smallint REFERENCES dirimps
-       );
-
-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
-       -- RIS-Index) and other references (e.g. col. AB and AI):
-       -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
-       --   i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
-       -- _ Do not mixup things with different meanings in one table at all
-       --   (which would mean a model differing a bit from RIS-Index ideas)
-       );
-
-CREATE TABLE wamos_fairway.reference_water_levels (
-       reference_water_level varchar(20) PRIMARY KEY
-       );
-
-CREATE TABLE wamos_fairway.gauges (
-       location isrs PRIMARY KEY,
-       function_code varchar(10) NOT NULL, -- XXX: What is this really for?
-       objname varchar NOT NULL,
-       is_left boolean, -- XXX: Or reference position_codes?
-       geom geometry(POINT, 4326) NOT NULL,
-       applicability isrsrange,
-       validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy
-       -- pasted text from a more general specification is given
-       -- (a gauge is not a berth!)
-       -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
-       zero_point double precision NOT NULL,
-       geodref varchar(4) REFERENCES depth_references,
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL
-       );
-CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
-
-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,
-       PRIMARY KEY (gauge_id, reference_water_level),
-       value int NOT NULL
-       );
-
-CREATE TABLE wamos_fairway.gauge_measurements (
-       fk_gauge_id isrs NOT NULL REFERENCES gauges,
-       measure_date timestamp with time zone NOT NULL,
-       PRIMARY KEY (fk_gauge_id, measure_date),
-       -- XXX: Is country_code really relevant for WAMOS or just NtS?
-       -- country_code char(2) NOT NULL REFERENCES countries,
-       -- TODO: add relations to stuff provided as enumerations
-       sender varchar NOT NULL, -- "from" attribute from DRC
-       language_code varchar NOT NULL REFERENCES language_codes,
-       date_issue timestamp with time zone NOT NULL,
-       -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
-       -- XXX: Always ZPG?
-       water_level double precision NOT NULL,
-       predicted boolean NOT NULL,
-       is_waterlevel boolean NOT NULL,
-       -- XXX: "measure_code" if really only W or Q
-       -- XXX: Do we need "unit" attribute or can we normalise on import?
-       value_min double precision, -- XXX: NOT NULL if predicted?
-       value_max double precision, -- XXX: NOT NULL if predicted?
-       --- TODO: Add a double range type for checking?
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL -- "originator"
-       );
-CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
-
-CREATE TABLE wamos_waterway.waterway_axis (
-       wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
-       -- TODO: Do we need to check data set quality as described in DRC 2.1.6?
-       objnam varchar NOT NULL,
-       nobjnam varchar
-       );
-
-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
-       );
-
-CREATE TABLE wamos_waterway.position_codes (
-       position_code char(2) PRIMARY KEY
-       -- Use smallint because of fairway availability provided on daily basis?
-       -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
-       -- sheet "Position_code" or RIS-Index encoding guide?
-       -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
-       -- Clarify!
-       -- TODO: Do we need an attribute "meaning" or so?
-       );
-
--- 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_waterway.distance_marks (
-       location_code isrs PRIMARY KEY,
-       geom geometry(POINT, 4326) NOT NULL,
-       distance_mark_function varchar(8)
-           NOT NULL REFERENCES distance_mark_functions,
-       -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
-       -- to encode the same thing as the object code in ISRS location code.
-       position_code char(2) NOT NULL REFERENCES position_codes
-       );
-
-CREATE TABLE wamos_waterway.sections_stretches (
-       id varchar PRIMARY KEY,
-       is_section boolean NOT NULL, -- maps 'function' from interface
-       stretch isrsrange,
-       objnam varchar NOT NULL,
-       nobjnam varchar,
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL
-       );
-CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
-
-CREATE TABLE wamos_waterway.waterway_profiles (
-       location isrs NOT NULL,
-       validity tstzrange,
-       EXCLUDE USING GIST (validity WITH &&),
-       PRIMARY KEY (location, validity),
-       lnwl smallint,
-       mwl smallint,
-       hnwl smallint,
-       fe30 smallint,
-       fe100 smallint,
-       -- XXX: further normalise using reference_water_levels?
-       CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
-             OR validity IS NOT NULL),
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL
-       );
-CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
-
-CREATE TABLE wamos_waterway.levels_of_service (
-       level_of_service smallint PRIMARY KEY
-       );
-
-CREATE TABLE wamos_waterway.fairway_dimensions (
-       area geometry(POLYGON, 4326) PRIMARY KEY,
-       level_of_service smallint NOT NULL REFERENCES levels_of_service,
-       min_width smallint NOT NULL,
-       max_width smallint NOT NULL,
-       min_depth smallint NOT NULL,
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL
-       );
-CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
-
---
--- Bottlenecks
---
-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_fairway.survey_types (
-       survey_type varchar PRIMARY KEY
-       );
-
-CREATE TABLE wamos_fairway.coverage_types (
-       coverage_type varchar PRIMARY KEY
-       );
-
-CREATE TABLE wamos_fairway.limiting_factors (
-       limiting_factor varchar PRIMARY KEY
-       );
-
--- 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_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.
-       objnam varchar,
-       nobjnm varchar,
-       stretch isrsrange NOT NULL,
-       area geometry(POLYGON, 4326) NOT NULL,
-       rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
-       lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
-       responsible_country char(2) NOT NULL REFERENCES countries,
-       revisiting_time smallint NOT NULL,
-       limiting varchar NOT NULL REFERENCES limiting_factors,
-       -- surtyp varchar NOT NULL REFERENCES survey_types,
-       -- XXX: Also an attribut of sounding result?
-       -- coverage varchar REFERENCES coverage_types,
-       -- XXX: Also an attribut of sounding result?
-       -- CHECK allowed combinations of surtyp and coverage or
-       -- different model approach?
-       -- depth_reference char(3) NOT NULL REFERENCES depth_references,
-       -- XXX: Also an attribut of sounding result?
-       date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
-       source_organization varchar NOT NULL,
-       -- additional_data xml -- Currently not relevant for WAMOS
-       staging_done boolean NOT NULL DEFAULT false
-       );
-CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
-       FOR EACH ROW EXECUTE PROCEDURE update_date_info();
-
-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_fairway.sounding_results (
-       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
-       date_info date NOT NULL,
-       PRIMARY KEY (bottleneck_id, date_info),
-       area geometry(POLYGON, 4326) NOT NULL,
-       surtyp varchar NOT NULL REFERENCES survey_types,
-       coverage varchar REFERENCES coverage_types,
-       depth_reference char(3) NOT NULL REFERENCES depth_references,
-       sounding_data raster NOT NULL,
-       staging_done boolean NOT NULL DEFAULT false
-       );
-
---
--- Fairway availability
---
-CREATE TABLE wamos_fairway.measure_types (
-       measure_type varchar PRIMARY KEY
-       );
-
-CREATE TABLE wamos_fairway.fairway_availability (
-       id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
-       position_code char(2) REFERENCES position_codes,
-       bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
-       surdat date NOT NULL,
-       UNIQUE (bottleneck_id, surdat),
-       -- additional_data xml -- Currently not relevant for WAMOS
-       critical boolean,
-       date_info timestamp with time zone 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 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),
-       fairway_depth smallint,
-       fairway_width smallint,
-       fairway_radius int,
-       CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL),
-       shallowest_spot geometry(POINT, 4326)
-       );
-
-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?
-       PRIMARY KEY (fairway_availability_id, profile_pdf_url),
-       pdf_generation_date timestamp with time zone NOT NULL,
-       source_organization varchar NOT NULL
-       );
-
-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,
-       PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
-       available_depth_value smallint,
-       available_width_value smallint,
-       water_level_value smallint,
-       CHECK(COALESCE(available_depth_value, available_width_value,
-           water_level_value) IS NOT NULL),
-       measure_type varchar NOT NULL REFERENCES measure_types,
-       source_organization varchar NOT NULL,
-       forecast_generation_time timestamp with time zone,
-       CHECK(measure_type <> 'forecasted'
-             OR forecast_generation_time IS NOT NULL),
-       value_lifetime timestamp with time zone,
-       CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL)
-       );
-
-COMMIT;