X-Git-Url: http://www.git.stargrave.org/?a=blobdiff_plain;f=lib%2FPublicInbox%2FOver.pm;h=19da056a10aff5de4455e80c1e30b03190352258;hb=d7780e12856d83b9f5c58586691e2fbe711321ed;hp=a055b4cdbe6114061f3e20d3dfb782418521b304;hpb=5a69dffc4155c8810dd8f2f3474f7a8a5c6d16a2;p=public-inbox.git
diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm
index a055b4cd..19da056a 100644
--- a/lib/PublicInbox/Over.pm
+++ b/lib/PublicInbox/Over.pm
@@ -1,4 +1,4 @@
-# Copyright (C) 2018-2020 all contributors
+# Copyright (C) 2018-2021 all contributors
# License: AGPL-3.0+
# for XOVER, OVER in NNTP, and feeds/homepage/threads in PSGI
@@ -6,8 +6,8 @@
# tweaked/updated over time and rebuilt.
package PublicInbox::Over;
use strict;
-use warnings;
-use DBI;
+use v5.10.1;
+use DBI qw(:sql_types); # SQL_BLOB
use DBD::SQLite;
use PublicInbox::Smsg;
use Compress::Zlib qw(uncompress);
@@ -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;
}
@@ -54,7 +72,7 @@ sub new {
bless { filename => $f }, $class;
}
-sub disconnect {
+sub dbh_close {
my ($self) = @_;
if (my $dbh = delete $self->{dbh}) {
delete $self->{-get_art};
@@ -62,7 +80,7 @@ sub disconnect {
}
}
-sub connect { $_[0]->{dbh} //= $_[0]->dbh_new }
+sub dbh ($) { $_[0]->{dbh} //= $_[0]->dbh_new } # dbh_new may be subclassed
sub load_from_row ($;$) {
my ($smsg, $cull) = @_;
@@ -79,10 +97,9 @@ sub load_from_row ($;$) {
sub do_get {
my ($self, $sql, $opts, @args) = @_;
- my $dbh = $self->connect;
my $lim = (($opts->{limit} || 0) + 0) || DEFAULT_LIMIT;
$sql .= "LIMIT $lim";
- my $msgs = $dbh->selectall_arrayref($sql, { Slice => {} }, @args);
+ my $msgs = dbh($self)->selectall_arrayref($sql, { Slice => {} }, @args);
my $cull = $opts->{cull};
load_from_row($_, $cull) for @$msgs;
$msgs
@@ -109,7 +126,7 @@ sub get_all {
my $nr = scalar(@_) or return [];
my $in = '?' . (',?' x ($nr - 1));
do_get($self, <<"", { cull => 1, limit => $nr }, @_);
-SELECT num,ds,ddd FROM over WHERE num IN ($in)
+SELECT num,ts,ds,ddd FROM over WHERE num IN ($in)
}
@@ -117,7 +134,7 @@ sub nothing () { wantarray ? (0, []) : [] };
sub get_thread {
my ($self, $mid, $prev) = @_;
- my $dbh = $self->connect;
+ my $dbh = dbh($self);
my $opts = { cull => 1 };
my $id = $dbh->selectrow_array(<<'', undef, $mid);
@@ -179,6 +196,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 = dbh($self);
+ 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);
@@ -198,31 +244,46 @@ sub recent {
$s = '+num > 0 ORDER BY ts DESC';
}
}
- my $msgs = do_get($self, <<"", $opts, @v);
+ do_get($self, <<"", $opts, @v);
SELECT ts,ds,ddd FROM over WHERE $s
- return $msgs unless wantarray;
-
- my $nr = $self->{dbh}->selectrow_array(<<'');
-SELECT COUNT(num) FROM over WHERE num > 0
-
- ($nr, $msgs);
}
sub get_art {
my ($self, $num) = @_;
# caching $sth ourselves is faster than prepare_cached
- my $sth = $self->{-get_art} //= $self->connect->prepare(<<'');
-SELECT num,ds,ts,ddd FROM over WHERE num = ? LIMIT 1
+ my $sth = $self->{-get_art} //= dbh($self)->prepare(<<'');
+SELECT num,tid,ds,ts,ddd FROM over WHERE num = ? LIMIT 1
$sth->execute($num);
my $smsg = $sth->fetchrow_hashref;
$smsg ? load_from_row($smsg) : undef;
}
+sub get_xref3 {
+ my ($self, $num, $raw) = @_;
+ my $dbh = dbh($self);
+ my $sth = $dbh->prepare_cached(<<'', undef, 1);
+SELECT ibx_id,xnum,oidbin FROM xref3 WHERE docid = ? ORDER BY ibx_id,xnum ASC
+
+ $sth->execute($num);
+ my $rows = $sth->fetchall_arrayref;
+ return $rows if $raw;
+ my $eidx_key_sth = $dbh->prepare_cached(<<'', undef, 1);
+SELECT eidx_key FROM inboxes WHERE ibx_id = ?
+
+ [ map {
+ my $r = $_;
+ $eidx_key_sth->execute($r->[0]);
+ my $eidx_key = $eidx_key_sth->fetchrow_array;
+ $eidx_key //= "missing://ibx_id=$r->[0]";
+ "$eidx_key:$r->[1]:".unpack('H*', $r->[2]);
+ } @$rows ];
+}
+
sub next_by_mid {
my ($self, $mid, $id, $prev) = @_;
- my $dbh = $self->connect;
+ my $dbh = dbh($self);
unless (defined $$id) {
my $sth = $dbh->prepare_cached(<<'', undef, 1);
@@ -246,7 +307,7 @@ ORDER BY num ASC LIMIT 1
# IMAP search, this is limited by callers to UID_SLICE size (50K)
sub uid_range {
my ($self, $beg, $end, $sql) = @_;
- my $dbh = $self->connect;
+ my $dbh = dbh($self);
my $q = 'SELECT num FROM over WHERE num >= ? AND num <= ?';
# This is read-only, anyways; but caller should verify it's
@@ -258,7 +319,7 @@ sub uid_range {
sub max {
my ($self) = @_;
- my $sth = $self->connect->prepare_cached(<<'', undef, 1);
+ my $sth = dbh($self)->prepare_cached(<<'', undef, 1);
SELECT MAX(num) FROM over WHERE num > 0
$sth->execute;
@@ -267,7 +328,7 @@ SELECT MAX(num) FROM over WHERE num > 0
sub imap_exists {
my ($self, $uid_base, $uid_end) = @_;
- my $sth = $self->connect->prepare_cached(<<'', undef, 1);
+ my $sth = dbh($self)->prepare_cached(<<'', undef, 1);
SELECT COUNT(num) FROM over WHERE num > ? AND num <= ?
$sth->execute($uid_base, $uid_end);
@@ -282,10 +343,43 @@ sub check_inodes {
my $st = pack('dd', $st[0], $st[1]);
# don't actually reopen, just let {dbh} be recreated later
- disconnect($self) if $st ne ($self->{st} // $st);
+ dbh_close($self) if $st ne ($self->{st} // $st);
} else {
warn "W: stat $f: $!\n";
}
}
+sub oidbin_exists {
+ my ($self, $oidbin) = @_;
+ if (wantarray) {
+ my $sth = $self->dbh->prepare_cached(<<'', undef, 1);
+SELECT docid FROM xref3 WHERE oidbin = ? ORDER BY docid ASC
+
+ $sth->bind_param(1, $oidbin, SQL_BLOB);
+ $sth->execute;
+ my $tmp = $sth->fetchall_arrayref;
+ map { $_->[0] } @$tmp;
+ } else {
+ my $sth = $self->dbh->prepare_cached(<<'', undef, 1);
+SELECT COUNT(*) FROM xref3 WHERE oidbin = ?
+
+ $sth->bind_param(1, $oidbin, SQL_BLOB);
+ $sth->execute;
+ $sth->fetchrow_array;
+ }
+}
+
+sub blob_exists { oidbin_exists($_[0], pack('H*', $_[1])) }
+
+# used by NNTP.pm
+sub ids_after {
+ my ($self, $num) = @_;
+ my $ids = dbh($self)->selectcol_arrayref(<<'', undef, $$num);
+SELECT num FROM over WHERE num > ?
+ORDER BY num ASC LIMIT 1000
+
+ $$num = $ids->[-1] if @$ids;
+ $ids;
+}
+
1;