From 924fcdc5a5095043388bcce85da09af0d3161bd0 Mon Sep 17 00:00:00 2001
From: Eric Wong <e@80x24.org>
Date: Wed, 12 Apr 2023 06:19:10 +0000
Subject: 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.
---
 lib/PublicInbox/LeiMailSync.pm | 37 +++++++++++++++++++++++++++----------
 1 file changed, 27 insertions(+), 10 deletions(-)

(limited to 'lib/PublicInbox/LeiMailSync.pm')

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
-- 
cgit v1.2.3-24-ge0c7