EmailProvider.java revision d306ba34387f3a7e77a4b8d98c6ac45cc14b95ad
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.provider.EmailContent.Account; 22import com.android.email.provider.EmailContent.AccountColumns; 23import com.android.email.provider.EmailContent.Attachment; 24import com.android.email.provider.EmailContent.AttachmentColumns; 25import com.android.email.provider.EmailContent.Body; 26import com.android.email.provider.EmailContent.BodyColumns; 27import com.android.email.provider.EmailContent.HostAuth; 28import com.android.email.provider.EmailContent.HostAuthColumns; 29import com.android.email.provider.EmailContent.Mailbox; 30import com.android.email.provider.EmailContent.MailboxColumns; 31import com.android.email.provider.EmailContent.Message; 32import com.android.email.provider.EmailContent.MessageColumns; 33import com.android.email.provider.EmailContent.SyncColumns; 34import com.android.email.service.AttachmentDownloadService; 35 36import android.accounts.AccountManager; 37import android.content.ContentProvider; 38import android.content.ContentProviderOperation; 39import android.content.ContentProviderResult; 40import android.content.ContentResolver; 41import android.content.ContentUris; 42import android.content.ContentValues; 43import android.content.Context; 44import android.content.OperationApplicationException; 45import android.content.UriMatcher; 46import android.database.Cursor; 47import android.database.MatrixCursor; 48import android.database.SQLException; 49import android.database.sqlite.SQLiteDatabase; 50import android.database.sqlite.SQLiteException; 51import android.database.sqlite.SQLiteOpenHelper; 52import android.net.Uri; 53import android.util.Log; 54 55import java.io.File; 56import java.util.ArrayList; 57 58public class EmailProvider extends ContentProvider { 59 60 private static final String TAG = "EmailProvider"; 61 62 protected static final String DATABASE_NAME = "EmailProvider.db"; 63 protected static final String BODY_DATABASE_NAME = "EmailProviderBody.db"; 64 65 public static final String ACTION_ATTACHMENT_UPDATED = "com.android.email.ATTACHMENT_UPDATED"; 66 public static final String ATTACHMENT_UPDATED_EXTRA_FLAGS = 67 "com.android.email.ATTACHMENT_UPDATED_FLAGS"; 68 69 public static final String EMAIL_MESSAGE_MIME_TYPE = 70 "vnd.android.cursor.item/email-message"; 71 public static final String EMAIL_ATTACHMENT_MIME_TYPE = 72 "vnd.android.cursor.item/email-attachment"; 73 74 public static final Uri INTEGRITY_CHECK_URI = 75 Uri.parse("content://" + EmailContent.AUTHORITY + "/integrityCheck"); 76 77 // Definitions for our queries looking for orphaned messages 78 private static final String[] ORPHANS_PROJECTION 79 = new String[] {MessageColumns.ID, MessageColumns.MAILBOX_KEY}; 80 private static final int ORPHANS_ID = 0; 81 private static final int ORPHANS_MAILBOX_KEY = 1; 82 83 private static final String WHERE_ID = EmailContent.RECORD_ID + "=?"; 84 85 // We'll cache the following four tables; sizes are best estimates of effective values 86 private static final ContentCache sCacheAccount = 87 new ContentCache("Account", Account.CONTENT_PROJECTION, 4); 88 private static final ContentCache sCacheHostAuth = 89 new ContentCache("HostAuth", HostAuth.CONTENT_PROJECTION, 8); 90 /*package*/ static final ContentCache sCacheMailbox = 91 new ContentCache("Mailbox", Mailbox.CONTENT_PROJECTION, 8); 92 private static final ContentCache sCacheMessage = 93 new ContentCache("Message", Message.CONTENT_PROJECTION, 3); 94 95 // Any changes to the database format *must* include update-in-place code. 96 // Original version: 3 97 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 98 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 99 // Version 6: Adding Message.mServerTimeStamp column 100 // Version 7: Replace the mailbox_delete trigger with a version that removes orphaned messages 101 // from the Message_Deletes and Message_Updates tables 102 // Version 8: Add security flags column to accounts table 103 // Version 9: Add security sync key and signature to accounts table 104 // Version 10: Add meeting info to message table 105 // Version 11: Add content and flags to attachment table 106 // Version 12: Add content_bytes to attachment table. content is deprecated. 107 // Version 13: Add messageCount to Mailbox table. 108 // Version 14: Add snippet to Message table 109 // Version 15: Fix upgrade problem in version 14. 110 // Version 16: Add accountKey to Attachment table 111 public static final int DATABASE_VERSION = 16; 112 113 // Any changes to the database format *must* include update-in-place code. 114 // Original version: 2 115 // Version 3: Add "sourceKey" column 116 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 117 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 118 // Version 6: Adding Body.mIntroText column 119 public static final int BODY_DATABASE_VERSION = 6; 120 121 public static final String EMAIL_AUTHORITY = "com.android.email.provider"; 122 // The notifier authority is used to send notifications regarding changes to messages (insert, 123 // delete, or update) and is intended as an optimization for use by clients of message list 124 // cursors (initially, the email AppWidget). 125 public static final String EMAIL_NOTIFIER_AUTHORITY = "com.android.email.notifier"; 126 127 private static final int ACCOUNT_BASE = 0; 128 private static final int ACCOUNT = ACCOUNT_BASE; 129 private static final int ACCOUNT_ID = ACCOUNT_BASE + 1; 130 private static final int ACCOUNT_ID_ADD_TO_FIELD = ACCOUNT_BASE + 2; 131 private static final int ACCOUNT_RESET_NEW_COUNT = ACCOUNT_BASE + 3; 132 private static final int ACCOUNT_RESET_NEW_COUNT_ID = ACCOUNT_BASE + 4; 133 134 private static final int MAILBOX_BASE = 0x1000; 135 private static final int MAILBOX = MAILBOX_BASE; 136 private static final int MAILBOX_ID = MAILBOX_BASE + 1; 137 private static final int MAILBOX_ID_ADD_TO_FIELD = MAILBOX_BASE + 2; 138 139 private static final int MESSAGE_BASE = 0x2000; 140 private static final int MESSAGE = MESSAGE_BASE; 141 private static final int MESSAGE_ID = MESSAGE_BASE + 1; 142 private static final int SYNCED_MESSAGE_ID = MESSAGE_BASE + 2; 143 144 private static final int ATTACHMENT_BASE = 0x3000; 145 private static final int ATTACHMENT = ATTACHMENT_BASE; 146 private static final int ATTACHMENT_ID = ATTACHMENT_BASE + 1; 147 private static final int ATTACHMENTS_MESSAGE_ID = ATTACHMENT_BASE + 2; 148 149 private static final int HOSTAUTH_BASE = 0x4000; 150 private static final int HOSTAUTH = HOSTAUTH_BASE; 151 private static final int HOSTAUTH_ID = HOSTAUTH_BASE + 1; 152 153 private static final int UPDATED_MESSAGE_BASE = 0x5000; 154 private static final int UPDATED_MESSAGE = UPDATED_MESSAGE_BASE; 155 private static final int UPDATED_MESSAGE_ID = UPDATED_MESSAGE_BASE + 1; 156 157 private static final int DELETED_MESSAGE_BASE = 0x6000; 158 private static final int DELETED_MESSAGE = DELETED_MESSAGE_BASE; 159 private static final int DELETED_MESSAGE_ID = DELETED_MESSAGE_BASE + 1; 160 161 // MUST ALWAYS EQUAL THE LAST OF THE PREVIOUS BASE CONSTANTS 162 private static final int LAST_EMAIL_PROVIDER_DB_BASE = DELETED_MESSAGE_BASE; 163 164 // DO NOT CHANGE BODY_BASE!! 165 private static final int BODY_BASE = LAST_EMAIL_PROVIDER_DB_BASE + 0x1000; 166 private static final int BODY = BODY_BASE; 167 private static final int BODY_ID = BODY_BASE + 1; 168 169 private static final int BASE_SHIFT = 12; // 12 bits to the base type: 0, 0x1000, 0x2000, etc. 170 171 // TABLE_NAMES MUST remain in the order of the BASE constants above (e.g. ACCOUNT_BASE = 0x0000, 172 // MESSAGE_BASE = 0x1000, etc.) 173 private static final String[] TABLE_NAMES = { 174 EmailContent.Account.TABLE_NAME, 175 EmailContent.Mailbox.TABLE_NAME, 176 EmailContent.Message.TABLE_NAME, 177 EmailContent.Attachment.TABLE_NAME, 178 EmailContent.HostAuth.TABLE_NAME, 179 EmailContent.Message.UPDATED_TABLE_NAME, 180 EmailContent.Message.DELETED_TABLE_NAME, 181 EmailContent.Body.TABLE_NAME 182 }; 183 184 // CONTENT_CACHES MUST remain in the order of the BASE constants above 185 private static final ContentCache[] CONTENT_CACHES = { 186 sCacheAccount, 187 sCacheMailbox, 188 sCacheMessage, 189 null, 190 sCacheHostAuth, 191 null, 192 null, 193 null}; 194 195 private static final UriMatcher sURIMatcher = new UriMatcher(UriMatcher.NO_MATCH); 196 197 /** 198 * Let's only generate these SQL strings once, as they are used frequently 199 * Note that this isn't relevant for table creation strings, since they are used only once 200 */ 201 private static final String UPDATED_MESSAGE_INSERT = "insert or ignore into " + 202 Message.UPDATED_TABLE_NAME + " select * from " + Message.TABLE_NAME + " where " + 203 EmailContent.RECORD_ID + '='; 204 205 private static final String UPDATED_MESSAGE_DELETE = "delete from " + 206 Message.UPDATED_TABLE_NAME + " where " + EmailContent.RECORD_ID + '='; 207 208 private static final String DELETED_MESSAGE_INSERT = "insert or replace into " + 209 Message.DELETED_TABLE_NAME + " select * from " + Message.TABLE_NAME + " where " + 210 EmailContent.RECORD_ID + '='; 211 212 private static final String DELETE_ORPHAN_BODIES = "delete from " + Body.TABLE_NAME + 213 " where " + BodyColumns.MESSAGE_KEY + " in " + "(select " + BodyColumns.MESSAGE_KEY + 214 " from " + Body.TABLE_NAME + " except select " + EmailContent.RECORD_ID + " from " + 215 Message.TABLE_NAME + ')'; 216 217 private static final String DELETE_BODY = "delete from " + Body.TABLE_NAME + 218 " where " + BodyColumns.MESSAGE_KEY + '='; 219 220 private static final String ID_EQUALS = EmailContent.RECORD_ID + "=?"; 221 222 private static final String TRIGGER_MAILBOX_DELETE = 223 "create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME + 224 " begin" + 225 " delete from " + Message.TABLE_NAME + 226 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 227 "; delete from " + Message.UPDATED_TABLE_NAME + 228 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 229 "; delete from " + Message.DELETED_TABLE_NAME + 230 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 231 "; end"; 232 233 private static final ContentValues CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT; 234 235 public static final String MESSAGE_URI_PARAMETER_MAILBOX_ID = "mailboxId"; 236 237 static { 238 // Email URI matching table 239 UriMatcher matcher = sURIMatcher; 240 241 // All accounts 242 matcher.addURI(EMAIL_AUTHORITY, "account", ACCOUNT); 243 // A specific account 244 // insert into this URI causes a mailbox to be added to the account 245 matcher.addURI(EMAIL_AUTHORITY, "account/#", ACCOUNT_ID); 246 247 // Special URI to reset the new message count. Only update works, and content values 248 // will be ignored. 249 matcher.addURI(EMAIL_AUTHORITY, "resetNewMessageCount", ACCOUNT_RESET_NEW_COUNT); 250 matcher.addURI(EMAIL_AUTHORITY, "resetNewMessageCount/#", ACCOUNT_RESET_NEW_COUNT_ID); 251 252 // All mailboxes 253 matcher.addURI(EMAIL_AUTHORITY, "mailbox", MAILBOX); 254 // A specific mailbox 255 // insert into this URI causes a message to be added to the mailbox 256 // ** NOTE For now, the accountKey must be set manually in the values! 257 matcher.addURI(EMAIL_AUTHORITY, "mailbox/#", MAILBOX_ID); 258 259 // All messages 260 matcher.addURI(EMAIL_AUTHORITY, "message", MESSAGE); 261 // A specific message 262 // insert into this URI causes an attachment to be added to the message 263 matcher.addURI(EMAIL_AUTHORITY, "message/#", MESSAGE_ID); 264 265 // A specific attachment 266 matcher.addURI(EMAIL_AUTHORITY, "attachment", ATTACHMENT); 267 // A specific attachment (the header information) 268 matcher.addURI(EMAIL_AUTHORITY, "attachment/#", ATTACHMENT_ID); 269 // The attachments of a specific message (query only) (insert & delete TBD) 270 matcher.addURI(EMAIL_AUTHORITY, "attachment/message/#", ATTACHMENTS_MESSAGE_ID); 271 272 // All mail bodies 273 matcher.addURI(EMAIL_AUTHORITY, "body", BODY); 274 // A specific mail body 275 matcher.addURI(EMAIL_AUTHORITY, "body/#", BODY_ID); 276 277 // All hostauth records 278 matcher.addURI(EMAIL_AUTHORITY, "hostauth", HOSTAUTH); 279 // A specific hostauth 280 matcher.addURI(EMAIL_AUTHORITY, "hostauth/#", HOSTAUTH_ID); 281 282 // Atomically a constant value to a particular field of a mailbox/account 283 matcher.addURI(EMAIL_AUTHORITY, "mailboxIdAddToField/#", MAILBOX_ID_ADD_TO_FIELD); 284 matcher.addURI(EMAIL_AUTHORITY, "accountIdAddToField/#", ACCOUNT_ID_ADD_TO_FIELD); 285 286 /** 287 * THIS URI HAS SPECIAL SEMANTICS 288 * ITS USE IS INTENDED FOR THE UI APPLICATION TO MARK CHANGES THAT NEED TO BE SYNCED BACK 289 * TO A SERVER VIA A SYNC ADAPTER 290 */ 291 matcher.addURI(EMAIL_AUTHORITY, "syncedMessage/#", SYNCED_MESSAGE_ID); 292 293 /** 294 * THE URIs BELOW THIS POINT ARE INTENDED TO BE USED BY SYNC ADAPTERS ONLY 295 * THEY REFER TO DATA CREATED AND MAINTAINED BY CALLS TO THE SYNCED_MESSAGE_ID URI 296 * BY THE UI APPLICATION 297 */ 298 // All deleted messages 299 matcher.addURI(EMAIL_AUTHORITY, "deletedMessage", DELETED_MESSAGE); 300 // A specific deleted message 301 matcher.addURI(EMAIL_AUTHORITY, "deletedMessage/#", DELETED_MESSAGE_ID); 302 303 // All updated messages 304 matcher.addURI(EMAIL_AUTHORITY, "updatedMessage", UPDATED_MESSAGE); 305 // A specific updated message 306 matcher.addURI(EMAIL_AUTHORITY, "updatedMessage/#", UPDATED_MESSAGE_ID); 307 308 CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT = new ContentValues(); 309 CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT.put(Account.NEW_MESSAGE_COUNT, 0); 310 } 311 312 313 /** 314 * Wrap the UriMatcher call so we can throw a runtime exception if an unknown Uri is passed in 315 * @param uri the Uri to match 316 * @return the match value 317 */ 318 private static int findMatch(Uri uri, String methodName) { 319 int match = sURIMatcher.match(uri); 320 if (match < 0) { 321 throw new IllegalArgumentException("Unknown uri: " + uri); 322 } else if (Email.LOGD) { 323 Log.v(TAG, methodName + ": uri=" + uri + ", match is " + match); 324 } 325 return match; 326 } 327 328 /* 329 * Internal helper method for index creation. 330 * Example: 331 * "create index message_" + MessageColumns.FLAG_READ 332 * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");" 333 */ 334 /* package */ 335 static String createIndex(String tableName, String columnName) { 336 return "create index " + tableName.toLowerCase() + '_' + columnName 337 + " on " + tableName + " (" + columnName + ");"; 338 } 339 340 static void createMessageTable(SQLiteDatabase db) { 341 String messageColumns = MessageColumns.DISPLAY_NAME + " text, " 342 + MessageColumns.TIMESTAMP + " integer, " 343 + MessageColumns.SUBJECT + " text, " 344 + MessageColumns.FLAG_READ + " integer, " 345 + MessageColumns.FLAG_LOADED + " integer, " 346 + MessageColumns.FLAG_FAVORITE + " integer, " 347 + MessageColumns.FLAG_ATTACHMENT + " integer, " 348 + MessageColumns.FLAGS + " integer, " 349 + MessageColumns.CLIENT_ID + " integer, " 350 + MessageColumns.MESSAGE_ID + " text, " 351 + MessageColumns.MAILBOX_KEY + " integer, " 352 + MessageColumns.ACCOUNT_KEY + " integer, " 353 + MessageColumns.FROM_LIST + " text, " 354 + MessageColumns.TO_LIST + " text, " 355 + MessageColumns.CC_LIST + " text, " 356 + MessageColumns.BCC_LIST + " text, " 357 + MessageColumns.REPLY_TO_LIST + " text, " 358 + MessageColumns.MEETING_INFO + " text, " 359 + MessageColumns.SNIPPET + " text" 360 + ");"; 361 362 // This String and the following String MUST have the same columns, except for the type 363 // of those columns! 364 String createString = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 365 + SyncColumns.SERVER_ID + " text, " 366 + SyncColumns.SERVER_TIMESTAMP + " integer, " 367 + messageColumns; 368 369 // For the updated and deleted tables, the id is assigned, but we do want to keep track 370 // of the ORDER of updates using an autoincrement primary key. We use the DATA column 371 // at this point; it has no other function 372 String altCreateString = " (" + EmailContent.RECORD_ID + " integer unique, " 373 + SyncColumns.SERVER_ID + " text, " 374 + SyncColumns.SERVER_TIMESTAMP + " integer, " 375 + messageColumns; 376 377 // The three tables have the same schema 378 db.execSQL("create table " + Message.TABLE_NAME + createString); 379 db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString); 380 db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString); 381 382 String indexColumns[] = { 383 MessageColumns.TIMESTAMP, 384 MessageColumns.FLAG_READ, 385 MessageColumns.FLAG_LOADED, 386 MessageColumns.MAILBOX_KEY, 387 SyncColumns.SERVER_ID 388 }; 389 390 for (String columnName : indexColumns) { 391 db.execSQL(createIndex(Message.TABLE_NAME, columnName)); 392 } 393 394 // Deleting a Message deletes all associated Attachments 395 // Deleting the associated Body cannot be done in a trigger, because the Body is stored 396 // in a separate database, and trigger cannot operate on attached databases. 397 db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME + 398 " begin delete from " + Attachment.TABLE_NAME + 399 " where " + AttachmentColumns.MESSAGE_KEY + "=old." + EmailContent.RECORD_ID + 400 "; end"); 401 402 // Add triggers to keep unread count accurate per mailbox 403 404 // NOTE: SQLite's before triggers are not safe when recursive triggers are involved. 405 // Use caution when changing them. 406 407 // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox 408 db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME + 409 " when NEW." + MessageColumns.FLAG_READ + "=0" + 410 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 411 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 412 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 413 "; end"); 414 415 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 416 db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME + 417 " when OLD." + MessageColumns.FLAG_READ + "=0" + 418 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 419 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 420 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 421 "; end"); 422 423 // Change a message's mailbox 424 db.execSQL("create trigger unread_message_move before update of " + 425 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 426 " when OLD." + MessageColumns.FLAG_READ + "=0" + 427 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 428 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 429 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 430 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 431 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 432 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 433 "; end"); 434 435 // Change a message's read state 436 db.execSQL("create trigger unread_message_read before update of " + 437 MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME + 438 " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ + 439 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 440 '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ + 441 " when 0 then -1 else 1 end" + 442 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 443 "; end"); 444 445 // Add triggers to update message count per mailbox 446 447 // Insert a message. 448 db.execSQL("create trigger message_count_message_insert after insert on " + 449 Message.TABLE_NAME + 450 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 451 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 452 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 453 "; end"); 454 455 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 456 db.execSQL("create trigger message_count_message_delete after delete on " + 457 Message.TABLE_NAME + 458 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 459 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 460 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 461 "; end"); 462 463 // Change a message's mailbox 464 db.execSQL("create trigger message_count_message_move after update of " + 465 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 466 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 467 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 468 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 469 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 470 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 471 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 472 "; end"); 473 } 474 475 static void resetMessageTable(SQLiteDatabase db, int oldVersion, int newVersion) { 476 try { 477 db.execSQL("drop table " + Message.TABLE_NAME); 478 db.execSQL("drop table " + Message.UPDATED_TABLE_NAME); 479 db.execSQL("drop table " + Message.DELETED_TABLE_NAME); 480 } catch (SQLException e) { 481 } 482 createMessageTable(db); 483 } 484 485 static void createAccountTable(SQLiteDatabase db) { 486 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 487 + AccountColumns.DISPLAY_NAME + " text, " 488 + AccountColumns.EMAIL_ADDRESS + " text, " 489 + AccountColumns.SYNC_KEY + " text, " 490 + AccountColumns.SYNC_LOOKBACK + " integer, " 491 + AccountColumns.SYNC_INTERVAL + " text, " 492 + AccountColumns.HOST_AUTH_KEY_RECV + " integer, " 493 + AccountColumns.HOST_AUTH_KEY_SEND + " integer, " 494 + AccountColumns.FLAGS + " integer, " 495 + AccountColumns.IS_DEFAULT + " integer, " 496 + AccountColumns.COMPATIBILITY_UUID + " text, " 497 + AccountColumns.SENDER_NAME + " text, " 498 + AccountColumns.RINGTONE_URI + " text, " 499 + AccountColumns.PROTOCOL_VERSION + " text, " 500 + AccountColumns.NEW_MESSAGE_COUNT + " integer, " 501 + AccountColumns.SECURITY_FLAGS + " integer, " 502 + AccountColumns.SECURITY_SYNC_KEY + " text, " 503 + AccountColumns.SIGNATURE + " text " 504 + ");"; 505 db.execSQL("create table " + Account.TABLE_NAME + s); 506 // Deleting an account deletes associated Mailboxes and HostAuth's 507 db.execSQL("create trigger account_delete before delete on " + Account.TABLE_NAME + 508 " begin delete from " + Mailbox.TABLE_NAME + 509 " where " + MailboxColumns.ACCOUNT_KEY + "=old." + EmailContent.RECORD_ID + 510 "; delete from " + HostAuth.TABLE_NAME + 511 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV + 512 "; delete from " + HostAuth.TABLE_NAME + 513 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND + 514 "; end"); 515 } 516 517 static void resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) { 518 try { 519 db.execSQL("drop table " + Account.TABLE_NAME); 520 } catch (SQLException e) { 521 } 522 createAccountTable(db); 523 } 524 525 static void createHostAuthTable(SQLiteDatabase db) { 526 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 527 + HostAuthColumns.PROTOCOL + " text, " 528 + HostAuthColumns.ADDRESS + " text, " 529 + HostAuthColumns.PORT + " integer, " 530 + HostAuthColumns.FLAGS + " integer, " 531 + HostAuthColumns.LOGIN + " text, " 532 + HostAuthColumns.PASSWORD + " text, " 533 + HostAuthColumns.DOMAIN + " text, " 534 + HostAuthColumns.ACCOUNT_KEY + " integer" 535 + ");"; 536 db.execSQL("create table " + HostAuth.TABLE_NAME + s); 537 } 538 539 static void resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) { 540 try { 541 db.execSQL("drop table " + HostAuth.TABLE_NAME); 542 } catch (SQLException e) { 543 } 544 createHostAuthTable(db); 545 } 546 547 static void createMailboxTable(SQLiteDatabase db) { 548 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 549 + MailboxColumns.DISPLAY_NAME + " text, " 550 + MailboxColumns.SERVER_ID + " text, " 551 + MailboxColumns.PARENT_SERVER_ID + " text, " 552 + MailboxColumns.ACCOUNT_KEY + " integer, " 553 + MailboxColumns.TYPE + " integer, " 554 + MailboxColumns.DELIMITER + " integer, " 555 + MailboxColumns.SYNC_KEY + " text, " 556 + MailboxColumns.SYNC_LOOKBACK + " integer, " 557 + MailboxColumns.SYNC_INTERVAL + " integer, " 558 + MailboxColumns.SYNC_TIME + " integer, " 559 + MailboxColumns.UNREAD_COUNT + " integer, " 560 + MailboxColumns.FLAG_VISIBLE + " integer, " 561 + MailboxColumns.FLAGS + " integer, " 562 + MailboxColumns.VISIBLE_LIMIT + " integer, " 563 + MailboxColumns.SYNC_STATUS + " text, " 564 + MailboxColumns.MESSAGE_COUNT + " integer not null default 0" 565 + ");"; 566 db.execSQL("create table " + Mailbox.TABLE_NAME + s); 567 db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID 568 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")"); 569 db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY 570 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")"); 571 // Deleting a Mailbox deletes associated Messages in all three tables 572 db.execSQL(TRIGGER_MAILBOX_DELETE); 573 } 574 575 static void resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) { 576 try { 577 db.execSQL("drop table " + Mailbox.TABLE_NAME); 578 } catch (SQLException e) { 579 } 580 createMailboxTable(db); 581 } 582 583 static void createAttachmentTable(SQLiteDatabase db) { 584 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 585 + AttachmentColumns.FILENAME + " text, " 586 + AttachmentColumns.MIME_TYPE + " text, " 587 + AttachmentColumns.SIZE + " integer, " 588 + AttachmentColumns.CONTENT_ID + " text, " 589 + AttachmentColumns.CONTENT_URI + " text, " 590 + AttachmentColumns.MESSAGE_KEY + " integer, " 591 + AttachmentColumns.LOCATION + " text, " 592 + AttachmentColumns.ENCODING + " text, " 593 + AttachmentColumns.CONTENT + " text, " 594 + AttachmentColumns.FLAGS + " integer, " 595 + AttachmentColumns.CONTENT_BYTES + " blob, " 596 + AttachmentColumns.ACCOUNT_KEY + " integer" 597 + ");"; 598 db.execSQL("create table " + Attachment.TABLE_NAME + s); 599 db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY)); 600 } 601 602 static void resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) { 603 try { 604 db.execSQL("drop table " + Attachment.TABLE_NAME); 605 } catch (SQLException e) { 606 } 607 createAttachmentTable(db); 608 } 609 610 static void createBodyTable(SQLiteDatabase db) { 611 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 612 + BodyColumns.MESSAGE_KEY + " integer, " 613 + BodyColumns.HTML_CONTENT + " text, " 614 + BodyColumns.TEXT_CONTENT + " text, " 615 + BodyColumns.HTML_REPLY + " text, " 616 + BodyColumns.TEXT_REPLY + " text, " 617 + BodyColumns.SOURCE_MESSAGE_KEY + " text, " 618 + BodyColumns.INTRO_TEXT + " text" 619 + ");"; 620 db.execSQL("create table " + Body.TABLE_NAME + s); 621 db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY)); 622 } 623 624 static void upgradeBodyTable(SQLiteDatabase db, int oldVersion, int newVersion) { 625 if (oldVersion < 5) { 626 try { 627 db.execSQL("drop table " + Body.TABLE_NAME); 628 createBodyTable(db); 629 } catch (SQLException e) { 630 } 631 } else if (oldVersion == 5) { 632 try { 633 db.execSQL("alter table " + Body.TABLE_NAME 634 + " add " + BodyColumns.INTRO_TEXT + " text"); 635 } catch (SQLException e) { 636 // Shouldn't be needed unless we're debugging and interrupt the process 637 Log.w(TAG, "Exception upgrading EmailProviderBody.db from v5 to v6", e); 638 } 639 oldVersion = 6; 640 } 641 } 642 643 private SQLiteDatabase mDatabase; 644 private SQLiteDatabase mBodyDatabase; 645 646 public synchronized SQLiteDatabase getDatabase(Context context) { 647 // Always return the cached database, if we've got one 648 if (mDatabase != null) { 649 return mDatabase; 650 } 651 652 // Whenever we create or re-cache the databases, make sure that we haven't lost one 653 // to corruption 654 checkDatabases(); 655 656 DatabaseHelper helper = new DatabaseHelper(context, DATABASE_NAME); 657 mDatabase = helper.getWritableDatabase(); 658 if (mDatabase != null) { 659 mDatabase.setLockingEnabled(true); 660 BodyDatabaseHelper bodyHelper = new BodyDatabaseHelper(context, BODY_DATABASE_NAME); 661 mBodyDatabase = bodyHelper.getWritableDatabase(); 662 if (mBodyDatabase != null) { 663 mBodyDatabase.setLockingEnabled(true); 664 String bodyFileName = mBodyDatabase.getPath(); 665 mDatabase.execSQL("attach \"" + bodyFileName + "\" as BodyDatabase"); 666 } 667 } 668 669 // Check for any orphaned Messages in the updated/deleted tables 670 deleteOrphans(mDatabase, Message.UPDATED_TABLE_NAME); 671 deleteOrphans(mDatabase, Message.DELETED_TABLE_NAME); 672 673 return mDatabase; 674 } 675 676 /*package*/ static SQLiteDatabase getReadableDatabase(Context context) { 677 DatabaseHelper helper = new EmailProvider().new DatabaseHelper(context, DATABASE_NAME); 678 return helper.getReadableDatabase(); 679 } 680 681 /** {@inheritDoc} */ 682 @Override 683 public void shutdown() { 684 if (mDatabase != null) { 685 mDatabase.close(); 686 mDatabase = null; 687 } 688 if (mBodyDatabase != null) { 689 mBodyDatabase.close(); 690 mBodyDatabase = null; 691 } 692 } 693 694 /*package*/ static void deleteOrphans(SQLiteDatabase database, String tableName) { 695 if (database != null) { 696 // We'll look at all of the items in the table; there won't be many typically 697 Cursor c = database.query(tableName, ORPHANS_PROJECTION, null, null, null, null, null); 698 // Usually, there will be nothing in these tables, so make a quick check 699 try { 700 if (c.getCount() == 0) return; 701 ArrayList<Long> foundMailboxes = new ArrayList<Long>(); 702 ArrayList<Long> notFoundMailboxes = new ArrayList<Long>(); 703 ArrayList<Long> deleteList = new ArrayList<Long>(); 704 String[] bindArray = new String[1]; 705 while (c.moveToNext()) { 706 // Get the mailbox key and see if we've already found this mailbox 707 // If so, we're fine 708 long mailboxId = c.getLong(ORPHANS_MAILBOX_KEY); 709 // If we already know this mailbox doesn't exist, mark the message for deletion 710 if (notFoundMailboxes.contains(mailboxId)) { 711 deleteList.add(c.getLong(ORPHANS_ID)); 712 // If we don't know about this mailbox, we'll try to find it 713 } else if (!foundMailboxes.contains(mailboxId)) { 714 bindArray[0] = Long.toString(mailboxId); 715 Cursor boxCursor = database.query(Mailbox.TABLE_NAME, 716 Mailbox.ID_PROJECTION, WHERE_ID, bindArray, null, null, null); 717 try { 718 // If it exists, we'll add it to the "found" mailboxes 719 if (boxCursor.moveToFirst()) { 720 foundMailboxes.add(mailboxId); 721 // Otherwise, we'll add to "not found" and mark the message for deletion 722 } else { 723 notFoundMailboxes.add(mailboxId); 724 deleteList.add(c.getLong(ORPHANS_ID)); 725 } 726 } finally { 727 boxCursor.close(); 728 } 729 } 730 } 731 // Now, delete the orphan messages 732 for (long messageId: deleteList) { 733 bindArray[0] = Long.toString(messageId); 734 database.delete(tableName, WHERE_ID, bindArray); 735 } 736 } finally { 737 c.close(); 738 } 739 } 740 } 741 742 private class BodyDatabaseHelper extends SQLiteOpenHelper { 743 BodyDatabaseHelper(Context context, String name) { 744 super(context, name, null, BODY_DATABASE_VERSION); 745 } 746 747 @Override 748 public void onCreate(SQLiteDatabase db) { 749 Log.d(TAG, "Creating EmailProviderBody database"); 750 createBodyTable(db); 751 } 752 753 @Override 754 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 755 upgradeBodyTable(db, oldVersion, newVersion); 756 } 757 758 @Override 759 public void onOpen(SQLiteDatabase db) { 760 } 761 } 762 763 private class DatabaseHelper extends SQLiteOpenHelper { 764 Context mContext; 765 766 DatabaseHelper(Context context, String name) { 767 super(context, name, null, DATABASE_VERSION); 768 mContext = context; 769 } 770 771 @Override 772 public void onCreate(SQLiteDatabase db) { 773 Log.d(TAG, "Creating EmailProvider database"); 774 // Create all tables here; each class has its own method 775 createMessageTable(db); 776 createAttachmentTable(db); 777 createMailboxTable(db); 778 createHostAuthTable(db); 779 createAccountTable(db); 780 } 781 782 @Override 783 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 784 // For versions prior to 5, delete all data 785 // Versions >= 5 require that data be preserved! 786 if (oldVersion < 5) { 787 android.accounts.Account[] accounts = AccountManager.get(mContext) 788 .getAccountsByType(Email.EXCHANGE_ACCOUNT_MANAGER_TYPE); 789 for (android.accounts.Account account: accounts) { 790 AccountManager.get(mContext).removeAccount(account, null, null); 791 } 792 resetMessageTable(db, oldVersion, newVersion); 793 resetAttachmentTable(db, oldVersion, newVersion); 794 resetMailboxTable(db, oldVersion, newVersion); 795 resetHostAuthTable(db, oldVersion, newVersion); 796 resetAccountTable(db, oldVersion, newVersion); 797 return; 798 } 799 if (oldVersion == 5) { 800 // Message Tables: Add SyncColumns.SERVER_TIMESTAMP 801 try { 802 db.execSQL("alter table " + Message.TABLE_NAME 803 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 804 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 805 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 806 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 807 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 808 } catch (SQLException e) { 809 // Shouldn't be needed unless we're debugging and interrupt the process 810 Log.w(TAG, "Exception upgrading EmailProvider.db from v5 to v6", e); 811 } 812 oldVersion = 6; 813 } 814 if (oldVersion == 6) { 815 // Use the newer mailbox_delete trigger 816 db.execSQL("drop trigger mailbox_delete;"); 817 db.execSQL(TRIGGER_MAILBOX_DELETE); 818 oldVersion = 7; 819 } 820 if (oldVersion == 7) { 821 // add the security (provisioning) column 822 try { 823 db.execSQL("alter table " + Account.TABLE_NAME 824 + " add column " + AccountColumns.SECURITY_FLAGS + " integer" + ";"); 825 } catch (SQLException e) { 826 // Shouldn't be needed unless we're debugging and interrupt the process 827 Log.w(TAG, "Exception upgrading EmailProvider.db from 7 to 8 " + e); 828 } 829 oldVersion = 8; 830 } 831 if (oldVersion == 8) { 832 // accounts: add security sync key & user signature columns 833 try { 834 db.execSQL("alter table " + Account.TABLE_NAME 835 + " add column " + AccountColumns.SECURITY_SYNC_KEY + " text" + ";"); 836 db.execSQL("alter table " + Account.TABLE_NAME 837 + " add column " + AccountColumns.SIGNATURE + " text" + ";"); 838 } catch (SQLException e) { 839 // Shouldn't be needed unless we're debugging and interrupt the process 840 Log.w(TAG, "Exception upgrading EmailProvider.db from 8 to 9 " + e); 841 } 842 oldVersion = 9; 843 } 844 if (oldVersion == 9) { 845 // Message: add meeting info column into Message tables 846 try { 847 db.execSQL("alter table " + Message.TABLE_NAME 848 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 849 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 850 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 851 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 852 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 853 } catch (SQLException e) { 854 // Shouldn't be needed unless we're debugging and interrupt the process 855 Log.w(TAG, "Exception upgrading EmailProvider.db from 9 to 10 " + e); 856 } 857 oldVersion = 10; 858 } 859 if (oldVersion == 10) { 860 // Attachment: add content and flags columns 861 try { 862 db.execSQL("alter table " + Attachment.TABLE_NAME 863 + " add column " + AttachmentColumns.CONTENT + " text" + ";"); 864 db.execSQL("alter table " + Attachment.TABLE_NAME 865 + " add column " + AttachmentColumns.FLAGS + " integer" + ";"); 866 } catch (SQLException e) { 867 // Shouldn't be needed unless we're debugging and interrupt the process 868 Log.w(TAG, "Exception upgrading EmailProvider.db from 10 to 11 " + e); 869 } 870 oldVersion = 11; 871 } 872 if (oldVersion == 11) { 873 // Attachment: add content_bytes 874 try { 875 db.execSQL("alter table " + Attachment.TABLE_NAME 876 + " add column " + AttachmentColumns.CONTENT_BYTES + " blob" + ";"); 877 } catch (SQLException e) { 878 // Shouldn't be needed unless we're debugging and interrupt the process 879 Log.w(TAG, "Exception upgrading EmailProvider.db from 11 to 12 " + e); 880 } 881 oldVersion = 12; 882 } 883 if (oldVersion == 12) { 884 try { 885 db.execSQL("alter table " + Mailbox.TABLE_NAME 886 + " add column " + Mailbox.MESSAGE_COUNT 887 +" integer not null default 0" + ";"); 888 recalculateMessageCount(db); 889 } catch (SQLException e) { 890 // Shouldn't be needed unless we're debugging and interrupt the process 891 Log.w(TAG, "Exception upgrading EmailProvider.db from 12 to 13 " + e); 892 } 893 oldVersion = 13; 894 } 895 if (oldVersion == 13) { 896 try { 897 db.execSQL("alter table " + Message.TABLE_NAME 898 + " add column " + Message.SNIPPET 899 +" text" + ";"); 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 13 to 14 " + e); 903 } 904 oldVersion = 14; 905 } 906 if (oldVersion == 14) { 907 try { 908 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 909 + " add column " + Message.SNIPPET +" text" + ";"); 910 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 911 + " add column " + Message.SNIPPET +" text" + ";"); 912 } catch (SQLException e) { 913 // Shouldn't be needed unless we're debugging and interrupt the process 914 Log.w(TAG, "Exception upgrading EmailProvider.db from 14 to 15 " + e); 915 } 916 oldVersion = 15; 917 } 918 if (oldVersion == 15) { 919 try { 920 db.execSQL("alter table " + Attachment.TABLE_NAME 921 + " add column " + Attachment.ACCOUNT_KEY +" integer" + ";"); 922 // Update all existing attachments to add the accountKey data 923 db.execSQL("update " + Attachment.TABLE_NAME + " set " + 924 Attachment.ACCOUNT_KEY + "= (SELECT " + Message.TABLE_NAME + "." + 925 Message.ACCOUNT_KEY + " from " + Message.TABLE_NAME + " where " + 926 Message.TABLE_NAME + "." + Message.RECORD_ID + " = " + 927 Attachment.TABLE_NAME + "." + Attachment.MESSAGE_KEY + ")"); 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 15 to 16 " + e); 931 } 932 oldVersion = 16; 933 } 934 } 935 936 @Override 937 public void onOpen(SQLiteDatabase db) { 938 } 939 } 940 941 @Override 942 public int delete(Uri uri, String selection, String[] selectionArgs) { 943 final int match = findMatch(uri, "delete"); 944 Context context = getContext(); 945 // Pick the correct database for this operation 946 // If we're in a transaction already (which would happen during applyBatch), then the 947 // body database is already attached to the email database and any attempt to use the 948 // body database directly will result in a SQLiteException (the database is locked) 949 SQLiteDatabase db = getDatabase(context); 950 int table = match >> BASE_SHIFT; 951 String id = "0"; 952 boolean messageDeletion = false; 953 ContentResolver resolver = context.getContentResolver(); 954 955 ContentCache cache = CONTENT_CACHES[table]; 956 String tableName = TABLE_NAMES[table]; 957 int result = -1; 958 959 try { 960 switch (match) { 961 // These are cases in which one or more Messages might get deleted, either by 962 // cascade or explicitly 963 case MAILBOX_ID: 964 case MAILBOX: 965 case ACCOUNT_ID: 966 case ACCOUNT: 967 case MESSAGE: 968 case SYNCED_MESSAGE_ID: 969 case MESSAGE_ID: 970 // Handle lost Body records here, since this cannot be done in a trigger 971 // The process is: 972 // 1) Begin a transaction, ensuring that both databases are affected atomically 973 // 2) Do the requested deletion, with cascading deletions handled in triggers 974 // 3) End the transaction, committing all changes atomically 975 // 976 // Bodies are auto-deleted here; Attachments are auto-deleted via trigger 977 messageDeletion = true; 978 db.beginTransaction(); 979 resolver.notifyChange(Message.NOTIFIER_URI, null); 980 break; 981 } 982 switch (match) { 983 case BODY_ID: 984 case DELETED_MESSAGE_ID: 985 case SYNCED_MESSAGE_ID: 986 case MESSAGE_ID: 987 case UPDATED_MESSAGE_ID: 988 case ATTACHMENT_ID: 989 case MAILBOX_ID: 990 case ACCOUNT_ID: 991 case HOSTAUTH_ID: 992 id = uri.getPathSegments().get(1); 993 if (match == SYNCED_MESSAGE_ID) { 994 // For synced messages, first copy the old message to the deleted table and 995 // delete it from the updated table (in case it was updated first) 996 // Note that this is all within a transaction, for atomicity 997 db.execSQL(DELETED_MESSAGE_INSERT + id); 998 db.execSQL(UPDATED_MESSAGE_DELETE + id); 999 } 1000 if (cache != null) { 1001 cache.lock(id); 1002 } 1003 try { 1004 result = db.delete(tableName, whereWithId(id, selection), selectionArgs); 1005 if (cache != null) { 1006 switch(match) { 1007 case ACCOUNT_ID: 1008 // Account deletion will clear all of the caches, as HostAuth's, 1009 // Mailboxes, and Messages will be deleted in the process 1010 sCacheMailbox.invalidate("Delete", uri, selection); 1011 sCacheHostAuth.invalidate("Delete", uri, selection); 1012 //$FALL-THROUGH$ 1013 case MAILBOX_ID: 1014 // Mailbox deletion will clear the Message cache 1015 sCacheMessage.invalidate("Delete", uri, selection); 1016 //$FALL-THROUGH$ 1017 case SYNCED_MESSAGE_ID: 1018 case MESSAGE_ID: 1019 case HOSTAUTH_ID: 1020 cache.invalidate("Delete", uri, selection); 1021 break; 1022 } 1023 } 1024 } finally { 1025 if (cache != null) { 1026 cache.unlock(id); 1027 } 1028 } 1029 break; 1030 case ATTACHMENTS_MESSAGE_ID: 1031 // All attachments for the given message 1032 id = uri.getPathSegments().get(2); 1033 result = db.delete(tableName, 1034 whereWith(Attachment.MESSAGE_KEY + "=" + id, selection), selectionArgs); 1035 break; 1036 1037 case BODY: 1038 case MESSAGE: 1039 case DELETED_MESSAGE: 1040 case UPDATED_MESSAGE: 1041 case ATTACHMENT: 1042 case MAILBOX: 1043 case ACCOUNT: 1044 case HOSTAUTH: 1045 switch(match) { 1046 // See the comments above for deletion of ACCOUNT_ID, etc 1047 case ACCOUNT: 1048 sCacheMailbox.invalidate("Delete", uri, selection); 1049 sCacheHostAuth.invalidate("Delete", uri, selection); 1050 //$FALL-THROUGH$ 1051 case MAILBOX: 1052 sCacheMessage.invalidate("Delete", uri, selection); 1053 //$FALL-THROUGH$ 1054 case MESSAGE: 1055 case HOSTAUTH: 1056 cache.invalidate("Delete", uri, selection); 1057 break; 1058 } 1059 result = db.delete(tableName, selection, selectionArgs); 1060 break; 1061 1062 default: 1063 throw new IllegalArgumentException("Unknown URI " + uri); 1064 } 1065 if (messageDeletion) { 1066 if (match == MESSAGE_ID) { 1067 // Delete the Body record associated with the deleted message 1068 db.execSQL(DELETE_BODY + id); 1069 } else { 1070 // Delete any orphaned Body records 1071 db.execSQL(DELETE_ORPHAN_BODIES); 1072 } 1073 db.setTransactionSuccessful(); 1074 } 1075 } catch (SQLiteException e) { 1076 checkDatabases(); 1077 throw e; 1078 } finally { 1079 if (messageDeletion) { 1080 db.endTransaction(); 1081 } 1082 } 1083 1084 // Notify all existing cursors. 1085 resolver.notifyChange(EmailContent.CONTENT_URI, null); 1086 return result; 1087 } 1088 1089 @Override 1090 // Use the email- prefix because message, mailbox, and account are so generic (e.g. SMS, IM) 1091 public String getType(Uri uri) { 1092 int match = findMatch(uri, "getType"); 1093 switch (match) { 1094 case BODY_ID: 1095 return "vnd.android.cursor.item/email-body"; 1096 case BODY: 1097 return "vnd.android.cursor.dir/email-body"; 1098 case UPDATED_MESSAGE_ID: 1099 case MESSAGE_ID: 1100 // NOTE: According to the framework folks, we're supposed to invent mime types as 1101 // a way of passing information to drag & drop recipients. 1102 // If there's a mailboxId parameter in the url, we respond with a mime type that 1103 // has -n appended, where n is the mailboxId of the message. The drag & drop code 1104 // uses this information to know not to allow dragging the item to its own mailbox 1105 String mimeType = EMAIL_MESSAGE_MIME_TYPE; 1106 String mailboxId = uri.getQueryParameter(MESSAGE_URI_PARAMETER_MAILBOX_ID); 1107 if (mailboxId != null) { 1108 mimeType += "-" + mailboxId; 1109 } 1110 return mimeType; 1111 case UPDATED_MESSAGE: 1112 case MESSAGE: 1113 return "vnd.android.cursor.dir/email-message"; 1114 case MAILBOX: 1115 return "vnd.android.cursor.dir/email-mailbox"; 1116 case MAILBOX_ID: 1117 return "vnd.android.cursor.item/email-mailbox"; 1118 case ACCOUNT: 1119 return "vnd.android.cursor.dir/email-account"; 1120 case ACCOUNT_ID: 1121 return "vnd.android.cursor.item/email-account"; 1122 case ATTACHMENTS_MESSAGE_ID: 1123 case ATTACHMENT: 1124 return "vnd.android.cursor.dir/email-attachment"; 1125 case ATTACHMENT_ID: 1126 return EMAIL_ATTACHMENT_MIME_TYPE; 1127 case HOSTAUTH: 1128 return "vnd.android.cursor.dir/email-hostauth"; 1129 case HOSTAUTH_ID: 1130 return "vnd.android.cursor.item/email-hostauth"; 1131 default: 1132 throw new IllegalArgumentException("Unknown URI " + uri); 1133 } 1134 } 1135 1136 @Override 1137 public Uri insert(Uri uri, ContentValues values) { 1138 int match = findMatch(uri, "insert"); 1139 Context context = getContext(); 1140 ContentResolver resolver = context.getContentResolver(); 1141 1142 // See the comment at delete(), above 1143 SQLiteDatabase db = getDatabase(context); 1144 int table = match >> BASE_SHIFT; 1145 long id; 1146 1147 // We do NOT allow setting of unreadCount/messageCount via the provider 1148 // These columns are maintained via triggers 1149 if (match == MAILBOX_ID || match == MAILBOX) { 1150 values.put(MailboxColumns.UNREAD_COUNT, 0); 1151 values.put(MailboxColumns.MESSAGE_COUNT, 0); 1152 } 1153 1154 Uri resultUri = null; 1155 1156 try { 1157 switch (match) { 1158 case MESSAGE: 1159 resolver.notifyChange(Message.NOTIFIER_URI, null); 1160 //$FALL-THROUGH$ 1161 case UPDATED_MESSAGE: 1162 case DELETED_MESSAGE: 1163 case BODY: 1164 case ATTACHMENT: 1165 case MAILBOX: 1166 case ACCOUNT: 1167 case HOSTAUTH: 1168 id = db.insert(TABLE_NAMES[table], "foo", values); 1169 resultUri = ContentUris.withAppendedId(uri, id); 1170 // Clients shouldn't normally be adding rows to these tables, as they are 1171 // maintained by triggers. However, we need to be able to do this for unit 1172 // testing, so we allow the insert and then throw the same exception that we 1173 // would if this weren't allowed. 1174 if (match == UPDATED_MESSAGE || match == DELETED_MESSAGE) { 1175 throw new IllegalArgumentException("Unknown URL " + uri); 1176 } 1177 if (match == ATTACHMENT) { 1178 int flags = 0; 1179 if (values.containsKey(Attachment.FLAGS)) { 1180 flags = values.getAsInteger(Attachment.FLAGS); 1181 } 1182 // Report all new attachments to the download service 1183 AttachmentDownloadService.attachmentChanged(id, flags); 1184 } 1185 break; 1186 case MAILBOX_ID: 1187 // This implies adding a message to a mailbox 1188 // Hmm, a problem here is that we can't link the account as well, so it must be 1189 // already in the values... 1190 id = Long.parseLong(uri.getPathSegments().get(1)); 1191 values.put(MessageColumns.MAILBOX_KEY, id); 1192 return insert(Message.CONTENT_URI, values); // Recurse 1193 case MESSAGE_ID: 1194 // This implies adding an attachment to a message. 1195 id = Long.parseLong(uri.getPathSegments().get(1)); 1196 values.put(AttachmentColumns.MESSAGE_KEY, id); 1197 return insert(Attachment.CONTENT_URI, values); // Recurse 1198 case ACCOUNT_ID: 1199 // This implies adding a mailbox to an account. 1200 id = Long.parseLong(uri.getPathSegments().get(1)); 1201 values.put(MailboxColumns.ACCOUNT_KEY, id); 1202 return insert(Mailbox.CONTENT_URI, values); // Recurse 1203 case ATTACHMENTS_MESSAGE_ID: 1204 id = db.insert(TABLE_NAMES[table], "foo", values); 1205 resultUri = ContentUris.withAppendedId(Attachment.CONTENT_URI, id); 1206 break; 1207 default: 1208 throw new IllegalArgumentException("Unknown URL " + uri); 1209 } 1210 } catch (SQLiteException e) { 1211 checkDatabases(); 1212 throw e; 1213 } 1214 1215 // Notify all existing cursors. 1216 resolver.notifyChange(EmailContent.CONTENT_URI, null); 1217 return resultUri; 1218 } 1219 1220 @Override 1221 public boolean onCreate() { 1222 checkDatabases(); 1223 return false; 1224 } 1225 1226 /** 1227 * The idea here is that the two databases (EmailProvider.db and EmailProviderBody.db must 1228 * always be in sync (i.e. there are two database or NO databases). This code will delete 1229 * any "orphan" database, so that both will be created together. Note that an "orphan" database 1230 * will exist after either of the individual databases is deleted due to data corruption. 1231 */ 1232 public void checkDatabases() { 1233 // Uncache the databases 1234 if (mDatabase != null) { 1235 mDatabase = null; 1236 } 1237 if (mBodyDatabase != null) { 1238 mBodyDatabase = null; 1239 } 1240 // Look for orphans, and delete as necessary; these must always be in sync 1241 File databaseFile = getContext().getDatabasePath(DATABASE_NAME); 1242 File bodyFile = getContext().getDatabasePath(BODY_DATABASE_NAME); 1243 1244 // TODO Make sure attachments are deleted 1245 if (databaseFile.exists() && !bodyFile.exists()) { 1246 Log.w(TAG, "Deleting orphaned EmailProvider database..."); 1247 databaseFile.delete(); 1248 } else if (bodyFile.exists() && !databaseFile.exists()) { 1249 Log.w(TAG, "Deleting orphaned EmailProviderBody database..."); 1250 bodyFile.delete(); 1251 } 1252 } 1253 1254 @Override 1255 public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, 1256 String sortOrder) { 1257 long time = 0L; 1258 if (Email.DEBUG) { 1259 time = System.nanoTime(); 1260 } 1261 Cursor c = null; 1262 int match; 1263 try { 1264 match = findMatch(uri, "query"); 1265 } catch (IllegalArgumentException e) { 1266 String uriString = uri.toString(); 1267 // If we were passed an illegal uri, see if it ends in /-1 1268 // if so, and if substituting 0 for -1 results in a valid uri, return an empty cursor 1269 if (uriString != null && uriString.endsWith("/-1")) { 1270 uri = Uri.parse(uriString.substring(0, uriString.length() - 2) + "0"); 1271 match = findMatch(uri, "query"); 1272 switch (match) { 1273 case BODY_ID: 1274 case MESSAGE_ID: 1275 case DELETED_MESSAGE_ID: 1276 case UPDATED_MESSAGE_ID: 1277 case ATTACHMENT_ID: 1278 case MAILBOX_ID: 1279 case ACCOUNT_ID: 1280 case HOSTAUTH_ID: 1281 return new MatrixCursor(projection, 0); 1282 } 1283 } 1284 throw e; 1285 } 1286 Context context = getContext(); 1287 // See the comment at delete(), above 1288 SQLiteDatabase db = getDatabase(context); 1289 int table = match >> BASE_SHIFT; 1290 String limit = uri.getQueryParameter(EmailContent.PARAMETER_LIMIT); 1291 String id; 1292 1293 // Find the cache for this query's table (if any) 1294 ContentCache cache = null; 1295 String tableName = TABLE_NAMES[table]; 1296 // We can only use the cache if there's no selection 1297 if (selection == null) { 1298 cache = CONTENT_CACHES[table]; 1299 } 1300 if (cache == null) { 1301 ContentCache.notCacheable(uri, selection); 1302 } 1303 1304 try { 1305 switch (match) { 1306 case BODY: 1307 case MESSAGE: 1308 case UPDATED_MESSAGE: 1309 case DELETED_MESSAGE: 1310 case ATTACHMENT: 1311 case MAILBOX: 1312 case ACCOUNT: 1313 case HOSTAUTH: 1314 c = db.query(tableName, projection, 1315 selection, selectionArgs, null, null, sortOrder, limit); 1316 break; 1317 case BODY_ID: 1318 case MESSAGE_ID: 1319 case DELETED_MESSAGE_ID: 1320 case UPDATED_MESSAGE_ID: 1321 case ATTACHMENT_ID: 1322 case MAILBOX_ID: 1323 case ACCOUNT_ID: 1324 case HOSTAUTH_ID: 1325 id = uri.getPathSegments().get(1); 1326 if (cache != null) { 1327 c = cache.getCachedCursor(id, projection); 1328 } 1329 if (c == null) { 1330 CacheToken token = null; 1331 if (cache != null) { 1332 token = cache.getCacheToken(id); 1333 } 1334 c = db.query(tableName, projection, whereWithId(id, selection), 1335 selectionArgs, null, null, sortOrder, limit); 1336 if (cache != null) { 1337 c = cache.putCursor(c, id, projection, token); 1338 } 1339 } 1340 break; 1341 case ATTACHMENTS_MESSAGE_ID: 1342 // All attachments for the given message 1343 id = uri.getPathSegments().get(2); 1344 c = db.query(Attachment.TABLE_NAME, projection, 1345 whereWith(Attachment.MESSAGE_KEY + "=" + id, selection), 1346 selectionArgs, null, null, sortOrder, limit); 1347 break; 1348 default: 1349 throw new IllegalArgumentException("Unknown URI " + uri); 1350 } 1351 } catch (SQLiteException e) { 1352 checkDatabases(); 1353 throw e; 1354 } catch (RuntimeException e) { 1355 checkDatabases(); 1356 e.printStackTrace(); 1357 throw e; 1358 } finally { 1359 if (cache != null && Email.DEBUG) { 1360 cache.recordQueryTime(c, System.nanoTime() - time); 1361 } 1362 } 1363 1364 if ((c != null) && !isTemporary()) { 1365 c.setNotificationUri(getContext().getContentResolver(), uri); 1366 } 1367 return c; 1368 } 1369 1370 private String whereWithId(String id, String selection) { 1371 StringBuilder sb = new StringBuilder(256); 1372 sb.append("_id="); 1373 sb.append(id); 1374 if (selection != null) { 1375 sb.append(" AND ("); 1376 sb.append(selection); 1377 sb.append(')'); 1378 } 1379 return sb.toString(); 1380 } 1381 1382 /** 1383 * Combine a locally-generated selection with a user-provided selection 1384 * 1385 * This introduces risk that the local selection might insert incorrect chars 1386 * into the SQL, so use caution. 1387 * 1388 * @param where locally-generated selection, must not be null 1389 * @param selection user-provided selection, may be null 1390 * @return a single selection string 1391 */ 1392 private String whereWith(String where, String selection) { 1393 if (selection == null) { 1394 return where; 1395 } 1396 StringBuilder sb = new StringBuilder(where); 1397 sb.append(" AND ("); 1398 sb.append(selection); 1399 sb.append(')'); 1400 1401 return sb.toString(); 1402 } 1403 1404 @Override 1405 public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { 1406 // Handle this special case the fastest possible way 1407 if (uri == INTEGRITY_CHECK_URI) { 1408 checkDatabases(); 1409 return 0; 1410 } 1411 1412 // Notify all existing cursors, except for ACCOUNT_RESET_NEW_COUNT(_ID) 1413 Uri notificationUri = EmailContent.CONTENT_URI; 1414 1415 int match = findMatch(uri, "update"); 1416 Context context = getContext(); 1417 ContentResolver resolver = context.getContentResolver(); 1418 // See the comment at delete(), above 1419 SQLiteDatabase db = getDatabase(context); 1420 int table = match >> BASE_SHIFT; 1421 int result; 1422 1423 // We do NOT allow setting of unreadCount/messageCount via the provider 1424 // These columns are maintained via triggers 1425 if (match == MAILBOX_ID || match == MAILBOX) { 1426 values.remove(MailboxColumns.UNREAD_COUNT); 1427 values.remove(MailboxColumns.MESSAGE_COUNT); 1428 } 1429 1430 ContentCache cache = CONTENT_CACHES[table]; 1431 String tableName = TABLE_NAMES[table]; 1432 String id; 1433 1434 try { 1435 switch (match) { 1436 case MAILBOX_ID_ADD_TO_FIELD: 1437 case ACCOUNT_ID_ADD_TO_FIELD: 1438 id = uri.getPathSegments().get(1); 1439 String field = values.getAsString(EmailContent.FIELD_COLUMN_NAME); 1440 Long add = values.getAsLong(EmailContent.ADD_COLUMN_NAME); 1441 if (field == null || add == null) { 1442 throw new IllegalArgumentException("No field/add specified " + uri); 1443 } 1444 ContentValues actualValues = new ContentValues(); 1445 if (cache != null) { 1446 cache.lock(id); 1447 } 1448 try { 1449 db.beginTransaction(); 1450 try { 1451 Cursor c = db.query(tableName, 1452 new String[] {EmailContent.RECORD_ID, field}, 1453 whereWithId(id, selection), 1454 selectionArgs, null, null, null); 1455 try { 1456 result = 0; 1457 String[] bind = new String[1]; 1458 if (c.moveToNext()) { 1459 bind[0] = c.getString(0); // _id 1460 long value = c.getLong(1) + add; 1461 actualValues.put(field, value); 1462 result = db.update(tableName, actualValues, ID_EQUALS, bind); 1463 } 1464 db.setTransactionSuccessful(); 1465 } finally { 1466 c.close(); 1467 } 1468 } finally { 1469 db.endTransaction(); 1470 } 1471 } finally { 1472 if (cache != null) { 1473 cache.unlock(id, actualValues); 1474 } 1475 } 1476 break; 1477 case SYNCED_MESSAGE_ID: 1478 resolver.notifyChange(Message.NOTIFIER_URI, null); 1479 //$FALL-THROUGH$ 1480 case UPDATED_MESSAGE_ID: 1481 case MESSAGE_ID: 1482 case BODY_ID: 1483 case ATTACHMENT_ID: 1484 case MAILBOX_ID: 1485 case ACCOUNT_ID: 1486 case HOSTAUTH_ID: 1487 id = uri.getPathSegments().get(1); 1488 if (cache != null) { 1489 cache.lock(id); 1490 } 1491 try { 1492 if (match == SYNCED_MESSAGE_ID) { 1493 // For synced messages, first copy the old message to the updated table 1494 // Note the insert or ignore semantics, guaranteeing that only the first 1495 // update will be reflected in the updated message table; therefore this 1496 // row will always have the "original" data 1497 db.execSQL(UPDATED_MESSAGE_INSERT + id); 1498 } else if (match == MESSAGE_ID) { 1499 db.execSQL(UPDATED_MESSAGE_DELETE + id); 1500 } 1501 result = db.update(tableName, values, whereWithId(id, selection), 1502 selectionArgs); 1503 } catch (SQLiteException e) { 1504 // Null out values (so they aren't cached) and re-throw 1505 values = null; 1506 throw e; 1507 } finally { 1508 if (cache != null) { 1509 cache.unlock(id, values); 1510 } 1511 } 1512 if (match == ATTACHMENT_ID) { 1513 if (values.containsKey(Attachment.FLAGS)) { 1514 int flags = values.getAsInteger(Attachment.FLAGS); 1515 AttachmentDownloadService.attachmentChanged( 1516 Integer.parseInt(id), flags); 1517 } 1518 } 1519 break; 1520 case BODY: 1521 case MESSAGE: 1522 case UPDATED_MESSAGE: 1523 case ATTACHMENT: 1524 case MAILBOX: 1525 case ACCOUNT: 1526 case HOSTAUTH: 1527 switch(match) { 1528 case MESSAGE: 1529 case ACCOUNT: 1530 case MAILBOX: 1531 case HOSTAUTH: 1532 // If we're doing some generic update, the whole cache needs to be 1533 // invalidated. This case should be quite rare 1534 cache.invalidate("Update", uri, selection); 1535 break; 1536 } 1537 result = db.update(tableName, values, selection, selectionArgs); 1538 break; 1539 case ACCOUNT_RESET_NEW_COUNT_ID: 1540 id = uri.getPathSegments().get(1); 1541 if (cache != null) { 1542 cache.lock(id); 1543 } 1544 try { 1545 result = db.update(tableName, CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT, 1546 whereWithId(id, selection), selectionArgs); 1547 } finally { 1548 if (cache != null) { 1549 cache.unlock(id, values); 1550 } 1551 } 1552 notificationUri = Account.CONTENT_URI; // Only notify account cursors. 1553 break; 1554 case ACCOUNT_RESET_NEW_COUNT: 1555 result = db.update(tableName, CONTENT_VALUES_RESET_NEW_MESSAGE_COUNT, 1556 selection, selectionArgs); 1557 // Affects all accounts. Just invalidate all account cache. 1558 cache.invalidate("Reset all new counts", null, null); 1559 notificationUri = Account.CONTENT_URI; // Only notify account cursors. 1560 break; 1561 default: 1562 throw new IllegalArgumentException("Unknown URI " + uri); 1563 } 1564 } catch (SQLiteException e) { 1565 checkDatabases(); 1566 throw e; 1567 } 1568 1569 resolver.notifyChange(notificationUri, null); 1570 return result; 1571 } 1572 1573 /* (non-Javadoc) 1574 * @see android.content.ContentProvider#applyBatch(android.content.ContentProviderOperation) 1575 */ 1576 @Override 1577 public ContentProviderResult[] applyBatch(ArrayList<ContentProviderOperation> operations) 1578 throws OperationApplicationException { 1579 Context context = getContext(); 1580 SQLiteDatabase db = getDatabase(context); 1581 db.beginTransaction(); 1582 try { 1583 ContentProviderResult[] results = super.applyBatch(operations); 1584 db.setTransactionSuccessful(); 1585 return results; 1586 } finally { 1587 db.endTransaction(); 1588 } 1589 } 1590 1591 /** 1592 * Count the number of messages in each mailbox, and update the message count column. 1593 */ 1594 /* package */ static void recalculateMessageCount(SQLiteDatabase db) { 1595 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 1596 "= (select count(*) from " + Message.TABLE_NAME + 1597 " where " + Message.MAILBOX_KEY + " = " + 1598 Mailbox.TABLE_NAME + "." + EmailContent.RECORD_ID + ")"); 1599 } 1600} 1601