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