EmailProvider.java revision 77398c42899a383680005b92955a29ab3d872c5c
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; 33 34import android.content.ContentProvider; 35import android.content.ContentProviderOperation; 36import android.content.ContentProviderResult; 37import android.content.ContentUris; 38import android.content.ContentValues; 39import android.content.Context; 40import android.content.OperationApplicationException; 41import android.content.UriMatcher; 42import android.database.Cursor; 43import android.database.SQLException; 44import android.database.sqlite.SQLiteDatabase; 45import android.database.sqlite.SQLiteOpenHelper; 46import android.net.Uri; 47import android.util.Log; 48 49import java.util.ArrayList; 50 51public class EmailProvider extends ContentProvider { 52 53 private static final String TAG = "EmailProvider"; 54 55 static final String DATABASE_NAME = "EmailProvider.db"; 56 static final String BODY_DATABASE_NAME = "EmailProviderBody.db"; 57 58 // Any changes to the database format *must* include update-in-place code. 59 60 public static final int DATABASE_VERSION = 1; 61 public static final int BODY_DATABASE_VERSION = 1; 62 63 public static final String EMAIL_AUTHORITY = "com.android.email.provider"; 64 65 private static final int ACCOUNT_BASE = 0; 66 private static final int ACCOUNT = ACCOUNT_BASE; 67 private static final int ACCOUNT_MAILBOXES = ACCOUNT_BASE + 1; 68 private static final int ACCOUNT_ID = ACCOUNT_BASE + 2; 69 private static final int ACCOUNT_ID_ADD_TO_FIELD = ACCOUNT_BASE + 3; 70 71 private static final int MAILBOX_BASE = 0x1000; 72 private static final int MAILBOX = MAILBOX_BASE; 73 private static final int MAILBOX_MESSAGES = MAILBOX_BASE + 1; 74 private static final int MAILBOX_ID = MAILBOX_BASE + 2; 75 private static final int MAILBOX_ID_ADD_TO_FIELD = MAILBOX_BASE + 3; 76 77 private static final int MESSAGE_BASE = 0x2000; 78 private static final int MESSAGE = MESSAGE_BASE; 79 private static final int MESSAGE_ID = MESSAGE_BASE + 1; 80 private static final int SYNCED_MESSAGE_ID = MESSAGE_BASE + 2; 81 82 private static final int ATTACHMENT_BASE = 0x3000; 83 private static final int ATTACHMENT = ATTACHMENT_BASE; 84 private static final int ATTACHMENT_CONTENT = ATTACHMENT_BASE + 1; 85 private static final int ATTACHMENT_ID = ATTACHMENT_BASE + 2; 86 private static final int ATTACHMENTS_MESSAGE_ID = ATTACHMENT_BASE + 3; 87 88 private static final int HOSTAUTH_BASE = 0x4000; 89 private static final int HOSTAUTH = HOSTAUTH_BASE; 90 private static final int HOSTAUTH_ID = HOSTAUTH_BASE + 1; 91 92 private static final int UPDATED_MESSAGE_BASE = 0x5000; 93 private static final int UPDATED_MESSAGE = UPDATED_MESSAGE_BASE; 94 private static final int UPDATED_MESSAGE_ID = UPDATED_MESSAGE_BASE + 1; 95 96 private static final int DELETED_MESSAGE_BASE = 0x6000; 97 private static final int DELETED_MESSAGE = DELETED_MESSAGE_BASE; 98 private static final int DELETED_MESSAGE_ID = DELETED_MESSAGE_BASE + 1; 99 private static final int DELETED_MESSAGE_MAILBOX = DELETED_MESSAGE_BASE + 2; 100 101 // MUST ALWAYS EQUAL THE LAST OF THE PREVIOUS BASE CONSTANTS 102 private static final int LAST_EMAIL_PROVIDER_DB_BASE = DELETED_MESSAGE_BASE; 103 104 // DO NOT CHANGE BODY_BASE!! 105 private static final int BODY_BASE = LAST_EMAIL_PROVIDER_DB_BASE + 0x1000; 106 private static final int BODY = BODY_BASE; 107 private static final int BODY_ID = BODY_BASE + 1; 108 private static final int BODY_MESSAGE_ID = BODY_BASE + 2; 109 private static final int BODY_HTML = BODY_BASE + 3; 110 private static final int BODY_TEXT = BODY_BASE + 4; 111 112 113 private static final int BASE_SHIFT = 12; // 12 bits to the base type: 0, 0x1000, 0x2000, etc. 114 115 private static final String[] TABLE_NAMES = { 116 EmailContent.Account.TABLE_NAME, 117 EmailContent.Mailbox.TABLE_NAME, 118 EmailContent.Message.TABLE_NAME, 119 EmailContent.Attachment.TABLE_NAME, 120 EmailContent.HostAuth.TABLE_NAME, 121 EmailContent.Message.UPDATED_TABLE_NAME, 122 EmailContent.Message.DELETED_TABLE_NAME, 123 EmailContent.Body.TABLE_NAME 124 }; 125 126 private static final UriMatcher sURIMatcher = new UriMatcher(UriMatcher.NO_MATCH); 127 128 /** 129 * Let's only generate these SQL strings once, as they are used frequently 130 * Note that this isn't relevant for table creation strings, since they are used only once 131 */ 132 private static final String UPDATED_MESSAGE_INSERT = "insert or ignore into " + 133 Message.UPDATED_TABLE_NAME + " select * from " + Message.TABLE_NAME + " where " + 134 EmailContent.RECORD_ID + '='; 135 136 private static final String UPDATED_MESSAGE_DELETE = "delete from " + 137 Message.UPDATED_TABLE_NAME + " where " + EmailContent.RECORD_ID + '='; 138 139 private static final String DELETED_MESSAGE_INSERT = "insert or replace into " + 140 Message.DELETED_TABLE_NAME + " select * from " + Message.TABLE_NAME + " where " + 141 EmailContent.RECORD_ID + '='; 142 143 private static final String DELETE_ORPHAN_BODIES = "delete from " + Body.TABLE_NAME + 144 " where " + BodyColumns.MESSAGE_KEY + " in " + "(select " + BodyColumns.MESSAGE_KEY + 145 " from " + Body.TABLE_NAME + " except select " + EmailContent.RECORD_ID + " from " + 146 Message.TABLE_NAME + ')'; 147 148 private static final String DELETE_BODY = "delete from " + Body.TABLE_NAME + 149 " where " + BodyColumns.MESSAGE_KEY + '='; 150 151 private static final String ID_EQUALS = EmailContent.RECORD_ID + "=?"; 152 153 static { 154 // Email URI matching table 155 UriMatcher matcher = sURIMatcher; 156 157 // All accounts 158 matcher.addURI(EMAIL_AUTHORITY, "account", ACCOUNT); 159 // A specific account 160 // insert into this URI causes a mailbox to be added to the account 161 matcher.addURI(EMAIL_AUTHORITY, "account/#", ACCOUNT_ID); 162 // The mailboxes in a specific account 163 matcher.addURI(EMAIL_AUTHORITY, "account/#/mailbox", ACCOUNT_MAILBOXES); 164 165 // All mailboxes 166 matcher.addURI(EMAIL_AUTHORITY, "mailbox", MAILBOX); 167 // A specific mailbox 168 // insert into this URI causes a message to be added to the mailbox 169 // ** NOTE For now, the accountKey must be set manually in the values! 170 matcher.addURI(EMAIL_AUTHORITY, "mailbox/#", MAILBOX_ID); 171 // The messages in a specific mailbox 172 matcher.addURI(EMAIL_AUTHORITY, "mailbox/#/message", MAILBOX_MESSAGES); 173 174 // All messages 175 matcher.addURI(EMAIL_AUTHORITY, "message", MESSAGE); 176 // A specific message 177 // insert into this URI causes an attachment to be added to the message 178 matcher.addURI(EMAIL_AUTHORITY, "message/#", MESSAGE_ID); 179 180 // A specific attachment 181 matcher.addURI(EMAIL_AUTHORITY, "attachment", ATTACHMENT); 182 // A specific attachment (the header information) 183 matcher.addURI(EMAIL_AUTHORITY, "attachment/#", ATTACHMENT_ID); 184 // The content for a specific attachment 185 // NOT IMPLEMENTED 186 matcher.addURI(EMAIL_AUTHORITY, "attachment/content/*", ATTACHMENT_CONTENT); 187 // The attachments of a specific message (query only) (insert & delete TBD) 188 matcher.addURI(EMAIL_AUTHORITY, "attachment/message/#", ATTACHMENTS_MESSAGE_ID); 189 190 // All mail bodies 191 matcher.addURI(EMAIL_AUTHORITY, "body", BODY); 192 // A specific mail body 193 matcher.addURI(EMAIL_AUTHORITY, "body/#", BODY_ID); 194 // The body for a specific message 195 matcher.addURI(EMAIL_AUTHORITY, "body/message/#", BODY_MESSAGE_ID); 196 // The HTML part of a specific mail body 197 matcher.addURI(EMAIL_AUTHORITY, "body/#/html", BODY_HTML); 198 // The plain text part of a specific mail body 199 matcher.addURI(EMAIL_AUTHORITY, "body/#/text", BODY_TEXT); 200 201 // All hostauth records 202 matcher.addURI(EMAIL_AUTHORITY, "hostauth", HOSTAUTH); 203 // A specific hostauth 204 matcher.addURI(EMAIL_AUTHORITY, "hostauth/#", HOSTAUTH_ID); 205 206 // Atomically a constant value to a particular field of a mailbox/account 207 matcher.addURI(EMAIL_AUTHORITY, "mailboxIdAddToField/#", MAILBOX_ID_ADD_TO_FIELD); 208 matcher.addURI(EMAIL_AUTHORITY, "accountIdAddToField/#", ACCOUNT_ID_ADD_TO_FIELD); 209 210 /** 211 * THIS URI HAS SPECIAL SEMANTICS 212 * ITS USE IS INTENDED FOR THE UI APPLICATION TO MARK CHANGES THAT NEED TO BE SYNCED BACK 213 * TO A SERVER VIA A SYNC ADAPTER 214 */ 215 matcher.addURI(EMAIL_AUTHORITY, "syncedMessage/#", SYNCED_MESSAGE_ID); 216 217 /** 218 * THE URIs BELOW THIS POINT ARE INTENDED TO BE USED BY SYNC ADAPTERS ONLY 219 * THEY REFER TO DATA CREATED AND MAINTAINED BY CALLS TO THE SYNCED_MESSAGE_ID URI 220 * BY THE UI APPLICATION 221 */ 222 // All deleted messages 223 matcher.addURI(EMAIL_AUTHORITY, "deletedMessage", DELETED_MESSAGE); 224 // A specific deleted message 225 matcher.addURI(EMAIL_AUTHORITY, "deletedMessage/#", DELETED_MESSAGE_ID); 226 // All deleted messages from a specific mailbox 227 // NOT IMPLEMENTED; do we need this as a convenience? 228 matcher.addURI(EMAIL_AUTHORITY, "deletedMessage/mailbox/#", DELETED_MESSAGE_MAILBOX); 229 230 // All updated messages 231 matcher.addURI(EMAIL_AUTHORITY, "updatedMessage", UPDATED_MESSAGE); 232 // A specific updated message 233 matcher.addURI(EMAIL_AUTHORITY, "updatedMessage/#", UPDATED_MESSAGE_ID); 234 } 235 236 /* 237 * Internal helper method for index creation. 238 * Example: 239 * "create index message_" + MessageColumns.FLAG_READ 240 * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");" 241 */ 242 /* package */ 243 static String createIndex(String tableName, String columnName) { 244 return "create index " + tableName.toLowerCase() + '_' + columnName 245 + " on " + tableName + " (" + columnName + ");"; 246 } 247 248 static void createMessageTable(SQLiteDatabase db) { 249 String messageColumns = MessageColumns.DISPLAY_NAME + " text, " 250 + MessageColumns.TIMESTAMP + " integer, " 251 + MessageColumns.SUBJECT + " text, " 252 + MessageColumns.PREVIEW + " text, " 253 + MessageColumns.FLAG_READ + " integer, " 254 + MessageColumns.FLAG_LOADED + " integer, " 255 + MessageColumns.FLAG_FAVORITE + " integer, " 256 + MessageColumns.FLAG_ATTACHMENT + " integer, " 257 + MessageColumns.FLAGS + " integer, " 258 + MessageColumns.TEXT_INFO + " text, " 259 + MessageColumns.HTML_INFO + " text, " 260 + MessageColumns.CLIENT_ID + " integer, " 261 + MessageColumns.MESSAGE_ID + " text, " 262 + MessageColumns.THREAD_ID + " text, " 263 + MessageColumns.MAILBOX_KEY + " integer, " 264 + MessageColumns.ACCOUNT_KEY + " integer, " 265 + MessageColumns.REFERENCE_KEY + " integer, " 266 + MessageColumns.SENDER_LIST + " text, " 267 + MessageColumns.FROM_LIST + " text, " 268 + MessageColumns.TO_LIST + " text, " 269 + MessageColumns.CC_LIST + " text, " 270 + MessageColumns.BCC_LIST + " text, " 271 + MessageColumns.REPLY_TO_LIST + " text" 272 + ");"; 273 274 // This String and the following String MUST have the same columns, except for the type 275 // of those columns! 276 String createString = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 277 + SyncColumns.ACCOUNT_KEY + " integer, " 278 + SyncColumns.SERVER_ID + " integer, " 279 + SyncColumns.SERVER_VERSION + " integer, " 280 + SyncColumns.DATA + " text, " 281 + SyncColumns.DIRTY_COUNT + " integer, " 282 + messageColumns; 283 284 // For the updated and deleted tables, the id is assigned, but we do want to keep track 285 // of the ORDER of updates using an autoincrement primary key. We use the DATA column 286 // at this point; it has no other function 287 String altCreateString = " (" + EmailContent.RECORD_ID + " integer unique, " 288 + SyncColumns.ACCOUNT_KEY + " integer, " 289 + SyncColumns.SERVER_ID + " integer, " 290 + SyncColumns.SERVER_VERSION + " integer, " 291 + SyncColumns.DATA + " integer primary key autoincrement, " 292 + SyncColumns.DIRTY_COUNT + " integer, " 293 + messageColumns; 294 295 // The three tables have the same schema 296 db.execSQL("create table " + Message.TABLE_NAME + createString); 297 db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString); 298 db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString); 299 300 String indexColumns[] = { 301 MessageColumns.TIMESTAMP, 302 MessageColumns.FLAG_READ, 303 MessageColumns.FLAG_LOADED, 304 MessageColumns.MAILBOX_KEY, 305 SyncColumns.SERVER_ID 306 }; 307 308 for (String columnName : indexColumns) { 309 db.execSQL(createIndex(Message.TABLE_NAME, columnName)); 310 } 311 312 // Deleting a Message deletes all associated Attachments 313 // Deleting the associated Body cannot be done in a trigger, because the Body is stored 314 // in a separate database, and trigger cannot operate on attached databases. 315 db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME + 316 " begin delete from " + Attachment.TABLE_NAME + 317 " where " + AttachmentColumns.MESSAGE_KEY + "=old." + EmailContent.RECORD_ID + 318 "; end"); 319 320 // Add triggers to keep unread count accurate per mailbox 321 322 // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox 323 db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME + 324 " when NEW." + MessageColumns.FLAG_READ + "=0" + 325 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 326 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 327 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 328 "; end"); 329 330 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 331 db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME + 332 " when OLD." + MessageColumns.FLAG_READ + "=0" + 333 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 334 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 335 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 336 "; end"); 337 338 // Change a message's mailbox 339 db.execSQL("create trigger unread_message_move before update of " + 340 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 341 " when OLD." + MessageColumns.FLAG_READ + "=0" + 342 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 343 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 344 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 345 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 346 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 347 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 348 "; end"); 349 350 // Change a message's read state 351 db.execSQL("create trigger unread_message_read before update of " + 352 MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME + 353 " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ + 354 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 355 '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ + 356 " when 0 then -1 else 1 end" + 357 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 358 "; end"); 359 } 360 361 static void upgradeMessageTable(SQLiteDatabase db, int oldVersion, int newVersion) { 362 try { 363 db.execSQL("drop table " + Message.TABLE_NAME); 364 db.execSQL("drop table " + Message.UPDATED_TABLE_NAME); 365 db.execSQL("drop table " + Message.DELETED_TABLE_NAME); 366 } catch (SQLException e) { 367 } 368 createMessageTable(db); 369 } 370 371 static void createAccountTable(SQLiteDatabase db) { 372 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 373 + AccountColumns.DISPLAY_NAME + " text, " 374 + AccountColumns.EMAIL_ADDRESS + " text, " 375 + AccountColumns.SYNC_KEY + " text, " 376 + AccountColumns.SYNC_LOOKBACK + " integer, " 377 + AccountColumns.SYNC_INTERVAL + " text, " 378 + AccountColumns.HOST_AUTH_KEY_RECV + " integer, " 379 + AccountColumns.HOST_AUTH_KEY_SEND + " integer, " 380 + AccountColumns.FLAGS + " integer, " 381 + AccountColumns.IS_DEFAULT + " integer, " 382 + AccountColumns.COMPATIBILITY_UUID + " text, " 383 + AccountColumns.SENDER_NAME + " text, " 384 + AccountColumns.RINGTONE_URI + " text, " 385 + AccountColumns.PROTOCOL_VERSION + " text, " 386 + AccountColumns.NEW_MESSAGE_COUNT + " integer" 387 + ");"; 388 db.execSQL("create table " + Account.TABLE_NAME + s); 389 // Deleting an account deletes associated Mailboxes and HostAuth's 390 db.execSQL("create trigger account_delete before delete on " + Account.TABLE_NAME + 391 " begin delete from " + Mailbox.TABLE_NAME + 392 " where " + MailboxColumns.ACCOUNT_KEY + "=old." + EmailContent.RECORD_ID + 393 "; delete from " + HostAuth.TABLE_NAME + 394 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV + 395 "; delete from " + HostAuth.TABLE_NAME + 396 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND + 397 "; end"); 398 } 399 400 static void upgradeAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) { 401 try { 402 db.execSQL("drop table " + Account.TABLE_NAME); 403 } catch (SQLException e) { 404 } 405 createAccountTable(db); 406 } 407 408 static void createHostAuthTable(SQLiteDatabase db) { 409 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 410 + HostAuthColumns.PROTOCOL + " text, " 411 + HostAuthColumns.ADDRESS + " text, " 412 + HostAuthColumns.PORT + " integer, " 413 + HostAuthColumns.FLAGS + " integer, " 414 + HostAuthColumns.LOGIN + " text, " 415 + HostAuthColumns.PASSWORD + " text, " 416 + HostAuthColumns.DOMAIN + " text, " 417 + HostAuthColumns.ACCOUNT_KEY + " integer" 418 + ");"; 419 db.execSQL("create table " + HostAuth.TABLE_NAME + s); 420 } 421 422 static void upgradeHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) { 423 try { 424 db.execSQL("drop table " + HostAuth.TABLE_NAME); 425 } catch (SQLException e) { 426 } 427 createHostAuthTable(db); 428 } 429 430 static void createMailboxTable(SQLiteDatabase db) { 431 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 432 + MailboxColumns.DISPLAY_NAME + " text, " 433 + MailboxColumns.SERVER_ID + " text unique on conflict replace, " 434 + MailboxColumns.PARENT_SERVER_ID + " text, " 435 + MailboxColumns.ACCOUNT_KEY + " integer, " 436 + MailboxColumns.TYPE + " integer, " 437 + MailboxColumns.DELIMITER + " integer, " 438 + MailboxColumns.SYNC_KEY + " text, " 439 + MailboxColumns.SYNC_LOOKBACK + " integer, " 440 + MailboxColumns.SYNC_INTERVAL + " integer, " 441 + MailboxColumns.SYNC_TIME + " integer, " 442 + MailboxColumns.UNREAD_COUNT + " integer, " 443 + MailboxColumns.FLAG_VISIBLE + " integer, " 444 + MailboxColumns.FLAGS + " integer, " 445 + MailboxColumns.VISIBLE_LIMIT + " integer, " 446 + MailboxColumns.SYNC_STATUS + " text" 447 + ");"; 448 db.execSQL("create table " + Mailbox.TABLE_NAME + s); 449 db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID 450 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")"); 451 db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY 452 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")"); 453 // Deleting a Mailbox deletes associated Messages 454 db.execSQL("create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME + 455 " begin delete from " + Message.TABLE_NAME + 456 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 457 "; end"); 458 } 459 460 static void upgradeMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) { 461 try { 462 db.execSQL("drop table " + Mailbox.TABLE_NAME); 463 } catch (SQLException e) { 464 } 465 createMailboxTable(db); 466 } 467 468 static void createAttachmentTable(SQLiteDatabase db) { 469 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 470 + AttachmentColumns.FILENAME + " text, " 471 + AttachmentColumns.MIME_TYPE + " text, " 472 + AttachmentColumns.SIZE + " integer, " 473 + AttachmentColumns.CONTENT_ID + " text, " 474 + AttachmentColumns.CONTENT_URI + " text, " 475 + AttachmentColumns.MESSAGE_KEY + " integer, " 476 + AttachmentColumns.LOCATION + " text, " 477 + AttachmentColumns.ENCODING + " text" 478 + ");"; 479 db.execSQL("create table " + Attachment.TABLE_NAME + s); 480 db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY)); 481 } 482 483 static void upgradeAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) { 484 try { 485 db.execSQL("drop table " + Attachment.TABLE_NAME); 486 } catch (SQLException e) { 487 } 488 createAttachmentTable(db); 489 } 490 491 static void createBodyTable(SQLiteDatabase db) { 492 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 493 + BodyColumns.MESSAGE_KEY + " integer, " 494 + BodyColumns.HTML_CONTENT + " text, " 495 + BodyColumns.TEXT_CONTENT + " text" 496 + ");"; 497 db.execSQL("create table " + Body.TABLE_NAME + s); 498 db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY)); 499 } 500 501 static void upgradeBodyTable(SQLiteDatabase db, int oldVersion, int newVersion) { 502 try { 503 db.execSQL("drop table " + Body.TABLE_NAME); 504 } catch (SQLException e) { 505 } 506 createBodyTable(db); 507 } 508 509 private final int mDatabaseVersion = DATABASE_VERSION; 510 private final int mBodyDatabaseVersion = BODY_DATABASE_VERSION; 511 512 private SQLiteDatabase mDatabase; 513 private SQLiteDatabase mBodyDatabase; 514 private boolean mInTransaction = false; 515 516 public synchronized SQLiteDatabase getDatabase(Context context) { 517 if (mDatabase != null) { 518 return mDatabase; 519 } 520 DatabaseHelper helper = new DatabaseHelper(context, DATABASE_NAME); 521 mDatabase = helper.getWritableDatabase(); 522 if (mDatabase != null) { 523 mDatabase.setLockingEnabled(true); 524 BodyDatabaseHelper bodyHelper = new BodyDatabaseHelper(context, BODY_DATABASE_NAME); 525 mBodyDatabase = bodyHelper.getWritableDatabase(); 526 if (mBodyDatabase != null) { 527 mBodyDatabase.setLockingEnabled(true); 528 String bodyFileName = mBodyDatabase.getPath(); 529 mDatabase.execSQL("attach \"" + bodyFileName + "\" as BodyDatabase"); 530 } 531 } 532 return mDatabase; 533 } 534 535 private class BodyDatabaseHelper extends SQLiteOpenHelper { 536 BodyDatabaseHelper(Context context, String name) { 537 super(context, name, null, mBodyDatabaseVersion); 538 } 539 540 @Override 541 public void onCreate(SQLiteDatabase db) { 542 // Create all tables here; each class has its own method 543 createBodyTable(db); 544 } 545 546 @Override 547 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 548 upgradeBodyTable(db, oldVersion, newVersion); 549 } 550 551 @Override 552 public void onOpen(SQLiteDatabase db) { 553 } 554 } 555 556 private class DatabaseHelper extends SQLiteOpenHelper { 557 DatabaseHelper(Context context, String name) { 558 super(context, name, null, mDatabaseVersion); 559 } 560 561 @Override 562 public void onCreate(SQLiteDatabase db) { 563 // Create all tables here; each class has its own method 564 createMessageTable(db); 565 createAttachmentTable(db); 566 createMailboxTable(db); 567 createHostAuthTable(db); 568 createAccountTable(db); 569 } 570 571 @Override 572 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 573 upgradeMessageTable(db, oldVersion, newVersion); 574 upgradeAttachmentTable(db, oldVersion, newVersion); 575 upgradeMailboxTable(db, oldVersion, newVersion); 576 upgradeHostAuthTable(db, oldVersion, newVersion); 577 upgradeAccountTable(db, oldVersion, newVersion); 578 } 579 580 @Override 581 public void onOpen(SQLiteDatabase db) { 582 } 583 } 584 585 @Override 586 public int delete(Uri uri, String selection, String[] selectionArgs) { 587 final int match = sURIMatcher.match(uri); 588 Context context = getContext(); 589 // Pick the correct database for this operation 590 // If we're in a transaction already (which would happen during applyBatch), then the 591 // body database is already attached to the email database and any attempt to use the 592 // body database directly will result in a SQLiteException (the database is locked) 593 SQLiteDatabase db = getDatabase(context); 594 int table = match >> BASE_SHIFT; 595 String id = "0"; 596 boolean messageDeletion = false; 597 598 if (Email.LOGD) { 599 Log.v(TAG, "EmailProvider.delete: uri=" + uri + ", match is " + match); 600 } 601 602 int result = -1; 603 604 try { 605 switch (match) { 606 // These are cases in which one or more Messages might get deleted, either by 607 // cascade or explicitly 608 case MAILBOX_ID: 609 case MAILBOX: 610 case ACCOUNT_ID: 611 case ACCOUNT: 612 case MESSAGE: 613 case SYNCED_MESSAGE_ID: 614 case MESSAGE_ID: 615 // Handle lost Body records here, since this cannot be done in a trigger 616 // The process is: 617 // 1) Begin a transaction, ensuring that both databases are affected atomically 618 // 2) Do the requested deletion, with cascading deletions handled in triggers 619 // 3) End the transaction, committing all changes atomically 620 621 messageDeletion = true; 622 if (!mInTransaction) { 623 db.beginTransaction(); 624 } 625 break; 626 } 627 switch (match) { 628 case BODY_ID: 629 case DELETED_MESSAGE_ID: 630 case SYNCED_MESSAGE_ID: 631 case MESSAGE_ID: 632 case UPDATED_MESSAGE_ID: 633 case ATTACHMENT_ID: 634 case MAILBOX_ID: 635 case ACCOUNT_ID: 636 case HOSTAUTH_ID: 637 id = uri.getPathSegments().get(1); 638 if (match == SYNCED_MESSAGE_ID) { 639 // For synced messages, first copy the old message to the deleted table and 640 // delete it from the updated table (in case it was updated first) 641 // Note that this is all within a transaction, for atomicity 642 db.execSQL(DELETED_MESSAGE_INSERT + id); 643 db.execSQL(UPDATED_MESSAGE_DELETE + id); 644 } 645 result = db.delete(TABLE_NAMES[table], whereWithId(id, selection), 646 selectionArgs); 647 break; 648 case BODY: 649 case MESSAGE: 650 case DELETED_MESSAGE: 651 case UPDATED_MESSAGE: 652 case ATTACHMENT: 653 case MAILBOX: 654 case ACCOUNT: 655 case HOSTAUTH: 656 result = db.delete(TABLE_NAMES[table], selection, selectionArgs); 657 break; 658 default: 659 throw new IllegalArgumentException("Unknown URI " + uri); 660 } 661 if (messageDeletion) { 662 if (match == MESSAGE_ID) { 663 // Delete the Body record associated with the deleted message 664 db.execSQL(DELETE_BODY + id); 665 } else { 666 // Delete any orphaned Body records 667 db.execSQL(DELETE_ORPHAN_BODIES); 668 } 669 if (!mInTransaction) { 670 db.setTransactionSuccessful(); 671 } 672 } 673 } finally { 674 if (messageDeletion) { 675 if (!mInTransaction) { 676 db.endTransaction(); 677 } 678 } 679 } 680 getContext().getContentResolver().notifyChange(uri, null); 681 return result; 682 } 683 684 @Override 685 // Use the email- prefix because message, mailbox, and account are so generic (e.g. SMS, IM) 686 public String getType(Uri uri) { 687 int match = sURIMatcher.match(uri); 688 switch (match) { 689 case BODY_ID: 690 return "vnd.android.cursor.item/email-body"; 691 case BODY: 692 return "vnd.android.cursor.dir/email-message"; 693 case UPDATED_MESSAGE_ID: 694 case MESSAGE_ID: 695 return "vnd.android.cursor.item/email-message"; 696 case MAILBOX_MESSAGES: 697 case UPDATED_MESSAGE: 698 case MESSAGE: 699 return "vnd.android.cursor.dir/email-message"; 700 case ACCOUNT_MAILBOXES: 701 case MAILBOX: 702 return "vnd.android.cursor.dir/email-mailbox"; 703 case MAILBOX_ID: 704 return "vnd.android.cursor.item/email-mailbox"; 705 case ACCOUNT: 706 return "vnd.android.cursor.dir/email-account"; 707 case ACCOUNT_ID: 708 return "vnd.android.cursor.item/email-account"; 709 case ATTACHMENTS_MESSAGE_ID: 710 case ATTACHMENT: 711 return "vnd.android.cursor.dir/email-attachment"; 712 case ATTACHMENT_ID: 713 return "vnd.android.cursor.item/email-attachment"; 714 case HOSTAUTH: 715 return "vnd.android.cursor.dir/email-hostauth"; 716 case HOSTAUTH_ID: 717 return "vnd.android.cursor.item/email-hostauth"; 718 default: 719 throw new IllegalArgumentException("Unknown URI " + uri); 720 } 721 } 722 723 @Override 724 public Uri insert(Uri uri, ContentValues values) { 725 int match = sURIMatcher.match(uri); 726 Context context = getContext(); 727 // See the comment at delete(), above 728 SQLiteDatabase db = getDatabase(context); 729 int table = match >> BASE_SHIFT; 730 long id; 731 732 if (Email.LOGD) { 733 Log.v(TAG, "EmailProvider.insert: uri=" + uri + ", match is " + match); 734 } 735 736 Uri resultUri = null; 737 738 switch (match) { 739 case BODY: 740 case MESSAGE: 741 case ATTACHMENT: 742 case MAILBOX: 743 case ACCOUNT: 744 case HOSTAUTH: 745 // Make sure all new message records have dirty count of 0 746 if (match == MESSAGE) { 747 values.put(SyncColumns.DIRTY_COUNT, 0); 748 } 749 id = db.insert(TABLE_NAMES[table], "foo", values); 750 resultUri = ContentUris.withAppendedId(uri, id); 751 break; 752 case MAILBOX_ID: 753 // This implies adding a message to a mailbox 754 // Hmm, one problem here is that we can't link the account as well, so it must be 755 // already in the values... 756 id = Long.parseLong(uri.getPathSegments().get(1)); 757 values.put(MessageColumns.MAILBOX_KEY, id); 758 resultUri = insert(Message.CONTENT_URI, values); 759 break; 760 case MESSAGE_ID: 761 // This implies adding an attachment to a message. 762 id = Long.parseLong(uri.getPathSegments().get(1)); 763 values.put(AttachmentColumns.MESSAGE_KEY, id); 764 resultUri = insert(Attachment.CONTENT_URI, values); 765 break; 766 case ACCOUNT_ID: 767 // This implies adding a mailbox to an account. 768 id = Long.parseLong(uri.getPathSegments().get(1)); 769 values.put(MailboxColumns.ACCOUNT_KEY, id); 770 resultUri = insert(Mailbox.CONTENT_URI, values); 771 break; 772 case ATTACHMENTS_MESSAGE_ID: 773 id = db.insert(TABLE_NAMES[table], "foo", values); 774 resultUri = ContentUris.withAppendedId(Attachment.CONTENT_URI, id); 775 break; 776 default: 777 throw new IllegalArgumentException("Unknown URL " + uri); 778 } 779 780 // Notify with the base uri, not the new uri (nobody is watching a new record) 781 getContext().getContentResolver().notifyChange(uri, null); 782 return resultUri; 783 } 784 785 @Override 786 public boolean onCreate() { 787 // TODO Auto-generated method stub 788 return false; 789 } 790 791 @Override 792 public Cursor query(Uri uri, String[] projection, String selection, String[] selectionArgs, 793 String sortOrder) { 794 Cursor c = null; 795 Uri notificationUri = EmailContent.CONTENT_URI; 796 int match = sURIMatcher.match(uri); 797 Context context = getContext(); 798 // See the comment at delete(), above 799 SQLiteDatabase db = getDatabase(context); 800 int table = match >> BASE_SHIFT; 801 String id; 802 803 if (Email.LOGD) { 804 Log.v(TAG, "EmailProvider.query: uri=" + uri + ", match is " + match); 805 } 806 807 switch (match) { 808 case BODY: 809 case MESSAGE: 810 case UPDATED_MESSAGE: 811 case DELETED_MESSAGE: 812 case ATTACHMENT: 813 case MAILBOX: 814 case ACCOUNT: 815 case HOSTAUTH: 816 c = db.query(TABLE_NAMES[table], projection, 817 selection, selectionArgs, null, null, sortOrder); 818 break; 819 case BODY_ID: 820 case MESSAGE_ID: 821 case DELETED_MESSAGE_ID: 822 case UPDATED_MESSAGE_ID: 823 case ATTACHMENT_ID: 824 case MAILBOX_ID: 825 case ACCOUNT_ID: 826 case HOSTAUTH_ID: 827 id = uri.getPathSegments().get(1); 828 c = db.query(TABLE_NAMES[table], projection, 829 whereWithId(id, selection), selectionArgs, null, null, sortOrder); 830 break; 831 case ATTACHMENTS_MESSAGE_ID: 832 // All attachments for the given message 833 id = uri.getPathSegments().get(2); 834 c = db.query(Attachment.TABLE_NAME, projection, 835 whereWith(Attachment.MESSAGE_KEY + "=" + id, selection), 836 selectionArgs, null, null, sortOrder); 837 break; 838 default: 839 throw new IllegalArgumentException("Unknown URI " + uri); 840 } 841 842 if ((c != null) && !isTemporary()) { 843 c.setNotificationUri(getContext().getContentResolver(), notificationUri); 844 } 845 return c; 846 } 847 848 private String whereWithId(String id, String selection) { 849 StringBuilder sb = new StringBuilder(256); 850 sb.append("_id="); 851 sb.append(id); 852 if (selection != null) { 853 sb.append(" AND "); 854 sb.append(selection); 855 } 856 return sb.toString(); 857 } 858 859 private String whereWith(String where, String selection) { 860 StringBuilder sb = new StringBuilder(where); 861 if (selection != null) { 862 sb.append(" AND "); 863 sb.append(selection); 864 } 865 return sb.toString(); 866 } 867 868 @Override 869 public int update(Uri uri, ContentValues values, String selection, String[] selectionArgs) { 870 int match = sURIMatcher.match(uri); 871 Context context = getContext(); 872 // See the comment at delete(), above 873 SQLiteDatabase db = getDatabase(context); 874 int table = match >> BASE_SHIFT; 875 int result; 876 877 if (Email.LOGD) { 878 Log.v(TAG, "EmailProvider.update: uri=" + uri + ", match is " + match); 879 } 880 881 // We do NOT allow setting of unreadCount via the provider 882 // This column is maintained via triggers 883 if (match == MAILBOX_ID || match == MAILBOX) { 884 values.remove(MailboxColumns.UNREAD_COUNT); 885 } 886 887 String id; 888 switch (match) { 889 case MAILBOX_ID_ADD_TO_FIELD: 890 case ACCOUNT_ID_ADD_TO_FIELD: 891 if (!mInTransaction) { 892 db.beginTransaction(); 893 } 894 id = uri.getPathSegments().get(1); 895 String field = values.getAsString(EmailContent.FIELD_COLUMN_NAME); 896 Long add = values.getAsLong(EmailContent.ADD_COLUMN_NAME); 897 if (field == null || add == null) { 898 throw new IllegalArgumentException("No field/add specified " + uri); 899 } 900 Cursor c = db.query(TABLE_NAMES[table], 901 new String[] {EmailContent.RECORD_ID, field}, whereWithId(id, selection), 902 selectionArgs, null, null, null); 903 try { 904 result = 0; 905 ContentValues cv = new ContentValues(); 906 String[] bind = new String[1]; 907 while (c.moveToNext()) { 908 bind[0] = c.getString(0); 909 long value = c.getLong(1) + add; 910 cv.put(field, value); 911 result = db.update(TABLE_NAMES[table], cv, ID_EQUALS, bind); 912 } 913 } finally { 914 c.close(); 915 } 916 if (!mInTransaction) { 917 db.setTransactionSuccessful(); 918 db.endTransaction(); 919 } 920 break; 921 case BODY_ID: 922 case MESSAGE_ID: 923 case SYNCED_MESSAGE_ID: 924 case UPDATED_MESSAGE_ID: 925 case ATTACHMENT_ID: 926 case MAILBOX_ID: 927 case ACCOUNT_ID: 928 case HOSTAUTH_ID: 929 id = uri.getPathSegments().get(1); 930 if (match == SYNCED_MESSAGE_ID) { 931 // For synced messages, first copy the old message to the updated table 932 // Note the insert or ignore semantics, guaranteeing that only the first 933 // update will be reflected in the updated message table; therefore this row 934 // will always have the "original" data 935 db.execSQL(UPDATED_MESSAGE_INSERT + id); 936 } else if (match == MESSAGE_ID) { 937 db.execSQL(UPDATED_MESSAGE_DELETE + id); 938 } 939 result = db.update(TABLE_NAMES[table], values, whereWithId(id, selection), 940 selectionArgs); 941 break; 942 case BODY: 943 case MESSAGE: 944 case UPDATED_MESSAGE: 945 case ATTACHMENT: 946 case MAILBOX: 947 case ACCOUNT: 948 case HOSTAUTH: 949 result = db.update(TABLE_NAMES[table], values, selection, selectionArgs); 950 break; 951 default: 952 throw new IllegalArgumentException("Unknown URI " + uri); 953 } 954 955 getContext().getContentResolver().notifyChange(uri, null); 956 return result; 957 } 958 959 /* (non-Javadoc) 960 * @see android.content.ContentProvider#applyBatch(android.content.ContentProviderOperation) 961 */ 962 @Override 963 public ContentProviderResult[] applyBatch(ArrayList<ContentProviderOperation> operations) 964 throws OperationApplicationException { 965 Context context = getContext(); 966 SQLiteDatabase db = getDatabase(context); 967 db.beginTransaction(); 968 mInTransaction = true; 969 try { 970 ContentProviderResult[] results = super.applyBatch(operations); 971 db.setTransactionSuccessful(); 972 return results; 973 } finally { 974 db.endTransaction(); 975 mInTransaction = false; 976 } 977 } 978} 979