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