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