Mercurial > gemma

annotate schema/gemma.sql @ 5666:37c2354a6024 clickable-links

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