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