flutter sqflite数据库私聊设计及应用
2022-01-26 本文已影响0人
简单coder
我在flutter中sqflite基本使用与注意点已经讲过sqflite的基本注意点与使用方法,这里不再讲api,只是单纯的从应用设计层面讲下对于自建页面私聊页面数据库表结构设计等等
当前环境
# flutter
sdk: ">=2.12.0 <3.0.0"
#数据库
sqflite: ^2.0.1
初始化数据库
初始化数据库用于在用户登录后, 创建或打开以 userId+环境隔离的数据库,我目前只做了 userId 隔离,环境隔离主要是为了防止统一机子测试环境正式环境用户 id 重复等问题,大家自己建库时注意一下即可.
iOS数据库位置位于 documents 下,我打印了其地址,并用DB Browser For Sqlite打开,
/Users/suyikun/Library/Developer/CoreSimulator/Devices/0FE2E5FD-68AA-475D-841C-9D1DCE490F95/data/Containers/Data/Application/857BE1B8-DA17-4819-97F9-CBFDBC4CF451/Documents/201413533.db
表字段设计
私聊页面需要两张表 会话表chat_session, 消息表chat_message
会话表模型
@JsonSerializable()
class ChatSession {
String? sessionId;
int? sort;
int? status;
int? unreads;
int? sessionType;
int? sayhi;
ChatSessionExt? ext;
int? updateTimestamp;
ChatSession({
this.sessionId,
this.sort,
this.status,
this.unreads,
this.sessionType,
this.sayhi,
this.ext,
this.updateTimestamp,
});
static int getSort(sessionId) {
if (sessionId == ChatSystemSession.official.sessionId) {
return ChatSystemSession.official.sort ?? 0;
} else if (sessionId == ChatSystemSession.friendRequest.sessionId) {
return ChatSystemSession.friendRequest.sort ?? 0;
} else {
return ChatSystemSession.normal.sort ?? 0;
}
}
factory ChatSession.fromJson(Map<String, dynamic> json) {
return ChatSession(
sessionId: json['sessionId'] as String?,
sort: json['sort'] as int?,
status: json['status'] as int?,
unreads: json['unreads'] as int?,
sessionType: json['sessionType'] as int?,
sayhi: json['sayhi'] as int?,
ext: json['ext'] == null
? null
: ChatSessionExt.fromJson(JSON.json.decode(json['ext'])),
updateTimestamp: json['updateTimestamp'] as int?,
);
}
factory ChatSession.fromMessage(ChatMessage message) {
return ChatSession(
sessionId: message.sessionId,
sort: getSort(message.sessionId),
status: 0,
unreads: 0,
sessionType: 0,
sayhi: 0,
ext: ChatSessionExt(
nickname: message.isSender ? message.ext?.receiverNickname : message.ext?.sendNickname,
headUrl: message.isSender ? message.ext?.receiverHeadUrl : message.ext?.senderHeadUrl,
),
updateTimestamp: DateTime.now().microsecondsSinceEpoch,
);
}
Map<String, dynamic> toJson() {
return <String, dynamic>{
'sessionId': this.sessionId,
'sort': this.sort,
'status': this.status,
'unreads': this.unreads,
'sessionType': this.sessionType,
'sayhi': this.sayhi,
'ext': JSON.json.encode(this.ext),
'updateTimestamp': this.updateTimestamp,
};
}
@override
bool operator ==(Object other) {
if (identical(other, this)) return true;
if (other is! ChatSession) return false;
final mapEquals = const DeepCollectionEquality().equals;
return mapEquals(other.toJson(), toJson());
}
@override
int get hashCode =>
sessionId.hashCode ^
sort.hashCode ^
status.hashCode ^
unreads.hashCode ^
sessionType.hashCode ^
sayhi.hashCode ^
ext.hashCode ^
updateTimestamp.hashCode;
}
会话表
注意会话表ext字段的解析
toJSON
'ext': JSON.json.encode(this.ext),
fromJSON
ext: json['ext'] == null
? null
: ChatSessionExt.fromJson(JSON.json.decode(json['ext'])),
updateTimestamp: json['updateTimestamp'] as int?,
我们会话和消息两表的数据记录ext 业务数据永远都是 TEXT 存储.
session表浏览数据时会看到一条默认数据
这类似与系统消息,一般来说,会话表设计肯定是带有一个系统消息置顶等,或者好友请求等会话置顶,后续的私聊消息再进行时间降序排序,所以在查询时,应先增加一个 sort键, 然后以 timeStamp 排序.
在 onCreate回调中,先执行完创表,然后再增加系统会话
final systemSession = ChatSession(
sessionId: "99999",
sort: 9999,
status: 0,
unreads: 0,
sessionType: 0,
sayhi: 0,
updateTimestamp: 0,
ext: ChatSessionExt(
nickname: "洞主",
headUrl: "/user/100135092_b0c5fdee054b486292f7c5b8a781d1d4.jpg"));
db.insert(_chatSession, systemSession.toJson());
查询时:
Future<List<ChatSession>> queryAllSession() async {
if (_db == null) {
return Future.value([]);
}
List<Map<String, dynamic>> list = await _db!.rawQuery(
"select * from $_chatSession order by sort desc,updateTimestamp desc");
final sessions =
list.compactMap((element) => ChatSession.fromJson(element));
return sessions;
}
消息表
@JsonSerializable()
class ChatMessage {
String? sessionId;
String? msgId;
int? type;
int? timeStamp;
String? content;
String? sender;
String? recipient;
ChatMessageExt? ext;
bool get isSender => sender == UserStore.current?.userId?.toString();
ChatMessage({
this.msgId,
this.type,
this.timeStamp,
this.content,
this.sender,
this.recipient,
this.ext,
});
factory ChatMessage.fromJson(Map<String, dynamic> json) {
final message = ChatMessage(
msgId: json['msgId'] as String?,
type: json['type'] as int?,
timeStamp: json['timeStamp'] as int?,
content: json['content'] as String?,
sender: json['sender'] as String?,
recipient: json['recipient'] as String?,
ext: json['ext'] == null
? null
: ChatMessageExt.fromJson(JSON.json.decode(json['ext'])),
);
if (message.sender == '9999') {// 系统消息
message.sessionId = '9999';
} else {
message.sessionId = message.sender;
}
return message;
}
Map<String, dynamic> toJson() {
return <String, dynamic>{
'sessionId': this.sessionId,
'msgId': this.msgId,
'type': this.type,
'timeStamp': this.timeStamp,
'content': this.content,
'sender': this.sender,
'recipient': this.recipient,
'ext': JSON.json.encode(this.ext),
};
}
}
发送消息时, 先判断会话有没有创建,没有就创建会话,然后再执行插入消息数据, 从im服务器收到消息也是同理,先判断会话,再插入消息.
//MARK: ------------------------message------------------------
Future<int> insertMessage(ChatMessage message,
{bool createSessionIfNeeded = true}) async {
if (_db == null) {
return Future.value(0);
}
var sessionId = message.sessionId;
if (sessionId == null || sessionId.isEmpty) {
return Future.value(0);
}
final session = await querySession(sessionId);
if (session == null) {
// 创建 session
var session = ChatSession.fromMessage(message);
int res = await insertSession(session);
if (res == 0) {
print("___创建session失败");
return 0;
}
}
int result = await _db!.insert(_chatMessage, message.toJson());
return result;
}
查询消息这块,建议是时间降序查找,然后在列表展示时 reversed()一下,因为我们肯定是要求是滚动到底部,这样在做分页时sql 写起来比较简单,拿到数组first 然后 timestamp<${time} limit 20,反正就是这意思,大家想想就知道了.
这里给大家展示一下我稍微写的私聊页面,这里不涉及到 im 部分,仅仅是 kit 的搭建.
数据库消息入库
会话表
消息表
sqlflite 总结:
优点:
使用十分方便,API十分简单,pub.dev 上星星很高,受众人数多
缺点:
需要手写 sql.
如果是真正的实际业务中,它还有一个致命的缺陷,那就是升级维护十分的麻烦,目前我还没有找到一个比较完整的 diff 算法,去针对数据库升级,我在上一篇所说的这个逻辑是十分的不好的
这个每个版本的比较是下下下策,很遗憾 sqflite 的升级算法不够,而我自己又写不出比较完美的表结构差异比较升级的算法,所以后面实际业务中,我大概率是不会使用该数据库,而是转向市场上比较优秀的对象型数据库,比如 realm, objectbox 等, 它们是支持数据库自动升级的,而且他们都是有支持 flutter 版本的,如果有时间,我会将目前的db_helper底层改成该数据库.
2022-01-26 15:45:53更新贴一下 db_helper 的简单代码吧,后面大概率不会使用 sqflite 了
class DBHelper extends GetxService {
static DBHelper get to => Get.find();
final _chatSession = "chat_session";
final _chatMessage = "chat_message";
Database? _db;
Future<DBHelper> init() async {
if (UserStore.to.isLogin) {
await open();
}
return this;
}
@override
void onReady() {
super.onReady();
}
open() async {
if (!UserStore.to.isLogin) {
return;
}
if (_db != null) {
if (_db?.path.contains("${UserStore.current?.userId ?? 0}") ?? false) {
// 如果_db 不为空,且与当前用户一致
return;
} else {
_db?.close();
}
}
final sqlFileName = "${UserStore.to.userId}.db";
final path = "${await getDatabasesPath()}/$sqlFileName";
print("~~~sql:$path");
_db = await openDatabase(path, version: 3,
onCreate: (Database db, int version) {
db.execute("""
CREATE TABLE If NOT EXISTS $_chatSession (
"sessionId" TEXT NOT NULL UNIQUE,
"sort" INTEGER NOT NULL,
"status" INTEGER NOT NULL DEFAULT 0,
"unreads" INTEGER NOT NULL DEFAULT 0,
"sessionType" INTEGER NOT NULL DEFAULT 0,
"sayhi" INTEGER NOT NULL DEFAULT 0,
"ext" TEXT,
"updateTimestamp" INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY("sessionId")
);
""");
db.execute("""
CREATE TABLE If NOT EXISTS $_chatMessage (
"sessionId" TEXT NOT NULL,
"msgId" TEXT NOT NULL,
"type" INTEGER NOT NULL,
"timeStamp" INTEGER NOT NULL DEFAULT 0,
"content" TEXT NOT NULL DEFAULT '',
"sender" TEXT,
"recipient" TEXT,
"ext" TEXT,
PRIMARY KEY("msgId")
);
""");
final systemSession = ChatSession(
sessionId: "99999",
sort: 9999,
status: 0,
unreads: 0,
sessionType: 0,
sayhi: 0,
updateTimestamp: 0,
ext: ChatSessionExt(
nickname: "洞主",
headUrl: "/user/100135092_b0c5fdee054b486292f7c5b8a781d1d4.jpg"));
db.insert(_chatSession, systemSession.toJson());
}, onUpgrade: (Database db, int oldVersion, int newVersion) {
try {
var batch = db.batch();
// if (oldVersion < 2) {
// batch.execute("""
// alter table $_chatMessage add column priceId not null default 0;
// """);
// }
// sqlite 不支持直接删除表结构字段, 建议业务结构数据全部存储与一个 text 的字段中.数据库字段仅做少量修改
// if (oldVersion < 3) {
// batch.execute("""
// alter table $_chatMessage delete column priceId;
// """);
// }
// if (oldVersion < 4) {
// batch.execute("""
// alter table $_chatMessage add column type not null default 0;
// """);
// }
batch.commit();
} catch (e) {
log(e.toString());
}
});
}
//MARK: ------------------------session------------------------
Future<int> insertSession(ChatSession? session) async {
if (_db == null) {
return Future.value(0);
}
if (session == null) {
return Future.value(0);
}
int result = await _db!.insert(_chatSession, session.toJson());
return result;
}
Future<List<ChatSession>> queryAllSession() async {
if (_db == null) {
return Future.value([]);
}
List<Map<String, dynamic>> list = await _db!.rawQuery(
"select * from $_chatSession order by sort desc,updateTimestamp desc");
final sessions =
list.compactMap((element) => ChatSession.fromJson(element));
return sessions;
}
Future<ChatSession?> querySession(String sessionId) async {
if (_db == null) {
return null;
}
if (sessionId.length == 0) {
return null;
}
List<Map<String, dynamic>> lists = await _db!.rawQuery(
"select * from $_chatSession where sessionId = $sessionId limit 1");
if (lists.length > 0) {
return ChatSession.fromJson(lists.first);
} else {
return null;
}
}
//MARK: ------------------------message------------------------
Future<int> insertMessage(ChatMessage message,
{bool createSessionIfNeeded = true}) async {
if (_db == null) {
return Future.value(0);
}
var sessionId = message.sessionId;
if (sessionId == null || sessionId.isEmpty) {
return Future.value(0);
}
final session = await querySession(sessionId);
if (session == null) {
// 创建 session
var session = ChatSession.fromMessage(message);
int res = await insertSession(session);
if (res == 0) {
print("___创建session失败");
return 0;
}
}
int result = await _db!.insert(_chatMessage, message.toJson());
return result;
}
Future<List<ChatMessage>> queryAllMessages(String sessionId) async {
if (_db == null) {
return Future.value([]);
}
List<Map<String, dynamic>> list = await _db!.rawQuery(
"select * from $_chatMessage where sessionId = $sessionId order by timeStamp asc");
var messages = list.compactMap((element) => ChatMessage.fromJson(element));
return messages;
}
}
