annotate schema/gemma_tests.sql @ 5016:cf25b23e3eec

Keep historic data of waterway axis ... and accordingly configure the respective layer as WMS-T.
author Tom Gottfried <tom@intevation.de>
date Fri, 13 Mar 2020 17:34:59 +0100
parents e8b2dc771f9e
children e21cbb9768a2
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($$
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');