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