[PATCH 4/7] view: avoid offset during pagination

["Eric Wong (Contractor, The Linux Foundation)" <e@80x24.org>]

OFFSET in SQLite gets painful to deal with.  Instead,
rely on timestamps (from Received:) for pagination.
This also sets us up for more precise Date searching
in case we want it.
---
 lib/PublicInbox/Feed.pm  | 25 ++------------
 lib/PublicInbox/Inbox.pm |  4 +--
 lib/PublicInbox/Over.pm  | 24 ++++++++++---
 lib/PublicInbox/View.pm  | 90 ++++++++++++++++++++++++++++++++++--------------
 4 files changed, 89 insertions(+), 54 deletions(-)

diff --git a/lib/PublicInbox/Feed.pm b/lib/PublicInbox/Feed.pm
index ff20d7f..5cb044b 100644
--- a/lib/PublicInbox/Feed.pm
+++ b/lib/PublicInbox/Feed.pm
@@ -75,7 +75,7 @@ sub new_html {
 			my $more = scalar @$msgs;
 			return PublicInbox::View::index_entry($m, $ctx, $more);
 		}
-		new_html_footer($ctx);
+		PublicInbox::View::pagination_footer($ctx, './new.html');
 	});
 }
 
@@ -85,21 +85,6 @@ sub _no_thread () {
 	[404, ['Content-Type', 'text/plain'], ["No feed found for thread\n"]];
 }
 
