聊天服务器——数据库模块

数据库模块

sqlite3前缀知识

1:创建表

CREATE TABLE IF NOT EXISTS mytable (id integer primary key,name text);
​
​
CREATE TABLE IF NOT EXISTS [tb_BuddyMsg] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [uin] INTEGER, [nickname] TEXT, [time] INTEGER, [sendflag] INTEGER, [content] TEXT)

2：插入数据

INSERT INTO mytable (id,name) values (1000, "zhangsan");

3：高效的数据绑定

因为sqlite3_exec执行效率低，而且只能执行单一的语句。因此我们采用

sqlite3_prepare()

sqlite3_stmt()

sqlite3_bind()

sqlite3_step()

采用这四个方式来结构化执行数据库操作

创建消息记录文件

先是使用sqlite3_open16打开数据库，

然后在数据库里创建三个表，分别是好友消息、群消息和临时会话消息表

如果创建失败，就退出数据库，并删除对应的文件

// 创建消息记录文件
BOOL CMessageLogger::CreateMsgLogFile()
{
    sqlite3*conn = NULL;
    char*err_msg = NULL;
    char* lpSql;
    int nRet;
​
    nRet = sqlite3_open16(m_strFileName.c_str(), &conn);
    if (nRet != SQLITE_OK)
        return FALSE;
​
    lpSql = "CREATE TABLE IF NOT EXISTS [tb_BuddyMsg] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [uin] INTEGER, [nickname] TEXT, [time] INTEGER, [sendflag] INTEGER, [content] TEXT)";
    nRet = sqlite3_exec(conn, lpSql, 0, 0, &err_msg);   // 创建好友消息表
    if (nRet != SQLITE_OK)
    {
        sqlite3_close(conn);
        ::DeleteFile(m_strFileName.c_str());
        return FALSE;
    }
​
    lpSql = "CREATE TABLE IF NOT EXISTS [tb_GroupMsg] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [groupnum] INTEGER, [uin] INTEGER, [nickname] TEXT, [time] INTEGER, [content] TEXT)";
    nRet = sqlite3_exec(conn, lpSql, 0, 0, &err_msg);   // 创建群消息表
    if (nRet != SQLITE_OK)
    {
        sqlite3_close(conn);
        ::DeleteFile(m_strFileName.c_str());
        return FALSE;
    }
​
    lpSql = "CREATE TABLE IF NOT EXISTS [tb_SessMsg] ([id] INTEGER PRIMARY KEY AUTOINCREMENT, [uin] INTEGER, [nickname] TEXT, [time] INTEGER, [sendflag] INTEGER, [content] TEXT)";
    nRet = sqlite3_exec(conn, lpSql, 0, 0, &err_msg);   // 创建临时会话(群成员)消息表
    if (nRet != SQLITE_OK)
    {
        sqlite3_close(conn);
        ::DeleteFile(m_strFileName.c_str());
        return FALSE;
    }
​
    sqlite3_close(conn);
    return TRUE;
}

写入消息

以好友消息为例，就是传入好友消息结构体对应的几个数据，生成一个结构体

有一个函数专门负责把结构体里的数据插入到数据库的表里

下面这个函数，就是把好友消息的几个参数传进去，然后把这几个参数打包成结构体，最后传到另一个WriteBuddyMsgLog函数里。

BOOL CMessageLogger::WriteBuddyMsgLog(UINT nUTalkNum, LPCTSTR lpNickName,
                                      UINT64 nTime, BOOL bSendFlag, LPCTSTR lpContent)
{
    BUDDY_MSG_LOG stMsgLog = {0};
    stMsgLog.nUTalkNum = nUTalkNum;
    if (lpNickName != NULL)
        stMsgLog.strNickName = lpNickName;
    stMsgLog.nTime = nTime;
    stMsgLog.bSendFlag = bSendFlag;
    if (lpContent != NULL)
        stMsgLog.strContent = lpContent;
    return WriteBuddyMsgLog(&stMsgLog);
}

下面这个函数就是传入好友消息结构体，然后把结构体里的数据插入到数据库里

sqlite3_stmt*

sqlite3_open16

sqlite3_prepare

sqlite3_bind

sqlite3_step

// 写入一条好友消息记录
BOOL CMessageLogger::WriteBuddyMsgLog(BUDDY_MSG_LOG* lpMsgLog)
{
    sqlite3*conn = NULL;
    sqlite3_stmt*stmt = NULL;
    const char*err_msg = NULL;
    char* lpSql = "INSERT INTO [tb_BuddyMsg] ([id],[uin],[nickname],[time],[sendflag],[content]) VALUES (NULL,?,?,?,?,?)";
    int nRet;
​
    if (NULL == lpMsgLog)
        return FALSE;
​
    nRet = sqlite3_open16(m_strFileName.c_str(), &conn);
    if (nRet != SQLITE_OK)
        return FALSE;
​
    nRet = sqlite3_prepare(conn, lpSql, -1, &stmt, &err_msg);
    if (nRet != SQLITE_OK)
    {
        sqlite3_close(conn);
        return FALSE;
    }
​
    sqlite3_bind_int(stmt, 1, lpMsgLog->nUTalkNum);
    sqlite3_bind_text16(stmt, 2, lpMsgLog->strNickName.c_str(), -1, SQLITE_STATIC);
    //暂且这么转换一下,lpMsgLog->nTime可能不对！
    sqlite3_bind_int64(stmt, 3, lpMsgLog->nTime);
    sqlite3_bind_int(stmt, 4, lpMsgLog->bSendFlag);
    sqlite3_bind_text16(stmt, 5, lpMsgLog->strContent.c_str(), -1, SQLITE_STATIC);
​
    nRet = sqlite3_step(stmt);
​
    sqlite3_finalize(stmt);
    sqlite3_close(conn);
​
    return (nRet != SQLITE_DONE) ? FALSE : TRUE;
}

