Mercurial > gemma

annotate schema/gemma.sql @ 4016:9ab7e1056360

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