DBHelper.java revision ca79aba675d5282b6ba365184f3727b7b24a568f
1c5afb16430a145f20d7c887e45f47b38687054daMarc Blank/* 2c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Copyright (C) 2012 The Android Open Source Project 3c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * 4c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Licensed under the Apache License, Version 2.0 (the "License"); 5c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * you may not use this file except in compliance with the License. 6c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * You may obtain a copy of the License at 7c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * 8c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * http://www.apache.org/licenses/LICENSE-2.0 9c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * 10c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Unless required by applicable law or agreed to in writing, software 11c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * distributed under the License is distributed on an "AS IS" BASIS, 12c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * See the License for the specific language governing permissions and 14c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * limitations under the License. 15c5afb16430a145f20d7c887e45f47b38687054daMarc Blank */ 16c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 17c5afb16430a145f20d7c887e45f47b38687054daMarc Blankpackage com.android.email.provider; 18c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 19c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.accounts.AccountManager; 20c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.content.ContentResolver; 21c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.content.ContentValues; 22c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.content.Context; 23c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.database.Cursor; 24c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.database.SQLException; 25c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.database.sqlite.SQLiteDatabase; 26c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.database.sqlite.SQLiteOpenHelper; 27c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.provider.CalendarContract; 28c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.provider.ContactsContract; 2932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport android.text.TextUtils; 30c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 3132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport com.android.email.R; 32c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.email2.ui.MailActivityEmail; 33c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.mail.Address; 34c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.Account; 35c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent; 36c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.AccountColumns; 37c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.Attachment; 38c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.AttachmentColumns; 39c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.Body; 40c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.BodyColumns; 41c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.HostAuthColumns; 42c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.MailboxColumns; 43c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.Message; 44c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.MessageColumns; 45c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.PolicyColumns; 46c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.QuickResponseColumns; 47c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.SyncColumns; 48c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.HostAuth; 49c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.Mailbox; 50ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Huimport com.android.emailcommon.provider.MessageChangeLogTable; 51ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Huimport com.android.emailcommon.provider.MessageMove; 52ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Huimport com.android.emailcommon.provider.MessageStateChange; 53c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.Policy; 54c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.QuickResponse; 55c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.service.LegacyPolicySet; 5621b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Huimport com.android.emailcommon.service.SyncWindow; 57c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.mail.providers.UIProvider; 5832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport com.android.mail.utils.LogUtils; 59c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.google.common.annotations.VisibleForTesting; 6032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport com.google.common.collect.ImmutableMap; 6132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 6232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport java.util.Map; 63c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 64c5afb16430a145f20d7c887e45f47b38687054daMarc Blankpublic final class DBHelper { 65c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String TAG = "EmailProvider"; 66c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 6754347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank private static final String LEGACY_SCHEME_IMAP = "imap"; 6854347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank private static final String LEGACY_SCHEME_POP3 = "pop3"; 6954347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank private static final String LEGACY_SCHEME_EAS = "eas"; 7054347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank 71c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String WHERE_ID = EmailContent.RECORD_ID + "=?"; 72c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 73c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String TRIGGER_MAILBOX_DELETE = 74c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME + 75c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin" + 76c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " delete from " + Message.TABLE_NAME + 77c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 78c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + Message.UPDATED_TABLE_NAME + 79c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 80c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + Message.DELETED_TABLE_NAME + 81c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 82c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"; 83c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 84c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String TRIGGER_ACCOUNT_DELETE = 85c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "create trigger account_delete before delete on " + Account.TABLE_NAME + 86c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin delete from " + Mailbox.TABLE_NAME + 87c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + MailboxColumns.ACCOUNT_KEY + "=old." + EmailContent.RECORD_ID + 88c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + HostAuth.TABLE_NAME + 89c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV + 90c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + HostAuth.TABLE_NAME + 91c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND + 92c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + Policy.TABLE_NAME + 93c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.POLICY_KEY + 94c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"; 95c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 96c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Any changes to the database format *must* include update-in-place code. 97c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Original version: 3 98c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 4: Database wipe required; changing AccountManager interface w/Exchange 99c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 5: Database wipe required; changing AccountManager interface w/Exchange 100c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 6: Adding Message.mServerTimeStamp column 101c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 7: Replace the mailbox_delete trigger with a version that removes orphaned messages 102c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // from the Message_Deletes and Message_Updates tables 103c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 8: Add security flags column to accounts table 104c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 9: Add security sync key and signature to accounts table 105c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 10: Add meeting info to message table 106c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 11: Add content and flags to attachment table 107c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 12: Add content_bytes to attachment table. content is deprecated. 108c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 13: Add messageCount to Mailbox table. 109c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 14: Add snippet to Message table 110c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 15: Fix upgrade problem in version 14. 111c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 16: Add accountKey to Attachment table 112c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 17: Add parentKey to Mailbox table 113c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 18: Copy Mailbox.displayName to Mailbox.serverId for all IMAP & POP3 mailboxes. 114c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Column Mailbox.serverId is used for the server-side pathname of a mailbox. 115c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 19: Add Policy table; add policyKey to Account table and trigger to delete an 116c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Account's policy when the Account is deleted 117c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 20: Add new policies to Policy table 118c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 21: Add lastSeenMessageKey column to Mailbox table 119c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 22: Upgrade path for IMAP/POP accounts to integrate with AccountManager 120c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 23: Add column to mailbox table for time of last access 121c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 24: Add column to hostauth table for client cert alias 122c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 25: Added QuickResponse table 123c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 26: Update IMAP accounts to add FLAG_SUPPORTS_SEARCH flag 124c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 27: Add protocolSearchInfo to Message table 125c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 28: Add notifiedMessageId and notifiedMessageCount to Account 126c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 29: Add protocolPoliciesEnforced and protocolPoliciesUnsupported to Policy 127c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 30: Use CSV of RFC822 addresses instead of "packed" values 128c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 31: Add columns to mailbox for ui status/last result 129c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 32: Add columns to mailbox for last notified message key/count; insure not null 130c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // for "notified" columns 131c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 33: Add columns to attachment for ui provider columns 132c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 34: Add total count to mailbox 133c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 35: Set up defaults for lastTouchedCount for drafts and sent 134c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 36: mblank intentionally left this space 135c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 37: Add flag for settings support in folders 136c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 38&39: Add threadTopic to message (for future support) 137c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 39 is last Email1 version 138c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 100 is first Email2 version 139c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 101 SHOULD NOT BE USED 140c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 102&103: Add hierarchicalName to Mailbox 141c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank // Version 104&105: add syncData to Message 1427d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank // Version 106: Add certificate to HostAuth 143b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy // Version 107: Add a SEEN column to the message table 1449a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook // Version 108: Add a cachedFile column to the attachments table 14532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // Version 109: Migrate the account so they have the correct account manager types 14621b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu // Version 110: Stop updating message_count, don't use auto lookback, and don't use 1475ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // ping/push_hold sync states. Note that message_count updating is restored in 113. 148aa0ca16a27e4a56a029e5cdebf96de5723bd84b6Yu Ping Hu // Version 111: Delete Exchange account mailboxes. 149a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // Version 112: Convert Mailbox syncInterval to a boolean (whether or not this mailbox 150a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // syncs along with the account). 1515ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Version 113: Restore message_count to being useful. 152c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon // Version 114: Add lastFullSyncTime column 15352135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon // Version 115: Add pingDuration column 154ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu // Version 116: Add MessageMove & MessageStateChange tables. 155c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 156ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu public static final int DATABASE_VERSION = 116; 157c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 158c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Any changes to the database format *must* include update-in-place code. 159c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Original version: 2 160c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 3: Add "sourceKey" column 161c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 4: Database wipe required; changing AccountManager interface w/Exchange 162c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 5: Database wipe required; changing AccountManager interface w/Exchange 163c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 6: Adding Body.mIntroText column 164c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 7/8: Adding quoted text start pos 165c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 8 is last Email1 version 166c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public static final int BODY_DATABASE_VERSION = 100; 167c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 168c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /* 169c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Internal helper method for index creation. 170c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Example: 171c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * "create index message_" + MessageColumns.FLAG_READ 172c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");" 173c5afb16430a145f20d7c887e45f47b38687054daMarc Blank */ 174c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /* package */ 175c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static String createIndex(String tableName, String columnName) { 176c5afb16430a145f20d7c887e45f47b38687054daMarc Blank return "create index " + tableName.toLowerCase() + '_' + columnName 177c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " on " + tableName + " (" + columnName + ");"; 178c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 179c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1805ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu static void createMessageCountTriggers(final SQLiteDatabase db) { 1815ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Insert a message. 1825ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu db.execSQL("create trigger message_count_message_insert after insert on " + 1835ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu Message.TABLE_NAME + 1845ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 1855ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 1865ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 1875ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu "; end"); 1885ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu 1895ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Delete a message. 1905ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu db.execSQL("create trigger message_count_message_delete after delete on " + 1915ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu Message.TABLE_NAME + 1925ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 1935ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 1945ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 1955ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu "; end"); 1965ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu 1975ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Change a message's mailbox. 1985ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu db.execSQL("create trigger message_count_message_move after update of " + 1995ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 2005ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 2015ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 2025ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 2035ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 2045ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 2055ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 2065ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu "; end"); 2075ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu } 2085ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu 209c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createMessageTable(SQLiteDatabase db) { 210c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String messageColumns = MessageColumns.DISPLAY_NAME + " text, " 211c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.TIMESTAMP + " integer, " 212c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.SUBJECT + " text, " 213c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAG_READ + " integer, " 214c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAG_LOADED + " integer, " 215c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAG_FAVORITE + " integer, " 216c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAG_ATTACHMENT + " integer, " 217c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAGS + " integer, " 218c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.DRAFT_INFO + " integer, " 219c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.MESSAGE_ID + " text, " 220c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.MAILBOX_KEY + " integer, " 221c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.ACCOUNT_KEY + " integer, " 222c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FROM_LIST + " text, " 223c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.TO_LIST + " text, " 224c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.CC_LIST + " text, " 225c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.BCC_LIST + " text, " 226c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.REPLY_TO_LIST + " text, " 227c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.MEETING_INFO + " text, " 228c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.SNIPPET + " text, " 229c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.PROTOCOL_SEARCH_INFO + " text, " 230c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank + MessageColumns.THREAD_TOPIC + " text, " 231b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + MessageColumns.SYNC_DATA + " text, " 232b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + MessageColumns.FLAG_SEEN + " integer" 233c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 234c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 235c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // This String and the following String MUST have the same columns, except for the type 236c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // of those columns! 237c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String createString = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 238c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + SyncColumns.SERVER_ID + " text, " 239c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + SyncColumns.SERVER_TIMESTAMP + " integer, " 240c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + messageColumns; 241c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 242c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // For the updated and deleted tables, the id is assigned, but we do want to keep track 243c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // of the ORDER of updates using an autoincrement primary key. We use the DATA column 244c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // at this point; it has no other function 245c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String altCreateString = " (" + EmailContent.RECORD_ID + " integer unique, " 246c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + SyncColumns.SERVER_ID + " text, " 247c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + SyncColumns.SERVER_TIMESTAMP + " integer, " 248c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + messageColumns; 249c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 250c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // The three tables have the same schema 251c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Message.TABLE_NAME + createString); 252c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString); 253c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString); 254c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 255c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String indexColumns[] = { 256c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.TIMESTAMP, 257c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.FLAG_READ, 258c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.FLAG_LOADED, 259c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.MAILBOX_KEY, 260c5afb16430a145f20d7c887e45f47b38687054daMarc Blank SyncColumns.SERVER_ID 261c5afb16430a145f20d7c887e45f47b38687054daMarc Blank }; 262c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 263c5afb16430a145f20d7c887e45f47b38687054daMarc Blank for (String columnName : indexColumns) { 264c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(createIndex(Message.TABLE_NAME, columnName)); 265c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 266c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 267c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Deleting a Message deletes all associated Attachments 268c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Deleting the associated Body cannot be done in a trigger, because the Body is stored 269c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // in a separate database, and trigger cannot operate on attached databases. 270c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME + 271c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin delete from " + Attachment.TABLE_NAME + 272c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + AttachmentColumns.MESSAGE_KEY + "=old." + EmailContent.RECORD_ID + 273c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 274c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 275c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Add triggers to keep unread count accurate per mailbox 276c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 277c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // NOTE: SQLite's before triggers are not safe when recursive triggers are involved. 278c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Use caution when changing them. 279c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 280c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox 281c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME + 282c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when NEW." + MessageColumns.FLAG_READ + "=0" + 283c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 284c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "+1" + 285c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 286c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 287c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 288c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 289c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME + 290c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when OLD." + MessageColumns.FLAG_READ + "=0" + 291c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 292c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "-1" + 293c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 294c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 295c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 296c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Change a message's mailbox 297c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger unread_message_move before update of " + 298c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 299c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when OLD." + MessageColumns.FLAG_READ + "=0" + 300c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 301c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "-1" + 302c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 303c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 304c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "+1" + 305c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 306c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 307c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 308c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Change a message's read state 309c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger unread_message_read before update of " + 310c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME + 311c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ + 312c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 313c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ + 314c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when 0 then -1 else 1 end" + 315c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 316c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 3175ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu 3185ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Add triggers to maintain message_count. 3195ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu createMessageCountTriggers(db); 320c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 321c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 322c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetMessageTable(SQLiteDatabase db, int oldVersion, int newVersion) { 323c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 324c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Message.TABLE_NAME); 325c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Message.UPDATED_TABLE_NAME); 326c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Message.DELETED_TABLE_NAME); 327c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 328c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 329c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createMessageTable(db); 330c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 331c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 332ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 333ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Common columns for all {@link MessageChangeLogTable} tables. 334ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 335ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static String MESSAGE_CHANGE_LOG_COLUMNS = 336ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu MessageChangeLogTable.ID + " integer primary key autoincrement, " 337ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageChangeLogTable.MESSAGE_KEY + " integer, " 338ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageChangeLogTable.SERVER_ID + " text, " 339ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageChangeLogTable.ACCOUNT_KEY + " integer, " 340ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageChangeLogTable.STATUS + " integer, "; 341ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 342ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 343ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Create indices common to all {@link MessageChangeLogTable} tables. 344ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param db The {@link SQLiteDatabase}. 345ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param tableName The name of this particular table. 346ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 347ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static void createMessageChangeLogTableIndices(final SQLiteDatabase db, 348ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu final String tableName) { 349ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL(createIndex(tableName, MessageChangeLogTable.MESSAGE_KEY)); 350ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL(createIndex(tableName, MessageChangeLogTable.ACCOUNT_KEY)); 351ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 352ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 353ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 354ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Create triggers common to all {@link MessageChangeLogTable} tables. 355ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param db The {@link SQLiteDatabase}. 356ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param tableName The name of this particular table. 357ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 358ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static void createMessageChangeLogTableTriggers(final SQLiteDatabase db, 359ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu final String tableName) { 360ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu // Trigger to delete from the change log when a message is deleted. 361ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL("create trigger " + tableName + "_delete_message before delete on " 362ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + Message.TABLE_NAME + " for each row begin delete from " + tableName 363ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + " where " + MessageChangeLogTable.MESSAGE_KEY + "=old." + MessageColumns.ID 364ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + "; end"); 365ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 366ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu // Trigger to delete from the change log when an account is deleted. 367ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL("create trigger " + tableName + "_delete_account before delete on " 368ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + Account.TABLE_NAME + " for each row begin delete from " + tableName 369ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + " where " + MessageChangeLogTable.ACCOUNT_KEY + "=old." + AccountColumns.ID 370ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + "; end"); 371ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 372ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 373ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 374ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Create the MessageMove table. 375ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param db The {@link SQLiteDatabase}. 376ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 377ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static void createMessageMoveTable(final SQLiteDatabase db) { 378ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL("create table " + MessageMove.TABLE_NAME + " (" 379ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MESSAGE_CHANGE_LOG_COLUMNS 380ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageMove.SRC_FOLDER_KEY + " integer, " 381ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageMove.DST_FOLDER_KEY + " integer, " 382ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageMove.SRC_FOLDER_SERVER_ID + " text, " 383ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageMove.DST_FOLDER_SERVER_ID + " text);"); 384ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 385ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageChangeLogTableIndices(db, MessageMove.TABLE_NAME); 386ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageChangeLogTableTriggers(db, MessageMove.TABLE_NAME); 387ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 388ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 389ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 390ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Create the MessageStateChange table. 391ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param db The {@link SQLiteDatabase}. 392ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 393ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static void createMessageStateChangeTable(final SQLiteDatabase db) { 394ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL("create table " + MessageStateChange.TABLE_NAME + " (" 395ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MESSAGE_CHANGE_LOG_COLUMNS 396ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageStateChange.OLD_FLAG_READ + " integer, " 397ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageStateChange.NEW_FLAG_READ + " integer, " 398ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageStateChange.OLD_FLAG_FAVORITE + " integer, " 399ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageStateChange.NEW_FLAG_FAVORITE + " integer);"); 400ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 401ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageChangeLogTableIndices(db, MessageStateChange.TABLE_NAME); 402ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageChangeLogTableTriggers(db, MessageStateChange.TABLE_NAME); 403ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 404ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 405c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @SuppressWarnings("deprecation") 406c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createAccountTable(SQLiteDatabase db) { 407c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 408c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.DISPLAY_NAME + " text, " 409c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.EMAIL_ADDRESS + " text, " 410c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SYNC_KEY + " text, " 411c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SYNC_LOOKBACK + " integer, " 412c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SYNC_INTERVAL + " text, " 413c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.HOST_AUTH_KEY_RECV + " integer, " 414c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.HOST_AUTH_KEY_SEND + " integer, " 415c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.FLAGS + " integer, " 416c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.IS_DEFAULT + " integer, " 417c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.COMPATIBILITY_UUID + " text, " 418c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SENDER_NAME + " text, " 419c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.RINGTONE_URI + " text, " 420c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.PROTOCOL_VERSION + " text, " 421c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.NEW_MESSAGE_COUNT + " integer, " 422c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SECURITY_FLAGS + " integer, " 423c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SECURITY_SYNC_KEY + " text, " 424c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SIGNATURE + " text, " 42552135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon + AccountColumns.POLICY_KEY + " integer, " 42652135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon + AccountColumns.PING_DURATION + " integer" 427c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 428c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Account.TABLE_NAME + s); 429c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Deleting an account deletes associated Mailboxes and HostAuth's 430c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(TRIGGER_ACCOUNT_DELETE); 431c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 432c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 433c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) { 434c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 435c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Account.TABLE_NAME); 436c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 437c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 438c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAccountTable(db); 439c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 440c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 441c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createPolicyTable(SQLiteDatabase db) { 442c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 443c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_MODE + " integer, " 444c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_MIN_LENGTH + " integer, " 445c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_EXPIRATION_DAYS + " integer, " 446c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_HISTORY + " integer, " 447c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_COMPLEX_CHARS + " integer, " 448c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_MAX_FAILS + " integer, " 449c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_SCREEN_LOCK_TIME + " integer, " 450c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.REQUIRE_REMOTE_WIPE + " integer, " 451c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.REQUIRE_ENCRYPTION + " integer, " 452c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.REQUIRE_ENCRYPTION_EXTERNAL + " integer, " 453c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + " integer, " 454c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.DONT_ALLOW_CAMERA + " integer, " 455c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer, " 456c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.DONT_ALLOW_HTML + " integer, " 457c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer, " 458c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + " integer, " 459c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + " integer, " 460c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer, " 461c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer, " 462c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_RECOVERY_ENABLED + " integer, " 463c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PROTOCOL_POLICIES_ENFORCED + " text, " 464c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PROTOCOL_POLICIES_UNSUPPORTED + " text" 465c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 466c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Policy.TABLE_NAME + s); 467c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 468c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 469c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createHostAuthTable(SQLiteDatabase db) { 470c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 471c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.PROTOCOL + " text, " 472c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.ADDRESS + " text, " 473c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.PORT + " integer, " 474c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.FLAGS + " integer, " 475c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.LOGIN + " text, " 476c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.PASSWORD + " text, " 477c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.DOMAIN + " text, " 478c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.ACCOUNT_KEY + " integer," 4797d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank + HostAuthColumns.CLIENT_CERT_ALIAS + " text," 480cee9881650e01da155b3d7117357a15f49a7a4a1Marc Blank + HostAuthColumns.SERVER_CERT + " blob" 481c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 482c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + HostAuth.TABLE_NAME + s); 483c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 484c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 485c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) { 486c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 487c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + HostAuth.TABLE_NAME); 488c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 489c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 490c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createHostAuthTable(db); 491c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 492c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 493c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createMailboxTable(SQLiteDatabase db) { 494c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 495c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.DISPLAY_NAME + " text, " 496c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SERVER_ID + " text, " 497c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.PARENT_SERVER_ID + " text, " 498c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.PARENT_KEY + " integer, " 499c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.ACCOUNT_KEY + " integer, " 500c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.TYPE + " integer, " 501c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.DELIMITER + " integer, " 502c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_KEY + " text, " 503c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_LOOKBACK + " integer, " 504c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_INTERVAL + " integer, " 505c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_TIME + " integer, " 506c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.UNREAD_COUNT + " integer, " 507c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.FLAG_VISIBLE + " integer, " 508c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.FLAGS + " integer, " 509c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.VISIBLE_LIMIT + " integer, " 510c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_STATUS + " text, " 511c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.MESSAGE_COUNT + " integer not null default 0, " 512c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.LAST_TOUCHED_TIME + " integer default 0, " 513c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.UI_SYNC_STATUS + " integer default 0, " 514c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.UI_LAST_SYNC_RESULT + " integer default 0, " 515c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.LAST_NOTIFIED_MESSAGE_KEY + " integer not null default 0, " 516c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.LAST_NOTIFIED_MESSAGE_COUNT + " integer not null default 0, " 517c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.TOTAL_COUNT + " integer, " 518c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon + MailboxColumns.HIERARCHICAL_NAME + " text, " 519c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon + MailboxColumns.LAST_FULL_SYNC_TIME + " integer" 520c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 521c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Mailbox.TABLE_NAME + s); 522c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID 523c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")"); 524c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY 525c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")"); 526c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Deleting a Mailbox deletes associated Messages in all three tables 527c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(TRIGGER_MAILBOX_DELETE); 528c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 529c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 530c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) { 531c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 532c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Mailbox.TABLE_NAME); 533c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 534c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 535c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createMailboxTable(db); 536c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 537c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 538c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createAttachmentTable(SQLiteDatabase db) { 539c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 540c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.FILENAME + " text, " 541c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.MIME_TYPE + " text, " 542c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.SIZE + " integer, " 543c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.CONTENT_ID + " text, " 544c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.CONTENT_URI + " text, " 545c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.MESSAGE_KEY + " integer, " 546c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.LOCATION + " text, " 547c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.ENCODING + " text, " 548c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.CONTENT + " text, " 549c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.FLAGS + " integer, " 550c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.CONTENT_BYTES + " blob, " 551c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.ACCOUNT_KEY + " integer, " 552c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.UI_STATE + " integer, " 553c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.UI_DESTINATION + " integer, " 5549a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook + AttachmentColumns.UI_DOWNLOADED_SIZE + " integer, " 5559a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook + AttachmentColumns.CACHED_FILE + " text" 556c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 557c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Attachment.TABLE_NAME + s); 558c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY)); 559c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 560c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 561c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) { 562c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 563c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Attachment.TABLE_NAME); 564c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 565c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 566c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAttachmentTable(db); 567c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 568c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 569c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createQuickResponseTable(SQLiteDatabase db) { 570c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 571c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + QuickResponseColumns.TEXT + " text, " 572c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + QuickResponseColumns.ACCOUNT_KEY + " integer" 573c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 574c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + QuickResponse.TABLE_NAME + s); 575c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 576c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 577c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createBodyTable(SQLiteDatabase db) { 578c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 579c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.MESSAGE_KEY + " integer, " 580c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.HTML_CONTENT + " text, " 581c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.TEXT_CONTENT + " text, " 582c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.HTML_REPLY + " text, " 583c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.TEXT_REPLY + " text, " 584c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.SOURCE_MESSAGE_KEY + " text, " 585c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.INTRO_TEXT + " text, " 586c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.QUOTED_TEXT_START_POS + " integer" 587c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 588c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Body.TABLE_NAME + s); 589c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY)); 590c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 591c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 592c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void upgradeBodyTable(SQLiteDatabase db, int oldVersion, int newVersion) { 593c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion < 5) { 594c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 595c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Body.TABLE_NAME); 596c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createBodyTable(db); 597c5afb16430a145f20d7c887e45f47b38687054daMarc Blank oldVersion = 5; 598c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 599c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 600c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 601c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion == 5) { 602c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 603c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Body.TABLE_NAME 604c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add " + BodyColumns.INTRO_TEXT + " text"); 605c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 606c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 607560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProviderBody.db from v5 to v6", e); 608c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 609c5afb16430a145f20d7c887e45f47b38687054daMarc Blank oldVersion = 6; 610c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 611c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion == 6 || oldVersion == 7) { 612c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 613c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Body.TABLE_NAME 614c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add " + BodyColumns.QUOTED_TEXT_START_POS + " integer"); 615c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 616c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 617560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProviderBody.db from v6 to v8", e); 618c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 619c5afb16430a145f20d7c887e45f47b38687054daMarc Blank oldVersion = 8; 620c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 621c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion == 8) { 622c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Move to Email2 version 623c5afb16430a145f20d7c887e45f47b38687054daMarc Blank oldVersion = 100; 624c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 625c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 626c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 627c5afb16430a145f20d7c887e45f47b38687054daMarc Blank protected static class BodyDatabaseHelper extends SQLiteOpenHelper { 628c5afb16430a145f20d7c887e45f47b38687054daMarc Blank BodyDatabaseHelper(Context context, String name) { 629c5afb16430a145f20d7c887e45f47b38687054daMarc Blank super(context, name, null, BODY_DATABASE_VERSION); 630c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 631c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 632c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 633c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onCreate(SQLiteDatabase db) { 634560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.d(TAG, "Creating EmailProviderBody database"); 635c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createBodyTable(db); 636c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 637c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 638c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 639c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 640c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeBodyTable(db, oldVersion, newVersion); 641c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 642c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 643c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 644c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onOpen(SQLiteDatabase db) { 645c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 646c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 647c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 648c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Counts the number of messages in each mailbox, and updates the message count column. */ 649c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @VisibleForTesting 650c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void recalculateMessageCount(SQLiteDatabase db) { 651c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 652c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "= (select count(*) from " + Message.TABLE_NAME + 653c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " where " + Message.MAILBOX_KEY + " = " + 654c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.TABLE_NAME + "." + EmailContent.RECORD_ID + ")"); 655c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 656c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 657c5afb16430a145f20d7c887e45f47b38687054daMarc Blank protected static class DatabaseHelper extends SQLiteOpenHelper { 658c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Context mContext; 659c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 660c5afb16430a145f20d7c887e45f47b38687054daMarc Blank DatabaseHelper(Context context, String name) { 661c5afb16430a145f20d7c887e45f47b38687054daMarc Blank super(context, name, null, DATABASE_VERSION); 662c5afb16430a145f20d7c887e45f47b38687054daMarc Blank mContext = context; 663c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 664c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 665c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 666c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onCreate(SQLiteDatabase db) { 667560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.d(TAG, "Creating EmailProvider database"); 668c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Create all tables here; each class has its own method 669c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createMessageTable(db); 670c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAttachmentTable(db); 671c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createMailboxTable(db); 672c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createHostAuthTable(db); 673c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAccountTable(db); 674ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageMoveTable(db); 675ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageStateChangeTable(db); 676c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createPolicyTable(db); 677c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createQuickResponseTable(db); 678c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 679c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 68054347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank @Override 681c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { 682c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion == 101 && newVersion == 100) { 683560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.d(TAG, "Downgrade from v101 to v100"); 684c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } else { 685c5afb16430a145f20d7c887e45f47b38687054daMarc Blank super.onDowngrade(db, oldVersion, newVersion); 686c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 687c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 688c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 689c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 690c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @SuppressWarnings("deprecation") 691c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 692c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // For versions prior to 5, delete all data 693c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Versions >= 5 require that data be preserved! 694c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion < 5) { 695c5afb16430a145f20d7c887e45f47b38687054daMarc Blank android.accounts.Account[] accounts = AccountManager.get(mContext) 696e714bb9d153cfe13a7f0932e7d67ea08fa5a1d98Marc Blank .getAccountsByType("eas"); 697c5afb16430a145f20d7c887e45f47b38687054daMarc Blank for (android.accounts.Account account: accounts) { 698c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AccountManager.get(mContext).removeAccount(account, null, null); 699c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 700c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetMessageTable(db, oldVersion, newVersion); 701c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetAttachmentTable(db, oldVersion, newVersion); 702c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetMailboxTable(db, oldVersion, newVersion); 703c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetHostAuthTable(db, oldVersion, newVersion); 704c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetAccountTable(db, oldVersion, newVersion); 705c5afb16430a145f20d7c887e45f47b38687054daMarc Blank return; 706c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 707c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion == 5) { 708c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Message Tables: Add SyncColumns.SERVER_TIMESTAMP 709c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 710c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 711c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 712c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 713c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 714c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 715c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 716c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 717c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 718560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v5 to v6", e); 719c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 720c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7215ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 6) { 722c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Use the newer mailbox_delete trigger 723c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop trigger mailbox_delete;"); 724c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(TRIGGER_MAILBOX_DELETE); 725c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7265ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 7) { 727c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // add the security (provisioning) column 728c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 729c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Account.TABLE_NAME 730c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AccountColumns.SECURITY_FLAGS + " integer" + ";"); 731c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 732c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 733560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 7 to 8 " + e); 734c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 735c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7365ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 8) { 737c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // accounts: add security sync key & user signature columns 738c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 739c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Account.TABLE_NAME 740c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AccountColumns.SECURITY_SYNC_KEY + " text" + ";"); 741c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Account.TABLE_NAME 742c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AccountColumns.SIGNATURE + " text" + ";"); 743c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 744c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 745560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 8 to 9 " + e); 746c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 747c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7485ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 9) { 749c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Message: add meeting info column into Message tables 750c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 751c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 752c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 753c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 754c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 755c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 756c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 757c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 758c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 759560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 9 to 10 " + e); 760c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 761c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7625ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 10) { 763c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Attachment: add content and flags columns 764c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 765c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 766c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AttachmentColumns.CONTENT + " text" + ";"); 767c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 768c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AttachmentColumns.FLAGS + " integer" + ";"); 769c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 770c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 771560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 10 to 11 " + e); 772c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 773c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7745ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 11) { 775c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Attachment: add content_bytes 776c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 777c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 778c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AttachmentColumns.CONTENT_BYTES + " blob" + ";"); 779c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 780c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 781560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 11 to 12 " + e); 782c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 783c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7845ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 12) { 785c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 786c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 787c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.MESSAGE_COUNT 788c5afb16430a145f20d7c887e45f47b38687054daMarc Blank +" integer not null default 0" + ";"); 789c5afb16430a145f20d7c887e45f47b38687054daMarc Blank recalculateMessageCount(db); 790c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 791c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 792560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 12 to 13 " + e); 793c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 794c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7955ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 13) { 796c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 797c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 798c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Message.SNIPPET 799c5afb16430a145f20d7c887e45f47b38687054daMarc Blank +" text" + ";"); 800c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 801c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 802560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 13 to 14 " + e); 803c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 804c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8055ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 14) { 806c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 807c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 808c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Message.SNIPPET +" text" + ";"); 809c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 810c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Message.SNIPPET +" text" + ";"); 811c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 812c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 813560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 14 to 15 " + e); 814c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 815c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8165ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 15) { 817c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 818c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 819c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Attachment.ACCOUNT_KEY +" integer" + ";"); 820c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Update all existing attachments to add the accountKey data 821c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Attachment.TABLE_NAME + " set " + 822c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Attachment.ACCOUNT_KEY + "= (SELECT " + Message.TABLE_NAME + "." + 823c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Message.ACCOUNT_KEY + " from " + Message.TABLE_NAME + " where " + 824c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Message.TABLE_NAME + "." + Message.RECORD_ID + " = " + 825c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Attachment.TABLE_NAME + "." + Attachment.MESSAGE_KEY + ")"); 826c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 827c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 828560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 15 to 16 " + e); 829c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 830c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8315ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 16) { 832c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 833c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 834c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.PARENT_KEY + " integer;"); 835c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 836c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 837560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 16 to 17 " + e); 838c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 839c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8405ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 17) { 841c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion17ToVersion18(db); 842c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8435ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 18) { 844c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 845c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Account.TABLE_NAME 846c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Account.POLICY_KEY + " integer;"); 847c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop trigger account_delete;"); 848c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(TRIGGER_ACCOUNT_DELETE); 849c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createPolicyTable(db); 850c5afb16430a145f20d7c887e45f47b38687054daMarc Blank convertPolicyFlagsToPolicyTable(db); 851c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 852c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 853560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 18 to 19 " + e); 854c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 855c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8565ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 19) { 857c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 858c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 859c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + 860c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " integer;"); 861c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 862c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.DONT_ALLOW_CAMERA + " integer;"); 863c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 864c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer;"); 865c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 866c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.DONT_ALLOW_HTML + " integer;"); 867c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 868c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer;"); 869c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 870c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + 871c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " integer;"); 872c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 873c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + 874c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " integer;"); 875c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 876c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer;"); 877c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 878c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer;"); 879c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 880c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.PASSWORD_RECOVERY_ENABLED + 881c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " integer;"); 882c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 883c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 884560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 19 to 20 " + e); 885c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 886c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8875ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 21) { 888c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion21ToVersion22(db, mContext); 889c5afb16430a145f20d7c887e45f47b38687054daMarc Blank oldVersion = 22; 890c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8915ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 22) { 892c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion22ToVersion23(db); 893c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8945ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 23) { 895c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion23ToVersion24(db); 896c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 8975ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 24) { 898c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion24ToVersion25(db); 899c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9005ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 25) { 901c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion25ToVersion26(db); 902c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9035ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 26) { 904c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 905c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 906c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Message.PROTOCOL_SEARCH_INFO + " text;"); 907c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 908c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";"); 909c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 910c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";"); 911c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 912c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 913560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 26 to 27 " + e); 914c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 915c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9165ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 28) { 917c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 918c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 919c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Policy.PROTOCOL_POLICIES_ENFORCED + " text;"); 920c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 921c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Policy.PROTOCOL_POLICIES_UNSUPPORTED + " text;"); 922c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 923c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 924560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 28 to 29 " + e); 925c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 926c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9275ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 29) { 928c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion29ToVersion30(db); 929c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9305ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 30) { 931c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 932c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 933c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.UI_SYNC_STATUS + " integer;"); 934c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 935c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.UI_LAST_SYNC_RESULT + " integer;"); 936c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 937c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 938560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 30 to 31 " + e); 939c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 940c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9415ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 31) { 942c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 943c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 944c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " integer;"); 945c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 946c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " integer;"); 947c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + 948c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " IS NULL"); 949c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + 950c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " IS NULL"); 951c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 952c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 953560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 31 to 32 " + e); 954c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 955c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9565ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 32) { 957c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 958c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 959c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Attachment.UI_STATE + " integer;"); 960c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 961c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Attachment.UI_DESTINATION + " integer;"); 962c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 963c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Attachment.UI_DOWNLOADED_SIZE + " integer;"); 964c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // If we have a contentUri then the attachment is saved 965c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // uiDestination of 0 = "cache", so we don't have to set this 966c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Attachment.TABLE_NAME + " set " + Attachment.UI_STATE + 967c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=" + UIProvider.AttachmentState.SAVED + " where " + 968c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AttachmentColumns.CONTENT_URI + " is not null;"); 969c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 970c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 971560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 32 to 33 " + e); 972c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 973c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9745ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 33) { 975c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 976c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 977c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MailboxColumns.TOTAL_COUNT + " integer;"); 978c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 979c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 980560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 33 to 34 " + e); 981c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 982c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9835ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 34) { 984c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 985c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 986c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.LAST_TOUCHED_TIME + " = " + 987c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.DRAFTS_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 988c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " = " + Mailbox.TYPE_DRAFTS); 989c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 990c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.LAST_TOUCHED_TIME + " = " + 991c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.SENT_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 992c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " = " + Mailbox.TYPE_SENT); 993c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 994c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 995560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 34 to 35 " + e); 996c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 997c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9985ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 36) { 999c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1000c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Set "supports settings" for EAS mailboxes 1001c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1002c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.FLAGS + "=" + MailboxColumns.FLAGS + "|" + 1003c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.FLAG_SUPPORTS_SETTINGS + " where (" + 1004c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.FLAGS + "&" + Mailbox.FLAG_HOLDS_MAIL + ")!=0 and " + 1005c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.ACCOUNT_KEY + " IN (SELECT " + Account.TABLE_NAME + 1006c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "." + AccountColumns.ID + " from " + Account.TABLE_NAME + "," + 1007c5afb16430a145f20d7c887e45f47b38687054daMarc Blank HostAuth.TABLE_NAME + " where " + Account.TABLE_NAME + "." + 1008c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AccountColumns.HOST_AUTH_KEY_RECV + "=" + HostAuth.TABLE_NAME + "." + 1009c5afb16430a145f20d7c887e45f47b38687054daMarc Blank HostAuthColumns.ID + " and " + HostAuthColumns.PROTOCOL + "='" + 101054347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank LEGACY_SCHEME_EAS + "')"); 1011c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1012c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1013560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 35 to 36 " + e); 1014c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1015c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10165ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 37) { 1017c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1018c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 1019c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1020c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1021c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1022560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 37 to 38 " + e); 1023c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1024c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10255ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 38) { 1026c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1027c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1028c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1029c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1030c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1031c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1032c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1033560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 38 to 39 " + e); 1034c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1035c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10365ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 39) { 1037c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeToEmail2(db); 1038c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10395ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 102) { 1040c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1041c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1042c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add " + MailboxColumns.HIERARCHICAL_NAME + " text"); 1043c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1044c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1045560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v10x to v103", e); 1046c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1047c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10485ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 103) { 1049c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank try { 1050c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 1051c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank + " add " + MessageColumns.SYNC_DATA + " text"); 1052c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } catch (SQLException e) { 1053c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1054560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v103 to v104", e); 1055c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } 1056c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } 10575ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 104) { 1058c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank try { 1059c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1060c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank + " add " + MessageColumns.SYNC_DATA + " text"); 1061c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1062c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank + " add " + MessageColumns.SYNC_DATA + " text"); 1063c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } catch (SQLException e) { 1064c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1065560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v104 to v105", e); 1066c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } 1067c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } 10685ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 105) { 10697d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank try { 10707d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank db.execSQL("alter table " + HostAuth.TABLE_NAME 10717d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank + " add " + HostAuthColumns.SERVER_CERT + " blob"); 10727d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank } catch (SQLException e) { 10737d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1074560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v105 to v106", e); 10757d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank } 10767d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank } 10775ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 106) { 1078b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy try { 1079b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy db.execSQL("alter table " + Message.TABLE_NAME 1080b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + " add " + MessageColumns.FLAG_SEEN + " integer"); 1081b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1082b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + " add " + MessageColumns.FLAG_SEEN + " integer"); 1083b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1084b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + " add " + MessageColumns.FLAG_SEEN + " integer"); 1085b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy } catch (SQLException e) { 1086b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy // Shouldn't be needed unless we're debugging and interrupt the process 1087560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v106 to v107", e); 1088b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy } 1089b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy } 10905ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 107) { 10919a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook try { 10929a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook db.execSQL("alter table " + Attachment.TABLE_NAME 10939a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook + " add column " + Attachment.CACHED_FILE +" text" + ";"); 10949a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook } catch (SQLException e) { 10959a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook // Shouldn't be needed unless we're debugging and interrupt the process 1096560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v107 to v108", e); 10979a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook } 10989a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook } 10995ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 108) { 110032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // Migrate the accounts with the correct account type 110132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook migrateLegacyAccounts(db, mContext); 110232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 11035ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 109) { 110421b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu // Fix any mailboxes that have ping or push_hold states. 110521b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 110621b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu + "=" + Mailbox.CHECK_INTERVAL_PUSH + " where " 110721b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu + MailboxColumns.SYNC_INTERVAL + "<" + Mailbox.CHECK_INTERVAL_PUSH); 110821b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu 110921b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu // Fix invalid syncLookback values. 111021b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu db.execSQL("update " + Account.TABLE_NAME + " set " + AccountColumns.SYNC_LOOKBACK 1111b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + "=" + SyncWindow.SYNC_WINDOW_1_WEEK + " where " 1112b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + AccountColumns.SYNC_LOOKBACK + " is null or " 1113b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + AccountColumns.SYNC_LOOKBACK + "<" + SyncWindow.SYNC_WINDOW_1_DAY + " or " 1114b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + AccountColumns.SYNC_LOOKBACK + ">" + SyncWindow.SYNC_WINDOW_ALL); 1115b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu 111621b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_LOOKBACK 1117b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + "=" + SyncWindow.SYNC_WINDOW_ACCOUNT + " where " 1118b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + MailboxColumns.SYNC_LOOKBACK + " is null or " 1119b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + MailboxColumns.SYNC_LOOKBACK + "<" + SyncWindow.SYNC_WINDOW_1_DAY + " or " 1120b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + MailboxColumns.SYNC_LOOKBACK + ">" + SyncWindow.SYNC_WINDOW_ALL); 1121aa0ca16a27e4a56a029e5cdebf96de5723bd84b6Yu Ping Hu } 11225ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 110) { 1123a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // Delete account mailboxes. 1124aa0ca16a27e4a56a029e5cdebf96de5723bd84b6Yu Ping Hu db.execSQL("delete from " + Mailbox.TABLE_NAME + " where " + MailboxColumns.TYPE 1125aa0ca16a27e4a56a029e5cdebf96de5723bd84b6Yu Ping Hu + "=" +Mailbox.TYPE_EAS_ACCOUNT_MAILBOX); 1126a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu } 11275ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 111) { 1128a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // Mailbox sync interval now indicates whether this mailbox syncs with the rest 1129a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // of the account. Anyone who was syncing at all, plus outboxes, are set to 1, 1130a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // everyone else is 0. 1131a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 1132a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu + "=case when " + MailboxColumns.SYNC_INTERVAL + "=" 1133a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu + Mailbox.CHECK_INTERVAL_NEVER + " then 0 else 1 end"); 113421b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu } 11355ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion >= 110 && oldVersion <= 112) { 11365ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // v110 had dropped these triggers, but starting with v113 we restored them 11375ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // (and altered the 109 -> 110 upgrade code to stop dropping them). 11385ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // We therefore only add them back for the versions in between. We also need to 11395ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // compute the correct value at this point as well. 11405ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu recalculateMessageCount(db); 11415ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu createMessageCountTriggers(db); 11425ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu } 1143c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon 1144c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon if (oldVersion <= 113) { 1145c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon try { 1146c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon db.execSQL("alter table " + Mailbox.TABLE_NAME 1147c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon + " add column " + MailboxColumns.LAST_FULL_SYNC_TIME +" integer" + ";"); 114852135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon final ContentValues cv = new ContentValues(1); 1149c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon cv.put(MailboxColumns.LAST_FULL_SYNC_TIME, 0); 1150c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon db.update(Mailbox.TABLE_NAME, cv, null, null); 115152135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon } catch (final SQLException e) { 115252135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon // Shouldn't be needed unless we're debugging and interrupt the process 115352135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v113 to v114", e); 115452135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon } 115552135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon } 115652135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon 115752135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon if (oldVersion <= 114) { 115852135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon try { 115952135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon db.execSQL("alter table " + Account.TABLE_NAME 116052135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon + " add column " + AccountColumns.PING_DURATION +" integer" + ";"); 116152135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon final ContentValues cv = new ContentValues(1); 116252135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon cv.put(AccountColumns.PING_DURATION, 0); 116352135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon db.update(Account.TABLE_NAME, cv, null, null); 116452135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon } catch (final SQLException e) { 1165c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon // Shouldn't be needed unless we're debugging and interrupt the process 1166c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v113 to v114", e); 1167c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon } 1168c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon } 1169ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 1170ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu if (oldVersion <= 115) { 1171ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageMoveTable(db); 1172ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageStateChangeTable(db); 1173ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 1174c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1175c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1176c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 1177c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onOpen(SQLiteDatabase db) { 11780c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook try { 11790c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook // Cleanup some nasty records 11800c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook db.execSQL("DELETE FROM " + Account.TABLE_NAME 11810c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook + " WHERE " + AccountColumns.DISPLAY_NAME + " ISNULL;"); 11820c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook db.execSQL("DELETE FROM " + HostAuth.TABLE_NAME 11830c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook + " WHERE " + HostAuthColumns.PROTOCOL + " ISNULL;"); 11840c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook } catch (SQLException e) { 11850c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook // Shouldn't be needed unless we're debugging and interrupt the process 11860c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook LogUtils.e(TAG, e, "Exception cleaning EmailProvider.db"); 11870c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook } 1188c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1189c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1190c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1191c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @VisibleForTesting 1192c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @SuppressWarnings("deprecation") 1193c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void convertPolicyFlagsToPolicyTable(SQLiteDatabase db) { 1194c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Cursor c = db.query(Account.TABLE_NAME, 1195c5afb16430a145f20d7c887e45f47b38687054daMarc Blank new String[] {EmailContent.RECORD_ID /*0*/, AccountColumns.SECURITY_FLAGS /*1*/}, 1196c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AccountColumns.SECURITY_FLAGS + ">0", null, null, null, null); 119789c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi try { 119889c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi ContentValues cv = new ContentValues(); 119989c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi String[] args = new String[1]; 120089c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi while (c.moveToNext()) { 120189c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi long securityFlags = c.getLong(1 /*SECURITY_FLAGS*/); 120289c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi Policy policy = LegacyPolicySet.flagsToPolicy(securityFlags); 120389c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi long policyId = db.insert(Policy.TABLE_NAME, null, policy.toContentValues()); 120489c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi cv.put(AccountColumns.POLICY_KEY, policyId); 120589c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi cv.putNull(AccountColumns.SECURITY_FLAGS); 120689c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi args[0] = Long.toString(c.getLong(0 /*RECORD_ID*/)); 120789c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi db.update(Account.TABLE_NAME, cv, EmailContent.RECORD_ID + "=?", args); 120889c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi } 120989c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi } finally { 121089c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi c.close(); 1211c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1212c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1213c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1214c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v17 to v18 */ 1215c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @VisibleForTesting 1216c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void upgradeFromVersion17ToVersion18(SQLiteDatabase db) { 1217c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Copy the displayName column to the serverId column. In v18 of the database, 1218c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // we use the serverId for IMAP/POP3 mailboxes instead of overloading the 1219c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // display name. 1220c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // 1221c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // For posterity; this is the command we're executing: 1222c5afb16430a145f20d7c887e45f47b38687054daMarc Blank //sqlite> UPDATE mailbox SET serverid=displayname WHERE mailbox._id in ( 1223c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> SELECT mailbox._id FROM mailbox,account,hostauth WHERE 1224c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> (mailbox.parentkey isnull OR mailbox.parentkey=0) AND 1225c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> mailbox.accountkey=account._id AND 1226c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> account.hostauthkeyrecv=hostauth._id AND 1227c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> (hostauth.protocol='imap' OR hostauth.protocol='pop3')); 1228c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1229c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL( 1230c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "UPDATE " + Mailbox.TABLE_NAME + " SET " 1231c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SERVER_ID + "=" + MailboxColumns.DISPLAY_NAME 1232c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " WHERE " 1233c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " IN ( SELECT " 1234c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " FROM " 1235c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "," + Account.TABLE_NAME + "," 1236c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuth.TABLE_NAME + " WHERE " 1237c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + "(" 1238c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + " isnull OR " 1239c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + "=0 " 1240c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ") AND " 1241c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "." + MailboxColumns.ACCOUNT_KEY + "=" 1242c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Account.TABLE_NAME + "." + AccountColumns.ID + " AND " 1243c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV + "=" 1244c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuth.TABLE_NAME + "." + HostAuthColumns.ID + " AND ( " 1245c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap' OR " 1246c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='pop3' ) )"); 1247c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1248c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1249560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 17 to 18 " + e); 1250c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1251c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentCache.invalidateAllCaches(); 1252c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1253c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1254c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** 1255c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Upgrade the database from v21 to v22 1256c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * This entails creating AccountManager accounts for all pop3 and imap accounts 1257c5afb16430a145f20d7c887e45f47b38687054daMarc Blank */ 1258c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1259c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] V21_ACCOUNT_PROJECTION = 1260c5afb16430a145f20d7c887e45f47b38687054daMarc Blank new String[] {AccountColumns.HOST_AUTH_KEY_RECV, AccountColumns.EMAIL_ADDRESS}; 1261c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V21_ACCOUNT_RECV = 0; 1262c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V21_ACCOUNT_EMAIL = 1; 1263c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1264c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] V21_HOSTAUTH_PROJECTION = 1265c5afb16430a145f20d7c887e45f47b38687054daMarc Blank new String[] {HostAuthColumns.PROTOCOL, HostAuthColumns.PASSWORD}; 1266c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V21_HOSTAUTH_PROTOCOL = 0; 1267c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V21_HOSTAUTH_PASSWORD = 1; 1268c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 126932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook private static void createAccountManagerAccount(Context context, String login, String type, 1270c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String password) { 127132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final AccountManager accountManager = AccountManager.get(context); 127232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 127332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook if (isAccountPresent(accountManager, login, type)) { 127432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // The account already exists,just return 127532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook return; 127632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 127732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook LogUtils.v("Email", "Creating account %s %s", login, type); 127832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final android.accounts.Account amAccount = new android.accounts.Account(login, type); 1279c5afb16430a145f20d7c887e45f47b38687054daMarc Blank accountManager.addAccountExplicitly(amAccount, password, null); 1280c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 1281c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setSyncAutomatically(amAccount, EmailContent.AUTHORITY, true); 1282c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setIsSyncable(amAccount, ContactsContract.AUTHORITY, 0); 1283c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setIsSyncable(amAccount, CalendarContract.AUTHORITY, 0); 1284c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1285c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 128632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook private static boolean isAccountPresent(AccountManager accountManager, String name, 128732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook String type) { 128832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final android.accounts.Account[] amAccounts = accountManager.getAccountsByType(type); 128932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook if (amAccounts != null) { 129032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook for (android.accounts.Account account : amAccounts) { 129132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook if (TextUtils.equals(account.name, name) && TextUtils.equals(account.type, type)) { 129232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook return true; 129332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 129432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 129532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 129632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook return false; 129732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 129832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 1299c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @VisibleForTesting 1300c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void upgradeFromVersion21ToVersion22(SQLiteDatabase db, Context accountManagerContext) { 130132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook migrateLegacyAccounts(db, accountManagerContext); 130232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 130332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 130432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook private static void migrateLegacyAccounts(SQLiteDatabase db, Context accountManagerContext) { 130532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final Map<String, String> legacyToNewTypeMap = new ImmutableMap.Builder<String, String>() 130632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook .put(LEGACY_SCHEME_POP3, 130732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook accountManagerContext.getString(R.string.account_manager_type_pop3)) 130832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook .put(LEGACY_SCHEME_IMAP, 130932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook accountManagerContext.getString(R.string.account_manager_type_legacy_imap)) 131032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook .put(LEGACY_SCHEME_EAS, 131132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook accountManagerContext.getString(R.string.account_manager_type_exchange)) 131232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook .build(); 1313c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1314c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Loop through accounts, looking for pop/imap accounts 131532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final Cursor accountCursor = db.query(Account.TABLE_NAME, V21_ACCOUNT_PROJECTION, null, 1316c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null, null); 1317c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 131832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final String[] hostAuthArgs = new String[1]; 1319c5afb16430a145f20d7c887e45f47b38687054daMarc Blank while (accountCursor.moveToNext()) { 1320c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthArgs[0] = accountCursor.getString(V21_ACCOUNT_RECV); 1321c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Get the "receive" HostAuth for this account 132232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 1323c5afb16430a145f20d7c887e45f47b38687054daMarc Blank V21_HOSTAUTH_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs, 1324c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null); 1325c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1326c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (hostAuthCursor.moveToFirst()) { 132732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final String protocol = hostAuthCursor.getString(V21_HOSTAUTH_PROTOCOL); 1328c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // If this is a pop3 or imap account, create the account manager account 132954347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank if (LEGACY_SCHEME_IMAP.equals(protocol) || 133032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook LEGACY_SCHEME_POP3.equals(protocol)) { 133132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // If this is a pop3 or imap account, create the account manager 133232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // account 1333c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (MailActivityEmail.DEBUG) { 1334560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.d(TAG, "Create AccountManager account for " + protocol 1335560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy + "account: " 1336560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy + accountCursor.getString(V21_ACCOUNT_EMAIL)); 1337c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1338c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAccountManagerAccount(accountManagerContext, 1339c5afb16430a145f20d7c887e45f47b38687054daMarc Blank accountCursor.getString(V21_ACCOUNT_EMAIL), 134032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook legacyToNewTypeMap.get(protocol), 1341c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthCursor.getString(V21_HOSTAUTH_PASSWORD)); 134254347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank } else if (LEGACY_SCHEME_EAS.equals(protocol)) { 134332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // If an EAS account, make Email sync automatically (equivalent of 134432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // checking the "Sync Email" box in settings 134532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 134632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook android.accounts.Account amAccount = new android.accounts.Account( 134732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook accountCursor.getString(V21_ACCOUNT_EMAIL), 134832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook legacyToNewTypeMap.get(protocol)); 1349c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 1350c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setSyncAutomatically(amAccount, 1351c5afb16430a145f20d7c887e45f47b38687054daMarc Blank EmailContent.AUTHORITY, true); 1352c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1353c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1354c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1355c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthCursor.close(); 1356c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1357c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1358c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1359c5afb16430a145f20d7c887e45f47b38687054daMarc Blank accountCursor.close(); 1360c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1361c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1362c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1363560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception while migrating accounts " + e); 1364c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1365c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1366c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1367c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v22 to v23 */ 1368c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion22ToVersion23(SQLiteDatabase db) { 1369c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1370c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1371c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.LAST_TOUCHED_TIME + " integer default 0;"); 1372c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1373c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1374560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 22 to 23 " + e); 1375c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1376c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1377c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1378c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Adds in a column for information about a client certificate to use. */ 1379c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion23ToVersion24(SQLiteDatabase db) { 1380c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1381c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + HostAuth.TABLE_NAME 1382c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + HostAuth.CLIENT_CERT_ALIAS + " text;"); 1383c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1384c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1385560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 23 to 24 " + e); 1386c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1387c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1388c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1389c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v24 to v25 by creating table for quick responses */ 1390c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion24ToVersion25(SQLiteDatabase db) { 1391c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1392c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createQuickResponseTable(db); 1393c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1394c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1395560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 24 to 25 " + e); 1396c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1397c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1398c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1399c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] V25_ACCOUNT_PROJECTION = 1400c5afb16430a145f20d7c887e45f47b38687054daMarc Blank new String[] {AccountColumns.ID, AccountColumns.FLAGS, AccountColumns.HOST_AUTH_KEY_RECV}; 1401c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V25_ACCOUNT_ID = 0; 1402c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V25_ACCOUNT_FLAGS = 1; 1403c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V25_ACCOUNT_RECV = 2; 1404c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1405c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] V25_HOSTAUTH_PROJECTION = new String[] {HostAuthColumns.PROTOCOL}; 1406c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V25_HOSTAUTH_PROTOCOL = 0; 1407c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1408c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v25 to v26 by adding FLAG_SUPPORTS_SEARCH to IMAP accounts */ 1409c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion25ToVersion26(SQLiteDatabase db) { 1410c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1411c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Loop through accounts, looking for imap accounts 1412c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Cursor accountCursor = db.query(Account.TABLE_NAME, V25_ACCOUNT_PROJECTION, null, 1413c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null, null); 1414c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentValues cv = new ContentValues(); 1415c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1416c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String[] hostAuthArgs = new String[1]; 1417c5afb16430a145f20d7c887e45f47b38687054daMarc Blank while (accountCursor.moveToNext()) { 1418c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthArgs[0] = accountCursor.getString(V25_ACCOUNT_RECV); 1419c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Get the "receive" HostAuth for this account 1420c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 1421c5afb16430a145f20d7c887e45f47b38687054daMarc Blank V25_HOSTAUTH_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs, 1422c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null); 1423c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1424c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (hostAuthCursor.moveToFirst()) { 1425c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String protocol = hostAuthCursor.getString(V25_HOSTAUTH_PROTOCOL); 1426c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // If this is an imap account, add the search flag 142754347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank if (LEGACY_SCHEME_IMAP.equals(protocol)) { 1428c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String id = accountCursor.getString(V25_ACCOUNT_ID); 1429c5afb16430a145f20d7c887e45f47b38687054daMarc Blank int flags = accountCursor.getInt(V25_ACCOUNT_FLAGS); 1430c5afb16430a145f20d7c887e45f47b38687054daMarc Blank cv.put(AccountColumns.FLAGS, flags | Account.FLAGS_SUPPORTS_SEARCH); 1431c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.update(Account.TABLE_NAME, cv, Account.RECORD_ID + "=?", 1432c5afb16430a145f20d7c887e45f47b38687054daMarc Blank new String[] {id}); 1433c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1434c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1435c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1436c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthCursor.close(); 1437c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1438c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1439c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1440c5afb16430a145f20d7c887e45f47b38687054daMarc Blank accountCursor.close(); 1441c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1442c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1443c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1444560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 25 to 26 " + e); 1445c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1446c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1447c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1448c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v29 to v30 by updating all address fields in Message */ 1449c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int[] ADDRESS_COLUMN_INDICES = new int[] { 1450c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Message.CONTENT_BCC_LIST_COLUMN, Message.CONTENT_CC_LIST_COLUMN, 1451c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Message.CONTENT_FROM_LIST_COLUMN, Message.CONTENT_REPLY_TO_COLUMN, 1452c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Message.CONTENT_TO_LIST_COLUMN 1453c5afb16430a145f20d7c887e45f47b38687054daMarc Blank }; 1454c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] ADDRESS_COLUMN_NAMES = new String[] { 1455c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Message.BCC_LIST, Message.CC_LIST, Message.FROM_LIST, Message.REPLY_TO_LIST, Message.TO_LIST 1456c5afb16430a145f20d7c887e45f47b38687054daMarc Blank }; 1457c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1458c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion29ToVersion30(SQLiteDatabase db) { 1459c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1460c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Loop through all messages, updating address columns to new format (CSV, RFC822) 1461c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Cursor messageCursor = db.query(Message.TABLE_NAME, Message.CONTENT_PROJECTION, null, 1462c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null, null); 1463c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentValues cv = new ContentValues(); 1464c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String[] whereArgs = new String[1]; 1465c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1466c5afb16430a145f20d7c887e45f47b38687054daMarc Blank while (messageCursor.moveToNext()) { 1467c5afb16430a145f20d7c887e45f47b38687054daMarc Blank for (int i = 0; i < ADDRESS_COLUMN_INDICES.length; i++) { 1468c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Address[] addrs = 1469c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Address.unpack(messageCursor.getString(ADDRESS_COLUMN_INDICES[i])); 1470c5afb16430a145f20d7c887e45f47b38687054daMarc Blank cv.put(ADDRESS_COLUMN_NAMES[i], Address.pack(addrs)); 1471c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1472c5afb16430a145f20d7c887e45f47b38687054daMarc Blank whereArgs[0] = messageCursor.getString(Message.CONTENT_ID_COLUMN); 1473c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.update(Message.TABLE_NAME, cv, WHERE_ID, whereArgs); 1474c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1475c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1476c5afb16430a145f20d7c887e45f47b38687054daMarc Blank messageCursor.close(); 1477c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1478c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1479c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1480560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 29 to 30 " + e); 1481c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1482c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1483c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1484c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeToEmail2(SQLiteDatabase db) { 1485c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Perform cleanup operations from Email1 to Email2; Email1 will have added new 1486c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // data that won't conform to what's expected in Email2 1487c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1488c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // From 31->32 upgrade 1489c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1490c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + 1491c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " IS NULL"); 1492c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + 1493c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " IS NULL"); 1494c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1495560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 31 to 32/100 " + e); 1496c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1497c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1498c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // From 32->33 upgrade 1499c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1500c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Attachment.TABLE_NAME + " set " + Attachment.UI_STATE + 1501c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=" + UIProvider.AttachmentState.SAVED + " where " + 1502c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AttachmentColumns.CONTENT_URI + " is not null;"); 1503c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1504560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 32 to 33/100 " + e); 1505c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1506c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1507c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // From 34->35 upgrade 1508c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1509c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1510c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.LAST_TOUCHED_TIME + " = " + 1511c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.DRAFTS_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1512c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " = " + Mailbox.TYPE_DRAFTS); 1513c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1514c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.LAST_TOUCHED_TIME + " = " + 1515c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.SENT_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1516c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " = " + Mailbox.TYPE_SENT); 1517c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1518560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 34 to 35/100 " + e); 1519c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1520c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1521c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // From 35/36->37 1522c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1523c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1524c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.FLAGS + "=" + MailboxColumns.FLAGS + "|" + 1525c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.FLAG_SUPPORTS_SETTINGS + " where (" + 1526c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.FLAGS + "&" + Mailbox.FLAG_HOLDS_MAIL + ")!=0 and " + 1527c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.ACCOUNT_KEY + " IN (SELECT " + Account.TABLE_NAME + 1528c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "." + AccountColumns.ID + " from " + Account.TABLE_NAME + "," + 1529c5afb16430a145f20d7c887e45f47b38687054daMarc Blank HostAuth.TABLE_NAME + " where " + Account.TABLE_NAME + "." + 1530c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AccountColumns.HOST_AUTH_KEY_RECV + "=" + HostAuth.TABLE_NAME + "." + 1531c5afb16430a145f20d7c887e45f47b38687054daMarc Blank HostAuthColumns.ID + " and " + HostAuthColumns.PROTOCOL + "='" + 153254347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank LEGACY_SCHEME_EAS + "')"); 1533c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1534560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 35/36 to 37/100 " + e); 1535c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1536c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1537c5afb16430a145f20d7c887e45f47b38687054daMarc Blank} 1538