Mercurial > gemma

annotate schema/gemma_tests.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
parents 722b7c305319
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- without warranty, see README.md and license for details.
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 -- Copyright (C) 2019 by via donau
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 -- Software engineering by Intevation GmbH
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 -- Author(s):
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 --
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 -- pgTAP test script for gemma schema definition
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 --
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
4602
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
18 SELECT ok(is_valid_from_item('SELECT * FROM sys_admin.published_services'),
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
19 'Valid statement passes check');
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
20
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
21 SELECT ok(NOT is_valid_from_item('This is not SQL'),
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
22 'Arbitrary text does not pass check');
9fef9930aa8a Add field for GeoServer SQL view definition
Tom Gottfried <tom@intevation.de>
parents: 4118
diff changeset
23
4603
d24e951206ca Do not try to execute NULL as a subquery
Tom Gottfried <tom@intevation.de>
parents: 4602
diff changeset
24 SELECT ok(is_valid_from_item(NULL) IS NULL,
d24e951206ca Do not try to execute NULL as a subquery
Tom Gottfried <tom@intevation.de>
parents: 4602
diff changeset
25 'NULL value is not checked');
d24e951206ca Do not try to execute NULL as a subquery
Tom Gottfried <tom@intevation.de>
parents: 4602
diff changeset
26
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 SELECT throws_ok($$
5141
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
28 SET CONSTRAINTS waterway.waterway_axis_wtwaxs_unique IMMEDIATE;
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES
5009
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4603
diff changeset
30 (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test'),
e8b2dc771f9e Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents: 4603
diff changeset
31 (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test')
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 $$,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 23505, NULL,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 'No duplicate geometries can be inserted into waterway_axis');
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35
5141
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
36 SELECT throws_ok($$
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
37 SET CONSTRAINTS waterway.waterway_axis_wtwaxs_unique IMMEDIATE;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
38 INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES (
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
39 ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
40 'test',
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
41 tstzrange(NULL, current_timestamp)
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
42 ), (
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
43 ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
44 'test',
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
45 tstzrange(current_timestamp - interval '1d', NULL)
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
46 )
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
47 $$,
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
48 23505, NULL,
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
49 'Duplicate axis geometries cannot be inserted if validities intersect');
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents: 5095
diff changeset
50
5016
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
51 SELECT lives_ok($$
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
52 INSERT INTO waterway.waterway_axis (wtwaxs, objnam, validity) VALUES (
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
53 ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
54 'test',
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
55 tstzrange(NULL, current_timestamp)
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
56 ), (
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
57 ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'),
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
58 'test',
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
59 tstzrange(current_timestamp, NULL)
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
60 )
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
61 $$,
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
62 'Duplicate axis geometries can be inserted if validity differs');
cf25b23e3eec Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents: 5009
diff changeset
63
2781
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 SELECT throws_ok($$
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 INSERT INTO waterway.waterway_area (area) VALUES
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66 (ST_GeogFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))')),
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 (ST_GeogFromText('POLYGON((0 0, 0 1, 1 1, 1 0, 0 0))'))
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68 $$,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 23505, NULL,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 'No duplicate geometries can be inserted into waterway_area');
5095
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
71
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
72 SELECT throws_ok($$
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
73 INSERT INTO waterway.fairway_dimensions (
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
74 area, level_of_service,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
75 min_width, max_width, min_depth, source_organization
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
76 ) VALUES (
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
77 ST_GeogFromText('MULTIPOLYGON(((0 0, 1 1, 1 0, 0 0)))'), 3,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
78 100, 200, 2, 'test'
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
79 ), (
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
80 ST_GeogFromText('MULTIPOLYGON(((0 0, 1 1, 1 0, 0 0)))'), 3,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
81 100, 200, 2, 'test'
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
82 )
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
83 $$,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
84 23505, NULL,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
85 'No duplicate geometries can be inserted into fairway_dimensions');
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
86
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
87 SELECT lives_ok($$
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
88 INSERT INTO waterway.fairway_dimensions (
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
89 area, level_of_service,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
90 min_width, max_width, min_depth, source_organization, staging_done
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
91 ) VALUES (
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
92 ST_GeogFromText('MULTIPOLYGON(((0 0, 1 1, 1 0, 0 0)))'), 3,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
93 100, 200, 2, 'test', false
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
94 ), (
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
95 ST_GeogFromText('MULTIPOLYGON(((0 0, 1 1, 1 0, 0 0)))'), 3,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
96 100, 200, 2, 'test', true
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
97 )
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
98 $$,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
99 'Duplicate fairway area can be inserted if stage_done differs');
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
100
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
101 SELECT throws_ok($$
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
102 UPDATE waterway.fairway_dimensions SET staging_done = true
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
103 $$,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
104 23505, NULL,
e21cbb9768a2 Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents: 5016
diff changeset
105 'No duplicate fairway area can be released from staging area');