Mercurial > gemma
annotate schema/gemma.sql @ 5652:9b3779f649c2 v5.7.1
Bumped version for release.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Mon, 07 Aug 2023 17:59:18 +0200 |
parents | 8797274e2739 |
children | 796196f83678 |
rev | line source |
---|---|
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
1 -- This is Free Software under GNU Affero General Public License v >= 3.0 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
2 -- without warranty, see README.md and license for details. |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
3 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
4 -- SPDX-License-Identifier: AGPL-3.0-or-later |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
5 -- License-Filename: LICENSES/AGPL-3.0.txt |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
6 |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
7 -- Copyright (C) 2018, 2019 by via donau |
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
9 -- Software engineering by Intevation GmbH |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
10 |
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
11 -- Author(s): |
1301
2304778c4432
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1298
diff
changeset
|
12 -- * Tom Gottfried <tom@intevation.de> |
1336
f65d1767452c
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1301
diff
changeset
|
13 -- * Sascha Teichmann <sascha.teichmann@intevation.de> |
2172
7e2c77ccc02f
Removed trigger to update date_info on gauge_measurements.
Sascha Wilde <wilde@intevation.de>
parents:
2170
diff
changeset
|
14 -- * Sascha Wilde <sascha.wilde@intevation.de> |
1298
6590208e3ee1
add headers for licensing to some schema files
Fadi Abbud <fadi.abbud@intevation.de>
parents:
1288
diff
changeset
|
15 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 BEGIN; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
18 -- |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
19 -- Extensions |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
20 -- |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 CREATE EXTENSION postgis; |
2083
6deafd6f7f86
Fix exclusion constraint for waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2076
diff
changeset
|
22 -- needed for multi-column GiST indexes with otherwise unsupported types: |
6deafd6f7f86
Fix exclusion constraint for waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2076
diff
changeset
|
23 CREATE EXTENSION btree_gist; |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
25 -- |
4602
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
26 -- Functions to be used in CHECK constraints |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
27 -- |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
28 |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
29 -- Check if a given string can be used as a FROM item in an SQL statement |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
30 CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
31 AS $$ |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
32 BEGIN |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
33 EXECUTE format('SELECT * FROM (%s) AS test', stmt); |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
34 RETURN true; |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
35 EXCEPTION |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
36 WHEN OTHERS THEN |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
37 RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
38 RETURN false; |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
39 END |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
40 $$ |
4603
d24e951206ca
Do not try to execute NULL as a subquery
Tom Gottfried <tom@intevation.de>
parents:
4602
diff
changeset
|
41 LANGUAGE plpgsql |
d24e951206ca
Do not try to execute NULL as a subquery
Tom Gottfried <tom@intevation.de>
parents:
4602
diff
changeset
|
42 STRICT; |
4602
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
43 |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
44 |
9fef9930aa8a
Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents:
4236
diff
changeset
|
45 -- |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
46 -- Trigger functions |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
47 -- |
96
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
48 -- TODO: will there ever be UPDATEs or can we drop that function due to |
d036e1bd5f00
Add roles, privileges and RLS policies.
Tom Gottfried <tom@intevation.de>
parents:
95
diff
changeset
|
49 -- historicisation? |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 CREATE FUNCTION update_date_info() RETURNS trigger |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 LANGUAGE plpgsql |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
52 AS $$ |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 BEGIN |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 NEW.date_info = CURRENT_TIMESTAMP; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
55 RETURN NEW; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
56 END; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
57 $$; |
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
58 |
4846
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
59 -- |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
60 -- Trigger functions to be used in constraint triggers |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
61 -- |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
62 /* The constraint triggers are subject to a possible race condition |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
63 due to PostgreSQL MVCC: Trigger functions do not see uncommited changes |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
64 of other transactions running in parallel. |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
65 |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
66 Since we serialize imports at application level, the race condition |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
67 should not happen, though. In case serialization at application level |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
68 should be removed, database side locking/serialization would have |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
69 to be applied. |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
70 */ |
c69e35ec6adf
Explaining comment on constraint triggers
Tom Gottfried <tom@intevation.de>
parents:
4755
diff
changeset
|
71 |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
72 -- 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
|
73 -- of geometries in the column with its name given as an argument to the |
5141
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
74 -- trigger function. If additional column names are given as trigger arguments, |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
75 -- the group of given columns is tested for equality, if no operators are |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
76 -- given. Optionally, a column can be compared with a different operator |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
77 -- given after the keyword "WITH" in the trigger argument. |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
78 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
|
79 $$ |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
80 DECLARE |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
81 new_geom geometry; |
5005 | 82 tg_arg text; |
5141
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
83 col varchar; |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
84 opr varchar; |
5005 | 85 filters text; |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
86 has_equal boolean; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
87 BEGIN |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
88 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
|
89 INTO new_geom |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
90 USING NEW; |
5005 | 91 FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP |
92 -- Test each additional argument for equality | |
5141
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
93 -- or with operator given after keyword "WITH" |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
94 tg_arg = lower(tg_arg); |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
95 col = split_part(tg_arg, ' with ', 1); |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
96 opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '='); |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
97 filters = format('%s AND %I %s $2.%2$I', filters, col, opr); |
5005 | 98 END LOOP; |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
99 EXECUTE format( |
5005 | 100 'SELECT EXISTS(SELECT 1 FROM %I.%I ' |
101 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))' | |
102 '%s)', | |
103 TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters) | |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
104 INTO has_equal |
5005 | 105 USING new_geom, NEW; |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
106 IF has_equal THEN |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
107 RAISE EXCEPTION |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
108 'new row for relation "%" violates constraint trigger "%"', |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
109 TG_TABLE_NAME, TG_NAME |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
110 USING |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
111 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
|
112 Box2D(new_geom)), |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
113 ERRCODE = 23505, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
114 SCHEMA = TG_TABLE_SCHEMA, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
115 TABLE = TG_TABLE_NAME, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
116 COLUMN = TG_ARGV[0], |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
117 CONSTRAINT = TG_NAME; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
118 END IF; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
119 RETURN NEW; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
120 END; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
121 $$ |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
122 LANGUAGE plpgsql; |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
123 |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
124 -- 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
|
125 -- 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
|
126 -- 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
|
127 -- 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
|
128 CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
129 $$ |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
130 DECLARE |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
131 -- 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
|
132 -- 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
|
133 -- 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
|
134 referenced_gauge text; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
135 new_validity tstzrange; |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
136 BEGIN |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
137 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
|
138 INTO referenced_gauge |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
139 USING NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
140 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
|
141 INTO new_validity |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
142 USING NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
143 IF EXISTS ( SELECT * FROM waterway.gauges |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
144 WHERE location = referenced_gauge::isrs |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
145 AND validity && new_validity ) |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
146 THEN |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
147 RETURN NEW; |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
148 ELSE |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
149 RAISE EXCEPTION |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
150 '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
|
151 TG_TABLE_NAME, TG_NAME |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
152 USING |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
153 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
|
154 isrs_AsText(referenced_gauge::isrs)), |
4132
ec8438712447
Enable better error handling for referenced gauges/bottlenecks constraints.
Sascha Wilde <wilde@intevation.de>
parents:
4127
diff
changeset
|
155 ERRCODE = 23503, |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
156 SCHEMA = TG_TABLE_SCHEMA, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
157 TABLE = TG_TABLE_NAME, |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
158 COLUMN = TG_ARGV[0], |
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
159 CONSTRAINT = TG_NAME; |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
160 END IF; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
161 END; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
162 $$ |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
163 LANGUAGE plpgsql; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
164 |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
165 -- The same for objects with a timestamp instead of a validity range. |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
166 CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
167 $$ |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
168 DECLARE |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
169 -- FIXME: I'm using text for the isrs code and cast it on demand. |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
170 -- If someone is able to get it to work with isrs or isrs_base as |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
171 -- type, feel free to show me how its done... ;-) [sw] |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
172 referenced_gauge text; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
173 new_tstz timestamptz; |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
174 BEGIN |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
175 EXECUTE format('SELECT $1.%I', TG_ARGV[0]) |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
176 INTO referenced_gauge |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
177 USING NEW; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
178 EXECUTE format('SELECT $1.%I', TG_ARGV[1]) |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
179 INTO new_tstz |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
180 USING NEW; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
181 IF EXISTS ( SELECT * FROM waterway.gauges |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
182 WHERE location = referenced_gauge::isrs |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
183 AND validity @> new_tstz ) |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
184 THEN |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
185 RETURN NEW; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
186 ELSE |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
187 RAISE EXCEPTION |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
188 'new row for relation "%" violates constraint trigger "%"', |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
189 TG_TABLE_NAME, TG_NAME |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
190 USING |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
191 DETAIL = format('No matching gauge %s found.', |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
192 isrs_AsText(referenced_gauge::isrs)), |
4132
ec8438712447
Enable better error handling for referenced gauges/bottlenecks constraints.
Sascha Wilde <wilde@intevation.de>
parents:
4127
diff
changeset
|
193 ERRCODE = 23503, |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
194 SCHEMA = TG_TABLE_SCHEMA, |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
195 TABLE = TG_TABLE_NAME, |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
196 COLUMN = TG_ARGV[0], |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
197 CONSTRAINT = TG_NAME; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
198 END IF; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
199 END; |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
200 $$ |
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
201 LANGUAGE plpgsql; |
3666
db87f34805fb
Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents:
3665
diff
changeset
|
202 |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
203 -- Trigger function to be used as a constraint trigger to enforce |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
204 -- existance of a referenced bottleneck with validity at a given time. |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
205 -- The columns with the referenced bottleneck id and the timestamp are |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
206 -- given as arguments to the trigger function. |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
207 CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
208 $$ |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
209 DECLARE |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
210 referenced_bottleneck_id text; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
211 new_tstz timestamptz; |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
212 BEGIN |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
213 EXECUTE format('SELECT $1.%I', TG_ARGV[0]) |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
214 INTO referenced_bottleneck_id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
215 USING NEW; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
216 EXECUTE format('SELECT $1.%I', TG_ARGV[1]) |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
217 INTO new_tstz |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
218 USING NEW; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
219 IF EXISTS ( SELECT * FROM waterway.bottlenecks |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
220 WHERE bottleneck_id = referenced_bottleneck_id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
221 AND validity @> new_tstz ) |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
222 THEN |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
223 RETURN NEW; |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
224 ELSE |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
225 RAISE EXCEPTION |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
226 'new row for relation "%" violates constraint trigger "%"', |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
227 TG_TABLE_NAME, TG_NAME |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
228 USING |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
229 DETAIL = format('No matching bottleneck %s for %s found.', |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
230 referenced_bottleneck_id, new_tstz), |
4132
ec8438712447
Enable better error handling for referenced gauges/bottlenecks constraints.
Sascha Wilde <wilde@intevation.de>
parents:
4127
diff
changeset
|
231 ERRCODE = 23503, |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
232 SCHEMA = TG_TABLE_SCHEMA, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
233 TABLE = TG_TABLE_NAME, |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
234 COLUMN = TG_ARGV[0], |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
235 CONSTRAINT = TG_NAME; |
3665
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
236 END IF; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
237 END; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
238 $$ |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
239 LANGUAGE plpgsql; |
29ef6d41e4af
Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents:
3656
diff
changeset
|
240 |
3746
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
241 -- Constraint trigger: sounding Results must intersect with the area |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
242 -- of the bottleneck they belong to. The "xx" at the beginning of the |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
243 -- name is to ensure, it is fired last after other triggers. |
3746
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
244 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
|
245 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
|
246 AS $$ |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
247 BEGIN |
4882
566e9ee70d45
Backout changesets 5c12b7cdc58c and 8a4c98b80fbd
Tom Gottfried <tom@intevation.de>
parents:
4873
diff
changeset
|
248 IF NOT st_intersects((SELECT area |
3746
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
249 FROM waterway.bottlenecks |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
250 WHERE bottleneck_id = NEW.bottleneck_id |
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
251 AND validity @> NEW.date_info::timestamptz), |
3746
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
252 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
|
253 THEN |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
254 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
|
255 '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
|
256 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
|
257 USING |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
258 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
|
259 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
|
260 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
|
261 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
|
262 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
|
263 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
|
264 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
|
265 END; |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
266 $$; |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
267 |
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
268 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
269 -- |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
270 -- GEMA meta data |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
271 -- |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
272 CREATE TABLE gemma_schema_version ( |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
273 version int PRIMARY KEY, |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
274 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
|
275 ); |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
276 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
277 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
|
278 LANGUAGE sql |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
279 AS $$ |
4173
d3fb2f37380b
Schema qualify tables in function body
Tom Gottfried <tom@intevation.de>
parents:
4132
diff
changeset
|
280 SELECT max(version) FROM public.gemma_schema_version; |
3978
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
281 $$; |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
282 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
283 |
7b7c324b318f
Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents:
3956
diff
changeset
|
284 -- |
115
d349db18bece
s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
113
diff
changeset
|
285 -- GEMMA data |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
286 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
287 |
81
acaa485c0c1e
Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents:
80
diff
changeset
|
288 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
289 -- 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
|
290 -- |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
291 CREATE TABLE language_codes ( |
182 | 292 language_code varchar PRIMARY KEY |
293 ); | |
84
d905022a48e9
More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents:
83
diff
changeset
|
294 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
295 CREATE TABLE catccls ( |
182 | 296 catccl smallint PRIMARY KEY |
297 -- 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
|
298 -- (see page 328 of edition 2.3) |
182 | 299 ); |
927
48f70782400d
Add categories of CEMT class and directions of impact
Tom Gottfried <tom@intevation.de>
parents:
919
diff
changeset
|
300 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
|
301 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
302 CREATE TABLE dirimps ( |
182 | 303 dirimp smallint PRIMARY KEY |
304 -- 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
|
305 -- (see page 381 of edition 2.3) |
182 | 306 ); |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
307 INSERT INTO dirimps VALUES (1), (2), (3), (4), (5); |
85
1a640da943b6
Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents:
84
diff
changeset
|
308 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
309 CREATE TABLE depth_references ( |
182 | 310 depth_reference varchar(4) PRIMARY KEY |
311 -- See col. AB and AI RIS-Index Encoding Guide | |
312 -- XXX: We need a way to distinguish between geodetic (eg. col. AP | |
313 -- RIS-Index) and other references (e.g. col. AB and AI): | |
314 -- _ multi-column FK with a boolean column (geodetic/non-geodetic; | |
315 -- i.e. absolut/not absolut) and DEFAULTs and CHECKs at the FK side. | |
316 -- _ Do not mixup things with different meanings in one table at all | |
317 -- (which would mean a model differing a bit from RIS-Index ideas) | |
318 ); | |
93 | 319 |
934
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
320 CREATE TABLE catdis ( |
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
321 catdis smallint PRIMARY KEY |
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
322 -- 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
|
323 -- (see page 171 of edition 2.3) |
182 | 324 ); |
934
e6220a19f284
Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents:
930
diff
changeset
|
325 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
|
326 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
327 CREATE TABLE position_codes ( |
182 | 328 position_code char(2) PRIMARY KEY |
329 -- Use smallint because of fairway availability provided on daily basis? | |
330 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx, | |
331 -- sheet "Position_code" or RIS-Index encoding guide? | |
332 -- XXX: DRC 2.1.7 and 2.2.5 _seem_ to reference the same thing here. | |
333 -- Clarify! | |
334 -- TODO: Do we need an attribute "meaning" or so? | |
335 ); | |
87
c46fb3f1faeb
Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents:
86
diff
changeset
|
336 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
337 CREATE TABLE levels_of_service ( |
1662
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
338 level_of_service smallint PRIMARY KEY, |
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
339 name varchar(4) |
182 | 340 ); |
1662
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
341 INSERT INTO levels_of_service ( |
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
342 level_of_service, |
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
343 name |
d8ca44615bfc
Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1642
diff
changeset
|
344 ) 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
|
345 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
346 CREATE TABLE riverbed_materials ( |
182 | 347 material varchar PRIMARY KEY |
348 -- XXX: Should this table contain choices from DRC 2.2.3 or | |
349 -- from IENC Encoding Guide M.4.3, attribute NATSUR? | |
350 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
351 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
352 CREATE TABLE survey_types ( |
182 | 353 survey_type varchar PRIMARY KEY |
354 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
355 |
5402
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
356 INSERT INTO survey_types (survey_type) VALUES ('single'), ('multi'), ('marking'); |
4628
28999c7c0c18
Fill single and multi into survey type table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4623
diff
changeset
|
357 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
358 CREATE TABLE coverage_types ( |
182 | 359 coverage_type varchar PRIMARY KEY |
360 ); | |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
361 |
577
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
362 CREATE TABLE limiting_factors ( |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
363 limiting_factor varchar PRIMARY KEY |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
364 ); |
e6c9d2da2e20
Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents:
569
diff
changeset
|
365 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
366 CREATE TABLE measure_types ( |
182 | 367 measure_type varchar PRIMARY KEY |
368 ); | |
58
30cb2f87c268
Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
56
diff
changeset
|
369 |
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
|
370 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
|
371 '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
|
372 '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
|
373 '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
|
374 ); |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
375 |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
376 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
377 -- Namespace not to be accessed directly by any user |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
378 CREATE SCHEMA internal |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
379 -- Profile data are only accessible via the view users.list_users. |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
380 CREATE TABLE user_profiles ( |
4723
baabc2b2f094
Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
4722
diff
changeset
|
381 username varchar PRIMARY KEY |
baabc2b2f094
Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
4722
diff
changeset
|
382 CHECK(octet_length(username) <= 63) |
baabc2b2f094
Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents:
4722
diff
changeset
|
383 CHECK(to_regrole(quote_ident(username)) IS NOT NULL), |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
384 -- keep username length compatible with role identifier |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
385 country char(2) NOT NULL REFERENCES countries, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
386 map_extent box2d NOT NULL, |
5323
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5144
diff
changeset
|
387 email_address varchar NOT NULL, |
5496
8797274e2739
Added a active column to users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5425
diff
changeset
|
388 report_reciever boolean NOT NULL DEFAULT false, |
8797274e2739
Added a active column to users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5425
diff
changeset
|
389 active boolean NOT NULL DEFAULT true |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
390 ) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
391 ; |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
392 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
393 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
394 -- Namespace to be accessed by sys_admin only |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
395 CREATE SCHEMA sys_admin |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
396 CREATE TABLE system_config ( |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
397 config_key varchar PRIMARY KEY, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
398 config_val varchar |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
399 ) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
400 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
401 CREATE TABLE password_reset_requests ( |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
402 hash varchar(32) PRIMARY KEY, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
403 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
404 username varchar NOT NULL UNIQUE |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
405 REFERENCES internal.user_profiles(username) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
406 ON DELETE CASCADE ON UPDATE CASCADE |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
407 ) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
408 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
409 -- Tables with geo data to be published with GeoServer. |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
410 CREATE TABLE external_services ( |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
411 local_name varchar PRIMARY KEY, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
412 remote_url varchar NOT NULL, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
413 is_wfs boolean NOT NULL DEFAULT TRUE |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
414 ) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
415 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
416 CREATE TABLE published_services ( |
4618
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
417 schema varchar CHECK(to_regnamespace(schema) IS NOT NULL), |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
418 name varchar, |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
419 PRIMARY KEY (schema, name), |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
420 -- SQL statement used for an SQL view in GeoServer: |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
421 view_def text CHECK (is_valid_from_item(view_def)), |
4673
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4648
diff
changeset
|
422 -- Column in output of SQL statement to be used as primary key: |
443867b548b5
Fix identifiers in layers generated from SQL views
Tom Gottfried <tom@intevation.de>
parents:
4648
diff
changeset
|
423 key_column varchar, |
4618
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
424 -- SRID to be used with SQL view: |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
425 srid int REFERENCES spatial_ref_sys, |
4919
399458e60a9b
Geo styles as ZIP: Forgot to change the schema itself.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4913
diff
changeset
|
426 -- SLD style document or ZIP blob: |
399458e60a9b
Geo styles as ZIP: Forgot to change the schema itself.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4913
diff
changeset
|
427 style bytea, |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
428 as_wms boolean NOT NULL DEFAULT TRUE, |
4618
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
429 as_wfs boolean NOT NULL DEFAULT TRUE, |
4995
4a816ecf70de
Added support for WMS-Time in configuring the GeoServer.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4971
diff
changeset
|
430 wmst_attribute varchar DEFAULT NULL, |
4a816ecf70de
Added support for WMS-Time in configuring the GeoServer.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4971
diff
changeset
|
431 wmst_end_attribute varchar DEFAULT NULL, |
4618
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
432 -- Either give a valid relation or a SQL statement: |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
433 CHECK (to_regclass(schema || '.' || name) IS NOT NULL |
0f2c3cb139cc
Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
diff
changeset
|
434 OR view_def IS NOT NULL) |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
435 ) |
4930
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
436 |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
437 CREATE TABLE layer_groups ( |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
438 name varchar PRIMARY KEY |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
439 ) |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
440 |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
441 CREATE TABLE grouped_layers ( |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
442 group_name varchar REFERENCES layer_groups ON DELETE CASCADE, |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
443 schema varchar, |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
444 name varchar, |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
445 ord int NOT NULL DEFAULT 0, |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
446 PRIMARY KEY (group_name, schema, name), |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
447 UNIQUE (group_name, schema, name, ord), |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
448 FOREIGN KEY(schema, name) REFERENCES published_services |
8b83b18a1d49
Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4925
diff
changeset
|
449 ) |
5384
18969a4d31b6
Added a table to store SQL update scripts for aggregated data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5340
diff
changeset
|
450 |
18969a4d31b6
Added a table to store SQL update scripts for aggregated data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5340
diff
changeset
|
451 -- Table to store scripts which updates aggregated data. |
18969a4d31b6
Added a table to store SQL update scripts for aggregated data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5340
diff
changeset
|
452 CREATE TABLE stats_updates ( |
18969a4d31b6
Added a table to store SQL update scripts for aggregated data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5340
diff
changeset
|
453 name varchar PRIMARY key, |
18969a4d31b6
Added a table to store SQL update scripts for aggregated data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5340
diff
changeset
|
454 script TEXT NULL |
18969a4d31b6
Added a table to store SQL update scripts for aggregated data.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5340
diff
changeset
|
455 ) |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
456 ; |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
457 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
458 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
459 -- Namespace for user management related data |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
460 CREATE SCHEMA users |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
461 CREATE TABLE stretches ( |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
462 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
4718
92640ae5be07
Backed out changeset 9279fdb7a422
Tom Gottfried <tom@intevation.de>
parents:
4717
diff
changeset
|
463 name varchar NOT NULL, |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
464 stretch isrsrange NOT NULL, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
465 area geography(MULTIPOLYGON, 4326) NOT NULL |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
466 CHECK(ST_IsValid(CAST(area AS geometry))), |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
467 objnam varchar NOT NULL, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
468 nobjnam varchar, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
469 date_info timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
470 source_organization varchar NOT NULL, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
471 staging_done boolean NOT NULL DEFAULT false, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
472 UNIQUE(name, staging_done) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
473 ) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
474 CREATE TRIGGER stretches_date_info |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
475 BEFORE UPDATE ON stretches |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
476 FOR EACH ROW EXECUTE PROCEDURE update_date_info() |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
477 |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
478 CREATE TABLE stretch_countries ( |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
479 stretch_id int NOT NULL REFERENCES stretches(id) |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
480 ON DELETE CASCADE, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
481 country char(2) NOT NULL REFERENCES countries, |
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
482 PRIMARY KEY(stretch_id, country) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
483 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
484 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
485 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
|
486 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
|
487 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
|
488 country char(2) REFERENCES countries, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
489 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
|
490 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
|
491 UNIQUE (template_name, template_type, country) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
492 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
493 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
|
494 FOR EACH ROW EXECUTE PROCEDURE update_date_info() |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
495 |
4755
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4749
diff
changeset
|
496 CREATE VIEW list_users WITH (security_barrier) AS |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
497 SELECT |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
498 r.rolname, |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
499 p.username, |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
500 CAST('' AS varchar) AS pw, |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
501 p.country, |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
502 p.map_extent, |
5323
eec88a166251
Added report_reciever column to user_profiles table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5144
diff
changeset
|
503 p.email_address, |
5496
8797274e2739
Added a active column to users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5425
diff
changeset
|
504 p.report_reciever, |
8797274e2739
Added a active column to users.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5425
diff
changeset
|
505 p.active |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
506 FROM internal.user_profiles p |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
507 JOIN pg_roles u ON p.username = u.rolname |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
508 JOIN pg_auth_members a ON u.oid = a.member |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
509 JOIN pg_roles r ON a.roleid = r.oid |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
510 WHERE p.username = current_user |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
511 OR pg_has_role('waterway_admin', 'MEMBER') |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
512 AND p.country = ( |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
513 SELECT country FROM internal.user_profiles |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
514 WHERE username = current_user) |
4755
dfd990a4ac64
Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents:
4749
diff
changeset
|
515 AND r.rolname <> 'sys_admin' |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
516 OR pg_has_role('sys_admin', 'MEMBER') |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
517 ; |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
518 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
519 |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
520 -- |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
521 -- Functions to be used in DEFAULT expresions |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
522 -- |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
523 |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
524 -- Return current_user's country code |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
525 CREATE FUNCTION users.user_country(user_name name DEFAULT current_user) |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
526 RETURNS internal.user_profiles.country%TYPE |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
527 AS $$ |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
528 SELECT country FROM users.list_users |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
529 WHERE username = user_name |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
530 $$ |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
531 LANGUAGE SQL |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
532 STABLE PARALLEL SAFE; |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
533 |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
534 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
535 -- 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
|
536 CREATE SCHEMA waterway |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
537 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
538 -- Eventually obsolete. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
539 -- 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
|
540 -- CREATE TABLE rwdrs ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
541 -- tretch isrsrange PRIMARY KEY, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
542 -- -- 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
|
543 -- -- 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
|
544 -- -- below anyhow. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
545 -- -- 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
|
546 -- -- 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
|
547 -- -- => 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
|
548 -- -- factor, this might be an issue. |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
549 -- rwdr double precision NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
550 -- EXCLUDE USING GIST (stretch WITH &&) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
551 --) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
552 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
553 CREATE TABLE waterway_area ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
554 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
|
555 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
|
556 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
|
557 catccl smallint REFERENCES catccls, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
558 dirimp smallint REFERENCES dirimps |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
559 ) |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
560 CREATE CONSTRAINT TRIGGER waterway_area_area_unique |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
561 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
|
562 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
|
563 |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
564 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
565 CREATE TABLE gauges ( |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
566 location isrs CHECK( |
579
642df1164aca
Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents:
577
diff
changeset
|
567 (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
|
568 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
|
569 objname varchar NOT NULL, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
570 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
|
571 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
|
572 applicability_to_km int8, |
3648
0ec5c8ec1e44
Avoid empty validity time ranges
Tom Gottfried <tom@intevation.de>
parents:
3647
diff
changeset
|
573 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
|
574 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
|
575 geodref varchar, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
576 date_info timestamp with time zone NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
577 source_organization varchar, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
578 lastupdate timestamp with time zone NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
579 -- entry removed from external data source (RIS-Index)/historicised: |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
580 erased boolean NOT NULL DEFAULT false, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
581 PRIMARY KEY (location, validity), |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
582 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
|
583 DEFERRABLE INITIALLY DEFERRED |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
584 ) |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
585 -- Allow only one non-erased entry per location |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
586 CREATE UNIQUE INDEX gauges_erased_unique_constraint |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
587 ON gauges (location) |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
588 WHERE NOT erased |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
589 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
590 CREATE TABLE gauges_reference_water_levels ( |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
591 location isrs NOT NULL, |
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
592 validity tstzrange NOT NULL, |
3402
c04b1409a596
Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents:
3389
diff
changeset
|
593 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
|
594 -- 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
|
595 -- names, too: |
2317
8a8680e70d2e
Cleanup schema for reference water levels
Tom Gottfried <tom@intevation.de>
parents:
2300
diff
changeset
|
596 depth_reference varchar NOT NULL, -- REFERENCES depth_references, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
597 PRIMARY KEY (location, validity, depth_reference), |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
598 value int NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
599 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
600 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
601 CREATE TABLE gauge_measurements ( |
1636
37ee25bc2bbe
Updated database schema for gauge meaurement imports.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1583
diff
changeset
|
602 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
603 location isrs NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
604 measure_date timestamp with time zone NOT NULL, |
1636
37ee25bc2bbe
Updated database schema for gauge meaurement imports.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1583
diff
changeset
|
605 country_code char(2) NOT NULL REFERENCES countries, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
606 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
|
607 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
|
608 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
|
609 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
|
610 water_level double precision NOT NULL, |
3387
d9eda49a52f4
Remove obsolete DEFAULT values
Tom Gottfried <tom@intevation.de>
parents:
3307
diff
changeset
|
611 date_info timestamp with time zone NOT NULL, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
612 source_organization varchar NOT NULL, -- "originator" from NtS response |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
613 staging_done boolean NOT NULL DEFAULT false, |
4016
9ab7e1056360
Optimize index setup a bit
Tom Gottfried <tom@intevation.de>
parents:
4006
diff
changeset
|
614 UNIQUE (measure_date, location, staging_done) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
615 ) |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
616 CREATE CONSTRAINT TRIGGER waterway_gauge_measurements_reference_gauge |
4068
76482935b6e5
Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
617 AFTER INSERT OR UPDATE OF location ON gauge_measurements |
76482935b6e5
Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
618 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
619 |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
620 CREATE TABLE gauge_predictions ( |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
621 location isrs NOT NULL, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
622 measure_date timestamp with time zone NOT NULL, |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
623 country_code char(2) NOT NULL REFERENCES countries, |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
624 sender varchar NOT NULL, -- "from" element from NtS response |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
625 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
|
626 date_issue timestamp with time zone NOT NULL, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
627 reference_code varchar(4) NOT NULL REFERENCES depth_references, |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
628 water_level double precision NOT NULL, |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
629 conf_interval numrange |
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
630 CHECK (conf_interval @> CAST(water_level AS numeric)), |
3387
d9eda49a52f4
Remove obsolete DEFAULT values
Tom Gottfried <tom@intevation.de>
parents:
3307
diff
changeset
|
631 date_info timestamp with time zone NOT NULL, |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
632 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
|
633 PRIMARY KEY (measure_date, location, date_issue) |
3277
232fc90e6ee2
Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents:
3217
diff
changeset
|
634 ) |
4045
12e3933b2050
Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents:
4044
diff
changeset
|
635 CREATE CONSTRAINT TRIGGER waterway_gauge_predictions_reference_gauge |
4068
76482935b6e5
Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
636 AFTER INSERT OR UPDATE OF location ON gauge_predictions |
76482935b6e5
Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
637 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge_ts('location','measure_date') |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
638 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
639 CREATE TABLE waterway_axis ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
640 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
5009
e8b2dc771f9e
Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents:
5005
diff
changeset
|
641 wtwaxs 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
|
642 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
|
643 -- 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
|
644 objnam varchar NOT NULL, |
5016
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
645 nobjnam varchar, |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
646 validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
647 CHECK (NOT isempty(validity)), |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
648 -- Last time an import job found this entry in a data source: |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
649 last_found 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
|
650 ) |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
2770
diff
changeset
|
651 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique |
5016
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
652 AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis |
5141
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
653 DEFERRABLE INITIALLY DEFERRED |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
654 FOR EACH ROW |
722b7c305319
Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
5129
diff
changeset
|
655 EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&') |
5016
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
656 CREATE INDEX waterway_axis_validity |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
657 ON waterway_axis USING GiST (validity) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
658 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
659 -- 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
|
660 -- 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
|
661 -- 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
|
662 CREATE TABLE distance_marks_virtual ( |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
663 location_code isrs PRIMARY KEY, |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
664 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
|
665 related_enc varchar(12) NOT NULL, |
1b11ff97c1d1
Add wwname to import of virtual distance marks.
Sascha Wilde <wilde@intevation.de>
parents:
3747
diff
changeset
|
666 wwname varchar |
948
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
667 ) |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
668 |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
669 CREATE TABLE distance_marks ( |
1861
5083a1d19a4b
Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1831
diff
changeset
|
670 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
|
671 country char(2) REFERENCES countries, |
5083a1d19a4b
Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1831
diff
changeset
|
672 hectom int, |
948
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
673 geom geography(POINT, 4326) NOT NULL, |
5f89868bd75e
Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents:
944
diff
changeset
|
674 -- 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
|
675 catdis smallint REFERENCES catdis, |
5083a1d19a4b
Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents:
1831
diff
changeset
|
676 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
|
677 related_enc varchar(12) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
678 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
679 |
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
|
680 -- 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
|
681 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
|
682 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
4718
92640ae5be07
Backed out changeset 9279fdb7a422
Tom Gottfried <tom@intevation.de>
parents:
4717
diff
changeset
|
683 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
|
684 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
|
685 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
|
686 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
|
687 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
|
688 nobjnam varchar, |
4740
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
689 country char(2) NOT NULL REFERENCES countries |
2440d2f86f4e
Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents:
4723
diff
changeset
|
690 DEFAULT users.user_country(), |
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
|
691 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
|
692 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
|
693 staging_done boolean NOT NULL DEFAULT false, |
4749
fd9f171b87e4
Generally disallow the same section name for different countries
Tom Gottfried <tom@intevation.de>
parents:
4748
diff
changeset
|
694 -- Disallow the same name for different countries |
fd9f171b87e4
Generally disallow the same section name for different countries
Tom Gottfried <tom@intevation.de>
parents:
4748
diff
changeset
|
695 EXCLUDE USING GiST (name WITH =, country WITH <>), |
fd9f171b87e4
Generally disallow the same section name for different countries
Tom Gottfried <tom@intevation.de>
parents:
4748
diff
changeset
|
696 -- Allow the same name one time in and outside staging area, each |
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
|
697 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
|
698 ) |
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
|
699 CREATE TRIGGER sections_date_info |
4389
5e38667f740c
Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents:
4358
diff
changeset
|
700 BEFORE UPDATE ON sections |
3171
c8ded555c2a8
Sections import: Added a sections import. Derived from the stretches import w/o the countries.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
3008
diff
changeset
|
701 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
|
702 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
703 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
|
704 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
|
705 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
|
706 geom geography(linestring, 4326), |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
707 validity tstzrange, |
2072
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
708 lnwl double precision, |
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
709 mwl double precision, |
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
710 hnwl double precision, |
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
711 fe30 double precision, |
b4d8d320feab
Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2064
diff
changeset
|
712 fe100 double precision, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
713 -- XXX: further normalise using reference_water_levels? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
714 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
|
715 OR validity IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
716 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
|
717 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
|
718 staging_done boolean NOT NULL DEFAULT false, |
2130
f3aabc05f9b2
Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2128
diff
changeset
|
719 EXCLUDE USING GIST ( |
f3aabc05f9b2
Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2128
diff
changeset
|
720 isrs_asText(location) WITH =, |
f3aabc05f9b2
Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2128
diff
changeset
|
721 validity WITH &&, |
f3aabc05f9b2
Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents:
2128
diff
changeset
|
722 CAST(staging_done AS int) WITH =) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
723 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
724 CREATE TRIGGER waterway_profiles_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
725 BEFORE UPDATE ON waterway_profiles |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
726 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
|
727 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
728 CREATE TABLE fairway_dimensions ( |
383
84597b9da68e
Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents:
371
diff
changeset
|
729 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
5030
737d7859dd86
Store fairway dimensions as MultiPolygon
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
730 area geography(MULTIPOLYGON, 4326) NOT NULL |
5031
8c590ef35280
Improve feedback if geometry cannot be stored
Tom Gottfried <tom@intevation.de>
parents:
5030
diff
changeset
|
731 CHECK(ST_IsValid(CAST(area AS geometry)) |
8c590ef35280
Improve feedback if geometry cannot be stored
Tom Gottfried <tom@intevation.de>
parents:
5030
diff
changeset
|
732 AND NOT ST_IsEmpty(CAST(area AS geometry))), |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
733 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
|
734 min_width smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
735 max_width smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
736 min_depth smallint NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
737 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
|
738 source_organization varchar NOT NULL, |
5144
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
739 staging_done boolean NOT NULL DEFAULT false, |
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
740 validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) |
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
741 CHECK (NOT isempty(validity)) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
742 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
743 CREATE TRIGGER fairway_dimensions_date_info |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
744 BEFORE UPDATE ON fairway_dimensions |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
745 FOR EACH ROW EXECUTE PROCEDURE update_date_info() |
5095
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5031
diff
changeset
|
746 CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique |
5144
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
747 AFTER INSERT OR UPDATE OF area, validity, staging_done |
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
748 ON fairway_dimensions |
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
749 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals( |
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
750 'area', 'validity WITH &&', 'staging_done') |
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
751 CREATE INDEX fairway_dimensions_validity |
f11b9b50fcc9
Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
5141
diff
changeset
|
752 ON fairway_dimensions USING GiST (validity) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
753 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
754 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
755 -- Bottlenecks |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
756 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
757 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
|
758 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3624
diff
changeset
|
759 bottleneck_id varchar NOT NULL, |
3648
0ec5c8ec1e44
Avoid empty validity time ranges
Tom Gottfried <tom@intevation.de>
parents:
3647
diff
changeset
|
760 validity tstzrange NOT NULL CHECK (NOT isempty(validity)), |
3302
ec6163c6687d
'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents:
3278
diff
changeset
|
761 gauge_location isrs NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
762 objnam varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
763 nobjnm varchar, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
764 stretch isrsrange NOT NULL, |
1984
48001472e1d8
Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents:
1983
diff
changeset
|
765 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
|
766 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
|
767 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
|
768 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
|
769 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
|
770 revisiting_time smallint, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
771 limiting varchar NOT NULL REFERENCES limiting_factors, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
772 -- surtyp varchar NOT NULL REFERENCES survey_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
773 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
774 -- coverage varchar REFERENCES coverage_types, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
775 -- XXX: Also an attribut of sounding result? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
776 -- 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
|
777 -- different model approach? |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
778 -- 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
|
779 -- XXX: Also an attribut of sounding result? |
3645
02951a62e8c6
'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents:
3624
diff
changeset
|
780 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
|
781 source_organization varchar NOT NULL, |
4111
692aba3e8b85
Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents:
4106
diff
changeset
|
782 staging_done boolean NOT NULL DEFAULT false, |
692aba3e8b85
Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents:
4106
diff
changeset
|
783 UNIQUE (bottleneck_id, validity, staging_done), |
692aba3e8b85
Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents:
4106
diff
changeset
|
784 EXCLUDE USING GiST (bottleneck_id WITH =, |
692aba3e8b85
Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents:
4106
diff
changeset
|
785 validity WITH &&, |
692aba3e8b85
Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents:
4106
diff
changeset
|
786 CAST(staging_done AS int) WITH =) |
692aba3e8b85
Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents:
4106
diff
changeset
|
787 DEFERRABLE INITIALLY DEFERRED |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
788 ) |
4041
3fcb95a07948
WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents:
4016
diff
changeset
|
789 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
|
790 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
|
791 FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity') |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
792 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
793 CREATE TABLE bottlenecks_riverbed_materials ( |
2999
b3c3c5b5b7c1
Bottleneck import: Import riverbed materials, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2867
diff
changeset
|
794 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
|
795 ON DELETE CASCADE, |
2076
0e006077bbfa
Add missing NOT NULL constraints
Tom Gottfried <tom@intevation.de>
parents:
2072
diff
changeset
|
796 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
|
797 -- 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
|
798 PRIMARY KEY (bottleneck_id, riverbed) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
799 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
800 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
801 CREATE TABLE sounding_results ( |
656
9ef2f80a4645
Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents:
655
diff
changeset
|
802 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
|
803 bottleneck_id varchar NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
804 date_info date NOT NULL, |
656
9ef2f80a4645
Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents:
655
diff
changeset
|
805 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
|
806 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
|
807 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
|
808 surtyp varchar REFERENCES survey_types, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
809 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
|
810 depth_reference varchar NOT NULL, -- REFERENCES depth_references, |
5304
6cf4fdaa93f9
Added new ZPG exception column to sounding result table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5144
diff
changeset
|
811 zpg_exception bool NOT NULL DEFAULT FALSE, -- Found no LDC in ZPG case. |
4648
66fcd898efd9
Started with conversion tool.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4628
diff
changeset
|
812 mesh_checksum varchar, |
66fcd898efd9
Started with conversion tool.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4628
diff
changeset
|
813 mesh_index bytea, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
814 staging_done boolean NOT NULL DEFAULT false |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
815 ) |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
816 CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck |
4068
76482935b6e5
Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
817 AFTER INSERT OR UPDATE OF bottleneck_id ON sounding_results |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
818 FOR EACH ROW |
4068
76482935b6e5
Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents:
4067
diff
changeset
|
819 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') |
4047
8c6bc85db711
WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4045
diff
changeset
|
820 CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area |
3746
433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents:
3666
diff
changeset
|
821 AFTER INSERT OR UPDATE ON sounding_results |
3747
ad67e4286d65
Fixed typo in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents:
3746
diff
changeset
|
822 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
|
823 |
4561
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
824 CREATE TABLE sounding_results_iso_areas ( |
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
825 sounding_result_id int NOT NULL REFERENCES sounding_results |
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
826 ON DELETE CASCADE, |
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
827 height numeric NOT NULL, |
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
828 areas geography(MULTIPOLYGON, 4326) NOT NULL, |
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
829 -- TODO: generate valid simple features and add constraint: |
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
830 -- CHECK(ST_IsSimple(CAST(areas AS geometry))), |
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
831 PRIMARY KEY (sounding_result_id, height) |
f7b57136c800
Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4461
diff
changeset
|
832 ) |
5402
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
833 |
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
834 CREATE TABLE sounding_results_marking_points ( |
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
835 sounding_result_id int NOT NULL REFERENCES sounding_results |
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
836 ON DELETE CASCADE, |
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
837 height numeric NOT NULL, |
5425
345515bc4548
Make GeoServer layer sounding_results_marking_points_geoserver working.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5422
diff
changeset
|
838 -- XXX: GeoServer does not like geography(MULTIPOINTZ) |
345515bc4548
Make GeoServer layer sounding_results_marking_points_geoserver working.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5422
diff
changeset
|
839 -- We need to track this down. Maybe with |
345515bc4548
Make GeoServer layer sounding_results_marking_points_geoserver working.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5422
diff
changeset
|
840 -- GeoServer upstream. |
5422
ad8e3fffb868
schema: geography supports MULTIPOINTZ as modifier.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5417
diff
changeset
|
841 points geography(MULTIPOINTZ, 4326) NOT NULL, |
5402
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
842 PRIMARY KEY (sounding_result_id, height) |
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
843 ) |
f5063fa7f666
Add schema change for marking vessel single beam scans.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5395
diff
changeset
|
844 |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
845 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
846 -- Fairway availability |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
847 -- |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
848 CREATE TABLE fairway_availability ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
849 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
4461
7128741faeb9
Changed fa data to accept "position" as strings, not a 2char "position_code"
Sascha Wilde <wilde@intevation.de>
parents:
4449
diff
changeset
|
850 position varchar, |
4067
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
851 bottleneck_id varchar NOT NULL, |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
852 surdat date NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
853 UNIQUE (bottleneck_id, surdat), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
854 -- 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
|
855 critical boolean, |
4057
b79b60c0cc5a
date_info has to be provided by the data source
Tom Gottfried <tom@intevation.de>
parents:
4033
diff
changeset
|
856 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
|
857 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
858 ) |
4067
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
859 -- FIXME: From the DRC it is unclear what the exact semantics of |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
860 -- surdat and Date_Info ar unclear. Currently we assume that |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
861 -- (fk_bn_fid,surdat) has to be unique, but that might be false. |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
862 -- Anyway, I will date_info here to check for an matching |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
863 -- reference gauge at the bottleneck. The reason for this |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
864 -- decision is purely practical (and might be semantically |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
865 -- disputable: the bottleneck data in the demo system is not old |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
866 -- enough to cover rthe surdat times... |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
867 CREATE CONSTRAINT TRIGGER fairway_availability_referenced_bottleneck |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
868 AFTER INSERT OR UPDATE OF bottleneck_id ON fairway_availability |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
869 FOR EACH ROW |
0ba3fc89b499
Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
4047
diff
changeset
|
870 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info') |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
871 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
872 CREATE TABLE fa_reference_values ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
873 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
|
874 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
|
875 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
|
876 fairway_depth smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
877 fairway_width smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
878 fairway_radius int, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
879 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
|
880 IS NOT NULL), |
370
fe87457a05d7
Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents:
368
diff
changeset
|
881 shallowest_spot geography(POINT, 4326) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
882 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
883 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
884 CREATE TABLE bottleneck_pdfs ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
885 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
|
886 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
|
887 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
|
888 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
|
889 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
|
890 source_organization varchar NOT NULL |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
891 ) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
892 |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
893 CREATE TABLE effective_fairway_availability ( |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
894 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
|
895 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
|
896 level_of_service smallint NOT NULL REFERENCES levels_of_service, |
4859
082027fb2d58
Allow "Measured" and "Forecast" values to coexist for the same time and gauge.
Sascha Wilde <wilde@intevation.de>
parents:
4846
diff
changeset
|
897 PRIMARY KEY (fairway_availability_id, measure_date, level_of_service, |
082027fb2d58
Allow "Measured" and "Forecast" values to coexist for the same time and gauge.
Sascha Wilde <wilde@intevation.de>
parents:
4846
diff
changeset
|
898 measure_type), |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
899 available_depth_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
900 available_width_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
901 water_level_value smallint, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
902 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
|
903 water_level_value) IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
904 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
|
905 source_organization varchar NOT NULL, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
906 forecast_generation_time timestamp with time zone, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
907 CHECK(measure_type <> 'forecasted' |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
908 OR forecast_generation_time IS NOT NULL), |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
909 value_lifetime timestamp with time zone, |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
910 CHECK(measure_type = 'minimum guaranteed' |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
911 OR value_lifetime IS NOT NULL) |
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
912 ) |
4894
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
913 |
4909
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
914 -- Attributes common to all fairway marks |
4894
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
915 CREATE TABLE fairway_marks ( |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
916 validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
917 CHECK (NOT isempty(validity)), |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
918 -- Last time an import job found this entry in a data source: |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
919 last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, |
4894
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
920 geom geography(POINT, 4326) NOT NULL, |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
921 -- Attributes according to IENC Feature Catalogue: |
4894
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
922 datsta varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
923 datend varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
924 persta varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
925 perend varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
926 objnam varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
927 nobjnm varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
928 inform varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
929 ninfom varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
930 scamin int, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
931 picrep varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
932 txtdsc varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
933 sordat varchar, |
4909
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
934 sorind varchar |
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
935 ) |
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
936 |
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
937 -- Additional attributes for IENC features BCNLAT/bcnlat |
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
938 CREATE TABLE fairway_marks_bcnlat ( |
4894
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
939 colour varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
940 colpat varchar, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
941 condtn int, |
8eb36d0d5bdf
Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents:
4859
diff
changeset
|
942 bcnshp int, |
4912
bfd8ef836998
Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents:
4909
diff
changeset
|
943 catlam int |
4909
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
944 ) INHERITS (fairway_marks) |
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
945 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
946 CREATE TABLE fairway_marks_bcnlat_hydro ( |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
947 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
948 ) INHERITS (fairway_marks_bcnlat) |
4895
9f799077a3e6
Prevent importing non-distinct fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4894
diff
changeset
|
949 -- Prevent identical entries using composite type comparison |
9f799077a3e6
Prevent importing non-distinct fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4894
diff
changeset
|
950 -- (i.e. considering two NULL values in a field equal): |
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
951 CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows |
4965 | 952 ON fairway_marks_bcnlat_hydro |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
953 ((CAST((validity, last_found, geom, |
4909
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
954 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
6f244b5eb716
Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents:
4897
diff
changeset
|
955 scamin, picrep, txtdsc, sordat, sorind, |
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
956 colour, colpat, condtn, bcnshp, catlam, 0 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
957 ) AS fairway_marks_bcnlat_hydro) |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
958 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
959 CREATE INDEX fairway_marks_bcnlat_hydro_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
960 ON fairway_marks_bcnlat_hydro USING GiST (validity) |
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
961 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
962 CREATE TABLE fairway_marks_bcnlat_ienc ( |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
963 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
964 ) INHERITS (fairway_marks_bcnlat) |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
965 -- Prevent identical entries using composite type comparison |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
966 -- (i.e. considering two NULL values in a field equal): |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
967 CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows |
4965 | 968 ON fairway_marks_bcnlat_ienc |
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
969 ((CAST((validity, last_found, geom, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
970 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
971 scamin, picrep, txtdsc, sordat, sorind, |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
972 colour, colpat, condtn, bcnshp, catlam, 0 |
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
973 ) AS fairway_marks_bcnlat_ienc) |
4895
9f799077a3e6
Prevent importing non-distinct fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4894
diff
changeset
|
974 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
975 CREATE INDEX fairway_marks_bcnlat_ienc_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
976 ON fairway_marks_bcnlat_ienc USING GiST (validity) |
4912
bfd8ef836998
Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents:
4909
diff
changeset
|
977 |
bfd8ef836998
Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents:
4909
diff
changeset
|
978 CREATE TABLE fairway_marks_bcnlat_dirimps ( |
4962
1b309a8e7673
Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents:
4956
diff
changeset
|
979 fm_bcnlat_id int REFERENCES fairway_marks_bcnlat_ienc, |
4912
bfd8ef836998
Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents:
4909
diff
changeset
|
980 dirimp smallint REFERENCES dirimps, |
bfd8ef836998
Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents:
4909
diff
changeset
|
981 PRIMARY KEY (fm_bcnlat_id, dirimp) |
bfd8ef836998
Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents:
4909
diff
changeset
|
982 ) |
4913
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
983 |
4922
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
984 -- Additional attributes for IENC feature BOYCAR |
4913
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
985 CREATE TABLE fairway_marks_boycar ( |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
986 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
987 colour varchar, |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
988 colpat varchar, |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
989 conrad int, |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
990 marsys int, |
4925
b86ce7fc4da3
Add missing BOYCAR attribute
Tom Gottfried <tom@intevation.de>
parents:
4922
diff
changeset
|
991 boyshp int, |
b86ce7fc4da3
Add missing BOYCAR attribute
Tom Gottfried <tom@intevation.de>
parents:
4922
diff
changeset
|
992 catcam int |
4913
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
993 ) INHERITS (fairway_marks) |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
994 -- Prevent identical entries using composite type comparison |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
995 -- (i.e. considering two NULL values in a field equal): |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
996 CREATE UNIQUE INDEX fairway_marks_boycar_distinct_rows |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
997 ON fairway_marks_boycar |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
998 ((CAST((validity, last_found, geom, |
4913
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
999 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
1000 scamin, picrep, txtdsc, sordat, sorind, |
4925
b86ce7fc4da3
Add missing BOYCAR attribute
Tom Gottfried <tom@intevation.de>
parents:
4922
diff
changeset
|
1001 0, colour, colpat, conrad, marsys, boyshp, catcam |
4913
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
1002 ) AS fairway_marks_boycar) |
8c1a3d5e3962
Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents:
4912
diff
changeset
|
1003 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1004 CREATE INDEX fairway_marks_boycar_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1005 ON fairway_marks_boycar USING GiST (validity) |
4922
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1006 |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1007 -- Additional attributes for IENC feature BOYLAT |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1008 CREATE TABLE fairway_marks_boylat ( |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1009 colour varchar, |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1010 colpat varchar, |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1011 conrad int, |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1012 marsys int, |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1013 boyshp int, |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1014 catlam int |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1015 ) INHERITS (fairway_marks) |
4964
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1016 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1017 CREATE TABLE fairway_marks_boylat_hydro ( |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1018 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1019 ) INHERITS (fairway_marks_boylat) |
4922
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1020 -- Prevent identical entries using composite type comparison |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1021 -- (i.e. considering two NULL values in a field equal): |
4964
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1022 CREATE UNIQUE INDEX fairway_marks_boylat_hydro_distinct_rows |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1023 ON fairway_marks_boylat_hydro |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
1024 ((CAST((validity, last_found, geom, |
4922
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1025 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1026 scamin, picrep, txtdsc, sordat, sorind, |
4964
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1027 colour, colpat, conrad, marsys, boyshp, catlam, 0 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1028 ) AS fairway_marks_boylat_hydro) |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1029 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1030 CREATE INDEX fairway_marks_boylat_hydro_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1031 ON fairway_marks_boylat_hydro USING GiST (validity) |
4964
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1032 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1033 CREATE TABLE fairway_marks_boylat_ienc ( |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1034 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1035 ) INHERITS (fairway_marks_boylat) |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1036 -- Prevent identical entries using composite type comparison |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1037 -- (i.e. considering two NULL values in a field equal): |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1038 CREATE UNIQUE INDEX fairway_marks_boylat_ienc_distinct_rows |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1039 ON fairway_marks_boylat_ienc |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1040 ((CAST((validity, last_found, geom, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1041 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1042 scamin, picrep, txtdsc, sordat, sorind, |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1043 colour, colpat, conrad, marsys, boyshp, catlam, 0 |
58dc06e91c39
Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4962
diff
changeset
|
1044 ) AS fairway_marks_boylat_ienc) |
4922
9bd6a0ca63ea
Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents:
4919
diff
changeset
|
1045 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1046 CREATE INDEX fairway_marks_boylat_ienc_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1047 ON fairway_marks_boylat_ienc USING GiST (validity) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1048 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1049 -- Additional attributes for IENC feature BOYSAW |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1050 CREATE TABLE fairway_marks_boysaw ( |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1051 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1052 colour varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1053 colpat varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1054 conrad int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1055 marsys int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1056 boyshp int |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1057 ) INHERITS (fairway_marks) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1058 -- Prevent identical entries using composite type comparison |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1059 -- (i.e. considering two NULL values in a field equal): |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1060 CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1061 ON fairway_marks_boysaw |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
1062 ((CAST((validity, last_found, geom, |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1063 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1064 scamin, picrep, txtdsc, sordat, sorind, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1065 0, colour, colpat, conrad, marsys, boyshp |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1066 ) AS fairway_marks_boysaw) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1067 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1068 CREATE INDEX fairway_marks_boysaw_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1069 ON fairway_marks_boysaw USING GiST (validity) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1070 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1071 -- Additional attributes for IENC feature BOYSPP |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1072 CREATE TABLE fairway_marks_boyspp ( |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1073 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1074 colour varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1075 colpat varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1076 conrad int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1077 marsys int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1078 boyshp int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1079 catspm varchar |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1080 ) INHERITS (fairway_marks) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1081 -- Prevent identical entries using composite type comparison |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1082 -- (i.e. considering two NULL values in a field equal): |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1083 CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1084 ON fairway_marks_boyspp |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
1085 ((CAST((validity, last_found, geom, |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1086 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1087 scamin, picrep, txtdsc, sordat, sorind, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1088 0, colour, colpat, conrad, marsys, boyshp, catspm |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1089 ) AS fairway_marks_boyspp) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1090 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1091 CREATE INDEX fairway_marks_boyspp_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1092 ON fairway_marks_boyspp USING GiST (validity) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1093 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1094 -- Additional attributes for IENC features DAYMAR/daymar |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1095 CREATE TABLE fairway_marks_daymar ( |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1096 colour varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1097 colpat varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1098 condtn int, |
4967
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1099 topshp int |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1100 ) INHERITS (fairway_marks) |
4967
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1101 |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1102 CREATE TABLE fairway_marks_daymar_hydro ( |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1103 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1104 ) INHERITS (fairway_marks_daymar) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1105 -- Prevent identical entries using composite type comparison |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1106 -- (i.e. considering two NULL values in a field equal): |
4967
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1107 CREATE UNIQUE INDEX fairway_marks_daymar_hydro_distinct_rows |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1108 ON fairway_marks_daymar_hydro |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
1109 ((CAST((validity, last_found, geom, |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1110 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1111 scamin, picrep, txtdsc, sordat, sorind, |
4967
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1112 colour, colpat, condtn, topshp, 0 |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1113 ) AS fairway_marks_daymar_hydro) |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1114 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1115 CREATE INDEX fairway_marks_daymar_hydro_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1116 ON fairway_marks_daymar_hydro USING GiST (validity) |
4967
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1117 |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1118 CREATE TABLE fairway_marks_daymar_ienc ( |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1119 orient double precision, |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1120 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1121 ) INHERITS (fairway_marks_daymar) |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1122 -- Prevent identical entries using composite type comparison |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1123 -- (i.e. considering two NULL values in a field equal): |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1124 CREATE UNIQUE INDEX fairway_marks_daymar_ienc_distinct_rows |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1125 ON fairway_marks_daymar_ienc |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1126 ((CAST((validity, last_found, geom, |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1127 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1128 scamin, picrep, txtdsc, sordat, sorind, |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1129 colour, colpat, condtn, topshp, orient, 0 |
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1130 ) AS fairway_marks_daymar_ienc) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1131 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1132 CREATE INDEX fairway_marks_daymar_ienc_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1133 ON fairway_marks_daymar_ienc USING GiST (validity) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1134 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1135 CREATE TABLE fairway_marks_daymar_dirimps ( |
4967
3f704ebad0c5
Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents:
4965
diff
changeset
|
1136 fm_daymar_id int REFERENCES fairway_marks_daymar_ienc, |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1137 dirimp smallint REFERENCES dirimps, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1138 PRIMARY KEY (fm_daymar_id, dirimp) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1139 ) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1140 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1141 -- Additional attributes for IENC feature LIGHTS |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1142 CREATE TABLE fairway_marks_lights ( |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1143 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1144 colour varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1145 condtn int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1146 orient double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1147 catlit varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1148 exclit int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1149 litchr int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1150 litvis varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1151 mltylt int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1152 sectr1 double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1153 sectr2 double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1154 siggrp varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1155 sigper double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1156 sigseq varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1157 status varchar |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1158 ) INHERITS (fairway_marks) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1159 -- Prevent identical entries using composite type comparison |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1160 -- (i.e. considering two NULL values in a field equal): |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1161 CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1162 ON fairway_marks_lights |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
1163 ((CAST((validity, last_found, geom, |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1164 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1165 scamin, picrep, txtdsc, sordat, sorind, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1166 0, colour, condtn, orient, catlit, exclit, litchr, litvis, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1167 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1168 ) AS fairway_marks_lights) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1169 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1170 CREATE INDEX fairway_marks_lights_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1171 ON fairway_marks_lights USING GiST (validity) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1172 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1173 -- Additional attributes for IENC feature RTPBCN |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1174 CREATE TABLE fairway_marks_rtpbcn ( |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1175 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1176 condtn int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1177 siggrp varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1178 catrtb int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1179 radwal varchar |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1180 ) INHERITS (fairway_marks) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1181 -- Prevent identical entries using composite type comparison |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1182 -- (i.e. considering two NULL values in a field equal): |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1183 CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1184 ON fairway_marks_rtpbcn |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
1185 ((CAST((validity, last_found, geom, |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1186 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1187 scamin, picrep, txtdsc, sordat, sorind, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1188 0, condtn, siggrp, catrtb, radwal |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1189 ) AS fairway_marks_rtpbcn) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1190 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1191 CREATE INDEX fairway_marks_rtpbcn_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1192 ON fairway_marks_rtpbcn USING GiST (validity) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1193 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1194 -- Additional attributes for IENC feature TOPMAR |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1195 CREATE TABLE fairway_marks_topmar ( |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1196 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1197 colour varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1198 colpat varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1199 condtn int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1200 topshp int |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1201 ) INHERITS (fairway_marks) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1202 -- Prevent identical entries using composite type comparison |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1203 -- (i.e. considering two NULL values in a field equal): |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1204 CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1205 ON fairway_marks_topmar |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
1206 ((CAST((validity, last_found, geom, |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1207 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1208 scamin, picrep, txtdsc, sordat, sorind, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1209 0, colour, colpat, condtn, topshp |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1210 ) AS fairway_marks_topmar) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1211 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1212 CREATE INDEX fairway_marks_topmar_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1213 ON fairway_marks_topmar USING GiST (validity) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1214 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1215 -- Additional attributes for IENC feature NOTMRK |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1216 CREATE TABLE fairway_marks_notmrk ( |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1217 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1218 condtn int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1219 marsys int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1220 orient double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1221 status varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1222 addmrk varchar, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1223 catnmk int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1224 disipd double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1225 disipu double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1226 disbk1 double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1227 disbk2 double precision, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1228 fnctnm int, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1229 bnkwtw int |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1230 ) INHERITS (fairway_marks) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1231 -- Prevent identical entries using composite type comparison |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1232 -- (i.e. considering two NULL values in a field equal): |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1233 CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1234 ON fairway_marks_notmrk |
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
4940
diff
changeset
|
1235 ((CAST((validity, last_found, geom, |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1236 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1237 scamin, picrep, txtdsc, sordat, sorind, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1238 0, condtn, marsys, orient, status, addmrk, catnmk, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1239 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1240 ) AS fairway_marks_notmrk) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1241 )) |
4971
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1242 CREATE INDEX fairway_marks_notmrk_validity |
de190de05f67
Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents:
4967
diff
changeset
|
1243 ON fairway_marks_notmrk USING GiST (validity) |
4940
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1244 |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1245 CREATE TABLE fairway_marks_notmrk_dirimps ( |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1246 fm_notmrk_id int REFERENCES fairway_marks_notmrk, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1247 dirimp smallint REFERENCES dirimps, |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1248 PRIMARY KEY (fm_notmrk_id, dirimp) |
b3b2ba09a450
Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents:
4930
diff
changeset
|
1249 ) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1250 ; |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1251 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1252 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1253 -- |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1254 -- Import queue and respective logging |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1255 -- |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1256 CREATE TYPE import_state AS ENUM ( |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1257 'queued', |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1258 'running', |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1259 'failed', 'unchanged', 'pending', |
5096
f64ff954ee31
Added new reviewed state.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
5031
diff
changeset
|
1260 'accepted', 'declined', 'reviewed' |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1261 ); |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1262 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1263 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
|
1264 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1265 -- 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
|
1266 CREATE SCHEMA import |
1547
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
1267 |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
1268 CREATE TABLE import_configuration ( |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
1269 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
|
1270 username varchar NOT NULL |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
1271 REFERENCES internal.user_profiles(username) |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
1272 ON DELETE CASCADE |
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
1273 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
|
1274 kind varchar NOT NULL |
1547
d4b7a6d054cd
Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1520
diff
changeset
|
1275 ) |
1702
49b89575ab31
Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1684
diff
changeset
|
1276 |
49b89575ab31
Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1684
diff
changeset
|
1277 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
|
1278 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
|
1279 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
|
1280 ON DELETE CASCADE |
49b89575ab31
Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1684
diff
changeset
|
1281 ON UPDATE CASCADE, |
49b89575ab31
Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1684
diff
changeset
|
1282 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
|
1283 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
|
1284 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
|
1285 ) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1286 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1287 CREATE TABLE imports ( |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1288 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
|
1289 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
|
1290 kind varchar NOT NULL, |
4098
cfa0a5775d70
Reapplied timezone patch to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4094
diff
changeset
|
1291 enqueued timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
4748
47922c1a088d
Added a 'changed' column to the import.imports table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4740
diff
changeset
|
1292 changed timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
4098
cfa0a5775d70
Reapplied timezone patch to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4094
diff
changeset
|
1293 due timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1294 retry_wait interval |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1295 CHECK(retry_wait IS NULL |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1296 OR retry_wait >= interval '0 microseconds'), |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1297 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
|
1298 username varchar NOT NULL |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1299 REFERENCES internal.user_profiles(username) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1300 ON DELETE CASCADE |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1301 ON UPDATE CASCADE, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1302 signer varchar |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1303 REFERENCES internal.user_profiles(username) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1304 ON DELETE SET NULL |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1305 ON UPDATE CASCADE, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1306 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
|
1307 data TEXT, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1308 summary TEXT |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1309 ) |
5122
0b6b62d247e8
Prioritize review jobs on selection
Tom Gottfried <tom@intevation.de>
parents:
5096
diff
changeset
|
1310 -- Mainly for listing imports in clients: |
0b6b62d247e8
Prioritize review jobs on selection
Tom Gottfried <tom@intevation.de>
parents:
5096
diff
changeset
|
1311 CREATE INDEX enqueued_idx ON imports(enqueued) |
0b6b62d247e8
Prioritize review jobs on selection
Tom Gottfried <tom@intevation.de>
parents:
5096
diff
changeset
|
1312 -- For fast retrieval of queued imports by the import queue in backend: |
0b6b62d247e8
Prioritize review jobs on selection
Tom Gottfried <tom@intevation.de>
parents:
5096
diff
changeset
|
1313 CREATE INDEX state_idx ON imports(state) |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1314 |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1315 CREATE TABLE import_logs ( |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1316 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
|
1317 ON DELETE CASCADE, |
4100
cc3d607b49cc
Merged default into timezone branch.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
diff
changeset
|
1318 time timestamp with time zone NOT NULL DEFAULT CURRENT_TIMESTAMP, |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1319 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
|
1320 msg TEXT NOT NULL |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1321 ) |
4187
65a5501dc13d
Enable faster access to import logs
Tom Gottfried <tom@intevation.de>
parents:
4173
diff
changeset
|
1322 CREATE INDEX import_logs_import_id ON import.import_logs (import_id) |
2627
3a242e6aa56d
Import log: Add filter for log entries with warnings only: GET /api/imports?warnings=true
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2589
diff
changeset
|
1323 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1324 CREATE TABLE track_imports ( |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1325 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
|
1326 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
|
1327 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
|
1328 relation regclass NOT NULL, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1329 key int NOT NULL, |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1330 UNIQUE (relation, key) |
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1331 ) |
195
5dc8e734487a
Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents:
184
diff
changeset
|
1332 ; |
56
f378959820be
Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents:
55
diff
changeset
|
1333 |
1995
59055c8301df
Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents:
1986
diff
changeset
|
1334 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
|
1335 $$ |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1336 DECLARE |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1337 tmp RECORD; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1338 BEGIN |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1339 FOR tmp IN |
4126
52f7264265bb
Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
4106
diff
changeset
|
1340 SELECT relation, array_agg(key) AS keys |
52f7264265bb
Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
4106
diff
changeset
|
1341 FROM import.track_imports |
52f7264265bb
Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
4106
diff
changeset
|
1342 WHERE import_id = imp_id AND NOT deletion |
52f7264265bb
Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
4106
diff
changeset
|
1343 GROUP BY relation |
1193
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1344 LOOP |
4126
52f7264265bb
Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
4106
diff
changeset
|
1345 EXECUTE format('DELETE FROM %s WHERE id = ANY($1)', tmp.relation) |
52f7264265bb
Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
4106
diff
changeset
|
1346 USING tmp.keys; |
1193
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1347 END LOOP; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1348 END; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1349 $$ |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1350 LANGUAGE plpgsql; |
58acc343b1b6
Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
1190
diff
changeset
|
1351 |
2542
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1352 CREATE SCHEMA caching |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1353 |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1354 CREATE TABLE sounding_differences ( |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1355 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
|
1356 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
|
1357 ON DELETE CASCADE, |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1358 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
|
1359 ON DELETE CASCADE, |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1360 UNIQUE (minuend, subtrahend) |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1361 ) |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1362 |
4573
26e9846ed69f
Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4561
diff
changeset
|
1363 CREATE TABLE sounding_differences_iso_areas ( |
26e9846ed69f
Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4561
diff
changeset
|
1364 sounding_differences_id int NOT NULL REFERENCES sounding_differences(id) |
26e9846ed69f
Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4561
diff
changeset
|
1365 ON DELETE CASCADE, |
26e9846ed69f
Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4561
diff
changeset
|
1366 height numeric NOT NULL, |
26e9846ed69f
Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4561
diff
changeset
|
1367 areas geography(MULTIPOLYGON, 4326) NOT NULL, |
26e9846ed69f
Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4561
diff
changeset
|
1368 PRIMARY KEY (sounding_differences_id, height) |
26e9846ed69f
Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
4561
diff
changeset
|
1369 ) |
2542
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1370 ; |
fc7d828695c9
Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents:
2529
diff
changeset
|
1371 |
35
62e14b4d25fc
First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1372 COMMIT; |