src/shared/ModelBaseMysql.lua

local ModelBaseMysql = class("ModelBaseMysql")
ModelBaseMysql.key = "key"
ModelBaseMysql.schema = {}
local mysqlproxy = require "shared.mysqlproxy"
local string_format = string.format
local table_insert = table.insert
local table_unpack = table.unpack
local assert = assert
local next = next
local ipairs = ipairs
local pairs = pairs
local tostring = tostring
local tonumber = tonumber
local mysqlproxy = mysqlproxy
local function filterProperties(properties, filter)
    for i, field in ipairs(filter) do
        properties[field] = nil
    end
end
function ModelBaseMysql:ctor(properties)
    self.cacheFields = {} --缓存字段 不更新数据库的字段
    self[self.class.key .. "_"] = properties[self.class.key] --数据库key
    properties[self.class.key] = nil
    if not self:isValidKey() then
        print(string_format("%s [%s:key] should be give in new(ctor)", tostring(self), self.class.__cname))
        return
    end
    if type(properties) ~= "table" then properties = {} end
    self:loadProperties(properties)  --缺少的域将设置默认值
    self:getPriKey()
end
--[[--
返回对象的 ID 值。
**Returns:**
-   ID 值
]]
function ModelBaseMysql:getKey()
    local id = self[self.class.key .. "_"]
    assert(id ~= nil, string_format("%s [%s:getKey()] Invalid key", tostring(self), self.class.__cname))
    return id
end
function ModelBaseMysql:getPriKey()
    for k, v in pairs(self.class.schema) do
        local objType, def, keyType, length = table_unpack(v)
        if keyType == "pri" or keyType == "pri_auto" then
            self.pri_key = k
            break
        end
    end
end
function ModelBaseMysql:load(properties)
    if not self:isValidKey() then
        print(string_format("%s [%s:id] should be set before load", tostring(self), self.class.__cname))
        return false
    end
    local load = false
    if not properties then
        properties = mysqlproxy:query(string_format("SELECT * from `%s` where `%s` = %s;", self.class.__cname, self.pri_key, self:getKey()))
        load = true
    end
    if not next(properties) then return false end
    local data = load and properties[1] or properties
    self:loadProperties(data)
    self:onLoad()
    return true
end
--创建model对应的redis数据, 必须已经设置了ID
function ModelBaseMysql:create()
    if not self:isValidKey() then
        print(string_format("%s [%s:key] should be set before create", tostring(self), self.class.__cname))
        return nil
    end
    self:save()
    self:onCreate()
    return self
end
-- save 忽略 缓存配置
function ModelBaseMysql:save()
    local redisProperties = self:getProperties()
    local params = {}
    for fieldName, value in pairs(redisProperties) do
        local propname = fieldName .. "_"
        if self.class.schema[fieldName][1] == "table" then
            if not next(self[propname]) then 
                params[fieldName] = "NULL"
            else
                local result = mysqlproxy:quote_sql_str(MsgPack.pack(self[propname]))
                --params[fieldName] = "'" .. MsgPack.pack(self[propname]) .. "'"
                params[fieldName] = result
            end
        elseif self.class.schema[fieldName][1] == "string" then
            local result = mysqlproxy:quote_sql_str(self[propname])
            --params[fieldName] = "'" .. self[propname] .. "'"
            params[fieldName] = result
        else
            params[fieldName] = self[propname]
        end
    end
    if next(params) then
        -- insert update
        local sql = "INSERT INTO `%s` (%s) VALUES (%s) ON DUPLICATE KEY UPDATE %s;"
        local tbName = self.class.__cname
        local key_list = ""
        local value_list = ""
        local update_list = ""
        for k, v in pairs(params) do
            if key_list ~= "" then
               key_list = key_list .. ","
            end
            if value_list ~= "" then
               value_list = value_list .. ","
            end
            if update_list ~= "" then
               update_list = update_list .. ","
            end
            key_list = key_list .. "`" .. k .. "`"
            value_list = value_list .. v
            update_list = update_list .. "`" .. k .. "`=" .. v
        end
        sql = string_format(sql, tbName, key_list, value_list, update_list)
        local res = mysqlproxy:query(sql)
        if res["errno"] then
            skynet.error(sql)
            skynet.error(res["err"])
        end
    end
