BirdTalk 聊天系统客户端数据库设计

飞鸟真人

于 2024-06-29 16:50:23 发布

阅读量979

点赞数 26

本文链接：https://blog.csdn.net/robinfoxnan/article/details/140066641

版权

即时通信专栏收录该内容

18 篇文章 0 订阅

订阅专栏

BirdTalk 聊天系统客户端数据库设计

考虑到客户端本地用户信息数据量不会太多，那么，可以使用范式来设计。

为了支持多用户登录，每个用户使用ID命名一个sqlite数据库的文件。

比如db10001.db。每个用户的数据都是分开的，切换用户时候，数据不会混杂在一起，效率高；

1.用户信息表 (users)

在聊天过程中涉及的所有用户的基本信息都使用这个表。

1.1 表结构

字段名	数据类型	约束	描述
`id`	INTEGER	PRIMARY KEY	用户唯一标识
`name`	TEXT		用户姓名
`nick`	TEXT		用户昵称
`age`	INTEGER		用户年龄
`gender`	TEXT		用户性别
`region`	TEXT		用户所在地区
`icon`	TEXT		用户头像路径或URL
`follows`	INTEGER		用户关注数
`fans`	INTEGER		用户粉丝数
`introduction`	TEXT		用户简介
`last_login_time`	TEXT		最后登录时间
`is_online`	INTEGER		是否在线
`key`	TEXT		用户自定义秘钥字符串
`crypt_type`	TEXT		算法
`mask`	INTEGER		给对方的权限

1.2 sql语句

CREATE TABLE
IF
	NOT EXISTS users (
		id INTEGER PRIMARY KEY,
		name TEXT,
		nick TEXT,
		age INTEGER,
		gender TEXT,
		region TEXT,
		icon TEXT,
		phone TEXT,
		email TEXT,
		follows INTEGER,
		fans INTEGER,
		introduction TEXT,
		last_login_time TEXT,
		is_online INTEGER,
		mask INTEGER,
		crypt_key TEXT,
		crypt_type TEXT mask INTEGER 
	);

2.关注表设计

2.1 表结构

字段名	数据类型	约束	描述
`id`	INTEGER	PRIMARY KEY	用户唯一标识
`nick1`	TEXT		用户昵称

String createFollowsTable = "CREATE TABLE IF NOT EXISTS follows ("
                + "id INTEGER PRIMARY KEY, "
                + "nick1 TEXT, "
                + ");";
                
CREATE TABLE IF NOT EXISTS follows (id INTEGER PRIMARY KEY, nick1 TEXT);

2.2 关注视图

CREATE VIEW IF NOT EXISTS user_follow_view AS
SELECT 
    follows.id AS id,
    follows.nick1 AS nick1,
    users.name AS name,
    users.nick AS nick,
    users.age AS age,
    users.gender AS gender,
    users.region AS region,
    users.icon AS icon,
    users.phone AS phone,
    users.email AS email,
    users.follows AS follows,
    users.fans AS fans,
    users.introduction AS introduction,
    users.last_login_time AS last_login_time,
    users.is_online AS is_online,
    users.crypt_key AS crypt_key,
    users.crypt_type AS crypt_type,
    users.mask AS mask
   
FROM follows LEFT JOIN users ON follows.id = users.id;

3. 粉丝表设计

3.1 表结构

字段名	数据类型	约束	描述
`id`	INTEGER	PRIMARY KEY	用户唯一标识
`nick2`	TEXT		用户昵称

String createFansTable = "CREATE TABLE IF NOT EXISTS fans ("
                + "id INTEGER PRIMARY KEY, "
                + "nick2 TEXT, "
                + ");";
                
CREATE TABLE IF NOT EXISTS fans (id INTEGER PRIMARY KEY, nick2 TEXT);

3.2 粉丝视图

CREATE VIEW IF NOT EXISTS user_fan_view AS
SELECT 
    fans.id AS id,
    fans.nick2 AS nick2,
    users.name AS name,
    users.nick AS nick,
    users.age AS age,
    users.gender AS gender,
    users.region AS region,
    users.icon AS icon,
    users.phone AS phone,
    users.email AS email,
    users.follows AS follows,
    users.fans AS fans,
    users.introduction AS introduction,
    users.last_login_time AS last_login_time,
    users.is_online AS is_online,
    users.crypt_key AS crypt_key,
    users.crypt_type AS crypt_type,
    users.mask AS mask
   
FROM fans LEFT JOIN users ON fans.id = users.id;

