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