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