chat-flutter/lib/tools/tools_sqlite.dart

import 'dart:convert';
import 'package:demo/config/app_config.dart';
import 'package:demo/tools/tools_enum.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart' as path_;

// 数据库操作
class ToolsSqlite {
  ToolsSqlite._();
  static ToolsSqlite? _singleton;
  factory ToolsSqlite() => _singleton ??= ToolsSqlite._();

  Database? _database;

  // 初始化
  Future<Database> _initDB() async {
    String databasesPath = await getDatabasesPath();
    return await openDatabase(
      path_.join(databasesPath, AppConfig.dbName),
      version: 1,
      onCreate: (Database db, int version) async {
        var batch = db.batch();
        // 创建消息表
        await db.execute(
            'CREATE TABLE chat_msg (chatId TEXT, nickname TEXT, portrait TEXT, msgId TEXT, msgType TEXT, content TEXT, chatTalk TEXT, createTime TEXT, PRIMARY KEY(chatId))');
        // 创建历史表
        await db.execute(
            'CREATE TABLE chat_his (msgId TEXT, chatId TEXT, portrait TEXT, nickname TEXT, source TEXT, msgType TEXT, content TEXT, chatTalk TEXT, createTime TEXT, PRIMARY KEY(msgId))');
        // 创建好友表
        await db.execute(
            'CREATE TABLE chat_friend (userId TEXT, portrait TEXT, nickname TEXT, userNo TEXT, remark TEXT, intro TEXT, top TEXT, disturb TEXT, black TEXT, PRIMARY KEY(userId))');
        // 创建群聊表
        await db.execute(
            'CREATE TABLE chat_group (groupId TEXT, groupName TEXT, groupNo TEXT, portrait TEXT, notice TEXT, memberTop TEXT, memberDisturb TEXT, PRIMARY KEY(groupId))');
        batch.commit();
      },
      onUpgrade: (Database db, int v1, int v2) async {
        if (v1 == v2) {
          return;
        }
        // 开启事物
        var batch = db.batch();
        // 增量脚本
        // 提交事物
        batch.commit();
      },
    );
  }

  // 获取数据源
  Future<Database> get database async {
    if (_database == null || !_database!.isOpen) {
      _database = await _initDB();
    }
    return _database!;
  }

  // 聊天消息
  get msg {
    return _ChatMsgHander();
  }

  // 历史消息
  get his {
    return _ChatHisHander();
  }

  // 好友列表
  get friend {
    return _ChatFriendHander();
  }

  // 群聊列表
  get group {
    return _ChatGroupHander();
  }

  // 扩展处理
  get extend {
    return _ChatExtendHander();
  }
}

// 聊天消息
class _ChatMsgHander {
  // 聊天消息表
  static String tableName = 'chat_msg';
  // 写入消息
  add(ChatMsg chatMsg) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 新增
    await db.insert(
      tableName,
      chatMsg.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // 删除消息
  delete(String chatId) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 删除
    await db.delete(
      tableName,
      where: 'chatId = ?',
      whereArgs: [chatId],
    );
  }

  // 更新详情
  update(String chatId, Map<String, Object?> values) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 更新
    await db.update(
      tableName,
      values,
      where: 'chatId = ?',
      whereArgs: [chatId],
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  // 获取全部
  Future<List<ChatMsg>> getList() async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 执行
    List<Map<String, dynamic>> dataList = await db.query(
      tableName,
      columns: [
        'chatId',
        'nickname',
        'portrait',
        'msgId',
        'msgType',
        'content',
        'chatTalk',
        'createTime',
      ],
      orderBy: 'createTime',
    );
    if (dataList.isEmpty) {
      return [];
    }
    return dataList.map((data) => ChatMsg.fromJson(data)).toList();
  }
}

// 消息表
class ChatMsg {
  // 聊天对象
  String chatId;
  // 昵称
  String nickname;
  // 头像
  String portrait;
  // 消息id
  String msgId;
  // 消息类型
  MsgType msgType;
  // 消息内容
  Map<String, dynamic> content;
  // 聊天对象类型
  ChatTalk chatTalk;
  // 置顶
  bool top;
  // 静默
  bool disturb;
  // 创建时间
  DateTime createTime;

