[geary: 1/66] Start towards clearing old messages beyond prefetch window

[Michael Gratton <mjog src gnome org>]

commit 880df8691f90aebb393699e3f7c2b4e0faac2510
Author: Chris Heywood <15127-creywood users noreply gitlab gnome org>
Date:   Tue Dec 17 12:43:55 2019 +0100

    Start towards clearing old messages beyond prefetch window
    
    - Add custom optimised query to ImapDB.Folder and use in synchronizer

 src/engine/imap-db/imap-db-folder.vala             | 28 ++++++++++++++++++++++
 .../imap-engine-account-synchronizer.vala          |  8 ++++++-
 2 files changed, 35 insertions(+), 1 deletion(-)
---
diff --git a/src/engine/imap-db/imap-db-folder.vala b/src/engine/imap-db/imap-db-folder.vala
index 9b8541737..381bafde4 100644
--- a/src/engine/imap-db/imap-db-folder.vala
+++ b/src/engine/imap-db/imap-db-folder.vala
@@ -882,6 +882,34 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
         }, cancellable);
     }
 
+    public async void detach_emails_before_timestamp(DateTime cutoff,
+        Cancellable? cancellable) throws Error {
+        warning("Detaching emails before %s for folder ID %", cutoff.to_string(), 
this.folder_id.to_string());
+
+        yield db.exec_transaction_async(Db.TransactionType.WO, (cx) => {
+            // Query was found to be faster than other approaches. MessageLocationTable.ordering
+            // isn't relied on due to IMAP folder UIDs not guaranteed to be in order.
+            StringBuilder sql = new StringBuilder();
+            sql.append("""
+                DELETE FROM MessageLocationTable
+                WHERE folder_id = ?
+                AND message_id IN (
+                    SELECT id
+                    FROM MessageTable
+                    INDEXED BY MessageTableInternalDateTimeTIndex
+                    WHERE internaldate_time_t < ?
+                )
+            """);
+
+            Db.Statement stmt = cx.prepare(sql.str);
+            stmt.bind_rowid(0, this.folder_id);
+            stmt.bind_int64(1, cutoff.to_unix());
+            stmt.exec(cancellable);
+
+            return Db.TransactionOutcome.COMMIT;
+        }, cancellable);
+    }
+
     public async void mark_email_async(Gee.Collection<ImapDB.EmailIdentifier> to_mark,
         Geary.EmailFlags? flags_to_add, Geary.EmailFlags? flags_to_remove, Cancellable? cancellable)
         throws Error {
diff --git a/src/engine/imap-engine/imap-engine-account-synchronizer.vala 
b/src/engine/imap-engine/imap-engine-account-synchronizer.vala
index 5d399f241..f0f2b84cc 100644
--- a/src/engine/imap-engine/imap-engine-account-synchronizer.vala
+++ b/src/engine/imap-engine/imap-engine-account-synchronizer.vala
@@ -243,9 +243,15 @@ private class Geary.ImapEngine.CheckFolderSync : RefreshFolderSync {
             prefetch_max_epoch = this.sync_max_epoch;
         }
 
+        ImapDB.Folder local_folder = ((MinimalFolder) this.folder).local_folder;
+
+        // Detach older emails outside the prefetch window
+        if (this.account.information.prefetch_period_days >= 0) {
+            yield local_folder.detach_emails_before_timestamp(prefetch_max_epoch, cancellable);
+        }
+
         // get oldest local email and its time, as well as number
         // of messages in local store
-        ImapDB.Folder local_folder = ((MinimalFolder) this.folder).local_folder;
         Gee.List<Geary.Email>? list = yield local_folder.list_email_by_id_async(
             null,
             1,