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