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

最新推荐文章于 2024-05-04 21:19:16 发布

hailushijie

最新推荐文章于 2024-05-04 21:19:16 发布

阅读量4k

点赞数 2

CC 4.0 BY-SA版权

分类专栏：短彩信 Android 文章标签： android短彩信数据库设计

本文链接：https://blog.youkuaiyun.com/droyon/article/details/8724403

Android 同时被 2 个专栏收录

174 篇文章

订阅专栏

短彩信

10 篇文章

订阅专栏

本文详细介绍了短信和彩信数据库的设计思路，包括如何通过触发器更新会话信息、检查未读消息状态及统计消息数量等内容。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

转载请注明出处：http://blog.youkuaiyun.com/droyon/article/details/8724403

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

关于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 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判断条件上

WHERE threads._id = new.thread_id;

WHERE threads._id = old.thread_id;

一个new，一个old很能说明问题，也就是说，这两个常量，一个用在处理新插入信息，一个处理旧的信息。

4、

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、

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、

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、

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、

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、

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、

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、

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、

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.

这是数据库设计会用的静态常量字符串，后面会介绍数据库创建等。