MmsSmsDatabaseHelper.java revision 3ce9cb8e04c821453dd7731b30309d508017f89c
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.BroadcastReceiver; 26import android.content.ContentValues; 27import android.content.Context; 28import android.content.Intent; 29import android.content.IntentFilter; 30import android.database.Cursor; 31import android.database.sqlite.SQLiteDatabase; 32import android.database.sqlite.SQLiteOpenHelper; 33import android.provider.BaseColumns; 34import android.provider.Telephony; 35import android.provider.Telephony.Mms; 36import android.provider.Telephony.MmsSms; 37import android.provider.Telephony.Sms; 38import android.provider.Telephony.Threads; 39import android.provider.Telephony.Mms.Addr; 40import android.provider.Telephony.Mms.Part; 41import android.provider.Telephony.Mms.Rate; 42import android.provider.Telephony.MmsSms.PendingMessages; 43import android.util.Log; 44 45import com.google.android.mms.pdu.EncodedStringValue; 46import com.google.android.mms.pdu.PduHeaders; 47 48public class MmsSmsDatabaseHelper extends SQLiteOpenHelper { 49 private static final String TAG = "MmsSmsDatabaseHelper"; 50 51 private static final String SMS_UPDATE_THREAD_READ_BODY = 52 " UPDATE threads SET read = " + 53 " CASE (SELECT COUNT(*)" + 54 " FROM sms" + 55 " WHERE " + Sms.READ + " = 0" + 56 " AND " + Sms.THREAD_ID + " = threads._id)" + 57 " WHEN 0 THEN 1" + 58 " ELSE 0" + 59 " END" + 60 " WHERE threads._id = new." + Sms.THREAD_ID + "; "; 61 62 private static final String UPDATE_THREAD_COUNT_ON_NEW = 63 " UPDATE threads SET message_count = " + 64 " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " + 65 " ON threads._id = " + Sms.THREAD_ID + 66 " WHERE " + Sms.THREAD_ID + " = new.thread_id" + 67 " AND sms." + Sms.TYPE + " != 3) + " + 68 " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " + 69 " ON threads._id = " + Mms.THREAD_ID + 70 " WHERE " + Mms.THREAD_ID + " = new.thread_id" + 71 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 72 " AND " + Mms.MESSAGE_BOX + " != 3) " + 73 " WHERE threads._id = new.thread_id; "; 74 75 private static final String UPDATE_THREAD_COUNT_ON_OLD = 76 " UPDATE threads SET message_count = " + 77 " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " + 78 " ON threads._id = " + Sms.THREAD_ID + 79 " WHERE " + Sms.THREAD_ID + " = old.thread_id" + 80 " AND sms." + Sms.TYPE + " != 3) + " + 81 " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " + 82 " ON threads._id = " + Mms.THREAD_ID + 83 " WHERE " + Mms.THREAD_ID + " = old.thread_id" + 84 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 85 " AND " + Mms.MESSAGE_BOX + " != 3) " + 86 " WHERE threads._id = old.thread_id; "; 87 88 private static final String SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE = 89 "BEGIN" + 90 " UPDATE threads SET" + 91 " date = (strftime('%s','now') * 1000), " + 92 " snippet = new." + Sms.BODY + ", " + 93 " snippet_cs = 0" + 94 " WHERE threads._id = new." + Sms.THREAD_ID + "; " + 95 UPDATE_THREAD_COUNT_ON_NEW + 96 SMS_UPDATE_THREAD_READ_BODY + 97 "END;"; 98 99 private static final String PDU_UPDATE_THREAD_CONSTRAINTS = 100 " WHEN new." + Mms.MESSAGE_TYPE + "=" + 101 PduHeaders.MESSAGE_TYPE_RETRIEVE_CONF + 102 " OR new." + Mms.MESSAGE_TYPE + "=" + 103 PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + 104 " OR new." + Mms.MESSAGE_TYPE + "=" + 105 PduHeaders.MESSAGE_TYPE_SEND_REQ + " "; 106 107 // When looking in the pdu table for unread messages, only count messages that 108 // are displayed to the user. The constants are defined in PduHeaders and could be used 109 // here, but the string "(m_type=132 OR m_type=130 OR m_type=128)" is used throughout this 110 // file and so it is used here to be consistent. 111 // m_type=128 = MESSAGE_TYPE_SEND_REQ 112 // m_type=130 = MESSAGE_TYPE_NOTIFICATION_IND 113 // m_type=132 = MESSAGE_TYPE_RETRIEVE_CONF 114 private static final String PDU_UPDATE_THREAD_READ_BODY = 115 " UPDATE threads SET read = " + 116 " CASE (SELECT COUNT(*)" + 117 " FROM " + MmsProvider.TABLE_PDU + 118 " WHERE " + Mms.READ + " = 0" + 119 " AND " + Mms.THREAD_ID + " = threads._id " + 120 " AND (m_type=132 OR m_type=130 OR m_type=128)) " + 121 " WHEN 0 THEN 1" + 122 " ELSE 0" + 123 " END" + 124 " WHERE threads._id = new." + Mms.THREAD_ID + "; "; 125 126 private static final String PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE = 127 "BEGIN" + 128 " UPDATE threads SET" + 129 " date = (strftime('%s','now') * 1000), " + 130 " snippet = new." + Mms.SUBJECT + ", " + 131 " snippet_cs = new." + Mms.SUBJECT_CHARSET + 132 " WHERE threads._id = new." + Mms.THREAD_ID + "; " + 133 UPDATE_THREAD_COUNT_ON_NEW + 134 PDU_UPDATE_THREAD_READ_BODY + 135 "END;"; 136 137 private static final String UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE = 138 " UPDATE threads SET snippet = " + 139 " (SELECT snippet FROM" + 140 " (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" + 141 " UNION SELECT date, body AS snippet, thread_id FROM sms)" + 142 " WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " + 143 " WHERE threads._id = OLD.thread_id; " + 144 " UPDATE threads SET snippet_cs = " + 145 " (SELECT snippet_cs FROM" + 146 " (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" + 147 " UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" + 148 " WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " + 149 " WHERE threads._id = OLD.thread_id; "; 150 151 152 // When a part is inserted, if it is not text/plain or application/smil 153 // (which both can exist with text-only MMSes), then there is an attachment. 154 // Set has_attachment=1 in the threads table for the thread in question. 155 private static final String PART_UPDATE_THREADS_ON_INSERT_TRIGGER = 156 "CREATE TRIGGER update_threads_on_insert_part " + 157 " AFTER INSERT ON part " + 158 " WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " + 159 " BEGIN " + 160 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 161 " (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " + 162 " WHERE part._id=new._id LIMIT 1); " + 163 " END"; 164 165 // When the 'mid' column in the part table is updated, we need to run the trigger to update 166 // the threads table's has_attachment column, if the part is an attachment. 167 private static final String PART_UPDATE_THREADS_ON_UPDATE_TRIGGER = 168 "CREATE TRIGGER update_threads_on_update_part " + 169 " AFTER UPDATE of " + Part.MSG_ID + " ON part " + 170 " WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " + 171 " BEGIN " + 172 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 173 " (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " + 174 " WHERE part._id=new._id LIMIT 1); " + 175 " END"; 176 177 178 // When a part is deleted (with the same non-text/SMIL constraint as when 179 // we set has_attachment), update the threads table for all threads. 180 // Unfortunately we cannot update only the thread that the part was 181 // attached to, as it is possible that the part has been orphaned and 182 // the message it was attached to is already gone. 183 private static final String PART_UPDATE_THREADS_ON_DELETE_TRIGGER = 184 "CREATE TRIGGER update_threads_on_delete_part " + 185 " AFTER DELETE ON part " + 186 " WHEN old.ct != 'text/plain' AND old.ct != 'application/smil' " + 187 " BEGIN " + 188 " UPDATE threads SET has_attachment = " + 189 " CASE " + 190 " (SELECT COUNT(*) FROM part JOIN pdu " + 191 " WHERE pdu.thread_id = threads._id " + 192 " AND part.ct != 'text/plain' AND part.ct != 'application/smil' " + 193 " AND part.mid = pdu._id)" + 194 " WHEN 0 THEN 0 " + 195 " ELSE 1 " + 196 " END; " + 197 " END"; 198 199 // When the 'thread_id' column in the pdu table is updated, we need to run the trigger to update 200 // the threads table's has_attachment column, if the message has an attachment in 'part' table 201 private static final String PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER = 202 "CREATE TRIGGER update_threads_on_update_pdu " + 203 " AFTER UPDATE of thread_id ON pdu " + 204 " BEGIN " + 205 " UPDATE threads SET has_attachment=1 WHERE _id IN " + 206 " (SELECT pdu.thread_id FROM part JOIN pdu " + 207 " WHERE part.ct != 'text/plain' AND part.ct != 'application/smil' " + 208 " AND part.mid = pdu._id);" + 209 " END"; 210 211 private static MmsSmsDatabaseHelper sInstance = null; 212 private static boolean sTriedAutoIncrement = false; 213 private static boolean sFakeLowStorageTest = false; // for testing only 214 215 static final String DATABASE_NAME = "mmssms.db"; 216 static final int DATABASE_VERSION = 55; 217 private final Context mContext; 218 private LowStorageMonitor mLowStorageMonitor; 219 220 221 private MmsSmsDatabaseHelper(Context context) { 222 super(context, DATABASE_NAME, null, DATABASE_VERSION); 223 224 mContext = context; 225 } 226 227 /** 228 * Return a singleton helper for the combined MMS and SMS 229 * database. 230 */ 231 /* package */ static synchronized MmsSmsDatabaseHelper getInstance(Context context) { 232 if (sInstance == null) { 233 sInstance = new MmsSmsDatabaseHelper(context); 234 } 235 return sInstance; 236 } 237 238 public static void updateThread(SQLiteDatabase db, long thread_id) { 239 if (thread_id < 0) { 240 updateAllThreads(db, null, null); 241 return; 242 } 243 244 // Delete the row for this thread in the threads table if 245 // there are no more messages attached to it in either 246 // the sms or pdu tables. 247 int rows = db.delete("threads", 248 "_id = ? AND _id NOT IN" + 249 " (SELECT thread_id FROM sms " + 250 " UNION SELECT thread_id FROM pdu)", 251 new String[] { String.valueOf(thread_id) }); 252 if (rows > 0) { 253 // If this deleted a row, let's remove orphaned canonical_addresses and get outta here 254 db.delete("canonical_addresses", 255 "_id NOT IN (SELECT DISTINCT recipient_ids FROM threads)", null); 256 return; 257 } 258 // Update the message count in the threads table as the sum 259 // of all messages in both the sms and pdu tables. 260 db.execSQL( 261 " UPDATE threads SET message_count = " + 262 " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " + 263 " ON threads._id = " + Sms.THREAD_ID + 264 " WHERE " + Sms.THREAD_ID + " = " + thread_id + 265 " AND sms." + Sms.TYPE + " != 3) + " + 266 " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " + 267 " ON threads._id = " + Mms.THREAD_ID + 268 " WHERE " + Mms.THREAD_ID + " = " + thread_id + 269 " AND (m_type=132 OR m_type=130 OR m_type=128)" + 270 " AND " + Mms.MESSAGE_BOX + " != 3) " + 271 " WHERE threads._id = " + thread_id + ";"); 272 273 // Update the date and the snippet (and its character set) in 274 // the threads table to be that of the most recent message in 275 // the thread. 276 db.execSQL( 277 " UPDATE threads" + 278 " SET" + 279 " date =" + 280 " (SELECT date FROM" + 281 " (SELECT date * 1000 AS date, thread_id FROM pdu" + 282 " UNION SELECT date, thread_id FROM sms)" + 283 " WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," + 284 " snippet =" + 285 " (SELECT snippet FROM" + 286 " (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" + 287 " UNION SELECT date, body AS snippet, thread_id FROM sms)" + 288 " WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," + 289 " snippet_cs =" + 290 " (SELECT snippet_cs FROM" + 291 " (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" + 292 " UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" + 293 " WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)" + 294 " WHERE threads._id = " + thread_id + ";"); 295 296 // Update the error column of the thread to indicate if there 297 // are any messages in it that have failed to send. 298 // First check to see if there are any messages with errors in this thread. 299 String query = "SELECT thread_id FROM sms WHERE type=" + 300 Telephony.TextBasedSmsColumns.MESSAGE_TYPE_FAILED + 301 " AND thread_id = " + thread_id + 302 " LIMIT 1"; 303 int setError = 0; 304 Cursor c = db.rawQuery(query, null); 305 if (c != null) { 306 try { 307 setError = c.getCount(); // Because of the LIMIT 1, count will be 1 or 0. 308 } finally { 309 c.close(); 310 } 311 } 312 // What's the current state of the error flag in the threads table? 313 String errorQuery = "SELECT error FROM threads WHERE _id = " + thread_id; 314 c = db.rawQuery(errorQuery, null); 315 if (c != null) { 316 try { 317 if (c.moveToNext()) { 318 int curError = c.getInt(0); 319 if (curError != setError) { 320 // The current thread error column differs, update it. 321 db.execSQL("UPDATE threads SET error=" + setError + 322 " WHERE _id = " + thread_id); 323 } 324 } 325 } finally { 326 c.close(); 327 } 328 } 329 } 330 331 public static void updateAllThreads(SQLiteDatabase db, String where, String[] whereArgs) { 332 if (where == null) { 333 where = ""; 334 } else { 335 where = "WHERE (" + where + ")"; 336 } 337 String query = "SELECT _id FROM threads WHERE _id IN " + 338 "(SELECT DISTINCT thread_id FROM sms " + where + ")"; 339 Cursor c = db.rawQuery(query, whereArgs); 340 if (c != null) { 341 try { 342 while (c.moveToNext()) { 343 updateThread(db, c.getInt(0)); 344 } 345 } finally { 346 c.close(); 347 } 348 } 349 // TODO: there are several db operations in this function. Lets wrap them in a 350 // transaction to make it faster. 351 // remove orphaned threads 352 db.delete("threads", 353 "_id NOT IN (SELECT DISTINCT thread_id FROM sms " + 354 "UNION SELECT DISTINCT thread_id FROM pdu)", null); 355 356 // remove orphaned canonical_addresses 357 db.delete("canonical_addresses", 358 "_id NOT IN (SELECT DISTINCT recipient_ids FROM threads)", null); 359 } 360 361 public static int deleteOneSms(SQLiteDatabase db, int message_id) { 362 int thread_id = -1; 363 // Find the thread ID that the specified SMS belongs to. 364 Cursor c = db.query("sms", new String[] { "thread_id" }, 365 "_id=" + message_id, null, null, null, null); 366 if (c != null) { 367 if (c.moveToFirst()) { 368 thread_id = c.getInt(0); 369 } 370 c.close(); 371 } 372 373 // Delete the specified message. 374 int rows = db.delete("sms", "_id=" + message_id, null); 375 if (thread_id > 0) { 376 // Update its thread. 377 updateThread(db, thread_id); 378 } 379 return rows; 380 } 381 382 @Override 383 public void onCreate(SQLiteDatabase db) { 384 createMmsTables(db); 385 createSmsTables(db); 386 createCommonTables(db); 387 createCommonTriggers(db); 388 createMmsTriggers(db); 389 createWordsTables(db); 390 createIndices(db); 391 } 392 393 // When upgrading the database we need to populate the words 394 // table with the rows out of sms and part. 395 private void populateWordsTable(SQLiteDatabase db) { 396 final String TABLE_WORDS = "words"; 397 { 398 Cursor smsRows = db.query( 399 "sms", 400 new String[] { Sms._ID, Sms.BODY }, 401 null, 402 null, 403 null, 404 null, 405 null); 406 try { 407 if (smsRows != null) { 408 smsRows.moveToPosition(-1); 409 ContentValues cv = new ContentValues(); 410 while (smsRows.moveToNext()) { 411 cv.clear(); 412 413 long id = smsRows.getLong(0); // 0 for Sms._ID 414 String body = smsRows.getString(1); // 1 for Sms.BODY 415 416 cv.put(Telephony.MmsSms.WordsTable.ID, id); 417 cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body); 418 cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id); 419 cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1); 420 db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv); 421 } 422 } 423 } finally { 424 if (smsRows != null) { 425 smsRows.close(); 426 } 427 } 428 } 429 430 { 431 Cursor mmsRows = db.query( 432 "part", 433 new String[] { Part._ID, Part.TEXT }, 434 "ct = 'text/plain'", 435 null, 436 null, 437 null, 438 null); 439 try { 440 if (mmsRows != null) { 441 mmsRows.moveToPosition(-1); 442 ContentValues cv = new ContentValues(); 443 while (mmsRows.moveToNext()) { 444 cv.clear(); 445 446 long id = mmsRows.getLong(0); // 0 for Part._ID 447 String body = mmsRows.getString(1); // 1 for Part.TEXT 448 449 cv.put(Telephony.MmsSms.WordsTable.ID, id); 450 cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body); 451 cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id); 452 cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1); 453 db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv); 454 } 455 } 456 } finally { 457 if (mmsRows != null) { 458 mmsRows.close(); 459 } 460 } 461 } 462 } 463 464 private void createWordsTables(SQLiteDatabase db) { 465 try { 466 db.execSQL("CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER, table_to_use INTEGER);"); 467 468 // monitor the sms table 469 // NOTE don't handle inserts using a trigger because it has an unwanted 470 // side effect: the value returned for the last row ends up being the 471 // id of one of the trigger insert not the original row insert. 472 // Handle inserts manually in the provider. 473 db.execSQL("CREATE TRIGGER sms_words_update AFTER UPDATE ON sms BEGIN UPDATE words " + 474 " SET index_text = NEW.body WHERE (source_id=NEW._id AND table_to_use=1); " + 475 " END;"); 476 db.execSQL("CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM " + 477 " words WHERE source_id = OLD._id AND table_to_use = 1; END;"); 478 479 // monitor the mms table 480 db.execSQL("CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words " + 481 " SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " + 482 " END;"); 483 db.execSQL("CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM " + 484 " words WHERE source_id = OLD._id AND table_to_use = 2; END;"); 485 486 populateWordsTable(db); 487 } catch (Exception ex) { 488 Log.e(TAG, "got exception creating words table: " + ex.toString()); 489 } 490 } 491 492 private void createIndices(SQLiteDatabase db) { 493 createThreadIdIndex(db); 494 } 495 496 private void createThreadIdIndex(SQLiteDatabase db) { 497 try { 498 db.execSQL("CREATE INDEX IF NOT EXISTS typeThreadIdIndex ON sms" + 499 " (type, thread_id);"); 500 } catch (Exception ex) { 501 Log.e(TAG, "got exception creating indices: " + ex.toString()); 502 } 503 } 504 505 private void createMmsTables(SQLiteDatabase db) { 506 // N.B.: Whenever the columns here are changed, the columns in 507 // {@ref MmsSmsProvider} must be changed to match. 508 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PDU + " (" + 509 Mms._ID + " INTEGER PRIMARY KEY," + 510 Mms.THREAD_ID + " INTEGER," + 511 Mms.DATE + " INTEGER," + 512 Mms.DATE_SENT + " INTEGER DEFAULT 0," + 513 Mms.MESSAGE_BOX + " INTEGER," + 514 Mms.READ + " INTEGER DEFAULT 0," + 515 Mms.MESSAGE_ID + " TEXT," + 516 Mms.SUBJECT + " TEXT," + 517 Mms.SUBJECT_CHARSET + " INTEGER," + 518 Mms.CONTENT_TYPE + " TEXT," + 519 Mms.CONTENT_LOCATION + " TEXT," + 520 Mms.EXPIRY + " INTEGER," + 521 Mms.MESSAGE_CLASS + " TEXT," + 522 Mms.MESSAGE_TYPE + " INTEGER," + 523 Mms.MMS_VERSION + " INTEGER," + 524 Mms.MESSAGE_SIZE + " INTEGER," + 525 Mms.PRIORITY + " INTEGER," + 526 Mms.READ_REPORT + " INTEGER," + 527 Mms.REPORT_ALLOWED + " INTEGER," + 528 Mms.RESPONSE_STATUS + " INTEGER," + 529 Mms.STATUS + " INTEGER," + 530 Mms.TRANSACTION_ID + " TEXT," + 531 Mms.RETRIEVE_STATUS + " INTEGER," + 532 Mms.RETRIEVE_TEXT + " TEXT," + 533 Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," + 534 Mms.READ_STATUS + " INTEGER," + 535 Mms.CONTENT_CLASS + " INTEGER," + 536 Mms.RESPONSE_TEXT + " TEXT," + 537 Mms.DELIVERY_TIME + " INTEGER," + 538 Mms.DELIVERY_REPORT + " INTEGER," + 539 Mms.LOCKED + " INTEGER DEFAULT 0," + 540 Mms.SEEN + " INTEGER DEFAULT 0" + 541 ");"); 542 543 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_ADDR + " (" + 544 Addr._ID + " INTEGER PRIMARY KEY," + 545 Addr.MSG_ID + " INTEGER," + 546 Addr.CONTACT_ID + " INTEGER," + 547 Addr.ADDRESS + " TEXT," + 548 Addr.TYPE + " INTEGER," + 549 Addr.CHARSET + " INTEGER);"); 550 551 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PART + " (" + 552 Part._ID + " INTEGER PRIMARY KEY," + 553 Part.MSG_ID + " INTEGER," + 554 Part.SEQ + " INTEGER DEFAULT 0," + 555 Part.CONTENT_TYPE + " TEXT," + 556 Part.NAME + " TEXT," + 557 Part.CHARSET + " INTEGER," + 558 Part.CONTENT_DISPOSITION + " TEXT," + 559 Part.FILENAME + " TEXT," + 560 Part.CONTENT_ID + " TEXT," + 561 Part.CONTENT_LOCATION + " TEXT," + 562 Part.CT_START + " INTEGER," + 563 Part.CT_TYPE + " TEXT," + 564 Part._DATA + " TEXT," + 565 Part.TEXT + " TEXT);"); 566 567 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_RATE + " (" + 568 Rate.SENT_TIME + " INTEGER);"); 569 570 db.execSQL("CREATE TABLE " + MmsProvider.TABLE_DRM + " (" + 571 BaseColumns._ID + " INTEGER PRIMARY KEY," + 572 "_data TEXT);"); 573 } 574 575 private void createMmsTriggers(SQLiteDatabase db) { 576 // Cleans up parts when a MM is deleted. 577 db.execSQL("CREATE TRIGGER part_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 578 "BEGIN " + 579 " DELETE FROM " + MmsProvider.TABLE_PART + 580 " WHERE " + Part.MSG_ID + "=old._id;" + 581 "END;"); 582 583 // Cleans up address info when a MM is deleted. 584 db.execSQL("CREATE TRIGGER addr_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 585 "BEGIN " + 586 " DELETE FROM " + MmsProvider.TABLE_ADDR + 587 " WHERE " + Addr.MSG_ID + "=old._id;" + 588 "END;"); 589 590 // Delete obsolete delivery-report, read-report while deleting their 591 // associated Send.req. 592 db.execSQL("CREATE TRIGGER cleanup_delivery_and_read_report " + 593 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 594 "WHEN old." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + " " + 595 "BEGIN " + 596 " DELETE FROM " + MmsProvider.TABLE_PDU + 597 " WHERE (" + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_DELIVERY_IND + 598 " OR " + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_ORIG_IND + 599 ")" + 600 " AND " + Mms.MESSAGE_ID + "=old." + Mms.MESSAGE_ID + "; " + 601 "END;"); 602 603 // Update threads table to indicate whether attachments exist when 604 // parts are inserted or deleted. 605 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 606 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 607 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 608 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 609 } 610 611 private void createSmsTables(SQLiteDatabase db) { 612 // N.B.: Whenever the columns here are changed, the columns in 613 // {@ref MmsSmsProvider} must be changed to match. 614 db.execSQL("CREATE TABLE sms (" + 615 "_id INTEGER PRIMARY KEY," + 616 "thread_id INTEGER," + 617 "address TEXT," + 618 "person INTEGER," + 619 "date INTEGER," + 620 "date_sent INTEGER DEFAULT 0," + 621 "protocol INTEGER," + 622 "read INTEGER DEFAULT 0," + 623 "status INTEGER DEFAULT -1," + // a TP-Status value 624 // or -1 if it 625 // status hasn't 626 // been received 627 "type INTEGER," + 628 "reply_path_present INTEGER," + 629 "subject TEXT," + 630 "body TEXT," + 631 "service_center TEXT," + 632 "locked INTEGER DEFAULT 0," + 633 "error_code INTEGER DEFAULT 0," + 634 "seen INTEGER DEFAULT 0" + 635 ");"); 636 637 /** 638 * This table is used by the SMS dispatcher to hold 639 * incomplete partial messages until all the parts arrive. 640 */ 641 db.execSQL("CREATE TABLE raw (" + 642 "_id INTEGER PRIMARY KEY," + 643 "date INTEGER," + 644 "reference_number INTEGER," + // one per full message 645 "count INTEGER," + // the number of parts 646 "sequence INTEGER," + // the part number of this message 647 "destination_port INTEGER," + 648 "address TEXT," + 649 "pdu TEXT);"); // the raw PDU for this part 650 651 db.execSQL("CREATE TABLE attachments (" + 652 "sms_id INTEGER," + 653 "content_url TEXT," + 654 "offset INTEGER);"); 655 656 /** 657 * This table is used by the SMS dispatcher to hold pending 658 * delivery status report intents. 659 */ 660 db.execSQL("CREATE TABLE sr_pending (" + 661 "reference_number INTEGER," + 662 "action TEXT," + 663 "data TEXT);"); 664 } 665 666 private void createCommonTables(SQLiteDatabase db) { 667 // TODO Ensure that each entry is removed when the last use of 668 // any address equivalent to its address is removed. 669 670 /** 671 * This table maps the first instance seen of any particular 672 * MMS/SMS address to an ID, which is then used as its 673 * canonical representation. If the same address or an 674 * equivalent address (as determined by our Sqlite 675 * PHONE_NUMBERS_EQUAL extension) is seen later, this same ID 676 * will be used. 677 */ 678 db.execSQL("CREATE TABLE canonical_addresses (" + 679 "_id INTEGER PRIMARY KEY," + 680 "address TEXT);"); 681 682 /** 683 * This table maps the subject and an ordered set of recipient 684 * IDs, separated by spaces, to a unique thread ID. The IDs 685 * come from the canonical_addresses table. This works 686 * because messages are considered to be part of the same 687 * thread if they have the same subject (or a null subject) 688 * and the same set of recipients. 689 */ 690 db.execSQL("CREATE TABLE threads (" + 691 Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 692 Threads.DATE + " INTEGER DEFAULT 0," + 693 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 694 Threads.RECIPIENT_IDS + " TEXT," + 695 Threads.SNIPPET + " TEXT," + 696 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 697 Threads.READ + " INTEGER DEFAULT 1," + 698 Threads.TYPE + " INTEGER DEFAULT 0," + 699 Threads.ERROR + " INTEGER DEFAULT 0," + 700 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 701 702 /** 703 * This table stores the queue of messages to be sent/downloaded. 704 */ 705 db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" + 706 PendingMessages._ID + " INTEGER PRIMARY KEY," + 707 PendingMessages.PROTO_TYPE + " INTEGER," + 708 PendingMessages.MSG_ID + " INTEGER," + 709 PendingMessages.MSG_TYPE + " INTEGER," + 710 PendingMessages.ERROR_TYPE + " INTEGER," + 711 PendingMessages.ERROR_CODE + " INTEGER," + 712 PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," + 713 PendingMessages.DUE_TIME + " INTEGER," + 714 PendingMessages.LAST_TRY + " INTEGER);"); 715 716 } 717 718 // TODO Check the query plans for these triggers. 719 private void createCommonTriggers(SQLiteDatabase db) { 720 // Updates threads table whenever a message is added to pdu. 721 db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " + 722 MmsProvider.TABLE_PDU + " " + 723 PDU_UPDATE_THREAD_CONSTRAINTS + 724 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 725 726 // Updates threads table whenever a message is added to sms. 727 db.execSQL("CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms " + 728 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 729 730 // Updates threads table whenever a message in pdu is updated. 731 db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" + 732 " UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX + 733 " ON " + MmsProvider.TABLE_PDU + " " + 734 PDU_UPDATE_THREAD_CONSTRAINTS + 735 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 736 737 // Updates threads table whenever a message in sms is updated. 738 db.execSQL("CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER" + 739 " UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE + 740 " ON sms " + 741 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 742 743 // Updates threads table whenever a message in pdu is updated. 744 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 745 " UPDATE OF " + Mms.READ + 746 " ON " + MmsProvider.TABLE_PDU + " " + 747 PDU_UPDATE_THREAD_CONSTRAINTS + 748 "BEGIN " + 749 PDU_UPDATE_THREAD_READ_BODY + 750 "END;"); 751 752 // Updates threads table whenever a message in sms is updated. 753 db.execSQL("CREATE TRIGGER sms_update_thread_read_on_update AFTER" + 754 " UPDATE OF " + Sms.READ + 755 " ON sms " + 756 "BEGIN " + 757 SMS_UPDATE_THREAD_READ_BODY + 758 "END;"); 759 760 // Update threads table whenever a message in pdu is deleted 761 db.execSQL("CREATE TRIGGER pdu_update_thread_on_delete " + 762 "AFTER DELETE ON pdu " + 763 "BEGIN " + 764 " UPDATE threads SET " + 765 " date = (strftime('%s','now') * 1000)" + 766 " WHERE threads._id = old." + Mms.THREAD_ID + "; " + 767 UPDATE_THREAD_COUNT_ON_OLD + 768 UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE + 769 "END;"); 770 771 // As of DATABASE_VERSION 55, we've removed these triggers that delete empty threads. 772 // These triggers interfere with saving drafts on brand new threads. Instead of 773 // triggers cleaning up empty threads, the empty threads should be cleaned up by 774 // an explicit call to delete with Threads.OBSOLETE_THREADS_URI. 775 776// // When the last message in a thread is deleted, these 777// // triggers ensure that the entry for its thread ID is removed 778// // from the threads table. 779// db.execSQL("CREATE TRIGGER delete_obsolete_threads_pdu " + 780// "AFTER DELETE ON pdu " + 781// "BEGIN " + 782// " DELETE FROM threads " + 783// " WHERE " + 784// " _id = old.thread_id " + 785// " AND _id NOT IN " + 786// " (SELECT thread_id FROM sms " + 787// " UNION SELECT thread_id from pdu); " + 788// "END;"); 789// 790// db.execSQL("CREATE TRIGGER delete_obsolete_threads_when_update_pdu " + 791// "AFTER UPDATE OF " + Mms.THREAD_ID + " ON pdu " + 792// "WHEN old." + Mms.THREAD_ID + " != new." + Mms.THREAD_ID + " " + 793// "BEGIN " + 794// " DELETE FROM threads " + 795// " WHERE " + 796// " _id = old.thread_id " + 797// " AND _id NOT IN " + 798// " (SELECT thread_id FROM sms " + 799// " UNION SELECT thread_id from pdu); " + 800// "END;"); 801 802 // Insert pending status for M-Notification.ind or M-ReadRec.ind 803 // when they are inserted into Inbox/Outbox. 804 db.execSQL("CREATE TRIGGER insert_mms_pending_on_insert " + 805 "AFTER INSERT ON pdu " + 806 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + 807 " OR new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_REC_IND + 808 " " + 809 "BEGIN " + 810 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 811 " (" + PendingMessages.PROTO_TYPE + "," + 812 " " + PendingMessages.MSG_ID + "," + 813 " " + PendingMessages.MSG_TYPE + "," + 814 " " + PendingMessages.ERROR_TYPE + "," + 815 " " + PendingMessages.ERROR_CODE + "," + 816 " " + PendingMessages.RETRY_INDEX + "," + 817 " " + PendingMessages.DUE_TIME + ") " + 818 " VALUES " + 819 " (" + MmsSms.MMS_PROTO + "," + 820 " new." + BaseColumns._ID + "," + 821 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 822 "END;"); 823 824 // Insert pending status for M-Send.req when it is moved into Outbox. 825 db.execSQL("CREATE TRIGGER insert_mms_pending_on_update " + 826 "AFTER UPDATE ON pdu " + 827 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + 828 " AND new." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 829 " AND old." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 830 "BEGIN " + 831 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 832 " (" + PendingMessages.PROTO_TYPE + "," + 833 " " + PendingMessages.MSG_ID + "," + 834 " " + PendingMessages.MSG_TYPE + "," + 835 " " + PendingMessages.ERROR_TYPE + "," + 836 " " + PendingMessages.ERROR_CODE + "," + 837 " " + PendingMessages.RETRY_INDEX + "," + 838 " " + PendingMessages.DUE_TIME + ") " + 839 " VALUES " + 840 " (" + MmsSms.MMS_PROTO + "," + 841 " new." + BaseColumns._ID + "," + 842 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 843 "END;"); 844 845 // When a message is moved out of Outbox, delete its pending status. 846 db.execSQL("CREATE TRIGGER delete_mms_pending_on_update " + 847 "AFTER UPDATE ON " + MmsProvider.TABLE_PDU + " " + 848 "WHEN old." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 849 " AND new." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 850 "BEGIN " + 851 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 852 " WHERE " + PendingMessages.MSG_ID + "=new._id; " + 853 "END;"); 854 855 // Delete pending status for a message when it is deleted. 856 db.execSQL("CREATE TRIGGER delete_mms_pending_on_delete " + 857 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 858 "BEGIN " + 859 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 860 " WHERE " + PendingMessages.MSG_ID + "=old._id; " + 861 "END;"); 862 863 // TODO Add triggers for SMS retry-status management. 864 865 // Update the error flag of threads when the error type of 866 // a pending MM is updated. 867 db.execSQL("CREATE TRIGGER update_threads_error_on_update_mms " + 868 " AFTER UPDATE OF err_type ON pending_msgs " + 869 " WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)" + 870 " OR (OLD.err_type >= 10 AND NEW.err_type < 10) " + 871 "BEGIN" + 872 " UPDATE threads SET error = " + 873 " CASE" + 874 " WHEN NEW.err_type >= 10 THEN error + 1" + 875 " ELSE error - 1" + 876 " END " + 877 " WHERE _id =" + 878 " (SELECT DISTINCT thread_id" + 879 " FROM pdu" + 880 " WHERE _id = NEW.msg_id); " + 881 "END;"); 882 883 // Update the error flag of threads when delete pending message. 884 db.execSQL("CREATE TRIGGER update_threads_error_on_delete_mms " + 885 " BEFORE DELETE ON pdu" + 886 " WHEN OLD._id IN (SELECT DISTINCT msg_id" + 887 " FROM pending_msgs" + 888 " WHERE err_type >= 10) " + 889 "BEGIN " + 890 " UPDATE threads SET error = error - 1" + 891 " WHERE _id = OLD.thread_id; " + 892 "END;"); 893 894 // Update the error flag of threads while moving an MM out of Outbox, 895 // which was failed to be sent permanently. 896 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 897 " BEFORE UPDATE OF msg_box ON pdu " + 898 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 899 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 900 " FROM pending_msgs" + 901 " WHERE err_type >= 10)) " + 902 "BEGIN " + 903 " UPDATE threads SET error = error - 1" + 904 " WHERE _id = OLD.thread_id; " + 905 "END;"); 906 907 // Update the error flag of threads after a text message was 908 // failed to send/receive. 909 db.execSQL("CREATE TRIGGER update_threads_error_on_update_sms " + 910 " AFTER UPDATE OF type ON sms" + 911 " WHEN (OLD.type != 5 AND NEW.type = 5)" + 912 " OR (OLD.type = 5 AND NEW.type != 5) " + 913 "BEGIN " + 914 " UPDATE threads SET error = " + 915 " CASE" + 916 " WHEN NEW.type = 5 THEN error + 1" + 917 " ELSE error - 1" + 918 " END " + 919 " WHERE _id = NEW.thread_id; " + 920 "END;"); 921 } 922 923 @Override 924 public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) { 925 Log.w(TAG, "Upgrading database from version " + oldVersion 926 + " to " + currentVersion + "."); 927 928 switch (oldVersion) { 929 case 40: 930 if (currentVersion <= 40) { 931 return; 932 } 933 934 db.beginTransaction(); 935 try { 936 upgradeDatabaseToVersion41(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 41: 946 if (currentVersion <= 41) { 947 return; 948 } 949 950 db.beginTransaction(); 951 try { 952 upgradeDatabaseToVersion42(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 42: 962 if (currentVersion <= 42) { 963 return; 964 } 965 966 db.beginTransaction(); 967 try { 968 upgradeDatabaseToVersion43(db); 969 db.setTransactionSuccessful(); 970 } catch (Throwable ex) { 971 Log.e(TAG, ex.getMessage(), ex); 972 break; 973 } finally { 974 db.endTransaction(); 975 } 976 // fall through 977 case 43: 978 if (currentVersion <= 43) { 979 return; 980 } 981 982 db.beginTransaction(); 983 try { 984 upgradeDatabaseToVersion44(db); 985 db.setTransactionSuccessful(); 986 } catch (Throwable ex) { 987 Log.e(TAG, ex.getMessage(), ex); 988 break; 989 } finally { 990 db.endTransaction(); 991 } 992 // fall through 993 case 44: 994 if (currentVersion <= 44) { 995 return; 996 } 997 998 db.beginTransaction(); 999 try { 1000 upgradeDatabaseToVersion45(db); 1001 db.setTransactionSuccessful(); 1002 } catch (Throwable ex) { 1003 Log.e(TAG, ex.getMessage(), ex); 1004 break; 1005 } finally { 1006 db.endTransaction(); 1007 } 1008 // fall through 1009 case 45: 1010 if (currentVersion <= 45) { 1011 return; 1012 } 1013 db.beginTransaction(); 1014 try { 1015 upgradeDatabaseToVersion46(db); 1016 db.setTransactionSuccessful(); 1017 } catch (Throwable ex) { 1018 Log.e(TAG, ex.getMessage(), ex); 1019 break; 1020 } finally { 1021 db.endTransaction(); 1022 } 1023 // fall through 1024 case 46: 1025 if (currentVersion <= 46) { 1026 return; 1027 } 1028 1029 db.beginTransaction(); 1030 try { 1031 upgradeDatabaseToVersion47(db); 1032 db.setTransactionSuccessful(); 1033 } catch (Throwable ex) { 1034 Log.e(TAG, ex.getMessage(), ex); 1035 break; 1036 } finally { 1037 db.endTransaction(); 1038 } 1039 // fall through 1040 case 47: 1041 if (currentVersion <= 47) { 1042 return; 1043 } 1044 1045 db.beginTransaction(); 1046 try { 1047 upgradeDatabaseToVersion48(db); 1048 db.setTransactionSuccessful(); 1049 } catch (Throwable ex) { 1050 Log.e(TAG, ex.getMessage(), ex); 1051 break; 1052 } finally { 1053 db.endTransaction(); 1054 } 1055 // fall through 1056 case 48: 1057 if (currentVersion <= 48) { 1058 return; 1059 } 1060 1061 db.beginTransaction(); 1062 try { 1063 createWordsTables(db); 1064 db.setTransactionSuccessful(); 1065 } catch (Throwable ex) { 1066 Log.e(TAG, ex.getMessage(), ex); 1067 break; 1068 } finally { 1069 db.endTransaction(); 1070 } 1071 // fall through 1072 case 49: 1073 if (currentVersion <= 49) { 1074 return; 1075 } 1076 db.beginTransaction(); 1077 try { 1078 createThreadIdIndex(db); 1079 db.setTransactionSuccessful(); 1080 } catch (Throwable ex) { 1081 Log.e(TAG, ex.getMessage(), ex); 1082 break; // force to destroy all old data; 1083 } finally { 1084 db.endTransaction(); 1085 } 1086 // fall through 1087 case 50: 1088 if (currentVersion <= 50) { 1089 return; 1090 } 1091 1092 db.beginTransaction(); 1093 try { 1094 upgradeDatabaseToVersion51(db); 1095 db.setTransactionSuccessful(); 1096 } catch (Throwable ex) { 1097 Log.e(TAG, ex.getMessage(), ex); 1098 break; 1099 } finally { 1100 db.endTransaction(); 1101 } 1102 // fall through 1103 case 51: 1104 if (currentVersion <= 51) { 1105 return; 1106 } 1107 // 52 was adding a new meta_data column, but that was removed. 1108 // fall through 1109 case 52: 1110 if (currentVersion <= 52) { 1111 return; 1112 } 1113 1114 db.beginTransaction(); 1115 try { 1116 upgradeDatabaseToVersion53(db); 1117 db.setTransactionSuccessful(); 1118 } catch (Throwable ex) { 1119 Log.e(TAG, ex.getMessage(), ex); 1120 break; 1121 } finally { 1122 db.endTransaction(); 1123 } 1124 // fall through 1125 case 53: 1126 if (currentVersion <= 53) { 1127 return; 1128 } 1129 1130 db.beginTransaction(); 1131 try { 1132 upgradeDatabaseToVersion54(db); 1133 db.setTransactionSuccessful(); 1134 } catch (Throwable ex) { 1135 Log.e(TAG, ex.getMessage(), ex); 1136 break; 1137 } finally { 1138 db.endTransaction(); 1139 } 1140 // fall through 1141 case 54: 1142 if (currentVersion <= 54) { 1143 return; 1144 } 1145 1146 db.beginTransaction(); 1147 try { 1148 upgradeDatabaseToVersion55(db); 1149 db.setTransactionSuccessful(); 1150 } catch (Throwable ex) { 1151 Log.e(TAG, ex.getMessage(), ex); 1152 break; 1153 } finally { 1154 db.endTransaction(); 1155 } 1156 return; 1157 } 1158 1159 Log.e(TAG, "Destroying all old data."); 1160 dropAll(db); 1161 onCreate(db); 1162 } 1163 1164 private void dropAll(SQLiteDatabase db) { 1165 // Clean the database out in order to start over from scratch. 1166 // We don't need to drop our triggers here because SQLite automatically 1167 // drops a trigger when its attached database is dropped. 1168 db.execSQL("DROP TABLE IF EXISTS canonical_addresses"); 1169 db.execSQL("DROP TABLE IF EXISTS threads"); 1170 db.execSQL("DROP TABLE IF EXISTS " + MmsSmsProvider.TABLE_PENDING_MSG); 1171 db.execSQL("DROP TABLE IF EXISTS sms"); 1172 db.execSQL("DROP TABLE IF EXISTS raw"); 1173 db.execSQL("DROP TABLE IF EXISTS attachments"); 1174 db.execSQL("DROP TABLE IF EXISTS thread_ids"); 1175 db.execSQL("DROP TABLE IF EXISTS sr_pending"); 1176 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PDU + ";"); 1177 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_ADDR + ";"); 1178 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PART + ";"); 1179 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_RATE + ";"); 1180 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_DRM + ";"); 1181 } 1182 1183 private void upgradeDatabaseToVersion41(SQLiteDatabase db) { 1184 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms"); 1185 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 1186 " BEFORE UPDATE OF msg_box ON pdu " + 1187 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 1188 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 1189 " FROM pending_msgs" + 1190 " WHERE err_type >= 10)) " + 1191 "BEGIN " + 1192 " UPDATE threads SET error = error - 1" + 1193 " WHERE _id = OLD.thread_id; " + 1194 "END;"); 1195 } 1196 1197 private void upgradeDatabaseToVersion42(SQLiteDatabase db) { 1198 db.execSQL("DROP TRIGGER IF EXISTS sms_update_thread_on_delete"); 1199 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_sms"); 1200 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_sms"); 1201 } 1202 1203 private void upgradeDatabaseToVersion43(SQLiteDatabase db) { 1204 // Add 'has_attachment' column to threads table. 1205 db.execSQL("ALTER TABLE threads ADD COLUMN has_attachment INTEGER DEFAULT 0"); 1206 1207 updateThreadsAttachmentColumn(db); 1208 1209 // Add insert and delete triggers for keeping it up to date. 1210 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 1211 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 1212 } 1213 1214 private void upgradeDatabaseToVersion44(SQLiteDatabase db) { 1215 updateThreadsAttachmentColumn(db); 1216 1217 // add the update trigger for keeping the threads up to date. 1218 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1219 } 1220 1221 private void upgradeDatabaseToVersion45(SQLiteDatabase db) { 1222 // Add 'locked' column to sms table. 1223 db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.LOCKED + " INTEGER DEFAULT 0"); 1224 1225 // Add 'locked' column to pdu table. 1226 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.LOCKED + " INTEGER DEFAULT 0"); 1227 } 1228 1229 private void upgradeDatabaseToVersion46(SQLiteDatabase db) { 1230 // add the "text" column for caching inline text (e.g. strings) instead of 1231 // putting them in an external file 1232 db.execSQL("ALTER TABLE part ADD COLUMN " + Part.TEXT + " TEXT"); 1233 1234 Cursor textRows = db.query( 1235 "part", 1236 new String[] { Part._ID, Part._DATA, Part.TEXT}, 1237 "ct = 'text/plain' OR ct == 'application/smil'", 1238 null, 1239 null, 1240 null, 1241 null); 1242 ArrayList<String> filesToDelete = new ArrayList<String>(); 1243 try { 1244 db.beginTransaction(); 1245 if (textRows != null) { 1246 int partDataColumn = textRows.getColumnIndex(Part._DATA); 1247 1248 // This code is imperfect in that we can't guarantee that all the 1249 // backing files get deleted. For example if the system aborts after 1250 // the database is updated but before we complete the process of 1251 // deleting files. 1252 while (textRows.moveToNext()) { 1253 String path = textRows.getString(partDataColumn); 1254 if (path != null) { 1255 try { 1256 InputStream is = new FileInputStream(path); 1257 byte [] data = new byte[is.available()]; 1258 is.read(data); 1259 EncodedStringValue v = new EncodedStringValue(data); 1260 db.execSQL("UPDATE part SET " + Part._DATA + " = NULL, " + 1261 Part.TEXT + " = ?", new String[] { v.getString() }); 1262 is.close(); 1263 filesToDelete.add(path); 1264 } catch (IOException e) { 1265 // TODO Auto-generated catch block 1266 e.printStackTrace(); 1267 } 1268 } 1269 } 1270 } 1271 db.setTransactionSuccessful(); 1272 } finally { 1273 db.endTransaction(); 1274 for (String pathToDelete : filesToDelete) { 1275 try { 1276 (new File(pathToDelete)).delete(); 1277 } catch (SecurityException ex) { 1278 Log.e(TAG, "unable to clean up old mms file for " + pathToDelete, ex); 1279 } 1280 } 1281 if (textRows != null) { 1282 textRows.close(); 1283 } 1284 } 1285 } 1286 1287 private void upgradeDatabaseToVersion47(SQLiteDatabase db) { 1288 updateThreadsAttachmentColumn(db); 1289 1290 // add the update trigger for keeping the threads up to date. 1291 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1292 } 1293 1294 private void upgradeDatabaseToVersion48(SQLiteDatabase db) { 1295 // Add 'error_code' column to sms table. 1296 db.execSQL("ALTER TABLE sms ADD COLUMN error_code INTEGER DEFAULT 0"); 1297 } 1298 1299 private void upgradeDatabaseToVersion51(SQLiteDatabase db) { 1300 db.execSQL("ALTER TABLE sms add COLUMN seen INTEGER DEFAULT 0"); 1301 db.execSQL("ALTER TABLE pdu add COLUMN seen INTEGER DEFAULT 0"); 1302 1303 try { 1304 // update the existing sms and pdu tables so the new "seen" column is the same as 1305 // the "read" column for each row. 1306 ContentValues contentValues = new ContentValues(); 1307 contentValues.put("seen", 1); 1308 int count = db.update("sms", contentValues, "read=1", null); 1309 Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count + 1310 " rows in sms table to have READ=1"); 1311 count = db.update("pdu", contentValues, "read=1", null); 1312 Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count + 1313 " rows in pdu table to have READ=1"); 1314 } catch (Exception ex) { 1315 Log.e(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51 caught ", ex); 1316 } 1317 } 1318 1319 private void upgradeDatabaseToVersion53(SQLiteDatabase db) { 1320 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_read_on_update"); 1321 1322 // Updates threads table whenever a message in pdu is updated. 1323 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 1324 " UPDATE OF " + Mms.READ + 1325 " ON " + MmsProvider.TABLE_PDU + " " + 1326 PDU_UPDATE_THREAD_CONSTRAINTS + 1327 "BEGIN " + 1328 PDU_UPDATE_THREAD_READ_BODY + 1329 "END;"); 1330 } 1331 1332 private void upgradeDatabaseToVersion54(SQLiteDatabase db) { 1333 // Add 'date_sent' column to sms table. 1334 db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.DATE_SENT + " INTEGER DEFAULT 0"); 1335 1336 // Add 'date_sent' column to pdu table. 1337 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.DATE_SENT + " INTEGER DEFAULT 0"); 1338 } 1339 1340 private void upgradeDatabaseToVersion55(SQLiteDatabase db) { 1341 // Drop removed triggers 1342 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_pdu"); 1343 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_when_update_pdu"); 1344 } 1345 1346 @Override 1347 public synchronized SQLiteDatabase getWritableDatabase() { 1348 SQLiteDatabase db = super.getWritableDatabase(); 1349 1350 if (!sTriedAutoIncrement) { 1351 sTriedAutoIncrement = true; 1352 boolean hasAutoIncrement = hasAutoIncrement(db); 1353 Log.d(TAG, "[getWritableDatabase] hasAutoIncrement: " + hasAutoIncrement); 1354 if (!hasAutoIncrement) { 1355 db.beginTransaction(); 1356 try { 1357 if (false && sFakeLowStorageTest) { 1358 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1359 " - fake exception"); 1360 throw new Exception("FakeLowStorageTest"); 1361 } 1362 upgradeThreadsTableToAutoIncrement(db); 1363 db.setTransactionSuccessful(); 1364 1365 if (mLowStorageMonitor != null) { 1366 // We've already updated the database. This receiver is no longer necessary. 1367 Log.d(TAG, "Unregistering mLowStorageMonitor - we've upgraded"); 1368 mContext.unregisterReceiver(mLowStorageMonitor); 1369 mLowStorageMonitor = null; 1370 } 1371 } catch (Throwable ex) { 1372 Log.e(TAG, "Failed to add autoIncrement: " + ex.getMessage(), ex); 1373 1374 if (sFakeLowStorageTest) { 1375 sFakeLowStorageTest = false; 1376 } 1377 1378 // We failed, perhaps because of low storage. Turn on a receiver to watch for 1379 // storage space. 1380 if (mLowStorageMonitor == null) { 1381 Log.d(TAG, "[getWritableDatabase] turning on storage monitor"); 1382 mLowStorageMonitor = new LowStorageMonitor(); 1383 IntentFilter intentFilter = new IntentFilter(); 1384 intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_LOW); 1385 intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_OK); 1386 mContext.registerReceiver(mLowStorageMonitor, intentFilter); 1387 } 1388 } finally { 1389 db.endTransaction(); 1390 } 1391 } 1392 } 1393 return db; 1394 } 1395 1396 private boolean hasAutoIncrement(SQLiteDatabase db) { 1397 boolean result = false; 1398 String query = "SELECT sql FROM sqlite_master WHERE type='table' AND name='threads'"; 1399 Cursor c = db.rawQuery(query, null); 1400 if (c != null) { 1401 try { 1402 if (c.moveToFirst()) { 1403 String schema = c.getString(0); 1404 result = schema != null ? schema.contains("AUTOINCREMENT") : false; 1405 Log.d(TAG, "[MmsSmsDb] hasAutoIncrement: " + schema + " result: " + result); 1406 } 1407 } finally { 1408 c.close(); 1409 } 1410 } 1411 return result; 1412 } 1413 1414 // upgradeThreadsTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1415 // the threads table. This could fail if the user has a lot of conversations and not enough 1416 // storage to make a copy of the threads table. That's ok. This upgrade is optional. It'll 1417 // be called again next time the device is rebooted. 1418 private void upgradeThreadsTableToAutoIncrement(SQLiteDatabase db) { 1419 if (hasAutoIncrement(db)) { 1420 Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: already upgraded"); 1421 return; 1422 } 1423 Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: upgrading"); 1424 1425 // Make the _id of the threads table autoincrement so we never re-use thread ids 1426 // Have to create a new temp threads table. Copy all the info from the old table. 1427 // Drop the old table and rename the new table to that of the old. 1428 db.execSQL("CREATE TABLE threads_temp (" + 1429 Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1430 Threads.DATE + " INTEGER DEFAULT 0," + 1431 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 1432 Threads.RECIPIENT_IDS + " TEXT," + 1433 Threads.SNIPPET + " TEXT," + 1434 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 1435 Threads.READ + " INTEGER DEFAULT 1," + 1436 Threads.TYPE + " INTEGER DEFAULT 0," + 1437 Threads.ERROR + " INTEGER DEFAULT 0," + 1438 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 1439 1440 db.execSQL("INSERT INTO threads_temp SELECT * from threads;"); 1441 db.execSQL("DROP TABLE threads;"); 1442 db.execSQL("ALTER TABLE threads_temp RENAME TO threads;"); 1443 } 1444 1445 private class LowStorageMonitor extends BroadcastReceiver { 1446 1447 public LowStorageMonitor() { 1448 } 1449 1450 public void onReceive(Context context, Intent intent) { 1451 String action = intent.getAction(); 1452 1453 Log.d(TAG, "[LowStorageMonitor] onReceive intent " + action); 1454 1455 if (Intent.ACTION_DEVICE_STORAGE_OK.equals(action)) { 1456 sTriedAutoIncrement = false; // try to upgrade on the next getWriteableDatabase 1457 } 1458 } 1459 } 1460 1461 private void updateThreadsAttachmentColumn(SQLiteDatabase db) { 1462 // Set the values of that column correctly based on the current 1463 // contents of the database. 1464 db.execSQL("UPDATE threads SET has_attachment=1 WHERE _id IN " + 1465 " (SELECT DISTINCT pdu.thread_id FROM part " + 1466 " JOIN pdu ON pdu._id=part.mid " + 1467 " WHERE part.ct != 'text/plain' AND part.ct != 'application/smil')"); 1468 } 1469} 1470