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