使用Sqlite构建服务端简易DBProxy数据库代理

使用Sqlite构建服务端简易DBProxy数据库代理

  目前博主构建的建议数据库代理（DBProxy）是依附于主线程单独开一个线程处理的（注意，这里只开了一个线程，这个线程依附于主线程）。单独开一个线程管理DBProxy，主要目的是不想让存盘（文件操作）的耗时影响到主线程，即我们将存盘操作从同步策略变换为异步策略。

关于Sqlite的封装可参考：https://ufgnix0802.blog.csdn.net/article/details/128192088

关于C++11的线程库封装可参考：https://ufgnix0802.blog.csdn.net/article/details/128069481

关于单例类模块的实现可参考：https://ufgnix0802.blog.csdn.net/article/details/128121759

  以下代码仅供思路，不做演示。

DBProxy.h

#pragma once

class IDBModule {
public:
    IDBModule()          {}
    virtual ~IDBModule() {}

    bool virtual DBExec(U::DB::DBSqlite* pSqlite) = 0;

    bool virtual OnDBExec() = 0;

    bool virtual Release()  = 0;
};


class DBProxy {
    SINGLETON_CLASS_FUNC_DECL(DBProxy)
public:
    int  Init(const char* dbPath);
    bool UnInit();

    bool Start();
    bool Stop();

    //同步初始化场景和商城
    bool GamaServiceSubmoduleConfig(class Scene* scene, class Shop* shop);

    bool PostHandlerEvent(IDBModule* iDBModule);
    bool OnPostHandlerEvent(IDBModule* iDBModule);
private:
    U::DB::DBSqlite   m_sqlite;
    U::THREAD::Thread m_thread;

    //任务队列
    std::mutex              m_mutexWork;
    std::vector<IDBModule*> m_workArr;

    int m_version;  //数据库版本号
};

DBProxy.cpp

#include "DBProxy.h"

SINGLETON_CLASS_IMPL(DBProxy)

