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