当前位置:操作系统 > 安卓/Android >>

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 ,
CopyRight © 2012 站长网 编程知识问答 www.zzzyk.com All Rights Reserved
部份技术文章来自网络,