Mercurial > gemma

annotate schema/import_tests.sql @ 5593:88a41fc07c97

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged surveysperbottleneckid
author Sascha Wilde <wilde@sha-bang.de>
date Tue, 19 Apr 2022 13:02:45 +0200
parents 52f7264265bb
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4126
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 -- without warranty, see README.md and license for details.
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 -- Copyright (C) 2019 by via donau
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 -- Software engineering by Intevation GmbH
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 -- Author(s):
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 -- * Tom Gottfried <tom@intevation.de>
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 --
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 -- pgTAP test script for import queue
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 --
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 \set imp_id 99
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 PREPARE insert_gms AS WITH
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 gms AS (
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 INSERT INTO waterway.gauge_measurements (
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 location,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 measure_date,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 country_code,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 sender,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 language_code,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 date_issue,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 reference_code,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 water_level,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 date_info,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 source_organization
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 ) SELECT
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 ('AT', 'XXX', '00001', 'G0001', 1)::isrs,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 t,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 'AT',
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 'test',
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 'DE',
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 current_timestamp,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 'ZPG',
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 0,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 current_timestamp,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 'test'
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 FROM generate_series(
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
44 current_timestamp - '12 h'::interval,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
45 current_timestamp - '6 h'::interval,
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
46 '15 min'::interval) AS times (t)
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
47 RETURNING id),
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
48 imps AS (
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
49 INSERT INTO import.imports (id, kind, username) VALUES (
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
50 $1, 'test', 'test_admin_ro')
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
51 RETURNING id)
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
52 INSERT INTO import.track_imports (import_id, relation, key)
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
53 SELECT imps.id, 'waterway.gauge_measurements', gms.id
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
54 FROM imps, gms;
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
55
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
56 EXECUTE insert_gms(:imp_id);
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
57 EXECUTE insert_gms(:imp_id + 1);
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
58
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
59 SELECT ok((SELECT count(*) FROM waterway.gauge_measurements
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
60 WHERE id IN(SELECT key
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
61 FROM import.track_imports
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
62 WHERE import_id IN(:imp_id, :imp_id + 1))
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
63 ) = (SELECT count(*)
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
64 FROM import.track_imports WHERE import_id IN(:imp_id, :imp_id + 1)),
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
65 'Tracked entries of test imports exist');
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
66
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
67 SELECT import.del_import(:imp_id);
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
68
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
69 SELECT ok(0 = (SELECT count(*) FROM waterway.gauge_measurements
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
70 WHERE id IN(SELECT key
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
71 FROM import.track_imports
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
72 WHERE import_id = :imp_id)),
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
73 'Tracked entries of first test import are deleted');
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
74
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
75 SELECT ok((SELECT count(*) FROM waterway.gauge_measurements
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
76 WHERE id IN(SELECT key
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
77 FROM import.track_imports
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
78 WHERE import_id = :imp_id + 1)
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
79 ) = (SELECT count(*)
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
80 FROM import.track_imports WHERE import_id = :imp_id + 1),
52f7264265bb Bulk-delete tracked entries per table
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
81 'Tracked entries of second test import still exist');