Mercurial > gemma
changeset 64:1e9589cf9c00
Create specific wamos schema.
This allows to use the -s option for postgresql_autodoc.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Fri, 25 May 2018 21:34:02 +0200 |
parents | c18f1db22077 |
children | cad19acc53b3 |
files | wamos.sql |
diffstat | 1 files changed, 20 insertions(+), 17 deletions(-) [+] |
line wrap: on
line diff
--- a/wamos.sql Fri May 25 21:22:55 2018 +0200 +++ b/wamos.sql Fri May 25 21:34:02 2018 +0200 @@ -2,6 +2,9 @@ CREATE EXTENSION postgis; +CREATE SCHEMA wamos; +SET search_path TO public, wamos; + CREATE FUNCTION update_date_info() RETURNS trigger LANGUAGE plpgsql AS $$ @@ -42,14 +45,14 @@ -- EXCLUDE USING GIST (stretch WITH &&) -- ); -CREATE TABLE gauges ( +CREATE TABLE wamos.gauges ( location isrs PRIMARY KEY, dummy_attrib varchar, "..." varchar -- TODO: add real gauge attributes (DRC 2.1.4) ); -CREATE TABLE countries ( +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 @@ -59,25 +62,25 @@ -- -- Bottlenecks -- -CREATE TABLE riverbed_materials ( +CREATE TABLE wamos.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 ( +CREATE TABLE wamos.survey_types ( survey_type varchar PRIMARY KEY ); -CREATE TABLE coverage_types ( +CREATE TABLE wamos.coverage_types ( coverage_type varchar PRIMARY KEY ); -CREATE TABLE limiting_factors ( +CREATE TABLE wamos.limiting_factors ( limiting_factor varchar PRIMARY KEY ); -CREATE TABLE depth_references ( +CREATE TABLE wamos.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 @@ -91,7 +94,7 @@ -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL) -CREATE TABLE bottlenecks ( +CREATE TABLE wamos.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. @@ -120,14 +123,14 @@ CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks FOR EACH ROW EXECUTE PROCEDURE update_date_info(); -CREATE TABLE bottlenecks_riverbed_materials ( +CREATE TABLE wamos.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 ( +CREATE TABLE wamos.sounding_results ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, date_info date NOT NULL, PRIMARY KEY (bottleneck_id, date_info), @@ -142,20 +145,20 @@ -- -- Fairway availability -- -CREATE TABLE spot_marks ( +CREATE TABLE wamos.spot_marks ( mark_name varchar PRIMARY KEY -- Use smallint because of fairway availability provided on daily basis? ); -CREATE TABLE levels_of_service ( +CREATE TABLE wamos.levels_of_service ( level_of_service smallint PRIMARY KEY ); -CREATE TABLE measure_types ( +CREATE TABLE wamos.measure_types ( measure_type varchar PRIMARY KEY ); -CREATE TABLE fairway_availability ( +CREATE TABLE wamos.fairway_availability ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, PRIMARY KEY (bottleneck_id, surdat), @@ -169,7 +172,7 @@ BEFORE UPDATE ON fairway_availability FOR EACH ROW EXECUTE PROCEDURE update_date_info(); -CREATE TABLE fa_reference_values ( +CREATE TABLE wamos.fa_reference_values ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, @@ -182,7 +185,7 @@ shallowest_spot geometry(POINT, 3146) ); -CREATE TABLE bottleneck_pdfs ( +CREATE TABLE wamos.bottleneck_pdfs ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability, @@ -193,7 +196,7 @@ source_organization varchar NOT NULL ); -CREATE TABLE effective_fairway_availability ( +CREATE TABLE wamos.effective_fairway_availability ( bottleneck_id varchar NOT NULL REFERENCES bottlenecks, surdat date NOT NULL, FOREIGN KEY (bottleneck_id, surdat) REFERENCES fairway_availability,