android缺彩信数据库设计源码解析（二）
android短彩信数据库设计源码解析（二）
4.2、删除threads数据库内的废数据，我们在第三步介绍过，什么样的数据叫做废数据。
这个函数是本类的重头戏，我们在这里先简单介绍一下他们。
11.2、
这条触发器是在彩信类型MESSAGE_TYPE_SEND_REQ，我们在第一篇文中介绍过，这个类型代表发送的彩信。在从pdu表内删除发送的彩信时，删除pdu表内对应的delivery_report以及read_report信息。
这个表表示了所有接收到的短信，彩信的address，映射address为id，并且这个表是自增长的，因而删除的id不会再次用到。
20、
谷歌的程序员也很会幽默。相信有英语语感的同学，看这段英语注释，是不是和我一样，会笑出来。程序员是严谨的，that's ok，好逗，程序员的节操那？。
这几天连续加班，昨天很早就睡了。一个人的时候要学会自我祝福，祝自己注意身体，天天精力充沛。明天父亲生日，在此祝父亲生日快乐，身体健康，笑口常开。
接着介绍android系统短彩信数据库设计。
单例模式，获取数据库设计类的单一引用。
关于数据模式，可以参考：http://blog.csdn.net/hailushijie/article/details/8715154。
1、
/**
* Return a singleton helper for the combined MMS and SMS
* database.
*/
/* package */ static synchronized MmsSmsDatabaseHelper getInstance(Context context) {
if (sInstance == null) {
sInstance = new MmsSmsDatabaseHelper(context);
}
return sInstance;
}如果对象不存在，那么实例化一个对象。
2、
static final String DATABASE_NAME = "mmssms.db";
static final int DATABASE_VERSION = 55;
private final Context mContext;
private LowStorageMonitor mLowStorageMonitor;
private MmsSmsDatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
mContext = context;
}super方法，第二个参数指明了数据库的名字 “mmssms.db”，第三个参数表明了当前数据库的版本。
3、
public static void updateThread(SQLiteDatabase db, long thread_id) {
if (thread_id < 0) {
updateAllThreads(db, null, null);
return;
}
// Delete the row for this thread in the threads table if
// there are no more messages attached to it in either
// the sms or pdu tables.
int rows = db.delete("threads",
"_id = ? AND _id NOT IN" +
" (SELECT thread_id FROM sms " +
" UNION SELECT thread_id FROM pdu)",
new String[] { String.valueOf(thread_id) });
if (rows > 0) {
// If this deleted a row, let's remove orphaned canonical_addresses and get outta here
db.delete("canonical_addresses",
"_id NOT IN (SELECT DISTINCT recipient_ids FROM threads)", null);
return;
}
// Update the message count in the threads table as the sum
// of all messages in both the sms and pdu tables.
db.execSQL(
" UPDATE threads SET message_count = " +
" (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +
" ON threads._id = " + Sms.THREAD_ID +
" WHERE " + Sms.THREAD_ID + " = " + 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 + " = " + thread_id +
" AND (m_type=132 OR m_type=130 OR m_type=128)" +
" AND " + Mms.MESSAGE_BOX + " != 3) " +
" WHERE threads._id = " + thread_id + ";");
// Update the date and the snippet (and its character set) in
// the threads table to be that of the most recent message in
// the thread.
db.execSQL(
" UPDATE threads" +
" SET" +
" date =" +
" (SELECT date FROM" +
" (SELECT date * 1000 AS date, thread_id FROM pdu" +
" UNION SELECT date, thread_id FROM sms)" +
" WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," +
" 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 = " + thread_id + " ORDER BY date DESC LIMIT 1)," +
" 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 = " + thread_id + " ORDER BY date DESC LIMIT 1)" +
" WHERE threads._id = " + thread_id + ";");
// Update the error column of the thread to indicate if there
// are any messages in it that have failed to send.
// First check to see if there are any messages with errors in this thread.
String query = "SELECT thread_id FROM sms WHERE type=" +
Telephony.TextBasedSmsColumns.MESSAGE_TYPE_FAILED +
" AND thread_id = " + thread_id +
" LIMIT 1";
int setError = 0;
Cursor c = db.rawQuery(query, null);
if (c != null) {
try {
setError = c.getCount(); // Because of the LIMIT 1, count will be 1 or 0.
} finally {
c.close();
}
}
// What's the current state of the error flag in the threads table?
String errorQuery = "SELECT error FROM threads WHERE _id = " + thread_id;
c = db.rawQuery(errorQuery, null);
if (c != null) {
try {
if (c.moveToNext()) {
int curError = c.getInt(0);
if (curError != setError) {
// The current thread error column differs, update it.
db.execSQL("UPDATE threads SET error=" + setError +
" WHERE _id = " + thread_id);
}
}
} finally {
c.close();
}
}
}从函数名可以看出，这个函数用于更新操作。更新threads表，这个表包含了短信息，彩信息的会话信息。所谓会话就是同一个收件人或发件人归为一类。
3.1、如果threadId小于零，那么执行updateAllThreads函数方法，这个方法稍后介绍。如果threadId<0，那么return。
if (thread_id < 0) {
updateAllThreads(db, null, null);
return;
}3.2、如果threads表中_id所对应的短信或者彩信数目和为0，那么删除此_id所对应的行。
并且删除canonical_addresses表中对应的依赖信息。
也就是说如果_id，已经成为废数据了，那么删除它，以及和它的依赖。
// Delete the row for this thread in the threads table if
// there are no more messages attached to it in either
// the sms or pdu tables.
int rows = db.delete("threads",
"_id = ? AND _id NOT IN" +
" (SELECT thread_id FROM sms " +
" UNION SELECT thread_id FROM pdu)",
new String[] { String.valueOf(thread_id) });
if (rows > 0) {
// If this deleted a row, let's remove orphaned canonical_addresses and get outta here
db.delete("canonical_addresses",
"_id NOT IN (SELECT DISTINCT recipient_ids FROM threads)", null);
return;
}3.3、如果没有在上面两个地方return，那么更新threads表内字段message_count，计算短信和彩信中，thread_id等于threads._Id的所有数据的和。
也就是说，短信和彩信中，属于同一会话的信息数目和。
// Update the message count in the threads table as the sum
// of all messages in both the sms and pdu tables.
db.execSQL(
" UPDATE threads SET message_count = " +
" (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +
" ON threads._id = " + Sms.THREAD_ID +
" WHERE " + Sms.THREAD_ID + " = " + 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 + " = " + thread_id +
" AND (m_type=132 OR m_type=130 OR m_type=128)" +
" AND " + Mms.MESSAGE_BOX + " != 3) " +
" WHERE threads._id = " + thread_id + ";");3.4、更新threads表内的date,snippet以及snippet_cs字段。说实话，我没大注意过snippet_cs字段，不知道有什么用。
关于snippet得出的规则：查找短信的body，彩信的sub字段，比较他们的时间，那么时间最靠前，把那个信息赋给snippet。sinppet先是在会话列表条目中，告诉用户当前会话最新的一条信息的大体信息内容。
// Update the date and the snippet (and its character set) in
// the threads table to be that of the most recent message in
// the thread.
db.execSQL(
" UPDATE threads" +
" SET" +
" date =" +
" (SELECT date FROM" +
" (SELECT date * 1000 AS date, thread_id FROM pdu" +
" UNION SELECT date, thread_id FROM sms)" +
" WHERE thread_id = " + thread_id + " ORDER BY date DESC LIMIT 1)," +
" 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 = " + thread_id + " ORDER BY date DESC LIMIT 1)," +
" 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 = " + thread_id + " ORDER BY date DESC LIMIT 1)" +
" WHERE threads._id = " + thread_id + ";");3.5、
// Update the error column of the thread to indicate if there
// are any messages in it that have failed to send.
// First check to see if there are any messages with errors in this thread.
String query = "SELECT thread_id FROM sms WHERE type=" +
Telephony.TextBasedSmsColumns.MESSAGE_TYPE_FAILED +
" AND thread_id = " + thread_id +
" LIMIT 1";
int setError = 0;
Cursor c = db.rawQuery(query, null);
if (c != null) {
try {
setError = c.getCount(); // Because of the LIMIT 1, count will be 1 or 0.
} finally {
c.close();
}
}
// What's the current state of the error flag in the threads table?
String errorQuery = "SELECT error FROM threads WHERE _id = " + thread_id;
c = db.rawQuery(errorQuery, null);
if (c != null) {
try {
if (c.moveToNext()) {
int curError = c.getInt(0);
if (curError != setError) {
// The current thread error column differs, update it.
db.execSQL("UPDATE threads SET error=" + setError +
" WHERE _id = " + thread_id);
}
}
} finally {
c.close();
}
更新threads数据库的error字段，这个字段用来表示当前会话内是否包含错误信息的数据。
4、
public static void updateAllThreads(SQLiteDatabase db, String where, String[] whereArgs) {
if (where == null) {
where = "";
} else {
where = "WHERE (" + where + ")";
}
String query = "SELECT _id FROM threads WHERE _id IN " +
"(SELECT DISTINCT thread_id FROM sms " + where + ")";
Cursor c = db.rawQuery(query, whereArgs);
if (c != null) {
try {
while (c.moveToNext()) {
updateThread(db, c.getInt(0));
}
} finally {
c.close();
}
}
// TODO: there are several db operations in this function. Lets wrap them in a
// transaction to make it faster.
// remove orphaned threads
db.delete("threads",
"_id NOT IN (SELECT DISTINCT thread_id FROM sms " +
"UNION SELECT DISTINCT thread_id FROM pdu)", null);
// remove orphaned canonical_addresses
db.delete("canonical_addresses",
"_id NOT IN (SELECT DISTINCT recipient_ids FROM threads)", null);
}4.1、首先根据where以及whereArgs，查找threads数据库，找出需要更新那些threads_id对应的信息，然后调用updateThread方法，去更新。
if (where == null) {
where = "";
} else {
where = "WHERE (" + where + ")";
}
String query = "SELECT _id FROM threads WHERE _id IN " +
"(SELECT DISTINCT thread_id FROM sms " + where + ")";
Cursor c = db.rawQuery(query, whereArgs);
if (c != null) {
try {
while (c.moveToNext()) {
updateThread(db, c.getInt(0));
}
} finally {
c.close();
}
}
4.2、删除threads数据库内的废数据，我们在第三步介绍过，什么样的数据叫做废数据。
db.delete("threads",
"_id NOT IN (SELECT DISTINCT thread_id FROM sms " +
"UNION SELECT DISTINCT thread_id FROM pdu)", null);
// remove orphaned canonical_addresses
db.delete("canonical_addresses",
"_id NOT IN (SELECT DISTINCT recipient_ids FROM threads)", null);5、
public static int deleteOneSms(SQLiteDatabase db, int message_id) {
int thread_id = -1;
// Find the thread ID that the specified SMS belongs to.
Cursor c = db.query("sms", new String[] { "thread_id" },
"_id=" + message_id, null, null, null, null);
if (c != null) {
if (c.moveToFirst()) {
thread_id = c.getInt(0);
}
c.close();
}
// Delete the specified message.
int rows = db.delete("sms", "_id=" + message_id, null);
if (thread_id > 0) {
// Update its thread.
updateThread(db, thread_id);
}
return rows;
}根据message_id查找sms数据库，得到此message_id信息所对应的threads_id，然后删掉message_id所对应的sms数据表中的数据，然后根据刚才查找出来的threads_id字段更新threads数据表。刚才我们看了，updateTheads函数，首先删掉废数据，然后更新message_count，更新date，snippet，snippet_cs，error字段等，可以回头看一下3的介绍。
说白了就是删除sms表内messag_id所对应的信息，然后根据更新被删除信息所在的threads数据库。
6、
@Override
public void onCreate(SQLiteDatabase db) {
createMmsTables(db);//创建彩信相关数据表。例如pdu
createSmsTables(db);//创建短信相关数据表。例如sms
createCommonTables(db);//创建短信和彩信都需要的相关的数据表，例如threads
createCommonTriggers(db);//创建公共类别的触发器
createMmsTriggers(db);//创建彩信的触发器
createWordsTables(db);//创建word相关数据表以及触发器，我们在搜索时会用到它
createIndices(db);//...
}
这个函数是本类的重头戏，我们在这里先简单介绍一下他们。
7、
// When upgrading the database we need to populate the words
// table with the rows out of sms and part.
private void populateWordsTable(SQLiteDatabase db) {
final String TABLE_WORDS = "words";
{
Cursor smsRows = db.query(
"sms",
new String[] { Sms._ID, Sms.BODY },
null,
null,
null,
null,
null);
try {
if (smsRows != null) {
smsRows.moveToPosition(-1);
ContentValues cv = new ContentValues();
while (smsRows.moveToNext()) {
cv.clear();
long id = smsRows.getLong(0); // 0 for Sms._ID
String body = smsRows.getString(1); // 1 for Sms.BODY
cv.put(Telephony.MmsSms.WordsTable.ID, id);
cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body);
cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id);
cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);
db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);
}
}
} finally {
if (smsRows != null) {
smsRows.close();
}
}
}
{
Cursor mmsRows = db.query(
"part",
new String[] { Part._ID, Part.TEXT },
"ct = 'text/plain'",
null,
null,
null,
null);
try {
if (mmsRows != null) {
mmsRows.moveToPosition(-1);
ContentValues cv = new ContentValues();
while (mmsRows.moveToNext()) {
cv.clear();
long id = mmsRows.getLong(0); // 0 for Part._ID
String body = mmsRows.getString(1); // 1 for Part.TEXT
cv.put(Telephony.MmsSms.WordsTable.ID, id);
cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body);
cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id);
cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);
db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);
}
}
} finally {
if (mmsRows != null) {
mmsRows.close();
}
}
}
}7.1、
从sms表内，取出相关数据，插入到words表内。
Cursor smsRows = db.query(
"sms",
new String[] { Sms._ID, Sms.BODY },
null,
null,
null,
null,
null);
try {
if (smsRows != null) {
smsRows.moveToPosition(-1);
ContentValues cv = new ContentValues();
while (smsRows.moveToNext()) {
cv.clear();
long id = smsRows.getLong(0); // 0 for Sms._ID
String body = smsRows.getString(1); // 1 for Sms.BODY
cv.put(Telephony.MmsSms.WordsTable.ID, id);
cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body);
cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id);
cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);
db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);
}
}
} finally {
if (smsRows != null) {
smsRows.close();
}
}这些相关数据，包括sms的_Id,body等。
7.2、同样的，part表也一样。
Cursor mmsRows = db.query(
"part",
new String[] { Part._ID, Part.TEXT },
"ct = 'text/plain'",
null,
null,
null,
null);
try {
if (mmsRows != null) {
mmsRows.moveToPosition(-1);
ContentValues cv = new ContentValues();
while (mmsRows.moveToNext()) {
cv.clear();
long id = mmsRows.getLong(0); // 0 for Part._ID
String body = mmsRows.getString(1); // 1 for Part.TEXT
cv.put(Telephony.MmsSms.WordsTable.ID, id);
cv.put(Telephony.MmsSms.WordsTable.INDEXED_TEXT, body);
cv.put(Telephony.MmsSms.WordsTable.SOURCE_ROW_ID, id);
cv.put(Telephony.MmsSms.WordsTable.TABLE_ID, 1);
db.insert(TABLE_WORDS, Telephony.MmsSms.WordsTable.INDEXED_TEXT, cv);
}
}
} finally {
if (mmsRows != null) {
mmsRows.close();
}
}8、第一个创建数据库的方法
private void createWordsTables(SQLiteDatabase db) {
try {
db.execSQL("CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER, table_to_use INTEGER);");
// monitor the sms table
// NOTE don't handle inserts using a trigger because it has an unwanted
// side effect: the value returned for the last row ends up being the
// id of one of the trigger insert not the original row insert.
// Handle inserts manually in the provider.
db.execSQL("CREATE TRIGGER sms_words_update AFTER UPDATE ON sms BEGIN UPDATE words " +
" SET index_text = NEW.body WHERE (source_id=NEW._id AND table_to_use=1); " +
" END;");
db.execSQL("CREATE TRIGGER sms_words_delete AFTER DELETE ON sms BEGIN DELETE FROM " +
" words WHERE source_id = OLD._id AND table_to_use = 1; END;");
// monitor the mms table
db.execSQL("CREATE TRIGGER mms_words_update AFTER UPDATE ON part BEGIN UPDATE words " +
" SET index_text = NEW.text WHERE (source_id=NEW._id AND table_to_use=2); " +
" END;");
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;");
populateWordsTable(db);
} catch (Exception ex) {
Log.e(TAG, "got exception creating words table: " + ex.toString());
}
}8.1、
db.execSQL("CREATE VIRTUAL TABLE words USING FTS3 (_id INTEGER PRIMARY KEY, index_text TEXT, source_id INTEGER, table_to_use INTEGER);创建words数据库，这个数据库包括了_Id，index_text，source_id，table_to_use等字段。
8.2、英文的意思是，当信息插入时，不使用触发器来处理它，因为它存在一个缺点，最后返回的id不是原生的id，我们手动处理插入sms信息。好吧，此处留下一个疑问？为什么谷歌在words表不使用触发器处理sms的插入。
// NOTE don't handle inserts using a trigger because it has an unwanted
// side effect: the value returned for the last row ends up being the
// id of one of the trigger insert not the original row insert.
// Handle inserts manually in the provider.
db.execSQL("CREATE TRIGGER sms_words_update AFTER UPDATE ON sms BEGIN UPDATE words " +
" SET index_text = NEW.body WHERE (source_id=NEW._id AND table_to_use=1); " +
" END;");针对words表，当sms表更新时，将相应的信息，拷贝到words表内。
下面的代码和上面基本类似，删除sms的触发器，更新part，删除part表的触发器。最后调用populateWordsTable方法，这个方法我们咋7中介绍了。
9、
private void createIndices(SQLiteDatabase db) {
createThreadIdIndex(db);
}
private void createThreadIdIndex(SQLiteDatabase db) {
try {
db.execSQL("CREATE INDEX IF NOT EXISTS typeThreadIdIndex ON sms" +
" (type, thread_id);");
} catch (Exception ex) {
Log.e(TAG, "got exception creating indices: " + ex.toString());
}
}创建索引，关于索引，摘抄自W3SCHOOL:
CREATE INDEX 语句用于在表中创建索引。 在不读取整个表的情况下，索引使数据库应用程序可以更快地查找数据。 索引 您可以在表中创建索引，以便更加快速高效地查询数据。 用户无法看到索引，它们只能被用来加速搜索/查询。 注释：更新一个包含索引的表需要比更新一个没有索引的表更多的时间，这是由于索引本身也需要更新。因此，理想的做法是仅仅在常常被搜索的列（以及表）上面创建索引。 SQL CREATE INDEX 语法 在表上创建一个简单的索引。允许使用重复的值： CREATE INDEX index_name ON table_name (column_name) 注释："column_name" 规定需要索引的列。10、
private void createMmsTables(SQLiteDatabase db) {
// N.B.: Whenever the columns here are changed, the columns in
// {@ref MmsSmsProvider} must be changed to match.
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PDU + " (" +
Mms._ID + " INTEGER PRIMARY KEY," +
Mms.THREAD_ID + " INTEGER," +
Mms.DATE + " INTEGER," +
Mms.DATE_SENT + " INTEGER DEFAULT 0," +
Mms.MESSAGE_BOX + " INTEGER," +
Mms.READ + " INTEGER DEFAULT 0," +
Mms.MESSAGE_ID + " TEXT," +
Mms.SUBJECT + " TEXT," +
Mms.SUBJECT_CHARSET + " INTEGER," +
Mms.CONTENT_TYPE + " TEXT," +
Mms.CONTENT_LOCATION + " TEXT," +
Mms.EXPIRY + " INTEGER," +
Mms.MESSAGE_CLASS + " TEXT," +
Mms.MESSAGE_TYPE + " INTEGER," +
Mms.MMS_VERSION + " INTEGER," +
Mms.MESSAGE_SIZE + " INTEGER," +
Mms.PRIORITY + " INTEGER," +
Mms.READ_REPORT + " INTEGER," +
Mms.REPORT_ALLOWED + " INTEGER," +
Mms.RESPONSE_STATUS + " INTEGER," +
Mms.STATUS + " INTEGER," +
Mms.TRANSACTION_ID + " TEXT," +
Mms.RETRIEVE_STATUS + " INTEGER," +
Mms.RETRIEVE_TEXT + " TEXT," +
Mms.RETRIEVE_TEXT_CHARSET + " INTEGER," +
Mms.READ_STATUS + " INTEGER," +
Mms.CONTENT_CLASS + " INTEGER," +
Mms.RESPONSE_TEXT + " TEXT," +
Mms.DELIVERY_TIME + " INTEGER," +
Mms.DELIVERY_REPORT + " INTEGER," +
Mms.LOCKED + " INTEGER DEFAULT 0," +
Mms.SEEN + " INTEGER DEFAULT 0" +
");");
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_ADDR + " (" +
Addr._ID + " INTEGER PRIMARY KEY," +
Addr.MSG_ID + " INTEGER," +
Addr.CONTACT_ID + " INTEGER," +
Addr.ADDRESS + " TEXT," +
Addr.TYPE + " INTEGER," +
Addr.CHARSET + " INTEGER);");
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_PART + " (" +
Part._ID + " INTEGER PRIMARY KEY," +
Part.MSG_ID + " INTEGER," +
Part.SEQ + " INTEGER DEFAULT 0," +
Part.CONTENT_TYPE + " TEXT," +
Part.NAME + " TEXT," +
Part.CHARSET + " INTEGER," +
Part.CONTENT_DISPOSITION + " TEXT," +
Part.FILENAME + " TEXT," +
Part.CONTENT_ID + " TEXT," +
Part.CONTENT_LOCATION + " TEXT," +
Part.CT_START + " INTEGER," +
Part.CT_TYPE + " TEXT," +
Part._DATA + " TEXT," +
Part.TEXT + " TEXT);");
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_RATE + " (" +
Rate.SENT_TIME + " INTEGER);");
db.execSQL("CREATE TABLE " + MmsProvider.TABLE_DRM + " (" +
BaseColumns._ID + " INTEGER PRIMARY KEY," +
"_data TEXT);");
}这个不多说了吧，创建彩信相关的数据表。依次创建pdu表，addr表，part表，rate表，drm表。
11、
private void createMmsTriggers(SQLiteDatabase db) {
// 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);
}创建彩信相关的触发器
11.1、
// 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;");当pdu表内的数据删除时，开始执行，删除part表内Part.MSG_ID等于被删除数据的_id的数据。11.2、
// 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;");当pdu表内的数据删除时，开始执行删除addr数据表内Addr.MSG_ID等于被删除数据的_id的数据
11.3、
// 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;");
在android彩信中，一条彩信对应两条信息，一条数据包含了彩信发送时的各种内容，包括主题，彩信类型，时间等，第二条信息包括了彩信的状态报告，不过android的彩信状态报告支持的不好，有其名无其实。彩信的发送报告是只要彩信数据成功到达基站服务器就认为发送成功了，这点和短信很不一样，短信到达接收方才算信息发送成功，在接收方收到短信，移动基站会发送一条指令，经过发送方的短信moderm转达，达到协议层，然后达到短信上层，发送方才会看到发送报告。不过彩信走网络，这点和短信不同。这条触发器是在彩信类型MESSAGE_TYPE_SEND_REQ，我们在第一篇文中介绍过，这个类型代表发送的彩信。在从pdu表内删除发送的彩信时，删除pdu表内对应的delivery_report以及read_report信息。
11.4
// 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);利用我们前面定义的静态变量，构建在part表更新时更新threads表内的has_attachment字段。
当插入心数据到part表内时，当更新part表的msg_id时，当删除part表内的数据是，当更新pdu表的_id时，执行触发器。
12、
private void createSmsTables(SQLiteDatabase db) {
// N.B.: Whenever the columns here are changed, the columns in
// {@ref MmsSmsProvider} must be changed to match.
db.execSQL("CREATE TABLE sms (" +
"_id INTEGER PRIMARY KEY," +
"thread_id INTEGER," +
"address TEXT," +
"person INTEGER," +
"date INTEGER," +
"date_sent INTEGER DEFAULT 0," +
"protocol INTEGER," +
"read INTEGER DEFAULT 0," +
"status INTEGER DEFAULT -1," + // a TP-Status value
// or -1 if it
// status hasn't
// been received
"type INTEGER," +
"reply_path_present INTEGER," +
"subject TEXT," +
"body TEXT," +
"service_center TEXT," +
"locked INTEGER DEFAULT 0," +
"error_code INTEGER DEFAULT 0," +
"seen INTEGER DEFAULT 0" +
");");
/**
* This table is used by the SMS dispatcher to hold
* incomplete partial messages until all the parts arrive.
*/
db.execSQL("CREATE TABLE raw (" +
"_id INTEGER PRIMARY KEY," +
"date INTEGER," +
"reference_number INTEGER," + // one per full message
"count INTEGER," + // the number of parts
"sequence INTEGER," + // the part number of this message
"destination_port INTEGER," +
"address TEXT," +
"pdu TEXT);"); // the raw PDU for this part
db.execSQL("CREATE TABLE attachments (" +
"sms_id INTEGER," +
"content_url TEXT," +
"offset INTEGER);");
/**
* This table is used by the SMS dispatcher to hold pending
* delivery status report intents.
*/
db.execSQL("CREATE TABLE sr_pending (" +
"reference_number INTEGER," +
"action TEXT," +
"data TEXT);");
}创建sms数据表，创建raw数据表 （长短信接收需要借助的表，在介绍framework层逻辑时会介绍），创建attachments表。，创建sr_pending表（这个表也很重要，在上层看不到它的身影的）。
13、
private void createCommonTables(SQLiteDatabase db) {
// TODO Ensure that each entry is removed when the last use of
// any address equivalent to its address is removed.
/**
* This table maps the first instance seen of any particular
* MMS/SMS address to an ID, which is then used as its
* canonical representation. If the same address or an
* equivalent address (as determined by our Sqlite
* PHONE_NUMBERS_EQUAL extension) is seen later, this same ID
* will be used. The _id is created with AUTOINCREMENT so it
* will never be reused again if a recipient is deleted.
*/
db.execSQL("CREATE TABLE canonical_addresses (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT," +
"address TEXT);");
/**
* This table maps the subject and an ordered set of recipient
* IDs, separated by spaces, to a unique thread ID. The IDs
* come from the canonical_addresses table. This works
* because messages are considered to be part of the same
* thread if they have the same subject (or a null subject)
* and the same set of recipients.
*/
db.execSQL("CREATE TABLE threads (" +
Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
Threads.DATE + " INTEGER DEFAULT 0," +
Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," +
Threads.RECIPIENT_IDS + " TEXT," +
Threads.SNIPPET + " TEXT," +
Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," +
Threads.READ + " INTEGER DEFAULT 1," +
Threads.TYPE + " INTEGER DEFAULT 0," +
Threads.ERROR + " INTEGER DEFAULT 0," +
Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);");
/**
* This table stores the queue of messages to be sent/downloaded.
*/
db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" +
PendingMessages._ID + " INTEGER PRIMARY KEY," +
PendingMessages.PROTO_TYPE + " INTEGER," +
PendingMessages.MSG_ID + " INTEGER," +
PendingMessages.MSG_TYPE + " INTEGER," +
PendingMessages.ERROR_TYPE + " INTEGER," +
PendingMessages.ERROR_CODE + " INTEGER," +
PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," +
PendingMessages.DUE_TIME + " INTEGER," +
PendingMessages.LAST_TRY + " INTEGER);");
}13.1
/**
* This table maps the first instance seen of any particular
* MMS/SMS address to an ID, which is then used as its
* canonical representation. If the same address or an
* equivalent address (as determined by our Sqlite
* PHONE_NUMBERS_EQUAL extension) is seen later, this same ID
* will be used. The _id is created with AUTOINCREMENT so it
* will never be reused again if a recipient is deleted.
*/
db.execSQL("CREATE TABLE canonical_addresses (" +
"_id INTEGER PRIMARY KEY AUTOINCREMENT," +
"address TEXT);");
这个表表示了所有接收到的短信，彩信的address，映射address为id，并且这个表是自增长的，因而删除的id不会再次用到。
13.2
/**
* This table maps the subject and an ordered set of recipient
* IDs, separated by spaces, to a unique thread ID. The IDs
* come from the canonical_addresses table. This works
* because messages are considered to be part of the same
* thread if they have the same subject (or a null subject)
* and the same set of recipients.
*/
db.execSQL("CREATE TABLE threads (" +
Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
Threads.DATE + " INTEGER DEFAULT 0," +
Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," +
Threads.RECIPIENT_IDS + " TEXT," +
Threads.SNIPPET + " TEXT," +
Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," +
Threads.READ + " INTEGER DEFAULT 1," +
Threads.TYPE + " INTEGER DEFAULT 0," +
Threads.ERROR + " INTEGER DEFAULT 0," +
Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);");创建threads表。
13.3
/**
* This table stores the queue of messages to be sent/downloaded.
*/
db.execSQL("CREATE TABLE " + MmsSmsProvider.TABLE_PENDING_MSG +" (" +
PendingMessages._ID + " INTEGER PRIMARY KEY," +
PendingMessages.PROTO_TYPE + " INTEGER," +
PendingMessages.MSG_ID + " INTEGER," +
PendingMessages.MSG_TYPE + " INTEGER," +
PendingMessages.ERROR_TYPE + " INTEGER," +
PendingMessages.ERROR_CODE + " INTEGER," +
PendingMessages.RETRY_INDEX + " INTEGER NOT NULL DEFAULT 0," +
PendingMessages.DUE_TIME + " INTEGER," +
PendingMessages.LAST_TRY + " INTEGER);");创建pending_msgs表，这个表，用于彩信框架中。在后面介绍彩信时会介绍。
14、
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;");
}
14.1
// 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);创建触发器，当向pdu表内插入新数据时，开始更新threads表内的date，snippet，snippet_cs，message_count,read等字段。
14.2
// 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);当插入心数据到sms数据表内，开始更新threads。
14.3
// 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);当更新pdu表的date，subject，message_box等字段，更新threads表的字段。
14.4
// 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);当更新sms表的date，body，type等字段，开始更新threads
14.5
// 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;");更新pdu表内的read字段，开始更新threads表内的read字段
14.6
// 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;");
更新sms字段的read字段，开始更新threads表的read字段
14.7
// 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;");当删除pdu表内的数据时，开始更新threads中的date，message_count等字段。
14.8
// 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;");当向pdu表内插入数据时，同时拷贝一份插入到pending_msgs表内。
14.9
// 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;");当更新了pdu表内的数据，开始插入数据到pending_msg中。
14.10
// 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;");当更新pdu表内的信息的message_box，信息转到发件箱，开始删除pending_msgs表内对应的数据。
pending_msg表内的数据是彩信待处理时的状态，当发送成功，会更新信息的状态。
14.11
// 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;");删除表pdu，同时删除pending_msg表内对应的信息。
14.12
// 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;");当更新了pending_msg表内的error_type，开始更新threads表内的error字段。这个时候一般彩信由于某些原因发送失败。
14.13
// 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;");当删除了pdu表内的数据，那么同时更新threads表内的error字段。
14.14
// 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;");当更新了pdu表内的message_box字段，更新threads中的error字段。某些彩信发送失败，导致error = 1,如果彩信重发机制下发送成功了，那么我们需要更新threads表内的error字段。
14.15
// 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;");
当sms的type字段后，开始更新treads表内的error字段。
15
public void onUpgrade(SQLiteDatabase db, int oldVersion, int currentVersion) {
Log.w(TAG, "Upgrading database from version " + oldVersion
+ " to " + currentVersion + ".");
switch (oldVersion) {
case 40:
if (currentVersion <= 40) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion41(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
// fall through
case 41:
...........
....
...
case 54:
if (currentVersion <= 54) {
return;
}
db.beginTransaction();
try {
upgradeDatabaseToVersion55(db);
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, ex.getMessage(), ex);
break;
} finally {
db.endTransaction();
}
return;
}
Log.e(TAG, "Destroying all old data.");
dropAll(db);
onCreate(db);
}
当更新时，dropAll，onCreate方法执行，删掉表，重新构建数据库。
16
private void dropAll(SQLiteDatabase db) {
// Clean the database out in order to start over from scratch.
// We don't need to drop our triggers here because SQLite automatically
// drops a trigger when its attached database is dropped.
db.execSQL("DROP TABLE IF EXISTS canonical_addresses");
db.execSQL("DROP TABLE IF EXISTS threads");
db.execSQL("DROP TABLE IF EXISTS " + MmsSmsProvider.TABLE_PENDING_MSG);
db.execSQL("DROP TABLE IF EXISTS sms");
db.execSQL("DROP TABLE IF EXISTS raw");
db.execSQL("DROP TABLE IF EXISTS attachments");
db.execSQL("DROP TABLE IF EXISTS thread_ids");
db.execSQL("DROP TABLE IF EXISTS sr_pending");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PDU + ";");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_ADDR + ";");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_PART + ";");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_RATE + ";");
db.execSQL("DROP TABLE IF EXISTS " + MmsProvider.TABLE_DRM + ";");
}删除表。
17、
private void upgradeDatabaseToVersion41(SQLiteDatabase db) {
db.execSQL("DROP TRIGGER IF EXISTS update_threads_error_on_move_mms");
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;");
}当数据库version更新时，会执行的函数，类似的函数还有一些，可以在onUpdate函数内找到
18
public synchronized SQLiteDatabase getWritableDatabase() {
SQLiteDatabase db = super.getWritableDatabase();
if (!sTriedAutoIncrement) {
sTriedAutoIncrement = true;
boolean hasAutoIncrementThreads = hasAutoIncrement(db, "threads");
boolean hasAutoIncrementAddresses = hasAutoIncrement(db, "canonical_addresses");
Log.d(TAG, "[getWritableDatabase] hasAutoIncrementThreads: " + hasAutoIncrementThreads +
" hasAutoIncrementAddresses: " + hasAutoIncrementAddresses);
boolean autoIncrementThreadsSuccess = true;
boolean autoIncrementAddressesSuccess = true;
if (!hasAutoIncrementThreads) {
db.beginTransaction();
try {
if (false && sFakeLowStorageTest) {
Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " +
" - fake exception");
throw new Exception("FakeLowStorageTest");
}
upgradeThreadsTableToAutoIncrement(db); // a no-op if already upgraded
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, "Failed to add autoIncrement to threads;: " + ex.getMessage(), ex);
autoIncrementThreadsSuccess = false;
} finally {
db.endTransaction();
}
}
if (!hasAutoIncrementAddresses) {
db.beginTransaction();
try {
if (false && sFakeLowStorageTest) {
Log.d(TAG, "[getWritableDatabase] mFakeLowStorageTest is true " +
" - fake exception");
throw new Exception("FakeLowStorageTest");
}
upgradeAddressTableToAutoIncrement(db); // a no-op if already upgraded
db.setTransactionSuccessful();
} catch (Throwable ex) {
Log.e(TAG, "Failed to add autoIncrement to canonical_addresses: " +
ex.getMessage(), ex);
autoIncrementAddressesSuccess = false;
} finally {
db.endTransaction();
}
}
if (autoIncrementThreadsSuccess && autoIncrementAddressesSuccess) {
if (mLowStorageMonitor != null) {
// We've already updated the database. This receiver is no longer necessary.
Log.d(TAG, "Unregistering mLowStorageMonitor - we've upgraded");
mContext.unregisterReceiver(mLowStorageMonitor);
mLowStorageMonitor = null;
}
} else {
if (sFakeLowStorageTest) {
sFakeLowStorageTest = false;
}
// We failed, perhaps because of low storage. Turn on a receiver to watch for
// storage space.
if (mLowStorageMonitor == null) {
Log.d(TAG, "[getWritableDatabase] turning on storage monitor");
mLowStorageMonitor = new LowStorageMonitor();
IntentFilter intentFilter = new IntentFilter();
intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_LOW);
intentFilter.addAction(Intent.ACTION_DEVICE_STORAGE_OK);
mContext.registerReceiver(mLowStorageMonitor, intentFilter);
}
}
}
return db;
}得到可写的数据库。
19、
// Determine whether a particular table has AUTOINCREMENT in its schema.
private boolean hasAutoIncrement(SQLiteDatabase db, String tableName) {
boolean result = false;
String query = "SELECT sql FROM sqlite_master WHERE type='table' AND name='" +
tableName + "'";
Cursor c = db.rawQuery(query, null);
if (c != null) {
try {
if (c.moveToFirst()) {
String schema = c.getString(0);
result = schema != null ? schema.contains("AUTOINCREMENT") : false;
Log.d(TAG, "[MmsSmsDb] tableName: " + tableName + " hasAutoIncrement: " +
schema + " result: " + result);
}
} finally {
c.close();
}
}
return result;
}判断数据库内是否含有AUTONICREMENT字段20、
// upgradeThreadsTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to
// the threads table. This could fail if the user has a lot of conversations and not enough
// storage to make a copy of the threads table. That's ok. This upgrade is optional. It'll
// be called again next time the device is rebooted.
private void upgradeThreadsTableToAutoIncrement(SQLiteDatabase db) {
if (hasAutoIncrement(db, "threads")) {
Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: already upgraded");
return;
}
Log.d(TAG, "[MmsSmsDb] upgradeThreadsTableToAutoIncrement: upgrading");
// Make the _id of the threads table autoincrement so we never re-use thread ids
// Have to create a new temp threads table. Copy all the info from the old table.
// Drop the old table and rename the new table to that of the old.
db.execSQL("CREATE TABLE threads_temp (" +
Threads._ID + " INTEGER PRIMARY KEY AUTOINCREMENT," +
Threads.DATE + " INTEGER DEFAULT 0," +
Threads.MESSAGE_COUNT + " INTEGER DEFAULT 0," +
Threads.RECIPIENT_IDS + " TEXT," +
Threads.SNIPPET + " TEXT," +
Threads.SNIPPET_CHARSET + " INTEGER DEFAULT 0," +
Threads.READ + " INTEGER DEFAULT 1," +
Threads.TYPE + " INTEGER DEFAULT 0," +
Threads.ERROR + " INTEGER DEFAULT 0," +
Threads.HAS_ATTACHMENT + " INTEGER DEFAULT 0);");
db.execSQL("INSERT INTO threads_temp SELECT * from threads;");
db.execSQL("DROP TABLE threads;");
db.execSQL("ALTER TABLE threads_temp RENAME TO threads;");
}
谷歌的程序员也很会幽默。相信有英语语感的同学，看这段英语注释，是不是和我一样，会笑出来。程序员是严谨的，that's ok，好逗，程序员的节操那？。
/ upgradeThreadsTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to
// the threads table. This could fail if the user has a lot of conversations and not enough
// storage to make a copy of the threads table. That's ok. This upgrade is optional. It'll
// be called again next time the device is rebooted.
这个函数是在谷歌程序员在后期维护MMS应用，想让thread数据库的_id字段，具备自增长。
在创建新信息时，会首先创建一个threadId，这个threadId就是数据表threads中的_id，如果最大_id的信息被删掉了，那么再次创建的信息的threadId和删掉的信息一样，这样的设计在以前可能不会有什么问题，但可能会引起一些bug，androd4.0以及android.4.1,谷歌的程序员一直在修订新需求产生的问题。
创建一个temp的表，将原表内的数据拷贝到这个temp表内，然后将temp表改名字。
21、
// upgradeAddressTableToAutoIncrement() is called to add the AUTOINCREMENT keyword to
// the canonical_addresses table. This could fail if the user has a lot of people they've
// messaged with and not enough storage to make a copy of the canonical_addresses table.
// That's ok. This upgrade is optional. It'll be called again next time the device is rebooted.
private void upgradeAddressTableToAutoIncrement(SQLiteDatabase db) {
if (hasAutoIncrement(db, "canonical_addresses")) {
Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: already upgraded");
return;
}
Log.d(TAG, "[MmsSmsDb] upgradeAddressTableToAutoIncrement: upgrading");
// Make the _id of the canonical_addresses table autoincrement so we never re-use ids
// Have to create a new temp canonical_addresses table. Copy all the info from the old
// table. Drop the old table and rename the new table to that of the old.
db.execSQL("CREATE TABLE canonical_addresses_temp (_id INTEGER PRIMARY KEY AUTOINCREMENT," +
"address TEXT);");
db.execSQL("INSERT INTO canonical_addresses_temp SELECT * from canonical_addresses;");
db.execSQL("DROP TABLE canonical_addresses;");
db.execSQL("ALTER TABLE canonical_addresses_temp RENAME TO canonical_addresses;");
}和20类似。
22、
private class LowStorageMonitor extends BroadcastReceiver {
public LowStorageMonitor() {
}
public void onReceive(Context context, Intent intent) {
String action = intent.getAction();
Log.d(TAG, "[LowStorageMonitor] onReceive intent " + action);
if (Intent.ACTION_DEVICE_STORAGE_OK.equals(action)) {
sTriedAutoIncrement = false; // try to upgrade on the next getWriteableDatabase
}
}
}广播接收者
23、
private void updateThreadsAttachmentColumn(SQLiteDatabase db) {
// Set the values of that column correctly based on the current
// contents of the database.
db.execSQL("UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT DISTINCT pdu.thread_id FROM part " +
" JOIN pdu ON pdu._id=part.mid " +
" WHERE part.ct != 'text/plain' AND part.ct != 'application/smil')");
}更新threads表中的has_attachment字段。
这就是SQLiteDatabaseHelper.java类。MmsProvider.java，SmsProvider.java，MmsSmsProvider.java都会使用它。