end
--[[--
确定对象是否设置了有效的 key。
]]
function ModelBaseMysql:isValidKey()
    local propname = self.class.key .. "_"
    local key = self[propname]
    return type(key) == "string" and key ~= ""
end
--[[--
加载对象的属性进内存。
NOTE: 如果properties缺少schema中的域, 将用默认值来填充
**Parameters:**
-   properties: 包含属性值的数组
]]
function ModelBaseMysql:loadProperties(properties)
    assert(type(properties) == "table", "Invalid properties")
    for field, schema in pairs(self.class.schema) do
        local typ, def = table_unpack(schema)
        local propname = field .. "_"
        if typ == "table" and type(properties[field]) == "string" then
            properties[field] = MsgPack.unpack(properties[field])
        end
        local val = properties[field] or def
        if val ~= nil then
            if typ == "number" then val = tonumber(val) end
            assert(type(val) == typ,
               string_format("%s [%s:loadProperties()] Type mismatch, %s expected %s, actual is %s",
                                 tostring(self), self.class.__cname, field, typ, type(val)))
            self[propname] = val
        end
    end
end
--[[--
取得对象的属性值。
**Parameters:**
-   fields: 要取得哪些属性的值，如果未指定该参数，则返回 fields 中设定的属性
-   filter: 要从结果中过滤掉哪些属性，如果未指定则不过滤
**Returns:**
-   包含属性值的数组
]]
function ModelBaseMysql:getProperties(fields, filter)
    local schema = self.class.schema
    if type(fields) ~= "table" then fields = table.keys(self.class.schema) end
    local properties = {}
    for i, field in ipairs(fields) do
        local propname = field .. "_"
        local typ = schema[field][1]
        local val = self[propname]
        assert(type(val) == typ,
               string_format("%s [%s:getProperties()] Type mismatch, %s expected %s, actual is %s",
                                 tostring(self), self.class.__cname, field, typ, type(val)))
        properties[field] = val
    end
    if type(filter) == "table" then
        filterProperties(properties, filter)
    end
    return properties
end
function ModelBaseMysql:getProperty(property)
    if type(property) ~= "string" then return nil end
    if not self.class.schema[property] then return nil end
    return self:getProperties({property})[property]
end
function ModelBaseMysql:setProperty(property, value, forceSave)
    if not self.class.schema[property] then
        print(string_format("%s [%s:setProperty()] Invalid property : %s",
            tostring(self), self.class.__cname, property))
        return
    end
    local typ, def = table_unpack(self.class.schema[property])
    local propname = property .. "_"
    if typ == "number" then value = tonumber(value) end
    if typ == "table" and not value then
        value = self[propname] -- table 可以用自己的缓冲
    end
    assert(type(value) == typ,
       string_format("%s [%s:setProperties()] Type mismatch, %s expected %s, actual is %s",
         tostring(self), self.class.__cname, property, typ, type(value)))
    if typ == "number" or typ == "string" then
        if self[propname] == value then
            return
        end
    end
    self[propname] = value
    --self:save()
    self.cacheFields[property] = self[propname]
    if forceSave then
        self:update()
    end