//0， 表示存在数据库且数据库代理初始化成功
//1， 表示不存在数据库且数据库代理初始化成功
//-1，表示初始化失败
int DBProxy::Init(const char* dbPath) {
    assert(nullptr != dbPath);
    int   result = -1;
    char* pErr   = nullptr;
    m_version    = 0;

    LOG_TRACE << "初始化数据库";
    if (!m_sqlite.Create(dbPath)) {
        LOG_ERROR << "init m_sqlite err...";
        goto Exit;
    }

    m_sqlite.Exec("select versio from dbver;", &pErr, 
        [&](int   column_size,
            char* column_value[], 
            char* column_name[]) {
        m_version = atoi(column_value[0]);
    });

    //如果数据库不存在，那么创建数据库，读取配置文件进行模块初始化
    //如果数据库存在，  那么同步读取数据库，初始化模块

    //如果不存在数据库，则创建数据库
    if (0 == m_version) {
        LOG_TRACE << "没有找到基础数据库，创建数据库，版本号:100";
        const char *sql = "        \
            CREATE TABLE `dbver` ( \
            `versio` int NULL,     \
            PRIMARY KEY (`versio`) \
            )";
        pErr = NULL;

        m_sqlite.Begin();
        //创建数据库
        m_sqlite.Exec(sql, &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }
        //插入版本号
        m_sqlite.Exec("insert into dbver values ('100');", &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }

        //启动外键
        m_sqlite.Exec("PRAGMA foreign_keys = ON;", &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }

        sql = "                             \
			CREATE TABLE \"Players\" (      \
			    \"PlayerID\" TEXT NOT NULL, \
			    \"Password\" TEXT NOT NULL, \
			    \"CreateTime\" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, \
				PRIMARY KEY(\"PlayerID\")   \
				); \
			CREATE INDEX \"PlayerIndex\" ON \"Players\" (\"PlayerID\"); \
			";

        //创建一张玩家管理表
        m_sqlite.Exec(sql, &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }

        sql = "                                   \
			CREATE TABLE \"Scenes\" (             \
			    \"SceneID\" INTEGER NOT NULL,     \
			    \"Width\"  INTEGER NOT NULL,      \
			    \"Length\" INTEGER NOT NULL,      \
			    \"MarkRoleID\" INTEGER NOT NULL,  \
			    \"CreateTime\" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, \
				PRIMARY KEY(\"SceneID\")          \
				); \
			CREATE INDEX \"SceneIndex\" ON \"Scenes\" (\"SceneID\");         \
			";

        //创建一张场景管理表
        m_sqlite.Exec(sql, &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }

        sql = "                              \
			CREATE TABLE \"Roles\" (         \
			    \"RoleID\" INTEGER NOT NULL, \
			    \"SceneID\" INTEGER NOT NULL,\
			    \"PlayerID\" TEXT NOT NULL,  \
			    \"RotX\" REAL NOT NULL,      \
			    \"RotY\" REAL NOT NULL,      \
			    \"Speed\" INTEGER NOT NULL,  \
                \"PosX\" REAL NOT NULL,      \
			    \"PosY\" REAL NOT NULL,      \
			    \"CreateTime\" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,   \
				PRIMARY KEY(\"RoleID\"),     \
                FOREIGN KEY \(\"PlayerID\"\) REFERENCES Players\(\"PlayerID\"\) \
                FOREIGN KEY \(\"SceneID\"\) REFERENCES Scenes\(\"SceneID\"\) \
				); \
			CREATE INDEX \"RoleIndex\" ON \"Roles\" (\"RoleID\"); \
			";

        //创建一张角色管理表
        m_sqlite.Exec(sql, &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }

        sql = "                              \
			CREATE TABLE \"Bags\" (          \
			    \"RoleID\" INTEGER NOT NULL, \
			    \"Bag\" BLOB NOT NULL,       \
			    \"CreateTime\" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, \
				PRIMARY KEY(\"RoleID\"),     \
                FOREIGN KEY \(\"RoleID\"\) REFERENCES Roles\(\"RoleID\"\)    \
				); \
			CREATE INDEX \"BagIndex\" ON \"Bags\" (\"RoleID\"); \
			";
        //创建一张背包管理表
        m_sqlite.Exec(sql, &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }

        sql = "                               \
			CREATE TABLE \"RoleMoneys\" (     \
			    \"RoleID\" INTEGER NOT NULL,  \
			    \"Money\"  INTEGER NOT NULL,  \
			    \"CreateTime\" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, \
				PRIMARY KEY(\"RoleID\"),      \
                FOREIGN KEY \(\"RoleID\"\) REFERENCES Roles\(\"RoleID\"\)    \
				); \
			CREATE INDEX \"MoneyIndex\" ON \"RoleMoneys\" (\"RoleID\");      \
			";
        //创建一张角色金钱管理表
        m_sqlite.Exec(sql, &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }

        sql = "                                \
			CREATE TABLE \"Shops\" (           \
			    \"ShopID\" INTEGER NOT NULL,   \
			    \"Shop\" BLOB NOT NULL,        \
			    \"CreateTime\" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, \
				PRIMARY KEY(\"ShopID\")        \
				); \
			CREATE INDEX \"ShopIndex\" ON \"Shops\" (\"ShopID\");            \
			";
        //创建一张商城管理表
        m_sqlite.Exec(sql, &pErr);
        if (NULL != pErr) {
            LOG_ERROR << pErr;
            pErr = NULL;
            goto Exit;
        }
        m_sqlite.Commit();

        //设置版本号
        m_version = 100;
        result    = 1;
    }
    else {
        result    = 0;
    }

    LOG_TRACE << "当前数据库版本号:" << m_version;

    if (!m_thread.Init()) {
        goto Exit;
    }

    m_workArr.clear();
    LOG_TRACE << "DB sqlite init success...";
Exit:
    return result;
}

bool DBProxy::UnInit() {
    if (!m_sqlite.UnInit()) {
        return false;
    }

    m_thread.UnInit();
    m_workArr.clear();
    return true;
}

