SQLite是SQL引擎中经常被忽视的一种风格。一些人认为，由于其高度灵活的特性和在几乎任何资源有限的平台上运行的能力，它是现有最丰富的SQL引擎。与MySQL、PostgreSQL、MSSQL或Oracle等其他SQL引擎不同，SQLite在没有服务器的情况下运行。SQLite不依赖于数据目录或持续运行的守护进程：数据库封装在单个文件中。
SQLite和iMessage
iMessage是当今最流行的消息平台之一，这主要是因为它内置于iOS和Mac设备中。自发布以来，它已经有了很大的发展。但是，它的核心仅仅是一个即时通讯平台。iMessage在后台使用SQLite存储有关消息、对话及其参与者的关系数据。
作为一名长期使用苹果的用户，自2009年11月10日我第一次使用iPhone以来，我就一直在备份和传输我的iPhone数据。因为我一直在以数字方式储存我的文本数据，所以我的iMessage数据库的大小接近1GB。
直到几年前，iMessage的内置搜索功能还非常有限，而且存在缺陷。尽管它最近有了显著的改进，但与几乎任何最终用户工具一样，它在查询方面非常有限。我们这些经常处理被限制在有限前端后面的数据的人通常希望能够直接访问SQL数据库。幸运的是，iMessage数据库不是不可访问的-事实上，它非常容易访问。
查找iMessage SQL数据库
在你的Mac上
如果你在Mac和iPhone上都启用了iMessage，那么你有两个不同的数据库可供选择。Mac上的数据库很容易找到，因为它位于~/Library/Messages/chat.db下。如果您不将Mac用于iMessage，或者，在我的情况下，您的Mac iMessage无法追溯很久，您可以通过执行Mac备份来提取iPhone的数据库。
在你的iPhone上
按照以下说明提取iPhone的iMessage数据库：
1.打开Finder并在“位置”下选择您的iPhone。
2.找到“备份”部分，选择“将iPhone上的所有数据备份到此Mac”，然后按“立即备份”立即创建新备份。这个过程可能需要一段时间。
3.完成后，您将在/Users/[username]/Library/Application Support/MobileSync/Backup/[Backup name]/3d/3d0d7e5fb2ce288813306e4d4636395e047a3d28下找到SQLite文件。
4.如果计划使用Arctype打开此数据库，则需要复制并重命名扩展名为.db的文件，以表明它是SQLite文件。
开始使用SQLite
与大多数SQL Server不同，连接到SQLite数据库不需要连接字符串、主机或用户名。您只需将SQL客户端指向数据库文件。
对于Arctype
1.在连接下拉列表下，选择“添加新数据源”
2.选择“SQLite”
3.查找SQLite数据库文件。文件必须具有.sqlite3或.db扩展名，Arctype才能打开它。
更详细的说明可以在Arctype文档中找到。
使用命令行
在UNIX终端上，键入sqlite3[filename]。
iMessage模式
关于Arctype，我最喜欢的部分之一是分析数据库模式有多容易。我是命令行工具和老派编辑的长期用户，但有时有一个更直观的交互式工具是一个救星。让我们深入研究Apple为iMessage创建的模式。今天，我们将重点讨论聊天表、消息表和句柄表，以及一些连接相关记录的连接表。
请注意，我创建了一个名为handle2的自定义视图，其中添加了一个字段id2，该字段混淆了我个人联系人的电话号码和电子邮件地址，您将在本文的示例中看到此视图。
挖掘iMessage
让我们编写一些查询并进行一些观察，如果没有直接SQL访问，这是不可能的。
用老消息激起你的怀旧情绪
首先，让我们从一个简单的查询开始，以查看前50条消息。如果你的聊天线程可以追溯到很多年前，那么从你的iPhone或Mac上获取早期消息是不容易的。
两个平台上的界面都要求您一次向后滚动大约25条消息。这非常耗时，如果用户在向后滚动时向您发送新消息，可能会导致崩溃或重置。
幸运的是，我们有自定义SQL来帮助我们：
select
h.id2 as sender_name,
m.text as message_body
from
message m
join handle2 h on h.rowid = m.handle_id
order by
m.date
limit
50;
手柄id表示用户的可读标识符。它将是一个电话号码或电子邮件地址。
使用SQL为您的友谊评分
让我们使用SQL找出谁是我们最好的朋友。假设你将友谊的质量视为发送短信数量的函数，这应该是非常准确的！
首先，让我们将来自_ me的消息数除以不产生回复率的消息数。该查询显示了我们发送消息的前10位用户的总消息量以及回复率。
乘1.0转换为实数据类型，以避免整数除法，这将导致1或0而不是小数。您可以使用此处的链接查看SQLite中整数除法的规则。
select
h.id2,
count(1) as cnt,
round(
sum(
case
when m.is_from_me then 1
else 0
end
) * 1.0 / count(1) * 100.0,
2
)
from
message m
join handle2 h on h.rowid = m.handle_id
group by
h.id
order by
cnt desc
limit
10;
这种分析的一个问题是，发送的消息越少并不一定意味着发送的单词越少。让我们添加更多字段以获得更好的洞察力。
在这里，我们可以看到发送和接收的字符总数、发送和接收文本消息的平均长度、发送和收到字符的总比率以及回复比率。在我的例子中，我收到更多信息的人发送的信息也比我长。
select
h.id,
count(1) as cnt,
sum(length(m.text)) as chars,
sum(length(m.text)) filter (where m.is_from_me) as chars_sent,
sum(length(m.text)) filter (where not m.is_from_me) as chars_received,
round(avg(length(m.text)) filter (where m.is_from_me)) as avg_length_sent,
round(avg(length(m.text)) filter (where not m.is_from_me)) as avg_length_received,
round((sum(length(m.text)) filter (where m.is_from_me) * 1.0 / sum(length(m.text)) filter (where not m.is_from_me)), 2) as characters_sent_ratio,
round((count(1) filter (where m.is_from_me)) * 1.0 / (count(1) filter (where not m.is_from_me)), 2) as reply_ratio
from
message m
join handle h on h.rowid = m.handle_id
group by
h.id
order by
cnt desc
limit
10;
此查询大量使用聚合筛选器。通过指定WHERE子句过滤掉不需要的记录，聚合筛选器允许您仅对部分数据使用聚合函数。
检查iMessage反应
有两个较新的iMessage特性，在其模式设计的上下文中，其实现非常有趣。最近有消息称，Android手机将能够正确显示iMessage的“反应”。历史上，如果您向非苹果设备发送iMessage响应，它将显示为文本添加，而不是图标。
随着Android设备新兼容性的宣布，我很想了解该功能的当前实现是如何工作的。
我选择了一些有反应和无反应的记录，并比较了结果。我发现associated_message_type列通常设置为0，但在具有反应的消息中，它是2000-2005之间的整数值。我还注意到associatedMessage_guid存在。苹果的5种反应类型似乎使用2000-2005，3000-3005表示用户删除反应，3表示Apple Pay请求。
从这项调查来看，反应似乎是作为iMessage发送的，并附加了反应的文本等价物和与父消息的外键关系。这允许非苹果设备无缝地发送和接收消息。
如果消息是通过短信发送的，那么将反应链接到它引用的消息的元数据就会丢失。如果设备支持iMessage，Apple设备将忽略消息的文本部分，查找相关消息并添加适当的反应作为视觉覆盖。
请注意，消息表同时包含ROWID和guid。ROWID是一个典型的自动递增整数id字段，用于连接本地数据库。但是，对于跨设备的相同消息，自动递增的主键将不相同。GUID是全局唯一的，由邮件作者生成，并发送给所有收件人。这允许在不同的数据库、设备和用户之间进行外键引用。有关GUID实用程序的更多信息，请参阅本文。
查找您最受欢迎的群聊
群组聊天存储在聊天表中。Join和chat_message_Join表分别用于将用户和消息与群聊天相关联。下面是一个查询，用于查找最常用的群聊（与>1名成员聊天）及其参与者的身份。
select
group_concat(distinct h.id2) as participants,
count(m. "ROWID") as message_count
from
chat c
join chat_handle_join chj on chj.chat_id = c."ROWID"
join handle2 h on h. "ROWID" = chj.handle_id
join chat_message_join cmj on cmj.chat_id = c."ROWID"
join message m on m. "ROWID" = cmj.message_id
group by
c."ROWID"
having
count(distinct h.id) > 1
order by
message_count desc
limit
10
group_concat函数是一个聚合函数，它将字符串连接在一起，MySQL使用它的名字与string_agg相同，PostgreSQL用户也很熟悉。在这里查看有关如何在SQLite中使用它的更多信息。
HAVING子句类似于WHERE子句，但对聚合函数进行操作。如果您想编写一个以聚合为条件的查询，但无法在WHERE子句中进行查询，那么HAVING就是您的选择。
结论
SQLite是一个强大的工具，其跨设备的丰富应用和众多的用例使其成为世界上最令人印象深刻的软件项目之一。如果您对幕后的情况感到好奇，那么SQLite的源代码是众所周知的组织良好且有趣的（对我们中的一些人来说）。
iMessage只是众多依赖SQLite的软件之一，被数百万最终用户使用。您可以免费试用像Arctype这样的SQL客户端，并开始探索为您日常使用的工具提供动力的数据库！
原文标题：Analyzing iMessage With SQL
原文作者：Daniel Lifflander
原文链接：https://dzone.com/articles/analyzing-imessage-with-sql
