[geary/mjog/search-update: 25/43] Geary.ImapDb.SearchQuery: Handle folder and deleted message exclusions

[Michael Gratton <mjog src gnome org>]

commit 6ffbfcf5d441a9f3ce7de06e0d8a9241da4cf2ec
Author: Michael Gratton <mike vee net>
Date:   Wed Nov 4 22:57:41 2020 +1100

    Geary.ImapDb.SearchQuery: Handle folder and deleted message exclusions
    
    Ensure designated excluded folders, folderless messages, and
    marked-for-deletion messages are excluded from FTS search results.

 src/engine/imap-db/imap-db-account.vala            | 48 +++++++++++++++++++++-
 src/engine/imap-db/imap-db-search-query.vala       | 37 +++++++++++++----
 test/engine/imap-db/imap-db-search-query-test.vala | 42 ++++++++++++++++++-
 3 files changed, 115 insertions(+), 12 deletions(-)
---
diff --git a/src/engine/imap-db/imap-db-account.vala b/src/engine/imap-db/imap-db-account.vala
index 13134d3fd..081281e7b 100644
--- a/src/engine/imap-db/imap-db-account.vala
+++ b/src/engine/imap-db/imap-db-account.vala
@@ -578,7 +578,7 @@ private class Geary.ImapDB.Account : BaseObject {
     }
 
     public async Gee.Collection<Geary.EmailIdentifier>? search_async(Geary.SearchQuery q,
-        int limit = 100, int offset = 0, Gee.Collection<Geary.FolderPath?>? folder_blacklist = null,
+        int limit = 100, int offset = 0, Gee.Collection<Geary.FolderPath?>? excluded_folders = null,
         Gee.Collection<Geary.EmailIdentifier>? search_ids = null, Cancellable? cancellable = null)
         throws Error {
 
@@ -595,10 +595,22 @@ private class Geary.ImapDB.Account : BaseObject {
         Gee.Map<EmailIdentifier,Gee.Set<string>>? search_matches = null;
 
         yield db.exec_transaction_async(RO, (cx) => {
+            string? excluded_folder_ids_sql = null;
+            bool exclude_folderless = false;
+            if (excluded_folders != null) {
+                excluded_folder_ids_sql = do_get_excluded_folder_ids(
+                    excluded_folders, cx, out exclude_folderless, cancellable
+                );
+            }
+
             var id_map = new Gee.HashMap<int64?, ImapDB.EmailIdentifier>(
                 Collection.int64_hash_func, Collection.int64_equal_func);
             Db.Statement stmt = query.get_search_query(
-                cx, search_ids_sql, folder_blacklist, limit, offset, cancellable
+                cx,
+                search_ids_sql,
+                excluded_folder_ids_sql,
+                exclude_folderless,
+                limit, offset
             );
             Db.Result result = stmt.exec(cancellable);
             while (!result.finished) {
@@ -1277,6 +1289,38 @@ private class Geary.ImapDB.Account : BaseObject {
         }
     }
 
+    // Turn the collection of folder paths into actual folder ids.  As a
+    // special case, if "folderless" or orphan emails are to be excluded,
+    // set the out bool to true.
+    private string do_get_excluded_folder_ids(
+        Gee.Collection<Geary.FolderPath?> excluded_folder,
+        Db.Connection cx,
+        out bool exclude_folderless,
+        GLib.Cancellable? cancellable
+    ) throws GLib.Error {
+        exclude_folderless = false;
+
+        var ids = new GLib.StringBuilder();
+        var is_first = true;
+        foreach (Geary.FolderPath? folder_path in excluded_folder) {
+            if (folder_path == null) {
+                exclude_folderless = true;
+            } else {
+                int64 id;
+                do_fetch_folder_id(cx, folder_path, true, out id, cancellable);
+                if (id != Db.INVALID_ROWID) {
+                    if (!is_first) {
+                        ids.append_c(',');
+                    }
+                    ids.append(id.to_string());
+                    is_first = false;
+                }
+            }
+        }
+
+        return ids.str;
+    }
+
     private inline void check_open() throws GLib.Error {
         if (!this.db.is_open) {
             throw new EngineError.OPEN_REQUIRED("Database not open");
diff --git a/src/engine/imap-db/imap-db-search-query.vala b/src/engine/imap-db/imap-db-search-query.vala
index e03e2d0ff..24ae267ff 100644
--- a/src/engine/imap-db/imap-db-search-query.vala
+++ b/src/engine/imap-db/imap-db-search-query.vala
@@ -45,19 +45,35 @@ private class Geary.ImapDB.SearchQuery : Geary.SearchQuery {
     internal Db.Statement get_search_query(
         Db.Connection cx,
         string? search_ids_sql,
-        Gee.Collection<Geary.FolderPath>? folder_blacklist,
+        string? excluded_folder_ids_sql,
+        bool exclude_folderless,
         int limit,
-        int offset,
-        GLib.Cancellable? cancellable
+        int offset
     ) throws GLib.Error {
         var sql = new GLib.StringBuilder();
-        var conditions_added = false;
 
         sql.append("""
-            SELECT mst.rowid
-            FROM MessageSearchTable as mst
-            INNER JOIN MessageTable AS mt ON mt.id = mst.rowid
-            WHERE""");
+                SELECT DISTINCT mst.rowid
+                FROM MessageSearchTable as mst
+                INNER JOIN MessageTable AS mt ON mt.id = mst.rowid""");
+        if (exclude_folderless) {
+            sql.append("""
+                INNER JOIN MessageLocationTable AS mlt ON mt.id = mlt.message_id""");
+        } else {
+            sql.append("""
+                LEFT JOIN MessageLocationTable AS mlt ON mt.id = mlt.message_id""");
+        }
+
+        var conditions_added = false;
+        sql.append("""
+                WHERE""");
+        if (excluded_folder_ids_sql != null) {
+            sql.append_printf(
+                " mlt.folder_id NOT IN (%s)",
+                excluded_folder_ids_sql
+            );
+            conditions_added = true;
+        }
         conditions_added = sql_add_term_conditions(sql, conditions_added);
         if (!String.is_empty(search_ids_sql)) {
             if (conditions_added) {
@@ -65,6 +81,11 @@ private class Geary.ImapDB.SearchQuery : Geary.SearchQuery {
             }
             sql.append(""" id IN (%s)""".printf(search_ids_sql));
         }
+        if (conditions_added) {
+            sql.append(" AND");
+        }
+        // Exclude deleted messages, but not folderless messages
+        sql.append(" mlt.remove_marker IN (0, null)");
         sql.append("""
                 ORDER BY mt.internaldate_time_t DESC""");
         if (limit > 0) {
diff --git a/test/engine/imap-db/imap-db-search-query-test.vala 
b/test/engine/imap-db/imap-db-search-query-test.vala
index 510402611..571e63195 100644
--- a/test/engine/imap-db/imap-db-search-query-test.vala
+++ b/test/engine/imap-db/imap-db-search-query-test.vala
@@ -20,6 +20,7 @@ public class Geary.ImapDB.SearchQueryTest : TestCase {
         add_test("email_text_terms_stemmed", email_text_terms_stemmed);
         add_test("email_text_terms_specific", email_text_terms_specific);
         add_test("email_flag_terms", email_flag_terms);
+        add_test("excluded_folders", excluded_folders);
     }
 
     public override void set_up() throws GLib.Error {
@@ -169,6 +170,43 @@ public class Geary.ImapDB.SearchQueryTest : TestCase {
         assert_queries(flagged);
     }
 
+    public void excluded_folders() throws GLib.Error {
+        var query = new_search_query(
+            { new Geary.SearchQuery.EmailTextTerm(ALL, EXACT, "test")},
+            "test"
+        );
+
+        var search_with_excluded_ids = query.get_search_query(
+            this.account.db.get_primary_connection(),
+            null,
+            "10,20,30,40",
+            false,
+            10,
+            0
+        );
+        search_with_excluded_ids.exec(null);
+
+        var search_with_exclude_folderless = query.get_search_query(
+            this.account.db.get_primary_connection(),
+            null,
+            null,
+            true,
+            10,
+            0
+        );
+        search_with_exclude_folderless.exec(null);
+
+        var search_with_both = query.get_search_query(
+            this.account.db.get_primary_connection(),
+            null,
+            "10,20,30,40",
+            true,
+            10,
+            0
+        );
+        search_with_both.exec(null);
+    }
+
     private SearchQuery new_search_query(Geary.SearchQuery.Term[] ops, string raw)
         throws GLib.Error {
         return new SearchQuery(
@@ -183,9 +221,9 @@ public class Geary.ImapDB.SearchQueryTest : TestCase {
             this.account.db.get_primary_connection(),
             null,
             null,
-            0,
+            false,
             10,
-            null
+            0
         );
         search.exec(null);