Mercurial > gemma

annotate schema/import_tests.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
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');