  ChatMsg(
    this.chatId,
    this.nickname,
    this.portrait,
    this.msgId,
    this.msgType,
    this.content,
    this.chatTalk,
    this.createTime, {
    this.top = false,
    this.disturb = false,
  });

  factory ChatMsg.fromJson(Map<String, dynamic> data) {
    return ChatMsg(
      data['chatId'],
      data['nickname'],
      data['portrait'],
      data['msgId'],
      MsgType.init(data['msgType']),
      jsonDecode(data['content']),
      ChatTalk.init(data['chatTalk']),
      DateTime.fromMillisecondsSinceEpoch(int.parse(data['createTime'])),
    );
  }

  factory ChatMsg.fromChatHis(ChatHis chatHis) {
    return ChatMsg(
      chatHis.chatId,
      chatHis.nickname,
      chatHis.portrait,
      chatHis.msgId,
      chatHis.msgType,
      chatHis.content,
      chatHis.chatTalk,
      chatHis.createTime,
    );
  }

  Map<String, dynamic> toJson() => {
        'chatId': chatId,
        'nickname': nickname,
        'portrait': portrait,
        'msgId': msgId,
        'content': jsonEncode(content),
        'chatTalk': chatTalk.value,
        'createTime': createTime.millisecondsSinceEpoch.toString(),
        'msgType': msgType.value,
      };
}

// 历史处理
class _ChatHisHander {
  static String tableName = 'chat_his';

  // 新增消息
  add(ChatHis chatHis) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 新增
    await db.insert(
      tableName,
      chatHis.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // 更新详情
  update(String chatId, Map<String, Object?> values) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 更新
    await db.update(
      tableName,
      values,
      where: 'chatId = ?',
      whereArgs: [chatId],
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  // 分页查询
  Future<List<ChatHis>> getPager(String chatId, int page) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 查询
    List<Map<String, dynamic>> dataList = await db.query(
      tableName,
      where: 'chatId = ?',
      whereArgs: [chatId],
      orderBy: 'msgId desc',
      limit: 50,
      offset: (page - 1) * 50,
    );
    if (dataList.isEmpty) {
      return [];
    }
    return dataList.map((data) => ChatHis.fromJson(data)).toList();
  }
}

class ChatHis {
  // 消息id
  String msgId;
  // 对象id
  String chatId;
  // 对象头像
  String portrait;
  // 对象昵称
  String nickname;
  // 消息来源
  Map<String, dynamic> source;
  // 消息类型
  MsgType msgType;
  // 消息内容
  Map<String, dynamic> content;
  // 聊天对象类型
  ChatTalk chatTalk;
  // 创建时间
  DateTime createTime;

  ChatHis(
    this.msgId,
    this.chatId,
    this.portrait,
    this.nickname,
    this.source,
    this.msgType,
    this.content,
    this.chatTalk,
    this.createTime,
  );

  factory ChatHis.fromJson(Map<String, dynamic> data) {
    Map<String, dynamic> source = jsonDecode(data['source']);
    return ChatHis(
      data['msgId'],
      data['chatId'],
      data['portrait'],
      data['nickname'],
      source,
      MsgType.init(data['msgType']),
      jsonDecode(data['content']),
      ChatTalk.init(data['chatTalk']),
      DateTime.fromMillisecondsSinceEpoch(int.parse(data['createTime'])),
    );
  }

  Map<String, dynamic> toJson() => {
        'msgId': msgId,
        'chatId': chatId,
        'source': jsonEncode(source),
        'portrait': portrait,
        'nickname': nickname,
        'content': jsonEncode(content),
        'createTime': createTime.millisecondsSinceEpoch.toString(),
        'msgType': msgType.value,
        'chatTalk': chatTalk.value,
      };
}

// 好友表
class _ChatFriendHander {
  static String tableName = 'chat_friend';

  // 新增好友
  add(ChatFriend chatFriend) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 新增
    await db.insert(
      tableName,
      chatFriend.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // 通过ID查询
  Future<ChatFriend?> getById(String userId) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 查询
    List<Map<String, dynamic>> dataList = await db.query(
      tableName,
      where: 'userId = ?',
      whereArgs: [userId],
      limit: 1,
    );
    if (dataList.isEmpty) {
      return null;
    }
    return ChatFriend.fromJson(dataList.first);
  }