end
function ModelBaseMysql:setProperties(fields, forceSave)
    for property, value in pairs(fields) do
         if not self.class.schema[property] then
            print(string_format("%s [%s:setProperty()] Invalid property : %s",
                tostring(self), self.class.__cname, property))
        else
            local typ, def = table_unpack(self.class.schema[property])
            local propname = property .. "_"
            if typ == "number" then value = tonumber(value) end
            if typ == "table" and not value then
                value = self[propname] -- table 可以用自己的缓冲
            end
            assert(type(value) == typ,
               string_format("%s [%s:setProperties()] Type mismatch, %s expected %s, actual is %s",
                 tostring(self), self.class.__cname, property, typ, type(value)))
            if typ == "number" or typ == "string" then
                if self[propname] ~= value then
                    self[propname] = value
                    self.cacheFields[property] = self[propname]
                end
            else
                self[propname] = value
                self.cacheFields[property] = self[propname]
            end
        end
    end
    if forceSave then
        self:update()
    end
    --self:save()
end
function ModelBaseMysql:incrProperty(property, value, forceSave)
    if not self.class.schema[property] then
        print(string_format("%s [%s:setProperty()] Invalid property : %s",
            tostring(self), self.class.__cname, property))
        return
    end
    local typ, def = table_unpack(self.class.schema[property])
    local propname = property .. "_"
    if typ == "table" or typ == "string" then return end
    if typ == "number" then value = tonumber(value) end
    self:setProperty(property, self[propname] + value, forceSave)
end
function ModelBaseMysql:onLoad()
end
function ModelBaseMysql:onCreate()
end
function ModelBaseMysql:checkKeyExists(key)
    local res = mysqlproxy:query(string_format("SELECT * FROM `%s` WHERE `%s` = %s", self.class.__cname, self.pri_key, key))
    return next(res)
end
function ModelBaseMysql:checkTableSchema()
    -- 1.检测是否表存在
    local typeMap = {
        number = {"int", 0},
        string = {"varchar", "", 128},
        table = {"blob", "NULL"},
        pri = {"bigint", 0},
    }
    local tbName = self.class.__cname
    local create_sql = [[
		CREATE TABLE IF NOT EXISTS `%s` (
            %s
            PRIMARY KEY (`%s`)%s
			) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
    ]]
    local index_tpl_str = [[,INDEX `%s_Index` (`%s`)]]
    
    local alter_sql = [[
		ALTER TABLE `%s` ADD COLUMN (
            %s
		) ;
    ]]
    local field_tpl_str = "`%s` %s%s DEFAULT %s"
    local auto_increment_str = "`%s` %s NOT NULL AUTO_INCREMENT"
    local field_str = ""
    local res = mysqlproxy:query("desc `".. tbName .. "`;")
    local keyList = {}
    if res["err"] then -- 表不存在
        local schema = {}
        for k, v in pairs(self.class.schema) do
            local keyType = v[3]
            if keyType == "pri" or keyType == "pri_auto" then
                self.pri_key = k
                table_insert(schema, 1, {k, v})
            else
                if keyType == "index" then
                    table_insert(keyList, k)
                end
                table_insert(schema, {k, v})
            end
        end
        for _, tbl in ipairs(schema) do
            local k, v = tbl[1], tbl[2]
            local objType, def, keyType, length = table_unpack(v)
            local isAutoPriKey = false
            assert(typeMap[objType], string_format("schema invalid type, %s, %s", tbName, k))
            -- 主键使用bigint存储
            if keyType == "pri" then objType = "pri" end
            if keyType == "pri" or keyType == "pri_auto" then
                objType = "pri"
                if keyType == "pri_auto" then
                    isAutoPriKey = true
                end
            end
            local info = typeMap[objType]
            local suffix = ""
            local fieldType = info[1]
            if objType == "table" or not def or def == "" then def = info[2] end
            if type(def) == "string" and def ~= "NULL" then def = "'" .. def .. "'" end
            if info[3] and not length then length = info[3] end
            -- 设置字段长度
            if info[3] then suffix = string.format("(%d)", length) end
            -- 很长的string使用blob
            if  keyType == "blob" then 
                fieldType = keyType
                suffix = ""
                def = "NULL"
            end
            if not isAutoPriKey then
                field_str = field_str .. string.format(field_tpl_str..",", k, fieldType, suffix, def)
            else
                field_str = field_str .. string.format(auto_increment_str..",", k, fieldType)
            end
        end
        assert(self.pri_key, string_format("table not include primary key, [%s]", tbName))
        local index_key_str = ""
        for _, k in ipairs(keyList) do
            index_key_str = index_key_str .. string_format(index_tpl_str, k, k)
        end
        -- 创建表格
        print(string_format(create_sql, tbName, field_str, self.pri_key, index_key_str))
        mysqlproxy:query(string_format(create_sql, tbName, field_str, self.pri_key, index_key_str))
    else    -- 检测是否有添加新字段
        local addCol = {}
        local curCols = {}
        for _, col in ipairs(res) do
            curCols[col["Field"]] = 1
        end
        for k, v in pairs(self.class.schema) do
            local objType, def, keyType, length = table_unpack(v)
            if not curCols[k] then
                print(string_format("table [%s] add new column [%s]", tbName, k))
                assert(typeMap[objType], string_format("schema invalid type, [%s], [%s]", tbName, k))
                local info = typeMap[objType]
                local suffix = ""
                local fieldType = info[1]
                if objType == "table" or not def or def == "" then def = info[2] end
                if type(def) == "string" and def ~= "NULL" then def = "'" .. def .. "'" end
                if info[3] and not length then length = info[3] end
                -- 设置字段长度
                if info[3] then suffix = string.format("(%d)", length) end
                -- 很长的string使用blob
                if  keyType == "blob" then 
                    fieldType = keyType
                    suffix = ""
                    def = "NULL"
                end
                local sep = ","
                if field_str == "" then
                    sep = ""
                end
                field_str = field_str .. string.format(sep..field_tpl_str, k, fieldType, suffix, def)
            end
        end
        -- 添加新列
        if field_str ~= "" then
            mysqlproxy:query(string_format(alter_sql, tbName, field_str))
        end
    end