3.3 互相关注的视图

CREATE VIEW IF NOT EXISTS mutual_follows_view AS
SELECT 
    follows.id AS id,
    follows.nick1 AS nick1,
    fans.nick2 AS nick2,
    
    users.name AS name,
    users.nick AS nick,
    users.age AS age,
    users.gender AS gender,
    users.region AS region,
    users.icon AS icon,
    users.phone AS phone,
    users.email AS email,
    users.follows AS follows,
    users.fans AS fans,
    users.introduction AS introduction,
    users.last_login_time AS last_login_time,
    users.is_online AS is_online,
    users.crypt_key AS crypt_key,
    users.crypt_type AS crypt_type,
    users.mask AS mask
FROM 
    follows INNER JOIN fans ON follows.id = fans.id 
LEFT JOIN 
    users  ON  follows.id = users.id;

4. 所在群组的用户

4.1 表结构

字段名	数据类型	约束	描述
`id`	INTEGER	PRIMARY KEY	组ID
`nick2`	TEXT	PRIMARY KEY	用户id
`role`	INTEGER		角色

CREATE TABLE IF NOT EXISTS group_members (
    gid INTEGER NOT NULL,
    uid INTEGER NOT NULL,
    role INTEGER,
    nick3 TEXT,
    PRIMARY KEY (gid, uid)
);

4.2 群用户的视图

CREATE VIEW IF NOT EXISTS group_mem_view AS
SELECT 
    group_members.gid AS gid,
    group_members.uid AS uid,
    group_members.nick3 AS nick3,
    
    users.name AS name,
    users.nick AS nick,
    users.age AS age,
    users.gender AS gender,
    users.region AS region,
    users.icon AS icon,
    users.phone AS phone,
    users.email AS email,
    users.follows AS follows,
    users.fans AS fans,
    users.introduction AS introduction,
    users.last_login_time AS last_login_time,
    users.is_online AS is_online,
    users.crypt_key AS crypt_key,
    users.crypt_type AS crypt_type,
    users.mask AS mask
   
FROM group_members LEFT JOIN users ON group_members.uid = users.id;

5. 会话列表存储

5.1 在显示的会话

每个群组一个会话，只要加入了群组就是存在会话，登录后需要去同步数据；

每个双向好友都是一个会话，但是没有新消息的好友不一定有会话，为了界面整洁，也许已经删除了；

这里使用Topic这个概念来描述会话：

列	类型	约束	描述
tid	INTEGER	PRIMARY KEY	对方的 ID 或组 ID
sync_id	INTEGER		最后同步的最后一条消息 ID
read_id	INTEGER		已经读到的最后一条
type	INTEGER	0表示P2P，1表示群组，2频道	会话类型 (private/group)
visible	INTEGER	0表示私聊，1表示公开	群组有这个属性，公开或者不公开
title	TEXT	群聊可以重新设置名字	会话标题，通常是对方的名字或群组的名称
icon	TEXT	群聊可以设置图标

5.2 建表

CREATE TABLE IF NOT EXISTS topic (
    tid INTEGER PRIMARY KEY,
    sync_id INTEGER,
    read_id INTEGER,
    type INTEGER,
    visible INTEGER,
    title TEXT,
    icon TEXT
);

在群组中，通过同步 sync_id和read_id，可以知道是否有未读消息，这里并不能准确计算未读条数；因为ID 是雪花算法，不是唯一连续的；

在私聊中，所有的会话的消息在服务器端都是一个队列存储，也无法得知未读消息条数；而且对于私聊来说，这个字段没有意义；

所以，需要单独加1条记录用于表示私聊队列的同步位置：tid = 0;

6. 消息存储

6.1 群组聊天记录

群组以会话为核心，所以每个群组一个表，定期清理历史数据

表名为： gchat + [id]

比如群的号码为20001

则聊天消息的表为: gchat20001

6.1.1 结构描述

字段名	数据类型	备注
id	INTEGER	消息ID
tid	INTEGER	会话ID：私聊就是对方的uid，如果是群聊就是gid
uid	INTEGER	发数据的人，有可能是自己
send_id	INTEGER	收到的还是发出的（0: 发出, 1: 收到），这个字段为了处理更快
dev_id	TEXT	发出的设备的id
io	INTEGER	流入还是发出
msg_type	TEXT	消息类型（文本, 语音, 视频），如果是裸数据则需要依赖这个解码
data	BLOB	消息内容（字节流），如果是封装数据，则drafty格式解码
is_plain	INTEGER	是否已经解密（0: 否, 1: 是）
tm	TEXT	提交时间戳
tm1	TEXT	发送成功时间戳
tm2	TEXT	送达时间戳
tm3	TEXT	阅读时间戳
crypt_type	TEXT	加密类型
print	INTEGER	秘钥指纹，用于判断当前设置的秘钥能否解码

