box-server/common/utils/mysqlUtil.lua

--[[
Descripttion:
version:
Author: Neo,Huang
Date: 2022-08-24 20:08:31
LastEditors: Neo,Huang
LastEditTime: 2022-08-24 20:09:12
--]]
local lib_game_mysql = require("lib_game_mysql")
local constDb = require("db")
local sqlUtil = require("utils.sqlUtil")

local root = {}

function root:query(sql)
    return lib_game_mysql:query(sql)
end
-- 插入数据
function root:insert(sql)
    if is_empty(sql) then
        return false
    end
    local ret = lib_game_mysql:query(sql)
    if ret.errno and ret.errno > 0 then
        log.error("数据库[插入数据]错误! sql[%s] ret[%s]", tostring(sql), tostring(ret))
        return false
    end
    return true, ret
end
-- 替换数据
function root:replace(sql)
    if is_empty(sql) then
        return false
    end
    local ret = lib_game_mysql:query(sql)
    if ret.errno and ret.errno > 0 then
        log.error("数据库[替换数据]错误! sql[%s] ret[%s]", tostring(sql), tostring(ret))
        return false
    end
    return true, ret
end
-- 查询数据
function root:select(sql)
    if is_empty(sql) then
        return false
    end
    local ret = lib_game_mysql:query(sql)
    if ret.errno and ret.errno > 0 then
        log.error("数据库[查询数据]错误! sql[%s] ret[%s]", tostring(sql), tostring(ret))
        return false
    end
    return true, ret
end
-- 更新数据
function root:update(sql)
    if is_empty(sql) then
        return false
    end
    local ret = lib_game_mysql:query(sql)
    if ret.errno and ret.errno > 0 then
        log.error("数据库[更新数据]错误! sql[%s] ret[%s]", tostring(sql), tostring(ret))
        return false
    end
    return true, ret
end
-- 删除数据
function root:delete(sql)
    if is_empty(sql) then
        return false
    end
    local ret = lib_game_mysql:query(sql)
    if ret.errno and ret.errno > 0 then
        log.error("数据库[删除数据]错误! sql[%s] ret[%s]", tostring(sql), tostring(ret))
        return false
    end
    return true, ret
end

local function l_add_mysql_column(columnName, module)
    local sql = sqlUtil.alter_table_add_column(module.tabName, columnName, module.columnNameOptions[columnName])
    local result = lib_game_mysql:query(sql)
    if result.errno then
        log.error("addColumnName fatal err, sql:%s err[%s]", sql, result.err)
    end
end
-- 初始模块
function root:init_module_table(module)
    if not module.isSaveDB or is_empty(module.columnNameOptions) then
        return
    end
    -- create table
    local sql =
        string.format(
        "SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s' and table_name ='%s'",
        constDb["mysql"]["game"]["database"],
        module.tabName
    )
    local result = lib_game_mysql:query(sql)
    if result == nil or #result == 0 then
        sql = sqlUtil.create_table(module.tabName, module.columnNameOptions, module.keyName, module.secondaryKeys)
        local ret = lib_game_mysql:query(sql)
        if ret.errno and ret.errno > 0 then
            log.error("数据库[初始模块]错误! sql[%s] ret[%s]", tostring(sql), tostring(ret))
            return false
        end
        return
    end

    -- get table Options
    local tableColumns = {}
    sql = sqlUtil.get_able_column_options(constDb["mysql"]["game"]["database"], module.tabName)
    result = lib_game_mysql:query(sql)
    if result then
        for i, v in pairs(result) do
            assert(v["COLUMN_NAME"], cjson_encode(v))
            local column_name = v["COLUMN_NAME"]
            tableColumns[column_name] = true
        end
    end

    -- add column
    for key, v in pairs(module.columnNameOptions) do
        if not tableColumns[key] then
            l_add_mysql_column(key, module)
        end
    end
end

return root