Mercurial > gemma

comparison schema/gemma.sql @ 4389:5e38667f740c stretches-for-responsibility

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use stretches as areas of responsibility. This is heavily based on a patch by Tom Gottfried (read: >90% of the work was done by Tom).
author Sascha Wilde <wilde@intevation.de>
date Thu, 12 Sep 2019 18:13:47 +0200
parents a7196b55c064
children ce884af9f42f
comparison
equal deleted inserted replaced
4371:4a5ed371011f 4389:5e38667f740c
2 -- without warranty, see README.md and license for details. 2 -- without warranty, see README.md and license for details.
3 3
4 -- SPDX-License-Identifier: AGPL-3.0-or-later 4 -- SPDX-License-Identifier: AGPL-3.0-or-later
5 -- License-Filename: LICENSES/AGPL-3.0.txt 5 -- License-Filename: LICENSES/AGPL-3.0.txt
6 6
7 -- Copyright (C) 2018,2019 by via donau 7 -- Copyright (C) 2018, 2019 by via donau
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH 8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
9 -- Software engineering by Intevation GmbH 9 -- Software engineering by Intevation GmbH
10 10
11 -- Author(s): 11 -- Author(s):
12 -- * Tom Gottfried <tom@intevation.de> 12 -- * Tom Gottfried <tom@intevation.de>
234 234
235 -- 235 --
236 -- GEMMA data 236 -- GEMMA data
237 -- 237 --
238 238
239 -- Namespace not to be accessed directly by any user
240 CREATE SCHEMA internal
241 -- Profile data are only accessible via the view users.list_users.
242 CREATE TABLE user_profiles (
243 username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
244 -- keep username length compatible with role identifier
245 map_extent box2d NOT NULL,
246 email_address varchar NOT NULL
247 )
248 -- Columns referencing user-visible schemas added below.
249 ;
250
251
252 -- Namespace to be accessed by sys_admin only
253 CREATE SCHEMA sys_admin
254 CREATE TABLE system_config (
255 config_key varchar PRIMARY KEY,
256 config_val varchar
257 )
258
259 CREATE TABLE password_reset_requests (
260 hash varchar(32) PRIMARY KEY,
261 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
262 username varchar NOT NULL UNIQUE
263 REFERENCES internal.user_profiles(username)
264 ON DELETE CASCADE ON UPDATE CASCADE
265 )
266
267 -- Tables with geo data to be published with GeoServer.
268 CREATE TABLE external_services (
269 local_name varchar PRIMARY KEY,
270 remote_url varchar NOT NULL,
271 is_wfs boolean NOT NULL DEFAULT TRUE
272 )
273
274 CREATE TABLE published_services (
275 name regclass PRIMARY KEY,
276 style xml CHECK(style IS DOCUMENT),
277 as_wms boolean NOT NULL DEFAULT TRUE,
278 as_wfs boolean NOT NULL DEFAULT TRUE
279 )
280 ;
281
282 -- 239 --
283 -- Look-up tables with data that are static in a running system 240 -- Look-up tables with data that are static in a running system
284 -- 241 --
285 CREATE TABLE language_codes ( 242 CREATE TABLE language_codes (
286 language_code varchar PRIMARY KEY 243 language_code varchar PRIMARY KEY
364 'map', 321 'map',
365 'diagram', 322 'diagram',
366 'report' 323 'report'
367 ); 324 );
368 325
326
327 -- Namespace not to be accessed directly by any user
328 CREATE SCHEMA internal
329 -- Profile data are only accessible via the view users.list_users.
330 CREATE TABLE user_profiles (
331 username varchar PRIMARY KEY CHECK(octet_length(username) <= 63),
332 -- keep username length compatible with role identifier
333 country char(2) NOT NULL REFERENCES countries,
334 map_extent box2d NOT NULL,
335 email_address varchar NOT NULL
336 )
337 -- Columns referencing user-visible schemas added below.
338 ;
339
340
341 -- Namespace to be accessed by sys_admin only
342 CREATE SCHEMA sys_admin
343 CREATE TABLE system_config (
344 config_key varchar PRIMARY KEY,
345 config_val varchar
346 )
347
348 CREATE TABLE password_reset_requests (
349 hash varchar(32) PRIMARY KEY,
350 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
351 username varchar NOT NULL UNIQUE
352 REFERENCES internal.user_profiles(username)
353 ON DELETE CASCADE ON UPDATE CASCADE
354 )
355
356 -- Tables with geo data to be published with GeoServer.
357 CREATE TABLE external_services (
358 local_name varchar PRIMARY KEY,
359 remote_url varchar NOT NULL,
360 is_wfs boolean NOT NULL DEFAULT TRUE
361 )
362
363 CREATE TABLE published_services (
364 name regclass PRIMARY KEY,
365 style xml CHECK(style IS DOCUMENT),
366 as_wms boolean NOT NULL DEFAULT TRUE,
367 as_wfs boolean NOT NULL DEFAULT TRUE
368 )
369 ;
370
371
369 -- Namespace for user management related data 372 -- Namespace for user management related data
370 CREATE SCHEMA users 373 CREATE SCHEMA users
371 CREATE TABLE responsibility_areas ( 374 CREATE TABLE stretches (
372 country char(2) PRIMARY KEY REFERENCES countries, 375 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
373 area geography(MULTIPOLYGON, 4326) 376 name varchar NOT NULL,
374 CHECK(ST_IsValid(CAST(area AS geometry))) 377 stretch isrsrange NOT NULL,
378 area geography(MULTIPOLYGON, 4326) NOT NULL
379 CHECK(ST_IsValid(CAST(area AS geometry))),
380 objnam varchar NOT NULL,
381 nobjnam varchar,
382 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
383 source_organization varchar NOT NULL,
384 staging_done boolean NOT NULL DEFAULT false,
385 UNIQUE(name, staging_done)
386 )
387 CREATE TRIGGER stretches_date_info
388 BEFORE UPDATE ON stretches
389 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
390
391 CREATE TABLE stretch_countries (
392 stretch_id int NOT NULL REFERENCES stretches(id)
393 ON DELETE CASCADE,
394 country char(2) NOT NULL REFERENCES countries,
395 PRIMARY KEY(stretch_id, country)
375 ) 396 )
376 397
377 CREATE TABLE templates ( 398 CREATE TABLE templates (
378 template_name varchar NOT NULL, 399 template_name varchar NOT NULL,
379 template_type template_types NOT NULL DEFAULT 'map'::template_types, 400 template_type template_types NOT NULL DEFAULT 'map'::template_types,
383 UNIQUE (template_name, template_type, country) 404 UNIQUE (template_name, template_type, country)
384 ) 405 )
385 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates 406 CREATE TRIGGER templates_date_info BEFORE UPDATE ON templates
386 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 407 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
387 ; 408 ;
388 ALTER TABLE internal.user_profiles ADD 409
389 country char(2) NOT NULL REFERENCES users.responsibility_areas;
390 410
391 -- Namespace for waterway data that can change in a running system 411 -- Namespace for waterway data that can change in a running system
392 CREATE SCHEMA waterway 412 CREATE SCHEMA waterway
393 413
394 -- Eventually obsolete. 414 -- Eventually obsolete.
543 pk_sequence VARCHAR(64), 563 pk_sequence VARCHAR(64),
544 unique (table_schema, table_name, pk_column), 564 unique (table_schema, table_name, pk_column),
545 check (pk_policy in ('sequence', 'assigned', 'autogenerated')) 565 check (pk_policy in ('sequence', 'assigned', 'autogenerated'))
546 ) 566 )
547 567
548 CREATE TABLE stretches (
549 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
550 name varchar NOT NULL,
551 stretch isrsrange NOT NULL,
552 area geography(MULTIPOLYGON, 4326) NOT NULL
553 CHECK(ST_IsValid(CAST(area AS geometry))),
554 objnam varchar NOT NULL,
555 nobjnam varchar,
556 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
557 source_organization varchar NOT NULL,
558 staging_done boolean NOT NULL DEFAULT false,
559 UNIQUE(name, staging_done)
560 )
561 CREATE TRIGGER stretches_date_info
562 BEFORE UPDATE ON stretches
563 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
564
565 CREATE TABLE stretch_countries (
566 stretches_id int NOT NULL REFERENCES stretches(id)
567 ON DELETE CASCADE,
568 country_code char(2) NOT NULL REFERENCES countries(country_code),
569 UNIQUE(stretches_id, country_code)
570 )
571
572 -- Like stretches without the countries 568 -- Like stretches without the countries
573 CREATE TABLE sections ( 569 CREATE TABLE sections (
574 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 570 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
575 name varchar NOT NULL, 571 name varchar NOT NULL,
576 section isrsrange NOT NULL, 572 section isrsrange NOT NULL,
582 source_organization varchar NOT NULL, 578 source_organization varchar NOT NULL,
583 staging_done boolean NOT NULL DEFAULT false, 579 staging_done boolean NOT NULL DEFAULT false,
584 UNIQUE(name, staging_done) 580 UNIQUE(name, staging_done)
585 ) 581 )
586 CREATE TRIGGER sections_date_info 582 CREATE TRIGGER sections_date_info
587 BEFORE UPDATE ON stretches 583 BEFORE UPDATE ON sections
588 FOR EACH ROW EXECUTE PROCEDURE update_date_info() 584 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
589 585
590 CREATE TABLE waterway_profiles ( 586 CREATE TABLE waterway_profiles (
591 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, 587 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
592 location isrs NOT NULL REFERENCES distance_marks_virtual, 588 location isrs NOT NULL REFERENCES distance_marks_virtual,