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