1 /* 2 * Copyright (C) 2008 The Android Open Source Project 3 * 4 * Licensed under the Apache License, Version 2.0 (the "License"); 5 * you may not use this file except in compliance with the License. 6 * You may obtain a copy of the License at 7 * 8 * http://www.apache.org/licenses/LICENSE-2.0 9 * 10 * Unless required by applicable law or agreed to in writing, software 11 * distributed under the License is distributed on an "AS IS" BASIS, 12 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. 13 * See the License for the specific language governing permissions and 14 * limitations under the License. 15 */ 16 17 package com.android.providers.telephony; 18 19 import java.io.IOException; 20 import java.io.InputStream; 21 import java.io.FileInputStream; 22 import java.io.File; 23 import java.util.ArrayList; 24 import java.util.HashSet; 25 import java.util.Iterator; 26 27 import android.content.BroadcastReceiver; 28 import android.content.ContentValues; 29 import android.content.Context; 30 import android.content.Intent; 31 import android.content.IntentFilter; 32 import android.database.Cursor; 33 import android.database.sqlite.SQLiteDatabase; 34 import android.database.sqlite.SQLiteOpenHelper; 35 import android.provider.BaseColumns; 36 import android.provider.Telephony; 37 import android.provider.Telephony.Mms; 38 import android.provider.Telephony.MmsSms; 39 import android.provider.Telephony.Sms; 40 import android.provider.Telephony.Threads; 41 import android.provider.Telephony.Mms.Addr; 42 import android.provider.Telephony.Mms.Part; 43 import android.provider.Telephony.Mms.Rate; 44 import android.provider.Telephony.MmsSms.PendingMessages; 45 import android.telephony.SubscriptionManager; 46 import android.util.Log; 47 48 import com.google.android.mms.pdu.EncodedStringValue; 49 import com.google.android.mms.pdu.PduHeaders; 50 51 public 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 = 60; 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 635 // Unlike the other trigger-creating functions, this function can be called multiple times 636 // without harm. 637 private void createMmsTriggers(SQLiteDatabase db) { 638 // Cleans up parts when a MM is deleted. 639 db.execSQL("DROP TRIGGER IF EXISTS part_cleanup"); 640 db.execSQL("CREATE TRIGGER part_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 641 "BEGIN " + 642 " DELETE FROM " + MmsProvider.TABLE_PART + 643 " WHERE " + Part.MSG_ID + "=old._id;" + 644 "END;"); 645 646 // Cleans up address info when a MM is deleted. 647 db.execSQL("DROP TRIGGER IF EXISTS addr_cleanup"); 648 db.execSQL("CREATE TRIGGER addr_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " + 649 "BEGIN " + 650 " DELETE FROM " + MmsProvider.TABLE_ADDR + 651 " WHERE " + Addr.MSG_ID + "=old._id;" + 652 "END;"); 653 654 // Delete obsolete delivery-report, read-report while deleting their 655 // associated Send.req. 656 db.execSQL("DROP TRIGGER IF EXISTS cleanup_delivery_and_read_report"); 657 db.execSQL("CREATE TRIGGER cleanup_delivery_and_read_report " + 658 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 659 "WHEN old." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + " " + 660 "BEGIN " + 661 " DELETE FROM " + MmsProvider.TABLE_PDU + 662 " WHERE (" + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_DELIVERY_IND + 663 " OR " + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_ORIG_IND + 664 ")" + 665 " AND " + Mms.MESSAGE_ID + "=old." + Mms.MESSAGE_ID + "; " + 666 "END;"); 667 668 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_insert_part"); 669 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 670 671 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_update_part"); 672 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 673 674 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_delete_part"); 675 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 676 677 db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_update_pdu"); 678 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 679 680 // Delete pending status for a message when it is deleted. 681 db.execSQL("DROP TRIGGER IF EXISTS delete_mms_pending_on_delete"); 682 db.execSQL("CREATE TRIGGER delete_mms_pending_on_delete " + 683 "AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " + 684 "BEGIN " + 685 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 686 " WHERE " + PendingMessages.MSG_ID + "=old._id; " + 687 "END;"); 688 689 // When a message is moved out of Outbox, delete its pending status. 690 db.execSQL("DROP TRIGGER IF EXISTS delete_mms_pending_on_update"); 691 db.execSQL("CREATE TRIGGER delete_mms_pending_on_update " + 692 "AFTER UPDATE ON " + MmsProvider.TABLE_PDU + " " + 693 "WHEN old." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 694 " AND new." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 695 "BEGIN " + 696 " DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG + 697 " WHERE " + PendingMessages.MSG_ID + "=new._id; " + 698 "END;"); 699 700 // Insert pending status for M-Notification.ind or M-ReadRec.ind 701 // when they are inserted into Inbox/Outbox. 702 db.execSQL("DROP TRIGGER IF EXISTS insert_mms_pending_on_insert"); 703 db.execSQL("CREATE TRIGGER insert_mms_pending_on_insert " + 704 "AFTER INSERT ON pdu " + 705 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND + 706 " OR new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_REC_IND + 707 " " + 708 "BEGIN " + 709 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 710 " (" + PendingMessages.PROTO_TYPE + "," + 711 " " + PendingMessages.MSG_ID + "," + 712 " " + PendingMessages.MSG_TYPE + "," + 713 " " + PendingMessages.ERROR_TYPE + "," + 714 " " + PendingMessages.ERROR_CODE + "," + 715 " " + PendingMessages.RETRY_INDEX + "," + 716 " " + PendingMessages.DUE_TIME + ") " + 717 " VALUES " + 718 " (" + MmsSms.MMS_PROTO + "," + 719 " new." + BaseColumns._ID + "," + 720 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 721 "END;"); 722 723 724 // Insert pending status for M-Send.req when it is moved into Outbox. 725 db.execSQL("DROP TRIGGER IF EXISTS insert_mms_pending_on_update"); 726 db.execSQL("CREATE TRIGGER insert_mms_pending_on_update " + 727 "AFTER UPDATE ON pdu " + 728 "WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + 729 " AND new." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX + 730 " AND old." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " + 731 "BEGIN " + 732 " INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG + 733 " (" + PendingMessages.PROTO_TYPE + "," + 734 " " + PendingMessages.MSG_ID + "," + 735 " " + PendingMessages.MSG_TYPE + "," + 736 " " + PendingMessages.ERROR_TYPE + "," + 737 " " + PendingMessages.ERROR_CODE + "," + 738 " " + PendingMessages.RETRY_INDEX + "," + 739 " " + PendingMessages.DUE_TIME + ") " + 740 " VALUES " + 741 " (" + MmsSms.MMS_PROTO + "," + 742 " new." + BaseColumns._ID + "," + 743 " new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" + 744 "END;"); 745 746 // monitor the mms table 747 db.execSQL("DROP TRIGGER IF EXISTS mms_words_update"); 748 db.execSQL("CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words " + 749 " SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " + 750 " END;"); 751 752 db.execSQL("DROP TRIGGER IF EXISTS mms_words_delete"); 753 db.execSQL("CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM " + 754 " words WHERE source_id = OLD._id AND table_to_use = 2; END;"); 755 756 // Updates threads table whenever a message in pdu is updated. 757 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_date_subject_on_update"); 758 db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" + 759 " UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX + 760 " ON " + MmsProvider.TABLE_PDU + " " + 761 PDU_UPDATE_THREAD_CONSTRAINTS + 762 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 763 764 // Update threads table whenever a message in pdu is deleted 765 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_delete"); 766 db.execSQL("CREATE TRIGGER pdu_update_thread_on_delete " + 767 "AFTER DELETE ON pdu " + 768 "BEGIN " + 769 " UPDATE threads SET " + 770 " date = (strftime('%s','now') * 1000)" + 771 " WHERE threads._id = old." + Mms.THREAD_ID + "; " + 772 UPDATE_THREAD_COUNT_ON_OLD + 773 UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE + 774 "END;"); 775 776 // Updates threads table whenever a message is added to pdu. 777 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_insert"); 778 db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " + 779 MmsProvider.TABLE_PDU + " " + 780 PDU_UPDATE_THREAD_CONSTRAINTS + 781 PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 782 783 // Updates threads table whenever a message in pdu is updated. 784 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_read_on_update"); 785 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 786 " UPDATE OF " + Mms.READ + 787 " ON " + MmsProvider.TABLE_PDU + " " + 788 PDU_UPDATE_THREAD_CONSTRAINTS + 789 "BEGIN " + 790 PDU_UPDATE_THREAD_READ_BODY + 791 "END;"); 792 793 // Update the error flag of threads when delete pending message. 794 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_mms"); 795 db.execSQL("CREATE TRIGGER update_threads_error_on_delete_mms " + 796 " BEFORE DELETE ON pdu" + 797 " WHEN OLD._id IN (SELECT DISTINCT msg_id" + 798 " FROM pending_msgs" + 799 " WHERE err_type >= 10) " + 800 "BEGIN " + 801 " UPDATE threads SET error = error - 1" + 802 " WHERE _id = OLD.thread_id; " + 803 "END;"); 804 805 // Update the error flag of threads while moving an MM out of Outbox, 806 // which was failed to be sent permanently. 807 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms"); 808 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 809 " BEFORE UPDATE OF msg_box ON pdu " + 810 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 811 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 812 " FROM pending_msgs" + 813 " WHERE err_type >= 10)) " + 814 "BEGIN " + 815 " UPDATE threads SET error = error - 1" + 816 " WHERE _id = OLD.thread_id; " + 817 "END;"); 818 } 819 820 private void createSmsTables(SQLiteDatabase db) { 821 // N.B.: Whenever the columns here are changed, the columns in 822 // {@ref MmsSmsProvider} must be changed to match. 823 db.execSQL("CREATE TABLE sms (" + 824 "_id INTEGER PRIMARY KEY," + 825 "thread_id INTEGER," + 826 "address TEXT," + 827 "person INTEGER," + 828 "date INTEGER," + 829 "date_sent INTEGER DEFAULT 0," + 830 "protocol INTEGER," + 831 "read INTEGER DEFAULT 0," + 832 "status INTEGER DEFAULT -1," + // a TP-Status value 833 // or -1 if it 834 // status hasn't 835 // been received 836 "type INTEGER," + 837 "reply_path_present INTEGER," + 838 "subject TEXT," + 839 "body TEXT," + 840 "service_center TEXT," + 841 "locked INTEGER DEFAULT 0," + 842 "sub_id INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 843 "error_code INTEGER DEFAULT 0," + 844 "creator TEXT," + 845 "seen INTEGER DEFAULT 0" + 846 ");"); 847 848 /** 849 * This table is used by the SMS dispatcher to hold 850 * incomplete partial messages until all the parts arrive. 851 */ 852 db.execSQL("CREATE TABLE raw (" + 853 "_id INTEGER PRIMARY KEY," + 854 "date INTEGER," + 855 "reference_number INTEGER," + // one per full message 856 "count INTEGER," + // the number of parts 857 "sequence INTEGER," + // the part number of this message 858 "destination_port INTEGER," + 859 "address TEXT," + 860 "sub_id INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 861 "pdu TEXT);"); // the raw PDU for this part 862 863 db.execSQL("CREATE TABLE attachments (" + 864 "sms_id INTEGER," + 865 "content_url TEXT," + 866 "offset INTEGER);"); 867 868 /** 869 * This table is used by the SMS dispatcher to hold pending 870 * delivery status report intents. 871 */ 872 db.execSQL("CREATE TABLE sr_pending (" + 873 "reference_number INTEGER," + 874 "action TEXT," + 875 "data TEXT);"); 876 } 877 878 private void createCommonTables(SQLiteDatabase db) { 879 // TODO Ensure that each entry is removed when the last use of 880 // any address equivalent to its address is removed. 881 882 /** 883 * This table maps the first instance seen of any particular 884 * MMS/SMS address to an ID, which is then used as its 885 * canonical representation. If the same address or an 886 * equivalent address (as determined by our Sqlite 887 * PHONE_NUMBERS_EQUAL extension) is seen later, this same ID 888 * will be used. The _id is created with AUTOINCREMENT so it 889 * will never be reused again if a recipient is deleted. 890 */ 891 db.execSQL("CREATE TABLE canonical_addresses (" + 892 "_id INTEGER PRIMARY KEY AUTOINCREMENT," + 893 "address TEXT);"); 894 895 /** 896 * This table maps the subject and an ordered set of recipient 897 * IDs, separated by spaces, to a unique thread ID. The IDs 898 * come from the canonical_addresses table. This works 899 * because messages are considered to be part of the same 900 * thread if they have the same subject (or a null subject) 901 * and the same set of recipients. 902 */ 903 db.execSQL("CREATE TABLE threads (" + 904 Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 905 Threads.DATE + " INTEGER DEFAULT 0," + 906 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 907 Threads.RECIPIENT_IDS + " TEXT," + 908 Threads.SNIPPET + " TEXT," + 909 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 910 Threads.READ + " INTEGER DEFAULT 1," + 911 Threads.ARCHIVED + " INTEGER DEFAULT 0," + 912 Threads.TYPE + " INTEGER DEFAULT 0," + 913 Threads.ERROR + " INTEGER DEFAULT 0," + 914 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 915 916 /** 917 * This table stores the queue of messages to be sent/downloaded. 918 */ 919 db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" + 920 PendingMessages._ID + " INTEGER PRIMARY KEY," + 921 PendingMessages.PROTO_TYPE + " INTEGER," + 922 PendingMessages.MSG_ID + " INTEGER," + 923 PendingMessages.MSG_TYPE + " INTEGER," + 924 PendingMessages.ERROR_TYPE + " INTEGER," + 925 PendingMessages.ERROR_CODE + " INTEGER," + 926 PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," + 927 PendingMessages.DUE_TIME + " INTEGER," + 928 PendingMessages.SUBSCRIPTION_ID + " INTEGER DEFAULT " + 929 SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 930 PendingMessages.LAST_TRY + " INTEGER);"); 931 932 } 933 934 // TODO Check the query plans for these triggers. 935 private void createCommonTriggers(SQLiteDatabase db) { 936 // Updates threads table whenever a message is added to sms. 937 db.execSQL("CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms " + 938 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 939 940 // Updates threads table whenever a message in sms is updated. 941 db.execSQL("CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER" + 942 " UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE + 943 " ON sms " + 944 SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE); 945 946 // Updates threads table whenever a message in sms is updated. 947 db.execSQL("CREATE TRIGGER sms_update_thread_read_on_update AFTER" + 948 " UPDATE OF " + Sms.READ + 949 " ON sms " + 950 "BEGIN " + 951 SMS_UPDATE_THREAD_READ_BODY + 952 "END;"); 953 954 // As of DATABASE_VERSION 55, we've removed these triggers that delete empty threads. 955 // These triggers interfere with saving drafts on brand new threads. Instead of 956 // triggers cleaning up empty threads, the empty threads should be cleaned up by 957 // an explicit call to delete with Threads.OBSOLETE_THREADS_URI. 958 959 // // When the last message in a thread is deleted, these 960 // // triggers ensure that the entry for its thread ID is removed 961 // // from the threads table. 962 // db.execSQL("CREATE TRIGGER delete_obsolete_threads_pdu " + 963 // "AFTER DELETE ON pdu " + 964 // "BEGIN " + 965 // " DELETE FROM threads " + 966 // " WHERE " + 967 // " _id = old.thread_id " + 968 // " AND _id NOT IN " + 969 // " (SELECT thread_id FROM sms " + 970 // " UNION SELECT thread_id from pdu); " + 971 // "END;"); 972 // 973 // db.execSQL("CREATE TRIGGER delete_obsolete_threads_when_update_pdu " + 974 // "AFTER UPDATE OF " + Mms.THREAD_ID + " ON pdu " + 975 // "WHEN old." + Mms.THREAD_ID + " != new." + Mms.THREAD_ID + " " + 976 // "BEGIN " + 977 // " DELETE FROM threads " + 978 // " WHERE " + 979 // " _id = old.thread_id " + 980 // " AND _id NOT IN " + 981 // " (SELECT thread_id FROM sms " + 982 // " UNION SELECT thread_id from pdu); " + 983 // "END;"); 984 985 // TODO Add triggers for SMS retry-status management. 986 987 // Update the error flag of threads when the error type of 988 // a pending MM is updated. 989 db.execSQL("CREATE TRIGGER update_threads_error_on_update_mms " + 990 " AFTER UPDATE OF err_type ON pending_msgs " + 991 " WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)" + 992 " OR (OLD.err_type >= 10 AND NEW.err_type < 10) " + 993 "BEGIN" + 994 " UPDATE threads SET error = " + 995 " CASE" + 996 " WHEN NEW.err_type >= 10 THEN error + 1" + 997 " ELSE error - 1" + 998 " END " + 999 " WHERE _id =" + 1000 " (SELECT DISTINCT thread_id" + 1001 " FROM pdu" + 1002 " WHERE _id = NEW.msg_id); " + 1003 "END;"); 1004 1005 // Update the error flag of threads after a text message was 1006 // failed to send/receive. 1007 db.execSQL("CREATE TRIGGER update_threads_error_on_update_sms " + 1008 " AFTER UPDATE OF type ON sms" + 1009 " WHEN (OLD.type != 5 AND NEW.type = 5)" + 1010 " OR (OLD.type = 5 AND NEW.type != 5) " + 1011 "BEGIN " + 1012 " UPDATE threads SET error = " + 1013 " CASE" + 1014 " WHEN NEW.type = 5 THEN error + 1" + 1015 " ELSE error - 1" + 1016 " END " + 1017 " WHERE _id = NEW.thread_id; " + 1018 "END;"); 1019 } 1020 1021 @Override 1022 public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) { 1023 Log.w(TAG, "Upgrading database from version " + oldVersion 1024 + " to " + currentVersion + "."); 1025 1026 switch (oldVersion) { 1027 case 40: 1028 if (currentVersion <= 40) { 1029 return; 1030 } 1031 1032 db.beginTransaction(); 1033 try { 1034 upgradeDatabaseToVersion41(db); 1035 db.setTransactionSuccessful(); 1036 } catch (Throwable ex) { 1037 Log.e(TAG, ex.getMessage(), ex); 1038 break; 1039 } finally { 1040 db.endTransaction(); 1041 } 1042 // fall through 1043 case 41: 1044 if (currentVersion <= 41) { 1045 return; 1046 } 1047 1048 db.beginTransaction(); 1049 try { 1050 upgradeDatabaseToVersion42(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 42: 1060 if (currentVersion <= 42) { 1061 return; 1062 } 1063 1064 db.beginTransaction(); 1065 try { 1066 upgradeDatabaseToVersion43(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 43: 1076 if (currentVersion <= 43) { 1077 return; 1078 } 1079 1080 db.beginTransaction(); 1081 try { 1082 upgradeDatabaseToVersion44(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 44: 1092 if (currentVersion <= 44) { 1093 return; 1094 } 1095 1096 db.beginTransaction(); 1097 try { 1098 upgradeDatabaseToVersion45(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 45: 1108 if (currentVersion <= 45) { 1109 return; 1110 } 1111 db.beginTransaction(); 1112 try { 1113 upgradeDatabaseToVersion46(db); 1114 db.setTransactionSuccessful(); 1115 } catch (Throwable ex) { 1116 Log.e(TAG, ex.getMessage(), ex); 1117 break; 1118 } finally { 1119 db.endTransaction(); 1120 } 1121 // fall through 1122 case 46: 1123 if (currentVersion <= 46) { 1124 return; 1125 } 1126 1127 db.beginTransaction(); 1128 try { 1129 upgradeDatabaseToVersion47(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 47: 1139 if (currentVersion <= 47) { 1140 return; 1141 } 1142 1143 db.beginTransaction(); 1144 try { 1145 upgradeDatabaseToVersion48(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 48: 1155 if (currentVersion <= 48) { 1156 return; 1157 } 1158 1159 db.beginTransaction(); 1160 try { 1161 createWordsTables(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 49: 1171 if (currentVersion <= 49) { 1172 return; 1173 } 1174 db.beginTransaction(); 1175 try { 1176 createThreadIdIndex(db); 1177 db.setTransactionSuccessful(); 1178 } catch (Throwable ex) { 1179 Log.e(TAG, ex.getMessage(), ex); 1180 break; // force to destroy all old data; 1181 } finally { 1182 db.endTransaction(); 1183 } 1184 // fall through 1185 case 50: 1186 if (currentVersion <= 50) { 1187 return; 1188 } 1189 1190 db.beginTransaction(); 1191 try { 1192 upgradeDatabaseToVersion51(db); 1193 db.setTransactionSuccessful(); 1194 } catch (Throwable ex) { 1195 Log.e(TAG, ex.getMessage(), ex); 1196 break; 1197 } finally { 1198 db.endTransaction(); 1199 } 1200 // fall through 1201 case 51: 1202 if (currentVersion <= 51) { 1203 return; 1204 } 1205 // 52 was adding a new meta_data column, but that was removed. 1206 // fall through 1207 case 52: 1208 if (currentVersion <= 52) { 1209 return; 1210 } 1211 1212 db.beginTransaction(); 1213 try { 1214 upgradeDatabaseToVersion53(db); 1215 db.setTransactionSuccessful(); 1216 } catch (Throwable ex) { 1217 Log.e(TAG, ex.getMessage(), ex); 1218 break; 1219 } finally { 1220 db.endTransaction(); 1221 } 1222 // fall through 1223 case 53: 1224 if (currentVersion <= 53) { 1225 return; 1226 } 1227 1228 db.beginTransaction(); 1229 try { 1230 upgradeDatabaseToVersion54(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 54: 1240 if (currentVersion <= 54) { 1241 return; 1242 } 1243 1244 db.beginTransaction(); 1245 try { 1246 upgradeDatabaseToVersion55(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 55: 1256 if (currentVersion <= 55) { 1257 return; 1258 } 1259 1260 db.beginTransaction(); 1261 try { 1262 upgradeDatabaseToVersion56(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 56: 1272 if (currentVersion <= 56) { 1273 return; 1274 } 1275 1276 db.beginTransaction(); 1277 try { 1278 upgradeDatabaseToVersion57(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 57: 1288 if (currentVersion <= 57) { 1289 return; 1290 } 1291 1292 db.beginTransaction(); 1293 try { 1294 upgradeDatabaseToVersion58(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 58: 1304 if (currentVersion <= 58) { 1305 return; 1306 } 1307 1308 db.beginTransaction(); 1309 try { 1310 upgradeDatabaseToVersion59(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 59: 1320 if (currentVersion <= 59) { 1321 return; 1322 } 1323 1324 db.beginTransaction(); 1325 try { 1326 upgradeDatabaseToVersion60(db); 1327 db.setTransactionSuccessful(); 1328 } catch (Throwable ex) { 1329 Log.e(TAG, ex.getMessage(), ex); 1330 break; 1331 } finally { 1332 db.endTransaction(); 1333 } 1334 return; 1335 } 1336 1337 Log.e(TAG, "Destroying all old data."); 1338 dropAll(db); 1339 onCreate(db); 1340 } 1341 1342 private void dropAll(SQLiteDatabase db) { 1343 // Clean the database out in order to start over from scratch. 1344 // We don't need to drop our triggers here because SQLite automatically 1345 // drops a trigger when its attached database is dropped. 1346 db.execSQL("DROP TABLE IF EXISTS canonical_addresses"); 1347 db.execSQL("DROP TABLE IF EXISTS threads"); 1348 db.execSQL("DROP TABLE IF EXISTS " + MmsSmsProvider.TABLE_PENDING_MSG); 1349 db.execSQL("DROP TABLE IF EXISTS sms"); 1350 db.execSQL("DROP TABLE IF EXISTS raw"); 1351 db.execSQL("DROP TABLE IF EXISTS attachments"); 1352 db.execSQL("DROP TABLE IF EXISTS thread_ids"); 1353 db.execSQL("DROP TABLE IF EXISTS sr_pending"); 1354 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PDU + ";"); 1355 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_ADDR + ";"); 1356 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PART + ";"); 1357 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_RATE + ";"); 1358 db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_DRM + ";"); 1359 } 1360 1361 private void upgradeDatabaseToVersion41(SQLiteDatabase db) { 1362 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms"); 1363 db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " + 1364 " BEFORE UPDATE OF msg_box ON pdu " + 1365 " WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " + 1366 " AND (OLD._id IN (SELECT DISTINCT msg_id" + 1367 " FROM pending_msgs" + 1368 " WHERE err_type >= 10)) " + 1369 "BEGIN " + 1370 " UPDATE threads SET error = error - 1" + 1371 " WHERE _id = OLD.thread_id; " + 1372 "END;"); 1373 } 1374 1375 private void upgradeDatabaseToVersion42(SQLiteDatabase db) { 1376 db.execSQL("DROP TRIGGER IF EXISTS sms_update_thread_on_delete"); 1377 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_sms"); 1378 db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_delete_sms"); 1379 } 1380 1381 private void upgradeDatabaseToVersion43(SQLiteDatabase db) { 1382 // Add 'has_attachment' column to threads table. 1383 db.execSQL("ALTER TABLE threads ADD COLUMN has_attachment INTEGER DEFAULT 0"); 1384 1385 updateThreadsAttachmentColumn(db); 1386 1387 // Add insert and delete triggers for keeping it up to date. 1388 db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER); 1389 db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER); 1390 } 1391 1392 private void upgradeDatabaseToVersion44(SQLiteDatabase db) { 1393 updateThreadsAttachmentColumn(db); 1394 1395 // add the update trigger for keeping the threads up to date. 1396 db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1397 } 1398 1399 private void upgradeDatabaseToVersion45(SQLiteDatabase db) { 1400 // Add 'locked' column to sms table. 1401 db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.LOCKED + " INTEGER DEFAULT 0"); 1402 1403 // Add 'locked' column to pdu table. 1404 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.LOCKED + " INTEGER DEFAULT 0"); 1405 } 1406 1407 private void upgradeDatabaseToVersion46(SQLiteDatabase db) { 1408 // add the "text" column for caching inline text (e.g. strings) instead of 1409 // putting them in an external file 1410 db.execSQL("ALTER TABLE part ADD COLUMN " + Part.TEXT + " TEXT"); 1411 1412 Cursor textRows = db.query( 1413 "part", 1414 new String[] { Part._ID, Part._DATA, Part.TEXT}, 1415 "ct = 'text/plain' OR ct == 'application/smil'", 1416 null, 1417 null, 1418 null, 1419 null); 1420 ArrayList<String> filesToDelete = new ArrayList<String>(); 1421 try { 1422 db.beginTransaction(); 1423 if (textRows != null) { 1424 int partDataColumn = textRows.getColumnIndex(Part._DATA); 1425 1426 // This code is imperfect in that we can't guarantee that all the 1427 // backing files get deleted. For example if the system aborts after 1428 // the database is updated but before we complete the process of 1429 // deleting files. 1430 while (textRows.moveToNext()) { 1431 String path = textRows.getString(partDataColumn); 1432 if (path != null) { 1433 try { 1434 InputStream is = new FileInputStream(path); 1435 byte [] data = new byte[is.available()]; 1436 is.read(data); 1437 EncodedStringValue v = new EncodedStringValue(data); 1438 db.execSQL("UPDATE part SET " + Part._DATA + " = NULL, " + 1439 Part.TEXT + " = ?", new String[] { v.getString() }); 1440 is.close(); 1441 filesToDelete.add(path); 1442 } catch (IOException e) { 1443 // TODO Auto-generated catch block 1444 e.printStackTrace(); 1445 } 1446 } 1447 } 1448 } 1449 db.setTransactionSuccessful(); 1450 } finally { 1451 db.endTransaction(); 1452 for (String pathToDelete : filesToDelete) { 1453 try { 1454 (new File(pathToDelete)).delete(); 1455 } catch (SecurityException ex) { 1456 Log.e(TAG, "unable to clean up old mms file for " + pathToDelete, ex); 1457 } 1458 } 1459 if (textRows != null) { 1460 textRows.close(); 1461 } 1462 } 1463 } 1464 1465 private void upgradeDatabaseToVersion47(SQLiteDatabase db) { 1466 updateThreadsAttachmentColumn(db); 1467 1468 // add the update trigger for keeping the threads up to date. 1469 db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER); 1470 } 1471 1472 private void upgradeDatabaseToVersion48(SQLiteDatabase db) { 1473 // Add 'error_code' column to sms table. 1474 db.execSQL("ALTER TABLE sms ADD COLUMN error_code INTEGER DEFAULT 0"); 1475 } 1476 1477 private void upgradeDatabaseToVersion51(SQLiteDatabase db) { 1478 db.execSQL("ALTER TABLE sms add COLUMN seen INTEGER DEFAULT 0"); 1479 db.execSQL("ALTER TABLE pdu add COLUMN seen INTEGER DEFAULT 0"); 1480 1481 try { 1482 // update the existing sms and pdu tables so the new "seen" column is the same as 1483 // the "read" column for each row. 1484 ContentValues contentValues = new ContentValues(); 1485 contentValues.put("seen", 1); 1486 int count = db.update("sms", contentValues, "read=1", null); 1487 Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count + 1488 " rows in sms table to have READ=1"); 1489 count = db.update("pdu", contentValues, "read=1", null); 1490 Log.d(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51: updated " + count + 1491 " rows in pdu table to have READ=1"); 1492 } catch (Exception ex) { 1493 Log.e(TAG, "[MmsSmsDb] upgradeDatabaseToVersion51 caught ", ex); 1494 } 1495 } 1496 1497 private void upgradeDatabaseToVersion53(SQLiteDatabase db) { 1498 db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_read_on_update"); 1499 1500 // Updates threads table whenever a message in pdu is updated. 1501 db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" + 1502 " UPDATE OF " + Mms.READ + 1503 " ON " + MmsProvider.TABLE_PDU + " " + 1504 PDU_UPDATE_THREAD_CONSTRAINTS + 1505 "BEGIN " + 1506 PDU_UPDATE_THREAD_READ_BODY + 1507 "END;"); 1508 } 1509 1510 private void upgradeDatabaseToVersion54(SQLiteDatabase db) { 1511 // Add 'date_sent' column to sms table. 1512 db.execSQL("ALTER TABLE sms ADD COLUMN " + Sms.DATE_SENT + " INTEGER DEFAULT 0"); 1513 1514 // Add 'date_sent' column to pdu table. 1515 db.execSQL("ALTER TABLE pdu ADD COLUMN " + Mms.DATE_SENT + " INTEGER DEFAULT 0"); 1516 } 1517 1518 private void upgradeDatabaseToVersion55(SQLiteDatabase db) { 1519 // Drop removed triggers 1520 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_pdu"); 1521 db.execSQL("DROP TRIGGER IF EXISTS delete_obsolete_threads_when_update_pdu"); 1522 } 1523 1524 private void upgradeDatabaseToVersion56(SQLiteDatabase db) { 1525 // Add 'text_only' column to pdu table. 1526 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU + " ADD COLUMN " + Mms.TEXT_ONLY + 1527 " INTEGER DEFAULT 0"); 1528 } 1529 1530 private void upgradeDatabaseToVersion57(SQLiteDatabase db) { 1531 // Clear out bad rows, those with empty threadIds, from the pdu table. 1532 db.execSQL("DELETE FROM " + MmsProvider.TABLE_PDU + " WHERE " + Mms.THREAD_ID + " IS NULL"); 1533 } 1534 1535 private void upgradeDatabaseToVersion58(SQLiteDatabase db) { 1536 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU + 1537 " ADD COLUMN " + Mms.SUBSCRIPTION_ID 1538 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1539 db.execSQL("ALTER TABLE " + MmsSmsProvider.TABLE_PENDING_MSG 1540 +" ADD COLUMN " + "pending_sub_id" 1541 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1542 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_SMS 1543 + " ADD COLUMN " + Sms.SUBSCRIPTION_ID 1544 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1545 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_RAW 1546 +" ADD COLUMN " + Sms.SUBSCRIPTION_ID 1547 + " INTEGER DEFAULT " + SubscriptionManager.INVALID_SUBSCRIPTION_ID); 1548 } 1549 1550 private void upgradeDatabaseToVersion59(SQLiteDatabase db) { 1551 db.execSQL("ALTER TABLE " + MmsProvider.TABLE_PDU +" ADD COLUMN " 1552 + Mms.CREATOR + " TEXT"); 1553 db.execSQL("ALTER TABLE " + SmsProvider.TABLE_SMS +" ADD COLUMN " 1554 + Sms.CREATOR + " TEXT"); 1555 } 1556 1557 private void upgradeDatabaseToVersion60(SQLiteDatabase db) { 1558 db.execSQL("ALTER TABLE " + MmsSmsProvider.TABLE_THREADS +" ADD COLUMN " 1559 + Threads.ARCHIVED + " INTEGER DEFAULT 0"); 1560 } 1561 1562 @Override 1563 public synchronized SQLiteDatabase getWritableDatabase() { 1564 SQLiteDatabase db = super.getWritableDatabase(); 1565 1566 if (!sTriedAutoIncrement) { 1567 sTriedAutoIncrement = true; 1568 boolean hasAutoIncrementThreads = hasAutoIncrement(db, MmsSmsProvider.TABLE_THREADS); 1569 boolean hasAutoIncrementAddresses = hasAutoIncrement(db, "canonical_addresses"); 1570 boolean hasAutoIncrementPart = hasAutoIncrement(db, "part"); 1571 boolean hasAutoIncrementPdu = hasAutoIncrement(db, "pdu"); 1572 Log.d(TAG, "[getWritableDatabase] hasAutoIncrementThreads: " + hasAutoIncrementThreads + 1573 " hasAutoIncrementAddresses: " + hasAutoIncrementAddresses + 1574 " hasAutoIncrementPart: " + hasAutoIncrementPart + 1575 " hasAutoIncrementPdu: " + hasAutoIncrementPdu); 1576 boolean autoIncrementThreadsSuccess = true; 1577 boolean autoIncrementAddressesSuccess = true; 1578 boolean autoIncrementPartSuccess = true; 1579 boolean autoIncrementPduSuccess = true; 1580 if (!hasAutoIncrementThreads) { 1581 db.beginTransaction(); 1582 try { 1583 if (false && sFakeLowStorageTest) { 1584 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1585 " - fake exception"); 1586 throw new Exception("FakeLowStorageTest"); 1587 } 1588 upgradeThreadsTableToAutoIncrement(db); // a no-op if already upgraded 1589 db.setTransactionSuccessful(); 1590 } catch (Throwable ex) { 1591 Log.e(TAG, "Failed to add autoIncrement to threads;: " + ex.getMessage(), ex); 1592 autoIncrementThreadsSuccess = false; 1593 } finally { 1594 db.endTransaction(); 1595 } 1596 } 1597 if (!hasAutoIncrementAddresses) { 1598 db.beginTransaction(); 1599 try { 1600 if (false && sFakeLowStorageTest) { 1601 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1602 " - fake exception"); 1603 throw new Exception("FakeLowStorageTest"); 1604 } 1605 upgradeAddressTableToAutoIncrement(db); // a no-op if already upgraded 1606 db.setTransactionSuccessful(); 1607 } catch (Throwable ex) { 1608 Log.e(TAG, "Failed to add autoIncrement to canonical_addresses: " + 1609 ex.getMessage(), ex); 1610 autoIncrementAddressesSuccess = false; 1611 } finally { 1612 db.endTransaction(); 1613 } 1614 } 1615 if (!hasAutoIncrementPart) { 1616 db.beginTransaction(); 1617 try { 1618 if (false && sFakeLowStorageTest) { 1619 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1620 " - fake exception"); 1621 throw new Exception("FakeLowStorageTest"); 1622 } 1623 upgradePartTableToAutoIncrement(db); // a no-op if already upgraded 1624 db.setTransactionSuccessful(); 1625 } catch (Throwable ex) { 1626 Log.e(TAG, "Failed to add autoIncrement to part: " + 1627 ex.getMessage(), ex); 1628 autoIncrementPartSuccess = false; 1629 } finally { 1630 db.endTransaction(); 1631 } 1632 } 1633 if (!hasAutoIncrementPdu) { 1634 db.beginTransaction(); 1635 try { 1636 if (false && sFakeLowStorageTest) { 1637 Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " + 1638 " - fake exception"); 1639 throw new Exception("FakeLowStorageTest"); 1640 } 1641 upgradePduTableToAutoIncrement(db); // a no-op if already upgraded 1642 db.setTransactionSuccessful(); 1643 } catch (Throwable ex) { 1644 Log.e(TAG, "Failed to add autoIncrement to pdu: " + 1645 ex.getMessage(), ex); 1646 autoIncrementPduSuccess = false; 1647 } finally { 1648 db.endTransaction(); 1649 } 1650 } 1651 if (autoIncrementThreadsSuccess && 1652 autoIncrementAddressesSuccess && 1653 autoIncrementPartSuccess && 1654 autoIncrementPduSuccess) { 1655 if (mLowStorageMonitor != null) { 1656 // We've already updated the database. This receiver is no longer necessary. 1657 Log.d(TAG, "Unregistering mLowStorageMonitor - we've upgraded"); 1658 mContext.unregisterReceiver(mLowStorageMonitor); 1659 mLowStorageMonitor = null; 1660 } 1661 } else { 1662 if (sFakeLowStorageTest) { 1663 sFakeLowStorageTest = false; 1664 } 1665 1666 // We failed, perhaps because of low storage. Turn on a receiver to watch for 1667 // storage space. 1668 if (mLowStorageMonitor == null) { 1669 Log.d(TAG, "[getWritableDatabase] turning on storage monitor"); 1670 mLowStorageMonitor = new LowStorageMonitor(); 1671 IntentFilter intentFilter = new IntentFilter(); 1672 intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_LOW); 1673 intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_OK); 1674 mContext.registerReceiver(mLowStorageMonitor, intentFilter); 1675 } 1676 } 1677 } 1678 return db; 1679 } 1680 1681 // Determine whether a particular table has AUTOINCREMENT in its schema. 1682 private boolean hasAutoIncrement(SQLiteDatabase db, String tableName) { 1683 boolean result = false; 1684 String query = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" + 1685 tableName + "'"; 1686 Cursor c = db.rawQuery(query, null); 1687 if (c != null) { 1688 try { 1689 if (c.moveToFirst()) { 1690 String schema = c.getString(0); 1691 result = schema != null ? schema.contains("AUTOINCREMENT") : false; 1692 Log.d(TAG, "[MmsSmsDb] tableName: " + tableName + " hasAutoIncrement: " + 1693 schema + " result: " + result); 1694 } 1695 } finally { 1696 c.close(); 1697 } 1698 } 1699 return result; 1700 } 1701 1702 // upgradeThreadsTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1703 // the threads table. This could fail if the user has a lot of conversations and not enough 1704 // storage to make a copy of the threads table. That's ok. This upgrade is optional. It'll 1705 // be called again next time the device is rebooted. 1706 private void upgradeThreadsTableToAutoIncrement(SQLiteDatabase db) { 1707 if (hasAutoIncrement(db, MmsSmsProvider.TABLE_THREADS)) { 1708 Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: already upgraded"); 1709 return; 1710 } 1711 Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: upgrading"); 1712 1713 // Make the _id of the threads table autoincrement so we never re-use thread ids 1714 // Have to create a new temp threads table. Copy all the info from the old table. 1715 // Drop the old table and rename the new table to that of the old. 1716 db.execSQL("CREATE TABLE threads_temp (" + 1717 Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1718 Threads.DATE + " INTEGER DEFAULT 0," + 1719 Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," + 1720 Threads.RECIPIENT_IDS + " TEXT," + 1721 Threads.SNIPPET + " TEXT," + 1722 Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," + 1723 Threads.READ + " INTEGER DEFAULT 1," + 1724 Threads.TYPE + " INTEGER DEFAULT 0," + 1725 Threads.ERROR + " INTEGER DEFAULT 0," + 1726 Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);"); 1727 1728 db.execSQL("INSERT INTO threads_temp SELECT * from threads;"); 1729 db.execSQL("DROP TABLE threads;"); 1730 db.execSQL("ALTER TABLE threads_temp RENAME TO threads;"); 1731 } 1732 1733 // upgradeAddressTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1734 // the canonical_addresses table. This could fail if the user has a lot of people they've 1735 // messaged with and not enough storage to make a copy of the canonical_addresses table. 1736 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 1737 private void upgradeAddressTableToAutoIncrement(SQLiteDatabase db) { 1738 if (hasAutoIncrement(db, "canonical_addresses")) { 1739 Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: already upgraded"); 1740 return; 1741 } 1742 Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: upgrading"); 1743 1744 // Make the _id of the canonical_addresses table autoincrement so we never re-use ids 1745 // Have to create a new temp canonical_addresses table. Copy all the info from the old 1746 // table. Drop the old table and rename the new table to that of the old. 1747 db.execSQL("CREATE TABLE canonical_addresses_temp (_id INTEGER PRIMARY KEY AUTOINCREMENT," + 1748 "address TEXT);"); 1749 1750 db.execSQL("INSERT INTO canonical_addresses_temp SELECT * from canonical_addresses;"); 1751 db.execSQL("DROP TABLE canonical_addresses;"); 1752 db.execSQL("ALTER TABLE canonical_addresses_temp RENAME TO canonical_addresses;"); 1753 } 1754 1755 // upgradePartTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1756 // the part table. This could fail if the user has a lot of sound/video/picture attachments 1757 // and not enough storage to make a copy of the part table. 1758 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 1759 private void upgradePartTableToAutoIncrement(SQLiteDatabase db) { 1760 if (hasAutoIncrement(db, "part")) { 1761 Log.d(TAG, "[MmsSmsDb] upgradePartTableToAutoIncrement: already upgraded"); 1762 return; 1763 } 1764 Log.d(TAG, "[MmsSmsDb] upgradePartTableToAutoIncrement: upgrading"); 1765 1766 // Make the _id of the part table autoincrement so we never re-use ids 1767 // Have to create a new temp part table. Copy all the info from the old 1768 // table. Drop the old table and rename the new table to that of the old. 1769 db.execSQL("CREATE TABLE part_temp (" + 1770 Part._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1771 Part.MSG_ID + " INTEGER," + 1772 Part.SEQ + " INTEGER DEFAULT 0," + 1773 Part.CONTENT_TYPE + " TEXT," + 1774 Part.NAME + " TEXT," + 1775 Part.CHARSET + " INTEGER," + 1776 Part.CONTENT_DISPOSITION + " TEXT," + 1777 Part.FILENAME + " TEXT," + 1778 Part.CONTENT_ID + " TEXT," + 1779 Part.CONTENT_LOCATION + " TEXT," + 1780 Part.CT_START + " INTEGER," + 1781 Part.CT_TYPE + " TEXT," + 1782 Part._DATA + " TEXT," + 1783 Part.TEXT + " TEXT);"); 1784 1785 db.execSQL("INSERT INTO part_temp SELECT * from part;"); 1786 db.execSQL("DROP TABLE part;"); 1787 db.execSQL("ALTER TABLE part_temp RENAME TO part;"); 1788 1789 // part-related triggers get tossed when the part table is dropped -- rebuild them. 1790 createMmsTriggers(db); 1791 } 1792 1793 // upgradePduTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to 1794 // the pdu table. This could fail if the user has a lot of mms messages 1795 // and not enough storage to make a copy of the pdu table. 1796 // That's ok. This upgrade is optional. It'll be called again next time the device is rebooted. 1797 private void upgradePduTableToAutoIncrement(SQLiteDatabase db) { 1798 if (hasAutoIncrement(db, "pdu")) { 1799 Log.d(TAG, "[MmsSmsDb] upgradePduTableToAutoIncrement: already upgraded"); 1800 return; 1801 } 1802 Log.d(TAG, "[MmsSmsDb] upgradePduTableToAutoIncrement: upgrading"); 1803 1804 // Make the _id of the part table autoincrement so we never re-use ids 1805 // Have to create a new temp part table. Copy all the info from the old 1806 // table. Drop the old table and rename the new table to that of the old. 1807 db.execSQL("CREATE TABLE pdu_temp (" + 1808 Mms._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + 1809 Mms.THREAD_ID + " INTEGER," + 1810 Mms.DATE + " INTEGER," + 1811 Mms.DATE_SENT + " INTEGER DEFAULT 0," + 1812 Mms.MESSAGE_BOX + " INTEGER," + 1813 Mms.READ + " INTEGER DEFAULT 0," + 1814 Mms.MESSAGE_ID + " TEXT," + 1815 Mms.SUBJECT + " TEXT," + 1816 Mms.SUBJECT_CHARSET + " INTEGER," + 1817 Mms.CONTENT_TYPE + " TEXT," + 1818 Mms.CONTENT_LOCATION + " TEXT," + 1819 Mms.EXPIRY + " INTEGER," + 1820 Mms.MESSAGE_CLASS + " TEXT," + 1821 Mms.MESSAGE_TYPE + " INTEGER," + 1822 Mms.MMS_VERSION + " INTEGER," + 1823 Mms.MESSAGE_SIZE + " INTEGER," + 1824 Mms.PRIORITY + " INTEGER," + 1825 Mms.READ_REPORT + " INTEGER," + 1826 Mms.REPORT_ALLOWED + " INTEGER," + 1827 Mms.RESPONSE_STATUS + " INTEGER," + 1828 Mms.STATUS + " INTEGER," + 1829 Mms.TRANSACTION_ID + " TEXT," + 1830 Mms.RETRIEVE_STATUS + " INTEGER," + 1831 Mms.RETRIEVE_TEXT + " TEXT," + 1832 Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," + 1833 Mms.READ_STATUS + " INTEGER," + 1834 Mms.CONTENT_CLASS + " INTEGER," + 1835 Mms.RESPONSE_TEXT + " TEXT," + 1836 Mms.DELIVERY_TIME + " INTEGER," + 1837 Mms.DELIVERY_REPORT + " INTEGER," + 1838 Mms.LOCKED + " INTEGER DEFAULT 0," + 1839 Mms.SUBSCRIPTION_ID + " INTEGER DEFAULT " 1840 + SubscriptionManager.INVALID_SUBSCRIPTION_ID + ", " + 1841 Mms.SEEN + " INTEGER DEFAULT 0," + 1842 Mms.TEXT_ONLY + " INTEGER DEFAULT 0" + 1843 ");"); 1844 1845 db.execSQL("INSERT INTO pdu_temp SELECT * from pdu;"); 1846 db.execSQL("DROP TABLE pdu;"); 1847 db.execSQL("ALTER TABLE pdu_temp RENAME TO pdu;"); 1848 1849 // pdu-related triggers get tossed when the part table is dropped -- rebuild them. 1850 createMmsTriggers(db); 1851 } 1852 1853 private class LowStorageMonitor extends BroadcastReceiver { 1854 1855 public LowStorageMonitor() { 1856 } 1857 1858 public void onReceive(Context context, Intent intent) { 1859 String action = intent.getAction(); 1860 1861 Log.d(TAG, "[LowStorageMonitor] onReceive intent " + action); 1862 1863 if (Intent.ACTION_DEVICE_STORAGE_OK.equals(action)) { 1864 sTriedAutoIncrement = false; // try to upgrade on the next getWriteableDatabase 1865 } 1866 } 1867 } 1868 1869 private void updateThreadsAttachmentColumn(SQLiteDatabase db) { 1870 // Set the values of that column correctly based on the current 1871 // contents of the database. 1872 db.execSQL("UPDATE threads SET has_attachment=1 WHERE _id IN " + 1873 " (SELECT DISTINCT pdu.thread_id FROM part " + 1874 " JOIN pdu ON pdu._id=part.mid " + 1875 " WHERE part.ct != 'text/plain' AND part.ct != 'application/smil')"); 1876 } 1877 } 1878
