gemma: schema/gemma.sql annotate

annotate schema/gemma.sql @ 1298:6590208e3ee1

add headers for licensing to some schema files * Add first version of header lines to some schema file, some email addresses taken from raw commit info.

author	Fadi Abbud <fadi.abbud@intevation.de>
date	Fri, 23 Nov 2018 09:35:10 +0100
parents	9f7dc950ffd2
children	2304778c4432

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
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	7 -- Copyright (C) 2018 by via donau
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):
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	12 -- * Tom <tom@intevation.de>
6590208e3ee1 add headers for licensing to some schema files Fadi Abbud <fadi.abbud@intevation.de> parents: 1288 diff changeset	13
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	14 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	15
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	16 --
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	17 -- Infrastructure
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	18 --
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	19 CREATE EXTENSION postgis;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	20
96 d036e1bd5f00 Add roles, privileges and RLS policies. Tom Gottfried <tom@intevation.de> parents: 95 diff changeset	21 -- 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	22 -- historicisation?
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	23 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	24 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	25 AS $$
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	26 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	27 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	28 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	29 END;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	30 $$;
62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	31
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	32 --
115 d349db18bece s/wamos/gemma/g on database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 113 diff changeset	33 -- GEMMA data
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	34 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	35
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	36 -- 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	37 CREATE SCHEMA internal
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	38 -- 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	39 CREATE TABLE user_profiles (
361 f5087cebc740 Enforce PostgreSQL identifier length on username Tom Gottfried <tom@intevation.de> parents: 345 diff changeset	40 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	41 -- 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	42 map_extent box2d NOT NULL,
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	43 email_address varchar NOT NULL
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	44 )
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	45 -- Columns referencing user-visible schemas added below.
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	46 ;
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	47
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	48
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	49 -- 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	50 CREATE SCHEMA sys_admin
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	51 CREATE TABLE system_config (
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	52 config_key varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	53 config_val varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	54 )
437 b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	55
478 3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	56 CREATE TABLE password_reset_requests (
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	57 hash varchar(32) PRIMARY KEY,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	58 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	59 username varchar NOT NULL
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	60 REFERENCES internal.user_profiles(username)
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	61 ON DELETE CASCADE ON UPDATE CASCADE
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	62 )
3af7ca761f6a Purge password reset role Tom Gottfried <tom@intevation.de> parents: 457 diff changeset	63
624 8772979f8750 Remove test data from schema Tom Gottfried <tom@intevation.de> parents: 611 diff changeset	64 -- Tables with geo data to be published with GeoServer.
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	65 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	66 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	67 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	68 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	69 )
b8366b24dc55 Added two tables to configure the WFS/WMS services of gemma. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 383 diff changeset	70
457 62ffb6c8a42e Remove unnecessary schema qualifiers Tom Gottfried <tom@intevation.de> parents: 449 diff changeset	71 CREATE TABLE published_services (
598 4854a1e85870 Ensure published service is based on existing table Tom Gottfried <tom@intevation.de> parents: 580 diff changeset	72 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	73 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	74 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	75 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	76 )
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	77 ;
81 acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	78
acaa485c0c1e Use different schemas for different diagrams. Add TODOs. Tom Gottfried <tom@intevation.de> parents: 80 diff changeset	79 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	80 -- 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	81 --
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	82 CREATE TABLE countries (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	83 country_code char(2) PRIMARY KEY -- ISO 3166 country code
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	84 -- 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	85 -- This might be an issue in case cache space becomes a limiting
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	86 -- factor when there are many FKs pointing here.
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	87 );
82 c71e43f88ae0 Add auxilliary tables. Tom Gottfried <tom@intevation.de> parents: 81 diff changeset	88
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	89 CREATE TABLE language_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	90 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	91 );
84 d905022a48e9 More user attributes from APUC3. Tom Gottfried <tom@intevation.de> parents: 83 diff changeset	92
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	93 CREATE TABLE catccls (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	94 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	95 -- 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	96 -- (see page 328 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	97 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	98 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	99
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	100 CREATE TABLE dirimps (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	101 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	102 -- 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	103 -- (see page 381 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	104 );
927 48f70782400d Add categories of CEMT class and directions of impact Tom Gottfried <tom@intevation.de> parents: 919 diff changeset	105 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	106 -- 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	107
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	108 CREATE TABLE depth_references (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	109 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	110 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	111 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	112 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	113 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	114 -- 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	115 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	116 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	117 );
93 765906789840 Add gauge attributes. Tom Gottfried <tom@intevation.de> parents: 92 diff changeset	118
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	119 CREATE TABLE reference_water_levels (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	120 reference_water_level varchar(20) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	121 );
70 28108455ad35 Add reference water levels to gauges. Tom Gottfried <tom@intevation.de> parents: 69 diff changeset	122
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	123 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	124 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	125 -- 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	126 -- (see page 171 of edition 2.3)
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	127 );
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	128 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	129
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	130 CREATE TABLE position_codes (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	131 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	132 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	133 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	134 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	135 -- 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	136 -- Clarify!
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	137 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	138 );
87 c46fb3f1faeb Add distance mark attributes and try to consolidate. Tom Gottfried <tom@intevation.de> parents: 86 diff changeset	139
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	140 CREATE TABLE levels_of_service (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	141 level_of_service smallint PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	142 );
368 78588014ad0a Populate levels_of_service Tom Gottfried <tom@intevation.de> parents: 361 diff changeset	143 INSERT INTO levels_of_service VALUES (1), (2), (3);
89 a36bfec5edd3 Move more tables to waterway schema to tweak diagrams. Tom Gottfried <tom@intevation.de> parents: 88 diff changeset	144
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	145 CREATE TABLE riverbed_materials (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	146 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	147 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	148 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	149 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	150
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	151 CREATE TABLE survey_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	152 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	153 );
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	154
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	155 CREATE TABLE coverage_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	156 coverage_type varchar PRIMARY KEY
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
577 e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	159 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	160 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	161 );
e6c9d2da2e20 Backed out changeset f3452ce5c056 Tom Gottfried <tom@intevation.de> parents: 569 diff changeset	162
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	163 CREATE TABLE measure_types (
182 4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	164 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL Tom Gottfried <tom@intevation.de> parents: 177 diff changeset	165 );
58 30cb2f87c268 Add effective fairway availability. Tom Gottfried <tom@intevation.de> parents: 56 diff changeset	166
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	167
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	168 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	169 CREATE SCHEMA users
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	170 CREATE TABLE responsibility_areas (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	171 country char(2) PRIMARY KEY REFERENCES countries,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	172 area geography(MULTIPOLYGON, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	173 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	174
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	175 CREATE TABLE templates (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	176 template_name varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	177 template_data bytea NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	178 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	179 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	180 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	181 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	182
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	183 CREATE TABLE user_templates (
225 8b9cae6d3a21 Add database function to update role and user profile Tom Gottfried <tom@intevation.de> parents: 212 diff changeset	184 username varchar NOT NULL
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	185 REFERENCES internal.user_profiles
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	186 ON DELETE CASCADE ON UPDATE CASCADE,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	187 template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	188 PRIMARY KEY (username, template_name)
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 ;
268 72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	191 ALTER TABLE internal.user_profiles ADD
72062ca52746 Make user_profiles table invisible for users Tom Gottfried <tom@intevation.de> parents: 234 diff changeset	192 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	193
319 ac760b0f22a9 Add special role for password reset Tom Gottfried <tom@intevation.de> parents: 268 diff changeset	194
831 fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	195 -- Namespace for system wide configuration
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	196 CREATE SCHEMA systemconf
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	197 CREATE TABLE feature_colours (
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	198 feature_name varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	199 style_attr varchar,
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	200 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	201 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	202 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	203 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	204 PRIMARY KEY (feature_name, style_attr)
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	205 )
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	206 ;
fa417ff1f355 Extended schema to save feature style information. Sascha Wilde <wilde@intevation.de> parents: 821 diff changeset	207
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	208 -- 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	209 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	210
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	211 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	212 -- 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	213 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	214 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	215 -- -- 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	216 -- -- 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	217 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	218 -- -- 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	219 -- -- 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	220 -- -- => 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	221 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	222 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	223 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	224 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	225
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	226 CREATE TABLE waterway_area (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	227 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	228 area geography(POLYGON, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	229 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	230 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	231 )
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	232 CREATE UNIQUE INDEX ON waterway_area ((ST_GeoHash(area, 23)))
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	233
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	234 CREATE TABLE gauges (
579 642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	235 location isrs PRIMARY KEY CHECK(
642df1164aca Ensure gauges are identified by appropriate ISRS location code Tom Gottfried <tom@intevation.de> parents: 577 diff changeset	236 (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	237 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	238 objname varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	239 is_left boolean, -- XXX: Or reference position_codes?
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	240 geom geography(POINT, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	241 applicability isrsrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	242 validity tstzrange,-- XXX: Should ranges be NOT NULL? In DRC, only copy
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	243 -- 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	244 -- (a gauge is not a berth!)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	245 -- 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	246 zero_point double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	247 geodref varchar(4) REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	248 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	249 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	250 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	251 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	252 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	253
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	254 CREATE TABLE gauges_reference_water_levels (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	255 gauge_id isrs NOT NULL REFERENCES gauges,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	256 reference_water_level varchar(20)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	257 NOT NULL REFERENCES reference_water_levels,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	258 PRIMARY KEY (gauge_id, reference_water_level),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	259 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	260 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	261
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	262 CREATE TABLE gauge_measurements (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	263 fk_gauge_id isrs NOT NULL REFERENCES gauges,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	264 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	265 PRIMARY KEY (fk_gauge_id, measure_date),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	266 -- XXX: Is country_code really relevant for GEMMA or just NtS?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	267 -- country_code char(2) NOT NULL REFERENCES countries,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	268 -- 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	269 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	270 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	271 date_issue timestamp with time zone NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	272 -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	273 -- XXX: Always ZPG?
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,
345 b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	283 source_organization varchar NOT NULL, -- "originator"
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	284 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	285 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	286 CREATE TRIGGER gauge_measurements_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	287 BEFORE UPDATE ON gauge_measurements
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	288 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	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,
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	292 wtwaxs geography(LINESTRING, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	293 -- 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	294 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	295 nobjnam varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	296 )
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	297 CREATE UNIQUE INDEX ON waterway_axis ((ST_GeoHash(wtwaxs, 23)))
195 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 (
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	309 country char(2) NOT NULL REFERENCES countries,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	310 hectom int NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	311 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	312 -- include location in primary key, because we have no fairway code:
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	313 PRIMARY KEY (country, hectom, geom),
934 e6220a19f284 Add categories of distance marks Tom Gottfried <tom@intevation.de> parents: 930 diff changeset	314 catdis smallint NOT NULL REFERENCES catdis,
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	315 position_code char(2) NOT NULL REFERENCES position_codes,
5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	316 related_enc varchar(12) NOT NULL
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
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	322 CREATE VIEW waterway.distance_marks_geoserver AS
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	323 SELECT location_code::VARCHAR,
f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	324 geom::Geometry(POINT, 4326),
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	325 related_enc,
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	326 (location_code).hectometre
948 5f89868bd75e Store virtual and physical distance marks separately Tom Gottfried <tom@intevation.de> parents: 944 diff changeset	327 FROM waterway.distance_marks_virtual
690 f595b3455d75 Schema: Add view for waterway axis Bernhard Reiter <bernhard@intevation.de> parents: 659 diff changeset	328
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	329 CREATE TABLE sections_stretches (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	330 id varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	331 is_section boolean NOT NULL, -- maps 'function' from interface
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	332 stretch isrsrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	333 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	334 nobjnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	335 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	336 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	337 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	338 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	339 CREATE TRIGGER sections_stretches_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	340 BEFORE UPDATE ON sections_stretches
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	341 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	342
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	343 CREATE TABLE waterway_profiles (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	344 location isrs NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	345 validity tstzrange,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	346 EXCLUDE USING GIST (validity WITH &&),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	347 PRIMARY KEY (location, validity),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	348 lnwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	349 mwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	350 hnwl smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	351 fe30 smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	352 fe100 smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	353 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	354 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	355 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	356 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	357 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	358 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	359 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	360 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	361 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	362 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	363
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	364 CREATE TABLE fairway_dimensions (
383 84597b9da68e Let all tables have a PRIMARY KEY again Tom Gottfried <tom@intevation.de> parents: 371 diff changeset	365 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	366 area geography(POLYGON, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	367 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	368 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	369 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	370 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	371 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	372 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables Tom Gottfried <tom@intevation.de> parents: 323 diff changeset	373 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	374 )
371 d5b707bbd1d5 Do not create B-Tree indexes on potentially large spatial data Tom Gottfried <tom@intevation.de> parents: 370 diff changeset	375 CREATE UNIQUE INDEX ON fairway_dimensions ((ST_GeoHash(area, 23)))
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	376 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	377 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	378 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	379
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	380 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	381 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	382 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	383 -- 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	384 -- (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	385 -- 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	386 CREATE TABLE bottlenecks (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	387 bottleneck_id varchar PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	388 fk_g_fid isrs NOT NULL REFERENCES gauges,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	389 -- 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	390 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	391 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	392 stretch isrsrange NOT NULL,
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	393 area geography(POLYGON, 4326) NOT NULL,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	394 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	395 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	396 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	397 revisiting_time smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	398 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	399 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	400 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	401 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	402 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	403 -- 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	404 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	405 -- 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	406 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	407 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	408 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	409 -- 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	410 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	411 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	412 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	413 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	414
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	415 CREATE TABLE bottlenecks_riverbed_materials (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	416 bottleneck_id varchar REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	417 riverbed varchar REFERENCES riverbed_materials,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	418 -- 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	419 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	420 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	421
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	422 CREATE TABLE sounding_results (
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	423 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	424 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	425 date_info date NOT NULL,
656 9ef2f80a4645 Fix syntax error in schema script Tom Gottfried <tom@intevation.de> parents: 655 diff changeset	426 UNIQUE (bottleneck_id, date_info),
917 5b90217aa1bb Fix nullability in sounding results Tom Gottfried <tom@intevation.de> parents: 916 diff changeset	427 area geography(POLYGON, 4326) NOT NULL,
611 effd22c0ae5a Sounding result: Write simple SQL insert dumper. Not deterministic, yet. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 609 diff changeset	428 surtyp varchar REFERENCES survey_types,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	429 coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	430 depth_reference char(3) NOT NULL REFERENCES depth_references,
917 5b90217aa1bb Fix nullability in sounding results Tom Gottfried <tom@intevation.de> parents: 916 diff changeset	431 point_cloud geography(MULTIPOINTZ, 4326) NOT NULL,
1131 2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	432 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results Tom Gottfried <tom@intevation.de> parents: 1085 diff changeset	433 octree_index bytea,
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	434 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	435 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	436
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	437 CREATE TABLE sounding_results_contour_lines (
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	438 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	439 ON DELETE CASCADE,
731 4d6329afc1ea Added table for contour lines of sounding results. Sascha L. Teichmann <teichmann@intevation.de> parents: 720 diff changeset	440 height numeric NOT NULL,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	441 lines geography(multilinestring, 4326) NOT NULL,
1133 dd4071019676 Delete contour lines with their sounding result Tom Gottfried <tom@intevation.de> parents: 1131 diff changeset	442 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	443 )
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	444 -- 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	445 -- At least geoserver-2.13.2 does not serve type geography correctly
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	446 CREATE VIEW waterway.sounding_results_contour_lines_geoserver AS
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	447 SELECT bottleneck_id,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	448 date_info,
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	449 height,
919 271561dce2e6 Store contour lines in 2D Tom Gottfried <tom@intevation.de> parents: 917 diff changeset	450 CAST(lines AS geometry(multilinestring, 4326)) AS lines
821 d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	451 FROM waterway.sounding_results_contour_lines cl
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	452 JOIN waterway.sounding_results sr
d32516051d08 Add view to help geoserver serve contour lines Tom Gottfried <tom@intevation.de> parents: 736 diff changeset	453 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	454
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	455 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	456 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	457 --
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	458 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	459 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	460 position_code char(2) REFERENCES position_codes,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	461 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	462 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	463 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	464 -- 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	465 critical boolean,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	466 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	467 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	468 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	469 CREATE TRIGGER fairway_availability_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	470 BEFORE UPDATE ON fairway_availability
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	471 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	472
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	473 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	474 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	475 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	476 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	477 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	478 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	479 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	480 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	481 IS NOT NULL),
370 fe87457a05d7 Store spatial data as geography Tom Gottfried <tom@intevation.de> parents: 368 diff changeset	482 shallowest_spot geography(POINT, 4326)
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	483 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	484
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	485 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	486 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	487 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	488 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	489 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	490 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	491 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	492 )
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	493
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	494 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	495 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	496 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	497 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	498 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	499 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	500 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	501 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	502 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	503 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	504 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	505 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	506 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	507 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	508 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	509 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	510 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	511 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	512 )
1085 72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	513
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	514 CREATE VIEW waterway.bottleneck_overview AS
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	515 SELECT
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	516 objnam AS name,
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	517 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	518 (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	519 (upper(stretch)).hectometre AS to,
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	520 sr.current
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	521 FROM waterway.bottlenecks bn LEFT JOIN (
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	522 SELECT bottleneck_id, max(date_info) AS current FROM
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	523 waterway.sounding_results
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	524 GROUP BY bottleneck_id) sr ON sr.bottleneck_id = bn.bottleneck_id
72cad6277e3a Added waterway.bottleneck_overview view to database schema. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1012 diff changeset	525 ORDER BY objnam
195 5dc8e734487a Introduce database schemas as privilege-based namespaces Tom Gottfried <tom@intevation.de> parents: 184 diff changeset	526 ;
56 f378959820be Started to draft schema for fairway availability. Tom Gottfried <tom@intevation.de> parents: 55 diff changeset	527
1012 875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	528 --
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	529 -- Import queue and respective logging
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	530 --
1190 e3de65179889 The imort queue has now six states: Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1172 diff changeset	531 CREATE TYPE waterway.import_state AS ENUM (
e3de65179889 The imort queue has now six states: Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1172 diff changeset	532 'queued', 'running', 'failed',
e3de65179889 The imort queue has now six states: Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1172 diff changeset	533 'pending', 'accepted', 'declined'
e3de65179889 The imort queue has now six states: Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1172 diff changeset	534 );
1012 875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	535
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	536 CREATE TABLE waterway.imports (
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	537 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	538 state waterway.import_state NOT NULL DEFAULT 'queued',
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	539 enqueued timestamp NOT NULL DEFAULT now(),
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	540 kind varchar NOT NULL,
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	541 username varchar NOT NULL
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	542 REFERENCES internal.user_profiles(username)
1194 7db850de0952 Added a signer who makes the final decison on an import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1193 diff changeset	543 ON DELETE CASCADE
7db850de0952 Added a signer who makes the final decison on an import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1193 diff changeset	544 ON UPDATE CASCADE,
7db850de0952 Added a signer who makes the final decison on an import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1193 diff changeset	545 signer varchar
7db850de0952 Added a signer who makes the final decison on an import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1193 diff changeset	546 REFERENCES internal.user_profiles(username)
7db850de0952 Added a signer who makes the final decison on an import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1193 diff changeset	547 ON DELETE SET NULL
7db850de0952 Added a signer who makes the final decison on an import. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1193 diff changeset	548 ON UPDATE CASCADE,
1012 875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	549 data TEXT
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	550 );
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	551
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	552 CREATE INDEX enqueued_idx ON waterway.imports(enqueued, state);
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	553
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	554 CREATE TYPE waterway.log_type AS ENUM ('info', 'warn', 'error');
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	555
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	556 CREATE TABLE waterway.import_logs (
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	557 import_id int NOT NULL REFERENCES waterway.imports(id) ON DELETE CASCADE,
1012 875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	558 time timestamp NOT NULL DEFAULT now(),
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	559 kind waterway.log_type NOT NULL DEFAULT 'info',
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	560 msg TEXT NOT NULL
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	561 );
875b0a9a3c92 Fixed schema definition of import queue. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 998 diff changeset	562
1168 930fdd8b474f Track successfull imports in a separate table to be able to remove them later. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1133 diff changeset	563 CREATE TABLE waterway.track_imports (
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	564 import_id int NOT NULL REFERENCES waterway.imports(id) ON DELETE CASCADE,
1168 930fdd8b474f Track successfull imports in a separate table to be able to remove them later. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1133 diff changeset	565 relation regclass NOT NULL,
930fdd8b474f Track successfull imports in a separate table to be able to remove them later. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1133 diff changeset	566 key int NOT NULL,
930fdd8b474f Track successfull imports in a separate table to be able to remove them later. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1133 diff changeset	567 UNIQUE (relation, key)
930fdd8b474f Track successfull imports in a separate table to be able to remove them later. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1133 diff changeset	568 );
930fdd8b474f Track successfull imports in a separate table to be able to remove them later. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1133 diff changeset	569
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	570 CREATE FUNCTION waterway.del_import(imp_id int) RETURNS void AS
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	571 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	572 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	573 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	574 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	575 FOR tmp IN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	576 SELECT * FROM waterway.track_imports WHERE import_id = imp_id
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	577 LOOP
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	578 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	579 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	580 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	581 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	582 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	583
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	584 CREATE FUNCTION waterway.del_import() RETURNS trigger AS
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	585 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	586 BEGIN
1193 58acc343b1b6 Implemented the db stuff of the review process. Needs testing. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1190 diff changeset	587 EXECUTE format('DELETE FROM %s WHERE id = $1', OLD.relation) USING OLD.key;
1172 c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	588 END;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	589 $$
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	590 LANGUAGE plpgsql;
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	591
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	592 CREATE TRIGGER delete_import AFTER DELETE ON waterway.track_imports
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	593 FOR EACH ROW EXECUTE PROCEDURE waterway.del_import();
c3955e3db074 Propagate deletion of imports through the track_imports table. Sascha L. Teichmann <sascha.teichmann@intevation.de> parents: 1168 diff changeset	594
35 62e14b4d25fc First working draft of schema for bottlenecks. Tom Gottfried <tom@intevation.de> parents: diff changeset	595 COMMIT;

Mercurial > gemma

annotate schema/gemma.sql @ 1298:6590208e3ee1