編輯：關於Android編程
維護短彩信很長時間了，終於想寫點什麼了，那就從數據庫開始吧！不當之處，歡迎指正。
關於LEFT_JOIN,INNER_JOIN等數據庫知識，大家可以訪問W3SCHOOL。
MmsSmsDatabaseHelper.java
1、首先看一下私有靜態常量。這些靜態常量在構建數據庫觸發器，構建數據表時會用到。
[java]
private static final String SMS_UPDATE_THREAD_READ_BODY =
" UPDATE threads SET read = " +
" CASE (SELECT COUNT(*)" +
" FROM sms" +
" WHERE " + Sms.READ + " = 0" +
" AND " + Sms.THREAD_ID + " = threads._id)" +
" WHEN 0 THEN 1" +
" ELSE 0" +
" END" +
" WHERE threads._id = new." + Sms.THREAD_ID + "; ";
private static final String SMS_UPDATE_THREAD_READ_BODY =
" UPDATE threads SET read = " +
" CASE (SELECT COUNT(*)" +
" FROM sms" +
" WHERE " + Sms.READ + " = 0" +
" AND " + Sms.THREAD_ID + " = threads._id)" +
" WHEN 0 THEN 1" +
" ELSE 0" +
" END" +
" WHERE threads._id = new." + Sms.THREAD_ID + "; ";大體是這樣，裡面內嵌了一個函數，函數內容是，首先查詢sms數據表中Sms.THREAD_ID的值為threads._id的所有行，再次找出這些行中Sms.READ的值為0的行，統計其行數目。如果行數目為0,則輸出1,否則輸出0.將輸出的值賦給threads數據表_id值為new.Sms.THREAD_ID所對應的行中的read字段。
說白了就是檢查短信數據庫中是否存在未讀短信。
2、
[java]
private static final String UPDATE_THREAD_COUNT_ON_NEW =
" UPDATE threads SET message_count = " +
" (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +
" ON threads._id = " + Sms.THREAD_ID +
" WHERE " + Sms.THREAD_ID + " = new.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 + " = new.thread_id" +
" AND (m_type=132 OR m_type=130 OR m_type=128)" +
" AND " + Mms.MESSAGE_BOX + " != 3) " +
" WHERE threads._id = new.thread_id; ";
private static final String UPDATE_THREAD_COUNT_ON_NEW =
" UPDATE threads SET message_count = " +
" (SELECT COUNT(sms._id) FROM sms LEFT JOIN threads " +
" ON threads._id = " + Sms.THREAD_ID +
" WHERE " + Sms.THREAD_ID + " = new.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 + " = new.thread_id" +
" AND (m_type=132 OR m_type=130 OR m_type=128)" +
" AND " + Mms.MESSAGE_BOX + " != 3) " +
" WHERE threads._id = new.thread_id; ";
內嵌了一個搜索體。搜索體的作用是找出sms數據表中，特定thread_id值對應的，並且Sms.TYPE 不等於3（草稿信息的類型）的信息數目。然後加上 彩信數據表pdu表中，特定thread_id對應的信息，並且信息類型等於132（接收的彩信，已下載彩信內容的類型）和130（接收的彩信，沒下載前的類型），以及128（發送的彩信）。並且message_box不等於3（草稿信息的類型）。
說白了，就是將短信和彩信中信息的數據加在一起，存到threads數據表的message_count字段中。
3、
[java]
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 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; ";
和上面基本類似，不同在最後的where判斷條件上[java] view plaincopyprint?WHERE threads._id = new.thread_id;
WHERE threads._id = new.thread_id;[java] view plaincopyprint?WHERE threads._id = old.thread_id;
WHERE threads._id = old.thread_id;一個new，一個old很能說明問題，也就是說，這兩個常量，一個用在處理新插入信息，一個處理舊的信息。
4、
[java]
private static final String SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =
"BEGIN" +
" UPDATE threads SET" +
" date = (strftime('%s','now') * 1000), " +
" snippet = new." + Sms.BODY + ", " +
" snippet_cs = 0" +
" WHERE threads._id = new." + Sms.THREAD_ID + "; " +
UPDATE_THREAD_COUNT_ON_NEW +
SMS_UPDATE_THREAD_READ_BODY +
"END;";
private static final String SMS_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =
"BEGIN" +
" UPDATE threads SET" +
" date = (strftime('%s','now') * 1000), " +
" snippet = new." + Sms.BODY + ", " +
" snippet_cs = 0" +
" WHERE threads._id = new." + Sms.THREAD_ID + "; " +
UPDATE_THREAD_COUNT_ON_NEW +
SMS_UPDATE_THREAD_READ_BODY +
"END;";這個常量用在處理那些新更新的數據，更新threads數據表的date字段、snippet字段（信息會話列表中，顯示最新信息的部分提示），snippet_cs字段。然後使用我們前面介紹的兩個靜態常量。
說白了，在更新sms數據表時，觸發更新threads數據表。
5、
[java]
private static final String PDU_UPDATE_THREAD_CONSTRAINTS =
" WHEN new." + Mms.MESSAGE_TYPE + "=" +
PduHeaders.MESSAGE_TYPE_RETRIEVE_CONF +
" OR new." + Mms.MESSAGE_TYPE + "=" +
PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND +
" OR new." + Mms.MESSAGE_TYPE + "=" +
PduHeaders.MESSAGE_TYPE_SEND_REQ + " ";
private static final String PDU_UPDATE_THREAD_CONSTRAINTS =
" WHEN new." + Mms.MESSAGE_TYPE + "=" +
PduHeaders.MESSAGE_TYPE_RETRIEVE_CONF +
" OR new." + Mms.MESSAGE_TYPE + "=" +
PduHeaders.MESSAGE_TYPE_NOTIFICATION_IND +
" OR new." + Mms.MESSAGE_TYPE + "=" +
PduHeaders.MESSAGE_TYPE_SEND_REQ + " ";這個靜態常量字符串的意思是說，信息類型是發送的彩信或者接收的彩信類型，或者接收的彩信、但數據未下載的信息三種類型。
6、
[java]
private static final String PDU_UPDATE_THREAD_READ_BODY =
" UPDATE threads SET read = " +
" CASE (SELECT COUNT(*)" +
" FROM " + MmsProvider.TABLE_PDU +
" WHERE " + Mms.READ + " = 0" +
" AND " + Mms.THREAD_ID + " = threads._id " +
" AND (m_type=132 OR m_type=130 OR m_type=128)) " +
" WHEN 0 THEN 1" +
" ELSE 0" +
" END" +
" WHERE threads._id = new." + Mms.THREAD_ID + "; ";
private static final String PDU_UPDATE_THREAD_READ_BODY =
" UPDATE threads SET read = " +
" CASE (SELECT COUNT(*)" +
" FROM " + MmsProvider.TABLE_PDU +
" WHERE " + Mms.READ + " = 0" +
" AND " + Mms.THREAD_ID + " = threads._id " +
" AND (m_type=132 OR m_type=130 OR m_type=128)) " +
" WHEN 0 THEN 1" +
" ELSE 0" +
" END" +
" WHERE threads._id = new." + Mms.THREAD_ID + "; ";和短信一樣，計算彩信中是否存在未讀信息，如果存在在threads數據表中置標志為。檢索的彩信僅僅是發送或者接收也就是5中所描述的三種彩信。
7、
[java]
private static final String PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =
"BEGIN" +
" UPDATE threads SET" +
" date = (strftime('%s','now') * 1000), " +
" snippet = new." + Mms.SUBJECT + ", " +
" snippet_cs = new." + Mms.SUBJECT_CHARSET +
" WHERE threads._id = new." + Mms.THREAD_ID + "; " +
UPDATE_THREAD_COUNT_ON_NEW +
PDU_UPDATE_THREAD_READ_BODY +
"END;";
private static final String PDU_UPDATE_THREAD_DATE_SNIPPET_COUNT_ON_UPDATE =
"BEGIN" +
" UPDATE threads SET" +
" date = (strftime('%s','now') * 1000), " +
" snippet = new." + Mms.SUBJECT + ", " +
" snippet_cs = new." + Mms.SUBJECT_CHARSET +
" WHERE threads._id = new." + Mms.THREAD_ID + "; " +
UPDATE_THREAD_COUNT_ON_NEW +
PDU_UPDATE_THREAD_READ_BODY +
"END;";和短信一樣，在更新pdu表時，觸發更新threads表，其中更新date，snippet，snippet_cs字段，同時用到了前面定義的靜態常量。
8、
[java]
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; ";
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; ";在彩信或者短信數據庫刪除信息條目時，更新會話信息數據庫threads中的snippet以及snippet_cs字段。
9、
[java]
private static final String PART_UPDATE_THREADS_ON_INSERT_TRIGGER =
"CREATE TRIGGER update_threads_on_insert_part " +
" AFTER INSERT ON part " +
" WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " +
" WHERE part._id=new._id LIMIT 1); " +
" END";
private static final String PART_UPDATE_THREADS_ON_INSERT_TRIGGER =
"CREATE TRIGGER update_threads_on_insert_part " +
" AFTER INSERT ON part " +
" WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " +
" WHERE part._id=new._id LIMIT 1); " +
" END";這個常量字符串是用於創建觸發器，在向part表內插入一條信息時，並且其CONTENT_TYPE(ct)不等於“text/plain”或者“application/smil”時，開始更新會話表threads，設置表內字段has_attachment為1，如果thread_id存在。
10、
[java]
private static final String PART_UPDATE_THREADS_ON_UPDATE_TRIGGER =
"CREATE TRIGGER update_threads_on_update_part " +
" AFTER UPDATE of " + Part.MSG_ID + " ON part " +
" WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " +
" WHERE part._id=new._id LIMIT 1); " +
" END";
private static final String PART_UPDATE_THREADS_ON_UPDATE_TRIGGER =
"CREATE TRIGGER update_threads_on_update_part " +
" AFTER UPDATE of " + Part.MSG_ID + " ON part " +
" WHEN new.ct != 'text/plain' AND new.ct != 'application/smil' " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu ON pdu._id=part.mid " +
" WHERE part._id=new._id LIMIT 1); " +
" END";這個常量字段用於創建觸發器，內容是：當更新了part數據表中的Part.MSG_ID字段時，並且CONTENT_TYPE不等於text/plain或者application/smil時，開始更新數據庫threads數據表，設置表內字段has_attachment = 1,並且當_id存在時。
11、
[java]
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";
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";用戶創建觸發器，在刪除part表內的數據時，如果ct也就是CONTENT_TYPE不等於text/plain或者application/smail時，開始更新threads數據表，它沒有where限制，也就是設置表內所有數據的has_attachment字段等於下面函數的輸出值。
函數作用：查詢part表內特定的part.mid所對應的行的數量，如果為0,輸出0,如果不為0,那麼輸出1.
12、
[java]
private static final String PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER =
"CREATE TRIGGER update_threads_on_update_pdu " +
" AFTER UPDATE of thread_id ON pdu " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu " +
" WHERE part.ct != 'text/plain' AND part.ct != 'application/smil' " +
" AND part.mid = pdu._id);" +
" END";
private static final String PDU_UPDATE_THREADS_ON_UPDATE_TRIGGER =
"CREATE TRIGGER update_threads_on_update_pdu " +
" AFTER UPDATE of thread_id ON pdu " +
" BEGIN " +
" UPDATE threads SET has_attachment=1 WHERE _id IN " +
" (SELECT pdu.thread_id FROM part JOIN pdu " +
" WHERE part.ct != 'text/plain' AND part.ct != 'application/smil' " +
" AND part.mid = pdu._id);" +
" END";創建一個觸發器，在更新了pdu數據表內的thread_id字段，開始更新thread數據表，設置has_attchment字段等於1，並且特定的thread_id存在，這個特定thread_id來自於更新的pdu表行中的_id所對應的thread_id.
這是數據庫設計會用的靜態常量字符串，後面會介紹數據庫創建等。
Android實現滑動到頂部懸停的效果
先來看下要實現效果圖：查閱資料後，發現網上大部分都是用這種方法實現的：多寫一個和需要懸浮的部分一模一樣的layout，先把浮動區域的可見性設置為gone。當浮動區域滑動到
怎樣使用移動電源充電？移動電源怎樣用？
隨著手機的普及，移動電源也開始火起來了，非常的方便，出門沒電必備，能先將電充進移動電源裡，儲存在裡面，等手機沒電再用移動電源給手機充電。那麼小編現在就給大家
如何徹底清除Android手機的使用痕跡
手機中保存並記錄著很多我們個人數據，比如浏覽器記錄、微信賬號、聊天記錄等，這些信息如果被有心人盯上自然後後患無窮。有時候我們會將手機借給好友，或是購新機後在
Android基礎入門教程——10.2 SmsManager(短信管理器)
本節引言： 本節帶來的是Android中的SmsManager(短息管理器)，見名知意，就是用來管理手機短信的， 而該類的應用場景並不多，一般是我
