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