Change trigger to allow duplicate messages in search folders
b/11294681
b/11325976
The problem is that when we get a message as part of a search
result, we'll end up deleting that message from the inbox (or
whatever folder it's currently in). This is because there is
a trigger that deletes messages if a new message is inserted
that has the same serverId and account.
Now, messages with duplicate serverId/account combinations are
allowed if one of the messages is in a SEARCH type folder.
Also, make a change so that when a message comes down in
a search result, we do also copy it into the primary mailbox
that message resides in, we only add it to the SEARCH folder.
Prior to this there was some code that intended to put
the search result message into the regular mailbox it's supposed
to be in, so that we'd have correct state in that message.
Unfortunately, there are several problems with this:
1. The code didn't work, it would make a copy in the regular
folder, and then unconditionally move it to the search folder.
2. If we leave this code in place, putting the message
temporarily into the regular folder still activates the duplicate
message deletion trigger, wiping out the original copy, even with
the update to the trigger.
3. It's unclear that it's even desirable to load the search
result message into the regular folder. It could be a very old
message that would not have been synced before, leaving a large
gap in your inbox, which is confusing and could interfere with
IMAP syncing.
Change-Id: I34671a3b677ab42a3efd0d170a6ebd9246ec493d
diff --git a/src/com/android/email/provider/DBHelper.java b/src/com/android/email/provider/DBHelper.java
index e275f42..e6cf459 100644
--- a/src/com/android/email/provider/DBHelper.java
+++ b/src/com/android/email/provider/DBHelper.java
@@ -155,7 +155,8 @@
// Version 117: Add trigger to delete duplicate messages on sync.
// Version 118: Set syncInterval to 0 for all IMAP mailboxes
// Version 119: Disable syncing of DRAFTS type folders.
- public static final int DATABASE_VERSION = 119;
+ // Version 120: Changed duplicateMessage deletion trigger to ignore search mailboxes.
+ public static final int DATABASE_VERSION = 120;
// Any changes to the database format *must* include update-in-place code.
// Original version: 2
@@ -208,17 +209,33 @@
"; end");
}
+ static void dropDeleteDuplicateMessagesTrigger(final SQLiteDatabase db) {
+ db.execSQL("drop trigger message_delete_duplicates_on_insert");
+ }
+
/**
* Add a trigger to delete duplicate server side messages before insertion.
- * @param db The {@link SQLiteDatabase}
+ * Here is the plain text of this sql:
+ * create trigger message_delete_duplicates_on_insert before insert on
+ * Message for each row when new.serverId is not null and
+ * (select Mailbox.type from Mailbox where _id=new.mailboxKey) != 8
+ * begin delete from Message where new.serverId=severId and
+ * new.accountKey=accountKey and
+ * (select Mailbox.type from Mailbox where _id=mailboxKey) != 8; end
*/
static void createDeleteDuplicateMessagesTrigger(final SQLiteDatabase db) {
db.execSQL("create trigger message_delete_duplicates_on_insert before insert on "
+ Message.TABLE_NAME + " for each row when new." + SyncColumns.SERVER_ID
- + " is not null begin delete from " + Message.TABLE_NAME + " where new."
+ + " is not null and "
+ + "(select " + Mailbox.TABLE_NAME + "." + MailboxColumns.TYPE + " from "
+ + Mailbox.TABLE_NAME + " where " + MailboxColumns.ID + "=new."
+ + MessageColumns.MAILBOX_KEY + ")!=" + Mailbox.TYPE_SEARCH
+ + " begin delete from " + Message.TABLE_NAME + " where new."
+ SyncColumns.SERVER_ID + "=" + SyncColumns.SERVER_ID + " and new."
- + MessageColumns.ACCOUNT_KEY + "=" + MessageColumns.ACCOUNT_KEY + "; end");
-
+ + MessageColumns.ACCOUNT_KEY + "=" + MessageColumns.ACCOUNT_KEY
+ + " and (select " + Mailbox.TABLE_NAME + "." + MailboxColumns.TYPE + " from "
+ + Mailbox.TABLE_NAME + " where " + MailboxColumns.ID + "="
+ + MessageColumns.MAILBOX_KEY + ")!=" + Mailbox.TYPE_SEARCH +"; end");
}
static void createMessageTable(SQLiteDatabase db) {
@@ -1189,9 +1206,11 @@
createMessageStateChangeTable(db);
}
- if (oldVersion <= 116) {
- createDeleteDuplicateMessagesTrigger(db);
- }
+ /**
+ * Originally, at 116, we added a trigger to delete duplicate messages.
+ * But we needed to change that trigger for version 120, so when we get
+ * there, we'll drop the trigger if it exists and create a new version.
+ */
/**
* This statement changes the syncInterval column to 0 for all IMAP mailboxes.
@@ -1234,6 +1253,19 @@
+ MailboxColumns.ID + " from " + Mailbox.TABLE_NAME + " where "
+ MailboxColumns.TYPE + "=" + Mailbox.TYPE_DRAFTS + ")");
}
+
+ if (oldVersion <= 119) {
+ if (oldVersion >= 116) {
+ /**
+ * This trigger was originally created at version 116, but we needed to change
+ * it for version 120. So if our oldVersion is 116 or more, we know we have that
+ * trigger and must drop it before re creating it.
+ */
+ dropDeleteDuplicateMessagesTrigger(db);
+ }
+ createDeleteDuplicateMessagesTrigger(db);
+ }
+
}
@Override
diff --git a/src/com/android/email/service/ImapService.java b/src/com/android/email/service/ImapService.java
index 15bc5fd..733ec7b 100644
--- a/src/com/android/email/service/ImapService.java
+++ b/src/com/android/email/service/ImapService.java
@@ -1473,8 +1473,6 @@
// Copy the fields that are available into the message
LegacyConversions.updateMessageFields(localMessage,
message, account.mId, mailbox.mId);
- // Commit the message to the local store
- Utilities.saveOrUpdate(localMessage, context);
localMessage.mMailboxKey = destMailboxId;
// We load 50k or so; maybe it's complete, maybe not...
int flag = EmailContent.Message.FLAG_LOADED_COMPLETE;
