MmsSmsDatabaseHelper.java revision 94489f821be2401e4a9045901d9112d42273b362
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 java.io.IOException; 20import java.io.InputStream; 21import java.io.FileInputStream; 22import java.io.File; 23import java.util.ArrayList; 24 25import android.content.ContentValues; 26import android.content.Context; 27import android.database.Cursor; 28import android.database.sqlite.SQLiteDatabase; 29import android.database.sqlite.SQLiteOpenHelper; 30import android.provider.BaseColumns; 31import android.provider.Telephony; 32import android.provider.Telephony.Mms; 33import android.provider.Telephony.MmsSms; 34import android.provider.Telephony.Sms; 35import android.provider.Telephony.Threads; 36import android.provider.Telephony.Mms.Addr; 37import android.provider.Telephony.Mms.Part; 38import android.provider.Telephony.Mms.Rate; 39import android.provider.Telephony.MmsSms.PendingMessages; 40import android.util.Log; 41 42import com.android.mmscommon.EncodedStringValue; 43import com.android.mmscommon.PduHeaders; 44 45public class MmsSmsDatabaseHelper extends SQLiteOpenHelper { 46 private static final String TAG = "MmsSmsDatabaseHelper"; 47 48 private static final String SMS_UPDATE_THREAD_READ_BODY = 49 " UPDATE threads SET read = " + 50 " CASE (SELECT COUNT(*)" + 51 " FROM sms" + 52 " WHERE " + Sms.READ + " = 0" + 53 " AND " + Sms.THREAD_ID + " = threads._id)" + 54 " WHEN 0 THEN 1" + 55 " ELSE 0" + 56 " END" + 57 " WHERE threads._id = new." + Sms.THREAD_ID + "; "; 58 59 private static final String UPDATE_THREAD_COUNT_ON_NEW = 60 " UPDATE threads SET message_count = " + 61 " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " + 62 " ON threads._id = " + Sms.THREAD_ID + 63 " WHERE " + Sms.THREAD_ID + " = new.thread_id" + 64 " AND sms." + Sms.TYPE + " != 3) + " + 65 " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " + 66 " ON threads._id = " + Mms.THREAD_ID + 67 " WHERE " + Mms.THREAD_ID + " = new.thread_id" + 68 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 69 " AND " + Mms.MESSAGE_BOX + " != 3) " + 70 " WHERE threads._id = new.thread_id; "; 71 72 private static final String UPDATE_THREAD_COUNT_ON_OLD = 73 " UPDATE threads SET message_count = " + 74 " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " + 75 " ON threads._id = " + Sms.THREAD_ID + 76 " WHERE " + Sms.THREAD_ID + " = old.thread_id" + 77 " AND sms." + Sms.TYPE + " != 3) + " + 78 " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " + 79 " ON threads._id = " + Mms.THREAD_ID + 80 " WHERE " + Mms.THREAD_ID + " = old.thread_id" + 81 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 82 " AND " + Mms.MESSAGE_BOX + " != 3) " + 83 " WHERE threads._id = old.thread_id; "; 84 85 private static final String SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE = 86 "BEGIN" + 87 " UPDATE threads SET" + 88 " date = (strftime('%s','now') * 1000), " + 89 " snippet = new." + Sms.BODY + ", " + 90 " snippet_cs = 0" + 91 " WHERE threads._id = new." + Sms.THREAD_ID + "; " + 92 UPDATE_THREAD_COUNT_ON_NEW + 93 SMS_UPDATE_THREAD_READ_BODY + 94 "END;"; 95 96 private static final String PDU_UPDATE_THREAD_CONSTRAINTS = 97 " WHEN new." + Mms.MESSAGE_TYPE + "=" + 98 PduHeaders.MESSAGE_TYPE_RETRIEVE_CONF + 99 " OR new." + Mms.MESSAGE_TYPE + "=" + 100 PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + 101 " OR new." + Mms.MESSAGE_TYPE + "=" + 102 PduHeaders.MESSAGE_TYPE_SEND_REQ + " "; 103 104 private static final String PDU_UPDATE_THREAD_READ_BODY = 105 " UPDATE threads SET read = " + 106 " CASE (SELECT COUNT(*)" + 107 " FROM " + MmsProvider.TABLE_PDU + 108 " WHERE " + Mms.READ + " = 0" + 109 " AND " + Mms.THREAD_ID + " = threads._id)" + 110 " WHEN 0 THEN 1" + 111 " ELSE 0" + 112 " END" + 113 " WHERE threads._id = new." + Mms.THREAD_ID + "; "; 114 115 private static final String PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE = 116 "BEGIN" + 117 " UPDATE threads SET" + 118 " date = (strftime('%s','now') * 1000), " + 119 " snippet = new." + Mms.SUBJECT + ", " + 120 " snippet_cs = new." + Mms.SUBJECT_CHARSET + 121 " WHERE threads._id = new." + Mms.THREAD_ID + "; " + 122 UPDATE_THREAD_COUNT_ON_NEW + 123 PDU_UPDATE_THREAD_READ_BODY + 124 "END;"; 125 126 private static final String UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE = 127 " UPDATE threads SET snippet = " + 128 " (SELECT snippet FROM" + 129 " (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" + 130 " UNION SELECT date, body AS snippet, thread_id FROM sms)" + 131 " WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " + 132 " WHERE threads._id = OLD.thread_id; " + 133 " UPDATE threads SET snippet_cs = " + 134 " (SELECT snippet_cs FROM" + 135 " (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" + 136 " UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" + 137 " WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " + 138 " WHERE threads._id = OLD.thread_id; "; 139 140 141 // When a part is inserted, if it is not text/plain or application/smil 142 // (which both can exist with text-only MMSes), then there is an attachment. 143 // Set has_attachment=1 in the threads table for the thread in question. 144 private static final String PART_UPDATE_THREADS_ON_INSERT_TRIGGER = 145 "CREATE TRIGGER update_threads_on_insert_part " + 146 " AFTER INSERT ON part " + 147 " WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " + 148 " BEGIN " + 149 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 150 " (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " + 151 " WHERE part._id=new._id LIMIT 1); " + 152 " END"; 153 154 // When the 'mid' column in the part table is updated, we need to run the trigger to update 155 // the threads table's has_attachment column, if the part is an attachment. 156 private static final String PART_UPDATE_THREADS_ON_UPDATE_TRIGGER = 157 "CREATE TRIGGER update_threads_on_update_part " + 158 " AFTER UPDATE of " + Part.MSG_ID + " ON part " + 159 " WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " + 160 " BEGIN " + 161 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 162 " (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " + 163 " WHERE part._id=new._id LIMIT 1); " + 164 " END"; 165 166 167 // When a part is deleted (with the same non-text/SMIL constraint as when 168 // we set has_attachment), update the threads table for all threads. 169 // Unfortunately we cannot update only the thread that the part was 170 // attached to, as it is possible that the part has been orphaned and 171 // the message it was attached to is already gone. 172 private static final String PART_UPDATE_THREADS_ON_DELETE_TRIGGER = 173 "CREATE TRIGGER update_threads_on_delete_part " + 174 " AFTER DELETE ON part " + 175 " WHEN old.ct != 'text/plain' AND old.ct != 'application/smil' " + 176 " BEGIN " + 177 " UPDATE threads SET has_attachment = " + 178 " CASE " + 179 " (SELECT COUNT(*) FROM part JOIN pdu " + 180 " WHERE pdu.thread_id = threads._id " + 181 " AND part.ct != 'text/plain' AND part.ct != 'application/smil' " + 182 " AND part.mid = pdu._id)" + 183 " WHEN 0 THEN 0 " + 184 " ELSE 1 " + 185 " END; " + 186 " END"; 187 188 // When the 'thread_id' column in the pdu table is updated, we need to run the trigger to update 189 // the threads table's has_attachment column, if the message has an attachment in 'part' table 190 private static final String PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER = 191 "CREATE TRIGGER update_threads_on_update_pdu " + 192 " AFTER UPDATE of thread_id ON pdu " + 193 " BEGIN " + 194 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 195 " (SELECT pdu.thread_id FROM part JOIN pdu " + 196 " WHERE part.ct != 'text/plain' AND part.ct != 'application/smil' " + 197 " AND part.mid = pdu._id);" + 198 " END"; 199 200 private static MmsSmsDatabaseHelper mInstance = null; 201 202 static final String DATABASE_NAME = "mmssms.db"; 203 static final int DATABASE_VERSION = 49; 204 205 private MmsSmsDatabaseHelper(Context context) { 206 super(context, DATABASE_NAME, null, DATABASE_VERSION); 207 } 208 209 /** 210 * Return a singleton helper for the combined MMS and SMS 211 * database. 212 */ 213 /* package */ static synchronized MmsSmsDatabaseHelper getInstance(Context context) { 214 if (mInstance == null) { 215 mInstance = new MmsSmsDatabaseHelper(context); 216 } 217 return mInstance; 218 } 219 220 public static void updateThread(SQLiteDatabase db, long thread_id) { 221 if (thread_id < 0) { 222 updateAllThreads(db, null, null); 223 return; 224 } 225 226 // Delete the row for this thread in the threads table if 227 // there are no more messages attached to it in either 228 // the sms or pdu tables. 229 int rows = db.delete("threads", 230 "_id = ? AND _id NOT IN" + 231 " (SELECT thread_id FROM sms " + 232 " UNION SELECT thread_id FROM pdu)", 233 new String[] { String.valueOf(thread_id) }); 234 if (rows > 0) { 235 // If this deleted a row, we have no more work to do. 236 return; 237 } 238 // Update the message count in the threads table as the sum 239 // of all messages in both the sms and pdu tables. 240 db.execSQL( 241 " UPDATE threads SET message_count = " + 242 " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " + 243 " ON threads._id = " + Sms.THREAD_ID + 244 " WHERE " + Sms.THREAD_ID + " = " + thread_id + 245 " AND sms." + Sms.TYPE + " != 3) + " + 246 " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " + 247 " ON threads._id = " + Mms.THREAD_ID + 248 " WHERE " + Mms.THREAD_ID + " = " + thread_id + 249 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 250 " AND " + Mms.MESSAGE_BOX + " != 3) " + 251 " WHERE threads._id = " + thread_id + ";"); 252 253 // Update the date and the snippet (and its character set) in 254 // the threads table to be that of the most recent message in 255 // the thread. 256 db.execSQL( 257 " UPDATE threads" + 258 " SET" + 259 " date =" + 260 " (SELECT date FROM" + 261 " (SELECT date * 1000 AS date, thread_id FROM pdu" + 262 " UNION SELECT date, thread_id FROM sms)" + 263 " WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," + 264 " snippet =" + 265 " (SELECT snippet FROM" + 266 " (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" + 267 " UNION SELECT date, body AS snippet, thread_id FROM sms)" + 268 " WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," + 269 " snippet_cs =" + 270 " (SELECT snippet_cs FROM" + 271 " (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" + 272 " UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" + 273 " WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)" + 274 " WHERE threads._id = " + thread_id + ";"); 275 276 // Update the error column of the thread to indicate if there 277 // are any messages in it that have failed to send. 278 db.execSQL( 279 "UPDATE threads SET error =" + 280 " (SELECT COUNT(*) FROM sms WHERE type=5" + 281 " AND thread_id = " + thread_id + " LIMIT 1)" + 282 " WHERE threads._id = " + thread_id + ";"); 283 } 284 285 public static void updateAllThreads(SQLiteDatabase db, String where, String[] whereArgs) { 286 if (where == null) { 287 where = ""; 288 } else { 289 where = "WHERE (" + where + ")"; 290 } 291 String query = "SELECT _id FROM threads WHERE _id IN " + 292 "(SELECT DISTINCT thread_id FROM sms " + where + ")"; 293 Cursor c = db.rawQuery(query, whereArgs); 294 if (c != null) { 295 while (c.moveToNext()) { 296 updateThread(db, c.getInt(0)); 297 } 298 c.close(); 299 } 300 // remove orphaned threads 301 db.delete("threads", 302 "_id NOT IN (SELECT DISTINCT thread_id FROM sms " + 303 "UNION SELECT DISTINCT thread_id FROM pdu)", null); 304 } 305 306 public static int deleteOneSms(SQLiteDatabase db, int message_id) { 307 int thread_id = -1; 308 // Find the thread ID that the specified SMS belongs to. 309 Cursor c = db.query("sms", new String[] { "thread_id" }, 310 "_id=" + message_id, null, null, null, null); 311 if (c != null) { 312 if (c.moveToFirst()) { 313 thread_id = c.getInt(0); 314 } 315 c.close(); 316 } 317 318 // Delete the specified message. 319 int rows = db.delete("sms", "_id=" + message_id, null); 320 if (thread_id > 0) { 321 // Update its thread. 322 updateThread(db, thread_id); 323 } 324 return rows; 325 } 326 327 @Override 328 public void onCreate(SQLiteDatabase db) { 329 createMmsTables(db); 330 createSmsTables(db); 331 createCommonTables(db); 332 createCommonTriggers(db); 333 createMmsTriggers(db); 334 createWordsTables(db); 335 } 336 337 // When upgrading the database we need to populate the words 338 // table with the rows out of sms and part. 339 private void populateWordsTable(SQLiteDatabase db) { 340 final String TABLE_WORDS = "words"; 341 { 342 Cursor smsRows = db.query( 343 "sms", 344 new String[] { Sms._ID, Sms.BODY }, 345 null, 346 null, 347 null, 348 null, 349 null); 350 try { 351 if (smsRows != null) { 352 smsRows.moveToPosition(-1); 353 ContentValues cv = new ContentValues(); 354 while (smsRows.moveToNext()) { 355 cv.clear(); 356 357 long id = smsRows.getLong(0); // 0 for Sms._ID 358 String body = smsRows.getString(1); // 1 for Sms.BODY 359 360 cv.put(Telephony.MmsSms.WordsTable.ID, id); 361 cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body); 362 cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id); 363 cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1); 364 db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv); 365 } 366 } 367 } finally { 368 if (smsRows != null) { 369 smsRows.close(); 370 } 371 } 372 } 373 374 { 375 Cursor mmsRows = db.query( 376 "part", 377 new String[] { Part._ID, Part.TEXT }, 378 "ct = 'text/plain'", 379 null, 380 null, 381 null, 382 null); 383 try { 384 if (mmsRows != null) { 385 mmsRows.moveToPosition(-1); 386 ContentValues cv = new ContentValues(); 387 while (mmsRows.moveToNext()) { 388 cv.clear(); 389 390 long id = mmsRows.getLong(0); // 0 for Part._ID 391 String body = mmsRows.getString(1); // 1 for Part.TEXT 392 393 cv.put(Telephony.MmsSms.WordsTable.ID, id); 394 cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body); 395 cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id); 396 cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1); 397 db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv); 398 } 399 } 400 } finally { 401 if (mmsRows != null) { 402 mmsRows.close(); 403 } 404 } 405 } 406 } 407 408 private void createWordsTables(SQLiteDatabase db) { 409 try { 410 db.execSQL("CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER, table_to_use INTEGER);"); 411 412 // monitor the sms table 413 // NOTE don't handle inserts using a trigger because it has an unwanted 414 // side effect: the value returned for the last row ends up being the 415 // id of one of the trigger insert not the original row insert. 416 // Handle inserts manually in the provider. 417 db.execSQL("CREATE TRIGGER sms_words_update AFTER UPDATE ON sms BEGIN UPDATE words " + 418 " SET index_text = NEW.body WHERE (source_id=NEW._id AND table_to_use=1); " + 419 " END;"); 420 db.execSQL("CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM " + 421 " words WHERE source_id = OLD._id AND table_to_use = 1; END;"); 422 423 // monitor the mms table 424 db.execSQL("CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words " + 425 " SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " + 426 " END;"); 427 db.execSQL("CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM " + 428 " words WHERE source_id = OLD._id AND table_to_use = 2; END;"); 429 430 populateWordsTable(db); 431 } catch (Exception ex) { 432 Log.e(TAG, "got exception creating words table: " + ex.toString()); 433 } 434 } 435 436 private void createMmsTables(SQLiteDatabase db) { 437 // N.B.: Whenever the columns here are changed, the columns in 438 // {@ref MmsSmsProvider} must be changed to match. 439 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PDU + " (" + 440 Mms._ID + " INTEGER PRIMARY KEY," + 441 Mms.THREAD_ID + " INTEGER," + 442 Mms.DATE + " INTEGER," + 443 Mms.MESSAGE_BOX + " INTEGER," + 444 Mms.READ + " INTEGER DEFAULT 0," + 445 Mms.MESSAGE_ID + " TEXT," + 446 Mms.SUBJECT + " TEXT," + 447 Mms.SUBJECT_CHARSET + " INTEGER," + 448 Mms.CONTENT_TYPE + " TEXT," + 449 Mms.CONTENT_LOCATION + " TEXT," + 450 Mms.EXPIRY + " INTEGER," + 451 Mms.MESSAGE_CLASS + " TEXT," + 452 Mms.MESSAGE_TYPE + " INTEGER," + 453 Mms.MMS_VERSION + " INTEGER," + 454 Mms.MESSAGE_SIZE + " INTEGER," + 455 Mms.PRIORITY + " INTEGER," + 456 Mms.READ_REPORT + " INTEGER," + 457 Mms.REPORT_ALLOWED + " INTEGER," + 458 Mms.RESPONSE_STATUS + " INTEGER," + 459 Mms.STATUS + " INTEGER," + 460 Mms.TRANSACTION_ID + " TEXT," + 461 Mms.RETRIEVE_STATUS + " INTEGER," + 462 Mms.RETRIEVE_TEXT + " TEXT," + 463 Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," + 464 Mms.READ_STATUS + " INTEGER," + 465 Mms.CONTENT_CLASS + " INTEGER," + 466 Mms.RESPONSE_TEXT + " TEXT," + 467 Mms.DELIVERY_TIME + " INTEGER," + 468 Mms.DELIVERY_REPORT + " INTEGER," + 469 Mms.LOCKED + " INTEGER DEFAULT 0" + 470 ");"); 471 472 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_ADDR + " (" + 473 Addr._ID + " INTEGER PRIMARY KEY," + 474 Addr.MSG_ID + " INTEGER," + 475 Addr.CONTACT_ID + " INTEGER," + 476 Addr.ADDRESS + " TEXT," + 477 Addr.TYPE + " INTEGER," + 478 Addr.CHARSET + " INTEGER);"); 479 480 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PART + " (" + 481 Part._ID + " INTEGER PRIMARY KEY," + 482 Part.MSG_ID + " INTEGER," + 483 Part.SEQ + " INTEGER DEFAULT 0," + 484 Part.CONTENT_TYPE + " TEXT," + 485 Part.NAME + " TEXT," + 486 Part.CHARSET + " INTEGER," + 487 Part.CONTENT_DISPOSITION + " TEXT," + 488 Part.FILENAME + " TEXT," + 489 Part.CONTENT_ID + " TEXT," + 490 Part.CONTENT_LOCATION + " TEXT," + 491 Part.CT_START + " INTEGER," + 492 Part.CT_TYPE + " TEXT," + 493 Part._DATA + " TEXT," + 494 Part.TEXT + " TEXT);"); 495 496 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_RATE + " (" + 497 Rate.SENT_TIME + " INTEGER);"); 498 499 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_DRM + " (" + 500 BaseColumns._ID + " INTEGER PRIMARY KEY," + 501 "_data TEXT);"); 502 } 503 504 private void createMmsTriggers(SQLiteDatabase db) { 505 // Cleans up parts when a MM is deleted. 506 db.execSQL("CREATE TRIGGER part_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 507 "BEGIN " + 508 " DELETE FROM " + MmsProvider.TABLE_PART + 509 " WHERE " + Part.MSG_ID + "=old._id;" + 510 "END;"); 511 512 // Cleans up address info when a MM is deleted. 513 db.execSQL("CREATE TRIGGER addr_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 514 "BEGIN " + 515 " DELETE FROM " + MmsProvider.TABLE_ADDR + 516 " WHERE " + Addr.MSG_ID + "=old._id;" + 517 "END;"); 518 519 // Delete obsolete delivery-report, read-report while deleting their 520 // associated Send.req. 521 db.execSQL("CREATE TRIGGER cleanup_delivery_and_read_report " + 522 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 523 "WHEN old." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + " " + 524 "BEGIN " + 525 " DELETE FROM " + MmsProvider.TABLE_PDU + 526 " WHERE (" + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_DELIVERY_IND + 527 " OR " + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_ORIG_IND + 528 ")" + 529 " AND " + Mms.MESSAGE_ID + "=old." + Mms.MESSAGE_ID + "; " + 530 "END;"); 531 532 // Update threads table to indicate whether attachments exist when 533 // parts are inserted or deleted. 534 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 535 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 536 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 537 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 538 } 539 540 private void createSmsTables(SQLiteDatabase db) { 541 // N.B.: Whenever the columns here are changed, the columns in 542 // {@ref MmsSmsProvider} must be changed to match. 543 db.execSQL("CREATE TABLE sms (" + 544 "_id INTEGER PRIMARY KEY," + 545 "thread_id INTEGER," + 546 "address TEXT," + 547 "person INTEGER," + 548 "date INTEGER," + 549 "protocol INTEGER," + 550 "read INTEGER DEFAULT 0," + 551 "status INTEGER DEFAULT -1," + // a TP-Status value 552 // or -1 if it 553 // status hasn't 554 // been received 555 "type INTEGER," + 556 "reply_path_present INTEGER," + 557 "subject TEXT," + 558 "body TEXT," + 559 "service_center TEXT," + 560 "locked INTEGER DEFAULT 0," + 561 "error_code INTEGER DEFAULT 0" + 562 ");"); 563 564 /** 565 * This table is used by the SMS dispatcher to hold 566 * incomplete partial messages until all the parts arrive. 567 */ 568 db.execSQL("CREATE TABLE raw (" + 569 "_id INTEGER PRIMARY KEY," + 570 "date INTEGER," + 571 "reference_number INTEGER," + // one per full message 572 "count INTEGER," + // the number of parts 573 "sequence INTEGER," + // the part number of this message 574 "destination_port INTEGER," + 575 "address TEXT," + 576 "pdu TEXT);"); // the raw PDU for this part 577 578 db.execSQL("CREATE TABLE attachments (" + 579 "sms_id INTEGER," + 580 "content_url TEXT," + 581 "offset INTEGER);"); 582 583 /** 584 * This table is used by the SMS dispatcher to hold pending 585 * delivery status report intents. 586 */ 587 db.execSQL("CREATE TABLE sr_pending (" + 588 "reference_number INTEGER," + 589 "action TEXT," + 590 "data TEXT);"); 591 } 592 593 private void createCommonTables(SQLiteDatabase db) { 594 // TODO Ensure that each entry is removed when the last use of 595 // any address equivalent to its address is removed. 596 597 /** 598 * This table maps the first instance seen of any particular 599 * MMS/SMS address to an ID, which is then used as its 600 * canonical representation. If the same address or an 601 * equivalent address (as determined by our Sqlite 602 * PHONE_NUMBERS_EQUAL extension) is seen later, this same ID 603 * will be used. 604 */ 605 db.execSQL("CREATE TABLE canonical_addresses (" + 606 "_id INTEGER PRIMARY KEY," + 607 "address TEXT);"); 608 609 /** 610 * This table maps the subject and an ordered set of recipient 611 * IDs, separated by spaces, to a unique thread ID. The IDs 612 * come from the canonical_addresses table. This works 613 * because messages are considered to be part of the same 614 * thread if they have the same subject (or a null subject) 615 * and the same set of recipients. 616 */ 617 db.execSQL("CREATE TABLE threads (" + 618 Threads._ID + " INTEGER PRIMARY KEY," + 619 Threads.DATE + " INTEGER DEFAULT 0," + 620 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 621 Threads.RECIPIENT_IDS + " TEXT," + 622 Threads.SNIPPET + " TEXT," + 623 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 624 Threads.READ + " INTEGER DEFAULT 1," + 625 Threads.TYPE + " INTEGER DEFAULT 0," + 626 Threads.ERROR + " INTEGER DEFAULT 0," + 627 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 628 629 /** 630 * This table stores the queue of messages to be sent/downloaded. 631 */ 632 db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" + 633 PendingMessages._ID + " INTEGER PRIMARY KEY," + 634 PendingMessages.PROTO_TYPE + " INTEGER," + 635 PendingMessages.MSG_ID + " INTEGER," + 636 PendingMessages.MSG_TYPE + " INTEGER," + 637 PendingMessages.ERROR_TYPE + " INTEGER," + 638 PendingMessages.ERROR_CODE + " INTEGER," + 639 PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," + 640 PendingMessages.DUE_TIME + " INTEGER," + 641 PendingMessages.LAST_TRY + " INTEGER);"); 642 643 } 644 645 // TODO Check the query plans for these triggers. 646 private void createCommonTriggers(SQLiteDatabase db) { 647 // Updates threads table whenever a message is added to pdu. 648 db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " + 649 MmsProvider.TABLE_PDU + " " + 650 PDU_UPDATE_THREAD_CONSTRAINTS + 651 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 652 653 // Updates threads table whenever a message is added to sms. 654 db.execSQL("CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms " + 655 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 656 657 // Updates threads table whenever a message in pdu is updated. 658 db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" + 659 " UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX + 660 " ON " + MmsProvider.TABLE_PDU + " " + 661 PDU_UPDATE_THREAD_CONSTRAINTS + 662 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 663 664 // Updates threads table whenever a message in sms is updated. 665 db.execSQL("CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER" + 666 " UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE + 667 " ON sms " + 668 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 669 670 // Updates threads table whenever a message in pdu is updated. 671 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 672 " UPDATE OF " + Mms.READ + 673 " ON " + MmsProvider.TABLE_PDU + " " + 674 PDU_UPDATE_THREAD_CONSTRAINTS + 675 "BEGIN " + 676 PDU_UPDATE_THREAD_READ_BODY + 677 "END;"); 678 679 // Updates threads table whenever a message in sms is updated. 680 db.execSQL("CREATE TRIGGER sms_update_thread_read_on_update AFTER" + 681 " UPDATE OF " + Sms.READ + 682 " ON sms " + 683 "BEGIN " + 684 SMS_UPDATE_THREAD_READ_BODY + 685 "END;"); 686 687 // Update threads table whenever a message in pdu is deleted 688 db.execSQL("CREATE TRIGGER pdu_update_thread_on_delete " + 689 "AFTER DELETE ON pdu " + 690 "BEGIN " + 691 " UPDATE threads SET " + 692 " date = (strftime('%s','now') * 1000)" + 693 " WHERE threads._id = old." + Mms.THREAD_ID + "; " + 694 UPDATE_THREAD_COUNT_ON_OLD + 695 UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE + 696 "END;"); 697 698 // When the last message in a thread is deleted, these 699 // triggers ensure that the entry for its thread ID is removed 700 // from the threads table. 701 db.execSQL("CREATE TRIGGER delete_obsolete_threads_pdu " + 702 "AFTER DELETE ON pdu " + 703 "BEGIN " + 704 " DELETE FROM threads " + 705 " WHERE " + 706 " _id = old.thread_id " + 707 " AND _id NOT IN " + 708 " (SELECT thread_id FROM sms " + 709 " UNION SELECT thread_id from pdu); " + 710 "END;"); 711 712 db.execSQL("CREATE TRIGGER delete_obsolete_threads_when_update_pdu " + 713 "AFTER UPDATE OF " + Mms.THREAD_ID + " ON pdu " + 714 "WHEN old." + Mms.THREAD_ID + " != new." + Mms.THREAD_ID + " " + 715 "BEGIN " + 716 " DELETE FROM threads " + 717 " WHERE " + 718 " _id = old.thread_id " + 719 " AND _id NOT IN " + 720 " (SELECT thread_id FROM sms " + 721 " UNION SELECT thread_id from pdu); " + 722 "END;"); 723 // Insert pending status for M-Notification.ind or M-ReadRec.ind 724 // when they are inserted into Inbox/Outbox. 725 db.execSQL("CREATE TRIGGER insert_mms_pending_on_insert " + 726 "AFTER INSERT ON pdu " + 727 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + 728 " OR new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_REC_IND + 729 " " + 730 "BEGIN " + 731 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 732 " (" + PendingMessages.PROTO_TYPE + "," + 733 " " + PendingMessages.MSG_ID + "," + 734 " " + PendingMessages.MSG_TYPE + "," + 735 " " + PendingMessages.ERROR_TYPE + "," + 736 " " + PendingMessages.ERROR_CODE + "," + 737 " " + PendingMessages.RETRY_INDEX + "," + 738 " " + PendingMessages.DUE_TIME + ") " + 739 " VALUES " + 740 " (" + MmsSms.MMS_PROTO + "," + 741 " new." + BaseColumns._ID + "," + 742 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 743 "END;"); 744 745 // Insert pending status for M-Send.req when it is moved into Outbox. 746 db.execSQL("CREATE TRIGGER insert_mms_pending_on_update " + 747 "AFTER UPDATE ON pdu " + 748 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + 749 " AND new." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 750 " AND old." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 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 // When a message is moved out of Outbox, delete its pending status. 767 db.execSQL("CREATE TRIGGER delete_mms_pending_on_update " + 768 "AFTER UPDATE ON " + MmsProvider.TABLE_PDU + " " + 769 "WHEN old." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 770 " AND new." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 771 "BEGIN " + 772 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 773 " WHERE " + PendingMessages.MSG_ID + "=new._id; " + 774 "END;"); 775 776 // Delete pending status for a message when it is deleted. 777 db.execSQL("CREATE TRIGGER delete_mms_pending_on_delete " + 778 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 779 "BEGIN " + 780 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 781 " WHERE " + PendingMessages.MSG_ID + "=old._id; " + 782 "END;"); 783 784 // TODO Add triggers for SMS retry-status management. 785 786 // Update the error flag of threads when the error type of 787 // a pending MM is updated. 788 db.execSQL("CREATE TRIGGER update_threads_error_on_update_mms " + 789 " AFTER UPDATE OF err_type ON pending_msgs " + 790 " WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)" + 791 " OR (OLD.err_type >= 10 AND NEW.err_type < 10) " + 792 "BEGIN" + 793 " UPDATE threads SET error = " + 794 " CASE" + 795 " WHEN NEW.err_type >= 10 THEN error + 1" + 796 " ELSE error - 1" + 797 " END " + 798 " WHERE _id =" + 799 " (SELECT DISTINCT thread_id" + 800 " FROM pdu" + 801 " WHERE _id = NEW.msg_id); " + 802 "END;"); 803 804 // Update the error flag of threads when delete pending message. 805 db.execSQL("CREATE TRIGGER update_threads_error_on_delete_mms " + 806 " BEFORE DELETE ON pdu" + 807 " WHEN OLD._id IN (SELECT DISTINCT msg_id" + 808 " FROM pending_msgs" + 809 " WHERE err_type >= 10) " + 810 "BEGIN " + 811 " UPDATE threads SET error = error - 1" + 812 " WHERE _id = OLD.thread_id; " + 813 "END;"); 814 815 // Update the error flag of threads while moving an MM out of Outbox, 816 // which was failed to be sent permanently. 817 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 818 " BEFORE UPDATE OF msg_box ON pdu " + 819 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 820 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 821 " FROM pending_msgs" + 822 " WHERE err_type >= 10)) " + 823 "BEGIN " + 824 " UPDATE threads SET error = error - 1" + 825 " WHERE _id = OLD.thread_id; " + 826 "END;"); 827 828 // Update the error flag of threads after a text message was 829 // failed to send/receive. 830 db.execSQL("CREATE TRIGGER update_threads_error_on_update_sms " + 831 " AFTER UPDATE OF type ON sms" + 832 " WHEN (OLD.type != 5 AND NEW.type = 5)" + 833 " OR (OLD.type = 5 AND NEW.type != 5) " + 834 "BEGIN " + 835 " UPDATE threads SET error = " + 836 " CASE" + 837 " WHEN NEW.type = 5 THEN error + 1" + 838 " ELSE error - 1" + 839 " END " + 840 " WHERE _id = NEW.thread_id; " + 841 "END;"); 842 } 843 844 @Override 845 public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) { 846 Log.w(TAG, "Upgrading database from version " + oldVersion 847 + " to " + currentVersion + "."); 848 849 switch (oldVersion) { 850 case 40: 851 if (currentVersion <= 40) { 852 return; 853 } 854 855 db.beginTransaction(); 856 try { 857 upgradeDatabaseToVersion41(db); 858 db.setTransactionSuccessful(); 859 } catch (Throwable ex) { 860 Log.e(TAG, ex.getMessage(), ex); 861 break; 862 } finally { 863 db.endTransaction(); 864 } 865 // fall through 866 case 41: 867 if (currentVersion <= 41) { 868 return; 869 } 870 871 db.beginTransaction(); 872 try { 873 upgradeDatabaseToVersion42(db); 874 db.setTransactionSuccessful(); 875 } catch (Throwable ex) { 876 Log.e(TAG, ex.getMessage(), ex); 877 break; 878 } finally { 879 db.endTransaction(); 880 } 881 // fall through 882 case 42: 883 if (currentVersion <= 42) { 884 return; 885 } 886 887 db.beginTransaction(); 888 try { 889 upgradeDatabaseToVersion43(db); 890 db.setTransactionSuccessful(); 891 } catch (Throwable ex) { 892 Log.e(TAG, ex.getMessage(), ex); 893 break; 894 } finally { 895 db.endTransaction(); 896 } 897 // fall through 898 case 43: 899 if (currentVersion <= 43) { 900 return; 901 } 902 903 db.beginTransaction(); 904 try { 905 upgradeDatabaseToVersion44(db); 906 db.setTransactionSuccessful(); 907 } catch (Throwable ex) { 908 Log.e(TAG, ex.getMessage(), ex); 909 break; 910 } finally { 911 db.endTransaction(); 912 } 913 // fall through 914 case 44: 915 if (currentVersion <= 44) { 916 return; 917 } 918 919 db.beginTransaction(); 920 try { 921 upgradeDatabaseToVersion45(db); 922 db.setTransactionSuccessful(); 923 } catch (Throwable ex) { 924 Log.e(TAG, ex.getMessage(), ex); 925 break; 926 } finally { 927 db.endTransaction(); 928 } 929 // fall through 930 case 45: 931 if (currentVersion <= 45) { 932 return; 933 } 934 db.beginTransaction(); 935 try { 936 upgradeDatabaseToVersion46(db); 937 db.setTransactionSuccessful(); 938 } catch (Throwable ex) { 939 Log.e(TAG, ex.getMessage(), ex); 940 break; 941 } finally { 942 db.endTransaction(); 943 } 944 // fall through 945 case 46: 946 if (currentVersion <= 46) { 947 return; 948 } 949 950 db.beginTransaction(); 951 try { 952 upgradeDatabaseToVersion47(db); 953 db.setTransactionSuccessful(); 954 } catch (Throwable ex) { 955 Log.e(TAG, ex.getMessage(), ex); 956 break; 957 } finally { 958 db.endTransaction(); 959 } 960 // fall through 961 case 47: 962 if (currentVersion <= 47) { 963 return; 964 } 965 966 db.beginTransaction(); 967 try { 968 upgradeDatabaseToVersion48(db); 969 db.setTransactionSuccessful(); 970 } catch (Throwable ex) { 971 Log.e(TAG, ex.getMessage(), ex); 972 break; 973 } finally { 974 db.endTransaction(); 975 } 976 case 48: 977 if (currentVersion <= 48) { 978 return; 979 } 980 981 db.beginTransaction(); 982 try { 983 createWordsTables(db); 984 db.setTransactionSuccessful(); 985 } catch (Throwable ex) { 986 Log.e(TAG, ex.getMessage(), ex); 987 break; 988 } finally { 989 db.endTransaction(); 990 } 991 992 return; 993 } 994 995 996 Log.e(TAG, "Destroying all old data."); 997 dropAll(db); 998 onCreate(db); 999 } 1000 1001 private void dropAll(SQLiteDatabase db) { 1002 // Clean the database out in order to start over from scratch. 1003 // We don't need to drop our triggers here because SQLite automatically 1004 // drops a trigger when its attached database is dropped. 1005 db.execSQL("DROP TABLE IF EXISTS canonical_addresses"); 1006 db.execSQL("DROP TABLE IF EXISTS threads"); 1007 db.execSQL("DROP TABLE IF EXISTS " + MmsSmsProvider.TABLE_PENDING_MSG); 1008 db.execSQL("DROP TABLE IF EXISTS sms"); 1009 db.execSQL("DROP TABLE IF EXISTS raw"); 1010 db.execSQL("DROP TABLE IF EXISTS attachments"); 1011 db.execSQL("DROP TABLE IF EXISTS thread_ids"); 1012 db.execSQL("DROP TABLE IF EXISTS sr_pending"); 1013 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PDU + ";"); 1014 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_ADDR + ";"); 1015 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PART + ";"); 1016 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_RATE + ";"); 1017 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_DRM + ";"); 1018 } 1019 1020 private void upgradeDatabaseToVersion41(SQLiteDatabase db) { 1021 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms"); 1022 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 1023 " BEFORE UPDATE OF msg_box ON pdu " + 1024 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 1025 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 1026 " FROM pending_msgs" + 1027 " WHERE err_type >= 10)) " + 1028 "BEGIN " + 1029 " UPDATE threads SET error = error - 1" + 1030 " WHERE _id = OLD.thread_id; " + 1031 "END;"); 1032 } 1033 1034 private void upgradeDatabaseToVersion42(SQLiteDatabase db) { 1035 db.execSQL("DROP TRIGGER IF EXISTS sms_update_thread_on_delete"); 1036 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_sms"); 1037 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_sms"); 1038 } 1039 1040 private void upgradeDatabaseToVersion43(SQLiteDatabase db) { 1041 // Add 'has_attachment' column to threads table. 1042 db.execSQL("ALTER TABLE threads ADD COLUMN has_attachment INTEGER DEFAULT 0"); 1043 1044 updateThreadsAttachmentColumn(db); 1045 1046 // Add insert and delete triggers for keeping it up to date. 1047 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 1048 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 1049 } 1050 1051 private void upgradeDatabaseToVersion44(SQLiteDatabase db) { 1052 updateThreadsAttachmentColumn(db); 1053 1054 // add the update trigger for keeping the threads up to date. 1055 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1056 } 1057 1058 private void upgradeDatabaseToVersion45(SQLiteDatabase db) { 1059 // Add 'locked' column to sms table. 1060 db.execSQL("ALTER TABLE sms ADD COLUMN locked INTEGER DEFAULT 0"); 1061 1062 // Add 'locked' column to pdu table. 1063 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.LOCKED + " INTEGER DEFAULT 0"); 1064 } 1065 1066 private void upgradeDatabaseToVersion46(SQLiteDatabase db) { 1067 // add the "text" column for caching inline text (e.g. strings) instead of 1068 // putting them in an external file 1069 db.execSQL("ALTER TABLE part ADD COLUMN " + Part.TEXT + " TEXT"); 1070 1071 Cursor textRows = db.query( 1072 "part", 1073 new String[] { Part._ID, Part._DATA, Part.TEXT}, 1074 "ct = 'text/plain' OR ct == 'application/smil'", 1075 null, 1076 null, 1077 null, 1078 null); 1079 ArrayList<String> filesToDelete = new ArrayList<String>(); 1080 try { 1081 if (textRows != null) { 1082 int partIdColumn = textRows.getColumnIndex(Part._ID); 1083 int partDataColumn = textRows.getColumnIndex(Part._DATA); 1084 int partTextColumn = textRows.getColumnIndex(Part.TEXT); 1085 1086 // This code is imperfect in that we can't guarantee that all the 1087 // backing files get deleted. For example if the system aborts after 1088 // the database is updated but before we complete the process of 1089 // deleting files. 1090 while (textRows.moveToNext()) { 1091 String path = textRows.getString(partDataColumn); 1092 if (path != null) { 1093 try { 1094 InputStream is = new FileInputStream(path); 1095 byte [] data = new byte[is.available()]; 1096 is.read(data); 1097 EncodedStringValue v = new EncodedStringValue(data); 1098 textRows.updateString(partTextColumn, v.getString()); 1099 textRows.updateToNull(partDataColumn); 1100 is.close(); 1101 filesToDelete.add(path); 1102 } catch (IOException e) { 1103 // TODO Auto-generated catch block 1104 e.printStackTrace(); 1105 } 1106 } 1107 } 1108 } 1109 } finally { 1110 textRows.commitUpdates(); 1111 for (String pathToDelete : filesToDelete) { 1112 try { 1113 (new File(pathToDelete)).delete(); 1114 } catch (SecurityException ex) { 1115 Log.e(TAG, "unable to clean up old mms file for " + pathToDelete, ex); 1116 } 1117 } 1118 if (textRows != null) { 1119 textRows.close(); 1120 } 1121 } 1122 } 1123 1124 private void upgradeDatabaseToVersion47(SQLiteDatabase db) { 1125 updateThreadsAttachmentColumn(db); 1126 1127 // add the update trigger for keeping the threads up to date. 1128 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1129 } 1130 1131 private void upgradeDatabaseToVersion48(SQLiteDatabase db) { 1132 // Add 'error_code' column to sms table. 1133 db.execSQL("ALTER TABLE sms ADD COLUMN error_code INTEGER DEFAULT 0"); 1134 } 1135 1136 private void updateThreadsAttachmentColumn(SQLiteDatabase db) { 1137 // Set the values of that column correctly based on the current 1138 // contents of the database. 1139 db.execSQL("UPDATE threads SET has_attachment=1 WHERE _id IN " + 1140 " (SELECT DISTINCT pdu.thread_id FROM part " + 1141 " JOIN pdu ON pdu._id=part.mid " + 1142 " WHERE part.ct != 'text/plain' AND part.ct != 'application/smil')"); 1143 } 1144} 1145