[geary: 1/3] Retain a number of messages when performing GC

[Michael Gratton <mjog src gnome org>]

commit 497d9906bdbca4106d06ec6ac954a4f79f54b4bb
Author: Chris Heywood <15127-creywood users noreply gitlab gnome org>
Date:   Mon Aug 24 18:27:24 2020 +1000

    Retain a number of messages when performing GC
    
    Avoids behaviour described in #937 where messages can be repetitively
    detached and downloaded if they are outside the account download
    threshold but at the top of the folder.

 src/engine/imap-db/imap-db-folder.vala | 21 +++++++++++++--------
 1 file changed, 13 insertions(+), 8 deletions(-)
---
diff --git a/src/engine/imap-db/imap-db-folder.vala b/src/engine/imap-db/imap-db-folder.vala
index fd796796d..52f3e82fb 100644
--- a/src/engine/imap-db/imap-db-folder.vala
+++ b/src/engine/imap-db/imap-db-folder.vala
@@ -43,6 +43,10 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
     private const int CREATE_MERGE_EMAIL_CHUNK_COUNT = 25;
     private const int OLD_MSG_DETACH_BATCH_SIZE = 1000;
 
+    // When old messages beyond the period set in the account preferences are removed this number 
+    // are retained even if they are beyond the threshold.
+    private const int MINIMUM_MESSAGES_TO_RETAIN_DURING_GC = 100;
+
     [Flags]
     public enum ListFlags {
         NONE = 0,
@@ -943,20 +947,21 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
             // UIDs not guaranteed to be in order.
             StringBuilder sql = new StringBuilder();
             sql.append("""
-                SELECT id, message_id, ordering
-                FROM MessageLocationTable
+                SELECT ml.id, ml.message_id, ml.ordering
+                FROM MessageLocationTable ml
+                INNER JOIN MessageTable m
+                INDEXED BY MessageTableInternalDateTimeTIndex
+                    ON ml.message_id = m.id
                 WHERE folder_id = ?
-                AND message_id IN (
-                    SELECT id
-                    FROM MessageTable
-                    INDEXED BY MessageTableInternalDateTimeTIndex
-                    WHERE internaldate_time_t < ?
-                )
+                AND m.internaldate_time_t < ?
+                ORDER BY m.internaldate_time_t DESC
+                LIMIT -1 OFFSET ?;
             """);
 
             Db.Statement stmt = cx.prepare(sql.str);
             stmt.bind_rowid(0, folder_id);
             stmt.bind_int64(1, cutoff.to_unix());
+            stmt.bind_int64(2, MINIMUM_MESSAGES_TO_RETAIN_DURING_GC);
 
             Db.Result results = stmt.exec(cancellable);