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