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