1 /* 2 * Copyright (C) 2012 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 17 package com.android.email.provider; 18 19 import android.accounts.AccountManager; 20 import android.content.ContentResolver; 21 import android.content.ContentValues; 22 import android.content.Context; 23 import android.database.Cursor; 24 import android.database.SQLException; 25 import android.database.sqlite.SQLiteDatabase; 26 import android.database.sqlite.SQLiteOpenHelper; 27 import android.provider.ContactsContract; 28 import android.util.Log; 29 30 import com.android.email.Email; 31 import com.android.emailcommon.AccountManagerTypes; 32 import com.android.emailcommon.CalendarProviderStub; 33 import com.android.emailcommon.mail.Address; 34 import com.android.emailcommon.provider.Account; 35 import com.android.emailcommon.provider.EmailContent; 36 import com.android.emailcommon.provider.EmailContent.AccountColumns; 37 import com.android.emailcommon.provider.EmailContent.Attachment; 38 import com.android.emailcommon.provider.EmailContent.AttachmentColumns; 39 import com.android.emailcommon.provider.EmailContent.Body; 40 import com.android.emailcommon.provider.EmailContent.BodyColumns; 41 import com.android.emailcommon.provider.EmailContent.HostAuthColumns; 42 import com.android.emailcommon.provider.EmailContent.MailboxColumns; 43 import com.android.emailcommon.provider.EmailContent.Message; 44 import com.android.emailcommon.provider.EmailContent.MessageColumns; 45 import com.android.emailcommon.provider.EmailContent.PolicyColumns; 46 import com.android.emailcommon.provider.EmailContent.QuickResponseColumns; 47 import com.android.emailcommon.provider.EmailContent.SyncColumns; 48 import com.android.emailcommon.provider.HostAuth; 49 import com.android.emailcommon.provider.Mailbox; 50 import com.android.emailcommon.provider.Policy; 51 import com.android.emailcommon.provider.QuickResponse; 52 import com.android.emailcommon.service.LegacyPolicySet; 53 import com.google.common.annotations.VisibleForTesting; 54 55 public final class DBHelper { 56 private static final String TAG = "EmailProvider"; 57 58 private static final String WHERE_ID = EmailContent.RECORD_ID + "=?"; 59 60 private static final String TRIGGER_MAILBOX_DELETE = 61 "create trigger mailbox_delete before delete on " + Mailbox.TABLE_NAME + 62 " begin" + 63 " delete from " + Message.TABLE_NAME + 64 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 65 "; delete from " + Message.UPDATED_TABLE_NAME + 66 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 67 "; delete from " + Message.DELETED_TABLE_NAME + 68 " where " + MessageColumns.MAILBOX_KEY + "=old." + EmailContent.RECORD_ID + 69 "; end"; 70 71 private static final String TRIGGER_ACCOUNT_DELETE = 72 "create trigger account_delete before delete on " + Account.TABLE_NAME + 73 " begin delete from " + Mailbox.TABLE_NAME + 74 " where " + MailboxColumns.ACCOUNT_KEY + "=old." + EmailContent.RECORD_ID + 75 "; delete from " + HostAuth.TABLE_NAME + 76 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_RECV + 77 "; delete from " + HostAuth.TABLE_NAME + 78 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.HOST_AUTH_KEY_SEND + 79 "; delete from " + Policy.TABLE_NAME + 80 " where " + EmailContent.RECORD_ID + "=old." + AccountColumns.POLICY_KEY + 81 "; end"; 82 83 // Any changes to the database format *must* include update-in-place code. 84 // Original version: 3 85 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 86 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 87 // Version 6: Adding Message.mServerTimeStamp column 88 // Version 7: Replace the mailbox_delete trigger with a version that removes orphaned messages 89 // from the Message_Deletes and Message_Updates tables 90 // Version 8: Add security flags column to accounts table 91 // Version 9: Add security sync key and signature to accounts table 92 // Version 10: Add meeting info to message table 93 // Version 11: Add content and flags to attachment table 94 // Version 12: Add content_bytes to attachment table. content is deprecated. 95 // Version 13: Add messageCount to Mailbox table. 96 // Version 14: Add snippet to Message table 97 // Version 15: Fix upgrade problem in version 14. 98 // Version 16: Add accountKey to Attachment table 99 // Version 17: Add parentKey to Mailbox table 100 // Version 18: Copy Mailbox.displayName to Mailbox.serverId for all IMAP & POP3 mailboxes. 101 // Column Mailbox.serverId is used for the server-side pathname of a mailbox. 102 // Version 19: Add Policy table; add policyKey to Account table and trigger to delete an 103 // Account's policy when the Account is deleted 104 // Version 20: Add new policies to Policy table 105 // Version 21: Add lastSeenMessageKey column to Mailbox table 106 // Version 22: Upgrade path for IMAP/POP accounts to integrate with AccountManager 107 // Version 23: Add column to mailbox table for time of last access 108 // Version 24: Add column to hostauth table for client cert alias 109 // Version 25: Added QuickResponse table 110 // Version 26: Update IMAP accounts to add FLAG_SUPPORTS_SEARCH flag 111 // Version 27: Add protocolSearchInfo to Message table 112 // Version 28: Add notifiedMessageId and notifiedMessageCount to Account 113 // Version 29: Add protocolPoliciesEnforced and protocolPoliciesUnsupported to Policy 114 // Version 30: Use CSV of RFC822 addresses instead of "packed" values 115 // Version 31: Add columns to mailbox for ui status/last result 116 // Version 32: Add columns to mailbox for last notified message key/count; insure not null 117 // for "notified" columns 118 // Version 33: Add columns to attachment for ui provider columns 119 // Version 34: Add total count to mailbox 120 // Version 35: Set up defaults for lastTouchedCount for drafts and sent 121 // Version 36: mblank intentionally left this space 122 // Version 37: Add flag for settings support in folders 123 // Version 38&39: Add threadTopic to message (for future support) 124 125 // Versions 100+ are in Email2 126 127 public static final int DATABASE_VERSION = 39; 128 129 // Any changes to the database format *must* include update-in-place code. 130 // Original version: 2 131 // Version 3: Add "sourceKey" column 132 // Version 4: Database wipe required; changing AccountManager interface w/Exchange 133 // Version 5: Database wipe required; changing AccountManager interface w/Exchange 134 // Version 6: Adding Body.mIntroText column 135 // Version 7/8: Adding quoted text start pos 136 137 // Versions 100+ are in Email2 138 139 public static final int BODY_DATABASE_VERSION = 8; 140 141 /* 142 * Internal helper method for index creation. 143 * Example: 144 * "create index message_" + MessageColumns.FLAG_READ 145 * + " on " + Message.TABLE_NAME + " (" + MessageColumns.FLAG_READ + ");" 146 */ 147 /* package */ 148 static String createIndex(String tableName, String columnName) { 149 return "create index " + tableName.toLowerCase() + '_' + columnName 150 + " on " + tableName + " (" + columnName + ");"; 151 } 152 153 static void createMessageTable(SQLiteDatabase db) { 154 String messageColumns = MessageColumns.DISPLAY_NAME + " text, " 155 + MessageColumns.TIMESTAMP + " integer, " 156 + MessageColumns.SUBJECT + " text, " 157 + MessageColumns.FLAG_READ + " integer, " 158 + MessageColumns.FLAG_LOADED + " integer, " 159 + MessageColumns.FLAG_FAVORITE + " integer, " 160 + MessageColumns.FLAG_ATTACHMENT + " integer, " 161 + MessageColumns.FLAGS + " integer, " 162 + MessageColumns.CLIENT_ID + " integer, " 163 + MessageColumns.MESSAGE_ID + " text, " 164 + MessageColumns.MAILBOX_KEY + " integer, " 165 + MessageColumns.ACCOUNT_KEY + " integer, " 166 + MessageColumns.FROM_LIST + " text, " 167 + MessageColumns.TO_LIST + " text, " 168 + MessageColumns.CC_LIST + " text, " 169 + MessageColumns.BCC_LIST + " text, " 170 + MessageColumns.REPLY_TO_LIST + " text, " 171 + MessageColumns.MEETING_INFO + " text, " 172 + MessageColumns.SNIPPET + " text, " 173 + MessageColumns.PROTOCOL_SEARCH_INFO + " text, " 174 + MessageColumns.THREAD_TOPIC + " text" 175 + ");"; 176 177 // This String and the following String MUST have the same columns, except for the type 178 // of those columns! 179 String createString = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 180 + SyncColumns.SERVER_ID + " text, " 181 + SyncColumns.SERVER_TIMESTAMP + " integer, " 182 + messageColumns; 183 184 // For the updated and deleted tables, the id is assigned, but we do want to keep track 185 // of the ORDER of updates using an autoincrement primary key. We use the DATA column 186 // at this point; it has no other function 187 String altCreateString = " (" + EmailContent.RECORD_ID + " integer unique, " 188 + SyncColumns.SERVER_ID + " text, " 189 + SyncColumns.SERVER_TIMESTAMP + " integer, " 190 + messageColumns; 191 192 // The three tables have the same schema 193 db.execSQL("create table " + Message.TABLE_NAME + createString); 194 db.execSQL("create table " + Message.UPDATED_TABLE_NAME + altCreateString); 195 db.execSQL("create table " + Message.DELETED_TABLE_NAME + altCreateString); 196 197 String indexColumns[] = { 198 MessageColumns.TIMESTAMP, 199 MessageColumns.FLAG_READ, 200 MessageColumns.FLAG_LOADED, 201 MessageColumns.MAILBOX_KEY, 202 SyncColumns.SERVER_ID 203 }; 204 205 for (String columnName : indexColumns) { 206 db.execSQL(createIndex(Message.TABLE_NAME, columnName)); 207 } 208 209 // Deleting a Message deletes all associated Attachments 210 // Deleting the associated Body cannot be done in a trigger, because the Body is stored 211 // in a separate database, and trigger cannot operate on attached databases. 212 db.execSQL("create trigger message_delete before delete on " + Message.TABLE_NAME + 213 " begin delete from " + Attachment.TABLE_NAME + 214 " where " + AttachmentColumns.MESSAGE_KEY + "=old." + EmailContent.RECORD_ID + 215 "; end"); 216 217 // Add triggers to keep unread count accurate per mailbox 218 219 // NOTE: SQLite's before triggers are not safe when recursive triggers are involved. 220 // Use caution when changing them. 221 222 // Insert a message; if flagRead is zero, add to the unread count of the message's mailbox 223 db.execSQL("create trigger unread_message_insert before insert on " + Message.TABLE_NAME + 224 " when NEW." + MessageColumns.FLAG_READ + "=0" + 225 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 226 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 227 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 228 "; end"); 229 230 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 231 db.execSQL("create trigger unread_message_delete before delete on " + Message.TABLE_NAME + 232 " when OLD." + MessageColumns.FLAG_READ + "=0" + 233 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 234 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 235 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 236 "; end"); 237 238 // Change a message's mailbox 239 db.execSQL("create trigger unread_message_move before update of " + 240 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 241 " when OLD." + MessageColumns.FLAG_READ + "=0" + 242 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 243 '=' + MailboxColumns.UNREAD_COUNT + "-1" + 244 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 245 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 246 '=' + MailboxColumns.UNREAD_COUNT + "+1" + 247 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 248 "; end"); 249 250 // Change a message's read state 251 db.execSQL("create trigger unread_message_read before update of " + 252 MessageColumns.FLAG_READ + " on " + Message.TABLE_NAME + 253 " when OLD." + MessageColumns.FLAG_READ + "!=NEW." + MessageColumns.FLAG_READ + 254 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.UNREAD_COUNT + 255 '=' + MailboxColumns.UNREAD_COUNT + "+ case OLD." + MessageColumns.FLAG_READ + 256 " when 0 then -1 else 1 end" + 257 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 258 "; end"); 259 260 // Add triggers to update message count per mailbox 261 262 // Insert a message. 263 db.execSQL("create trigger message_count_message_insert after insert on " + 264 Message.TABLE_NAME + 265 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 266 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 267 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 268 "; end"); 269 270 // Delete a message; if flagRead is zero, decrement the unread count of the msg's mailbox 271 db.execSQL("create trigger message_count_message_delete after delete on " + 272 Message.TABLE_NAME + 273 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 274 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 275 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 276 "; end"); 277 278 // Change a message's mailbox 279 db.execSQL("create trigger message_count_message_move after update of " + 280 MessageColumns.MAILBOX_KEY + " on " + Message.TABLE_NAME + 281 " begin update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 282 '=' + MailboxColumns.MESSAGE_COUNT + "-1" + 283 " where " + EmailContent.RECORD_ID + "=OLD." + MessageColumns.MAILBOX_KEY + 284 "; update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 285 '=' + MailboxColumns.MESSAGE_COUNT + "+1" + 286 " where " + EmailContent.RECORD_ID + "=NEW." + MessageColumns.MAILBOX_KEY + 287 "; end"); 288 } 289 290 static void resetMessageTable(SQLiteDatabase db, int oldVersion, int newVersion) { 291 try { 292 db.execSQL("drop table " + Message.TABLE_NAME); 293 db.execSQL("drop table " + Message.UPDATED_TABLE_NAME); 294 db.execSQL("drop table " + Message.DELETED_TABLE_NAME); 295 } catch (SQLException e) { 296 } 297 createMessageTable(db); 298 } 299 300 @SuppressWarnings("deprecation") 301 static void createAccountTable(SQLiteDatabase db) { 302 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 303 + AccountColumns.DISPLAY_NAME + " text, " 304 + AccountColumns.EMAIL_ADDRESS + " text, " 305 + AccountColumns.SYNC_KEY + " text, " 306 + AccountColumns.SYNC_LOOKBACK + " integer, " 307 + AccountColumns.SYNC_INTERVAL + " text, " 308 + AccountColumns.HOST_AUTH_KEY_RECV + " integer, " 309 + AccountColumns.HOST_AUTH_KEY_SEND + " integer, " 310 + AccountColumns.FLAGS + " integer, " 311 + AccountColumns.IS_DEFAULT + " integer, " 312 + AccountColumns.COMPATIBILITY_UUID + " text, " 313 + AccountColumns.SENDER_NAME + " text, " 314 + AccountColumns.RINGTONE_URI + " text, " 315 + AccountColumns.PROTOCOL_VERSION + " text, " 316 + AccountColumns.NEW_MESSAGE_COUNT + " integer, " 317 + AccountColumns.SECURITY_FLAGS + " integer, " 318 + AccountColumns.SECURITY_SYNC_KEY + " text, " 319 + AccountColumns.SIGNATURE + " text, " 320 + AccountColumns.POLICY_KEY + " integer, " 321 + AccountColumns.NOTIFIED_MESSAGE_ID + " integer, " 322 + AccountColumns.NOTIFIED_MESSAGE_COUNT + " integer" 323 + ");"; 324 db.execSQL("create table " + Account.TABLE_NAME + s); 325 // Deleting an account deletes associated Mailboxes and HostAuth's 326 db.execSQL(TRIGGER_ACCOUNT_DELETE); 327 } 328 329 static void resetAccountTable(SQLiteDatabase db, int oldVersion, int newVersion) { 330 try { 331 db.execSQL("drop table " + Account.TABLE_NAME); 332 } catch (SQLException e) { 333 } 334 createAccountTable(db); 335 } 336 337 static void createPolicyTable(SQLiteDatabase db) { 338 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 339 + PolicyColumns.PASSWORD_MODE + " integer, " 340 + PolicyColumns.PASSWORD_MIN_LENGTH + " integer, " 341 + PolicyColumns.PASSWORD_EXPIRATION_DAYS + " integer, " 342 + PolicyColumns.PASSWORD_HISTORY + " integer, " 343 + PolicyColumns.PASSWORD_COMPLEX_CHARS + " integer, " 344 + PolicyColumns.PASSWORD_MAX_FAILS + " integer, " 345 + PolicyColumns.MAX_SCREEN_LOCK_TIME + " integer, " 346 + PolicyColumns.REQUIRE_REMOTE_WIPE + " integer, " 347 + PolicyColumns.REQUIRE_ENCRYPTION + " integer, " 348 + PolicyColumns.REQUIRE_ENCRYPTION_EXTERNAL + " integer, " 349 + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + " integer, " 350 + PolicyColumns.DONT_ALLOW_CAMERA + " integer, " 351 + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer, " 352 + PolicyColumns.DONT_ALLOW_HTML + " integer, " 353 + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer, " 354 + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + " integer, " 355 + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + " integer, " 356 + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer, " 357 + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer, " 358 + PolicyColumns.PASSWORD_RECOVERY_ENABLED + " integer, " 359 + PolicyColumns.PROTOCOL_POLICIES_ENFORCED + " text, " 360 + PolicyColumns.PROTOCOL_POLICIES_UNSUPPORTED + " text" 361 + ");"; 362 db.execSQL("create table " + Policy.TABLE_NAME + s); 363 } 364 365 static void createHostAuthTable(SQLiteDatabase db) { 366 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 367 + HostAuthColumns.PROTOCOL + " text, " 368 + HostAuthColumns.ADDRESS + " text, " 369 + HostAuthColumns.PORT + " integer, " 370 + HostAuthColumns.FLAGS + " integer, " 371 + HostAuthColumns.LOGIN + " text, " 372 + HostAuthColumns.PASSWORD + " text, " 373 + HostAuthColumns.DOMAIN + " text, " 374 + HostAuthColumns.ACCOUNT_KEY + " integer," 375 + HostAuthColumns.CLIENT_CERT_ALIAS + " text" 376 + ");"; 377 db.execSQL("create table " + HostAuth.TABLE_NAME + s); 378 } 379 380 static void resetHostAuthTable(SQLiteDatabase db, int oldVersion, int newVersion) { 381 try { 382 db.execSQL("drop table " + HostAuth.TABLE_NAME); 383 } catch (SQLException e) { 384 } 385 createHostAuthTable(db); 386 } 387 388 static void createMailboxTable(SQLiteDatabase db) { 389 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 390 + MailboxColumns.DISPLAY_NAME + " text, " 391 + MailboxColumns.SERVER_ID + " text, " 392 + MailboxColumns.PARENT_SERVER_ID + " text, " 393 + MailboxColumns.PARENT_KEY + " integer, " 394 + MailboxColumns.ACCOUNT_KEY + " integer, " 395 + MailboxColumns.TYPE + " integer, " 396 + MailboxColumns.DELIMITER + " integer, " 397 + MailboxColumns.SYNC_KEY + " text, " 398 + MailboxColumns.SYNC_LOOKBACK + " integer, " 399 + MailboxColumns.SYNC_INTERVAL + " integer, " 400 + MailboxColumns.SYNC_TIME + " integer, " 401 + MailboxColumns.UNREAD_COUNT + " integer, " 402 + MailboxColumns.FLAG_VISIBLE + " integer, " 403 + MailboxColumns.FLAGS + " integer, " 404 + MailboxColumns.VISIBLE_LIMIT + " integer, " 405 + MailboxColumns.SYNC_STATUS + " text, " 406 + MailboxColumns.MESSAGE_COUNT + " integer not null default 0, " 407 + MailboxColumns.LAST_TOUCHED_TIME + " integer default 0, " 408 + MailboxColumns.UI_SYNC_STATUS + " integer default 0, " 409 + MailboxColumns.UI_LAST_SYNC_RESULT + " integer default 0, " 410 + MailboxColumns.LAST_NOTIFIED_MESSAGE_KEY + " integer not null default 0, " 411 + MailboxColumns.LAST_NOTIFIED_MESSAGE_COUNT + " integer not null default 0, " 412 + MailboxColumns.TOTAL_COUNT + " integer, " 413 + MailboxColumns.LAST_SEEN_MESSAGE_KEY + " integer" 414 + ");"; 415 db.execSQL("create table " + Mailbox.TABLE_NAME + s); 416 db.execSQL("create index mailbox_" + MailboxColumns.SERVER_ID 417 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.SERVER_ID + ")"); 418 db.execSQL("create index mailbox_" + MailboxColumns.ACCOUNT_KEY 419 + " on " + Mailbox.TABLE_NAME + " (" + MailboxColumns.ACCOUNT_KEY + ")"); 420 // Deleting a Mailbox deletes associated Messages in all three tables 421 db.execSQL(TRIGGER_MAILBOX_DELETE); 422 } 423 424 static void resetMailboxTable(SQLiteDatabase db, int oldVersion, int newVersion) { 425 try { 426 db.execSQL("drop table " + Mailbox.TABLE_NAME); 427 } catch (SQLException e) { 428 } 429 createMailboxTable(db); 430 } 431 432 static void createAttachmentTable(SQLiteDatabase db) { 433 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 434 + AttachmentColumns.FILENAME + " text, " 435 + AttachmentColumns.MIME_TYPE + " text, " 436 + AttachmentColumns.SIZE + " integer, " 437 + AttachmentColumns.CONTENT_ID + " text, " 438 + AttachmentColumns.CONTENT_URI + " text, " 439 + AttachmentColumns.MESSAGE_KEY + " integer, " 440 + AttachmentColumns.LOCATION + " text, " 441 + AttachmentColumns.ENCODING + " text, " 442 + AttachmentColumns.CONTENT + " text, " 443 + AttachmentColumns.FLAGS + " integer, " 444 + AttachmentColumns.CONTENT_BYTES + " blob, " 445 + AttachmentColumns.ACCOUNT_KEY + " integer, " 446 + AttachmentColumns.UI_STATE + " integer, " 447 + AttachmentColumns.UI_DESTINATION + " integer, " 448 + AttachmentColumns.UI_DOWNLOADED_SIZE + " integer" 449 + ");"; 450 db.execSQL("create table " + Attachment.TABLE_NAME + s); 451 db.execSQL(createIndex(Attachment.TABLE_NAME, AttachmentColumns.MESSAGE_KEY)); 452 } 453 454 static void resetAttachmentTable(SQLiteDatabase db, int oldVersion, int newVersion) { 455 try { 456 db.execSQL("drop table " + Attachment.TABLE_NAME); 457 } catch (SQLException e) { 458 } 459 createAttachmentTable(db); 460 } 461 462 static void createQuickResponseTable(SQLiteDatabase db) { 463 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 464 + QuickResponseColumns.TEXT + " text, " 465 + QuickResponseColumns.ACCOUNT_KEY + " integer" 466 + ");"; 467 db.execSQL("create table " + QuickResponse.TABLE_NAME + s); 468 } 469 470 static void createBodyTable(SQLiteDatabase db) { 471 String s = " (" + EmailContent.RECORD_ID + " integer primary key autoincrement, " 472 + BodyColumns.MESSAGE_KEY + " integer, " 473 + BodyColumns.HTML_CONTENT + " text, " 474 + BodyColumns.TEXT_CONTENT + " text, " 475 + BodyColumns.HTML_REPLY + " text, " 476 + BodyColumns.TEXT_REPLY + " text, " 477 + BodyColumns.SOURCE_MESSAGE_KEY + " text, " 478 + BodyColumns.INTRO_TEXT + " text, " 479 + BodyColumns.QUOTED_TEXT_START_POS + " integer" 480 + ");"; 481 db.execSQL("create table " + Body.TABLE_NAME + s); 482 db.execSQL(createIndex(Body.TABLE_NAME, BodyColumns.MESSAGE_KEY)); 483 } 484 485 static void upgradeBodyTable(SQLiteDatabase db, int oldVersion, int newVersion) { 486 if (oldVersion < 5) { 487 try { 488 db.execSQL("drop table " + Body.TABLE_NAME); 489 createBodyTable(db); 490 oldVersion = 5; 491 } catch (SQLException e) { 492 } 493 } 494 if (oldVersion == 5) { 495 try { 496 db.execSQL("alter table " + Body.TABLE_NAME 497 + " add " + BodyColumns.INTRO_TEXT + " text"); 498 } catch (SQLException e) { 499 // Shouldn't be needed unless we're debugging and interrupt the process 500 Log.w(TAG, "Exception upgrading EmailProviderBody.db from v5 to v6", e); 501 } 502 oldVersion = 6; 503 } 504 if (oldVersion == 6 || oldVersion ==7) { 505 try { 506 db.execSQL("alter table " + Body.TABLE_NAME 507 + " add " + BodyColumns.QUOTED_TEXT_START_POS + " integer"); 508 } catch (SQLException e) { 509 // Shouldn't be needed unless we're debugging and interrupt the process 510 Log.w(TAG, "Exception upgrading EmailProviderBody.db from v6 to v8", e); 511 } 512 oldVersion = 8; 513 } 514 } 515 516 protected static class BodyDatabaseHelper extends SQLiteOpenHelper { 517 BodyDatabaseHelper(Context context, String name) { 518 super(context, name, null, BODY_DATABASE_VERSION); 519 } 520 521 @Override 522 public void onCreate(SQLiteDatabase db) { 523 Log.d(TAG, "Creating EmailProviderBody database"); 524 createBodyTable(db); 525 } 526 527 @Override 528 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 529 upgradeBodyTable(db, oldVersion, newVersion); 530 } 531 532 @Override 533 public void onOpen(SQLiteDatabase db) { 534 try { 535 // Cleanup some nasty records 536 db.execSQL("delete from " + Account.TABLE_NAME 537 + " WHERE " + AccountColumns.DISPLAY_NAME + " ISNULL;"); 538 db.execSQL("delete from " + HostAuth.TABLE_NAME 539 + " WHERE " + HostAuthColumns.PROTOCOL + " ISNULL;"); 540 } catch (SQLException e) { 541 // Shouldn't be needed unless we're debugging and interrupt the process 542 Log.w(TAG, "Exception cleaning EmailProvider.db" + e); 543 } 544 } 545 } 546 547 /** Counts the number of messages in each mailbox, and updates the message count column. */ 548 @VisibleForTesting 549 static void recalculateMessageCount(SQLiteDatabase db) { 550 db.execSQL("update " + Mailbox.TABLE_NAME + " set " + MailboxColumns.MESSAGE_COUNT + 551 "= (select count(*) from " + Message.TABLE_NAME + 552 " where " + Message.MAILBOX_KEY + " = " + 553 Mailbox.TABLE_NAME + "." + EmailContent.RECORD_ID + ")"); 554 } 555 556 protected static class DatabaseHelper extends SQLiteOpenHelper { 557 Context mContext; 558 559 DatabaseHelper(Context context, String name) { 560 super(context, name, null, DATABASE_VERSION); 561 mContext = context; 562 } 563 564 @Override 565 public void onCreate(SQLiteDatabase db) { 566 Log.d(TAG, "Creating EmailProvider database"); 567 // Create all tables here; each class has its own method 568 createMessageTable(db); 569 createAttachmentTable(db); 570 createMailboxTable(db); 571 createHostAuthTable(db); 572 createAccountTable(db); 573 createPolicyTable(db); 574 createQuickResponseTable(db); 575 } 576 577 @Override 578 @SuppressWarnings("deprecation") 579 public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 580 // For versions prior to 5, delete all data 581 // Versions >= 5 require that data be preserved! 582 if (oldVersion < 5) { 583 android.accounts.Account[] accounts = AccountManager.get(mContext) 584 .getAccountsByType(AccountManagerTypes.TYPE_EXCHANGE); 585 for (android.accounts.Account account: accounts) { 586 AccountManager.get(mContext).removeAccount(account, null, null); 587 } 588 resetMessageTable(db, oldVersion, newVersion); 589 resetAttachmentTable(db, oldVersion, newVersion); 590 resetMailboxTable(db, oldVersion, newVersion); 591 resetHostAuthTable(db, oldVersion, newVersion); 592 resetAccountTable(db, oldVersion, newVersion); 593 return; 594 } 595 if (oldVersion == 5) { 596 // Message Tables: Add SyncColumns.SERVER_TIMESTAMP 597 try { 598 db.execSQL("alter table " + Message.TABLE_NAME 599 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 600 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 601 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 602 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 603 + " add column " + SyncColumns.SERVER_TIMESTAMP + " integer" + ";"); 604 } catch (SQLException e) { 605 // Shouldn't be needed unless we're debugging and interrupt the process 606 Log.w(TAG, "Exception upgrading EmailProvider.db from v5 to v6", e); 607 } 608 oldVersion = 6; 609 } 610 if (oldVersion == 6) { 611 // Use the newer mailbox_delete trigger 612 db.execSQL("drop trigger mailbox_delete;"); 613 db.execSQL(TRIGGER_MAILBOX_DELETE); 614 oldVersion = 7; 615 } 616 if (oldVersion == 7) { 617 // add the security (provisioning) column 618 try { 619 db.execSQL("alter table " + Account.TABLE_NAME 620 + " add column " + AccountColumns.SECURITY_FLAGS + " integer" + ";"); 621 } catch (SQLException e) { 622 // Shouldn't be needed unless we're debugging and interrupt the process 623 Log.w(TAG, "Exception upgrading EmailProvider.db from 7 to 8 " + e); 624 } 625 oldVersion = 8; 626 } 627 if (oldVersion == 8) { 628 // accounts: add security sync key & user signature columns 629 try { 630 db.execSQL("alter table " + Account.TABLE_NAME 631 + " add column " + AccountColumns.SECURITY_SYNC_KEY + " text" + ";"); 632 db.execSQL("alter table " + Account.TABLE_NAME 633 + " add column " + AccountColumns.SIGNATURE + " text" + ";"); 634 } catch (SQLException e) { 635 // Shouldn't be needed unless we're debugging and interrupt the process 636 Log.w(TAG, "Exception upgrading EmailProvider.db from 8 to 9 " + e); 637 } 638 oldVersion = 9; 639 } 640 if (oldVersion == 9) { 641 // Message: add meeting info column into Message tables 642 try { 643 db.execSQL("alter table " + Message.TABLE_NAME 644 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 645 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 646 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 647 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 648 + " add column " + MessageColumns.MEETING_INFO + " text" + ";"); 649 } catch (SQLException e) { 650 // Shouldn't be needed unless we're debugging and interrupt the process 651 Log.w(TAG, "Exception upgrading EmailProvider.db from 9 to 10 " + e); 652 } 653 oldVersion = 10; 654 } 655 if (oldVersion == 10) { 656 // Attachment: add content and flags columns 657 try { 658 db.execSQL("alter table " + Attachment.TABLE_NAME 659 + " add column " + AttachmentColumns.CONTENT + " text" + ";"); 660 db.execSQL("alter table " + Attachment.TABLE_NAME 661 + " add column " + AttachmentColumns.FLAGS + " integer" + ";"); 662 } catch (SQLException e) { 663 // Shouldn't be needed unless we're debugging and interrupt the process 664 Log.w(TAG, "Exception upgrading EmailProvider.db from 10 to 11 " + e); 665 } 666 oldVersion = 11; 667 } 668 if (oldVersion == 11) { 669 // Attachment: add content_bytes 670 try { 671 db.execSQL("alter table " + Attachment.TABLE_NAME 672 + " add column " + AttachmentColumns.CONTENT_BYTES + " blob" + ";"); 673 } catch (SQLException e) { 674 // Shouldn't be needed unless we're debugging and interrupt the process 675 Log.w(TAG, "Exception upgrading EmailProvider.db from 11 to 12 " + e); 676 } 677 oldVersion = 12; 678 } 679 if (oldVersion == 12) { 680 try { 681 db.execSQL("alter table " + Mailbox.TABLE_NAME 682 + " add column " + Mailbox.MESSAGE_COUNT 683 +" integer not null default 0" + ";"); 684 recalculateMessageCount(db); 685 } catch (SQLException e) { 686 // Shouldn't be needed unless we're debugging and interrupt the process 687 Log.w(TAG, "Exception upgrading EmailProvider.db from 12 to 13 " + e); 688 } 689 oldVersion = 13; 690 } 691 if (oldVersion == 13) { 692 try { 693 db.execSQL("alter table " + Message.TABLE_NAME 694 + " add column " + Message.SNIPPET 695 +" text" + ";"); 696 } catch (SQLException e) { 697 // Shouldn't be needed unless we're debugging and interrupt the process 698 Log.w(TAG, "Exception upgrading EmailProvider.db from 13 to 14 " + e); 699 } 700 oldVersion = 14; 701 } 702 if (oldVersion == 14) { 703 try { 704 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 705 + " add column " + Message.SNIPPET +" text" + ";"); 706 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 707 + " add column " + Message.SNIPPET +" text" + ";"); 708 } catch (SQLException e) { 709 // Shouldn't be needed unless we're debugging and interrupt the process 710 Log.w(TAG, "Exception upgrading EmailProvider.db from 14 to 15 " + e); 711 } 712 oldVersion = 15; 713 } 714 if (oldVersion == 15) { 715 try { 716 db.execSQL("alter table " + Attachment.TABLE_NAME 717 + " add column " + Attachment.ACCOUNT_KEY +" integer" + ";"); 718 // Update all existing attachments to add the accountKey data 719 db.execSQL("update " + Attachment.TABLE_NAME + " set " + 720 Attachment.ACCOUNT_KEY + "= (SELECT " + Message.TABLE_NAME + "." + 721 Message.ACCOUNT_KEY + " from " + Message.TABLE_NAME + " where " + 722 Message.TABLE_NAME + "." + Message.RECORD_ID + " = " + 723 Attachment.TABLE_NAME + "." + Attachment.MESSAGE_KEY + ")"); 724 } catch (SQLException e) { 725 // Shouldn't be needed unless we're debugging and interrupt the process 726 Log.w(TAG, "Exception upgrading EmailProvider.db from 15 to 16 " + e); 727 } 728 oldVersion = 16; 729 } 730 if (oldVersion == 16) { 731 try { 732 db.execSQL("alter table " + Mailbox.TABLE_NAME 733 + " add column " + Mailbox.PARENT_KEY + " integer;"); 734 } catch (SQLException e) { 735 // Shouldn't be needed unless we're debugging and interrupt the process 736 Log.w(TAG, "Exception upgrading EmailProvider.db from 16 to 17 " + e); 737 } 738 oldVersion = 17; 739 } 740 if (oldVersion == 17) { 741 upgradeFromVersion17ToVersion18(db); 742 oldVersion = 18; 743 } 744 if (oldVersion == 18) { 745 try { 746 db.execSQL("alter table " + Account.TABLE_NAME 747 + " add column " + Account.POLICY_KEY + " integer;"); 748 db.execSQL("drop trigger account_delete;"); 749 db.execSQL(TRIGGER_ACCOUNT_DELETE); 750 createPolicyTable(db); 751 convertPolicyFlagsToPolicyTable(db); 752 } catch (SQLException e) { 753 // Shouldn't be needed unless we're debugging and interrupt the process 754 Log.w(TAG, "Exception upgrading EmailProvider.db from 18 to 19 " + e); 755 } 756 oldVersion = 19; 757 } 758 if (oldVersion == 19) { 759 try { 760 db.execSQL("alter table " + Policy.TABLE_NAME 761 + " add column " + PolicyColumns.REQUIRE_MANUAL_SYNC_WHEN_ROAMING + 762 " integer;"); 763 db.execSQL("alter table " + Policy.TABLE_NAME 764 + " add column " + PolicyColumns.DONT_ALLOW_CAMERA + " integer;"); 765 db.execSQL("alter table " + Policy.TABLE_NAME 766 + " add column " + PolicyColumns.DONT_ALLOW_ATTACHMENTS + " integer;"); 767 db.execSQL("alter table " + Policy.TABLE_NAME 768 + " add column " + PolicyColumns.DONT_ALLOW_HTML + " integer;"); 769 db.execSQL("alter table " + Policy.TABLE_NAME 770 + " add column " + PolicyColumns.MAX_ATTACHMENT_SIZE + " integer;"); 771 db.execSQL("alter table " + Policy.TABLE_NAME 772 + " add column " + PolicyColumns.MAX_TEXT_TRUNCATION_SIZE + 773 " integer;"); 774 db.execSQL("alter table " + Policy.TABLE_NAME 775 + " add column " + PolicyColumns.MAX_HTML_TRUNCATION_SIZE + 776 " integer;"); 777 db.execSQL("alter table " + Policy.TABLE_NAME 778 + " add column " + PolicyColumns.MAX_EMAIL_LOOKBACK + " integer;"); 779 db.execSQL("alter table " + Policy.TABLE_NAME 780 + " add column " + PolicyColumns.MAX_CALENDAR_LOOKBACK + " integer;"); 781 db.execSQL("alter table " + Policy.TABLE_NAME 782 + " add column " + PolicyColumns.PASSWORD_RECOVERY_ENABLED + 783 " integer;"); 784 } catch (SQLException e) { 785 // Shouldn't be needed unless we're debugging and interrupt the process 786 Log.w(TAG, "Exception upgrading EmailProvider.db from 19 to 20 " + e); 787 } 788 oldVersion = 20; 789 } 790 if (oldVersion == 20) { 791 try { 792 db.execSQL("alter table " + Mailbox.TABLE_NAME 793 + " add column " + Mailbox.LAST_SEEN_MESSAGE_KEY + " integer;"); 794 } catch (SQLException e) { 795 // Shouldn't be needed unless we're debugging and interrupt the process 796 Log.w(TAG, "Exception upgrading EmailProvider.db from 20 to 21 " + e); 797 } 798 oldVersion = 21; 799 } 800 if (oldVersion == 21) { 801 upgradeFromVersion21ToVersion22(db, mContext); 802 oldVersion = 22; 803 } 804 if (oldVersion == 22) { 805 upgradeFromVersion22ToVersion23(db); 806 oldVersion = 23; 807 } 808 if (oldVersion == 23) { 809 upgradeFromVersion23ToVersion24(db); 810 oldVersion = 24; 811 } 812 if (oldVersion == 24) { 813 upgradeFromVersion24ToVersion25(db); 814 oldVersion = 25; 815 } 816 if (oldVersion == 25) { 817 upgradeFromVersion25ToVersion26(db); 818 oldVersion = 26; 819 } 820 if (oldVersion == 26) { 821 try { 822 db.execSQL("alter table " + Message.TABLE_NAME 823 + " add column " + Message.PROTOCOL_SEARCH_INFO + " text;"); 824 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 825 + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";"); 826 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 827 + " add column " + Message.PROTOCOL_SEARCH_INFO +" text" + ";"); 828 } catch (SQLException e) { 829 // Shouldn't be needed unless we're debugging and interrupt the process 830 Log.w(TAG, "Exception upgrading EmailProvider.db from 26 to 27 " + e); 831 } 832 oldVersion = 27; 833 } 834 if (oldVersion == 27) { 835 try { 836 db.execSQL("alter table " + Account.TABLE_NAME 837 + " add column " + Account.NOTIFIED_MESSAGE_ID + " integer;"); 838 db.execSQL("alter table " + Account.TABLE_NAME 839 + " add column " + Account.NOTIFIED_MESSAGE_COUNT + " integer;"); 840 } catch (SQLException e) { 841 // Shouldn't be needed unless we're debugging and interrupt the process 842 Log.w(TAG, "Exception upgrading EmailProvider.db from 27 to 28 " + e); 843 } 844 oldVersion = 28; 845 } 846 if (oldVersion == 28) { 847 try { 848 db.execSQL("alter table " + Policy.TABLE_NAME 849 + " add column " + Policy.PROTOCOL_POLICIES_ENFORCED + " text;"); 850 db.execSQL("alter table " + Policy.TABLE_NAME 851 + " add column " + Policy.PROTOCOL_POLICIES_UNSUPPORTED + " text;"); 852 } catch (SQLException e) { 853 // Shouldn't be needed unless we're debugging and interrupt the process 854 Log.w(TAG, "Exception upgrading EmailProvider.db from 28 to 29 " + e); 855 } 856 oldVersion = 29; 857 } 858 if (oldVersion == 29) { 859 upgradeFromVersion29ToVersion30(db); 860 oldVersion = 30; 861 } 862 if (oldVersion == 30) { 863 try { 864 db.execSQL("alter table " + Mailbox.TABLE_NAME 865 + " add column " + Mailbox.UI_SYNC_STATUS + " integer;"); 866 db.execSQL("alter table " + Mailbox.TABLE_NAME 867 + " add column " + Mailbox.UI_LAST_SYNC_RESULT + " integer;"); 868 } catch (SQLException e) { 869 // Shouldn't be needed unless we're debugging and interrupt the process 870 Log.w(TAG, "Exception upgrading EmailProvider.db from 30 to 31 " + e); 871 } 872 oldVersion = 31; 873 } 874 if (oldVersion == 31) { 875 try { 876 db.execSQL("alter table " + Mailbox.TABLE_NAME 877 + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " integer;"); 878 db.execSQL("alter table " + Mailbox.TABLE_NAME 879 + " add column " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " integer;"); 880 db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + 881 "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_KEY + " IS NULL"); 882 db.execSQL("update Mailbox set " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + 883 "=0 where " + Mailbox.LAST_NOTIFIED_MESSAGE_COUNT + " IS NULL"); 884 } catch (SQLException e) { 885 // Shouldn't be needed unless we're debugging and interrupt the process 886 Log.w(TAG, "Exception upgrading EmailProvider.db from 31 to 32 " + e); 887 } 888 oldVersion = 32; 889 } 890 if (oldVersion == 32) { 891 try { 892 db.execSQL("alter table " + Attachment.TABLE_NAME 893 + " add column " + Attachment.UI_STATE + " integer;"); 894 db.execSQL("alter table " + Attachment.TABLE_NAME 895 + " add column " + Attachment.UI_DESTINATION + " integer;"); 896 db.execSQL("alter table " + Attachment.TABLE_NAME 897 + " add column " + Attachment.UI_DOWNLOADED_SIZE + " integer;"); 898 } catch (SQLException e) { 899 // Shouldn't be needed unless we're debugging and interrupt the process 900 Log.w(TAG, "Exception upgrading EmailProvider.db from 32 to 33 " + e); 901 } 902 oldVersion = 33; 903 } 904 if (oldVersion == 33) { 905 try { 906 db.execSQL("alter table " + Mailbox.TABLE_NAME 907 + " add column " + MailboxColumns.TOTAL_COUNT + " integer;"); 908 } catch (SQLException e) { 909 // Shouldn't be needed unless we're debugging and interrupt the process 910 Log.w(TAG, "Exception upgrading EmailProvider.db from 33 to 34 " + e); 911 } 912 oldVersion = 34; 913 } 914 if (oldVersion == 34) { 915 oldVersion = 35; 916 } 917 if (oldVersion == 35 || oldVersion == 36) { 918 oldVersion = 37; 919 } 920 if (oldVersion == 37) { 921 try { 922 db.execSQL("alter table " + Message.TABLE_NAME 923 + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 924 } catch (SQLException e) { 925 // Shouldn't be needed unless we're debugging and interrupt the process 926 Log.w(TAG, "Exception upgrading EmailProvider.db from 37 to 38 " + e); 927 } 928 oldVersion = 38; 929 } 930 if (oldVersion == 38) { 931 try { 932 db.execSQL("alter table " + Message.DELETED_TABLE_NAME 933 + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 934 db.execSQL("alter table " + Message.UPDATED_TABLE_NAME 935 + " add column " + MessageColumns.THREAD_TOPIC + " text;"); 936 } catch (SQLException e) { 937 // Shouldn't be needed unless we're debugging and interrupt the process 938 Log.w(TAG, "Exception upgrading EmailProvider.db from 38 to 39 " + e); 939 } 940 oldVersion = 39; 941 } 942 } 943 944 @Override 945 public void onOpen(SQLiteDatabase db) { 946 } 947 } 948 949 @VisibleForTesting 950 @SuppressWarnings("deprecation") 951 static void convertPolicyFlagsToPolicyTable(SQLiteDatabase db) { 952 Cursor c = db.query(Account.TABLE_NAME, 953 new String[] {EmailContent.RECORD_ID /*0*/, AccountColumns.SECURITY_FLAGS /*1*/}, 954 AccountColumns.SECURITY_FLAGS + ">0", null, null, null, null); 955 ContentValues cv = new ContentValues(); 956 String[] args = new String[1]; 957 while (c.moveToNext()) { 958 long securityFlags = c.getLong(1 /*SECURITY_FLAGS*/); 959 Policy policy = LegacyPolicySet.flagsToPolicy(securityFlags); 960 long policyId = db.insert(Policy.TABLE_NAME, null, policy.toContentValues()); 961 cv.put(AccountColumns.POLICY_KEY, policyId); 962 cv.putNull(AccountColumns.SECURITY_FLAGS); 963 args[0] = Long.toString(c.getLong(0 /*RECORD_ID*/)); 964 db.update(Account.TABLE_NAME, cv, EmailContent.RECORD_ID + "=?", args); 965 } 966 } 967 968 /** Upgrades the database from v17 to v18 */ 969 @VisibleForTesting 970 static void upgradeFromVersion17ToVersion18(SQLiteDatabase db) { 971 // Copy the displayName column to the serverId column. In v18 of the database, 972 // we use the serverId for IMAP/POP3 mailboxes instead of overloading the 973 // display name. 974 // 975 // For posterity; this is the command we're executing: 976 //sqlite> UPDATE mailbox SET serverid=displayname WHERE mailbox._id in ( 977 // ...> SELECT mailbox._id FROM mailbox,account,hostauth WHERE 978 // ...> (mailbox.parentkey isnull OR mailbox.parentkey=0) AND 979 // ...> mailbox.accountkey=account._id AND 980 // ...> account.hostauthkeyrecv=hostauth._id AND 981 // ...> (hostauth.protocol='imap' OR hostauth.protocol='pop3')); 982 try { 983 db.execSQL( 984 "UPDATE " + Mailbox.TABLE_NAME + " SET " 985 + MailboxColumns.SERVER_ID + "=" + MailboxColumns.DISPLAY_NAME 986 + " WHERE " 987 + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " IN ( SELECT " 988 + Mailbox.TABLE_NAME + "." + MailboxColumns.ID + " FROM " 989 + Mailbox.TABLE_NAME + "," + Account.TABLE_NAME + "," 990 + HostAuth.TABLE_NAME + " WHERE " 991 + "(" 992 + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + " isnull OR " 993 + Mailbox.TABLE_NAME + "." + MailboxColumns.PARENT_KEY + "=0 " 994 + ") AND " 995 + Mailbox.TABLE_NAME + "." + MailboxColumns.ACCOUNT_KEY + "=" 996 + Account.TABLE_NAME + "." + AccountColumns.ID + " AND " 997 + Account.TABLE_NAME + "." + AccountColumns.HOST_AUTH_KEY_RECV + "=" 998 + HostAuth.TABLE_NAME + "." + HostAuthColumns.ID + " AND ( " 999 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='imap' OR " 1000 + HostAuth.TABLE_NAME + "." + HostAuthColumns.PROTOCOL + "='pop3' ) )"); 1001 } catch (SQLException e) { 1002 // Shouldn't be needed unless we're debugging and interrupt the process 1003 Log.w(TAG, "Exception upgrading EmailProvider.db from 17 to 18 " + e); 1004 } 1005 ContentCache.invalidateAllCaches(); 1006 } 1007 1008 /** 1009 * Upgrade the database from v21 to v22 1010 * This entails creating AccountManager accounts for all pop3 and imap accounts 1011 */ 1012 1013 private static final String[] V21_ACCOUNT_PROJECTION = 1014 new String[] {AccountColumns.HOST_AUTH_KEY_RECV, AccountColumns.EMAIL_ADDRESS}; 1015 private static final int V21_ACCOUNT_RECV = 0; 1016 private static final int V21_ACCOUNT_EMAIL = 1; 1017 1018 private static final String[] V21_HOSTAUTH_PROJECTION = 1019 new String[] {HostAuthColumns.PROTOCOL, HostAuthColumns.PASSWORD}; 1020 private static final int V21_HOSTAUTH_PROTOCOL = 0; 1021 private static final int V21_HOSTAUTH_PASSWORD = 1; 1022 1023 static private void createAccountManagerAccount(Context context, String login, 1024 String password) { 1025 AccountManager accountManager = AccountManager.get(context); 1026 android.accounts.Account amAccount = 1027 new android.accounts.Account(login, AccountManagerTypes.TYPE_POP_IMAP); 1028 accountManager.addAccountExplicitly(amAccount, password, null); 1029 ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 1030 ContentResolver.setSyncAutomatically(amAccount, EmailContent.AUTHORITY, true); 1031 ContentResolver.setIsSyncable(amAccount, ContactsContract.AUTHORITY, 0); 1032 ContentResolver.setIsSyncable(amAccount, CalendarProviderStub.AUTHORITY, 0); 1033 } 1034 1035 @VisibleForTesting 1036 static void upgradeFromVersion21ToVersion22(SQLiteDatabase db, Context accountManagerContext) { 1037 try { 1038 // Loop through accounts, looking for pop/imap accounts 1039 Cursor accountCursor = db.query(Account.TABLE_NAME, V21_ACCOUNT_PROJECTION, null, 1040 null, null, null, null); 1041 try { 1042 String[] hostAuthArgs = new String[1]; 1043 while (accountCursor.moveToNext()) { 1044 hostAuthArgs[0] = accountCursor.getString(V21_ACCOUNT_RECV); 1045 // Get the "receive" HostAuth for this account 1046 Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 1047 V21_HOSTAUTH_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs, 1048 null, null, null); 1049 try { 1050 if (hostAuthCursor.moveToFirst()) { 1051 String protocol = hostAuthCursor.getString(V21_HOSTAUTH_PROTOCOL); 1052 // If this is a pop3 or imap account, create the account manager account 1053 if (HostAuth.SCHEME_IMAP.equals(protocol) || 1054 HostAuth.SCHEME_POP3.equals(protocol)) { 1055 if (Email.DEBUG) { 1056 Log.d(TAG, "Create AccountManager account for " + protocol + 1057 "account: " + 1058 accountCursor.getString(V21_ACCOUNT_EMAIL)); 1059 } 1060 createAccountManagerAccount(accountManagerContext, 1061 accountCursor.getString(V21_ACCOUNT_EMAIL), 1062 hostAuthCursor.getString(V21_HOSTAUTH_PASSWORD)); 1063 // If an EAS account, make Email sync automatically (equivalent of 1064 // checking the "Sync Email" box in settings 1065 } else if (HostAuth.SCHEME_EAS.equals(protocol)) { 1066 android.accounts.Account amAccount = 1067 new android.accounts.Account( 1068 accountCursor.getString(V21_ACCOUNT_EMAIL), 1069 AccountManagerTypes.TYPE_EXCHANGE); 1070 ContentResolver.setIsSyncable(amAccount, EmailContent.AUTHORITY, 1); 1071 ContentResolver.setSyncAutomatically(amAccount, 1072 EmailContent.AUTHORITY, true); 1073 1074 } 1075 } 1076 } finally { 1077 hostAuthCursor.close(); 1078 } 1079 } 1080 } finally { 1081 accountCursor.close(); 1082 } 1083 } catch (SQLException e) { 1084 // Shouldn't be needed unless we're debugging and interrupt the process 1085 Log.w(TAG, "Exception upgrading EmailProvider.db from 20 to 21 " + e); 1086 } 1087 } 1088 1089 /** Upgrades the database from v22 to v23 */ 1090 private static void upgradeFromVersion22ToVersion23(SQLiteDatabase db) { 1091 try { 1092 db.execSQL("alter table " + Mailbox.TABLE_NAME 1093 + " add column " + Mailbox.LAST_TOUCHED_TIME + " integer default 0;"); 1094 } catch (SQLException e) { 1095 // Shouldn't be needed unless we're debugging and interrupt the process 1096 Log.w(TAG, "Exception upgrading EmailProvider.db from 22 to 23 " + e); 1097 } 1098 } 1099 1100 /** Adds in a column for information about a client certificate to use. */ 1101 private static void upgradeFromVersion23ToVersion24(SQLiteDatabase db) { 1102 try { 1103 db.execSQL("alter table " + HostAuth.TABLE_NAME 1104 + " add column " + HostAuth.CLIENT_CERT_ALIAS + " text;"); 1105 } catch (SQLException e) { 1106 // Shouldn't be needed unless we're debugging and interrupt the process 1107 Log.w(TAG, "Exception upgrading EmailProvider.db from 23 to 24 " + e); 1108 } 1109 } 1110 1111 /** Upgrades the database from v24 to v25 by creating table for quick responses */ 1112 private static void upgradeFromVersion24ToVersion25(SQLiteDatabase db) { 1113 try { 1114 createQuickResponseTable(db); 1115 } catch (SQLException e) { 1116 // Shouldn't be needed unless we're debugging and interrupt the process 1117 Log.w(TAG, "Exception upgrading EmailProvider.db from 24 to 25 " + e); 1118 } 1119 } 1120 1121 private static final String[] V25_ACCOUNT_PROJECTION = 1122 new String[] {AccountColumns.ID, AccountColumns.FLAGS, AccountColumns.HOST_AUTH_KEY_RECV}; 1123 private static final int V25_ACCOUNT_ID = 0; 1124 private static final int V25_ACCOUNT_FLAGS = 1; 1125 private static final int V25_ACCOUNT_RECV = 2; 1126 1127 private static final String[] V25_HOSTAUTH_PROJECTION = new String[] {HostAuthColumns.PROTOCOL}; 1128 private static final int V25_HOSTAUTH_PROTOCOL = 0; 1129 1130 /** Upgrades the database from v25 to v26 by adding FLAG_SUPPORTS_SEARCH to IMAP accounts */ 1131 private static void upgradeFromVersion25ToVersion26(SQLiteDatabase db) { 1132 try { 1133 // Loop through accounts, looking for imap accounts 1134 Cursor accountCursor = db.query(Account.TABLE_NAME, V25_ACCOUNT_PROJECTION, null, 1135 null, null, null, null); 1136 ContentValues cv = new ContentValues(); 1137 try { 1138 String[] hostAuthArgs = new String[1]; 1139 while (accountCursor.moveToNext()) { 1140 hostAuthArgs[0] = accountCursor.getString(V25_ACCOUNT_RECV); 1141 // Get the "receive" HostAuth for this account 1142 Cursor hostAuthCursor = db.query(HostAuth.TABLE_NAME, 1143 V25_HOSTAUTH_PROJECTION, HostAuth.RECORD_ID + "=?", hostAuthArgs, 1144 null, null, null); 1145 try { 1146 if (hostAuthCursor.moveToFirst()) { 1147 String protocol = hostAuthCursor.getString(V25_HOSTAUTH_PROTOCOL); 1148 // If this is an imap account, add the search flag 1149 if (HostAuth.SCHEME_IMAP.equals(protocol)) { 1150 String id = accountCursor.getString(V25_ACCOUNT_ID); 1151 int flags = accountCursor.getInt(V25_ACCOUNT_FLAGS); 1152 cv.put(AccountColumns.FLAGS, flags | Account.FLAGS_SUPPORTS_SEARCH); 1153 db.update(Account.TABLE_NAME, cv, Account.RECORD_ID + "=?", 1154 new String[] {id}); 1155 } 1156 } 1157 } finally { 1158 hostAuthCursor.close(); 1159 } 1160 } 1161 } finally { 1162 accountCursor.close(); 1163 } 1164 } catch (SQLException e) { 1165 // Shouldn't be needed unless we're debugging and interrupt the process 1166 Log.w(TAG, "Exception upgrading EmailProvider.db from 25 to 26 " + e); 1167 } 1168 } 1169 1170 /** Upgrades the database from v29 to v30 by updating all address fields in Message */ 1171 private static final int[] ADDRESS_COLUMN_INDICES = new int[] { 1172 Message.CONTENT_BCC_LIST_COLUMN, Message.CONTENT_CC_LIST_COLUMN, 1173 Message.CONTENT_FROM_LIST_COLUMN, Message.CONTENT_REPLY_TO_COLUMN, 1174 Message.CONTENT_TO_LIST_COLUMN 1175 }; 1176 private static final String[] ADDRESS_COLUMN_NAMES = new String[] { 1177 Message.BCC_LIST, Message.CC_LIST, Message.FROM_LIST, Message.REPLY_TO_LIST, Message.TO_LIST 1178 }; 1179 1180 private static void upgradeFromVersion29ToVersion30(SQLiteDatabase db) { 1181 try { 1182 // Loop through all messages, updating address columns to new format (CSV, RFC822) 1183 Cursor messageCursor = db.query(Message.TABLE_NAME, Message.CONTENT_PROJECTION, null, 1184 null, null, null, null); 1185 ContentValues cv = new ContentValues(); 1186 String[] whereArgs = new String[1]; 1187 try { 1188 while (messageCursor.moveToNext()) { 1189 for (int i = 0; i < ADDRESS_COLUMN_INDICES.length; i++) { 1190 Address[] addrs = 1191 Address.unpack(messageCursor.getString(ADDRESS_COLUMN_INDICES[i])); 1192 cv.put(ADDRESS_COLUMN_NAMES[i], Address.pack(addrs)); 1193 } 1194 whereArgs[0] = messageCursor.getString(Message.CONTENT_ID_COLUMN); 1195 db.update(Message.TABLE_NAME, cv, WHERE_ID, whereArgs); 1196 } 1197 } finally { 1198 messageCursor.close(); 1199 } 1200 } catch (SQLException e) { 1201 // Shouldn't be needed unless we're debugging and interrupt the process 1202 Log.w(TAG, "Exception upgrading EmailProvider.db from 29 to 30 " + e); 1203 } 1204 } 1205 1206 } 1207