end
function  ModelBaseMysql:loadFields(fields)
    if not self:isValidKey() then
        print(string_format("%s [%s:id] should be set before load", tostring(self), self.class.__cname))
        return
    end
    if not next(fields) then
        return
    end
    local final = {}
    for _, v in ipairs(fields) do
        table.insert(final, '`'..v..'`')
    end
    local field_list = table.concat(final, ",")
    local res = mysqlproxy:query(string_format("SELECT %s from `%s` where `%s` = %s;", field_list, self.class.__cname, self.pri_key, self:getKey()))
    if res["errno"] then
        return
    end
    return res[1]
end
function ModelBaseMysql:update()
    if next(self.cacheFields) then
        self:updateFields(self.cacheFields)
        self.cacheFields = {}
    end
end
function ModelBaseMysql:updateFields(fields)
    local params = {}
    for field, value in pairs(fields) do
        if self.class.schema[field][1] == "table" then
            if next(value) then 
                local result = mysqlproxy:quote_sql_str(MsgPack.pack(value))
                params[field] = result
            else
                params[field] = mysqlproxy:quote_sql_str(MsgPack.pack({}))
            end
        elseif self.class.schema[field][1] == "string" then
            local result = mysqlproxy:quote_sql_str(value)
            params[field] = result
        else
            params[field] = value 
        end
    end
    if next(params) then
        local sql = "UPDATE `%s` SET %s WHERE `%s` = %s;"
        local tbName = self.class.__cname
        local tmp = {}
        for k, v in pairs(params) do
            table.insert(tmp, '`' .. k .. '` = ' .. v)
        end
        sql = string_format(sql, tbName, table.concat(tmp, ","), self.pri_key, self:getKey())
        local res = mysqlproxy:query(sql)
        if res["errno"] then
            skynet.error("error sql:"..sql)
            skynet.error("error str:"..res["err"])
            return false
        else
            if (res["affected_rows"] or -1) == 0 then
                skynet.error("affected row = 0")
                skynet.error("error sql:"..sql)
            end
        end
    end
    return true
end
return ModelBaseMysql