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