TelephonyProvider: Improve the performance of deleting Mms.
Triggers "update_threads_on_delete_part" and "mms_words_delete"
run too many times, when delete attachment rows from part
table, it caused Mms application can't deleting lots of MMS
in an acceptable time.
1. Drop the triggers "update_threads_on_insert_part" and
"mms_words_delete".
2. Add code instead of trigger's job to avoid unnecessary
operation, when delete a lot of mms.
Change-Id: I151aca0354e157821306ef50d9d3745bdaf4ce99
CRs-Fixed: 786459, 1016740
diff --git a/src/com/android/providers/telephony/MmsProvider.java b/src/com/android/providers/telephony/MmsProvider.java
index 5acfe13..d7a6803 100644
--- a/src/com/android/providers/telephony/MmsProvider.java
+++ b/src/com/android/providers/telephony/MmsProvider.java
@@ -50,6 +50,7 @@
import java.io.File;
import java.io.FileNotFoundException;
import java.io.IOException;
+import java.util.HashSet;
/**
* The class to provide base facility to access MMS related content,
@@ -637,6 +638,8 @@
selectionArgs, uri);
} else if (TABLE_PART.equals(table)) {
deletedRows = deleteParts(db, finalSelection, selectionArgs);
+ cleanUpWords(db);
+ updateHasAttachment(db);
} else if (TABLE_DRM.equals(table)) {
deletedRows = deleteTempDrmData(db, finalSelection, selectionArgs);
} else {
@@ -651,12 +654,13 @@
static int deleteMessages(Context context, SQLiteDatabase db,
String selection, String[] selectionArgs, Uri uri) {
- Cursor cursor = db.query(TABLE_PDU, new String[] { Mms._ID },
+ Cursor cursor = db.query(TABLE_PDU, new String[] { Mms._ID, Mms.THREAD_ID },
selection, selectionArgs, null, null, null);
if (cursor == null) {
return 0;
}
+ HashSet<Long> threadIds = new HashSet<Long>();
try {
if (cursor.getCount() == 0) {
return 0;
@@ -665,12 +669,18 @@
while (cursor.moveToNext()) {
deleteParts(db, Part.MSG_ID + " = ?",
new String[] { String.valueOf(cursor.getLong(0)) });
+ threadIds.add(cursor.getLong(1));
}
+ cleanUpWords(db);
+ updateHasAttachment(db);
} finally {
cursor.close();
}
int count = db.delete(TABLE_PDU, selection, selectionArgs);
+ for (long thread : threadIds) {
+ MmsSmsDatabaseHelper.updateThread(db, thread);
+ }
if (count > 0) {
Intent intent = new Intent(Mms.Intents.CONTENT_CHANGED_ACTION);
intent.putExtra(Mms.Intents.DELETED_CONTENTS, uri);
@@ -682,6 +692,18 @@
return count;
}
+ private static void cleanUpWords(SQLiteDatabase db) {
+ db.execSQL("DELETE FROM words WHERE source_id not in (select _id from part) AND "
+ + "table_to_use = 2");
+ }
+
+ private static void updateHasAttachment(SQLiteDatabase db) {
+ db.execSQL("UPDATE threads SET has_attachment = CASE "
+ + "(SELECT COUNT(*) FROM part JOIN pdu WHERE part.mid = pdu._id AND "
+ + "pdu.thread_id = threads._id AND part.ct != 'text/plain' "
+ + "AND part.ct != 'application/smil') WHEN 0 THEN 0 ELSE 1 END");
+ }
+
private static int deleteParts(SQLiteDatabase db, String selection,
String[] selectionArgs) {
return deleteDataRows(db, TABLE_PART, selection, selectionArgs);
diff --git a/src/com/android/providers/telephony/MmsSmsDatabaseHelper.java b/src/com/android/providers/telephony/MmsSmsDatabaseHelper.java
index abe7ffd..4f89900 100644
--- a/src/com/android/providers/telephony/MmsSmsDatabaseHelper.java
+++ b/src/com/android/providers/telephony/MmsSmsDatabaseHelper.java
@@ -94,19 +94,6 @@
" AND " + Mms.MESSAGE_BOX + " != 3) " +
" WHERE threads._id = new.thread_id; ";
- private static final String UPDATE_THREAD_COUNT_ON_OLD =
- " UPDATE threads SET message_count = " +
- " (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +
- " ON threads._id = " + Sms.THREAD_ID +
- " WHERE " + Sms.THREAD_ID + " = old.thread_id" +
- " AND sms." + Sms.TYPE + " != 3) + " +
- " (SELECT COUNT(pdu._id) FROM pdu LEFT JOIN threads " +
- " ON threads._id = " + Mms.THREAD_ID +
- " WHERE " + Mms.THREAD_ID + " = old.thread_id" +
- " AND (m_type=132 OR m_type=130 OR m_type=128)" +
- " AND " + Mms.MESSAGE_BOX + " != 3) " +
- " WHERE threads._id = old.thread_id; ";
-
private static final String SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =
"BEGIN" +
" UPDATE threads SET" +
@@ -156,21 +143,6 @@
PDU_UPDATE_THREAD_READ_BODY +
"END;";
- private static final String UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE =
- " UPDATE threads SET snippet = " +
- " (SELECT snippet FROM" +
- " (SELECT date * 1000 AS date, sub AS snippet, thread_id FROM pdu" +
- " UNION SELECT date, body AS snippet, thread_id FROM sms)" +
- " WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " +
- " WHERE threads._id = OLD.thread_id; " +
- " UPDATE threads SET snippet_cs = " +
- " (SELECT snippet_cs FROM" +
- " (SELECT date * 1000 AS date, sub_cs AS snippet_cs, thread_id FROM pdu" +
- " UNION SELECT date, 0 AS snippet_cs, thread_id FROM sms)" +
- " WHERE thread_id = OLD.thread_id ORDER BY date DESC LIMIT 1) " +
- " WHERE threads._id = OLD.thread_id; ";
-
-
// When a part is inserted, if it is not text/plain or application/smil
// (which both can exist with text-only MMSes), then there is an attachment.
// Set has_attachment=1 in the threads table for the thread in question.
@@ -196,28 +168,6 @@
" WHERE part._id=new._id LIMIT 1); " +
" END";
-
- // When a part is deleted (with the same non-text/SMIL constraint as when
- // we set has_attachment), update the threads table for all threads.
- // Unfortunately we cannot update only the thread that the part was
- // attached to, as it is possible that the part has been orphaned and
- // the message it was attached to is already gone.
- private static final String PART_UPDATE_THREADS_ON_DELETE_TRIGGER =
- "CREATE TRIGGER update_threads_on_delete_part " +
- " AFTER DELETE ON part " +
- " WHEN old.ct != 'text/plain' AND old.ct != 'application/smil' " +
- " BEGIN " +
- " UPDATE threads SET has_attachment = " +
- " CASE " +
- " (SELECT COUNT(*) FROM part JOIN pdu " +
- " WHERE pdu.thread_id = threads._id " +
- " AND part.ct != 'text/plain' AND part.ct != 'application/smil' " +
- " AND part.mid = pdu._id)" +
- " WHEN 0 THEN 0 " +
- " ELSE 1 " +
- " END; " +
- " END";
-
// When the 'thread_id' column in the pdu table is updated, we need to run the trigger to update
// the threads table's has_attachment column, if the message has an attachment in 'part' table
private static final String PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER =
@@ -550,6 +500,15 @@
createSmsTables(db);
}
}
+
+ // Improve the performance of deleting Mms.
+ dropMmsTriggers(db);
+ }
+
+ private void dropMmsTriggers(SQLiteDatabase db) {
+ db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_delete_part");
+ db.execSQL("DROP TRIGGER IF EXISTS mms_words_delete");
+ db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_delete");
}
// When upgrading the database we need to populate the words
@@ -646,8 +605,18 @@
private void createIndices(SQLiteDatabase db) {
createThreadIdIndex(db);
+ createPduPartIndex(db);
}
+ private void createPduPartIndex(SQLiteDatabase db) {
+ try {
+ db.execSQL("CREATE INDEX IF NOT EXISTS index_part ON " + MmsProvider.TABLE_PART +
+ " (mid);");
+ } catch (Exception ex) {
+ Log.e(TAG, "got exception creating indices: " + ex.toString());
+ }
+ }
+
private void createThreadIdIndex(SQLiteDatabase db) {
try {
db.execSQL("CREATE INDEX IF NOT EXISTS typeThreadIdIndex ON sms" +
@@ -779,9 +748,6 @@
db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_update_part");
db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER);
- db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_delete_part");
- db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER);
-
db.execSQL("DROP TRIGGER IF EXISTS update_threads_on_update_pdu");
db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER);
@@ -857,10 +823,6 @@
" SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " +
" END;");
- db.execSQL("DROP TRIGGER IF EXISTS mms_words_delete");
- db.execSQL("CREATE TRIGGER mms_words_delete AFTER DELETE ON part BEGIN DELETE FROM " +
- " words WHERE source_id = OLD._id AND table_to_use = 2; END;");
-
// Updates threads table whenever a message in pdu is updated.
db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_date_subject_on_update");
db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" +
@@ -869,18 +831,6 @@
PDU_UPDATE_THREAD_CONSTRAINTS +
PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
- // Update threads table whenever a message in pdu is deleted
- db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_delete");
- db.execSQL("CREATE TRIGGER pdu_update_thread_on_delete " +
- "AFTER DELETE ON pdu " +
- "BEGIN " +
- " UPDATE threads SET " +
- " date = (strftime('%s','now') * 1000)" +
- " WHERE threads._id = old." + Mms.THREAD_ID + "; " +
- UPDATE_THREAD_COUNT_ON_OLD +
- UPDATE_THREAD_SNIPPET_SNIPPET_CS_ON_DELETE +
- "END;");
-
// Updates threads table whenever a message is added to pdu.
db.execSQL("DROP TRIGGER IF EXISTS pdu_update_thread_on_insert");
db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " +
@@ -1567,9 +1517,8 @@
updateThreadsAttachmentColumn(db);
- // Add insert and delete triggers for keeping it up to date.
+ // Add insert triggers for keeping it up to date.
db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER);
- db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER);
}
private void upgradeDatabaseToVersion44(SQLiteDatabase db) {