Mercurial > gemma

annotate schema/gemma.sql @ 2342:4e325dda6a2d

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