Mercurial > gemma

annotate schema/gemma.sql @ 2128:bc310a0b5bae

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