读数据

以读取好友消息记录为例，有传入一个vector的引用，当筛选出好友的消息后，就放入到vector里。

// 读出一条或多条好友消息记录
UINT CMessageLogger::ReadBuddyMsgLog(UINT nUTalkNum, UINT nOffset, UINT nRows, 
                                     std::vector<BUDDY_MSG_LOG*>& arrMsgLog)
{
    sqlite3*conn = NULL;
    sqlite3_stmt*stmt = NULL;
    const char*err_msg = NULL;
    char* lpSql;
    int nRet;
​
    UINT nMsgCnt = ReadBuddyMsgLogCount(nUTalkNum, nOffset, nRows);
    if (nMsgCnt <= 0)
        return 0;
​
    nRet = sqlite3_open16(m_strFileName.c_str(), &conn);
    if (nRet != SQLITE_OK)
        return 0;
​
    if (0 == nOffset && 0 == nRows)
        lpSql = "SELECT* FROM [tb_BuddyMsg] WHERE [uin]=? ORDER BY [time]";
    else
        lpSql = "SELECT* FROM [tb_BuddyMsg] WHERE [uin]=? ORDER BY [time] LIMIT ?,?";
​
    nRet = sqlite3_prepare(conn, lpSql, -1, &stmt, &err_msg);
    if (nRet != SQLITE_OK)
    {
        sqlite3_close(conn);
        return 0;
    }
​
    if (0 == nOffset && 0 == nRows)
        sqlite3_bind_int(stmt, 1, nUTalkNum);
    else
    {
        sqlite3_bind_int(stmt, 1, nUTalkNum);
        sqlite3_bind_int(stmt, 2, nOffset);
        sqlite3_bind_int(stmt, 3, nRows);
    }
​
    while (SQLITE_ROW == sqlite3_step(stmt))
    {
        int nCols = sqlite3_column_count(stmt);
        if (nCols != 6)
            continue;
​
        BUDDY_MSG_LOG* lpMsgLog = new BUDDY_MSG_LOG;
        if (NULL == lpMsgLog)
            continue;
​
        lpMsgLog->nID = sqlite3_column_int(stmt, 0);
        lpMsgLog->nUTalkNum = sqlite3_column_int(stmt, 1);
        const wchar_t* lpNickName = (const wchar_t*)sqlite3_column_text16(stmt, 2);
        if (lpNickName != NULL)
            lpMsgLog->strNickName = lpNickName;
        lpMsgLog->nTime = sqlite3_column_int64(stmt, 3);
        lpMsgLog->bSendFlag = sqlite3_column_int(stmt, 4);
        const wchar_t* lpContent = (const wchar_t*)sqlite3_column_text16(stmt, 5);
        if (lpContent != NULL)
            lpMsgLog->strContent = lpContent;
​
        arrMsgLog.push_back(lpMsgLog);
    }
​
    sqlite3_finalize(stmt);
    sqlite3_close(conn);
​
    return arrMsgLog.size();
}

获取记录数

以好友消息记录数为例，使用数据COUNT语法读取好友消息的总数。

// 获取好友消息记录数
UINT CMessageLogger::GetBuddyMsgLogCount(UINT nUTalkNum)
{
    sqlite3*conn = NULL;
    sqlite3_stmt*stmt = NULL;
    const char*err_msg = NULL;
    char* lpSql = "SELECT COUNT(*) FROM [tb_BuddyMsg] WHERE [uin]=?";
    int nRet;
​
    nRet = sqlite3_open16(m_strFileName.c_str(), &conn);
    if (nRet != SQLITE_OK)
        return 0;
​
    nRet = sqlite3_prepare(conn, lpSql, -1, &stmt, &err_msg);
    if (nRet != SQLITE_OK)
    {
        sqlite3_close(conn);
        return 0;
    }
​
    sqlite3_bind_int(stmt, 1, nUTalkNum);
​
    nRet = sqlite3_step(stmt);
    if (nRet != SQLITE_ROW)
    {
        sqlite3_finalize(stmt);
        sqlite3_close(conn);
        return 0;
    }
​
    UINT nCount = sqlite3_column_int(stmt, 0);
​
    sqlite3_finalize(stmt);
    sqlite3_close(conn);
​
    return nCount;
}

删除

删除的话就是使用Delete