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