Mercurial > gemma
annotate schema/gemma_tests.sql @ 5652:9b3779f649c2 v5.7.1
Bumped version for release.
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Mon, 07 Aug 2023 17:59:18 +0200 |
parents | 722b7c305319 |
children |
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'); |