1/* 2 * Copyright (C) 2008 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.providers.telephony; 18 19import android.content.BroadcastReceiver; 20import android.content.ContentValues; 21import android.content.Context; 22import android.content.Intent; 23import android.content.IntentFilter; 24import android.database.Cursor; 25import android.database.sqlite.SQLiteDatabase; 26import android.database.sqlite.SQLiteException; 27import android.database.sqlite.SQLiteOpenHelper; 28import android.os.storage.StorageManager; 29import android.provider.BaseColumns; 30import android.provider.Telephony; 31import android.provider.Telephony.Mms; 32import android.provider.Telephony.Mms.Addr; 33import android.provider.Telephony.Mms.Part; 34import android.provider.Telephony.Mms.Rate; 35import android.provider.Telephony.MmsSms; 36import android.provider.Telephony.MmsSms.PendingMessages; 37import android.provider.Telephony.Sms; 38import android.provider.Telephony.Threads; 39import android.telephony.SubscriptionManager; 40import android.util.Log; 41 42import com.android.internal.annotations.VisibleForTesting; 43import com.google.android.mms.pdu.EncodedStringValue; 44import com.google.android.mms.pdu.PduHeaders; 45 46import java.io.File; 47import java.io.FileInputStream; 48import java.io.IOException; 49import java.io.InputStream; 50import java.util.ArrayList; 51import java.util.HashSet; 52import java.util.Iterator; 53 54/** 55 * A {@link SQLiteOpenHelper} that handles DB management of SMS and MMS tables. 56 * 57 * From N, SMS and MMS tables are split into two groups with different levels of encryption. 58 * - the raw table, which lives inside DE(Device Encrypted) storage. 59 * - all other tables, which lives under CE(Credential Encrypted) storage. 60 * 61 * All tables are created by this class in the same database that can live either in DE or CE 62 * storage. But not all tables in the same database should be used. Only DE tables should be used 63 * in the database created in DE and only CE tables should be used in the database created in CE. 64 * The only exception is a non-FBE device migrating from M to N, in which case the DE and CE tables 65 * will actually live inside the same storage/database. 66 * 67 * This class provides methods to create instances that manage databases in different storage. 68 * It's the responsibility of the clients of this class to make sure the right instance is 69 * used to access tables that are supposed to live inside the intended storage. 70 */ 71public class MmsSmsDatabaseHelper extends SQLiteOpenHelper { 72 private static final String TAG = "MmsSmsDatabaseHelper"; 73 74 private static final String SMS_UPDATE_THREAD_READ_BODY = 75 " UPDATE threads SET read = " + 76 " CASE (SELECT COUNT(*)" + 77 " FROM sms" + 78 " WHERE " + Sms.READ + " = 0" + 79 " AND " + Sms.THREAD_ID + " = threads._id)" + 80 " WHEN 0 THEN 1" + 81 " ELSE 0" + 82 " END" + 83 " WHERE threads._id = new." + Sms.THREAD_ID + "; "; 84 85 private static final String UPDATE_THREAD_COUNT_ON_NEW = 86 " UPDATE threads SET message_count = " + 87 " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " + 88 " ON threads._id = " + Sms.THREAD_ID + 89 " WHERE " + Sms.THREAD_ID + " = new.thread_id" + 90 " AND sms." + Sms.TYPE + " != 3) + " + 91 " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " + 92 " ON threads._id = " + Mms.THREAD_ID + 93 " WHERE " + Mms.THREAD_ID + " = new.thread_id" + 94 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 95 " AND " + Mms.MESSAGE_BOX + " != 3) " + 96 " WHERE threads._id = new.thread_id; "; 97 98 private static final String UPDATE_THREAD_COUNT_ON_OLD = 99 " UPDATE threads SET message_count = " + 100 " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " + 101 " ON threads._id = " + Sms.THREAD_ID + 102 " WHERE " + Sms.THREAD_ID + " = old.thread_id" + 103 " AND sms." + Sms.TYPE + " != 3) + " + 104 " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " + 105 " ON threads._id = " + Mms.THREAD_ID + 106 " WHERE " + Mms.THREAD_ID + " = old.thread_id" + 107 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 108 " AND " + Mms.MESSAGE_BOX + " != 3) " + 109 " WHERE threads._id = old.thread_id; "; 110 111 private static final String SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE = 112 "BEGIN" + 113 " UPDATE threads SET" + 114 " date = (strftime('%s','now') * 1000), " + 115 " snippet = new." + Sms.BODY + ", " + 116 " snippet_cs = 0" + 117 " WHERE threads._id = new." + Sms.THREAD_ID + "; " + 118 UPDATE_THREAD_COUNT_ON_NEW + 119 SMS_UPDATE_THREAD_READ_BODY + 120 "END;"; 121 122 private static final String PDU_UPDATE_THREAD_CONSTRAINTS = 123 " WHEN new." + Mms.MESSAGE_TYPE + "=" + 124 PduHeaders.MESSAGE_TYPE_RETRIEVE_CONF + 125 " OR new." + Mms.MESSAGE_TYPE + "=" + 126 PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + 127 " OR new." + Mms.MESSAGE_TYPE + "=" + 128 PduHeaders.MESSAGE_TYPE_SEND_REQ + " "; 129 130 // When looking in the pdu table for unread messages, only count messages that 131 // are displayed to the user. The constants are defined in PduHeaders and could be used 132 // here, but the string "(m_type=132 OR m_type=130 OR m_type=128)" is used throughout this 133 // file and so it is used here to be consistent. 134 // m_type=128 = MESSAGE_TYPE_SEND_REQ 135 // m_type=130 = MESSAGE_TYPE_NOTIFICATION_IND 136 // m_type=132 = MESSAGE_TYPE_RETRIEVE_CONF 137 private static final String PDU_UPDATE_THREAD_READ_BODY = 138 " UPDATE threads SET read = " + 139 " CASE (SELECT COUNT(*)" + 140 " FROM " + MmsProvider.TABLE_PDU + 141 " WHERE " + Mms.READ + " = 0" + 142 " AND " + Mms.THREAD_ID + " = threads._id " + 143 " AND (m_type=132 OR m_type=130 OR m_type=128)) " + 144 " WHEN 0 THEN 1" + 145 " ELSE 0" + 146 " END" + 147 " WHERE threads._id = new." + Mms.THREAD_ID + "; "; 148 149 private static final String PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE = 150 "BEGIN" + 151 " UPDATE threads SET" + 152 " date = (strftime('%s','now') * 1000), " + 153 " snippet = new." + Mms.SUBJECT + ", " + 154 " snippet_cs = new." + Mms.SUBJECT_CHARSET + 155 " WHERE threads._id = new." + Mms.THREAD_ID + "; " + 156 UPDATE_THREAD_COUNT_ON_NEW + 157 PDU_UPDATE_THREAD_READ_BODY + 158 "END;"; 159 160 private static final String UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE = 161 " UPDATE threads SET snippet = " + 162 " (SELECT snippet FROM" + 163 " (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" + 164 " UNION SELECT date, body AS snippet, thread_id FROM sms)" + 165 " WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " + 166 " WHERE threads._id = OLD.thread_id; " + 167 " UPDATE threads SET snippet_cs = " + 168 " (SELECT snippet_cs FROM" + 169 " (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" + 170 " UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" + 171 " WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " + 172 " WHERE threads._id = OLD.thread_id; "; 173 174 175 // When a part is inserted, if it is not text/plain or application/smil 176 // (which both can exist with text-only MMSes), then there is an attachment. 177 // Set has_attachment=1 in the threads table for the thread in question. 178 private static final String PART_UPDATE_THREADS_ON_INSERT_TRIGGER = 179 "CREATE TRIGGER update_threads_on_insert_part " + 180 " AFTER INSERT ON part " + 181 " WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " + 182 " BEGIN " + 183 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 184 " (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " + 185 " WHERE part._id=new._id LIMIT 1); " + 186 " END"; 187 188 // When the 'mid' column in the part table is updated, we need to run the trigger to update 189 // the threads table's has_attachment column, if the part is an attachment. 190 private static final String PART_UPDATE_THREADS_ON_UPDATE_TRIGGER = 191 "CREATE TRIGGER update_threads_on_update_part " + 192 " AFTER UPDATE of " + Part.MSG_ID + " ON part " + 193 " WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " + 194 " BEGIN " + 195 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 196 " (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " + 197 " WHERE part._id=new._id LIMIT 1); " + 198 " END"; 199 200 201 // When a part is deleted (with the same non-text/SMIL constraint as when 202 // we set has_attachment), update the threads table for all threads. 203 // Unfortunately we cannot update only the thread that the part was 204 // attached to, as it is possible that the part has been orphaned and 205 // the message it was attached to is already gone. 206 private static final String PART_UPDATE_THREADS_ON_DELETE_TRIGGER = 207 "CREATE TRIGGER update_threads_on_delete_part " + 208 " AFTER DELETE ON part " + 209 " WHEN old.ct != 'text/plain' AND old.ct != 'application/smil' " + 210 " BEGIN " + 211 " UPDATE threads SET has_attachment = " + 212 " CASE " + 213 " (SELECT COUNT(*) FROM part JOIN pdu " + 214 " WHERE pdu.thread_id = threads._id " + 215 " AND part.ct != 'text/plain' AND part.ct != 'application/smil' " + 216 " AND part.mid = pdu._id)" + 217 " WHEN 0 THEN 0 " + 218 " ELSE 1 " + 219 " END; " + 220 " END"; 221 222 // When the 'thread_id' column in the pdu table is updated, we need to run the trigger to update 223 // the threads table's has_attachment column, if the message has an attachment in 'part' table 224 private static final String PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER = 225 "CREATE TRIGGER update_threads_on_update_pdu " + 226 " AFTER UPDATE of thread_id ON pdu " + 227 " BEGIN " + 228 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 229 " (SELECT pdu.thread_id FROM part JOIN pdu " + 230 " WHERE part.ct != 'text/plain' AND part.ct != 'application/smil' " + 231 " AND part.mid = pdu._id);" + 232 " END"; 233 234 private static MmsSmsDatabaseHelper sDeInstance = null; 235 private static MmsSmsDatabaseHelper sCeInstance = null; 236 237 private static final String[] BIND_ARGS_NONE = new String[0]; 238 239 private static boolean sTriedAutoIncrement = false; 240 private static boolean sFakeLowStorageTest = false; // for testing only 241 242 static final String DATABASE_NAME = "mmssms.db"; 243 static final int DATABASE_VERSION = 67; 244 private static final int IDLE_CONNECTION_TIMEOUT_MS = 30000; 245 246 private final Context mContext; 247 private LowStorageMonitor mLowStorageMonitor; 248 249 250 private MmsSmsDatabaseHelper(Context context) { 251 super(context, DATABASE_NAME, null, DATABASE_VERSION); 252 mContext = context; 253 // Memory optimization - close idle connections after 30s of inactivity 254 setIdleConnectionTimeout(IDLE_CONNECTION_TIMEOUT_MS); 255 } 256 257 /** 258 * Returns a singleton helper for the combined MMS and SMS database in device encrypted storage. 259 */ 260 /* package */ static synchronized MmsSmsDatabaseHelper getInstanceForDe(Context context) { 261 if (sDeInstance == null) { 262 sDeInstance = new MmsSmsDatabaseHelper(ProviderUtil.getDeviceEncryptedContext(context)); 263 } 264 return sDeInstance; 265 } 266 267 /** 268 * Returns a singleton helper for the combined MMS and SMS database in credential encrypted 269 * storage. If FBE is not available, use the device encrypted storage instead. 270 */ 271 /* package */ static synchronized MmsSmsDatabaseHelper getInstanceForCe(Context context) { 272 if (sCeInstance == null) { 273 if (StorageManager.isFileEncryptedNativeOrEmulated()) { 274 sCeInstance = new MmsSmsDatabaseHelper( 275 ProviderUtil.getCredentialEncryptedContext(context)); 276 } else { 277 sCeInstance = getInstanceForDe(context); 278 } 279 } 280 return sCeInstance; 281 } 282 283 /** 284 * Look through all the recipientIds referenced by the threads and then delete any 285 * unreferenced rows from the canonical_addresses table. 286 */ 287 private static void removeUnferencedCanonicalAddresses(SQLiteDatabase db) { 288 Cursor c = db.query(MmsSmsProvider.TABLE_THREADS, new String[] { "recipient_ids" }, 289 null, null, null, null, null); 290 if (c != null) { 291 try { 292 if (c.getCount() == 0) { 293 // no threads, delete all addresses 294 int rows = db.delete("canonical_addresses", null, null); 295 } else { 296 // Find all the referenced recipient_ids from the threads. recipientIds is 297 // a space-separated list of recipient ids: "1 14 21" 298 HashSet<Integer> recipientIds = new HashSet<Integer>(); 299 while (c.moveToNext()) { 300 String[] recips = c.getString(0).split(" "); 301 for (String recip : recips) { 302 try { 303 int recipientId = Integer.parseInt(recip); 304 recipientIds.add(recipientId); 305 } catch (Exception e) { 306 } 307 } 308 } 309 // Now build a selection string of all the unique recipient ids 310 StringBuilder sb = new StringBuilder(); 311 Iterator<Integer> iter = recipientIds.iterator(); 312 sb.append("_id NOT IN ("); 313 while (iter.hasNext()) { 314 sb.append(iter.next()); 315 if (iter.hasNext()) { 316 sb.append(","); 317 } 318 } 319 sb.append(")"); 320 int rows = db.delete("canonical_addresses", sb.toString(), null); 321 } 322 } finally { 323 c.close(); 324 } 325 } 326 } 327 328 public static void updateThread(SQLiteDatabase db, long thread_id) { 329 if (thread_id < 0) { 330 updateThreads(db, null, null); 331 return; 332 } 333 updateThreads(db, "(thread_id = ?)", new String[]{ String.valueOf(thread_id) }); 334 } 335 336 /** 337 * Update all threads containing SMS matching the 'where' condition. Note that the condition 338 * is applied to individual messages in the sms table, NOT the threads table. 339 */ 340 public static void updateThreads(SQLiteDatabase db, String where, String[] whereArgs) { 341 if (where == null) { 342 where = "1"; 343 } 344 if (whereArgs == null) { 345 whereArgs = BIND_ARGS_NONE; 346 } 347 db.beginTransaction(); 348 try { 349 // Delete rows in the threads table if 350 // there are no more messages attached to it in either 351 // the sms or pdu tables. 352 // Note that we do this regardless of whether they match 'where'. 353 int rows = db.delete(MmsSmsProvider.TABLE_THREADS, 354 "_id NOT IN (" + 355 " SELECT DISTINCT thread_id FROM sms WHERE thread_id IS NOT NULL" + 356 " UNION" + 357 " SELECT DISTINCT thread_id FROM pdu WHERE thread_id IS NOT NULL)", 358 null); 359 if (rows > 0) { 360 // If this deleted a row, let's remove orphaned canonical_addresses 361 removeUnferencedCanonicalAddresses(db); 362 } 363 364 // Update the message count in the threads table as the sum 365 // of all messages in both the sms and pdu tables. 366 db.execSQL( 367 " UPDATE threads" + 368 " SET message_count = (" + 369 " SELECT COUNT(sms._id) FROM sms" + 370 " WHERE " + Sms.THREAD_ID + " = threads._id" + 371 " AND sms." + Sms.TYPE + " != 3" + 372 " ) + (" + 373 " SELECT COUNT(pdu._id) FROM pdu" + 374 " WHERE " + Mms.THREAD_ID + " = threads._id" + 375 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 376 " AND " + Mms.MESSAGE_BOX + " != 3" + 377 " )" + 378 " WHERE EXISTS (" + 379 " SELECT _id" + 380 " FROM sms" + 381 " WHERE thread_id = threads._id" + 382 " AND (" + where + ")" + 383 " LIMIT 1" + 384 " );", 385 whereArgs); 386 387 // Update the date and the snippet (and its character set) in 388 // the threads table to be that of the most recent message in 389 // the thread. 390 db.execSQL( 391 " WITH matches AS (" + 392 " SELECT date * 1000 AS date, sub AS snippet, sub_cs AS snippet_cs, thread_id" + 393 " FROM pdu" + 394 " WHERE thread_id = threads._id" + 395 " UNION" + 396 " SELECT date, body AS snippet, 0 AS snippet_cs, thread_id" + 397 " FROM sms" + 398 " WHERE thread_id = threads._id" + 399 " ORDER BY date DESC" + 400 " LIMIT 1" + 401 " )" + 402 " UPDATE threads" + 403 " SET date = (SELECT date FROM matches)," + 404 " snippet = (SELECT snippet FROM matches)," + 405 " snippet_cs = (SELECT snippet_cs FROM matches)" + 406 " WHERE EXISTS (" + 407 " SELECT _id" + 408 " FROM sms" + 409 " WHERE thread_id = threads._id" + 410 " AND (" + where + ")" + 411 " LIMIT 1" + 412 " );", 413 whereArgs); 414 415 // Update the error column of the thread to indicate if there 416 // are any messages in it that have failed to send. 417 // First check to see if there are any messages with errors in this thread. 418 db.execSQL( 419 " UPDATE threads" + 420 " SET error = EXISTS (" + 421 " SELECT type" + 422 " FROM sms" + 423 " WHERE type=" + Telephony.TextBasedSmsColumns.MESSAGE_TYPE_FAILED + 424 " AND thread_id = threads._id" + 425 " )" + 426 " WHERE EXISTS (" + 427 " SELECT _id" + 428 " FROM sms" + 429 " WHERE thread_id = threads._id" + 430 " AND (" + where + ")" + 431 " LIMIT 1" + 432 " );", 433 whereArgs); 434 435 db.setTransactionSuccessful(); 436 } catch (Throwable ex) { 437 Log.e(TAG, ex.getMessage(), ex); 438 } finally { 439 db.endTransaction(); 440 } 441 } 442 443 public static int deleteOneSms(SQLiteDatabase db, int message_id) { 444 int thread_id = -1; 445 // Find the thread ID that the specified SMS belongs to. 446 Cursor c = db.query("sms", new String[] { "thread_id" }, 447 "_id=" + message_id, null, null, null, null); 448 if (c != null) { 449 if (c.moveToFirst()) { 450 thread_id = c.getInt(0); 451 } 452 c.close(); 453 } 454 455 // Delete the specified message. 456 int rows = db.delete("sms", "_id=" + message_id, null); 457 if (thread_id > 0) { 458 // Update its thread. 459 updateThread(db, thread_id); 460 } 461 return rows; 462 } 463 464 @Override 465 public void onCreate(SQLiteDatabase db) { 466 createMmsTables(db); 467 createSmsTables(db); 468 createCommonTables(db); 469 createCommonTriggers(db); 470 createMmsTriggers(db); 471 createWordsTables(db); 472 createIndices(db); 473 } 474 475 // When upgrading the database we need to populate the words 476 // table with the rows out of sms and part. 477 private void populateWordsTable(SQLiteDatabase db) { 478 final String TABLE_WORDS = "words"; 479 { 480 Cursor smsRows = db.query( 481 "sms", 482 new String[] { Sms._ID, Sms.BODY }, 483 null, 484 null, 485 null, 486 null, 487 null); 488 try { 489 if (smsRows != null) { 490 smsRows.moveToPosition(-1); 491 ContentValues cv = new ContentValues(); 492 while (smsRows.moveToNext()) { 493 cv.clear(); 494 495 long id = smsRows.getLong(0); // 0 for Sms._ID 496 String body = smsRows.getString(1); // 1 for Sms.BODY 497 498 cv.put(Telephony.MmsSms.WordsTable.ID, id); 499 cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body); 500 cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id); 501 cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1); 502 db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv); 503 } 504 } 505 } finally { 506 if (smsRows != null) { 507 smsRows.close(); 508 } 509 } 510 } 511 512 { 513 Cursor mmsRows = db.query( 514 "part", 515 new String[] { Part._ID, Part.TEXT }, 516 "ct = 'text/plain'", 517 null, 518 null, 519 null, 520 null); 521 try { 522 if (mmsRows != null) { 523 mmsRows.moveToPosition(-1); 524 ContentValues cv = new ContentValues(); 525 while (mmsRows.moveToNext()) { 526 cv.clear(); 527 528 long id = mmsRows.getLong(0); // 0 for Part._ID 529 String body = mmsRows.getString(1); // 1 for Part.TEXT 530 531 cv.put(Telephony.MmsSms.WordsTable.ID, id); 532 cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body); 533 cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id); 534 cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1); 535 db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv); 536 } 537 } 538 } finally { 539 if (mmsRows != null) { 540 mmsRows.close(); 541 } 542 } 543 } 544 } 545 546 private void createWordsTables(SQLiteDatabase db) { 547 try { 548 db.execSQL("CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER, table_to_use INTEGER);"); 549 550 // monitor the sms table 551 // NOTE don't handle inserts using a trigger because it has an unwanted 552 // side effect: the value returned for the last row ends up being the 553 // id of one of the trigger insert not the original row insert. 554 // Handle inserts manually in the provider. 555 db.execSQL("CREATE TRIGGER sms_words_update AFTER UPDATE ON sms BEGIN UPDATE words " + 556 " SET index_text = NEW.body WHERE (source_id=NEW._id AND table_to_use=1); " + 557 " END;"); 558 db.execSQL("CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM " + 559 " words WHERE source_id = OLD._id AND table_to_use = 1; END;"); 560 561 populateWordsTable(db); 562 } catch (Exception ex) { 563 Log.e(TAG, "got exception creating words table: " + ex.toString()); 564 } 565 } 566 567 private void createIndices(SQLiteDatabase db) { 568 createThreadIdIndex(db); 569 createThreadIdDateIndex(db); 570 createPartMidIndex(db); 571 createAddrMsgIdIndex(db); 572 } 573 574 private void createThreadIdIndex(SQLiteDatabase db) { 575 try { 576 db.execSQL("CREATE INDEX IF NOT EXISTS typeThreadIdIndex ON sms" + 577 " (type, thread_id);"); 578 } catch (Exception ex) { 579 Log.e(TAG, "got exception creating indices: " + ex.toString()); 580 } 581 } 582 583 private void createThreadIdDateIndex(SQLiteDatabase db) { 584 try { 585 db.execSQL("CREATE INDEX IF NOT EXISTS threadIdDateIndex ON sms" + 586 " (thread_id, date);"); 587 } catch (Exception ex) { 588 Log.e(TAG, "got exception creating indices: " + ex.toString()); 589 } 590 } 591 592 private void createPartMidIndex(SQLiteDatabase db) { 593 try { 594 db.execSQL("CREATE INDEX IF NOT EXISTS partMidIndex ON part (mid)"); 595 } catch (Exception ex) { 596 Log.e(TAG, "got exception creating indices: " + ex.toString()); 597 } 598 } 599 600 private void createAddrMsgIdIndex(SQLiteDatabase db) { 601 try { 602 db.execSQL("CREATE INDEX IF NOT EXISTS addrMsgIdIndex ON addr (msg_id)"); 603 } catch (Exception ex) { 604 Log.e(TAG, "got exception creating indices: " + ex.toString()); 605 } 606 } 607 608 private void createMmsTables(SQLiteDatabase db) { 609 // N.B.: Whenever the columns here are changed, the columns in 610 // {@ref MmsSmsProvider} must be changed to match. 611 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PDU + " (" + 612 Mms._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 613 Mms.THREAD_ID + " INTEGER," + 614 Mms.DATE + " INTEGER," + 615 Mms.DATE_SENT + " INTEGER DEFAULT 0," + 616 Mms.MESSAGE_BOX + " INTEGER," + 617 Mms.READ + " INTEGER DEFAULT 0," + 618 Mms.MESSAGE_ID + " TEXT," + 619 Mms.SUBJECT + " TEXT," + 620 Mms.SUBJECT_CHARSET + " INTEGER," + 621 Mms.CONTENT_TYPE + " TEXT," + 622 Mms.CONTENT_LOCATION + " TEXT," + 623 Mms.EXPIRY + " INTEGER," + 624 Mms.MESSAGE_CLASS + " TEXT," + 625 Mms.MESSAGE_TYPE + " INTEGER," + 626 Mms.MMS_VERSION + " INTEGER," + 627 Mms.MESSAGE_SIZE + " INTEGER," + 628 Mms.PRIORITY + " INTEGER," + 629 Mms.READ_REPORT + " INTEGER," + 630 Mms.REPORT_ALLOWED + " INTEGER," + 631 Mms.RESPONSE_STATUS + " INTEGER," + 632 Mms.STATUS + " INTEGER," + 633 Mms.TRANSACTION_ID + " TEXT," + 634 Mms.RETRIEVE_STATUS + " INTEGER," + 635 Mms.RETRIEVE_TEXT + " TEXT," + 636 Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," + 637 Mms.READ_STATUS + " INTEGER," + 638 Mms.CONTENT_CLASS + " INTEGER," + 639 Mms.RESPONSE_TEXT + " TEXT," + 640 Mms.DELIVERY_TIME + " INTEGER," + 641 Mms.DELIVERY_REPORT + " INTEGER," + 642 Mms.LOCKED + " INTEGER DEFAULT 0," + 643 Mms.SUBSCRIPTION_ID + " INTEGER DEFAULT " 644 + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 645 Mms.SEEN + " INTEGER DEFAULT 0," + 646 Mms.CREATOR + " TEXT," + 647 Mms.TEXT_ONLY + " INTEGER DEFAULT 0" + 648 ");"); 649 650 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_ADDR + " (" + 651 Addr._ID + " INTEGER PRIMARY KEY," + 652 Addr.MSG_ID + " INTEGER," + 653 Addr.CONTACT_ID + " INTEGER," + 654 Addr.ADDRESS + " TEXT," + 655 Addr.TYPE + " INTEGER," + 656 Addr.CHARSET + " INTEGER);"); 657 658 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PART + " (" + 659 Part._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 660 Part.MSG_ID + " INTEGER," + 661 Part.SEQ + " INTEGER DEFAULT 0," + 662 Part.CONTENT_TYPE + " TEXT," + 663 Part.NAME + " TEXT," + 664 Part.CHARSET + " INTEGER," + 665 Part.CONTENT_DISPOSITION + " TEXT," + 666 Part.FILENAME + " TEXT," + 667 Part.CONTENT_ID + " TEXT," + 668 Part.CONTENT_LOCATION + " TEXT," + 669 Part.CT_START + " INTEGER," + 670 Part.CT_TYPE + " TEXT," + 671 Part._DATA + " TEXT," + 672 Part.TEXT + " TEXT);"); 673 674 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_RATE + " (" + 675 Rate.SENT_TIME + " INTEGER);"); 676 677 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_DRM + " (" + 678 BaseColumns._ID + " INTEGER PRIMARY KEY," + 679 "_data TEXT);"); 680 681 // Restricted view of pdu table, only sent/received messages without wap pushes 682 db.execSQL("CREATE VIEW " + MmsProvider.VIEW_PDU_RESTRICTED + " AS " + 683 "SELECT * FROM " + MmsProvider.TABLE_PDU + " WHERE " + 684 "(" + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_INBOX + 685 " OR " + 686 Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_SENT + ")" + 687 " AND " + 688 "(" + Mms.MESSAGE_TYPE + "!=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + ");"); 689 } 690 691 // Unlike the other trigger-creating functions, this function can be called multiple times 692 // without harm. 693 private void createMmsTriggers(SQLiteDatabase db) { 694 // Cleans up parts when a MM is deleted. 695 db.execSQL("DROP TRIGGER IF EXISTS part_cleanup"); 696 db.execSQL("CREATE TRIGGER part_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 697 "BEGIN " + 698 " DELETE FROM " + MmsProvider.TABLE_PART + 699 " WHERE " + Part.MSG_ID + "=old._id;" + 700 "END;"); 701 702 // Cleans up address info when a MM is deleted. 703 db.execSQL("DROP TRIGGER IF EXISTS addr_cleanup"); 704 db.execSQL("CREATE TRIGGER addr_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 705 "BEGIN " + 706 " DELETE FROM " + MmsProvider.TABLE_ADDR + 707 " WHERE " + Addr.MSG_ID + "=old._id;" + 708 "END;"); 709 710 // Delete obsolete delivery-report, read-report while deleting their 711 // associated Send.req. 712 db.execSQL("DROP TRIGGER IF EXISTS cleanup_delivery_and_read_report"); 713 db.execSQL("CREATE TRIGGER cleanup_delivery_and_read_report " + 714 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 715 "WHEN old." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + " " + 716 "BEGIN " + 717 " DELETE FROM " + MmsProvider.TABLE_PDU + 718 " WHERE (" + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_DELIVERY_IND + 719 " OR " + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_ORIG_IND + 720 ")" + 721 " AND " + Mms.MESSAGE_ID + "=old." + Mms.MESSAGE_ID + "; " + 722 "END;"); 723 724 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_insert_part"); 725 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 726 727 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_update_part"); 728 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 729 730 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_delete_part"); 731 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 732 733 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_update_pdu"); 734 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 735 736 // Delete pending status for a message when it is deleted. 737 db.execSQL("DROP TRIGGER IF EXISTS delete_mms_pending_on_delete"); 738 db.execSQL("CREATE TRIGGER delete_mms_pending_on_delete " + 739 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 740 "BEGIN " + 741 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 742 " WHERE " + PendingMessages.MSG_ID + "=old._id; " + 743 "END;"); 744 745 // When a message is moved out of Outbox, delete its pending status. 746 db.execSQL("DROP TRIGGER IF EXISTS delete_mms_pending_on_update"); 747 db.execSQL("CREATE TRIGGER delete_mms_pending_on_update " + 748 "AFTER UPDATE ON " + MmsProvider.TABLE_PDU + " " + 749 "WHEN old." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 750 " AND new." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 751 "BEGIN " + 752 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 753 " WHERE " + PendingMessages.MSG_ID + "=new._id; " + 754 "END;"); 755 756 // Insert pending status for M-Notification.ind or M-ReadRec.ind 757 // when they are inserted into Inbox/Outbox. 758 db.execSQL("DROP TRIGGER IF EXISTS insert_mms_pending_on_insert"); 759 db.execSQL("CREATE TRIGGER insert_mms_pending_on_insert " + 760 "AFTER INSERT ON pdu " + 761 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + 762 " OR new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_REC_IND + 763 " " + 764 "BEGIN " + 765 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 766 " (" + PendingMessages.PROTO_TYPE + "," + 767 " " + PendingMessages.MSG_ID + "," + 768 " " + PendingMessages.MSG_TYPE + "," + 769 " " + PendingMessages.ERROR_TYPE + "," + 770 " " + PendingMessages.ERROR_CODE + "," + 771 " " + PendingMessages.RETRY_INDEX + "," + 772 " " + PendingMessages.DUE_TIME + ") " + 773 " VALUES " + 774 " (" + MmsSms.MMS_PROTO + "," + 775 " new." + BaseColumns._ID + "," + 776 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 777 "END;"); 778 779 780 // Insert pending status for M-Send.req when it is moved into Outbox. 781 db.execSQL("DROP TRIGGER IF EXISTS insert_mms_pending_on_update"); 782 db.execSQL("CREATE TRIGGER insert_mms_pending_on_update " + 783 "AFTER UPDATE ON pdu " + 784 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + 785 " AND new." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 786 " AND old." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 787 "BEGIN " + 788 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 789 " (" + PendingMessages.PROTO_TYPE + "," + 790 " " + PendingMessages.MSG_ID + "," + 791 " " + PendingMessages.MSG_TYPE + "," + 792 " " + PendingMessages.ERROR_TYPE + "," + 793 " " + PendingMessages.ERROR_CODE + "," + 794 " " + PendingMessages.RETRY_INDEX + "," + 795 " " + PendingMessages.DUE_TIME + ") " + 796 " VALUES " + 797 " (" + MmsSms.MMS_PROTO + "," + 798 " new." + BaseColumns._ID + "," + 799 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 800 "END;"); 801 802 // monitor the mms table 803 db.execSQL("DROP TRIGGER IF EXISTS mms_words_update"); 804 db.execSQL("CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words " + 805 " SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " + 806 " END;"); 807 808 db.execSQL("DROP TRIGGER IF EXISTS mms_words_delete"); 809 db.execSQL("CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM " + 810 " words WHERE source_id = OLD._id AND table_to_use = 2; END;"); 811 812 // Updates threads table whenever a message in pdu is updated. 813 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_date_subject_on_update"); 814 db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" + 815 " UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX + 816 " ON " + MmsProvider.TABLE_PDU + " " + 817 PDU_UPDATE_THREAD_CONSTRAINTS + 818 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 819 820 // Update threads table whenever a message in pdu is deleted 821 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_delete"); 822 db.execSQL("CREATE TRIGGER pdu_update_thread_on_delete " + 823 "AFTER DELETE ON pdu " + 824 "BEGIN " + 825 " UPDATE threads SET " + 826 " date = (strftime('%s','now') * 1000)" + 827 " WHERE threads._id = old." + Mms.THREAD_ID + "; " + 828 UPDATE_THREAD_COUNT_ON_OLD + 829 UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE + 830 "END;"); 831 832 // Updates threads table whenever a message is added to pdu. 833 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_insert"); 834 db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " + 835 MmsProvider.TABLE_PDU + " " + 836 PDU_UPDATE_THREAD_CONSTRAINTS + 837 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 838 839 // Updates threads table whenever a message in pdu is updated. 840 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_read_on_update"); 841 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 842 " UPDATE OF " + Mms.READ + 843 " ON " + MmsProvider.TABLE_PDU + " " + 844 PDU_UPDATE_THREAD_CONSTRAINTS + 845 "BEGIN " + 846 PDU_UPDATE_THREAD_READ_BODY + 847 "END;"); 848 849 // Update the error flag of threads when delete pending message. 850 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_mms"); 851 db.execSQL("CREATE TRIGGER update_threads_error_on_delete_mms " + 852 " BEFORE DELETE ON pdu" + 853 " WHEN OLD._id IN (SELECT DISTINCT msg_id" + 854 " FROM pending_msgs" + 855 " WHERE err_type >= 10) " + 856 "BEGIN " + 857 " UPDATE threads SET error = error - 1" + 858 " WHERE _id = OLD.thread_id; " + 859 "END;"); 860 861 // Update the error flag of threads while moving an MM out of Outbox, 862 // which was failed to be sent permanently. 863 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms"); 864 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 865 " BEFORE UPDATE OF msg_box ON pdu " + 866 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 867 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 868 " FROM pending_msgs" + 869 " WHERE err_type >= 10)) " + 870 "BEGIN " + 871 " UPDATE threads SET error = error - 1" + 872 " WHERE _id = OLD.thread_id; " + 873 "END;"); 874 } 875 876 @VisibleForTesting 877 public static String CREATE_SMS_TABLE_STRING = 878 "CREATE TABLE sms (" + 879 "_id INTEGER PRIMARY KEY," + 880 "thread_id INTEGER," + 881 "address TEXT," + 882 "person INTEGER," + 883 "date INTEGER," + 884 "date_sent INTEGER DEFAULT 0," + 885 "protocol INTEGER," + 886 "read INTEGER DEFAULT 0," + 887 "status INTEGER DEFAULT -1," + // a TP-Status value 888 // or -1 if it 889 // status hasn't 890 // been received 891 "type INTEGER," + 892 "reply_path_present INTEGER," + 893 "subject TEXT," + 894 "body TEXT," + 895 "service_center TEXT," + 896 "locked INTEGER DEFAULT 0," + 897 "sub_id INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 898 "error_code INTEGER DEFAULT 0," + 899 "creator TEXT," + 900 "seen INTEGER DEFAULT 0" + 901 ");"; 902 903 @VisibleForTesting 904 public static String CREATE_ATTACHMENTS_TABLE_STRING = 905 "CREATE TABLE attachments (" + 906 "sms_id INTEGER," + 907 "content_url TEXT," + 908 "offset INTEGER);"; 909 910 /** 911 * This table is used by the SMS dispatcher to hold 912 * incomplete partial messages until all the parts arrive. 913 */ 914 @VisibleForTesting 915 public static String CREATE_RAW_TABLE_STRING = 916 "CREATE TABLE raw (" + 917 "_id INTEGER PRIMARY KEY," + 918 "date INTEGER," + 919 "reference_number INTEGER," + // one per full message 920 "count INTEGER," + // the number of parts 921 "sequence INTEGER," + // the part number of this message 922 "destination_port INTEGER," + 923 "address TEXT," + 924 "sub_id INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 925 "pdu TEXT," + // the raw PDU for this part 926 "deleted INTEGER DEFAULT 0," + // bool to indicate if row is deleted 927 "message_body TEXT," + // message body 928 "display_originating_addr TEXT);"; 929 // email address if from an email gateway, otherwise same as address 930 private void createSmsTables(SQLiteDatabase db) { 931 // N.B.: Whenever the columns here are changed, the columns in 932 // {@ref MmsSmsProvider} must be changed to match. 933 db.execSQL(CREATE_SMS_TABLE_STRING); 934 935 db.execSQL(CREATE_RAW_TABLE_STRING); 936 937 db.execSQL(CREATE_ATTACHMENTS_TABLE_STRING); 938 939 /** 940 * This table is used by the SMS dispatcher to hold pending 941 * delivery status report intents. 942 */ 943 db.execSQL("CREATE TABLE sr_pending (" + 944 "reference_number INTEGER," + 945 "action TEXT," + 946 "data TEXT);"); 947 948 // Restricted view of sms table, only sent/received messages 949 db.execSQL("CREATE VIEW " + SmsProvider.VIEW_SMS_RESTRICTED + " AS " + 950 "SELECT * FROM " + SmsProvider.TABLE_SMS + " WHERE " + 951 Sms.TYPE + "=" + Sms.MESSAGE_TYPE_INBOX + 952 " OR " + 953 Sms.TYPE + "=" + Sms.MESSAGE_TYPE_SENT + ";"); 954 } 955 956 private void createCommonTables(SQLiteDatabase db) { 957 // TODO Ensure that each entry is removed when the last use of 958 // any address equivalent to its address is removed. 959 960 /** 961 * This table maps the first instance seen of any particular 962 * MMS/SMS address to an ID, which is then used as its 963 * canonical representation. If the same address or an 964 * equivalent address (as determined by our Sqlite 965 * PHONE_NUMBERS_EQUAL extension) is seen later, this same ID 966 * will be used. The _id is created with AUTOINCREMENT so it 967 * will never be reused again if a recipient is deleted. 968 */ 969 db.execSQL("CREATE TABLE canonical_addresses (" + 970 "_id INTEGER PRIMARY KEY AUTOINCREMENT," + 971 "address TEXT);"); 972 973 /** 974 * This table maps the subject and an ordered set of recipient 975 * IDs, separated by spaces, to a unique thread ID. The IDs 976 * come from the canonical_addresses table. This works 977 * because messages are considered to be part of the same 978 * thread if they have the same subject (or a null subject) 979 * and the same set of recipients. 980 */ 981 db.execSQL("CREATE TABLE threads (" + 982 Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 983 Threads.DATE + " INTEGER DEFAULT 0," + 984 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 985 Threads.RECIPIENT_IDS + " TEXT," + 986 Threads.SNIPPET + " TEXT," + 987 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 988 Threads.READ + " INTEGER DEFAULT 1," + 989 Threads.ARCHIVED + " INTEGER DEFAULT 0," + 990 Threads.TYPE + " INTEGER DEFAULT 0," + 991 Threads.ERROR + " INTEGER DEFAULT 0," + 992 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 993 994 /** 995 * This table stores the queue of messages to be sent/downloaded. 996 */ 997 db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" + 998 PendingMessages._ID + " INTEGER PRIMARY KEY," + 999 PendingMessages.PROTO_TYPE + " INTEGER," + 1000 PendingMessages.MSG_ID + " INTEGER," + 1001 PendingMessages.MSG_TYPE + " INTEGER," + 1002 PendingMessages.ERROR_TYPE + " INTEGER," + 1003 PendingMessages.ERROR_CODE + " INTEGER," + 1004 PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," + 1005 PendingMessages.DUE_TIME + " INTEGER," + 1006 PendingMessages.SUBSCRIPTION_ID + " INTEGER DEFAULT " + 1007 SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 1008 PendingMessages.LAST_TRY + " INTEGER);"); 1009 1010 } 1011 1012 // TODO Check the query plans for these triggers. 1013 private void createCommonTriggers(SQLiteDatabase db) { 1014 // Updates threads table whenever a message is added to sms. 1015 db.execSQL("CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms " + 1016 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 1017 1018 // Updates threads table whenever a message in sms is updated. 1019 db.execSQL("CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER" + 1020 " UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE + 1021 " ON sms " + 1022 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 1023 1024 // Updates threads table whenever a message in sms is updated. 1025 db.execSQL("CREATE TRIGGER sms_update_thread_read_on_update AFTER" + 1026 " UPDATE OF " + Sms.READ + 1027 " ON sms " + 1028 "BEGIN " + 1029 SMS_UPDATE_THREAD_READ_BODY + 1030 "END;"); 1031 1032 // As of DATABASE_VERSION 55, we've removed these triggers that delete empty threads. 1033 // These triggers interfere with saving drafts on brand new threads. Instead of 1034 // triggers cleaning up empty threads, the empty threads should be cleaned up by 1035 // an explicit call to delete with Threads.OBSOLETE_THREADS_URI. 1036 1037// // When the last message in a thread is deleted, these 1038// // triggers ensure that the entry for its thread ID is removed 1039// // from the threads table. 1040// db.execSQL("CREATE TRIGGER delete_obsolete_threads_pdu " + 1041// "AFTER DELETE ON pdu " + 1042// "BEGIN " + 1043// " DELETE FROM threads " + 1044// " WHERE " + 1045// " _id = old.thread_id " + 1046// " AND _id NOT IN " + 1047// " (SELECT thread_id FROM sms " + 1048// " UNION SELECT thread_id from pdu); " + 1049// "END;"); 1050// 1051// db.execSQL("CREATE TRIGGER delete_obsolete_threads_when_update_pdu " + 1052// "AFTER UPDATE OF " + Mms.THREAD_ID + " ON pdu " + 1053// "WHEN old." + Mms.THREAD_ID + " != new." + Mms.THREAD_ID + " " + 1054// "BEGIN " + 1055// " DELETE FROM threads " + 1056// " WHERE " + 1057// " _id = old.thread_id " + 1058// " AND _id NOT IN " + 1059// " (SELECT thread_id FROM sms " + 1060// " UNION SELECT thread_id from pdu); " + 1061// "END;"); 1062 1063 // TODO Add triggers for SMS retry-status management. 1064 1065 // Update the error flag of threads when the error type of 1066 // a pending MM is updated. 1067 db.execSQL("CREATE TRIGGER update_threads_error_on_update_mms " + 1068 " AFTER UPDATE OF err_type ON pending_msgs " + 1069 " WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)" + 1070 " OR (OLD.err_type >= 10 AND NEW.err_type < 10) " + 1071 "BEGIN" + 1072 " UPDATE threads SET error = " + 1073 " CASE" + 1074 " WHEN NEW.err_type >= 10 THEN error + 1" + 1075 " ELSE error - 1" + 1076 " END " + 1077 " WHERE _id =" + 1078 " (SELECT DISTINCT thread_id" + 1079 " FROM pdu" + 1080 " WHERE _id = NEW.msg_id); " + 1081 "END;"); 1082 1083 // Update the error flag of threads after a text message was 1084 // failed to send/receive. 1085 db.execSQL("CREATE TRIGGER update_threads_error_on_update_sms " + 1086 " AFTER UPDATE OF type ON sms" + 1087 " WHEN (OLD.type != 5 AND NEW.type = 5)" + 1088 " OR (OLD.type = 5 AND NEW.type != 5) " + 1089 "BEGIN " + 1090 " UPDATE threads SET error = " + 1091 " CASE" + 1092 " WHEN NEW.type = 5 THEN error + 1" + 1093 " ELSE error - 1" + 1094 " END " + 1095 " WHERE _id = NEW.thread_id; " + 1096 "END;"); 1097 } 1098 1099 @Override 1100 public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) { 1101 Log.w(TAG, "Upgrading database from version " + oldVersion 1102 + " to " + currentVersion + "."); 1103 1104 switch (oldVersion) { 1105 case 40: 1106 if (currentVersion <= 40) { 1107 return; 1108 } 1109 1110 db.beginTransaction(); 1111 try { 1112 upgradeDatabaseToVersion41(db); 1113 db.setTransactionSuccessful(); 1114 } catch (Throwable ex) { 1115 Log.e(TAG, ex.getMessage(), ex); 1116 break; 1117 } finally { 1118 db.endTransaction(); 1119 } 1120 // fall through 1121 case 41: 1122 if (currentVersion <= 41) { 1123 return; 1124 } 1125 1126 db.beginTransaction(); 1127 try { 1128 upgradeDatabaseToVersion42(db); 1129 db.setTransactionSuccessful(); 1130 } catch (Throwable ex) { 1131 Log.e(TAG, ex.getMessage(), ex); 1132 break; 1133 } finally { 1134 db.endTransaction(); 1135 } 1136 // fall through 1137 case 42: 1138 if (currentVersion <= 42) { 1139 return; 1140 } 1141 1142 db.beginTransaction(); 1143 try { 1144 upgradeDatabaseToVersion43(db); 1145 db.setTransactionSuccessful(); 1146 } catch (Throwable ex) { 1147 Log.e(TAG, ex.getMessage(), ex); 1148 break; 1149 } finally { 1150 db.endTransaction(); 1151 } 1152 // fall through 1153 case 43: 1154 if (currentVersion <= 43) { 1155 return; 1156 } 1157 1158 db.beginTransaction(); 1159 try { 1160 upgradeDatabaseToVersion44(db); 1161 db.setTransactionSuccessful(); 1162 } catch (Throwable ex) { 1163 Log.e(TAG, ex.getMessage(), ex); 1164 break; 1165 } finally { 1166 db.endTransaction(); 1167 } 1168 // fall through 1169 case 44: 1170 if (currentVersion <= 44) { 1171 return; 1172 } 1173 1174 db.beginTransaction(); 1175 try { 1176 upgradeDatabaseToVersion45(db); 1177 db.setTransactionSuccessful(); 1178 } catch (Throwable ex) { 1179 Log.e(TAG, ex.getMessage(), ex); 1180 break; 1181 } finally { 1182 db.endTransaction(); 1183 } 1184 // fall through 1185 case 45: 1186 if (currentVersion <= 45) { 1187 return; 1188 } 1189 db.beginTransaction(); 1190 try { 1191 upgradeDatabaseToVersion46(db); 1192 db.setTransactionSuccessful(); 1193 } catch (Throwable ex) { 1194 Log.e(TAG, ex.getMessage(), ex); 1195 break; 1196 } finally { 1197 db.endTransaction(); 1198 } 1199 // fall through 1200 case 46: 1201 if (currentVersion <= 46) { 1202 return; 1203 } 1204 1205 db.beginTransaction(); 1206 try { 1207 upgradeDatabaseToVersion47(db); 1208 db.setTransactionSuccessful(); 1209 } catch (Throwable ex) { 1210 Log.e(TAG, ex.getMessage(), ex); 1211 break; 1212 } finally { 1213 db.endTransaction(); 1214 } 1215 // fall through 1216 case 47: 1217 if (currentVersion <= 47) { 1218 return; 1219 } 1220 1221 db.beginTransaction(); 1222 try { 1223 upgradeDatabaseToVersion48(db); 1224 db.setTransactionSuccessful(); 1225 } catch (Throwable ex) { 1226 Log.e(TAG, ex.getMessage(), ex); 1227 break; 1228 } finally { 1229 db.endTransaction(); 1230 } 1231 // fall through 1232 case 48: 1233 if (currentVersion <= 48) { 1234 return; 1235 } 1236 1237 db.beginTransaction(); 1238 try { 1239 createWordsTables(db); 1240 db.setTransactionSuccessful(); 1241 } catch (Throwable ex) { 1242 Log.e(TAG, ex.getMessage(), ex); 1243 break; 1244 } finally { 1245 db.endTransaction(); 1246 } 1247 // fall through 1248 case 49: 1249 if (currentVersion <= 49) { 1250 return; 1251 } 1252 db.beginTransaction(); 1253 try { 1254 createThreadIdIndex(db); 1255 db.setTransactionSuccessful(); 1256 } catch (Throwable ex) { 1257 Log.e(TAG, ex.getMessage(), ex); 1258 break; // force to destroy all old data; 1259 } finally { 1260 db.endTransaction(); 1261 } 1262 // fall through 1263 case 50: 1264 if (currentVersion <= 50) { 1265 return; 1266 } 1267 1268 db.beginTransaction(); 1269 try { 1270 upgradeDatabaseToVersion51(db); 1271 db.setTransactionSuccessful(); 1272 } catch (Throwable ex) { 1273 Log.e(TAG, ex.getMessage(), ex); 1274 break; 1275 } finally { 1276 db.endTransaction(); 1277 } 1278 // fall through 1279 case 51: 1280 if (currentVersion <= 51) { 1281 return; 1282 } 1283 // 52 was adding a new meta_data column, but that was removed. 1284 // fall through 1285 case 52: 1286 if (currentVersion <= 52) { 1287 return; 1288 } 1289 1290 db.beginTransaction(); 1291 try { 1292 upgradeDatabaseToVersion53(db); 1293 db.setTransactionSuccessful(); 1294 } catch (Throwable ex) { 1295 Log.e(TAG, ex.getMessage(), ex); 1296 break; 1297 } finally { 1298 db.endTransaction(); 1299 } 1300 // fall through 1301 case 53: 1302 if (currentVersion <= 53) { 1303 return; 1304 } 1305 1306 db.beginTransaction(); 1307 try { 1308 upgradeDatabaseToVersion54(db); 1309 db.setTransactionSuccessful(); 1310 } catch (Throwable ex) { 1311 Log.e(TAG, ex.getMessage(), ex); 1312 break; 1313 } finally { 1314 db.endTransaction(); 1315 } 1316 // fall through 1317 case 54: 1318 if (currentVersion <= 54) { 1319 return; 1320 } 1321 1322 db.beginTransaction(); 1323 try { 1324 upgradeDatabaseToVersion55(db); 1325 db.setTransactionSuccessful(); 1326 } catch (Throwable ex) { 1327 Log.e(TAG, ex.getMessage(), ex); 1328 break; 1329 } finally { 1330 db.endTransaction(); 1331 } 1332 // fall through 1333 case 55: 1334 if (currentVersion <= 55) { 1335 return; 1336 } 1337 1338 db.beginTransaction(); 1339 try { 1340 upgradeDatabaseToVersion56(db); 1341 db.setTransactionSuccessful(); 1342 } catch (Throwable ex) { 1343 Log.e(TAG, ex.getMessage(), ex); 1344 break; 1345 } finally { 1346 db.endTransaction(); 1347 } 1348 // fall through 1349 case 56: 1350 if (currentVersion <= 56) { 1351 return; 1352 } 1353 1354 db.beginTransaction(); 1355 try { 1356 upgradeDatabaseToVersion57(db); 1357 db.setTransactionSuccessful(); 1358 } catch (Throwable ex) { 1359 Log.e(TAG, ex.getMessage(), ex); 1360 break; 1361 } finally { 1362 db.endTransaction(); 1363 } 1364 // fall through 1365 case 57: 1366 if (currentVersion <= 57) { 1367 return; 1368 } 1369 1370 db.beginTransaction(); 1371 try { 1372 upgradeDatabaseToVersion58(db); 1373 db.setTransactionSuccessful(); 1374 } catch (Throwable ex) { 1375 Log.e(TAG, ex.getMessage(), ex); 1376 break; 1377 } finally { 1378 db.endTransaction(); 1379 } 1380 // fall through 1381 case 58: 1382 if (currentVersion <= 58) { 1383 return; 1384 } 1385 1386 db.beginTransaction(); 1387 try { 1388 upgradeDatabaseToVersion59(db); 1389 db.setTransactionSuccessful(); 1390 } catch (Throwable ex) { 1391 Log.e(TAG, ex.getMessage(), ex); 1392 break; 1393 } finally { 1394 db.endTransaction(); 1395 } 1396 // fall through 1397 case 59: 1398 if (currentVersion <= 59) { 1399 return; 1400 } 1401 1402 db.beginTransaction(); 1403 try { 1404 upgradeDatabaseToVersion60(db); 1405 db.setTransactionSuccessful(); 1406 } catch (Throwable ex) { 1407 Log.e(TAG, ex.getMessage(), ex); 1408 break; 1409 } finally { 1410 db.endTransaction(); 1411 } 1412 // fall through 1413 case 60: 1414 if (currentVersion <= 60) { 1415 return; 1416 } 1417 1418 db.beginTransaction(); 1419 try { 1420 upgradeDatabaseToVersion61(db); 1421 db.setTransactionSuccessful(); 1422 } catch (Throwable ex) { 1423 Log.e(TAG, ex.getMessage(), ex); 1424 break; 1425 } finally { 1426 db.endTransaction(); 1427 } 1428 // fall through 1429 case 61: 1430 if (currentVersion <= 61) { 1431 return; 1432 } 1433 1434 db.beginTransaction(); 1435 try { 1436 upgradeDatabaseToVersion62(db); 1437 db.setTransactionSuccessful(); 1438 } catch (Throwable ex) { 1439 Log.e(TAG, ex.getMessage(), ex); 1440 break; 1441 } finally { 1442 db.endTransaction(); 1443 } 1444 // fall through 1445 case 62: 1446 if (currentVersion <= 62) { 1447 return; 1448 } 1449 1450 db.beginTransaction(); 1451 try { 1452 // upgrade to 63: just add a happy little index. 1453 createThreadIdDateIndex(db); 1454 db.setTransactionSuccessful(); 1455 } catch (Throwable ex) { 1456 Log.e(TAG, ex.getMessage(), ex); 1457 break; 1458 } finally { 1459 db.endTransaction(); 1460 } 1461 // fall through 1462 case 63: 1463 if (currentVersion <= 63) { 1464 return; 1465 } 1466 1467 db.beginTransaction(); 1468 try { 1469 upgradeDatabaseToVersion64(db); 1470 db.setTransactionSuccessful(); 1471 } catch (Throwable ex) { 1472 Log.e(TAG, ex.getMessage(), ex); 1473 break; 1474 } finally { 1475 db.endTransaction(); 1476 } 1477 // fall through 1478 case 64: 1479 if (currentVersion <= 64) { 1480 return; 1481 } 1482 1483 db.beginTransaction(); 1484 try { 1485 upgradeDatabaseToVersion65(db); 1486 db.setTransactionSuccessful(); 1487 } catch (Throwable ex) { 1488 Log.e(TAG, ex.getMessage(), ex); 1489 break; 1490 } finally { 1491 db.endTransaction(); 1492 } 1493 // fall through 1494 case 65: 1495 if (currentVersion <= 65) { 1496 return; 1497 } 1498 1499 db.beginTransaction(); 1500 try { 1501 upgradeDatabaseToVersion66(db); 1502 db.setTransactionSuccessful(); 1503 } catch (Throwable ex) { 1504 Log.e(TAG, ex.getMessage(), ex); 1505 break; 1506 } finally { 1507 db.endTransaction(); 1508 } 1509 // fall through 1510 case 66: 1511 if (currentVersion <= 66) { 1512 return; 1513 } 1514 db.beginTransaction(); 1515 try { 1516 createPartMidIndex(db); 1517 createAddrMsgIdIndex(db); 1518 db.setTransactionSuccessful(); 1519 } catch (Throwable ex) { 1520 Log.e(TAG, ex.getMessage(), ex); 1521 break; // force to destroy all old data; 1522 } finally { 1523 db.endTransaction(); 1524 } 1525 return; 1526 } 1527 1528 Log.e(TAG, "Destroying all old data."); 1529 dropAll(db); 1530 onCreate(db); 1531 } 1532 1533 private void dropAll(SQLiteDatabase db) { 1534 // Clean the database out in order to start over from scratch. 1535 // We don't need to drop our triggers here because SQLite automatically 1536 // drops a trigger when its attached database is dropped. 1537 db.execSQL("DROP TABLE IF EXISTS canonical_addresses"); 1538 db.execSQL("DROP TABLE IF EXISTS threads"); 1539 db.execSQL("DROP TABLE IF EXISTS " + MmsSmsProvider.TABLE_PENDING_MSG); 1540 db.execSQL("DROP TABLE IF EXISTS sms"); 1541 db.execSQL("DROP TABLE IF EXISTS raw"); 1542 db.execSQL("DROP TABLE IF EXISTS attachments"); 1543 db.execSQL("DROP TABLE IF EXISTS thread_ids"); 1544 db.execSQL("DROP TABLE IF EXISTS sr_pending"); 1545 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PDU + ";"); 1546 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_ADDR + ";"); 1547 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PART + ";"); 1548 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_RATE + ";"); 1549 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_DRM + ";"); 1550 } 1551 1552 private void upgradeDatabaseToVersion41(SQLiteDatabase db) { 1553 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms"); 1554 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 1555 " BEFORE UPDATE OF msg_box ON pdu " + 1556 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 1557 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 1558 " FROM pending_msgs" + 1559 " WHERE err_type >= 10)) " + 1560 "BEGIN " + 1561 " UPDATE threads SET error = error - 1" + 1562 " WHERE _id = OLD.thread_id; " + 1563 "END;"); 1564 } 1565 1566 private void upgradeDatabaseToVersion42(SQLiteDatabase db) { 1567 db.execSQL("DROP TRIGGER IF EXISTS sms_update_thread_on_delete"); 1568 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_sms"); 1569 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_sms"); 1570 } 1571 1572 private void upgradeDatabaseToVersion43(SQLiteDatabase db) { 1573 // Add 'has_attachment' column to threads table. 1574 db.execSQL("ALTER TABLE threads ADD COLUMN has_attachment INTEGER DEFAULT 0"); 1575 1576 updateThreadsAttachmentColumn(db); 1577 1578 // Add insert and delete triggers for keeping it up to date. 1579 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 1580 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 1581 } 1582 1583 private void upgradeDatabaseToVersion44(SQLiteDatabase db) { 1584 updateThreadsAttachmentColumn(db); 1585 1586 // add the update trigger for keeping the threads up to date. 1587 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1588 } 1589 1590 private void upgradeDatabaseToVersion45(SQLiteDatabase db) { 1591 // Add 'locked' column to sms table. 1592 db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.LOCKED + " INTEGER DEFAULT 0"); 1593 1594 // Add 'locked' column to pdu table. 1595 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.LOCKED + " INTEGER DEFAULT 0"); 1596 } 1597 1598 private void upgradeDatabaseToVersion46(SQLiteDatabase db) { 1599 // add the "text" column for caching inline text (e.g. strings) instead of 1600 // putting them in an external file 1601 db.execSQL("ALTER TABLE part ADD COLUMN " + Part.TEXT + " TEXT"); 1602 1603 Cursor textRows = db.query( 1604 "part", 1605 new String[] { Part._ID, Part._DATA, Part.TEXT}, 1606 "ct = 'text/plain' OR ct == 'application/smil'", 1607 null, 1608 null, 1609 null, 1610 null); 1611 ArrayList<String> filesToDelete = new ArrayList<String>(); 1612 try { 1613 db.beginTransaction(); 1614 if (textRows != null) { 1615 int partDataColumn = textRows.getColumnIndex(Part._DATA); 1616 1617 // This code is imperfect in that we can't guarantee that all the 1618 // backing files get deleted. For example if the system aborts after 1619 // the database is updated but before we complete the process of 1620 // deleting files. 1621 while (textRows.moveToNext()) { 1622 String path = textRows.getString(partDataColumn); 1623 if (path != null) { 1624 try { 1625 InputStream is = new FileInputStream(path); 1626 byte [] data = new byte[is.available()]; 1627 is.read(data); 1628 EncodedStringValue v = new EncodedStringValue(data); 1629 db.execSQL("UPDATE part SET " + Part._DATA + " = NULL, " + 1630 Part.TEXT + " = ?", new String[] { v.getString() }); 1631 is.close(); 1632 filesToDelete.add(path); 1633 } catch (IOException e) { 1634 // TODO Auto-generated catch block 1635 e.printStackTrace(); 1636 } 1637 } 1638 } 1639 } 1640 db.setTransactionSuccessful(); 1641 } finally { 1642 db.endTransaction(); 1643 for (String pathToDelete : filesToDelete) { 1644 try { 1645 (new File(pathToDelete)).delete(); 1646 } catch (SecurityException ex) { 1647 Log.e(TAG, "unable to clean up old mms file for " + pathToDelete, ex); 1648 } 1649 } 1650 if (textRows != null) { 1651 textRows.close(); 1652 } 1653 } 1654 } 1655 1656 private void upgradeDatabaseToVersion47(SQLiteDatabase db) { 1657 updateThreadsAttachmentColumn(db); 1658 1659 // add the update trigger for keeping the threads up to date. 1660 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1661 } 1662 1663 private void upgradeDatabaseToVersion48(SQLiteDatabase db) { 1664 // Add 'error_code' column to sms table. 1665 db.execSQL("ALTER TABLE sms ADD COLUMN error_code INTEGER DEFAULT 0"); 1666 } 1667 1668 private void upgradeDatabaseToVersion51(SQLiteDatabase db) { 1669 db.execSQL("ALTER TABLE sms add COLUMN seen INTEGER DEFAULT 0"); 1670 db.execSQL("ALTER TABLE pdu add COLUMN seen INTEGER DEFAULT 0"); 1671 1672 try { 1673 // update the existing sms and pdu tables so the new "seen" column is the same as 1674 // the "read" column for each row. 1675 ContentValues contentValues = new ContentValues(); 1676 contentValues.put("seen", 1); 1677 int count = db.update("sms", contentValues, "read=1", null); 1678 Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count + 1679 " rows in sms table to have READ=1"); 1680 count = db.update("pdu", contentValues, "read=1", null); 1681 Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count + 1682 " rows in pdu table to have READ=1"); 1683 } catch (Exception ex) { 1684 Log.e(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51 caught ", ex); 1685 } 1686 } 1687 1688 private void upgradeDatabaseToVersion53(SQLiteDatabase db) { 1689 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_read_on_update"); 1690 1691 // Updates threads table whenever a message in pdu is updated. 1692 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 1693 " UPDATE OF " + Mms.READ + 1694 " ON " + MmsProvider.TABLE_PDU + " " + 1695 PDU_UPDATE_THREAD_CONSTRAINTS + 1696 "BEGIN " + 1697 PDU_UPDATE_THREAD_READ_BODY + 1698 "END;"); 1699 } 1700 1701 private void upgradeDatabaseToVersion54(SQLiteDatabase db) { 1702 // Add 'date_sent' column to sms table. 1703 db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.DATE_SENT + " INTEGER DEFAULT 0"); 1704 1705 // Add 'date_sent' column to pdu table. 1706 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.DATE_SENT + " INTEGER DEFAULT 0"); 1707 } 1708 1709 private void upgradeDatabaseToVersion55(SQLiteDatabase db) { 1710 // Drop removed triggers 1711 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_pdu"); 1712 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_when_update_pdu"); 1713 } 1714 1715 private void upgradeDatabaseToVersion56(SQLiteDatabase db) { 1716 // Add 'text_only' column to pdu table. 1717 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU + " ADD COLUMN " + Mms.TEXT_ONLY + 1718 " INTEGER DEFAULT 0"); 1719 } 1720 1721 private void upgradeDatabaseToVersion57(SQLiteDatabase db) { 1722 // Clear out bad rows, those with empty threadIds, from the pdu table. 1723 db.execSQL("DELETE FROM " + MmsProvider.TABLE_PDU + " WHERE " + Mms.THREAD_ID + " IS NULL"); 1724 } 1725 1726 private void upgradeDatabaseToVersion58(SQLiteDatabase db) { 1727 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU + 1728 " ADD COLUMN " + Mms.SUBSCRIPTION_ID 1729 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1730 db.execSQL("ALTER TABLE " + MmsSmsProvider.TABLE_PENDING_MSG 1731 +" ADD COLUMN " + "pending_sub_id" 1732 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1733 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_SMS 1734 + " ADD COLUMN " + Sms.SUBSCRIPTION_ID 1735 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1736 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW 1737 +" ADD COLUMN " + Sms.SUBSCRIPTION_ID 1738 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1739 } 1740 1741 private void upgradeDatabaseToVersion59(SQLiteDatabase db) { 1742 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU +" ADD COLUMN " 1743 + Mms.CREATOR + " TEXT"); 1744 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_SMS +" ADD COLUMN " 1745 + Sms.CREATOR + " TEXT"); 1746 } 1747 1748 private void upgradeDatabaseToVersion60(SQLiteDatabase db) { 1749 db.execSQL("ALTER TABLE " + MmsSmsProvider.TABLE_THREADS +" ADD COLUMN " 1750 + Threads.ARCHIVED + " INTEGER DEFAULT 0"); 1751 } 1752 1753 private void upgradeDatabaseToVersion61(SQLiteDatabase db) { 1754 db.execSQL("CREATE VIEW " + SmsProvider.VIEW_SMS_RESTRICTED + " AS " + 1755 "SELECT * FROM " + SmsProvider.TABLE_SMS + " WHERE " + 1756 Sms.TYPE + "=" + Sms.MESSAGE_TYPE_INBOX + 1757 " OR " + 1758 Sms.TYPE + "=" + Sms.MESSAGE_TYPE_SENT + ";"); 1759 db.execSQL("CREATE VIEW " + MmsProvider.VIEW_PDU_RESTRICTED + " AS " + 1760 "SELECT * FROM " + MmsProvider.TABLE_PDU + " WHERE " + 1761 "(" + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_INBOX + 1762 " OR " + 1763 Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_SENT + ")" + 1764 " AND " + 1765 "(" + Mms.MESSAGE_TYPE + "!=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + ");"); 1766 1767 } 1768 1769 private void upgradeDatabaseToVersion62(SQLiteDatabase db) { 1770 // When a non-FBE device is upgraded to N, all MMS attachment files are moved from 1771 // /data/data to /data/user_de. We need to update the paths stored in the parts table to 1772 // reflect this change. 1773 String newPartsDirPath; 1774 try { 1775 newPartsDirPath = mContext.getDir(MmsProvider.PARTS_DIR_NAME, 0).getCanonicalPath(); 1776 } 1777 catch (IOException e){ 1778 Log.e(TAG, "openFile: check file path failed " + e, e); 1779 return; 1780 } 1781 1782 // The old path of the part files will be something like this: 1783 // /data/data/0/com.android.providers.telephony/app_parts 1784 // The new path of the part files will be something like this: 1785 // /data/user_de/0/com.android.providers.telephony/app_parts 1786 int partsDirIndex = newPartsDirPath.lastIndexOf( 1787 File.separator, newPartsDirPath.lastIndexOf(MmsProvider.PARTS_DIR_NAME)); 1788 String partsDirName = newPartsDirPath.substring(partsDirIndex) + File.separator; 1789 // The query to update the part path will be: 1790 // UPDATE part SET _data = '/data/user_de/0/com.android.providers.telephony' || 1791 // SUBSTR(_data, INSTR(_data, '/app_parts/')) 1792 // WHERE INSTR(_data, '/app_parts/') > 0 1793 db.execSQL("UPDATE " + MmsProvider.TABLE_PART + 1794 " SET " + Part._DATA + " = '" + newPartsDirPath.substring(0, partsDirIndex) + "' ||" + 1795 " SUBSTR(" + Part._DATA + ", INSTR(" + Part._DATA + ", '" + partsDirName + "'))" + 1796 " WHERE INSTR(" + Part._DATA + ", '" + partsDirName + "') > 0"); 1797 } 1798 1799 private void upgradeDatabaseToVersion64(SQLiteDatabase db) { 1800 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW +" ADD COLUMN deleted INTEGER DEFAULT 0"); 1801 } 1802 1803 private void upgradeDatabaseToVersion65(SQLiteDatabase db) { 1804 // aosp and internal code diverged at version 63. Aosp did createThreadIdDateIndex() on 1805 // upgrading to 63, whereas internal (nyc) added column 'deleted'. A device upgrading from 1806 // nyc will have columns deleted and message_body in raw table with version 64, but not 1807 // createThreadIdDateIndex() 1808 try { 1809 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW + " ADD COLUMN message_body TEXT"); 1810 } catch (SQLiteException e) { 1811 Log.w(TAG, "[upgradeDatabaseToVersion65] Exception adding column message_body; " + 1812 "trying createThreadIdDateIndex() instead: " + e); 1813 createThreadIdDateIndex(db); 1814 } 1815 } 1816 1817 private void upgradeDatabaseToVersion66(SQLiteDatabase db) { 1818 try { 1819 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW 1820 + " ADD COLUMN display_originating_addr TEXT"); 1821 } catch (SQLiteException e) { 1822 Log.e(TAG, "[upgradeDatabaseToVersion66] Exception adding column " 1823 + "display_originating_addr; " + e); 1824 } 1825 } 1826 1827 @Override 1828 public synchronized SQLiteDatabase getWritableDatabase() { 1829 SQLiteDatabase db = super.getWritableDatabase(); 1830 1831 if (!sTriedAutoIncrement) { 1832 sTriedAutoIncrement = true; 1833 boolean hasAutoIncrementThreads = hasAutoIncrement(db, MmsSmsProvider.TABLE_THREADS); 1834 boolean hasAutoIncrementAddresses = hasAutoIncrement(db, "canonical_addresses"); 1835 boolean hasAutoIncrementPart = hasAutoIncrement(db, "part"); 1836 boolean hasAutoIncrementPdu = hasAutoIncrement(db, "pdu"); 1837 Log.d(TAG, "[getWritableDatabase] hasAutoIncrementThreads: " + hasAutoIncrementThreads + 1838 " hasAutoIncrementAddresses: " + hasAutoIncrementAddresses + 1839 " hasAutoIncrementPart: " + hasAutoIncrementPart + 1840 " hasAutoIncrementPdu: " + hasAutoIncrementPdu); 1841 boolean autoIncrementThreadsSuccess = true; 1842 boolean autoIncrementAddressesSuccess = true; 1843 boolean autoIncrementPartSuccess = true; 1844 boolean autoIncrementPduSuccess = true; 1845 if (!hasAutoIncrementThreads) { 1846 db.beginTransaction(); 1847 try { 1848 if (false && sFakeLowStorageTest) { 1849 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1850 " - fake exception"); 1851 throw new Exception("FakeLowStorageTest"); 1852 } 1853 upgradeThreadsTableToAutoIncrement(db); // a no-op if already upgraded 1854 db.setTransactionSuccessful(); 1855 } catch (Throwable ex) { 1856 Log.e(TAG, "Failed to add autoIncrement to threads;: " + ex.getMessage(), ex); 1857 autoIncrementThreadsSuccess = false; 1858 } finally { 1859 db.endTransaction(); 1860 } 1861 } 1862 if (!hasAutoIncrementAddresses) { 1863 db.beginTransaction(); 1864 try { 1865 if (false && sFakeLowStorageTest) { 1866 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1867 " - fake exception"); 1868 throw new Exception("FakeLowStorageTest"); 1869 } 1870 upgradeAddressTableToAutoIncrement(db); // a no-op if already upgraded 1871 db.setTransactionSuccessful(); 1872 } catch (Throwable ex) { 1873 Log.e(TAG, "Failed to add autoIncrement to canonical_addresses: " + 1874 ex.getMessage(), ex); 1875 autoIncrementAddressesSuccess = false; 1876 } finally { 1877 db.endTransaction(); 1878 } 1879 } 1880 if (!hasAutoIncrementPart) { 1881 db.beginTransaction(); 1882 try { 1883 if (false && sFakeLowStorageTest) { 1884 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1885 " - fake exception"); 1886 throw new Exception("FakeLowStorageTest"); 1887 } 1888 upgradePartTableToAutoIncrement(db); // a no-op if already upgraded 1889 db.setTransactionSuccessful(); 1890 } catch (Throwable ex) { 1891 Log.e(TAG, "Failed to add autoIncrement to part: " + 1892 ex.getMessage(), ex); 1893 autoIncrementPartSuccess = false; 1894 } finally { 1895 db.endTransaction(); 1896 } 1897 } 1898 if (!hasAutoIncrementPdu) { 1899 db.beginTransaction(); 1900 try { 1901 if (false && sFakeLowStorageTest) { 1902 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1903 " - fake exception"); 1904 throw new Exception("FakeLowStorageTest"); 1905 } 1906 upgradePduTableToAutoIncrement(db); // a no-op if already upgraded 1907 db.setTransactionSuccessful(); 1908 } catch (Throwable ex) { 1909 Log.e(TAG, "Failed to add autoIncrement to pdu: " + 1910 ex.getMessage(), ex); 1911 autoIncrementPduSuccess = false; 1912 } finally { 1913 db.endTransaction(); 1914 } 1915 } 1916 if (autoIncrementThreadsSuccess && 1917 autoIncrementAddressesSuccess && 1918 autoIncrementPartSuccess && 1919 autoIncrementPduSuccess) { 1920 if (mLowStorageMonitor != null) { 1921 // We've already updated the database. This receiver is no longer necessary. 1922 Log.d(TAG, "Unregistering mLowStorageMonitor - we've upgraded"); 1923 mContext.unregisterReceiver(mLowStorageMonitor); 1924 mLowStorageMonitor = null; 1925 } 1926 } else { 1927 if (sFakeLowStorageTest) { 1928 sFakeLowStorageTest = false; 1929 } 1930 1931 // We failed, perhaps because of low storage. Turn on a receiver to watch for 1932 // storage space. 1933 if (mLowStorageMonitor == null) { 1934 Log.d(TAG, "[getWritableDatabase] turning on storage monitor"); 1935 mLowStorageMonitor = new LowStorageMonitor(); 1936 IntentFilter intentFilter = new IntentFilter(); 1937 intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_LOW); 1938 intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_OK); 1939 mContext.registerReceiver(mLowStorageMonitor, intentFilter); 1940 } 1941 } 1942 } 1943 return db; 1944 } 1945 1946 // Determine whether a particular table has AUTOINCREMENT in its schema. 1947 private boolean hasAutoIncrement(SQLiteDatabase db, String tableName) { 1948 boolean result = false; 1949 String query = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" + 1950 tableName + "'"; 1951 Cursor c = db.rawQuery(query, null); 1952 if (c != null) { 1953 try { 1954 if (c.moveToFirst()) { 1955 String schema = c.getString(0); 1956 result = schema != null ? schema.contains("AUTOINCREMENT") : false; 1957 Log.d(TAG, "[MmsSmsDb] tableName: " + tableName + " hasAutoIncrement: " + 1958 schema + " result: " + result); 1959 } 1960 } finally { 1961 c.close(); 1962 } 1963 } 1964 return result; 1965 } 1966 1967 // upgradeThreadsTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1968 // the threads table. This could fail if the user has a lot of conversations and not enough 1969 // storage to make a copy of the threads table. That's ok. This upgrade is optional. It'll 1970 // be called again next time the device is rebooted. 1971 private void upgradeThreadsTableToAutoIncrement(SQLiteDatabase db) { 1972 if (hasAutoIncrement(db, MmsSmsProvider.TABLE_THREADS)) { 1973 Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: already upgraded"); 1974 return; 1975 } 1976 Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: upgrading"); 1977 1978 // Make the _id of the threads table autoincrement so we never re-use thread ids 1979 // Have to create a new temp threads table. Copy all the info from the old table. 1980 // Drop the old table and rename the new table to that of the old. 1981 db.execSQL("CREATE TABLE threads_temp (" + 1982 Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1983 Threads.DATE + " INTEGER DEFAULT 0," + 1984 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 1985 Threads.RECIPIENT_IDS + " TEXT," + 1986 Threads.SNIPPET + " TEXT," + 1987 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 1988 Threads.READ + " INTEGER DEFAULT 1," + 1989 Threads.TYPE + " INTEGER DEFAULT 0," + 1990 Threads.ERROR + " INTEGER DEFAULT 0," + 1991 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 1992 1993 db.execSQL("INSERT INTO threads_temp SELECT * from threads;"); 1994 db.execSQL("DROP TABLE threads;"); 1995 db.execSQL("ALTER TABLE threads_temp RENAME TO threads;"); 1996 } 1997 1998 // upgradeAddressTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1999 // the canonical_addresses table. This could fail if the user has a lot of people they've 2000 // messaged with and not enough storage to make a copy of the canonical_addresses table. 2001 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 2002 private void upgradeAddressTableToAutoIncrement(SQLiteDatabase db) { 2003 if (hasAutoIncrement(db, "canonical_addresses")) { 2004 Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: already upgraded"); 2005 return; 2006 } 2007 Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: upgrading"); 2008 2009 // Make the _id of the canonical_addresses table autoincrement so we never re-use ids 2010 // Have to create a new temp canonical_addresses table. Copy all the info from the old 2011 // table. Drop the old table and rename the new table to that of the old. 2012 db.execSQL("CREATE TABLE canonical_addresses_temp (_id INTEGER PRIMARY KEY AUTOINCREMENT," + 2013 "address TEXT);"); 2014 2015 db.execSQL("INSERT INTO canonical_addresses_temp SELECT * from canonical_addresses;"); 2016 db.execSQL("DROP TABLE canonical_addresses;"); 2017 db.execSQL("ALTER TABLE canonical_addresses_temp RENAME TO canonical_addresses;"); 2018 } 2019 2020 // upgradePartTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 2021 // the part table. This could fail if the user has a lot of sound/video/picture attachments 2022 // and not enough storage to make a copy of the part table. 2023 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 2024 private void upgradePartTableToAutoIncrement(SQLiteDatabase db) { 2025 if (hasAutoIncrement(db, "part")) { 2026 Log.d(TAG, "[MmsSmsDb] upgradePartTableToAutoIncrement: already upgraded"); 2027 return; 2028 } 2029 Log.d(TAG, "[MmsSmsDb] upgradePartTableToAutoIncrement: upgrading"); 2030 2031 // Make the _id of the part table autoincrement so we never re-use ids 2032 // Have to create a new temp part table. Copy all the info from the old 2033 // table. Drop the old table and rename the new table to that of the old. 2034 db.execSQL("CREATE TABLE part_temp (" + 2035 Part._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 2036 Part.MSG_ID + " INTEGER," + 2037 Part.SEQ + " INTEGER DEFAULT 0," + 2038 Part.CONTENT_TYPE + " TEXT," + 2039 Part.NAME + " TEXT," + 2040 Part.CHARSET + " INTEGER," + 2041 Part.CONTENT_DISPOSITION + " TEXT," + 2042 Part.FILENAME + " TEXT," + 2043 Part.CONTENT_ID + " TEXT," + 2044 Part.CONTENT_LOCATION + " TEXT," + 2045 Part.CT_START + " INTEGER," + 2046 Part.CT_TYPE + " TEXT," + 2047 Part._DATA + " TEXT," + 2048 Part.TEXT + " TEXT);"); 2049 2050 db.execSQL("INSERT INTO part_temp SELECT * from part;"); 2051 db.execSQL("DROP TABLE part;"); 2052 db.execSQL("ALTER TABLE part_temp RENAME TO part;"); 2053 2054 // part-related triggers get tossed when the part table is dropped -- rebuild them. 2055 createMmsTriggers(db); 2056 } 2057 2058 // upgradePduTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 2059 // the pdu table. This could fail if the user has a lot of mms messages 2060 // and not enough storage to make a copy of the pdu table. 2061 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 2062 private void upgradePduTableToAutoIncrement(SQLiteDatabase db) { 2063 if (hasAutoIncrement(db, "pdu")) { 2064 Log.d(TAG, "[MmsSmsDb] upgradePduTableToAutoIncrement: already upgraded"); 2065 return; 2066 } 2067 Log.d(TAG, "[MmsSmsDb] upgradePduTableToAutoIncrement: upgrading"); 2068 2069 // Make the _id of the part table autoincrement so we never re-use ids 2070 // Have to create a new temp part table. Copy all the info from the old 2071 // table. Drop the old table and rename the new table to that of the old. 2072 db.execSQL("CREATE TABLE pdu_temp (" + 2073 Mms._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 2074 Mms.THREAD_ID + " INTEGER," + 2075 Mms.DATE + " INTEGER," + 2076 Mms.DATE_SENT + " INTEGER DEFAULT 0," + 2077 Mms.MESSAGE_BOX + " INTEGER," + 2078 Mms.READ + " INTEGER DEFAULT 0," + 2079 Mms.MESSAGE_ID + " TEXT," + 2080 Mms.SUBJECT + " TEXT," + 2081 Mms.SUBJECT_CHARSET + " INTEGER," + 2082 Mms.CONTENT_TYPE + " TEXT," + 2083 Mms.CONTENT_LOCATION + " TEXT," + 2084 Mms.EXPIRY + " INTEGER," + 2085 Mms.MESSAGE_CLASS + " TEXT," + 2086 Mms.MESSAGE_TYPE + " INTEGER," + 2087 Mms.MMS_VERSION + " INTEGER," + 2088 Mms.MESSAGE_SIZE + " INTEGER," + 2089 Mms.PRIORITY + " INTEGER," + 2090 Mms.READ_REPORT + " INTEGER," + 2091 Mms.REPORT_ALLOWED + " INTEGER," + 2092 Mms.RESPONSE_STATUS + " INTEGER," + 2093 Mms.STATUS + " INTEGER," + 2094 Mms.TRANSACTION_ID + " TEXT," + 2095 Mms.RETRIEVE_STATUS + " INTEGER," + 2096 Mms.RETRIEVE_TEXT + " TEXT," + 2097 Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," + 2098 Mms.READ_STATUS + " INTEGER," + 2099 Mms.CONTENT_CLASS + " INTEGER," + 2100 Mms.RESPONSE_TEXT + " TEXT," + 2101 Mms.DELIVERY_TIME + " INTEGER," + 2102 Mms.DELIVERY_REPORT + " INTEGER," + 2103 Mms.LOCKED + " INTEGER DEFAULT 0," + 2104 Mms.SUBSCRIPTION_ID + " INTEGER DEFAULT " 2105 + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 2106 Mms.SEEN + " INTEGER DEFAULT 0," + 2107 Mms.TEXT_ONLY + " INTEGER DEFAULT 0" + 2108 ");"); 2109 2110 db.execSQL("INSERT INTO pdu_temp SELECT * from pdu;"); 2111 db.execSQL("DROP TABLE pdu;"); 2112 db.execSQL("ALTER TABLE pdu_temp RENAME TO pdu;"); 2113 2114 // pdu-related triggers get tossed when the part table is dropped -- rebuild them. 2115 createMmsTriggers(db); 2116 } 2117 2118 private class LowStorageMonitor extends BroadcastReceiver { 2119 2120 public LowStorageMonitor() { 2121 } 2122 2123 public void onReceive(Context context, Intent intent) { 2124 String action = intent.getAction(); 2125 2126 Log.d(TAG, "[LowStorageMonitor] onReceive intent " + action); 2127 2128 if (Intent.ACTION_DEVICE_STORAGE_OK.equals(action)) { 2129 sTriedAutoIncrement = false; // try to upgrade on the next getWriteableDatabase 2130 } 2131 } 2132 } 2133 2134 private void updateThreadsAttachmentColumn(SQLiteDatabase db) { 2135 // Set the values of that column correctly based on the current 2136 // contents of the database. 2137 db.execSQL("UPDATE threads SET has_attachment=1 WHERE _id IN " + 2138 " (SELECT DISTINCT pdu.thread_id FROM part " + 2139 " JOIN pdu ON pdu._id=part.mid " + 2140 " WHERE part.ct != 'text/plain' AND part.ct != 'application/smil')"); 2141 } 2142} 2143