gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 2230:4374d942b23d

Remove debris and associate templates to country While the original idea was to associate templates to users, managed by a waterway admin per country, the new intention was to share templates between users per country. Just removing any constraints on readability and management of the templates did not fulfill this idea.

author	Tom Gottfried <tom@intevation.de>
date	Wed, 13 Feb 2019 10:52:14 +0100
parents	7e2c77ccc02f
children	37ae1bee3e4a

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 countries (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	87 country_code char(2) PRIMARY KEY -- ISO 3166 country code
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	88 -- A smallint PK would require even less disk space i.e. on the FK side.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	89 -- This might be an issue in case cache space becomes a limiting
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	90 -- factor when there are many FKs pointing here.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	91 );
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	92
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	93 CREATE TABLE language_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	94 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	95 );
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	96
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	97 CREATE TABLE catccls (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	98 catccl 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 328 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 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	103
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	104 CREATE TABLE dirimps (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	105 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	106 -- 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	107 -- (see page 381 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	108 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	109 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	110 -- 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	111
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	112 CREATE TABLE depth_references (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	113 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	114 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	115 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	116 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	117 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	118 -- 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	119 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	120 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	121 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	122
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	123 CREATE TABLE reference_water_levels (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	124 reference_water_level varchar(20) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	125 );
70 28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	126
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	127 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	128 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	129 -- 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	130 -- (see page 171 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	131 );
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	132 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	133
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	134 CREATE TABLE position_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	135 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	136 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	137 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	138 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	139 -- 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	140 -- Clarify!
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	141 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	142 );
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	143
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	144 CREATE TABLE levels_of_service (
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	145 level_of_service smallint PRIMARY KEY,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	146 name varchar(4)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	147 );
1662 d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	148 INSERT INTO levels_of_service (
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	149 level_of_service,
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	150 name
d8ca44615bfc Implemented first version of fairway availability import. Raimund Renkert <raimund.renkert@intevation.de> parents: 1642 diff changeset	151 ) 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	152
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	153 CREATE TABLE riverbed_materials (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	154 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	155 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	156 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	157 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	158
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	159 CREATE TABLE survey_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	160 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	161 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	162
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	163 CREATE TABLE coverage_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	164 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	165 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	166
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	167 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	168 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	169 );
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	170
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	171 CREATE TABLE measure_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	172 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	173 );
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	174
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	175
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	176 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	177 CREATE SCHEMA users
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	178 CREATE TABLE responsibility_areas (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	179 country char(2) PRIMARY KEY REFERENCES countries,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	180 area geography(MULTIPOLYGON, 4326)
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	181 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	182 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	183
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	184 CREATE TABLE templates (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	185 template_name varchar PRIMARY KEY,
2230 4374d942b23d Remove debris and associate templates to country Tom Gottfried <tom@intevation.de> parents: 2172 diff changeset	186 country char(2) NOT NULL REFERENCES countries,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	187 template_data bytea NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	188 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	189 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	190 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	191 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	192 ;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	193 ALTER TABLE internal.user_profiles ADD
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	194 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	195
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	196
831 fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	197 -- Namespace for system wide configuration
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	198 CREATE SCHEMA systemconf
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	199 CREATE TABLE feature_colours (
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	200 feature_name varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	201 style_attr varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	202 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	203 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	204 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	205 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	206 PRIMARY KEY (feature_name, style_attr)
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	207 )
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	208 ;
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	209
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	210 -- 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	211 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	212
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	213 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	214 -- 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	215 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	216 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	217 -- -- 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	218 -- -- 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	219 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	220 -- -- 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	221 -- -- 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	222 -- -- => 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	223 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	224 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	225 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	226 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	227
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	228 CREATE TABLE waterway_area (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	229 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	230 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	231 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	232 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	233 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	234 )
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	235
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	236 CREATE TABLE gauges (
1825 484ae4fe09d7 Backed out changeset 332e42a2088d (no staging needed). Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1824 diff changeset	237 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	238 (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	239 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	240 objname varchar NOT NULL,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	241 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	242 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	243 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	244 validity tstzrange,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	245 -- 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	246 -- (a gauge is not a berth!)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	247 -- 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	248 zero_point double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	249 geodref varchar(4) REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	250 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	251 source_organization varchar
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	252 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	253 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	254 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	255
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	256 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	257 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	258 reference_water_level varchar(20)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	259 NOT NULL REFERENCES reference_water_levels,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	260 PRIMARY KEY (gauge_id, reference_water_level),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	261 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	262 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	263
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	264 CREATE TABLE gauge_measurements (
1636 37ee25bc2bbe Updated database schema for gauge meaurement imports. Raimund Renkert <raimund.renkert@intevation.de> parents: 1583 diff changeset	265 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	266 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	267 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	268 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	269 -- 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	270 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	271 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	272 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	273 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	274 water_level double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	275 predicted boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	276 is_waterlevel boolean NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	277 -- 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	278 -- 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	279 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	280 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	281 --- 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	282 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	283 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	284 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	285 -- 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	286 -- 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	287 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	288 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	289
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	290 CREATE TABLE waterway_axis (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	291 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	292 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	293 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	294 -- 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	295 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	296 nobjnam varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	297 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	298
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	299 -- 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	300 -- 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	301 -- 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	302 CREATE TABLE distance_marks_virtual (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	303 location_code isrs PRIMARY KEY,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	304 geom geography(POINT, 4326) NOT NULL,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	305 related_enc varchar(12) NOT NULL
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	306 )
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	307
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	308 CREATE TABLE distance_marks (
1861 5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	309 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	310 country char(2) REFERENCES countries,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	311 hectom int,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	312 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	313 -- 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	314 catdis smallint REFERENCES catdis,
5083a1d19a4b Adjusted database schema for distance marks importer. Raimund Renkert <raimund.renkert@intevation.de> parents: 1831 diff changeset	315 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	316 related_enc varchar(12)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	317 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	318
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	319 -- 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	320 -- 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	321 -- 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	322 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	323 SELECT location_code,
3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	324 isrs_asText(location_code) AS location,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	325 geom::Geometry(POINT, 4326),
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	326 related_enc,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	327 (location_code).hectometre
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	328 FROM distance_marks_virtual
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	329
1446 1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	330 -- 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	331 -- 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	332 -- 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	333 -- See
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	334 -- 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	335 -- for details.
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	336 CREATE TABLE gt_pk_metadata (
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	337 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	338 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	339 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	340 pk_column_idx INTEGER,
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	341 pk_policy VARCHAR(32),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	342 pk_sequence VARCHAR(64),
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	343 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	344 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	345 )
1e19184472bf Add configuration of primary key metadata used by geoserver. Sascha Wilde <wilde@intevation.de> parents: 1392 diff changeset	346
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	347 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	348 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	349 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	350 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	351 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	352 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	353 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	354 nobjnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	355 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	356 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	357 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	358 UNIQUE(name, staging_done)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	359 )
1894 d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	360
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	361 CREATE TABLE stretch_countries (
1906 32c56e6c089a Stretch import: Added forgotten source file. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1894 diff changeset	362 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	363 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	364 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	365 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	366 )
d72a1539ef3c Schema: Replaced sections_stretches table with a pure stretches table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1861 diff changeset	367
1929 f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	368 -- Published view for GeoServer
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	369 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	370 id,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	371 name,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	372 (stretch).lower::varchar as lower,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	373 (stretch).upper::varchar as upper,
1983 f9f1babe52ae Fix area generation from multipolygon input Tom Gottfried <tom@intevation.de> parents: 1975 diff changeset	374 area::Geometry(MULTIPOLYGON, 4326),
1929 f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	375 objnam,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	376 nobjnam,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	377 date_info,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	378 source_organization,
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	379 (SELECT string_agg(country_code, ', ')
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	380 FROM stretch_countries
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	381 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	382 staging_done
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	383 FROM stretches
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	384
f538d9a23329 Better suited GeoServer view for waterway.stretches. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1906 diff changeset	385
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	386 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	387 BEFORE UPDATE ON stretches
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	388 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	389
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	390 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	391 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	392 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	393 geom geography(linestring, 4326),
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	394 validity tstzrange,
2072 b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	395 lnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	396 mwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	397 hnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	398 fe30 double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 2064 diff changeset	399 fe100 double precision,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	400 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	401 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	402 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	403 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	404 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	405 staging_done boolean NOT NULL DEFAULT false,
2130 f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	406 EXCLUDE USING GIST (
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	407 isrs_asText(location) WITH =,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	408 validity WITH &&,
f3aabc05f9b2 Fix constraints on waterway profiles Tom Gottfried <tom@intevation.de> parents: 2128 diff changeset	409 CAST(staging_done AS int) WITH =)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	410 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	411 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	412 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	413 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	414
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	415 CREATE TABLE fairway_dimensions (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	416 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	417 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	418 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	419 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	420 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	421 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	422 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	423 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	424 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	425 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	426 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	427 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	428 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	429 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	430
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	431 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	432 -- Bottlenecks
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 -- 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	435 -- (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	436 -- 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	437 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	438 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	439 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	440 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	441 -- 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	442 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	443 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	444 stretch isrsrange NOT NULL,
1984 48001472e1d8 Ensure geometries in database are valid OGC simple features Tom Gottfried <tom@intevation.de> parents: 1983 diff changeset	445 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	446 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	447 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	448 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	449 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	450 revisiting_time smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	451 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	452 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	453 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	454 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	455 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	456 -- 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	457 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	458 -- 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	459 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	460 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	461 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	462 -- 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	463 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	464 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	465 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	466 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	467
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	468 CREATE TABLE bottlenecks_riverbed_materials (
2076 0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	469 bottleneck_id int NOT NULL REFERENCES bottlenecks(id),
0e006077bbfa Add missing NOT NULL constraints Tom Gottfried <tom@intevation.de> parents: 2072 diff changeset	470 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	471 -- 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	472 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	473 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	474
1963 f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	475 -- Published view for GeoServer
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	476 CREATE VIEW bottlenecks_geoserver AS SELECT
2085 bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	477 b.id,
1963 f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	478 b.bottleneck_id,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	479 b.objnam,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	480 b.nobjnm,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	481 b.stretch,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	482 b.area,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	483 b.rb,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	484 b.lb,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	485 b.responsible_country,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	486 b.revisiting_time,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	487 b.limiting,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	488 b.date_info,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	489 b.source_organization,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	490 g.location AS gauge_isrs_code,
f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	491 g.objname AS gauge_objname,
2085 bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	492 rwl_ldc.value AS ldc,
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	493 rwl_mw.value AS mw,
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	494 rwl_hdc.value AS hdc
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	495 FROM bottlenecks b, gauges g,
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	496 (SELECT gauge_id, value FROM gauges_reference_water_levels
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	497 WHERE reference_water_level='LDC') rwl_ldc,
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	498 (SELECT gauge_id, value FROM gauges_reference_water_levels
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	499 WHERE reference_water_level='MW') rwl_mw,
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	500 (SELECT gauge_id, value FROM gauges_reference_water_levels
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	501 WHERE reference_water_level='HDC') rwl_hdc
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	502 WHERE b.fk_g_fid = g.location AND g.location = rwl_ldc.gauge_id
bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	503 AND g.location = rwl_mw.gauge_id AND g.location = rwl_hdc.gauge_id
1963 f7a35ba9f409 Added view for publishing bottlenecks Sascha Wilde <wilde@intevation.de> parents: 1929 diff changeset	504
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	505 CREATE TABLE sounding_results (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	506 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	507 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	508 date_info date NOT NULL,
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	509 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	510 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	511 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	512 surtyp varchar REFERENCES survey_types,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	513 coverage varchar REFERENCES coverage_types,
2015 b4ba751e70a1 Fix column type to match referenced column Tom Gottfried <tom@intevation.de> parents: 1995 diff changeset	514 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	515 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	516 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	517 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	518 octree_index bytea,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	519 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	520 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	521
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	522 CREATE TABLE sounding_results_contour_lines (
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	523 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	524 ON DELETE CASCADE,
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	525 height numeric NOT NULL,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	526 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	527 -- 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	528 -- 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	529 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	530 )
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	531 -- 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	532 -- 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	533 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	534 SELECT bottleneck_id,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	535 date_info,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	536 height,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	537 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	538 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	539 JOIN sounding_results sr
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	540 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	541
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	542 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	543 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	544 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	545 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	546 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	547 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	548 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	549 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	550 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	551 -- 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	552 critical boolean,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	553 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	554 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	555 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	556 CREATE TRIGGER fairway_availability_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	557 BEFORE UPDATE ON fairway_availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	558 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	559
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	560 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	561 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	562 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	563 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	564 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	565 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	566 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	567 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	568 IS NOT NULL),
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	569 shallowest_spot geography(POINT, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	570 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	571
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	572 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	573 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	574 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	575 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	576 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	577 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	578 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	579 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	580
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	581 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	582 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	583 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	584 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	585 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	586 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	587 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	588 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	589 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	590 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	591 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	592 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	593 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	594 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	595 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	596 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	597 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	598 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	599 )
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	600
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	601 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	602 SELECT
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	603 objnam AS name,
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	604 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	605 (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	606 (upper(stretch)).hectometre AS to,
1520 6ad1f431bc85 Fixed date of latest measurement in bottlenecks list. Sascha Wilde <wilde@intevation.de> parents: 1446 diff changeset	607 sr.current::text
1831 74a3d8d8939e Remove superfluous schema qualifiers in definition of the schema itself Tom Gottfried <tom@intevation.de> parents: 1826 diff changeset	608 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	609 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	610 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	611 ORDER BY objnam
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	612 ;
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	613
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	614 -- 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	615 INSERT INTO waterway.gt_pk_metadata VALUES
2170 3bfe48e32f20 Fixed de-duplication (primary geoserver id) for virt dist marks. Sascha Wilde <wilde@intevation.de> parents: 2159 diff changeset	616 ('waterway', 'distance_marks_geoserver', 'location_code'),
2085 bca8bda0b805 Changed bottlenecks_geoserver view and added config to prevent duplications. Sascha Wilde <wilde@intevation.de> parents: 2084 diff changeset	617 ('waterway', 'bottlenecks_geoserver', 'id');
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	618
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	619 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	620 -- Import queue and respective logging
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	621 --
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	622 CREATE TYPE import_state AS ENUM (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	623 'queued',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	624 'running',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	625 'failed', 'unchanged', 'pending',
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	626 'accepted', 'declined'
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	627 );
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	628
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	629 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	630
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	631 -- 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	632 CREATE SCHEMA import
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	633
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	634 CREATE TABLE import_configuration (
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	635 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	636 username varchar NOT NULL
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	637 REFERENCES internal.user_profiles(username)
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	638 ON DELETE CASCADE
d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	639 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	640 kind varchar NOT NULL
1547 d4b7a6d054cd Add table to store import configuration. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1520 diff changeset	641 )
1702 49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	642
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	643 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	644 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	645 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	646 ON DELETE CASCADE
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	647 ON UPDATE CASCADE,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1684 diff changeset	648 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	649 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	650 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	651 )
1995 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 CREATE TABLE imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	654 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	655 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	656 kind varchar NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	657 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	658 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	659 retry_wait interval
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	660 CHECK(retry_wait IS NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	661 OR retry_wait >= interval '0 microseconds'),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	662 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	663 username varchar NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	664 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	665 ON DELETE CASCADE
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	666 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	667 signer varchar
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	668 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	669 ON DELETE SET NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	670 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	671 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	672 data TEXT,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	673 summary TEXT
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	674 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	675
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	676 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	677
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	678 CREATE TABLE import_logs (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	679 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	680 ON DELETE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	681 time timestamp NOT NULL DEFAULT now(),
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	682 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	683 msg TEXT NOT NULL
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	684 )
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	685
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	686 CREATE TABLE track_imports (
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	687 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	688 ON DELETE CASCADE,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	689 relation regclass NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	690 key int NOT NULL,
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	691 UNIQUE (relation, key)
59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	692 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	693 ;
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	694
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	695 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	696 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	697 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	698 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	699 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	700 FOR tmp IN
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	701 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	702 LOOP
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	703 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	704 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	705 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	706 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	707 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	708
1995 59055c8301df Move import queue to its own database namespace Tom Gottfried <tom@intevation.de> parents: 1986 diff changeset	709 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	710 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	711 BEGIN
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	712 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	713 RETURN NULL;
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	714 END;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	715 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	716 LANGUAGE plpgsql;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	717
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	718 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 2230:4374d942b23d