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