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