Add lastmsgid limit on fetching backlog to SQLite

Copy lastmsgid fetch limit improvements from PostgreSQL to SQLite,
improving performance by not requiring scanning from the start of the
backlog table to the present.

This builds upon pull request #273, including the original intent of
improving performance for unread backlog fetching.  The original pull
request left out these lines when applying the PostgreSQL changes to
SQLite.

NOTE:  The client-side generating these invalid values should be
fixed in future commits.

diff --git a/src/core/SQL/SQLite/21/select_messagesNewerThan.sql b/src/core/SQL/SQLite/21/select_messagesNewerThan.sql
index 69d948d..bac2a04 100644 (file)
--- a/src/core/SQL/SQLite/21/select_messagesNewerThan.sql
+++ b/src/core/SQL/SQLite/21/select_messagesNewerThan.sql
@@ -1,7 +1,8 @@
 SELECT messageid, time,  type, flags, sender, message
 FROM backlog
 JOIN sender ON backlog.senderid = sender.senderid
-WHERE bufferid = :bufferid
-    AND backlog.messageid >= :firstmsg
+WHERE backlog.messageid >= :firstmsg
+    AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid)
+    AND bufferid = :bufferid
 ORDER BY messageid DESC
 LIMIT :limit

diff --git a/src/core/SQL/SQLite/21/select_messagesNewestK.sql b/src/core/SQL/SQLite/21/select_messagesNewestK.sql
index 6cc81cc..6f86e70 100644 (file)
--- a/src/core/SQL/SQLite/21/select_messagesNewestK.sql
+++ b/src/core/SQL/SQLite/21/select_messagesNewestK.sql
@@ -2,5 +2,6 @@ SELECT messageid, time,  type, flags, sender, message
 FROM backlog
 JOIN sender ON backlog.senderid = sender.senderid
 WHERE bufferid = :bufferid
+AND backlog.messageid <= (SELECT buffer.lastmsgid FROM buffer WHERE buffer.bufferid = :bufferid)
 ORDER BY messageid DESC
 LIMIT :limit