From b9534449ecce2c59bb4aebad6051f91c3116b187 Mon Sep 17 00:00:00 2001
From: "Eric Wong (Contractor, The Linux Foundation)" <e@80x24.org>
Date: Tue, 3 Apr 2018 11:09:09 +0000
Subject: view: avoid offset during pagination

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/Over.pm | 24 ++++++++++++++++++++----
 1 file changed, 20 insertions(+), 4 deletions(-)

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

diff --git a/lib/PublicInbox/Over.pm b/lib/PublicInbox/Over.pm
index a7fd1315..b230d44a 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;
 
-- 
cgit v1.2.3-24-ge0c7