Mercurial > gemma

comparison schema/gemma.sql @ 195:5dc8e734487a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Introduce database schemas as privilege-based namespaces Some privileges changed (e.g. for responsibility_areas), but additional privileges were not left off intentionally before. Search path settings have been replaced by schema-qualifying names in statements to prevent object definitions from being dependend on search path settings.
author Tom Gottfried <tom@intevation.de>
date Fri, 20 Jul 2018 17:28:16 +0200
parents bc7829defa99
children 9585982180ab
comparison
equal deleted inserted replaced
194:cf5dcc1761df 195:5dc8e734487a
33 ); 33 );
34 34
35 -- 35 --
36 -- GEMMA data 36 -- GEMMA data
37 -- 37 --
38 CREATE SCHEMA gemma; 38
39 CREATE SCHEMA gemma_waterway; 39 -- Namespace to be accessed by sys_admin only
40 CREATE SCHEMA gemma_fairway; 40 CREATE SCHEMA sys_admin
41 SET search_path TO public, gemma, gemma_waterway, gemma_fairway; 41 CREATE TABLE sys_admin.system_config (
42 -- TODO: consolidate schemas. The current distribution of tables is mainly 42 config_key varchar PRIMARY KEY,
43 -- for diagram generation. A privilege based distribution might ease 43 config_val varchar
44 -- privilege management. 44 )
45 45 ;
46 -- 46
47 -- Auxiliary tables 47 --
48 -- 48 -- Look-up tables with data that are static in a running system
49 CREATE TABLE gemma.system_config ( 49 --
50 config_key varchar PRIMARY KEY, 50 CREATE TABLE countries (
51 config_val varchar
52 );
53
54 CREATE TABLE gemma.countries (
55 country_code char(2) PRIMARY KEY -- ISO 3166 country code 51 country_code char(2) PRIMARY KEY -- ISO 3166 country code
56 -- A smallint PK would require even less disk space i.e. on the FK side. 52 -- A smallint PK would require even less disk space i.e. on the FK side.
57 -- This might be an issue in case cache space becomes a limiting 53 -- This might be an issue in case cache space becomes a limiting
58 -- factor when there are many FKs pointing here. 54 -- factor when there are many FKs pointing here.
59 ); 55 );
60 56
61 CREATE TABLE gemma.responsibility_areas ( 57 CREATE TABLE language_codes (
62 country char(2) PRIMARY KEY REFERENCES countries,
63 area geometry(MULTIPOLYGON, 4326)
64 --XXX: Should be geography (elsewhere too)
65 );
66
67 CREATE TABLE gemma.language_codes (
68 language_code varchar PRIMARY KEY 58 language_code varchar PRIMARY KEY
69 ); 59 );
70 60
71 CREATE TABLE gemma.user_profiles ( 61 CREATE TABLE catccls (
72 username varchar PRIMARY KEY, -- TODO: check it's in pg_roles by trigger
73 country char(2) NOT NULL REFERENCES countries,
74 map_extent box2d,
75 email_adress varchar NOT NULL UNIQUE
76 );
77
78 CREATE TABLE gemma.templates (
79 template_name varchar PRIMARY KEY,
80 template_data bytea NOT NULL,
81 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
82 );
83 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
84 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
85
86 CREATE TABLE gemma.user_templates (
87 username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE,
88 template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE,
89 PRIMARY KEY (username, template_name)
90 );
91
92 --
93 -- General river information
94 --
95
96 -- Eventually obsolete.
97 -- See https://roundup-intern.intevation.de/gemma/issue5
98 -- CREATE TABLE rwdrs (
99 -- stretch isrsrange PRIMARY KEY,
100 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
101 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index
102 -- -- below anyhow.
103 -- -- Is it a good idea to build B-tree indexes on relatively large
104 -- -- composites of string values or should we use inter PKs?
105 -- -- => In case the index is used and cache space becomes a limiting
106 -- -- factor, this might be an issue.
107 -- rwdr double precision NOT NULL,
108 -- EXCLUDE USING GIST (stretch WITH &&)
109 --);
110
111 CREATE TABLE gemma_waterway.catccls (
112 catccl smallint PRIMARY KEY 62 catccl smallint PRIMARY KEY
113 -- TODO: Do we need name and/or definition from IENC feature catalogue? 63 -- TODO: Do we need name and/or definition from IENC feature catalogue?
114 ); 64 );
115 65
116 CREATE TABLE gemma_waterway.dirimps ( 66 CREATE TABLE dirimps (
117 dirimp smallint PRIMARY KEY 67 dirimp smallint PRIMARY KEY
118 -- TODO: Do we need name and/or definition from IENC feature catalogue? 68 -- TODO: Do we need name and/or definition from IENC feature catalogue?
119 ); 69 );
120 70
121 CREATE TABLE gemma_waterway.waterway_area ( 71 CREATE TABLE depth_references (
122 area geometry(POLYGON, 4326) PRIMARY KEY,
123 catccl smallint REFERENCES catccls,
124 dirimp smallint REFERENCES dirimps
125 );
126
127 CREATE TABLE gemma_fairway.depth_references (
128 depth_reference varchar(4) PRIMARY KEY 72 depth_reference varchar(4) PRIMARY KEY
129 -- See col. AB and AI RIS-Index Encoding Guide 73 -- See col. AB and AI RIS-Index Encoding Guide
130 -- XXX: We need a way to distinguish between geodetic (eg. col. AP 74 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
131 -- RIS-Index) and other references (e.g. col. AB and AI): 75 -- RIS-Index) and other references (e.g. col. AB and AI):
132 -- _ multi-column FK with a boolean column (geodetic/non-geodetic; 76 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
133 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. 77 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
134 -- _ Do not mixup things with different meanings in one table at all 78 -- _ Do not mixup things with different meanings in one table at all
135 -- (which would mean a model differing a bit from RIS-Index ideas) 79 -- (which would mean a model differing a bit from RIS-Index ideas)
136 ); 80 );
137 81
138 CREATE TABLE gemma_fairway.reference_water_levels ( 82 CREATE TABLE reference_water_levels (
139 reference_water_level varchar(20) PRIMARY KEY 83 reference_water_level varchar(20) PRIMARY KEY
140 ); 84 );
141 85
142 CREATE TABLE gemma_fairway.gauges ( 86 CREATE TABLE distance_mark_functions (
143 location isrs PRIMARY KEY,
144 function_code varchar(10) NOT NULL, -- XXX: What is this really for?
145 objname varchar NOT NULL,
146 is_left boolean, -- XXX: Or reference position_codes?
147 geom geometry(POINT, 4326) NOT NULL,
148 applicability isrsrange,
149 validity tstzrange, -- XXX: Should ranges be NOT NULL? In DRC, only copy
150 -- pasted text from a more general specification is given
151 -- (a gauge is not a berth!)
152 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
153 zero_point double precision NOT NULL,
154 geodref varchar(4) REFERENCES depth_references,
155 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
156 source_organization varchar NOT NULL
157 );
158 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
159 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
160
161 CREATE TABLE gemma_fairway.gauges_reference_water_levels (
162 gauge_id isrs NOT NULL REFERENCES gauges,
163 reference_water_level varchar(20)
164 NOT NULL REFERENCES reference_water_levels,
165 PRIMARY KEY (gauge_id, reference_water_level),
166 value int NOT NULL
167 );
168
169 CREATE TABLE gemma_fairway.gauge_measurements (
170 fk_gauge_id isrs NOT NULL REFERENCES gauges,
171 measure_date timestamp with time zone NOT NULL,
172 PRIMARY KEY (fk_gauge_id, measure_date),
173 -- XXX: Is country_code really relevant for GEMMA or just NtS?
174 -- country_code char(2) NOT NULL REFERENCES countries,
175 -- TODO: add relations to stuff provided as enumerations
176 sender varchar NOT NULL, -- "from" attribute from DRC
177 language_code varchar NOT NULL REFERENCES language_codes,
178 date_issue timestamp with time zone NOT NULL,
179 -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
180 -- XXX: Always ZPG?
181 water_level double precision NOT NULL,
182 predicted boolean NOT NULL,
183 is_waterlevel boolean NOT NULL,
184 -- XXX: "measure_code" if really only W or Q
185 -- XXX: Do we need "unit" attribute or can we normalise on import?
186 value_min double precision, -- XXX: NOT NULL if predicted?
187 value_max double precision, -- XXX: NOT NULL if predicted?
188 --- TODO: Add a double range type for checking?
189 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
190 source_organization varchar NOT NULL -- "originator"
191 );
192 CREATE TRIGGER gauge_measurements_date_info BEFORE UPDATE ON gauge_measurements
193 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
194
195 CREATE TABLE gemma_waterway.waterway_axis (
196 wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
197 -- TODO: Do we need to check data set quality as described in DRC 2.1.6?
198 objnam varchar NOT NULL,
199 nobjnam varchar
200 );
201
202 CREATE TABLE gemma_waterway.distance_mark_functions (
203 -- XXX: Redundant information to object code in isrs code of dist. mark 87 -- XXX: Redundant information to object code in isrs code of dist. mark
204 distance_mark_function varchar(8) PRIMARY KEY 88 distance_mark_function varchar(8) PRIMARY KEY
205 ); 89 );
206 90
207 CREATE TABLE gemma_waterway.position_codes ( 91 CREATE TABLE position_codes (
208 position_code char(2) PRIMARY KEY 92 position_code char(2) PRIMARY KEY
209 -- Use smallint because of fairway availability provided on daily basis? 93 -- Use smallint because of fairway availability provided on daily basis?
210 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, 94 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
211 -- sheet "Position_code" or RIS-Index encoding guide? 95 -- sheet "Position_code" or RIS-Index encoding guide?
212 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. 96 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
213 -- Clarify! 97 -- Clarify!
214 -- TODO: Do we need an attribute "meaning" or so? 98 -- TODO: Do we need an attribute "meaning" or so?
215 ); 99 );
216 100
217 -- This table allows linkage between the 1D ISRS location codes and 2D space 101 CREATE TABLE levels_of_service (
218 -- e.g. for cutting bottleneck area out of waterway area based on virtual
219 -- distance marks along waterway axis (see SUC7).
220 CREATE TABLE gemma_waterway.distance_marks (
221 location_code isrs PRIMARY KEY,
222 geom geometry(POINT, 4326) NOT NULL,
223 distance_mark_function varchar(8)
224 NOT NULL REFERENCES distance_mark_functions,
225 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
226 -- to encode the same thing as the object code in ISRS location code.
227 position_code char(2) NOT NULL REFERENCES position_codes
228 );
229
230 CREATE TABLE gemma_waterway.sections_stretches (
231 id varchar PRIMARY KEY,
232 is_section boolean NOT NULL, -- maps 'function' from interface
233 stretch isrsrange,
234 objnam varchar NOT NULL,
235 nobjnam varchar,
236 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
237 source_organization varchar NOT NULL
238 );
239 CREATE TRIGGER sections_stretches_date_info BEFORE UPDATE ON sections_stretches
240 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
241
242 CREATE TABLE gemma_waterway.waterway_profiles (
243 location isrs NOT NULL,
244 validity tstzrange,
245 EXCLUDE USING GIST (validity WITH &&),
246 PRIMARY KEY (location, validity),
247 lnwl smallint,
248 mwl smallint,
249 hnwl smallint,
250 fe30 smallint,
251 fe100 smallint,
252 -- XXX: further normalise using reference_water_levels?
253 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
254 OR validity IS NOT NULL),
255 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
256 source_organization varchar NOT NULL
257 );
258 CREATE TRIGGER waterway_profiles_date_info BEFORE UPDATE ON waterway_profiles
259 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
260
261 CREATE TABLE gemma_waterway.levels_of_service (
262 level_of_service smallint PRIMARY KEY 102 level_of_service smallint PRIMARY KEY
263 ); 103 );
264 104
265 CREATE TABLE gemma_waterway.fairway_dimensions ( 105 CREATE TABLE riverbed_materials (
266 area geometry(POLYGON, 4326) PRIMARY KEY,
267 level_of_service smallint NOT NULL REFERENCES levels_of_service,
268 min_width smallint NOT NULL,
269 max_width smallint NOT NULL,
270 min_depth smallint NOT NULL,
271 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
272 source_organization varchar NOT NULL
273 );
274 CREATE TRIGGER fairway_dimensions_date_info BEFORE UPDATE ON fairway_dimensions
275 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
276
277 --
278 -- Bottlenecks
279 --
280 CREATE TABLE gemma_fairway.riverbed_materials (
281 material varchar PRIMARY KEY 106 material varchar PRIMARY KEY
282 -- XXX: Should this table contain choices from DRC 2.2.3 or 107 -- XXX: Should this table contain choices from DRC 2.2.3 or
283 -- from IENC Encoding Guide M.4.3, attribute NATSUR? 108 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
284 ); 109 );
285 110
286 CREATE TABLE gemma_fairway.survey_types ( 111 CREATE TABLE survey_types (
287 survey_type varchar PRIMARY KEY 112 survey_type varchar PRIMARY KEY
288 ); 113 );
289 114
290 CREATE TABLE gemma_fairway.coverage_types ( 115 CREATE TABLE coverage_types (
291 coverage_type varchar PRIMARY KEY 116 coverage_type varchar PRIMARY KEY
292 ); 117 );
293 118
294 CREATE TABLE gemma_fairway.limiting_factors ( 119 CREATE TABLE limiting_factors (
295 limiting_factor varchar PRIMARY KEY 120 limiting_factor varchar PRIMARY KEY
296 ); 121 );
297 122
298 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL 123 CREATE TABLE measure_types (
299 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
300 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
301 CREATE TABLE gemma_fairway.bottlenecks (
302 bottleneck_id varchar PRIMARY KEY,
303 fk_g_fid isrs NOT NULL REFERENCES gauges,
304 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
305 objnam varchar,
306 nobjnm varchar,
307 stretch isrsrange NOT NULL,
308 area geometry(POLYGON, 4326) NOT NULL,
309 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
310 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
311 responsible_country char(2) NOT NULL REFERENCES countries,
312 revisiting_time smallint NOT NULL,
313 limiting varchar NOT NULL REFERENCES limiting_factors,
314 -- surtyp varchar NOT NULL REFERENCES survey_types,
315 -- XXX: Also an attribut of sounding result?
316 -- coverage varchar REFERENCES coverage_types,
317 -- XXX: Also an attribut of sounding result?
318 -- CHECK allowed combinations of surtyp and coverage or
319 -- different model approach?
320 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
321 -- XXX: Also an attribut of sounding result?
322 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
323 source_organization varchar NOT NULL,
324 -- additional_data xml -- Currently not relevant for GEMMA
325 staging_done boolean NOT NULL DEFAULT false
326 );
327 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
328 FOR EACH ROW EXECUTE PROCEDURE update_date_info();
329
330 CREATE TABLE gemma_fairway.bottlenecks_riverbed_materials (
331 bottleneck_id varchar REFERENCES bottlenecks,
332 riverbed varchar REFERENCES riverbed_materials,
333 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
334 PRIMARY KEY (bottleneck_id, riverbed)
335 );
336
337 CREATE TABLE gemma_fairway.sounding_results (
338 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
339 date_info date NOT NULL,
340 PRIMARY KEY (bottleneck_id, date_info),
341 area geometry(POLYGON, 4326) NOT NULL,
342 surtyp varchar NOT NULL REFERENCES survey_types,
343 coverage varchar REFERENCES coverage_types,
344 depth_reference char(3) NOT NULL REFERENCES depth_references,
345 sounding_data raster NOT NULL,
346 staging_done boolean NOT NULL DEFAULT false
347 );
348
349 --
350 -- Fairway availability
351 --
352 CREATE TABLE gemma_fairway.measure_types (
353 measure_type varchar PRIMARY KEY 124 measure_type varchar PRIMARY KEY
354 ); 125 );
355 126
356 CREATE TABLE gemma_fairway.fairway_availability ( 127
357 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 128 -- Namespace for user management related data
358 position_code char(2) REFERENCES position_codes, 129 CREATE SCHEMA users
359 bottleneck_id varchar NOT NULL REFERENCES bottlenecks, 130 CREATE TABLE responsibility_areas (
360 surdat date NOT NULL, 131 country char(2) PRIMARY KEY REFERENCES countries,
361 UNIQUE (bottleneck_id, surdat), 132 area geometry(MULTIPOLYGON, 4326)
362 -- additional_data xml -- Currently not relevant for GEMMA 133 --XXX: Should be geography (elsewhere too)
363 critical boolean, 134 )
364 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, 135
365 source_organization varchar NOT NULL 136 CREATE TABLE user_profiles (
366 ); 137 username varchar PRIMARY KEY,-- TODO: check it's in pg_roles by trigger
367 CREATE TRIGGER fairway_availability_date_info 138 country char(2) NOT NULL REFERENCES countries,
368 BEFORE UPDATE ON fairway_availability 139 map_extent box2d,
369 FOR EACH ROW EXECUTE PROCEDURE update_date_info(); 140 email_adress varchar NOT NULL UNIQUE
370 141 )
371 CREATE TABLE gemma_fairway.fa_reference_values ( 142
372 fairway_availability_id int NOT NULL REFERENCES fairway_availability, 143 CREATE TABLE templates (
373 level_of_service smallint NOT NULL REFERENCES levels_of_service, 144 template_name varchar PRIMARY KEY,
374 PRIMARY KEY (fairway_availability_id, level_of_service), 145 template_data bytea NOT NULL,
375 fairway_depth smallint, 146 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
376 fairway_width smallint, 147 )
377 fairway_radius int, 148 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
378 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius) IS NOT NULL), 149 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
379 shallowest_spot geometry(POINT, 4326) 150
380 ); 151 CREATE TABLE user_templates (
381 152 username varchar NOT NULL REFERENCES user_profiles ON DELETE CASCADE,
382 CREATE TABLE gemma_fairway.bottleneck_pdfs ( 153 template_name varchar NOT NULL REFERENCES templates ON DELETE CASCADE,
383 fairway_availability_id int NOT NULL REFERENCES fairway_availability, 154 PRIMARY KEY (username, template_name)
384 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL 155 )
385 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow? 156 ;
386 PRIMARY KEY (fairway_availability_id, profile_pdf_url), 157
387 pdf_generation_date timestamp with time zone NOT NULL, 158 -- Namespace for waterway data that can change in a running system
388 source_organization varchar NOT NULL 159 CREATE SCHEMA waterway
389 ); 160
390 161 -- Eventually obsolete.
391 CREATE TABLE gemma_fairway.effective_fairway_availability ( 162 -- See https://roundup-intern.intevation.de/gemma/issue5
392 fairway_availability_id int NOT NULL REFERENCES fairway_availability, 163 -- CREATE TABLE rwdrs (
393 measure_date timestamp with time zone NOT NULL, 164 -- tretch isrsrange PRIMARY KEY,
394 level_of_service smallint NOT NULL REFERENCES levels_of_service, 165 -- -- https://www.postgresql.org/docs/10/static/sql-createindex.html:
395 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service), 166 -- -- Only B-tree supports UNIQUE indexes, but we need the GIST index
396 available_depth_value smallint, 167 -- -- below anyhow.
397 available_width_value smallint, 168 -- -- Is it a good idea to build B-tree indexes on relatively large
398 water_level_value smallint, 169 -- -- composites of string values or should we use inter PKs?
399 CHECK(COALESCE(available_depth_value, available_width_value, 170 -- -- => In case the index is used and cache space becomes a limiting
400 water_level_value) IS NOT NULL), 171 -- -- factor, this might be an issue.
401 measure_type varchar NOT NULL REFERENCES measure_types, 172 -- rwdr double precision NOT NULL,
402 source_organization varchar NOT NULL, 173 -- EXCLUDE USING GIST (stretch WITH &&)
403 forecast_generation_time timestamp with time zone, 174 --)
404 CHECK(measure_type <> 'forecasted' 175
405 OR forecast_generation_time IS NOT NULL), 176 CREATE TABLE waterway_area (
406 value_lifetime timestamp with time zone, 177 area geometry(POLYGON, 4326) PRIMARY KEY,
407 CHECK(measure_type = 'minimum guaranteed' OR value_lifetime IS NOT NULL) 178 catccl smallint REFERENCES catccls,
408 ); 179 dirimp smallint REFERENCES dirimps
180 )
181
182 CREATE TABLE gauges (
183 location isrs PRIMARY KEY,
184 function_code varchar(10) NOT NULL, -- XXX: What is this really for?
185 objname varchar NOT NULL,
186 is_left boolean, -- XXX: Or reference position_codes?
187 geom geometry(POINT, 4326) NOT NULL,
188 applicability isrsrange,
189 validity tstzrange,-- XXX: Should ranges be NOT NULL? In DRC, only copy
190 -- pasted text from a more general specification is given
191 -- (a gauge is not a berth!)
192 -- TODO: Ranges need a joint exclusion constaint to prevent overlaps?
193 zero_point double precision NOT NULL,
194 geodref varchar(4) REFERENCES depth_references,
195 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
196 source_organization varchar NOT NULL
197 )
198 CREATE TRIGGER gauges_date_info BEFORE UPDATE ON gauges
199 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
200
201 CREATE TABLE gauges_reference_water_levels (
202 gauge_id isrs NOT NULL REFERENCES gauges,
203 reference_water_level varchar(20)
204 NOT NULL REFERENCES reference_water_levels,
205 PRIMARY KEY (gauge_id, reference_water_level),
206 value int NOT NULL
207 )
208
209 CREATE TABLE gauge_measurements (
210 fk_gauge_id isrs NOT NULL REFERENCES gauges,
211 measure_date timestamp with time zone NOT NULL,
212 PRIMARY KEY (fk_gauge_id, measure_date),
213 -- XXX: Is country_code really relevant for GEMMA or just NtS?
214 -- country_code char(2) NOT NULL REFERENCES countries,
215 -- TODO: add relations to stuff provided as enumerations
216 sender varchar NOT NULL, -- "from" attribute from DRC
217 language_code varchar NOT NULL REFERENCES language_codes,
218 date_issue timestamp with time zone NOT NULL,
219 -- reference_code varchar(4) NOT NULL REFERENCES depth_references,
220 -- XXX: Always ZPG?
221 water_level double precision NOT NULL,
222 predicted boolean NOT NULL,
223 is_waterlevel boolean NOT NULL,
224 -- XXX: "measure_code" if really only W or Q
225 -- XXX: Do we need "unit" attribute or can we normalise on import?
226 value_min double precision, -- XXX: NOT NULL if predicted?
227 value_max double precision, -- XXX: NOT NULL if predicted?
228 --- TODO: Add a double range type for checking?
229 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
230 source_organization varchar NOT NULL -- "originator"
231 )
232 CREATE TRIGGER gauge_measurements_date_info
233 BEFORE UPDATE ON gauge_measurements
234 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
235
236 CREATE TABLE waterway_axis (
237 wtwaxs geometry(LINESTRING, 4326) PRIMARY KEY,
238 -- TODO: Do we need to check data set quality (DRC 2.1.6)?
239 objnam varchar NOT NULL,
240 nobjnam varchar
241 )
242
243 -- This table allows linkage between 1D ISRS location codes and 2D space
244 -- e.g. for cutting bottleneck area out of waterway area based on virtual
245 -- distance marks along waterway axis (see SUC7).
246 CREATE TABLE distance_marks (
247 location_code isrs PRIMARY KEY,
248 geom geometry(POINT, 4326) NOT NULL,
249 distance_mark_function varchar(8)
250 NOT NULL REFERENCES distance_mark_functions,
251 -- XXX: attribute "function" in DRC 2.1.7 as well as CATDIS seem
252 -- to encode the same thing as the object code in ISRS location code.
253 position_code char(2) NOT NULL REFERENCES position_codes
254 )
255
256 CREATE TABLE sections_stretches (
257 id varchar PRIMARY KEY,
258 is_section boolean NOT NULL, -- maps 'function' from interface
259 stretch isrsrange,
260 objnam varchar NOT NULL,
261 nobjnam varchar,
262 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
263 source_organization varchar NOT NULL
264 )
265 CREATE TRIGGER sections_stretches_date_info
266 BEFORE UPDATE ON sections_stretches
267 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
268
269 CREATE TABLE waterway_profiles (
270 location isrs NOT NULL,
271 validity tstzrange,
272 EXCLUDE USING GIST (validity WITH &&),
273 PRIMARY KEY (location, validity),
274 lnwl smallint,
275 mwl smallint,
276 hnwl smallint,
277 fe30 smallint,
278 fe100 smallint,
279 -- XXX: further normalise using reference_water_levels?
280 CHECK(COALESCE(lnwl, mwl, hnwl, fe30, fe100) IS NULL
281 OR validity IS NOT NULL),
282 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
283 source_organization varchar NOT NULL
284 )
285 CREATE TRIGGER waterway_profiles_date_info
286 BEFORE UPDATE ON waterway_profiles
287 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
288
289 CREATE TABLE fairway_dimensions (
290 area geometry(POLYGON, 4326) PRIMARY KEY,
291 level_of_service smallint NOT NULL REFERENCES levels_of_service,
292 min_width smallint NOT NULL,
293 max_width smallint NOT NULL,
294 min_depth smallint NOT NULL,
295 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
296 source_organization varchar NOT NULL
297 )
298 CREATE TRIGGER fairway_dimensions_date_info
299 BEFORE UPDATE ON fairway_dimensions
300 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
301
302 --
303 -- Bottlenecks
304 --
305 -- XXX: Nullability differs between DRC (attributes marked "O") and WSDL
306 -- (minOccurs=0; nillable seems to be set arbitrarily as even bottleneck_id and
307 -- fk_g_fid (both mandatory, i.e. marked "M" in DRC) have nillable="true" in WSDL)
308 CREATE TABLE bottlenecks (
309 bottleneck_id varchar PRIMARY KEY,
310 fk_g_fid isrs NOT NULL REFERENCES gauges,
311 -- XXX: DRC references "ch. 3.1.1", which does not exist in document.
312 objnam varchar,
313 nobjnm varchar,
314 stretch isrsrange NOT NULL,
315 area geometry(POLYGON, 4326) NOT NULL,
316 rb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
317 lb char(2) NOT NULL REFERENCES countries, -- from rb_lb in interface
318 responsible_country char(2) NOT NULL REFERENCES countries,
319 revisiting_time smallint NOT NULL,
320 limiting varchar NOT NULL REFERENCES limiting_factors,
321 -- surtyp varchar NOT NULL REFERENCES survey_types,
322 -- XXX: Also an attribut of sounding result?
323 -- coverage varchar REFERENCES coverage_types,
324 -- XXX: Also an attribut of sounding result?
325 -- CHECK allowed combinations of surtyp and coverage or
326 -- different model approach?
327 -- depth_reference char(3) NOT NULL REFERENCES depth_references,
328 -- XXX: Also an attribut of sounding result?
329 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
330 source_organization varchar NOT NULL,
331 -- additional_data xml -- Currently not relevant for GEMMA
332 staging_done boolean NOT NULL DEFAULT false
333 )
334 CREATE TRIGGER bottleneck_date_info BEFORE UPDATE ON bottlenecks
335 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
336
337 CREATE TABLE bottlenecks_riverbed_materials (
338 bottleneck_id varchar REFERENCES bottlenecks,
339 riverbed varchar REFERENCES riverbed_materials,
340 -- XXX: should be 'natsur' according to IENC Encoding Guide M.4.3
341 PRIMARY KEY (bottleneck_id, riverbed)
342 )
343
344 CREATE TABLE sounding_results (
345 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
346 date_info date NOT NULL,
347 PRIMARY KEY (bottleneck_id, date_info),
348 area geometry(POLYGON, 4326) NOT NULL,
349 surtyp varchar NOT NULL REFERENCES survey_types,
350 coverage varchar REFERENCES coverage_types,
351 depth_reference char(3) NOT NULL REFERENCES depth_references,
352 sounding_data raster NOT NULL,
353 staging_done boolean NOT NULL DEFAULT false
354 )
355
356 --
357 -- Fairway availability
358 --
359 CREATE TABLE fairway_availability (
360 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
361 position_code char(2) REFERENCES position_codes,
362 bottleneck_id varchar NOT NULL REFERENCES bottlenecks,
363 surdat date NOT NULL,
364 UNIQUE (bottleneck_id, surdat),
365 -- additional_data xml -- Currently not relevant for GEMMA
366 critical boolean,
367 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
368 source_organization varchar NOT NULL
369 )
370 CREATE TRIGGER fairway_availability_date_info
371 BEFORE UPDATE ON fairway_availability
372 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
373
374 CREATE TABLE fa_reference_values (
375 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
376 level_of_service smallint NOT NULL REFERENCES levels_of_service,
377 PRIMARY KEY (fairway_availability_id, level_of_service),
378 fairway_depth smallint,
379 fairway_width smallint,
380 fairway_radius int,
381 CHECK(COALESCE(fairway_depth, fairway_width, fairway_radius)
382 IS NOT NULL),
383 shallowest_spot geometry(POINT, 4326)
384 )
385
386 CREATE TABLE bottleneck_pdfs (
387 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
388 profile_pdf_filename varchar NOT NULL, -- redundant to last part of URL
389 profile_pdf_url varchar NOT NULL, -- prohibit insecure URLs somehow?
390 PRIMARY KEY (fairway_availability_id, profile_pdf_url),
391 pdf_generation_date timestamp with time zone NOT NULL,
392 source_organization varchar NOT NULL
393 )
394
395 CREATE TABLE effective_fairway_availability (
396 fairway_availability_id int NOT NULL REFERENCES fairway_availability,
397 measure_date timestamp with time zone NOT NULL,
398 level_of_service smallint NOT NULL REFERENCES levels_of_service,
399 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service),
400 available_depth_value smallint,
401 available_width_value smallint,
402 water_level_value smallint,
403 CHECK(COALESCE(available_depth_value, available_width_value,
404 water_level_value) IS NOT NULL),
405 measure_type varchar NOT NULL REFERENCES measure_types,
406 source_organization varchar NOT NULL,
407 forecast_generation_time timestamp with time zone,
408 CHECK(measure_type <> 'forecasted'
409 OR forecast_generation_time IS NOT NULL),
410 value_lifetime timestamp with time zone,
411 CHECK(measure_type = 'minimum guaranteed'
412 OR value_lifetime IS NOT NULL)
413 )
414 ;
409 415
410 COMMIT; 416 COMMIT;