Mercurial > gemma
annotate schema/gemma.sql @ 1234:1a5564655f2a
refac: Sidebar reorganized
In order to make context switches between administrative tasks
which are map related and those which are system related, we now have
a category "administration" and "systemadministration".
The Riverbedmorphology does nothing than display the map, so it is
renamed to that (map). In case the context of "systemadministration"
is chosen, the "map" brings you just back to the map.
author | Thomas Junk <thomas.junk@intevation.de> |
---|---|
date | Tue, 20 Nov 2018 09:54:53 +0100 |
parents | 7db850de0952 |
children | 9f7dc950ffd2 |
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 |
934
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
110 CREATE TABLE catdis ( |
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
111 catdis smallint PRIMARY KEY |
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
112 -- 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
|
113 -- (see page 171 of edition 2.3) |
182 | 114 ); |
934
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
115 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
|
116 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
117 CREATE TABLE position_codes ( |
182 | 118 position_code char(2) PRIMARY KEY |
119 -- Use smallint because of fairway availability provided on daily basis? | |
120 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, | |
121 -- sheet "Position_code" or RIS-Index encoding guide? | |
122 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. | |
123 -- Clarify! | |
124 -- TODO: Do we need an attribute "meaning" or so? | |
125 ); | |
87
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
126 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
127 CREATE TABLE levels_of_service ( |
182 | 128 level_of_service smallint PRIMARY KEY |
129 ); | |
368
78588014ad0a
Populate levels_of_service
Tom Gottfried <tom@intevation.de>
parents:
361
diff
changeset
|
130 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
|
131 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
132 CREATE TABLE riverbed_materials ( |
182 | 133 material varchar PRIMARY KEY |
134 -- XXX: Should this table contain choices from DRC 2.2.3 or | |
135 -- from IENC Encoding Guide M.4.3, attribute NATSUR? | |
136 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
137 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
138 CREATE TABLE survey_types ( |
182 | 139 survey_type varchar PRIMARY KEY |
140 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
141 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
142 CREATE TABLE coverage_types ( |
182 | 143 coverage_type varchar PRIMARY KEY |
144 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
145 |
577
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
146 CREATE TABLE limiting_factors ( |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
147 limiting_factor varchar PRIMARY KEY |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
148 ); |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
149 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
150 CREATE TABLE measure_types ( |
182 | 151 measure_type varchar PRIMARY KEY |
152 ); | |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
153 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
154 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
155 -- Namespace for user management related data |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
156 CREATE SCHEMA users |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
157 CREATE TABLE responsibility_areas ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
158 country char(2) PRIMARY KEY REFERENCES countries, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
159 area geography(MULTIPOLYGON, 4326) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
160 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
161 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
162 CREATE TABLE templates ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
163 template_name varchar PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
164 template_data bytea NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
165 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
|
166 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
167 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
|
168 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
|
169 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
170 CREATE TABLE user_templates ( |
225
8b9cae6d3a21
Add database function to update role and user profile
Tom Gottfried <tom@intevation.de>
parents:
212
diff
changeset
|
171 username varchar NOT NULL |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
172 REFERENCES internal.user_profiles |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
173 ON DELETE CASCADE ON UPDATE CASCADE, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
174 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
|
175 PRIMARY KEY (username, template_name) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
176 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
177 ; |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
178 ALTER TABLE internal.user_profiles ADD |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
179 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
|
180 |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
181 |
831
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
182 -- Namespace for system wide configuration |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
183 CREATE SCHEMA systemconf |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
184 CREATE TABLE feature_colours ( |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
185 feature_name varchar, |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
186 style_attr varchar, |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
187 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
|
188 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
|
189 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
|
190 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
|
191 PRIMARY KEY (feature_name, style_attr) |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
192 ) |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
193 ; |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
194 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
195 -- 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
|
196 CREATE SCHEMA waterway |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
197 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
198 -- Eventually obsolete. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
199 -- 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
|
200 -- CREATE TABLE rwdrs ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
201 -- tretch isrsrange PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
202 -- -- 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
|
203 -- -- 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
|
204 -- -- below anyhow. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
205 -- -- 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
|
206 -- -- 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
|
207 -- -- => 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
|
208 -- -- factor, this might be an issue. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
209 -- rwdr double precision NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
210 -- EXCLUDE USING GIST (stretch WITH &&) |
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 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
213 CREATE TABLE waterway_area ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
214 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
|
215 area geography(POLYGON, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
216 catccl smallint REFERENCES catccls, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
217 dirimp smallint REFERENCES dirimps |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
218 ) |
371
d5b707bbd1d5
Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
219 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
|
220 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
221 CREATE TABLE gauges ( |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
577
diff
changeset
|
222 location isrs PRIMARY KEY CHECK( |
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
577
diff
changeset
|
223 (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
|
224 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
|
225 objname varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
226 is_left boolean, -- XXX: Or reference position_codes? |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
227 geom geography(POINT, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
228 applicability isrsrange, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
229 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
|
230 -- 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
|
231 -- (a gauge is not a berth!) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
232 -- 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
|
233 zero_point double precision NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
234 geodref varchar(4) REFERENCES depth_references, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
235 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
|
236 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
237 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
238 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
|
239 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
|
240 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
241 CREATE TABLE gauges_reference_water_levels ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
242 gauge_id isrs NOT NULL REFERENCES gauges, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
243 reference_water_level varchar(20) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
244 NOT NULL REFERENCES reference_water_levels, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
245 PRIMARY KEY (gauge_id, reference_water_level), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
246 value int NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
247 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
248 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
249 CREATE TABLE gauge_measurements ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
250 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
|
251 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
|
252 PRIMARY KEY (fk_gauge_id, measure_date), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
253 -- 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
|
254 -- 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
|
255 -- 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
|
256 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
|
257 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
|
258 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
|
259 -- 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
|
260 -- XXX: Always ZPG? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
261 water_level double precision NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
262 predicted boolean NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
263 is_waterlevel boolean NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
264 -- 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
|
265 -- 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
|
266 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
|
267 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
|
268 --- 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
|
269 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
|
270 source_organization varchar NOT NULL, -- "originator" |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
271 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
|
272 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
273 CREATE TRIGGER gauge_measurements_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
274 BEFORE UPDATE ON gauge_measurements |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
275 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
|
276 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
277 CREATE TABLE waterway_axis ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
278 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
|
279 wtwaxs geography(LINESTRING, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
280 -- 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
|
281 objnam varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
282 nobjnam varchar |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
283 ) |
371
d5b707bbd1d5
Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
284 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
|
285 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
286 -- 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
|
287 -- 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
|
288 -- 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
|
289 CREATE TABLE distance_marks_virtual ( |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
290 location_code isrs PRIMARY KEY, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
291 geom geography(POINT, 4326) NOT NULL, |
948
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
292 related_enc varchar(12) NOT NULL |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
293 ) |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
294 |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
295 CREATE TABLE distance_marks ( |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
296 country char(2) NOT NULL REFERENCES countries, |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
297 hectom int NOT NULL, |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
298 geom geography(POINT, 4326) NOT NULL, |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
299 -- 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
|
300 PRIMARY KEY (country, hectom, geom), |
934
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
301 catdis smallint NOT NULL REFERENCES catdis, |
948
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
302 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
|
303 related_enc varchar(12) NOT NULL |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
304 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
305 |
690
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
306 -- 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
|
307 -- 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
|
308 -- 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
|
309 CREATE VIEW waterway.distance_marks_geoserver AS |
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
310 SELECT location_code::VARCHAR, |
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
311 geom::Geometry(POINT, 4326), |
948
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
312 related_enc, |
690
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
313 (location_code).hectometre |
948
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
314 FROM waterway.distance_marks_virtual |
690
f595b3455d75
Schema: Add view for waterway axis
Bernhard Reiter <bernhard@intevation.de>
parents:
659
diff
changeset
|
315 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
316 CREATE TABLE sections_stretches ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
317 id varchar PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
318 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
|
319 stretch isrsrange, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
320 objnam varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
321 nobjnam varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
322 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
|
323 source_organization varchar NOT NULL, |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
324 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
|
325 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
326 CREATE TRIGGER sections_stretches_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
327 BEFORE UPDATE ON sections_stretches |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
328 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
|
329 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
330 CREATE TABLE waterway_profiles ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
331 location isrs NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
332 validity tstzrange, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
333 EXCLUDE USING GIST (validity WITH &&), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
334 PRIMARY KEY (location, validity), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
335 lnwl smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
336 mwl smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
337 hnwl smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
338 fe30 smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
339 fe100 smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
340 -- XXX: further normalise using reference_water_levels? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
341 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
|
342 OR validity IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
343 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
|
344 source_organization varchar NOT NULL, |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
345 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
|
346 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
347 CREATE TRIGGER waterway_profiles_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
348 BEFORE UPDATE ON waterway_profiles |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
349 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
|
350 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
351 CREATE TABLE fairway_dimensions ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
352 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
|
353 area geography(POLYGON, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
354 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
|
355 min_width smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
356 max_width smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
357 min_depth smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
358 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
|
359 source_organization varchar NOT NULL, |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
360 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
|
361 ) |
371
d5b707bbd1d5
Do not create B-Tree indexes on potentially large spatial data
Tom Gottfried <tom@intevation.de>
parents:
370
diff
changeset
|
362 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
|
363 CREATE TRIGGER fairway_dimensions_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
364 BEFORE UPDATE ON fairway_dimensions |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
365 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
|
366 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
367 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
368 -- Bottlenecks |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
369 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
370 -- 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
|
371 -- (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
|
372 -- 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
|
373 CREATE TABLE bottlenecks ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
374 bottleneck_id varchar PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
375 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
|
376 -- 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
|
377 objnam varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
378 nobjnm varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
379 stretch isrsrange NOT NULL, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
380 area geography(POLYGON, 4326) NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
381 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
|
382 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
|
383 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
|
384 revisiting_time smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
385 limiting varchar NOT NULL REFERENCES limiting_factors, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
386 -- surtyp varchar NOT NULL REFERENCES survey_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
387 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
388 -- coverage varchar REFERENCES coverage_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
389 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
390 -- 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
|
391 -- different model approach? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
392 -- 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
|
393 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
394 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
|
395 source_organization varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
396 -- 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
|
397 staging_done boolean NOT NULL DEFAULT false |
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 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
|
400 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
|
401 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
402 CREATE TABLE bottlenecks_riverbed_materials ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
403 bottleneck_id varchar REFERENCES bottlenecks, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
404 riverbed varchar REFERENCES riverbed_materials, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
405 -- 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
|
406 PRIMARY KEY (bottleneck_id, riverbed) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
407 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
408 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
409 CREATE TABLE sounding_results ( |
656
9ef2f80a4645
Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents:
655
diff
changeset
|
410 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
|
411 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
412 date_info date NOT NULL, |
656
9ef2f80a4645
Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents:
655
diff
changeset
|
413 UNIQUE (bottleneck_id, date_info), |
917
5b90217aa1bb
Fix nullability in sounding results
Tom Gottfried <tom@intevation.de>
parents:
916
diff
changeset
|
414 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
|
415 surtyp varchar REFERENCES survey_types, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
416 coverage varchar REFERENCES coverage_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
417 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
|
418 point_cloud geography(MULTIPOINTZ, 4326) NOT NULL, |
1131
2e6b47cdb2ca
Store octrees along with sounding results
Tom Gottfried <tom@intevation.de>
parents:
1085
diff
changeset
|
419 octree_checksum varchar, |
2e6b47cdb2ca
Store octrees along with sounding results
Tom Gottfried <tom@intevation.de>
parents:
1085
diff
changeset
|
420 octree_index bytea, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
421 staging_done boolean NOT NULL DEFAULT false |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
422 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
423 |
731
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
424 CREATE TABLE sounding_results_contour_lines ( |
1133
dd4071019676
Delete contour lines with their sounding result
Tom Gottfried <tom@intevation.de>
parents:
1131
diff
changeset
|
425 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
|
426 ON DELETE CASCADE, |
731
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
427 height numeric NOT NULL, |
919
271561dce2e6
Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents:
917
diff
changeset
|
428 lines geography(multilinestring, 4326) NOT NULL, |
1133
dd4071019676
Delete contour lines with their sounding result
Tom Gottfried <tom@intevation.de>
parents:
1131
diff
changeset
|
429 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
|
430 ) |
821
d32516051d08
Add view to help geoserver serve contour lines
Tom Gottfried <tom@intevation.de>
parents:
736
diff
changeset
|
431 -- 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
|
432 -- 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
|
433 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
|
434 SELECT bottleneck_id, |
d32516051d08
Add view to help geoserver serve contour lines
Tom Gottfried <tom@intevation.de>
parents:
736
diff
changeset
|
435 date_info, |
d32516051d08
Add view to help geoserver serve contour lines
Tom Gottfried <tom@intevation.de>
parents:
736
diff
changeset
|
436 height, |
919
271561dce2e6
Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents:
917
diff
changeset
|
437 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
|
438 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
|
439 JOIN waterway.sounding_results sr |
d32516051d08
Add view to help geoserver serve contour lines
Tom Gottfried <tom@intevation.de>
parents:
736
diff
changeset
|
440 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
|
441 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
442 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
443 -- Fairway availability |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
444 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
445 CREATE TABLE fairway_availability ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
446 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
|
447 position_code char(2) REFERENCES position_codes, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
448 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
449 surdat date NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
450 UNIQUE (bottleneck_id, surdat), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
451 -- 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
|
452 critical boolean, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
453 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
|
454 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
455 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
456 CREATE TRIGGER fairway_availability_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
457 BEFORE UPDATE ON fairway_availability |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
458 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
|
459 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
460 CREATE TABLE fa_reference_values ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
461 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
|
462 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
|
463 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
|
464 fairway_depth smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
465 fairway_width smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
466 fairway_radius int, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
467 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
|
468 IS NOT NULL), |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
469 shallowest_spot geography(POINT, 4326) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
470 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
471 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
472 CREATE TABLE bottleneck_pdfs ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
473 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
|
474 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
|
475 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
|
476 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
|
477 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
|
478 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
479 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
480 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
481 CREATE TABLE effective_fairway_availability ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
482 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
|
483 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
|
484 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
|
485 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
|
486 available_depth_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
487 available_width_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
488 water_level_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
489 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
|
490 water_level_value) IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
491 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
|
492 source_organization varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
493 forecast_generation_time timestamp with time zone, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
494 CHECK(measure_type <> 'forecasted' |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
495 OR forecast_generation_time IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
496 value_lifetime timestamp with time zone, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
497 CHECK(measure_type = 'minimum guaranteed' |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
498 OR value_lifetime IS NOT NULL) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
499 ) |
1085
72cad6277e3a
Added waterway.bottleneck_overview view to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1012
diff
changeset
|
500 |
72cad6277e3a
Added waterway.bottleneck_overview view to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1012
diff
changeset
|
501 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
|
502 SELECT |
72cad6277e3a
Added waterway.bottleneck_overview view to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1012
diff
changeset
|
503 objnam AS name, |
72cad6277e3a
Added waterway.bottleneck_overview view to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1012
diff
changeset
|
504 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
|
505 (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
|
506 (upper(stretch)).hectometre AS to, |
72cad6277e3a
Added waterway.bottleneck_overview view to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1012
diff
changeset
|
507 sr.current |
72cad6277e3a
Added waterway.bottleneck_overview view to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1012
diff
changeset
|
508 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
|
509 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
|
510 waterway.sounding_results |
72cad6277e3a
Added waterway.bottleneck_overview view to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1012
diff
changeset
|
511 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
|
512 ORDER BY objnam |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
513 ; |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
514 |
1012
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
515 -- |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
516 -- Import queue and respective logging |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
517 -- |
1190
e3de65179889
The imort queue has now six states:
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1172
diff
changeset
|
518 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
|
519 'queued', 'running', 'failed', |
e3de65179889
The imort queue has now six states:
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1172
diff
changeset
|
520 'pending', 'accepted', 'declined' |
e3de65179889
The imort queue has now six states:
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1172
diff
changeset
|
521 ); |
1012
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
522 |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
523 CREATE TABLE waterway.imports ( |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
524 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
|
525 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
|
526 enqueued timestamp NOT NULL DEFAULT now(), |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
527 kind varchar NOT NULL, |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
528 username varchar NOT NULL |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
529 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
|
530 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
|
531 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
|
532 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
|
533 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
|
534 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
|
535 ON UPDATE CASCADE, |
1012
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
536 data TEXT |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
537 ); |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
538 |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
539 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
|
540 |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
541 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
|
542 |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
543 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
|
544 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
|
545 time timestamp NOT NULL DEFAULT now(), |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
546 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
|
547 msg TEXT NOT NULL |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
548 ); |
875b0a9a3c92
Fixed schema definition of import queue.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
998
diff
changeset
|
549 |
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
|
550 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
|
551 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
|
552 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
|
553 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
|
554 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
|
555 ); |
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
|
556 |
1193
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
557 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
|
558 $$ |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
559 DECLARE |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
560 tmp RECORD; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
561 BEGIN |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
562 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
|
563 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
|
564 LOOP |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
565 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
|
566 END LOOP; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
567 END; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
568 $$ |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
569 LANGUAGE plpgsql; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
570 |
1172
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
571 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
|
572 $$ |
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
573 BEGIN |
1193
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
574 EXECUTE format('DELETE FROM %s WHERE id = $1', OLD.relation) USING OLD.key; |
1172
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
575 END; |
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
576 $$ |
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
577 LANGUAGE plpgsql; |
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
578 |
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
579 CREATE TRIGGER delete_import AFTER DELETE ON waterway.track_imports |
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
580 FOR EACH ROW EXECUTE PROCEDURE waterway.del_import(); |
c3955e3db074
Propagate deletion of imports through the track_imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1168
diff
changeset
|
581 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
582 COMMIT; |