Mercurial > gemma
annotate schema/import_tests.sql @ 5625:39c1698fb0ff
Bumped dev version
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 22 Dec 2022 18:43:46 +0100 |
parents | 52f7264265bb |
children |
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'); |