gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 2455:54c9fe587fe6

Subdivide SQL function to prepare for improved error handling The context of an error (e.g. the function in which it occured) can be inferred by the database client. Not doing all in one statement will render the context more meaningful.

author	Tom Gottfried <tom@intevation.de>
date	Fri, 01 Mar 2019 18:38:02 +0100
parents	960550ccca55
children	45d51a49f191

rev	line source
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	3
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	6
2172 7e2c77ccc02f Removed trigger to update date_info on gauge_measurements. Sascha Wilde <wilde@intevation.de> parents: 2170 diff changeset	7 -- Copyright (C) 2018,2019 by via donau
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	10
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	11 -- Author(s):
1301 2304778c4432 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1298 diff changeset	12 -- * Tom Gottfried <tom@intevation.de>
1336 f65d1767452c add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1301 diff changeset	13 -- * Sascha Teichmann <sascha.teichmann@intevation.de>
2172 7e2c77ccc02f Removed trigger to update date_info on gauge_measurements. Sascha Wilde <wilde@intevation.de> parents: 2170 diff changeset	14 -- * Sascha Wilde <sascha.wilde@intevation.de>
1298 6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	15
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	16 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	17
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	18 --
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	19 -- Infrastructure
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	20 --
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	21 CREATE EXTENSION postgis;
2083 6deafd6f7f86 Fix exclusion constraint for waterway profiles Tom Gottfried <tom@intevation.de> parents: 2076 diff changeset	22 -- needed for multi-column GiST indexes with otherwise unsupported types:
6deafd6f7f86 Fix exclusion constraint for waterway profiles Tom Gottfried <tom@intevation.de> parents: 2076 diff changeset	23 CREATE EXTENSION btree_gist;
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	24
96 d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	25 -- TODO: will there ever be UPDATEs or can we drop that function due to
d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	26 -- historicisation?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	27 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	28 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	29 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	30 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	31 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	32 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	33 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	34 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	35
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	36 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	37 -- GEMMA data
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	38 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	39
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	40 -- Namespace not to be accessed directly by any user
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	41 CREATE SCHEMA internal
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	42 -- Profile data are only accessible via the view users.list_users.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	43 CREATE TABLE user_profiles (
361 f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	44 username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	45 -- keep username length compatible with role identifier
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	46 map_extent box2d NOT NULL,
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	47 email_address varchar NOT NULL
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	48 )
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	49 -- Columns referencing user-visible schemas added below.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	50 ;
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	51
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	52
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	53 -- Namespace to be accessed by sys_admin only
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	54 CREATE SCHEMA sys_admin
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	55 CREATE TABLE system_config (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	56 config_key varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	57 config_val varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	58 )
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	59
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	60 CREATE TABLE password_reset_requests (
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	61 hash varchar(32) PRIMARY KEY,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	62 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	63 username varchar NOT NULL
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	64 REFERENCES internal.user_profiles(username)
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	65 ON DELETE CASCADE ON UPDATE CASCADE
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	66 )
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	67
624 8772979f8750 Remove test data from schema Tom Gottfried <tom@intevation.de> parents: 611 diff changeset	68 -- Tables with geo data to be published with GeoServer.
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	69 CREATE TABLE external_services (
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	70 local_name varchar PRIMARY KEY,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	71 remote_url varchar NOT NULL,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	72 is_wfs boolean NOT NULL DEFAULT TRUE
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	73 )
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	74
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	75 CREATE TABLE published_services (
598 4854a1e85870 Ensure published service is based on existing table Tom Gottfried <tom@intevation.de> parents: 580 diff changeset	76 name regclass PRIMARY KEY,
1288 9f7dc950ffd2 Accept only well-formed XML documents as style in database Tom Gottfried <tom@intevation.de> parents: 1194 diff changeset	77 style xml CHECK(style IS DOCUMENT),
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	78 as_wms boolean NOT NULL DEFAULT TRUE,
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	79 as_wfs boolean NOT NULL DEFAULT TRUE
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	80 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	81 ;
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	82
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	83 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	84 -- Look-up tables with data that are static in a running system
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	85 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	86 CREATE TABLE language_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	87 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	88 );
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	89
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	90 CREATE TABLE catccls (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	91 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	92 -- TODO: Do we need name and/or definition from IENC feature catalogue?
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	93 -- (see page 328 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	94 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	95 INSERT INTO catccls VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11);
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	96
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	97 CREATE TABLE dirimps (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	98 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	99 -- TODO: Do we need name and/or definition from IENC feature catalogue?
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	100 -- (see page 381 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	101 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	102 INSERT INTO dirimps VALUES (1), (2), (3), (4);
48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	103 -- dirimp_5 left out because it cannot be used for waterway area
85 1a640da943b6 Add waterway area attributes. Tom Gottfried <tom@intevation.de> parents: 84 diff changeset	104
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	105 CREATE TABLE depth_references (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	106 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	107 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	108 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	109 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	110 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	111 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	112 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	113 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	114 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	115
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	116 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	117 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	118 -- TODO: Do we need name and/or definition from IENC feature catalogue?
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	119 -- (see page 171 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	120 );
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	121 INSERT INTO catdis VALUES (1), (2), (3), (4);
78 012948f18faa Distance marks as link between 1D and 2D. Tom Gottfried <tom@intevation.de> parents: 77 diff changeset	122
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	123 CREATE TABLE position_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	124 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	125 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	126 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	127 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	128 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	129 -- Clarify!
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	130 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	131 );
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	132
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	133 CREATE TABLE levels_of_service (
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	134 level_of_service smallint PRIMARY KEY,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	135 name varchar(4)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	136 );
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	137 INSERT INTO levels_of_service (
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	138 level_of_service,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	139 name
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	140 ) VALUES (1, 'LOS1'), (2, 'LOS2'), (3, 'LOS3');
89 a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	141
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	142 CREATE TABLE riverbed_materials (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	143 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	144 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	145 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	146 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	147
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	148 CREATE TABLE survey_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	149 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	150 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	151
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	152 CREATE TABLE coverage_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	153 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	154 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	155
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	156 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	157 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	158 );
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	159
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	160 CREATE TABLE measure_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	161 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	162 );
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	163
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	164
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	165 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	166 CREATE SCHEMA users
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	167 CREATE TABLE responsibility_areas (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	168 country char(2) PRIMARY KEY REFERENCES countries,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	169 area geography(MULTIPOLYGON, 4326)
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	170 CHECK(ST_IsValid(CAST(area AS geometry)))
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	171 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	172
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	173 CREATE TABLE templates (
2367 0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2341 diff changeset	174 template_name varchar NOT NULL,
2267 37ae1bee3e4a Ajjusted RLS for user templates. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2230 diff changeset	175 country char(2) REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	176 template_data bytea NOT NULL,
2367 0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2341 diff changeset	177 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2341 diff changeset	178 UNIQUE (template_name, country)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	179 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	180 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	181 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	182 ;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	183 ALTER TABLE internal.user_profiles ADD
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	184 country char(2) NOT NULL REFERENCES users.responsibility_areas;
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	185
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	186
831 fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	187 -- Namespace for system wide configuration
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	188 CREATE SCHEMA systemconf
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	189 CREATE TABLE feature_colours (
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	190 feature_name varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	191 style_attr varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	192 r int NOT NULL CHECK (r >= 0 AND r < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	193 g int NOT NULL CHECK (g >= 0 AND g < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	194 b int NOT NULL CHECK (b >= 0 AND b < 256),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	195 a numeric NOT NULL CHECK (a >= 0 AND a <= 1),
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	196 PRIMARY KEY (feature_name, style_attr)
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	197 )
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	198 ;
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	199
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	200 -- Namespace for waterway data that can change in a running system
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	201 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	202
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	203 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	204 -- See https://roundup-intern.intevation.de/gemma/issue5
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	205 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	206 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	207 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	208 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	209 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	210 -- -- Is it a good idea to build B-tree indexes on relatively large
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	211 -- -- composites of string values or should we use inter PKs?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	212 -- -- => In case the index is used and cache space becomes a limiting
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	213 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	214 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	215 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	216 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	217
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	218 CREATE TABLE waterway_area (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	219 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	220 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	221 CHECK(ST_IsValid(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	222 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	223 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	224 )
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	225
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	226 CREATE TABLE gauges (
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	227 location isrs PRIMARY KEY CHECK(
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	228 (location).orc SIMILAR TO 'G[[:digit:]]{4}'
642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	229 AND CAST(substring((location).orc from 2 for 4) AS int) < 2048),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	230 objname varchar NOT NULL,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	231 geom geography(POINT, 4326) NOT NULL,
1826 d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	232 applicability_from_km int8,
d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	233 applicability_to_km int8,
d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	234 validity tstzrange,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	235 -- pasted text from a more general specification is given
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	236 -- (a gauge is not a berth!)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	237 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	238 zero_point double precision NOT NULL,
2369 89a3096e1988 Schema: Dropped wrong constraints in gauges.geodref. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2367 diff changeset	239 geodref varchar,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	240 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1826 d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1825 diff changeset	241 source_organization varchar
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	242 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	243 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	244 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	245
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	246 CREATE TABLE gauges_reference_water_levels (
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	247 gauge_id isrs NOT NULL REFERENCES gauges,
2318 06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	248 -- Omit foreign key constraint to be able to store not NtS-compliant
06c4e57435f1 Warn on import of unknown reference level codes Tom Gottfried <tom@intevation.de> parents: 2317 diff changeset	249 -- names, too:
2317 8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2300 diff changeset	250 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
8a8680e70d2e Cleanup schema for reference water levels Tom Gottfried <tom@intevation.de> parents: 2300 diff changeset	251 PRIMARY KEY (gauge_id, depth_reference),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	252 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	253 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	254
2451 960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	255 CREATE VIEW gauges_geoserver AS
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	256 SELECT
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	257 g.location,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	258 isrs_asText(g.location) AS isrs_code,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	259 g.objname,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	260 g.geom,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	261 g.applicability_from_km,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	262 g.applicability_to_km,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	263 g.validity,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	264 g.zero_point,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	265 g.geodref,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	266 g.date_info,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	267 g.source_organization,
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	268 json_strip_nulls(json_object_agg(coalesce(r.depth_reference,'empty'),
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	269 r.value))
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	270 AS reference_water_levels
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	271 FROM gauges g LEFT JOIN LATERAL (
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	272 SELECT gauge_id, depth_reference, value
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	273 FROM gauges_reference_water_levels
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	274 ) r ON r.gauge_id = g.location
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	275 GROUP BY g.location
960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	276
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	277 CREATE TABLE gauge_measurements (
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	278 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	279 fk_gauge_id isrs NOT NULL REFERENCES gauges,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	280 measure_date timestamp with time zone NOT NULL,
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	281 country_code char(2) NOT NULL REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	282 -- TODO: add relations to stuff provided as enumerations
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	283 sender varchar NOT NULL, -- "from" attribute from DRC
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	284 language_code varchar NOT NULL REFERENCES language_codes,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	285 date_issue timestamp with time zone NOT NULL,
1775 fcb0106ec510 Gauge measurement import: Added reference_code column. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1759 diff changeset	286 reference_code varchar(4) NOT NULL REFERENCES depth_references,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	287 water_level double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	288 predicted boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	289 is_waterlevel boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	290 -- XXX: "measure_code" if really only W or Q
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	291 -- XXX: Do we need "unit" attribute or can we normalise on import?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	292 value_min double precision, -- XXX: NOT NULL if predicted?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	293 value_max double precision, -- XXX: NOT NULL if predicted?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	294 --- TODO: Add a double range type for checking?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	295 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1780 48791416bea5 (Approved) gauge measurement import: Fixed row level security. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1775 diff changeset	296 source_organization varchar NOT NULL, -- "originator"
1783 8fd132b9cdbd Gauge measurements: Add an unique constraint so there could only Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1780 diff changeset	297 staging_done boolean NOT NULL DEFAULT false,
8fd132b9cdbd Gauge measurements: Add an unique constraint so there could only Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1780 diff changeset	298 -- So we can have a staged and
8fd132b9cdbd Gauge measurements: Add an unique constraint so there could only Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1780 diff changeset	299 -- a non-staged fk_gauge_id/measure_date pair.
8fd132b9cdbd Gauge measurements: Add an unique constraint so there could only Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1780 diff changeset	300 UNIQUE (fk_gauge_id, measure_date, staging_done)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	301 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	302
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	303 CREATE TABLE waterway_axis (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	304 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	305 wtwaxs geography(LINESTRING, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	306 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	307 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	308 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	309 nobjnam varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	310 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	311
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	312 -- This table allows linkage between 1D ISRS location codes and 2D space
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	313 -- e.g. for cutting bottleneck area out of waterway area based on virtual
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	314 -- distance marks along waterway axis (see SUC7).
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	315 CREATE TABLE distance_marks_virtual (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	316 location_code isrs PRIMARY KEY,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	317 geom geography(POINT, 4326) NOT NULL,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	318 related_enc varchar(12) NOT NULL
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	319 )
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	320
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	321 CREATE TABLE distance_marks (
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	322 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	323 country char(2) REFERENCES countries,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	324 hectom int,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	325 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	326 -- include location in primary key, because we have no fairway code:
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	327 catdis smallint REFERENCES catdis,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	328 position_code char(2) REFERENCES position_codes,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	329 related_enc varchar(12)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	330 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	331
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	332 -- A table to help geoserver serve the distance marks as WFS 1.1.0.
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	333 -- At least geoserver-2.13.2 does not serve type geography correctly
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	334 -- and does not serve the location_code as isrs type
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	335 CREATE VIEW distance_marks_geoserver AS
2170 3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	336 SELECT location_code,
3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	337 isrs_asText(location_code) AS location,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	338 geom::Geometry(POINT, 4326),
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	339 related_enc,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	340 (location_code).hectometre
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	341 FROM distance_marks_virtual
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	342
2300 3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	343 CREATE VIEW distance_marks_ashore_geoserver AS
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	344 SELECT id,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	345 country,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	346 geom::Geometry(POINT, 4326),
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	347 related_enc,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	348 hectom,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	349 catdis,
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	350 position_code
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	351 FROM distance_marks
3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	352
1446 1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	353 -- We need to configure primary keys for the views used by
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	354 -- geoserver for wfs, otherwise it will generate ids on the fly,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	355 -- which will change for the same feature...
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	356 -- See
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	357 -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	358 -- for details.
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	359 CREATE TABLE gt_pk_metadata (
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	360 table_schema VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	361 table_name VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	362 pk_column VARCHAR(32) NOT NULL,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	363 pk_column_idx INTEGER,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	364 pk_policy VARCHAR(32),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	365 pk_sequence VARCHAR(64),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	366 unique (table_schema, table_name, pk_column),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	367 check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	368 )
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	369
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	370 CREATE TABLE stretches (
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	371 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	372 name varchar NOT NULL,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	373 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	374 area geography(MULTIPOLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	375 CHECK(ST_IsValid(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	376 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	377 nobjnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	378 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	379 source_organization varchar NOT NULL,
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	380 staging_done boolean NOT NULL DEFAULT false,
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	381 UNIQUE(name, staging_done)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	382 )
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	383
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	384 CREATE TABLE stretch_countries (
1906 32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1894 diff changeset	385 stretches_id int NOT NULL REFERENCES stretches(id)
32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1894 diff changeset	386 ON DELETE CASCADE,
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	387 country_code char(2) NOT NULL REFERENCES countries(country_code),
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	388 UNIQUE(stretches_id, country_code)
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	389 )
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	390
1929 f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	391 -- Published view for GeoServer
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	392 CREATE VIEW stretches_geoserver AS SELECT
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	393 id,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	394 name,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	395 (stretch).lower::varchar as lower,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	396 (stretch).upper::varchar as upper,
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1975 diff changeset	397 area::Geometry(MULTIPOLYGON, 4326),
1929 f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	398 objnam,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	399 nobjnam,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	400 date_info,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	401 source_organization,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	402 (SELECT string_agg(country_code, ', ')
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	403 FROM stretch_countries
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	404 WHERE stretches_id = id) AS countries,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	405 staging_done
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	406 FROM stretches
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	407
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	408
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	409 CREATE TRIGGER sections_stretches_date_info
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	410 BEFORE UPDATE ON stretches
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	411 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	412
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	413 CREATE TABLE waterway_profiles (
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	414 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
2141 3f58809d2beb Waterway profiles have to match a distance mark Tom Gottfried <tom@intevation.de> parents: 2130 diff changeset	415 location isrs NOT NULL REFERENCES distance_marks_virtual,
3f58809d2beb Waterway profiles have to match a distance mark Tom Gottfried <tom@intevation.de> parents: 2130 diff changeset	416 geom geography(linestring, 4326),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	417 validity tstzrange,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	418 lnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	419 mwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	420 hnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	421 fe30 double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	422 fe100 double precision,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	423 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	424 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	425 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	426 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	427 source_organization varchar NOT NULL,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	428 staging_done boolean NOT NULL DEFAULT false,
2130 f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	429 EXCLUDE USING GIST (
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	430 isrs_asText(location) WITH =,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	431 validity WITH &&,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	432 CAST(staging_done AS int) WITH =)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	433 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	434 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	435 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	436 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	437
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	438 CREATE TABLE fairway_dimensions (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	439 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	440 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	441 CHECK(ST_IsValid(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	442 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	443 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	444 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	445 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	446 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	447 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	448 staging_done boolean NOT NULL DEFAULT false
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	449 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	450 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	451 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	452 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	453
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	454 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	455 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	456 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	457 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	458 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	459 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	460 CREATE TABLE bottlenecks (
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	461 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	462 bottleneck_id varchar UNIQUE NOT NULL,
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	463 fk_g_fid isrs NOT NULL REFERENCES gauges,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	464 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	465 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	466 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	467 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	468 area geography(MULTIPOLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	469 CHECK(ST_IsValid(CAST(area AS geometry))),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	470 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	471 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	472 responsible_country char(2) NOT NULL REFERENCES countries,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	473 revisiting_time smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	474 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	475 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	476 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	477 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	478 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	479 -- CHECK allowed combinations of surtyp and coverage or
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	480 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	481 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	482 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	483 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	484 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	485 -- additional_data xml -- Currently not relevant for GEMMA
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	486 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	487 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	488 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	489 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	490
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	491 CREATE TABLE bottlenecks_riverbed_materials (
2076 0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	492 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	493 riverbed varchar NOT NULL REFERENCES riverbed_materials,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	494 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	495 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	496 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	497
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	498 CREATE TABLE sounding_results (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	499 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	500 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	501 date_info date NOT NULL,
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	502 UNIQUE (bottleneck_id, date_info),
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	503 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	504 CHECK(ST_IsValid(CAST(area AS geometry))),
611 effd22c0ae5a Sounding result: Write simple SQL insert dumper. Not deterministic, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 609 diff changeset	505 surtyp varchar REFERENCES survey_types,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	506 coverage varchar REFERENCES coverage_types,
2444 7ca6bdb2d174 depth_reference in sounding data must be more permissive. Sascha Wilde <wilde@intevation.de> parents: 2396 diff changeset	507 depth_reference varchar(4) NOT NULL, -- REFERENCES depth_references,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	508 point_cloud geography(MULTIPOINTZ, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	509 CHECK(ST_IsSimple(CAST(point_cloud AS geometry))),
1131 2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	510 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	511 octree_index bytea,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	512 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	513 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	514
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	515 CREATE TABLE sounding_results_contour_lines (
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	516 sounding_result_id int NOT NULL REFERENCES sounding_results
dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	517 ON DELETE CASCADE,
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	518 height numeric NOT NULL,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	519 lines geography(multilinestring, 4326) NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	520 -- TODO: generate valid simple features and add constraint:
48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	521 -- CHECK(ST_IsSimple(CAST(lines AS geometry))),
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	522 PRIMARY KEY (sounding_result_id, height)
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	523 )
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	524 -- A view to help geoserver serve contour lines.
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	525 -- At least geoserver-2.13.2 does not serve type geography correctly
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	526 CREATE VIEW sounding_results_contour_lines_geoserver AS
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	527 SELECT bottleneck_id,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	528 date_info,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	529 height,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	530 CAST(lines AS geometry(multilinestring, 4326)) AS lines
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	531 FROM sounding_results_contour_lines cl
74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	532 JOIN sounding_results sr
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	533 ON sr.id = cl.sounding_result_id
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	534
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	535 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	536 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	537 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	538 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	539 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	540 position_code char(2) REFERENCES position_codes,
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	541 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	542 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	543 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	544 -- additional_data xml -- Currently not relevant for GEMMA
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	545 critical boolean,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	546 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	547 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	548 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	549 CREATE TRIGGER fairway_availability_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	550 BEFORE UPDATE ON fairway_availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	551 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	552
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	553 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	554 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	555 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	556 PRIMARY KEY (fairway_availability_id, level_of_service),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	557 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	558 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	559 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	560 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	561 IS NOT NULL),
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	562 shallowest_spot geography(POINT, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	563 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	564
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	565 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	566 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	567 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	568 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	569 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	570 pdf_generation_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	571 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	572 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	573
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	574 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	575 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	576 measure_date timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	577 level_of_service smallint NOT NULL REFERENCES levels_of_service,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	578 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	579 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	580 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	581 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	582 CHECK(COALESCE(available_depth_value, available_width_value,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	583 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	584 measure_type varchar NOT NULL REFERENCES measure_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	585 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	586 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	587 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	588 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	589 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	590 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	591 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	592 )
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	593
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	594 CREATE VIEW bottleneck_overview AS
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	595 SELECT
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	596 objnam AS name,
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	597 ST_Centroid(area)::Geometry AS point,
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	598 (lower(stretch)).hectometre AS from,
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	599 (upper(stretch)).hectometre AS to,
1520 6ad1f431bc85 Fixed date of latest measurement in bottlenecks list. Sascha Wilde <wilde@intevation.de> parents: 1446 diff changeset	600 sr.current::text
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	601 FROM bottlenecks bn LEFT JOIN (
74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	602 SELECT bottleneck_id, max(date_info) AS current FROM sounding_results
1572 056a86b24be2 Made bottleneck primary key an int. Attention: This may break something! Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1547 diff changeset	603 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.id
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	604 ORDER BY objnam
2396 6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	605
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	606 -- Published view for GeoServer
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	607 CREATE VIEW bottlenecks_geoserver AS
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	608 WITH fairway_availability_latest AS (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	609 SELECT DISTINCT ON (bottleneck_id) bottleneck_id,date_info,critical
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	610 FROM fairway_availability
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	611 ORDER BY bottleneck_id, date_info DESC NULLS LAST),
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	612 gauge_measurements_waterlevel AS (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	613 SELECT DISTINCT ON (fk_gauge_id)
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	614 fk_gauge_id, measure_date, predicted, water_level
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	615 FROM gauge_measurements WHERE predicted ='false'
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	616 ORDER BY fk_gauge_id, measure_date DESC NULLS LAST)
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	617 SELECT
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	618 b.id,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	619 b.bottleneck_id,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	620 b.objnam,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	621 b.nobjnm,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	622 b.stretch,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	623 b.area,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	624 b.rb,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	625 b.lb,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	626 b.responsible_country,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	627 b.revisiting_time,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	628 b.limiting,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	629 b.date_info,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	630 b.source_organization,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	631 g.location AS gauge_isrs_code,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	632 g.objname AS gauge_objname,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	633 json_object_agg(r.depth_reference, r.value) AS reference_water_levels,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	634 fal.date_info AS fa_date_info,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	635 fal.critical AS fa_critical,
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	636 gmw.water_level as gm_waterlevel
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	637 FROM bottlenecks b LEFT JOIN gauges g ON b.fk_g_fid = g.location
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	638 LEFT JOIN LATERAL (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	639 SELECT gauge_id,depth_reference,value
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	640 FROM gauges_reference_water_levels
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	641 ) r ON r.gauge_id = b.fk_g_fid
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	642 LEFT JOIN LATERAL (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	643 SELECT bottleneck_id,date_info,critical
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	644 FROM fairway_availability_latest
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	645 WHERE b.id=bottleneck_id) fal ON TRUE
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	646 LEFT JOIN LATERAL (
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	647 SELECT water_level
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	648 FROM gauge_measurements_waterlevel
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	649 WHERE b.fk_g_fid=fk_gauge_id) gmw ON TRUE
6af41bb6656f Using a table in a view before it is created is not possible Tom Gottfried <tom@intevation.de> parents: 2395 diff changeset	650 GROUP BY b.id, g.location, fal.date_info, fal.critical, gmw.water_level;
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	651 ;
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	652
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	653 -- Configure primary keys for geoserver views
2085 bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	654 INSERT INTO waterway.gt_pk_metadata VALUES
2451 960550ccca55 Added gauges layer. Sascha Wilde <wilde@intevation.de> parents: 2444 diff changeset	655 ('waterway', 'gauges_geoserver', 'location'),
2170 3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	656 ('waterway', 'distance_marks_geoserver', 'location_code'),
2300 3dd2de314b1b Use distance marks from wamos db in map. Sascha Wilde <wilde@intevation.de> parents: 2287 diff changeset	657 ('waterway', 'distance_marks_ashore_geoserver', 'id'),
2341 fb353ff08377 Added geoserver config for stretches layer. Sascha Wilde <wilde@intevation.de> parents: 2327 diff changeset	658 ('waterway', 'bottlenecks_geoserver', 'id'),
fb353ff08377 Added geoserver config for stretches layer. Sascha Wilde <wilde@intevation.de> parents: 2327 diff changeset	659 ('waterway', 'stretches_geoserver', 'id');
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	660
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	661 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	662 -- Import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	663 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	664 CREATE TYPE import_state AS ENUM (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	665 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	666 'running',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	667 'failed', 'unchanged', 'pending',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	668 'accepted', 'declined'
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	669 );
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	670
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	671 CREATE TYPE log_type AS ENUM ('info', 'warn', 'error');
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	672
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	673 -- Namespace for import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	674 CREATE SCHEMA import
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	675
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	676 CREATE TABLE import_configuration (
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	677 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	678 username varchar NOT NULL
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	679 REFERENCES internal.user_profiles(username)
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	680 ON DELETE CASCADE
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	681 ON UPDATE CASCADE,
2042 d29ac997eb34 This breaks this branch!!!! Starting to remove the old persistent layer for configured imports. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1995 diff changeset	682 kind varchar NOT NULL
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	683 )
1702 49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	684
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	685 CREATE TABLE import_configuration_attributes (
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	686 import_configuration_id int NOT NULL
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	687 REFERENCES import_configuration(id)
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	688 ON DELETE CASCADE
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	689 ON UPDATE CASCADE,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	690 k VARCHAR NOT NULL,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	691 v TEXT NOT NULL,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	692 UNIQUE (import_configuration_id, k)
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	693 )
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	694
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	695 CREATE TABLE imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	696 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	697 state import_state NOT NULL DEFAULT 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	698 kind varchar NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	699 enqueued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	700 due timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	701 retry_wait interval
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	702 CHECK(retry_wait IS NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	703 OR retry_wait >= interval '0 microseconds'),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	704 trys_left int, -- if NULL and retry_wait NOT NULL, endless
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	705 username varchar NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	706 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	707 ON DELETE CASCADE
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	708 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	709 signer varchar
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	710 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	711 ON DELETE SET NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	712 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	713 send_email boolean NOT NULL DEFAULT false,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	714 data TEXT,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	715 summary TEXT
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	716 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	717
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	718 CREATE INDEX enqueued_idx ON imports(enqueued, state)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	719
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	720 CREATE TABLE import_logs (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	721 import_id int NOT NULL REFERENCES imports(id)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	722 ON DELETE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	723 time timestamp NOT NULL DEFAULT now(),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	724 kind log_type NOT NULL DEFAULT 'info',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	725 msg TEXT NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	726 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	727
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	728 CREATE TABLE track_imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	729 import_id int NOT NULL REFERENCES imports(id)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	730 ON DELETE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	731 relation regclass NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	732 key int NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	733 UNIQUE (relation, key)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	734 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	735 ;
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	736
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	737 CREATE FUNCTION import.del_import(imp_id int) RETURNS void AS
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	738 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	739 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	740 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	741 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	742 FOR tmp IN
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	743 SELECT * FROM import.track_imports WHERE import_id = imp_id
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	744 LOOP
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	745 EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	746 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	747 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	748 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	749 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	750
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	751 CREATE FUNCTION import.del_import() RETURNS trigger AS
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	752 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	753 BEGIN
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	754 EXECUTE format('DELETE FROM %s WHERE id = $1', OLD.relation) USING OLD.key;
1354 1000e0e71d5a Added missing rights and fixed trigger function. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1338 diff changeset	755 RETURN NULL;
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	756 END;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	757 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	758 LANGUAGE plpgsql;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	759
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	760 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 2455:54c9fe587fe6