bool DBProxy::Start() {
    LOG_TRACE << "启动数据库线程";
    m_thread.Start(nullptr, [this](U::THREAD::Thread* pThread) {
        while (pThread->IsRun()) {
            //处理任务队列
            if (m_workArr.size() > 0) {
                std::vector<IDBModule*> workArr;
                do {
                    std::lock_guard<std::mutex> __(m_mutexWork);
                    m_workArr.swap(workArr);
                    m_workArr.clear();
                } while (false);

                for (auto& work : workArr) {
                    OnPostHandlerEvent(work);
                }
            }
            //else {
            //    //休息1ms
            //    pThread->Wait();
            //}
        }
    });
    return false;
}

bool DBProxy::Stop() {
    m_sqlite.Destroy();
    m_thread.Close();
    return true;
}

bool DBProxy::GamaServiceSubmoduleConfig(class Scene* scene,
    class Shop* shop) {
    assert(nullptr != scene);
    assert(nullptr != shop);

    char  sql[1024] = { 0 };
    char* pError    = NULL;
    bool  result    = false;

    //数据库同步读，初始化场景及商城
    sprintf(sql, "SELECT * FROM Scenes WHERE SceneID = %d;", 1);
    m_sqlite.Begin();
    //查询场景表
    m_sqlite.Exec(sql, &pError, [&scene](int column_size, char* column_value[],
        char* column_name[]) {
        scene->Init(
            atoi(column_value[1]),
            atoi(column_value[2]),
            atoi(column_value[3]));
    });

    if (NULL != pError) {
        NET_UV_LOG_ERROR("%s\n", pError);
        pError = NULL;
        goto Exit;
    }

    sprintf(sql, "SELECT * FROM Shops WHERE ShopID = %d;", 1);
    m_sqlite.Exec(sql, &pError, [&shop](int column_size, char* column_value[],
        char* column_name[]) {
        TCCamp::ShopSaveData saveData;
        TCCamp::PBShopItem   pbShopItem;

        ShopItem* shopItem = nullptr;

        saveData.ParseFromArray(column_value[1], strlen(column_value[1]));
        for (int index = 0; index < saveData.items().size(); index++) {
            pbShopItem = saveData.items(index);
            shopItem = (ShopItem*)MALLOC(sizeof(ShopItem));
            new(shopItem) ShopItem();
            shopItem->Init(
                pbShopItem.id(),
                pbShopItem.price(),
                pbShopItem.personallimitmaxcount(),
                pbShopItem.globallimitcount(),
                pbShopItem.globallimitmaxcount(),
                pbShopItem.starttime(),
                pbShopItem.endtime());
            shop->AddShopItem(shopItem);
        }
    });

    if (NULL != pError) {
        NET_UV_LOG_ERROR("%s\n", pError);
        pError = NULL;
        goto Exit;
    }
    m_sqlite.Commit();

    result = true;
Exit:
    return result;
}

bool DBProxy::PostHandlerEvent(IDBModule* iDBModule) {
    std::lock_guard<std::mutex> __(m_mutexWork);
    m_workArr.push_back(iDBModule);
    return true;
}

bool DBProxy::OnPostHandlerEvent(IDBModule* iDBModule) {
    iDBModule->DBExec(&m_sqlite);
    //iDBModule->Release();
    return true;
}

使用实例（这里构建一个DBLogin，即登录数据库操作模块）

//登录DB
class DBLogin : public IDBModule {
public:
    bool Init(ReqEvent* req, GameService* gameService, PlayerMgr* playerMgr,
        std::string playerID, std::string pwd) {
        m_reqEvent    = req;
        m_gameService = gameService;
        m_playerMgr   = playerMgr;

        m_playerID = playerID;
        m_pwd      = pwd;

        m_count = 2;
        m_code  = TCCamp::PB_RESULT_CODE::SUCCESS;
        return true;
    }

    bool UnInit() {
        m_reqEvent    = nullptr;
        m_gameService = nullptr;
        m_playerMgr   = nullptr;
        return true;
    }

