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