1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
|
#!/usr/bin/perl
# This script is used to verify the consistency of the item tables (e.g. 'vn')
# with the latest revision in the corresponding history tables (e.g. 'vn_hist').
#
# The edit_* functions generated by sqleditfunc.pl should ensure this
# consistency, but bugs can happen and migration scripts sometimes bypass these
# functions.
#
# Outputs SQL statements that can be piped to 'psql'. The generated SELECT
# statements should not return any rows.
use v5.24;
use warnings;
use Cwd 'abs_path';
my $ROOT;
BEGIN { ($ROOT = abs_path $0) =~ s{/util/revision-integrity\.pl$}{}; }
use lib "$ROOT/lib";
use VNDB::Schema;
my $schema = VNDB::Schema::schema;
for my $table (sort { $a->{name} cmp $b->{name} } values %$schema) {
next if $table->{name} !~ /^(.+)_hist$/;
my($main, $type) = ($1, $1);
$type =~ s/_[^_]+$// while !$schema->{$type}{dbentry_type};
my ($mainlock, $histlock) = $main eq $type ? ('locked, hidden, ', 'c.ilock, c.ihid, ') : ('','');
my $cols = join ', ', map "e.\"$_->{name}\"", grep $_->{name} ne 'chid', $table->{cols}->@*;
print "SELECT '$main' as table, id, $mainlock $cols
FROM $main e
EXCEPT
SELECT '$main', c.itemid, $histlock $cols
FROM $table->{name} e
JOIN changes c ON c.type = '$schema->{$type}{dbentry_type}' AND e.chid = c.id
WHERE NOT EXISTS(SELECT 1 FROM changes c2 WHERE c2.type = c.type AND c2.itemid = c.itemid AND c2.rev > c.rev);\n\n"
}
|