Mercurial > gemma
annotate schema/gemma.sql @ 931:e758e12b38c9 geo-style
Merged default into geo-style branch.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Mon, 08 Oct 2018 14:48:58 +0200 |
parents | 688e1530f66a 71445b091415 |
children | feef06db5d9d |
rev | line source |
---|---|
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 BEGIN; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
3 -- |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
4 -- Infrastructure |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
5 -- |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 CREATE EXTENSION postgis; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
8 -- 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
|
9 -- historicisation? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 CREATE FUNCTION update_date_info() RETURNS trigger |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 LANGUAGE plpgsql |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 AS $$ |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 BEGIN |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 NEW.date_info = CURRENT_TIMESTAMP; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 RETURN NEW; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 END; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 $$; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
19 -- |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
20 -- GEMMA data |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
21 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
22 |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
23 -- 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
|
24 CREATE SCHEMA internal |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
25 -- 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
|
26 CREATE TABLE user_profiles ( |
361
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
345
diff
changeset
|
27 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
|
28 -- 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
|
29 map_extent box2d NOT NULL, |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
30 email_address varchar NOT NULL |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
31 ) |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
32 -- Columns referencing user-visible schemas added below. |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
33 ; |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
34 |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
35 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
36 -- 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
|
37 CREATE SCHEMA sys_admin |
457
62ffb6c8a42e
Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents:
449
diff
changeset
|
38 CREATE TABLE system_config ( |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
39 config_key varchar PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
40 config_val varchar |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
41 ) |
437
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
42 |
478
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
43 CREATE TABLE password_reset_requests ( |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
44 hash varchar(32) PRIMARY KEY, |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
45 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
46 username varchar NOT NULL |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
47 REFERENCES internal.user_profiles(username) |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
48 ON DELETE CASCADE ON UPDATE CASCADE |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
49 ) |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
50 |
624
8772979f8750
Remove test data from schema
Tom Gottfried <tom@intevation.de>
parents:
611
diff
changeset
|
51 -- Tables with geo data to be published with GeoServer. |
457
62ffb6c8a42e
Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents:
449
diff
changeset
|
52 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
|
53 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
|
54 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
|
55 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
|
56 ) |
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
57 |
457
62ffb6c8a42e
Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents:
449
diff
changeset
|
58 CREATE TABLE published_services ( |
598
4854a1e85870
Ensure published service is based on existing table
Tom Gottfried <tom@intevation.de>
parents:
580
diff
changeset
|
59 name regclass PRIMARY KEY, |
437
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
60 style bytea, |
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
61 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
|
62 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
|
63 ) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
64 ; |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
65 |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
66 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
67 -- 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
|
68 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
69 CREATE TABLE countries ( |
182 | 70 country_code char(2) PRIMARY KEY -- ISO 3166 country code |
71 -- A smallint PK would require even less disk space i.e. on the FK side. | |
72 -- This might be an issue in case cache space becomes a limiting | |
73 -- factor when there are many FKs pointing here. | |
74 ); | |
82 | 75 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
76 CREATE TABLE language_codes ( |
182 | 77 language_code varchar PRIMARY KEY |
78 ); | |
84
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
79 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
80 CREATE TABLE catccls ( |
182 | 81 catccl smallint PRIMARY KEY |
82 -- 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
|
83 -- (see page 328 of edition 2.3) |
182 | 84 ); |
927
48f70782400d
Add categories of CEMT class and directions of impact
Tom Gottfried <tom@intevation.de>
parents:
919
diff
changeset
|
85 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
|
86 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
87 CREATE TABLE dirimps ( |
182 | 88 dirimp smallint PRIMARY KEY |
89 -- 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
|
90 -- (see page 381 of edition 2.3) |
182 | 91 ); |
927
48f70782400d
Add categories of CEMT class and directions of impact
Tom Gottfried <tom@intevation.de>
parents:
919
diff
changeset
|
92 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
|
93 -- 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
|
94 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
95 CREATE TABLE depth_references ( |
182 | 96 depth_reference varchar(4) PRIMARY KEY |
97 -- See col. AB and AI RIS-Index Encoding Guide | |
98 -- XXX: We need a way to distinguish between geodetic (eg. col. AP | |
99 -- RIS-Index) and other references (e.g. col. AB and AI): | |
100 -- _ multi-column FK with a boolean column (geodetic/non-geodetic; | |
101 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. | |
102 -- _ Do not mixup things with different meanings in one table at all | |
103 -- (which would mean a model differing a bit from RIS-Index ideas) | |
104 ); | |
93 | 105 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
106 CREATE TABLE reference_water_levels ( |
182 | 107 reference_water_level varchar(20) PRIMARY KEY |
108 ); | |
70
28108455ad35
Add reference water levels to gauges.
Tom Gottfried <tom@intevation.de>
parents:
69
diff
changeset
|
109 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
110 CREATE TABLE distance_mark_functions ( |
182 | 111 -- XXX: Redundant information to object code in isrs code of dist. mark |
112 distance_mark_function varchar(8) PRIMARY KEY | |
113 ); | |
78
012948f18faa
Distance marks as link between 1D and 2D.
Tom Gottfried <tom@intevation.de>
parents:
77
diff
changeset
|
114 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
115 CREATE TABLE position_codes ( |
182 | 116 position_code char(2) PRIMARY KEY |
117 -- Use smallint because of fairway availability provided on daily basis? | |
118 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, | |
119 -- sheet "Position_code" or RIS-Index encoding guide? | |
120 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. | |
121 -- Clarify! | |
122 -- TODO: Do we need an attribute "meaning" or so? | |
123 ); | |
87
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
124 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
125 CREATE TABLE levels_of_service ( |
182 | 126 level_of_service smallint PRIMARY KEY |
127 ); | |
368
78588014ad0a
Populate levels_of_service
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
128 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
|
129 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
130 CREATE TABLE riverbed_materials ( |
182 | 131 material varchar PRIMARY KEY |
132 -- XXX: Should this table contain choices from DRC 2.2.3 or | |
133 -- from IENC Encoding Guide M.4.3, attribute NATSUR? | |
134 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
135 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
136 CREATE TABLE survey_types ( |
182 | 137 survey_type varchar PRIMARY KEY |
138 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
139 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
140 CREATE TABLE coverage_types ( |
182 | 141 coverage_type varchar PRIMARY KEY |
142 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
143 |
577
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
144 CREATE TABLE limiting_factors ( |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
145 limiting_factor varchar PRIMARY KEY |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
146 ); |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
147 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
148 CREATE TABLE measure_types ( |
182 | 149 measure_type varchar PRIMARY KEY |
150 ); | |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
151 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
152 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
153 -- Namespace for user management related data |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
154 CREATE SCHEMA users |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
155 CREATE TABLE responsibility_areas ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
156 country char(2) PRIMARY KEY REFERENCES countries, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
157 area geography(MULTIPOLYGON, 4326) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
158 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
159 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
160 CREATE TABLE templates ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
161 template_name varchar PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
162 template_data bytea NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
163 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
|
164 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
165 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
|
166 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
|
167 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
168 CREATE TABLE user_templates ( |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
169 username varchar NOT NULL |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
170 REFERENCES internal.user_profiles |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
171 ON DELETE CASCADE ON UPDATE CASCADE, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
172 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
|
173 PRIMARY KEY (username, template_name) |
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 ; |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
176 ALTER TABLE internal.user_profiles ADD |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
177 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
|
178 |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
179 |
831
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
180 -- Namespace for system wide configuration |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
181 CREATE SCHEMA systemconf |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
182 CREATE TABLE feature_colours ( |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
183 feature_name varchar, |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
184 style_attr varchar, |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
185 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
|
186 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
|
187 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
|
188 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
|
189 PRIMARY KEY (feature_name, style_attr) |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
190 ) |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
191 ; |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
192 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
193 -- 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
|
194 CREATE SCHEMA waterway |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
195 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
196 -- Eventually obsolete. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
197 -- 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
|
198 -- CREATE TABLE rwdrs ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
199 -- tretch isrsrange PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
200 -- -- 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
|
201 -- -- 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
|
202 -- -- below anyhow. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
203 -- -- 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
|
204 -- -- 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
|
205 -- -- => 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
|
206 -- -- factor, this might be an issue. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
207 -- rwdr double precision NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
208 -- EXCLUDE USING GIST (stretch WITH &&) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
209 --) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
210 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
211 CREATE TABLE waterway_area ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
212 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
|
213 area geography(POLYGON, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
214 catccl smallint REFERENCES catccls, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
215 dirimp smallint REFERENCES dirimps |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
216 ) |
371
d5b707bbd1d5
Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
217 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
|
218 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
219 CREATE TABLE gauges ( |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
577
diff
changeset
|
220 location isrs PRIMARY KEY CHECK( |
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
577
diff
changeset
|
221 (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
|
222 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
|
223 objname varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
224 is_left boolean, -- XXX: Or reference position_codes? |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
225 geom geography(POINT, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
226 applicability isrsrange, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
227 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
|
228 -- 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
|
229 -- (a gauge is not a berth!) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
230 -- 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
|
231 zero_point double precision NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
232 geodref varchar(4) REFERENCES depth_references, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
233 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
|
234 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
235 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
236 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
|
237 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
|
238 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
239 CREATE TABLE gauges_reference_water_levels ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
240 gauge_id isrs NOT NULL REFERENCES gauges, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
241 reference_water_level varchar(20) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
242 NOT NULL REFERENCES reference_water_levels, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
243 PRIMARY KEY (gauge_id, reference_water_level), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
244 value int NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
245 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
246 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
247 CREATE TABLE gauge_measurements ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
248 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
|
249 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
|
250 PRIMARY KEY (fk_gauge_id, measure_date), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
251 -- 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
|
252 -- 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
|
253 -- 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
|
254 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
|
255 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
|
256 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
|
257 -- 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
|
258 -- XXX: Always ZPG? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
259 water_level double precision NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
260 predicted boolean NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
261 is_waterlevel boolean NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
262 -- 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
|
263 -- 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
|
264 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
|
265 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
|
266 --- 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
|
267 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
|
268 source_organization varchar NOT NULL, -- "originator" |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
269 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
|
270 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
271 CREATE TRIGGER gauge_measurements_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
272 BEFORE UPDATE ON gauge_measurements |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
273 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
|
274 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
275 CREATE TABLE waterway_axis ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
276 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
|
277 wtwaxs geography(LINESTRING, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
278 -- 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
|
279 objnam varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
280 nobjnam varchar |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
281 ) |
371
d5b707bbd1d5
Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
282 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
|
283 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
284 -- 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
|
285 -- 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
|
286 -- distance marks along waterway axis (see SUC7). |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
287 CREATE TABLE distance_marks ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
288 location_code isrs PRIMARY KEY, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
289 geom geography(POINT, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
290 distance_mark_function varchar(8) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
291 NOT NULL REFERENCES distance_mark_functions, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
292 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
293 -- to encode the same thing as the object code in ISRS location code. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
294 position_code char(2) NOT NULL REFERENCES position_codes |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
295 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
296 |
690
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
297 -- 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
|
298 -- 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
|
299 -- 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
|
300 CREATE VIEW waterway.distance_marks_geoserver AS |
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
301 SELECT location_code::VARCHAR, |
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
302 geom::Geometry(POINT, 4326), |
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
303 distance_mark_function, |
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
304 position_code, |
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
305 (location_code).hectometre |
697
77928a3497f1
schema: fix view distance_marks_geoserver
Bernhard Reiter <bernhard@intevation.de>
parents:
690
diff
changeset
|
306 FROM waterway.distance_marks |
690
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
307 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
308 CREATE TABLE sections_stretches ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
309 id varchar PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
310 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
|
311 stretch isrsrange, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
312 objnam varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
313 nobjnam varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
314 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
|
315 source_organization varchar NOT NULL, |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
316 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
|
317 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
318 CREATE TRIGGER sections_stretches_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
319 BEFORE UPDATE ON sections_stretches |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
320 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
|
321 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
322 CREATE TABLE waterway_profiles ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
323 location isrs NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
324 validity tstzrange, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
325 EXCLUDE USING GIST (validity WITH &&), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
326 PRIMARY KEY (location, validity), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
327 lnwl smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
328 mwl smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
329 hnwl smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
330 fe30 smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
331 fe100 smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
332 -- XXX: further normalise using reference_water_levels? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
333 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
|
334 OR validity IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
335 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
345
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
336 source_organization varchar NOT NULL, |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
337 staging_done boolean NOT NULL DEFAULT false |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
338 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
339 CREATE TRIGGER waterway_profiles_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
340 BEFORE UPDATE ON waterway_profiles |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
341 FOR EACH ROW EXECUTE PROCEDURE update_date_info() |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
342 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
343 CREATE TABLE fairway_dimensions ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
344 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
|
345 area geography(POLYGON, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
346 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
|
347 min_width smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
348 max_width smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
349 min_depth smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
350 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
|
351 source_organization varchar NOT NULL, |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
352 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
|
353 ) |
371
d5b707bbd1d5
Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
354 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
|
355 CREATE TRIGGER fairway_dimensions_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
356 BEFORE UPDATE ON fairway_dimensions |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
357 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
|
358 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
359 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
360 -- Bottlenecks |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
361 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
362 -- 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
|
363 -- (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
|
364 -- 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
|
365 CREATE TABLE bottlenecks ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
366 bottleneck_id varchar PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
367 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
|
368 -- 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
|
369 objnam varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
370 nobjnm varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
371 stretch isrsrange NOT NULL, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
372 area geography(POLYGON, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
373 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
|
374 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
|
375 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
|
376 revisiting_time smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
377 limiting varchar NOT NULL REFERENCES limiting_factors, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
378 -- surtyp varchar NOT NULL REFERENCES survey_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
379 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
380 -- coverage varchar REFERENCES coverage_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
381 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
382 -- 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
|
383 -- different model approach? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
384 -- 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
|
385 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
386 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
|
387 source_organization varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
388 -- 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
|
389 staging_done boolean NOT NULL DEFAULT false |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
390 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
391 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
|
392 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
|
393 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
394 CREATE TABLE bottlenecks_riverbed_materials ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
395 bottleneck_id varchar REFERENCES bottlenecks, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
396 riverbed varchar REFERENCES riverbed_materials, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
397 -- 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
|
398 PRIMARY KEY (bottleneck_id, riverbed) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
399 ) |
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 CREATE TABLE sounding_results ( |
656
9ef2f80a4645
Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents:
655
diff
changeset
|
402 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
|
403 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
404 date_info date NOT NULL, |
656
9ef2f80a4645
Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents:
655
diff
changeset
|
405 UNIQUE (bottleneck_id, date_info), |
917
5b90217aa1bb
Fix nullability in sounding results
Tom Gottfried <tom@intevation.de>
parents:
916
diff
changeset
|
406 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
|
407 surtyp varchar REFERENCES survey_types, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
408 coverage varchar REFERENCES coverage_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
409 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
|
410 point_cloud geography(MULTIPOINTZ, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
411 staging_done boolean NOT NULL DEFAULT false |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
412 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
413 |
720
aeaa2adf5a8b
octree: write SQL insert statements for octree indices.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
697
diff
changeset
|
414 CREATE TABLE octrees ( |
aeaa2adf5a8b
octree: write SQL insert statements for octree indices.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
697
diff
changeset
|
415 sounding_result_id int NOT NULL UNIQUE REFERENCES sounding_results, |
aeaa2adf5a8b
octree: write SQL insert statements for octree indices.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
697
diff
changeset
|
416 checksum varchar NOT NULL, |
aeaa2adf5a8b
octree: write SQL insert statements for octree indices.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
697
diff
changeset
|
417 octree_index bytea NOT NULL |
aeaa2adf5a8b
octree: write SQL insert statements for octree indices.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
697
diff
changeset
|
418 ) |
731
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
419 |
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
420 CREATE TABLE sounding_results_contour_lines ( |
736
34ea9d5df653
Fixed problem with unique constraint for countour lines.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
731
diff
changeset
|
421 sounding_result_id int NOT NULL REFERENCES sounding_results, |
731
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
422 height numeric NOT NULL, |
919
271561dce2e6
Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents:
917
diff
changeset
|
423 lines geography(multilinestring, 4326) NOT NULL, |
731
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
424 UNIQUE (sounding_result_id, height) |
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
425 ) |
821
d32516051d08
Add view to help geoserver serve contour lines
Tom Gottfried <tom@intevation.de>
parents:
736
diff
changeset
|
426 -- 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
|
427 -- 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
|
428 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
|
429 SELECT bottleneck_id, |
d32516051d08
Add view to help geoserver serve contour lines
Tom Gottfried <tom@intevation.de>
parents:
736
diff
changeset
|
430 date_info, |
d32516051d08
Add view to help geoserver serve contour lines
Tom Gottfried <tom@intevation.de>
parents:
736
diff
changeset
|
431 height, |
919
271561dce2e6
Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents:
917
diff
changeset
|
432 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
|
433 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
|
434 JOIN waterway.sounding_results sr |
d32516051d08
Add view to help geoserver serve contour lines
Tom Gottfried <tom@intevation.de>
parents:
736
diff
changeset
|
435 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
|
436 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
437 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
438 -- Fairway availability |
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 fairway_availability ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
441 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
|
442 position_code char(2) REFERENCES position_codes, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
443 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
444 surdat date NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
445 UNIQUE (bottleneck_id, surdat), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
446 -- 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
|
447 critical boolean, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
448 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
|
449 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
450 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
451 CREATE TRIGGER fairway_availability_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
452 BEFORE UPDATE ON fairway_availability |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
453 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
|
454 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
455 CREATE TABLE fa_reference_values ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
456 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
|
457 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
|
458 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
|
459 fairway_depth smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
460 fairway_width smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
461 fairway_radius int, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
462 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
|
463 IS NOT NULL), |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
464 shallowest_spot geography(POINT, 4326) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
465 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
466 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
467 CREATE TABLE bottleneck_pdfs ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
468 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
|
469 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
|
470 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
|
471 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
|
472 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
|
473 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
474 ) |
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 effective_fairway_availability ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
477 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
|
478 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
|
479 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
|
480 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
|
481 available_depth_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
482 available_width_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
483 water_level_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
484 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
|
485 water_level_value) IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
486 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
|
487 source_organization varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
488 forecast_generation_time timestamp with time zone, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
489 CHECK(measure_type <> 'forecasted' |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
490 OR forecast_generation_time IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
491 value_lifetime timestamp with time zone, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
492 CHECK(measure_type = 'minimum guaranteed' |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
493 OR value_lifetime IS NOT NULL) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
494 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
495 ; |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
496 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
497 COMMIT; |