lei_mail_sync: cleanup stale/dangling fids if possible

I'm not sure how it happens or if/when it was fixed, but my
earliest lei installations have hit some
"E: fid=$fid for $oidhex unknown" messages on `lei import'
invocations.

This really should've enabled the foreign keys pragma to begin
with; but we'll probably start using that in the future.  For
now, at least rely on a transaction to keep things consistent
in SQLite.

1 files changed, 27 insertions, 10 deletions

diff --git a/lib/PublicInbox/LeiMailSync.pm b/lib/PublicInbox/LeiMailSync.pm
index 665206a8..ea4d48c1 100644
--- a/lib/PublicInbox/LeiMailSync.pm
+++ b/lib/PublicInbox/LeiMailSync.pm
@@ -339,6 +339,17 @@ SELECT $op(uid) FROM blob2num WHERE fid = ?
         $ret;
 }
 
+# must be called with lock
+sub _forget_fids ($;@) {
+        my $dbh = shift;
+        $dbh->begin_work;
+        for my $t (qw(blob2name blob2num folders)) {
+                my $sth = $dbh->prepare_cached("DELETE FROM $t WHERE fid = ?");
+                $sth->execute($_) for @_;
+        }
+        $dbh->commit;
+}
+
 # returns a { location => [ list-of-ids-or-names ] } mapping
 sub locations_for {
         my ($self, $oidbin) = @_;
@@ -379,18 +390,28 @@ sub locations_for {
 
         $sth = $dbh->prepare('SELECT loc FROM folders WHERE fid = ? LIMIT 1');
         my $ret = {};
+        my $drop_fids = $dbh->{ReadOnly} ? undef : {};
         while (my ($fid, $ids) = each %fid2id) {
                 $sth->execute($fid);
                 my ($loc) = $sth->fetchrow_array;
                 unless (defined $loc) {
+                        my $del = '';
+                        if ($drop_fids) {
+                                $del = ' (deleting)';
+                                $drop_fids->{$fid} = $fid;
+                        }
                         my $oidhex = unpack('H*', $oidbin);
-                        warn "E: fid=$fid for $oidhex unknown:\n", map {
-                                        'E: '.(ref() ? $$_ : "#$_")."\n";
+                        warn "E: fid=$fid for $oidhex stale/unknown:\n", map {
+                                        'E: '.(ref() ? $$_ : "#$_")."$del\n";
                                 } @$ids;
                         next;
                 }
                 $ret->{$loc} = $ids;
         }
+        if ($drop_fids && scalar(values %$drop_fids)) {
+                my $lk = $self->lock_for_scope;
+                _forget_fids($self->{dbh}, values %$drop_fids);
+        }
         scalar(keys %$ret) ? $ret : undef;
 }
 
@@ -596,14 +617,10 @@ EOF
 sub forget_folders {
         my ($self, @folders) = @_;
         my $lk = $self->lock_for_scope;
-        for my $folder (@folders) {
-                my $fid = delete($self->{fmap}->{$folder}) //
-                        fid_for($self, $folder) // next;
-                for my $t (qw(blob2name blob2num folders)) {
-                        $self->{dbh}->do("DELETE FROM $t WHERE fid = ?",
-                                        undef, $fid);
-                }
-        }
+        _forget_fids($self->{dbh}, map {
+                delete($self->{fmap}->{$_}) //
+                        fid_for($self, $_) // ();
+        } @folders);
 }
 
 # only used for changing canonicalization errors