Mercurial > gemma

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

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +0100
parents 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');