1 /* 2 * Copyright (C) 2012 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package com.android.email.provider; 18 19 import android.accounts.AccountManager; 20 import android.content.ContentResolver; 21 import android.content.ContentValues; 22 import android.content.Context; 23 import android.database.Cursor; 24 import android.database.SQLException; 25 import android.database.sqlite.SQLiteDatabase; 26 import android.database.sqlite.SQLiteDoneException; 27 import android.database.sqlite.SQLiteOpenHelper; 28 import android.database.sqlite.SQLiteStatement; 29 import android.provider.BaseColumns; 30 import android.provider.CalendarContract; 31 import android.provider.ContactsContract; 32 import android.text.TextUtils; 33 34 import com.android.email.DebugUtils; 35 import com.android.email.R; 36 import com.android.emailcommon.mail.Address; 37 import com.android.emailcommon.provider.Account; 38 import com.android.emailcommon.provider.Credential; 39 import com.android.emailcommon.provider.EmailContent; 40 import com.android.emailcommon.provider.EmailContent.AccountColumns; 41 import com.android.emailcommon.provider.EmailContent.Attachment; 42 import com.android.emailcommon.provider.EmailContent.AttachmentColumns; 43 import com.android.emailcommon.provider.EmailContent.Body; 44 import com.android.emailcommon.provider.EmailContent.BodyColumns; 45 import com.android.emailcommon.provider.EmailContent.HostAuthColumns; 46 import com.android.emailcommon.provider.EmailContent.MailboxColumns; 47 import com.android.emailcommon.provider.EmailContent.Message; 48 import com.android.emailcommon.provider.EmailContent.MessageColumns; 49 import com.android.emailcommon.provider.EmailContent.PolicyColumns; 50 import com.android.emailcommon.provider.EmailContent.QuickResponseColumns; 51 import com.android.emailcommon.provider.EmailContent.SyncColumns; 52 import com.android.emailcommon.provider.HostAuth; 53 import com.android.emailcommon.provider.Mailbox; 54 import com.android.emailcommon.provider.MessageChangeLogTable; 55 import com.android.emailcommon.provider.MessageMove; 56 import com.android.emailcommon.provider.MessageStateChange; 57 import com.android.emailcommon.provider.Policy; 58 import com.android.emailcommon.provider.QuickResponse; 59 import com.android.emailcommon.service.LegacyPolicySet; 60 import com.android.emailcommon.service.SyncWindow; 61 import com.android.mail.providers.UIProvider; 62 import com.android.mail.utils.LogUtils; 63 import com.google.common.annotations.VisibleForTesting; 64 import com.google.common.collect.ImmutableMap; 65 66 import java.io.File; 67 import java.io.FileWriter; 68 import java.io.IOException; 69 import java.util.Map; 70 71 public final class DBHelper { 72 private static final String TAG = "EmailProvider"; 73 74 private static final String LEGACY_SCHEME_IMAP = "imap"; 75 private static final String LEGACY_SCHEME_POP3 = "pop3"; 76 private static final String LEGACY_SCHEME_EAS = "eas"; 77 78 79 private static final String WHERE_ID = BaseColumns._ID + "=?"; 80 81 private static final String TRIGGER_MAILBOX_DELETE = 82 "create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME + 83 " begin" + 84 " delete from " + Message.TABLE_NAME + 85 " where " + MessageColumns.MAILBOX_KEY + "=old." + BaseColumns._ID + 86 "; delete from " + Message.UPDATED_TABLE_NAME + 87 " where " + MessageColumns.MAILBOX_KEY + "=old." + BaseColumns._ID + 88 "; delete from " + Message.DELETED_TABLE_NAME + 89 " where " + MessageColumns.MAILBOX_KEY + "=old." + BaseColumns._ID + 90 "; end"; 91 92 private static final String TRIGGER_ACCOUNT_DELETE = 93 "create trigger account_delete before delete on " + Account.TABLE_NAME + 94 " begin delete from " + Mailbox.TABLE_NAME + 95 " where " + MailboxColumns.ACCOUNT_KEY + "=old." + BaseColumns._ID + 96 "; delete from " + HostAuth.TABLE_NAME + 97 " where " + BaseColumns._ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV + 98 "; delete from " + HostAuth.TABLE_NAME + 99 " where " + BaseColumns._ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND + 100 "; delete from " + Policy.TABLE_NAME + 101 " where " + BaseColumns._ID + "=old." + AccountColumns.POLICY_KEY + 102 "; end"; 103 104 private static final String TRIGGER_HOST_AUTH_DELETE = 105 "create trigger host_auth_delete after delete on " + HostAuth.TABLE_NAME + 106 " begin delete from " + Credential.TABLE_NAME + 107 " where " + Credential._ID + "=old." + HostAuthColumns.CREDENTIAL_KEY + 108 " and (select count(*) from " + HostAuth.TABLE_NAME + " where " + 109 HostAuthColumns.CREDENTIAL_KEY + "=old." + HostAuthColumns.CREDENTIAL_KEY + ")=0" + 110 "; end"; 111 112 113 // Any changes to the database format *must* include update-in-place code. 114 // Original version: 3 115 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 116 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 117 // Version 6: Adding Message.mServerTimeStamp column 118 // Version 7: Replace the mailbox_delete trigger with a version that removes orphaned messages 119 // from the Message_Deletes and Message_Updates tables 120 // Version 8: Add security flags column to accounts table 121 // Version 9: Add security sync key and signature to accounts table 122 // Version 10: Add meeting info to message table 123 // Version 11: Add content and flags to attachment table 124 // Version 12: Add content_bytes to attachment table. content is deprecated. 125 // Version 13: Add messageCount to Mailbox table. 126 // Version 14: Add snippet to Message table 127 // Version 15: Fix upgrade problem in version 14. 128 // Version 16: Add accountKey to Attachment table 129 // Version 17: Add parentKey to Mailbox table 130 // Version 18: Copy Mailbox.displayName to Mailbox.serverId for all IMAP & POP3 mailboxes. 131 // Column Mailbox.serverId is used for the server-side pathname of a mailbox. 132 // Version 19: Add Policy table; add policyKey to Account table and trigger to delete an 133 // Account's policy when the Account is deleted 134 // Version 20: Add new policies to Policy table 135 // Version 21: Add lastSeenMessageKey column to Mailbox table 136 // Version 22: Upgrade path for IMAP/POP accounts to integrate with AccountManager 137 // Version 23: Add column to mailbox table for time of last access 138 // Version 24: Add column to hostauth table for client cert alias 139 // Version 25: Added QuickResponse table 140 // Version 26: Update IMAP accounts to add FLAG_SUPPORTS_SEARCH flag 141 // Version 27: Add protocolSearchInfo to Message table 142 // Version 28: Add notifiedMessageId and notifiedMessageCount to Account 143 // Version 29: Add protocolPoliciesEnforced and protocolPoliciesUnsupported to Policy 144 // Version 30: Use CSV of RFC822 addresses instead of "packed" values 145 // Version 31: Add columns to mailbox for ui status/last result 146 // Version 32: Add columns to mailbox for last notified message key/count; insure not null 147 // for "notified" columns 148 // Version 33: Add columns to attachment for ui provider columns 149 // Version 34: Add total count to mailbox 150 // Version 35: Set up defaults for lastTouchedCount for drafts and sent 151 // Version 36: mblank intentionally left this space 152 // Version 37: Add flag for settings support in folders 153 // Version 38&39: Add threadTopic to message (for future support) 154 // Version 39 is last Email1 version 155 // Version 100 is first Email2 version 156 // Version 101 SHOULD NOT BE USED 157 // Version 102&103: Add hierarchicalName to Mailbox 158 // Version 104&105: add syncData to Message 159 // Version 106: Add certificate to HostAuth 160 // Version 107: Add a SEEN column to the message table 161 // Version 108: Add a cachedFile column to the attachments table 162 // Version 109: Migrate the account so they have the correct account manager types 163 // Version 110: Stop updating message_count, don't use auto lookback, and don't use 164 // ping/push_hold sync states. Note that message_count updating is restored in 113. 165 // Version 111: Delete Exchange account mailboxes. 166 // Version 112: Convert Mailbox syncInterval to a boolean (whether or not this mailbox 167 // syncs along with the account). 168 // Version 113: Restore message_count to being useful. 169 // Version 114: Add lastFullSyncTime column 170 // Version 115: Add pingDuration column 171 // Version 116: Add MessageMove & MessageStateChange tables. 172 // Version 117: Add trigger to delete duplicate messages on sync. 173 // Version 118: Set syncInterval to 0 for all IMAP mailboxes 174 // Version 119: Disable syncing of DRAFTS type folders. 175 // Version 120: Changed duplicateMessage deletion trigger to ignore search mailboxes. 176 // Version 121: Add mainMailboxKey, which will be set for messages that are in the fake 177 // "search_results" folder to reflect the mailbox that the server considers 178 // the message to be in. Also, wipe out any stale search_result folders. 179 // Version 122: Need to update Message_Updates and Message_Deletes to match previous. 180 // Version 123: Changed the duplicateMesage deletion trigger to ignore accounts that aren't 181 // exchange accounts. 182 // Version 124: Added MAX_ATTACHMENT_SIZE to the account table 183 // Version 125: Add credentials table for OAuth. 184 // Version 126: Decode address lists for To, From, Cc, Bcc and Reply-To columns in Message. 185 // Version 127: Force mFlags to contain the correct flags for EAS accounts given a protocol 186 // version above 12.0 187 public static final int DATABASE_VERSION = 127; 188 189 // Any changes to the database format *must* include update-in-place code. 190 // Original version: 2 191 // Version 3: Add "sourceKey" column 192 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 193 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 194 // Version 6: Adding Body.mIntroText column 195 // Version 7/8: Adding quoted text start pos 196 // Version 8 is last Email1 version 197 // Version 100 is the first Email2 version 198 // Version 101: Move body contents to external files 199 public static final int BODY_DATABASE_VERSION = 101; 200 201 /* 202 * Internal helper method for index creation. 203 * Example: 204 * "create index message_" + MessageColumns.FLAG_READ 205 * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");" 206 */ 207 /* package */ createIndex(String tableName, String columnName)208 static String createIndex(String tableName, String columnName) { 209 return "create index " + tableName.toLowerCase() + '_' + columnName 210 + " on " + tableName + " (" + columnName + ");"; 211 } 212 createMessageCountTriggers(final SQLiteDatabase db)213 static void createMessageCountTriggers(final SQLiteDatabase db) { 214 // Insert a message. 215 db.execSQL("create trigger message_count_message_insert after insert on " + 216 Message.TABLE_NAME + 217 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 218 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 219 " where " + BaseColumns._ID + "=NEW." + MessageColumns.MAILBOX_KEY + 220 "; end"); 221 222 // Delete a message. 223 db.execSQL("create trigger message_count_message_delete after delete on " + 224 Message.TABLE_NAME + 225 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 226 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 227 " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 228 "; end"); 229 230 // Change a message's mailbox. 231 db.execSQL("create trigger message_count_message_move after update of " + 232 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 233 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 234 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 235 " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 236 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 237 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 238 " where " + BaseColumns._ID + "=NEW." + MessageColumns.MAILBOX_KEY + 239 "; end"); 240 } 241 createCredentialsTable(SQLiteDatabase db)242 static void createCredentialsTable(SQLiteDatabase db) { 243 String s = " (" + Credential._ID + " integer primary key autoincrement, " 244 + Credential.PROVIDER_COLUMN + " text," 245 + Credential.ACCESS_TOKEN_COLUMN + " text," 246 + Credential.REFRESH_TOKEN_COLUMN + " text," 247 + Credential.EXPIRATION_COLUMN + " integer" 248 + ");"; 249 db.execSQL("create table " + Credential.TABLE_NAME + s); 250 db.execSQL(TRIGGER_HOST_AUTH_DELETE); 251 } 252 dropDeleteDuplicateMessagesTrigger(final SQLiteDatabase db)253 static void dropDeleteDuplicateMessagesTrigger(final SQLiteDatabase db) { 254 db.execSQL("drop trigger message_delete_duplicates_on_insert"); 255 } 256 257 /** 258 * Add a trigger to delete duplicate server side messages before insertion. 259 * This should delete any messages older messages that have the same serverId and account as 260 * the new message, if: 261 * Neither message is in a SEARCH type mailbox, and 262 * The new message's mailbox's account is an exchange account. 263 * 264 * Here is the plain text of this sql: 265 * create trigger message_delete_duplicates_on_insert before insert on 266 * Message for each row when new.syncServerId is not null and 267 * (select type from Mailbox where _id=new.mailboxKey) != 8 and 268 * (select HostAuth.protocol from HostAuth, Account where 269 * new.accountKey=account._id and account.hostAuthKeyRecv=hostAuth._id) = 'gEas' 270 * begin delete from Message where new.syncServerId=syncSeverId and 271 * new.accountKey=accountKey and 272 * (select Mailbox.type from Mailbox where _id=mailboxKey) != 8; end 273 */ createDeleteDuplicateMessagesTrigger(final Context context, final SQLiteDatabase db)274 static void createDeleteDuplicateMessagesTrigger(final Context context, 275 final SQLiteDatabase db) { 276 db.execSQL("create trigger message_delete_duplicates_on_insert before insert on " 277 + Message.TABLE_NAME + " for each row when new." + SyncColumns.SERVER_ID 278 + " is not null and " 279 + "(select " + MailboxColumns.TYPE + " from " + Mailbox.TABLE_NAME 280 + " where " + MailboxColumns._ID + "=new." 281 + MessageColumns.MAILBOX_KEY + ")!=" + Mailbox.TYPE_SEARCH 282 + " and (select " 283 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + " from " 284 + HostAuth.TABLE_NAME + "," + Account.TABLE_NAME 285 + " where new." + MessageColumns.ACCOUNT_KEY 286 + "=" + Account.TABLE_NAME + "." + AccountColumns._ID 287 + " and " + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV 288 + "=" + HostAuth.TABLE_NAME + "." + HostAuthColumns._ID 289 + ")='" + context.getString(R.string.protocol_eas) + "'" 290 + " begin delete from " + Message.TABLE_NAME + " where new." 291 + SyncColumns.SERVER_ID + "=" + SyncColumns.SERVER_ID + " and new." 292 + MessageColumns.ACCOUNT_KEY + "=" + MessageColumns.ACCOUNT_KEY 293 + " and (select " + Mailbox.TABLE_NAME + "." + MailboxColumns.TYPE + " from " 294 + Mailbox.TABLE_NAME + " where " + MailboxColumns._ID + "=" 295 + MessageColumns.MAILBOX_KEY + ")!=" + Mailbox.TYPE_SEARCH +"; end"); 296 } 297 createMessageTable(Context context, SQLiteDatabase db)298 static void createMessageTable(Context context, SQLiteDatabase db) { 299 String messageColumns = MessageColumns.DISPLAY_NAME + " text, " 300 + MessageColumns.TIMESTAMP + " integer, " 301 + MessageColumns.SUBJECT + " text, " 302 + MessageColumns.FLAG_READ + " integer, " 303 + MessageColumns.FLAG_LOADED + " integer, " 304 + MessageColumns.FLAG_FAVORITE + " integer, " 305 + MessageColumns.FLAG_ATTACHMENT + " integer, " 306 + MessageColumns.FLAGS + " integer, " 307 + MessageColumns.DRAFT_INFO + " integer, " 308 + MessageColumns.MESSAGE_ID + " text, " 309 + MessageColumns.MAILBOX_KEY + " integer, " 310 + MessageColumns.ACCOUNT_KEY + " integer, " 311 + MessageColumns.FROM_LIST + " text, " 312 + MessageColumns.TO_LIST + " text, " 313 + MessageColumns.CC_LIST + " text, " 314 + MessageColumns.BCC_LIST + " text, " 315 + MessageColumns.REPLY_TO_LIST + " text, " 316 + MessageColumns.MEETING_INFO + " text, " 317 + MessageColumns.SNIPPET + " text, " 318 + MessageColumns.PROTOCOL_SEARCH_INFO + " text, " 319 + MessageColumns.THREAD_TOPIC + " text, " 320 + MessageColumns.SYNC_DATA + " text, " 321 + MessageColumns.FLAG_SEEN + " integer, " 322 + MessageColumns.MAIN_MAILBOX_KEY + " integer" 323 + ");"; 324 325 // This String and the following String MUST have the same columns, except for the type 326 // of those columns! 327 String createString = " (" + BaseColumns._ID + " integer primary key autoincrement, " 328 + SyncColumns.SERVER_ID + " text, " 329 + SyncColumns.SERVER_TIMESTAMP + " integer, " 330 + messageColumns; 331 332 // For the updated and deleted tables, the id is assigned, but we do want to keep track 333 // of the ORDER of updates using an autoincrement primary key. We use the DATA column 334 // at this point; it has no other function 335 String altCreateString = " (" + BaseColumns._ID + " integer unique, " 336 + SyncColumns.SERVER_ID + " text, " 337 + SyncColumns.SERVER_TIMESTAMP + " integer, " 338 + messageColumns; 339 340 // The three tables have the same schema 341 db.execSQL("create table " + Message.TABLE_NAME + createString); 342 db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString); 343 db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString); 344 345 String indexColumns[] = { 346 MessageColumns.TIMESTAMP, 347 MessageColumns.FLAG_READ, 348 MessageColumns.FLAG_LOADED, 349 MessageColumns.MAILBOX_KEY, 350 SyncColumns.SERVER_ID 351 }; 352 353 for (String columnName : indexColumns) { 354 db.execSQL(createIndex(Message.TABLE_NAME, columnName)); 355 } 356 357 // Deleting a Message deletes all associated Attachments 358 // Deleting the associated Body cannot be done in a trigger, because the Body is stored 359 // in a separate database, and trigger cannot operate on attached databases. 360 db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME + 361 " begin delete from " + Attachment.TABLE_NAME + 362 " where " + AttachmentColumns.MESSAGE_KEY + "=old." + BaseColumns._ID + 363 "; end"); 364 365 // Add triggers to keep unread count accurate per mailbox 366 367 // NOTE: SQLite's before triggers are not safe when recursive triggers are involved. 368 // Use caution when changing them. 369 370 // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox 371 db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME + 372 " when NEW." + MessageColumns.FLAG_READ + "=0" + 373 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 374 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 375 " where " + BaseColumns._ID + "=NEW." + MessageColumns.MAILBOX_KEY + 376 "; end"); 377 378 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 379 db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME + 380 " when OLD." + MessageColumns.FLAG_READ + "=0" + 381 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 382 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 383 " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 384 "; end"); 385 386 // Change a message's mailbox 387 db.execSQL("create trigger unread_message_move before update of " + 388 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 389 " when OLD." + MessageColumns.FLAG_READ + "=0" + 390 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 391 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 392 " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 393 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 394 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 395 " where " + BaseColumns._ID + "=NEW." + MessageColumns.MAILBOX_KEY + 396 "; end"); 397 398 // Change a message's read state 399 db.execSQL("create trigger unread_message_read before update of " + 400 MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME + 401 " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ + 402 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 403 '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ + 404 " when 0 then -1 else 1 end" + 405 " where " + BaseColumns._ID + "=OLD." + MessageColumns.MAILBOX_KEY + 406 "; end"); 407 408 // Add triggers to maintain message_count. 409 createMessageCountTriggers(db); 410 createDeleteDuplicateMessagesTrigger(context, db); 411 } 412 resetMessageTable(Context context, SQLiteDatabase db, int oldVersion, int newVersion)413 static void resetMessageTable(Context context, SQLiteDatabase db, 414 int oldVersion, int newVersion) { 415 try { 416 db.execSQL("drop table " + Message.TABLE_NAME); 417 db.execSQL("drop table " + Message.UPDATED_TABLE_NAME); 418 db.execSQL("drop table " + Message.DELETED_TABLE_NAME); 419 } catch (SQLException e) { 420 } 421 createMessageTable(context, db); 422 } 423 424 /** 425 * Common columns for all {@link MessageChangeLogTable} tables. 426 */ 427 private static String MESSAGE_CHANGE_LOG_COLUMNS = 428 MessageChangeLogTable.ID + " integer primary key autoincrement, " 429 + MessageChangeLogTable.MESSAGE_KEY + " integer, " 430 + MessageChangeLogTable.SERVER_ID + " text, " 431 + MessageChangeLogTable.ACCOUNT_KEY + " integer, " 432 + MessageChangeLogTable.STATUS + " integer, "; 433 434 /** 435 * Create indices common to all {@link MessageChangeLogTable} tables. 436 * @param db The {@link SQLiteDatabase}. 437 * @param tableName The name of this particular table. 438 */ createMessageChangeLogTableIndices(final SQLiteDatabase db, final String tableName)439 private static void createMessageChangeLogTableIndices(final SQLiteDatabase db, 440 final String tableName) { 441 db.execSQL(createIndex(tableName, MessageChangeLogTable.MESSAGE_KEY)); 442 db.execSQL(createIndex(tableName, MessageChangeLogTable.ACCOUNT_KEY)); 443 } 444 445 /** 446 * Create triggers common to all {@link MessageChangeLogTable} tables. 447 * @param db The {@link SQLiteDatabase}. 448 * @param tableName The name of this particular table. 449 */ createMessageChangeLogTableTriggers(final SQLiteDatabase db, final String tableName)450 private static void createMessageChangeLogTableTriggers(final SQLiteDatabase db, 451 final String tableName) { 452 // Trigger to delete from the change log when a message is deleted. 453 db.execSQL("create trigger " + tableName + "_delete_message before delete on " 454 + Message.TABLE_NAME + " for each row begin delete from " + tableName 455 + " where " + MessageChangeLogTable.MESSAGE_KEY + "=old." + MessageColumns._ID 456 + "; end"); 457 458 // Trigger to delete from the change log when an account is deleted. 459 db.execSQL("create trigger " + tableName + "_delete_account before delete on " 460 + Account.TABLE_NAME + " for each row begin delete from " + tableName 461 + " where " + MessageChangeLogTable.ACCOUNT_KEY + "=old." + AccountColumns._ID 462 + "; end"); 463 } 464 465 /** 466 * Create the MessageMove table. 467 * @param db The {@link SQLiteDatabase}. 468 */ createMessageMoveTable(final SQLiteDatabase db)469 private static void createMessageMoveTable(final SQLiteDatabase db) { 470 db.execSQL("create table " + MessageMove.TABLE_NAME + " (" 471 + MESSAGE_CHANGE_LOG_COLUMNS 472 + MessageMove.SRC_FOLDER_KEY + " integer, " 473 + MessageMove.DST_FOLDER_KEY + " integer, " 474 + MessageMove.SRC_FOLDER_SERVER_ID + " text, " 475 + MessageMove.DST_FOLDER_SERVER_ID + " text);"); 476 477 createMessageChangeLogTableIndices(db, MessageMove.TABLE_NAME); 478 createMessageChangeLogTableTriggers(db, MessageMove.TABLE_NAME); 479 } 480 481 /** 482 * Create the MessageStateChange table. 483 * @param db The {@link SQLiteDatabase}. 484 */ createMessageStateChangeTable(final SQLiteDatabase db)485 private static void createMessageStateChangeTable(final SQLiteDatabase db) { 486 db.execSQL("create table " + MessageStateChange.TABLE_NAME + " (" 487 + MESSAGE_CHANGE_LOG_COLUMNS 488 + MessageStateChange.OLD_FLAG_READ + " integer, " 489 + MessageStateChange.NEW_FLAG_READ + " integer, " 490 + MessageStateChange.OLD_FLAG_FAVORITE + " integer, " 491 + MessageStateChange.NEW_FLAG_FAVORITE + " integer);"); 492 493 createMessageChangeLogTableIndices(db, MessageStateChange.TABLE_NAME); 494 createMessageChangeLogTableTriggers(db, MessageStateChange.TABLE_NAME); 495 } 496 497 @SuppressWarnings("deprecation") createAccountTable(SQLiteDatabase db)498 static void createAccountTable(SQLiteDatabase db) { 499 String s = " (" + AccountColumns._ID + " integer primary key autoincrement, " 500 + AccountColumns.DISPLAY_NAME + " text, " 501 + AccountColumns.EMAIL_ADDRESS + " text, " 502 + AccountColumns.SYNC_KEY + " text, " 503 + AccountColumns.SYNC_LOOKBACK + " integer, " 504 + AccountColumns.SYNC_INTERVAL + " text, " 505 + AccountColumns.HOST_AUTH_KEY_RECV + " integer, " 506 + AccountColumns.HOST_AUTH_KEY_SEND + " integer, " 507 + AccountColumns.FLAGS + " integer, " 508 + AccountColumns.IS_DEFAULT + " integer, " 509 + AccountColumns.COMPATIBILITY_UUID + " text, " 510 + AccountColumns.SENDER_NAME + " text, " 511 + AccountColumns.RINGTONE_URI + " text, " 512 + AccountColumns.PROTOCOL_VERSION + " text, " 513 + AccountColumns.NEW_MESSAGE_COUNT + " integer, " 514 + AccountColumns.SECURITY_FLAGS + " integer, " 515 + AccountColumns.SECURITY_SYNC_KEY + " text, " 516 + AccountColumns.SIGNATURE + " text, " 517 + AccountColumns.POLICY_KEY + " integer, " 518 + AccountColumns.MAX_ATTACHMENT_SIZE + " integer, " 519 + AccountColumns.PING_DURATION + " integer" 520 + ");"; 521 db.execSQL("create table " + Account.TABLE_NAME + s); 522 // Deleting an account deletes associated Mailboxes and HostAuth's 523 db.execSQL(TRIGGER_ACCOUNT_DELETE); 524 } 525 resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion)526 static void resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) { 527 try { 528 db.execSQL("drop table " + Account.TABLE_NAME); 529 } catch (SQLException e) { 530 } 531 createAccountTable(db); 532 } 533 createPolicyTable(SQLiteDatabase db)534 static void createPolicyTable(SQLiteDatabase db) { 535 String s = " (" + PolicyColumns._ID + " integer primary key autoincrement, " 536 + PolicyColumns.PASSWORD_MODE + " integer, " 537 + PolicyColumns.PASSWORD_MIN_LENGTH + " integer, " 538 + PolicyColumns.PASSWORD_EXPIRATION_DAYS + " integer, " 539 + PolicyColumns.PASSWORD_HISTORY + " integer, " 540 + PolicyColumns.PASSWORD_COMPLEX_CHARS + " integer, " 541 + PolicyColumns.PASSWORD_MAX_FAILS + " integer, " 542 + PolicyColumns.MAX_SCREEN_LOCK_TIME + " integer, " 543 + PolicyColumns.REQUIRE_REMOTE_WIPE + " integer, " 544 + PolicyColumns.REQUIRE_ENCRYPTION + " integer, " 545 + PolicyColumns.REQUIRE_ENCRYPTION_EXTERNAL + " integer, " 546 + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + " integer, " 547 + PolicyColumns.DONT_ALLOW_CAMERA + " integer, " 548 + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer, " 549 + PolicyColumns.DONT_ALLOW_HTML + " integer, " 550 + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer, " 551 + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + " integer, " 552 + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + " integer, " 553 + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer, " 554 + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer, " 555 + PolicyColumns.PASSWORD_RECOVERY_ENABLED + " integer, " 556 + PolicyColumns.PROTOCOL_POLICIES_ENFORCED + " text, " 557 + PolicyColumns.PROTOCOL_POLICIES_UNSUPPORTED + " text" 558 + ");"; 559 db.execSQL("create table " + Policy.TABLE_NAME + s); 560 } 561 createHostAuthTable(SQLiteDatabase db)562 static void createHostAuthTable(SQLiteDatabase db) { 563 String s = " (" + HostAuthColumns._ID + " integer primary key autoincrement, " 564 + HostAuthColumns.PROTOCOL + " text, " 565 + HostAuthColumns.ADDRESS + " text, " 566 + HostAuthColumns.PORT + " integer, " 567 + HostAuthColumns.FLAGS + " integer, " 568 + HostAuthColumns.LOGIN + " text, " 569 + HostAuthColumns.PASSWORD + " text, " 570 + HostAuthColumns.DOMAIN + " text, " 571 + HostAuthColumns.ACCOUNT_KEY + " integer," 572 + HostAuthColumns.CLIENT_CERT_ALIAS + " text," 573 + HostAuthColumns.SERVER_CERT + " blob," 574 + HostAuthColumns.CREDENTIAL_KEY + " integer" 575 + ");"; 576 db.execSQL("create table " + HostAuth.TABLE_NAME + s); 577 } 578 resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion)579 static void resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) { 580 try { 581 db.execSQL("drop table " + HostAuth.TABLE_NAME); 582 } catch (SQLException e) { 583 } 584 createHostAuthTable(db); 585 } 586 587 @SuppressWarnings("deprecation") createMailboxTable(SQLiteDatabase db)588 static void createMailboxTable(SQLiteDatabase db) { 589 String s = " (" + MailboxColumns._ID + " integer primary key autoincrement, " 590 + MailboxColumns.DISPLAY_NAME + " text, " 591 + MailboxColumns.SERVER_ID + " text, " 592 + MailboxColumns.PARENT_SERVER_ID + " text, " 593 + MailboxColumns.PARENT_KEY + " integer, " 594 + MailboxColumns.ACCOUNT_KEY + " integer, " 595 + MailboxColumns.TYPE + " integer, " 596 + MailboxColumns.DELIMITER + " integer, " 597 + MailboxColumns.SYNC_KEY + " text, " 598 + MailboxColumns.SYNC_LOOKBACK + " integer, " 599 + MailboxColumns.SYNC_INTERVAL + " integer, " 600 + MailboxColumns.SYNC_TIME + " integer, " 601 + MailboxColumns.UNREAD_COUNT + " integer, " 602 + MailboxColumns.FLAG_VISIBLE + " integer, " 603 + MailboxColumns.FLAGS + " integer, " 604 + MailboxColumns.VISIBLE_LIMIT + " integer, " 605 + MailboxColumns.SYNC_STATUS + " text, " 606 + MailboxColumns.MESSAGE_COUNT + " integer not null default 0, " 607 + MailboxColumns.LAST_TOUCHED_TIME + " integer default 0, " 608 + MailboxColumns.UI_SYNC_STATUS + " integer default 0, " 609 + MailboxColumns.UI_LAST_SYNC_RESULT + " integer default 0, " 610 + MailboxColumns.LAST_NOTIFIED_MESSAGE_KEY + " integer not null default 0, " 611 + MailboxColumns.LAST_NOTIFIED_MESSAGE_COUNT + " integer not null default 0, " 612 + MailboxColumns.TOTAL_COUNT + " integer, " 613 + MailboxColumns.HIERARCHICAL_NAME + " text, " 614 + MailboxColumns.LAST_FULL_SYNC_TIME + " integer" 615 + ");"; 616 db.execSQL("create table " + Mailbox.TABLE_NAME + s); 617 db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID 618 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")"); 619 db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY 620 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")"); 621 // Deleting a Mailbox deletes associated Messages in all three tables 622 db.execSQL(TRIGGER_MAILBOX_DELETE); 623 } 624 resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion)625 static void resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) { 626 try { 627 db.execSQL("drop table " + Mailbox.TABLE_NAME); 628 } catch (SQLException e) { 629 } 630 createMailboxTable(db); 631 } 632 createAttachmentTable(SQLiteDatabase db)633 static void createAttachmentTable(SQLiteDatabase db) { 634 String s = " (" + AttachmentColumns._ID + " integer primary key autoincrement, " 635 + AttachmentColumns.FILENAME + " text, " 636 + AttachmentColumns.MIME_TYPE + " text, " 637 + AttachmentColumns.SIZE + " integer, " 638 + AttachmentColumns.CONTENT_ID + " text, " 639 + AttachmentColumns.CONTENT_URI + " text, " 640 + AttachmentColumns.MESSAGE_KEY + " integer, " 641 + AttachmentColumns.LOCATION + " text, " 642 + AttachmentColumns.ENCODING + " text, " 643 + AttachmentColumns.CONTENT + " text, " 644 + AttachmentColumns.FLAGS + " integer, " 645 + AttachmentColumns.CONTENT_BYTES + " blob, " 646 + AttachmentColumns.ACCOUNT_KEY + " integer, " 647 + AttachmentColumns.UI_STATE + " integer, " 648 + AttachmentColumns.UI_DESTINATION + " integer, " 649 + AttachmentColumns.UI_DOWNLOADED_SIZE + " integer, " 650 + AttachmentColumns.CACHED_FILE + " text" 651 + ");"; 652 db.execSQL("create table " + Attachment.TABLE_NAME + s); 653 db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY)); 654 } 655 resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion)656 static void resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) { 657 try { 658 db.execSQL("drop table " + Attachment.TABLE_NAME); 659 } catch (SQLException e) { 660 } 661 createAttachmentTable(db); 662 } 663 createQuickResponseTable(SQLiteDatabase db)664 static void createQuickResponseTable(SQLiteDatabase db) { 665 String s = " (" + QuickResponseColumns._ID + " integer primary key autoincrement, " 666 + QuickResponseColumns.TEXT + " text, " 667 + QuickResponseColumns.ACCOUNT_KEY + " integer" 668 + ");"; 669 db.execSQL("create table " + QuickResponse.TABLE_NAME + s); 670 } 671 672 @SuppressWarnings("deprecation") createBodyTable(SQLiteDatabase db)673 static void createBodyTable(SQLiteDatabase db) { 674 String s = " (" + BodyColumns._ID + " integer primary key autoincrement, " 675 + BodyColumns.MESSAGE_KEY + " integer, " 676 + BodyColumns.HTML_CONTENT + " text, " 677 + BodyColumns.TEXT_CONTENT + " text, " 678 + BodyColumns.HTML_REPLY + " text, " 679 + BodyColumns.TEXT_REPLY + " text, " 680 + BodyColumns.SOURCE_MESSAGE_KEY + " text, " 681 + BodyColumns.INTRO_TEXT + " text, " 682 + BodyColumns.QUOTED_TEXT_START_POS + " integer" 683 + ");"; 684 db.execSQL("create table " + Body.TABLE_NAME + s); 685 db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY)); 686 } 687 upgradeBodyToVersion5(final SQLiteDatabase db)688 private static void upgradeBodyToVersion5(final SQLiteDatabase db) { 689 try { 690 db.execSQL("drop table " + Body.TABLE_NAME); 691 createBodyTable(db); 692 } catch (final SQLException e) { 693 // Shouldn't be needed unless we're debugging and interrupt the process 694 LogUtils.w(TAG, e, "Exception upgrading EmailProviderBody.db from <v5"); 695 } 696 } 697 698 @SuppressWarnings("deprecation") upgradeBodyFromVersion5ToVersion6(final SQLiteDatabase db)699 private static void upgradeBodyFromVersion5ToVersion6(final SQLiteDatabase db) { 700 try { 701 db.execSQL("alter table " + Body.TABLE_NAME 702 + " add " + BodyColumns.INTRO_TEXT + " text"); 703 } catch (final SQLException e) { 704 // Shouldn't be needed unless we're debugging and interrupt the process 705 LogUtils.w(TAG, e, "Exception upgrading EmailProviderBody.db from v5 to v6"); 706 } 707 } 708 upgradeBodyFromVersion6ToVersion8(final SQLiteDatabase db)709 private static void upgradeBodyFromVersion6ToVersion8(final SQLiteDatabase db) { 710 try { 711 db.execSQL("alter table " + Body.TABLE_NAME 712 + " add " + BodyColumns.QUOTED_TEXT_START_POS + " integer"); 713 } catch (final SQLException e) { 714 // Shouldn't be needed unless we're debugging and interrupt the process 715 LogUtils.w(TAG, e, "Exception upgrading EmailProviderBody.db from v6 to v8"); 716 } 717 } 718 719 /** 720 * This upgrade migrates email bodies out of the database and into individual files. 721 */ upgradeBodyFromVersion100ToVersion101(final Context context, final SQLiteDatabase db)722 private static void upgradeBodyFromVersion100ToVersion101(final Context context, 723 final SQLiteDatabase db) { 724 try { 725 // We can't read the body parts through the cursor because they might be over 2MB 726 final String projection[] = { BodyColumns.MESSAGE_KEY }; 727 final Cursor cursor = db.query(Body.TABLE_NAME, projection, 728 null, null, null, null, null); 729 if (cursor == null) { 730 throw new IllegalStateException("Could not read body table for upgrade"); 731 } 732 733 final SQLiteStatement htmlSql = db.compileStatement( 734 "SELECT " + BodyColumns.HTML_CONTENT + 735 " FROM " + Body.TABLE_NAME + 736 " WHERE " + BodyColumns.MESSAGE_KEY + "=?" 737 ); 738 739 final SQLiteStatement textSql = db.compileStatement( 740 "SELECT " + BodyColumns.TEXT_CONTENT + 741 " FROM " + Body.TABLE_NAME + 742 " WHERE " + BodyColumns.MESSAGE_KEY + "=?" 743 ); 744 745 while (cursor.moveToNext()) { 746 final long messageId = cursor.getLong(0); 747 htmlSql.bindLong(1, messageId); 748 try { 749 final String htmlString = htmlSql.simpleQueryForString(); 750 if (!TextUtils.isEmpty(htmlString)) { 751 final File htmlFile = EmailProvider.getBodyFile(context, messageId, "html"); 752 final FileWriter w = new FileWriter(htmlFile); 753 try { 754 w.write(htmlString); 755 } finally { 756 w.close(); 757 } 758 } 759 } catch (final SQLiteDoneException e) { 760 LogUtils.v(LogUtils.TAG, e, "Done with the HTML column"); 761 } 762 textSql.bindLong(1, messageId); 763 try { 764 final String textString = textSql.simpleQueryForString(); 765 if (!TextUtils.isEmpty(textString)) { 766 final File textFile = EmailProvider.getBodyFile(context, messageId, "txt"); 767 final FileWriter w = new FileWriter(textFile); 768 try { 769 w.write(textString); 770 } finally { 771 w.close(); 772 } 773 } 774 } catch (final SQLiteDoneException e) { 775 LogUtils.v(LogUtils.TAG, e, "Done with the text column"); 776 } 777 } 778 779 db.execSQL("update " + Body.TABLE_NAME + 780 " set " + BodyColumns.HTML_CONTENT + "=NULL," 781 + BodyColumns.TEXT_CONTENT + "=NULL"); 782 } catch (final SQLException e) { 783 // Shouldn't be needed unless we're debugging and interrupt the process 784 LogUtils.w(TAG, e, "Exception upgrading EmailProviderBody.db from v100 to v101"); 785 } catch (final IOException e) { 786 throw new RuntimeException(e); 787 } 788 } 789 790 791 protected static class BodyDatabaseHelper extends SQLiteOpenHelper { 792 final Context mContext; 793 BodyDatabaseHelper(Context context, String name)794 BodyDatabaseHelper(Context context, String name) { 795 super(context, name, null, BODY_DATABASE_VERSION); 796 mContext = context; 797 } 798 799 @Override onCreate(SQLiteDatabase db)800 public void onCreate(SQLiteDatabase db) { 801 LogUtils.d(TAG, "Creating EmailProviderBody database"); 802 createBodyTable(db); 803 } 804 805 @Override onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion)806 public void onUpgrade(final SQLiteDatabase db, final int oldVersion, final int newVersion) { 807 if (oldVersion < 5) { 808 upgradeBodyToVersion5(db); 809 } 810 if (oldVersion < 6) { 811 upgradeBodyFromVersion5ToVersion6(db); 812 } 813 if (oldVersion < 8) { 814 upgradeBodyFromVersion6ToVersion8(db); 815 } 816 if (oldVersion < 101) { 817 upgradeBodyFromVersion100ToVersion101(mContext, db); 818 } 819 } 820 821 @Override onOpen(SQLiteDatabase db)822 public void onOpen(SQLiteDatabase db) { 823 } 824 } 825 826 /** Counts the number of messages in each mailbox, and updates the message count column. */ 827 @VisibleForTesting recalculateMessageCount(SQLiteDatabase db)828 static void recalculateMessageCount(SQLiteDatabase db) { 829 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 830 "= (select count(*) from " + Message.TABLE_NAME + 831 " where " + MessageColumns.MAILBOX_KEY + " = " + 832 Mailbox.TABLE_NAME + "." + MailboxColumns._ID + ")"); 833 } 834 835 protected static class DatabaseHelper extends SQLiteOpenHelper { 836 final Context mContext; 837 DatabaseHelper(Context context, String name)838 DatabaseHelper(Context context, String name) { 839 super(context, name, null, DATABASE_VERSION); 840 mContext = context; 841 } 842 843 @Override onCreate(SQLiteDatabase db)844 public void onCreate(SQLiteDatabase db) { 845 LogUtils.d(TAG, "Creating EmailProvider database"); 846 // Create all tables here; each class has its own method 847 createMessageTable(mContext, db); 848 createAttachmentTable(db); 849 createMailboxTable(db); 850 createHostAuthTable(db); 851 createAccountTable(db); 852 createMessageMoveTable(db); 853 createMessageStateChangeTable(db); 854 createPolicyTable(db); 855 createQuickResponseTable(db); 856 createCredentialsTable(db); 857 } 858 859 @Override onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion)860 public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) { 861 if (oldVersion == 101 && newVersion == 100) { 862 LogUtils.d(TAG, "Downgrade from v101 to v100"); 863 } else { 864 super.onDowngrade(db, oldVersion, newVersion); 865 } 866 } 867 868 @Override 869 @SuppressWarnings("deprecation") onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)870 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 871 // For versions prior to 5, delete all data 872 // Versions >= 5 require that data be preserved! 873 if (oldVersion < 5) { 874 android.accounts.Account[] accounts = AccountManager.get(mContext) 875 .getAccountsByType(LEGACY_SCHEME_EAS); 876 for (android.accounts.Account account: accounts) { 877 AccountManager.get(mContext).removeAccount(account, null, null); 878 } 879 resetMessageTable(mContext, db, oldVersion, newVersion); 880 resetAttachmentTable(db, oldVersion, newVersion); 881 resetMailboxTable(db, oldVersion, newVersion); 882 resetHostAuthTable(db, oldVersion, newVersion); 883 resetAccountTable(db, oldVersion, newVersion); 884 return; 885 } 886 if (oldVersion == 5) { 887 // Message Tables: Add SyncColumns.SERVER_TIMESTAMP 888 try { 889 db.execSQL("alter table " + Message.TABLE_NAME 890 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 891 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 892 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 893 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 894 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 895 } catch (SQLException e) { 896 // Shouldn't be needed unless we're debugging and interrupt the process 897 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v5 to v6", e); 898 } 899 } 900 // TODO: Change all these to strict inequalities 901 if (oldVersion <= 6) { 902 // Use the newer mailbox_delete trigger 903 db.execSQL("drop trigger mailbox_delete;"); 904 db.execSQL(TRIGGER_MAILBOX_DELETE); 905 } 906 if (oldVersion <= 7) { 907 // add the security (provisioning) column 908 try { 909 db.execSQL("alter table " + Account.TABLE_NAME 910 + " add column " + AccountColumns.SECURITY_FLAGS + " integer" + ";"); 911 } catch (SQLException e) { 912 // Shouldn't be needed unless we're debugging and interrupt the process 913 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 7 to 8 " + e); 914 } 915 } 916 if (oldVersion <= 8) { 917 // accounts: add security sync key & user signature columns 918 try { 919 db.execSQL("alter table " + Account.TABLE_NAME 920 + " add column " + AccountColumns.SECURITY_SYNC_KEY + " text" + ";"); 921 db.execSQL("alter table " + Account.TABLE_NAME 922 + " add column " + AccountColumns.SIGNATURE + " text" + ";"); 923 } catch (SQLException e) { 924 // Shouldn't be needed unless we're debugging and interrupt the process 925 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 8 to 9 " + e); 926 } 927 } 928 if (oldVersion <= 9) { 929 // Message: add meeting info column into Message tables 930 try { 931 db.execSQL("alter table " + Message.TABLE_NAME 932 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 933 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 934 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 935 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 936 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 937 } catch (SQLException e) { 938 // Shouldn't be needed unless we're debugging and interrupt the process 939 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 9 to 10 " + e); 940 } 941 } 942 if (oldVersion <= 10) { 943 // Attachment: add content and flags columns 944 try { 945 db.execSQL("alter table " + Attachment.TABLE_NAME 946 + " add column " + AttachmentColumns.CONTENT + " text" + ";"); 947 db.execSQL("alter table " + Attachment.TABLE_NAME 948 + " add column " + AttachmentColumns.FLAGS + " integer" + ";"); 949 } catch (SQLException e) { 950 // Shouldn't be needed unless we're debugging and interrupt the process 951 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 10 to 11 " + e); 952 } 953 } 954 if (oldVersion <= 11) { 955 // Attachment: add content_bytes 956 try { 957 db.execSQL("alter table " + Attachment.TABLE_NAME 958 + " add column " + AttachmentColumns.CONTENT_BYTES + " blob" + ";"); 959 } catch (SQLException e) { 960 // Shouldn't be needed unless we're debugging and interrupt the process 961 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 11 to 12 " + e); 962 } 963 } 964 if (oldVersion <= 12) { 965 try { 966 db.execSQL("alter table " + Mailbox.TABLE_NAME 967 + " add column " + Mailbox.MESSAGE_COUNT 968 +" integer not null default 0" + ";"); 969 recalculateMessageCount(db); 970 } catch (SQLException e) { 971 // Shouldn't be needed unless we're debugging and interrupt the process 972 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 12 to 13 " + e); 973 } 974 } 975 if (oldVersion <= 13) { 976 try { 977 db.execSQL("alter table " + Message.TABLE_NAME 978 + " add column " + MessageColumns.SNIPPET 979 +" text" + ";"); 980 } catch (SQLException e) { 981 // Shouldn't be needed unless we're debugging and interrupt the process 982 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 13 to 14 " + e); 983 } 984 } 985 if (oldVersion <= 14) { 986 try { 987 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 988 + " add column " + MessageColumns.SNIPPET +" text" + ";"); 989 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 990 + " add column " + MessageColumns.SNIPPET +" text" + ";"); 991 } catch (SQLException e) { 992 // Shouldn't be needed unless we're debugging and interrupt the process 993 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 14 to 15 " + e); 994 } 995 } 996 if (oldVersion <= 15) { 997 try { 998 db.execSQL("alter table " + Attachment.TABLE_NAME 999 + " add column " + AttachmentColumns.ACCOUNT_KEY +" integer" + ";"); 1000 // Update all existing attachments to add the accountKey data 1001 db.execSQL("update " + Attachment.TABLE_NAME + " set " + 1002 AttachmentColumns.ACCOUNT_KEY + "= (SELECT " + Message.TABLE_NAME + 1003 "." + MessageColumns.ACCOUNT_KEY + " from " + Message.TABLE_NAME + 1004 " where " + Message.TABLE_NAME + "." + MessageColumns._ID + " = " + 1005 Attachment.TABLE_NAME + "." + AttachmentColumns.MESSAGE_KEY + ")"); 1006 } catch (SQLException e) { 1007 // Shouldn't be needed unless we're debugging and interrupt the process 1008 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 15 to 16 " + e); 1009 } 1010 } 1011 if (oldVersion <= 16) { 1012 try { 1013 db.execSQL("alter table " + Mailbox.TABLE_NAME 1014 + " add column " + Mailbox.PARENT_KEY + " integer;"); 1015 } catch (SQLException e) { 1016 // Shouldn't be needed unless we're debugging and interrupt the process 1017 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 16 to 17 " + e); 1018 } 1019 } 1020 if (oldVersion <= 17) { 1021 upgradeFromVersion17ToVersion18(db); 1022 } 1023 if (oldVersion <= 18) { 1024 try { 1025 db.execSQL("alter table " + Account.TABLE_NAME 1026 + " add column " + AccountColumns.POLICY_KEY + " integer;"); 1027 db.execSQL("drop trigger account_delete;"); 1028 db.execSQL(TRIGGER_ACCOUNT_DELETE); 1029 createPolicyTable(db); 1030 convertPolicyFlagsToPolicyTable(db); 1031 } catch (SQLException e) { 1032 // Shouldn't be needed unless we're debugging and interrupt the process 1033 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 18 to 19 " + e); 1034 } 1035 } 1036 if (oldVersion <= 19) { 1037 try { 1038 db.execSQL("alter table " + Policy.TABLE_NAME 1039 + " add column " + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + 1040 " integer;"); 1041 db.execSQL("alter table " + Policy.TABLE_NAME 1042 + " add column " + PolicyColumns.DONT_ALLOW_CAMERA + " integer;"); 1043 db.execSQL("alter table " + Policy.TABLE_NAME 1044 + " add column " + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer;"); 1045 db.execSQL("alter table " + Policy.TABLE_NAME 1046 + " add column " + PolicyColumns.DONT_ALLOW_HTML + " integer;"); 1047 db.execSQL("alter table " + Policy.TABLE_NAME 1048 + " add column " + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer;"); 1049 db.execSQL("alter table " + Policy.TABLE_NAME 1050 + " add column " + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + 1051 " integer;"); 1052 db.execSQL("alter table " + Policy.TABLE_NAME 1053 + " add column " + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + 1054 " integer;"); 1055 db.execSQL("alter table " + Policy.TABLE_NAME 1056 + " add column " + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer;"); 1057 db.execSQL("alter table " + Policy.TABLE_NAME 1058 + " add column " + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer;"); 1059 db.execSQL("alter table " + Policy.TABLE_NAME 1060 + " add column " + PolicyColumns.PASSWORD_RECOVERY_ENABLED + 1061 " integer;"); 1062 } catch (SQLException e) { 1063 // Shouldn't be needed unless we're debugging and interrupt the process 1064 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 19 to 20 " + e); 1065 } 1066 } 1067 if (oldVersion <= 21) { 1068 upgradeFromVersion21ToVersion22(db, mContext); 1069 oldVersion = 22; 1070 } 1071 if (oldVersion <= 22) { 1072 upgradeFromVersion22ToVersion23(db); 1073 } 1074 if (oldVersion <= 23) { 1075 upgradeFromVersion23ToVersion24(db); 1076 } 1077 if (oldVersion <= 24) { 1078 upgradeFromVersion24ToVersion25(db); 1079 } 1080 if (oldVersion <= 25) { 1081 upgradeFromVersion25ToVersion26(db); 1082 } 1083 if (oldVersion <= 26) { 1084 try { 1085 db.execSQL("alter table " + Message.TABLE_NAME 1086 + " add column " + MessageColumns.PROTOCOL_SEARCH_INFO + " text;"); 1087 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1088 + " add column " + MessageColumns.PROTOCOL_SEARCH_INFO +" text" + ";"); 1089 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1090 + " add column " + MessageColumns.PROTOCOL_SEARCH_INFO +" text" + ";"); 1091 } catch (SQLException e) { 1092 // Shouldn't be needed unless we're debugging and interrupt the process 1093 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 26 to 27 " + e); 1094 } 1095 } 1096 if (oldVersion <= 28) { 1097 try { 1098 db.execSQL("alter table " + Policy.TABLE_NAME 1099 + " add column " + Policy.PROTOCOL_POLICIES_ENFORCED + " text;"); 1100 db.execSQL("alter table " + Policy.TABLE_NAME 1101 + " add column " + Policy.PROTOCOL_POLICIES_UNSUPPORTED + " text;"); 1102 } catch (SQLException e) { 1103 // Shouldn't be needed unless we're debugging and interrupt the process 1104 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 28 to 29 " + e); 1105 } 1106 } 1107 if (oldVersion <= 29) { 1108 upgradeFromVersion29ToVersion30(db); 1109 } 1110 if (oldVersion <= 30) { 1111 try { 1112 db.execSQL("alter table " + Mailbox.TABLE_NAME 1113 + " add column " + Mailbox.UI_SYNC_STATUS + " integer;"); 1114 db.execSQL("alter table " + Mailbox.TABLE_NAME 1115 + " add column " + Mailbox.UI_LAST_SYNC_RESULT + " integer;"); 1116 } catch (SQLException e) { 1117 // Shouldn't be needed unless we're debugging and interrupt the process 1118 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 30 to 31 " + e); 1119 } 1120 } 1121 if (oldVersion <= 31) { 1122 try { 1123 db.execSQL("alter table " + Mailbox.TABLE_NAME 1124 + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " integer;"); 1125 db.execSQL("alter table " + Mailbox.TABLE_NAME 1126 + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " integer;"); 1127 db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + 1128 "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " IS NULL"); 1129 db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + 1130 "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " IS NULL"); 1131 } catch (SQLException e) { 1132 // Shouldn't be needed unless we're debugging and interrupt the process 1133 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 31 to 32 " + e); 1134 } 1135 } 1136 if (oldVersion <= 32) { 1137 try { 1138 db.execSQL("alter table " + Attachment.TABLE_NAME 1139 + " add column " + AttachmentColumns.UI_STATE + " integer;"); 1140 db.execSQL("alter table " + Attachment.TABLE_NAME 1141 + " add column " + AttachmentColumns.UI_DESTINATION + " integer;"); 1142 db.execSQL("alter table " + Attachment.TABLE_NAME 1143 + " add column " + AttachmentColumns.UI_DOWNLOADED_SIZE + " integer;"); 1144 // If we have a contentUri then the attachment is saved 1145 // uiDestination of 0 = "cache", so we don't have to set this 1146 db.execSQL("update " + Attachment.TABLE_NAME + " set " + 1147 AttachmentColumns.UI_STATE + "=" + UIProvider.AttachmentState.SAVED + 1148 " where " + AttachmentColumns.CONTENT_URI + " is not null;"); 1149 } catch (SQLException e) { 1150 // Shouldn't be needed unless we're debugging and interrupt the process 1151 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 32 to 33 " + e); 1152 } 1153 } 1154 if (oldVersion <= 33) { 1155 try { 1156 db.execSQL("alter table " + Mailbox.TABLE_NAME 1157 + " add column " + MailboxColumns.TOTAL_COUNT + " integer;"); 1158 } catch (SQLException e) { 1159 // Shouldn't be needed unless we're debugging and interrupt the process 1160 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 33 to 34 " + e); 1161 } 1162 } 1163 if (oldVersion <= 34) { 1164 try { 1165 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1166 MailboxColumns.LAST_TOUCHED_TIME + " = " + 1167 Mailbox.DRAFTS_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1168 " = " + Mailbox.TYPE_DRAFTS); 1169 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1170 MailboxColumns.LAST_TOUCHED_TIME + " = " + 1171 Mailbox.SENT_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1172 " = " + Mailbox.TYPE_SENT); 1173 } catch (SQLException e) { 1174 // Shouldn't be needed unless we're debugging and interrupt the process 1175 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 34 to 35 " + e); 1176 } 1177 } 1178 if (oldVersion <= 36) { 1179 try { 1180 // Set "supports settings" for EAS mailboxes 1181 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1182 MailboxColumns.FLAGS + "=" + MailboxColumns.FLAGS + "|" + 1183 Mailbox.FLAG_SUPPORTS_SETTINGS + " where (" + 1184 MailboxColumns.FLAGS + "&" + Mailbox.FLAG_HOLDS_MAIL + ")!=0 and " + 1185 MailboxColumns.ACCOUNT_KEY + " IN (SELECT " + Account.TABLE_NAME + 1186 "." + AccountColumns._ID + " from " + Account.TABLE_NAME + "," + 1187 HostAuth.TABLE_NAME + " where " + Account.TABLE_NAME + "." + 1188 AccountColumns.HOST_AUTH_KEY_RECV + "=" + HostAuth.TABLE_NAME + "." + 1189 HostAuthColumns._ID + " and " + HostAuthColumns.PROTOCOL + "='" + 1190 LEGACY_SCHEME_EAS + "')"); 1191 } catch (SQLException e) { 1192 // Shouldn't be needed unless we're debugging and interrupt the process 1193 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 35 to 36 " + e); 1194 } 1195 } 1196 if (oldVersion <= 37) { 1197 try { 1198 db.execSQL("alter table " + Message.TABLE_NAME 1199 + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1200 } catch (SQLException e) { 1201 // Shouldn't be needed unless we're debugging and interrupt the process 1202 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 37 to 38 " + e); 1203 } 1204 } 1205 if (oldVersion <= 38) { 1206 try { 1207 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1208 + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1209 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1210 + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 1211 } catch (SQLException e) { 1212 // Shouldn't be needed unless we're debugging and interrupt the process 1213 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 38 to 39 " + e); 1214 } 1215 } 1216 if (oldVersion <= 39) { 1217 upgradeToEmail2(db); 1218 } 1219 if (oldVersion <= 102) { 1220 try { 1221 db.execSQL("alter table " + Mailbox.TABLE_NAME 1222 + " add " + MailboxColumns.HIERARCHICAL_NAME + " text"); 1223 } catch (SQLException e) { 1224 // Shouldn't be needed unless we're debugging and interrupt the process 1225 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v10x to v103", e); 1226 } 1227 } 1228 if (oldVersion <= 103) { 1229 try { 1230 db.execSQL("alter table " + Message.TABLE_NAME 1231 + " add " + MessageColumns.SYNC_DATA + " text"); 1232 } catch (SQLException e) { 1233 // Shouldn't be needed unless we're debugging and interrupt the process 1234 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v103 to v104", e); 1235 } 1236 } 1237 if (oldVersion <= 104) { 1238 try { 1239 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1240 + " add " + MessageColumns.SYNC_DATA + " text"); 1241 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1242 + " add " + MessageColumns.SYNC_DATA + " text"); 1243 } catch (SQLException e) { 1244 // Shouldn't be needed unless we're debugging and interrupt the process 1245 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v104 to v105", e); 1246 } 1247 } 1248 if (oldVersion <= 105) { 1249 try { 1250 db.execSQL("alter table " + HostAuth.TABLE_NAME 1251 + " add " + HostAuthColumns.SERVER_CERT + " blob"); 1252 } catch (SQLException e) { 1253 // Shouldn't be needed unless we're debugging and interrupt the process 1254 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v105 to v106", e); 1255 } 1256 } 1257 if (oldVersion <= 106) { 1258 try { 1259 db.execSQL("alter table " + Message.TABLE_NAME 1260 + " add " + MessageColumns.FLAG_SEEN + " integer"); 1261 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1262 + " add " + MessageColumns.FLAG_SEEN + " integer"); 1263 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1264 + " add " + MessageColumns.FLAG_SEEN + " integer"); 1265 } catch (SQLException e) { 1266 // Shouldn't be needed unless we're debugging and interrupt the process 1267 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v106 to v107", e); 1268 } 1269 } 1270 if (oldVersion <= 107) { 1271 try { 1272 db.execSQL("alter table " + Attachment.TABLE_NAME 1273 + " add column " + AttachmentColumns.CACHED_FILE +" text" + ";"); 1274 } catch (SQLException e) { 1275 // Shouldn't be needed unless we're debugging and interrupt the process 1276 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v107 to v108", e); 1277 } 1278 } 1279 if (oldVersion <= 108) { 1280 // Migrate the accounts with the correct account type 1281 migrateLegacyAccounts(db, mContext); 1282 } 1283 if (oldVersion <= 109) { 1284 // Fix any mailboxes that have ping or push_hold states. 1285 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 1286 + "=" + Mailbox.CHECK_INTERVAL_PUSH + " where " 1287 + MailboxColumns.SYNC_INTERVAL + "<" + Mailbox.CHECK_INTERVAL_PUSH); 1288 1289 // Fix invalid syncLookback values. 1290 db.execSQL("update " + Account.TABLE_NAME + " set " + AccountColumns.SYNC_LOOKBACK 1291 + "=" + SyncWindow.SYNC_WINDOW_1_WEEK + " where " 1292 + AccountColumns.SYNC_LOOKBACK + " is null or " 1293 + AccountColumns.SYNC_LOOKBACK + "<" + SyncWindow.SYNC_WINDOW_1_DAY + " or " 1294 + AccountColumns.SYNC_LOOKBACK + ">" + SyncWindow.SYNC_WINDOW_ALL); 1295 1296 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_LOOKBACK 1297 + "=" + SyncWindow.SYNC_WINDOW_ACCOUNT + " where " 1298 + MailboxColumns.SYNC_LOOKBACK + " is null or " 1299 + MailboxColumns.SYNC_LOOKBACK + "<" + SyncWindow.SYNC_WINDOW_1_DAY + " or " 1300 + MailboxColumns.SYNC_LOOKBACK + ">" + SyncWindow.SYNC_WINDOW_ALL); 1301 } 1302 if (oldVersion <= 110) { 1303 // Delete account mailboxes. 1304 db.execSQL("delete from " + Mailbox.TABLE_NAME + " where " + MailboxColumns.TYPE 1305 + "=" +Mailbox.TYPE_EAS_ACCOUNT_MAILBOX); 1306 } 1307 if (oldVersion <= 111) { 1308 // Mailbox sync interval now indicates whether this mailbox syncs with the rest 1309 // of the account. Anyone who was syncing at all, plus outboxes, are set to 1, 1310 // everyone else is 0. 1311 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 1312 + "=case when " + MailboxColumns.SYNC_INTERVAL + "=" 1313 + Mailbox.CHECK_INTERVAL_NEVER + " then 0 else 1 end"); 1314 } 1315 if (oldVersion >= 110 && oldVersion <= 112) { 1316 // v110 had dropped these triggers, but starting with v113 we restored them 1317 // (and altered the 109 -> 110 upgrade code to stop dropping them). 1318 // We therefore only add them back for the versions in between. We also need to 1319 // compute the correct value at this point as well. 1320 recalculateMessageCount(db); 1321 createMessageCountTriggers(db); 1322 } 1323 1324 if (oldVersion <= 113) { 1325 try { 1326 db.execSQL("alter table " + Mailbox.TABLE_NAME 1327 + " add column " + MailboxColumns.LAST_FULL_SYNC_TIME +" integer" + ";"); 1328 final ContentValues cv = new ContentValues(1); 1329 cv.put(MailboxColumns.LAST_FULL_SYNC_TIME, 0); 1330 db.update(Mailbox.TABLE_NAME, cv, null, null); 1331 } catch (final SQLException e) { 1332 // Shouldn't be needed unless we're debugging and interrupt the process 1333 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v113 to v114", e); 1334 } 1335 } 1336 1337 if (oldVersion <= 114) { 1338 try { 1339 db.execSQL("alter table " + Account.TABLE_NAME 1340 + " add column " + AccountColumns.PING_DURATION +" integer" + ";"); 1341 final ContentValues cv = new ContentValues(1); 1342 cv.put(AccountColumns.PING_DURATION, 0); 1343 db.update(Account.TABLE_NAME, cv, null, null); 1344 } catch (final SQLException e) { 1345 // Shouldn't be needed unless we're debugging and interrupt the process 1346 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v113 to v114", e); 1347 } 1348 } 1349 1350 if (oldVersion <= 115) { 1351 createMessageMoveTable(db); 1352 createMessageStateChangeTable(db); 1353 } 1354 1355 /** 1356 * Originally, at 116, we added a trigger to delete duplicate messages. 1357 * But we needed to change that trigger for version 120, so when we get 1358 * there, we'll drop the trigger if it exists and create a new version. 1359 */ 1360 1361 /** 1362 * This statement changes the syncInterval column to 0 for all IMAP mailboxes. 1363 * It does this by matching mailboxes against all account IDs whose receive auth is 1364 * either R.string.protocol_legacy_imap, R.string.protocol_imap or "imap" 1365 */ 1366 if (oldVersion <= 117) { 1367 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 1368 + "=0 where " + MailboxColumns.ACCOUNT_KEY + " in (select " 1369 + Account.TABLE_NAME + "." + AccountColumns._ID + " from " 1370 + Account.TABLE_NAME + " join " + HostAuth.TABLE_NAME + " where " 1371 + HostAuth.TABLE_NAME + "." + HostAuthColumns._ID + "=" 1372 + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV 1373 + " and (" + HostAuth.TABLE_NAME + "." 1374 + HostAuthColumns.PROTOCOL + "='" 1375 + mContext.getString(R.string.protocol_legacy_imap) + "' or " 1376 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='" 1377 + mContext.getString(R.string.protocol_imap) + "' or " 1378 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap'));"); 1379 } 1380 1381 /** 1382 * This statement changes the sync interval column to 0 for all DRAFTS type mailboxes, 1383 * and deletes any messages that are: 1384 * * synced from the server, and 1385 * * in an exchange account draft folder 1386 * 1387 * This is primary for Exchange (b/11158759) but we don't sync draft folders for any 1388 * other account type anyway. 1389 * This will only affect people who used intermediate builds between email1 and email2, 1390 * it should be a no-op for most users. 1391 */ 1392 if (oldVersion <= 118) { 1393 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.SYNC_INTERVAL 1394 + "=0 where " + MailboxColumns.TYPE + "=" + Mailbox.TYPE_DRAFTS); 1395 1396 db.execSQL("delete from " + Message.TABLE_NAME + " where " 1397 + "(" + SyncColumns.SERVER_ID + " not null and " 1398 + SyncColumns.SERVER_ID + "!='') and " 1399 + MessageColumns.MAILBOX_KEY + " in (select " 1400 + MailboxColumns._ID + " from " + Mailbox.TABLE_NAME + " where " 1401 + MailboxColumns.TYPE + "=" + Mailbox.TYPE_DRAFTS + ")"); 1402 } 1403 1404 // We originally dropped and recreated the deleteDuplicateMessagesTrigger here at 1405 // version 120. We needed to update it again at version 123, so there's no reason 1406 // to do it twice. 1407 1408 // Add the mainMailboxKey column, and get rid of any messages in the search_results 1409 // folder. 1410 if (oldVersion <= 120) { 1411 db.execSQL("alter table " + Message.TABLE_NAME 1412 + " add " + MessageColumns.MAIN_MAILBOX_KEY + " integer"); 1413 1414 // Delete all TYPE_SEARCH mailboxes. These will be for stale queries anyway, and 1415 // the messages in them will not have the mainMailboxKey column correctly populated. 1416 // We have a trigger (See TRIGGER_MAILBOX_DELETE) that will delete any messages 1417 // in the deleted mailboxes. 1418 db.execSQL("delete from " + Mailbox.TABLE_NAME + " where " 1419 + Mailbox.TYPE + "=" + Mailbox.TYPE_SEARCH); 1420 } 1421 1422 if (oldVersion <= 121) { 1423 // The previous update omitted making these changes to the Message_Updates and 1424 // Message_Deletes tables. The app will actually crash in between these versions! 1425 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1426 + " add " + MessageColumns.MAIN_MAILBOX_KEY + " integer"); 1427 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1428 + " add " + MessageColumns.MAIN_MAILBOX_KEY + " integer"); 1429 } 1430 1431 if (oldVersion <= 122) { 1432 if (oldVersion >= 117) { 1433 /** 1434 * This trigger was originally created at version 117, but we needed to change 1435 * it for version 122. So if our oldVersion is 117 or more, we know we have that 1436 * trigger and must drop it before re creating it. 1437 */ 1438 dropDeleteDuplicateMessagesTrigger(db); 1439 } 1440 createDeleteDuplicateMessagesTrigger(mContext, db); 1441 } 1442 1443 if (oldVersion <= 123) { 1444 try { 1445 db.execSQL("alter table " + Account.TABLE_NAME 1446 + " add column " + AccountColumns.MAX_ATTACHMENT_SIZE +" integer" + ";"); 1447 final ContentValues cv = new ContentValues(1); 1448 cv.put(AccountColumns.MAX_ATTACHMENT_SIZE, 0); 1449 db.update(Account.TABLE_NAME, cv, null, null); 1450 } catch (final SQLException e) { 1451 // Shouldn't be needed unless we're debugging and interrupt the process 1452 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from v123 to v124", e); 1453 } 1454 } 1455 1456 if (oldVersion <= 124) { 1457 createCredentialsTable(db); 1458 // Add the credentialKey column, and set it to -1 for all pre-existing hostAuths. 1459 db.execSQL("alter table " + HostAuth.TABLE_NAME 1460 + " add " + HostAuthColumns.CREDENTIAL_KEY + " integer"); 1461 db.execSQL("update " + HostAuth.TABLE_NAME + " set " 1462 + HostAuthColumns.CREDENTIAL_KEY + "=-1"); 1463 } 1464 1465 if (oldVersion <= 125) { 1466 upgradeFromVersion125ToVersion126(db); 1467 } 1468 1469 if (oldVersion <= 126) { 1470 upgradeFromVersion126ToVersion127(mContext, db); 1471 } 1472 } 1473 1474 @Override onOpen(SQLiteDatabase db)1475 public void onOpen(SQLiteDatabase db) { 1476 try { 1477 // Cleanup some nasty records 1478 db.execSQL("DELETE FROM " + Account.TABLE_NAME 1479 + " WHERE " + AccountColumns.DISPLAY_NAME + " ISNULL;"); 1480 db.execSQL("DELETE FROM " + HostAuth.TABLE_NAME 1481 + " WHERE " + HostAuthColumns.PROTOCOL + " ISNULL;"); 1482 } catch (SQLException e) { 1483 // Shouldn't be needed unless we're debugging and interrupt the process 1484 LogUtils.e(TAG, e, "Exception cleaning EmailProvider.db"); 1485 } 1486 } 1487 } 1488 1489 @VisibleForTesting 1490 @SuppressWarnings("deprecation") convertPolicyFlagsToPolicyTable(SQLiteDatabase db)1491 static void convertPolicyFlagsToPolicyTable(SQLiteDatabase db) { 1492 Cursor c = db.query(Account.TABLE_NAME, 1493 new String[] {BaseColumns._ID /*0*/, AccountColumns.SECURITY_FLAGS /*1*/}, 1494 AccountColumns.SECURITY_FLAGS + ">0", null, null, null, null); 1495 try { 1496 ContentValues cv = new ContentValues(); 1497 String[] args = new String[1]; 1498 while (c.moveToNext()) { 1499 long securityFlags = c.getLong(1 /*SECURITY_FLAGS*/); 1500 Policy policy = LegacyPolicySet.flagsToPolicy(securityFlags); 1501 long policyId = db.insert(Policy.TABLE_NAME, null, policy.toContentValues()); 1502 cv.put(AccountColumns.POLICY_KEY, policyId); 1503 cv.putNull(AccountColumns.SECURITY_FLAGS); 1504 args[0] = Long.toString(c.getLong(0 /*_ID*/)); 1505 db.update(Account.TABLE_NAME, cv, BaseColumns._ID + "=?", args); 1506 } 1507 } finally { 1508 c.close(); 1509 } 1510 } 1511 1512 /** Upgrades the database from v17 to v18 */ 1513 @VisibleForTesting upgradeFromVersion17ToVersion18(SQLiteDatabase db)1514 static void upgradeFromVersion17ToVersion18(SQLiteDatabase db) { 1515 // Copy the displayName column to the serverId column. In v18 of the database, 1516 // we use the serverId for IMAP/POP3 mailboxes instead of overloading the 1517 // display name. 1518 // 1519 // For posterity; this is the command we're executing: 1520 //sqlite> UPDATE mailbox SET serverid=displayname WHERE mailbox._id in ( 1521 // ...> SELECT mailbox._id FROM mailbox,account,hostauth WHERE 1522 // ...> (mailbox.parentkey isnull OR mailbox.parentkey=0) AND 1523 // ...> mailbox.accountkey=account._id AND 1524 // ...> account.hostauthkeyrecv=hostauth._id AND 1525 // ...> (hostauth.protocol='imap' OR hostauth.protocol='pop3')); 1526 try { 1527 db.execSQL( 1528 "UPDATE " + Mailbox.TABLE_NAME + " SET " 1529 + MailboxColumns.SERVER_ID + "=" + MailboxColumns.DISPLAY_NAME 1530 + " WHERE " 1531 + Mailbox.TABLE_NAME + "." + MailboxColumns._ID + " IN ( SELECT " 1532 + Mailbox.TABLE_NAME + "." + MailboxColumns._ID + " FROM " 1533 + Mailbox.TABLE_NAME + "," + Account.TABLE_NAME + "," 1534 + HostAuth.TABLE_NAME + " WHERE " 1535 + "(" 1536 + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + " isnull OR " 1537 + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + "=0 " 1538 + ") AND " 1539 + Mailbox.TABLE_NAME + "." + MailboxColumns.ACCOUNT_KEY + "=" 1540 + Account.TABLE_NAME + "." + AccountColumns._ID + " AND " 1541 + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV + "=" 1542 + HostAuth.TABLE_NAME + "." + HostAuthColumns._ID + " AND ( " 1543 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap' OR " 1544 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='pop3' ) )"); 1545 } catch (SQLException e) { 1546 // Shouldn't be needed unless we're debugging and interrupt the process 1547 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 17 to 18 " + e); 1548 } 1549 ContentCache.invalidateAllCaches(); 1550 } 1551 1552 /** 1553 * Upgrade the database from v21 to v22 1554 * This entails creating AccountManager accounts for all pop3 and imap accounts 1555 */ 1556 1557 private static final String[] V21_ACCOUNT_PROJECTION = 1558 new String[] {AccountColumns.HOST_AUTH_KEY_RECV, AccountColumns.EMAIL_ADDRESS}; 1559 private static final int V21_ACCOUNT_RECV = 0; 1560 private static final int V21_ACCOUNT_EMAIL = 1; 1561 1562 private static final String[] V21_HOSTAUTH_PROJECTION = 1563 new String[] {HostAuthColumns.PROTOCOL, HostAuthColumns.PASSWORD}; 1564 private static final int V21_HOSTAUTH_PROTOCOL = 0; 1565 private static final int V21_HOSTAUTH_PASSWORD = 1; 1566 createAccountManagerAccount(Context context, String login, String type, String password)1567 private static void createAccountManagerAccount(Context context, String login, String type, 1568 String password) { 1569 final AccountManager accountManager = AccountManager.get(context); 1570 1571 if (isAccountPresent(accountManager, login, type)) { 1572 // The account already exists,just return 1573 return; 1574 } 1575 LogUtils.v("Email", "Creating account %s %s", login, type); 1576 final android.accounts.Account amAccount = new android.accounts.Account(login, type); 1577 accountManager.addAccountExplicitly(amAccount, password, null); 1578 ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 1579 ContentResolver.setSyncAutomatically(amAccount, EmailContent.AUTHORITY, true); 1580 ContentResolver.setIsSyncable(amAccount, ContactsContract.AUTHORITY, 0); 1581 ContentResolver.setIsSyncable(amAccount, CalendarContract.AUTHORITY, 0); 1582 } 1583 isAccountPresent(AccountManager accountManager, String name, String type)1584 private static boolean isAccountPresent(AccountManager accountManager, String name, 1585 String type) { 1586 final android.accounts.Account[] amAccounts = accountManager.getAccountsByType(type); 1587 if (amAccounts != null) { 1588 for (android.accounts.Account account : amAccounts) { 1589 if (TextUtils.equals(account.name, name) && TextUtils.equals(account.type, type)) { 1590 return true; 1591 } 1592 } 1593 } 1594 return false; 1595 } 1596 1597 @VisibleForTesting upgradeFromVersion21ToVersion22(SQLiteDatabase db, Context accountManagerContext)1598 static void upgradeFromVersion21ToVersion22(SQLiteDatabase db, Context accountManagerContext) { 1599 migrateLegacyAccounts(db, accountManagerContext); 1600 } 1601 migrateLegacyAccounts(SQLiteDatabase db, Context accountManagerContext)1602 private static void migrateLegacyAccounts(SQLiteDatabase db, Context accountManagerContext) { 1603 final Map<String, String> legacyToNewTypeMap = new ImmutableMap.Builder<String, String>() 1604 .put(LEGACY_SCHEME_POP3, 1605 accountManagerContext.getString(R.string.account_manager_type_pop3)) 1606 .put(LEGACY_SCHEME_IMAP, 1607 accountManagerContext.getString(R.string.account_manager_type_legacy_imap)) 1608 .put(LEGACY_SCHEME_EAS, 1609 accountManagerContext.getString(R.string.account_manager_type_exchange)) 1610 .build(); 1611 try { 1612 // Loop through accounts, looking for pop/imap accounts 1613 final Cursor accountCursor = db.query(Account.TABLE_NAME, V21_ACCOUNT_PROJECTION, null, 1614 null, null, null, null); 1615 try { 1616 final String[] hostAuthArgs = new String[1]; 1617 while (accountCursor.moveToNext()) { 1618 hostAuthArgs[0] = accountCursor.getString(V21_ACCOUNT_RECV); 1619 // Get the "receive" HostAuth for this account 1620 final Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 1621 V21_HOSTAUTH_PROJECTION, HostAuthColumns._ID + "=?", hostAuthArgs, 1622 null, null, null); 1623 try { 1624 if (hostAuthCursor.moveToFirst()) { 1625 final String protocol = hostAuthCursor.getString(V21_HOSTAUTH_PROTOCOL); 1626 // If this is a pop3 or imap account, create the account manager account 1627 if (LEGACY_SCHEME_IMAP.equals(protocol) || 1628 LEGACY_SCHEME_POP3.equals(protocol)) { 1629 // If this is a pop3 or imap account, create the account manager 1630 // account 1631 if (DebugUtils.DEBUG) { 1632 LogUtils.d(TAG, "Create AccountManager account for " + protocol 1633 + "account: " 1634 + accountCursor.getString(V21_ACCOUNT_EMAIL)); 1635 } 1636 createAccountManagerAccount(accountManagerContext, 1637 accountCursor.getString(V21_ACCOUNT_EMAIL), 1638 legacyToNewTypeMap.get(protocol), 1639 hostAuthCursor.getString(V21_HOSTAUTH_PASSWORD)); 1640 } else if (LEGACY_SCHEME_EAS.equals(protocol)) { 1641 // If an EAS account, make Email sync automatically (equivalent of 1642 // checking the "Sync Email" box in settings 1643 1644 android.accounts.Account amAccount = new android.accounts.Account( 1645 accountCursor.getString(V21_ACCOUNT_EMAIL), 1646 legacyToNewTypeMap.get(protocol)); 1647 ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 1648 ContentResolver.setSyncAutomatically(amAccount, 1649 EmailContent.AUTHORITY, true); 1650 } 1651 } 1652 } finally { 1653 hostAuthCursor.close(); 1654 } 1655 } 1656 } finally { 1657 accountCursor.close(); 1658 } 1659 } catch (SQLException e) { 1660 // Shouldn't be needed unless we're debugging and interrupt the process 1661 LogUtils.w(TAG, "Exception while migrating accounts " + e); 1662 } 1663 } 1664 1665 /** Upgrades the database from v22 to v23 */ upgradeFromVersion22ToVersion23(SQLiteDatabase db)1666 private static void upgradeFromVersion22ToVersion23(SQLiteDatabase db) { 1667 try { 1668 db.execSQL("alter table " + Mailbox.TABLE_NAME 1669 + " add column " + Mailbox.LAST_TOUCHED_TIME + " integer default 0;"); 1670 } catch (SQLException e) { 1671 // Shouldn't be needed unless we're debugging and interrupt the process 1672 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 22 to 23 " + e); 1673 } 1674 } 1675 1676 /** Adds in a column for information about a client certificate to use. */ upgradeFromVersion23ToVersion24(SQLiteDatabase db)1677 private static void upgradeFromVersion23ToVersion24(SQLiteDatabase db) { 1678 try { 1679 db.execSQL("alter table " + HostAuth.TABLE_NAME 1680 + " add column " + HostAuthColumns.CLIENT_CERT_ALIAS + " text;"); 1681 } catch (SQLException e) { 1682 // Shouldn't be needed unless we're debugging and interrupt the process 1683 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 23 to 24 " + e); 1684 } 1685 } 1686 1687 /** Upgrades the database from v24 to v25 by creating table for quick responses */ upgradeFromVersion24ToVersion25(SQLiteDatabase db)1688 private static void upgradeFromVersion24ToVersion25(SQLiteDatabase db) { 1689 try { 1690 createQuickResponseTable(db); 1691 } catch (SQLException e) { 1692 // Shouldn't be needed unless we're debugging and interrupt the process 1693 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 24 to 25 " + e); 1694 } 1695 } 1696 1697 private static final String[] V25_ACCOUNT_PROJECTION = 1698 new String[] {AccountColumns._ID, AccountColumns.FLAGS, AccountColumns.HOST_AUTH_KEY_RECV}; 1699 private static final int V25_ACCOUNT_ID = 0; 1700 private static final int V25_ACCOUNT_FLAGS = 1; 1701 private static final int V25_ACCOUNT_RECV = 2; 1702 1703 private static final String[] V25_HOSTAUTH_PROJECTION = new String[] {HostAuthColumns.PROTOCOL}; 1704 private static final int V25_HOSTAUTH_PROTOCOL = 0; 1705 1706 /** Upgrades the database from v25 to v26 by adding FLAG_SUPPORTS_SEARCH to IMAP accounts */ upgradeFromVersion25ToVersion26(SQLiteDatabase db)1707 private static void upgradeFromVersion25ToVersion26(SQLiteDatabase db) { 1708 try { 1709 // Loop through accounts, looking for imap accounts 1710 Cursor accountCursor = db.query(Account.TABLE_NAME, V25_ACCOUNT_PROJECTION, null, 1711 null, null, null, null); 1712 ContentValues cv = new ContentValues(); 1713 try { 1714 String[] hostAuthArgs = new String[1]; 1715 while (accountCursor.moveToNext()) { 1716 hostAuthArgs[0] = accountCursor.getString(V25_ACCOUNT_RECV); 1717 // Get the "receive" HostAuth for this account 1718 Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 1719 V25_HOSTAUTH_PROJECTION, HostAuthColumns._ID + "=?", hostAuthArgs, 1720 null, null, null); 1721 try { 1722 if (hostAuthCursor.moveToFirst()) { 1723 String protocol = hostAuthCursor.getString(V25_HOSTAUTH_PROTOCOL); 1724 // If this is an imap account, add the search flag 1725 if (LEGACY_SCHEME_IMAP.equals(protocol)) { 1726 String id = accountCursor.getString(V25_ACCOUNT_ID); 1727 int flags = accountCursor.getInt(V25_ACCOUNT_FLAGS); 1728 cv.put(AccountColumns.FLAGS, flags | Account.FLAGS_SUPPORTS_SEARCH); 1729 db.update(Account.TABLE_NAME, cv, AccountColumns._ID + "=?", 1730 new String[] {id}); 1731 } 1732 } 1733 } finally { 1734 hostAuthCursor.close(); 1735 } 1736 } 1737 } finally { 1738 accountCursor.close(); 1739 } 1740 } catch (SQLException e) { 1741 // Shouldn't be needed unless we're debugging and interrupt the process 1742 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 25 to 26 " + e); 1743 } 1744 } 1745 1746 /** Upgrades the database from v29 to v30 by updating all address fields in Message */ 1747 private static final int[] ADDRESS_COLUMN_INDICES = { 1748 Message.CONTENT_BCC_LIST_COLUMN, 1749 Message.CONTENT_CC_LIST_COLUMN, 1750 Message.CONTENT_FROM_LIST_COLUMN, 1751 Message.CONTENT_REPLY_TO_COLUMN, 1752 Message.CONTENT_TO_LIST_COLUMN 1753 }; 1754 private static final String[] ADDRESS_COLUMN_NAMES = { 1755 MessageColumns.BCC_LIST, 1756 MessageColumns.CC_LIST, 1757 MessageColumns.FROM_LIST, 1758 MessageColumns.REPLY_TO_LIST, 1759 MessageColumns.TO_LIST 1760 }; 1761 upgradeFromVersion29ToVersion30(SQLiteDatabase db)1762 private static void upgradeFromVersion29ToVersion30(SQLiteDatabase db) { 1763 try { 1764 // Loop through all messages, updating address columns to new format (CSV, RFC822) 1765 Cursor messageCursor = db.query(Message.TABLE_NAME, Message.CONTENT_PROJECTION, null, 1766 null, null, null, null); 1767 ContentValues cv = new ContentValues(); 1768 String[] whereArgs = new String[1]; 1769 try { 1770 while (messageCursor.moveToNext()) { 1771 for (int i = 0; i < ADDRESS_COLUMN_INDICES.length; i++) { 1772 Address[] addrs = 1773 Address.fromHeader(messageCursor.getString(ADDRESS_COLUMN_INDICES[i])); 1774 cv.put(ADDRESS_COLUMN_NAMES[i], Address.toHeader(addrs)); 1775 } 1776 whereArgs[0] = messageCursor.getString(Message.CONTENT_ID_COLUMN); 1777 db.update(Message.TABLE_NAME, cv, WHERE_ID, whereArgs); 1778 } 1779 } finally { 1780 messageCursor.close(); 1781 } 1782 } catch (SQLException e) { 1783 // Shouldn't be needed unless we're debugging and interrupt the process 1784 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 29 to 30 " + e); 1785 } 1786 } 1787 upgradeFromVersion125ToVersion126(SQLiteDatabase db)1788 private static void upgradeFromVersion125ToVersion126(SQLiteDatabase db) { 1789 try { 1790 // Loop through all messages, updating address columns to their decoded form 1791 Cursor messageCursor = db.query(Message.TABLE_NAME, Message.CONTENT_PROJECTION, null, 1792 null, null, null, null); 1793 ContentValues cv = new ContentValues(); 1794 String[] whereArgs = new String[1]; 1795 try { 1796 while (messageCursor.moveToNext()) { 1797 for (int i = 0; i < ADDRESS_COLUMN_INDICES.length; i++) { 1798 Address[] addrs = 1799 Address.fromHeader(messageCursor.getString(ADDRESS_COLUMN_INDICES[i])); 1800 cv.put(ADDRESS_COLUMN_NAMES[i], Address.toString(addrs)); 1801 } 1802 whereArgs[0] = messageCursor.getString(Message.CONTENT_ID_COLUMN); 1803 db.update(Message.TABLE_NAME, cv, WHERE_ID, whereArgs); 1804 } 1805 } finally { 1806 messageCursor.close(); 1807 } 1808 } catch (SQLException e) { 1809 // Shouldn't be needed unless we're debugging and interrupt the process 1810 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 125 to 126 " + e); 1811 } 1812 } 1813 1814 /** 1815 * Update all accounts that are EAS v12.0 or greater with SmartForward and search flags 1816 */ upgradeFromVersion126ToVersion127(final Context context, final SQLiteDatabase db)1817 private static void upgradeFromVersion126ToVersion127(final Context context, 1818 final SQLiteDatabase db) { 1819 try { 1820 // These are the flags that we want to add to the Account table for the 1821 // appropriate rows. 1822 final long newFlags = Account.FLAGS_SUPPORTS_GLOBAL_SEARCH + 1823 Account.FLAGS_SUPPORTS_SEARCH + Account.FLAGS_SUPPORTS_SMART_FORWARD; 1824 1825 // For posterity; this is the command we're executing: 1826 // UPDATE Account SET flags=flags|[new flags] WHERE _id IN (SELECT t1._id FROM Account 1827 // t1 INNER JOIN HostAuth t2 ON t1.hostAuthKeyRecv=t2._id WHERE t2.protocol='gEas' AND 1828 // CAST(t1.protocolVersion AS REAL)>=12.0) 1829 db.execSQL( 1830 "UPDATE " + Account.TABLE_NAME + " SET " + AccountColumns.FLAGS + "=" + 1831 AccountColumns.FLAGS + "|" + Long.toString(newFlags) + " WHERE " + 1832 AccountColumns._ID + " IN (SELECT t1." + AccountColumns._ID + " FROM " + 1833 Account.TABLE_NAME + " t1 INNER JOIN " + HostAuth.TABLE_NAME + 1834 " t2 ON t1." + AccountColumns.HOST_AUTH_KEY_RECV + "=t2._id WHERE t2." + 1835 HostAuthColumns.PROTOCOL + "='" + 1836 context.getString(R.string.protocol_eas) + "' AND CAST(t1." + 1837 AccountColumns.PROTOCOL_VERSION + " AS REAL)>=12.0)"); 1838 } catch (SQLException e) { 1839 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 126 to 127 " + e); 1840 } 1841 } 1842 upgradeToEmail2(SQLiteDatabase db)1843 private static void upgradeToEmail2(SQLiteDatabase db) { 1844 // Perform cleanup operations from Email1 to Email2; Email1 will have added new 1845 // data that won't conform to what's expected in Email2 1846 1847 // From 31->32 upgrade 1848 try { 1849 db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + 1850 "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " IS NULL"); 1851 db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + 1852 "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " IS NULL"); 1853 } catch (SQLException e) { 1854 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 31 to 32/100 " + e); 1855 } 1856 1857 // From 32->33 upgrade 1858 try { 1859 db.execSQL("update " + Attachment.TABLE_NAME + " set " + AttachmentColumns.UI_STATE + 1860 "=" + UIProvider.AttachmentState.SAVED + " where " + 1861 AttachmentColumns.CONTENT_URI + " is not null;"); 1862 } catch (SQLException e) { 1863 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 32 to 33/100 " + e); 1864 } 1865 1866 // From 34->35 upgrade 1867 try { 1868 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1869 MailboxColumns.LAST_TOUCHED_TIME + " = " + 1870 Mailbox.DRAFTS_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1871 " = " + Mailbox.TYPE_DRAFTS); 1872 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1873 MailboxColumns.LAST_TOUCHED_TIME + " = " + 1874 Mailbox.SENT_DEFAULT_TOUCH_TIME + " WHERE " + MailboxColumns.TYPE + 1875 " = " + Mailbox.TYPE_SENT); 1876 } catch (SQLException e) { 1877 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 34 to 35/100 " + e); 1878 } 1879 1880 // From 35/36->37 1881 try { 1882 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + 1883 MailboxColumns.FLAGS + "=" + MailboxColumns.FLAGS + "|" + 1884 Mailbox.FLAG_SUPPORTS_SETTINGS + " where (" + 1885 MailboxColumns.FLAGS + "&" + Mailbox.FLAG_HOLDS_MAIL + ")!=0 and " + 1886 MailboxColumns.ACCOUNT_KEY + " IN (SELECT " + Account.TABLE_NAME + 1887 "." + AccountColumns._ID + " from " + Account.TABLE_NAME + "," + 1888 HostAuth.TABLE_NAME + " where " + Account.TABLE_NAME + "." + 1889 AccountColumns.HOST_AUTH_KEY_RECV + "=" + HostAuth.TABLE_NAME + "." + 1890 HostAuthColumns._ID + " and " + HostAuthColumns.PROTOCOL + "='" + 1891 LEGACY_SCHEME_EAS + "')"); 1892 } catch (SQLException e) { 1893 LogUtils.w(TAG, "Exception upgrading EmailProvider.db from 35/36 to 37/100 " + e); 1894 } 1895 } 1896 } 1897