over+msgmap: respect WAL journal_mode if set

[public-inbox.git] / lib / PublicInbox / Over.pm

diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm
index 34d0b05da4188dd5267795da6ee95faf7bed9890..3e74b7a6f3ccd9372d67078fab01a38ebc92aa05 100644 (file)
--- a/lib/PublicInbox/Over.pm
+++ b/lib/PublicInbox/Over.pm
@@ -21,9 +21,6 @@ sub dbh_new {
                        require PublicInbox::Spawn;
                        open my $fh, '+>>', $f or die "failed to open $f: $!";
                        PublicInbox::Spawn::nodatacow_fd(fileno($fh));
-                       my $j = "$f-journal";
-                       open $fh, '+>>', $j or die "failed to open $j: $!";
-                       PublicInbox::Spawn::nodatacow_fd(fileno($fh));
                } else {
                        $self->{filename} = $f; # die on stat() below:
                }
@@ -45,7 +42,28 @@ sub dbh_new {
                $st = pack('dd', $st[0], $st[1]);
        } while ($st ne $self->{st} && $tries++ < 3);
        warn "W: $f: .st_dev, .st_ino unstable\n" if $st ne $self->{st};
-       $dbh->do('PRAGMA synchronous = OFF') if ($rw // 0) > 1;
+
+       if ($rw) {
+               # TRUNCATE reduces I/O compared to the default (DELETE).
+               #
+               # Do not use WAL by default since we expect the case
+               # where any users may read via read-only daemons
+               # (-httpd/-imapd/-nntpd); but only a single user has
+               # write permissions for -watch/-mda.
+               #
+               # Read-only WAL support in SQLite 3.22.0 (2018-01-22)
+               # doesn't do what we need: it is only intended for
+               # immutable read-only media (e.g. CD-ROM) and not
+               # usable for our use case described above.
+               #
+               # If an admin is willing to give read-only daemons R/W
+               # permissions; they can enable WAL manually and we will
+               # respect that by not clobbering it.
+               my $jm = $dbh->selectrow_array('PRAGMA journal_mode');
+               $dbh->do('PRAGMA journal_mode = TRUNCATE') if $jm ne 'wal';
+
+               $dbh->do('PRAGMA synchronous = OFF') if $rw > 1;
+       }
        $dbh;
 }
 
@@ -179,6 +197,35 @@ ORDER BY $sort_col DESC
        ($nr, $msgs);
 }
 
+# strict `tid' matches, only, for thread-expanded mbox.gz search results
+# and future CLI interface
+# returns true if we have IDs, undef if not
+sub expand_thread {
+       my ($self, $ctx) = @_;
+       my $dbh = $self->connect;
+       do {
+               defined(my $num = $ctx->{ids}->[0]) or return;
+               my ($tid) = $dbh->selectrow_array(<<'', undef, $num);
+SELECT tid FROM over WHERE num = ?
+
+               if (defined($tid)) {
+                       my $sql = <<'';
+SELECT num FROM over WHERE tid = ? AND num > ?
+ORDER BY num ASC LIMIT 1000
+
+                       my $xids = $dbh->selectcol_arrayref($sql, undef, $tid,
+                                                       $ctx->{prev} // 0);
+                       if (scalar(@$xids)) {
+                               $ctx->{prev} = $xids->[-1];
+                               $ctx->{xids} = $xids;
+                               return 1; # success
+                       }
+               }
+               $ctx->{prev} = 0;
+               shift @{$ctx->{ids}};
+       } while (1);
+}
+
 sub recent {
        my ($self, $opts, $after, $before) = @_;
        my ($s, @v);