DBHelper.java revision 70bbe5ec3cabfc711cf4486318a04bb18e34ebf3
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.ContactsContract; 28import android.util.Log; 29 30import com.android.email.Email; 31import com.android.emailcommon.AccountManagerTypes; 32import com.android.emailcommon.CalendarProviderStub; 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.mail.providers.UIProvider; 54import com.google.common.annotations.VisibleForTesting; 55 56public final class DBHelper { 57 private static final String TAG = "EmailProvider"; 58 59 private static final String WHERE_ID = EmailContent.RECORD_ID + "=?"; 60 61 private static final String TRIGGER_MAILBOX_DELETE = 62 "create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME + 63 " begin" + 64 " delete from " + Message.TABLE_NAME + 65 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 66 "; delete from " + Message.UPDATED_TABLE_NAME + 67 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 68 "; delete from " + Message.DELETED_TABLE_NAME + 69 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 70 "; end"; 71 72 private static final String TRIGGER_ACCOUNT_DELETE = 73 "create trigger account_delete before delete on " + Account.TABLE_NAME + 74 " begin delete from " + Mailbox.TABLE_NAME + 75 " where " + MailboxColumns.ACCOUNT_KEY + "=old." + EmailContent.RECORD_ID + 76 "; delete from " + HostAuth.TABLE_NAME + 77 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV + 78 "; delete from " + HostAuth.TABLE_NAME + 79 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND + 80 "; delete from " + Policy.TABLE_NAME + 81 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.POLICY_KEY + 82 "; end"; 83 84 // Any changes to the database format *must* include update-in-place code. 85 // Original version: 3 86 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 87 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 88 // Version 6: Adding Message.mServerTimeStamp column 89 // Version 7: Replace the mailbox_delete trigger with a version that removes orphaned messages 90 // from the Message_Deletes and Message_Updates tables 91 // Version 8: Add security flags column to accounts table 92 // Version 9: Add security sync key and signature to accounts table 93 // Version 10: Add meeting info to message table 94 // Version 11: Add content and flags to attachment table 95 // Version 12: Add content_bytes to attachment table. content is deprecated. 96 // Version 13: Add messageCount to Mailbox table. 97 // Version 14: Add snippet to Message table 98 // Version 15: Fix upgrade problem in version 14. 99 // Version 16: Add accountKey to Attachment table 100 // Version 17: Add parentKey to Mailbox table 101 // Version 18: Copy Mailbox.displayName to Mailbox.serverId for all IMAP & POP3 mailboxes. 102 // Column Mailbox.serverId is used for the server-side pathname of a mailbox. 103 // Version 19: Add Policy table; add policyKey to Account table and trigger to delete an 104 // Account's policy when the Account is deleted 105 // Version 20: Add new policies to Policy table 106 // Version 21: Add lastSeenMessageKey column to Mailbox table 107 // Version 22: Upgrade path for IMAP/POP accounts to integrate with AccountManager 108 // Version 23: Add column to mailbox table for time of last access 109 // Version 24: Add column to hostauth table for client cert alias 110 // Version 25: Added QuickResponse table 111 // Version 26: Update IMAP accounts to add FLAG_SUPPORTS_SEARCH flag 112 // Version 27: Add protocolSearchInfo to Message table 113 // Version 28: Add notifiedMessageId and notifiedMessageCount to Account 114 // Version 29: Add protocolPoliciesEnforced and protocolPoliciesUnsupported to Policy 115 // Version 30: Use CSV of RFC822 addresses instead of "packed" values 116 // Version 31: Add columns to mailbox for ui status/last result 117 // Version 32: Add columns to mailbox for last notified message key/count; insure not null 118 // for "notified" columns 119 // Version 33: Add columns to attachment for ui provider columns 120 121 public static final int DATABASE_VERSION = 33; 122 123 // Any changes to the database format *must* include update-in-place code. 124 // Original version: 2 125 // Version 3: Add "sourceKey" column 126 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 127 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 128 // Version 6: Adding Body.mIntroText column 129 public static final int BODY_DATABASE_VERSION = 6; 130 131 /* 132 * Internal helper method for index creation. 133 * Example: 134 * "create index message_" + MessageColumns.FLAG_READ 135 * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");" 136 */ 137 /* package */ 138 static String createIndex(String tableName, String columnName) { 139 return "create index " + tableName.toLowerCase() + '_' + columnName 140 + " on " + tableName + " (" + columnName + ");"; 141 } 142 143 static void createMessageTable(SQLiteDatabase db) { 144 String messageColumns = MessageColumns.DISPLAY_NAME + " text, " 145 + MessageColumns.TIMESTAMP + " integer, " 146 + MessageColumns.SUBJECT + " text, " 147 + MessageColumns.FLAG_READ + " integer, " 148 + MessageColumns.FLAG_LOADED + " integer, " 149 + MessageColumns.FLAG_FAVORITE + " integer, " 150 + MessageColumns.FLAG_ATTACHMENT + " integer, " 151 + MessageColumns.FLAGS + " integer, " 152 + MessageColumns.CLIENT_ID + " integer, " 153 + MessageColumns.MESSAGE_ID + " text, " 154 + MessageColumns.MAILBOX_KEY + " integer, " 155 + MessageColumns.ACCOUNT_KEY + " integer, " 156 + MessageColumns.FROM_LIST + " text, " 157 + MessageColumns.TO_LIST + " text, " 158 + MessageColumns.CC_LIST + " text, " 159 + MessageColumns.BCC_LIST + " text, " 160 + MessageColumns.REPLY_TO_LIST + " text, " 161 + MessageColumns.MEETING_INFO + " text, " 162 + MessageColumns.SNIPPET + " text, " 163 + MessageColumns.PROTOCOL_SEARCH_INFO + " text" 164 + ");"; 165 166 // This String and the following String MUST have the same columns, except for the type 167 // of those columns! 168 String createString = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 169 + SyncColumns.SERVER_ID + " text, " 170 + SyncColumns.SERVER_TIMESTAMP + " integer, " 171 + messageColumns; 172 173 // For the updated and deleted tables, the id is assigned, but we do want to keep track 174 // of the ORDER of updates using an autoincrement primary key. We use the DATA column 175 // at this point; it has no other function 176 String altCreateString = " (" + EmailContent.RECORD_ID + " integer unique, " 177 + SyncColumns.SERVER_ID + " text, " 178 + SyncColumns.SERVER_TIMESTAMP + " integer, " 179 + messageColumns; 180 181 // The three tables have the same schema 182 db.execSQL("create table " + Message.TABLE_NAME + createString); 183 db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString); 184 db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString); 185 186 String indexColumns[] = { 187 MessageColumns.TIMESTAMP, 188 MessageColumns.FLAG_READ, 189 MessageColumns.FLAG_LOADED, 190 MessageColumns.MAILBOX_KEY, 191 SyncColumns.SERVER_ID 192 }; 193 194 for (String columnName : indexColumns) { 195 db.execSQL(createIndex(Message.TABLE_NAME, columnName)); 196 } 197 198 // Deleting a Message deletes all associated Attachments 199 // Deleting the associated Body cannot be done in a trigger, because the Body is stored 200 // in a separate database, and trigger cannot operate on attached databases. 201 db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME + 202 " begin delete from " + Attachment.TABLE_NAME + 203 " where " + AttachmentColumns.MESSAGE_KEY + "=old." + EmailContent.RECORD_ID + 204 "; end"); 205 206 // Add triggers to keep unread count accurate per mailbox 207 208 // NOTE: SQLite's before triggers are not safe when recursive triggers are involved. 209 // Use caution when changing them. 210 211 // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox 212 db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME + 213 " when NEW." + MessageColumns.FLAG_READ + "=0" + 214 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 215 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 216 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 217 "; end"); 218 219 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 220 db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME + 221 " when OLD." + MessageColumns.FLAG_READ + "=0" + 222 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 223 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 224 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 225 "; end"); 226 227 // Change a message's mailbox 228 db.execSQL("create trigger unread_message_move before update of " + 229 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 230 " when OLD." + MessageColumns.FLAG_READ + "=0" + 231 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 232 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 233 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 234 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 235 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 236 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 237 "; end"); 238 239 // Change a message's read state 240 db.execSQL("create trigger unread_message_read before update of " + 241 MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME + 242 " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ + 243 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 244 '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ + 245 " when 0 then -1 else 1 end" + 246 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 247 "; end"); 248 249 // Add triggers to update message count per mailbox 250 251 // Insert a message. 252 db.execSQL("create trigger message_count_message_insert after insert on " + 253 Message.TABLE_NAME + 254 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 255 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 256 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 257 "; end"); 258 259 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 260 db.execSQL("create trigger message_count_message_delete after delete on " + 261 Message.TABLE_NAME + 262 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 263 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 264 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 265 "; end"); 266 267 // Change a message's mailbox 268 db.execSQL("create trigger message_count_message_move after update of " + 269 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 270 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 271 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 272 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 273 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 274 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 275 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 276 "; end"); 277 } 278 279 static void resetMessageTable(SQLiteDatabase db, int oldVersion, int newVersion) { 280 try { 281 db.execSQL("drop table " + Message.TABLE_NAME); 282 db.execSQL("drop table " + Message.UPDATED_TABLE_NAME); 283 db.execSQL("drop table " + Message.DELETED_TABLE_NAME); 284 } catch (SQLException e) { 285 } 286 createMessageTable(db); 287 } 288 289 @SuppressWarnings("deprecation") 290 static void createAccountTable(SQLiteDatabase db) { 291 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 292 + AccountColumns.DISPLAY_NAME + " text, " 293 + AccountColumns.EMAIL_ADDRESS + " text, " 294 + AccountColumns.SYNC_KEY + " text, " 295 + AccountColumns.SYNC_LOOKBACK + " integer, " 296 + AccountColumns.SYNC_INTERVAL + " text, " 297 + AccountColumns.HOST_AUTH_KEY_RECV + " integer, " 298 + AccountColumns.HOST_AUTH_KEY_SEND + " integer, " 299 + AccountColumns.FLAGS + " integer, " 300 + AccountColumns.IS_DEFAULT + " integer, " 301 + AccountColumns.COMPATIBILITY_UUID + " text, " 302 + AccountColumns.SENDER_NAME + " text, " 303 + AccountColumns.RINGTONE_URI + " text, " 304 + AccountColumns.PROTOCOL_VERSION + " text, " 305 + AccountColumns.NEW_MESSAGE_COUNT + " integer, " 306 + AccountColumns.SECURITY_FLAGS + " integer, " 307 + AccountColumns.SECURITY_SYNC_KEY + " text, " 308 + AccountColumns.SIGNATURE + " text, " 309 + AccountColumns.POLICY_KEY + " integer" 310 + ");"; 311 db.execSQL("create table " + Account.TABLE_NAME + s); 312 // Deleting an account deletes associated Mailboxes and HostAuth's 313 db.execSQL(TRIGGER_ACCOUNT_DELETE); 314 } 315 316 static void resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) { 317 try { 318 db.execSQL("drop table " + Account.TABLE_NAME); 319 } catch (SQLException e) { 320 } 321 createAccountTable(db); 322 } 323 324 static void createPolicyTable(SQLiteDatabase db) { 325 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 326 + PolicyColumns.PASSWORD_MODE + " integer, " 327 + PolicyColumns.PASSWORD_MIN_LENGTH + " integer, " 328 + PolicyColumns.PASSWORD_EXPIRATION_DAYS + " integer, " 329 + PolicyColumns.PASSWORD_HISTORY + " integer, " 330 + PolicyColumns.PASSWORD_COMPLEX_CHARS + " integer, " 331 + PolicyColumns.PASSWORD_MAX_FAILS + " integer, " 332 + PolicyColumns.MAX_SCREEN_LOCK_TIME + " integer, " 333 + PolicyColumns.REQUIRE_REMOTE_WIPE + " integer, " 334 + PolicyColumns.REQUIRE_ENCRYPTION + " integer, " 335 + PolicyColumns.REQUIRE_ENCRYPTION_EXTERNAL + " integer, " 336 + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + " integer, " 337 + PolicyColumns.DONT_ALLOW_CAMERA + " integer, " 338 + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer, " 339 + PolicyColumns.DONT_ALLOW_HTML + " integer, " 340 + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer, " 341 + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + " integer, " 342 + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + " integer, " 343 + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer, " 344 + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer, " 345 + PolicyColumns.PASSWORD_RECOVERY_ENABLED + " integer, " 346 + PolicyColumns.PROTOCOL_POLICIES_ENFORCED + " text, " 347 + PolicyColumns.PROTOCOL_POLICIES_UNSUPPORTED + " text" 348 + ");"; 349 db.execSQL("create table " + Policy.TABLE_NAME + s); 350 } 351 352 static void createHostAuthTable(SQLiteDatabase db) { 353 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 354 + HostAuthColumns.PROTOCOL + " text, " 355 + HostAuthColumns.ADDRESS + " text, " 356 + HostAuthColumns.PORT + " integer, " 357 + HostAuthColumns.FLAGS + " integer, " 358 + HostAuthColumns.LOGIN + " text, " 359 + HostAuthColumns.PASSWORD + " text, " 360 + HostAuthColumns.DOMAIN + " text, " 361 + HostAuthColumns.ACCOUNT_KEY + " integer," 362 + HostAuthColumns.CLIENT_CERT_ALIAS + " text" 363 + ");"; 364 db.execSQL("create table " + HostAuth.TABLE_NAME + s); 365 } 366 367 static void resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) { 368 try { 369 db.execSQL("drop table " + HostAuth.TABLE_NAME); 370 } catch (SQLException e) { 371 } 372 createHostAuthTable(db); 373 } 374 375 static void createMailboxTable(SQLiteDatabase db) { 376 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 377 + MailboxColumns.DISPLAY_NAME + " text, " 378 + MailboxColumns.SERVER_ID + " text, " 379 + MailboxColumns.PARENT_SERVER_ID + " text, " 380 + MailboxColumns.PARENT_KEY + " integer, " 381 + MailboxColumns.ACCOUNT_KEY + " integer, " 382 + MailboxColumns.TYPE + " integer, " 383 + MailboxColumns.DELIMITER + " integer, " 384 + MailboxColumns.SYNC_KEY + " text, " 385 + MailboxColumns.SYNC_LOOKBACK + " integer, " 386 + MailboxColumns.SYNC_INTERVAL + " integer, " 387 + MailboxColumns.SYNC_TIME + " integer, " 388 + MailboxColumns.UNREAD_COUNT + " integer, " 389 + MailboxColumns.FLAG_VISIBLE + " integer, " 390 + MailboxColumns.FLAGS + " integer, " 391 + MailboxColumns.VISIBLE_LIMIT + " integer, " 392 + MailboxColumns.SYNC_STATUS + " text, " 393 + MailboxColumns.MESSAGE_COUNT + " integer not null default 0, " 394 + MailboxColumns.LAST_TOUCHED_TIME + " integer default 0, " 395 + MailboxColumns.UI_SYNC_STATUS + " integer default 0, " 396 + MailboxColumns.UI_LAST_SYNC_RESULT + " integer default 0, " 397 + MailboxColumns.LAST_NOTIFIED_MESSAGE_KEY + " integer not null default 0, " 398 + MailboxColumns.LAST_NOTIFIED_MESSAGE_COUNT + " integer not null default 0" 399 + ");"; 400 db.execSQL("create table " + Mailbox.TABLE_NAME + s); 401 db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID 402 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")"); 403 db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY 404 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")"); 405 // Deleting a Mailbox deletes associated Messages in all three tables 406 db.execSQL(TRIGGER_MAILBOX_DELETE); 407 } 408 409 static void resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) { 410 try { 411 db.execSQL("drop table " + Mailbox.TABLE_NAME); 412 } catch (SQLException e) { 413 } 414 createMailboxTable(db); 415 } 416 417 static void createAttachmentTable(SQLiteDatabase db) { 418 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 419 + AttachmentColumns.FILENAME + " text, " 420 + AttachmentColumns.MIME_TYPE + " text, " 421 + AttachmentColumns.SIZE + " integer, " 422 + AttachmentColumns.CONTENT_ID + " text, " 423 + AttachmentColumns.CONTENT_URI + " text, " 424 + AttachmentColumns.MESSAGE_KEY + " integer, " 425 + AttachmentColumns.LOCATION + " text, " 426 + AttachmentColumns.ENCODING + " text, " 427 + AttachmentColumns.CONTENT + " text, " 428 + AttachmentColumns.FLAGS + " integer, " 429 + AttachmentColumns.CONTENT_BYTES + " blob, " 430 + AttachmentColumns.ACCOUNT_KEY + " integer, " 431 + AttachmentColumns.UI_STATE + " integer, " 432 + AttachmentColumns.UI_DESTINATION + " integer, " 433 + AttachmentColumns.UI_DOWNLOADED_SIZE + " integer" 434 + ");"; 435 db.execSQL("create table " + Attachment.TABLE_NAME + s); 436 db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY)); 437 } 438 439 static void resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) { 440 try { 441 db.execSQL("drop table " + Attachment.TABLE_NAME); 442 } catch (SQLException e) { 443 } 444 createAttachmentTable(db); 445 } 446 447 static void createQuickResponseTable(SQLiteDatabase db) { 448 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 449 + QuickResponseColumns.TEXT + " text, " 450 + QuickResponseColumns.ACCOUNT_KEY + " integer" 451 + ");"; 452 db.execSQL("create table " + QuickResponse.TABLE_NAME + s); 453 } 454 455 static void createBodyTable(SQLiteDatabase db) { 456 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 457 + BodyColumns.MESSAGE_KEY + " integer, " 458 + BodyColumns.HTML_CONTENT + " text, " 459 + BodyColumns.TEXT_CONTENT + " text, " 460 + BodyColumns.HTML_REPLY + " text, " 461 + BodyColumns.TEXT_REPLY + " text, " 462 + BodyColumns.SOURCE_MESSAGE_KEY + " text, " 463 + BodyColumns.INTRO_TEXT + " text" 464 + ");"; 465 db.execSQL("create table " + Body.TABLE_NAME + s); 466 db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY)); 467 } 468 469 static void upgradeBodyTable(SQLiteDatabase db, int oldVersion, int newVersion) { 470 if (oldVersion < 5) { 471 try { 472 db.execSQL("drop table " + Body.TABLE_NAME); 473 createBodyTable(db); 474 } catch (SQLException e) { 475 } 476 } else if (oldVersion == 5) { 477 try { 478 db.execSQL("alter table " + Body.TABLE_NAME 479 + " add " + BodyColumns.INTRO_TEXT + " text"); 480 } catch (SQLException e) { 481 // Shouldn't be needed unless we're debugging and interrupt the process 482 Log.w(TAG, "Exception upgrading EmailProviderBody.db from v5 to v6", e); 483 } 484 oldVersion = 6; 485 } 486 } 487 488 protected static class BodyDatabaseHelper extends SQLiteOpenHelper { 489 BodyDatabaseHelper(Context context, String name) { 490 super(context, name, null, BODY_DATABASE_VERSION); 491 } 492 493 @Override 494 public void onCreate(SQLiteDatabase db) { 495 Log.d(TAG, "Creating EmailProviderBody database"); 496 createBodyTable(db); 497 } 498 499 @Override 500 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 501 upgradeBodyTable(db, oldVersion, newVersion); 502 } 503 504 @Override 505 public void onOpen(SQLiteDatabase db) { 506 } 507 } 508 509 /** Counts the number of messages in each mailbox, and updates the message count column. */ 510 @VisibleForTesting 511 static void recalculateMessageCount(SQLiteDatabase db) { 512 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 513 "= (select count(*) from " + Message.TABLE_NAME + 514 " where " + Message.MAILBOX_KEY + " = " + 515 Mailbox.TABLE_NAME + "." + EmailContent.RECORD_ID + ")"); 516 } 517 518 protected static class DatabaseHelper extends SQLiteOpenHelper { 519 Context mContext; 520 521 DatabaseHelper(Context context, String name) { 522 super(context, name, null, DATABASE_VERSION); 523 mContext = context; 524 } 525 526 @Override 527 public void onCreate(SQLiteDatabase db) { 528 Log.d(TAG, "Creating EmailProvider database"); 529 // Create all tables here; each class has its own method 530 createMessageTable(db); 531 createAttachmentTable(db); 532 createMailboxTable(db); 533 createHostAuthTable(db); 534 createAccountTable(db); 535 createPolicyTable(db); 536 createQuickResponseTable(db); 537 } 538 539 @Override 540 @SuppressWarnings("deprecation") 541 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 542 // For versions prior to 5, delete all data 543 // Versions >= 5 require that data be preserved! 544 if (oldVersion < 5) { 545 android.accounts.Account[] accounts = AccountManager.get(mContext) 546 .getAccountsByType(AccountManagerTypes.TYPE_EXCHANGE); 547 for (android.accounts.Account account: accounts) { 548 AccountManager.get(mContext).removeAccount(account, null, null); 549 } 550 resetMessageTable(db, oldVersion, newVersion); 551 resetAttachmentTable(db, oldVersion, newVersion); 552 resetMailboxTable(db, oldVersion, newVersion); 553 resetHostAuthTable(db, oldVersion, newVersion); 554 resetAccountTable(db, oldVersion, newVersion); 555 return; 556 } 557 if (oldVersion == 5) { 558 // Message Tables: Add SyncColumns.SERVER_TIMESTAMP 559 try { 560 db.execSQL("alter table " + Message.TABLE_NAME 561 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 562 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 563 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 564 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 565 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 566 } catch (SQLException e) { 567 // Shouldn't be needed unless we're debugging and interrupt the process 568 Log.w(TAG, "Exception upgrading EmailProvider.db from v5 to v6", e); 569 } 570 oldVersion = 6; 571 } 572 if (oldVersion == 6) { 573 // Use the newer mailbox_delete trigger 574 db.execSQL("drop trigger mailbox_delete;"); 575 db.execSQL(TRIGGER_MAILBOX_DELETE); 576 oldVersion = 7; 577 } 578 if (oldVersion == 7) { 579 // add the security (provisioning) column 580 try { 581 db.execSQL("alter table " + Account.TABLE_NAME 582 + " add column " + AccountColumns.SECURITY_FLAGS + " integer" + ";"); 583 } catch (SQLException e) { 584 // Shouldn't be needed unless we're debugging and interrupt the process 585 Log.w(TAG, "Exception upgrading EmailProvider.db from 7 to 8 " + e); 586 } 587 oldVersion = 8; 588 } 589 if (oldVersion == 8) { 590 // accounts: add security sync key & user signature columns 591 try { 592 db.execSQL("alter table " + Account.TABLE_NAME 593 + " add column " + AccountColumns.SECURITY_SYNC_KEY + " text" + ";"); 594 db.execSQL("alter table " + Account.TABLE_NAME 595 + " add column " + AccountColumns.SIGNATURE + " text" + ";"); 596 } catch (SQLException e) { 597 // Shouldn't be needed unless we're debugging and interrupt the process 598 Log.w(TAG, "Exception upgrading EmailProvider.db from 8 to 9 " + e); 599 } 600 oldVersion = 9; 601 } 602 if (oldVersion == 9) { 603 // Message: add meeting info column into Message tables 604 try { 605 db.execSQL("alter table " + Message.TABLE_NAME 606 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 607 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 608 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 609 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 610 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 611 } catch (SQLException e) { 612 // Shouldn't be needed unless we're debugging and interrupt the process 613 Log.w(TAG, "Exception upgrading EmailProvider.db from 9 to 10 " + e); 614 } 615 oldVersion = 10; 616 } 617 if (oldVersion == 10) { 618 // Attachment: add content and flags columns 619 try { 620 db.execSQL("alter table " + Attachment.TABLE_NAME 621 + " add column " + AttachmentColumns.CONTENT + " text" + ";"); 622 db.execSQL("alter table " + Attachment.TABLE_NAME 623 + " add column " + AttachmentColumns.FLAGS + " 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 10 to 11 " + e); 627 } 628 oldVersion = 11; 629 } 630 if (oldVersion == 11) { 631 // Attachment: add content_bytes 632 try { 633 db.execSQL("alter table " + Attachment.TABLE_NAME 634 + " add column " + AttachmentColumns.CONTENT_BYTES + " blob" + ";"); 635 } catch (SQLException e) { 636 // Shouldn't be needed unless we're debugging and interrupt the process 637 Log.w(TAG, "Exception upgrading EmailProvider.db from 11 to 12 " + e); 638 } 639 oldVersion = 12; 640 } 641 if (oldVersion == 12) { 642 try { 643 db.execSQL("alter table " + Mailbox.TABLE_NAME 644 + " add column " + Mailbox.MESSAGE_COUNT 645 +" integer not null default 0" + ";"); 646 recalculateMessageCount(db); 647 } catch (SQLException e) { 648 // Shouldn't be needed unless we're debugging and interrupt the process 649 Log.w(TAG, "Exception upgrading EmailProvider.db from 12 to 13 " + e); 650 } 651 oldVersion = 13; 652 } 653 if (oldVersion == 13) { 654 try { 655 db.execSQL("alter table " + Message.TABLE_NAME 656 + " add column " + Message.SNIPPET 657 +" text" + ";"); 658 } catch (SQLException e) { 659 // Shouldn't be needed unless we're debugging and interrupt the process 660 Log.w(TAG, "Exception upgrading EmailProvider.db from 13 to 14 " + e); 661 } 662 oldVersion = 14; 663 } 664 if (oldVersion == 14) { 665 try { 666 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 667 + " add column " + Message.SNIPPET +" text" + ";"); 668 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 669 + " add column " + Message.SNIPPET +" text" + ";"); 670 } catch (SQLException e) { 671 // Shouldn't be needed unless we're debugging and interrupt the process 672 Log.w(TAG, "Exception upgrading EmailProvider.db from 14 to 15 " + e); 673 } 674 oldVersion = 15; 675 } 676 if (oldVersion == 15) { 677 try { 678 db.execSQL("alter table " + Attachment.TABLE_NAME 679 + " add column " + Attachment.ACCOUNT_KEY +" integer" + ";"); 680 // Update all existing attachments to add the accountKey data 681 db.execSQL("update " + Attachment.TABLE_NAME + " set " + 682 Attachment.ACCOUNT_KEY + "= (SELECT " + Message.TABLE_NAME + "." + 683 Message.ACCOUNT_KEY + " from " + Message.TABLE_NAME + " where " + 684 Message.TABLE_NAME + "." + Message.RECORD_ID + " = " + 685 Attachment.TABLE_NAME + "." + Attachment.MESSAGE_KEY + ")"); 686 } catch (SQLException e) { 687 // Shouldn't be needed unless we're debugging and interrupt the process 688 Log.w(TAG, "Exception upgrading EmailProvider.db from 15 to 16 " + e); 689 } 690 oldVersion = 16; 691 } 692 if (oldVersion == 16) { 693 try { 694 db.execSQL("alter table " + Mailbox.TABLE_NAME 695 + " add column " + Mailbox.PARENT_KEY + " integer;"); 696 } catch (SQLException e) { 697 // Shouldn't be needed unless we're debugging and interrupt the process 698 Log.w(TAG, "Exception upgrading EmailProvider.db from 16 to 17 " + e); 699 } 700 oldVersion = 17; 701 } 702 if (oldVersion == 17) { 703 upgradeFromVersion17ToVersion18(db); 704 oldVersion = 18; 705 } 706 if (oldVersion == 18) { 707 try { 708 db.execSQL("alter table " + Account.TABLE_NAME 709 + " add column " + Account.POLICY_KEY + " integer;"); 710 db.execSQL("drop trigger account_delete;"); 711 db.execSQL(TRIGGER_ACCOUNT_DELETE); 712 createPolicyTable(db); 713 convertPolicyFlagsToPolicyTable(db); 714 } catch (SQLException e) { 715 // Shouldn't be needed unless we're debugging and interrupt the process 716 Log.w(TAG, "Exception upgrading EmailProvider.db from 18 to 19 " + e); 717 } 718 oldVersion = 19; 719 } 720 if (oldVersion == 19) { 721 try { 722 db.execSQL("alter table " + Policy.TABLE_NAME 723 + " add column " + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + 724 " integer;"); 725 db.execSQL("alter table " + Policy.TABLE_NAME 726 + " add column " + PolicyColumns.DONT_ALLOW_CAMERA + " integer;"); 727 db.execSQL("alter table " + Policy.TABLE_NAME 728 + " add column " + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer;"); 729 db.execSQL("alter table " + Policy.TABLE_NAME 730 + " add column " + PolicyColumns.DONT_ALLOW_HTML + " integer;"); 731 db.execSQL("alter table " + Policy.TABLE_NAME 732 + " add column " + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer;"); 733 db.execSQL("alter table " + Policy.TABLE_NAME 734 + " add column " + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + 735 " integer;"); 736 db.execSQL("alter table " + Policy.TABLE_NAME 737 + " add column " + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + 738 " integer;"); 739 db.execSQL("alter table " + Policy.TABLE_NAME 740 + " add column " + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer;"); 741 db.execSQL("alter table " + Policy.TABLE_NAME 742 + " add column " + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer;"); 743 db.execSQL("alter table " + Policy.TABLE_NAME 744 + " add column " + PolicyColumns.PASSWORD_RECOVERY_ENABLED + 745 " integer;"); 746 } catch (SQLException e) { 747 // Shouldn't be needed unless we're debugging and interrupt the process 748 Log.w(TAG, "Exception upgrading EmailProvider.db from 19 to 20 " + e); 749 } 750 oldVersion = 20; 751 } 752 if (oldVersion == 20) { 753 oldVersion = 21; 754 } 755 if (oldVersion == 21) { 756 upgradeFromVersion21ToVersion22(db, mContext); 757 oldVersion = 22; 758 } 759 if (oldVersion == 22) { 760 upgradeFromVersion22ToVersion23(db); 761 oldVersion = 23; 762 } 763 if (oldVersion == 23) { 764 upgradeFromVersion23ToVersion24(db); 765 oldVersion = 24; 766 } 767 if (oldVersion == 24) { 768 upgradeFromVersion24ToVersion25(db); 769 oldVersion = 25; 770 } 771 if (oldVersion == 25) { 772 upgradeFromVersion25ToVersion26(db); 773 oldVersion = 26; 774 } 775 if (oldVersion == 26) { 776 try { 777 db.execSQL("alter table " + Message.TABLE_NAME 778 + " add column " + Message.PROTOCOL_SEARCH_INFO + " text;"); 779 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 780 + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";"); 781 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 782 + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";"); 783 } catch (SQLException e) { 784 // Shouldn't be needed unless we're debugging and interrupt the process 785 Log.w(TAG, "Exception upgrading EmailProvider.db from 26 to 27 " + e); 786 } 787 oldVersion = 27; 788 } 789 if (oldVersion == 27) { 790 oldVersion = 28; 791 } 792 if (oldVersion == 28) { 793 try { 794 db.execSQL("alter table " + Policy.TABLE_NAME 795 + " add column " + Policy.PROTOCOL_POLICIES_ENFORCED + " text;"); 796 db.execSQL("alter table " + Policy.TABLE_NAME 797 + " add column " + Policy.PROTOCOL_POLICIES_UNSUPPORTED + " text;"); 798 } catch (SQLException e) { 799 // Shouldn't be needed unless we're debugging and interrupt the process 800 Log.w(TAG, "Exception upgrading EmailProvider.db from 28 to 29 " + e); 801 } 802 oldVersion = 29; 803 } 804 if (oldVersion == 29) { 805 upgradeFromVersion29ToVersion30(db); 806 oldVersion = 30; 807 } 808 if (oldVersion == 30) { 809 try { 810 db.execSQL("alter table " + Mailbox.TABLE_NAME 811 + " add column " + Mailbox.UI_SYNC_STATUS + " integer;"); 812 db.execSQL("alter table " + Mailbox.TABLE_NAME 813 + " add column " + Mailbox.UI_LAST_SYNC_RESULT + " integer;"); 814 } catch (SQLException e) { 815 // Shouldn't be needed unless we're debugging and interrupt the process 816 Log.w(TAG, "Exception upgrading EmailProvider.db from 30 to 31 " + e); 817 } 818 oldVersion = 31; 819 } 820 if (oldVersion == 31) { 821 try { 822 db.execSQL("alter table " + Mailbox.TABLE_NAME 823 + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " integer;"); 824 db.execSQL("alter table " + Mailbox.TABLE_NAME 825 + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " integer;"); 826 db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + 827 "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " IS NULL"); 828 db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + 829 "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " IS NULL"); 830 } catch (SQLException e) { 831 // Shouldn't be needed unless we're debugging and interrupt the process 832 Log.w(TAG, "Exception upgrading EmailProvider.db from 31 to 32 " + e); 833 } 834 oldVersion = 32; 835 } 836 if (oldVersion == 32) { 837 try { 838 db.execSQL("alter table " + Attachment.TABLE_NAME 839 + " add column " + Attachment.UI_STATE + " integer;"); 840 db.execSQL("alter table " + Attachment.TABLE_NAME 841 + " add column " + Attachment.UI_DESTINATION + " integer;"); 842 db.execSQL("alter table " + Attachment.TABLE_NAME 843 + " add column " + Attachment.UI_DOWNLOADED_SIZE + " integer;"); 844 // If we have a contentUri then the attachment is saved 845 // uiDestination of 0 = "cache", so we don't have to set this 846 db.execSQL("update " + Attachment.TABLE_NAME + " set " + Attachment.UI_STATE + 847 "=" + UIProvider.AttachmentState.SAVED + " where " + 848 AttachmentColumns.CONTENT_URI + " is not null;"); 849 } catch (SQLException e) { 850 // Shouldn't be needed unless we're debugging and interrupt the process 851 Log.w(TAG, "Exception upgrading EmailProvider.db from 32 to 33 " + e); 852 } 853 oldVersion = 33; 854 } 855 } 856 857 @Override 858 public void onOpen(SQLiteDatabase db) { 859 } 860 } 861 862 @VisibleForTesting 863 @SuppressWarnings("deprecation") 864 static void convertPolicyFlagsToPolicyTable(SQLiteDatabase db) { 865 Cursor c = db.query(Account.TABLE_NAME, 866 new String[] {EmailContent.RECORD_ID /*0*/, AccountColumns.SECURITY_FLAGS /*1*/}, 867 AccountColumns.SECURITY_FLAGS + ">0", null, null, null, null); 868 ContentValues cv = new ContentValues(); 869 String[] args = new String[1]; 870 while (c.moveToNext()) { 871 long securityFlags = c.getLong(1 /*SECURITY_FLAGS*/); 872 Policy policy = LegacyPolicySet.flagsToPolicy(securityFlags); 873 long policyId = db.insert(Policy.TABLE_NAME, null, policy.toContentValues()); 874 cv.put(AccountColumns.POLICY_KEY, policyId); 875 cv.putNull(AccountColumns.SECURITY_FLAGS); 876 args[0] = Long.toString(c.getLong(0 /*RECORD_ID*/)); 877 db.update(Account.TABLE_NAME, cv, EmailContent.RECORD_ID + "=?", args); 878 } 879 } 880 881 /** Upgrades the database from v17 to v18 */ 882 @VisibleForTesting 883 static void upgradeFromVersion17ToVersion18(SQLiteDatabase db) { 884 // Copy the displayName column to the serverId column. In v18 of the database, 885 // we use the serverId for IMAP/POP3 mailboxes instead of overloading the 886 // display name. 887 // 888 // For posterity; this is the command we're executing: 889 //sqlite> UPDATE mailbox SET serverid=displayname WHERE mailbox._id in ( 890 // ...> SELECT mailbox._id FROM mailbox,account,hostauth WHERE 891 // ...> (mailbox.parentkey isnull OR mailbox.parentkey=0) AND 892 // ...> mailbox.accountkey=account._id AND 893 // ...> account.hostauthkeyrecv=hostauth._id AND 894 // ...> (hostauth.protocol='imap' OR hostauth.protocol='pop3')); 895 try { 896 db.execSQL( 897 "UPDATE " + Mailbox.TABLE_NAME + " SET " 898 + MailboxColumns.SERVER_ID + "=" + MailboxColumns.DISPLAY_NAME 899 + " WHERE " 900 + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " IN ( SELECT " 901 + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " FROM " 902 + Mailbox.TABLE_NAME + "," + Account.TABLE_NAME + "," 903 + HostAuth.TABLE_NAME + " WHERE " 904 + "(" 905 + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + " isnull OR " 906 + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + "=0 " 907 + ") AND " 908 + Mailbox.TABLE_NAME + "." + MailboxColumns.ACCOUNT_KEY + "=" 909 + Account.TABLE_NAME + "." + AccountColumns.ID + " AND " 910 + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV + "=" 911 + HostAuth.TABLE_NAME + "." + HostAuthColumns.ID + " AND ( " 912 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap' OR " 913 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='pop3' ) )"); 914 } catch (SQLException e) { 915 // Shouldn't be needed unless we're debugging and interrupt the process 916 Log.w(TAG, "Exception upgrading EmailProvider.db from 17 to 18 " + e); 917 } 918 ContentCache.invalidateAllCaches(); 919 } 920 921 /** 922 * Upgrade the database from v21 to v22 923 * This entails creating AccountManager accounts for all pop3 and imap accounts 924 */ 925 926 private static final String[] V21_ACCOUNT_PROJECTION = 927 new String[] {AccountColumns.HOST_AUTH_KEY_RECV, AccountColumns.EMAIL_ADDRESS}; 928 private static final int V21_ACCOUNT_RECV = 0; 929 private static final int V21_ACCOUNT_EMAIL = 1; 930 931 private static final String[] V21_HOSTAUTH_PROJECTION = 932 new String[] {HostAuthColumns.PROTOCOL, HostAuthColumns.PASSWORD}; 933 private static final int V21_HOSTAUTH_PROTOCOL = 0; 934 private static final int V21_HOSTAUTH_PASSWORD = 1; 935 936 static private void createAccountManagerAccount(Context context, String login, 937 String password) { 938 AccountManager accountManager = AccountManager.get(context); 939 android.accounts.Account amAccount = 940 new android.accounts.Account(login, AccountManagerTypes.TYPE_POP_IMAP); 941 accountManager.addAccountExplicitly(amAccount, password, null); 942 ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 943 ContentResolver.setSyncAutomatically(amAccount, EmailContent.AUTHORITY, true); 944 ContentResolver.setIsSyncable(amAccount, ContactsContract.AUTHORITY, 0); 945 ContentResolver.setIsSyncable(amAccount, CalendarProviderStub.AUTHORITY, 0); 946 } 947 948 @VisibleForTesting 949 static void upgradeFromVersion21ToVersion22(SQLiteDatabase db, Context accountManagerContext) { 950 try { 951 // Loop through accounts, looking for pop/imap accounts 952 Cursor accountCursor = db.query(Account.TABLE_NAME, V21_ACCOUNT_PROJECTION, null, 953 null, null, null, null); 954 try { 955 String[] hostAuthArgs = new String[1]; 956 while (accountCursor.moveToNext()) { 957 hostAuthArgs[0] = accountCursor.getString(V21_ACCOUNT_RECV); 958 // Get the "receive" HostAuth for this account 959 Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 960 V21_HOSTAUTH_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs, 961 null, null, null); 962 try { 963 if (hostAuthCursor.moveToFirst()) { 964 String protocol = hostAuthCursor.getString(V21_HOSTAUTH_PROTOCOL); 965 // If this is a pop3 or imap account, create the account manager account 966 if (HostAuth.SCHEME_IMAP.equals(protocol) || 967 HostAuth.SCHEME_POP3.equals(protocol)) { 968 if (Email.DEBUG) { 969 Log.d(TAG, "Create AccountManager account for " + protocol + 970 "account: " + 971 accountCursor.getString(V21_ACCOUNT_EMAIL)); 972 } 973 createAccountManagerAccount(accountManagerContext, 974 accountCursor.getString(V21_ACCOUNT_EMAIL), 975 hostAuthCursor.getString(V21_HOSTAUTH_PASSWORD)); 976 // If an EAS account, make Email sync automatically (equivalent of 977 // checking the "Sync Email" box in settings 978 } else if (HostAuth.SCHEME_EAS.equals(protocol)) { 979 android.accounts.Account amAccount = 980 new android.accounts.Account( 981 accountCursor.getString(V21_ACCOUNT_EMAIL), 982 AccountManagerTypes.TYPE_EXCHANGE); 983 ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 984 ContentResolver.setSyncAutomatically(amAccount, 985 EmailContent.AUTHORITY, true); 986 987 } 988 } 989 } finally { 990 hostAuthCursor.close(); 991 } 992 } 993 } finally { 994 accountCursor.close(); 995 } 996 } catch (SQLException e) { 997 // Shouldn't be needed unless we're debugging and interrupt the process 998 Log.w(TAG, "Exception upgrading EmailProvider.db from 20 to 21 " + e); 999 } 1000 } 1001 1002 /** Upgrades the database from v22 to v23 */ 1003 private static void upgradeFromVersion22ToVersion23(SQLiteDatabase db) { 1004 try { 1005 db.execSQL("alter table " + Mailbox.TABLE_NAME 1006 + " add column " + Mailbox.LAST_TOUCHED_TIME + " integer default 0;"); 1007 } catch (SQLException e) { 1008 // Shouldn't be needed unless we're debugging and interrupt the process 1009 Log.w(TAG, "Exception upgrading EmailProvider.db from 22 to 23 " + e); 1010 } 1011 } 1012 1013 /** Adds in a column for information about a client certificate to use. */ 1014 private static void upgradeFromVersion23ToVersion24(SQLiteDatabase db) { 1015 try { 1016 db.execSQL("alter table " + HostAuth.TABLE_NAME 1017 + " add column " + HostAuth.CLIENT_CERT_ALIAS + " text;"); 1018 } catch (SQLException e) { 1019 // Shouldn't be needed unless we're debugging and interrupt the process 1020 Log.w(TAG, "Exception upgrading EmailProvider.db from 23 to 24 " + e); 1021 } 1022 } 1023 1024 /** Upgrades the database from v24 to v25 by creating table for quick responses */ 1025 private static void upgradeFromVersion24ToVersion25(SQLiteDatabase db) { 1026 try { 1027 createQuickResponseTable(db); 1028 } catch (SQLException e) { 1029 // Shouldn't be needed unless we're debugging and interrupt the process 1030 Log.w(TAG, "Exception upgrading EmailProvider.db from 24 to 25 " + e); 1031 } 1032 } 1033 1034 private static final String[] V25_ACCOUNT_PROJECTION = 1035 new String[] {AccountColumns.ID, AccountColumns.FLAGS, AccountColumns.HOST_AUTH_KEY_RECV}; 1036 private static final int V25_ACCOUNT_ID = 0; 1037 private static final int V25_ACCOUNT_FLAGS = 1; 1038 private static final int V25_ACCOUNT_RECV = 2; 1039 1040 private static final String[] V25_HOSTAUTH_PROJECTION = new String[] {HostAuthColumns.PROTOCOL}; 1041 private static final int V25_HOSTAUTH_PROTOCOL = 0; 1042 1043 /** Upgrades the database from v25 to v26 by adding FLAG_SUPPORTS_SEARCH to IMAP accounts */ 1044 private static void upgradeFromVersion25ToVersion26(SQLiteDatabase db) { 1045 try { 1046 // Loop through accounts, looking for imap accounts 1047 Cursor accountCursor = db.query(Account.TABLE_NAME, V25_ACCOUNT_PROJECTION, null, 1048 null, null, null, null); 1049 ContentValues cv = new ContentValues(); 1050 try { 1051 String[] hostAuthArgs = new String[1]; 1052 while (accountCursor.moveToNext()) { 1053 hostAuthArgs[0] = accountCursor.getString(V25_ACCOUNT_RECV); 1054 // Get the "receive" HostAuth for this account 1055 Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 1056 V25_HOSTAUTH_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs, 1057 null, null, null); 1058 try { 1059 if (hostAuthCursor.moveToFirst()) { 1060 String protocol = hostAuthCursor.getString(V25_HOSTAUTH_PROTOCOL); 1061 // If this is an imap account, add the search flag 1062 if (HostAuth.SCHEME_IMAP.equals(protocol)) { 1063 String id = accountCursor.getString(V25_ACCOUNT_ID); 1064 int flags = accountCursor.getInt(V25_ACCOUNT_FLAGS); 1065 cv.put(AccountColumns.FLAGS, flags | Account.FLAGS_SUPPORTS_SEARCH); 1066 db.update(Account.TABLE_NAME, cv, Account.RECORD_ID + "=?", 1067 new String[] {id}); 1068 } 1069 } 1070 } finally { 1071 hostAuthCursor.close(); 1072 } 1073 } 1074 } finally { 1075 accountCursor.close(); 1076 } 1077 } catch (SQLException e) { 1078 // Shouldn't be needed unless we're debugging and interrupt the process 1079 Log.w(TAG, "Exception upgrading EmailProvider.db from 25 to 26 " + e); 1080 } 1081 } 1082 1083 /** Upgrades the database from v29 to v30 by updating all address fields in Message */ 1084 private static final int[] ADDRESS_COLUMN_INDICES = new int[] { 1085 Message.CONTENT_BCC_LIST_COLUMN, Message.CONTENT_CC_LIST_COLUMN, 1086 Message.CONTENT_FROM_LIST_COLUMN, Message.CONTENT_REPLY_TO_COLUMN, 1087 Message.CONTENT_TO_LIST_COLUMN 1088 }; 1089 private static final String[] ADDRESS_COLUMN_NAMES = new String[] { 1090 Message.BCC_LIST, Message.CC_LIST, Message.FROM_LIST, Message.REPLY_TO_LIST, Message.TO_LIST 1091 }; 1092 1093 private static void upgradeFromVersion29ToVersion30(SQLiteDatabase db) { 1094 try { 1095 // Loop through all messages, updating address columns to new format (CSV, RFC822) 1096 Cursor messageCursor = db.query(Message.TABLE_NAME, Message.CONTENT_PROJECTION, null, 1097 null, null, null, null); 1098 ContentValues cv = new ContentValues(); 1099 String[] whereArgs = new String[1]; 1100 try { 1101 while (messageCursor.moveToNext()) { 1102 for (int i = 0; i < ADDRESS_COLUMN_INDICES.length; i++) { 1103 Address[] addrs = 1104 Address.unpack(messageCursor.getString(ADDRESS_COLUMN_INDICES[i])); 1105 cv.put(ADDRESS_COLUMN_NAMES[i], Address.pack(addrs)); 1106 } 1107 whereArgs[0] = messageCursor.getString(Message.CONTENT_ID_COLUMN); 1108 db.update(Message.TABLE_NAME, cv, WHERE_ID, whereArgs); 1109 } 1110 } finally { 1111 messageCursor.close(); 1112 } 1113 } catch (SQLException e) { 1114 // Shouldn't be needed unless we're debugging and interrupt the process 1115 Log.w(TAG, "Exception upgrading EmailProvider.db from 29 to 30 " + e); 1116 } 1117 } 1118 1119} 1120