Mercurial > gemma

annotate schema/gemma.sql @ 4723:baabc2b2f094

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Avoid creating user profiles without matching role The INSTEAD OF triggers on users.list_users did that already, but profile data coming e.g. via restoring a dump had been added also if there was no matching database role in the cluster. This also unifies the errors occuring on creation of users with existing role names that differed between roles with and without profile before. Note this is no referential integrity. A dropped role still leaves an orphaned profile behind.
author Tom Gottfried <tom@intevation.de>
date Thu, 17 Oct 2019 18:56:59 +0200
parents 462d8f71da62
children 2440d2f86f4e
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
2 -- without warranty, see README.md and license for details.
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
3
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
6
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
7 -- Copyright (C) 2018, 2019 by via donau
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
9 -- Software engineering by Intevation GmbH
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
10
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
11 -- Author(s):
1301
2304778c4432 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1298
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
1336
f65d1767452c add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1301
diff changeset
13 -- * Sascha Teichmann <sascha.teichmann@intevation.de>
2172
7e2c77ccc02f Removed trigger to update date_info on gauge_measurements.
Sascha Wilde <wilde@intevation.de>
parents: 2170
diff changeset
14 -- * Sascha Wilde <sascha.wilde@intevation.de>
1298
6590208e3ee1 add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents: 1288
diff changeset
15
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 BEGIN;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
18 --
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
19 -- Extensions
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
20 --
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 CREATE EXTENSION postgis;
2083
6deafd6f7f86 Fix exclusion constraint for waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2076
diff changeset
22 -- needed for multi-column GiST indexes with otherwise unsupported types:
6deafd6f7f86 Fix exclusion constraint for waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2076
diff changeset
23 CREATE EXTENSION btree_gist;
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
25 --
4602
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
26 -- Functions to be used in CHECK constraints
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
27 --
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
28
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
29 -- Check if a given string can be used as a FROM item in an SQL statement
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
30 CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
31 AS $$
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
32 BEGIN
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
33 EXECUTE format('SELECT * FROM (%s) AS test', stmt);
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
34 RETURN true;
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
35 EXCEPTION
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
36 WHEN OTHERS THEN
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
37 RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE;
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
38 RETURN false;
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
39 END
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
40 $$
4603
d24e951206ca Do not try to execute NULL as a subquery
Tom Gottfried <tom@intevation.de>
parents: 4602
diff changeset
41 LANGUAGE plpgsql
d24e951206ca Do not try to execute NULL as a subquery
Tom Gottfried <tom@intevation.de>
parents: 4602
diff changeset
42 STRICT;
4602
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
43
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
44
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4236
diff changeset
45 --
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
46 -- Trigger functions
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
47 --
96
d036e1bd5f00 Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents: 95
diff changeset
48 -- 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
49 -- historicisation?
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 CREATE FUNCTION update_date_info() RETURNS trigger
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 LANGUAGE plpgsql
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 AS $$
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 BEGIN
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 NEW.date_info = CURRENT_TIMESTAMP;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55 RETURN NEW;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 END;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 $$;
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
59 -- Trigger function to be used as a constraint trigger to enforce uniqueness
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
60 -- of geometries in the column with its name given as an argument to the
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
61 -- trigger function
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
62 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
63 $$
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
64 DECLARE
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
65 new_geom geometry;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
66 has_equal boolean;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
67 BEGIN
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
68 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
69 INTO new_geom
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
70 USING NEW;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
71 EXECUTE format(
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
72 'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I '
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
73 'WHERE id <> $2',
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
74 TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME)
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
75 INTO has_equal
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
76 USING new_geom, NEW.id;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
77 IF has_equal THEN
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
78 RAISE EXCEPTION
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
79 'new row for relation "%" violates constraint trigger "%"',
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
80 TG_TABLE_NAME, TG_NAME
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
81 USING
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
82 DETAIL = format('Failing row contains geometry in %s',
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
83 Box2D(new_geom)),
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
84 ERRCODE = 23505,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
85 SCHEMA = TG_TABLE_SCHEMA,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
86 TABLE = TG_TABLE_NAME,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
87 COLUMN = TG_ARGV[0],
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
88 CONSTRAINT = TG_NAME;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
89 END IF;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
90 RETURN NEW;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
91 END;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
92 $$
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
93 LANGUAGE plpgsql;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
94
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
95 -- Trigger function to be used as a constraint trigger to enforce
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
96 -- existance of a referenced gauge with intersecting validity. The
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
97 -- columns with the referenced gauge isrs code an the validity are
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
98 -- given as arguments to the trigger function.
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
99 CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
100 $$
3666
db87f34805fb Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents: 3665
diff changeset
101 DECLARE
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
102 -- FIXME: I'm using text for the isrs code and cast it on demand.
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
103 -- If someone is able to get it to work with isrs or isrs_base as
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
104 -- type, feel free to show me how its done... ;-) [sw]
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
105 referenced_gauge text;
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
106 new_validity tstzrange;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
107 BEGIN
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
108 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
109 INTO referenced_gauge
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
110 USING NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
111 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
112 INTO new_validity
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
113 USING NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
114 IF EXISTS ( SELECT * FROM waterway.gauges
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
115 WHERE location = referenced_gauge::isrs
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
116 AND validity && new_validity )
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
117 THEN
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
118 RETURN NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
119 ELSE
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
120 RAISE EXCEPTION
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
121 'new row for relation "%" violates constraint trigger "%"',
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
122 TG_TABLE_NAME, TG_NAME
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
123 USING
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
124 DETAIL = format('No matching gauge %s found.',
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
125 isrs_AsText(referenced_gauge::isrs)),
4132
ec8438712447 Enable better error handling for referenced gauges/bottlenecks constraints.
Sascha Wilde <wilde@intevation.de>
parents: 4127
diff changeset
126 ERRCODE = 23503,
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
127 SCHEMA = TG_TABLE_SCHEMA,
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
128 TABLE = TG_TABLE_NAME,
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
129 COLUMN = TG_ARGV[0],
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
130 CONSTRAINT = TG_NAME;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
131 END IF;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
132 END;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
133 $$
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
134 LANGUAGE plpgsql;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
135
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
136 -- The same for objects with a timestamp instead of a validity range.
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
137 CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
138 $$
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
139 DECLARE
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
140 -- FIXME: I'm using text for the isrs code and cast it on demand.
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
141 -- If someone is able to get it to work with isrs or isrs_base as
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
142 -- type, feel free to show me how its done... ;-) [sw]
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
143 referenced_gauge text;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
144 new_tstz timestamptz;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
145 BEGIN
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
146 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
147 INTO referenced_gauge
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
148 USING NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
149 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
150 INTO new_tstz
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
151 USING NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
152 IF EXISTS ( SELECT * FROM waterway.gauges
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
153 WHERE location = referenced_gauge::isrs
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
154 AND validity @> new_tstz )
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
155 THEN
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
156 RETURN NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
157 ELSE
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
158 RAISE EXCEPTION
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
159 'new row for relation "%" violates constraint trigger "%"',
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
160 TG_TABLE_NAME, TG_NAME
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
161 USING
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
162 DETAIL = format('No matching gauge %s found.',
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
163 isrs_AsText(referenced_gauge::isrs)),
4132
ec8438712447 Enable better error handling for referenced gauges/bottlenecks constraints.
Sascha Wilde <wilde@intevation.de>
parents: 4127
diff changeset
164 ERRCODE = 23503,
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
165 SCHEMA = TG_TABLE_SCHEMA,
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
166 TABLE = TG_TABLE_NAME,
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
167 COLUMN = TG_ARGV[0],
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
168 CONSTRAINT = TG_NAME;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
169 END IF;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
170 END;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
171 $$
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
172 LANGUAGE plpgsql;
3666
db87f34805fb Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents: 3665
diff changeset
173
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
174 -- Trigger function to be used as a constraint trigger to enforce
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
175 -- existance of a referenced bottleneck with validity at a given time.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
176 -- The columns with the referenced bottleneck id and the timestamp are
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
177 -- given as arguments to the trigger function.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
178 CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
179 $$
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
180 DECLARE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
181 referenced_bottleneck_id text;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
182 new_tstz timestamptz;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
183 BEGIN
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
184 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
185 INTO referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
186 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
187 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
188 INTO new_tstz
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
189 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
190 IF EXISTS ( SELECT * FROM waterway.bottlenecks
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
191 WHERE bottleneck_id = referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
192 AND validity @> new_tstz )
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
193 THEN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
194 RETURN NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
195 ELSE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
196 RAISE EXCEPTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
197 'new row for relation "%" violates constraint trigger "%"',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
198 TG_TABLE_NAME, TG_NAME
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
199 USING
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
200 DETAIL = format('No matching bottleneck %s for %s found.',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
201 referenced_bottleneck_id, new_tstz),
4132
ec8438712447 Enable better error handling for referenced gauges/bottlenecks constraints.
Sascha Wilde <wilde@intevation.de>
parents: 4127
diff changeset
202 ERRCODE = 23503,
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
203 SCHEMA = TG_TABLE_SCHEMA,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
204 TABLE = TG_TABLE_NAME,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
205 COLUMN = TG_ARGV[0],
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
206 CONSTRAINT = TG_NAME;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
207 END IF;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
208 END;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
209 $$
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
210 LANGUAGE plpgsql;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
211
3746
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
212 -- Constraint trigger: sounding Results must intersect with the area
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
213 -- of the bottleneck they belong to. The "xx" at the beginning of the
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
214 -- name is to ensure, it is fired last after other triggers.
3746
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
215 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
216 LANGUAGE plpgsql
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
217 AS $$
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
218 BEGIN
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
219 IF NOT st_intersects((SELECT area
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
220 FROM waterway.bottlenecks
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
221 WHERE bottleneck_id = NEW.bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
222 AND validity @> NEW.date_info::timestamptz),
3746
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
223 NEW.area)
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
224 THEN
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
225 RAISE EXCEPTION
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
226 'new row for relation "%" violates constraint trigger "%"',
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
227 TG_TABLE_NAME, TG_NAME
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
228 USING
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
229 DETAIL = 'Failing row area has no intersection with bottleneck.',
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
230 ERRCODE = 23514,
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
231 SCHEMA = TG_TABLE_SCHEMA,
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
232 TABLE = TG_TABLE_NAME,
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
233 CONSTRAINT = TG_NAME;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
234 END IF;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
235 RETURN NEW;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
236 END;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
237 $$;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
238
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
239
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
240 --
3978
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
241 -- GEMA meta data
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
242 --
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
243 CREATE TABLE gemma_schema_version (
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
244 version int PRIMARY KEY,
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
245 update_date timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
246 );
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
247
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
248 CREATE OR REPLACE FUNCTION get_schema_version() RETURNS int
3996
a2921151b193 Schema: Use SQL instead of PLPGSQL for get_schema_version().
Sascha Wilde <wilde@intevation.de>
parents: 3978
diff changeset
249 LANGUAGE sql
3978
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
250 AS $$
4173
d3fb2f37380b Schema qualify tables in function body
Tom Gottfried <tom@intevation.de>
parents: 4132
diff changeset
251 SELECT max(version) FROM public.gemma_schema_version;
3978
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
252 $$;
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
253
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
254
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
255 --
115
d349db18bece s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 113
diff changeset
256 -- GEMMA data
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
257 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
258
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
259 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
260 -- 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
261 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
262 CREATE TABLE language_codes (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
263 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
264 );
84
d905022a48e9 More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents: 83
diff changeset
265
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
266 CREATE TABLE catccls (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
267 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
268 -- 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
269 -- (see page 328 of edition 2.3)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
270 );
927
48f70782400d Add categories of CEMT class and directions of impact
Tom Gottfried <tom@intevation.de>
parents: 919
diff changeset
271 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
272
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
273 CREATE TABLE dirimps (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
274 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
275 -- 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
276 -- (see page 381 of edition 2.3)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
277 );
927
48f70782400d Add categories of CEMT class and directions of impact
Tom Gottfried <tom@intevation.de>
parents: 919
diff changeset
278 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
279 -- 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
280
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
281 CREATE TABLE depth_references (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
282 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
283 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
284 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
285 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
286 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
287 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side.
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
288 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
289 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
290 );
93
765906789840 Add gauge attributes.
Tom Gottfried <tom@intevation.de>
parents: 92
diff changeset
291
934
e6220a19f284 Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents: 930
diff changeset
292 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents: 930
diff changeset
293 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents: 930
diff changeset
294 -- 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
295 -- (see page 171 of edition 2.3)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
296 );
934
e6220a19f284 Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents: 930
diff changeset
297 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
298
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
299 CREATE TABLE position_codes (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
300 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
301 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
302 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
303 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
304 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here.
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
305 -- Clarify!
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
306 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
307 );
87
c46fb3f1faeb Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents: 86
diff changeset
308
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
309 CREATE TABLE levels_of_service (
1662
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
310 level_of_service smallint PRIMARY KEY,
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
311 name varchar(4)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
312 );
1662
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
313 INSERT INTO levels_of_service (
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
314 level_of_service,
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
315 name
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
316 ) VALUES (1, 'LOS1'), (2, 'LOS2'), (3, 'LOS3');
89
a36bfec5edd3 Move more tables to waterway schema to tweak diagrams.
Tom Gottfried <tom@intevation.de>
parents: 88
diff changeset
317
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
318 CREATE TABLE riverbed_materials (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
319 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
320 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
321 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
322 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
323
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
324 CREATE TABLE survey_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
325 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
326 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
327
4628
28999c7c0c18 Fill single and multi into survey type table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4623
diff changeset
328 INSERT INTO survey_types (survey_type) VALUES ('single'), ('multi');
28999c7c0c18 Fill single and multi into survey type table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4623
diff changeset
329
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
330 CREATE TABLE coverage_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
331 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
332 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
333
577
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
334 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
335 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
336 );
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
337
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
338 CREATE TABLE measure_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
339 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
340 );
58
30cb2f87c268 Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 56
diff changeset
341
3189
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3176
diff changeset
342 CREATE TYPE template_types AS ENUM (
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3176
diff changeset
343 'map',
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3176
diff changeset
344 'diagram',
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3176
diff changeset
345 'report'
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3176
diff changeset
346 );
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
347
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
348
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
349 -- Namespace not to be accessed directly by any user
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
350 CREATE SCHEMA internal
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
351 -- Profile data are only accessible via the view users.list_users.
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
352 CREATE TABLE user_profiles (
4723
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4722
diff changeset
353 username varchar PRIMARY KEY
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4722
diff changeset
354 CHECK(octet_length(username) <= 63)
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4722
diff changeset
355 CHECK(to_regrole(quote_ident(username)) IS NOT NULL),
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
356 -- keep username length compatible with role identifier
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
357 country char(2) NOT NULL REFERENCES countries,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
358 map_extent box2d NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
359 email_address varchar NOT NULL
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
360 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
361 ;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
362
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
363
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
364 -- Namespace to be accessed by sys_admin only
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
365 CREATE SCHEMA sys_admin
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
366 CREATE TABLE system_config (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
367 config_key varchar PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
368 config_val varchar
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
369 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
370
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
371 CREATE TABLE password_reset_requests (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
372 hash varchar(32) PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
373 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
374 username varchar NOT NULL UNIQUE
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
375 REFERENCES internal.user_profiles(username)
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
376 ON DELETE CASCADE ON UPDATE CASCADE
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
377 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
378
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
379 -- Tables with geo data to be published with GeoServer.
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
380 CREATE TABLE external_services (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
381 local_name varchar PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
382 remote_url varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
383 is_wfs boolean NOT NULL DEFAULT TRUE
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
384 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
385
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
386 CREATE TABLE published_services (
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
387 schema varchar CHECK(to_regnamespace(schema) IS NOT NULL),
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
388 name varchar,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
389 PRIMARY KEY (schema, name),
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
390 -- SQL statement used for an SQL view in GeoServer:
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
391 view_def text CHECK (is_valid_from_item(view_def)),
4673
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4648
diff changeset
392 -- Column in output of SQL statement to be used as primary key:
443867b548b5 Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents: 4648
diff changeset
393 key_column varchar,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
394 -- SRID to be used with SQL view:
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
395 srid int REFERENCES spatial_ref_sys,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
396 -- SLD style document:
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
397 style xml CHECK(style IS DOCUMENT),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
398 as_wms boolean NOT NULL DEFAULT TRUE,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
399 as_wfs boolean NOT NULL DEFAULT TRUE,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
400 -- Either give a valid relation or a SQL statement:
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
401 CHECK (to_regclass(schema || '.' || name) IS NOT NULL
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
402 OR view_def IS NOT NULL)
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
403 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
404 ;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
405
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
406
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
407 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
408 CREATE SCHEMA users
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
409 CREATE TABLE stretches (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
410 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4718
92640ae5be07 Backed out changeset 9279fdb7a422
Tom Gottfried <tom@intevation.de>
parents: 4717
diff changeset
411 name varchar NOT NULL,
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
412 stretch isrsrange NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
413 area geography(MULTIPOLYGON, 4326) NOT NULL
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
414 CHECK(ST_IsValid(CAST(area AS geometry))),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
415 objnam varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
416 nobjnam varchar,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
417 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
418 source_organization varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
419 staging_done boolean NOT NULL DEFAULT false,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
420 UNIQUE(name, staging_done)
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
421 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
422 CREATE TRIGGER stretches_date_info
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
423 BEFORE UPDATE ON stretches
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
424 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
425
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
426 CREATE TABLE stretch_countries (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
427 stretch_id int NOT NULL REFERENCES stretches(id)
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
428 ON DELETE CASCADE,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
429 country char(2) NOT NULL REFERENCES countries,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
430 PRIMARY KEY(stretch_id, country)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
431 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
432
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
433 CREATE TABLE templates (
2367
0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2341
diff changeset
434 template_name varchar NOT NULL,
3189
6f8fb2053881 Schema: Added type column for users.templates. TODO: Add backend code to filter by it.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3176
diff changeset
435 template_type template_types NOT NULL DEFAULT 'map'::template_types,
2267
37ae1bee3e4a Ajjusted RLS for user templates.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2230
diff changeset
436 country char(2) REFERENCES countries,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
437 template_data bytea NOT NULL,
2367
0aedae39726e Print templates: Removed primary key from templates table and add a unique constraint to template name and country instead to make possible that different countries can use templates with same name.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2341
diff changeset
438 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
3217
4c254651d80b Added template types "map", "diagram", "report".
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3189
diff changeset
439 UNIQUE (template_name, template_type, country)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
440 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
441 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
442 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
443 ;
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
444
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
445
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
446 -- 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
447 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
448
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
449 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
450 -- 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
451 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
452 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
453 -- -- 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
454 -- -- 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
455 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
456 -- -- 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
457 -- -- 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
458 -- -- => 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
459 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
460 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
461 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
462 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
463
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
464 CREATE TABLE waterway_area (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
465 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
466 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
467 CHECK(ST_IsValid(CAST(area AS geometry))),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
468 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
469 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
470 )
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
471 CREATE CONSTRAINT TRIGGER waterway_area_area_unique
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
472 AFTER INSERT OR UPDATE OF area ON waterway_area
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
473 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('area')
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
474
56
f378959820be Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 55
diff changeset
475
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
476 CREATE TABLE gauges (
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
477 location isrs CHECK(
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 577
diff changeset
478 (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
479 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
480 objname varchar NOT NULL,
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
481 geom geography(POINT, 4326) NOT NULL,
1826
d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1825
diff changeset
482 applicability_from_km int8,
d4e2637eed58 Schema: Adjust gauges once again to be in sync with RIS index.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1825
diff changeset
483 applicability_to_km int8,
3648
0ec5c8ec1e44 Avoid empty validity time ranges
Tom Gottfried <tom@intevation.de>
parents: 3647
diff changeset
484 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
485 zero_point double precision NOT NULL,
2369
89a3096e1988 Schema: Dropped wrong constraints in gauges.geodref.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2367
diff changeset
486 geodref varchar,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
487 date_info timestamp with time zone NOT NULL,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
488 source_organization varchar,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
489 lastupdate timestamp with time zone NOT NULL,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
490 -- entry removed from external data source (RIS-Index)/historicised:
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
491 erased boolean NOT NULL DEFAULT false,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
492 PRIMARY KEY (location, validity),
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
493 EXCLUDE USING GiST (isrs_astext(location) WITH =, validity WITH &&)
3402
c04b1409a596 Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents: 3389
diff changeset
494 DEFERRABLE INITIALLY DEFERRED
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
495 )
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
496 -- Allow only one non-erased entry per location
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
497 CREATE UNIQUE INDEX gauges_erased_unique_constraint
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
498 ON gauges (location)
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
499 WHERE NOT erased
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
500
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
501 CREATE TABLE gauges_reference_water_levels (
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
502 location isrs NOT NULL,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
503 validity tstzrange NOT NULL,
3402
c04b1409a596 Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents: 3389
diff changeset
504 FOREIGN KEY (location, validity) REFERENCES gauges ON UPDATE CASCADE,
2318
06c4e57435f1 Warn on import of unknown reference level codes
Tom Gottfried <tom@intevation.de>
parents: 2317
diff changeset
505 -- Omit foreign key constraint to be able to store not NtS-compliant
06c4e57435f1 Warn on import of unknown reference level codes
Tom Gottfried <tom@intevation.de>
parents: 2317
diff changeset
506 -- names, too:
2317
8a8680e70d2e Cleanup schema for reference water levels
Tom Gottfried <tom@intevation.de>
parents: 2300
diff changeset
507 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
508 PRIMARY KEY (location, validity, depth_reference),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
509 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
510 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
511
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
512 CREATE TABLE gauge_measurements (
1636
37ee25bc2bbe Updated database schema for gauge meaurement imports.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1583
diff changeset
513 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
514 location isrs NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
515 measure_date timestamp with time zone NOT NULL,
1636
37ee25bc2bbe Updated database schema for gauge meaurement imports.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1583
diff changeset
516 country_code char(2) NOT NULL REFERENCES countries,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
517 sender varchar NOT NULL, -- "from" element from NtS response
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
518 language_code varchar NOT NULL REFERENCES language_codes,
3389
45a629a3a8b8 Fix constraints on relationship between gauges and measurements/predictions
Tom Gottfried <tom@intevation.de>
parents: 3387
diff changeset
519 date_issue timestamp with time zone NOT NULL,
1775
fcb0106ec510 Gauge measurement import: Added reference_code column.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1759
diff changeset
520 reference_code varchar(4) NOT NULL REFERENCES depth_references,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
521 water_level double precision NOT NULL,
3387
d9eda49a52f4 Remove obsolete DEFAULT values
Tom Gottfried <tom@intevation.de>
parents: 3307
diff changeset
522 date_info timestamp with time zone NOT NULL,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
523 source_organization varchar NOT NULL, -- "originator" from NtS response
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
524 staging_done boolean NOT NULL DEFAULT false,
4016
9ab7e1056360 Optimize index setup a bit
Tom Gottfried <tom@intevation.de>
parents: 4006
diff changeset
525 UNIQUE (measure_date, location, staging_done)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
526 )
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
527 CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge
4068
76482935b6e5 Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 4067
diff changeset
528 AFTER INSERT OR UPDATE OF location ON gauge_measurements
76482935b6e5 Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 4067
diff changeset
529 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date')
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
530
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
531 CREATE TABLE gauge_predictions (
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
532 location isrs NOT NULL,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
533 measure_date timestamp with time zone NOT NULL,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
534 country_code char(2) NOT NULL REFERENCES countries,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
535 sender varchar NOT NULL, -- "from" element from NtS response
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
536 language_code varchar NOT NULL REFERENCES language_codes,
3389
45a629a3a8b8 Fix constraints on relationship between gauges and measurements/predictions
Tom Gottfried <tom@intevation.de>
parents: 3387
diff changeset
537 date_issue timestamp with time zone NOT NULL,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
538 reference_code varchar(4) NOT NULL REFERENCES depth_references,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
539 water_level double precision NOT NULL,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
540 conf_interval numrange
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
541 CHECK (conf_interval @> CAST(water_level AS numeric)),
3387
d9eda49a52f4 Remove obsolete DEFAULT values
Tom Gottfried <tom@intevation.de>
parents: 3307
diff changeset
542 date_info timestamp with time zone NOT NULL,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
543 source_organization varchar NOT NULL, -- "originator" from NtS response
4016
9ab7e1056360 Optimize index setup a bit
Tom Gottfried <tom@intevation.de>
parents: 4006
diff changeset
544 PRIMARY KEY (measure_date, location, date_issue)
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
545 )
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
546 CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge
4068
76482935b6e5 Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 4067
diff changeset
547 AFTER INSERT OR UPDATE OF location ON gauge_predictions
76482935b6e5 Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 4067
diff changeset
548 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date')
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
549
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
550 CREATE TABLE waterway_axis (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
551 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
552 wtwaxs geography(LINESTRING, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
553 CHECK(ST_IsSimple(CAST(wtwaxs AS geometry))),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
554 -- 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
555 objnam varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
556 nobjnam varchar
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
557 )
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
558 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
559 AFTER INSERT OR UPDATE OF wtwaxs ON waterway_axis
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
560 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs')
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
561
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
562 -- 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
563 -- 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
564 -- 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
565 CREATE TABLE distance_marks_virtual (
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
566 location_code isrs PRIMARY KEY,
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
567 geom geography(POINT, 4326) NOT NULL,
3778
1b11ff97c1d1 Add wwname to import of virtual distance marks.
Sascha Wilde <wilde@intevation.de>
parents: 3747
diff changeset
568 related_enc varchar(12) NOT NULL,
1b11ff97c1d1 Add wwname to import of virtual distance marks.
Sascha Wilde <wilde@intevation.de>
parents: 3747
diff changeset
569 wwname varchar
948
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
570 )
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
571
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
572 CREATE TABLE distance_marks (
1861
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
573 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
574 country char(2) REFERENCES countries,
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
575 hectom int,
948
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
576 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
577 -- include location in primary key, because we have no fairway code:
1861
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
578 catdis smallint REFERENCES catdis,
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
579 position_code char(2) REFERENCES position_codes,
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
580 related_enc varchar(12)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
581 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
582
3171
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
583 -- Like stretches without the countries
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
584 CREATE TABLE sections (
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
585 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4718
92640ae5be07 Backed out changeset 9279fdb7a422
Tom Gottfried <tom@intevation.de>
parents: 4717
diff changeset
586 name varchar NOT NULL,
3307
b90b17d0b5a9 added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents: 3302
diff changeset
587 section isrsrange NOT NULL,
3171
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
588 area geography(MULTIPOLYGON, 4326) NOT NULL
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
589 CHECK(ST_IsValid(CAST(area AS geometry))),
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
590 objnam varchar NOT NULL,
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
591 nobjnam varchar,
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
592 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
593 source_organization varchar NOT NULL,
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
594 staging_done boolean NOT NULL DEFAULT false,
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
595 UNIQUE(name, staging_done)
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
596 )
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
597 CREATE TRIGGER sections_date_info
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
598 BEFORE UPDATE ON sections
3171
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
599 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
c8ded555c2a8 Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 3008
diff changeset
600
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
601 CREATE TABLE waterway_profiles (
2072
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
602 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
2141
3f58809d2beb Waterway profiles have to match a distance mark
Tom Gottfried <tom@intevation.de>
parents: 2130
diff changeset
603 location isrs NOT NULL REFERENCES distance_marks_virtual,
3f58809d2beb Waterway profiles have to match a distance mark
Tom Gottfried <tom@intevation.de>
parents: 2130
diff changeset
604 geom geography(linestring, 4326),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
605 validity tstzrange,
2072
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
606 lnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
607 mwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
608 hnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
609 fe30 double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
610 fe100 double precision,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
611 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
612 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
613 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
614 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
615 source_organization varchar NOT NULL,
2072
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
616 staging_done boolean NOT NULL DEFAULT false,
2130
f3aabc05f9b2 Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2128
diff changeset
617 EXCLUDE USING GIST (
f3aabc05f9b2 Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2128
diff changeset
618 isrs_asText(location) WITH =,
f3aabc05f9b2 Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2128
diff changeset
619 validity WITH &&,
f3aabc05f9b2 Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2128
diff changeset
620 CAST(staging_done AS int) WITH =)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
621 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
622 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
623 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
624 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
625
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
626 CREATE TABLE fairway_dimensions (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
627 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
1984
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
628 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
629 CHECK(ST_IsValid(CAST(area AS geometry))),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
630 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
631 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
632 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
633 min_depth smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
634 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
635 source_organization varchar NOT NULL,
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 323
diff changeset
636 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
637 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
638 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
639 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
640 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
641
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
642 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
643 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
644 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
645 CREATE TABLE bottlenecks (
1572
056a86b24be2 Made bottleneck primary key an int. Attention: This may break something!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1547
diff changeset
646 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3624
diff changeset
647 bottleneck_id varchar NOT NULL,
3648
0ec5c8ec1e44 Avoid empty validity time ranges
Tom Gottfried <tom@intevation.de>
parents: 3647
diff changeset
648 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
649 gauge_location isrs NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
650 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
651 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
652 stretch isrsrange NOT NULL,
1984
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
653 area geography(MULTIPOLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
654 CHECK(ST_IsValid(CAST(area AS geometry))),
2765
41f5ac76d642 BT import: allow for rb and lb to be null.
Sascha Wilde <wilde@intevation.de>
parents: 2627
diff changeset
655 rb char(2) REFERENCES countries, -- from rb_lb in interface
41f5ac76d642 BT import: allow for rb and lb to be null.
Sascha Wilde <wilde@intevation.de>
parents: 2627
diff changeset
656 lb char(2) REFERENCES countries, -- from rb_lb in interface
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
657 responsible_country char(2) NOT NULL REFERENCES countries,
3423
6592396f5061 Make revisiting time of a bottleneck optional
Tom Gottfried <tom@intevation.de>
parents: 3402
diff changeset
658 revisiting_time smallint,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
659 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
660 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
661 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
662 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
663 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
664 -- 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
665 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
666 -- 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
667 -- XXX: Also an attribut of sounding result?
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3624
diff changeset
668 date_info timestamp with time zone NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
669 source_organization varchar NOT NULL,
4111
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents: 4106
diff changeset
670 staging_done boolean NOT NULL DEFAULT false,
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents: 4106
diff changeset
671 UNIQUE (bottleneck_id, validity, staging_done),
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents: 4106
diff changeset
672 EXCLUDE USING GiST (bottleneck_id WITH =,
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents: 4106
diff changeset
673 validity WITH &&,
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents: 4106
diff changeset
674 CAST(staging_done AS int) WITH =)
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents: 4106
diff changeset
675 DEFERRABLE INITIALLY DEFERRED
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
676 )
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
677 CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
678 AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
679 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity')
56
f378959820be Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 55
diff changeset
680
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
681 CREATE TABLE bottlenecks_riverbed_materials (
2999
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2867
diff changeset
682 bottleneck_id int NOT NULL REFERENCES bottlenecks(id)
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2867
diff changeset
683 ON DELETE CASCADE,
2076
0e006077bbfa Add missing NOT NULL constraints
Tom Gottfried <tom@intevation.de>
parents: 2072
diff changeset
684 riverbed varchar NOT NULL REFERENCES riverbed_materials,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
685 -- 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
686 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
687 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
688
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
689 CREATE TABLE sounding_results (
656
9ef2f80a4645 Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents: 655
diff changeset
690 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3656
2a079d0a71c1 Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents: 3648
diff changeset
691 bottleneck_id varchar NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
692 date_info date NOT NULL,
656
9ef2f80a4645 Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents: 655
diff changeset
693 UNIQUE (bottleneck_id, date_info),
1984
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
694 area geography(POLYGON, 4326) NOT NULL
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
695 CHECK(ST_IsValid(CAST(area AS geometry))),
611
effd22c0ae5a Sounding result: Write simple SQL insert dumper. Not deterministic, yet.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 609
diff changeset
696 surtyp varchar REFERENCES survey_types,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
697 coverage varchar REFERENCES coverage_types,
3624
3012d0b3badc Allow same values for depth_reference in SR as in gauges
Sascha Wilde <wilde@intevation.de>
parents: 3535
diff changeset
698 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
1131
2e6b47cdb2ca Store octrees along with sounding results
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
699 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
700 octree_index bytea,
4648
66fcd898efd9 Started with conversion tool.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4628
diff changeset
701 mesh_checksum varchar,
66fcd898efd9 Started with conversion tool.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4628
diff changeset
702 mesh_index bytea,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
703 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
704 )
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
705 CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
4068
76482935b6e5 Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 4067
diff changeset
706 AFTER INSERT OR UPDATE OF bottleneck_id ON sounding_results
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
707 FOR EACH ROW
4068
76482935b6e5 Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 4067
diff changeset
708 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info')
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
709 CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
3746
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
710 AFTER INSERT OR UPDATE ON sounding_results
3747
ad67e4286d65 Fixed typo in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 3746
diff changeset
711 FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area()
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
712
4561
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
713 CREATE TABLE sounding_results_iso_areas (
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
714 sounding_result_id int NOT NULL REFERENCES sounding_results
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
715 ON DELETE CASCADE,
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
716 height numeric NOT NULL,
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
717 areas geography(MULTIPOLYGON, 4326) NOT NULL,
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
718 -- TODO: generate valid simple features and add constraint:
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
719 -- CHECK(ST_IsSimple(CAST(areas AS geometry))),
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
720 PRIMARY KEY (sounding_result_id, height)
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
721 )
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
722 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
723 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
724 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
725 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
726 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4461
7128741faeb9 Changed fa data to accept "position" as strings, not a 2char "position_code"
Sascha Wilde <wilde@intevation.de>
parents: 4449
diff changeset
727 position varchar,
4067
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
728 bottleneck_id varchar NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
729 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
730 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
731 -- 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
732 critical boolean,
4057
b79b60c0cc5a date_info has to be provided by the data source
Tom Gottfried <tom@intevation.de>
parents: 4033
diff changeset
733 date_info timestamp with time zone NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
734 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
735 )
4067
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
736 -- FIXME: From the DRC it is unclear what the exact semantics of
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
737 -- surdat and Date_Info ar unclear. Currently we assume that
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
738 -- (fk_bn_fid,surdat) has to be unique, but that might be false.
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
739 -- Anyway, I will date_info here to check for an matching
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
740 -- reference gauge at the bottleneck. The reason for this
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
741 -- decision is purely practical (and might be semantically
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
742 -- disputable: the bottleneck data in the demo system is not old
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
743 -- enough to cover rthe surdat times...
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
744 CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
745 AFTER INSERT OR UPDATE OF bottleneck_id ON fairway_availability
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
746 FOR EACH ROW
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
747 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info')
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
748
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
749 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
750 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
751 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
752 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
753 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
754 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
755 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
756 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
757 IS NOT NULL),
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
758 shallowest_spot geography(POINT, 4326)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
759 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
760
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
761 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
762 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
763 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
764 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
765 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
766 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
767 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
768 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
769
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
770 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
771 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
772 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
773 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
774 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
775 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
776 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
777 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
778 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
779 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
780 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
781 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
782 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
783 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
784 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
785 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
786 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
787 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
788 )
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
789 ;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
790
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
791
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
792 --
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
793 -- Import queue and respective logging
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
794 --
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
795 CREATE TYPE import_state AS ENUM (
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
796 'queued',
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
797 'running',
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
798 'failed', 'unchanged', 'pending',
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
799 'accepted', 'declined'
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
800 );
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
801
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
802 CREATE TYPE log_type AS ENUM ('info', 'warn', 'error');
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
803
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
804 -- Namespace for import queue and respective logging
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
805 CREATE SCHEMA import
1547
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
806
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
807 CREATE TABLE import_configuration (
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
808 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
809 username varchar NOT NULL
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
810 REFERENCES internal.user_profiles(username)
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
811 ON DELETE CASCADE
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
812 ON UPDATE CASCADE,
2042
d29ac997eb34 This breaks this branch!!!! Starting to remove the old persistent layer for configured imports.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1995
diff changeset
813 kind varchar NOT NULL
1547
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
814 )
1702
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
815
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
816 CREATE TABLE import_configuration_attributes (
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
817 import_configuration_id int NOT NULL
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
818 REFERENCES import_configuration(id)
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
819 ON DELETE CASCADE
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
820 ON UPDATE CASCADE,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
821 k VARCHAR NOT NULL,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
822 v TEXT NOT NULL,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
823 UNIQUE (import_configuration_id, k)
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
824 )
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
825
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
826 CREATE TABLE imports (
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
827 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
828 state import_state NOT NULL DEFAULT 'queued',
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
829 kind varchar NOT NULL,
4098
cfa0a5775d70 Reapplied timezone patch to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4094
diff changeset
830 enqueued timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
cfa0a5775d70 Reapplied timezone patch to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4094
diff changeset
831 due timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
832 retry_wait interval
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
833 CHECK(retry_wait IS NULL
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
834 OR retry_wait >= interval '0 microseconds'),
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
835 trys_left int, -- if NULL and retry_wait NOT NULL, endless
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
836 username varchar NOT NULL
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
837 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
838 ON DELETE CASCADE
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
839 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
840 signer varchar
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
841 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
842 ON DELETE SET NULL
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
843 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
844 send_email boolean NOT NULL DEFAULT false,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
845 data TEXT,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
846 summary TEXT
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
847 )
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
848
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
849 CREATE INDEX enqueued_idx ON imports(enqueued, state)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
850
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
851 CREATE TABLE import_logs (
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
852 import_id int NOT NULL REFERENCES imports(id)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
853 ON DELETE CASCADE,
4100
cc3d607b49cc Merged default into timezone branch.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4098 4099
diff changeset
854 time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP,
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
855 kind log_type NOT NULL DEFAULT 'info',
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
856 msg TEXT NOT NULL
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
857 )
4187
65a5501dc13d Enable faster access to import logs
Tom Gottfried <tom@intevation.de>
parents: 4173
diff changeset
858 CREATE INDEX import_logs_import_id ON import.import_logs (import_id)
2627
3a242e6aa56d Import log: Add filter for log entries with warnings only: GET /api/imports?warnings=true
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2589
diff changeset
859
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
860 CREATE TABLE track_imports (
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
861 import_id int NOT NULL REFERENCES imports(id)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
862 ON DELETE CASCADE,
4031
4bf1c8d91bac Import queue: Added a column to the tracking table to delete entries on stage done.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4016
diff changeset
863 deletion bool NOT NULL DEFAULT false,
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
864 relation regclass NOT NULL,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
865 key int NOT NULL,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
866 UNIQUE (relation, key)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
867 )
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
868 ;
56
f378959820be Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 55
diff changeset
869
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
870 CREATE FUNCTION import.del_import(imp_id int) RETURNS void AS
1193
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
871 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
872 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
873 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
874 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
875 FOR tmp IN
4126
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
876 SELECT relation, array_agg(key) AS keys
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
877 FROM import.track_imports
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
878 WHERE import_id = imp_id AND NOT deletion
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
879 GROUP BY relation
1193
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
880 LOOP
4126
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
881 EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation)
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
882 USING tmp.keys;
1193
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
883 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
884 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
885 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
886 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
887
2542
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
888 CREATE SCHEMA caching
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
889
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
890 CREATE TABLE sounding_differences (
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
891 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
892 minuend int NOT NULL REFERENCES waterway.sounding_results(id)
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
893 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
894 subtrahend int NOT NULL REFERENCES waterway.sounding_results(id)
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
895 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
896 UNIQUE (minuend, subtrahend)
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
897 )
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
898
4573
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
899 CREATE TABLE sounding_differences_iso_areas (
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
900 sounding_differences_id int NOT NULL REFERENCES sounding_differences(id)
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
901 ON DELETE CASCADE,
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
902 height numeric NOT NULL,
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
903 areas geography(MULTIPOLYGON, 4326) NOT NULL,
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
904 PRIMARY KEY (sounding_differences_id, height)
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
905 )
2542
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
906 ;
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
907
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
908 COMMIT;