[geary/wip/search-docid] Use docid instead of id in search table

[Charles Lindsay <clindsay src gnome org>]

commit 4465074dedfd7aadb32e81e04c7b722f5a8a3164
Author: Charles Lindsay <chaz yorba org>
Date:   Fri Mar 7 12:19:36 2014 -0800

    Use docid instead of id in search table
    
    We had previously included an 'id INTEGER PRIMARY KEY' column in the
    MessageSearchTable, assuming it would get the same rowid alias treatment
    as it does in non-FTS tables.  That assumption was wrong: it was being
    created as a FTS column.  This fixes it so we use docid everywhere.
    
    To fix the old incorrect docid values, we simply blow away the search
    table and let the natural search table population process, which now has
    the correct docid insertion code, fix the problem.
    
    This also removes the id column from the search table creation SQL, but
    this will only affect new users.  Upgraders will see an empty, vestigal
    id column in their search table.  Since SQLite doesn't easily let you
    remove columns, it's just easier to ignore the column than go through
    all the work to fix it.

 sql/CMakeLists.txt                       |    1 +
 sql/version-020.sql                      |    7 +++++++
 src/engine/imap-db/imap-db-account.vala  |    6 +++---
 src/engine/imap-db/imap-db-database.vala |    5 ++---
 src/engine/imap-db/imap-db-folder.vala   |   12 ++++++------
 5 files changed, 19 insertions(+), 12 deletions(-)
---
diff --git a/sql/CMakeLists.txt b/sql/CMakeLists.txt
index 84ad3e4..be17579 100644
--- a/sql/CMakeLists.txt
+++ b/sql/CMakeLists.txt
@@ -19,3 +19,4 @@ install(FILES version-016.sql DESTINATION ${SQL_DEST})
 install(FILES version-017.sql DESTINATION ${SQL_DEST})
 install(FILES version-018.sql DESTINATION ${SQL_DEST})
 install(FILES version-019.sql DESTINATION ${SQL_DEST})
