首页 > 程序开发 > 移动开发 > Android > 正文
android短彩信数据库设计源码解析(一)
2013-03-27 09:50:52     我来说两句       作者:hailushijie
收藏    我要投稿

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

关于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安装报错a folder failed to be moved ... when ready press yes to try again
下一篇:android 4.2.1短信SMS常用接口整理
相关文章
图文推荐
排行
热门
文章
下载
读书

关于我们 | 联系我们 | 广告服务 | 投资合作 | 版权申明 | 在线帮助 | 网站地图 | 作品发布 | Vip技术培训
版权所有: 红黑联盟--致力于做最好的IT技术学习网站