SQL 建表语句

CREATE TABLE IF NOT EXISTS gchat20001 (
    id INTEGER PRIMARY KEY,
    tid INTEGER,
    uid INTEGER,
    send_id INTEGER,
    dev_id TEXT,
    io INTEGER,
    msg_type TEXT,
    data BLOB,
    is_plain INTEGER,
    tm INTEGER,
    tm1 INTEGER,
    tm2 INTEGER,
    tm3 INTEGER,
    crypt_type TEXT,
    print INTEGER,
    status TEXT
);

6.2.2 同步问题与未读条目

对于群组，如果一个终端长时间未登录，那么其实是不知道有多少条未读数据的，可能会有几千条都不止，那么都加载过来也没有意义；

正确的做法肯定是倒序加载，那么是否还需要本地存储数据？

1）登录时候优先倒序加载，并且带上最后同步的位置，但是需要在最后一条的状态中加一个标记 forward_loading,表示正在从此位置加载；

id	tm	tid	…	status
…
1100000		201		forward_loading

2）如果倒序加载返回数据少于限制数量（100）,并且最早一条应该等于改标记条目；表示缺失的部分加载完毕，该条目状态改为loaded;

id	tid	status
…
1100000	201	loaded
…
1200000

3）无论加载完毕与否，同步点都更新一下；（Topic表中）

4）如果没有加载完，根据用户需要继续加载，收到的100条的最早的一条也需要对status字段标记，backward_loading

id	tid	status
…
1100000	201	forward_loading
1200001		backward_loading
…
1300000

如果用户继续向前继续加载的时候，发现backward_loading条目，就应该从服务器反向加载；

未读的条目其实是不一定能精确计算的，只能提示有新消息；

没有服务器回执的消息，放到内存中等待服务器回执，并且status=“sending”,如果失败了，则设置为"fail",如果服务器给回执了，那么就是status=“”, 群聊不等待接收回执和读回执。因为计算已读人数计算量比较大；

6.2 私聊的聊天记录

6.2.1 同步流程与表结构

登录后，首先按照上次的同步ID来同步数据，收到的数据写表：

1.1）所有消息都存储在一个统一的大表中，各个会话还是通过表关联来获取数据；

也就是说，收到新的消息后每条都先写入消息表，同时写入未读表；

表名为： pchat，结构与群聊的结构是一样的。

CREATE TABLE IF NOT EXISTS pchat (
    id INTEGER PRIMARY KEY,
    tid INTEGER,
    uid INTEGER,
    send_id INTEGER,
    dev_id TEXT,
    io INTEGER,
    msg_type TEXT,
    data BLOB,
    is_plain INTEGER,
    tm INTEGER,
    tm1 INTEGER,
    tm2 INTEGER,
    tm3 INTEGER,
    crypt_type TEXT,
    print INTEGER，
    status TEXT,
);

而上次最后同步的消息ID，就是主键的最后一条。

SELECT id FROM pchat ORDER BY id DESC LIMIT 1;

这种方法利用了主键索引的优势，ORDER BY id DESC 会按照主键字段 id 的倒序排序，然后 LIMIT 1 只返回第一条结果，即最大的消息ID。对于主键索引，数据库系统通常会有优化，因此这种方式可能是高效的。

但是，如果用户清空历史数据后，表就空了，这个时候就无法同步数据了，所以还是需要有个位置来记录同步数据，那么选择会话列表中存储：设置一条TID=0的单独的记录；

会话分表存放，启动加载的时候：除了未读数据以外，向前加载1天的数据，并添加到各个会话中；

如果用户拉动聊天框，则继续加载前面的部分，直接从会话表中查找。

各个会话表名字为：pchat + [id]

比如与好友10002的会话的记录，收到数据后，将写到pchat10002表中，

表结构如下，只有1列：

列	类型	约束	描述
id	INTEGER	PRIMARY KEY	消息ID

查询语句得进行拼接，示例从某个位置加载20条数据的sql如下：

SELECT 
    pchat.*
   
FROM pchat10002 LEFT JOIN pchat ON pchat10002.id = pchat.id where pchat10002.id<=100002 limit 20;

