Mercurial > gemma
annotate schema/gemma_tests.sql @ 5131:52e3980e3462 queued-stage-done
review decisions controller: Lowered immediate feedback timeout to 5 secs.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Fri, 27 Mar 2020 13:09:02 +0100 |
parents | e21cbb9768a2 |
children | 722b7c305319 |
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($$ |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES |
5009
e8b2dc771f9e
Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents:
4603
diff
changeset
|
29 (ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), 'test'), |
e8b2dc771f9e
Store axis as MultiLinestring
Tom Gottfried <tom@intevation.de>
parents:
4603
diff
changeset
|
30 (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
|
31 $$, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 23505, NULL, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 '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
|
34 |
5016
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
35 SELECT lives_ok($$ |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
36 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
|
37 ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
38 'test', |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
39 tstzrange(NULL, current_timestamp) |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
40 ), ( |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
41 ST_GeogFromText('MULTILINESTRING((0 0, 1 1))'), |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
42 'test', |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
43 tstzrange(current_timestamp, NULL) |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
44 ) |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
45 $$, |
cf25b23e3eec
Keep historic data of waterway axis
Tom Gottfried <tom@intevation.de>
parents:
5009
diff
changeset
|
46 '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
|
47 |
2781
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 SELECT throws_ok($$ |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 INSERT INTO waterway.waterway_area (area) VALUES |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 (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
|
51 (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
|
52 $$, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
53 23505, NULL, |
a2127495093e
Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
54 'No duplicate geometries can be inserted into waterway_area'); |
5095
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
55 |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
56 SELECT throws_ok($$ |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
57 INSERT INTO waterway.fairway_dimensions ( |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
58 area, level_of_service, |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
59 min_width, max_width, min_depth, source_organization |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
60 ) VALUES ( |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
61 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
|
62 100, 200, 2, 'test' |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
63 ), ( |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
64 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
|
65 100, 200, 2, 'test' |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
66 ) |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
67 $$, |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
68 23505, NULL, |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
69 'No duplicate geometries can be inserted into fairway_dimensions'); |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
70 |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
71 SELECT lives_ok($$ |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
72 INSERT INTO waterway.fairway_dimensions ( |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
73 area, level_of_service, |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
74 min_width, max_width, min_depth, source_organization, staging_done |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
75 ) VALUES ( |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
76 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
|
77 100, 200, 2, 'test', false |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
78 ), ( |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
79 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
|
80 100, 200, 2, 'test', true |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
81 ) |
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 'Duplicate fairway area can be inserted if stage_done differs'); |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
84 |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
85 SELECT throws_ok($$ |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
86 UPDATE waterway.fairway_dimensions SET staging_done = true |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
87 $$, |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
88 23505, NULL, |
e21cbb9768a2
Prevent duplicate fairway areas
Tom Gottfried <tom@intevation.de>
parents:
5016
diff
changeset
|
89 'No duplicate fairway area can be released from staging area'); |