1694 lines
42 KiB
Go
1694 lines
42 KiB
Go
package v4
|
||
|
||
import (
|
||
"context"
|
||
"crypto/md5"
|
||
"database/sql"
|
||
"encoding/hex"
|
||
"fmt"
|
||
"regexp"
|
||
"sort"
|
||
"strings"
|
||
"time"
|
||
|
||
"github.com/fsnotify/fsnotify"
|
||
_ "github.com/mattn/go-sqlite3"
|
||
"github.com/rs/zerolog/log"
|
||
|
||
"github.com/sjzar/chatlog/internal/errors"
|
||
"github.com/sjzar/chatlog/internal/model"
|
||
"github.com/sjzar/chatlog/internal/wechatdb/datasource/dbm"
|
||
"github.com/sjzar/chatlog/pkg/util"
|
||
)
|
||
|
||
const (
|
||
Message = "message"
|
||
Contact = "contact"
|
||
Session = "session"
|
||
Media = "media"
|
||
Voice = "voice"
|
||
)
|
||
|
||
var Groups = []*dbm.Group{
|
||
{
|
||
Name: Message,
|
||
Pattern: `^message_([0-9]?[0-9])?\.db$`,
|
||
BlackList: []string{},
|
||
},
|
||
{
|
||
Name: Contact,
|
||
Pattern: `^contact\.db$`,
|
||
BlackList: []string{},
|
||
},
|
||
{
|
||
Name: Session,
|
||
Pattern: `session\.db$`,
|
||
BlackList: []string{},
|
||
},
|
||
{
|
||
Name: Media,
|
||
Pattern: `^hardlink\.db$`,
|
||
BlackList: []string{},
|
||
},
|
||
{
|
||
Name: Voice,
|
||
Pattern: `^media_([0-9]?[0-9])?\.db$`,
|
||
BlackList: []string{},
|
||
},
|
||
{
|
||
Name: "headimg",
|
||
Pattern: `^head_image\.db$`,
|
||
BlackList: []string{},
|
||
},
|
||
}
|
||
|
||
// MessageDBInfo 存储消息数据库的信息
|
||
type MessageDBInfo struct {
|
||
FilePath string
|
||
StartTime time.Time
|
||
EndTime time.Time
|
||
}
|
||
|
||
type DataSource struct {
|
||
path string
|
||
dbm *dbm.DBManager
|
||
|
||
// 消息数据库信息
|
||
messageInfos []MessageDBInfo
|
||
}
|
||
|
||
func New(path string) (*DataSource, error) {
|
||
|
||
ds := &DataSource{
|
||
path: path,
|
||
dbm: dbm.NewDBManager(path),
|
||
messageInfos: make([]MessageDBInfo, 0),
|
||
}
|
||
|
||
for _, g := range Groups {
|
||
ds.dbm.AddGroup(g)
|
||
}
|
||
|
||
if err := ds.dbm.Start(); err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
if err := ds.initMessageDbs(); err != nil {
|
||
return nil, errors.DBInitFailed(err)
|
||
}
|
||
|
||
ds.dbm.AddCallback(Message, func(event fsnotify.Event) error {
|
||
if !event.Op.Has(fsnotify.Create) {
|
||
return nil
|
||
}
|
||
if err := ds.initMessageDbs(); err != nil {
|
||
log.Err(err).Msgf("Failed to reinitialize message DBs: %s", event.Name)
|
||
}
|
||
return nil
|
||
})
|
||
|
||
return ds, nil
|
||
}
|
||
|
||
func (ds *DataSource) SetCallback(group string, callback func(event fsnotify.Event) error) error {
|
||
if group == "chatroom" {
|
||
group = Contact
|
||
}
|
||
return ds.dbm.AddCallback(group, callback)
|
||
}
|
||
|
||
func (ds *DataSource) initMessageDbs() error {
|
||
dbPaths, err := ds.dbm.GetDBPath(Message)
|
||
if err != nil {
|
||
if strings.Contains(err.Error(), "db file not found") {
|
||
ds.messageInfos = make([]MessageDBInfo, 0)
|
||
return nil
|
||
}
|
||
return err
|
||
}
|
||
|
||
// 处理每个数据库文件
|
||
infos := make([]MessageDBInfo, 0)
|
||
for _, filePath := range dbPaths {
|
||
db, err := ds.dbm.OpenDB(filePath)
|
||
if err != nil {
|
||
log.Err(err).Msgf("获取数据库 %s 失败", filePath)
|
||
continue
|
||
}
|
||
|
||
// 获取 Timestamp 表中的开始时间
|
||
var startTime time.Time
|
||
var timestamp int64
|
||
|
||
row := db.QueryRow("SELECT timestamp FROM Timestamp LIMIT 1")
|
||
if err := row.Scan(×tamp); err != nil {
|
||
log.Err(err).Msgf("获取数据库 %s 的时间戳失败", filePath)
|
||
continue
|
||
}
|
||
startTime = time.Unix(timestamp, 0)
|
||
|
||
// 保存数据库信息
|
||
infos = append(infos, MessageDBInfo{
|
||
FilePath: filePath,
|
||
StartTime: startTime,
|
||
})
|
||
}
|
||
|
||
// 按照 StartTime 排序数据库文件
|
||
sort.Slice(infos, func(i, j int) bool {
|
||
return infos[i].StartTime.Before(infos[j].StartTime)
|
||
})
|
||
|
||
// 设置结束时间
|
||
for i := range infos {
|
||
if i == len(infos)-1 {
|
||
infos[i].EndTime = time.Now().Add(time.Hour)
|
||
} else {
|
||
infos[i].EndTime = infos[i+1].StartTime
|
||
}
|
||
}
|
||
if len(ds.messageInfos) > 0 && len(infos) < len(ds.messageInfos) {
|
||
log.Warn().Msgf("message db count decreased from %d to %d, skip init", len(ds.messageInfos), len(infos))
|
||
return nil
|
||
}
|
||
ds.messageInfos = infos
|
||
return nil
|
||
}
|
||
|
||
// getDBInfosForTimeRange 获取时间范围内的数据库信息
|
||
func (ds *DataSource) getDBInfosForTimeRange(startTime, endTime time.Time) []MessageDBInfo {
|
||
var dbs []MessageDBInfo
|
||
for _, info := range ds.messageInfos {
|
||
if info.StartTime.Before(endTime) && info.EndTime.After(startTime) {
|
||
dbs = append(dbs, info)
|
||
}
|
||
}
|
||
return dbs
|
||
}
|
||
|
||
func (ds *DataSource) GetMessages(ctx context.Context, startTime, endTime time.Time, talker string, sender string, keyword string, limit, offset int) ([]*model.Message, error) {
|
||
if talker == "" {
|
||
return nil, errors.ErrTalkerEmpty
|
||
}
|
||
|
||
// 解析talker参数，支持多个talker（以英文逗号分隔）
|
||
talkers := util.Str2List(talker, ",")
|
||
if len(talkers) == 0 {
|
||
return nil, errors.ErrTalkerEmpty
|
||
}
|
||
|
||
// 找到时间范围内的数据库文件
|
||
dbInfos := ds.getDBInfosForTimeRange(startTime, endTime)
|
||
if len(dbInfos) == 0 {
|
||
return nil, errors.TimeRangeNotFound(startTime, endTime)
|
||
}
|
||
|
||
// 解析sender参数，支持多个发送者（以英文逗号分隔）
|
||
senders := util.Str2List(sender, ",")
|
||
|
||
// 预编译正则表达式（如果有keyword）
|
||
var regex *regexp.Regexp
|
||
if keyword != "" {
|
||
var err error
|
||
regex, err = regexp.Compile(keyword)
|
||
if err != nil {
|
||
return nil, errors.QueryFailed("invalid regex pattern", err)
|
||
}
|
||
}
|
||
|
||
// 从每个相关数据库中查询消息，并在读取时进行过滤
|
||
filteredMessages := []*model.Message{}
|
||
|
||
for _, dbInfo := range dbInfos {
|
||
// 检查上下文是否已取消
|
||
if err := ctx.Err(); err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
db, err := ds.dbm.OpenDB(dbInfo.FilePath)
|
||
if err != nil {
|
||
log.Error().Msgf("数据库 %s 未打开", dbInfo.FilePath)
|
||
continue
|
||
}
|
||
|
||
// 对每个talker进行查询
|
||
for _, talkerItem := range talkers {
|
||
// 构建表名
|
||
_talkerMd5Bytes := md5.Sum([]byte(talkerItem))
|
||
talkerMd5 := hex.EncodeToString(_talkerMd5Bytes[:])
|
||
tableName := "Msg_" + talkerMd5
|
||
|
||
// 检查表是否存在
|
||
var exists bool
|
||
err = db.QueryRowContext(ctx,
|
||
"SELECT 1 FROM sqlite_master WHERE type='table' AND name=?",
|
||
tableName).Scan(&exists)
|
||
|
||
if err != nil {
|
||
if err == sql.ErrNoRows {
|
||
// 表不存在，继续下一个talker
|
||
continue
|
||
}
|
||
return nil, errors.QueryFailed("", err)
|
||
}
|
||
|
||
// 构建查询条件
|
||
conditions := []string{"create_time >= ? AND create_time <= ?"}
|
||
args := []interface{}{startTime.Unix(), endTime.Unix()}
|
||
log.Debug().Msgf("Table name: %s", tableName)
|
||
log.Debug().Msgf("Start time: %d, End time: %d", startTime.Unix(), endTime.Unix())
|
||
|
||
query := fmt.Sprintf(`
|
||
SELECT m.sort_seq, m.server_id, m.local_type, n.user_name, m.create_time, m.message_content, m.packed_info_data, m.status
|
||
FROM %s m
|
||
LEFT JOIN Name2Id n ON m.real_sender_id = n.rowid
|
||
WHERE %s
|
||
ORDER BY m.sort_seq ASC
|
||
`, tableName, strings.Join(conditions, " AND "))
|
||
|
||
// 执行查询
|
||
rows, err := db.QueryContext(ctx, query, args...)
|
||
if err != nil {
|
||
// 如果表不存在，SQLite 会返回错误
|
||
if strings.Contains(err.Error(), "no such table") {
|
||
continue
|
||
}
|
||
log.Err(err).Msgf("从数据库 %s 查询消息失败", dbInfo.FilePath)
|
||
continue
|
||
}
|
||
|
||
// 处理查询结果，在读取时进行过滤
|
||
for rows.Next() {
|
||
var msg model.MessageV4
|
||
err := rows.Scan(
|
||
&msg.SortSeq,
|
||
&msg.ServerID,
|
||
&msg.LocalType,
|
||
&msg.UserName,
|
||
&msg.CreateTime,
|
||
&msg.MessageContent,
|
||
&msg.PackedInfoData,
|
||
&msg.Status,
|
||
)
|
||
if err != nil {
|
||
rows.Close()
|
||
return nil, errors.ScanRowFailed(err)
|
||
}
|
||
|
||
// 将消息转换为标准格式
|
||
message := msg.Wrap(talkerItem)
|
||
|
||
// 应用sender过滤
|
||
if len(senders) > 0 {
|
||
senderMatch := false
|
||
for _, s := range senders {
|
||
if message.Sender == s {
|
||
senderMatch = true
|
||
break
|
||
}
|
||
}
|
||
if !senderMatch {
|
||
continue // 不匹配sender，跳过此消息
|
||
}
|
||
}
|
||
|
||
// 应用keyword过滤
|
||
if regex != nil {
|
||
plainText := message.PlainTextContent()
|
||
if !regex.MatchString(plainText) {
|
||
continue // 不匹配keyword，跳过此消息
|
||
}
|
||
}
|
||
|
||
// 通过所有过滤条件，保留此消息
|
||
filteredMessages = append(filteredMessages, message)
|
||
|
||
// 检查是否已经满足分页处理数量
|
||
if limit > 0 && len(filteredMessages) >= offset+limit {
|
||
// 已经获取了足够的消息，可以提前返回
|
||
rows.Close()
|
||
|
||
// 对所有消息按时间排序
|
||
sort.Slice(filteredMessages, func(i, j int) bool {
|
||
return filteredMessages[i].Seq < filteredMessages[j].Seq
|
||
})
|
||
|
||
// 处理分页
|
||
if offset >= len(filteredMessages) {
|
||
return []*model.Message{}, nil
|
||
}
|
||
end := offset + limit
|
||
if end > len(filteredMessages) {
|
||
end = len(filteredMessages)
|
||
}
|
||
return filteredMessages[offset:end], nil
|
||
}
|
||
}
|
||
rows.Close()
|
||
}
|
||
}
|
||
|
||
// 对所有消息按时间排序
|
||
sort.Slice(filteredMessages, func(i, j int) bool {
|
||
return filteredMessages[i].Seq < filteredMessages[j].Seq
|
||
})
|
||
|
||
// 处理分页
|
||
if limit > 0 {
|
||
if offset >= len(filteredMessages) {
|
||
return []*model.Message{}, nil
|
||
}
|
||
end := offset + limit
|
||
if end > len(filteredMessages) {
|
||
end = len(filteredMessages)
|
||
}
|
||
return filteredMessages[offset:end], nil
|
||
}
|
||
|
||
return filteredMessages, nil
|
||
}
|
||
|
||
func (ds *DataSource) ListTalkers(ctx context.Context) ([]string, error) {
|
||
talkerSet := make(map[string]struct{})
|
||
add := func(name string) {
|
||
name = strings.TrimSpace(name)
|
||
if name == "" {
|
||
return
|
||
}
|
||
if _, ok := talkerSet[name]; ok {
|
||
return
|
||
}
|
||
talkerSet[name] = struct{}{}
|
||
}
|
||
|
||
db, err := ds.dbm.GetDB(Contact)
|
||
if err != nil {
|
||
log.Debug().Err(err).Msg("query contact usernames failed")
|
||
} else if db != nil {
|
||
rows, err := db.QueryContext(ctx, `SELECT username FROM contact WHERE IFNULL(username,'') <> ''`)
|
||
if err == nil {
|
||
for rows.Next() {
|
||
if err := ctx.Err(); err != nil {
|
||
rows.Close()
|
||
return nil, err
|
||
}
|
||
var username string
|
||
if scanErr := rows.Scan(&username); scanErr == nil {
|
||
add(username)
|
||
} else {
|
||
log.Debug().Err(scanErr).Msg("scan contact username failed")
|
||
}
|
||
}
|
||
if err := rows.Err(); err != nil {
|
||
log.Debug().Err(err).Msg("iterate contact usernames failed")
|
||
}
|
||
rows.Close()
|
||
} else {
|
||
log.Debug().Err(err).Msg("query contact usernames failed")
|
||
}
|
||
|
||
roomRows, err := db.QueryContext(ctx, `SELECT username FROM chat_room WHERE IFNULL(username,'') <> ''`)
|
||
if err == nil {
|
||
for roomRows.Next() {
|
||
if err := ctx.Err(); err != nil {
|
||
roomRows.Close()
|
||
return nil, err
|
||
}
|
||
var username string
|
||
if scanErr := roomRows.Scan(&username); scanErr == nil {
|
||
add(username)
|
||
} else {
|
||
log.Debug().Err(scanErr).Msg("scan chat_room username failed")
|
||
}
|
||
}
|
||
if err := roomRows.Err(); err != nil {
|
||
log.Debug().Err(err).Msg("iterate chat_room usernames failed")
|
||
}
|
||
roomRows.Close()
|
||
} else {
|
||
log.Debug().Err(err).Msg("query chat_room usernames failed")
|
||
}
|
||
}
|
||
|
||
db, err = ds.dbm.GetDB(Session)
|
||
if err != nil {
|
||
log.Debug().Err(err).Msg("query session usernames failed")
|
||
} else if db != nil {
|
||
rows, err := db.QueryContext(ctx, `SELECT username FROM SessionTable WHERE IFNULL(username,'') <> ''`)
|
||
if err == nil {
|
||
for rows.Next() {
|
||
if err := ctx.Err(); err != nil {
|
||
rows.Close()
|
||
return nil, err
|
||
}
|
||
var username string
|
||
if scanErr := rows.Scan(&username); scanErr == nil {
|
||
add(username)
|
||
} else {
|
||
log.Debug().Err(scanErr).Msg("scan session username failed")
|
||
}
|
||
}
|
||
if err := rows.Err(); err != nil {
|
||
log.Debug().Err(err).Msg("iterate session usernames failed")
|
||
}
|
||
rows.Close()
|
||
} else {
|
||
log.Debug().Err(err).Msg("query session usernames failed")
|
||
}
|
||
}
|
||
|
||
talkers := make([]string, 0, len(talkerSet))
|
||
for username := range talkerSet {
|
||
talkers = append(talkers, username)
|
||
}
|
||
sort.Strings(talkers)
|
||
return talkers, nil
|
||
}
|
||
|
||
func (ds *DataSource) IterateMessages(ctx context.Context, talkers []string, handler func(*model.Message) error) error {
|
||
if handler == nil {
|
||
return errors.InvalidArg("handler")
|
||
}
|
||
|
||
if len(talkers) == 0 {
|
||
var err error
|
||
talkers, err = ds.ListTalkers(ctx)
|
||
if err != nil {
|
||
return err
|
||
}
|
||
}
|
||
if len(talkers) == 0 {
|
||
return nil
|
||
}
|
||
|
||
tableNames := make(map[string]string, len(talkers))
|
||
for _, talker := range talkers {
|
||
hash := md5.Sum([]byte(talker))
|
||
tableNames[talker] = "Msg_" + hex.EncodeToString(hash[:])
|
||
}
|
||
|
||
for _, info := range ds.messageInfos {
|
||
if err := ctx.Err(); err != nil {
|
||
return err
|
||
}
|
||
|
||
db, err := ds.dbm.OpenDB(info.FilePath)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
|
||
for _, talker := range talkers {
|
||
if err := ctx.Err(); err != nil {
|
||
return err
|
||
}
|
||
tableName := tableNames[talker]
|
||
|
||
query := fmt.Sprintf(`
|
||
SELECT m.sort_seq, m.server_id, m.local_type, n.user_name,
|
||
m.create_time, m.message_content, m.packed_info_data, m.status
|
||
FROM %s AS m
|
||
LEFT JOIN Name2Id n ON m.real_sender_id = n.rowid
|
||
ORDER BY m.sort_seq ASC
|
||
`, tableName)
|
||
|
||
rows, err := db.QueryContext(ctx, query)
|
||
if err != nil {
|
||
if strings.Contains(err.Error(), "no such table") {
|
||
continue
|
||
}
|
||
return errors.QueryFailed("iterate messages", err)
|
||
}
|
||
|
||
for rows.Next() {
|
||
if err := ctx.Err(); err != nil {
|
||
rows.Close()
|
||
return err
|
||
}
|
||
var msg model.MessageV4
|
||
var messageContent []byte
|
||
if scanErr := rows.Scan(
|
||
&msg.SortSeq,
|
||
&msg.ServerID,
|
||
&msg.LocalType,
|
||
&msg.UserName,
|
||
&msg.CreateTime,
|
||
&messageContent,
|
||
&msg.PackedInfoData,
|
||
&msg.Status,
|
||
); scanErr != nil {
|
||
rows.Close()
|
||
return errors.ScanRowFailed(scanErr)
|
||
}
|
||
msg.MessageContent = messageContent
|
||
message := msg.Wrap(talker)
|
||
if err := handler(message); err != nil {
|
||
rows.Close()
|
||
return err
|
||
}
|
||
}
|
||
if err := rows.Err(); err != nil {
|
||
rows.Close()
|
||
return errors.QueryFailed("iterate message rows", err)
|
||
}
|
||
rows.Close()
|
||
}
|
||
}
|
||
|
||
return nil
|
||
}
|
||
|
||
func (ds *DataSource) GetDatasetFingerprint(context.Context) (string, error) {
|
||
return ds.dbm.FingerprintForGroups(Message)
|
||
}
|
||
|
||
// 联系人
|
||
func (ds *DataSource) GetContacts(ctx context.Context, key string, limit, offset int) ([]*model.Contact, error) {
|
||
var query string
|
||
var args []interface{}
|
||
|
||
if key != "" {
|
||
// 按照关键字查询
|
||
query = `SELECT username, local_type, alias, remark, nick_name
|
||
FROM contact
|
||
WHERE username = ? OR alias = ? OR remark = ? OR nick_name = ?`
|
||
args = []interface{}{key, key, key, key}
|
||
} else {
|
||
// 查询所有联系人
|
||
query = `SELECT username, local_type, alias, remark, nick_name FROM contact`
|
||
}
|
||
|
||
// 添加排序、分页
|
||
query += ` ORDER BY username`
|
||
if limit > 0 {
|
||
query += fmt.Sprintf(" LIMIT %d", limit)
|
||
if offset > 0 {
|
||
query += fmt.Sprintf(" OFFSET %d", offset)
|
||
}
|
||
}
|
||
|
||
// 执行查询
|
||
db, err := ds.dbm.GetDB(Contact)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
rows, err := db.QueryContext(ctx, query, args...)
|
||
if err != nil {
|
||
return nil, errors.QueryFailed(query, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
contacts := []*model.Contact{}
|
||
for rows.Next() {
|
||
var contactV4 model.ContactV4
|
||
err := rows.Scan(
|
||
&contactV4.UserName,
|
||
&contactV4.LocalType,
|
||
&contactV4.Alias,
|
||
&contactV4.Remark,
|
||
&contactV4.NickName,
|
||
)
|
||
|
||
if err != nil {
|
||
return nil, errors.ScanRowFailed(err)
|
||
}
|
||
|
||
contacts = append(contacts, contactV4.Wrap())
|
||
}
|
||
|
||
return contacts, nil
|
||
}
|
||
|
||
// 群聊
|
||
func (ds *DataSource) GetChatRooms(ctx context.Context, key string, limit, offset int) ([]*model.ChatRoom, error) {
|
||
var query string
|
||
var args []interface{}
|
||
|
||
// 执行查询
|
||
db, err := ds.dbm.GetDB(Contact)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
|
||
if key != "" {
|
||
// 按照关键字查询
|
||
query = `SELECT username, owner, ext_buffer FROM chat_room WHERE username = ?`
|
||
args = []interface{}{key}
|
||
|
||
rows, err := db.QueryContext(ctx, query, args...)
|
||
if err != nil {
|
||
return nil, errors.QueryFailed(query, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
chatRooms := []*model.ChatRoom{}
|
||
for rows.Next() {
|
||
var chatRoomV4 model.ChatRoomV4
|
||
err := rows.Scan(
|
||
&chatRoomV4.UserName,
|
||
&chatRoomV4.Owner,
|
||
&chatRoomV4.ExtBuffer,
|
||
)
|
||
|
||
if err != nil {
|
||
return nil, errors.ScanRowFailed(err)
|
||
}
|
||
|
||
chatRooms = append(chatRooms, chatRoomV4.Wrap())
|
||
}
|
||
|
||
// 如果没有找到群聊，尝试通过联系人查找
|
||
if len(chatRooms) == 0 {
|
||
contacts, err := ds.GetContacts(ctx, key, 1, 0)
|
||
if err == nil && len(contacts) > 0 && strings.HasSuffix(contacts[0].UserName, "@chatroom") {
|
||
// 再次尝试通过用户名查找群聊
|
||
rows, err := db.QueryContext(ctx,
|
||
`SELECT username, owner, ext_buffer FROM chat_room WHERE username = ?`,
|
||
contacts[0].UserName)
|
||
|
||
if err != nil {
|
||
return nil, errors.QueryFailed(query, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
for rows.Next() {
|
||
var chatRoomV4 model.ChatRoomV4
|
||
err := rows.Scan(
|
||
&chatRoomV4.UserName,
|
||
&chatRoomV4.Owner,
|
||
&chatRoomV4.ExtBuffer,
|
||
)
|
||
|
||
if err != nil {
|
||
return nil, errors.ScanRowFailed(err)
|
||
}
|
||
|
||
chatRooms = append(chatRooms, chatRoomV4.Wrap())
|
||
}
|
||
|
||
// 如果群聊记录不存在，但联系人记录存在，创建一个模拟的群聊对象
|
||
if len(chatRooms) == 0 {
|
||
chatRooms = append(chatRooms, &model.ChatRoom{
|
||
Name: contacts[0].UserName,
|
||
Users: make([]model.ChatRoomUser, 0),
|
||
User2DisplayName: make(map[string]string),
|
||
})
|
||
}
|
||
}
|
||
}
|
||
|
||
return chatRooms, nil
|
||
} else {
|
||
// 查询所有群聊
|
||
query = `SELECT username, owner, ext_buffer FROM chat_room`
|
||
|
||
// 添加排序、分页
|
||
query += ` ORDER BY username`
|
||
if limit > 0 {
|
||
query += fmt.Sprintf(" LIMIT %d", limit)
|
||
if offset > 0 {
|
||
query += fmt.Sprintf(" OFFSET %d", offset)
|
||
}
|
||
}
|
||
|
||
// 执行查询
|
||
rows, err := db.QueryContext(ctx, query, args...)
|
||
if err != nil {
|
||
return nil, errors.QueryFailed(query, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
chatRooms := []*model.ChatRoom{}
|
||
for rows.Next() {
|
||
var chatRoomV4 model.ChatRoomV4
|
||
err := rows.Scan(
|
||
&chatRoomV4.UserName,
|
||
&chatRoomV4.Owner,
|
||
&chatRoomV4.ExtBuffer,
|
||
)
|
||
|
||
if err != nil {
|
||
return nil, errors.ScanRowFailed(err)
|
||
}
|
||
|
||
chatRooms = append(chatRooms, chatRoomV4.Wrap())
|
||
}
|
||
|
||
return chatRooms, nil
|
||
}
|
||
}
|
||
|
||
// 最近会话
|
||
func (ds *DataSource) GetSessions(ctx context.Context, key string, limit, offset int) ([]*model.Session, error) {
|
||
var query string
|
||
var args []interface{}
|
||
|
||
if key != "" {
|
||
// 按照关键字查询
|
||
query = `SELECT username, summary, last_timestamp, last_msg_sender, last_sender_display_name
|
||
FROM SessionTable
|
||
WHERE username = ? OR last_sender_display_name = ?
|
||
ORDER BY sort_timestamp DESC`
|
||
args = []interface{}{key, key}
|
||
} else {
|
||
// 查询所有会话
|
||
query = `SELECT username, summary, last_timestamp, last_msg_sender, last_sender_display_name
|
||
FROM SessionTable
|
||
ORDER BY sort_timestamp DESC`
|
||
}
|
||
|
||
// 添加分页
|
||
if limit > 0 {
|
||
query += fmt.Sprintf(" LIMIT %d", limit)
|
||
if offset > 0 {
|
||
query += fmt.Sprintf(" OFFSET %d", offset)
|
||
}
|
||
}
|
||
|
||
// 执行查询
|
||
db, err := ds.dbm.GetDB(Session)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
rows, err := db.QueryContext(ctx, query, args...)
|
||
if err != nil {
|
||
return nil, errors.QueryFailed(query, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
sessions := []*model.Session{}
|
||
for rows.Next() {
|
||
var sessionV4 model.SessionV4
|
||
err := rows.Scan(
|
||
&sessionV4.Username,
|
||
&sessionV4.Summary,
|
||
&sessionV4.LastTimestamp,
|
||
&sessionV4.LastMsgSender,
|
||
&sessionV4.LastSenderDisplayName,
|
||
)
|
||
|
||
if err != nil {
|
||
return nil, errors.ScanRowFailed(err)
|
||
}
|
||
|
||
sessions = append(sessions, sessionV4.Wrap())
|
||
}
|
||
|
||
return sessions, nil
|
||
}
|
||
|
||
func (ds *DataSource) GetMedia(ctx context.Context, _type string, key string) (*model.Media, error) {
|
||
if key == "" {
|
||
return nil, errors.ErrKeyEmpty
|
||
}
|
||
|
||
var table string
|
||
switch _type {
|
||
case "image":
|
||
table = "image_hardlink_info_v3"
|
||
// 4.1.0 版本开始使用 v4 表
|
||
if !ds.IsExist(Media, table) {
|
||
table = "image_hardlink_info_v4"
|
||
}
|
||
case "video":
|
||
table = "video_hardlink_info_v3"
|
||
if !ds.IsExist(Media, table) {
|
||
table = "video_hardlink_info_v4"
|
||
}
|
||
case "file":
|
||
table = "file_hardlink_info_v3"
|
||
if !ds.IsExist(Media, table) {
|
||
table = "file_hardlink_info_v4"
|
||
}
|
||
case "voice":
|
||
return ds.GetVoice(ctx, key)
|
||
default:
|
||
return nil, errors.MediaTypeUnsupported(_type)
|
||
}
|
||
|
||
query := fmt.Sprintf(`
|
||
SELECT
|
||
f.md5,
|
||
f.file_name,
|
||
f.file_size,
|
||
f.modify_time,
|
||
IFNULL(d1.username,""),
|
||
IFNULL(d2.username,"")
|
||
FROM
|
||
%s f
|
||
LEFT JOIN
|
||
dir2id d1 ON d1.rowid = f.dir1
|
||
LEFT JOIN
|
||
dir2id d2 ON d2.rowid = f.dir2
|
||
`, table)
|
||
query += " WHERE f.md5 = ? OR f.file_name LIKE ? || '%'"
|
||
args := []interface{}{key, key}
|
||
|
||
// 执行查询
|
||
db, err := ds.dbm.GetDB(Media)
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
rows, err := db.QueryContext(ctx, query, args...)
|
||
if err != nil {
|
||
return nil, errors.QueryFailed(query, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
var media *model.Media
|
||
for rows.Next() {
|
||
var mediaV4 model.MediaV4
|
||
err := rows.Scan(
|
||
&mediaV4.Key,
|
||
&mediaV4.Name,
|
||
&mediaV4.Size,
|
||
&mediaV4.ModifyTime,
|
||
&mediaV4.Dir1,
|
||
&mediaV4.Dir2,
|
||
)
|
||
if err != nil {
|
||
return nil, errors.ScanRowFailed(err)
|
||
}
|
||
mediaV4.Type = _type
|
||
media = mediaV4.Wrap()
|
||
|
||
// 优先返回高清图
|
||
if _type == "image" && strings.HasSuffix(mediaV4.Name, "_h.dat") {
|
||
break
|
||
}
|
||
}
|
||
|
||
if media == nil {
|
||
return nil, errors.ErrMediaNotFound
|
||
}
|
||
|
||
return media, nil
|
||
}
|
||
|
||
func (ds *DataSource) IsExist(_db string, table string) bool {
|
||
db, err := ds.dbm.GetDB(_db)
|
||
if err != nil {
|
||
return false
|
||
}
|
||
var tableName string
|
||
query := "SELECT name FROM sqlite_master WHERE type='table' AND name=?;"
|
||
if err = db.QueryRow(query, table).Scan(&tableName); err != nil {
|
||
if errors.Is(err, sql.ErrNoRows) {
|
||
return false
|
||
}
|
||
return false
|
||
}
|
||
return true
|
||
}
|
||
|
||
func (ds *DataSource) GetVoice(ctx context.Context, key string) (*model.Media, error) {
|
||
if key == "" {
|
||
return nil, errors.ErrKeyEmpty
|
||
}
|
||
|
||
query := `
|
||
SELECT voice_data
|
||
FROM VoiceInfo
|
||
WHERE svr_id = ?
|
||
`
|
||
args := []interface{}{key}
|
||
|
||
dbs, err := ds.dbm.GetDBs(Voice)
|
||
if err != nil {
|
||
return nil, errors.DBConnectFailed("", err)
|
||
}
|
||
|
||
for _, db := range dbs {
|
||
rows, err := db.QueryContext(ctx, query, args...)
|
||
if err != nil {
|
||
return nil, errors.QueryFailed(query, err)
|
||
}
|
||
defer rows.Close()
|
||
|
||
for rows.Next() {
|
||
var voiceData []byte
|
||
err := rows.Scan(
|
||
&voiceData,
|
||
)
|
||
if err != nil {
|
||
return nil, errors.ScanRowFailed(err)
|
||
}
|
||
if len(voiceData) > 0 {
|
||
return &model.Media{
|
||
Type: "voice",
|
||
Key: key,
|
||
Data: voiceData,
|
||
}, nil
|
||
}
|
||
}
|
||
}
|
||
|
||
return nil, errors.ErrMediaNotFound
|
||
}
|
||
|
||
func (ds *DataSource) Close() error {
|
||
return ds.dbm.Close()
|
||
}
|
||
|
||
// GetAvatar for v4: read head_image.db -> head_image(username, image_buffer)
|
||
func (ds *DataSource) GetAvatar(ctx context.Context, username string, size string) (*model.Avatar, error) {
|
||
if username == "" {
|
||
return nil, errors.ErrKeyEmpty
|
||
}
|
||
// open head_image db
|
||
db, err := ds.dbm.GetDB("headimg")
|
||
if err != nil {
|
||
return nil, errors.ErrAvatarNotFound
|
||
}
|
||
// table may be head_image, columns: username, image_buffer (jfif), update_time, md5
|
||
row := db.QueryRowContext(ctx, `SELECT image_buffer FROM head_image WHERE username = ?`, username)
|
||
var buf []byte
|
||
if err := row.Scan(&buf); err != nil || len(buf) == 0 {
|
||
return nil, errors.ErrAvatarNotFound
|
||
}
|
||
return &model.Avatar{Username: username, ContentType: "image/jpeg", Data: buf}, nil
|
||
}
|
||
|
||
// GlobalMessageStats 聚合统计（Windows/Darwin v4）
|
||
func (ds *DataSource) GlobalMessageStats(ctx context.Context) (*model.GlobalMessageStats, error) {
|
||
stats := &model.GlobalMessageStats{ByType: make(map[string]int64)}
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return stats, nil
|
||
}
|
||
for _, db := range dbs {
|
||
// 列举所有 Msg_ 前缀表
|
||
trows, err := db.QueryContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'Msg_%'`)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
var tables []string
|
||
for trows.Next() {
|
||
var name string
|
||
if err := trows.Scan(&name); err == nil {
|
||
tables = append(tables, name)
|
||
}
|
||
}
|
||
trows.Close()
|
||
for _, tbl := range tables {
|
||
// total/sent/min/max
|
||
q := fmt.Sprintf(`SELECT COUNT(*) AS total,
|
||
SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS sent,
|
||
MIN(create_time) AS minct,
|
||
MAX(create_time) AS maxct FROM %s`, tbl)
|
||
row := db.QueryRowContext(ctx, q)
|
||
var total, sent, minct, maxct int64
|
||
if err := row.Scan(&total, &sent, &minct, &maxct); err == nil {
|
||
stats.Total += total
|
||
stats.Sent += sent
|
||
stats.Received += (total - sent)
|
||
if stats.EarliestUnix == 0 || (minct > 0 && minct < stats.EarliestUnix) {
|
||
stats.EarliestUnix = minct
|
||
}
|
||
if maxct > stats.LatestUnix {
|
||
stats.LatestUnix = maxct
|
||
}
|
||
}
|
||
// by type (细分 49)
|
||
// 先统计除 49 之外类型
|
||
q2 := fmt.Sprintf(`SELECT local_type, COUNT(*) FROM %s WHERE local_type != 49 GROUP BY local_type`, tbl)
|
||
rows, err := db.QueryContext(ctx, q2)
|
||
if err == nil {
|
||
for rows.Next() {
|
||
var t int64
|
||
var cnt int64
|
||
if err := rows.Scan(&t, &cnt); err == nil {
|
||
label := mapV4TypeToLabel(t)
|
||
if label != "" {
|
||
stats.ByType[label] += cnt
|
||
}
|
||
}
|
||
}
|
||
rows.Close()
|
||
}
|
||
// 针对 49 类型再做细分：简单解析 message_content 判断是文件、链接或通用 XML
|
||
q49 := fmt.Sprintf(`SELECT message_content FROM %s WHERE local_type = 49`, tbl)
|
||
orows, err := db.QueryContext(ctx, q49)
|
||
if err == nil {
|
||
for orows.Next() {
|
||
var mc []byte
|
||
if err := orows.Scan(&mc); err == nil {
|
||
content := string(mc)
|
||
// 可能压缩，简单特征判断（保持轻量；深度解压需额外性能，可后续拓展）
|
||
lc := strings.ToLower(content)
|
||
if strings.Contains(lc, "<appmsg") {
|
||
if strings.Contains(lc, "<type>") && strings.Contains(lc, "</type>") {
|
||
// 简单提取 type 数字
|
||
i1 := strings.Index(lc, "<type>")
|
||
i2 := strings.Index(lc[i1+6:], "</type>")
|
||
if i1 >= 0 && i2 > 0 {
|
||
val := lc[i1+6 : i1+6+i2]
|
||
// 常见：6=文件, 5/33=链接(网页), 3=音乐, 4=视频, 其他归类为 XML
|
||
if strings.TrimSpace(val) == "6" {
|
||
stats.ByType["文件消息"]++
|
||
continue
|
||
}
|
||
if strings.TrimSpace(val) == "5" || strings.TrimSpace(val) == "33" {
|
||
stats.ByType["链接消息"]++
|
||
continue
|
||
}
|
||
}
|
||
}
|
||
// 兜底：若包含 url 或 http(s) 关键词也认为链接
|
||
if strings.Contains(lc, "http://") || strings.Contains(lc, "https://") {
|
||
stats.ByType["链接消息"]++
|
||
continue
|
||
}
|
||
// 再兜底为 XML消息
|
||
stats.ByType["XML消息"]++
|
||
}
|
||
}
|
||
}
|
||
orows.Close()
|
||
}
|
||
}
|
||
}
|
||
return stats, nil
|
||
}
|
||
|
||
// GroupMessageCounts 统计群聊消息数量（v4）：通过 chat_room.username 计算 md5 映射到 Msg_ 表
|
||
func (ds *DataSource) GroupMessageCounts(ctx context.Context) (map[string]int64, error) {
|
||
result := make(map[string]int64)
|
||
cdb, err := ds.dbm.GetDB(Contact)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
// 获取所有群聊用户名
|
||
urows, err := cdb.QueryContext(ctx, `SELECT username FROM chat_room`)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
var rooms []string
|
||
for urows.Next() {
|
||
var u string
|
||
if err := urows.Scan(&u); err == nil {
|
||
rooms = append(rooms, u)
|
||
}
|
||
}
|
||
urows.Close()
|
||
if len(rooms) == 0 {
|
||
return result, nil
|
||
}
|
||
// 遍历消息库
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
for _, db := range dbs {
|
||
for _, uname := range rooms {
|
||
md5sum := md5.Sum([]byte(uname))
|
||
tbl := "Msg_" + hex.EncodeToString(md5sum[:])
|
||
// 检查表是否存在
|
||
var name string
|
||
err := db.QueryRowContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name=?`, tbl).Scan(&name)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
// 计数
|
||
q := fmt.Sprintf(`SELECT COUNT(*) FROM %s`, tbl)
|
||
var cnt int64
|
||
if err := db.QueryRowContext(ctx, q).Scan(&cnt); err == nil {
|
||
result[uname] += cnt
|
||
}
|
||
}
|
||
}
|
||
return result, nil
|
||
}
|
||
|
||
// MonthlyTrend 返回每月 sent/received（按 create_time 聚合）
|
||
func (ds *DataSource) MonthlyTrend(ctx context.Context, months int) ([]model.MonthlyTrend, error) {
|
||
agg := make(map[string][2]int64)
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return []model.MonthlyTrend{}, nil
|
||
}
|
||
for _, db := range dbs {
|
||
trows, err := db.QueryContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'Msg_%'`)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
var tables []string
|
||
for trows.Next() {
|
||
var name string
|
||
if err := trows.Scan(&name); err == nil {
|
||
tables = append(tables, name)
|
||
}
|
||
}
|
||
trows.Close()
|
||
for _, tbl := range tables {
|
||
q := fmt.Sprintf(`SELECT strftime('%%Y-%%m', datetime(create_time, 'unixepoch')) AS ym,
|
||
SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) AS sent,
|
||
SUM(CASE WHEN status!=2 THEN 1 ELSE 0 END) AS recv
|
||
FROM %s GROUP BY ym ORDER BY ym`, tbl)
|
||
rows, err := db.QueryContext(ctx, q)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
for rows.Next() {
|
||
var ym string
|
||
var sent, recv int64
|
||
if err := rows.Scan(&ym, &sent, &recv); err == nil {
|
||
cur := agg[ym]
|
||
cur[0] += sent
|
||
cur[1] += recv
|
||
agg[ym] = cur
|
||
}
|
||
}
|
||
rows.Close()
|
||
}
|
||
}
|
||
// 排序输出
|
||
keys := make([]string, 0, len(agg))
|
||
for k := range agg {
|
||
keys = append(keys, k)
|
||
}
|
||
sort.Strings(keys)
|
||
trends := make([]model.MonthlyTrend, 0, len(keys))
|
||
for _, k := range keys {
|
||
v := agg[k]
|
||
trends = append(trends, model.MonthlyTrend{Date: k, Sent: v[0], Received: v[1]})
|
||
}
|
||
return trends, nil
|
||
}
|
||
|
||
// Heatmap 小时x星期（wday: 0=Sunday..6）
|
||
func (ds *DataSource) Heatmap(ctx context.Context) ([24][7]int64, error) {
|
||
var grid [24][7]int64
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return grid, nil
|
||
}
|
||
for _, db := range dbs {
|
||
trows, err := db.QueryContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'Msg_%'`)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
var tables []string
|
||
for trows.Next() {
|
||
var name string
|
||
if err := trows.Scan(&name); err == nil {
|
||
tables = append(tables, name)
|
||
}
|
||
}
|
||
trows.Close()
|
||
for _, tbl := range tables {
|
||
q := fmt.Sprintf(`SELECT CAST(strftime('%%H', datetime(create_time,'unixepoch')) AS INTEGER) AS h,
|
||
CAST(strftime('%%w', datetime(create_time,'unixepoch')) AS INTEGER) AS d,
|
||
COUNT(*) FROM %s GROUP BY h,d`, tbl)
|
||
rows, err := db.QueryContext(ctx, q)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
for rows.Next() {
|
||
var h, d int
|
||
var cnt int64
|
||
if err := rows.Scan(&h, &d, &cnt); err == nil {
|
||
if h >= 0 && h < 24 && d >= 0 && d < 7 {
|
||
grid[h][d] += cnt
|
||
}
|
||
}
|
||
}
|
||
rows.Close()
|
||
}
|
||
}
|
||
return grid, nil
|
||
}
|
||
|
||
// IntimacyBase 统计按联系人（非群聊）聚合的亲密度基础数据（v4）
|
||
func (ds *DataSource) IntimacyBase(ctx context.Context) (map[string]*model.IntimacyBase, error) {
|
||
result := make(map[string]*model.IntimacyBase)
|
||
|
||
// 构建 md5->username 映射（来自 contact 表）
|
||
md5ToUser := make(map[string]string)
|
||
if cdb, err := ds.dbm.GetDB(Contact); err == nil && cdb != nil {
|
||
rows, err := cdb.QueryContext(ctx, `SELECT username FROM contact`)
|
||
if err == nil {
|
||
defer rows.Close()
|
||
for rows.Next() {
|
||
var uname string
|
||
if rows.Scan(&uname) == nil && uname != "" {
|
||
sum := md5.Sum([]byte(uname))
|
||
md5ToUser[hex.EncodeToString(sum[:])] = uname
|
||
}
|
||
}
|
||
}
|
||
}
|
||
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
|
||
// 列出所有 Msg_% 表并求全局最大时间
|
||
var maxCT int64
|
||
type tbl struct {
|
||
db *sql.DB
|
||
name string
|
||
}
|
||
var tables []tbl
|
||
for _, db := range dbs {
|
||
rows, err := db.QueryContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'Msg_%'`)
|
||
if err == nil {
|
||
for rows.Next() {
|
||
var name string
|
||
if rows.Scan(&name) == nil {
|
||
tables = append(tables, tbl{db: db, name: name})
|
||
}
|
||
}
|
||
rows.Close()
|
||
}
|
||
}
|
||
for _, t := range tables {
|
||
row := t.db.QueryRowContext(ctx, `SELECT MAX(create_time) FROM `+t.name)
|
||
var v sql.NullInt64
|
||
if row.Scan(&v) == nil && v.Valid && v.Int64 > maxCT {
|
||
maxCT = v.Int64
|
||
}
|
||
}
|
||
if maxCT == 0 {
|
||
return result, nil
|
||
}
|
||
since90 := maxCT - 90*86400
|
||
since7 := maxCT - 7*86400
|
||
|
||
// 按表（即按 talker）聚合
|
||
for _, t := range tables {
|
||
// 从表名提取 md5 并映射成 username
|
||
if !strings.HasPrefix(t.name, "Msg_") {
|
||
continue
|
||
}
|
||
md5hex := strings.TrimPrefix(t.name, "Msg_")
|
||
talker := md5ToUser[md5hex]
|
||
if talker == "" {
|
||
continue
|
||
}
|
||
if strings.HasSuffix(talker, "@chatroom") {
|
||
continue
|
||
}
|
||
|
||
// total, sent, min, max
|
||
row := t.db.QueryRowContext(ctx, `SELECT COUNT(*), SUM(CASE WHEN status=2 THEN 1 ELSE 0 END), MIN(create_time), MAX(create_time) FROM `+t.name)
|
||
var total, sent, minct, maxct sql.NullInt64
|
||
if row.Scan(&total, &sent, &minct, &maxct) == nil {
|
||
base := result[talker]
|
||
if base == nil {
|
||
base = &model.IntimacyBase{UserName: talker}
|
||
result[talker] = base
|
||
}
|
||
if total.Valid {
|
||
base.MsgCount += total.Int64
|
||
base.ReceivedCount += (total.Int64 - func() int64 {
|
||
if sent.Valid {
|
||
return sent.Int64
|
||
}
|
||
return 0
|
||
}())
|
||
}
|
||
if sent.Valid {
|
||
base.SentCount += sent.Int64
|
||
}
|
||
if minct.Valid {
|
||
if base.MinCreateUnix == 0 || minct.Int64 < base.MinCreateUnix {
|
||
base.MinCreateUnix = minct.Int64
|
||
}
|
||
}
|
||
if maxct.Valid {
|
||
if maxct.Int64 > base.MaxCreateUnix {
|
||
base.MaxCreateUnix = maxct.Int64
|
||
}
|
||
}
|
||
}
|
||
|
||
// 活跃天数
|
||
row2 := t.db.QueryRowContext(ctx, `SELECT COUNT(DISTINCT date(datetime(create_time,'unixepoch'))) FROM `+t.name)
|
||
var days sql.NullInt64
|
||
if row2.Scan(&days) == nil && days.Valid {
|
||
base := result[talker]
|
||
if base == nil {
|
||
base = &model.IntimacyBase{UserName: talker}
|
||
result[talker] = base
|
||
}
|
||
base.MessagingDays += days.Int64
|
||
}
|
||
|
||
// 最近90天消息数
|
||
row3 := t.db.QueryRowContext(ctx, `SELECT COUNT(*) FROM `+t.name+` WHERE create_time>=?`, since90)
|
||
var c90 sql.NullInt64
|
||
if row3.Scan(&c90) == nil && c90.Valid {
|
||
base := result[talker]
|
||
if base == nil {
|
||
base = &model.IntimacyBase{UserName: talker}
|
||
result[talker] = base
|
||
}
|
||
base.Last90DaysMsg += c90.Int64
|
||
}
|
||
|
||
// 过去7天发送
|
||
row4 := t.db.QueryRowContext(ctx, `SELECT SUM(CASE WHEN status=2 THEN 1 ELSE 0 END) FROM `+t.name+` WHERE create_time>=?`, since7)
|
||
var s7 sql.NullInt64
|
||
if row4.Scan(&s7) == nil && s7.Valid {
|
||
base := result[talker]
|
||
if base == nil {
|
||
base = &model.IntimacyBase{UserName: talker}
|
||
result[talker] = base
|
||
}
|
||
base.Past7DaysSentMsg += s7.Int64
|
||
}
|
||
}
|
||
|
||
return result, nil
|
||
}
|
||
|
||
func mapV4TypeToLabel(t int64) string {
|
||
// 依据文档统一的消息类型映射
|
||
switch t {
|
||
case 1:
|
||
return "文本消息"
|
||
case 3:
|
||
return "图片消息"
|
||
case 34:
|
||
return "语音消息"
|
||
case 37:
|
||
return "好友验证消息"
|
||
case 42:
|
||
return "好友推荐消息"
|
||
case 47:
|
||
return "聊天表情"
|
||
case 48:
|
||
return "位置消息"
|
||
case 49:
|
||
return "XML消息"
|
||
case 50:
|
||
return "音视频通话"
|
||
case 51:
|
||
return "手机端操作消息"
|
||
case 10000:
|
||
return "系统通知"
|
||
case 10002:
|
||
return "撤回消息"
|
||
default:
|
||
return ""
|
||
}
|
||
}
|
||
|
||
// GroupTodayMessageCounts 统计群聊今日消息数（v4）：通过 chat_room.username 计算 md5 映射到 Msg_ 表，create_time >= 今日零点
|
||
func (ds *DataSource) GroupTodayMessageCounts(ctx context.Context) (map[string]int64, error) {
|
||
result := make(map[string]int64)
|
||
cdb, err := ds.dbm.GetDB(Contact)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
// 获取所有群聊用户名
|
||
urows, err := cdb.QueryContext(ctx, `SELECT username FROM chat_room`)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
var rooms []string
|
||
for urows.Next() {
|
||
var u string
|
||
if err := urows.Scan(&u); err == nil {
|
||
rooms = append(rooms, u)
|
||
}
|
||
}
|
||
urows.Close()
|
||
if len(rooms) == 0 {
|
||
return result, nil
|
||
}
|
||
// 今日零点
|
||
now := time.Now()
|
||
today := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location())
|
||
since := today.Unix()
|
||
// 遍历消息库
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
for _, db := range dbs {
|
||
for _, uname := range rooms {
|
||
md5sum := md5.Sum([]byte(uname))
|
||
tbl := "Msg_" + hex.EncodeToString(md5sum[:])
|
||
// 检查表是否存在
|
||
var name string
|
||
err := db.QueryRowContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name=?`, tbl).Scan(&name)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
// 计数
|
||
q := fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE create_time >= ?`, tbl)
|
||
var cnt int64
|
||
if err := db.QueryRowContext(ctx, q, since).Scan(&cnt); err == nil {
|
||
result[uname] += cnt
|
||
}
|
||
}
|
||
}
|
||
return result, nil
|
||
}
|
||
|
||
// GroupTodayHourly 统计群聊今日按小时消息数（v4）
|
||
func (ds *DataSource) GroupTodayHourly(ctx context.Context) (map[string][24]int64, error) {
|
||
result := make(map[string][24]int64)
|
||
cdb, err := ds.dbm.GetDB(Contact)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
urows, err := cdb.QueryContext(ctx, `SELECT username FROM chat_room`)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
var rooms []string
|
||
for urows.Next() {
|
||
var u string
|
||
if urows.Scan(&u) == nil {
|
||
rooms = append(rooms, u)
|
||
}
|
||
}
|
||
urows.Close()
|
||
if len(rooms) == 0 {
|
||
return result, nil
|
||
}
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
now := time.Now()
|
||
start := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).Unix()
|
||
end := start + 86400
|
||
for _, db := range dbs {
|
||
for _, uname := range rooms {
|
||
md5sum := md5.Sum([]byte(uname))
|
||
tbl := "Msg_" + hex.EncodeToString(md5sum[:])
|
||
var name string
|
||
if err := db.QueryRowContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name=?`, tbl).Scan(&name); err != nil {
|
||
continue
|
||
}
|
||
q := fmt.Sprintf(`SELECT CAST(strftime('%%H', datetime(create_time,'unixepoch')) AS INTEGER) AS h, COUNT(*) FROM %s WHERE create_time >= ? AND create_time < ? GROUP BY h`, tbl)
|
||
rows, err := db.QueryContext(ctx, q, start, end)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
for rows.Next() {
|
||
var hour int
|
||
var cnt int64
|
||
if rows.Scan(&hour, &cnt) == nil {
|
||
if hour >= 0 && hour < 24 {
|
||
bucket := result[uname]
|
||
bucket[hour] += cnt
|
||
result[uname] = bucket
|
||
}
|
||
}
|
||
}
|
||
rows.Close()
|
||
}
|
||
}
|
||
return result, nil
|
||
}
|
||
|
||
// GroupWeekMessageCount 统计本周(周一00:00起至当前)所有群聊消息总数
|
||
// 复用 GroupMessageCounts + 时间过滤会很重，这里直接遍历相关 Msg_ 表做时间范围聚合
|
||
func (ds *DataSource) GroupWeekMessageCount(ctx context.Context) (int64, error) {
|
||
var total int64
|
||
cdb, err := ds.dbm.GetDB(Contact)
|
||
if err != nil {
|
||
return 0, nil
|
||
}
|
||
// 群列表
|
||
urows, err := cdb.QueryContext(ctx, `SELECT username FROM chat_room`)
|
||
if err != nil {
|
||
return 0, nil
|
||
}
|
||
var rooms []string
|
||
for urows.Next() {
|
||
var u string
|
||
if urows.Scan(&u) == nil {
|
||
rooms = append(rooms, u)
|
||
}
|
||
}
|
||
urows.Close()
|
||
if len(rooms) == 0 {
|
||
return 0, nil
|
||
}
|
||
now := time.Now()
|
||
// 计算周一 00:00
|
||
wday := int(now.Weekday()) // Sunday=0
|
||
// 以周一为起点，若是周日(0)则回退6天
|
||
offset := wday - 1
|
||
if wday == 0 {
|
||
offset = -6
|
||
}
|
||
monday := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).AddDate(0, 0, -offset)
|
||
since := monday.Unix()
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return 0, nil
|
||
}
|
||
for _, db := range dbs {
|
||
for _, uname := range rooms {
|
||
md5sum := md5.Sum([]byte(uname))
|
||
tbl := "Msg_" + hex.EncodeToString(md5sum[:])
|
||
var name string
|
||
if err := db.QueryRowContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name=?`, tbl).Scan(&name); err != nil {
|
||
continue
|
||
}
|
||
q := fmt.Sprintf(`SELECT COUNT(*) FROM %s WHERE create_time >= ?`, tbl)
|
||
var cnt int64
|
||
if err := db.QueryRowContext(ctx, q, since).Scan(&cnt); err == nil {
|
||
total += cnt
|
||
}
|
||
}
|
||
}
|
||
return total, nil
|
||
}
|
||
|
||
// GlobalTodayHourly 返回今日(本地时区)每小时全部消息量（v4）
|
||
func (ds *DataSource) GlobalTodayHourly(ctx context.Context) ([24]int64, error) {
|
||
var hours [24]int64
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return hours, nil
|
||
}
|
||
now := time.Now()
|
||
start := time.Date(now.Year(), now.Month(), now.Day(), 0, 0, 0, 0, now.Location()).Unix()
|
||
end := start + 86400
|
||
for _, db := range dbs {
|
||
trows, err := db.QueryContext(ctx, `SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'Msg_%'`)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
var tables []string
|
||
for trows.Next() {
|
||
var name string
|
||
if trows.Scan(&name) == nil {
|
||
tables = append(tables, name)
|
||
}
|
||
}
|
||
trows.Close()
|
||
for _, tbl := range tables {
|
||
q := fmt.Sprintf(`SELECT CAST(strftime('%%H', datetime(create_time,'unixepoch')) AS INTEGER) AS h, COUNT(*) FROM %s WHERE create_time >= ? AND create_time < ? GROUP BY h`, tbl)
|
||
rows, err := db.QueryContext(ctx, q, start, end)
|
||
if err != nil {
|
||
continue
|
||
}
|
||
for rows.Next() {
|
||
var h int
|
||
var cnt int64
|
||
if rows.Scan(&h, &cnt) == nil {
|
||
if h >= 0 && h < 24 {
|
||
hours[h] += cnt
|
||
}
|
||
}
|
||
}
|
||
rows.Close()
|
||
}
|
||
}
|
||
return hours, nil
|
||
}
|
||
|
||
// GroupMessageTypeStats 统计群聊消息类型分布（v4）
|
||
func (ds *DataSource) GroupMessageTypeStats(ctx context.Context) (map[string]int64, error) {
|
||
result := make(map[string]int64)
|
||
cdb, err := ds.dbm.GetDB(Contact)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
urows, err := cdb.QueryContext(ctx, `SELECT username FROM chat_room`)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
var rooms []string
|
||
for urows.Next() {
|
||
var u string
|
||
if urows.Scan(&u) == nil {
|
||
rooms = append(rooms, u)
|
||
}
|
||
}
|
||
urows.Close()
|
||
if len(rooms) == 0 {
|
||
return result, nil
|
||
}
|
||
dbs, err := ds.dbm.GetDBs(Message)
|
||
if err != nil {
|
||
return result, nil
|
||
}
|
||
for _, db := range dbs {
|
||
for _, uname := range rooms {
|
||
md5sum := md5.Sum([]byte(uname))
|
||
tbl := "Msg_" + hex.EncodeToString(md5sum[:])
|
||
// 先统计非49
|
||
q := fmt.Sprintf(`SELECT local_type, COUNT(*) FROM %s WHERE local_type != 49 GROUP BY local_type`, tbl)
|
||
rows, err := db.QueryContext(ctx, q)
|
||
if err == nil {
|
||
for rows.Next() {
|
||
var t int64
|
||
var cnt int64
|
||
if rows.Scan(&t, &cnt) == nil {
|
||
label := mapV4TypeToLabel(t)
|
||
if label != "" {
|
||
result[label] += cnt
|
||
}
|
||
}
|
||
}
|
||
rows.Close()
|
||
}
|
||
// 处理49
|
||
q49 := fmt.Sprintf(`SELECT message_content FROM %s WHERE local_type=49`, tbl)
|
||
orows, err := db.QueryContext(ctx, q49)
|
||
if err == nil {
|
||
for orows.Next() {
|
||
var mc []byte
|
||
if err := orows.Scan(&mc); err == nil {
|
||
lc := strings.ToLower(string(mc))
|
||
if strings.Contains(lc, "<appmsg") {
|
||
if strings.Contains(lc, "<type>") && strings.Contains(lc, "</type>") {
|
||
i1 := strings.Index(lc, "<type>")
|
||
i2 := strings.Index(lc[i1+6:], "</type>")
|
||
if i1 >= 0 && i2 > 0 {
|
||
val := strings.TrimSpace(lc[i1+6 : i1+6+i2])
|
||
if val == "6" {
|
||
result["文件消息"]++
|
||
continue
|
||
}
|
||
if val == "5" || val == "33" {
|
||
result["链接消息"]++
|
||
continue
|
||
}
|
||
}
|
||
}
|
||
}
|
||
if strings.Contains(lc, "http://") || strings.Contains(lc, "https://") {
|
||
result["链接消息"]++
|
||
continue
|
||
}
|
||
result["XML消息"]++
|
||
}
|
||
}
|
||
orows.Close()
|
||
}
|
||
}
|
||
}
|
||
return result, nil
|
||
}
|