    // 通过 IDBModule 继承
    // 子线程
    virtual bool DBExec(U::DB::DBSqlite* pSqlite) override {
        bool result = false;
        int  index  = 0;

        std::string selectPwd;
        bool bFind = false;

        U::DB::SQLITE3_RESULT_CODE nRet;

        char  sql[1024] = { 0 };
        char* pError    = NULL;

        if (nullptr == m_reqEvent->TCP_SESSION) {
            goto Exit;
        }

        m_reqEvent->CUR_STATUS = ReqEvent::TRACE_STATUS::QUERYING;
        //查询玩家表
        sprintf(sql
            , "SELECT PlayerID, Password FROM Players WHERE PlayerID = '%s';"
            , m_playerID.c_str());
        
        nRet = pSqlite->Exec(sql, &pError
            , [this, &selectPwd, &bFind](int column_size, char* column_value[],
                char* column_name[]) {
            bFind = true;
            selectPwd = column_value[1];
        });

        if (NULL != pError) {
            NET_UV_LOG_ERROR("%s\n", pError);
            pError = NULL;
            m_code = TCCamp::PB_RESULT_CODE::SERVER_ERROR;
            goto Exit;
        }

        if (!bFind) {
            m_code = TCCamp::PB_RESULT_CODE::LOGIN_ACCOUNT_NOT_EXIST;
            goto Exit;
        }

        //判断密码是否正确
        if (m_pwd != selectPwd) {
            m_code = TCCamp::PB_RESULT_CODE::LOGIN_PASSWORD_ERROR;
            goto Exit;
        }

        //正确则初始化角色列表
        //查询角色表
        sprintf(sql
            , "SELECT RoleID FROM Roles WHERE PlayerID = '%s';"
            , m_playerID.c_str());

        nRet = pSqlite->Exec(sql, &pError
            , [this](int column_size, char* column_value[],
                char* column_name[]) {
            m_roleArrs.push_back(atoi(column_value[0]));
        });

        if (NULL != pError) {
            NET_UV_LOG_ERROR("%s\n", pError);
            pError = NULL;
            m_code = TCCamp::PB_RESULT_CODE::SERVER_ERROR;
            goto Exit;
        }
        result = true;
    Exit:
        m_reqEvent->CUR_STATUS = ReqEvent::TRACE_STATUS::FINISH;
        //投递至GameService主线程
        m_gameService->PostMsg(POST_COM_KEY::PCK_DB_OPERATION, this);
        return result;
    }

    // 主线程
    virtual bool OnDBExec() override {
        //将player添加至playerMgr中
        //清理cache缓冲队列
        //回应客户端
        if (m_reqEvent->TCP_SESSION) {
            TCCamp::AccountLoginRsp rsp;
            rsp.set_result(m_code);
            if (m_code == TCCamp::PB_RESULT_CODE::SUCCESS) {
                Player* player = (Player*)MALLOC(sizeof(Player));
                new(player) Player();
                assert(nullptr != player);
                player->Init(m_playerID, m_pwd, m_reqEvent->TCP_SESSION);

                for (auto& roleID : m_roleArrs) {
                    player->AddRole(roleID);
                }

                m_playerMgr->AddPlayer(player);
            }

            LOG_INFO << "playerID:" << m_playerID << " login...";
            m_reqEvent->TCP_SESSION->Send(TCCamp::SERVER_CMD::SERVER_LOGIN_RSP,
                &rsp);
        }
        m_playerMgr->RemoveReqEvent(m_reqEvent);
        return true;
    }

    virtual bool Release()  override {
        /*--m_count;
        if (m_count == 0) {
            UnInit();
            delete this;
        }*/

        UnInit();
        delete this;
        return true;
    }

private:
    ReqEvent*    m_reqEvent;
    GameService* m_gameService;
    PlayerMgr*   m_playerMgr;

    std::string  m_playerID;
    std::string  m_pwd;

    std::vector<int64_t> m_roleArrs;

    int  m_count;
    TCCamp::PB_RESULT_CODE m_code;
};

int main() {
    DBLogin*  dbLogin = nullptr;
    
    dbLogin = new DBLogin();
    dbLogin->Init(req, m_gameService, this, 
        loginReq->accountid(), 
        loginReq->password());

    //抛至数据库代理
    DBProxy::Ins().PostHandlerEvent(dbLogin);
    return 0;
}