codebase/source/db.hpp

#ifndef __DB_HPP__
#define __DB_HPP__
#include "util.hpp"
#include <mutex>
#include <cassert>

/*用户数据管理模块 -- 用于管理数据库数据，为数据库中的每张表都设计一个类，然后通过类对象来操作数据库表中的数据*/
/*用户信息表*/
class user_table {
public:
    user_table(const std::string &host, const std::string &user, const std::string &passwd, \
                               const std::string db = "gobang", uint16_t port = 4106) 
    {
        _mysql = mysql_util::mysql_create(host, user, passwd, db, port);
        assert(_mysql != nullptr);
        LOG(DEBUG, "用户数据管理模块初识化完毕");
    }

    ~user_table() {
        if(_mysql != nullptr) { 
            mysql_util::mysql_destroy(_mysql);
            _mysql = nullptr;
        }
        LOG(DEBUG, "用户数据管理模块已被销毁");
    }

    /*新用户注册*/
    bool registers(Json::Value &user) {
        if(user["username"].isNull() || user["password"].isNull()) {
            LOG(NORMAL, "please input username and password");
            return false;
        }
        // 由于用户名有唯一键约束，所以不需要担心用户已被注册的情况
        char sql[1024];
#define INSERT_USER "insert into user values(null, '%s', password('%s'), 1000, 0, 0)"
        sprintf(sql, INSERT_USER, user["username"].asCString(), user["password"].asCString());
        // LOG(DEBUG, "%s", sql);
        if(mysql_util::mysql_execute(_mysql, sql) == false) {
            LOG(NORMAL, "user register failed");
            return false;
        }
        LOG(NORMAL, "%s register success", user["username"].asCString());
        return true;
    }

    /*用户登录验证*/
    bool login(Json::Value &user) {
        // 与数据库中的用户名+密码进行比对
        // 注意：数据库的password是经过mysql password函数转换后的，所以sql查询时也需要对user["password"].asString()进行转化
#define SELECT_USER "select id, score, total_count, win_count from user where username = '%s' and password = password('%s')"   
        char sql[1024];
        sprintf(sql, SELECT_USER, user["username"].asCString(), user["password"].asCString());
        MYSQL_RES *res = nullptr;
        {
            // mysql查询与查询结果的本地保存两步操作需要加锁，避免多线程使用同一句柄进行操作的情况下发送结果集的数据覆盖问题
            // 将锁交给RAII unique_lock进行管理
            std::unique_lock<std::mutex> lock(_mutex);
            if(mysql_util::mysql_execute(_mysql, sql) == false) return false;;
            // 获取查询到的结果--一行记录
            res = mysql_store_result(_mysql);
            // 注意：当mysql查询结果为空时，mysql_store_result也不会返回空，所以不能在这里判断用户名密码是否正确
            if(res == nullptr) {
                LOG(NORMAL, "mysql store failed: ", mysql_error(_mysql));
                return false;
            }
        }
        int row_count = mysql_num_rows(res);
        int col_count = mysql_num_fields(res);
        // row_count 为0说明查询不到与当前用户名+密码匹配的数据，即用户名或密码错误
        if(row_count == 0) {
            LOG(NORMAL, "the username or password error, please input again");
            return false;
        }
        // 用户名存在唯一键约束
        if(row_count > 1) {
            LOG(ERROR, "there are same user %s in the database", user["username"].asCString());
            return false;
        }        
        LOG(NORMAL, "%s login success", user["username"].asCString());
        // 填充该用户的其他详细信息
        MYSQL_ROW row = mysql_fetch_row(res);
        user["id"] = std::stoi(row[0]);
        user["score"] = std::stoi(row[1]);
        user["total_count"] = std::stoi(row[2]);
        user["win_count"] = std::stoi(row[3]); 
        mysql_free_result(res);       
        return true;
    }