-sub new_html_footer {
-	my ($ctx) = @_;
-	my $qp = delete $ctx->{qp} or return;
-	my $latest = '';
-	my $next = delete $ctx->{next_page} || '';
-	if ($next) {
-		$next = qq!<a\nhref="?$next"\nrel=next>next</a>!;
-	}
-	if (!$qp) {
-		$latest = qq! <a\nhref='./new.html'>latest</a>!;
-		$next ||= '    ';
-	}
-	"<hr><pre>page: $next$latest</pre>";
-}
-
 sub recent_msgs {
 	my ($ctx) = @_;
 	my $ibx = $ctx->{-inbox};
@@ -110,13 +95,7 @@ sub recent_msgs {
 		die "BUG: unsupported inbox version: $v\n";
 	}
 	if (my $srch = $ibx->search) {
-		my $o = $qp ? $qp->{o} : 0;
-		$o += 0;
-		$o = 0 if $o < 0;
-		my $msgs = $ibx->recent({ limit => $max, offset => $o });
-		my $next = $o + $max;
-		$ctx->{next_page} = "o=$next" if scalar(@$msgs) == $max;
-		return $msgs;
+		return PublicInbox::View::paginate_recent($ctx);
 	}
 
 	my $hex = '[a-f0-9]';
diff --git a/lib/PublicInbox/Inbox.pm b/lib/PublicInbox/Inbox.pm
index 142b5c8..0ea18b4 100644
--- a/lib/PublicInbox/Inbox.pm
+++ b/lib/PublicInbox/Inbox.pm
@@ -318,8 +318,8 @@ sub msg_by_mid ($$;$) {
 }
 
 sub recent {
-	my ($self, $opts) = @_;
-	search($self)->query('', $opts);
+	my ($self, $opts, $after, $before) = @_;
+	search($self)->{over_ro}->recent($opts, $after, $before);
 }
 
 1;
diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm
index a7fd131..b230d44 100644
--- a/lib/PublicInbox/Over.pm
+++ b/lib/PublicInbox/Over.pm
@@ -109,10 +109,26 @@ SELECT COUNT(num) $cond
 }
 
 sub recent {
-	my ($self, $opts) = @_;
-	my $msgs = do_get($self, <<'', $opts);
-SELECT * FROM over WHERE num > 0
-ORDER BY ts DESC
+	my ($self, $opts, $after, $before) = @_;
+	my ($s, @v);
+	if (defined($before)) {
+		if (defined($after)) {
+			$s = 'num > 0 AND ts >= ? AND ts <= ? ORDER BY ts DESC';
+			@v = ($after, $before);
+		} else {
+			$s = 'num > 0 AND ts <= ? ORDER BY ts DESC';
+			@v = ($before);
+		}
+	} else {
+		if (defined($after)) {
+			$s = 'num > 0 AND ts >= ? ORDER BY ts ASC';
+			@v = ($after);
+		} else {
+			$s = 'num > 0 ORDER BY ts DESC';
+		}
+	}
+	my $msgs = do_get($self, <<"", $opts, @v);
+SELECT * FROM over WHERE $s
 
 	return $msgs unless wantarray;
 
diff --git a/lib/PublicInbox/View.pm b/lib/PublicInbox/View.pm
index cad90a7..cbed916 100644
--- a/lib/PublicInbox/View.pm
+++ b/lib/PublicInbox/View.pm
@@ -15,6 +15,7 @@ use PublicInbox::Address;
 use PublicInbox::WwwStream;
 use PublicInbox::Reply;
 require POSIX;
+use Time::Local qw(timegm);
 
 use constant INDENT => '  ';
 use constant TCHILD => '` ';
@@ -1032,43 +1033,82 @@ sub dump_topics {
 	200;
 }
 
+sub ts2str ($) {
+	my ($ts) = @_;
+	POSIX::strftime('%Y%m%d%H%M%S', gmtime($ts));
+}
+
+sub str2ts ($) {
+	my ($yyyy, $mon, $dd, $hh, $mm, $ss) = unpack('A4A2A2A2A2A2', $_[0]);
+	timegm($ss, $mm, $hh, $dd, $mon - 1, $yyyy);
+}
+
+sub pagination_footer ($$) {
+	my ($ctx, $latest) = @_;
+	delete $ctx->{qp} or return;
+	my $next = $ctx->{next_page} || '';
+	my $prev = $ctx->{prev_page} || '';
+	if ($prev) {
+		$next = $next ? "$next " : '     ';
+		$prev .= qq! <a\nhref='$latest'>latest</a>!;
+	}
+	"<hr><pre>page: $next$prev</pre>";
+}
+
 sub index_nav { # callback for WwwStream
 	my (undef, $ctx) = @_;
-	delete $ctx->{qp} or return;
-	my ($next, $prev);
-	$next = $prev = '    ';
-	my $latest = '';
+	pagination_footer($ctx, '.')
+}
+
+sub paginate_recent ($) {
+	my ($ctx) = @_;
+	my $t = $ctx->{qp}->{t} || '';
+	my $lim = 200; # this is our window
+	my $opts = { limit => $lim };
+	my ($after, $before);
+
+	# Xapian uses '..' but '-' is perhaps friendier to URL linkifiers
+	# if only $after exists "YYYYMMDD.." because "." could be skipped
+	# if interpreted as an end-of-sentence
+	$t =~ s/\A(\d{8,14})-// and $after = str2ts($1);
+	$t =~ /\A(\d{8,14})\z/ and $before = str2ts($1);
 
-	my $next_o = $ctx->{-next_o};
-	if ($next_o) {
-		$next = qq!<a\nhref="?o=$next_o"\nrel=next>next</a>!;
+	my $ibx = $ctx->{-inbox};
+	my $msgs = $ibx->recent($opts, $after, $before);
+	my $nr = scalar @$msgs;
+	if ($nr < $lim && defined($after)) {
+		$after = $before = undef;
+		$msgs = $ibx->recent($opts);
+		$nr = scalar @$msgs;
 	}
-	if (my $cur_o = $ctx->{-cur_o}) {
-		$latest = qq! <a\nhref=.>latest</a>!;
-
-		my $o = $cur_o - ($next_o - $cur_o);
-		if ($o > 0) {
-			$prev = qq!<a\nhref="?o=$o"\nrel=prev>prev</a>!;
-		} elsif ($o == 0) {
-			$prev = qq!<a\nhref=.\nrel=prev>prev</a>!;
+	my $more = $nr == $lim;
+	my ($newest, $oldest);
+	if ($nr) {
+		$newest = $msgs->[0]->{ts};
+		$oldest = $msgs->[-1]->{ts};
+		# if we only had $after, our SQL query in ->recent ordered
+		if ($newest < $oldest) {
+			($oldest, $newest) = ($newest, $oldest);
+			$more = 0 if defined($after) && $after < $oldest;
 		}
 	}
-	"<hr><pre>page: $next $prev$latest</pre>";
+	if (defined($oldest) && $more) {
+		my $s = ts2str($oldest);
+		$ctx->{next_page} = qq!<a\nhref="?t=$s"\nrel=next>next</a>!;
+	}
+	if (defined($newest) && (defined($before) || defined($after))) {
+		my $s = ts2str($newest);
+		$ctx->{prev_page} = qq!<a\nhref="?t=$s-"\nrel=prev>prev</a>!;
+	}
+	$msgs;
 }
 
 sub index_topics {
 	my ($ctx) = @_;
-	my ($off) = (($ctx->{qp}->{o} || '0') =~ /(\d+)/);
-
-	$ctx->{order} = [];
-	my $srch = $ctx->{srch};
-	my $msgs = $ctx->{-inbox}->recent({offset => $off, limit => 200 });
-	my $nr = scalar @$msgs;
-	if ($nr) {
+	my $msgs = paginate_recent($ctx);
+	if (@$msgs) {
 		walk_thread(thread_results($ctx, $msgs), $ctx, *acc_topic);
 	}
-	$ctx->{-next_o} = $off + $nr;
-	$ctx->{-cur_o} = $off;
 	PublicInbox::WwwStream->response($ctx, dump_topics($ctx), *index_nav);
 }
 
-- 
EW