本文详细介绍了MMS数据库中用于更新、管理消息状态的触发器，包括更新会话表、维护待发送/下载消息队列、错误标志位更新等关键操作。
Mms数据库的triggers
1. threads表将SMS、MMS的信息组织成一个conversation，所以当MMS、SMS信息发生改变时也需要更新threads表
2. 维护pending_msgs表格，其中包含pending_msgs表用于存储待发送或下载的SMS/MMS
3. 维护错误标志位
/**
* the name of the table that is used to store the queue of
* messages(both MMS and SMS) to be sent/downloaded.
*/
public static final String TABLE_PENDING_MSG = "pending_msgs";
private void createCommonTriggers(SQLiteDatabase db) {
// Updates threads table whenever a message is added to pdu.
db.execSQL("CREATE TRIGGER pdu_update_thread_on_insert AFTER INSERT ON " +
MmsProvider.TABLE_PDU + " " +
PDU_UPDATE_THREAD_CONSTRAINTS +
PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
// Updates threads table whenever a message is added to sms.
db.execSQL("CREATE TRIGGER sms_update_thread_on_insert AFTER INSERT ON sms " +
SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
// Updates threads table whenever a message in pdu is updated.
db.execSQL("CREATE TRIGGER pdu_update_thread_date_subject_on_update AFTER" +
" UPDATE OF " + Mms.DATE + ", " + Mms.SUBJECT + ", " + Mms.MESSAGE_BOX +
" ON " + MmsProvider.TABLE_PDU + " " +
PDU_UPDATE_THREAD_CONSTRAINTS +
PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
// Updates threads table whenever a message in sms is updated.
db.execSQL("CREATE TRIGGER sms_update_thread_date_subject_on_update AFTER" +
" UPDATE OF " + Sms.DATE + ", " + Sms.BODY + ", " + Sms.TYPE +
" ON sms " +
SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE);
// Updates threads table whenever a message in pdu is updated.
db.execSQL("CREATE TRIGGER pdu_update_thread_read_on_update AFTER" +
" UPDATE OF " + Mms.READ +
" ON " + MmsProvider.TABLE_PDU + " " +
PDU_UPDATE_THREAD_CONSTRAINTS +
"BEGIN " +
PDU_UPDATE_THREAD_READ_BODY +
"END;");
// Updates threads table whenever a message in sms is updated.
db.execSQL("CREATE TRIGGER sms_update_thread_read_on_update AFTER" +
" UPDATE OF " + Sms.READ +
" ON sms " +
"BEGIN " +
SMS_UPDATE_THREAD_READ_BODY +
"END;");
// Update threads table whenever a message in pdu is deleted
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;");
// As of DATABASE_VERSION 55, we've removed these triggers that delete empty threads.
// These triggers interfere with saving drafts on brand new threads. Instead of
// triggers cleaning up empty threads, the empty threads should be cleaned up by
// an explicit call to delete with Threads.OBSOLETE_THREADS_URI.
// // When the last message in a thread is deleted, these
// // triggers ensure that the entry for its thread ID is removed
// // from the threads table.
// db.execSQL("CREATE TRIGGER delete_obsolete_threads_pdu " +
// "AFTER DELETE ON pdu " +
// "BEGIN " +
// " DELETE FROM threads " +
// " WHERE " +
// " _id = old.thread_id " +
// " AND _id NOT IN " +
// " (SELECT thread_id FROM sms " +
// " UNION SELECT thread_id from pdu); " +
// "END;");
//
// db.execSQL("CREATE TRIGGER delete_obsolete_threads_when_update_pdu " +
// "AFTER UPDATE OF " + Mms.THREAD_ID + " ON pdu " +
// "WHEN old." + Mms.THREAD_ID + " != new." + Mms.THREAD_ID + " " +
// "BEGIN " +
// " DELETE FROM threads " +
// " WHERE " +
// " _id = old.thread_id " +
// " AND _id NOT IN " +
// " (SELECT thread_id FROM sms " +
// " UNION SELECT thread_id from pdu); " +
// "END;");
// Insert pending status for M-Notification.ind or M-ReadRec.ind
// when they are inserted into Inbox/Outbox.
db.execSQL("CREATE TRIGGER insert_mms_pending_on_insert " +
"AFTER INSERT ON pdu " +
"WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND +
" OR new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_REC_IND +
" " +
"BEGIN " +
" INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG +
" (" + PendingMessages.PROTO_TYPE + "," +
" " + PendingMessages.MSG_ID + "," +
" " + PendingMessages.MSG_TYPE + "," +
" " + PendingMessages.ERROR_TYPE + "," +
" " + PendingMessages.ERROR_CODE + "," +
" " + PendingMessages.RETRY_INDEX + "," +
" " + PendingMessages.DUE_TIME + ") " +
" VALUES " +
" (" + MmsSms.MMS_PROTO + "," +
" new." + BaseColumns._ID + "," +
" new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" +
"END;");
// Insert pending status for M-Send.req when it is moved into Outbox.
db.execSQL("CREATE TRIGGER insert_mms_pending_on_update " +
"AFTER UPDATE ON pdu " +
"WHEN new." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ +
" AND new." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX +
" AND old." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " +
"BEGIN " +
" INSERT INTO " + MmsSmsProvider.TABLE_PENDING_MSG +
" (" + PendingMessages.PROTO_TYPE + "," +
" " + PendingMessages.MSG_ID + "," +
" " + PendingMessages.MSG_TYPE + "," +
" " + PendingMessages.ERROR_TYPE + "," +
" " + PendingMessages.ERROR_CODE + "," +
" " + PendingMessages.RETRY_INDEX + "," +
" " + PendingMessages.DUE_TIME + ") " +
" VALUES " +
" (" + MmsSms.MMS_PROTO + "," +
" new." + BaseColumns._ID + "," +
" new." + Mms.MESSAGE_TYPE + ",0,0,0,0);" +
"END;");
// When a message is moved out of Outbox, delete its pending status.
db.execSQL("CREATE TRIGGER delete_mms_pending_on_update " +
"AFTER UPDATE ON " + MmsProvider.TABLE_PDU + " " +
"WHEN old." + Mms.MESSAGE_BOX + "=" + Mms.MESSAGE_BOX_OUTBOX +
" AND new." + Mms.MESSAGE_BOX + "!=" + Mms.MESSAGE_BOX_OUTBOX + " " +
"BEGIN " +
" DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG +
" WHERE " + PendingMessages.MSG_ID + "=new._id; " +
"END;");
// Delete pending status for a message when it is deleted.
db.execSQL("CREATE TRIGGER delete_mms_pending_on_delete " +
"AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " +
"BEGIN " +
" DELETE FROM " + MmsSmsProvider.TABLE_PENDING_MSG +
" WHERE " + PendingMessages.MSG_ID + "=old._id; " +
"END;");
// TODO Add triggers for SMS retry-status management.
// Update the error flag of threads when the error type of
// a pending MM is updated.
db.execSQL("CREATE TRIGGER update_threads_error_on_update_mms " +
" AFTER UPDATE OF err_type ON pending_msgs " +
" WHEN (OLD.err_type < 10 AND NEW.err_type >= 10)" +
" OR (OLD.err_type >= 10 AND NEW.err_type < 10) " +
"BEGIN" +
" UPDATE threads SET error = " +
" CASE" +
" WHEN NEW.err_type >= 10 THEN error + 1" +
" ELSE error - 1" +
" END " +
" WHERE _id =" +
" (SELECT DISTINCT thread_id" +
" FROM pdu" +
" WHERE _id = NEW.msg_id); " +
"END;");
// Update the error flag of threads when delete pending message.
db.execSQL("CREATE TRIGGER update_threads_error_on_delete_mms " +
" BEFORE DELETE ON pdu" +
" WHEN OLD._id IN (SELECT DISTINCT msg_id" +
" FROM pending_msgs" +
" WHERE err_type >= 10) " +
"BEGIN " +
" UPDATE threads SET error = error - 1" +
" WHERE _id = OLD.thread_id; " +
"END;");
// Update the error flag of threads while moving an MM out of Outbox,
// which was failed to be sent permanently.
db.execSQL("CREATE TRIGGER update_threads_error_on_move_mms " +
" BEFORE UPDATE OF msg_box ON pdu " +
" WHEN (OLD.msg_box = 4 AND NEW.msg_box != 4) " +
" AND (OLD._id IN (SELECT DISTINCT msg_id" +
" FROM pending_msgs" +
" WHERE err_type >= 10)) " +
"BEGIN " +
" UPDATE threads SET error = error - 1" +
" WHERE _id = OLD.thread_id; " +
"END;");
// Update the error flag of threads after a text message was
// failed to send/receive.
db.execSQL("CREATE TRIGGER update_threads_error_on_update_sms " +
" AFTER UPDATE OF type ON sms" +
" WHEN (OLD.type != 5 AND NEW.type = 5)" +
" OR (OLD.type = 5 AND NEW.type != 5) " +
"BEGIN " +
" UPDATE threads SET error = " +
" CASE" +
" WHEN NEW.type = 5 THEN error + 1" +
" ELSE error - 1" +
" END " +
" WHERE _id = NEW.thread_id; " +
"END;");
}
4. 特别为Mms设计的triggers
从中可以得知对于Mms delivery report/read report信息是分别作为一条数据项放在pdu表中的。
// Cleans up parts when a MM is deleted.
db.execSQL("CREATE TRIGGER part_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " +
"BEGIN " +
" DELETE FROM " + MmsProvider.TABLE_PART +
" WHERE " + Part.MSG_ID + "=old._id;" +
"END;");
// Cleans up address info when a MM is deleted.
db.execSQL("CREATE TRIGGER addr_cleanup DELETE ON " + MmsProvider.TABLE_PDU + " " +
"BEGIN " +
" DELETE FROM " + MmsProvider.TABLE_ADDR +
" WHERE " + Addr.MSG_ID + "=old._id;" +
"END;");
// Delete obsolete delivery-report, read-report while deleting their
// associated Send.req.
db.execSQL("CREATE TRIGGER cleanup_delivery_and_read_report " +
"AFTER DELETE ON " + MmsProvider.TABLE_PDU + " " +
"WHEN old." + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_SEND_REQ + " " +
"BEGIN " +
" DELETE FROM " + MmsProvider.TABLE_PDU +
" WHERE (" + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_DELIVERY_IND +
" OR " + Mms.MESSAGE_TYPE + "=" + PduHeaders.MESSAGE_TYPE_READ_ORIG_IND +
")" +
" AND " + Mms.MESSAGE_ID + "=old." + Mms.MESSAGE_ID + "; " +
"END;");
// Update threads table to indicate whether attachments exist when
// parts are inserted or deleted.
db.execSQL(PART_UPDATE_THREADS_ON_INSERT_TRIGGER);
db.execSQL(PART_UPDATE_THREADS_ON_UPDATE_TRIGGER);
db.execSQL(PART_UPDATE_THREADS_ON_DELETE_TRIGGER);
db.execSQL(PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER);
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
