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