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; 267525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantlerimport android.database.sqlite.SQLiteDoneException; 27c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.database.sqlite.SQLiteOpenHelper; 287525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantlerimport android.database.sqlite.SQLiteStatement; 293dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantlerimport android.provider.BaseColumns; 30c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.provider.CalendarContract; 31c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport android.provider.ContactsContract; 3232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport android.text.TextUtils; 33c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 3451c653646d14d841fbe527aee9fab7a1886338f8Martin Hibdonimport com.android.email.DebugUtils; 3532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport com.android.email.R; 36c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.mail.Address; 37c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.Account; 380b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdonimport com.android.emailcommon.provider.Credential; 39c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent; 40c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.AccountColumns; 41c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.Attachment; 42c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.AttachmentColumns; 43c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.Body; 44c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.BodyColumns; 45c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.HostAuthColumns; 46c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.MailboxColumns; 47c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.Message; 48c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.MessageColumns; 49c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.PolicyColumns; 50c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.QuickResponseColumns; 51c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.EmailContent.SyncColumns; 52c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.HostAuth; 53c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.Mailbox; 54ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Huimport com.android.emailcommon.provider.MessageChangeLogTable; 55ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Huimport com.android.emailcommon.provider.MessageMove; 56ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Huimport com.android.emailcommon.provider.MessageStateChange; 57c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.Policy; 58c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.provider.QuickResponse; 59c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.emailcommon.service.LegacyPolicySet; 6021b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Huimport com.android.emailcommon.service.SyncWindow; 61c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.android.mail.providers.UIProvider; 6232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport com.android.mail.utils.LogUtils; 63c5afb16430a145f20d7c887e45f47b38687054daMarc Blankimport com.google.common.annotations.VisibleForTesting; 6432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport com.google.common.collect.ImmutableMap; 6532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 667525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantlerimport java.io.File; 677525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantlerimport java.io.FileWriter; 687525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantlerimport java.io.IOException; 6932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrookimport java.util.Map; 70c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 71c5afb16430a145f20d7c887e45f47b38687054daMarc Blankpublic final class DBHelper { 72c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String TAG = "EmailProvider"; 73c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 7454347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank private static final String LEGACY_SCHEME_IMAP = "imap"; 7554347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank private static final String LEGACY_SCHEME_POP3 = "pop3"; 7654347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank private static final String LEGACY_SCHEME_EAS = "eas"; 7754347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank 7813ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon 793dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler private static final String WHERE_ID = BaseColumns._ID + "=?"; 80c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 81c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String TRIGGER_MAILBOX_DELETE = 82c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME + 83c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin" + 84c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " delete from " + Message.TABLE_NAME + 853dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + MessageColumns.MAILBOX_KEY + "=old." + BaseColumns._ID + 86c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + Message.UPDATED_TABLE_NAME + 873dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + MessageColumns.MAILBOX_KEY + "=old." + BaseColumns._ID + 88c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + Message.DELETED_TABLE_NAME + 893dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + MessageColumns.MAILBOX_KEY + "=old." + BaseColumns._ID + 90c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"; 91c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 92c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String TRIGGER_ACCOUNT_DELETE = 93c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "create trigger account_delete before delete on " + Account.TABLE_NAME + 94c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin delete from " + Mailbox.TABLE_NAME + 953dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + MailboxColumns.ACCOUNT_KEY + "=old." + BaseColumns._ID + 96c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + HostAuth.TABLE_NAME + 973dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV + 98c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + HostAuth.TABLE_NAME + 993dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND + 100c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; delete from " + Policy.TABLE_NAME + 1013dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=old." + AccountColumns.POLICY_KEY + 102c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"; 103c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1040b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon private static final String TRIGGER_HOST_AUTH_DELETE = 1050b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon "create trigger host_auth_delete after delete on " + HostAuth.TABLE_NAME + 1060b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon " begin delete from " + Credential.TABLE_NAME + 1073dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + Credential._ID + "=old." + HostAuthColumns.CREDENTIAL_KEY + 1080b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon " and (select count(*) from " + HostAuth.TABLE_NAME + " where " + 1093dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler HostAuthColumns.CREDENTIAL_KEY + "=old." + HostAuthColumns.CREDENTIAL_KEY + ")=0" + 1100b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon "; end"; 1110b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon 1120b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon 113c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Any changes to the database format *must* include update-in-place code. 114c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Original version: 3 115c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 4: Database wipe required; changing AccountManager interface w/Exchange 116c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 5: Database wipe required; changing AccountManager interface w/Exchange 117c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 6: Adding Message.mServerTimeStamp column 118c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 7: Replace the mailbox_delete trigger with a version that removes orphaned messages 119c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // from the Message_Deletes and Message_Updates tables 120c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 8: Add security flags column to accounts table 121c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 9: Add security sync key and signature to accounts table 122c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 10: Add meeting info to message table 123c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 11: Add content and flags to attachment table 124c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 12: Add content_bytes to attachment table. content is deprecated. 125c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 13: Add messageCount to Mailbox table. 126c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 14: Add snippet to Message table 127c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 15: Fix upgrade problem in version 14. 128c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 16: Add accountKey to Attachment table 129c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 17: Add parentKey to Mailbox table 130c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 18: Copy Mailbox.displayName to Mailbox.serverId for all IMAP & POP3 mailboxes. 131c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Column Mailbox.serverId is used for the server-side pathname of a mailbox. 132c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 19: Add Policy table; add policyKey to Account table and trigger to delete an 133c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Account's policy when the Account is deleted 134c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 20: Add new policies to Policy table 135c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 21: Add lastSeenMessageKey column to Mailbox table 136c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 22: Upgrade path for IMAP/POP accounts to integrate with AccountManager 137c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 23: Add column to mailbox table for time of last access 138c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 24: Add column to hostauth table for client cert alias 139c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 25: Added QuickResponse table 140c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 26: Update IMAP accounts to add FLAG_SUPPORTS_SEARCH flag 141c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 27: Add protocolSearchInfo to Message table 142c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 28: Add notifiedMessageId and notifiedMessageCount to Account 143c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 29: Add protocolPoliciesEnforced and protocolPoliciesUnsupported to Policy 144c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 30: Use CSV of RFC822 addresses instead of "packed" values 145c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 31: Add columns to mailbox for ui status/last result 146c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 32: Add columns to mailbox for last notified message key/count; insure not null 147c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // for "notified" columns 148c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 33: Add columns to attachment for ui provider columns 149c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 34: Add total count to mailbox 150c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 35: Set up defaults for lastTouchedCount for drafts and sent 151c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 36: mblank intentionally left this space 152c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 37: Add flag for settings support in folders 153c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 38&39: Add threadTopic to message (for future support) 154c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 39 is last Email1 version 155c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 100 is first Email2 version 156c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 101 SHOULD NOT BE USED 157c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 102&103: Add hierarchicalName to Mailbox 158c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank // Version 104&105: add syncData to Message 1597d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank // Version 106: Add certificate to HostAuth 160b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy // Version 107: Add a SEEN column to the message table 1619a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook // Version 108: Add a cachedFile column to the attachments table 16232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // Version 109: Migrate the account so they have the correct account manager types 16321b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu // Version 110: Stop updating message_count, don't use auto lookback, and don't use 1645ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // ping/push_hold sync states. Note that message_count updating is restored in 113. 165aa0ca16a27e4a56a029e5cdebf96de5723bd84b6Yu Ping Hu // Version 111: Delete Exchange account mailboxes. 166a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // Version 112: Convert Mailbox syncInterval to a boolean (whether or not this mailbox 167a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // syncs along with the account). 1685ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Version 113: Restore message_count to being useful. 169c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon // Version 114: Add lastFullSyncTime column 17052135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon // Version 115: Add pingDuration column 171ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu // Version 116: Add MessageMove & MessageStateChange tables. 172e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu // Version 117: Add trigger to delete duplicate messages on sync. 173e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler // Version 118: Set syncInterval to 0 for all IMAP mailboxes 174cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon // Version 119: Disable syncing of DRAFTS type folders. 175ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon // Version 120: Changed duplicateMessage deletion trigger to ignore search mailboxes. 176c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // Version 121: Add mainMailboxKey, which will be set for messages that are in the fake 177c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // "search_results" folder to reflect the mailbox that the server considers 178c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // the message to be in. Also, wipe out any stale search_result folders. 1797b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu // Version 122: Need to update Message_Updates and Message_Deletes to match previous. 18013ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon // Version 123: Changed the duplicateMesage deletion trigger to ignore accounts that aren't 18113ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon // exchange accounts. 182eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee // Version 124: Added MAX_ATTACHMENT_SIZE to the account table 183eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee // Version 125: Add credentials table for OAuth. 1845057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux // Version 126: Decode address lists for To, From, Cc, Bcc and Reply-To columns in Message. 1856f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee // Version 127: Force mFlags to contain the correct flags for EAS accounts given a protocol 1866f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee // version above 12.0 1876f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee public static final int DATABASE_VERSION = 127; 188c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 189c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Any changes to the database format *must* include update-in-place code. 190c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Original version: 2 191c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 3: Add "sourceKey" column 192c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 4: Database wipe required; changing AccountManager interface w/Exchange 193c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 5: Database wipe required; changing AccountManager interface w/Exchange 194c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 6: Adding Body.mIntroText column 195c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 7/8: Adding quoted text start pos 196c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Version 8 is last Email1 version 1977525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler // Version 100 is the first Email2 version 1987525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler // Version 101: Move body contents to external files 1997525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler public static final int BODY_DATABASE_VERSION = 101; 200c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 201c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /* 202c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Internal helper method for index creation. 203c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Example: 204c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * "create index message_" + MessageColumns.FLAG_READ 205c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");" 206c5afb16430a145f20d7c887e45f47b38687054daMarc Blank */ 207c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /* package */ 208c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static String createIndex(String tableName, String columnName) { 209c5afb16430a145f20d7c887e45f47b38687054daMarc Blank return "create index " + tableName.toLowerCase() + '_' + columnName 210c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " on " + tableName + " (" + columnName + ");"; 211c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 212c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 2135ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu static void createMessageCountTriggers(final SQLiteDatabase db) { 2145ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Insert a message. 2155ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu db.execSQL("create trigger message_count_message_insert after insert on " + 2165ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu Message.TABLE_NAME + 2175ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 2185ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 2193dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=NEW." + MessageColumns.MAILBOX_KEY + 2205ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu "; end"); 2215ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu 2225ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Delete a message. 2235ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu db.execSQL("create trigger message_count_message_delete after delete on " + 2245ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu Message.TABLE_NAME + 2255ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 2265ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 2273dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 2285ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu "; end"); 2295ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu 2305ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Change a message's mailbox. 2315ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu db.execSQL("create trigger message_count_message_move after update of " + 2325ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 2335ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 2345ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 2353dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 2365ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 2375ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 2383dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=NEW." + MessageColumns.MAILBOX_KEY + 2395ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu "; end"); 2405ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu } 2415ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu 2420b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon static void createCredentialsTable(SQLiteDatabase db) { 2433dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String s = " (" + Credential._ID + " integer primary key autoincrement, " 2440b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + Credential.PROVIDER_COLUMN + " text," 2450b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + Credential.ACCESS_TOKEN_COLUMN + " text," 2460b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + Credential.REFRESH_TOKEN_COLUMN + " text," 2470b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + Credential.EXPIRATION_COLUMN + " integer" 2480b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + ");"; 2490b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon db.execSQL("create table " + Credential.TABLE_NAME + s); 2500b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon db.execSQL(TRIGGER_HOST_AUTH_DELETE); 2510b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon } 2520b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon 253ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon static void dropDeleteDuplicateMessagesTrigger(final SQLiteDatabase db) { 254ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon db.execSQL("drop trigger message_delete_duplicates_on_insert"); 255ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon } 256ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon 257e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu /** 258e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu * Add a trigger to delete duplicate server side messages before insertion. 25913ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * This should delete any messages older messages that have the same serverId and account as 26013ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * the new message, if: 26113ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * Neither message is in a SEARCH type mailbox, and 26213ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * The new message's mailbox's account is an exchange account. 26313ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * 264ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon * Here is the plain text of this sql: 265ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon * create trigger message_delete_duplicates_on_insert before insert on 26613ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * Message for each row when new.syncServerId is not null and 26713ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * (select type from Mailbox where _id=new.mailboxKey) != 8 and 26813ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * (select HostAuth.protocol from HostAuth, Account where 26913ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * new.accountKey=account._id and account.hostAuthKeyRecv=hostAuth._id) = 'gEas' 27013ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * begin delete from Message where new.syncServerId=syncSeverId and 271ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon * new.accountKey=accountKey and 27213ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * (select Mailbox.type from Mailbox where _id=mailboxKey) != 8; end 273e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu */ 27413ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon static void createDeleteDuplicateMessagesTrigger(final Context context, 27513ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon final SQLiteDatabase db) { 276e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu db.execSQL("create trigger message_delete_duplicates_on_insert before insert on " 277e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu + Message.TABLE_NAME + " for each row when new." + SyncColumns.SERVER_ID 278ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon + " is not null and " 27913ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon + "(select " + MailboxColumns.TYPE + " from " + Mailbox.TABLE_NAME 2803dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " where " + MailboxColumns._ID + "=new." 281ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon + MessageColumns.MAILBOX_KEY + ")!=" + Mailbox.TYPE_SEARCH 28213ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon + " and (select " 28313ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + " from " 28413ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon + HostAuth.TABLE_NAME + "," + Account.TABLE_NAME 28513ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon + " where new." + MessageColumns.ACCOUNT_KEY 2863dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + "=" + Account.TABLE_NAME + "." + AccountColumns._ID 28713ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon + " and " + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV 2883dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + "=" + HostAuth.TABLE_NAME + "." + HostAuthColumns._ID 28913ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon + ")='" + context.getString(R.string.protocol_eas) + "'" 290ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon + " begin delete from " + Message.TABLE_NAME + " where new." 291e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu + SyncColumns.SERVER_ID + "=" + SyncColumns.SERVER_ID + " and new." 292ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon + MessageColumns.ACCOUNT_KEY + "=" + MessageColumns.ACCOUNT_KEY 293ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon + " and (select " + Mailbox.TABLE_NAME + "." + MailboxColumns.TYPE + " from " 2943dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + Mailbox.TABLE_NAME + " where " + MailboxColumns._ID + "=" 295ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon + MessageColumns.MAILBOX_KEY + ")!=" + Mailbox.TYPE_SEARCH +"; end"); 296e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu } 297e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu 29813ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon static void createMessageTable(Context context, SQLiteDatabase db) { 299c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String messageColumns = MessageColumns.DISPLAY_NAME + " text, " 300c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.TIMESTAMP + " integer, " 301c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.SUBJECT + " text, " 302c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAG_READ + " integer, " 303c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAG_LOADED + " integer, " 304c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAG_FAVORITE + " integer, " 305c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAG_ATTACHMENT + " integer, " 306c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FLAGS + " integer, " 307c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.DRAFT_INFO + " integer, " 308c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.MESSAGE_ID + " text, " 309c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.MAILBOX_KEY + " integer, " 310c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.ACCOUNT_KEY + " integer, " 311c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.FROM_LIST + " text, " 312c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.TO_LIST + " text, " 313c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.CC_LIST + " text, " 314c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.BCC_LIST + " text, " 315c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.REPLY_TO_LIST + " text, " 316c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.MEETING_INFO + " text, " 317c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.SNIPPET + " text, " 318c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MessageColumns.PROTOCOL_SEARCH_INFO + " text, " 319c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank + MessageColumns.THREAD_TOPIC + " text, " 320b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + MessageColumns.SYNC_DATA + " text, " 321c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon + MessageColumns.FLAG_SEEN + " integer, " 322c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon + MessageColumns.MAIN_MAILBOX_KEY + " integer" 323c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 324c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 325c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // This String and the following String MUST have the same columns, except for the type 326c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // of those columns! 3273dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String createString = " (" + BaseColumns._ID + " integer primary key autoincrement, " 328c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + SyncColumns.SERVER_ID + " text, " 329c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + SyncColumns.SERVER_TIMESTAMP + " integer, " 330c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + messageColumns; 331c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 332c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // For the updated and deleted tables, the id is assigned, but we do want to keep track 333c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // of the ORDER of updates using an autoincrement primary key. We use the DATA column 334c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // at this point; it has no other function 3353dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String altCreateString = " (" + BaseColumns._ID + " integer unique, " 336c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + SyncColumns.SERVER_ID + " text, " 337c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + SyncColumns.SERVER_TIMESTAMP + " integer, " 338c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + messageColumns; 339c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 340c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // The three tables have the same schema 341c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Message.TABLE_NAME + createString); 342c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString); 343c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString); 344c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 345c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String indexColumns[] = { 346c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.TIMESTAMP, 347c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.FLAG_READ, 348c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.FLAG_LOADED, 349c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.MAILBOX_KEY, 350c5afb16430a145f20d7c887e45f47b38687054daMarc Blank SyncColumns.SERVER_ID 351c5afb16430a145f20d7c887e45f47b38687054daMarc Blank }; 352c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 353c5afb16430a145f20d7c887e45f47b38687054daMarc Blank for (String columnName : indexColumns) { 354c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(createIndex(Message.TABLE_NAME, columnName)); 355c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 356c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 357c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Deleting a Message deletes all associated Attachments 358c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Deleting the associated Body cannot be done in a trigger, because the Body is stored 359c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // in a separate database, and trigger cannot operate on attached databases. 360c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME + 361c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin delete from " + Attachment.TABLE_NAME + 3623dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + AttachmentColumns.MESSAGE_KEY + "=old." + BaseColumns._ID + 363c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 364c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 365c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Add triggers to keep unread count accurate per mailbox 366c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 367c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // NOTE: SQLite's before triggers are not safe when recursive triggers are involved. 368c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Use caution when changing them. 369c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 370c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox 371c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME + 372c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when NEW." + MessageColumns.FLAG_READ + "=0" + 373c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 374c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "+1" + 3753dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=NEW." + MessageColumns.MAILBOX_KEY + 376c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 377c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 378c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 379c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME + 380c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when OLD." + MessageColumns.FLAG_READ + "=0" + 381c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 382c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "-1" + 3833dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 384c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 385c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 386c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Change a message's mailbox 387c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger unread_message_move before update of " + 388c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 389c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when OLD." + MessageColumns.FLAG_READ + "=0" + 390c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 391c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "-1" + 3923dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 393c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 394c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "+1" + 3953dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=NEW." + MessageColumns.MAILBOX_KEY + 396c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 397c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 398c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Change a message's read state 399c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create trigger unread_message_read before update of " + 400c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME + 401c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ + 402c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 403c5afb16430a145f20d7c887e45f47b38687054daMarc Blank '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ + 404c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " when 0 then -1 else 1 end" + 4053dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 406c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "; end"); 4075ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu 4085ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // Add triggers to maintain message_count. 4095ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu createMessageCountTriggers(db); 41013ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon createDeleteDuplicateMessagesTrigger(context, db); 411c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 412c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 41313ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon static void resetMessageTable(Context context, SQLiteDatabase db, 41413ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon int oldVersion, int newVersion) { 415c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 416c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Message.TABLE_NAME); 417c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Message.UPDATED_TABLE_NAME); 418c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Message.DELETED_TABLE_NAME); 419c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 420c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 42113ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon createMessageTable(context, db); 422c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 423c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 424ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 425ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Common columns for all {@link MessageChangeLogTable} tables. 426ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 427ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static String MESSAGE_CHANGE_LOG_COLUMNS = 428ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu MessageChangeLogTable.ID + " integer primary key autoincrement, " 429ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageChangeLogTable.MESSAGE_KEY + " integer, " 430ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageChangeLogTable.SERVER_ID + " text, " 431ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageChangeLogTable.ACCOUNT_KEY + " integer, " 432ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageChangeLogTable.STATUS + " integer, "; 433ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 434ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 435ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Create indices common to all {@link MessageChangeLogTable} tables. 436ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param db The {@link SQLiteDatabase}. 437ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param tableName The name of this particular table. 438ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 439ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static void createMessageChangeLogTableIndices(final SQLiteDatabase db, 440ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu final String tableName) { 441ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL(createIndex(tableName, MessageChangeLogTable.MESSAGE_KEY)); 442ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL(createIndex(tableName, MessageChangeLogTable.ACCOUNT_KEY)); 443ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 444ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 445ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 446ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Create triggers common to all {@link MessageChangeLogTable} tables. 447ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param db The {@link SQLiteDatabase}. 448ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param tableName The name of this particular table. 449ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 450ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static void createMessageChangeLogTableTriggers(final SQLiteDatabase db, 451ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu final String tableName) { 452ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu // Trigger to delete from the change log when a message is deleted. 453ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL("create trigger " + tableName + "_delete_message before delete on " 454ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + Message.TABLE_NAME + " for each row begin delete from " + tableName 4553dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " where " + MessageChangeLogTable.MESSAGE_KEY + "=old." + MessageColumns._ID 456ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + "; end"); 457ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 458ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu // Trigger to delete from the change log when an account is deleted. 459ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL("create trigger " + tableName + "_delete_account before delete on " 460ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + Account.TABLE_NAME + " for each row begin delete from " + tableName 4613dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " where " + MessageChangeLogTable.ACCOUNT_KEY + "=old." + AccountColumns._ID 462ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + "; end"); 463ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 464ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 465ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 466ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Create the MessageMove table. 467ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param db The {@link SQLiteDatabase}. 468ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 469ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static void createMessageMoveTable(final SQLiteDatabase db) { 470ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL("create table " + MessageMove.TABLE_NAME + " (" 471ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MESSAGE_CHANGE_LOG_COLUMNS 472ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageMove.SRC_FOLDER_KEY + " integer, " 473ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageMove.DST_FOLDER_KEY + " integer, " 474ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageMove.SRC_FOLDER_SERVER_ID + " text, " 475ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageMove.DST_FOLDER_SERVER_ID + " text);"); 476ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 477ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageChangeLogTableIndices(db, MessageMove.TABLE_NAME); 478ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageChangeLogTableTriggers(db, MessageMove.TABLE_NAME); 479ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 480ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 481ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu /** 482ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * Create the MessageStateChange table. 483ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu * @param db The {@link SQLiteDatabase}. 484ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu */ 485ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu private static void createMessageStateChangeTable(final SQLiteDatabase db) { 486ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu db.execSQL("create table " + MessageStateChange.TABLE_NAME + " (" 487ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MESSAGE_CHANGE_LOG_COLUMNS 488ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageStateChange.OLD_FLAG_READ + " integer, " 489ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageStateChange.NEW_FLAG_READ + " integer, " 490ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageStateChange.OLD_FLAG_FAVORITE + " integer, " 491ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu + MessageStateChange.NEW_FLAG_FAVORITE + " integer);"); 492ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 493ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageChangeLogTableIndices(db, MessageStateChange.TABLE_NAME); 494ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageChangeLogTableTriggers(db, MessageStateChange.TABLE_NAME); 495ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 496ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 497c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @SuppressWarnings("deprecation") 498c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createAccountTable(SQLiteDatabase db) { 4993dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String s = " (" + AccountColumns._ID + " integer primary key autoincrement, " 500c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.DISPLAY_NAME + " text, " 501c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.EMAIL_ADDRESS + " text, " 502c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SYNC_KEY + " text, " 503c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SYNC_LOOKBACK + " integer, " 504c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SYNC_INTERVAL + " text, " 505c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.HOST_AUTH_KEY_RECV + " integer, " 506c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.HOST_AUTH_KEY_SEND + " integer, " 507c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.FLAGS + " integer, " 508c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.IS_DEFAULT + " integer, " 509c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.COMPATIBILITY_UUID + " text, " 510c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SENDER_NAME + " text, " 511c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.RINGTONE_URI + " text, " 512c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.PROTOCOL_VERSION + " text, " 513c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.NEW_MESSAGE_COUNT + " integer, " 514c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SECURITY_FLAGS + " integer, " 515c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SECURITY_SYNC_KEY + " text, " 516c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AccountColumns.SIGNATURE + " text, " 51752135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon + AccountColumns.POLICY_KEY + " integer, " 518eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee + AccountColumns.MAX_ATTACHMENT_SIZE + " integer, " 51952135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon + AccountColumns.PING_DURATION + " integer" 520c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 521c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Account.TABLE_NAME + s); 522c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Deleting an account deletes associated Mailboxes and HostAuth's 523c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(TRIGGER_ACCOUNT_DELETE); 524c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 525c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 526c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) { 527c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 528c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Account.TABLE_NAME); 529c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 530c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 531c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAccountTable(db); 532c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 533c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 534c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createPolicyTable(SQLiteDatabase db) { 5353dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String s = " (" + PolicyColumns._ID + " integer primary key autoincrement, " 536c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_MODE + " integer, " 537c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_MIN_LENGTH + " integer, " 538c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_EXPIRATION_DAYS + " integer, " 539c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_HISTORY + " integer, " 540c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_COMPLEX_CHARS + " integer, " 541c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_MAX_FAILS + " integer, " 542c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_SCREEN_LOCK_TIME + " integer, " 543c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.REQUIRE_REMOTE_WIPE + " integer, " 544c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.REQUIRE_ENCRYPTION + " integer, " 545c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.REQUIRE_ENCRYPTION_EXTERNAL + " integer, " 546c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + " integer, " 547c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.DONT_ALLOW_CAMERA + " integer, " 548c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer, " 549c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.DONT_ALLOW_HTML + " integer, " 550c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer, " 551c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + " integer, " 552c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + " integer, " 553c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer, " 554c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer, " 555c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PASSWORD_RECOVERY_ENABLED + " integer, " 556c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PROTOCOL_POLICIES_ENFORCED + " text, " 557c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + PolicyColumns.PROTOCOL_POLICIES_UNSUPPORTED + " text" 558c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 559c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Policy.TABLE_NAME + s); 560c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 561c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 562c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createHostAuthTable(SQLiteDatabase db) { 5633dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String s = " (" + HostAuthColumns._ID + " integer primary key autoincrement, " 564c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.PROTOCOL + " text, " 565c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.ADDRESS + " text, " 566c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.PORT + " integer, " 567c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.FLAGS + " integer, " 568c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.LOGIN + " text, " 569c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.PASSWORD + " text, " 570c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.DOMAIN + " text, " 571c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuthColumns.ACCOUNT_KEY + " integer," 5727d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank + HostAuthColumns.CLIENT_CERT_ALIAS + " text," 5730b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + HostAuthColumns.SERVER_CERT + " blob," 5740b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + HostAuthColumns.CREDENTIAL_KEY + " integer" 575c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 576c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + HostAuth.TABLE_NAME + s); 577c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 578c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 579c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) { 580c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 581c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + HostAuth.TABLE_NAME); 582c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 583c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 584c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createHostAuthTable(db); 585c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 586c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 5877525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler @SuppressWarnings("deprecation") 588c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createMailboxTable(SQLiteDatabase db) { 5893dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String s = " (" + MailboxColumns._ID + " integer primary key autoincrement, " 590c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.DISPLAY_NAME + " text, " 591c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SERVER_ID + " text, " 592c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.PARENT_SERVER_ID + " text, " 593c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.PARENT_KEY + " integer, " 594c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.ACCOUNT_KEY + " integer, " 595c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.TYPE + " integer, " 596c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.DELIMITER + " integer, " 597c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_KEY + " text, " 598c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_LOOKBACK + " integer, " 599c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_INTERVAL + " integer, " 600c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_TIME + " integer, " 601c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.UNREAD_COUNT + " integer, " 602c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.FLAG_VISIBLE + " integer, " 603c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.FLAGS + " integer, " 604c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.VISIBLE_LIMIT + " integer, " 605c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SYNC_STATUS + " text, " 606c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.MESSAGE_COUNT + " integer not null default 0, " 607c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.LAST_TOUCHED_TIME + " integer default 0, " 608c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.UI_SYNC_STATUS + " integer default 0, " 609c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.UI_LAST_SYNC_RESULT + " integer default 0, " 610c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.LAST_NOTIFIED_MESSAGE_KEY + " integer not null default 0, " 611c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.LAST_NOTIFIED_MESSAGE_COUNT + " integer not null default 0, " 612c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.TOTAL_COUNT + " integer, " 613c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon + MailboxColumns.HIERARCHICAL_NAME + " text, " 614c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon + MailboxColumns.LAST_FULL_SYNC_TIME + " integer" 615c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 616c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Mailbox.TABLE_NAME + s); 617c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID 618c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")"); 619c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY 620c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")"); 621c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Deleting a Mailbox deletes associated Messages in all three tables 622c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(TRIGGER_MAILBOX_DELETE); 623c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 624c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 625c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) { 626c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 627c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Mailbox.TABLE_NAME); 628c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 629c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 630c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createMailboxTable(db); 631c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 632c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 633c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createAttachmentTable(SQLiteDatabase db) { 6343dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String s = " (" + AttachmentColumns._ID + " integer primary key autoincrement, " 635c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.FILENAME + " text, " 636c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.MIME_TYPE + " text, " 637c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.SIZE + " integer, " 638c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.CONTENT_ID + " text, " 639c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.CONTENT_URI + " text, " 640c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.MESSAGE_KEY + " integer, " 641c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.LOCATION + " text, " 642c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.ENCODING + " text, " 643c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.CONTENT + " text, " 644c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.FLAGS + " integer, " 645c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.CONTENT_BYTES + " blob, " 646c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.ACCOUNT_KEY + " integer, " 647c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.UI_STATE + " integer, " 648c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + AttachmentColumns.UI_DESTINATION + " integer, " 6499a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook + AttachmentColumns.UI_DOWNLOADED_SIZE + " integer, " 6509a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook + AttachmentColumns.CACHED_FILE + " text" 651c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 652c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Attachment.TABLE_NAME + s); 653c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY)); 654c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 655c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 656c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) { 657c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 658c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop table " + Attachment.TABLE_NAME); 659c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 660c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 661c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAttachmentTable(db); 662c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 663c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 664c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createQuickResponseTable(SQLiteDatabase db) { 6653dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String s = " (" + QuickResponseColumns._ID + " integer primary key autoincrement, " 666c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + QuickResponseColumns.TEXT + " text, " 667c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + QuickResponseColumns.ACCOUNT_KEY + " integer" 668c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 669c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + QuickResponse.TABLE_NAME + s); 670c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 671c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 6727525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler @SuppressWarnings("deprecation") 673c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void createBodyTable(SQLiteDatabase db) { 6743dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler String s = " (" + BodyColumns._ID + " integer primary key autoincrement, " 675c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.MESSAGE_KEY + " integer, " 676c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.HTML_CONTENT + " text, " 677c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.TEXT_CONTENT + " text, " 678c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.HTML_REPLY + " text, " 679c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.TEXT_REPLY + " text, " 680c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.SOURCE_MESSAGE_KEY + " text, " 681c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.INTRO_TEXT + " text, " 682c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + BodyColumns.QUOTED_TEXT_START_POS + " integer" 683c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ");"; 684c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("create table " + Body.TABLE_NAME + s); 685c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY)); 686c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 687c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 6887525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler private static void upgradeBodyToVersion5(final SQLiteDatabase db) { 6897525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler try { 6907525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler db.execSQL("drop table " + Body.TABLE_NAME); 6917525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler createBodyTable(db); 6927525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } catch (final SQLException e) { 6937525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler // Shouldn't be needed unless we're debugging and interrupt the process 6947525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler LogUtils.w(TAG, e, "Exception upgrading EmailProviderBody.db from <v5"); 695c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 6967525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 6977525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 6987525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler @SuppressWarnings("deprecation") 6997525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler private static void upgradeBodyFromVersion5ToVersion6(final SQLiteDatabase db) { 7007525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler try { 7017525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler db.execSQL("alter table " + Body.TABLE_NAME 7027525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler + " add " + BodyColumns.INTRO_TEXT + " text"); 7037525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } catch (final SQLException e) { 7047525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler // Shouldn't be needed unless we're debugging and interrupt the process 7057525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler LogUtils.w(TAG, e, "Exception upgrading EmailProviderBody.db from v5 to v6"); 706c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7077525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7087525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 7097525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler private static void upgradeBodyFromVersion6ToVersion8(final SQLiteDatabase db) { 7107525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler try { 7117525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler db.execSQL("alter table " + Body.TABLE_NAME 7127525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler + " add " + BodyColumns.QUOTED_TEXT_START_POS + " integer"); 7137525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } catch (final SQLException e) { 7147525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler // Shouldn't be needed unless we're debugging and interrupt the process 7157525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler LogUtils.w(TAG, e, "Exception upgrading EmailProviderBody.db from v6 to v8"); 716c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 7177525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7187525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 7197525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler /** 7207525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler * This upgrade migrates email bodies out of the database and into individual files. 7217525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler */ 7227525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler private static void upgradeBodyFromVersion100ToVersion101(final Context context, 7237525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final SQLiteDatabase db) { 7247525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler try { 7257525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler // We can't read the body parts through the cursor because they might be over 2MB 7267525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final String projection[] = { BodyColumns.MESSAGE_KEY }; 7277525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final Cursor cursor = db.query(Body.TABLE_NAME, projection, 7287525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler null, null, null, null, null); 7297525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler if (cursor == null) { 7307525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler throw new IllegalStateException("Could not read body table for upgrade"); 7317525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7327525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 7337525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final SQLiteStatement htmlSql = db.compileStatement( 7347525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler "SELECT " + BodyColumns.HTML_CONTENT + 7357525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler " FROM " + Body.TABLE_NAME + 7367525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler " WHERE " + BodyColumns.MESSAGE_KEY + "=?" 7377525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler ); 7387525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 7397525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final SQLiteStatement textSql = db.compileStatement( 7407525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler "SELECT " + BodyColumns.TEXT_CONTENT + 7417525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler " FROM " + Body.TABLE_NAME + 7427525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler " WHERE " + BodyColumns.MESSAGE_KEY + "=?" 7437525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler ); 7447525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 7457525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler while (cursor.moveToNext()) { 7467525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final long messageId = cursor.getLong(0); 7477525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler htmlSql.bindLong(1, messageId); 7487525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler try { 7497525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final String htmlString = htmlSql.simpleQueryForString(); 7507525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler if (!TextUtils.isEmpty(htmlString)) { 7517525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final File htmlFile = EmailProvider.getBodyFile(context, messageId, "html"); 7527525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final FileWriter w = new FileWriter(htmlFile); 7537525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler try { 7547525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler w.write(htmlString); 7557525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } finally { 7567525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler w.close(); 7577525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7587525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7597525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } catch (final SQLiteDoneException e) { 7607525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler LogUtils.v(LogUtils.TAG, e, "Done with the HTML column"); 7617525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7627525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler textSql.bindLong(1, messageId); 7637525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler try { 7647525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final String textString = textSql.simpleQueryForString(); 7657525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler if (!TextUtils.isEmpty(textString)) { 7667525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final File textFile = EmailProvider.getBodyFile(context, messageId, "txt"); 7677525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final FileWriter w = new FileWriter(textFile); 7687525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler try { 7697525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler w.write(textString); 7707525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } finally { 7717525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler w.close(); 7727525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7737525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7747525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } catch (final SQLiteDoneException e) { 7757525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler LogUtils.v(LogUtils.TAG, e, "Done with the text column"); 7767525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7777525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 7787525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 7797525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler db.execSQL("update " + Body.TABLE_NAME + 7807525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler " set " + BodyColumns.HTML_CONTENT + "=NULL," 7817525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler + BodyColumns.TEXT_CONTENT + "=NULL"); 7827525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } catch (final SQLException e) { 7837525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler // Shouldn't be needed unless we're debugging and interrupt the process 7847525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler LogUtils.w(TAG, e, "Exception upgrading EmailProviderBody.db from v100 to v101"); 7857525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } catch (final IOException e) { 7867525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler throw new RuntimeException(e); 787c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 788c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 789c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 7907525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 791c5afb16430a145f20d7c887e45f47b38687054daMarc Blank protected static class BodyDatabaseHelper extends SQLiteOpenHelper { 7927525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final Context mContext; 7937525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler 794c5afb16430a145f20d7c887e45f47b38687054daMarc Blank BodyDatabaseHelper(Context context, String name) { 795c5afb16430a145f20d7c887e45f47b38687054daMarc Blank super(context, name, null, BODY_DATABASE_VERSION); 7967525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler mContext = context; 797c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 798c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 799c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 800c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onCreate(SQLiteDatabase db) { 801560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.d(TAG, "Creating EmailProviderBody database"); 802c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createBodyTable(db); 803c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 804c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 805c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 8067525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) { 8077525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler if (oldVersion < 5) { 8087525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler upgradeBodyToVersion5(db); 8097525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 8107525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler if (oldVersion < 6) { 8117525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler upgradeBodyFromVersion5ToVersion6(db); 8127525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 8137525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler if (oldVersion < 8) { 8147525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler upgradeBodyFromVersion6ToVersion8(db); 8157525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 8167525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler if (oldVersion < 101) { 8177525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler upgradeBodyFromVersion100ToVersion101(mContext, db); 8187525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler } 819c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 820c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 821c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 822c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onOpen(SQLiteDatabase db) { 823c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 824c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 825c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 826c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Counts the number of messages in each mailbox, and updates the message count column. */ 827c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @VisibleForTesting 828c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void recalculateMessageCount(SQLiteDatabase db) { 829c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 830c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "= (select count(*) from " + Message.TABLE_NAME + 8313dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + MessageColumns.MAILBOX_KEY + " = " + 8323dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler Mailbox.TABLE_NAME + "." + MailboxColumns._ID + ")"); 833c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 834c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 835c5afb16430a145f20d7c887e45f47b38687054daMarc Blank protected static class DatabaseHelper extends SQLiteOpenHelper { 8367525feb244db87eadf3a95baf3918438b0fbbb75Tony Mantler final Context mContext; 837c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 838c5afb16430a145f20d7c887e45f47b38687054daMarc Blank DatabaseHelper(Context context, String name) { 839c5afb16430a145f20d7c887e45f47b38687054daMarc Blank super(context, name, null, DATABASE_VERSION); 840c5afb16430a145f20d7c887e45f47b38687054daMarc Blank mContext = context; 841c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 842c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 843c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 844c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onCreate(SQLiteDatabase db) { 845560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.d(TAG, "Creating EmailProvider database"); 846c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Create all tables here; each class has its own method 84713ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon createMessageTable(mContext, db); 848c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAttachmentTable(db); 849c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createMailboxTable(db); 850c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createHostAuthTable(db); 851c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAccountTable(db); 852ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageMoveTable(db); 853ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageStateChangeTable(db); 854c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createPolicyTable(db); 855c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createQuickResponseTable(db); 8560b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon createCredentialsTable(db); 857c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 858c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 85954347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank @Override 860c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { 861c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion == 101 && newVersion == 100) { 862560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.d(TAG, "Downgrade from v101 to v100"); 863c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } else { 864c5afb16430a145f20d7c887e45f47b38687054daMarc Blank super.onDowngrade(db, oldVersion, newVersion); 865c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 866c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 867c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 868c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 869c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @SuppressWarnings("deprecation") 870c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 871c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // For versions prior to 5, delete all data 872c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Versions >= 5 require that data be preserved! 873c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion < 5) { 874c5afb16430a145f20d7c887e45f47b38687054daMarc Blank android.accounts.Account[] accounts = AccountManager.get(mContext) 87513ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon .getAccountsByType(LEGACY_SCHEME_EAS); 876c5afb16430a145f20d7c887e45f47b38687054daMarc Blank for (android.accounts.Account account: accounts) { 877c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AccountManager.get(mContext).removeAccount(account, null, null); 878c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 87913ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon resetMessageTable(mContext, db, oldVersion, newVersion); 880c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetAttachmentTable(db, oldVersion, newVersion); 881c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetMailboxTable(db, oldVersion, newVersion); 882c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetHostAuthTable(db, oldVersion, newVersion); 883c5afb16430a145f20d7c887e45f47b38687054daMarc Blank resetAccountTable(db, oldVersion, newVersion); 884c5afb16430a145f20d7c887e45f47b38687054daMarc Blank return; 885c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 886c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (oldVersion == 5) { 887c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Message Tables: Add SyncColumns.SERVER_TIMESTAMP 888c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 889c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 890c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 891c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 892c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 893c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 894c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 895c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 896c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 897560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v5 to v6", e); 898c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 899c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 900e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler // TODO: Change all these to strict inequalities 9015ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 6) { 902c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Use the newer mailbox_delete trigger 903c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop trigger mailbox_delete;"); 904c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(TRIGGER_MAILBOX_DELETE); 905c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9065ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 7) { 907c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // add the security (provisioning) column 908c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 909c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Account.TABLE_NAME 910c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AccountColumns.SECURITY_FLAGS + " integer" + ";"); 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 7 to 8 " + e); 914c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 915c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9165ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 8) { 917c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // accounts: add security sync key & user signature columns 918c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 919c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Account.TABLE_NAME 920c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AccountColumns.SECURITY_SYNC_KEY + " text" + ";"); 921c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Account.TABLE_NAME 922c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AccountColumns.SIGNATURE + " text" + ";"); 923c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 924c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 925560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 8 to 9 " + e); 926c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 927c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9285ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 9) { 929c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Message: add meeting info column into Message tables 930c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 931c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 932c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 933c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 934c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 935c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 936c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 937c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 938c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 939560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 9 to 10 " + e); 940c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 941c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9425ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 10) { 943c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Attachment: add content and flags columns 944c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 945c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 946c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AttachmentColumns.CONTENT + " text" + ";"); 947c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 948c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AttachmentColumns.FLAGS + " integer" + ";"); 949c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 950c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 951560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 10 to 11 " + e); 952c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 953c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9545ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 11) { 955c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Attachment: add content_bytes 956c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 957c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 958c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + AttachmentColumns.CONTENT_BYTES + " blob" + ";"); 959c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 960c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 961560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 11 to 12 " + e); 962c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 963c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9645ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 12) { 965c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 966c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 967c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.MESSAGE_COUNT 968c5afb16430a145f20d7c887e45f47b38687054daMarc Blank +" integer not null default 0" + ";"); 969c5afb16430a145f20d7c887e45f47b38687054daMarc Blank recalculateMessageCount(db); 970c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 971c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 972560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 12 to 13 " + e); 973c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 974c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9755ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 13) { 976c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 977c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 9783dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + MessageColumns.SNIPPET 979c5afb16430a145f20d7c887e45f47b38687054daMarc Blank +" text" + ";"); 980c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 981c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 982560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 13 to 14 " + e); 983c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 984c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9855ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 14) { 986c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 987c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 9883dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + MessageColumns.SNIPPET +" text" + ";"); 989c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 9903dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + MessageColumns.SNIPPET +" text" + ";"); 991c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 992c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 993560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 14 to 15 " + e); 994c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 995c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 9965ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 15) { 997c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 998c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 9993dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + AttachmentColumns.ACCOUNT_KEY +" integer" + ";"); 1000c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Update all existing attachments to add the accountKey data 1001c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Attachment.TABLE_NAME + " set " + 10023dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler AttachmentColumns.ACCOUNT_KEY + "= (SELECT " + Message.TABLE_NAME + 10033dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler "." + MessageColumns.ACCOUNT_KEY + " from " + Message.TABLE_NAME + 10043dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + Message.TABLE_NAME + "." + MessageColumns._ID + " = " + 10053dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler Attachment.TABLE_NAME + "." + AttachmentColumns.MESSAGE_KEY + ")"); 1006c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1007c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1008560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 15 to 16 " + e); 1009c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1010c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10115ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 16) { 1012c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1013c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1014c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.PARENT_KEY + " integer;"); 1015c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1016c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1017560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 16 to 17 " + e); 1018c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1019c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10205ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 17) { 1021c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion17ToVersion18(db); 1022c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10235ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 18) { 1024c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1025c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Account.TABLE_NAME 10263dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + AccountColumns.POLICY_KEY + " integer;"); 1027c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("drop trigger account_delete;"); 1028c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL(TRIGGER_ACCOUNT_DELETE); 1029c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createPolicyTable(db); 1030c5afb16430a145f20d7c887e45f47b38687054daMarc Blank convertPolicyFlagsToPolicyTable(db); 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 18 to 19 " + e); 1034c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1035c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10365ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 19) { 1037c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1038c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1039c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + 1040c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " integer;"); 1041c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1042c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.DONT_ALLOW_CAMERA + " integer;"); 1043c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1044c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer;"); 1045c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1046c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.DONT_ALLOW_HTML + " integer;"); 1047c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1048c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer;"); 1049c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1050c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + 1051c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " integer;"); 1052c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1053c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + 1054c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " integer;"); 1055c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1056c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer;"); 1057c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1058c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer;"); 1059c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1060c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + PolicyColumns.PASSWORD_RECOVERY_ENABLED + 1061c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " integer;"); 1062c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1063c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1064560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 19 to 20 " + e); 1065c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1066c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10675ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 21) { 1068c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion21ToVersion22(db, mContext); 1069c5afb16430a145f20d7c887e45f47b38687054daMarc Blank oldVersion = 22; 1070c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10715ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 22) { 1072c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion22ToVersion23(db); 1073c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10745ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 23) { 1075c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion23ToVersion24(db); 1076c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10775ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 24) { 1078c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion24ToVersion25(db); 1079c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10805ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 25) { 1081c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion25ToVersion26(db); 1082c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10835ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 26) { 1084c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1085c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 10863dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + MessageColumns.PROTOCOL_SEARCH_INFO + " text;"); 1087c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 10883dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + MessageColumns.PROTOCOL_SEARCH_INFO +" text" + ";"); 1089c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 10903dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + MessageColumns.PROTOCOL_SEARCH_INFO +" text" + ";"); 1091c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1092c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1093560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 26 to 27 " + e); 1094c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1095c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 10965ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 28) { 1097c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1098c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1099c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Policy.PROTOCOL_POLICIES_ENFORCED + " text;"); 1100c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Policy.TABLE_NAME 1101c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Policy.PROTOCOL_POLICIES_UNSUPPORTED + " text;"); 1102c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1103c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1104560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 28 to 29 " + e); 1105c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1106c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 11075ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 29) { 1108c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeFromVersion29ToVersion30(db); 1109c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 11105ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 30) { 1111c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1112c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1113c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.UI_SYNC_STATUS + " integer;"); 1114c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1115c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.UI_LAST_SYNC_RESULT + " integer;"); 1116c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1117c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1118560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 30 to 31 " + e); 1119c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1120c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 11215ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 31) { 1122c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1123c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1124c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " integer;"); 1125c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1126c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " integer;"); 1127c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + 1128c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " IS NULL"); 1129c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + 1130c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " IS NULL"); 1131c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1132c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1133560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 31 to 32 " + e); 1134c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1135c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 11365ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 32) { 1137c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1138c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 11393dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + AttachmentColumns.UI_STATE + " integer;"); 1140c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 11413dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + AttachmentColumns.UI_DESTINATION + " integer;"); 1142c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Attachment.TABLE_NAME 11433dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + AttachmentColumns.UI_DOWNLOADED_SIZE + " integer;"); 1144c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // If we have a contentUri then the attachment is saved 1145c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // uiDestination of 0 = "cache", so we don't have to set this 11463dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler db.execSQL("update " + Attachment.TABLE_NAME + " set " + 11473dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler AttachmentColumns.UI_STATE + "=" + UIProvider.AttachmentState.SAVED + 11483dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler " where " + AttachmentColumns.CONTENT_URI + " is not null;"); 1149c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1150c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1151560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 32 to 33 " + e); 1152c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1153c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 11545ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 33) { 1155c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1156c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1157c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MailboxColumns.TOTAL_COUNT + " integer;"); 1158c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1159c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1160560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 33 to 34 " + e); 1161c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1162c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 11635ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 34) { 1164c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1165c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1166c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.LAST_TOUCHED_TIME + " = " + 1167c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.DRAFTS_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1168c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " = " + Mailbox.TYPE_DRAFTS); 1169c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1170c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.LAST_TOUCHED_TIME + " = " + 1171c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.SENT_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1172c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " = " + Mailbox.TYPE_SENT); 1173c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1174c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1175560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 34 to 35 " + e); 1176c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1177c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 11785ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 36) { 1179c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1180c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Set "supports settings" for EAS mailboxes 1181c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1182c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.FLAGS + "=" + MailboxColumns.FLAGS + "|" + 1183c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.FLAG_SUPPORTS_SETTINGS + " where (" + 1184c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.FLAGS + "&" + Mailbox.FLAG_HOLDS_MAIL + ")!=0 and " + 1185c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.ACCOUNT_KEY + " IN (SELECT " + Account.TABLE_NAME + 11863dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler "." + AccountColumns._ID + " from " + Account.TABLE_NAME + "," + 1187c5afb16430a145f20d7c887e45f47b38687054daMarc Blank HostAuth.TABLE_NAME + " where " + Account.TABLE_NAME + "." + 1188c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AccountColumns.HOST_AUTH_KEY_RECV + "=" + HostAuth.TABLE_NAME + "." + 11893dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler HostAuthColumns._ID + " and " + HostAuthColumns.PROTOCOL + "='" + 119054347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank LEGACY_SCHEME_EAS + "')"); 1191c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1192c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1193560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 35 to 36 " + e); 1194c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1195c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 11965ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 37) { 1197c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1198c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 1199c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1200c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1201c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1202560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 37 to 38 " + e); 1203c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1204c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 12055ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 38) { 1206c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1207c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1208c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1209c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1210c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1211c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1212c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1213560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 38 to 39 " + e); 1214c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1215c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 12165ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 39) { 1217c5afb16430a145f20d7c887e45f47b38687054daMarc Blank upgradeToEmail2(db); 1218c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 12195ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 102) { 1220c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1221c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1222c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add " + MailboxColumns.HIERARCHICAL_NAME + " text"); 1223c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1224c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1225560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v10x to v103", e); 1226c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1227c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 12285ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 103) { 1229c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank try { 1230c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank db.execSQL("alter table " + Message.TABLE_NAME 1231c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank + " add " + MessageColumns.SYNC_DATA + " text"); 1232c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } catch (SQLException e) { 1233c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1234560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v103 to v104", e); 1235c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } 1236c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } 12375ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 104) { 1238c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank try { 1239c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1240c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank + " add " + MessageColumns.SYNC_DATA + " text"); 1241c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1242c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank + " add " + MessageColumns.SYNC_DATA + " text"); 1243c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } catch (SQLException e) { 1244c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1245560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v104 to v105", e); 1246c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } 1247c6089bc01f2ae49fb11904a4b4f222811358254fMarc Blank } 12485ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 105) { 12497d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank try { 12507d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank db.execSQL("alter table " + HostAuth.TABLE_NAME 12517d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank + " add " + HostAuthColumns.SERVER_CERT + " blob"); 12527d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank } catch (SQLException e) { 12537d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1254560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v105 to v106", e); 12557d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank } 12567d5e2a7c08966ffd4a9e8c78f504cc4fd5be4216Marc Blank } 12575ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 106) { 1258b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy try { 1259b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy db.execSQL("alter table " + Message.TABLE_NAME 1260b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + " add " + MessageColumns.FLAG_SEEN + " integer"); 1261b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1262b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + " add " + MessageColumns.FLAG_SEEN + " integer"); 1263b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1264b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy + " add " + MessageColumns.FLAG_SEEN + " integer"); 1265b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy } catch (SQLException e) { 1266b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy // Shouldn't be needed unless we're debugging and interrupt the process 1267560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v106 to v107", e); 1268b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy } 1269b34608228f0b55e401415b67b8150ca9e00cee7dScott Kennedy } 12705ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 107) { 12719a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook try { 12729a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook db.execSQL("alter table " + Attachment.TABLE_NAME 12733dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + AttachmentColumns.CACHED_FILE +" text" + ";"); 12749a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook } catch (SQLException e) { 12759a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook // Shouldn't be needed unless we're debugging and interrupt the process 1276560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v107 to v108", e); 12779a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook } 12789a95253846ccc7a94dd7d4c618ec2d808e2a4000Paul Westbrook } 12795ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 108) { 128032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // Migrate the accounts with the correct account type 128132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook migrateLegacyAccounts(db, mContext); 128232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 12835ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 109) { 128421b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu // Fix any mailboxes that have ping or push_hold states. 128521b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 128621b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu + "=" + Mailbox.CHECK_INTERVAL_PUSH + " where " 128721b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu + MailboxColumns.SYNC_INTERVAL + "<" + Mailbox.CHECK_INTERVAL_PUSH); 128821b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu 128921b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu // Fix invalid syncLookback values. 129021b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu db.execSQL("update " + Account.TABLE_NAME + " set " + AccountColumns.SYNC_LOOKBACK 1291b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + "=" + SyncWindow.SYNC_WINDOW_1_WEEK + " where " 1292b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + AccountColumns.SYNC_LOOKBACK + " is null or " 1293b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + AccountColumns.SYNC_LOOKBACK + "<" + SyncWindow.SYNC_WINDOW_1_DAY + " or " 1294b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + AccountColumns.SYNC_LOOKBACK + ">" + SyncWindow.SYNC_WINDOW_ALL); 1295b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu 129621b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_LOOKBACK 1297b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + "=" + SyncWindow.SYNC_WINDOW_ACCOUNT + " where " 1298b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + MailboxColumns.SYNC_LOOKBACK + " is null or " 1299b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + MailboxColumns.SYNC_LOOKBACK + "<" + SyncWindow.SYNC_WINDOW_1_DAY + " or " 1300b3cb475fd29f827002b9743822b2a99ff85d1b38Yu Ping Hu + MailboxColumns.SYNC_LOOKBACK + ">" + SyncWindow.SYNC_WINDOW_ALL); 1301aa0ca16a27e4a56a029e5cdebf96de5723bd84b6Yu Ping Hu } 13025ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 110) { 1303a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // Delete account mailboxes. 1304aa0ca16a27e4a56a029e5cdebf96de5723bd84b6Yu Ping Hu db.execSQL("delete from " + Mailbox.TABLE_NAME + " where " + MailboxColumns.TYPE 1305aa0ca16a27e4a56a029e5cdebf96de5723bd84b6Yu Ping Hu + "=" +Mailbox.TYPE_EAS_ACCOUNT_MAILBOX); 1306a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu } 13075ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion <= 111) { 1308a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // Mailbox sync interval now indicates whether this mailbox syncs with the rest 1309a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // of the account. Anyone who was syncing at all, plus outboxes, are set to 1, 1310a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu // everyone else is 0. 1311a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 1312a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu + "=case when " + MailboxColumns.SYNC_INTERVAL + "=" 1313a54ee609cdb79ad3abdda2d7180a29617fa38610Yu Ping Hu + Mailbox.CHECK_INTERVAL_NEVER + " then 0 else 1 end"); 131421b2522d7f6b2bce8fc3382e6e532d4b4df1b140Yu Ping Hu } 13155ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu if (oldVersion >= 110 && oldVersion <= 112) { 13165ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // v110 had dropped these triggers, but starting with v113 we restored them 13175ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // (and altered the 109 -> 110 upgrade code to stop dropping them). 13185ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // We therefore only add them back for the versions in between. We also need to 13195ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu // compute the correct value at this point as well. 13205ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu recalculateMessageCount(db); 13215ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu createMessageCountTriggers(db); 13225ff368b84573833497764c9d1661cc97717b7094Yu Ping Hu } 1323c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon 1324c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon if (oldVersion <= 113) { 1325c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon try { 1326c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon db.execSQL("alter table " + Mailbox.TABLE_NAME 1327c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon + " add column " + MailboxColumns.LAST_FULL_SYNC_TIME +" integer" + ";"); 132852135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon final ContentValues cv = new ContentValues(1); 1329c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon cv.put(MailboxColumns.LAST_FULL_SYNC_TIME, 0); 1330c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon db.update(Mailbox.TABLE_NAME, cv, null, null); 133152135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon } catch (final SQLException e) { 133252135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon // Shouldn't be needed unless we're debugging and interrupt the process 133352135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v113 to v114", e); 133452135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon } 133552135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon } 133652135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon 133752135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon if (oldVersion <= 114) { 133852135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon try { 133952135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon db.execSQL("alter table " + Account.TABLE_NAME 134052135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon + " add column " + AccountColumns.PING_DURATION +" integer" + ";"); 134152135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon final ContentValues cv = new ContentValues(1); 134252135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon cv.put(AccountColumns.PING_DURATION, 0); 134352135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon db.update(Account.TABLE_NAME, cv, null, null); 134452135c6e8750f19084695cdda78ffe34719c4b6cMartin Hibdon } catch (final SQLException e) { 1345c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon // Shouldn't be needed unless we're debugging and interrupt the process 1346c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v113 to v114", e); 1347c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon } 1348c75f5880ab70d9f4938727587696b864bb4ea02aMartin Hibdon } 1349ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu 1350ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu if (oldVersion <= 115) { 1351ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageMoveTable(db); 1352ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu createMessageStateChangeTable(db); 1353ca79aba675d5282b6ba365184f3727b7b24a568fYu Ping Hu } 1354e3a4a1b25dd20dfd69793ca953f3299bcf6f0501Yu Ping Hu 1355ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon /** 1356ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon * Originally, at 116, we added a trigger to delete duplicate messages. 1357ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon * But we needed to change that trigger for version 120, so when we get 1358ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon * there, we'll drop the trigger if it exists and create a new version. 1359ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon */ 1360e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler 1361e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler /** 1362e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler * This statement changes the syncInterval column to 0 for all IMAP mailboxes. 1363e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler * It does this by matching mailboxes against all account IDs whose receive auth is 1364e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler * either R.string.protocol_legacy_imap, R.string.protocol_imap or "imap" 1365e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler */ 1366e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler if (oldVersion <= 117) { 1367e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 1368e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler + "=0 where " + MailboxColumns.ACCOUNT_KEY + " in (select " 13693dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + Account.TABLE_NAME + "." + AccountColumns._ID + " from " 1370e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler + Account.TABLE_NAME + " join " + HostAuth.TABLE_NAME + " where " 13713dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + HostAuth.TABLE_NAME + "." + HostAuthColumns._ID + "=" 13723dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV 13733dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " and (" + HostAuth.TABLE_NAME + "." 1374e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler + HostAuthColumns.PROTOCOL + "='" 1375e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler + mContext.getString(R.string.protocol_legacy_imap) + "' or " 1376e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='" 1377e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler + mContext.getString(R.string.protocol_imap) + "' or " 1378e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap'));"); 1379e54d6957dbbd14d35a1858cb02959fd8f9bb0d37Tony Mantler } 1380cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon 1381cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon /** 1382cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * This statement changes the sync interval column to 0 for all DRAFTS type mailboxes, 1383cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * and deletes any messages that are: 1384cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * * synced from the server, and 1385cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * * in an exchange account draft folder 1386cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * 1387cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * This is primary for Exchange (b/11158759) but we don't sync draft folders for any 1388cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * other account type anyway. 1389cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * This will only affect people who used intermediate builds between email1 and email2, 1390cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon * it should be a no-op for most users. 1391cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon */ 1392cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon if (oldVersion <= 118) { 1393cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 1394cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon + "=0 where " + MailboxColumns.TYPE + "=" + Mailbox.TYPE_DRAFTS); 1395cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon 1396cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon db.execSQL("delete from " + Message.TABLE_NAME + " where " 1397cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon + "(" + SyncColumns.SERVER_ID + " not null and " 1398cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon + SyncColumns.SERVER_ID + "!='') and " 1399cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon + MessageColumns.MAILBOX_KEY + " in (select " 14003dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + MailboxColumns._ID + " from " + Mailbox.TABLE_NAME + " where " 1401cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon + MailboxColumns.TYPE + "=" + Mailbox.TYPE_DRAFTS + ")"); 1402cb30243fe1fa3a2f7540495cbeea470e77b539beMartin Hibdon } 1403ac40b8648504ba5e4e2f8ad44ec2e5063a054cb3Martin Hibdon 140413ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon // We originally dropped and recreated the deleteDuplicateMessagesTrigger here at 140513ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon // version 120. We needed to update it again at version 123, so there's no reason 140613ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon // to do it twice. 1407c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon 1408c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // Add the mainMailboxKey column, and get rid of any messages in the search_results 1409c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // folder. 1410c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon if (oldVersion <= 120) { 1411c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon db.execSQL("alter table " + Message.TABLE_NAME 1412c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon + " add " + MessageColumns.MAIN_MAILBOX_KEY + " integer"); 1413c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon 1414c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // Delete all TYPE_SEARCH mailboxes. These will be for stale queries anyway, and 1415c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // the messages in them will not have the mainMailboxKey column correctly populated. 1416c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // We have a trigger (See TRIGGER_MAILBOX_DELETE) that will delete any messages 1417c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon // in the deleted mailboxes. 1418c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon db.execSQL("delete from " + Mailbox.TABLE_NAME + " where " 1419c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon + Mailbox.TYPE + "=" + Mailbox.TYPE_SEARCH); 1420c86fbb5bcbff72102f87747d94948f0749402229Martin Hibdon } 14217b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu 14227b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu if (oldVersion <= 121) { 14237b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu // The previous update omitted making these changes to the Message_Updates and 14247b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu // Message_Deletes tables. The app will actually crash in between these versions! 14257b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 14267b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu + " add " + MessageColumns.MAIN_MAILBOX_KEY + " integer"); 14277b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu db.execSQL("alter table " + Message.DELETED_TABLE_NAME 14287b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu + " add " + MessageColumns.MAIN_MAILBOX_KEY + " integer"); 14297b7d7d5cb57c9f0e589b686511eabf3c73f96f00Yu Ping Hu } 143013ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon 143113ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon if (oldVersion <= 122) { 143213ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon if (oldVersion >= 117) { 143313ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon /** 143413ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * This trigger was originally created at version 117, but we needed to change 143513ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * it for version 122. So if our oldVersion is 117 or more, we know we have that 143613ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon * trigger and must drop it before re creating it. 143713ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon */ 143813ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon dropDeleteDuplicateMessagesTrigger(db); 143913ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon } 144013ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon createDeleteDuplicateMessagesTrigger(mContext, db); 144113ff5b86ac9314bbce09ae9e4ec381b6273a497bMartin Hibdon } 14420b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon 14430b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon if (oldVersion <= 123) { 1444eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee try { 1445eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee db.execSQL("alter table " + Account.TABLE_NAME 1446eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee + " add column " + AccountColumns.MAX_ATTACHMENT_SIZE +" integer" + ";"); 1447eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee final ContentValues cv = new ContentValues(1); 1448eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee cv.put(AccountColumns.MAX_ATTACHMENT_SIZE, 0); 1449eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee db.update(Account.TABLE_NAME, cv, null, null); 1450eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee } catch (final SQLException e) { 1451eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee // Shouldn't be needed unless we're debugging and interrupt the process 1452eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v123 to v124", e); 1453eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee } 1454eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee } 1455eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee 1456eba80c114e87346aeb64bbff91ef11b0ba566fd6Anthony Lee if (oldVersion <= 124) { 14570b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon createCredentialsTable(db); 14580b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon // Add the credentialKey column, and set it to -1 for all pre-existing hostAuths. 14590b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon db.execSQL("alter table " + HostAuth.TABLE_NAME 14600b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + " add " + HostAuthColumns.CREDENTIAL_KEY + " integer"); 14612bf9590ddd463d39cc4dc73e26aaf5dd590415b2Jerry Xie db.execSQL("update " + HostAuth.TABLE_NAME + " set " 14620b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon + HostAuthColumns.CREDENTIAL_KEY + "=-1"); 14630b25179dab10dc7dfb91210cabfe637f3067d777Martin Hibdon } 14645057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux 14655057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux if (oldVersion <= 125) { 14665057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux upgradeFromVersion125ToVersion126(db); 14675057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux } 14686f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee 14696f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee if (oldVersion <= 126) { 14706f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee upgradeFromVersion126ToVersion127(mContext, db); 14716f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee } 1472c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1473c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1474c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @Override 1475c5afb16430a145f20d7c887e45f47b38687054daMarc Blank public void onOpen(SQLiteDatabase db) { 14760c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook try { 14770c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook // Cleanup some nasty records 14780c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook db.execSQL("DELETE FROM " + Account.TABLE_NAME 14790c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook + " WHERE " + AccountColumns.DISPLAY_NAME + " ISNULL;"); 14800c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook db.execSQL("DELETE FROM " + HostAuth.TABLE_NAME 14810c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook + " WHERE " + HostAuthColumns.PROTOCOL + " ISNULL;"); 14820c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook } catch (SQLException e) { 14830c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook // Shouldn't be needed unless we're debugging and interrupt the process 14840c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook LogUtils.e(TAG, e, "Exception cleaning EmailProvider.db"); 14850c06a7cf995c0d3e5dcccae97349891a46c3320fPaul Westbrook } 1486c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1487c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1488c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1489c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @VisibleForTesting 1490c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @SuppressWarnings("deprecation") 1491c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void convertPolicyFlagsToPolicyTable(SQLiteDatabase db) { 1492c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Cursor c = db.query(Account.TABLE_NAME, 14933dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler new String[] {BaseColumns._ID /*0*/, AccountColumns.SECURITY_FLAGS /*1*/}, 1494c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AccountColumns.SECURITY_FLAGS + ">0", null, null, null, null); 149589c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi try { 149689c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi ContentValues cv = new ContentValues(); 149789c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi String[] args = new String[1]; 149889c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi while (c.moveToNext()) { 149989c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi long securityFlags = c.getLong(1 /*SECURITY_FLAGS*/); 150089c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi Policy policy = LegacyPolicySet.flagsToPolicy(securityFlags); 150189c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi long policyId = db.insert(Policy.TABLE_NAME, null, policy.toContentValues()); 150289c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi cv.put(AccountColumns.POLICY_KEY, policyId); 150389c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi cv.putNull(AccountColumns.SECURITY_FLAGS); 15043dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler args[0] = Long.toString(c.getLong(0 /*_ID*/)); 15053dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler db.update(Account.TABLE_NAME, cv, BaseColumns._ID + "=?", args); 150689c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi } 150789c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi } finally { 150889c74e89e52e3d3223ec9c2f108a9b0735d99d33Yoohyun.choi c.close(); 1509c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1510c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1511c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1512c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v17 to v18 */ 1513c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @VisibleForTesting 1514c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void upgradeFromVersion17ToVersion18(SQLiteDatabase db) { 1515c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Copy the displayName column to the serverId column. In v18 of the database, 1516c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // we use the serverId for IMAP/POP3 mailboxes instead of overloading the 1517c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // display name. 1518c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // 1519c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // For posterity; this is the command we're executing: 1520c5afb16430a145f20d7c887e45f47b38687054daMarc Blank //sqlite> UPDATE mailbox SET serverid=displayname WHERE mailbox._id in ( 1521c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> SELECT mailbox._id FROM mailbox,account,hostauth WHERE 1522c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> (mailbox.parentkey isnull OR mailbox.parentkey=0) AND 1523c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> mailbox.accountkey=account._id AND 1524c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> account.hostauthkeyrecv=hostauth._id AND 1525c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // ...> (hostauth.protocol='imap' OR hostauth.protocol='pop3')); 1526c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1527c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL( 1528c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "UPDATE " + Mailbox.TABLE_NAME + " SET " 1529c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + MailboxColumns.SERVER_ID + "=" + MailboxColumns.DISPLAY_NAME 1530c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " WHERE " 15313dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + Mailbox.TABLE_NAME + "." + MailboxColumns._ID + " IN ( SELECT " 15323dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + Mailbox.TABLE_NAME + "." + MailboxColumns._ID + " FROM " 1533c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "," + Account.TABLE_NAME + "," 1534c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuth.TABLE_NAME + " WHERE " 1535c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + "(" 1536c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + " isnull OR " 1537c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + "=0 " 1538c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + ") AND " 1539c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Mailbox.TABLE_NAME + "." + MailboxColumns.ACCOUNT_KEY + "=" 15403dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + Account.TABLE_NAME + "." + AccountColumns._ID + " AND " 1541c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV + "=" 15423dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + HostAuth.TABLE_NAME + "." + HostAuthColumns._ID + " AND ( " 1543c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap' OR " 1544c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='pop3' ) )"); 1545c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1546c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1547560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 17 to 18 " + e); 1548c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1549c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentCache.invalidateAllCaches(); 1550c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1551c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1552c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** 1553c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * Upgrade the database from v21 to v22 1554c5afb16430a145f20d7c887e45f47b38687054daMarc Blank * This entails creating AccountManager accounts for all pop3 and imap accounts 1555c5afb16430a145f20d7c887e45f47b38687054daMarc Blank */ 1556c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1557c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] V21_ACCOUNT_PROJECTION = 1558c5afb16430a145f20d7c887e45f47b38687054daMarc Blank new String[] {AccountColumns.HOST_AUTH_KEY_RECV, AccountColumns.EMAIL_ADDRESS}; 1559c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V21_ACCOUNT_RECV = 0; 1560c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V21_ACCOUNT_EMAIL = 1; 1561c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1562c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] V21_HOSTAUTH_PROJECTION = 1563c5afb16430a145f20d7c887e45f47b38687054daMarc Blank new String[] {HostAuthColumns.PROTOCOL, HostAuthColumns.PASSWORD}; 1564c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V21_HOSTAUTH_PROTOCOL = 0; 1565c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V21_HOSTAUTH_PASSWORD = 1; 1566c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 156732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook private static void createAccountManagerAccount(Context context, String login, String type, 1568c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String password) { 156932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final AccountManager accountManager = AccountManager.get(context); 157032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 157132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook if (isAccountPresent(accountManager, login, type)) { 157232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // The account already exists,just return 157332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook return; 157432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 157532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook LogUtils.v("Email", "Creating account %s %s", login, type); 157632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final android.accounts.Account amAccount = new android.accounts.Account(login, type); 1577c5afb16430a145f20d7c887e45f47b38687054daMarc Blank accountManager.addAccountExplicitly(amAccount, password, null); 1578c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 1579c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setSyncAutomatically(amAccount, EmailContent.AUTHORITY, true); 1580c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setIsSyncable(amAccount, ContactsContract.AUTHORITY, 0); 1581c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setIsSyncable(amAccount, CalendarContract.AUTHORITY, 0); 1582c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1583c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 158432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook private static boolean isAccountPresent(AccountManager accountManager, String name, 158532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook String type) { 158632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final android.accounts.Account[] amAccounts = accountManager.getAccountsByType(type); 158732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook if (amAccounts != null) { 158832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook for (android.accounts.Account account : amAccounts) { 158932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook if (TextUtils.equals(account.name, name) && TextUtils.equals(account.type, type)) { 159032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook return true; 159132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 159232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 159332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 159432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook return false; 159532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 159632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 1597c5afb16430a145f20d7c887e45f47b38687054daMarc Blank @VisibleForTesting 1598c5afb16430a145f20d7c887e45f47b38687054daMarc Blank static void upgradeFromVersion21ToVersion22(SQLiteDatabase db, Context accountManagerContext) { 159932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook migrateLegacyAccounts(db, accountManagerContext); 160032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook } 160132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 160232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook private static void migrateLegacyAccounts(SQLiteDatabase db, Context accountManagerContext) { 160332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final Map<String, String> legacyToNewTypeMap = new ImmutableMap.Builder<String, String>() 160432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook .put(LEGACY_SCHEME_POP3, 160532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook accountManagerContext.getString(R.string.account_manager_type_pop3)) 160632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook .put(LEGACY_SCHEME_IMAP, 160732881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook accountManagerContext.getString(R.string.account_manager_type_legacy_imap)) 160832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook .put(LEGACY_SCHEME_EAS, 160932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook accountManagerContext.getString(R.string.account_manager_type_exchange)) 161032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook .build(); 1611c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1612c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Loop through accounts, looking for pop/imap accounts 161332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final Cursor accountCursor = db.query(Account.TABLE_NAME, V21_ACCOUNT_PROJECTION, null, 1614c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null, null); 1615c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 161632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final String[] hostAuthArgs = new String[1]; 1617c5afb16430a145f20d7c887e45f47b38687054daMarc Blank while (accountCursor.moveToNext()) { 1618c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthArgs[0] = accountCursor.getString(V21_ACCOUNT_RECV); 1619c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Get the "receive" HostAuth for this account 162032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 16213dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler V21_HOSTAUTH_PROJECTION, HostAuthColumns._ID + "=?", hostAuthArgs, 1622c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null); 1623c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1624c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (hostAuthCursor.moveToFirst()) { 162532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook final String protocol = hostAuthCursor.getString(V21_HOSTAUTH_PROTOCOL); 1626c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // If this is a pop3 or imap account, create the account manager account 162754347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank if (LEGACY_SCHEME_IMAP.equals(protocol) || 162832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook LEGACY_SCHEME_POP3.equals(protocol)) { 162932881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // If this is a pop3 or imap account, create the account manager 163032881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // account 163151c653646d14d841fbe527aee9fab7a1886338f8Martin Hibdon if (DebugUtils.DEBUG) { 1632560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.d(TAG, "Create AccountManager account for " + protocol 1633560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy + "account: " 1634560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy + accountCursor.getString(V21_ACCOUNT_EMAIL)); 1635c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1636c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createAccountManagerAccount(accountManagerContext, 1637c5afb16430a145f20d7c887e45f47b38687054daMarc Blank accountCursor.getString(V21_ACCOUNT_EMAIL), 163832881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook legacyToNewTypeMap.get(protocol), 1639c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthCursor.getString(V21_HOSTAUTH_PASSWORD)); 164054347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank } else if (LEGACY_SCHEME_EAS.equals(protocol)) { 164132881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // If an EAS account, make Email sync automatically (equivalent of 164232881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook // checking the "Sync Email" box in settings 164332881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook 164432881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook android.accounts.Account amAccount = new android.accounts.Account( 164532881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook accountCursor.getString(V21_ACCOUNT_EMAIL), 164632881e8dd375f16e6059452166503f7136f14ae5Paul Westbrook legacyToNewTypeMap.get(protocol)); 1647c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 1648c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentResolver.setSyncAutomatically(amAccount, 1649c5afb16430a145f20d7c887e45f47b38687054daMarc Blank EmailContent.AUTHORITY, true); 1650c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1651c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1652c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1653c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthCursor.close(); 1654c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1655c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1656c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1657c5afb16430a145f20d7c887e45f47b38687054daMarc Blank accountCursor.close(); 1658c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1659c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1660c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1661560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception while migrating accounts " + e); 1662c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1663c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1664c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1665c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v22 to v23 */ 1666c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion22ToVersion23(SQLiteDatabase db) { 1667c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1668c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + Mailbox.TABLE_NAME 1669c5afb16430a145f20d7c887e45f47b38687054daMarc Blank + " add column " + Mailbox.LAST_TOUCHED_TIME + " integer default 0;"); 1670c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1671c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1672560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 22 to 23 " + e); 1673c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1674c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1675c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1676c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Adds in a column for information about a client certificate to use. */ 1677c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion23ToVersion24(SQLiteDatabase db) { 1678c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1679c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("alter table " + HostAuth.TABLE_NAME 16803dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler + " add column " + HostAuthColumns.CLIENT_CERT_ALIAS + " text;"); 1681c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1682c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1683560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 23 to 24 " + e); 1684c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1685c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1686c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1687c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v24 to v25 by creating table for quick responses */ 1688c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion24ToVersion25(SQLiteDatabase db) { 1689c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1690c5afb16430a145f20d7c887e45f47b38687054daMarc Blank createQuickResponseTable(db); 1691c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1692c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1693560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 24 to 25 " + e); 1694c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1695c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1696c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1697c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] V25_ACCOUNT_PROJECTION = 16983dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler new String[] {AccountColumns._ID, AccountColumns.FLAGS, AccountColumns.HOST_AUTH_KEY_RECV}; 1699c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V25_ACCOUNT_ID = 0; 1700c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V25_ACCOUNT_FLAGS = 1; 1701c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V25_ACCOUNT_RECV = 2; 1702c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1703c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final String[] V25_HOSTAUTH_PROJECTION = new String[] {HostAuthColumns.PROTOCOL}; 1704c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static final int V25_HOSTAUTH_PROTOCOL = 0; 1705c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1706c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v25 to v26 by adding FLAG_SUPPORTS_SEARCH to IMAP accounts */ 1707c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion25ToVersion26(SQLiteDatabase db) { 1708c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1709c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Loop through accounts, looking for imap accounts 1710c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Cursor accountCursor = db.query(Account.TABLE_NAME, V25_ACCOUNT_PROJECTION, null, 1711c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null, null); 1712c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentValues cv = new ContentValues(); 1713c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1714c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String[] hostAuthArgs = new String[1]; 1715c5afb16430a145f20d7c887e45f47b38687054daMarc Blank while (accountCursor.moveToNext()) { 1716c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthArgs[0] = accountCursor.getString(V25_ACCOUNT_RECV); 1717c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Get the "receive" HostAuth for this account 1718c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 17193dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler V25_HOSTAUTH_PROJECTION, HostAuthColumns._ID + "=?", hostAuthArgs, 1720c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null); 1721c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1722c5afb16430a145f20d7c887e45f47b38687054daMarc Blank if (hostAuthCursor.moveToFirst()) { 1723c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String protocol = hostAuthCursor.getString(V25_HOSTAUTH_PROTOCOL); 1724c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // If this is an imap account, add the search flag 172554347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank if (LEGACY_SCHEME_IMAP.equals(protocol)) { 1726c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String id = accountCursor.getString(V25_ACCOUNT_ID); 1727c5afb16430a145f20d7c887e45f47b38687054daMarc Blank int flags = accountCursor.getInt(V25_ACCOUNT_FLAGS); 1728c5afb16430a145f20d7c887e45f47b38687054daMarc Blank cv.put(AccountColumns.FLAGS, flags | Account.FLAGS_SUPPORTS_SEARCH); 17293dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler db.update(Account.TABLE_NAME, cv, AccountColumns._ID + "=?", 1730c5afb16430a145f20d7c887e45f47b38687054daMarc Blank new String[] {id}); 1731c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1732c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1733c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1734c5afb16430a145f20d7c887e45f47b38687054daMarc Blank hostAuthCursor.close(); 1735c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1736c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1737c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1738c5afb16430a145f20d7c887e45f47b38687054daMarc Blank accountCursor.close(); 1739c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1740c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1741c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1742560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 25 to 26 " + e); 1743c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1744c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1745c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1746c5afb16430a145f20d7c887e45f47b38687054daMarc Blank /** Upgrades the database from v29 to v30 by updating all address fields in Message */ 17473dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler private static final int[] ADDRESS_COLUMN_INDICES = { 17483dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler Message.CONTENT_BCC_LIST_COLUMN, 17493dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler Message.CONTENT_CC_LIST_COLUMN, 17503dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler Message.CONTENT_FROM_LIST_COLUMN, 17513dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler Message.CONTENT_REPLY_TO_COLUMN, 17523dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler Message.CONTENT_TO_LIST_COLUMN 1753c5afb16430a145f20d7c887e45f47b38687054daMarc Blank }; 17543dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler private static final String[] ADDRESS_COLUMN_NAMES = { 17553dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler MessageColumns.BCC_LIST, 17563dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler MessageColumns.CC_LIST, 17573dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler MessageColumns.FROM_LIST, 17583dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler MessageColumns.REPLY_TO_LIST, 17593dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler MessageColumns.TO_LIST 1760c5afb16430a145f20d7c887e45f47b38687054daMarc Blank }; 1761c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1762c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeFromVersion29ToVersion30(SQLiteDatabase db) { 1763c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1764c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Loop through all messages, updating address columns to new format (CSV, RFC822) 1765c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Cursor messageCursor = db.query(Message.TABLE_NAME, Message.CONTENT_PROJECTION, null, 1766c5afb16430a145f20d7c887e45f47b38687054daMarc Blank null, null, null, null); 1767c5afb16430a145f20d7c887e45f47b38687054daMarc Blank ContentValues cv = new ContentValues(); 1768c5afb16430a145f20d7c887e45f47b38687054daMarc Blank String[] whereArgs = new String[1]; 1769c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1770c5afb16430a145f20d7c887e45f47b38687054daMarc Blank while (messageCursor.moveToNext()) { 1771c5afb16430a145f20d7c887e45f47b38687054daMarc Blank for (int i = 0; i < ADDRESS_COLUMN_INDICES.length; i++) { 1772c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Address[] addrs = 17731fa303478c61e0d703011996e358037eef523176James Lemieux Address.fromHeader(messageCursor.getString(ADDRESS_COLUMN_INDICES[i])); 17741fa303478c61e0d703011996e358037eef523176James Lemieux cv.put(ADDRESS_COLUMN_NAMES[i], Address.toHeader(addrs)); 1775c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1776c5afb16430a145f20d7c887e45f47b38687054daMarc Blank whereArgs[0] = messageCursor.getString(Message.CONTENT_ID_COLUMN); 1777c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.update(Message.TABLE_NAME, cv, WHERE_ID, whereArgs); 1778c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1779c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } finally { 1780c5afb16430a145f20d7c887e45f47b38687054daMarc Blank messageCursor.close(); 1781c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1782c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1783c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Shouldn't be needed unless we're debugging and interrupt the process 1784560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 29 to 30 " + e); 1785c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1786c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1787c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 17885057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux private static void upgradeFromVersion125ToVersion126(SQLiteDatabase db) { 17895057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux try { 17905057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux // Loop through all messages, updating address columns to their decoded form 17915057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux Cursor messageCursor = db.query(Message.TABLE_NAME, Message.CONTENT_PROJECTION, null, 17925057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux null, null, null, null); 17935057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux ContentValues cv = new ContentValues(); 17945057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux String[] whereArgs = new String[1]; 17955057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux try { 17965057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux while (messageCursor.moveToNext()) { 17975057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux for (int i = 0; i < ADDRESS_COLUMN_INDICES.length; i++) { 17985057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux Address[] addrs = 17995057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux Address.fromHeader(messageCursor.getString(ADDRESS_COLUMN_INDICES[i])); 18005057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux cv.put(ADDRESS_COLUMN_NAMES[i], Address.toString(addrs)); 18015057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux } 18025057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux whereArgs[0] = messageCursor.getString(Message.CONTENT_ID_COLUMN); 18035057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux db.update(Message.TABLE_NAME, cv, WHERE_ID, whereArgs); 18045057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux } 18055057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux } finally { 18065057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux messageCursor.close(); 18075057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux } 18085057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux } catch (SQLException e) { 18095057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux // Shouldn't be needed unless we're debugging and interrupt the process 18106f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 125 to 126 " + e); 18116f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee } 18126f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee } 18136f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee 18146f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee /** 18156f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee * Update all accounts that are EAS v12.0 or greater with SmartForward and search flags 18166f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee */ 18176f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee private static void upgradeFromVersion126ToVersion127(final Context context, 18186f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee final SQLiteDatabase db) { 18196f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee try { 18206f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee // These are the flags that we want to add to the Account table for the 18216f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee // appropriate rows. 18226f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee final long newFlags = Account.FLAGS_SUPPORTS_GLOBAL_SEARCH + 18236f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee Account.FLAGS_SUPPORTS_SEARCH + Account.FLAGS_SUPPORTS_SMART_FORWARD; 18246f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee 18256f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee // For posterity; this is the command we're executing: 18266f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee // UPDATE Account SET flags=flags|[new flags] WHERE _id IN (SELECT t1._id FROM Account 18276f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee // t1 INNER JOIN HostAuth t2 ON t1.hostAuthKeyRecv=t2._id WHERE t2.protocol='gEas' AND 18286f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee // CAST(t1.protocolVersion AS REAL)>=12.0) 18296f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee db.execSQL( 18306f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee "UPDATE " + Account.TABLE_NAME + " SET " + AccountColumns.FLAGS + "=" + 18316f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee AccountColumns.FLAGS + "|" + Long.toString(newFlags) + " WHERE " + 18323dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler AccountColumns._ID + " IN (SELECT t1." + AccountColumns._ID + " FROM " + 18336f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee Account.TABLE_NAME + " t1 INNER JOIN " + HostAuth.TABLE_NAME + 18346f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee " t2 ON t1." + AccountColumns.HOST_AUTH_KEY_RECV + "=t2._id WHERE t2." + 18356f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee HostAuthColumns.PROTOCOL + "='" + 18366f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee context.getString(R.string.protocol_eas) + "' AND CAST(t1." + 18376f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee AccountColumns.PROTOCOL_VERSION + " AS REAL)>=12.0)"); 18386f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee } catch (SQLException e) { 18396f7c1c824d7c5e31be8fd9878e812f2c0484bae7Anthony Lee LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 126 to 127 " + e); 18405057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux } 18415057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux } 18425057d6dffb994b4d1d02c05e06f679de8ef05ea5James Lemieux 1843c5afb16430a145f20d7c887e45f47b38687054daMarc Blank private static void upgradeToEmail2(SQLiteDatabase db) { 1844c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // Perform cleanup operations from Email1 to Email2; Email1 will have added new 1845c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // data that won't conform to what's expected in Email2 1846c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1847c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // From 31->32 upgrade 1848c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1849c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + 1850c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " IS NULL"); 1851c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + 1852c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " IS NULL"); 1853c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1854560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 31 to 32/100 " + e); 1855c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1856c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1857c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // From 32->33 upgrade 1858c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 18593dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler db.execSQL("update " + Attachment.TABLE_NAME + " set " + AttachmentColumns.UI_STATE + 1860c5afb16430a145f20d7c887e45f47b38687054daMarc Blank "=" + UIProvider.AttachmentState.SAVED + " where " + 1861c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AttachmentColumns.CONTENT_URI + " is not null;"); 1862c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1863560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 32 to 33/100 " + e); 1864c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1865c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1866c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // From 34->35 upgrade 1867c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1868c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1869c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.LAST_TOUCHED_TIME + " = " + 1870c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.DRAFTS_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1871c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " = " + Mailbox.TYPE_DRAFTS); 1872c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1873c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.LAST_TOUCHED_TIME + " = " + 1874c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.SENT_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1875c5afb16430a145f20d7c887e45f47b38687054daMarc Blank " = " + Mailbox.TYPE_SENT); 1876c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1877560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 34 to 35/100 " + e); 1878c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1879c5afb16430a145f20d7c887e45f47b38687054daMarc Blank 1880c5afb16430a145f20d7c887e45f47b38687054daMarc Blank // From 35/36->37 1881c5afb16430a145f20d7c887e45f47b38687054daMarc Blank try { 1882c5afb16430a145f20d7c887e45f47b38687054daMarc Blank db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1883c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.FLAGS + "=" + MailboxColumns.FLAGS + "|" + 1884c5afb16430a145f20d7c887e45f47b38687054daMarc Blank Mailbox.FLAG_SUPPORTS_SETTINGS + " where (" + 1885c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.FLAGS + "&" + Mailbox.FLAG_HOLDS_MAIL + ")!=0 and " + 1886c5afb16430a145f20d7c887e45f47b38687054daMarc Blank MailboxColumns.ACCOUNT_KEY + " IN (SELECT " + Account.TABLE_NAME + 18873dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler "." + AccountColumns._ID + " from " + Account.TABLE_NAME + "," + 1888c5afb16430a145f20d7c887e45f47b38687054daMarc Blank HostAuth.TABLE_NAME + " where " + Account.TABLE_NAME + "." + 1889c5afb16430a145f20d7c887e45f47b38687054daMarc Blank AccountColumns.HOST_AUTH_KEY_RECV + "=" + HostAuth.TABLE_NAME + "." + 18903dd85723a1af5537e23e4b05bdc361cce9cd42beTony Mantler HostAuthColumns._ID + " and " + HostAuthColumns.PROTOCOL + "='" + 189154347010fbbdd3ae1dea5b0e282514c640e16a5fMarc Blank LEGACY_SCHEME_EAS + "')"); 1892c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } catch (SQLException e) { 1893560bfadc3151f7a06f3b06e9a6c92cfa534c63ecScott Kennedy LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 35/36 to 37/100 " + e); 1894c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1895c5afb16430a145f20d7c887e45f47b38687054daMarc Blank } 1896c5afb16430a145f20d7c887e45f47b38687054daMarc Blank} 1897