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