如果没有数据的时候，就尝试网络加载；

6.2.2 未读数据表

1.2）所有未读的消息都应该放到一个临时表中，方便了解用户了解有多少未读的消息；

未读的临时表为:

列	类型	约束	描述
id	INTEGER	PRIMARY KEY	消息ID
uid	INTEGER		会话对方的ID
io	INTEGER	1表示收到的，0表示发出的；	收或者发

建表语句：

CREATE TABLE IF NOT EXISTS pchat_unread (
    tid INTEGER PRIMARY KEY,
    uid INTEGER,
    io  INTEGER
    );

统计各个会话的未读的消息数目为：

SELECT uid, COUNT(*) AS count
FROM pchat_unread
GROUP BY uid where io = 1;

用户读过以后，提交已读回执，然后从未读表中清除。

那么为啥要单独加一个未读表，而不是在数据中加一个未读标记来统计？

答案是：这个临时表数据很少，可以很快！！

登录后，可以从topic表中知道目前需要绘制多少个私聊的会话。或者说于此同时，

同步结束后，可以得知是否有需要新增的私聊会话，每个会话有多少未读消息；
对于发出的私聊消息，写入pchat, 写入pchat[id]，也需要写入未读表pchat_unread，这里表示没有收到对方的接收回执的消息，如果超时则需要查询回执，防止上下线抖动造成的回执丢失。（回执包本身没有确认到达机制）

7. 键值对表

有些东西没有地方存储，比如一些状态数据

列	类型	约束	描述
l	TEXT	PRIMARY KEY	键值
f	TEXT		字段
v	TEXT		值

这个东西就类似于redis中的hash存储；

要创建一个类似于 Redis 中的哈希表结构的 SQLite 表，包含主键 k，字段名 f，值 v，且都是 TEXT 类型，可以使用以下 SQL 语句：

7.1 建表

CREATE TABLE IF NOT EXISTS kv_table (
    k TEXT,
    f TEXT,
    v TEXT,
    PRIMARY KEY (k, f)
);

解释：

k TEXT：表示键 (key)。
f TEXT：表示字段名 (field)。
v TEXT：表示值 (value)。
PRIMARY KEY (k, f)：定义复合主键，确保 k 和 f 的组合在表中是唯一的。

示例

假设表名为 kv_table，可以插入以下数据：

INSERT INTO kv_table (k, f, v) VALUES ('user:1', 'name', 'Alice');
INSERT INTO kv_table (k, f, v) VALUES ('user:1', 'age', '30');
INSERT INTO kv_table (k, f, v) VALUES ('user:2', 'name', 'Bob');
INSERT INTO kv_table (k, f, v) VALUES ('user:2', 'age', '25');

表 kv_table 中的数据如下：

k	f	v
user:1	name	Alice
user:1	age	30
user:2	name	Bob
user:2	age	25

7.2 使用示例

查询某个键的所有字段和值

可以使用如下 SQL 语句查询：

SELECT f, v FROM kv_table WHERE k = 'user:1';

结果如下：

f	v
name	Alice
age	30

更新某个键的某个字段的值

可以使用如下 SQL 语句更新：

UPDATE kv_table
SET v = '31'
WHERE k = 'user:1' AND f = 'age';

删除某个键的某个字段

可以使用如下 SQL 语句删除：

DELETE FROM kv_table
WHERE k = 'user:1' AND f = 'age';

这样，你就可以通过 SQL 操作在 SQLite 中实现类似 Redis 哈希表的功能。

8. 登录账户表

只需要最基本的登录信息，因为其他的信息从服务端同步：

默认的自动登录的账户在xml文件中描述，如果自动登录，则自动加载对应的数据库文件，读取相关信息；

字段名	数据类型	约束	描述
`id`	INTEGER	PRIMARY KEY	用户唯一标识
`nick`	TEXT		用户昵称
`icon`	TEXT		用户头像路径或URL
`pwd`	INTEGER		用户关注数
`email`	INTEGER		用户粉丝数
`phone`	TEXT		用户简介
`print`	INTEGER		是否在线
`key`	TEXT		用户自定义秘钥字符串
`crypt_type`	TEXT		算法

CREATE TABLE account (
    id INTEGER PRIMARY KEY,
    nick TEXT,
    icon TEXT,
    pwd INTEGER,
    email INTEGER,
    phone TEXT,
    print INTEGER,
    key TEXT,
    crypt_type TEXT
);