Mercurial > gemma

annotate schema/gemma.sql @ 5122:0b6b62d247e8 queued-stage-done

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Prioritize review jobs on selection This reverts rev. 37784b70eea3 and instead moves review jobs forward in the queue when fetching the next job to be run. Also optimized index setup for filtering by state but not enqueued.
author Tom Gottfried <tom@intevation.de>
date Thu, 26 Mar 2020 14:41:23 +0100
parents f64ff954ee31
children 882b3d2308c4
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
5005
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
74 -- trigger function. If additional column names are given,
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
75 -- the group of given columns is tested for equality.
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
76 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
77 $$
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
78 DECLARE
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
79 new_geom geometry;
5005
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
80 tg_arg text;
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
81 filters text;
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
82 has_equal boolean;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
83 BEGIN
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
84 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
85 INTO new_geom
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
86 USING NEW;
5005
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
87 FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
88 -- Test each additional argument for equality
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
89 filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg);
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
90 END LOOP;
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
91 EXECUTE format(
5005
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
92 'SELECT EXISTS(SELECT 1 FROM %I.%I '
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
93 '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
94 '%s)',
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
95 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
96 INTO has_equal
5005
f697a398eff4 Amend trigger function
Tom Gottfried <tom@intevation.de>
parents: 4995
diff changeset
97 USING new_geom, NEW;
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
98 IF has_equal THEN
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
99 RAISE EXCEPTION
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
100 'new row for relation "%" violates constraint trigger "%"',
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
101 TG_TABLE_NAME, TG_NAME
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
102 USING
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
103 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
104 Box2D(new_geom)),
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
105 ERRCODE = 23505,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
106 SCHEMA = TG_TABLE_SCHEMA,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
107 TABLE = TG_TABLE_NAME,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
108 COLUMN = TG_ARGV[0],
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
109 CONSTRAINT = TG_NAME;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
110 END IF;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
111 RETURN NEW;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
112 END;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
113 $$
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
114 LANGUAGE plpgsql;
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
115
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
116 -- 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
117 -- 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
118 -- 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
119 -- 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
120 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
121 $$
3666
db87f34805fb Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents: 3665
diff changeset
122 DECLARE
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
123 -- 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
124 -- 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
125 -- 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
126 referenced_gauge text;
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
127 new_validity tstzrange;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
128 BEGIN
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
129 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
130 INTO referenced_gauge
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
131 USING NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
132 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
133 INTO new_validity
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
134 USING NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
135 IF EXISTS ( SELECT * FROM waterway.gauges
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
136 WHERE location = referenced_gauge::isrs
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
137 AND validity && new_validity )
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
138 THEN
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
139 RETURN NEW;
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
140 ELSE
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
141 RAISE EXCEPTION
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
142 '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
143 TG_TABLE_NAME, TG_NAME
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
144 USING
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
145 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
146 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
147 ERRCODE = 23503,
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
148 SCHEMA = TG_TABLE_SCHEMA,
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
149 TABLE = TG_TABLE_NAME,
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
150 COLUMN = TG_ARGV[0],
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
151 CONSTRAINT = TG_NAME;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
152 END IF;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
153 END;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
154 $$
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
155 LANGUAGE plpgsql;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
156
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
157 -- 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
158 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
159 $$
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
160 DECLARE
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
161 -- 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
162 -- 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
163 -- 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
164 referenced_gauge text;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
165 new_tstz timestamptz;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
166 BEGIN
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
167 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
168 INTO referenced_gauge
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
169 USING NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
170 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
171 INTO new_tstz
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
172 USING NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
173 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
174 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
175 AND validity @> new_tstz )
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
176 THEN
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
177 RETURN NEW;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
178 ELSE
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
179 RAISE EXCEPTION
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
180 '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
181 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
182 USING
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
183 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
184 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
185 ERRCODE = 23503,
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
186 SCHEMA = TG_TABLE_SCHEMA,
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
187 TABLE = TG_TABLE_NAME,
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
188 COLUMN = TG_ARGV[0],
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
189 CONSTRAINT = TG_NAME;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
190 END IF;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
191 END;
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
192 $$
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
193 LANGUAGE plpgsql;
3666
db87f34805fb Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents: 3665
diff changeset
194
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
195 -- 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
196 -- 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
197 -- 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
198 -- 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
199 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
200 $$
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
201 DECLARE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
202 referenced_bottleneck_id text;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
203 new_tstz timestamptz;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
204 BEGIN
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
205 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
206 INTO referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
207 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
208 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
209 INTO new_tstz
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
210 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
211 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
212 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
213 AND validity @> new_tstz )
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
214 THEN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
215 RETURN NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
216 ELSE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
217 RAISE EXCEPTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
218 '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
219 TG_TABLE_NAME, TG_NAME
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
220 USING
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
221 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
222 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
223 ERRCODE = 23503,
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
224 SCHEMA = TG_TABLE_SCHEMA,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
225 TABLE = TG_TABLE_NAME,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
226 COLUMN = TG_ARGV[0],
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
227 CONSTRAINT = TG_NAME;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
228 END IF;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
229 END;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
230 $$
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
231 LANGUAGE plpgsql;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 3656
diff changeset
232
3746
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
233 -- Constraint 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
234 -- 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
235 -- 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
236 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
237 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
238 AS $$
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
239 BEGIN
4882
566e9ee70d45 Backout changesets 5c12b7cdc58c and 8a4c98b80fbd
Tom Gottfried <tom@intevation.de>
parents: 4873
diff changeset
240 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
241 FROM waterway.bottlenecks
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
242 WHERE bottleneck_id = NEW.bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
243 AND validity @> NEW.date_info::timestamptz),
3746
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
244 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
245 THEN
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
246 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
247 '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
248 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
249 USING
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
250 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
251 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
252 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
253 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
254 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
255 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
256 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
257 END;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
258 $$;
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
259
433bad131e5c Added check trigger to sounding results to ensure they intersect the bn-area.
Sascha Wilde <wilde@intevation.de>
parents: 3666
diff changeset
260
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
261 --
3978
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
262 -- GEMA meta data
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
263 --
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
264 CREATE TABLE gemma_schema_version (
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
265 version int PRIMARY KEY,
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
266 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
267 );
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
268
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
269 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
270 LANGUAGE sql
3978
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
271 AS $$
4173
d3fb2f37380b Schema qualify tables in function body
Tom Gottfried <tom@intevation.de>
parents: 4132
diff changeset
272 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
273 $$;
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
274
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
275
7b7c324b318f Added database schema versioning and db update script.
Sascha Wilde <wilde@intevation.de>
parents: 3956
diff changeset
276 --
115
d349db18bece s/wamos/gemma/g on database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 113
diff changeset
277 -- GEMMA data
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
278 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
279
81
acaa485c0c1e Use different schemas for different diagrams. Add TODOs.
Tom Gottfried <tom@intevation.de>
parents: 80
diff changeset
280 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
281 -- 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
282 --
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
283 CREATE TABLE language_codes (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
284 language_code varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
285 );
84
d905022a48e9 More user attributes from APUC3.
Tom Gottfried <tom@intevation.de>
parents: 83
diff changeset
286
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
287 CREATE TABLE catccls (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
288 catccl smallint PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
289 -- 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
290 -- (see page 328 of edition 2.3)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
291 );
927
48f70782400d Add categories of CEMT class and directions of impact
Tom Gottfried <tom@intevation.de>
parents: 919
diff changeset
292 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
293
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
294 CREATE TABLE dirimps (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
295 dirimp smallint PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
296 -- 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
297 -- (see page 381 of edition 2.3)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
298 );
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
299 INSERT INTO dirimps VALUES (1), (2), (3), (4), (5);
85
1a640da943b6 Add waterway area attributes.
Tom Gottfried <tom@intevation.de>
parents: 84
diff changeset
300
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
301 CREATE TABLE depth_references (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
302 depth_reference varchar(4) PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
303 -- See col. AB and AI RIS-Index Encoding Guide
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
304 -- XXX: We need a way to distinguish between geodetic (eg. col. AP
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
305 -- RIS-Index) and other references (e.g. col. AB and AI):
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
306 -- _ multi-column FK with a boolean column (geodetic/non-geodetic;
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
307 -- 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
308 -- _ Do not mixup things with different meanings in one table at all
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
309 -- (which would mean a model differing a bit from RIS-Index ideas)
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
310 );
93
765906789840 Add gauge attributes.
Tom Gottfried <tom@intevation.de>
parents: 92
diff changeset
311
934
e6220a19f284 Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents: 930
diff changeset
312 CREATE TABLE catdis (
e6220a19f284 Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents: 930
diff changeset
313 catdis smallint PRIMARY KEY
e6220a19f284 Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents: 930
diff changeset
314 -- 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
315 -- (see page 171 of edition 2.3)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
316 );
934
e6220a19f284 Add categories of distance marks
Tom Gottfried <tom@intevation.de>
parents: 930
diff changeset
317 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
318
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
319 CREATE TABLE position_codes (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
320 position_code char(2) PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
321 -- Use smallint because of fairway availability provided on daily basis?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
322 -- Data come from 2015_06_23_RIS_Index_template_v2p0.xlsx,
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
323 -- sheet "Position_code" or RIS-Index encoding guide?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
324 -- 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
325 -- Clarify!
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
326 -- TODO: Do we need an attribute "meaning" or so?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
327 );
87
c46fb3f1faeb Add distance mark attributes and try to consolidate.
Tom Gottfried <tom@intevation.de>
parents: 86
diff changeset
328
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
329 CREATE TABLE levels_of_service (
1662
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
330 level_of_service smallint PRIMARY KEY,
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
331 name varchar(4)
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
332 );
1662
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
333 INSERT INTO levels_of_service (
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
334 level_of_service,
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
335 name
d8ca44615bfc Implemented first version of fairway availability import.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1642
diff changeset
336 ) 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
337
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
338 CREATE TABLE riverbed_materials (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
339 material varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
340 -- XXX: Should this table contain choices from DRC 2.2.3 or
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
341 -- from IENC Encoding Guide M.4.3, attribute NATSUR?
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
342 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
343
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
344 CREATE TABLE survey_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
345 survey_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
346 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
347
4628
28999c7c0c18 Fill single and multi into survey type table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4623
diff changeset
348 INSERT INTO survey_types (survey_type) VALUES ('single'), ('multi');
28999c7c0c18 Fill single and multi into survey type table.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4623
diff changeset
349
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
350 CREATE TABLE coverage_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
351 coverage_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
352 );
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
353
577
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
354 CREATE TABLE limiting_factors (
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
355 limiting_factor varchar PRIMARY KEY
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
356 );
e6c9d2da2e20 Backed out changeset f3452ce5c056
Tom Gottfried <tom@intevation.de>
parents: 569
diff changeset
357
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
358 CREATE TABLE measure_types (
182
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
359 measure_type varchar PRIMARY KEY
4df4e4bf480e Beautify SQL
Tom Gottfried <tom@intevation.de>
parents: 177
diff changeset
360 );
58
30cb2f87c268 Add effective fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 56
diff changeset
361
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
362 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
363 '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
364 '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
365 '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
366 );
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
367
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
368
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
369 -- 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
370 CREATE SCHEMA internal
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
371 -- 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
372 CREATE TABLE user_profiles (
4723
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4722
diff changeset
373 username varchar PRIMARY KEY
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4722
diff changeset
374 CHECK(octet_length(username) <= 63)
baabc2b2f094 Avoid creating user profiles without matching role
Tom Gottfried <tom@intevation.de>
parents: 4722
diff changeset
375 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
376 -- keep username length compatible with role identifier
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
377 country char(2) NOT NULL REFERENCES countries,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
378 map_extent box2d NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
379 email_address varchar NOT NULL
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
380 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
381 ;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
382
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
383
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
384 -- Namespace to be accessed by sys_admin only
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
385 CREATE SCHEMA sys_admin
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
386 CREATE TABLE system_config (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
387 config_key varchar PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
388 config_val varchar
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
389 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
390
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
391 CREATE TABLE password_reset_requests (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
392 hash varchar(32) PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
393 issued timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
394 username varchar NOT NULL UNIQUE
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
395 REFERENCES internal.user_profiles(username)
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
396 ON DELETE CASCADE ON UPDATE CASCADE
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
397 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
398
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
399 -- 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
400 CREATE TABLE external_services (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
401 local_name varchar PRIMARY KEY,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
402 remote_url varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
403 is_wfs boolean NOT NULL DEFAULT TRUE
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
404 )
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
405
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
406 CREATE TABLE published_services (
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
407 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
408 name varchar,
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
409 PRIMARY KEY (schema, name),
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
410 -- 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
411 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
412 -- 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
413 key_column varchar,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
414 -- SRID to be used with SQL view:
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
415 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
416 -- 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
417 style bytea,
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
418 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
419 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
420 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
421 wmst_end_attribute varchar DEFAULT NULL,
4618
0f2c3cb139cc Merge default into geoserver_sql_views
Tom Gottfried <tom@intevation.de>
parents: 4614 4449
diff changeset
422 -- 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
423 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
424 OR view_def IS NOT NULL)
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
425 )
4930
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
426
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
427 CREATE TABLE layer_groups (
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
428 name varchar PRIMARY KEY
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
429 )
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
430
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
431 CREATE TABLE grouped_layers (
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
432 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
433 schema varchar,
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
434 name varchar,
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
435 ord int NOT NULL DEFAULT 0,
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
436 PRIMARY KEY (group_name, schema, name),
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
437 UNIQUE (group_name, schema, name, ord),
8b83b18a1d49 Added database models for group layers.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4925
diff changeset
438 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
439 )
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
440 ;
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
441
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
442
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
443 -- Namespace for user management related data
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
444 CREATE SCHEMA users
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
445 CREATE TABLE stretches (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
446 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4718
92640ae5be07 Backed out changeset 9279fdb7a422
Tom Gottfried <tom@intevation.de>
parents: 4717
diff changeset
447 name varchar NOT NULL,
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
448 stretch isrsrange NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
449 area geography(MULTIPOLYGON, 4326) NOT NULL
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
450 CHECK(ST_IsValid(CAST(area AS geometry))),
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
451 objnam varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
452 nobjnam varchar,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
453 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
454 source_organization varchar NOT NULL,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
455 staging_done boolean NOT NULL DEFAULT false,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
456 UNIQUE(name, staging_done)
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 CREATE TRIGGER stretches_date_info
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
459 BEFORE UPDATE ON stretches
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
460 FOR EACH ROW EXECUTE PROCEDURE update_date_info()
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
461
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
462 CREATE TABLE stretch_countries (
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
463 stretch_id int NOT NULL REFERENCES stretches(id)
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
464 ON DELETE CASCADE,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
465 country char(2) NOT NULL REFERENCES countries,
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
466 PRIMARY KEY(stretch_id, country)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
467 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
468
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
469 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
470 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
471 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
472 country char(2) REFERENCES countries,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
473 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
474 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
475 UNIQUE (template_name, template_type, country)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
476 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
477 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
478 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
479
4755
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents: 4749
diff changeset
480 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
481 SELECT
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
482 r.rolname,
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
483 p.username,
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
484 CAST('' AS varchar) AS pw,
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
485 p.country,
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
486 p.map_extent,
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
487 p.email_address
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
488 FROM internal.user_profiles p
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
489 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
490 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
491 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
492 WHERE p.username = current_user
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
493 OR pg_has_role('waterway_admin', 'MEMBER')
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
494 AND p.country = (
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
495 SELECT country FROM internal.user_profiles
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
496 WHERE username = current_user)
4755
dfd990a4ac64 Hide sys_admin accounts to waterway admins
Tom Gottfried <tom@intevation.de>
parents: 4749
diff changeset
497 AND r.rolname <> 'sys_admin'
4740
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
498 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
499 ;
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
500
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
501
4740
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
502 --
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
503 -- Functions to be used in DEFAULT expresions
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
504 --
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
505
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
506 -- Return current_user's country code
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
507 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
508 RETURNS internal.user_profiles.country%TYPE
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
509 AS $$
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
510 SELECT country FROM users.list_users
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
511 WHERE username = user_name
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
512 $$
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
513 LANGUAGE SQL
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
514 STABLE PARALLEL SAFE;
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
515
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
516
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
517 -- 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
518 CREATE SCHEMA waterway
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
519
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
520 -- Eventually obsolete.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
521 -- 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
522 -- CREATE TABLE rwdrs (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
523 -- tretch isrsrange PRIMARY KEY,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
524 -- -- 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
525 -- -- 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
526 -- -- below anyhow.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
527 -- -- 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
528 -- -- 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
529 -- -- => 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
530 -- -- factor, this might be an issue.
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
531 -- rwdr double precision NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
532 -- EXCLUDE USING GIST (stretch WITH &&)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
533 --)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
534
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
535 CREATE TABLE waterway_area (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
536 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
537 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
538 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
539 catccl smallint REFERENCES catccls,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
540 dirimp smallint REFERENCES dirimps
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
541 )
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
542 CREATE CONSTRAINT TRIGGER waterway_area_area_unique
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
543 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
544 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
545
56
f378959820be Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 55
diff changeset
546
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
547 CREATE TABLE gauges (
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
548 location isrs CHECK(
579
642df1164aca Ensure gauges are identified by appropriate ISRS location code
Tom Gottfried <tom@intevation.de>
parents: 577
diff changeset
549 (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
550 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
551 objname varchar NOT NULL,
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
552 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
553 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
554 applicability_to_km int8,
3648
0ec5c8ec1e44 Avoid empty validity time ranges
Tom Gottfried <tom@intevation.de>
parents: 3647
diff changeset
555 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
556 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
557 geodref varchar,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
558 date_info timestamp with time zone NOT NULL,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
559 source_organization varchar,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
560 lastupdate timestamp with time zone NOT NULL,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
561 -- entry removed from external data source (RIS-Index)/historicised:
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
562 erased boolean NOT NULL DEFAULT false,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
563 PRIMARY KEY (location, validity),
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
564 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
565 DEFERRABLE INITIALLY DEFERRED
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
566 )
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
567 -- Allow only one non-erased entry per location
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
568 CREATE UNIQUE INDEX gauges_erased_unique_constraint
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
569 ON gauges (location)
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
570 WHERE NOT erased
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
571
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
572 CREATE TABLE gauges_reference_water_levels (
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
573 location isrs NOT NULL,
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
574 validity tstzrange NOT NULL,
3402
c04b1409a596 Fix adaptation of gauge temporal validity
Tom Gottfried <tom@intevation.de>
parents: 3389
diff changeset
575 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
576 -- 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
577 -- names, too:
2317
8a8680e70d2e Cleanup schema for reference water levels
Tom Gottfried <tom@intevation.de>
parents: 2300
diff changeset
578 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
579 PRIMARY KEY (location, validity, depth_reference),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
580 value int NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
581 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
582
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
583 CREATE TABLE gauge_measurements (
1636
37ee25bc2bbe Updated database schema for gauge meaurement imports.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1583
diff changeset
584 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
585 location isrs NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
586 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
587 country_code char(2) NOT NULL REFERENCES countries,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
588 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
589 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
590 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
591 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
592 water_level double precision NOT NULL,
3387
d9eda49a52f4 Remove obsolete DEFAULT values
Tom Gottfried <tom@intevation.de>
parents: 3307
diff changeset
593 date_info timestamp with time zone NOT NULL,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
594 source_organization varchar NOT NULL, -- "originator" from NtS response
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
595 staging_done boolean NOT NULL DEFAULT false,
4016
9ab7e1056360 Optimize index setup a bit
Tom Gottfried <tom@intevation.de>
parents: 4006
diff changeset
596 UNIQUE (measure_date, location, staging_done)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
597 )
4045
12e3933b2050 Added trigger constraints to ensure matching gauges for gauge measurements.
Sascha Wilde <wilde@intevation.de>
parents: 4044
diff changeset
598 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
599 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
600 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
601
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
602 CREATE TABLE gauge_predictions (
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
603 location isrs NOT NULL,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
604 measure_date timestamp with time zone NOT NULL,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
605 country_code char(2) NOT NULL REFERENCES countries,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
606 sender varchar NOT NULL, -- "from" element from NtS response
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
607 language_code varchar NOT NULL REFERENCES language_codes,
3389
45a629a3a8b8 Fix constraints on relationship between gauges and measurements/predictions
Tom Gottfried <tom@intevation.de>
parents: 3387
diff changeset
608 date_issue timestamp with time zone NOT NULL,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
609 reference_code varchar(4) NOT NULL REFERENCES depth_references,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
610 water_level double precision NOT NULL,
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
611 conf_interval numrange
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
612 CHECK (conf_interval @> CAST(water_level AS numeric)),
3387
d9eda49a52f4 Remove obsolete DEFAULT values
Tom Gottfried <tom@intevation.de>
parents: 3307
diff changeset
613 date_info timestamp with time zone NOT NULL,
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
diff changeset
614 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
615 PRIMARY KEY (measure_date, location, date_issue)
3277
232fc90e6ee2 Disentangle gauge measurements and predictions
Tom Gottfried <tom@intevation.de>
parents: 3217
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_predictions_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_predictions
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')
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
620
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
621 CREATE TABLE waterway_axis (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
622 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
5009
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 5005
diff changeset
623 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
624 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
625 -- 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
626 objnam varchar NOT NULL,
5016
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
627 nobjnam varchar,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
628 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
629 CHECK (NOT isempty(validity)),
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
630 -- 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
631 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
632 )
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents: 2770
diff changeset
633 CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
5016
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
634 AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
635 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity')
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
636 CREATE INDEX waterway_axis_validity
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
637 ON waterway_axis USING GiST (validity)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
638
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
639 -- 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
640 -- 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
641 -- 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
642 CREATE TABLE distance_marks_virtual (
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
643 location_code isrs PRIMARY KEY,
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
644 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
645 related_enc varchar(12) NOT NULL,
1b11ff97c1d1 Add wwname to import of virtual distance marks.
Sascha Wilde <wilde@intevation.de>
parents: 3747
diff changeset
646 wwname varchar
948
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
647 )
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
648
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
649 CREATE TABLE distance_marks (
1861
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
650 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
651 country char(2) REFERENCES countries,
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
652 hectom int,
948
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
653 geom geography(POINT, 4326) NOT NULL,
5f89868bd75e Store virtual and physical distance marks separately
Tom Gottfried <tom@intevation.de>
parents: 944
diff changeset
654 -- 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
655 catdis smallint REFERENCES catdis,
5083a1d19a4b Adjusted database schema for distance marks importer.
Raimund Renkert <raimund.renkert@intevation.de>
parents: 1831
diff changeset
656 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
657 related_enc varchar(12)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
658 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
659
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
660 -- 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
661 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
662 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
4718
92640ae5be07 Backed out changeset 9279fdb7a422
Tom Gottfried <tom@intevation.de>
parents: 4717
diff changeset
663 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
664 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
665 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
666 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
667 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
668 nobjnam varchar,
4740
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
669 country char(2) NOT NULL REFERENCES countries
2440d2f86f4e Authorize sections based on country of creator
Tom Gottfried <tom@intevation.de>
parents: 4723
diff changeset
670 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
671 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
672 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
673 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
674 -- 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
675 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
676 -- 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
677 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
678 )
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
679 CREATE TRIGGER sections_date_info
4389
5e38667f740c Use stretches as areas of responsibility.
Sascha Wilde <wilde@intevation.de>
parents: 4358
diff changeset
680 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
681 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
682
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
683 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
684 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
685 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
686 geom geography(linestring, 4326),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
687 validity tstzrange,
2072
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
688 lnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
689 mwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
690 hnwl double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
691 fe30 double precision,
b4d8d320feab Waterway profile import: Added import stub and changed database.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2064
diff changeset
692 fe100 double precision,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
693 -- XXX: further normalise using reference_water_levels?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
694 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
695 OR validity IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
696 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
697 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
698 staging_done boolean NOT NULL DEFAULT false,
2130
f3aabc05f9b2 Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2128
diff changeset
699 EXCLUDE USING GIST (
f3aabc05f9b2 Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2128
diff changeset
700 isrs_asText(location) WITH =,
f3aabc05f9b2 Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2128
diff changeset
701 validity WITH &&,
f3aabc05f9b2 Fix constraints on waterway profiles
Tom Gottfried <tom@intevation.de>
parents: 2128
diff changeset
702 CAST(staging_done AS int) WITH =)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
703 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
704 CREATE TRIGGER waterway_profiles_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
705 BEFORE UPDATE ON waterway_profiles
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
706 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
707
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
708 CREATE TABLE fairway_dimensions (
383
84597b9da68e Let all tables have a PRIMARY KEY again
Tom Gottfried <tom@intevation.de>
parents: 371
diff changeset
709 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
710 area geography(MULTIPOLYGON, 4326) NOT NULL
5031
8c590ef35280 Improve feedback if geometry cannot be stored
Tom Gottfried <tom@intevation.de>
parents: 5030
diff changeset
711 CHECK(ST_IsValid(CAST(area AS geometry))
8c590ef35280 Improve feedback if geometry cannot be stored
Tom Gottfried <tom@intevation.de>
parents: 5030
diff changeset
712 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
713 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
714 min_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
715 max_width smallint NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
716 min_depth smallint 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,
b97b3172c61a Add staging feature to more tables
Tom Gottfried <tom@intevation.de>
parents: 323
diff changeset
719 staging_done boolean NOT NULL DEFAULT false
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
720 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
721 CREATE TRIGGER fairway_dimensions_date_info
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
722 BEFORE UPDATE ON fairway_dimensions
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
723 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
724
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
725 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
726 -- Bottlenecks
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
727 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
728 CREATE TABLE 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
729 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3624
diff changeset
730 bottleneck_id varchar NOT NULL,
3648
0ec5c8ec1e44 Avoid empty validity time ranges
Tom Gottfried <tom@intevation.de>
parents: 3647
diff changeset
731 validity tstzrange NOT NULL CHECK (NOT isempty(validity)),
3302
ec6163c6687d 'Historicise' gauges on import
Tom Gottfried <tom@intevation.de>
parents: 3278
diff changeset
732 gauge_location isrs NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
733 objnam varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
734 nobjnm varchar,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
735 stretch isrsrange NOT NULL,
1984
48001472e1d8 Ensure geometries in database are valid OGC simple features
Tom Gottfried <tom@intevation.de>
parents: 1983
diff changeset
736 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
737 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
738 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
739 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
740 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
741 revisiting_time smallint,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
742 limiting varchar NOT NULL REFERENCES limiting_factors,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
743 -- surtyp varchar NOT NULL REFERENCES survey_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
744 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
745 -- coverage varchar REFERENCES coverage_types,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
746 -- XXX: Also an attribut of sounding result?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
747 -- 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
748 -- different model approach?
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
749 -- 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
750 -- XXX: Also an attribut of sounding result?
3645
02951a62e8c6 'Historicise' bottlenecks on import
Tom Gottfried <tom@intevation.de>
parents: 3624
diff changeset
751 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
752 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
753 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
754 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
755 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
756 validity WITH &&,
692aba3e8b85 Change constraints for bottlenecks to include staging_done flag.
Sascha Wilde <wilde@intevation.de>
parents: 4106
diff changeset
757 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
758 DEFERRABLE INITIALLY DEFERRED
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
759 )
4041
3fcb95a07948 WIP: Started to remove references to gauge validity.
Sascha Wilde <wilde@intevation.de>
parents: 4016
diff changeset
760 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
761 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
762 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
763
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
764 CREATE TABLE bottlenecks_riverbed_materials (
2999
b3c3c5b5b7c1 Bottleneck import: Import riverbed materials, too.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2867
diff changeset
765 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
766 ON DELETE CASCADE,
2076
0e006077bbfa Add missing NOT NULL constraints
Tom Gottfried <tom@intevation.de>
parents: 2072
diff changeset
767 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
768 -- 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
769 PRIMARY KEY (bottleneck_id, riverbed)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
770 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
771
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
772 CREATE TABLE sounding_results (
656
9ef2f80a4645 Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents: 655
diff changeset
773 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
774 bottleneck_id varchar NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
775 date_info date NOT NULL,
656
9ef2f80a4645 Fix syntax error in schema script
Tom Gottfried <tom@intevation.de>
parents: 655
diff changeset
776 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
777 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
778 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
779 surtyp varchar REFERENCES survey_types,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
780 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
781 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
1131
2e6b47cdb2ca Store octrees along with sounding results
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
782 octree_checksum varchar,
2e6b47cdb2ca Store octrees along with sounding results
Tom Gottfried <tom@intevation.de>
parents: 1085
diff changeset
783 octree_index bytea,
4648
66fcd898efd9 Started with conversion tool.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4628
diff changeset
784 mesh_checksum varchar,
66fcd898efd9 Started with conversion tool.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4628
diff changeset
785 mesh_index bytea,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
786 staging_done boolean NOT NULL DEFAULT false
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
787 )
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4045
diff changeset
788 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
789 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
790 FOR EACH ROW
4068
76482935b6e5 Fixed c&p errors in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 4067
diff changeset
791 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
792 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
793 AFTER INSERT OR UPDATE ON sounding_results
3747
ad67e4286d65 Fixed typo in gemma schema.
Sascha Wilde <wilde@intevation.de>
parents: 3746
diff changeset
794 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
795
4561
f7b57136c800 Added table to to store iso areas of sounding results.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4461
diff changeset
796 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
797 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
798 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
799 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
800 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
801 -- 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
802 -- 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
803 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
804 )
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
805 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
806 -- Fairway availability
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
807 --
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
808 CREATE TABLE fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
809 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
810 position varchar,
4067
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
811 bottleneck_id varchar NOT NULL,
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
812 surdat date NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
813 UNIQUE (bottleneck_id, surdat),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
814 -- 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
815 critical boolean,
4057
b79b60c0cc5a date_info has to be provided by the data source
Tom Gottfried <tom@intevation.de>
parents: 4033
diff changeset
816 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
817 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
818 )
4067
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
819 -- 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
820 -- 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
821 -- (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
822 -- 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
823 -- 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
824 -- 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
825 -- 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
826 -- 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
827 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
828 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
829 FOR EACH ROW
0ba3fc89b499 Adapted references to bottlenecks from fairway_availability in DB schema.
Sascha Wilde <wilde@intevation.de>
parents: 4047
diff changeset
830 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
831
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
832 CREATE TABLE fa_reference_values (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
833 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
834 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
835 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
836 fairway_depth smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
837 fairway_width smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
838 fairway_radius int,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
839 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
840 IS NOT NULL),
370
fe87457a05d7 Store spatial data as geography
Tom Gottfried <tom@intevation.de>
parents: 368
diff changeset
841 shallowest_spot geography(POINT, 4326)
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
842 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
843
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
844 CREATE TABLE bottleneck_pdfs (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
845 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
846 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
847 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
848 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
849 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
850 source_organization varchar NOT NULL
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
851 )
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
852
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
853 CREATE TABLE effective_fairway_availability (
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
854 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
855 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
856 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
857 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
858 measure_type),
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
859 available_depth_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
860 available_width_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
861 water_level_value smallint,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
862 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
863 water_level_value) IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
864 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
865 source_organization varchar NOT NULL,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
866 forecast_generation_time timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
867 CHECK(measure_type <> 'forecasted'
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
868 OR forecast_generation_time IS NOT NULL),
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
869 value_lifetime timestamp with time zone,
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
870 CHECK(measure_type = 'minimum guaranteed'
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
871 OR value_lifetime IS NOT NULL)
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
872 )
4894
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
873
4909
6f244b5eb716 Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents: 4897
diff changeset
874 -- Attributes common to all fairway marks
4894
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
875 CREATE TABLE fairway_marks (
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
876 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
877 CHECK (NOT isempty(validity)),
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
878 -- 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
879 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
880 geom geography(POINT, 4326) NOT NULL,
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
881 -- Attributes according to IENC Feature Catalogue:
4894
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
882 datsta varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
883 datend varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
884 persta varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
885 perend varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
886 objnam varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
887 nobjnm varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
888 inform varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
889 ninfom varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
890 scamin int,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
891 picrep varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
892 txtdsc varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
893 sordat varchar,
4909
6f244b5eb716 Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents: 4897
diff changeset
894 sorind varchar
6f244b5eb716 Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents: 4897
diff changeset
895 )
6f244b5eb716 Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents: 4897
diff changeset
896
6f244b5eb716 Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents: 4897
diff changeset
897 -- 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
898 CREATE TABLE fairway_marks_bcnlat (
4894
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
899 colour varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
900 colpat varchar,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
901 condtn int,
8eb36d0d5bdf Draft implementation of fairway marks import
Tom Gottfried <tom@intevation.de>
parents: 4859
diff changeset
902 bcnshp int,
4912
bfd8ef836998 Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents: 4909
diff changeset
903 catlam int
4909
6f244b5eb716 Use table inheritance to define common fairway marks attributes
Tom Gottfried <tom@intevation.de>
parents: 4897
diff changeset
904 ) INHERITS (fairway_marks)
4962
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents: 4956
diff changeset
905
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents: 4956
diff changeset
906 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
907 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
908 ) INHERITS (fairway_marks_bcnlat)
4895
9f799077a3e6 Prevent importing non-distinct fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4894
diff changeset
909 -- Prevent identical entries using composite type comparison
9f799077a3e6 Prevent importing non-distinct fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4894
diff changeset
910 -- (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
911 CREATE UNIQUE INDEX fairway_marks_bcnlat_hydro_distinct_rows
4965
be713316b47a Fixup rev. 1b309a8e7673
Tom Gottfried <tom@intevation.de>
parents: 4964
diff changeset
912 ON fairway_marks_bcnlat_hydro
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
913 ((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
914 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
915 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
916 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
917 ) AS fairway_marks_bcnlat_hydro)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents: 4956
diff changeset
918 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
919 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
920 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
921
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents: 4956
diff changeset
922 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
923 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
924 ) INHERITS (fairway_marks_bcnlat)
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents: 4956
diff changeset
925 -- 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
926 -- (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
927 CREATE UNIQUE INDEX fairway_marks_bcnlat_ienc_distinct_rows
4965
be713316b47a Fixup rev. 1b309a8e7673
Tom Gottfried <tom@intevation.de>
parents: 4964
diff changeset
928 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
929 ((CAST((validity, last_found, geom,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents: 4956
diff changeset
930 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
931 scamin, picrep, txtdsc, sordat, sorind,
1b309a8e7673 Distinguish more clearly between BCNLAT HYDRO and IENC features
Tom Gottfried <tom@intevation.de>
parents: 4956
diff changeset
932 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
933 ) AS fairway_marks_bcnlat_ienc)
4895
9f799077a3e6 Prevent importing non-distinct fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4894
diff changeset
934 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
935 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
936 ON fairway_marks_bcnlat_ienc USING GiST (validity)
4912
bfd8ef836998 Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents: 4909
diff changeset
937
bfd8ef836998 Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents: 4909
diff changeset
938 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
939 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
940 dirimp smallint REFERENCES dirimps,
bfd8ef836998 Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents: 4909
diff changeset
941 PRIMARY KEY (fm_bcnlat_id, dirimp)
bfd8ef836998 Fix handling of attribute dirimp
Tom Gottfried <tom@intevation.de>
parents: 4909
diff changeset
942 )
4913
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
943
4922
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
944 -- 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
945 CREATE TABLE fairway_marks_boycar (
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
946 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
947 colour varchar,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
948 colpat varchar,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
949 conrad int,
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
950 marsys int,
4925
b86ce7fc4da3 Add missing BOYCAR attribute
Tom Gottfried <tom@intevation.de>
parents: 4922
diff changeset
951 boyshp int,
b86ce7fc4da3 Add missing BOYCAR attribute
Tom Gottfried <tom@intevation.de>
parents: 4922
diff changeset
952 catcam int
4913
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
953 ) INHERITS (fairway_marks)
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
954 -- 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
955 -- (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
956 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
957 ON fairway_marks_boycar
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
958 ((CAST((validity, last_found, geom,
4913
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
959 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
960 scamin, picrep, txtdsc, sordat, sorind,
4925
b86ce7fc4da3 Add missing BOYCAR attribute
Tom Gottfried <tom@intevation.de>
parents: 4922
diff changeset
961 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
962 ) AS fairway_marks_boycar)
8c1a3d5e3962 Add import for fairway marks of type BOYCAR
Tom Gottfried <tom@intevation.de>
parents: 4912
diff changeset
963 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
964 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
965 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
966
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
967 -- Additional attributes for IENC feature BOYLAT
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
968 CREATE TABLE fairway_marks_boylat (
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
969 colour varchar,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
970 colpat varchar,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
971 conrad int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
972 marsys int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
973 boyshp int,
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
974 catlam int
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
975 ) INHERITS (fairway_marks)
4964
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
976
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
977 CREATE TABLE fairway_marks_boylat_hydro (
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
978 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
979 ) INHERITS (fairway_marks_boylat)
4922
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
980 -- 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
981 -- (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
982 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
983 ON fairway_marks_boylat_hydro
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
984 ((CAST((validity, last_found, geom,
4922
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
985 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
986 scamin, picrep, txtdsc, sordat, sorind,
4964
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
987 colour, colpat, conrad, marsys, boyshp, catlam, 0
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
988 ) AS fairway_marks_boylat_hydro)
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
989 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
990 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
991 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
992
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
993 CREATE TABLE fairway_marks_boylat_ienc (
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
994 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
995 ) INHERITS (fairway_marks_boylat)
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
996 -- Prevent identical entries using composite type comparison
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
997 -- (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
998 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
999 ON fairway_marks_boylat_ienc
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
1000 ((CAST((validity, last_found, geom,
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
1001 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
1002 scamin, picrep, txtdsc, sordat, sorind,
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
1003 colour, colpat, conrad, marsys, boyshp, catlam, 0
58dc06e91c39 Follow-up of rev. 1b309a8e7673 for BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4962
diff changeset
1004 ) AS fairway_marks_boylat_ienc)
4922
9bd6a0ca63ea Add import for fairway marks of type BOYLAT
Tom Gottfried <tom@intevation.de>
parents: 4919
diff changeset
1005 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
1006 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
1007 ON fairway_marks_boylat_ienc USING GiST (validity)
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1008
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1009 -- Additional attributes for IENC feature BOYSAW
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1010 CREATE TABLE fairway_marks_boysaw (
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1011 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1012 colour varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1013 colpat varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1014 conrad int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1015 marsys int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1016 boyshp int
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1017 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1018 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1019 -- (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
1020 CREATE UNIQUE INDEX fairway_marks_boysaw_distinct_rows
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1021 ON fairway_marks_boysaw
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
1022 ((CAST((validity, last_found, geom,
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1023 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1024 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1025 0, colour, colpat, conrad, marsys, boyshp
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1026 ) AS fairway_marks_boysaw)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1027 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
1028 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
1029 ON fairway_marks_boysaw USING GiST (validity)
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1030
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1031 -- Additional attributes for IENC feature BOYSPP
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1032 CREATE TABLE fairway_marks_boyspp (
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1033 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1034 colour varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1035 colpat varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1036 conrad int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1037 marsys int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1038 boyshp int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1039 catspm varchar
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1040 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1041 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1042 -- (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
1043 CREATE UNIQUE INDEX fairway_marks_boyspp_distinct_rows
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1044 ON fairway_marks_boyspp
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
1045 ((CAST((validity, last_found, geom,
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1046 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1047 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1048 0, colour, colpat, conrad, marsys, boyshp, catspm
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1049 ) AS fairway_marks_boyspp)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1050 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
1051 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
1052 ON fairway_marks_boyspp USING GiST (validity)
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1053
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1054 -- Additional attributes for IENC features DAYMAR/daymar
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1055 CREATE TABLE fairway_marks_daymar (
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1056 colour varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1057 colpat varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1058 condtn int,
4967
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1059 topshp int
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1060 ) INHERITS (fairway_marks)
4967
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1061
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1062 CREATE TABLE fairway_marks_daymar_hydro (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1063 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
1064 ) INHERITS (fairway_marks_daymar)
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1065 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1066 -- (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
1067 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
1068 ON fairway_marks_daymar_hydro
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
1069 ((CAST((validity, last_found, geom,
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1070 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1071 scamin, picrep, txtdsc, sordat, sorind,
4967
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1072 colour, colpat, condtn, topshp, 0
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1073 ) AS fairway_marks_daymar_hydro)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1074 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
1075 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
1076 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
1077
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1078 CREATE TABLE fairway_marks_daymar_ienc (
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1079 orient double precision,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1080 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
1081 ) INHERITS (fairway_marks_daymar)
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1082 -- Prevent identical entries using composite type comparison
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1083 -- (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
1084 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
1085 ON fairway_marks_daymar_ienc
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1086 ((CAST((validity, last_found, geom,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1087 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
1088 scamin, picrep, txtdsc, sordat, sorind,
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1089 colour, colpat, condtn, topshp, orient, 0
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1090 ) AS fairway_marks_daymar_ienc)
4940
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_daymar_ienc_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_daymar_ienc 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 CREATE TABLE fairway_marks_daymar_dirimps (
4967
3f704ebad0c5 Follow-up of rev. 1b309a8e7673 for DAYMAR
Tom Gottfried <tom@intevation.de>
parents: 4965
diff changeset
1096 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
1097 dirimp smallint REFERENCES dirimps,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1098 PRIMARY KEY (fm_daymar_id, dirimp)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1099 )
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1100
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1101 -- Additional attributes for IENC feature LIGHTS
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1102 CREATE TABLE fairway_marks_lights (
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1103 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1104 colour varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1105 condtn int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1106 orient double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1107 catlit varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1108 exclit int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1109 litchr int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1110 litvis varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1111 mltylt int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1112 sectr1 double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1113 sectr2 double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1114 siggrp varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1115 sigper double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1116 sigseq varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1117 status varchar
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1118 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1119 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1120 -- (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
1121 CREATE UNIQUE INDEX fairway_marks_lights_distinct_rows
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1122 ON fairway_marks_lights
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
1123 ((CAST((validity, last_found, geom,
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1124 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1125 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1126 0, colour, condtn, orient, catlit, exclit, litchr, litvis,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1127 mltylt, sectr1, sectr2, siggrp, sigper, sigseq, status
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1128 ) AS fairway_marks_lights)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1129 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
1130 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
1131 ON fairway_marks_lights USING GiST (validity)
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1132
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1133 -- Additional attributes for IENC feature RTPBCN
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1134 CREATE TABLE fairway_marks_rtpbcn (
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1135 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1136 condtn int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1137 siggrp varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1138 catrtb int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1139 radwal varchar
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1140 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1141 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1142 -- (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
1143 CREATE UNIQUE INDEX fairway_marks_rtpbcn_distinct_rows
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1144 ON fairway_marks_rtpbcn
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
1145 ((CAST((validity, last_found, geom,
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1146 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1147 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1148 0, condtn, siggrp, catrtb, radwal
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1149 ) AS fairway_marks_rtpbcn)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1150 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
1151 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
1152 ON fairway_marks_rtpbcn USING GiST (validity)
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1153
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1154 -- Additional attributes for IENC feature TOPMAR
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1155 CREATE TABLE fairway_marks_topmar (
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1156 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1157 colour varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1158 colpat varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1159 condtn int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1160 topshp int
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1161 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1162 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1163 -- (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
1164 CREATE UNIQUE INDEX fairway_marks_topmar_distinct_rows
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1165 ON fairway_marks_topmar
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
1166 ((CAST((validity, last_found, geom,
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1167 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1168 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1169 0, colour, colpat, condtn, topshp
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1170 ) AS fairway_marks_topmar)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1171 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
1172 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
1173 ON fairway_marks_topmar USING GiST (validity)
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1174
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1175 -- Additional attributes for IENC feature NOTMRK
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1176 CREATE TABLE fairway_marks_notmrk (
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1177 id int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1178 condtn int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1179 marsys int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1180 orient double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1181 status varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1182 addmrk varchar,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1183 catnmk int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1184 disipd double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1185 disipu double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1186 disbk1 double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1187 disbk2 double precision,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1188 fnctnm int,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1189 bnkwtw int
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1190 ) INHERITS (fairway_marks)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1191 -- Prevent identical entries using composite type comparison
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1192 -- (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
1193 CREATE UNIQUE INDEX fairway_marks_notmrk_distinct_rows
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1194 ON fairway_marks_notmrk
4956
7cc79c65a9e5 Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents: 4940
diff changeset
1195 ((CAST((validity, last_found, geom,
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1196 datsta, datend, persta, perend, objnam, nobjnm, inform, ninfom,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1197 scamin, picrep, txtdsc, sordat, sorind,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1198 0, condtn, marsys, orient, status, addmrk, catnmk,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1199 disipd, disipu, disbk1, disbk2, fnctnm, bnkwtw
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1200 ) AS fairway_marks_notmrk)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1201 ))
4971
de190de05f67 Add index to speed up fairway marks imports a bit
Tom Gottfried <tom@intevation.de>
parents: 4967
diff changeset
1202 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
1203 ON fairway_marks_notmrk USING GiST (validity)
4940
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1204
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1205 CREATE TABLE fairway_marks_notmrk_dirimps (
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1206 fm_notmrk_id int REFERENCES fairway_marks_notmrk,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1207 dirimp smallint REFERENCES dirimps,
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1208 PRIMARY KEY (fm_notmrk_id, dirimp)
b3b2ba09a450 Add missing fairway mark types
Tom Gottfried <tom@intevation.de>
parents: 4930
diff changeset
1209 )
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1210 ;
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1211
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1212
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1213 --
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1214 -- Import queue and respective logging
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1215 --
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1216 CREATE TYPE import_state AS ENUM (
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1217 'queued',
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1218 'running',
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1219 'failed', 'unchanged', 'pending',
5096
f64ff954ee31 Added new reviewed state.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 5031
diff changeset
1220 'accepted', 'declined', 'reviewed'
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1221 );
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1222
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1223 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
1224
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1225 -- 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
1226 CREATE SCHEMA import
1547
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
1227
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
1228 CREATE TABLE import_configuration (
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
1229 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
1230 username varchar NOT NULL
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
1231 REFERENCES internal.user_profiles(username)
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
1232 ON DELETE CASCADE
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
1233 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
1234 kind varchar NOT NULL
1547
d4b7a6d054cd Add table to store import configuration.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1520
diff changeset
1235 )
1702
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
1236
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
1237 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
1238 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
1239 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
1240 ON DELETE CASCADE
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
1241 ON UPDATE CASCADE,
49b89575ab31 Import configuration: [WIP] Added table for extra configuration attributes.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1684
diff changeset
1242 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
1243 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
1244 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
1245 )
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1246
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1247 CREATE TABLE imports (
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1248 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
1249 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
1250 kind varchar NOT NULL,
4098
cfa0a5775d70 Reapplied timezone patch to database schema.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4094
diff changeset
1251 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
1252 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
1253 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
1254 retry_wait interval
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1255 CHECK(retry_wait IS NULL
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1256 OR retry_wait >= interval '0 microseconds'),
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1257 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
1258 username varchar NOT NULL
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1259 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1260 ON DELETE CASCADE
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1261 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1262 signer varchar
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1263 REFERENCES internal.user_profiles(username)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1264 ON DELETE SET NULL
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1265 ON UPDATE CASCADE,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1266 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
1267 data TEXT,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1268 summary TEXT
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1269 )
5122
0b6b62d247e8 Prioritize review jobs on selection
Tom Gottfried <tom@intevation.de>
parents: 5096
diff changeset
1270 -- Mainly for listing imports in clients:
0b6b62d247e8 Prioritize review jobs on selection
Tom Gottfried <tom@intevation.de>
parents: 5096
diff changeset
1271 CREATE INDEX enqueued_idx ON imports(enqueued)
0b6b62d247e8 Prioritize review jobs on selection
Tom Gottfried <tom@intevation.de>
parents: 5096
diff changeset
1272 -- 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
1273 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
1274
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1275 CREATE TABLE import_logs (
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1276 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
1277 ON DELETE CASCADE,
4100
cc3d607b49cc Merged default into timezone branch.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4098 4099
diff changeset
1278 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
1279 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
1280 msg TEXT NOT NULL
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1281 )
4187
65a5501dc13d Enable faster access to import logs
Tom Gottfried <tom@intevation.de>
parents: 4173
diff changeset
1282 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
1283
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1284 CREATE TABLE track_imports (
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1285 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
1286 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
1287 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
1288 relation regclass NOT NULL,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1289 key int NOT NULL,
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1290 UNIQUE (relation, key)
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1291 )
195
5dc8e734487a Introduce database schemas as privilege-based namespaces
Tom Gottfried <tom@intevation.de>
parents: 184
diff changeset
1292 ;
56
f378959820be Started to draft schema for fairway availability.
Tom Gottfried <tom@intevation.de>
parents: 55
diff changeset
1293
1995
59055c8301df Move import queue to its own database namespace
Tom Gottfried <tom@intevation.de>
parents: 1986
diff changeset
1294 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
1295 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
1296 DECLARE
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
1297 tmp RECORD;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
1298 BEGIN
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
1299 FOR tmp IN
4126
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
1300 SELECT relation, array_agg(key) AS keys
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
1301 FROM import.track_imports
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
1302 WHERE import_id = imp_id AND NOT deletion
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
1303 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
1304 LOOP
4126
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents: 4106
diff changeset
1305 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
1306 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
1307 END LOOP;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
1308 END;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
1309 $$
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
1310 LANGUAGE plpgsql;
58acc343b1b6 Implemented the db stuff of the review process. Needs testing.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 1190
diff changeset
1311
2542
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1312 CREATE SCHEMA caching
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1313
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1314 CREATE TABLE sounding_differences (
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1315 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
1316 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
1317 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1318 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
1319 ON DELETE CASCADE,
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1320 UNIQUE (minuend, subtrahend)
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1321 )
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1322
4573
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
1323 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
1324 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
1325 ON DELETE CASCADE,
26e9846ed69f Added caching table for iso areas of sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 4561
diff changeset
1326 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
1327 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
1328 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
1329 )
2542
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1330 ;
fc7d828695c9 Schema: Added caching schema containing the sounding differences.
Sascha L. Teichmann <sascha.teichmann@intevation.de>
parents: 2529
diff changeset
1331
35
62e14b4d25fc First working draft of schema for bottlenecks.
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1332 COMMIT;