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