Mercurial > gemma
annotate schema/gemma.sql @ 4043:fbd7c012f10c historization_ng
Merged
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 24 Jul 2019 11:16:38 +0200 |
parents | 3fcb95a07948 4f2f34f5d14d |
children | f42f7f7eb81f |
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 |
2172
7e2c77ccc02f
Removed trigger to update date_info on gauge_measurements.
Sascha Wilde <wilde@intevation.de>
parents:
2170
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 -- |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
26 -- Trigger functions |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
27 -- |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
28 -- 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
|
29 -- historicisation? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 CREATE FUNCTION update_date_info() RETURNS trigger |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 LANGUAGE plpgsql |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 AS $$ |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 BEGIN |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 NEW.date_info = CURRENT_TIMESTAMP; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 RETURN NEW; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 END; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 $$; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
39 -- 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
|
40 -- 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
|
41 -- trigger function |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
42 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
|
43 $$ |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
44 DECLARE |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
45 new_geom geometry; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
46 has_equal boolean; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
47 BEGIN |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
48 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
|
49 INTO new_geom |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
50 USING NEW; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
51 EXECUTE format( |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
52 '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
|
53 'WHERE id <> $2', |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
54 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
|
55 INTO has_equal |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
56 USING new_geom, NEW.id; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
57 IF has_equal THEN |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
58 RAISE EXCEPTION |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
59 'new row for relation "%" violates constraint trigger "%"', |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
60 TG_TABLE_NAME, TG_NAME |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
61 USING |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
62 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
|
63 Box2D(new_geom)), |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
64 ERRCODE = 23505, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
65 SCHEMA = TG_TABLE_SCHEMA, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
66 TABLE = TG_TABLE_NAME, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
67 COLUMN = TG_ARGV[0], |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
68 CONSTRAINT = TG_NAME; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
69 END IF; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
70 RETURN NEW; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
71 END; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
72 $$ |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
73 LANGUAGE plpgsql; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
74 |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
75 -- 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
|
76 -- 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
|
77 -- 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
|
78 -- 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
|
79 CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
80 $$ |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
81 DECLARE |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
82 -- 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
|
83 -- 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
|
84 -- 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
|
85 referenced_gauge text; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
86 new_validity tstzrange; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
87 BEGIN |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
88 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
|
89 INTO referenced_gauge |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
90 USING NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
91 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
|
92 INTO new_validity |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
93 USING NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
94 IF EXISTS ( SELECT * FROM waterway.gauges |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
95 WHERE location = referenced_gauge::isrs |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
96 AND validity && new_validity ) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
97 THEN |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
98 RETURN NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
99 ELSE |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
100 RAISE EXCEPTION |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
101 '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
|
102 TG_TABLE_NAME, TG_NAME |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
103 USING |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
104 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
|
105 isrs_AsText(referenced_gauge::isrs)), |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
106 ERRCODE = 23505, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
107 SCHEMA = TG_TABLE_SCHEMA, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
108 TABLE = TG_TABLE_NAME, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
109 COLUMN = TG_ARGV[0], |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
110 CONSTRAINT = TG_NAME; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
111 END IF; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
112 END; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
113 $$ |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
114 LANGUAGE plpgsql; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
115 |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
116 -- Trigger functions to be used as statement-level AFTER triggers, |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
117 -- associating time-based referencing objects to matching version |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
118 CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
119 $$ |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
120 DECLARE |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
121 new_bn int; |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
122 new_bns int[]; |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
123 BEGIN |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
124 -- Avoid unnecessary execution ON UPDATE if validity did not change |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
125 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
126 UPDATE waterway.gauge_measurements |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
127 SET validity = NEW.validity |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
128 WHERE location = NEW.location |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
129 AND measure_date <@ NEW.validity; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
130 |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
131 -- build bottleneck validities from intersections with gauge validities |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
132 FOR new_bn IN |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
133 INSERT INTO waterway.bottlenecks ( |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
134 bottleneck_id, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
135 validity, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
136 gauge_location, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
137 gauge_validity, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
138 objnam, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
139 nobjnm, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
140 stretch, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
141 area, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
142 rb, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
143 lb, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
144 responsible_country, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
145 revisiting_time, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
146 limiting, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
147 date_info, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
148 source_organization, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
149 staging_done |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
150 ) SELECT |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
151 b.bottleneck_id, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
152 -- Anticipate non-intersecting gauge validities: |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
153 b.validity * CASE WHEN g.validity = NEW.validity |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
154 THEN NEW.validity ELSE g.validity - NEW.validity END, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
155 b.gauge_location, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
156 g.validity, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
157 b.objnam, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
158 b.nobjnm, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
159 b.stretch, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
160 b.area, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
161 b.rb, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
162 b.lb, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
163 b.responsible_country, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
164 b.revisiting_time, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
165 b.limiting, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
166 b.date_info, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
167 b.source_organization, |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
168 b.staging_done |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
169 FROM waterway.bottlenecks b JOIN waterway.gauges g |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
170 ON b.gauge_location = g.location |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
171 WHERE b.gauge_location = NEW.location |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
172 AND b.validity && NEW.validity |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
173 -- Avoid duplicate intersection results: |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
174 AND NOT (b.validity <@ NEW.validity |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
175 AND g.validity <> NEW.validity) |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
176 ON CONFLICT (bottleneck_id, validity) DO UPDATE SET |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
177 -- Associate to new matching gauge version |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
178 gauge_validity = EXCLUDED.gauge_validity |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
179 RETURNING id |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
180 LOOP |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
181 new_bns = new_bns || new_bn; |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
182 END LOOP; |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
183 -- Delete bottleneck versions superseded by new intersections: |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
184 DELETE FROM waterway.bottlenecks |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
185 WHERE gauge_location = NEW.location |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
186 AND validity && NEW.validity |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
187 AND id <> ALL(new_bns); |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
188 END IF; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
189 RETURN NULL; -- ignored |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
190 END; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
191 $$ |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
192 LANGUAGE plpgsql; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
193 |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
194 CREATE OR REPLACE FUNCTION move_bottleneck_referencing() RETURNS trigger AS |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
195 $$ |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
196 BEGIN |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
197 -- Avoid unnecessary execution ON UPDATE if validity did not change |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
198 IF OLD IS NULL OR NEW.validity <> OLD.validity THEN |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
199 UPDATE waterway.sounding_results |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
200 SET bottleneck_validity = NEW.validity |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
201 WHERE bottleneck_id = NEW.bottleneck_id |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
202 AND CAST(date_info AS timestamptz) <@ NEW.validity; |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
203 |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
204 -- Always associate fairway availability data to newest bottleneck |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
205 -- version to prevent problems in analysis over longer time periods |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
206 WITH |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
207 bn AS (SELECT id, validity FROM waterway.bottlenecks |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
208 WHERE bottleneck_id = NEW.bottleneck_id), |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
209 latest AS (SELECT id FROM bn |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
210 -- Candidates are past new validity or just inserted/updated |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
211 WHERE NOT validity &< NEW.validity OR id = NEW.id |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
212 ORDER BY upper(validity) DESC FETCH FIRST ROW ONLY) |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
213 UPDATE waterway.fairway_availability |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
214 SET bottleneck_id = (SELECT id FROM latest) |
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
215 WHERE bottleneck_id IN(SELECT id FROM bn EXCEPT SELECT id FROM latest); |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
216 END IF; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
217 RETURN NULL; -- ignored |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
218 END; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
219 $$ |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
220 LANGUAGE plpgsql; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
221 |
3746
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
222 -- Constraint trigger: sounding Results must intersect with the area |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
223 -- of the bottleneck they belong to. |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
224 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
|
225 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
|
226 AS $$ |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
227 BEGIN |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
228 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
|
229 FROM waterway.bottlenecks |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
230 WHERE (bottleneck_id, validity) |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
231 =(NEW.bottleneck_id, NEW.bottleneck_validity)), |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
232 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
|
233 THEN |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
234 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
|
235 '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
|
236 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
|
237 USING |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
238 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
|
239 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
|
240 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
|
241 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
|
242 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
|
243 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
|
244 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
|
245 END; |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
246 $$; |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
247 |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
248 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
249 -- |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
250 -- GEMA meta data |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
251 -- |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
252 CREATE TABLE gemma_schema_version ( |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
253 version int PRIMARY KEY, |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
254 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
|
255 ); |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
256 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
257 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
|
258 LANGUAGE sql |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
259 AS $$ |
3996
a2921151b193
Schema: Use SQL instead of PLPGSQL for get_schema_version().
Sascha Wilde <wilde@intevation.de>
parents:
3978
diff
changeset
|
260 SELECT max(version) FROM gemma_schema_version; |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
261 $$; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
262 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
263 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
264 -- |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
265 -- GEMMA data |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
266 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
267 |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
268 -- Namespace not to be accessed directly by any user |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
269 CREATE SCHEMA internal |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
270 -- Profile data are only accessible via the view users.list_users. |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
271 CREATE TABLE user_profiles ( |
361
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
345
diff
changeset
|
272 username varchar PRIMARY KEY CHECK(octet_length(username) <= 63), |
f5087cebc740
Enforce PostgreSQL identifier length on username
Tom Gottfried <tom@intevation.de>
parents:
345
diff
changeset
|
273 -- keep username length compatible with role identifier |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
274 map_extent box2d NOT NULL, |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
275 email_address varchar NOT NULL |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
276 ) |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
277 -- Columns referencing user-visible schemas added below. |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
278 ; |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
279 |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
280 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
281 -- Namespace to be accessed by sys_admin only |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
282 CREATE SCHEMA sys_admin |
457
62ffb6c8a42e
Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents:
449
diff
changeset
|
283 CREATE TABLE system_config ( |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
284 config_key varchar PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
285 config_val varchar |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
286 ) |
437
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
287 |
478
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
288 CREATE TABLE password_reset_requests ( |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
289 hash varchar(32) PRIMARY KEY, |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
290 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
3956
4f9a1ff2c2ee
Reworked password reset to be single mailed.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3778
diff
changeset
|
291 username varchar NOT NULL UNIQUE |
478
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
292 REFERENCES internal.user_profiles(username) |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
293 ON DELETE CASCADE ON UPDATE CASCADE |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
294 ) |
3af7ca761f6a
Purge password reset role
Tom Gottfried <tom@intevation.de>
parents:
457
diff
changeset
|
295 |
624
8772979f8750
Remove test data from schema
Tom Gottfried <tom@intevation.de>
parents:
611
diff
changeset
|
296 -- Tables with geo data to be published with GeoServer. |
457
62ffb6c8a42e
Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents:
449
diff
changeset
|
297 CREATE TABLE external_services ( |
437
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
298 local_name varchar PRIMARY KEY, |
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
299 remote_url varchar NOT NULL, |
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
300 is_wfs boolean NOT NULL DEFAULT TRUE |
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
301 ) |
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
302 |
457
62ffb6c8a42e
Remove unnecessary schema qualifiers
Tom Gottfried <tom@intevation.de>
parents:
449
diff
changeset
|
303 CREATE TABLE published_services ( |
598
4854a1e85870
Ensure published service is based on existing table
Tom Gottfried <tom@intevation.de>
parents:
580
diff
changeset
|
304 name regclass PRIMARY KEY, |
1288
9f7dc950ffd2
Accept only well-formed XML documents as style in database
Tom Gottfried <tom@intevation.de>
parents:
1194
diff
changeset
|
305 style xml CHECK(style IS DOCUMENT), |
437
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
306 as_wms boolean NOT NULL DEFAULT TRUE, |
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
307 as_wfs boolean NOT NULL DEFAULT TRUE |
b8366b24dc55
Added two tables to configure the WFS/WMS services of gemma.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
383
diff
changeset
|
308 ) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
309 ; |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
310 |
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
311 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
312 -- 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
|
313 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
314 CREATE TABLE language_codes ( |
182 | 315 language_code varchar PRIMARY KEY |
316 ); | |
84
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
317 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
318 CREATE TABLE catccls ( |
182 | 319 catccl smallint PRIMARY KEY |
320 -- 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
|
321 -- (see page 328 of edition 2.3) |
182 | 322 ); |
927
48f70782400d
Add categories of CEMT class and directions of impact
Tom Gottfried <tom@intevation.de>
parents:
919
diff
changeset
|
323 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
|
324 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
325 CREATE TABLE dirimps ( |
182 | 326 dirimp smallint PRIMARY KEY |
327 -- 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
|
328 -- (see page 381 of edition 2.3) |
182 | 329 ); |
927
48f70782400d
Add categories of CEMT class and directions of impact
Tom Gottfried <tom@intevation.de>
parents:
919
diff
changeset
|
330 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
|
331 -- 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
|
332 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
333 CREATE TABLE depth_references ( |
182 | 334 depth_reference varchar(4) PRIMARY KEY |
335 -- See col. AB and AI RIS-Index Encoding Guide | |
336 -- XXX: We need a way to distinguish between geodetic (eg. col. AP | |
337 -- RIS-Index) and other references (e.g. col. AB and AI): | |
338 -- _ multi-column FK with a boolean column (geodetic/non-geodetic; | |
339 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. | |
340 -- _ Do not mixup things with different meanings in one table at all | |
341 -- (which would mean a model differing a bit from RIS-Index ideas) | |
342 ); | |
93 | 343 |
934
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
344 CREATE TABLE catdis ( |
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
345 catdis smallint PRIMARY KEY |
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
346 -- 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
|
347 -- (see page 171 of edition 2.3) |
182 | 348 ); |
934
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
349 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
|
350 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
351 CREATE TABLE position_codes ( |
182 | 352 position_code char(2) PRIMARY KEY |
353 -- Use smallint because of fairway availability provided on daily basis? | |
354 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, | |
355 -- sheet "Position_code" or RIS-Index encoding guide? | |
356 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. | |
357 -- Clarify! | |
358 -- TODO: Do we need an attribute "meaning" or so? | |
359 ); | |
87
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
360 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
361 CREATE TABLE levels_of_service ( |
1662
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
362 level_of_service smallint PRIMARY KEY, |
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
363 name varchar(4) |
182 | 364 ); |
1662
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
365 INSERT INTO levels_of_service ( |
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
366 level_of_service, |
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
367 name |
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
368 ) 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
|
369 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
370 CREATE TABLE riverbed_materials ( |
182 | 371 material varchar PRIMARY KEY |
372 -- XXX: Should this table contain choices from DRC 2.2.3 or | |
373 -- from IENC Encoding Guide M.4.3, attribute NATSUR? | |
374 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
375 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
376 CREATE TABLE survey_types ( |
182 | 377 survey_type varchar PRIMARY KEY |
378 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
379 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
380 CREATE TABLE coverage_types ( |
182 | 381 coverage_type varchar PRIMARY KEY |
382 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
383 |
577
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
384 CREATE TABLE limiting_factors ( |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
385 limiting_factor varchar PRIMARY KEY |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
386 ); |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
387 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
388 CREATE TABLE measure_types ( |
182 | 389 measure_type varchar PRIMARY KEY |
390 ); | |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
391 |
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
|
392 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
|
393 '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
|
394 '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
|
395 '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
|
396 ); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
397 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
398 -- Namespace for user management related data |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
399 CREATE SCHEMA users |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
400 CREATE TABLE responsibility_areas ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
401 country char(2) PRIMARY KEY REFERENCES countries, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
402 area geography(MULTIPOLYGON, 4326) |
1984
48001472e1d8
Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
403 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
|
404 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
405 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
406 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
|
407 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
|
408 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
|
409 country char(2) REFERENCES countries, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
410 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
|
411 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
|
412 UNIQUE (template_name, template_type, country) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
413 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
414 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
|
415 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
|
416 ; |
268
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
417 ALTER TABLE internal.user_profiles ADD |
72062ca52746
Make user_profiles table invisible for users
Tom Gottfried <tom@intevation.de>
parents:
234
diff
changeset
|
418 country char(2) NOT NULL REFERENCES users.responsibility_areas; |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
419 |
319
ac760b0f22a9
Add special role for password reset
Tom Gottfried <tom@intevation.de>
parents:
268
diff
changeset
|
420 |
831
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
421 -- Namespace for system wide configuration |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
422 CREATE SCHEMA systemconf |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
423 CREATE TABLE feature_colours ( |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
424 feature_name varchar, |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
425 style_attr varchar, |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
426 r int NOT NULL CHECK (r >= 0 AND r < 256), |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
427 g int NOT NULL CHECK (g >= 0 AND g < 256), |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
428 b int NOT NULL CHECK (b >= 0 AND b < 256), |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
429 a numeric NOT NULL CHECK (a >= 0 AND a <= 1), |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
430 PRIMARY KEY (feature_name, style_attr) |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
431 ) |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
432 ; |
fa417ff1f355
Extended schema to save feature style information.
Sascha Wilde <wilde@intevation.de>
parents:
821
diff
changeset
|
433 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
434 -- 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
|
435 CREATE SCHEMA waterway |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
436 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
437 -- Eventually obsolete. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
438 -- 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
|
439 -- CREATE TABLE rwdrs ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
440 -- tretch isrsrange PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
441 -- -- 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
|
442 -- -- 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
|
443 -- -- below anyhow. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
444 -- -- 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
|
445 -- -- 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
|
446 -- -- => 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
|
447 -- -- factor, this might be an issue. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
448 -- rwdr double precision NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
449 -- EXCLUDE USING GIST (stretch WITH &&) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
450 --) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
451 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
452 CREATE TABLE waterway_area ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
453 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
|
454 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
|
455 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
|
456 catccl smallint REFERENCES catccls, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
457 dirimp smallint REFERENCES dirimps |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
458 ) |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
459 CREATE CONSTRAINT TRIGGER waterway_area_area_unique |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
460 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
|
461 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
|
462 |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
463 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
464 CREATE TABLE gauges ( |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
465 location isrs CHECK( |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
577
diff
changeset
|
466 (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
|
467 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
|
468 objname varchar NOT NULL, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
469 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
|
470 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
|
471 applicability_to_km int8, |
3648
0ec5c8ec1e44
Avoid empty validity time ranges
Tom Gottfried <tom@intevation.de>
parents:
3647
diff
changeset
|
472 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
|
473 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
|
474 geodref varchar, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
475 date_info timestamp with time zone NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
476 source_organization varchar, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
477 lastupdate timestamp with time zone NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
478 -- entry removed from external data source (RIS-Index)/historicised: |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
479 erased boolean NOT NULL DEFAULT false, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
480 PRIMARY KEY (location, validity), |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
481 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
|
482 DEFERRABLE INITIALLY DEFERRED |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
483 ) |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
484 -- Allow only one non-erased entry per location |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
485 CREATE UNIQUE INDEX gauges_erased_unique_constraint |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
486 ON gauges (location) |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
487 WHERE NOT erased |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
488 -- Associate referencing objects to matching gauge version |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
489 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
490 ON gauges FOR EACH ROW EXECUTE FUNCTION move_gauge_referencing() |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
491 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
492 CREATE TABLE gauges_reference_water_levels ( |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
493 location isrs NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
494 validity tstzrange NOT NULL, |
3402
c04b1409a596
Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents:
3389
diff
changeset
|
495 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
|
496 -- 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
|
497 -- names, too: |
2317
8a8680e70d2e
Cleanup schema for reference water levels
Tom Gottfried <tom@intevation.de>
parents:
2300
diff
changeset
|
498 depth_reference varchar NOT NULL, -- REFERENCES depth_references, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
499 PRIMARY KEY (location, validity, depth_reference), |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
500 value int NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
501 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
502 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
503 CREATE TABLE gauge_measurements ( |
1636
37ee25bc2bbe
Updated database schema for gauge meaurement imports.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1583
diff
changeset
|
504 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
505 location isrs NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
506 validity tstzrange NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
507 CONSTRAINT gauge_key |
3402
c04b1409a596
Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents:
3389
diff
changeset
|
508 FOREIGN KEY (location, validity) REFERENCES gauges |
c04b1409a596
Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents:
3389
diff
changeset
|
509 ON UPDATE CASCADE, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
510 measure_date timestamp with time zone NOT NULL, |
3389
45a629a3a8b8
Fix constraints on relationship between gauges and measurements/predictions
Tom Gottfried <tom@intevation.de>
parents:
3387
diff
changeset
|
511 CHECK (measure_date <@ validity), |
1636
37ee25bc2bbe
Updated database schema for gauge meaurement imports.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1583
diff
changeset
|
512 country_code char(2) NOT NULL REFERENCES countries, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
513 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
|
514 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
|
515 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
|
516 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
|
517 water_level double precision NOT NULL, |
3387
d9eda49a52f4
Remove obsolete DEFAULT values
Tom Gottfried <tom@intevation.de>
parents:
3307
diff
changeset
|
518 date_info timestamp with time zone NOT NULL, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
519 source_organization varchar NOT NULL, -- "originator" from NtS response |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
520 staging_done boolean NOT NULL DEFAULT false, |
4016
9ab7e1056360
Optimize index setup a bit
Tom Gottfried <tom@intevation.de>
parents:
4006
diff
changeset
|
521 UNIQUE (measure_date, location, staging_done) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
522 ) |
4016
9ab7e1056360
Optimize index setup a bit
Tom Gottfried <tom@intevation.de>
parents:
4006
diff
changeset
|
523 -- For fast retrieval of newest measurement per location: |
9ab7e1056360
Optimize index setup a bit
Tom Gottfried <tom@intevation.de>
parents:
4006
diff
changeset
|
524 CREATE INDEX gauge_measurements_location_measure_date_desc |
9ab7e1056360
Optimize index setup a bit
Tom Gottfried <tom@intevation.de>
parents:
4006
diff
changeset
|
525 ON waterway.gauge_measurements (location, measure_date DESC) |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
526 |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
527 CREATE TABLE gauge_predictions ( |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
528 location isrs NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
529 validity tstzrange NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
530 CONSTRAINT gauge_key |
3402
c04b1409a596
Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents:
3389
diff
changeset
|
531 FOREIGN KEY (location, validity) REFERENCES gauges |
c04b1409a596
Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents:
3389
diff
changeset
|
532 ON UPDATE CASCADE, |
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, |
3389
45a629a3a8b8
Fix constraints on relationship between gauges and measurements/predictions
Tom Gottfried <tom@intevation.de>
parents:
3387
diff
changeset
|
534 CHECK (measure_date >= lower(validity)), |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
535 country_code char(2) NOT NULL REFERENCES countries, |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
536 sender varchar NOT NULL, -- "from" element from NtS response |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
537 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
|
538 date_issue timestamp with time zone NOT NULL, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
539 reference_code varchar(4) NOT NULL REFERENCES depth_references, |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
540 water_level double precision NOT NULL, |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
541 conf_interval numrange |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
542 CHECK (conf_interval @> CAST(water_level AS numeric)), |
3387
d9eda49a52f4
Remove obsolete DEFAULT values
Tom Gottfried <tom@intevation.de>
parents:
3307
diff
changeset
|
543 date_info timestamp with time zone NOT NULL, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
544 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
|
545 PRIMARY KEY (measure_date, location, date_issue) |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
546 ) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
547 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
548 CREATE TABLE waterway_axis ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
549 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
|
550 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
|
551 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
|
552 -- 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
|
553 objnam varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
554 nobjnam varchar |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
555 ) |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
556 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
557 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
|
558 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
|
559 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
560 -- 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
|
561 -- 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
|
562 -- 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
|
563 CREATE TABLE distance_marks_virtual ( |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
564 location_code isrs PRIMARY KEY, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
565 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
|
566 related_enc varchar(12) NOT NULL, |
1b11ff97c1d1
Add wwname to import of virtual distance marks.
Sascha Wilde <wilde@intevation.de>
parents:
3747
diff
changeset
|
567 wwname varchar |
948
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
568 ) |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
569 |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
570 CREATE TABLE distance_marks ( |
1861
5083a1d19a4b
Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1831
diff
changeset
|
571 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
|
572 country char(2) REFERENCES countries, |
5083a1d19a4b
Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1831
diff
changeset
|
573 hectom int, |
948
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
574 geom geography(POINT, 4326) NOT NULL, |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
575 -- 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
|
576 catdis smallint REFERENCES catdis, |
5083a1d19a4b
Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1831
diff
changeset
|
577 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
|
578 related_enc varchar(12) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
579 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
580 |
1446
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
581 -- We need to configure primary keys for the views used by |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
582 -- geoserver for wfs, otherwise it will generate ids on the fly, |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
583 -- which will change for the same feature... |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
584 -- See |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
585 -- https://docs.geoserver.org/stable/en/user/data/database/primarykey.html |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
586 -- for details. |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
587 CREATE TABLE gt_pk_metadata ( |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
588 table_schema VARCHAR(32) NOT NULL, |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
589 table_name VARCHAR(32) NOT NULL, |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
590 pk_column VARCHAR(32) NOT NULL, |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
591 pk_column_idx INTEGER, |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
592 pk_policy VARCHAR(32), |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
593 pk_sequence VARCHAR(64), |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
594 unique (table_schema, table_name, pk_column), |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
595 check (pk_policy in ('sequence', 'assigned', 'autogenerated')) |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
596 ) |
1e19184472bf
Add configuration of primary key metadata used by geoserver.
Sascha Wilde <wilde@intevation.de>
parents:
1392
diff
changeset
|
597 |
1894
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
598 CREATE TABLE stretches ( |
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
599 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
600 name varchar NOT NULL, |
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
601 stretch isrsrange NOT NULL, |
1984
48001472e1d8
Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
602 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
|
603 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
|
604 objnam varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
605 nobjnam varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
606 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
|
607 source_organization varchar NOT NULL, |
1894
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
608 staging_done boolean NOT NULL DEFAULT false, |
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
609 UNIQUE(name, staging_done) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
610 ) |
3008
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
3003
diff
changeset
|
611 CREATE TRIGGER stretches_date_info |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
3003
diff
changeset
|
612 BEFORE UPDATE ON stretches |
f394e828a6d2
Separate view definitions for GeoServer from general schema definition
Tom Gottfried <tom@intevation.de>
parents:
3003
diff
changeset
|
613 FOR EACH ROW EXECUTE PROCEDURE update_date_info() |
1894
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
614 |
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
615 CREATE TABLE stretch_countries ( |
1906
32c56e6c089a
Stretch import: Added forgotten source file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1894
diff
changeset
|
616 stretches_id int NOT NULL REFERENCES stretches(id) |
32c56e6c089a
Stretch import: Added forgotten source file.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1894
diff
changeset
|
617 ON DELETE CASCADE, |
1894
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
618 country_code char(2) NOT NULL REFERENCES countries(country_code), |
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
619 UNIQUE(stretches_id, country_code) |
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
620 ) |
d72a1539ef3c
Schema: Replaced sections_stretches table with a pure stretches table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1861
diff
changeset
|
621 |
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
|
622 -- 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
|
623 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
|
624 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
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
|
625 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
|
626 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
|
627 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
|
628 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
|
629 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
|
630 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
|
631 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
|
632 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
|
633 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
|
634 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
|
635 ) |
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
|
636 CREATE TRIGGER sections_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
|
637 BEFORE UPDATE ON stretches |
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
|
638 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
|
639 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
640 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
|
641 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
|
642 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
|
643 geom geography(linestring, 4326), |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
644 validity tstzrange, |
2072
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
645 lnwl double precision, |
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
646 mwl double precision, |
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
647 hnwl double precision, |
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
648 fe30 double precision, |
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
649 fe100 double precision, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
650 -- XXX: further normalise using reference_water_levels? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
651 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
|
652 OR validity IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
653 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
|
654 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
|
655 staging_done boolean NOT NULL DEFAULT false, |
2130
f3aabc05f9b2
Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2128
diff
changeset
|
656 EXCLUDE USING GIST ( |
f3aabc05f9b2
Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2128
diff
changeset
|
657 isrs_asText(location) WITH =, |
f3aabc05f9b2
Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2128
diff
changeset
|
658 validity WITH &&, |
f3aabc05f9b2
Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2128
diff
changeset
|
659 CAST(staging_done AS int) WITH =) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
660 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
661 CREATE TRIGGER waterway_profiles_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
662 BEFORE UPDATE ON waterway_profiles |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
663 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
|
664 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
665 CREATE TABLE fairway_dimensions ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
666 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
|
667 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
|
668 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
|
669 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
|
670 min_width smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
671 max_width smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
672 min_depth smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
673 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
|
674 source_organization varchar NOT NULL, |
b97b3172c61a
Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents:
323
diff
changeset
|
675 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
|
676 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
677 CREATE TRIGGER fairway_dimensions_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
678 BEFORE UPDATE ON fairway_dimensions |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
679 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
|
680 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
681 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
682 -- Bottlenecks |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
683 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
684 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
|
685 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3624
diff
changeset
|
686 bottleneck_id varchar NOT NULL, |
3648
0ec5c8ec1e44
Avoid empty validity time ranges
Tom Gottfried <tom@intevation.de>
parents:
3647
diff
changeset
|
687 validity tstzrange NOT NULL CHECK (NOT isempty(validity)), |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3648
diff
changeset
|
688 UNIQUE (bottleneck_id, validity), |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3624
diff
changeset
|
689 EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&) |
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3624
diff
changeset
|
690 DEFERRABLE INITIALLY DEFERRED, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
691 gauge_location isrs NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
692 objnam varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
693 nobjnm varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
694 stretch isrsrange NOT NULL, |
1984
48001472e1d8
Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
695 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
|
696 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
|
697 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
|
698 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
|
699 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
|
700 revisiting_time smallint, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
701 limiting varchar NOT NULL REFERENCES limiting_factors, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
702 -- surtyp varchar NOT NULL REFERENCES survey_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
703 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
704 -- coverage varchar REFERENCES coverage_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
705 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
706 -- 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
|
707 -- different model approach? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
708 -- 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
|
709 -- XXX: Also an attribut of sounding result? |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3624
diff
changeset
|
710 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
|
711 source_organization varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
712 staging_done boolean NOT NULL DEFAULT false |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
713 ) |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
714 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
|
715 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
|
716 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity') |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
717 -- Associate referencing objects to matching bottleneck version |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
718 CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
719 ON bottlenecks FOR EACH ROW |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
720 EXECUTE FUNCTION move_bottleneck_referencing() |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
721 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
722 CREATE TABLE bottlenecks_riverbed_materials ( |
2999
b3c3c5b5b7c1
Bottleneck import: Import riverbed materials, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2867
diff
changeset
|
723 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
|
724 ON DELETE CASCADE, |
2076
0e006077bbfa
Add missing NOT NULL constraints
Tom Gottfried <tom@intevation.de>
parents:
2072
diff
changeset
|
725 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
|
726 -- 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
|
727 PRIMARY KEY (bottleneck_id, riverbed) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
728 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
729 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
730 CREATE TABLE sounding_results ( |
656
9ef2f80a4645
Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents:
655
diff
changeset
|
731 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
|
732 bottleneck_id varchar NOT NULL, |
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3648
diff
changeset
|
733 bottleneck_validity tstzrange NOT NULL, |
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3648
diff
changeset
|
734 CONSTRAINT bottleneck_key |
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3648
diff
changeset
|
735 FOREIGN KEY (bottleneck_id, bottleneck_validity) |
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3648
diff
changeset
|
736 REFERENCES bottlenecks (bottleneck_id, validity) |
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3648
diff
changeset
|
737 ON UPDATE CASCADE, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
738 date_info date NOT NULL, |
3656
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3648
diff
changeset
|
739 CHECK (tstzrange(date_info::timestamptz, |
2a079d0a71c1
Ensure sounding results are associated to matching bottleneck version
Tom Gottfried <tom@intevation.de>
parents:
3648
diff
changeset
|
740 date_info::timestamptz + '1 d'::interval) && bottleneck_validity), |
656
9ef2f80a4645
Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents:
655
diff
changeset
|
741 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
|
742 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
|
743 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
|
744 surtyp varchar REFERENCES survey_types, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
745 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
|
746 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
|
747 octree_checksum varchar, |
2e6b47cdb2ca
Store octrees along with sounding results
Tom Gottfried <tom@intevation.de>
parents:
1085
diff
changeset
|
748 octree_index bytea, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
749 staging_done boolean NOT NULL DEFAULT false |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
750 ) |
3746
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
751 CREATE CONSTRAINT TRIGGER sounding_results_in_bn_area |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
752 AFTER INSERT OR UPDATE ON sounding_results |
3747
ad67e4286d65
Fixed typo in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents:
3746
diff
changeset
|
753 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
|
754 |
731
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
755 CREATE TABLE sounding_results_contour_lines ( |
1133
dd4071019676
Delete contour lines with their sounding result
Tom Gottfried <tom@intevation.de>
parents:
1131
diff
changeset
|
756 sounding_result_id int NOT NULL REFERENCES sounding_results |
dd4071019676
Delete contour lines with their sounding result
Tom Gottfried <tom@intevation.de>
parents:
1131
diff
changeset
|
757 ON DELETE CASCADE, |
731
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
758 height numeric NOT NULL, |
919
271561dce2e6
Store contour lines in 2D
Tom Gottfried <tom@intevation.de>
parents:
917
diff
changeset
|
759 lines geography(multilinestring, 4326) NOT NULL, |
1984
48001472e1d8
Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
760 -- TODO: generate valid simple features and add constraint: |
48001472e1d8
Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
761 -- CHECK(ST_IsSimple(CAST(lines AS geometry))), |
1133
dd4071019676
Delete contour lines with their sounding result
Tom Gottfried <tom@intevation.de>
parents:
1131
diff
changeset
|
762 PRIMARY KEY (sounding_result_id, height) |
731
4d6329afc1ea
Added table for contour lines of sounding results.
Sascha L. Teichmann <teichmann@intevation.de>
parents:
720
diff
changeset
|
763 ) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
764 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
765 -- Fairway availability |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
766 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
767 CREATE TABLE fairway_availability ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
768 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
769 position_code char(2) REFERENCES position_codes, |
1572
056a86b24be2
Made bottleneck primary key an int. Attention: This may break something!
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1547
diff
changeset
|
770 bottleneck_id int NOT NULL REFERENCES bottlenecks(id), |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
771 surdat date NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
772 UNIQUE (bottleneck_id, surdat), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
773 -- 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
|
774 critical boolean, |
3647
123b9341408e
Fixup accidental change in rev. 02951a62e8c6
Tom Gottfried <tom@intevation.de>
parents:
3645
diff
changeset
|
775 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
776 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
777 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
778 CREATE TRIGGER fairway_availability_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
779 BEFORE UPDATE ON fairway_availability |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
780 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
|
781 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
782 CREATE TABLE fa_reference_values ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
783 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
|
784 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
|
785 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
|
786 fairway_depth smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
787 fairway_width smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
788 fairway_radius int, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
789 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
|
790 IS NOT NULL), |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
791 shallowest_spot geography(POINT, 4326) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
792 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
793 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
794 CREATE TABLE bottleneck_pdfs ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
795 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
|
796 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
|
797 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
|
798 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
|
799 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
|
800 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
801 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
802 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
803 CREATE TABLE effective_fairway_availability ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
804 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
|
805 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
|
806 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
|
807 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
|
808 available_depth_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
809 available_width_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
810 water_level_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
811 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
|
812 water_level_value) IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
813 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
|
814 source_organization varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
815 forecast_generation_time timestamp with time zone, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
816 CHECK(measure_type <> 'forecasted' |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
817 OR forecast_generation_time IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
818 value_lifetime timestamp with time zone, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
819 CHECK(measure_type = 'minimum guaranteed' |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
820 OR value_lifetime IS NOT NULL) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
821 ) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
822 ; |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
823 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
824 -- Configure primary keys for geoserver views |
2085
bca8bda0b805
Changed bottlenecks_geoserver view and added config to prevent duplications.
Sascha Wilde <wilde@intevation.de>
parents:
2084
diff
changeset
|
825 INSERT INTO waterway.gt_pk_metadata VALUES |
2451 | 826 ('waterway', 'gauges_geoserver', 'location'), |
2170
3bfe48e32f20
Fixed de-duplication (primary geoserver id) for virt dist marks.
Sascha Wilde <wilde@intevation.de>
parents:
2159
diff
changeset
|
827 ('waterway', 'distance_marks_geoserver', 'location_code'), |
2300
3dd2de314b1b
Use distance marks from wamos db in map.
Sascha Wilde <wilde@intevation.de>
parents:
2287
diff
changeset
|
828 ('waterway', 'distance_marks_ashore_geoserver', 'id'), |
2341
fb353ff08377
Added geoserver config for stretches layer.
Sascha Wilde <wilde@intevation.de>
parents:
2327
diff
changeset
|
829 ('waterway', 'bottlenecks_geoserver', 'id'), |
3307
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
830 ('waterway', 'stretches_geoserver', 'id'), |
b90b17d0b5a9
added sections_geoserver view, renamed stretch column to section in schema
Markus Kottlaender <markus@intevation.de>
parents:
3302
diff
changeset
|
831 ('waterway', 'sections_geoserver', 'id'); |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
832 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
833 -- |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
834 -- Import queue and respective logging |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
835 -- |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
836 CREATE TYPE import_state AS ENUM ( |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
837 'queued', |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
838 'running', |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
839 'failed', 'unchanged', 'pending', |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
840 'accepted', 'declined' |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
841 ); |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
842 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
843 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
|
844 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
845 -- 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
|
846 CREATE SCHEMA import |
1547
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
847 |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
848 CREATE TABLE import_configuration ( |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
849 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
|
850 username varchar NOT NULL |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
851 REFERENCES internal.user_profiles(username) |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
852 ON DELETE CASCADE |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
853 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
|
854 kind varchar NOT NULL |
1547
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
855 ) |
1702
49b89575ab31
Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1684
diff
changeset
|
856 |
49b89575ab31
Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1684
diff
changeset
|
857 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
|
858 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
|
859 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
|
860 ON DELETE CASCADE |
49b89575ab31
Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1684
diff
changeset
|
861 ON UPDATE CASCADE, |
49b89575ab31
Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1684
diff
changeset
|
862 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
|
863 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
|
864 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
|
865 ) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
866 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
867 CREATE TABLE imports ( |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
868 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
|
869 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
|
870 kind varchar NOT NULL, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
871 enqueued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
872 due timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
873 retry_wait interval |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
874 CHECK(retry_wait IS NULL |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
875 OR retry_wait >= interval '0 microseconds'), |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
876 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
|
877 username varchar NOT NULL |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
878 REFERENCES internal.user_profiles(username) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
879 ON DELETE CASCADE |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
880 ON UPDATE CASCADE, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
881 signer varchar |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
882 REFERENCES internal.user_profiles(username) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
883 ON DELETE SET NULL |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
884 ON UPDATE CASCADE, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
885 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
|
886 data TEXT, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
887 summary TEXT |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
888 ) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
889 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
890 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
|
891 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
892 CREATE TABLE import_logs ( |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
893 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
|
894 ON DELETE CASCADE, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
895 time timestamp NOT NULL DEFAULT now(), |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
896 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
|
897 msg TEXT NOT NULL |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
898 ) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
899 |
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
|
900 CREATE INDEX kind_idx ON import_logs(kind) |
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
|
901 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
902 CREATE TABLE track_imports ( |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
903 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
|
904 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
|
905 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
|
906 relation regclass NOT NULL, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
907 key int NOT NULL, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
908 UNIQUE (relation, key) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
909 ) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
910 ; |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
911 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
912 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
|
913 $$ |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
914 DECLARE |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
915 tmp RECORD; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
916 BEGIN |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
917 FOR tmp IN |
4033
4f2f34f5d14d
Schema: delete only import entry that are for deletion.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4031
diff
changeset
|
918 SELECT * FROM import.track_imports WHERE import_id = imp_id AND NOT deletion |
1193
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
919 LOOP |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
920 EXECUTE format('DELETE FROM %s WHERE id = $1', tmp.relation) USING tmp.key; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
921 END LOOP; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
922 END; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
923 $$ |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
924 LANGUAGE plpgsql; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
925 |
2542
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
926 CREATE SCHEMA caching |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
927 |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
928 CREATE TABLE sounding_differences ( |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
929 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
|
930 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
|
931 ON DELETE CASCADE, |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
932 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
|
933 ON DELETE CASCADE, |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
934 UNIQUE (minuend, subtrahend) |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
935 ) |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
936 |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
937 CREATE TABLE sounding_differences_contour_lines ( |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
938 sounding_differences_id int NOT NULL REFERENCES sounding_differences(id) |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
939 ON DELETE CASCADE, |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
940 height numeric NOT NULL, |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
941 lines geography(multilinestring, 4326) NOT NULL, |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
942 PRIMARY KEY (sounding_differences_id, height) |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
943 ) |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
944 ; |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
945 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
946 COMMIT; |