Mercurial > gemma
changeset 113:25b28fd0e256
Moved schema stuff to own subfolder.
| author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
|---|---|
| date | Tue, 26 Jun 2018 16:14:59 +0200 |
| parents | 894f633e2d3f |
| children | 79f5ba414586 |
| files | Dockerfile README auth.sql dot.tmpl schema/Dockerfile schema/README schema/auth.sql schema/dot.tmpl schema/tap_tests.sql schema/tap_tests_data.sql schema/wamos.sql tap_tests.sql tap_tests_data.sql wamos.sql |
| diffstat | 14 files changed, 713 insertions(+), 713 deletions(-) [+] |
line wrap: on
line diff
--- a/Dockerfile Fri Jun 15 20:20:44 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,42 +0,0 @@ -FROM centos:7 -MAINTAINER tom.gottfried@intevation.de - -# Add the PostgreSQL PGP key to verify the official yum repository packages -RUN rpm --import https://yum.postgresql.org/RPM-GPG-KEY-PGDG-10 - -# Add PostgreSQL's repository. It contains the most recent release -# of PostgreSQL, 10: -RUN yum -q -y install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm - -# Install PostgreSQL 10 and PostGIS -RUN yum -q -y install postgresql10-server -RUN yum -q -y install epel-release -RUN yum -q -y install postgis24_10 pgtap10 - -USER postgres -ENV PGBIN /usr/pgsql-10/bin/ - -# initdb PostgreSQL 10: -ENV PGDATA /var/lib/pgsql/10/data -RUN $PGBIN/initdb -E UTF8 2>&1 < /dev/null - -# Adjust PostgreSQL configuration so that remote connections to the -# database are possible. -ENV PGCONF /var/lib/pgsql/10/data/postgresql.conf -RUN echo "host all all 0.0.0.0/0 md5" >> /var/lib/pgsql/10/data/pg_hba.conf -RUN echo "listen_addresses='*'" >> $PGCONF - -# Expose the PostgreSQL port -EXPOSE 5432 - -# Create WAMOS role and database -ADD *.sql ./ -RUN $PGBIN/pg_ctl start -wo "--config_file=$PGCONF" && \ - psql -c "CREATE USER wamos PASSWORD 'wamos'" && \ - createdb wamos && \ - psql -f wamos.sql -d wamos && \ - psql -f auth.sql -d wamos && \ - $PGBIN/pg_ctl stop -m smart - -# Set the default command to run when starting the container -CMD ["/usr/pgsql-10/bin/postgres", "-D", "/var/lib/pgsql/10/data"]
--- a/README Fri Jun 15 20:20:44 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,24 +0,0 @@ -Build Dockerfile with e.g.: -$ docker build -t wamos . - -Get a running instance with e.g.: -$ docker run --name wamos -d -p 54321:5432 -v $PWD:/opt/wamos wamos - -Run tests for RLS policies as database superuser (within container) with: -$ psql -d wamos -Xf tap_tests.sql - -Create ER diagrams with e.g.: -Auxiliary tables: -$ postgresql_autodoc -p 54321 -h $dockerhost \ - -U wamos --password=wamos -t dot -l . -s 'wamos' -$ dot -Tpdf wamos.dot > wamos_auxiliary.pdf - -Waterway related tables: -$ postgresql_autodoc -p 54321 -h $dockerhost \ - -U wamos --password=wamos -t dot -l . -s 'wamos_waterway' -$ dot -Tpdf wamos.dot > wamos_waterway.pdf - -Fairway/bottleneck related tables: -$ postgresql_autodoc -p 54321 -h $dockerhost \ - -U wamos --password=wamos -t dot -l . -s 'wamos_fairway' -$ dot -Tpdf wamos.dot > wamos_fairway.pdf
--- a/auth.sql Fri Jun 15 20:20:44 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,89 +0,0 @@ -BEGIN; - --- --- Roles, privileges and policies for the WAMOS database --- -SET search_path TO public, wamos, wamos_waterway, wamos_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) --- -CREATE ROLE waterway_user; -CREATE ROLE waterway_admin IN ROLE waterway_user; -CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin; - --- --- 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 - 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 - 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; -GRANT SELECT ON responsibility_areas TO waterway_admin; - --- --- Extended privileges for sys_admin --- -GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA wamos - TO sys_admin; - --- --- RLS policies for waterway_user --- - --- Staging area -CREATE FUNCTION create_hide_staging_policy() RETURNS void AS $$ -DECLARE the_table varchar; -BEGIN - FOREACH the_table IN ARRAY ARRAY[ - 'bottlenecks', 'sounding_results'] - -- TODO: add all relevant tables here - LOOP - EXECUTE format('CREATE POLICY hide_staging ON %I ' - 'FOR SELECT TO waterway_user USING (staging_done)', the_table); - EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table); - END LOOP; -END; -$$ LANGUAGE plpgsql; -SELECT create_hide_staging_policy(); -DROP FUNCTION create_hide_staging_policy; - -CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user - USING (username = current_user); -ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY; - -CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user - USING (id IN(SELECT template_id FROM user_templates - WHERE username = current_user)); -ALTER TABLE templates ENABLE ROW LEVEL SECURITY; - --- --- RLS policies for waterway_admin --- - --- Staging area --- TODO: add all relevant tables here -CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin - USING (ST_Within(area, (SELECT area FROM responsibility_areas - WHERE country = (SELECT country FROM user_profiles)))); -CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin - USING (ST_Within(area, (SELECT area FROM responsibility_areas - WHERE country = (SELECT country FROM user_profiles)))); - -CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin - USING (id IN(SELECT template_id FROM user_templates ut - JOIN user_profiles p ON ut.username = p.username - WHERE p.country = (SELECT country FROM user_profiles))); - -COMMIT;
--- a/dot.tmpl Fri Jun 15 20:20:44 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,19 +0,0 @@ -digraph g { -graph [ -rankdir = "LR", -concentrate = true, -ratio = auto -]; -node [ -fontsize = "10", -shape = record -]; -edge [ -]; -<TMPL_LOOP name="schemas"><TMPL_LOOP name="tables"><TMPL_UNLESS name="view_definition"> -"<TMPL_IF name="number_of_schemas"><TMPL_VAR name="schema_dot">.</TMPL_IF name="number_of_schemas"><TMPL_VAR name="table_dot">" [shape = plaintext, label = <<TABLE BORDER="1" CELLBORDER="0" CELLSPACING="0"><TR><TD PORT="ltcol0"></TD><TD border="1" COLSPAN="2"> \N </TD><TD PORT="rtcol0"></TD></TR><TMPL_LOOP name="columns"><TR><TD PORT="ltcol<TMPL_VAR name="column_number">"></TD><TD align="left"><TMPL_VAR name="column_dot"></TD><TD align="left"><TMPL_VAR name="column_type"></TD><TD align="left" PORT="rtcol<TMPL_VAR name="column_number">"></TD></TR></TMPL_LOOP name="columns"></TABLE>> ]; -</TMPL_UNLESS name="view_definition"></TMPL_LOOP name="tables"></TMPL_LOOP name="schemas"> - -<TMPL_LOOP name="fk_links"> -"<TMPL_IF name="number_of_schemas"><TMPL_VAR name="handle0_schema">.</TMPL_IF name="number_of_schemas"><TMPL_VAR name="handle0_name">":rtcol<TMPL_VAR name="handle0_connection"> -> "<TMPL_IF name="number_of_schemas"><TMPL_VAR name="handle1_schema">.</TMPL_IF name="number_of_schemas"><TMPL_VAR name="handle1_name">":ltcol<TMPL_VAR name="handle1_connection"> [];</TMPL_LOOP name="fk_links"> -}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/Dockerfile Tue Jun 26 16:14:59 2018 +0200 @@ -0,0 +1,42 @@ +FROM centos:7 +MAINTAINER tom.gottfried@intevation.de + +# Add the PostgreSQL PGP key to verify the official yum repository packages +RUN rpm --import https://yum.postgresql.org/RPM-GPG-KEY-PGDG-10 + +# Add PostgreSQL's repository. It contains the most recent release +# of PostgreSQL, 10: +RUN yum -q -y install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm + +# Install PostgreSQL 10 and PostGIS +RUN yum -q -y install postgresql10-server +RUN yum -q -y install epel-release +RUN yum -q -y install postgis24_10 pgtap10 + +USER postgres +ENV PGBIN /usr/pgsql-10/bin/ + +# initdb PostgreSQL 10: +ENV PGDATA /var/lib/pgsql/10/data +RUN $PGBIN/initdb -E UTF8 2>&1 < /dev/null + +# Adjust PostgreSQL configuration so that remote connections to the +# database are possible. +ENV PGCONF /var/lib/pgsql/10/data/postgresql.conf +RUN echo "host all all 0.0.0.0/0 md5" >> /var/lib/pgsql/10/data/pg_hba.conf +RUN echo "listen_addresses='*'" >> $PGCONF + +# Expose the PostgreSQL port +EXPOSE 5432 + +# Create WAMOS role and database +ADD *.sql ./ +RUN $PGBIN/pg_ctl start -wo "--config_file=$PGCONF" && \ + psql -c "CREATE USER wamos PASSWORD 'wamos'" && \ + createdb wamos && \ + psql -f wamos.sql -d wamos && \ + psql -f auth.sql -d wamos && \ + $PGBIN/pg_ctl stop -m smart + +# Set the default command to run when starting the container +CMD ["/usr/pgsql-10/bin/postgres", "-D", "/var/lib/pgsql/10/data"]
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/README Tue Jun 26 16:14:59 2018 +0200 @@ -0,0 +1,24 @@ +Build Dockerfile with e.g.: +$ docker build -t wamos . + +Get a running instance with e.g.: +$ docker run --name wamos -d -p 54321:5432 -v $PWD:/opt/wamos wamos + +Run tests for RLS policies as database superuser (within container) with: +$ psql -d wamos -Xf tap_tests.sql + +Create ER diagrams with e.g.: +Auxiliary tables: +$ postgresql_autodoc -p 54321 -h $dockerhost \ + -U wamos --password=wamos -t dot -l . -s 'wamos' +$ dot -Tpdf wamos.dot > wamos_auxiliary.pdf + +Waterway related tables: +$ postgresql_autodoc -p 54321 -h $dockerhost \ + -U wamos --password=wamos -t dot -l . -s 'wamos_waterway' +$ dot -Tpdf wamos.dot > wamos_waterway.pdf + +Fairway/bottleneck related tables: +$ postgresql_autodoc -p 54321 -h $dockerhost \ + -U wamos --password=wamos -t dot -l . -s 'wamos_fairway' +$ dot -Tpdf wamos.dot > wamos_fairway.pdf
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/auth.sql Tue Jun 26 16:14:59 2018 +0200 @@ -0,0 +1,89 @@ +BEGIN; + +-- +-- Roles, privileges and policies for the WAMOS database +-- +SET search_path TO public, wamos, wamos_waterway, wamos_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) +-- +CREATE ROLE waterway_user; +CREATE ROLE waterway_admin IN ROLE waterway_user; +CREATE ROLE sys_admin CREATEROLE BYPASSRLS IN ROLE waterway_admin; + +-- +-- 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 + 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 + 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; +GRANT SELECT ON responsibility_areas TO waterway_admin; + +-- +-- Extended privileges for sys_admin +-- +GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA wamos + TO sys_admin; + +-- +-- RLS policies for waterway_user +-- + +-- Staging area +CREATE FUNCTION create_hide_staging_policy() RETURNS void AS $$ +DECLARE the_table varchar; +BEGIN + FOREACH the_table IN ARRAY ARRAY[ + 'bottlenecks', 'sounding_results'] + -- TODO: add all relevant tables here + LOOP + EXECUTE format('CREATE POLICY hide_staging ON %I ' + 'FOR SELECT TO waterway_user USING (staging_done)', the_table); + EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', the_table); + END LOOP; +END; +$$ LANGUAGE plpgsql; +SELECT create_hide_staging_policy(); +DROP FUNCTION create_hide_staging_policy; + +CREATE POLICY see_yourself ON user_profiles FOR SELECT TO waterway_user + USING (username = current_user); +ALTER TABLE user_profiles ENABLE ROW LEVEL SECURITY; + +CREATE POLICY own_templates ON templates FOR SELECT TO waterway_user + USING (id IN(SELECT template_id FROM user_templates + WHERE username = current_user)); +ALTER TABLE templates ENABLE ROW LEVEL SECURITY; + +-- +-- RLS policies for waterway_admin +-- + +-- Staging area +-- TODO: add all relevant tables here +CREATE POLICY responsibility_area ON bottlenecks FOR ALL TO waterway_admin + USING (ST_Within(area, (SELECT area FROM responsibility_areas + WHERE country = (SELECT country FROM user_profiles)))); +CREATE POLICY responsibility_area ON sounding_results FOR ALL TO waterway_admin + USING (ST_Within(area, (SELECT area FROM responsibility_areas + WHERE country = (SELECT country FROM user_profiles)))); + +CREATE POLICY manage_templates ON templates FOR ALL TO waterway_admin + USING (id IN(SELECT template_id FROM user_templates ut + JOIN user_profiles p ON ut.username = p.username + WHERE p.country = (SELECT country FROM user_profiles))); + +COMMIT;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/dot.tmpl Tue Jun 26 16:14:59 2018 +0200 @@ -0,0 +1,19 @@ +digraph g { +graph [ +rankdir = "LR", +concentrate = true, +ratio = auto +]; +node [ +fontsize = "10", +shape = record +]; +edge [ +]; +<TMPL_LOOP name="schemas"><TMPL_LOOP name="tables"><TMPL_UNLESS name="view_definition"> +"<TMPL_IF name="number_of_schemas"><TMPL_VAR name="schema_dot">.</TMPL_IF name="number_of_schemas"><TMPL_VAR name="table_dot">" [shape = plaintext, label = <<TABLE BORDER="1" CELLBORDER="0" CELLSPACING="0"><TR><TD PORT="ltcol0"></TD><TD border="1" COLSPAN="2"> \N </TD><TD PORT="rtcol0"></TD></TR><TMPL_LOOP name="columns"><TR><TD PORT="ltcol<TMPL_VAR name="column_number">"></TD><TD align="left"><TMPL_VAR name="column_dot"></TD><TD align="left"><TMPL_VAR name="column_type"></TD><TD align="left" PORT="rtcol<TMPL_VAR name="column_number">"></TD></TR></TMPL_LOOP name="columns"></TABLE>> ]; +</TMPL_UNLESS name="view_definition"></TMPL_LOOP name="tables"></TMPL_LOOP name="schemas"> + +<TMPL_LOOP name="fk_links"> +"<TMPL_IF name="number_of_schemas"><TMPL_VAR name="handle0_schema">.</TMPL_IF name="number_of_schemas"><TMPL_VAR name="handle0_name">":rtcol<TMPL_VAR name="handle0_connection"> -> "<TMPL_IF name="number_of_schemas"><TMPL_VAR name="handle1_schema">.</TMPL_IF name="number_of_schemas"><TMPL_VAR name="handle1_name">":ltcol<TMPL_VAR name="handle1_connection"> [];</TMPL_LOOP name="fk_links"> +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/tap_tests.sql Tue Jun 26 16:14:59 2018 +0200 @@ -0,0 +1,81 @@ +-- +-- pgTAP test script +-- +BEGIN; + +CREATE EXTENSION pgtap; + +SELECT plan(10); -- Give number of tests that have to be run + +SET search_path TO public, wamos, wamos_waterway, wamos_fairway; + +\i tap_tests_data.sql + +-- +-- Run tests as unprivileged user +-- +SET SESSION AUTHORIZATION waterway_user; + +SELECT throws_ok('CREATE TABLE test()', 42501, NULL, + 'No objects can be created'); + +SELECT isnt_empty('SELECT * FROM bottlenecks', + 'Staged data should be visible'); +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], + 'User should only see his own profile'); +SELECT results_eq('SELECT username FROM user_profiles', + 'SELECT CAST(current_user AS varchar)', + 'User should only see his own profile'); + +SELECT isnt_empty('SELECT * FROM templates', + 'User should see templates associated to him'); +SELECT is_empty('SELECT * FROM templates t + JOIN user_templates ut ON t.id = template_id + WHERE username <> current_user', + 'User should only see templates associated to him'); + +-- +-- Run tests as waterway administrator +-- +SET SESSION AUTHORIZATION waterway_admin; + +PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS + INSERT INTO bottlenecks ( + bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, + revisiting_time, limiting, source_organization) + VALUES + ($1, + ('AT', 'XXX', '00001', '00000', 1)::isrs, + isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, + ('AT', 'XXX', '00001', '00000', 2)::isrs), + $2, 'AT', 'AT', 'AT', + 1, 'depth', 'testorganization'); +-- XXX: In contrast to what table 240 in the PostgreSQL docs says +-- (https://www.postgresql.org/docs/10/static/sql-createpolicy.html), +-- the following tests seem to show that the USING expression is applied +-- also to an INSERT: +SELECT lives_ok('EXECUTE bn_insert(''test1'', + ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'', + 4326))', + 'Waterway admin can insert data within his region'); +SELECT throws_ok('EXECUTE bn_insert(''test2'', + ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'', + 4326))', 42501, NULL, + 'Waterway admin cannot insert data outside his region'); + +-- XXX: Why does this fail? POLICY manage_templates should allow to see +-- the template of waterway_user. +SELECT isnt_empty('SELECT * FROM templates t + JOIN user_templates ut ON t.id = template_id + WHERE username <> current_user', + 'Waterway admin should see templates of users in country'); + +-- +-- finish tests +-- +SELECT * FROM finish(); + +ROLLBACK;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/tap_tests_data.sql Tue Jun 26 16:14:59 2018 +0200 @@ -0,0 +1,49 @@ +SET search_path TO public, wamos, wamos_waterway, wamos_fairway; + +INSERT INTO countries VALUES ('AT'); + +INSERT INTO responsibility_areas VALUES + ('AT', ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326)); + +INSERT INTO user_profiles (username, country, email_adress) + VALUES + ('waterway_user', 'AT', 'xxx'), + ('waterway_admin', 'AT', 'yyy'), + ('sys_admin', 'AT', 'zzz'); + +INSERT INTO limiting_factors VALUES ('depth'), ('width'); + +INSERT INTO gauges ( + location, function_code, objname, geom, zero_point, source_organization) + VALUES + (('AT', 'XXX', '00001', '00000', 1)::isrs, + 'xxx', + 'testgauge', + ST_geomfromtext('POINT(0 0)', 4326), + 0, + 'testorganization'); + +INSERT INTO bottlenecks ( + bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, + revisiting_time, limiting, source_organization, staging_done) + VALUES + ('testbottleneck1', + ('AT', 'XXX', '00001', '00000', 1)::isrs, + isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, + ('AT', 'XXX', '00001', '00000', 2)::isrs), + ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), + 'AT', 'AT', 'AT', + 1, 'depth', 'testorganization', false), + ('testbottleneck2', + ('AT', 'XXX', '00001', '00000', 1)::isrs, + isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, + ('AT', 'XXX', '00001', '00000', 2)::isrs), + ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), + 'AT', 'AT', 'AT', + 1, 'depth', 'testorganization', true); + +INSERT INTO templates (dummy_attrib) + VALUES ('usertemplate'), ('othertemplate'); +INSERT INTO user_templates + SELECT 'waterway_user', id FROM templates + WHERE dummy_attrib = 'usertemplate';
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/wamos.sql Tue Jun 26 16:14:59 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 + ); + +-- +-- 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;
--- a/tap_tests.sql Fri Jun 15 20:20:44 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,81 +0,0 @@ --- --- pgTAP test script --- -BEGIN; - -CREATE EXTENSION pgtap; - -SELECT plan(10); -- Give number of tests that have to be run - -SET search_path TO public, wamos, wamos_waterway, wamos_fairway; - -\i tap_tests_data.sql - --- --- Run tests as unprivileged user --- -SET SESSION AUTHORIZATION waterway_user; - -SELECT throws_ok('CREATE TABLE test()', 42501, NULL, - 'No objects can be created'); - -SELECT isnt_empty('SELECT * FROM bottlenecks', - 'Staged data should be visible'); -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], - 'User should only see his own profile'); -SELECT results_eq('SELECT username FROM user_profiles', - 'SELECT CAST(current_user AS varchar)', - 'User should only see his own profile'); - -SELECT isnt_empty('SELECT * FROM templates', - 'User should see templates associated to him'); -SELECT is_empty('SELECT * FROM templates t - JOIN user_templates ut ON t.id = template_id - WHERE username <> current_user', - 'User should only see templates associated to him'); - --- --- Run tests as waterway administrator --- -SET SESSION AUTHORIZATION waterway_admin; - -PREPARE bn_insert (varchar, geometry(POLYGON, 4326)) AS - INSERT INTO bottlenecks ( - bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, - revisiting_time, limiting, source_organization) - VALUES - ($1, - ('AT', 'XXX', '00001', '00000', 1)::isrs, - isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, - ('AT', 'XXX', '00001', '00000', 2)::isrs), - $2, 'AT', 'AT', 'AT', - 1, 'depth', 'testorganization'); --- XXX: In contrast to what table 240 in the PostgreSQL docs says --- (https://www.postgresql.org/docs/10/static/sql-createpolicy.html), --- the following tests seem to show that the USING expression is applied --- also to an INSERT: -SELECT lives_ok('EXECUTE bn_insert(''test1'', - ST_geomfromtext(''POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'', - 4326))', - 'Waterway admin can insert data within his region'); -SELECT throws_ok('EXECUTE bn_insert(''test2'', - ST_geomfromtext(''POLYGON((1 0, 1 1, 2 1, 2 0, 1 0))'', - 4326))', 42501, NULL, - 'Waterway admin cannot insert data outside his region'); - --- XXX: Why does this fail? POLICY manage_templates should allow to see --- the template of waterway_user. -SELECT isnt_empty('SELECT * FROM templates t - JOIN user_templates ut ON t.id = template_id - WHERE username <> current_user', - 'Waterway admin should see templates of users in country'); - --- --- finish tests --- -SELECT * FROM finish(); - -ROLLBACK;
--- a/tap_tests_data.sql Fri Jun 15 20:20:44 2018 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,49 +0,0 @@ -SET search_path TO public, wamos, wamos_waterway, wamos_fairway; - -INSERT INTO countries VALUES ('AT'); - -INSERT INTO responsibility_areas VALUES - ('AT', ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326)); - -INSERT INTO user_profiles (username, country, email_adress) - VALUES - ('waterway_user', 'AT', 'xxx'), - ('waterway_admin', 'AT', 'yyy'), - ('sys_admin', 'AT', 'zzz'); - -INSERT INTO limiting_factors VALUES ('depth'), ('width'); - -INSERT INTO gauges ( - location, function_code, objname, geom, zero_point, source_organization) - VALUES - (('AT', 'XXX', '00001', '00000', 1)::isrs, - 'xxx', - 'testgauge', - ST_geomfromtext('POINT(0 0)', 4326), - 0, - 'testorganization'); - -INSERT INTO bottlenecks ( - bottleneck_id, fk_g_fid, stretch, area, rb, lb, responsible_country, - revisiting_time, limiting, source_organization, staging_done) - VALUES - ('testbottleneck1', - ('AT', 'XXX', '00001', '00000', 1)::isrs, - isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, - ('AT', 'XXX', '00001', '00000', 2)::isrs), - ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), - 'AT', 'AT', 'AT', - 1, 'depth', 'testorganization', false), - ('testbottleneck2', - ('AT', 'XXX', '00001', '00000', 1)::isrs, - isrsrange(('AT', 'XXX', '00001', '00000', 0)::isrs, - ('AT', 'XXX', '00001', '00000', 2)::isrs), - ST_geomfromtext('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))', 4326), - 'AT', 'AT', 'AT', - 1, 'depth', 'testorganization', true); - -INSERT INTO templates (dummy_attrib) - VALUES ('usertemplate'), ('othertemplate'); -INSERT INTO user_templates - SELECT 'waterway_user', id FROM templates - WHERE dummy_attrib = 'usertemplate';
--- a/wamos.sql Fri Jun 15 20:20:44 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;