Mercurial > gemma
view wamos.sql @ 59:37faa7359ea6
Interfaces may provide timestamps with time zone.
Storing the time zone seems easier than normalising
everything to UTC at application level.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Thu, 24 May 2018 14:41:41 +0200 |
parents | 30cb2f87c268 |
children | f0ca64da9446 |
line wrap: on
line source
BEGIN; CREATE EXTENSION postgis; 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 ); -- 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 gauges ( location isrs PRIMARY KEY, dummy_attrib varchar, "..." varchar -- TODO: add real gauge attributes (DRC 2.1.4) ); CREATE TABLE 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. ); -- -- Bottlenecks -- 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 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) ); -- 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 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, 3146) 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 ); CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks FOR EACH ROW EXECUTE PROCEDURE update_date_info(); CREATE TABLE 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 sounding_results ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, date_info date NOT NULL, PRIMARY KEY (bottleneck_id, date_info), area geometry(POLYGON, 3146) 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 ); -- -- Fairway availability -- CREATE TABLE spot_marks ( mark_name varchar PRIMARY KEY -- Use smallint because of fairway availability provided on daily basis? ); CREATE TABLE levels_of_service ( level_of_service smallint PRIMARY KEY ); CREATE TABLE measure_types ( measure_type varchar PRIMARY KEY ); CREATE TABLE fairway_availability ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, PRIMARY KEY (bottleneck_id, surdat), position varchar REFERENCES spot_marks, -- 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 fa_reference_values ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, level_of_service smallint NOT NULL REFERENCES levels_of_service, PRIMARY KEY (bottleneck_id, surdat, 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, 3146) ); CREATE TABLE bottleneck_pdfs ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, FOREIGN KEY (bottleneck_id, surdat) 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 (bottleneck_id, surdat, profile_pdf_url), pdf_generation_date timestamp with time zone NOT NULL, source_organization varchar NOT NULL ); CREATE TABLE effective_fairway_availability ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, measure_date timestamp with time zone NOT NULL, level_of_service smallint NOT NULL REFERENCES levels_of_service, PRIMARY KEY (bottleneck_id, surdat, 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;