Mercurial > gemma

annotate schema/gemma_tests.sql @ 3762:98d5dd2f0ca1

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Don't show unnecessary warnings when uploading TXT file for SR.
author Sascha Wilde <wilde@intevation.de>
date Wed, 26 Jun 2019 11:00:35 +0200
parents db87f34805fb
children b785b6bef578
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
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 SELECT throws_ok($$
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 INSERT INTO waterway.waterway_axis (wtwaxs, objnam) VALUES
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 (ST_GeogFromText('LINESTRING(0 0, 1 1)'), 'test'),
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 (ST_GeogFromText('LINESTRING(0 0, 1 1)'), 'test')
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 $$,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 23505, NULL,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 '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
25
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 SELECT throws_ok($$
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 INSERT INTO waterway.waterway_area (area) VALUES
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 (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
29 (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
30 $$,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 23505, NULL,
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 'No duplicate geometries can be inserted into waterway_area');
a2127495093e Prevent duplicate waterway axis and area geometries
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
34 START TRANSACTION;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
35 CREATE TEMP TABLE new_v (v) AS
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
36 SELECT tstzrange(current_timestamp - '2 d'::interval,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
37 current_timestamp - '12 h'::interval);
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
38 INSERT INTO waterway.gauges (
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
39 location,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
40 validity,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
41 objname,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
42 geom,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
43 zero_point,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
44 date_info,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
45 source_organization,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
46 lastupdate,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
47 erased)
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
48 VALUES (
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
49 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
50 (SELECT v FROM new_v),
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
51 'testgauge',
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
52 ST_geomfromtext('POINT(0 0)', 4326),
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
53 0,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
54 current_timestamp,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
55 'testorganization',
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
56 current_timestamp,
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
57 true);
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
58 -- Fix validity of old entry to match exclusion constraint
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
59 UPDATE waterway.gauges SET
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
60 validity = validity - (SELECT v FROM new_v)
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
61 WHERE location = ('AT', 'XXX', '00001', 'G0001', 1)::isrs
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
62 AND validity && (SELECT v FROM new_v)
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
63 AND NOT erased;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
64 COMMIT;
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
65 SELECT results_eq($$
3666
db87f34805fb Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents: 3665
diff changeset
66 SELECT count(*) FROM waterway.bottlenecks GROUP BY bottleneck_id;
3665
29ef6d41e4af Use database triggers to move referencing objects to new versions
Tom Gottfried <tom@intevation.de>
parents: 2781
diff changeset
67 $$,
3666
db87f34805fb Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents: 3665
diff changeset
68 CAST(ARRAY[2,2] AS bigint[]),
db87f34805fb Align bottleneck validity at gauges
Tom Gottfried <tom@intevation.de>
parents: 3665
diff changeset
69 'Bottlenecks have been split to two new matching gauge versions');