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