android短彩信数据库设计源码解析（一）

维护短彩信很长时间了，终于想写点什么了，那就从数据库开始吧！不当之处，欢迎指正。

关于LEFT_JOIN,INNER_JOIN等数据库知识，大家可以访问W3SCHOOL。

MmsSmsDatabaseHelper.java

1、首先看一下私有静态常量。这些静态常量在构建数据库触发器，构建数据表时会用到。

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、

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、

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 th