  // 更新信息
  update(String userId, Map<String, Object?> values) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 更新
    await db.update(
      tableName,
      values,
      where: 'userId = ?',
      whereArgs: [userId],
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  // 批量新增
  addBatch(List<ChatFriend> dataList) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 批量
    Batch batch = db.batch();
    // 删除
    batch.delete(
      tableName,
    );
    // 添加
    for (var data in dataList) {
      batch.insert(
        tableName,
        data.toJson(),
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }
    // 提交
    await batch.commit();
  }

  // 列表查询
  Future<List<ChatFriend>> getList() async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 查询
    List<Map<String, dynamic>> resultList = await db.query(
      tableName,
    );
    List<ChatFriend> dataList = [];
    if (resultList.isEmpty) {
      return dataList;
    }
    for (var data in resultList) {
      dataList.add(ChatFriend.fromJson(data));
    }
    return dataList;
  }

  // 删除
  delete(String userId) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 删除
    await db.delete(
      tableName,
      where: 'userId = ?',
      whereArgs: [userId],
    );
  }
}

class ChatFriend {
  // id
  String userId;
  // 头像
  String portrait;
  // 昵称
  String nickname;
  // no
  String userNo;
  // 备注
  String remark;
  // 签名
  String intro;
  // 置顶
  String top;
  // 静默
  String disturb;
  // 黑名单
  String black;

  ChatFriend(
    this.userId,
    this.portrait,
    this.nickname,
    this.userNo,
    this.remark,
    this.intro,
    this.top,
    this.disturb,
    this.black,
  );

  factory ChatFriend.fromJson(Map<String, dynamic>? data) {
    return ChatFriend(
      data?['userId'] ?? '',
      data?['portrait'] ?? '',
      data?['nickname'] ?? '',
      data?['userNo'] ?? '',
      data?['remark'] ?? '',
      data?['intro'] ?? '',
      data?['top'] ?? '',
      data?['disturb'] ?? '',
      data?['black'] ?? '',
    );
  }

  factory ChatFriend.init() {
    return ChatFriend.fromJson({});
  }

  Map<String, dynamic> toJson() => {
        'userId': userId,
        'portrait': portrait,
        'nickname': nickname,
        'userNo': userNo,
        'remark': remark,
        'intro': intro,
        'top': top,
        'disturb': disturb,
        'black': black,
      };
}

// 群聊表
class _ChatGroupHander {
  static String tableName = 'chat_group';

  // 新增群聊
  add(ChatGroup chatGroup) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 添加
    await db.insert(
      tableName,
      chatGroup.toJson(),
      conflictAlgorithm: ConflictAlgorithm.replace,
    );
  }

  // 列表查询
  Future<List<ChatGroup>> getList() async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 查询
    List<Map<String, dynamic>> resultList = await db.query(
      tableName,
    );
    List<ChatGroup> dataList = [];
    if (resultList.isEmpty) {
      return dataList;
    }
    for (var data in resultList) {
      dataList.add(ChatGroup.fromJson(data));
    }
    return dataList;
  }

  // 更新信息
  update(String groupId, Map<String, Object?> values) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 更新
    await db.update(
      tableName,
      values,
      where: 'groupId = ?',
      whereArgs: [groupId],
      conflictAlgorithm: ConflictAlgorithm.ignore,
    );
  }

  // 通过ID查询
  Future<ChatGroup?> getById(String groupId) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 查询
    List<Map<String, dynamic>> dataList = await db.query(
      tableName,
      where: 'groupId = ?',
      whereArgs: [groupId],
      limit: 1,
    );
    if (dataList.isEmpty) {
      return null;
    }
    return ChatGroup.fromJson(dataList.first);
  }

  // 批量新增
  addBatch(List<ChatGroup> dataList) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 批量
    Batch batch = db.batch();
    // 删除
    batch.delete(
      tableName,
    );
    // 添加
    for (var data in dataList) {
      batch.insert(
        tableName,
        data.toJson(),
        conflictAlgorithm: ConflictAlgorithm.replace,
      );
    }
    // 提交
    await batch.commit();
  }

  // 删除
  delete(String groupId) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 删除
    await db.delete(
      tableName,
      where: 'groupId = ?',
      whereArgs: [groupId],
    );
  }
}

