EmailProvider.java revision 4de538be2d17545fb63e781412b8565f0d0d97d4
1/* 2 * Copyright (C) 2009 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.ContentProvider; 21import android.content.ContentProviderOperation; 22import android.content.ContentProviderResult; 23import android.content.ContentResolver; 24import android.content.ContentUris; 25import android.content.ContentValues; 26import android.content.Context; 27import android.content.OperationApplicationException; 28import android.content.UriMatcher; 29import android.database.ContentObserver; 30import android.database.Cursor; 31import android.database.MatrixCursor; 32import android.database.SQLException; 33import android.database.sqlite.SQLiteDatabase; 34import android.database.sqlite.SQLiteException; 35import android.database.sqlite.SQLiteOpenHelper; 36import android.net.Uri; 37import android.provider.ContactsContract; 38import android.text.TextUtils; 39import android.util.Log; 40 41import com.android.email.Email; 42import com.android.email.Preferences; 43import com.android.email.provider.ContentCache.CacheToken; 44import com.android.email.service.AttachmentDownloadService; 45import com.android.emailcommon.AccountManagerTypes; 46import com.android.emailcommon.CalendarProviderStub; 47import com.android.emailcommon.Logging; 48import com.android.emailcommon.provider.Account; 49import com.android.emailcommon.provider.EmailContent; 50import com.android.emailcommon.provider.EmailContent.AccountColumns; 51import com.android.emailcommon.provider.EmailContent.Attachment; 52import com.android.emailcommon.provider.EmailContent.AttachmentColumns; 53import com.android.emailcommon.provider.EmailContent.Body; 54import com.android.emailcommon.provider.EmailContent.BodyColumns; 55import com.android.emailcommon.provider.EmailContent.HostAuthColumns; 56import com.android.emailcommon.provider.EmailContent.MailboxColumns; 57import com.android.emailcommon.provider.EmailContent.Message; 58import com.android.emailcommon.provider.EmailContent.MessageColumns; 59import com.android.emailcommon.provider.EmailContent.PolicyColumns; 60import com.android.emailcommon.provider.EmailContent.QuickResponseColumns; 61import com.android.emailcommon.provider.EmailContent.SyncColumns; 62import com.android.emailcommon.provider.HostAuth; 63import com.android.emailcommon.provider.Mailbox; 64import com.android.emailcommon.provider.Policy; 65import com.android.emailcommon.provider.QuickResponse; 66import com.android.emailcommon.service.LegacyPolicySet; 67import com.google.common.annotations.VisibleForTesting; 68 69import java.io.File; 70import java.util.ArrayList; 71import java.util.Arrays; 72import java.util.Collection; 73import java.util.HashMap; 74import java.util.List; 75import java.util.Map; 76 77public class EmailProvider extends ContentProvider { 78 79 private static final String TAG = "EmailProvider"; 80 81 protected static final String DATABASE_NAME = "EmailProvider.db"; 82 protected static final String BODY_DATABASE_NAME = "EmailProviderBody.db"; 83 protected static final String BACKUP_DATABASE_NAME = "EmailProviderBackup.db"; 84 85 public static final String ACTION_ATTACHMENT_UPDATED = "com.android.email.ATTACHMENT_UPDATED"; 86 public static final String ATTACHMENT_UPDATED_EXTRA_FLAGS = 87 "com.android.email.ATTACHMENT_UPDATED_FLAGS"; 88 89 public static final String EMAIL_MESSAGE_MIME_TYPE = 90 "vnd.android.cursor.item/email-message"; 91 public static final String EMAIL_ATTACHMENT_MIME_TYPE = 92 "vnd.android.cursor.item/email-attachment"; 93 94 public static final Uri INTEGRITY_CHECK_URI = 95 Uri.parse("content://" + EmailContent.AUTHORITY + "/integrityCheck"); 96 public static final Uri ACCOUNT_BACKUP_URI = 97 Uri.parse("content://" + EmailContent.AUTHORITY + "/accountBackup"); 98 99 /** Appended to the notification URI for delete operations */ 100 public static final String NOTIFICATION_OP_DELETE = "delete"; 101 /** Appended to the notification URI for insert operations */ 102 public static final String NOTIFICATION_OP_INSERT = "insert"; 103 /** Appended to the notification URI for update operations */ 104 public static final String NOTIFICATION_OP_UPDATE = "update"; 105 106 // Definitions for our queries looking for orphaned messages 107 private static final String[] ORPHANS_PROJECTION 108 = new String[] {MessageColumns.ID, MessageColumns.MAILBOX_KEY}; 109 private static final int ORPHANS_ID = 0; 110 private static final int ORPHANS_MAILBOX_KEY = 1; 111 112 private static final String WHERE_ID = EmailContent.RECORD_ID + "=?"; 113 114 // This is not a hard limit on accounts, per se, but beyond this, we can't guarantee that all 115 // critical mailboxes, host auth's, accounts, and policies are cached 116 private static final int MAX_CACHED_ACCOUNTS = 16; 117 // Inbox, Drafts, Sent, Outbox, Trash, and Search (these boxes are cached when possible) 118 private static final int NUM_ALWAYS_CACHED_MAILBOXES = 6; 119 120 // We'll cache the following four tables; sizes are best estimates of effective values 121 private final ContentCache mCacheAccount = 122 new ContentCache("Account", Account.CONTENT_PROJECTION, MAX_CACHED_ACCOUNTS); 123 private final ContentCache mCacheHostAuth = 124 new ContentCache("HostAuth", HostAuth.CONTENT_PROJECTION, MAX_CACHED_ACCOUNTS * 2); 125 /*package*/ final ContentCache mCacheMailbox = 126 new ContentCache("Mailbox", Mailbox.CONTENT_PROJECTION, 127 MAX_CACHED_ACCOUNTS * (NUM_ALWAYS_CACHED_MAILBOXES + 2)); 128 private final ContentCache mCacheMessage = 129 new ContentCache("Message", Message.CONTENT_PROJECTION, 8); 130 private final ContentCache mCachePolicy = 131 new ContentCache("Policy", Policy.CONTENT_PROJECTION, MAX_CACHED_ACCOUNTS); 132 133 // Any changes to the database format *must* include update-in-place code. 134 // Original version: 3 135 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 136 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 137 // Version 6: Adding Message.mServerTimeStamp column 138 // Version 7: Replace the mailbox_delete trigger with a version that removes orphaned messages 139 // from the Message_Deletes and Message_Updates tables 140 // Version 8: Add security flags column to accounts table 141 // Version 9: Add security sync key and signature to accounts table 142 // Version 10: Add meeting info to message table 143 // Version 11: Add content and flags to attachment table 144 // Version 12: Add content_bytes to attachment table. content is deprecated. 145 // Version 13: Add messageCount to Mailbox table. 146 // Version 14: Add snippet to Message table 147 // Version 15: Fix upgrade problem in version 14. 148 // Version 16: Add accountKey to Attachment table 149 // Version 17: Add parentKey to Mailbox table 150 // Version 18: Copy Mailbox.displayName to Mailbox.serverId for all IMAP & POP3 mailboxes. 151 // Column Mailbox.serverId is used for the server-side pathname of a mailbox. 152 // Version 19: Add Policy table; add policyKey to Account table and trigger to delete an 153 // Account's policy when the Account is deleted 154 // Version 20: Add new policies to Policy table 155 // Version 21: Add lastSeenMessageKey column to Mailbox table 156 // Version 22: Upgrade path for IMAP/POP accounts to integrate with AccountManager 157 // Version 23: Add column to mailbox table for time of last access 158 // Version 24: Add column to hostauth table for client cert alias 159 // Version 25: Added QuickResponse table 160 // Version 26: Update IMAP accounts to add FLAG_SUPPORTS_SEARCH flag 161 // Version 27: Add protocolSearchInfo to Message table 162 163 public static final int DATABASE_VERSION = 27; 164 165 // Any changes to the database format *must* include update-in-place code. 166 // Original version: 2 167 // Version 3: Add "sourceKey" column 168 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 169 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 170 // Version 6: Adding Body.mIntroText column 171 public static final int BODY_DATABASE_VERSION = 6; 172 173 private static final int ACCOUNT_BASE = 0; 174 private static final int ACCOUNT = ACCOUNT_BASE; 175 private static final int ACCOUNT_ID = ACCOUNT_BASE + 1; 176 private static final int ACCOUNT_ID_ADD_TO_FIELD = ACCOUNT_BASE + 2; 177 private static final int ACCOUNT_RESET_NEW_COUNT = ACCOUNT_BASE + 3; 178 private static final int ACCOUNT_RESET_NEW_COUNT_ID = ACCOUNT_BASE + 4; 179 private static final int ACCOUNT_DEFAULT_ID = ACCOUNT_BASE + 5; 180 181 private static final int MAILBOX_BASE = 0x1000; 182 private static final int MAILBOX = MAILBOX_BASE; 183 private static final int MAILBOX_ID = MAILBOX_BASE + 1; 184 private static final int MAILBOX_ID_FROM_ACCOUNT_AND_TYPE = MAILBOX_BASE + 2; 185 private static final int MAILBOX_ID_ADD_TO_FIELD = MAILBOX_BASE + 2; 186 187 private static final int MESSAGE_BASE = 0x2000; 188 private static final int MESSAGE = MESSAGE_BASE; 189 private static final int MESSAGE_ID = MESSAGE_BASE + 1; 190 private static final int SYNCED_MESSAGE_ID = MESSAGE_BASE + 2; 191 192 private static final int ATTACHMENT_BASE = 0x3000; 193 private static final int ATTACHMENT = ATTACHMENT_BASE; 194 private static final int ATTACHMENT_ID = ATTACHMENT_BASE + 1; 195 private static final int ATTACHMENTS_MESSAGE_ID = ATTACHMENT_BASE + 2; 196 197 private static final int HOSTAUTH_BASE = 0x4000; 198 private static final int HOSTAUTH = HOSTAUTH_BASE; 199 private static final int HOSTAUTH_ID = HOSTAUTH_BASE + 1; 200 201 private static final int UPDATED_MESSAGE_BASE = 0x5000; 202 private static final int UPDATED_MESSAGE = UPDATED_MESSAGE_BASE; 203 private static final int UPDATED_MESSAGE_ID = UPDATED_MESSAGE_BASE + 1; 204 205 private static final int DELETED_MESSAGE_BASE = 0x6000; 206 private static final int DELETED_MESSAGE = DELETED_MESSAGE_BASE; 207 private static final int DELETED_MESSAGE_ID = DELETED_MESSAGE_BASE + 1; 208 209 private static final int POLICY_BASE = 0x7000; 210 private static final int POLICY = POLICY_BASE; 211 private static final int POLICY_ID = POLICY_BASE + 1; 212 213 private static final int QUICK_RESPONSE_BASE = 0x8000; 214 private static final int QUICK_RESPONSE = QUICK_RESPONSE_BASE; 215 private static final int QUICK_RESPONSE_ID = QUICK_RESPONSE_BASE + 1; 216 private static final int QUICK_RESPONSE_ACCOUNT_ID = QUICK_RESPONSE_BASE + 2; 217 218 // MUST ALWAYS EQUAL THE LAST OF THE PREVIOUS BASE CONSTANTS 219 private static final int LAST_EMAIL_PROVIDER_DB_BASE = QUICK_RESPONSE_BASE; 220 221 // DO NOT CHANGE BODY_BASE!! 222 private static final int BODY_BASE = LAST_EMAIL_PROVIDER_DB_BASE + 0x1000; 223 private static final int BODY = BODY_BASE; 224 private static final int BODY_ID = BODY_BASE + 1; 225 226 private static final int BASE_SHIFT = 12; // 12 bits to the base type: 0, 0x1000, 0x2000, etc. 227 228 // TABLE_NAMES MUST remain in the order of the BASE constants above (e.g. ACCOUNT_BASE = 0x0000, 229 // MESSAGE_BASE = 0x1000, etc.) 230 private static final String[] TABLE_NAMES = { 231 Account.TABLE_NAME, 232 Mailbox.TABLE_NAME, 233 Message.TABLE_NAME, 234 Attachment.TABLE_NAME, 235 HostAuth.TABLE_NAME, 236 Message.UPDATED_TABLE_NAME, 237 Message.DELETED_TABLE_NAME, 238 Policy.TABLE_NAME, 239 QuickResponse.TABLE_NAME, 240 Body.TABLE_NAME 241 }; 242 243 // CONTENT_CACHES MUST remain in the order of the BASE constants above 244 private final ContentCache[] mContentCaches = { 245 mCacheAccount, 246 mCacheMailbox, 247 mCacheMessage, 248 null, // Attachment 249 mCacheHostAuth, 250 null, // Updated message 251 null, // Deleted message 252 mCachePolicy, 253 null, // Quick response 254 null // Body 255 }; 256 257 // CACHE_PROJECTIONS MUST remain in the order of the BASE constants above 258 private static final String[][] CACHE_PROJECTIONS = { 259 Account.CONTENT_PROJECTION, 260 Mailbox.CONTENT_PROJECTION, 261 Message.CONTENT_PROJECTION, 262 null, // Attachment 263 HostAuth.CONTENT_PROJECTION, 264 null, // Updated message 265 null, // Deleted message 266 Policy.CONTENT_PROJECTION, 267 null, // Quick response 268 null // Body 269 }; 270 271 private static final UriMatcher sURIMatcher = new UriMatcher(UriMatcher.NO_MATCH); 272 273 private static final String MAILBOX_PRE_CACHE_SELECTION = MailboxColumns.TYPE + " IN (" + 274 Mailbox.TYPE_INBOX + "," + Mailbox.TYPE_DRAFTS + "," + Mailbox.TYPE_TRASH + "," + 275 Mailbox.TYPE_SENT + "," + Mailbox.TYPE_SEARCH + "," + Mailbox.TYPE_OUTBOX + ")"; 276 277 /** 278 * Let's only generate these SQL strings once, as they are used frequently 279 * Note that this isn't relevant for table creation strings, since they are used only once 280 */ 281 private static final String UPDATED_MESSAGE_INSERT = "insert or ignore into " + 282 Message.UPDATED_TABLE_NAME + " select * from " + Message.TABLE_NAME + " where " + 283 EmailContent.RECORD_ID + '='; 284 285 private static final String UPDATED_MESSAGE_DELETE = "delete from " + 286 Message.UPDATED_TABLE_NAME + " where " + EmailContent.RECORD_ID + '='; 287 288 private static final String DELETED_MESSAGE_INSERT = "insert or replace into " + 289 Message.DELETED_TABLE_NAME + " select * from " + Message.TABLE_NAME + " where " + 290 EmailContent.RECORD_ID + '='; 291 292 private static final String DELETE_ORPHAN_BODIES = "delete from " + Body.TABLE_NAME + 293 " where " + BodyColumns.MESSAGE_KEY + " in " + "(select " + BodyColumns.MESSAGE_KEY + 294 " from " + Body.TABLE_NAME + " except select " + EmailContent.RECORD_ID + " from " + 295 Message.TABLE_NAME + ')'; 296 297 private static final String DELETE_BODY = "delete from " + Body.TABLE_NAME + 298 " where " + BodyColumns.MESSAGE_KEY + '='; 299 300 private static final String ID_EQUALS = EmailContent.RECORD_ID + "=?"; 301 302 private static final String TRIGGER_MAILBOX_DELETE = 303 "create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME + 304 " begin" + 305 " delete from " + Message.TABLE_NAME + 306 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 307 "; delete from " + Message.UPDATED_TABLE_NAME + 308 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 309 "; delete from " + Message.DELETED_TABLE_NAME + 310 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 311 "; end"; 312 313 private static final String TRIGGER_ACCOUNT_DELETE = 314 "create trigger account_delete before delete on " + Account.TABLE_NAME + 315 " begin delete from " + Mailbox.TABLE_NAME + 316 " where " + MailboxColumns.ACCOUNT_KEY + "=old." + EmailContent.RECORD_ID + 317 "; delete from " + HostAuth.TABLE_NAME + 318 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV + 319 "; delete from " + HostAuth.TABLE_NAME + 320 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND + 321 "; delete from " + Policy.TABLE_NAME + 322 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.POLICY_KEY + 323 "; end"; 324 325 private static final ContentValues CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT; 326 327 public static final String MESSAGE_URI_PARAMETER_MAILBOX_ID = "mailboxId"; 328 329 static { 330 // Email URI matching table 331 UriMatcher matcher = sURIMatcher; 332 333 // All accounts 334 matcher.addURI(EmailContent.AUTHORITY, "account", ACCOUNT); 335 // A specific account 336 // insert into this URI causes a mailbox to be added to the account 337 matcher.addURI(EmailContent.AUTHORITY, "account/#", ACCOUNT_ID); 338 matcher.addURI(EmailContent.AUTHORITY, "account/default", ACCOUNT_DEFAULT_ID); 339 340 // Special URI to reset the new message count. Only update works, and content values 341 // will be ignored. 342 matcher.addURI(EmailContent.AUTHORITY, "resetNewMessageCount", 343 ACCOUNT_RESET_NEW_COUNT); 344 matcher.addURI(EmailContent.AUTHORITY, "resetNewMessageCount/#", 345 ACCOUNT_RESET_NEW_COUNT_ID); 346 347 // All mailboxes 348 matcher.addURI(EmailContent.AUTHORITY, "mailbox", MAILBOX); 349 // A specific mailbox 350 // insert into this URI causes a message to be added to the mailbox 351 // ** NOTE For now, the accountKey must be set manually in the values! 352 matcher.addURI(EmailContent.AUTHORITY, "mailbox/#", MAILBOX_ID); 353 matcher.addURI(EmailContent.AUTHORITY, "mailboxIdFromAccountAndType/#/#", 354 MAILBOX_ID_FROM_ACCOUNT_AND_TYPE); 355 // All messages 356 matcher.addURI(EmailContent.AUTHORITY, "message", MESSAGE); 357 // A specific message 358 // insert into this URI causes an attachment to be added to the message 359 matcher.addURI(EmailContent.AUTHORITY, "message/#", MESSAGE_ID); 360 361 // A specific attachment 362 matcher.addURI(EmailContent.AUTHORITY, "attachment", ATTACHMENT); 363 // A specific attachment (the header information) 364 matcher.addURI(EmailContent.AUTHORITY, "attachment/#", ATTACHMENT_ID); 365 // The attachments of a specific message (query only) (insert & delete TBD) 366 matcher.addURI(EmailContent.AUTHORITY, "attachment/message/#", 367 ATTACHMENTS_MESSAGE_ID); 368 369 // All mail bodies 370 matcher.addURI(EmailContent.AUTHORITY, "body", BODY); 371 // A specific mail body 372 matcher.addURI(EmailContent.AUTHORITY, "body/#", BODY_ID); 373 374 // All hostauth records 375 matcher.addURI(EmailContent.AUTHORITY, "hostauth", HOSTAUTH); 376 // A specific hostauth 377 matcher.addURI(EmailContent.AUTHORITY, "hostauth/#", HOSTAUTH_ID); 378 379 // Atomically a constant value to a particular field of a mailbox/account 380 matcher.addURI(EmailContent.AUTHORITY, "mailboxIdAddToField/#", 381 MAILBOX_ID_ADD_TO_FIELD); 382 matcher.addURI(EmailContent.AUTHORITY, "accountIdAddToField/#", 383 ACCOUNT_ID_ADD_TO_FIELD); 384 385 /** 386 * THIS URI HAS SPECIAL SEMANTICS 387 * ITS USE IS INTENDED FOR THE UI APPLICATION TO MARK CHANGES THAT NEED TO BE SYNCED BACK 388 * TO A SERVER VIA A SYNC ADAPTER 389 */ 390 matcher.addURI(EmailContent.AUTHORITY, "syncedMessage/#", SYNCED_MESSAGE_ID); 391 392 /** 393 * THE URIs BELOW THIS POINT ARE INTENDED TO BE USED BY SYNC ADAPTERS ONLY 394 * THEY REFER TO DATA CREATED AND MAINTAINED BY CALLS TO THE SYNCED_MESSAGE_ID URI 395 * BY THE UI APPLICATION 396 */ 397 // All deleted messages 398 matcher.addURI(EmailContent.AUTHORITY, "deletedMessage", DELETED_MESSAGE); 399 // A specific deleted message 400 matcher.addURI(EmailContent.AUTHORITY, "deletedMessage/#", DELETED_MESSAGE_ID); 401 402 // All updated messages 403 matcher.addURI(EmailContent.AUTHORITY, "updatedMessage", UPDATED_MESSAGE); 404 // A specific updated message 405 matcher.addURI(EmailContent.AUTHORITY, "updatedMessage/#", UPDATED_MESSAGE_ID); 406 407 CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT = new ContentValues(); 408 CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT.put(Account.NEW_MESSAGE_COUNT, 0); 409 410 matcher.addURI(EmailContent.AUTHORITY, "policy", POLICY); 411 matcher.addURI(EmailContent.AUTHORITY, "policy/#", POLICY_ID); 412 413 // All quick responses 414 matcher.addURI(EmailContent.AUTHORITY, "quickresponse", QUICK_RESPONSE); 415 // A specific quick response 416 matcher.addURI(EmailContent.AUTHORITY, "quickresponse/#", QUICK_RESPONSE_ID); 417 // All quick responses associated with a particular account id 418 matcher.addURI(EmailContent.AUTHORITY, "quickresponse/account/#", 419 QUICK_RESPONSE_ACCOUNT_ID); 420 } 421 422 /** 423 * Wrap the UriMatcher call so we can throw a runtime exception if an unknown Uri is passed in 424 * @param uri the Uri to match 425 * @return the match value 426 */ 427 private static int findMatch(Uri uri, String methodName) { 428 int match = sURIMatcher.match(uri); 429 if (match < 0) { 430 throw new IllegalArgumentException("Unknown uri: " + uri); 431 } else if (Logging.LOGD) { 432 Log.v(TAG, methodName + ": uri=" + uri + ", match is " + match); 433 } 434 return match; 435 } 436 437 /* 438 * Internal helper method for index creation. 439 * Example: 440 * "create index message_" + MessageColumns.FLAG_READ 441 * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");" 442 */ 443 /* package */ 444 static String createIndex(String tableName, String columnName) { 445 return "create index " + tableName.toLowerCase() + '_' + columnName 446 + " on " + tableName + " (" + columnName + ");"; 447 } 448 449 static void createMessageTable(SQLiteDatabase db) { 450 String messageColumns = MessageColumns.DISPLAY_NAME + " text, " 451 + MessageColumns.TIMESTAMP + " integer, " 452 + MessageColumns.SUBJECT + " text, " 453 + MessageColumns.FLAG_READ + " integer, " 454 + MessageColumns.FLAG_LOADED + " integer, " 455 + MessageColumns.FLAG_FAVORITE + " integer, " 456 + MessageColumns.FLAG_ATTACHMENT + " integer, " 457 + MessageColumns.FLAGS + " integer, " 458 + MessageColumns.CLIENT_ID + " integer, " 459 + MessageColumns.MESSAGE_ID + " text, " 460 + MessageColumns.MAILBOX_KEY + " integer, " 461 + MessageColumns.ACCOUNT_KEY + " integer, " 462 + MessageColumns.FROM_LIST + " text, " 463 + MessageColumns.TO_LIST + " text, " 464 + MessageColumns.CC_LIST + " text, " 465 + MessageColumns.BCC_LIST + " text, " 466 + MessageColumns.REPLY_TO_LIST + " text, " 467 + MessageColumns.MEETING_INFO + " text, " 468 + MessageColumns.SNIPPET + " text, " 469 + MessageColumns.PROTOCOL_SEARCH_INFO + " text" 470 + ");"; 471 472 // This String and the following String MUST have the same columns, except for the type 473 // of those columns! 474 String createString = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 475 + SyncColumns.SERVER_ID + " text, " 476 + SyncColumns.SERVER_TIMESTAMP + " integer, " 477 + messageColumns; 478 479 // For the updated and deleted tables, the id is assigned, but we do want to keep track 480 // of the ORDER of updates using an autoincrement primary key. We use the DATA column 481 // at this point; it has no other function 482 String altCreateString = " (" + EmailContent.RECORD_ID + " integer unique, " 483 + SyncColumns.SERVER_ID + " text, " 484 + SyncColumns.SERVER_TIMESTAMP + " integer, " 485 + messageColumns; 486 487 // The three tables have the same schema 488 db.execSQL("create table " + Message.TABLE_NAME + createString); 489 db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString); 490 db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString); 491 492 String indexColumns[] = { 493 MessageColumns.TIMESTAMP, 494 MessageColumns.FLAG_READ, 495 MessageColumns.FLAG_LOADED, 496 MessageColumns.MAILBOX_KEY, 497 SyncColumns.SERVER_ID 498 }; 499 500 for (String columnName : indexColumns) { 501 db.execSQL(createIndex(Message.TABLE_NAME, columnName)); 502 } 503 504 // Deleting a Message deletes all associated Attachments 505 // Deleting the associated Body cannot be done in a trigger, because the Body is stored 506 // in a separate database, and trigger cannot operate on attached databases. 507 db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME + 508 " begin delete from " + Attachment.TABLE_NAME + 509 " where " + AttachmentColumns.MESSAGE_KEY + "=old." + EmailContent.RECORD_ID + 510 "; end"); 511 512 // Add triggers to keep unread count accurate per mailbox 513 514 // NOTE: SQLite's before triggers are not safe when recursive triggers are involved. 515 // Use caution when changing them. 516 517 // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox 518 db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME + 519 " when NEW." + MessageColumns.FLAG_READ + "=0" + 520 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 521 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 522 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 523 "; end"); 524 525 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 526 db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME + 527 " when OLD." + MessageColumns.FLAG_READ + "=0" + 528 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 529 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 530 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 531 "; end"); 532 533 // Change a message's mailbox 534 db.execSQL("create trigger unread_message_move before update of " + 535 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 536 " when OLD." + MessageColumns.FLAG_READ + "=0" + 537 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 538 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 539 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 540 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 541 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 542 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 543 "; end"); 544 545 // Change a message's read state 546 db.execSQL("create trigger unread_message_read before update of " + 547 MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME + 548 " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ + 549 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 550 '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ + 551 " when 0 then -1 else 1 end" + 552 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 553 "; end"); 554 555 // Add triggers to update message count per mailbox 556 557 // Insert a message. 558 db.execSQL("create trigger message_count_message_insert after insert on " + 559 Message.TABLE_NAME + 560 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 561 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 562 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 563 "; end"); 564 565 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 566 db.execSQL("create trigger message_count_message_delete after delete on " + 567 Message.TABLE_NAME + 568 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 569 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 570 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 571 "; end"); 572 573 // Change a message's mailbox 574 db.execSQL("create trigger message_count_message_move after update of " + 575 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 576 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 577 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 578 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 579 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 580 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 581 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 582 "; end"); 583 } 584 585 static void resetMessageTable(SQLiteDatabase db, int oldVersion, int newVersion) { 586 try { 587 db.execSQL("drop table " + Message.TABLE_NAME); 588 db.execSQL("drop table " + Message.UPDATED_TABLE_NAME); 589 db.execSQL("drop table " + Message.DELETED_TABLE_NAME); 590 } catch (SQLException e) { 591 } 592 createMessageTable(db); 593 } 594 595 @SuppressWarnings("deprecation") 596 static void createAccountTable(SQLiteDatabase db) { 597 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 598 + AccountColumns.DISPLAY_NAME + " text, " 599 + AccountColumns.EMAIL_ADDRESS + " text, " 600 + AccountColumns.SYNC_KEY + " text, " 601 + AccountColumns.SYNC_LOOKBACK + " integer, " 602 + AccountColumns.SYNC_INTERVAL + " text, " 603 + AccountColumns.HOST_AUTH_KEY_RECV + " integer, " 604 + AccountColumns.HOST_AUTH_KEY_SEND + " integer, " 605 + AccountColumns.FLAGS + " integer, " 606 + AccountColumns.IS_DEFAULT + " integer, " 607 + AccountColumns.COMPATIBILITY_UUID + " text, " 608 + AccountColumns.SENDER_NAME + " text, " 609 + AccountColumns.RINGTONE_URI + " text, " 610 + AccountColumns.PROTOCOL_VERSION + " text, " 611 + AccountColumns.NEW_MESSAGE_COUNT + " integer, " 612 + AccountColumns.SECURITY_FLAGS + " integer, " 613 + AccountColumns.SECURITY_SYNC_KEY + " text, " 614 + AccountColumns.SIGNATURE + " text, " 615 + AccountColumns.POLICY_KEY + " integer" 616 + ");"; 617 db.execSQL("create table " + Account.TABLE_NAME + s); 618 // Deleting an account deletes associated Mailboxes and HostAuth's 619 db.execSQL(TRIGGER_ACCOUNT_DELETE); 620 } 621 622 static void resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) { 623 try { 624 db.execSQL("drop table " + Account.TABLE_NAME); 625 } catch (SQLException e) { 626 } 627 createAccountTable(db); 628 } 629 630 static void createPolicyTable(SQLiteDatabase db) { 631 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 632 + PolicyColumns.PASSWORD_MODE + " integer, " 633 + PolicyColumns.PASSWORD_MIN_LENGTH + " integer, " 634 + PolicyColumns.PASSWORD_EXPIRATION_DAYS + " integer, " 635 + PolicyColumns.PASSWORD_HISTORY + " integer, " 636 + PolicyColumns.PASSWORD_COMPLEX_CHARS + " integer, " 637 + PolicyColumns.PASSWORD_MAX_FAILS + " integer, " 638 + PolicyColumns.MAX_SCREEN_LOCK_TIME + " integer, " 639 + PolicyColumns.REQUIRE_REMOTE_WIPE + " integer, " 640 + PolicyColumns.REQUIRE_ENCRYPTION + " integer, " 641 + PolicyColumns.REQUIRE_ENCRYPTION_EXTERNAL + " integer, " 642 + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + " integer, " 643 + PolicyColumns.DONT_ALLOW_CAMERA + " integer, " 644 + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer, " 645 + PolicyColumns.DONT_ALLOW_HTML + " integer, " 646 + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer, " 647 + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + " integer, " 648 + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + " integer, " 649 + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer, " 650 + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer, " 651 + PolicyColumns.PASSWORD_RECOVERY_ENABLED + " integer" 652 + ");"; 653 db.execSQL("create table " + Policy.TABLE_NAME + s); 654 } 655 656 static void createHostAuthTable(SQLiteDatabase db) { 657 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 658 + HostAuthColumns.PROTOCOL + " text, " 659 + HostAuthColumns.ADDRESS + " text, " 660 + HostAuthColumns.PORT + " integer, " 661 + HostAuthColumns.FLAGS + " integer, " 662 + HostAuthColumns.LOGIN + " text, " 663 + HostAuthColumns.PASSWORD + " text, " 664 + HostAuthColumns.DOMAIN + " text, " 665 + HostAuthColumns.ACCOUNT_KEY + " integer," 666 + HostAuthColumns.CLIENT_CERT_ALIAS + " text" 667 + ");"; 668 db.execSQL("create table " + HostAuth.TABLE_NAME + s); 669 } 670 671 static void resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) { 672 try { 673 db.execSQL("drop table " + HostAuth.TABLE_NAME); 674 } catch (SQLException e) { 675 } 676 createHostAuthTable(db); 677 } 678 679 static void createMailboxTable(SQLiteDatabase db) { 680 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 681 + MailboxColumns.DISPLAY_NAME + " text, " 682 + MailboxColumns.SERVER_ID + " text, " 683 + MailboxColumns.PARENT_SERVER_ID + " text, " 684 + MailboxColumns.PARENT_KEY + " integer, " 685 + MailboxColumns.ACCOUNT_KEY + " integer, " 686 + MailboxColumns.TYPE + " integer, " 687 + MailboxColumns.DELIMITER + " integer, " 688 + MailboxColumns.SYNC_KEY + " text, " 689 + MailboxColumns.SYNC_LOOKBACK + " integer, " 690 + MailboxColumns.SYNC_INTERVAL + " integer, " 691 + MailboxColumns.SYNC_TIME + " integer, " 692 + MailboxColumns.UNREAD_COUNT + " integer, " 693 + MailboxColumns.FLAG_VISIBLE + " integer, " 694 + MailboxColumns.FLAGS + " integer, " 695 + MailboxColumns.VISIBLE_LIMIT + " integer, " 696 + MailboxColumns.SYNC_STATUS + " text, " 697 + MailboxColumns.MESSAGE_COUNT + " integer not null default 0, " 698 + MailboxColumns.LAST_SEEN_MESSAGE_KEY + " integer, " 699 + MailboxColumns.LAST_TOUCHED_TIME + " integer default 0" 700 + ");"; 701 db.execSQL("create table " + Mailbox.TABLE_NAME + s); 702 db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID 703 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")"); 704 db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY 705 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")"); 706 // Deleting a Mailbox deletes associated Messages in all three tables 707 db.execSQL(TRIGGER_MAILBOX_DELETE); 708 } 709 710 static void resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) { 711 try { 712 db.execSQL("drop table " + Mailbox.TABLE_NAME); 713 } catch (SQLException e) { 714 } 715 createMailboxTable(db); 716 } 717 718 static void createAttachmentTable(SQLiteDatabase db) { 719 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 720 + AttachmentColumns.FILENAME + " text, " 721 + AttachmentColumns.MIME_TYPE + " text, " 722 + AttachmentColumns.SIZE + " integer, " 723 + AttachmentColumns.CONTENT_ID + " text, " 724 + AttachmentColumns.CONTENT_URI + " text, " 725 + AttachmentColumns.MESSAGE_KEY + " integer, " 726 + AttachmentColumns.LOCATION + " text, " 727 + AttachmentColumns.ENCODING + " text, " 728 + AttachmentColumns.CONTENT + " text, " 729 + AttachmentColumns.FLAGS + " integer, " 730 + AttachmentColumns.CONTENT_BYTES + " blob, " 731 + AttachmentColumns.ACCOUNT_KEY + " integer" 732 + ");"; 733 db.execSQL("create table " + Attachment.TABLE_NAME + s); 734 db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY)); 735 } 736 737 static void resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) { 738 try { 739 db.execSQL("drop table " + Attachment.TABLE_NAME); 740 } catch (SQLException e) { 741 } 742 createAttachmentTable(db); 743 } 744 745 static void createQuickResponseTable(SQLiteDatabase db) { 746 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 747 + QuickResponseColumns.TEXT + " text, " 748 + QuickResponseColumns.ACCOUNT_KEY + " integer" 749 + ");"; 750 db.execSQL("create table " + QuickResponse.TABLE_NAME + s); 751 } 752 753 static void createBodyTable(SQLiteDatabase db) { 754 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 755 + BodyColumns.MESSAGE_KEY + " integer, " 756 + BodyColumns.HTML_CONTENT + " text, " 757 + BodyColumns.TEXT_CONTENT + " text, " 758 + BodyColumns.HTML_REPLY + " text, " 759 + BodyColumns.TEXT_REPLY + " text, " 760 + BodyColumns.SOURCE_MESSAGE_KEY + " text, " 761 + BodyColumns.INTRO_TEXT + " text" 762 + ");"; 763 db.execSQL("create table " + Body.TABLE_NAME + s); 764 db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY)); 765 } 766 767 static void upgradeBodyTable(SQLiteDatabase db, int oldVersion, int newVersion) { 768 if (oldVersion < 5) { 769 try { 770 db.execSQL("drop table " + Body.TABLE_NAME); 771 createBodyTable(db); 772 } catch (SQLException e) { 773 } 774 } else if (oldVersion == 5) { 775 try { 776 db.execSQL("alter table " + Body.TABLE_NAME 777 + " add " + BodyColumns.INTRO_TEXT + " text"); 778 } catch (SQLException e) { 779 // Shouldn't be needed unless we're debugging and interrupt the process 780 Log.w(TAG, "Exception upgrading EmailProviderBody.db from v5 to v6", e); 781 } 782 oldVersion = 6; 783 } 784 } 785 786 private SQLiteDatabase mDatabase; 787 private SQLiteDatabase mBodyDatabase; 788 789 /** 790 * Orphan record deletion utility. Generates a sqlite statement like: 791 * delete from <table> where <column> not in (select <foreignColumn> from <foreignTable>) 792 * @param db the EmailProvider database 793 * @param table the table whose orphans are to be removed 794 * @param column the column deletion will be based on 795 * @param foreignColumn the column in the foreign table whose absence will trigger the deletion 796 * @param foreignTable the foreign table 797 */ 798 @VisibleForTesting 799 void deleteUnlinked(SQLiteDatabase db, String table, String column, String foreignColumn, 800 String foreignTable) { 801 int count = db.delete(table, column + " not in (select " + foreignColumn + " from " + 802 foreignTable + ")", null); 803 if (count > 0) { 804 Log.w(TAG, "Found " + count + " orphaned row(s) in " + table); 805 } 806 } 807 808 @VisibleForTesting 809 synchronized SQLiteDatabase getDatabase(Context context) { 810 // Always return the cached database, if we've got one 811 if (mDatabase != null) { 812 return mDatabase; 813 } 814 815 // Whenever we create or re-cache the databases, make sure that we haven't lost one 816 // to corruption 817 checkDatabases(); 818 819 DatabaseHelper helper = new DatabaseHelper(context, DATABASE_NAME); 820 mDatabase = helper.getWritableDatabase(); 821 mDatabase.setLockingEnabled(true); 822 BodyDatabaseHelper bodyHelper = new BodyDatabaseHelper(context, BODY_DATABASE_NAME); 823 mBodyDatabase = bodyHelper.getWritableDatabase(); 824 if (mBodyDatabase != null) { 825 mBodyDatabase.setLockingEnabled(true); 826 String bodyFileName = mBodyDatabase.getPath(); 827 mDatabase.execSQL("attach \"" + bodyFileName + "\" as BodyDatabase"); 828 } 829 830 // Restore accounts if the database is corrupted... 831 restoreIfNeeded(context, mDatabase); 832 833 if (Email.DEBUG) { 834 Log.d(TAG, "Deleting orphans..."); 835 } 836 // Check for any orphaned Messages in the updated/deleted tables 837 deleteMessageOrphans(mDatabase, Message.UPDATED_TABLE_NAME); 838 deleteMessageOrphans(mDatabase, Message.DELETED_TABLE_NAME); 839 // Delete orphaned mailboxes/messages/policies (account no longer exists) 840 deleteUnlinked(mDatabase, Mailbox.TABLE_NAME, MailboxColumns.ACCOUNT_KEY, AccountColumns.ID, 841 Account.TABLE_NAME); 842 deleteUnlinked(mDatabase, Message.TABLE_NAME, MessageColumns.ACCOUNT_KEY, AccountColumns.ID, 843 Account.TABLE_NAME); 844 deleteUnlinked(mDatabase, Policy.TABLE_NAME, PolicyColumns.ID, AccountColumns.POLICY_KEY, 845 Account.TABLE_NAME); 846 847 if (Email.DEBUG) { 848 Log.d(TAG, "EmailProvider pre-caching..."); 849 } 850 preCacheData(); 851 if (Email.DEBUG) { 852 Log.d(TAG, "EmailProvider ready."); 853 } 854 return mDatabase; 855 } 856 857 /** 858 * Pre-cache all of the items in a given table meeting the selection criteria 859 * @param tableUri the table uri 860 * @param baseProjection the base projection of that table 861 * @param selection the selection criteria 862 */ 863 private void preCacheTable(Uri tableUri, String[] baseProjection, String selection) { 864 Cursor c = query(tableUri, EmailContent.ID_PROJECTION, selection, null, null); 865 try { 866 while (c.moveToNext()) { 867 long id = c.getLong(EmailContent.ID_PROJECTION_COLUMN); 868 Cursor cachedCursor = query(ContentUris.withAppendedId( 869 tableUri, id), baseProjection, null, null, null); 870 if (cachedCursor != null) { 871 // For accounts, create a mailbox type map entry (if necessary) 872 if (tableUri == Account.CONTENT_URI) { 873 getOrCreateAccountMailboxTypeMap(id); 874 } 875 cachedCursor.close(); 876 } 877 } 878 } finally { 879 c.close(); 880 } 881 } 882 883 private final HashMap<Long, HashMap<Integer, Long>> mMailboxTypeMap = 884 new HashMap<Long, HashMap<Integer, Long>>(); 885 886 private HashMap<Integer, Long> getOrCreateAccountMailboxTypeMap(long accountId) { 887 synchronized(mMailboxTypeMap) { 888 HashMap<Integer, Long> accountMailboxTypeMap = mMailboxTypeMap.get(accountId); 889 if (accountMailboxTypeMap == null) { 890 accountMailboxTypeMap = new HashMap<Integer, Long>(); 891 mMailboxTypeMap.put(accountId, accountMailboxTypeMap); 892 } 893 return accountMailboxTypeMap; 894 } 895 } 896 897 private void addToMailboxTypeMap(Cursor c) { 898 long accountId = c.getLong(Mailbox.CONTENT_ACCOUNT_KEY_COLUMN); 899 int type = c.getInt(Mailbox.CONTENT_TYPE_COLUMN); 900 synchronized(mMailboxTypeMap) { 901 HashMap<Integer, Long> accountMailboxTypeMap = 902 getOrCreateAccountMailboxTypeMap(accountId); 903 accountMailboxTypeMap.put(type, c.getLong(Mailbox.CONTENT_ID_COLUMN)); 904 } 905 } 906 907 private long getMailboxIdFromMailboxTypeMap(long accountId, int type) { 908 synchronized(mMailboxTypeMap) { 909 HashMap<Integer, Long> accountMap = mMailboxTypeMap.get(accountId); 910 Long mailboxId = null; 911 if (accountMap != null) { 912 mailboxId = accountMap.get(type); 913 } 914 if (mailboxId == null) return Mailbox.NO_MAILBOX; 915 return mailboxId; 916 } 917 } 918 919 private void preCacheData() { 920 synchronized(mMailboxTypeMap) { 921 mMailboxTypeMap.clear(); 922 923 // Pre-cache accounts, host auth's, policies, and special mailboxes 924 preCacheTable(Account.CONTENT_URI, Account.CONTENT_PROJECTION, null); 925 preCacheTable(HostAuth.CONTENT_URI, HostAuth.CONTENT_PROJECTION, null); 926 preCacheTable(Policy.CONTENT_URI, Policy.CONTENT_PROJECTION, null); 927 preCacheTable(Mailbox.CONTENT_URI, Mailbox.CONTENT_PROJECTION, 928 MAILBOX_PRE_CACHE_SELECTION); 929 930 // Create a map from account,type to a mailbox 931 Map<String, Cursor> snapshot = mCacheMailbox.getSnapshot(); 932 Collection<Cursor> values = snapshot.values(); 933 if (values != null) { 934 for (Cursor c: values) { 935 if (c.moveToFirst()) { 936 addToMailboxTypeMap(c); 937 } 938 } 939 } 940 } 941 } 942 943 /*package*/ static SQLiteDatabase getReadableDatabase(Context context) { 944 DatabaseHelper helper = new DatabaseHelper(context, DATABASE_NAME); 945 return helper.getReadableDatabase(); 946 } 947 948 /** 949 * Restore user Account and HostAuth data from our backup database 950 */ 951 public static void restoreIfNeeded(Context context, SQLiteDatabase mainDatabase) { 952 if (Email.DEBUG) { 953 Log.w(TAG, "restoreIfNeeded..."); 954 } 955 // Check for legacy backup 956 String legacyBackup = Preferences.getLegacyBackupPreference(context); 957 // If there's a legacy backup, create a new-style backup and delete the legacy backup 958 // In the 1:1000000000 chance that the user gets an app update just as his database becomes 959 // corrupt, oh well... 960 if (!TextUtils.isEmpty(legacyBackup)) { 961 backupAccounts(context, mainDatabase); 962 Preferences.clearLegacyBackupPreference(context); 963 Log.w(TAG, "Created new EmailProvider backup database"); 964 return; 965 } 966 967 // If we have accounts, we're done 968 Cursor c = mainDatabase.query(Account.TABLE_NAME, EmailContent.ID_PROJECTION, null, null, 969 null, null, null); 970 if (c.moveToFirst()) { 971 if (Email.DEBUG) { 972 Log.w(TAG, "restoreIfNeeded: Account exists."); 973 } 974 return; // At least one account exists. 975 } 976 restoreAccounts(context, mainDatabase); 977 } 978 979 /** {@inheritDoc} */ 980 @Override 981 public void shutdown() { 982 if (mDatabase != null) { 983 mDatabase.close(); 984 mDatabase = null; 985 } 986 if (mBodyDatabase != null) { 987 mBodyDatabase.close(); 988 mBodyDatabase = null; 989 } 990 } 991 992 /*package*/ static void deleteMessageOrphans(SQLiteDatabase database, String tableName) { 993 if (database != null) { 994 // We'll look at all of the items in the table; there won't be many typically 995 Cursor c = database.query(tableName, ORPHANS_PROJECTION, null, null, null, null, null); 996 // Usually, there will be nothing in these tables, so make a quick check 997 try { 998 if (c.getCount() == 0) return; 999 ArrayList<Long> foundMailboxes = new ArrayList<Long>(); 1000 ArrayList<Long> notFoundMailboxes = new ArrayList<Long>(); 1001 ArrayList<Long> deleteList = new ArrayList<Long>(); 1002 String[] bindArray = new String[1]; 1003 while (c.moveToNext()) { 1004 // Get the mailbox key and see if we've already found this mailbox 1005 // If so, we're fine 1006 long mailboxId = c.getLong(ORPHANS_MAILBOX_KEY); 1007 // If we already know this mailbox doesn't exist, mark the message for deletion 1008 if (notFoundMailboxes.contains(mailboxId)) { 1009 deleteList.add(c.getLong(ORPHANS_ID)); 1010 // If we don't know about this mailbox, we'll try to find it 1011 } else if (!foundMailboxes.contains(mailboxId)) { 1012 bindArray[0] = Long.toString(mailboxId); 1013 Cursor boxCursor = database.query(Mailbox.TABLE_NAME, 1014 Mailbox.ID_PROJECTION, WHERE_ID, bindArray, null, null, null); 1015 try { 1016 // If it exists, we'll add it to the "found" mailboxes 1017 if (boxCursor.moveToFirst()) { 1018 foundMailboxes.add(mailboxId); 1019 // Otherwise, we'll add to "not found" and mark the message for deletion 1020 } else { 1021 notFoundMailboxes.add(mailboxId); 1022 deleteList.add(c.getLong(ORPHANS_ID)); 1023 } 1024 } finally { 1025 boxCursor.close(); 1026 } 1027 } 1028 } 1029 // Now, delete the orphan messages 1030 for (long messageId: deleteList) { 1031 bindArray[0] = Long.toString(messageId); 1032 database.delete(tableName, WHERE_ID, bindArray); 1033 } 1034 } finally { 1035 c.close(); 1036 } 1037 } 1038 } 1039 1040 private class BodyDatabaseHelper extends SQLiteOpenHelper { 1041 BodyDatabaseHelper(Context context, String name) { 1042 super(context, name, null, BODY_DATABASE_VERSION); 1043 } 1044 1045 @Override 1046 public void onCreate(SQLiteDatabase db) { 1047 Log.d(TAG, "Creating EmailProviderBody database"); 1048 createBodyTable(db); 1049 } 1050 1051 @Override 1052 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 1053 upgradeBodyTable(db, oldVersion, newVersion); 1054 } 1055 1056 @Override 1057 public void onOpen(SQLiteDatabase db) { 1058 } 1059 } 1060 1061 private static class DatabaseHelper extends SQLiteOpenHelper { 1062 Context mContext; 1063 1064 DatabaseHelper(Context context, String name) { 1065 super(context, name, null, DATABASE_VERSION); 1066 mContext = context; 1067 } 1068 1069 @Override 1070 public void onCreate(SQLiteDatabase db) { 1071 Log.d(TAG, "Creating EmailProvider database"); 1072 // Create all tables here; each class has its own method 1073 createMessageTable(db); 1074 createAttachmentTable(db); 1075 createMailboxTable(db); 1076 createHostAuthTable(db); 1077 createAccountTable(db); 1078 createPolicyTable(db); 1079 createQuickResponseTable(db); 1080 } 1081 1082 @Override 1083 @SuppressWarnings("deprecation") 1084 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 1085 // For versions prior to 5, delete all data 1086 // Versions >= 5 require that data be preserved! 1087 if (oldVersion < 5) { 1088 android.accounts.Account[] accounts = AccountManager.get(mContext) 1089 .getAccountsByType(AccountManagerTypes.TYPE_EXCHANGE); 1090 for (android.accounts.Account account: accounts) { 1091 AccountManager.get(mContext).removeAccount(account, null, null); 1092 } 1093 resetMessageTable(db, oldVersion, newVersion); 1094 resetAttachmentTable(db, oldVersion, newVersion); 1095 resetMailboxTable(db, oldVersion, newVersion); 1096 resetHostAuthTable(db, oldVersion, newVersion); 1097 resetAccountTable(db, oldVersion, newVersion); 1098 return; 1099 } 1100 if (oldVersion == 5) { 1101 // Message Tables: Add SyncColumns.SERVER_TIMESTAMP 1102 try { 1103 db.execSQL("alter table " + Message.TABLE_NAME 1104 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 1105 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1106 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 1107 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1108 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 1109 } catch (SQLException e) { 1110 // Shouldn't be needed unless we're debugging and interrupt the process 1111 Log.w(TAG, "Exception upgrading EmailProvider.db from v5 to v6", e); 1112 } 1113 oldVersion = 6; 1114 } 1115 if (oldVersion == 6) { 1116 // Use the newer mailbox_delete trigger 1117 db.execSQL("drop trigger mailbox_delete;"); 1118 db.execSQL(TRIGGER_MAILBOX_DELETE); 1119 oldVersion = 7; 1120 } 1121 if (oldVersion == 7) { 1122 // add the security (provisioning) column 1123 try { 1124 db.execSQL("alter table " + Account.TABLE_NAME 1125 + " add column " + AccountColumns.SECURITY_FLAGS + " integer" + ";"); 1126 } catch (SQLException e) { 1127 // Shouldn't be needed unless we're debugging and interrupt the process 1128 Log.w(TAG, "Exception upgrading EmailProvider.db from 7 to 8 " + e); 1129 } 1130 oldVersion = 8; 1131 } 1132 if (oldVersion == 8) { 1133 // accounts: add security sync key & user signature columns 1134 try { 1135 db.execSQL("alter table " + Account.TABLE_NAME 1136 + " add column " + AccountColumns.SECURITY_SYNC_KEY + " text" + ";"); 1137 db.execSQL("alter table " + Account.TABLE_NAME 1138 + " add column " + AccountColumns.SIGNATURE + " text" + ";"); 1139 } catch (SQLException e) { 1140 // Shouldn't be needed unless we're debugging and interrupt the process 1141 Log.w(TAG, "Exception upgrading EmailProvider.db from 8 to 9 " + e); 1142 } 1143 oldVersion = 9; 1144 } 1145 if (oldVersion == 9) { 1146 // Message: add meeting info column into Message tables 1147 try { 1148 db.execSQL("alter table " + Message.TABLE_NAME 1149 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 1150 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1151 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 1152 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1153 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 1154 } catch (SQLException e) { 1155 // Shouldn't be needed unless we're debugging and interrupt the process 1156 Log.w(TAG, "Exception upgrading EmailProvider.db from 9 to 10 " + e); 1157 } 1158 oldVersion = 10; 1159 } 1160 if (oldVersion == 10) { 1161 // Attachment: add content and flags columns 1162 try { 1163 db.execSQL("alter table " + Attachment.TABLE_NAME 1164 + " add column " + AttachmentColumns.CONTENT + " text" + ";"); 1165 db.execSQL("alter table " + Attachment.TABLE_NAME 1166 + " add column " + AttachmentColumns.FLAGS + " integer" + ";"); 1167 } catch (SQLException e) { 1168 // Shouldn't be needed unless we're debugging and interrupt the process 1169 Log.w(TAG, "Exception upgrading EmailProvider.db from 10 to 11 " + e); 1170 } 1171 oldVersion = 11; 1172 } 1173 if (oldVersion == 11) { 1174 // Attachment: add content_bytes 1175 try { 1176 db.execSQL("alter table " + Attachment.TABLE_NAME 1177 + " add column " + AttachmentColumns.CONTENT_BYTES + " blob" + ";"); 1178 } catch (SQLException e) { 1179 // Shouldn't be needed unless we're debugging and interrupt the process 1180 Log.w(TAG, "Exception upgrading EmailProvider.db from 11 to 12 " + e); 1181 } 1182 oldVersion = 12; 1183 } 1184 if (oldVersion == 12) { 1185 try { 1186 db.execSQL("alter table " + Mailbox.TABLE_NAME 1187 + " add column " + Mailbox.MESSAGE_COUNT 1188 +" integer not null default 0" + ";"); 1189 recalculateMessageCount(db); 1190 } catch (SQLException e) { 1191 // Shouldn't be needed unless we're debugging and interrupt the process 1192 Log.w(TAG, "Exception upgrading EmailProvider.db from 12 to 13 " + e); 1193 } 1194 oldVersion = 13; 1195 } 1196 if (oldVersion == 13) { 1197 try { 1198 db.execSQL("alter table " + Message.TABLE_NAME 1199 + " add column " + Message.SNIPPET 1200 +" text" + ";"); 1201 } catch (SQLException e) { 1202 // Shouldn't be needed unless we're debugging and interrupt the process 1203 Log.w(TAG, "Exception upgrading EmailProvider.db from 13 to 14 " + e); 1204 } 1205 oldVersion = 14; 1206 } 1207 if (oldVersion == 14) { 1208 try { 1209 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1210 + " add column " + Message.SNIPPET +" text" + ";"); 1211 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1212 + " add column " + Message.SNIPPET +" text" + ";"); 1213 } catch (SQLException e) { 1214 // Shouldn't be needed unless we're debugging and interrupt the process 1215 Log.w(TAG, "Exception upgrading EmailProvider.db from 14 to 15 " + e); 1216 } 1217 oldVersion = 15; 1218 } 1219 if (oldVersion == 15) { 1220 try { 1221 db.execSQL("alter table " + Attachment.TABLE_NAME 1222 + " add column " + Attachment.ACCOUNT_KEY +" integer" + ";"); 1223 // Update all existing attachments to add the accountKey data 1224 db.execSQL("update " + Attachment.TABLE_NAME + " set " + 1225 Attachment.ACCOUNT_KEY + "= (SELECT " + Message.TABLE_NAME + "." + 1226 Message.ACCOUNT_KEY + " from " + Message.TABLE_NAME + " where " + 1227 Message.TABLE_NAME + "." + Message.RECORD_ID + " = " + 1228 Attachment.TABLE_NAME + "." + Attachment.MESSAGE_KEY + ")"); 1229 } catch (SQLException e) { 1230 // Shouldn't be needed unless we're debugging and interrupt the process 1231 Log.w(TAG, "Exception upgrading EmailProvider.db from 15 to 16 " + e); 1232 } 1233 oldVersion = 16; 1234 } 1235 if (oldVersion == 16) { 1236 try { 1237 db.execSQL("alter table " + Mailbox.TABLE_NAME 1238 + " add column " + Mailbox.PARENT_KEY + " integer;"); 1239 } catch (SQLException e) { 1240 // Shouldn't be needed unless we're debugging and interrupt the process 1241 Log.w(TAG, "Exception upgrading EmailProvider.db from 16 to 17 " + e); 1242 } 1243 oldVersion = 17; 1244 } 1245 if (oldVersion == 17) { 1246 upgradeFromVersion17ToVersion18(db); 1247 oldVersion = 18; 1248 } 1249 if (oldVersion == 18) { 1250 try { 1251 db.execSQL("alter table " + Account.TABLE_NAME 1252 + " add column " + Account.POLICY_KEY + " integer;"); 1253 db.execSQL("drop trigger account_delete;"); 1254 db.execSQL(TRIGGER_ACCOUNT_DELETE); 1255 createPolicyTable(db); 1256 convertPolicyFlagsToPolicyTable(db); 1257 } catch (SQLException e) { 1258 // Shouldn't be needed unless we're debugging and interrupt the process 1259 Log.w(TAG, "Exception upgrading EmailProvider.db from 18 to 19 " + e); 1260 } 1261 oldVersion = 19; 1262 } 1263 if (oldVersion == 19) { 1264 try { 1265 db.execSQL("alter table " + Policy.TABLE_NAME 1266 + " add column " + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + 1267 " integer;"); 1268 db.execSQL("alter table " + Policy.TABLE_NAME 1269 + " add column " + PolicyColumns.DONT_ALLOW_CAMERA + " integer;"); 1270 db.execSQL("alter table " + Policy.TABLE_NAME 1271 + " add column " + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer;"); 1272 db.execSQL("alter table " + Policy.TABLE_NAME 1273 + " add column " + PolicyColumns.DONT_ALLOW_HTML + " integer;"); 1274 db.execSQL("alter table " + Policy.TABLE_NAME 1275 + " add column " + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer;"); 1276 db.execSQL("alter table " + Policy.TABLE_NAME 1277 + " add column " + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + 1278 " integer;"); 1279 db.execSQL("alter table " + Policy.TABLE_NAME 1280 + " add column " + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + 1281 " integer;"); 1282 db.execSQL("alter table " + Policy.TABLE_NAME 1283 + " add column " + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer;"); 1284 db.execSQL("alter table " + Policy.TABLE_NAME 1285 + " add column " + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer;"); 1286 db.execSQL("alter table " + Policy.TABLE_NAME 1287 + " add column " + PolicyColumns.PASSWORD_RECOVERY_ENABLED + 1288 " integer;"); 1289 } catch (SQLException e) { 1290 // Shouldn't be needed unless we're debugging and interrupt the process 1291 Log.w(TAG, "Exception upgrading EmailProvider.db from 19 to 20 " + e); 1292 } 1293 oldVersion = 20; 1294 } 1295 if (oldVersion == 20) { 1296 upgradeFromVersion20ToVersion21(db); 1297 oldVersion = 21; 1298 } 1299 if (oldVersion == 21) { 1300 upgradeFromVersion21ToVersion22(db, mContext); 1301 oldVersion = 22; 1302 } 1303 if (oldVersion == 22) { 1304 upgradeFromVersion22ToVersion23(db); 1305 oldVersion = 23; 1306 } 1307 if (oldVersion == 23) { 1308 upgradeFromVersion23ToVersion24(db); 1309 oldVersion = 24; 1310 } 1311 if (oldVersion == 24) { 1312 upgradeFromVersion24ToVersion25(db); 1313 oldVersion = 25; 1314 } 1315 if (oldVersion == 25) { 1316 upgradeFromVersion25ToVersion26(db); 1317 oldVersion = 26; 1318 } 1319 if (oldVersion == 26) { 1320 try { 1321 db.execSQL("alter table " + Message.TABLE_NAME 1322 + " add column " + Message.PROTOCOL_SEARCH_INFO + " text;"); 1323 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 1324 + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";"); 1325 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 1326 + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";"); 1327 } catch (SQLException e) { 1328 // Shouldn't be needed unless we're debugging and interrupt the process 1329 Log.w(TAG, "Exception upgrading EmailProvider.db from 26 to 27 " + e); 1330 } 1331 oldVersion = 27; 1332 } 1333 } 1334 1335 @Override 1336 public void onOpen(SQLiteDatabase db) { 1337 } 1338 } 1339 1340 @Override 1341 public int delete(Uri uri, String selection, String[] selectionArgs) { 1342 final int match = findMatch(uri, "delete"); 1343 Context context = getContext(); 1344 // Pick the correct database for this operation 1345 // If we're in a transaction already (which would happen during applyBatch), then the 1346 // body database is already attached to the email database and any attempt to use the 1347 // body database directly will result in a SQLiteException (the database is locked) 1348 SQLiteDatabase db = getDatabase(context); 1349 int table = match >> BASE_SHIFT; 1350 String id = "0"; 1351 boolean messageDeletion = false; 1352 ContentResolver resolver = context.getContentResolver(); 1353 1354 ContentCache cache = mContentCaches[table]; 1355 String tableName = TABLE_NAMES[table]; 1356 int result = -1; 1357 1358 try { 1359 switch (match) { 1360 // These are cases in which one or more Messages might get deleted, either by 1361 // cascade or explicitly 1362 case MAILBOX_ID: 1363 case MAILBOX: 1364 case ACCOUNT_ID: 1365 case ACCOUNT: 1366 case MESSAGE: 1367 case SYNCED_MESSAGE_ID: 1368 case MESSAGE_ID: 1369 // Handle lost Body records here, since this cannot be done in a trigger 1370 // The process is: 1371 // 1) Begin a transaction, ensuring that both databases are affected atomically 1372 // 2) Do the requested deletion, with cascading deletions handled in triggers 1373 // 3) End the transaction, committing all changes atomically 1374 // 1375 // Bodies are auto-deleted here; Attachments are auto-deleted via trigger 1376 messageDeletion = true; 1377 db.beginTransaction(); 1378 break; 1379 } 1380 switch (match) { 1381 case BODY_ID: 1382 case DELETED_MESSAGE_ID: 1383 case SYNCED_MESSAGE_ID: 1384 case MESSAGE_ID: 1385 case UPDATED_MESSAGE_ID: 1386 case ATTACHMENT_ID: 1387 case MAILBOX_ID: 1388 case ACCOUNT_ID: 1389 case HOSTAUTH_ID: 1390 case POLICY_ID: 1391 case QUICK_RESPONSE_ID: 1392 id = uri.getPathSegments().get(1); 1393 if (match == SYNCED_MESSAGE_ID) { 1394 // For synced messages, first copy the old message to the deleted table and 1395 // delete it from the updated table (in case it was updated first) 1396 // Note that this is all within a transaction, for atomicity 1397 db.execSQL(DELETED_MESSAGE_INSERT + id); 1398 db.execSQL(UPDATED_MESSAGE_DELETE + id); 1399 } 1400 if (cache != null) { 1401 cache.lock(id); 1402 } 1403 try { 1404 result = db.delete(tableName, whereWithId(id, selection), selectionArgs); 1405 if (cache != null) { 1406 switch(match) { 1407 case ACCOUNT_ID: 1408 // Account deletion will clear all of the caches, as HostAuth's, 1409 // Mailboxes, and Messages will be deleted in the process 1410 mCacheMailbox.invalidate("Delete", uri, selection); 1411 mCacheHostAuth.invalidate("Delete", uri, selection); 1412 mCachePolicy.invalidate("Delete", uri, selection); 1413 //$FALL-THROUGH$ 1414 case MAILBOX_ID: 1415 // Mailbox deletion will clear the Message cache 1416 mCacheMessage.invalidate("Delete", uri, selection); 1417 //$FALL-THROUGH$ 1418 case SYNCED_MESSAGE_ID: 1419 case MESSAGE_ID: 1420 case HOSTAUTH_ID: 1421 case POLICY_ID: 1422 cache.invalidate("Delete", uri, selection); 1423 // Make sure all data is properly cached 1424 if (match != MESSAGE_ID) { 1425 preCacheData(); 1426 } 1427 break; 1428 } 1429 } 1430 } finally { 1431 if (cache != null) { 1432 cache.unlock(id); 1433 } 1434 } 1435 break; 1436 case ATTACHMENTS_MESSAGE_ID: 1437 // All attachments for the given message 1438 id = uri.getPathSegments().get(2); 1439 result = db.delete(tableName, 1440 whereWith(Attachment.MESSAGE_KEY + "=" + id, selection), selectionArgs); 1441 break; 1442 1443 case BODY: 1444 case MESSAGE: 1445 case DELETED_MESSAGE: 1446 case UPDATED_MESSAGE: 1447 case ATTACHMENT: 1448 case MAILBOX: 1449 case ACCOUNT: 1450 case HOSTAUTH: 1451 case POLICY: 1452 switch(match) { 1453 // See the comments above for deletion of ACCOUNT_ID, etc 1454 case ACCOUNT: 1455 mCacheMailbox.invalidate("Delete", uri, selection); 1456 mCacheHostAuth.invalidate("Delete", uri, selection); 1457 mCachePolicy.invalidate("Delete", uri, selection); 1458 //$FALL-THROUGH$ 1459 case MAILBOX: 1460 mCacheMessage.invalidate("Delete", uri, selection); 1461 //$FALL-THROUGH$ 1462 case MESSAGE: 1463 case HOSTAUTH: 1464 case POLICY: 1465 cache.invalidate("Delete", uri, selection); 1466 break; 1467 } 1468 result = db.delete(tableName, selection, selectionArgs); 1469 switch(match) { 1470 case ACCOUNT: 1471 case MAILBOX: 1472 case HOSTAUTH: 1473 case POLICY: 1474 // Make sure all data is properly cached 1475 preCacheData(); 1476 break; 1477 } 1478 break; 1479 1480 default: 1481 throw new IllegalArgumentException("Unknown URI " + uri); 1482 } 1483 if (messageDeletion) { 1484 if (match == MESSAGE_ID) { 1485 // Delete the Body record associated with the deleted message 1486 db.execSQL(DELETE_BODY + id); 1487 } else { 1488 // Delete any orphaned Body records 1489 db.execSQL(DELETE_ORPHAN_BODIES); 1490 } 1491 db.setTransactionSuccessful(); 1492 } 1493 } catch (SQLiteException e) { 1494 checkDatabases(); 1495 throw e; 1496 } finally { 1497 if (messageDeletion) { 1498 db.endTransaction(); 1499 } 1500 } 1501 1502 // Notify all notifier cursors 1503 sendNotifierChange(getBaseNotificationUri(match), NOTIFICATION_OP_DELETE, id); 1504 1505 // Notify all email content cursors 1506 resolver.notifyChange(EmailContent.CONTENT_URI, null); 1507 return result; 1508 } 1509 1510 @Override 1511 // Use the email- prefix because message, mailbox, and account are so generic (e.g. SMS, IM) 1512 public String getType(Uri uri) { 1513 int match = findMatch(uri, "getType"); 1514 switch (match) { 1515 case BODY_ID: 1516 return "vnd.android.cursor.item/email-body"; 1517 case BODY: 1518 return "vnd.android.cursor.dir/email-body"; 1519 case UPDATED_MESSAGE_ID: 1520 case MESSAGE_ID: 1521 // NOTE: According to the framework folks, we're supposed to invent mime types as 1522 // a way of passing information to drag & drop recipients. 1523 // If there's a mailboxId parameter in the url, we respond with a mime type that 1524 // has -n appended, where n is the mailboxId of the message. The drag & drop code 1525 // uses this information to know not to allow dragging the item to its own mailbox 1526 String mimeType = EMAIL_MESSAGE_MIME_TYPE; 1527 String mailboxId = uri.getQueryParameter(MESSAGE_URI_PARAMETER_MAILBOX_ID); 1528 if (mailboxId != null) { 1529 mimeType += "-" + mailboxId; 1530 } 1531 return mimeType; 1532 case UPDATED_MESSAGE: 1533 case MESSAGE: 1534 return "vnd.android.cursor.dir/email-message"; 1535 case MAILBOX: 1536 return "vnd.android.cursor.dir/email-mailbox"; 1537 case MAILBOX_ID: 1538 return "vnd.android.cursor.item/email-mailbox"; 1539 case ACCOUNT: 1540 return "vnd.android.cursor.dir/email-account"; 1541 case ACCOUNT_ID: 1542 return "vnd.android.cursor.item/email-account"; 1543 case ATTACHMENTS_MESSAGE_ID: 1544 case ATTACHMENT: 1545 return "vnd.android.cursor.dir/email-attachment"; 1546 case ATTACHMENT_ID: 1547 return EMAIL_ATTACHMENT_MIME_TYPE; 1548 case HOSTAUTH: 1549 return "vnd.android.cursor.dir/email-hostauth"; 1550 case HOSTAUTH_ID: 1551 return "vnd.android.cursor.item/email-hostauth"; 1552 default: 1553 throw new IllegalArgumentException("Unknown URI " + uri); 1554 } 1555 } 1556 1557 @Override 1558 public Uri insert(Uri uri, ContentValues values) { 1559 int match = findMatch(uri, "insert"); 1560 Context context = getContext(); 1561 ContentResolver resolver = context.getContentResolver(); 1562 1563 // See the comment at delete(), above 1564 SQLiteDatabase db = getDatabase(context); 1565 int table = match >> BASE_SHIFT; 1566 String id = "0"; 1567 long longId; 1568 1569 // We do NOT allow setting of unreadCount/messageCount via the provider 1570 // These columns are maintained via triggers 1571 if (match == MAILBOX_ID || match == MAILBOX) { 1572 values.put(MailboxColumns.UNREAD_COUNT, 0); 1573 values.put(MailboxColumns.MESSAGE_COUNT, 0); 1574 } 1575 1576 Uri resultUri = null; 1577 1578 try { 1579 switch (match) { 1580 // NOTE: It is NOT legal for production code to insert directly into UPDATED_MESSAGE 1581 // or DELETED_MESSAGE; see the comment below for details 1582 case UPDATED_MESSAGE: 1583 case DELETED_MESSAGE: 1584 case MESSAGE: 1585 case BODY: 1586 case ATTACHMENT: 1587 case MAILBOX: 1588 case ACCOUNT: 1589 case HOSTAUTH: 1590 case POLICY: 1591 case QUICK_RESPONSE: 1592 longId = db.insert(TABLE_NAMES[table], "foo", values); 1593 resultUri = ContentUris.withAppendedId(uri, longId); 1594 switch(match) { 1595 case MAILBOX: 1596 if (values.containsKey(MailboxColumns.TYPE)) { 1597 // Only cache special mailbox types 1598 int type = values.getAsInteger(MailboxColumns.TYPE); 1599 if (type != Mailbox.TYPE_INBOX && type != Mailbox.TYPE_OUTBOX && 1600 type != Mailbox.TYPE_DRAFTS && type != Mailbox.TYPE_SENT && 1601 type != Mailbox.TYPE_TRASH && type != Mailbox.TYPE_SEARCH) { 1602 break; 1603 } 1604 } 1605 //$FALL-THROUGH$ 1606 case ACCOUNT: 1607 case HOSTAUTH: 1608 case POLICY: 1609 // Cache new account, host auth, policy, and some mailbox rows 1610 Cursor c = query(resultUri, CACHE_PROJECTIONS[table], null, null, null); 1611 if (c != null) { 1612 if (match == MAILBOX) { 1613 addToMailboxTypeMap(c); 1614 } else if (match == ACCOUNT) { 1615 getOrCreateAccountMailboxTypeMap(longId); 1616 } 1617 c.close(); 1618 } 1619 break; 1620 } 1621 // Clients shouldn't normally be adding rows to these tables, as they are 1622 // maintained by triggers. However, we need to be able to do this for unit 1623 // testing, so we allow the insert and then throw the same exception that we 1624 // would if this weren't allowed. 1625 if (match == UPDATED_MESSAGE || match == DELETED_MESSAGE) { 1626 throw new IllegalArgumentException("Unknown URL " + uri); 1627 } 1628 if (match == ATTACHMENT) { 1629 int flags = 0; 1630 if (values.containsKey(Attachment.FLAGS)) { 1631 flags = values.getAsInteger(Attachment.FLAGS); 1632 } 1633 // Report all new attachments to the download service 1634 AttachmentDownloadService.attachmentChanged(longId, flags); 1635 } 1636 break; 1637 case MAILBOX_ID: 1638 // This implies adding a message to a mailbox 1639 // Hmm, a problem here is that we can't link the account as well, so it must be 1640 // already in the values... 1641 longId = Long.parseLong(uri.getPathSegments().get(1)); 1642 values.put(MessageColumns.MAILBOX_KEY, longId); 1643 return insert(Message.CONTENT_URI, values); // Recurse 1644 case MESSAGE_ID: 1645 // This implies adding an attachment to a message. 1646 id = uri.getPathSegments().get(1); 1647 longId = Long.parseLong(id); 1648 values.put(AttachmentColumns.MESSAGE_KEY, longId); 1649 return insert(Attachment.CONTENT_URI, values); // Recurse 1650 case ACCOUNT_ID: 1651 // This implies adding a mailbox to an account. 1652 longId = Long.parseLong(uri.getPathSegments().get(1)); 1653 values.put(MailboxColumns.ACCOUNT_KEY, longId); 1654 return insert(Mailbox.CONTENT_URI, values); // Recurse 1655 case ATTACHMENTS_MESSAGE_ID: 1656 longId = db.insert(TABLE_NAMES[table], "foo", values); 1657 resultUri = ContentUris.withAppendedId(Attachment.CONTENT_URI, longId); 1658 break; 1659 default: 1660 throw new IllegalArgumentException("Unknown URL " + uri); 1661 } 1662 } catch (SQLiteException e) { 1663 checkDatabases(); 1664 throw e; 1665 } 1666 1667 // Notify all notifier cursors 1668 sendNotifierChange(getBaseNotificationUri(match), NOTIFICATION_OP_INSERT, id); 1669 1670 // Notify all existing cursors. 1671 resolver.notifyChange(EmailContent.CONTENT_URI, null); 1672 return resultUri; 1673 } 1674 1675 @Override 1676 public boolean onCreate() { 1677 checkDatabases(); 1678 return false; 1679 } 1680 1681 /** 1682 * The idea here is that the two databases (EmailProvider.db and EmailProviderBody.db must 1683 * always be in sync (i.e. there are two database or NO databases). This code will delete 1684 * any "orphan" database, so that both will be created together. Note that an "orphan" database 1685 * will exist after either of the individual databases is deleted due to data corruption. 1686 */ 1687 public void checkDatabases() { 1688 // Uncache the databases 1689 if (mDatabase != null) { 1690 mDatabase = null; 1691 } 1692 if (mBodyDatabase != null) { 1693 mBodyDatabase = null; 1694 } 1695 // Look for orphans, and delete as necessary; these must always be in sync 1696 File databaseFile = getContext().getDatabasePath(DATABASE_NAME); 1697 File bodyFile = getContext().getDatabasePath(BODY_DATABASE_NAME); 1698 1699 // TODO Make sure attachments are deleted 1700 if (databaseFile.exists() && !bodyFile.exists()) { 1701 Log.w(TAG, "Deleting orphaned EmailProvider database..."); 1702 databaseFile.delete(); 1703 } else if (bodyFile.exists() && !databaseFile.exists()) { 1704 Log.w(TAG, "Deleting orphaned EmailProviderBody database..."); 1705 bodyFile.delete(); 1706 } 1707 } 1708 1709 @Override 1710 public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, 1711 String sortOrder) { 1712 long time = 0L; 1713 if (Email.DEBUG) { 1714 time = System.nanoTime(); 1715 } 1716 Cursor c = null; 1717 int match; 1718 try { 1719 match = findMatch(uri, "query"); 1720 } catch (IllegalArgumentException e) { 1721 String uriString = uri.toString(); 1722 // If we were passed an illegal uri, see if it ends in /-1 1723 // if so, and if substituting 0 for -1 results in a valid uri, return an empty cursor 1724 if (uriString != null && uriString.endsWith("/-1")) { 1725 uri = Uri.parse(uriString.substring(0, uriString.length() - 2) + "0"); 1726 match = findMatch(uri, "query"); 1727 switch (match) { 1728 case BODY_ID: 1729 case MESSAGE_ID: 1730 case DELETED_MESSAGE_ID: 1731 case UPDATED_MESSAGE_ID: 1732 case ATTACHMENT_ID: 1733 case MAILBOX_ID: 1734 case ACCOUNT_ID: 1735 case HOSTAUTH_ID: 1736 case POLICY_ID: 1737 return new MatrixCursor(projection, 0); 1738 } 1739 } 1740 throw e; 1741 } 1742 Context context = getContext(); 1743 // See the comment at delete(), above 1744 SQLiteDatabase db = getDatabase(context); 1745 int table = match >> BASE_SHIFT; 1746 String limit = uri.getQueryParameter(EmailContent.PARAMETER_LIMIT); 1747 String id; 1748 1749 // Find the cache for this query's table (if any) 1750 ContentCache cache = null; 1751 String tableName = TABLE_NAMES[table]; 1752 // We can only use the cache if there's no selection 1753 if (selection == null) { 1754 cache = mContentCaches[table]; 1755 } 1756 if (cache == null) { 1757 ContentCache.notCacheable(uri, selection); 1758 } 1759 1760 try { 1761 switch (match) { 1762 case ACCOUNT_DEFAULT_ID: 1763 // Start with a snapshot of the cache 1764 Map<String, Cursor> accountCache = mCacheAccount.getSnapshot(); 1765 long accountId = Account.NO_ACCOUNT; 1766 // Find the account with "isDefault" set, or the lowest account ID otherwise. 1767 // Note that the snapshot from the cached isn't guaranteed to be sorted in any 1768 // way. 1769 Collection<Cursor> accounts = accountCache.values(); 1770 for (Cursor accountCursor: accounts) { 1771 // For now, at least, we can have zero count cursors (e.g. if someone looks 1772 // up a non-existent id); we need to skip these 1773 if (accountCursor.moveToFirst()) { 1774 boolean isDefault = 1775 accountCursor.getInt(Account.CONTENT_IS_DEFAULT_COLUMN) == 1; 1776 long iterId = accountCursor.getLong(Account.CONTENT_ID_COLUMN); 1777 // We'll remember this one if it's the default or the first one we see 1778 if (isDefault) { 1779 accountId = iterId; 1780 break; 1781 } else if ((accountId == Account.NO_ACCOUNT) || (iterId < accountId)) { 1782 accountId = iterId; 1783 } 1784 } 1785 } 1786 // Return a cursor with an id projection 1787 MatrixCursor mc = new MatrixCursor(EmailContent.ID_PROJECTION); 1788 mc.addRow(new Object[] {accountId}); 1789 return mc; 1790 case MAILBOX_ID_FROM_ACCOUNT_AND_TYPE: 1791 // Get accountId and type and find the mailbox in our map 1792 List<String> pathSegments = uri.getPathSegments(); 1793 accountId = Long.parseLong(pathSegments.get(1)); 1794 int type = Integer.parseInt(pathSegments.get(2)); 1795 long mailboxId = getMailboxIdFromMailboxTypeMap(accountId, type); 1796 // Return a cursor with an id projection 1797 mc = new MatrixCursor(EmailContent.ID_PROJECTION); 1798 mc.addRow(new Object[] {mailboxId}); 1799 return mc; 1800 case BODY: 1801 case MESSAGE: 1802 case UPDATED_MESSAGE: 1803 case DELETED_MESSAGE: 1804 case ATTACHMENT: 1805 case MAILBOX: 1806 case ACCOUNT: 1807 case HOSTAUTH: 1808 case POLICY: 1809 case QUICK_RESPONSE: 1810 // Special-case "count of accounts"; it's common and we always know it 1811 if (match == ACCOUNT && Arrays.equals(projection, EmailContent.COUNT_COLUMNS) && 1812 selection == null && limit.equals("1")) { 1813 int accountCount = mMailboxTypeMap.size(); 1814 // In the rare case there are MAX_CACHED_ACCOUNTS or more, we can't do this 1815 if (accountCount < MAX_CACHED_ACCOUNTS) { 1816 mc = new MatrixCursor(projection, 1); 1817 mc.addRow(new Object[] {accountCount}); 1818 return mc; 1819 } 1820 } 1821 c = db.query(tableName, projection, 1822 selection, selectionArgs, null, null, sortOrder, limit); 1823 break; 1824 case BODY_ID: 1825 case MESSAGE_ID: 1826 case DELETED_MESSAGE_ID: 1827 case UPDATED_MESSAGE_ID: 1828 case ATTACHMENT_ID: 1829 case MAILBOX_ID: 1830 case ACCOUNT_ID: 1831 case HOSTAUTH_ID: 1832 case POLICY_ID: 1833 case QUICK_RESPONSE_ID: 1834 id = uri.getPathSegments().get(1); 1835 if (cache != null) { 1836 c = cache.getCachedCursor(id, projection); 1837 } 1838 if (c == null) { 1839 CacheToken token = null; 1840 if (cache != null) { 1841 token = cache.getCacheToken(id); 1842 } 1843 c = db.query(tableName, projection, whereWithId(id, selection), 1844 selectionArgs, null, null, sortOrder, limit); 1845 if (cache != null) { 1846 c = cache.putCursor(c, id, projection, token); 1847 } 1848 } 1849 break; 1850 case ATTACHMENTS_MESSAGE_ID: 1851 // All attachments for the given message 1852 id = uri.getPathSegments().get(2); 1853 c = db.query(Attachment.TABLE_NAME, projection, 1854 whereWith(Attachment.MESSAGE_KEY + "=" + id, selection), 1855 selectionArgs, null, null, sortOrder, limit); 1856 break; 1857 case QUICK_RESPONSE_ACCOUNT_ID: 1858 // All quick responses for the given account 1859 id = uri.getPathSegments().get(2); 1860 c = db.query(QuickResponse.TABLE_NAME, projection, 1861 whereWith(QuickResponse.ACCOUNT_KEY + "=" + id, selection), 1862 selectionArgs, null, null, sortOrder); 1863 break; 1864 default: 1865 throw new IllegalArgumentException("Unknown URI " + uri); 1866 } 1867 } catch (SQLiteException e) { 1868 checkDatabases(); 1869 throw e; 1870 } catch (RuntimeException e) { 1871 checkDatabases(); 1872 e.printStackTrace(); 1873 throw e; 1874 } finally { 1875 if (cache != null && c != null && Email.DEBUG) { 1876 cache.recordQueryTime(c, System.nanoTime() - time); 1877 } 1878 } 1879 1880 if ((c != null) && !isTemporary()) { 1881 c.setNotificationUri(getContext().getContentResolver(), uri); 1882 } 1883 return c; 1884 } 1885 1886 private String whereWithId(String id, String selection) { 1887 StringBuilder sb = new StringBuilder(256); 1888 sb.append("_id="); 1889 sb.append(id); 1890 if (selection != null) { 1891 sb.append(" AND ("); 1892 sb.append(selection); 1893 sb.append(')'); 1894 } 1895 return sb.toString(); 1896 } 1897 1898 /** 1899 * Combine a locally-generated selection with a user-provided selection 1900 * 1901 * This introduces risk that the local selection might insert incorrect chars 1902 * into the SQL, so use caution. 1903 * 1904 * @param where locally-generated selection, must not be null 1905 * @param selection user-provided selection, may be null 1906 * @return a single selection string 1907 */ 1908 private String whereWith(String where, String selection) { 1909 if (selection == null) { 1910 return where; 1911 } 1912 StringBuilder sb = new StringBuilder(where); 1913 sb.append(" AND ("); 1914 sb.append(selection); 1915 sb.append(')'); 1916 1917 return sb.toString(); 1918 } 1919 1920 /** 1921 * Restore a HostAuth from a database, given its unique id 1922 * @param db the database 1923 * @param id the unique id (_id) of the row 1924 * @return a fully populated HostAuth or null if the row does not exist 1925 */ 1926 private static HostAuth restoreHostAuth(SQLiteDatabase db, long id) { 1927 Cursor c = db.query(HostAuth.TABLE_NAME, HostAuth.CONTENT_PROJECTION, 1928 HostAuth.RECORD_ID + "=?", new String[] {Long.toString(id)}, null, null, null); 1929 try { 1930 if (c.moveToFirst()) { 1931 HostAuth hostAuth = new HostAuth(); 1932 hostAuth.restore(c); 1933 return hostAuth; 1934 } 1935 return null; 1936 } finally { 1937 c.close(); 1938 } 1939 } 1940 1941 /** 1942 * Copy the Account and HostAuth tables from one database to another 1943 * @param fromDatabase the source database 1944 * @param toDatabase the destination database 1945 * @return the number of accounts copied, or -1 if an error occurred 1946 */ 1947 private static int copyAccountTables(SQLiteDatabase fromDatabase, SQLiteDatabase toDatabase) { 1948 if (fromDatabase == null || toDatabase == null) return -1; 1949 int copyCount = 0; 1950 try { 1951 // Lock both databases; for the "from" database, we don't want anyone changing it from 1952 // under us; for the "to" database, we want to make the operation atomic 1953 fromDatabase.beginTransaction(); 1954 toDatabase.beginTransaction(); 1955 // Delete anything hanging around here 1956 toDatabase.delete(Account.TABLE_NAME, null, null); 1957 toDatabase.delete(HostAuth.TABLE_NAME, null, null); 1958 // Get our account cursor 1959 Cursor c = fromDatabase.query(Account.TABLE_NAME, Account.CONTENT_PROJECTION, 1960 null, null, null, null, null); 1961 boolean noErrors = true; 1962 try { 1963 // Loop through accounts, copying them and associated host auth's 1964 while (c.moveToNext()) { 1965 Account account = new Account(); 1966 account.restore(c); 1967 1968 // Clear security sync key and sync key, as these were specific to the state of 1969 // the account, and we've reset that... 1970 // Clear policy key so that we can re-establish policies from the server 1971 // TODO This is pretty EAS specific, but there's a lot of that around 1972 account.mSecuritySyncKey = null; 1973 account.mSyncKey = null; 1974 account.mPolicyKey = 0; 1975 1976 // Copy host auth's and update foreign keys 1977 HostAuth hostAuth = restoreHostAuth(fromDatabase, account.mHostAuthKeyRecv); 1978 // The account might have gone away, though very unlikely 1979 if (hostAuth == null) continue; 1980 account.mHostAuthKeyRecv = toDatabase.insert(HostAuth.TABLE_NAME, null, 1981 hostAuth.toContentValues()); 1982 // EAS accounts have no send HostAuth 1983 if (account.mHostAuthKeySend > 0) { 1984 hostAuth = restoreHostAuth(fromDatabase, account.mHostAuthKeySend); 1985 // Belt and suspenders; I can't imagine that this is possible, since we 1986 // checked the validity of the account above, and the database is now locked 1987 if (hostAuth == null) continue; 1988 account.mHostAuthKeySend = toDatabase.insert(HostAuth.TABLE_NAME, null, 1989 hostAuth.toContentValues()); 1990 } 1991 // Now, create the account in the "to" database 1992 toDatabase.insert(Account.TABLE_NAME, null, account.toContentValues()); 1993 copyCount++; 1994 } 1995 } catch (SQLiteException e) { 1996 noErrors = false; 1997 copyCount = -1; 1998 } finally { 1999 fromDatabase.endTransaction(); 2000 if (noErrors) { 2001 // Say it's ok to commit 2002 toDatabase.setTransactionSuccessful(); 2003 } 2004 toDatabase.endTransaction(); 2005 c.close(); 2006 } 2007 } catch (SQLiteException e) { 2008 copyCount = -1; 2009 } 2010 return copyCount; 2011 } 2012 2013 private static SQLiteDatabase getBackupDatabase(Context context) { 2014 DatabaseHelper helper = new DatabaseHelper(context, BACKUP_DATABASE_NAME); 2015 return helper.getWritableDatabase(); 2016 } 2017 2018 /** 2019 * Backup account data, returning the number of accounts backed up 2020 */ 2021 private static int backupAccounts(Context context, SQLiteDatabase mainDatabase) { 2022 if (Email.DEBUG) { 2023 Log.d(TAG, "backupAccounts..."); 2024 } 2025 SQLiteDatabase backupDatabase = getBackupDatabase(context); 2026 try { 2027 int numBackedUp = copyAccountTables(mainDatabase, backupDatabase); 2028 if (numBackedUp < 0) { 2029 Log.e(TAG, "Account backup failed!"); 2030 } else if (Email.DEBUG) { 2031 Log.d(TAG, "Backed up " + numBackedUp + " accounts..."); 2032 } 2033 return numBackedUp; 2034 } finally { 2035 if (backupDatabase != null) { 2036 backupDatabase.close(); 2037 } 2038 } 2039 } 2040 2041 /** 2042 * Restore account data, returning the number of accounts restored 2043 */ 2044 private static int restoreAccounts(Context context, SQLiteDatabase mainDatabase) { 2045 if (Email.DEBUG) { 2046 Log.d(TAG, "restoreAccounts..."); 2047 } 2048 SQLiteDatabase backupDatabase = getBackupDatabase(context); 2049 try { 2050 int numRecovered = copyAccountTables(backupDatabase, mainDatabase); 2051 if (numRecovered > 0) { 2052 Log.e(TAG, "Recovered " + numRecovered + " accounts!"); 2053 } else if (numRecovered < 0) { 2054 Log.e(TAG, "Account recovery failed?"); 2055 } else if (Email.DEBUG) { 2056 Log.d(TAG, "No accounts to restore..."); 2057 } 2058 return numRecovered; 2059 } finally { 2060 if (backupDatabase != null) { 2061 backupDatabase.close(); 2062 } 2063 } 2064 } 2065 2066 @Override 2067 public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { 2068 // Handle this special case the fastest possible way 2069 if (uri == INTEGRITY_CHECK_URI) { 2070 checkDatabases(); 2071 return 0; 2072 } else if (uri == ACCOUNT_BACKUP_URI) { 2073 return backupAccounts(getContext(), getDatabase(getContext())); 2074 } 2075 2076 // Notify all existing cursors, except for ACCOUNT_RESET_NEW_COUNT(_ID) 2077 Uri notificationUri = EmailContent.CONTENT_URI; 2078 2079 int match = findMatch(uri, "update"); 2080 Context context = getContext(); 2081 ContentResolver resolver = context.getContentResolver(); 2082 // See the comment at delete(), above 2083 SQLiteDatabase db = getDatabase(context); 2084 int table = match >> BASE_SHIFT; 2085 int result; 2086 2087 // We do NOT allow setting of unreadCount/messageCount via the provider 2088 // These columns are maintained via triggers 2089 if (match == MAILBOX_ID || match == MAILBOX) { 2090 values.remove(MailboxColumns.UNREAD_COUNT); 2091 values.remove(MailboxColumns.MESSAGE_COUNT); 2092 } 2093 2094 ContentCache cache = mContentCaches[table]; 2095 String tableName = TABLE_NAMES[table]; 2096 String id = "0"; 2097 2098 try { 2099outer: 2100 switch (match) { 2101 case MAILBOX_ID_ADD_TO_FIELD: 2102 case ACCOUNT_ID_ADD_TO_FIELD: 2103 id = uri.getPathSegments().get(1); 2104 String field = values.getAsString(EmailContent.FIELD_COLUMN_NAME); 2105 Long add = values.getAsLong(EmailContent.ADD_COLUMN_NAME); 2106 if (field == null || add == null) { 2107 throw new IllegalArgumentException("No field/add specified " + uri); 2108 } 2109 ContentValues actualValues = new ContentValues(); 2110 if (cache != null) { 2111 cache.lock(id); 2112 } 2113 try { 2114 db.beginTransaction(); 2115 try { 2116 Cursor c = db.query(tableName, 2117 new String[] {EmailContent.RECORD_ID, field}, 2118 whereWithId(id, selection), 2119 selectionArgs, null, null, null); 2120 try { 2121 result = 0; 2122 String[] bind = new String[1]; 2123 if (c.moveToNext()) { 2124 bind[0] = c.getString(0); // _id 2125 long value = c.getLong(1) + add; 2126 actualValues.put(field, value); 2127 result = db.update(tableName, actualValues, ID_EQUALS, bind); 2128 } 2129 db.setTransactionSuccessful(); 2130 } finally { 2131 c.close(); 2132 } 2133 } finally { 2134 db.endTransaction(); 2135 } 2136 } finally { 2137 if (cache != null) { 2138 cache.unlock(id, actualValues); 2139 } 2140 } 2141 break; 2142 case SYNCED_MESSAGE_ID: 2143 case UPDATED_MESSAGE_ID: 2144 case MESSAGE_ID: 2145 case BODY_ID: 2146 case ATTACHMENT_ID: 2147 case MAILBOX_ID: 2148 case ACCOUNT_ID: 2149 case HOSTAUTH_ID: 2150 case QUICK_RESPONSE_ID: 2151 case POLICY_ID: 2152 id = uri.getPathSegments().get(1); 2153 if (cache != null) { 2154 cache.lock(id); 2155 } 2156 try { 2157 if (match == SYNCED_MESSAGE_ID) { 2158 // For synced messages, first copy the old message to the updated table 2159 // Note the insert or ignore semantics, guaranteeing that only the first 2160 // update will be reflected in the updated message table; therefore this 2161 // row will always have the "original" data 2162 db.execSQL(UPDATED_MESSAGE_INSERT + id); 2163 } else if (match == MESSAGE_ID) { 2164 db.execSQL(UPDATED_MESSAGE_DELETE + id); 2165 } 2166 result = db.update(tableName, values, whereWithId(id, selection), 2167 selectionArgs); 2168 } catch (SQLiteException e) { 2169 // Null out values (so they aren't cached) and re-throw 2170 values = null; 2171 throw e; 2172 } finally { 2173 if (cache != null) { 2174 cache.unlock(id, values); 2175 } 2176 } 2177 if (match == ATTACHMENT_ID) { 2178 if (values.containsKey(Attachment.FLAGS)) { 2179 int flags = values.getAsInteger(Attachment.FLAGS); 2180 AttachmentDownloadService.attachmentChanged( 2181 Integer.parseInt(id), flags); 2182 } 2183 } 2184 break; 2185 case BODY: 2186 case MESSAGE: 2187 case UPDATED_MESSAGE: 2188 case ATTACHMENT: 2189 case MAILBOX: 2190 case ACCOUNT: 2191 case HOSTAUTH: 2192 case POLICY: 2193 switch(match) { 2194 // To avoid invalidating the cache on updates, we execute them one at a 2195 // time using the XXX_ID uri; these are all executed atomically 2196 case ACCOUNT: 2197 case MAILBOX: 2198 case HOSTAUTH: 2199 case POLICY: 2200 Cursor c = db.query(tableName, EmailContent.ID_PROJECTION, 2201 selection, selectionArgs, null, null, null); 2202 db.beginTransaction(); 2203 result = 0; 2204 try { 2205 while (c.moveToNext()) { 2206 update(ContentUris.withAppendedId( 2207 uri, c.getLong(EmailContent.ID_PROJECTION_COLUMN)), 2208 values, null, null); 2209 result++; 2210 } 2211 db.setTransactionSuccessful(); 2212 } finally { 2213 db.endTransaction(); 2214 c.close(); 2215 } 2216 break outer; 2217 // Any cached table other than those above should be invalidated here 2218 case MESSAGE: 2219 // If we're doing some generic update, the whole cache needs to be 2220 // invalidated. This case should be quite rare 2221 cache.invalidate("Update", uri, selection); 2222 //$FALL-THROUGH$ 2223 default: 2224 result = db.update(tableName, values, selection, selectionArgs); 2225 break outer; 2226 } 2227 case ACCOUNT_RESET_NEW_COUNT_ID: 2228 id = uri.getPathSegments().get(1); 2229 if (cache != null) { 2230 cache.lock(id); 2231 } 2232 ContentValues newMessageCount = CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT; 2233 if (values != null) { 2234 Long set = values.getAsLong(EmailContent.SET_COLUMN_NAME); 2235 if (set != null) { 2236 newMessageCount = new ContentValues(); 2237 newMessageCount.put(Account.NEW_MESSAGE_COUNT, set); 2238 } 2239 } 2240 try { 2241 result = db.update(tableName, newMessageCount, 2242 whereWithId(id, selection), selectionArgs); 2243 } finally { 2244 if (cache != null) { 2245 cache.unlock(id, values); 2246 } 2247 } 2248 notificationUri = Account.CONTENT_URI; // Only notify account cursors. 2249 break; 2250 case ACCOUNT_RESET_NEW_COUNT: 2251 result = db.update(tableName, CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT, 2252 selection, selectionArgs); 2253 // Affects all accounts. Just invalidate all account cache. 2254 cache.invalidate("Reset all new counts", null, null); 2255 notificationUri = Account.CONTENT_URI; // Only notify account cursors. 2256 break; 2257 default: 2258 throw new IllegalArgumentException("Unknown URI " + uri); 2259 } 2260 } catch (SQLiteException e) { 2261 checkDatabases(); 2262 throw e; 2263 } 2264 2265 // Notify all notifier cursors 2266 sendNotifierChange(getBaseNotificationUri(match), NOTIFICATION_OP_UPDATE, id); 2267 2268 resolver.notifyChange(notificationUri, null); 2269 return result; 2270 } 2271 2272 /** 2273 * Returns the base notification URI for the given content type. 2274 * 2275 * @param match The type of content that was modified. 2276 */ 2277 private Uri getBaseNotificationUri(int match) { 2278 Uri baseUri = null; 2279 switch (match) { 2280 case MESSAGE: 2281 case MESSAGE_ID: 2282 case SYNCED_MESSAGE_ID: 2283 baseUri = Message.NOTIFIER_URI; 2284 break; 2285 case ACCOUNT: 2286 case ACCOUNT_ID: 2287 baseUri = Account.NOTIFIER_URI; 2288 break; 2289 } 2290 return baseUri; 2291 } 2292 2293 /** 2294 * Sends a change notification to any cursors observers of the given base URI. The final 2295 * notification URI is dynamically built to contain the specified information. It will be 2296 * of the format <<baseURI>>/<<op>>/<<id>>; where <<op>> and <<id>> are optional depending 2297 * upon the given values. 2298 * NOTE: If <<op>> is specified, notifications for <<baseURI>>/<<id>> will NOT be invoked. 2299 * If this is necessary, it can be added. However, due to the implementation of 2300 * {@link ContentObserver}, observers of <<baseURI>> will receive multiple notifications. 2301 * 2302 * @param baseUri The base URI to send notifications to. Must be able to take appended IDs. 2303 * @param op Optional operation to be appended to the URI. 2304 * @param id If a positive value, the ID to append to the base URI. Otherwise, no ID will be 2305 * appended to the base URI. 2306 */ 2307 private void sendNotifierChange(Uri baseUri, String op, String id) { 2308 if (baseUri == null) return; 2309 2310 final ContentResolver resolver = getContext().getContentResolver(); 2311 2312 // Append the operation, if specified 2313 if (op != null) { 2314 baseUri = baseUri.buildUpon().appendEncodedPath(op).build(); 2315 } 2316 2317 long longId = 0L; 2318 try { 2319 longId = Long.valueOf(id); 2320 } catch (NumberFormatException ignore) {} 2321 if (longId > 0) { 2322 resolver.notifyChange(ContentUris.withAppendedId(baseUri, longId), null); 2323 } else { 2324 resolver.notifyChange(baseUri, null); 2325 } 2326 } 2327 2328 @Override 2329 public ContentProviderResult[] applyBatch(ArrayList<ContentProviderOperation> operations) 2330 throws OperationApplicationException { 2331 Context context = getContext(); 2332 SQLiteDatabase db = getDatabase(context); 2333 db.beginTransaction(); 2334 try { 2335 ContentProviderResult[] results = super.applyBatch(operations); 2336 db.setTransactionSuccessful(); 2337 return results; 2338 } finally { 2339 db.endTransaction(); 2340 } 2341 } 2342 2343 /** Counts the number of messages in each mailbox, and updates the message count column. */ 2344 @VisibleForTesting 2345 static void recalculateMessageCount(SQLiteDatabase db) { 2346 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 2347 "= (select count(*) from " + Message.TABLE_NAME + 2348 " where " + Message.MAILBOX_KEY + " = " + 2349 Mailbox.TABLE_NAME + "." + EmailContent.RECORD_ID + ")"); 2350 } 2351 2352 @VisibleForTesting 2353 @SuppressWarnings("deprecation") 2354 static void convertPolicyFlagsToPolicyTable(SQLiteDatabase db) { 2355 Cursor c = db.query(Account.TABLE_NAME, 2356 new String[] {EmailContent.RECORD_ID /*0*/, AccountColumns.SECURITY_FLAGS /*1*/}, 2357 AccountColumns.SECURITY_FLAGS + ">0", null, null, null, null); 2358 ContentValues cv = new ContentValues(); 2359 String[] args = new String[1]; 2360 while (c.moveToNext()) { 2361 long securityFlags = c.getLong(1 /*SECURITY_FLAGS*/); 2362 Policy policy = LegacyPolicySet.flagsToPolicy(securityFlags); 2363 long policyId = db.insert(Policy.TABLE_NAME, null, policy.toContentValues()); 2364 cv.put(AccountColumns.POLICY_KEY, policyId); 2365 cv.putNull(AccountColumns.SECURITY_FLAGS); 2366 args[0] = Long.toString(c.getLong(0 /*RECORD_ID*/)); 2367 db.update(Account.TABLE_NAME, cv, EmailContent.RECORD_ID + "=?", args); 2368 } 2369 } 2370 2371 /** Upgrades the database from v17 to v18 */ 2372 @VisibleForTesting 2373 static void upgradeFromVersion17ToVersion18(SQLiteDatabase db) { 2374 // Copy the displayName column to the serverId column. In v18 of the database, 2375 // we use the serverId for IMAP/POP3 mailboxes instead of overloading the 2376 // display name. 2377 // 2378 // For posterity; this is the command we're executing: 2379 //sqlite> UPDATE mailbox SET serverid=displayname WHERE mailbox._id in ( 2380 // ...> SELECT mailbox._id FROM mailbox,account,hostauth WHERE 2381 // ...> mailbox.parentkey=0 AND mailbox.accountkey=account._id AND 2382 // ...> account.hostauthkeyrecv=hostauth._id AND 2383 // ...> (hostauth.protocol='imap' OR hostauth.protocol='pop3')); 2384 try { 2385 db.execSQL( 2386 "UPDATE " + Mailbox.TABLE_NAME + " SET " 2387 + MailboxColumns.SERVER_ID + "=" + MailboxColumns.DISPLAY_NAME 2388 + " WHERE " 2389 + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " IN ( SELECT " 2390 + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " FROM " 2391 + Mailbox.TABLE_NAME + "," + Account.TABLE_NAME + "," 2392 + HostAuth.TABLE_NAME + " WHERE " 2393 + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + "=0 AND " 2394 + Mailbox.TABLE_NAME + "." + MailboxColumns.ACCOUNT_KEY + "=" 2395 + Account.TABLE_NAME + "." + AccountColumns.ID + " AND " 2396 + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV + "=" 2397 + HostAuth.TABLE_NAME + "." + HostAuthColumns.ID + " AND ( " 2398 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap' OR " 2399 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='pop3' ) )"); 2400 } catch (SQLException e) { 2401 // Shouldn't be needed unless we're debugging and interrupt the process 2402 Log.w(TAG, "Exception upgrading EmailProvider.db from 17 to 18 " + e); 2403 } 2404 ContentCache.invalidateAllCaches(); 2405 } 2406 2407 /** Upgrades the database from v20 to v21 */ 2408 private static void upgradeFromVersion20ToVersion21(SQLiteDatabase db) { 2409 try { 2410 db.execSQL("alter table " + Mailbox.TABLE_NAME 2411 + " add column " + Mailbox.LAST_SEEN_MESSAGE_KEY + " integer;"); 2412 } catch (SQLException e) { 2413 // Shouldn't be needed unless we're debugging and interrupt the process 2414 Log.w(TAG, "Exception upgrading EmailProvider.db from 20 to 21 " + e); 2415 } 2416 } 2417 2418 /** 2419 * Upgrade the database from v21 to v22 2420 * This entails creating AccountManager accounts for all pop3 and imap accounts 2421 */ 2422 2423 private static final String[] RECV_PROJECTION = 2424 new String[] {AccountColumns.HOST_AUTH_KEY_RECV}; 2425 private static final int EMAIL_AND_RECV_COLUMN_RECV = 0; 2426 2427 static private void createAccountManagerAccount(Context context, HostAuth hostAuth) { 2428 AccountManager accountManager = AccountManager.get(context); 2429 android.accounts.Account amAccount = 2430 new android.accounts.Account(hostAuth.mLogin, AccountManagerTypes.TYPE_POP_IMAP); 2431 accountManager.addAccountExplicitly(amAccount, hostAuth.mPassword, null); 2432 ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 2433 ContentResolver.setSyncAutomatically(amAccount, EmailContent.AUTHORITY, true); 2434 ContentResolver.setIsSyncable(amAccount, ContactsContract.AUTHORITY, 0); 2435 ContentResolver.setIsSyncable(amAccount, CalendarProviderStub.AUTHORITY, 0); 2436 } 2437 2438 @VisibleForTesting 2439 static void upgradeFromVersion21ToVersion22(SQLiteDatabase db, Context accountManagerContext) { 2440 try { 2441 // Loop through accounts, looking for pop/imap accounts 2442 Cursor accountCursor = db.query(Account.TABLE_NAME, RECV_PROJECTION, null, 2443 null, null, null, null); 2444 try { 2445 String[] hostAuthArgs = new String[1]; 2446 while (accountCursor.moveToNext()) { 2447 hostAuthArgs[0] = accountCursor.getString(EMAIL_AND_RECV_COLUMN_RECV); 2448 // Get the "receive" HostAuth for this account 2449 Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 2450 HostAuth.CONTENT_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs, 2451 null, null, null); 2452 try { 2453 if (hostAuthCursor.moveToFirst()) { 2454 HostAuth hostAuth = new HostAuth(); 2455 hostAuth.restore(hostAuthCursor); 2456 String protocol = hostAuth.mProtocol; 2457 // If this is a pop3 or imap account, create the account manager account 2458 if ("imap".equals(protocol) || "pop3".equals(protocol)) { 2459 createAccountManagerAccount(accountManagerContext, hostAuth); 2460 } 2461 } 2462 } finally { 2463 hostAuthCursor.close(); 2464 } 2465 } 2466 } finally { 2467 accountCursor.close(); 2468 } 2469 } catch (SQLException e) { 2470 // Shouldn't be needed unless we're debugging and interrupt the process 2471 Log.w(TAG, "Exception upgrading EmailProvider.db from 20 to 21 " + e); 2472 } 2473 } 2474 2475 /** Upgrades the database from v22 to v23 */ 2476 private static void upgradeFromVersion22ToVersion23(SQLiteDatabase db) { 2477 try { 2478 db.execSQL("alter table " + Mailbox.TABLE_NAME 2479 + " add column " + Mailbox.LAST_TOUCHED_TIME + " integer default 0;"); 2480 } catch (SQLException e) { 2481 // Shouldn't be needed unless we're debugging and interrupt the process 2482 Log.w(TAG, "Exception upgrading EmailProvider.db from 22 to 23 " + e); 2483 } 2484 } 2485 2486 /** Adds in a column for information about a client certificate to use. */ 2487 private static void upgradeFromVersion23ToVersion24(SQLiteDatabase db) { 2488 try { 2489 db.execSQL("alter table " + HostAuth.TABLE_NAME 2490 + " add column " + HostAuth.CLIENT_CERT_ALIAS + " text;"); 2491 } catch (SQLException e) { 2492 // Shouldn't be needed unless we're debugging and interrupt the process 2493 Log.w(TAG, "Exception upgrading EmailProvider.db from 23 to 24 " + e); 2494 } 2495 } 2496 2497 /** Upgrades the database from v24 to v25 by creating table for quick responses */ 2498 private static void upgradeFromVersion24ToVersion25(SQLiteDatabase db) { 2499 try { 2500 createQuickResponseTable(db); 2501 } catch (SQLException e) { 2502 // Shouldn't be needed unless we're debugging and interrupt the process 2503 Log.w(TAG, "Exception upgrading EmailProvider.db from 24 to 25 " + e); 2504 } 2505 } 2506 2507 2508 private static final String[] ID_FLAGS_RECV_PROJECTION = 2509 new String[] {AccountColumns.ID, AccountColumns.FLAGS, AccountColumns.HOST_AUTH_KEY_RECV}; 2510 private static final int ID_FLAGS_RECV_COLUMN_ID = 0; 2511 private static final int ID_FLAGS_RECV_COLUMN_FLAGS = 1; 2512 private static final int ID_FLAGS_RECV_COLUMN_RECV = 2; 2513 2514 /** Upgrades the database from v25 to v26 by adding FLAG_SUPPORTS_SEARCH to IMAP accounts */ 2515 private static void upgradeFromVersion25ToVersion26(SQLiteDatabase db) { 2516 try { 2517 // Loop through accounts, looking for imap accounts 2518 Cursor accountCursor = db.query(Account.TABLE_NAME, ID_FLAGS_RECV_PROJECTION, null, 2519 null, null, null, null); 2520 ContentValues cv = new ContentValues(); 2521 try { 2522 String[] hostAuthArgs = new String[1]; 2523 while (accountCursor.moveToNext()) { 2524 hostAuthArgs[0] = accountCursor.getString(ID_FLAGS_RECV_COLUMN_RECV); 2525 // Get the "receive" HostAuth for this account 2526 Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 2527 HostAuth.CONTENT_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs, 2528 null, null, null); 2529 try { 2530 if (hostAuthCursor.moveToFirst()) { 2531 HostAuth hostAuth = new HostAuth(); 2532 hostAuth.restore(hostAuthCursor); 2533 // If this is an imap account, add the search flag 2534 if (HostAuth.SCHEME_IMAP.equals(hostAuth.mProtocol)) { 2535 String id = accountCursor.getString(ID_FLAGS_RECV_COLUMN_ID); 2536 int flags = accountCursor.getInt(ID_FLAGS_RECV_COLUMN_FLAGS); 2537 cv.put(AccountColumns.FLAGS, flags | Account.FLAGS_SUPPORTS_SEARCH); 2538 db.update(Account.TABLE_NAME, cv, Account.RECORD_ID + "=?", 2539 new String[] {id}); 2540 } 2541 } 2542 } finally { 2543 hostAuthCursor.close(); 2544 } 2545 } 2546 } finally { 2547 accountCursor.close(); 2548 } 2549 } catch (SQLException e) { 2550 // Shouldn't be needed unless we're debugging and interrupt the process 2551 Log.w(TAG, "Exception upgrading EmailProvider.db from 25 to 26 " + e); 2552 } 2553 } 2554 2555 /** 2556 * For testing purposes, check whether a given row is cached 2557 * @param baseUri the base uri of the EmailContent 2558 * @param id the row id of the EmailContent 2559 * @return whether or not the row is currently cached 2560 */ 2561 @VisibleForTesting 2562 protected boolean isCached(Uri baseUri, long id) { 2563 int match = findMatch(baseUri, "isCached"); 2564 int table = match >> BASE_SHIFT; 2565 ContentCache cache = mContentCaches[table]; 2566 if (cache == null) return false; 2567 Cursor cc = cache.get(Long.toString(id)); 2568 return (cc != null); 2569 } 2570} 2571