box-server/dev/modules.lua

--[[
Descripttion:
version:
Author: Neo,Huang
Date: 2022-07-04 19:29:32
LastEditors: Neo,Huang
LastEditTime: 2022-07-04 19:30:06
--]]
local dataMode = require("dataMode")
local lib_game_mysql = require("lib_game_mysql")

local constDb = require("db")

local root = {}

-- 模块表是否已存在
local function l_is_module_table_exist(mdlName)
    local tbName = string.format("mdl_%s", tostring(mdlName))
    local sql =
        string.format(
        "SELECT table_name FROM information_schema.TABLES WHERE TABLE_SCHEMA='%s' and table_name ='%s'",
        constDb["mysql"]["game"]["database"],
        tbName
    )
    local result = lib_game_mysql:query(sql)
    -- log.info("l_is_module_table_exist sql[%s] result[%s]", sql, tostring(result))
    if result and #result > 0 then
        return true
    end
    return false
end
-- 创建模块表
local function l_create_module_table(mdlName, columnNameOptions, keyName, secondaryKeys)
    local tbName = string.format("mdl_%s", tostring(mdlName))
    local columns = ""
    for k, v in pairs(columnNameOptions) do
        columns = string.format("%s %s %s,", columns, k, v)
    end
    local sql =
        string.format(
        "CREATE TABLE %s (%s PRIMARY KEY (%s)) DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_unicode_ci;",
        tbName,
        columns,
        keyName
    )
    if not is_empty(secondaryKeys) then
        sql =
            string.format(
            "CREATE TABLE %s (%s PRIMARY KEY (%s), %s) DEFAULT CHARSET=utf8mb4 COLLATE = utf8mb4_unicode_ci;",
            tbName,
            columns,
            keyName,
            secondaryKeys
        )
    end
    local ret = lib_game_mysql:query(sql)
    log.info("l_create_module_table sql[%s] ret[%s]", sql, tostring(ret))
    return true
end
-- 更新模块表字段
local function l_modify_module_table(mdlName, columnNameOptions)
    local tbName = string.format("mdl_%s", tostring(mdlName))
    -- 更新字段
    local mapColumns = {}
    local sql =
        string.format(
        "select COLUMN_NAME, IS_NULLABLE, COLUMN_TYPE, DATA_TYPE, COLUMN_COMMENT, COLUMN_DEFAULT from information_schema.columns where table_schema = '%s' and table_name = '%s'",
        constDb["mysql"]["game"]["database"],
        tbName
    )
    local result = lib_game_mysql:query(sql)
    if result then
        for _, v in pairs(result) do
            assert(v["COLUMN_NAME"], cjson_encode(v))
            local column_name = v["COLUMN_NAME"]
            mapColumns[column_name] = true
        end
    end

    -- add column
    local columns = ""
    for k, v in pairs(columnNameOptions) do
        if not mapColumns[k] then
            if not is_empty(columns) then
                columns = columns .. ","
            end
            columns = string.format("%s %s %s", columns, k, v)
        end
    end
    if not is_empty(columns) then
        sql = string.format("alter table %s add %s;", tbName, columns)
        lib_game_mysql:query(sql)
    end
end

-- 初始数据库模块表
function root:init_db_tables()
    for i, v in pairs(dataMode.get_module_class_map()) do
        local obj = v.new()
        local data = obj:mysql_get_table_info()
        if data and not is_empty(data.mdlName) then
            if not l_is_module_table_exist(data.mdlName) then
                l_create_module_table(data.mdlName, data.columnNameOptions, data.keyName, data.secondaryKeys)
            else
                l_modify_module_table(data.mdlName, data.columnNameOptions)
            end
        end
    end
end

return root