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 " +

补充：移动开发 , Android ,