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