class ChatGroup {
  // 群聊ID
  String groupId;
  // 群聊名称
  String groupName;
  // 群聊编号
  String groupNo;
  // 群聊头像
  String portrait;
  // 通知公告
  String notice;
  // 置顶
  String memberTop;
  // 静默
  String memberDisturb;

  ChatGroup(
    this.groupId,
    this.groupName,
    this.groupNo,
    this.portrait,
    this.notice,
    this.memberTop,
    this.memberDisturb,
  );

  factory ChatGroup.fromJson(Map<String, dynamic>? data) {
    return ChatGroup(
      data?['groupId'] ?? '',
      data?['groupName'] ?? '',
      data?['groupNo'] ?? '',
      data?['portrait'] ?? '',
      data?['notice'] ?? '',
      data?['memberTop'] ?? '',
      data?['memberDisturb'] ?? '',
    );
  }

  factory ChatGroup.init() {
    return ChatGroup.fromJson({});
  }

  Map<String, dynamic> toJson() => {
        'groupId': groupId,
        'groupName': groupName,
        'groupNo': groupNo,
        'portrait': portrait,
        'notice': notice,
        "memberTop": memberTop,
        "memberDisturb": memberDisturb,
      };
}

class _ChatExtendHander {
  static String tableHis = 'chat_his';
  static String tableMsg = 'chat_msg';

  // 清空消息
  clearMsg(String chatId, {bool delete = true}) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 批量
    Batch batch = db.batch();
    // 删除
    batch.delete(
      tableHis,
      where: 'chatId = ?',
      whereArgs: [chatId],
    );
    // 删除
    if (delete) {
      batch.delete(
        tableMsg,
        where: 'chatId = ?',
        whereArgs: [chatId],
      );
    }
    // 更新
    else {
      batch.update(
        tableMsg,
        {
          'msgType': MsgType.text.value,
          'content': jsonEncode({'data': ''}),
        },
        where: 'chatId = ?',
        whereArgs: [chatId],
      );
    }
    // 提交
    await batch.commit();
  }

  // 删除消息
  deleteMsg(String chatId, List<String> messageList) async {
    // 连接
    Database db = await ToolsSqlite().database;
    // 批量
    Batch batch = db.batch();
    // 删除
    for (var msgId in messageList) {
      batch.delete(
        tableHis,
        where: 'msgId = ?',
        whereArgs: [msgId],
      );
    }
    await batch.commit();
    // 刷新消息
    await _refreshMsg([chatId]);
  }

  // 刷新消息
  _refreshMsg(List<String> chatList) async {
    if (chatList.isEmpty) {
      return;
    }
    // 连接
    Database db = await ToolsSqlite().database;
    // 条件
    String query = chatList.map((chatId) => chatId).join(',');
    // 查询
    List<Map<String, dynamic>> dataList = await db.query(
      tableHis,
      groupBy: 'chatId',
      columns: [
        'chatId',
        'nickname',
        'portrait',
        'max(msgId) as msgId',
        'msgType',
        'content',
        'chatTalk',
        'createTime',
      ],
      where: 'chatId in ($query)',
    );
    Map<String, Map<String, Object?>> dataMap = {};
    for (var data in dataList) {
      dataMap[data['chatId']] = data;
    }
    // 时间
    String createTime = DateTime.now().millisecondsSinceEpoch.toString();
    // 批量
    Batch batch = db.batch();
    for (var chatId in chatList) {
      if (dataMap.containsKey(chatId)) {
        // 插入
        batch.insert(
          tableMsg,
          dataMap[chatId]!,
          conflictAlgorithm: ConflictAlgorithm.replace,
        );
      } else {
        // 更新
        batch.update(
          tableMsg,
          {
            'msgType': MsgType.text.value,
            'content': jsonEncode({'data': ''}),
            'createTime': createTime,
          },
          where: 'chatId = ?',
          whereArgs: [chatId],
          conflictAlgorithm: ConflictAlgorithm.ignore,
        );
      }
    }
    // 提交
    await batch.commit();
  }
}