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