+install(FILES version-020.sql DESTINATION ${SQL_DEST})
diff --git a/sql/version-020.sql b/sql/version-020.sql
new file mode 100644
index 0000000..8799b6e
--- /dev/null
+++ b/sql/version-020.sql
@@ -0,0 +1,7 @@
+--
+-- We had previously incorrectly included an id column in the search table.
+-- The code is fixed to use docid instead, so we just empty the table and let
+-- the natural search table population process make things right.
+--
+
+DELETE FROM MessageSearchTable
diff --git a/src/engine/imap-db/imap-db-account.vala b/src/engine/imap-db/imap-db-account.vala
index 1d4c1bb..034cb62 100644
--- a/src/engine/imap-db/imap-db-account.vala
+++ b/src/engine/imap-db/imap-db-account.vala
@@ -887,7 +887,7 @@ private class Geary.ImapDB.Account : BaseObject {
                 FROM MessageTable
                 INDEXED BY MessageTableInternalDateTimeTIndex
                 WHERE id IN (
-                    SELECT id
+                    SELECT docid
                     FROM MessageSearchTable
                     WHERE 1=1
             """);
@@ -958,7 +958,7 @@ private class Geary.ImapDB.Account : BaseObject {
             sql.append("""
                 SELECT offsets(MessageSearchTable), *
                 FROM MessageSearchTable
-                WHERE id IN (
+                WHERE docid IN (
             """);
             sql_append_ids(sql,
                 Geary.traverse<ImapDB.EmailIdentifier>(ids).map<int64?>(id => 
id.message_id).to_gee_iterable());
@@ -1126,7 +1126,7 @@ private class Geary.ImapDB.Account : BaseObject {
                 SELECT id
                 FROM MessageTable
                 WHERE id NOT IN (
-                    SELECT id
+                    SELECT docid
                     FROM MessageSearchTable
                 )
                 LIMIT ?
diff --git a/src/engine/imap-db/imap-db-database.vala b/src/engine/imap-db/imap-db-database.vala
index a594356..ef297db 100644
--- a/src/engine/imap-db/imap-db-database.vala
+++ b/src/engine/imap-db/imap-db-database.vala
@@ -158,7 +158,6 @@ private class Geary.ImapDB.Database : Geary.Db.VersionedDatabase {
             // algorithm) is determined at runtime.
             exec("""
                 CREATE VIRTUAL TABLE MessageSearchTable USING fts4(
-                    id INTEGER PRIMARY KEY,
                     body,
                     attachment,
                     subject,
@@ -394,8 +393,8 @@ private class Geary.ImapDB.Database : Geary.Db.VersionedDatabase {
                 
                 return Db.TransactionOutcome.COMMIT;
             });
-        } catch (Error err) {
-            debug("Error populating autocompletion table during upgrade to database schema 5");
+        } catch (Error e) {
+            debug("Error fixing up contacts table: %s", e.message);
         }
     }
     
diff --git a/src/engine/imap-db/imap-db-folder.vala b/src/engine/imap-db/imap-db-folder.vala
index bc9370c..8636877 100644
--- a/src/engine/imap-db/imap-db-folder.vala
+++ b/src/engine/imap-db/imap-db-folder.vala
@@ -1332,7 +1332,7 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
         
         Db.Statement stmt = cx.prepare("""
             INSERT INTO MessageSearchTable
-                (id, body, attachment, subject, from_field, receivers, cc, bcc)
+                (docid, body, attachment, subject, from_field, receivers, cc, bcc)
             VALUES (?, ?, ?, ?, ?, ?, ?, ?)
         """);
         stmt.bind_rowid(0, message_id);
@@ -1349,7 +1349,7 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
     
     private static bool do_check_for_message_search_row(Db.Connection cx, int64 message_id,
         Cancellable? cancellable) throws Error {
-        Db.Statement stmt = cx.prepare("SELECT 'TRUE' FROM MessageSearchTable WHERE id=?");
+        Db.Statement stmt = cx.prepare("SELECT 'TRUE' FROM MessageSearchTable WHERE docid=?");
         stmt.bind_rowid(0, message_id);
         
         Db.Result result = stmt.exec(cancellable);
@@ -1730,7 +1730,7 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
             }
             
             Db.Statement stmt = cx.prepare(
-                "UPDATE MessageSearchTable SET body=?, attachment=?, receivers=? WHERE id=?");
+                "UPDATE MessageSearchTable SET body=?, attachment=?, receivers=? WHERE docid=?");
             stmt.bind_string(0, body);
             stmt.bind_string(1, email.get_searchable_attachment_list());
             stmt.bind_string(2, recipients);
@@ -1741,7 +1741,7 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
         
         if (new_fields.is_any_set(Geary.Email.Field.SUBJECT)) {
             Db.Statement stmt = cx.prepare(
-                "UPDATE MessageSearchTable SET subject=? WHERE id=?");
+                "UPDATE MessageSearchTable SET subject=? WHERE docid=?");
             stmt.bind_string(0, (email.subject != null ? email.subject.to_searchable_string() : null));
             stmt.bind_rowid(1, message_id);
             
@@ -1750,7 +1750,7 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
         
         if (new_fields.is_any_set(Geary.Email.Field.ORIGINATORS)) {
             Db.Statement stmt = cx.prepare(
-                "UPDATE MessageSearchTable SET from_field=? WHERE id=?");
+                "UPDATE MessageSearchTable SET from_field=? WHERE docid=?");
             stmt.bind_string(0, (email.from != null ? email.from.to_searchable_string() : null));
             stmt.bind_rowid(1, message_id);
             
@@ -1759,7 +1759,7 @@ private class Geary.ImapDB.Folder : BaseObject, Geary.ReferenceSemantics {
         
         if (new_fields.is_any_set(Geary.Email.Field.RECEIVERS)) {
             Db.Statement stmt = cx.prepare(
-                "UPDATE MessageSearchTable SET cc=?, bcc=? WHERE id=?");
+                "UPDATE MessageSearchTable SET cc=?, bcc=? WHERE docid=?");
             stmt.bind_string(0, (email.cc != null ? email.cc.to_searchable_string() : null));
             stmt.bind_string(1, (email.bcc != null ? email.bcc.to_searchable_string() : null));
             stmt.bind_rowid(2, message_id);