Mercurial > gemma
view schema/gemma.sql @ 4504:b7e3d42fd9e7
When fetching gauge measurements look 1day into past and future.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 02 Oct 2019 15:18:49 +0200 |
parents | 7128741faeb9 |
children | f7b57136c800 d2eac69ba86b |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2018, 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Tom Gottfried <tom@intevation.de> -- * Sascha Teichmann <sascha.teichmann@intevation.de> -- * Sascha Wilde <sascha.wilde@intevation.de> BEGIN; -- -- Extensions -- CREATE EXTENSION postgis; -- needed for multi-column GiST indexes with otherwise unsupported types: CREATE EXTENSION btree_gist; -- -- Trigger functions -- -- 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; $$; -- Trigger function to be used as a constraint trigger to enforce uniqueness -- of geometries in the column with its name given as an argument to the -- trigger function CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS $$ DECLARE new_geom geometry; has_equal boolean; BEGIN EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0]) INTO new_geom USING NEW; EXECUTE format( 'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I ' 'WHERE id <> $2', TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME) INTO has_equal USING new_geom, NEW.id; IF has_equal THEN RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('Failing row contains geometry in %s', Box2D(new_geom)), ERRCODE = 23505, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; -- Trigger function to be used as a constraint trigger to enforce -- existance of a referenced gauge with intersecting validity. The -- columns with the referenced gauge isrs code an the validity are -- given as arguments to the trigger function. CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS $$ DECLARE -- FIXME: I'm using text for the isrs code and cast it on demand. -- If someone is able to get it to work with isrs or isrs_base as -- type, feel free to show me how its done... ;-) [sw] referenced_gauge text; new_validity tstzrange; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_gauge USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_validity USING NEW; IF EXISTS ( SELECT * FROM waterway.gauges WHERE location = referenced_gauge::isrs AND validity && new_validity ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching gauge %s found.', isrs_AsText(referenced_gauge::isrs)), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; -- The same for objects with a timestamp instead of a validity range. CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS $$ DECLARE -- FIXME: I'm using text for the isrs code and cast it on demand. -- If someone is able to get it to work with isrs or isrs_base as -- type, feel free to show me how its done... ;-) [sw] referenced_gauge text; new_tstz timestamptz; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_gauge USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_tstz USING NEW; IF EXISTS ( SELECT * FROM waterway.gauges WHERE location = referenced_gauge::isrs AND validity @> new_tstz ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching gauge %s found.', isrs_AsText(referenced_gauge::isrs)), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; -- Trigger function to be used as a constraint trigger to enforce -- existance of a referenced bottleneck with validity at a given time. -- The columns with the referenced bottleneck id and the timestamp are -- given as arguments to the trigger function. CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS $$ DECLARE referenced_bottleneck_id text; new_tstz timestamptz; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_bottleneck_id USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_tstz USING NEW; IF EXISTS ( SELECT * FROM waterway.bottlenecks WHERE bottleneck_id = referenced_bottleneck_id AND validity @> new_tstz ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching bottleneck %s for %s found.', referenced_bottleneck_id, new_tstz), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; -- Constraint trigger: sounding Results must intersect with the area -- of the bottleneck they belong to. The "xx" at the beginning of the -- name is to ensure, it is fired last after other triggers. CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN IF NOT st_intersects((SELECT area FROM waterway.bottlenecks WHERE bottleneck_id = NEW.bottleneck_id AND validity @> NEW.date_info::timestamptz), NEW.area) THEN RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = 'Failing row area has no intersection with bottleneck.', ERRCODE = 23514, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, CONSTRAINT = TG_NAME; END IF; RETURN NEW; END; $$; -- -- GEMA meta data -- CREATE TABLE gemma_schema_version ( version int PRIMARY KEY, update_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP ); CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int LANGUAGE sql AS $$ SELECT max(version) FROM public.gemma_schema_version; $$; -- -- GEMMA data -- -- -- Look-up tables with data that are static in a running system -- CREATE TABLE language_codes ( language_code varchar PRIMARY KEY ); CREATE TABLE catccls ( catccl smallint PRIMARY KEY -- TODO: Do we need name and/or definition from IENC feature catalogue? -- (see page 328 of edition 2.3) ); INSERT INTO catccls VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11); CREATE TABLE dirimps ( dirimp smallint PRIMARY KEY -- TODO: Do we need name and/or definition from IENC feature catalogue? -- (see page 381 of edition 2.3) ); INSERT INTO dirimps VALUES (1), (2), (3), (4); -- dirimp_5 left out because it cannot be used for waterway area CREATE TABLE 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 catdis ( catdis smallint PRIMARY KEY -- TODO: Do we need name and/or definition from IENC feature catalogue? -- (see page 171 of edition 2.3) ); INSERT INTO catdis VALUES (1), (2), (3), (4); CREATE TABLE 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? ); CREATE TABLE levels_of_service ( level_of_service smallint PRIMARY KEY, name varchar(4) ); INSERT INTO levels_of_service ( level_of_service, name ) VALUES (1, 'LOS1'), (2, 'LOS2'), (3, 'LOS3'); CREATE TABLE 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 survey_types ( survey_type varchar PRIMARY KEY ); CREATE TABLE coverage_types ( coverage_type varchar PRIMARY KEY ); CREATE TABLE limiting_factors ( limiting_factor varchar PRIMARY KEY ); CREATE TABLE measure_types ( measure_type varchar PRIMARY KEY ); CREATE TYPE template_types AS ENUM ( 'map', 'diagram', 'report' ); -- Namespace not to be accessed directly by any user CREATE SCHEMA internal -- Profile data are only accessible via the view users.list_users. CREATE TABLE user_profiles ( username varchar PRIMARY KEY CHECK(octet_length(username) <= 63), -- keep username length compatible with role identifier country char(2) NOT NULL REFERENCES countries, map_extent box2d NOT NULL, email_address varchar NOT NULL ) -- Columns referencing user-visible schemas added below. ; -- Namespace to be accessed by sys_admin only CREATE SCHEMA sys_admin CREATE TABLE system_config ( config_key varchar PRIMARY KEY, config_val varchar ) CREATE TABLE password_reset_requests ( hash varchar(32) PRIMARY KEY, issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, username varchar NOT NULL UNIQUE REFERENCES internal.user_profiles(username) ON DELETE CASCADE ON UPDATE CASCADE ) -- Tables with geo data to be published with GeoServer. CREATE TABLE external_services ( local_name varchar PRIMARY KEY, remote_url varchar NOT NULL, is_wfs boolean NOT NULL DEFAULT TRUE ) CREATE TABLE published_services ( name regclass PRIMARY KEY, style xml CHECK(style IS DOCUMENT), as_wms boolean NOT NULL DEFAULT TRUE, as_wfs boolean NOT NULL DEFAULT TRUE ) ; -- Namespace for user management related data CREATE SCHEMA users CREATE TABLE stretches ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name varchar NOT NULL, stretch isrsrange NOT NULL, area geography(MULTIPOLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), objnam varchar NOT NULL, nobjnam varchar, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false, UNIQUE(name, staging_done) ) CREATE TRIGGER stretches_date_info BEFORE UPDATE ON stretches FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE stretch_countries ( stretch_id int NOT NULL REFERENCES stretches(id) ON DELETE CASCADE, country char(2) NOT NULL REFERENCES countries, PRIMARY KEY(stretch_id, country) ) CREATE TABLE templates ( template_name varchar NOT NULL, template_type template_types NOT NULL DEFAULT 'map'::template_types, country char(2) REFERENCES countries, template_data bytea NOT NULL, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (template_name, template_type, country) ) CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates FOR EACH ROW EXECUTE PROCEDURE update_date_info() ; -- Namespace for waterway data that can change in a running system CREATE SCHEMA waterway -- Eventually obsolete. -- See https://roundup-intern.intevation.de/gemma/issue5 -- CREATE TABLE rwdrs ( -- tretch 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 waterway_area ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, area geography(POLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), catccl smallint REFERENCES catccls, dirimp smallint REFERENCES dirimps ) CREATE CONSTRAINT TRIGGER waterway_area_area_unique AFTER INSERT OR UPDATE OF area ON waterway_area FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area') CREATE TABLE gauges ( location isrs CHECK( (location).orc SIMILAR TO 'G[[:digit:]]{4}' AND CAST(substring((location).orc from 2 for 4) AS int) < 2048), objname varchar NOT NULL, geom geography(POINT, 4326) NOT NULL, applicability_from_km int8, applicability_to_km int8, validity tstzrange NOT NULL CHECK (NOT isempty(validity)), zero_point double precision NOT NULL, geodref varchar, date_info timestamp with time zone NOT NULL, source_organization varchar, lastupdate timestamp with time zone NOT NULL, -- entry removed from external data source (RIS-Index)/historicised: erased boolean NOT NULL DEFAULT false, PRIMARY KEY (location, validity), EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&) DEFERRABLE INITIALLY DEFERRED ) -- Allow only one non-erased entry per location CREATE UNIQUE INDEX gauges_erased_unique_constraint ON gauges (location) WHERE NOT erased CREATE TABLE gauges_reference_water_levels ( location isrs NOT NULL, validity tstzrange NOT NULL, FOREIGN KEY (location, validity) REFERENCES gauges ON UPDATE CASCADE, -- Omit foreign key constraint to be able to store not NtS-compliant -- names, too: depth_reference varchar NOT NULL, -- REFERENCES depth_references, PRIMARY KEY (location, validity, depth_reference), value int NOT NULL ) CREATE TABLE gauge_measurements ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, location isrs NOT NULL, measure_date timestamp with time zone NOT NULL, country_code char(2) NOT NULL REFERENCES countries, sender varchar NOT NULL, -- "from" element from NtS response 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, water_level double precision NOT NULL, date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL, -- "originator" from NtS response staging_done boolean NOT NULL DEFAULT false, UNIQUE (measure_date, location, staging_done) ) CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge AFTER INSERT OR UPDATE OF location ON gauge_measurements FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') CREATE TABLE gauge_predictions ( location isrs NOT NULL, measure_date timestamp with time zone NOT NULL, country_code char(2) NOT NULL REFERENCES countries, sender varchar NOT NULL, -- "from" element from NtS response 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, water_level double precision NOT NULL, conf_interval numrange CHECK (conf_interval @> CAST(water_level AS numeric)), date_info timestamp with time zone NOT NULL, source_organization varchar NOT NULL, -- "originator" from NtS response PRIMARY KEY (measure_date, location, date_issue) ) CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge AFTER INSERT OR UPDATE OF location ON gauge_predictions FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') CREATE TABLE waterway_axis ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, wtwaxs geography(LINESTRING, 4326) NOT NULL CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))), -- TODO: Do we need to check data set quality (DRC 2.1.6)? objnam varchar NOT NULL, nobjnam varchar ) CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique AFTER INSERT OR UPDATE OF wtwaxs ON waterway_axis FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs') -- This table allows linkage between 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 distance_marks_virtual ( location_code isrs PRIMARY KEY, geom geography(POINT, 4326) NOT NULL, related_enc varchar(12) NOT NULL, wwname varchar ) CREATE TABLE distance_marks ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, country char(2) REFERENCES countries, hectom int, geom geography(POINT, 4326) NOT NULL, -- include location in primary key, because we have no fairway code: catdis smallint REFERENCES catdis, position_code char(2) REFERENCES position_codes, related_enc varchar(12) ) -- We need to configure primary keys for the views used by -- geoserver for wfs, otherwise it will generate ids on the fly, -- which will change for the same feature... -- See -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html -- for details. CREATE TABLE gt_pk_metadata ( table_schema VARCHAR(32) NOT NULL, table_name VARCHAR(32) NOT NULL, pk_column VARCHAR(32) NOT NULL, pk_column_idx INTEGER, pk_policy VARCHAR(32), pk_sequence VARCHAR(64), unique (table_schema, table_name, pk_column), check (pk_policy in ('sequence', 'assigned', 'autogenerated')) ) -- Like stretches without the countries CREATE TABLE sections ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name varchar NOT NULL, section isrsrange NOT NULL, area geography(MULTIPOLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), objnam varchar NOT NULL, nobjnam varchar, date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false, UNIQUE(name, staging_done) ) CREATE TRIGGER sections_date_info BEFORE UPDATE ON sections FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE waterway_profiles ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, location isrs NOT NULL REFERENCES distance_marks_virtual, geom geography(linestring, 4326), validity tstzrange, lnwl double precision, mwl double precision, hnwl double precision, fe30 double precision, fe100 double precision, -- 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, staging_done boolean NOT NULL DEFAULT false, EXCLUDE USING GIST ( isrs_asText(location) WITH =, validity WITH &&, CAST(staging_done AS int) WITH =) ) CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles FOR EACH ROW EXECUTE PROCEDURE update_date_info() CREATE TABLE fairway_dimensions ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, area geography(POLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), 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, staging_done boolean NOT NULL DEFAULT false ) CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions FOR EACH ROW EXECUTE PROCEDURE update_date_info() -- -- Bottlenecks -- CREATE TABLE bottlenecks ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar NOT NULL, validity tstzrange NOT NULL CHECK (NOT isempty(validity)), gauge_location isrs NOT NULL, objnam varchar, nobjnm varchar, stretch isrsrange NOT NULL, area geography(MULTIPOLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), rb char(2) REFERENCES countries, -- from rb_lb in interface lb char(2) REFERENCES countries, -- from rb_lb in interface responsible_country char(2) NOT NULL REFERENCES countries, revisiting_time smallint, 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, source_organization varchar NOT NULL, staging_done boolean NOT NULL DEFAULT false, UNIQUE (bottleneck_id, validity, staging_done), EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&, CAST(staging_done AS int) WITH =) DEFERRABLE INITIALLY DEFERRED ) CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity') CREATE TABLE bottlenecks_riverbed_materials ( bottleneck_id int NOT NULL REFERENCES bottlenecks(id) ON DELETE CASCADE, riverbed varchar NOT NULL REFERENCES riverbed_materials, -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3 PRIMARY KEY (bottleneck_id, riverbed) ) CREATE TABLE sounding_results ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, bottleneck_id varchar NOT NULL, date_info date NOT NULL, UNIQUE (bottleneck_id, date_info), area geography(POLYGON, 4326) NOT NULL CHECK(ST_IsValid(CAST(area AS geometry))), surtyp varchar REFERENCES survey_types, coverage varchar REFERENCES coverage_types, depth_reference varchar NOT NULL, -- REFERENCES depth_references, octree_checksum varchar, octree_index bytea, staging_done boolean NOT NULL DEFAULT false ) CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck AFTER INSERT OR UPDATE OF bottleneck_id ON sounding_results FOR EACH ROW EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area AFTER INSERT OR UPDATE ON sounding_results FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area() CREATE TABLE sounding_results_contour_lines ( sounding_result_id int NOT NULL REFERENCES sounding_results ON DELETE CASCADE, height numeric NOT NULL, lines geography(multilinestring, 4326) NOT NULL, -- TODO: generate valid simple features and add constraint: -- CHECK(ST_IsSimple(CAST(lines AS geometry))), PRIMARY KEY (sounding_result_id, height) ) -- -- Fairway availability -- CREATE TABLE fairway_availability ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, position varchar, bottleneck_id varchar NOT NULL, 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, source_organization varchar NOT NULL ) -- FIXME: From the DRC it is unclear what the exact semantics of -- surdat and Date_Info ar unclear. Currently we assume that -- (fk_bn_fid,surdat) has to be unique, but that might be false. -- Anyway, I will date_info here to check for an matching -- reference gauge at the bottleneck. The reason for this -- decision is purely practical (and might be semantically -- disputable: the bottleneck data in the demo system is not old -- enough to cover rthe surdat times... CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck AFTER INSERT OR UPDATE OF bottleneck_id ON fairway_availability FOR EACH ROW EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') CREATE TABLE 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 geography(POINT, 4326) ) CREATE TABLE 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 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) ) ; -- Configure primary keys for geoserver views INSERT INTO waterway.gt_pk_metadata VALUES ('waterway', 'gauges_geoserver', 'isrs_code'), ('waterway', 'distance_marks_geoserver', 'location'), ('waterway', 'distance_marks_ashore_geoserver', 'id'), ('waterway', 'bottlenecks_geoserver', 'id'), ('waterway', 'stretches_geoserver', 'id'), ('waterway', 'sections_geoserver', 'id'); -- -- Import queue and respective logging -- CREATE TYPE import_state AS ENUM ( 'queued', 'running', 'failed', 'unchanged', 'pending', 'accepted', 'declined' ); CREATE TYPE log_type AS ENUM ('info', 'warn', 'error'); -- Namespace for import queue and respective logging CREATE SCHEMA import CREATE TABLE import_configuration ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, username varchar NOT NULL REFERENCES internal.user_profiles(username) ON DELETE CASCADE ON UPDATE CASCADE, kind varchar NOT NULL ) CREATE TABLE import_configuration_attributes ( import_configuration_id int NOT NULL REFERENCES import_configuration(id) ON DELETE CASCADE ON UPDATE CASCADE, k VARCHAR NOT NULL, v TEXT NOT NULL, UNIQUE (import_configuration_id, k) ) CREATE TABLE imports ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, state import_state NOT NULL DEFAULT 'queued', kind varchar NOT NULL, enqueued timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, due timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, retry_wait interval CHECK(retry_wait IS NULL OR retry_wait >= interval '0 microseconds'), trys_left int, -- if NULL and retry_wait NOT NULL, endless username varchar NOT NULL REFERENCES internal.user_profiles(username) ON DELETE CASCADE ON UPDATE CASCADE, signer varchar REFERENCES internal.user_profiles(username) ON DELETE SET NULL ON UPDATE CASCADE, send_email boolean NOT NULL DEFAULT false, data TEXT, summary TEXT ) CREATE INDEX enqueued_idx ON imports(enqueued, state) CREATE TABLE import_logs ( import_id int NOT NULL REFERENCES imports(id) ON DELETE CASCADE, time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, kind log_type NOT NULL DEFAULT 'info', msg TEXT NOT NULL ) CREATE INDEX import_logs_import_id ON import.import_logs (import_id) CREATE TABLE track_imports ( import_id int NOT NULL REFERENCES imports(id) ON DELETE CASCADE, deletion bool NOT NULL DEFAULT false, relation regclass NOT NULL, key int NOT NULL, UNIQUE (relation, key) ) ; CREATE FUNCTION import.del_import(imp_id int) RETURNS void AS $$ DECLARE tmp RECORD; BEGIN FOR tmp IN SELECT relation, array_agg(key) AS keys FROM import.track_imports WHERE import_id = imp_id AND NOT deletion GROUP BY relation LOOP EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation) USING tmp.keys; END LOOP; END; $$ LANGUAGE plpgsql; CREATE SCHEMA caching CREATE TABLE sounding_differences ( id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, minuend int NOT NULL REFERENCES waterway.sounding_results(id) ON DELETE CASCADE, subtrahend int NOT NULL REFERENCES waterway.sounding_results(id) ON DELETE CASCADE, UNIQUE (minuend, subtrahend) ) CREATE TABLE sounding_differences_contour_lines ( sounding_differences_id int NOT NULL REFERENCES sounding_differences(id) ON DELETE CASCADE, height numeric NOT NULL, lines geography(multilinestring, 4326) NOT NULL, PRIMARY KEY (sounding_differences_id, height) ) ; COMMIT;