1 files changed, 39 insertions, 0 deletions
diff --git a/util/revision-integrity.pl b/util/revision-integrity.pl
new file mode 100755
index 00000000..3c8a552d
--- /dev/null
+++ b/util/revision-integrity.pl
@@ -0,0 +1,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"
+}