    /*使用用户名查找用户的详细信息*/
    bool select_by_name(const std::string &name, Json::Value &user) {
#define SELECT_BY_USERNAME "select id, score, total_count, win_count from user where username = '%s'"
        char sql[1024];
        sprintf(sql, SELECT_BY_USERNAME, name.c_str());
        MYSQL_RES *res = nullptr;
        {
            // 加锁
            std::unique_lock<std::mutex> lock(_mutex);
            if(mysql_util::mysql_execute(_mysql, sql) == false) return false;
            // 获取查询到的结果--一行记录
            res = mysql_store_result(_mysql);
            // 注意：当mysql查询结果为空时，mysql_store_result也不会返回空，所以不能在这里判断用户是否存在
            if(res == nullptr) {
                LOG(NORMAL, "mysql store failed: ", mysql_error(_mysql));
                return false;
            }
        }
        int row_count = mysql_num_rows(res);
        int col_count = mysql_num_fields(res);
        // row_count为0说明查询不到与当前用户名匹配的数据，即用户不存在
        if(row_count == 0) {
            LOG(NORMAL, "the user with name %s does not exist", name.c_str());
            return false;
        }
        // 用户名存在唯一键约束
        if(row_count > 1) {
            LOG(ERROR, "there are same user name %s in the database", name.c_str());
            return false;
        }  
        MYSQL_ROW row = mysql_fetch_row(res);
        // password是转换后的，获取无意义
        user["id"] = std::stoi(row[0]);
        user["username"] = name.c_str();
        user["score"] = std::stoi(row[1]);
        user["total_count"] = std::stoi(row[2]);
        user["win_count"] = std::stoi(row[3]);
        mysql_free_result(res);
        return true;
    }

    /*使用用户ID查找用户的详细信息*/
    bool select_by_id(uint64_t id, Json::Value &user) {
#define SELECT_BY_ID "select username, score, total_count, win_count from user where id = %d"
        char sql[1024];
        sprintf(sql, SELECT_BY_ID, id);
        MYSQL_RES *res = nullptr;
        {
            // 加锁
            std::unique_lock<std::mutex> lock(_mutex);
            if(mysql_util::mysql_execute(_mysql, sql) == false) return false;
            // 获取查询到的结果--一行记录
            res = mysql_store_result(_mysql);
            // 注意：当mysql查询结果为空时，mysql_store_result也不会返回空，所以不能在这里判断用户是否存在
            if(res == nullptr) {
                LOG(NORMAL, "mysql store failed: ", mysql_error(_mysql));
                return false;
            }
        }
        int row_count = mysql_num_rows(res);
        int col_count = mysql_num_fields(res);
        // row_count为0说明查询不到与当前用户名ID匹配的数据，即用户不存在
        if(row_count == 0) {
            LOG(NORMAL, "the user with ID %d does not exist", id);
            return false;
        }
        // 用户名存在唯一键约束
        if(row_count > 1) {
            LOG(ERROR, "there are same user with ID %d in the database", id);
            return false;
        }
        MYSQL_ROW row = mysql_fetch_row(res);
        // password是转换后的，获取无意义
        user["id"] = (Json::UInt64)id;
        user["username"] = row[0];
        user["score"] = std::stoi(row[1]);
        user["total_count"] = std::stoi(row[2]);
        user["win_count"] = std::stoi(row[3]);
        mysql_free_result(res);
        return true;        
    }

    /*用户对战胜利，修改分数以及比赛和胜利场次，胜利一场增加30分*/
    bool win(uint64_t id) {
#define UPDATE_WIN "update user set score=score+30, total_count=total_count+1, win_count=win_count+1 where id = %d"
        char sql[1024];
        sprintf(sql, UPDATE_WIN, id);
        if(mysql_util::mysql_execute(_mysql, sql) == false) {
            LOG(ERROR, "update the user info of win failed");
            return false;
        }
        return true;
    }

    /*用户对战失败，修改分数以及比赛场次*，失败一场减少30分*/
    bool lose(uint64_t id) {
#define UPDATE_LOSE "update user set score=score-30, total_count=total_count+1 where id = %d"
        char sql[1024];
        sprintf(sql, UPDATE_LOSE, id);
        if(mysql_util::mysql_execute(_mysql, sql) == false) {
            LOG(ERROR, "update the user info of lose failed");
            return false;
        }
        return true;
    }
private:
    MYSQL *_mysql;     //mysql操作句柄
    std::mutex _mutex;  //解决多线程使用同一类对象(句柄)访问数据库时可能发生的线程安全问题
};
#endif