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 = 66; 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 } 571 572 private void createThreadIdIndex(SQLiteDatabase db) { 573 try { 574 db.execSQL("CREATE INDEX IF NOT EXISTS typeThreadIdIndex ON sms" + 575 " (type, thread_id);"); 576 } catch (Exception ex) { 577 Log.e(TAG, "got exception creating indices: " + ex.toString()); 578 } 579 } 580 581 private void createThreadIdDateIndex(SQLiteDatabase db) { 582 try { 583 db.execSQL("CREATE INDEX IF NOT EXISTS threadIdDateIndex ON sms" + 584 " (thread_id, date);"); 585 } catch (Exception ex) { 586 Log.e(TAG, "got exception creating indices: " + ex.toString()); 587 } 588 } 589 590 private void createMmsTables(SQLiteDatabase db) { 591 // N.B.: Whenever the columns here are changed, the columns in 592 // {@ref MmsSmsProvider} must be changed to match. 593 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PDU + " (" + 594 Mms._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 595 Mms.THREAD_ID + " INTEGER," + 596 Mms.DATE + " INTEGER," + 597 Mms.DATE_SENT + " INTEGER DEFAULT 0," + 598 Mms.MESSAGE_BOX + " INTEGER," + 599 Mms.READ + " INTEGER DEFAULT 0," + 600 Mms.MESSAGE_ID + " TEXT," + 601 Mms.SUBJECT + " TEXT," + 602 Mms.SUBJECT_CHARSET + " INTEGER," + 603 Mms.CONTENT_TYPE + " TEXT," + 604 Mms.CONTENT_LOCATION + " TEXT," + 605 Mms.EXPIRY + " INTEGER," + 606 Mms.MESSAGE_CLASS + " TEXT," + 607 Mms.MESSAGE_TYPE + " INTEGER," + 608 Mms.MMS_VERSION + " INTEGER," + 609 Mms.MESSAGE_SIZE + " INTEGER," + 610 Mms.PRIORITY + " INTEGER," + 611 Mms.READ_REPORT + " INTEGER," + 612 Mms.REPORT_ALLOWED + " INTEGER," + 613 Mms.RESPONSE_STATUS + " INTEGER," + 614 Mms.STATUS + " INTEGER," + 615 Mms.TRANSACTION_ID + " TEXT," + 616 Mms.RETRIEVE_STATUS + " INTEGER," + 617 Mms.RETRIEVE_TEXT + " TEXT," + 618 Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," + 619 Mms.READ_STATUS + " INTEGER," + 620 Mms.CONTENT_CLASS + " INTEGER," + 621 Mms.RESPONSE_TEXT + " TEXT," + 622 Mms.DELIVERY_TIME + " INTEGER," + 623 Mms.DELIVERY_REPORT + " INTEGER," + 624 Mms.LOCKED + " INTEGER DEFAULT 0," + 625 Mms.SUBSCRIPTION_ID + " INTEGER DEFAULT " 626 + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 627 Mms.SEEN + " INTEGER DEFAULT 0," + 628 Mms.CREATOR + " TEXT," + 629 Mms.TEXT_ONLY + " INTEGER DEFAULT 0" + 630 ");"); 631 632 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_ADDR + " (" + 633 Addr._ID + " INTEGER PRIMARY KEY," + 634 Addr.MSG_ID + " INTEGER," + 635 Addr.CONTACT_ID + " INTEGER," + 636 Addr.ADDRESS + " TEXT," + 637 Addr.TYPE + " INTEGER," + 638 Addr.CHARSET + " INTEGER);"); 639 640 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PART + " (" + 641 Part._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 642 Part.MSG_ID + " INTEGER," + 643 Part.SEQ + " INTEGER DEFAULT 0," + 644 Part.CONTENT_TYPE + " TEXT," + 645 Part.NAME + " TEXT," + 646 Part.CHARSET + " INTEGER," + 647 Part.CONTENT_DISPOSITION + " TEXT," + 648 Part.FILENAME + " TEXT," + 649 Part.CONTENT_ID + " TEXT," + 650 Part.CONTENT_LOCATION + " TEXT," + 651 Part.CT_START + " INTEGER," + 652 Part.CT_TYPE + " TEXT," + 653 Part._DATA + " TEXT," + 654 Part.TEXT + " TEXT);"); 655 656 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_RATE + " (" + 657 Rate.SENT_TIME + " INTEGER);"); 658 659 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_DRM + " (" + 660 BaseColumns._ID + " INTEGER PRIMARY KEY," + 661 "_data TEXT);"); 662 663 // Restricted view of pdu table, only sent/received messages without wap pushes 664 db.execSQL("CREATE VIEW " + MmsProvider.VIEW_PDU_RESTRICTED + " AS " + 665 "SELECT * FROM " + MmsProvider.TABLE_PDU + " WHERE " + 666 "(" + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_INBOX + 667 " OR " + 668 Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_SENT + ")" + 669 " AND " + 670 "(" + Mms.MESSAGE_TYPE + "!=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + ");"); 671 } 672 673 // Unlike the other trigger-creating functions, this function can be called multiple times 674 // without harm. 675 private void createMmsTriggers(SQLiteDatabase db) { 676 // Cleans up parts when a MM is deleted. 677 db.execSQL("DROP TRIGGER IF EXISTS part_cleanup"); 678 db.execSQL("CREATE TRIGGER part_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 679 "BEGIN " + 680 " DELETE FROM " + MmsProvider.TABLE_PART + 681 " WHERE " + Part.MSG_ID + "=old._id;" + 682 "END;"); 683 684 // Cleans up address info when a MM is deleted. 685 db.execSQL("DROP TRIGGER IF EXISTS addr_cleanup"); 686 db.execSQL("CREATE TRIGGER addr_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 687 "BEGIN " + 688 " DELETE FROM " + MmsProvider.TABLE_ADDR + 689 " WHERE " + Addr.MSG_ID + "=old._id;" + 690 "END;"); 691 692 // Delete obsolete delivery-report, read-report while deleting their 693 // associated Send.req. 694 db.execSQL("DROP TRIGGER IF EXISTS cleanup_delivery_and_read_report"); 695 db.execSQL("CREATE TRIGGER cleanup_delivery_and_read_report " + 696 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 697 "WHEN old." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + " " + 698 "BEGIN " + 699 " DELETE FROM " + MmsProvider.TABLE_PDU + 700 " WHERE (" + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_DELIVERY_IND + 701 " OR " + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_ORIG_IND + 702 ")" + 703 " AND " + Mms.MESSAGE_ID + "=old." + Mms.MESSAGE_ID + "; " + 704 "END;"); 705 706 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_insert_part"); 707 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 708 709 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_update_part"); 710 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 711 712 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_delete_part"); 713 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 714 715 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_update_pdu"); 716 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 717 718 // Delete pending status for a message when it is deleted. 719 db.execSQL("DROP TRIGGER IF EXISTS delete_mms_pending_on_delete"); 720 db.execSQL("CREATE TRIGGER delete_mms_pending_on_delete " + 721 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 722 "BEGIN " + 723 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 724 " WHERE " + PendingMessages.MSG_ID + "=old._id; " + 725 "END;"); 726 727 // When a message is moved out of Outbox, delete its pending status. 728 db.execSQL("DROP TRIGGER IF EXISTS delete_mms_pending_on_update"); 729 db.execSQL("CREATE TRIGGER delete_mms_pending_on_update " + 730 "AFTER UPDATE ON " + MmsProvider.TABLE_PDU + " " + 731 "WHEN old." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 732 " AND new." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 733 "BEGIN " + 734 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 735 " WHERE " + PendingMessages.MSG_ID + "=new._id; " + 736 "END;"); 737 738 // Insert pending status for M-Notification.ind or M-ReadRec.ind 739 // when they are inserted into Inbox/Outbox. 740 db.execSQL("DROP TRIGGER IF EXISTS insert_mms_pending_on_insert"); 741 db.execSQL("CREATE TRIGGER insert_mms_pending_on_insert " + 742 "AFTER INSERT ON pdu " + 743 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + 744 " OR new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_REC_IND + 745 " " + 746 "BEGIN " + 747 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 748 " (" + PendingMessages.PROTO_TYPE + "," + 749 " " + PendingMessages.MSG_ID + "," + 750 " " + PendingMessages.MSG_TYPE + "," + 751 " " + PendingMessages.ERROR_TYPE + "," + 752 " " + PendingMessages.ERROR_CODE + "," + 753 " " + PendingMessages.RETRY_INDEX + "," + 754 " " + PendingMessages.DUE_TIME + ") " + 755 " VALUES " + 756 " (" + MmsSms.MMS_PROTO + "," + 757 " new." + BaseColumns._ID + "," + 758 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 759 "END;"); 760 761 762 // Insert pending status for M-Send.req when it is moved into Outbox. 763 db.execSQL("DROP TRIGGER IF EXISTS insert_mms_pending_on_update"); 764 db.execSQL("CREATE TRIGGER insert_mms_pending_on_update " + 765 "AFTER UPDATE ON pdu " + 766 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + 767 " AND new." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 768 " AND old." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 769 "BEGIN " + 770 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 771 " (" + PendingMessages.PROTO_TYPE + "," + 772 " " + PendingMessages.MSG_ID + "," + 773 " " + PendingMessages.MSG_TYPE + "," + 774 " " + PendingMessages.ERROR_TYPE + "," + 775 " " + PendingMessages.ERROR_CODE + "," + 776 " " + PendingMessages.RETRY_INDEX + "," + 777 " " + PendingMessages.DUE_TIME + ") " + 778 " VALUES " + 779 " (" + MmsSms.MMS_PROTO + "," + 780 " new." + BaseColumns._ID + "," + 781 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 782 "END;"); 783 784 // monitor the mms table 785 db.execSQL("DROP TRIGGER IF EXISTS mms_words_update"); 786 db.execSQL("CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words " + 787 " SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " + 788 " END;"); 789 790 db.execSQL("DROP TRIGGER IF EXISTS mms_words_delete"); 791 db.execSQL("CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM " + 792 " words WHERE source_id = OLD._id AND table_to_use = 2; END;"); 793 794 // Updates threads table whenever a message in pdu is updated. 795 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_date_subject_on_update"); 796 db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" + 797 " UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX + 798 " ON " + MmsProvider.TABLE_PDU + " " + 799 PDU_UPDATE_THREAD_CONSTRAINTS + 800 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 801 802 // Update threads table whenever a message in pdu is deleted 803 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_delete"); 804 db.execSQL("CREATE TRIGGER pdu_update_thread_on_delete " + 805 "AFTER DELETE ON pdu " + 806 "BEGIN " + 807 " UPDATE threads SET " + 808 " date = (strftime('%s','now') * 1000)" + 809 " WHERE threads._id = old." + Mms.THREAD_ID + "; " + 810 UPDATE_THREAD_COUNT_ON_OLD + 811 UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE + 812 "END;"); 813 814 // Updates threads table whenever a message is added to pdu. 815 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_insert"); 816 db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " + 817 MmsProvider.TABLE_PDU + " " + 818 PDU_UPDATE_THREAD_CONSTRAINTS + 819 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 820 821 // Updates threads table whenever a message in pdu is updated. 822 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_read_on_update"); 823 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 824 " UPDATE OF " + Mms.READ + 825 " ON " + MmsProvider.TABLE_PDU + " " + 826 PDU_UPDATE_THREAD_CONSTRAINTS + 827 "BEGIN " + 828 PDU_UPDATE_THREAD_READ_BODY + 829 "END;"); 830 831 // Update the error flag of threads when delete pending message. 832 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_mms"); 833 db.execSQL("CREATE TRIGGER update_threads_error_on_delete_mms " + 834 " BEFORE DELETE ON pdu" + 835 " WHEN OLD._id IN (SELECT DISTINCT msg_id" + 836 " FROM pending_msgs" + 837 " WHERE err_type >= 10) " + 838 "BEGIN " + 839 " UPDATE threads SET error = error - 1" + 840 " WHERE _id = OLD.thread_id; " + 841 "END;"); 842 843 // Update the error flag of threads while moving an MM out of Outbox, 844 // which was failed to be sent permanently. 845 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms"); 846 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 847 " BEFORE UPDATE OF msg_box ON pdu " + 848 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 849 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 850 " FROM pending_msgs" + 851 " WHERE err_type >= 10)) " + 852 "BEGIN " + 853 " UPDATE threads SET error = error - 1" + 854 " WHERE _id = OLD.thread_id; " + 855 "END;"); 856 } 857 858 @VisibleForTesting 859 public static String CREATE_SMS_TABLE_STRING = 860 "CREATE TABLE sms (" + 861 "_id INTEGER PRIMARY KEY," + 862 "thread_id INTEGER," + 863 "address TEXT," + 864 "person INTEGER," + 865 "date INTEGER," + 866 "date_sent INTEGER DEFAULT 0," + 867 "protocol INTEGER," + 868 "read INTEGER DEFAULT 0," + 869 "status INTEGER DEFAULT -1," + // a TP-Status value 870 // or -1 if it 871 // status hasn't 872 // been received 873 "type INTEGER," + 874 "reply_path_present INTEGER," + 875 "subject TEXT," + 876 "body TEXT," + 877 "service_center TEXT," + 878 "locked INTEGER DEFAULT 0," + 879 "sub_id INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 880 "error_code INTEGER DEFAULT 0," + 881 "creator TEXT," + 882 "seen INTEGER DEFAULT 0" + 883 ");"; 884 885 @VisibleForTesting 886 public static String CREATE_ATTACHMENTS_TABLE_STRING = 887 "CREATE TABLE attachments (" + 888 "sms_id INTEGER," + 889 "content_url TEXT," + 890 "offset INTEGER);"; 891 892 private void createSmsTables(SQLiteDatabase db) { 893 // N.B.: Whenever the columns here are changed, the columns in 894 // {@ref MmsSmsProvider} must be changed to match. 895 db.execSQL(CREATE_SMS_TABLE_STRING); 896 897 /** 898 * This table is used by the SMS dispatcher to hold 899 * incomplete partial messages until all the parts arrive. 900 */ 901 db.execSQL("CREATE TABLE raw (" + 902 "_id INTEGER PRIMARY KEY," + 903 "date INTEGER," + 904 "reference_number INTEGER," + // one per full message 905 "count INTEGER," + // the number of parts 906 "sequence INTEGER," + // the part number of this message 907 "destination_port INTEGER," + 908 "address TEXT," + 909 "sub_id INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 910 "pdu TEXT," + // the raw PDU for this part 911 "deleted INTEGER DEFAULT 0," + // bool to indicate if row is deleted 912 "message_body TEXT," + // message body 913 "display_originating_addr TEXT);" 914 // email address if from an email gateway, otherwise same as address 915 ); 916 917 db.execSQL(CREATE_ATTACHMENTS_TABLE_STRING); 918 919 /** 920 * This table is used by the SMS dispatcher to hold pending 921 * delivery status report intents. 922 */ 923 db.execSQL("CREATE TABLE sr_pending (" + 924 "reference_number INTEGER," + 925 "action TEXT," + 926 "data TEXT);"); 927 928 // Restricted view of sms table, only sent/received messages 929 db.execSQL("CREATE VIEW " + SmsProvider.VIEW_SMS_RESTRICTED + " AS " + 930 "SELECT * FROM " + SmsProvider.TABLE_SMS + " WHERE " + 931 Sms.TYPE + "=" + Sms.MESSAGE_TYPE_INBOX + 932 " OR " + 933 Sms.TYPE + "=" + Sms.MESSAGE_TYPE_SENT + ";"); 934 } 935 936 private void createCommonTables(SQLiteDatabase db) { 937 // TODO Ensure that each entry is removed when the last use of 938 // any address equivalent to its address is removed. 939 940 /** 941 * This table maps the first instance seen of any particular 942 * MMS/SMS address to an ID, which is then used as its 943 * canonical representation. If the same address or an 944 * equivalent address (as determined by our Sqlite 945 * PHONE_NUMBERS_EQUAL extension) is seen later, this same ID 946 * will be used. The _id is created with AUTOINCREMENT so it 947 * will never be reused again if a recipient is deleted. 948 */ 949 db.execSQL("CREATE TABLE canonical_addresses (" + 950 "_id INTEGER PRIMARY KEY AUTOINCREMENT," + 951 "address TEXT);"); 952 953 /** 954 * This table maps the subject and an ordered set of recipient 955 * IDs, separated by spaces, to a unique thread ID. The IDs 956 * come from the canonical_addresses table. This works 957 * because messages are considered to be part of the same 958 * thread if they have the same subject (or a null subject) 959 * and the same set of recipients. 960 */ 961 db.execSQL("CREATE TABLE threads (" + 962 Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 963 Threads.DATE + " INTEGER DEFAULT 0," + 964 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 965 Threads.RECIPIENT_IDS + " TEXT," + 966 Threads.SNIPPET + " TEXT," + 967 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 968 Threads.READ + " INTEGER DEFAULT 1," + 969 Threads.ARCHIVED + " INTEGER DEFAULT 0," + 970 Threads.TYPE + " INTEGER DEFAULT 0," + 971 Threads.ERROR + " INTEGER DEFAULT 0," + 972 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 973 974 /** 975 * This table stores the queue of messages to be sent/downloaded. 976 */ 977 db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" + 978 PendingMessages._ID + " INTEGER PRIMARY KEY," + 979 PendingMessages.PROTO_TYPE + " INTEGER," + 980 PendingMessages.MSG_ID + " INTEGER," + 981 PendingMessages.MSG_TYPE + " INTEGER," + 982 PendingMessages.ERROR_TYPE + " INTEGER," + 983 PendingMessages.ERROR_CODE + " INTEGER," + 984 PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," + 985 PendingMessages.DUE_TIME + " INTEGER," + 986 PendingMessages.SUBSCRIPTION_ID + " INTEGER DEFAULT " + 987 SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 988 PendingMessages.LAST_TRY + " INTEGER);"); 989 990 } 991 992 // TODO Check the query plans for these triggers. 993 private void createCommonTriggers(SQLiteDatabase db) { 994 // Updates threads table whenever a message is added to sms. 995 db.execSQL("CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms " + 996 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 997 998 // Updates threads table whenever a message in sms is updated. 999 db.execSQL("CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER" + 1000 " UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE + 1001 " ON sms " + 1002 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 1003 1004 // Updates threads table whenever a message in sms is updated. 1005 db.execSQL("CREATE TRIGGER sms_update_thread_read_on_update AFTER" + 1006 " UPDATE OF " + Sms.READ + 1007 " ON sms " + 1008 "BEGIN " + 1009 SMS_UPDATE_THREAD_READ_BODY + 1010 "END;"); 1011 1012 // As of DATABASE_VERSION 55, we've removed these triggers that delete empty threads. 1013 // These triggers interfere with saving drafts on brand new threads. Instead of 1014 // triggers cleaning up empty threads, the empty threads should be cleaned up by 1015 // an explicit call to delete with Threads.OBSOLETE_THREADS_URI. 1016 1017// // When the last message in a thread is deleted, these 1018// // triggers ensure that the entry for its thread ID is removed 1019// // from the threads table. 1020// db.execSQL("CREATE TRIGGER delete_obsolete_threads_pdu " + 1021// "AFTER DELETE ON pdu " + 1022// "BEGIN " + 1023// " DELETE FROM threads " + 1024// " WHERE " + 1025// " _id = old.thread_id " + 1026// " AND _id NOT IN " + 1027// " (SELECT thread_id FROM sms " + 1028// " UNION SELECT thread_id from pdu); " + 1029// "END;"); 1030// 1031// db.execSQL("CREATE TRIGGER delete_obsolete_threads_when_update_pdu " + 1032// "AFTER UPDATE OF " + Mms.THREAD_ID + " ON pdu " + 1033// "WHEN old." + Mms.THREAD_ID + " != new." + Mms.THREAD_ID + " " + 1034// "BEGIN " + 1035// " DELETE FROM threads " + 1036// " WHERE " + 1037// " _id = old.thread_id " + 1038// " AND _id NOT IN " + 1039// " (SELECT thread_id FROM sms " + 1040// " UNION SELECT thread_id from pdu); " + 1041// "END;"); 1042 1043 // TODO Add triggers for SMS retry-status management. 1044 1045 // Update the error flag of threads when the error type of 1046 // a pending MM is updated. 1047 db.execSQL("CREATE TRIGGER update_threads_error_on_update_mms " + 1048 " AFTER UPDATE OF err_type ON pending_msgs " + 1049 " WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)" + 1050 " OR (OLD.err_type >= 10 AND NEW.err_type < 10) " + 1051 "BEGIN" + 1052 " UPDATE threads SET error = " + 1053 " CASE" + 1054 " WHEN NEW.err_type >= 10 THEN error + 1" + 1055 " ELSE error - 1" + 1056 " END " + 1057 " WHERE _id =" + 1058 " (SELECT DISTINCT thread_id" + 1059 " FROM pdu" + 1060 " WHERE _id = NEW.msg_id); " + 1061 "END;"); 1062 1063 // Update the error flag of threads after a text message was 1064 // failed to send/receive. 1065 db.execSQL("CREATE TRIGGER update_threads_error_on_update_sms " + 1066 " AFTER UPDATE OF type ON sms" + 1067 " WHEN (OLD.type != 5 AND NEW.type = 5)" + 1068 " OR (OLD.type = 5 AND NEW.type != 5) " + 1069 "BEGIN " + 1070 " UPDATE threads SET error = " + 1071 " CASE" + 1072 " WHEN NEW.type = 5 THEN error + 1" + 1073 " ELSE error - 1" + 1074 " END " + 1075 " WHERE _id = NEW.thread_id; " + 1076 "END;"); 1077 } 1078 1079 @Override 1080 public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) { 1081 Log.w(TAG, "Upgrading database from version " + oldVersion 1082 + " to " + currentVersion + "."); 1083 1084 switch (oldVersion) { 1085 case 40: 1086 if (currentVersion <= 40) { 1087 return; 1088 } 1089 1090 db.beginTransaction(); 1091 try { 1092 upgradeDatabaseToVersion41(db); 1093 db.setTransactionSuccessful(); 1094 } catch (Throwable ex) { 1095 Log.e(TAG, ex.getMessage(), ex); 1096 break; 1097 } finally { 1098 db.endTransaction(); 1099 } 1100 // fall through 1101 case 41: 1102 if (currentVersion <= 41) { 1103 return; 1104 } 1105 1106 db.beginTransaction(); 1107 try { 1108 upgradeDatabaseToVersion42(db); 1109 db.setTransactionSuccessful(); 1110 } catch (Throwable ex) { 1111 Log.e(TAG, ex.getMessage(), ex); 1112 break; 1113 } finally { 1114 db.endTransaction(); 1115 } 1116 // fall through 1117 case 42: 1118 if (currentVersion <= 42) { 1119 return; 1120 } 1121 1122 db.beginTransaction(); 1123 try { 1124 upgradeDatabaseToVersion43(db); 1125 db.setTransactionSuccessful(); 1126 } catch (Throwable ex) { 1127 Log.e(TAG, ex.getMessage(), ex); 1128 break; 1129 } finally { 1130 db.endTransaction(); 1131 } 1132 // fall through 1133 case 43: 1134 if (currentVersion <= 43) { 1135 return; 1136 } 1137 1138 db.beginTransaction(); 1139 try { 1140 upgradeDatabaseToVersion44(db); 1141 db.setTransactionSuccessful(); 1142 } catch (Throwable ex) { 1143 Log.e(TAG, ex.getMessage(), ex); 1144 break; 1145 } finally { 1146 db.endTransaction(); 1147 } 1148 // fall through 1149 case 44: 1150 if (currentVersion <= 44) { 1151 return; 1152 } 1153 1154 db.beginTransaction(); 1155 try { 1156 upgradeDatabaseToVersion45(db); 1157 db.setTransactionSuccessful(); 1158 } catch (Throwable ex) { 1159 Log.e(TAG, ex.getMessage(), ex); 1160 break; 1161 } finally { 1162 db.endTransaction(); 1163 } 1164 // fall through 1165 case 45: 1166 if (currentVersion <= 45) { 1167 return; 1168 } 1169 db.beginTransaction(); 1170 try { 1171 upgradeDatabaseToVersion46(db); 1172 db.setTransactionSuccessful(); 1173 } catch (Throwable ex) { 1174 Log.e(TAG, ex.getMessage(), ex); 1175 break; 1176 } finally { 1177 db.endTransaction(); 1178 } 1179 // fall through 1180 case 46: 1181 if (currentVersion <= 46) { 1182 return; 1183 } 1184 1185 db.beginTransaction(); 1186 try { 1187 upgradeDatabaseToVersion47(db); 1188 db.setTransactionSuccessful(); 1189 } catch (Throwable ex) { 1190 Log.e(TAG, ex.getMessage(), ex); 1191 break; 1192 } finally { 1193 db.endTransaction(); 1194 } 1195 // fall through 1196 case 47: 1197 if (currentVersion <= 47) { 1198 return; 1199 } 1200 1201 db.beginTransaction(); 1202 try { 1203 upgradeDatabaseToVersion48(db); 1204 db.setTransactionSuccessful(); 1205 } catch (Throwable ex) { 1206 Log.e(TAG, ex.getMessage(), ex); 1207 break; 1208 } finally { 1209 db.endTransaction(); 1210 } 1211 // fall through 1212 case 48: 1213 if (currentVersion <= 48) { 1214 return; 1215 } 1216 1217 db.beginTransaction(); 1218 try { 1219 createWordsTables(db); 1220 db.setTransactionSuccessful(); 1221 } catch (Throwable ex) { 1222 Log.e(TAG, ex.getMessage(), ex); 1223 break; 1224 } finally { 1225 db.endTransaction(); 1226 } 1227 // fall through 1228 case 49: 1229 if (currentVersion <= 49) { 1230 return; 1231 } 1232 db.beginTransaction(); 1233 try { 1234 createThreadIdIndex(db); 1235 db.setTransactionSuccessful(); 1236 } catch (Throwable ex) { 1237 Log.e(TAG, ex.getMessage(), ex); 1238 break; // force to destroy all old data; 1239 } finally { 1240 db.endTransaction(); 1241 } 1242 // fall through 1243 case 50: 1244 if (currentVersion <= 50) { 1245 return; 1246 } 1247 1248 db.beginTransaction(); 1249 try { 1250 upgradeDatabaseToVersion51(db); 1251 db.setTransactionSuccessful(); 1252 } catch (Throwable ex) { 1253 Log.e(TAG, ex.getMessage(), ex); 1254 break; 1255 } finally { 1256 db.endTransaction(); 1257 } 1258 // fall through 1259 case 51: 1260 if (currentVersion <= 51) { 1261 return; 1262 } 1263 // 52 was adding a new meta_data column, but that was removed. 1264 // fall through 1265 case 52: 1266 if (currentVersion <= 52) { 1267 return; 1268 } 1269 1270 db.beginTransaction(); 1271 try { 1272 upgradeDatabaseToVersion53(db); 1273 db.setTransactionSuccessful(); 1274 } catch (Throwable ex) { 1275 Log.e(TAG, ex.getMessage(), ex); 1276 break; 1277 } finally { 1278 db.endTransaction(); 1279 } 1280 // fall through 1281 case 53: 1282 if (currentVersion <= 53) { 1283 return; 1284 } 1285 1286 db.beginTransaction(); 1287 try { 1288 upgradeDatabaseToVersion54(db); 1289 db.setTransactionSuccessful(); 1290 } catch (Throwable ex) { 1291 Log.e(TAG, ex.getMessage(), ex); 1292 break; 1293 } finally { 1294 db.endTransaction(); 1295 } 1296 // fall through 1297 case 54: 1298 if (currentVersion <= 54) { 1299 return; 1300 } 1301 1302 db.beginTransaction(); 1303 try { 1304 upgradeDatabaseToVersion55(db); 1305 db.setTransactionSuccessful(); 1306 } catch (Throwable ex) { 1307 Log.e(TAG, ex.getMessage(), ex); 1308 break; 1309 } finally { 1310 db.endTransaction(); 1311 } 1312 // fall through 1313 case 55: 1314 if (currentVersion <= 55) { 1315 return; 1316 } 1317 1318 db.beginTransaction(); 1319 try { 1320 upgradeDatabaseToVersion56(db); 1321 db.setTransactionSuccessful(); 1322 } catch (Throwable ex) { 1323 Log.e(TAG, ex.getMessage(), ex); 1324 break; 1325 } finally { 1326 db.endTransaction(); 1327 } 1328 // fall through 1329 case 56: 1330 if (currentVersion <= 56) { 1331 return; 1332 } 1333 1334 db.beginTransaction(); 1335 try { 1336 upgradeDatabaseToVersion57(db); 1337 db.setTransactionSuccessful(); 1338 } catch (Throwable ex) { 1339 Log.e(TAG, ex.getMessage(), ex); 1340 break; 1341 } finally { 1342 db.endTransaction(); 1343 } 1344 // fall through 1345 case 57: 1346 if (currentVersion <= 57) { 1347 return; 1348 } 1349 1350 db.beginTransaction(); 1351 try { 1352 upgradeDatabaseToVersion58(db); 1353 db.setTransactionSuccessful(); 1354 } catch (Throwable ex) { 1355 Log.e(TAG, ex.getMessage(), ex); 1356 break; 1357 } finally { 1358 db.endTransaction(); 1359 } 1360 // fall through 1361 case 58: 1362 if (currentVersion <= 58) { 1363 return; 1364 } 1365 1366 db.beginTransaction(); 1367 try { 1368 upgradeDatabaseToVersion59(db); 1369 db.setTransactionSuccessful(); 1370 } catch (Throwable ex) { 1371 Log.e(TAG, ex.getMessage(), ex); 1372 break; 1373 } finally { 1374 db.endTransaction(); 1375 } 1376 // fall through 1377 case 59: 1378 if (currentVersion <= 59) { 1379 return; 1380 } 1381 1382 db.beginTransaction(); 1383 try { 1384 upgradeDatabaseToVersion60(db); 1385 db.setTransactionSuccessful(); 1386 } catch (Throwable ex) { 1387 Log.e(TAG, ex.getMessage(), ex); 1388 break; 1389 } finally { 1390 db.endTransaction(); 1391 } 1392 // fall through 1393 case 60: 1394 if (currentVersion <= 60) { 1395 return; 1396 } 1397 1398 db.beginTransaction(); 1399 try { 1400 upgradeDatabaseToVersion61(db); 1401 db.setTransactionSuccessful(); 1402 } catch (Throwable ex) { 1403 Log.e(TAG, ex.getMessage(), ex); 1404 break; 1405 } finally { 1406 db.endTransaction(); 1407 } 1408 // fall through 1409 case 61: 1410 if (currentVersion <= 61) { 1411 return; 1412 } 1413 1414 db.beginTransaction(); 1415 try { 1416 upgradeDatabaseToVersion62(db); 1417 db.setTransactionSuccessful(); 1418 } catch (Throwable ex) { 1419 Log.e(TAG, ex.getMessage(), ex); 1420 break; 1421 } finally { 1422 db.endTransaction(); 1423 } 1424 // fall through 1425 case 62: 1426 if (currentVersion <= 62) { 1427 return; 1428 } 1429 1430 db.beginTransaction(); 1431 try { 1432 // upgrade to 63: just add a happy little index. 1433 createThreadIdDateIndex(db); 1434 db.setTransactionSuccessful(); 1435 } catch (Throwable ex) { 1436 Log.e(TAG, ex.getMessage(), ex); 1437 break; 1438 } finally { 1439 db.endTransaction(); 1440 } 1441 // fall through 1442 case 63: 1443 if (currentVersion <= 63) { 1444 return; 1445 } 1446 1447 db.beginTransaction(); 1448 try { 1449 upgradeDatabaseToVersion64(db); 1450 db.setTransactionSuccessful(); 1451 } catch (Throwable ex) { 1452 Log.e(TAG, ex.getMessage(), ex); 1453 break; 1454 } finally { 1455 db.endTransaction(); 1456 } 1457 // fall through 1458 case 64: 1459 if (currentVersion <= 64) { 1460 return; 1461 } 1462 1463 db.beginTransaction(); 1464 try { 1465 upgradeDatabaseToVersion65(db); 1466 db.setTransactionSuccessful(); 1467 } catch (Throwable ex) { 1468 Log.e(TAG, ex.getMessage(), ex); 1469 break; 1470 } finally { 1471 db.endTransaction(); 1472 } 1473 // fall through 1474 case 65: 1475 if (currentVersion <= 65) { 1476 return; 1477 } 1478 1479 db.beginTransaction(); 1480 try { 1481 upgradeDatabaseToVersion66(db); 1482 db.setTransactionSuccessful(); 1483 } catch (Throwable ex) { 1484 Log.e(TAG, ex.getMessage(), ex); 1485 break; 1486 } finally { 1487 db.endTransaction(); 1488 } 1489 1490 return; 1491 } 1492 1493 Log.e(TAG, "Destroying all old data."); 1494 dropAll(db); 1495 onCreate(db); 1496 } 1497 1498 private void dropAll(SQLiteDatabase db) { 1499 // Clean the database out in order to start over from scratch. 1500 // We don't need to drop our triggers here because SQLite automatically 1501 // drops a trigger when its attached database is dropped. 1502 db.execSQL("DROP TABLE IF EXISTS canonical_addresses"); 1503 db.execSQL("DROP TABLE IF EXISTS threads"); 1504 db.execSQL("DROP TABLE IF EXISTS " + MmsSmsProvider.TABLE_PENDING_MSG); 1505 db.execSQL("DROP TABLE IF EXISTS sms"); 1506 db.execSQL("DROP TABLE IF EXISTS raw"); 1507 db.execSQL("DROP TABLE IF EXISTS attachments"); 1508 db.execSQL("DROP TABLE IF EXISTS thread_ids"); 1509 db.execSQL("DROP TABLE IF EXISTS sr_pending"); 1510 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PDU + ";"); 1511 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_ADDR + ";"); 1512 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PART + ";"); 1513 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_RATE + ";"); 1514 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_DRM + ";"); 1515 } 1516 1517 private void upgradeDatabaseToVersion41(SQLiteDatabase db) { 1518 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms"); 1519 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 1520 " BEFORE UPDATE OF msg_box ON pdu " + 1521 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 1522 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 1523 " FROM pending_msgs" + 1524 " WHERE err_type >= 10)) " + 1525 "BEGIN " + 1526 " UPDATE threads SET error = error - 1" + 1527 " WHERE _id = OLD.thread_id; " + 1528 "END;"); 1529 } 1530 1531 private void upgradeDatabaseToVersion42(SQLiteDatabase db) { 1532 db.execSQL("DROP TRIGGER IF EXISTS sms_update_thread_on_delete"); 1533 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_sms"); 1534 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_sms"); 1535 } 1536 1537 private void upgradeDatabaseToVersion43(SQLiteDatabase db) { 1538 // Add 'has_attachment' column to threads table. 1539 db.execSQL("ALTER TABLE threads ADD COLUMN has_attachment INTEGER DEFAULT 0"); 1540 1541 updateThreadsAttachmentColumn(db); 1542 1543 // Add insert and delete triggers for keeping it up to date. 1544 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 1545 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 1546 } 1547 1548 private void upgradeDatabaseToVersion44(SQLiteDatabase db) { 1549 updateThreadsAttachmentColumn(db); 1550 1551 // add the update trigger for keeping the threads up to date. 1552 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1553 } 1554 1555 private void upgradeDatabaseToVersion45(SQLiteDatabase db) { 1556 // Add 'locked' column to sms table. 1557 db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.LOCKED + " INTEGER DEFAULT 0"); 1558 1559 // Add 'locked' column to pdu table. 1560 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.LOCKED + " INTEGER DEFAULT 0"); 1561 } 1562 1563 private void upgradeDatabaseToVersion46(SQLiteDatabase db) { 1564 // add the "text" column for caching inline text (e.g. strings) instead of 1565 // putting them in an external file 1566 db.execSQL("ALTER TABLE part ADD COLUMN " + Part.TEXT + " TEXT"); 1567 1568 Cursor textRows = db.query( 1569 "part", 1570 new String[] { Part._ID, Part._DATA, Part.TEXT}, 1571 "ct = 'text/plain' OR ct == 'application/smil'", 1572 null, 1573 null, 1574 null, 1575 null); 1576 ArrayList<String> filesToDelete = new ArrayList<String>(); 1577 try { 1578 db.beginTransaction(); 1579 if (textRows != null) { 1580 int partDataColumn = textRows.getColumnIndex(Part._DATA); 1581 1582 // This code is imperfect in that we can't guarantee that all the 1583 // backing files get deleted. For example if the system aborts after 1584 // the database is updated but before we complete the process of 1585 // deleting files. 1586 while (textRows.moveToNext()) { 1587 String path = textRows.getString(partDataColumn); 1588 if (path != null) { 1589 try { 1590 InputStream is = new FileInputStream(path); 1591 byte [] data = new byte[is.available()]; 1592 is.read(data); 1593 EncodedStringValue v = new EncodedStringValue(data); 1594 db.execSQL("UPDATE part SET " + Part._DATA + " = NULL, " + 1595 Part.TEXT + " = ?", new String[] { v.getString() }); 1596 is.close(); 1597 filesToDelete.add(path); 1598 } catch (IOException e) { 1599 // TODO Auto-generated catch block 1600 e.printStackTrace(); 1601 } 1602 } 1603 } 1604 } 1605 db.setTransactionSuccessful(); 1606 } finally { 1607 db.endTransaction(); 1608 for (String pathToDelete : filesToDelete) { 1609 try { 1610 (new File(pathToDelete)).delete(); 1611 } catch (SecurityException ex) { 1612 Log.e(TAG, "unable to clean up old mms file for " + pathToDelete, ex); 1613 } 1614 } 1615 if (textRows != null) { 1616 textRows.close(); 1617 } 1618 } 1619 } 1620 1621 private void upgradeDatabaseToVersion47(SQLiteDatabase db) { 1622 updateThreadsAttachmentColumn(db); 1623 1624 // add the update trigger for keeping the threads up to date. 1625 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1626 } 1627 1628 private void upgradeDatabaseToVersion48(SQLiteDatabase db) { 1629 // Add 'error_code' column to sms table. 1630 db.execSQL("ALTER TABLE sms ADD COLUMN error_code INTEGER DEFAULT 0"); 1631 } 1632 1633 private void upgradeDatabaseToVersion51(SQLiteDatabase db) { 1634 db.execSQL("ALTER TABLE sms add COLUMN seen INTEGER DEFAULT 0"); 1635 db.execSQL("ALTER TABLE pdu add COLUMN seen INTEGER DEFAULT 0"); 1636 1637 try { 1638 // update the existing sms and pdu tables so the new "seen" column is the same as 1639 // the "read" column for each row. 1640 ContentValues contentValues = new ContentValues(); 1641 contentValues.put("seen", 1); 1642 int count = db.update("sms", contentValues, "read=1", null); 1643 Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count + 1644 " rows in sms table to have READ=1"); 1645 count = db.update("pdu", contentValues, "read=1", null); 1646 Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count + 1647 " rows in pdu table to have READ=1"); 1648 } catch (Exception ex) { 1649 Log.e(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51 caught ", ex); 1650 } 1651 } 1652 1653 private void upgradeDatabaseToVersion53(SQLiteDatabase db) { 1654 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_read_on_update"); 1655 1656 // Updates threads table whenever a message in pdu is updated. 1657 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 1658 " UPDATE OF " + Mms.READ + 1659 " ON " + MmsProvider.TABLE_PDU + " " + 1660 PDU_UPDATE_THREAD_CONSTRAINTS + 1661 "BEGIN " + 1662 PDU_UPDATE_THREAD_READ_BODY + 1663 "END;"); 1664 } 1665 1666 private void upgradeDatabaseToVersion54(SQLiteDatabase db) { 1667 // Add 'date_sent' column to sms table. 1668 db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.DATE_SENT + " INTEGER DEFAULT 0"); 1669 1670 // Add 'date_sent' column to pdu table. 1671 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.DATE_SENT + " INTEGER DEFAULT 0"); 1672 } 1673 1674 private void upgradeDatabaseToVersion55(SQLiteDatabase db) { 1675 // Drop removed triggers 1676 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_pdu"); 1677 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_when_update_pdu"); 1678 } 1679 1680 private void upgradeDatabaseToVersion56(SQLiteDatabase db) { 1681 // Add 'text_only' column to pdu table. 1682 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU + " ADD COLUMN " + Mms.TEXT_ONLY + 1683 " INTEGER DEFAULT 0"); 1684 } 1685 1686 private void upgradeDatabaseToVersion57(SQLiteDatabase db) { 1687 // Clear out bad rows, those with empty threadIds, from the pdu table. 1688 db.execSQL("DELETE FROM " + MmsProvider.TABLE_PDU + " WHERE " + Mms.THREAD_ID + " IS NULL"); 1689 } 1690 1691 private void upgradeDatabaseToVersion58(SQLiteDatabase db) { 1692 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU + 1693 " ADD COLUMN " + Mms.SUBSCRIPTION_ID 1694 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1695 db.execSQL("ALTER TABLE " + MmsSmsProvider.TABLE_PENDING_MSG 1696 +" ADD COLUMN " + "pending_sub_id" 1697 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1698 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_SMS 1699 + " ADD COLUMN " + Sms.SUBSCRIPTION_ID 1700 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1701 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW 1702 +" ADD COLUMN " + Sms.SUBSCRIPTION_ID 1703 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1704 } 1705 1706 private void upgradeDatabaseToVersion59(SQLiteDatabase db) { 1707 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU +" ADD COLUMN " 1708 + Mms.CREATOR + " TEXT"); 1709 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_SMS +" ADD COLUMN " 1710 + Sms.CREATOR + " TEXT"); 1711 } 1712 1713 private void upgradeDatabaseToVersion60(SQLiteDatabase db) { 1714 db.execSQL("ALTER TABLE " + MmsSmsProvider.TABLE_THREADS +" ADD COLUMN " 1715 + Threads.ARCHIVED + " INTEGER DEFAULT 0"); 1716 } 1717 1718 private void upgradeDatabaseToVersion61(SQLiteDatabase db) { 1719 db.execSQL("CREATE VIEW " + SmsProvider.VIEW_SMS_RESTRICTED + " AS " + 1720 "SELECT * FROM " + SmsProvider.TABLE_SMS + " WHERE " + 1721 Sms.TYPE + "=" + Sms.MESSAGE_TYPE_INBOX + 1722 " OR " + 1723 Sms.TYPE + "=" + Sms.MESSAGE_TYPE_SENT + ";"); 1724 db.execSQL("CREATE VIEW " + MmsProvider.VIEW_PDU_RESTRICTED + " AS " + 1725 "SELECT * FROM " + MmsProvider.TABLE_PDU + " WHERE " + 1726 "(" + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_INBOX + 1727 " OR " + 1728 Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_SENT + ")" + 1729 " AND " + 1730 "(" + Mms.MESSAGE_TYPE + "!=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + ");"); 1731 1732 } 1733 1734 private void upgradeDatabaseToVersion62(SQLiteDatabase db) { 1735 // When a non-FBE device is upgraded to N, all MMS attachment files are moved from 1736 // /data/data to /data/user_de. We need to update the paths stored in the parts table to 1737 // reflect this change. 1738 String newPartsDirPath; 1739 try { 1740 newPartsDirPath = mContext.getDir(MmsProvider.PARTS_DIR_NAME, 0).getCanonicalPath(); 1741 } 1742 catch (IOException e){ 1743 Log.e(TAG, "openFile: check file path failed " + e, e); 1744 return; 1745 } 1746 1747 // The old path of the part files will be something like this: 1748 // /data/data/0/com.android.providers.telephony/app_parts 1749 // The new path of the part files will be something like this: 1750 // /data/user_de/0/com.android.providers.telephony/app_parts 1751 int partsDirIndex = newPartsDirPath.lastIndexOf( 1752 File.separator, newPartsDirPath.lastIndexOf(MmsProvider.PARTS_DIR_NAME)); 1753 String partsDirName = newPartsDirPath.substring(partsDirIndex) + File.separator; 1754 // The query to update the part path will be: 1755 // UPDATE part SET _data = '/data/user_de/0/com.android.providers.telephony' || 1756 // SUBSTR(_data, INSTR(_data, '/app_parts/')) 1757 // WHERE INSTR(_data, '/app_parts/') > 0 1758 db.execSQL("UPDATE " + MmsProvider.TABLE_PART + 1759 " SET " + Part._DATA + " = '" + newPartsDirPath.substring(0, partsDirIndex) + "' ||" + 1760 " SUBSTR(" + Part._DATA + ", INSTR(" + Part._DATA + ", '" + partsDirName + "'))" + 1761 " WHERE INSTR(" + Part._DATA + ", '" + partsDirName + "') > 0"); 1762 } 1763 1764 private void upgradeDatabaseToVersion64(SQLiteDatabase db) { 1765 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW +" ADD COLUMN deleted INTEGER DEFAULT 0"); 1766 } 1767 1768 private void upgradeDatabaseToVersion65(SQLiteDatabase db) { 1769 // aosp and internal code diverged at version 63. Aosp did createThreadIdDateIndex() on 1770 // upgrading to 63, whereas internal (nyc) added column 'deleted'. A device upgrading from 1771 // nyc will have columns deleted and message_body in raw table with version 64, but not 1772 // createThreadIdDateIndex() 1773 try { 1774 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW + " ADD COLUMN message_body TEXT"); 1775 } catch (SQLiteException e) { 1776 Log.w(TAG, "[upgradeDatabaseToVersion65] Exception adding column message_body; " + 1777 "trying createThreadIdDateIndex() instead: " + e); 1778 createThreadIdDateIndex(db); 1779 } 1780 } 1781 1782 private void upgradeDatabaseToVersion66(SQLiteDatabase db) { 1783 try { 1784 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW 1785 + " ADD COLUMN display_originating_addr TEXT"); 1786 } catch (SQLiteException e) { 1787 Log.e(TAG, "[upgradeDatabaseToVersion66] Exception adding column " 1788 + "display_originating_addr; " + e); 1789 } 1790 } 1791 1792 @Override 1793 public synchronized SQLiteDatabase getWritableDatabase() { 1794 SQLiteDatabase db = super.getWritableDatabase(); 1795 1796 if (!sTriedAutoIncrement) { 1797 sTriedAutoIncrement = true; 1798 boolean hasAutoIncrementThreads = hasAutoIncrement(db, MmsSmsProvider.TABLE_THREADS); 1799 boolean hasAutoIncrementAddresses = hasAutoIncrement(db, "canonical_addresses"); 1800 boolean hasAutoIncrementPart = hasAutoIncrement(db, "part"); 1801 boolean hasAutoIncrementPdu = hasAutoIncrement(db, "pdu"); 1802 Log.d(TAG, "[getWritableDatabase] hasAutoIncrementThreads: " + hasAutoIncrementThreads + 1803 " hasAutoIncrementAddresses: " + hasAutoIncrementAddresses + 1804 " hasAutoIncrementPart: " + hasAutoIncrementPart + 1805 " hasAutoIncrementPdu: " + hasAutoIncrementPdu); 1806 boolean autoIncrementThreadsSuccess = true; 1807 boolean autoIncrementAddressesSuccess = true; 1808 boolean autoIncrementPartSuccess = true; 1809 boolean autoIncrementPduSuccess = true; 1810 if (!hasAutoIncrementThreads) { 1811 db.beginTransaction(); 1812 try { 1813 if (false && sFakeLowStorageTest) { 1814 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1815 " - fake exception"); 1816 throw new Exception("FakeLowStorageTest"); 1817 } 1818 upgradeThreadsTableToAutoIncrement(db); // a no-op if already upgraded 1819 db.setTransactionSuccessful(); 1820 } catch (Throwable ex) { 1821 Log.e(TAG, "Failed to add autoIncrement to threads;: " + ex.getMessage(), ex); 1822 autoIncrementThreadsSuccess = false; 1823 } finally { 1824 db.endTransaction(); 1825 } 1826 } 1827 if (!hasAutoIncrementAddresses) { 1828 db.beginTransaction(); 1829 try { 1830 if (false && sFakeLowStorageTest) { 1831 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1832 " - fake exception"); 1833 throw new Exception("FakeLowStorageTest"); 1834 } 1835 upgradeAddressTableToAutoIncrement(db); // a no-op if already upgraded 1836 db.setTransactionSuccessful(); 1837 } catch (Throwable ex) { 1838 Log.e(TAG, "Failed to add autoIncrement to canonical_addresses: " + 1839 ex.getMessage(), ex); 1840 autoIncrementAddressesSuccess = false; 1841 } finally { 1842 db.endTransaction(); 1843 } 1844 } 1845 if (!hasAutoIncrementPart) { 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 upgradePartTableToAutoIncrement(db); // a no-op if already upgraded 1854 db.setTransactionSuccessful(); 1855 } catch (Throwable ex) { 1856 Log.e(TAG, "Failed to add autoIncrement to part: " + 1857 ex.getMessage(), ex); 1858 autoIncrementPartSuccess = false; 1859 } finally { 1860 db.endTransaction(); 1861 } 1862 } 1863 if (!hasAutoIncrementPdu) { 1864 db.beginTransaction(); 1865 try { 1866 if (false && sFakeLowStorageTest) { 1867 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1868 " - fake exception"); 1869 throw new Exception("FakeLowStorageTest"); 1870 } 1871 upgradePduTableToAutoIncrement(db); // a no-op if already upgraded 1872 db.setTransactionSuccessful(); 1873 } catch (Throwable ex) { 1874 Log.e(TAG, "Failed to add autoIncrement to pdu: " + 1875 ex.getMessage(), ex); 1876 autoIncrementPduSuccess = false; 1877 } finally { 1878 db.endTransaction(); 1879 } 1880 } 1881 if (autoIncrementThreadsSuccess && 1882 autoIncrementAddressesSuccess && 1883 autoIncrementPartSuccess && 1884 autoIncrementPduSuccess) { 1885 if (mLowStorageMonitor != null) { 1886 // We've already updated the database. This receiver is no longer necessary. 1887 Log.d(TAG, "Unregistering mLowStorageMonitor - we've upgraded"); 1888 mContext.unregisterReceiver(mLowStorageMonitor); 1889 mLowStorageMonitor = null; 1890 } 1891 } else { 1892 if (sFakeLowStorageTest) { 1893 sFakeLowStorageTest = false; 1894 } 1895 1896 // We failed, perhaps because of low storage. Turn on a receiver to watch for 1897 // storage space. 1898 if (mLowStorageMonitor == null) { 1899 Log.d(TAG, "[getWritableDatabase] turning on storage monitor"); 1900 mLowStorageMonitor = new LowStorageMonitor(); 1901 IntentFilter intentFilter = new IntentFilter(); 1902 intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_LOW); 1903 intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_OK); 1904 mContext.registerReceiver(mLowStorageMonitor, intentFilter); 1905 } 1906 } 1907 } 1908 return db; 1909 } 1910 1911 // Determine whether a particular table has AUTOINCREMENT in its schema. 1912 private boolean hasAutoIncrement(SQLiteDatabase db, String tableName) { 1913 boolean result = false; 1914 String query = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" + 1915 tableName + "'"; 1916 Cursor c = db.rawQuery(query, null); 1917 if (c != null) { 1918 try { 1919 if (c.moveToFirst()) { 1920 String schema = c.getString(0); 1921 result = schema != null ? schema.contains("AUTOINCREMENT") : false; 1922 Log.d(TAG, "[MmsSmsDb] tableName: " + tableName + " hasAutoIncrement: " + 1923 schema + " result: " + result); 1924 } 1925 } finally { 1926 c.close(); 1927 } 1928 } 1929 return result; 1930 } 1931 1932 // upgradeThreadsTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1933 // the threads table. This could fail if the user has a lot of conversations and not enough 1934 // storage to make a copy of the threads table. That's ok. This upgrade is optional. It'll 1935 // be called again next time the device is rebooted. 1936 private void upgradeThreadsTableToAutoIncrement(SQLiteDatabase db) { 1937 if (hasAutoIncrement(db, MmsSmsProvider.TABLE_THREADS)) { 1938 Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: already upgraded"); 1939 return; 1940 } 1941 Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: upgrading"); 1942 1943 // Make the _id of the threads table autoincrement so we never re-use thread ids 1944 // Have to create a new temp threads table. Copy all the info from the old table. 1945 // Drop the old table and rename the new table to that of the old. 1946 db.execSQL("CREATE TABLE threads_temp (" + 1947 Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1948 Threads.DATE + " INTEGER DEFAULT 0," + 1949 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 1950 Threads.RECIPIENT_IDS + " TEXT," + 1951 Threads.SNIPPET + " TEXT," + 1952 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 1953 Threads.READ + " INTEGER DEFAULT 1," + 1954 Threads.TYPE + " INTEGER DEFAULT 0," + 1955 Threads.ERROR + " INTEGER DEFAULT 0," + 1956 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 1957 1958 db.execSQL("INSERT INTO threads_temp SELECT * from threads;"); 1959 db.execSQL("DROP TABLE threads;"); 1960 db.execSQL("ALTER TABLE threads_temp RENAME TO threads;"); 1961 } 1962 1963 // upgradeAddressTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1964 // the canonical_addresses table. This could fail if the user has a lot of people they've 1965 // messaged with and not enough storage to make a copy of the canonical_addresses table. 1966 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 1967 private void upgradeAddressTableToAutoIncrement(SQLiteDatabase db) { 1968 if (hasAutoIncrement(db, "canonical_addresses")) { 1969 Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: already upgraded"); 1970 return; 1971 } 1972 Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: upgrading"); 1973 1974 // Make the _id of the canonical_addresses table autoincrement so we never re-use ids 1975 // Have to create a new temp canonical_addresses table. Copy all the info from the old 1976 // table. Drop the old table and rename the new table to that of the old. 1977 db.execSQL("CREATE TABLE canonical_addresses_temp (_id INTEGER PRIMARY KEY AUTOINCREMENT," + 1978 "address TEXT);"); 1979 1980 db.execSQL("INSERT INTO canonical_addresses_temp SELECT * from canonical_addresses;"); 1981 db.execSQL("DROP TABLE canonical_addresses;"); 1982 db.execSQL("ALTER TABLE canonical_addresses_temp RENAME TO canonical_addresses;"); 1983 } 1984 1985 // upgradePartTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1986 // the part table. This could fail if the user has a lot of sound/video/picture attachments 1987 // and not enough storage to make a copy of the part table. 1988 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 1989 private void upgradePartTableToAutoIncrement(SQLiteDatabase db) { 1990 if (hasAutoIncrement(db, "part")) { 1991 Log.d(TAG, "[MmsSmsDb] upgradePartTableToAutoIncrement: already upgraded"); 1992 return; 1993 } 1994 Log.d(TAG, "[MmsSmsDb] upgradePartTableToAutoIncrement: upgrading"); 1995 1996 // Make the _id of the part table autoincrement so we never re-use ids 1997 // Have to create a new temp part table. Copy all the info from the old 1998 // table. Drop the old table and rename the new table to that of the old. 1999 db.execSQL("CREATE TABLE part_temp (" + 2000 Part._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 2001 Part.MSG_ID + " INTEGER," + 2002 Part.SEQ + " INTEGER DEFAULT 0," + 2003 Part.CONTENT_TYPE + " TEXT," + 2004 Part.NAME + " TEXT," + 2005 Part.CHARSET + " INTEGER," + 2006 Part.CONTENT_DISPOSITION + " TEXT," + 2007 Part.FILENAME + " TEXT," + 2008 Part.CONTENT_ID + " TEXT," + 2009 Part.CONTENT_LOCATION + " TEXT," + 2010 Part.CT_START + " INTEGER," + 2011 Part.CT_TYPE + " TEXT," + 2012 Part._DATA + " TEXT," + 2013 Part.TEXT + " TEXT);"); 2014 2015 db.execSQL("INSERT INTO part_temp SELECT * from part;"); 2016 db.execSQL("DROP TABLE part;"); 2017 db.execSQL("ALTER TABLE part_temp RENAME TO part;"); 2018 2019 // part-related triggers get tossed when the part table is dropped -- rebuild them. 2020 createMmsTriggers(db); 2021 } 2022 2023 // upgradePduTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 2024 // the pdu table. This could fail if the user has a lot of mms messages 2025 // and not enough storage to make a copy of the pdu table. 2026 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 2027 private void upgradePduTableToAutoIncrement(SQLiteDatabase db) { 2028 if (hasAutoIncrement(db, "pdu")) { 2029 Log.d(TAG, "[MmsSmsDb] upgradePduTableToAutoIncrement: already upgraded"); 2030 return; 2031 } 2032 Log.d(TAG, "[MmsSmsDb] upgradePduTableToAutoIncrement: upgrading"); 2033 2034 // Make the _id of the part table autoincrement so we never re-use ids 2035 // Have to create a new temp part table. Copy all the info from the old 2036 // table. Drop the old table and rename the new table to that of the old. 2037 db.execSQL("CREATE TABLE pdu_temp (" + 2038 Mms._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 2039 Mms.THREAD_ID + " INTEGER," + 2040 Mms.DATE + " INTEGER," + 2041 Mms.DATE_SENT + " INTEGER DEFAULT 0," + 2042 Mms.MESSAGE_BOX + " INTEGER," + 2043 Mms.READ + " INTEGER DEFAULT 0," + 2044 Mms.MESSAGE_ID + " TEXT," + 2045 Mms.SUBJECT + " TEXT," + 2046 Mms.SUBJECT_CHARSET + " INTEGER," + 2047 Mms.CONTENT_TYPE + " TEXT," + 2048 Mms.CONTENT_LOCATION + " TEXT," + 2049 Mms.EXPIRY + " INTEGER," + 2050 Mms.MESSAGE_CLASS + " TEXT," + 2051 Mms.MESSAGE_TYPE + " INTEGER," + 2052 Mms.MMS_VERSION + " INTEGER," + 2053 Mms.MESSAGE_SIZE + " INTEGER," + 2054 Mms.PRIORITY + " INTEGER," + 2055 Mms.READ_REPORT + " INTEGER," + 2056 Mms.REPORT_ALLOWED + " INTEGER," + 2057 Mms.RESPONSE_STATUS + " INTEGER," + 2058 Mms.STATUS + " INTEGER," + 2059 Mms.TRANSACTION_ID + " TEXT," + 2060 Mms.RETRIEVE_STATUS + " INTEGER," + 2061 Mms.RETRIEVE_TEXT + " TEXT," + 2062 Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," + 2063 Mms.READ_STATUS + " INTEGER," + 2064 Mms.CONTENT_CLASS + " INTEGER," + 2065 Mms.RESPONSE_TEXT + " TEXT," + 2066 Mms.DELIVERY_TIME + " INTEGER," + 2067 Mms.DELIVERY_REPORT + " INTEGER," + 2068 Mms.LOCKED + " INTEGER DEFAULT 0," + 2069 Mms.SUBSCRIPTION_ID + " INTEGER DEFAULT " 2070 + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 2071 Mms.SEEN + " INTEGER DEFAULT 0," + 2072 Mms.TEXT_ONLY + " INTEGER DEFAULT 0" + 2073 ");"); 2074 2075 db.execSQL("INSERT INTO pdu_temp SELECT * from pdu;"); 2076 db.execSQL("DROP TABLE pdu;"); 2077 db.execSQL("ALTER TABLE pdu_temp RENAME TO pdu;"); 2078 2079 // pdu-related triggers get tossed when the part table is dropped -- rebuild them. 2080 createMmsTriggers(db); 2081 } 2082 2083 private class LowStorageMonitor extends BroadcastReceiver { 2084 2085 public LowStorageMonitor() { 2086 } 2087 2088 public void onReceive(Context context, Intent intent) { 2089 String action = intent.getAction(); 2090 2091 Log.d(TAG, "[LowStorageMonitor] onReceive intent " + action); 2092 2093 if (Intent.ACTION_DEVICE_STORAGE_OK.equals(action)) { 2094 sTriedAutoIncrement = false; // try to upgrade on the next getWriteableDatabase 2095 } 2096 } 2097 } 2098 2099 private void updateThreadsAttachmentColumn(SQLiteDatabase db) { 2100 // Set the values of that column correctly based on the current 2101 // contents of the database. 2102 db.execSQL("UPDATE threads SET has_attachment=1 WHERE _id IN " + 2103 " (SELECT DISTINCT pdu.thread_id FROM part " + 2104 " JOIN pdu ON pdu._id=part.mid " + 2105 " WHERE part.ct != 'text/plain' AND part.ct != 'application/smil')"); 2106 } 2107} 2108