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