Lua Mysql接続プール

--[[
        ：

    local mysql = require "mysql_pool"

    local ret, res, sqlstate = mysql:query(sql, true);
    local tmp
    if ret then
        tmp = cjson.encode(res)
    else
        ngx.say("error")
    end
--]]

--[[
    ：
1.           lua_code_cache on
2.       ，       set_keepalive       （    ）
3.       SQL  ，  set_timeout     （     ？？）
--]]

module("mysql_pool", package.seeall)
local mysql = require("resty.mysql")
local cfg = require "youngyedu.common.resources.config"

local mysql_pool = {}

--[[
            ,         .
      :
        false,    .
        true,     
--]]
function mysql_pool:get_connect()
    if ngx.ctx[mysql_pool] then
        return true, ngx.ctx[mysql_pool]
    end

    local client, errmsg = mysql:new()
    if not client then
        return false, "mysql.socket_failed: " .. (errmsg or "nil")
    end

    client:set_timeout(10000)  --30 

    local options = {
        host = cfg.db.prod.HOST,
        port = cfg.db.prod.PORT,
        user = cfg.db.prod.USER,
        password = cfg.db.prod.PASSWORD,
        database = cfg.db.prod.DATABASE
    }

    local result, errmsg, errno, sqlstate = client:connect(options)
    if not result then
        return false, "mysql.cant_connect: " .. (errmsg or "nil") .. ", errno:" .. (errno or "nil") ..
                ", sql_state:" .. (sqlstate or "nil")
    end

    local query = "SET NAMES " .. "utf8"
    local result, errmsg, errno, sqlstate = client:query(query)
    if not result then
        return false, "mysql.query_failed: " .. (errmsg or "nil") .. ", errno:" .. (errno or "nil") ..
                ", sql_state:" .. (sqlstate or "nil")
    end

    ngx.ctx[mysql_pool] = client

    --   ，           
    --[[ comments by leon1509
    local count, err = client:get_reused_times()
    ngx.say("xxx reused times" .. count);
    --]]

    return true, ngx.ctx[mysql_pool]
end

--[[
             
     set_keepalive  close()         ,     nginx    ，          ，         
 --]]
function mysql_pool:close()
    if ngx.ctx[mysql_pool] then
        --      ，    close    keeplive          
        -- lua_code_cache   on     
        -- 60000 ： pool_max_idle_time ， 100：connections
        ngx.ctx[mysql_pool]:set_keepalive(60000, 80)
        --     set_keepalive，         query，   
        ngx.ctx[mysql_pool] = nil
    end
end

--[[
      
                  
                 
      :
        false,    ,sqlstate  .
        true,   ,sqlstate  .
--]]
function mysql_pool:query(sql, flag)
    local ret, client = self:get_connect(flag)
    if not ret then
        return false, client, nil
    end

    local result, errmsg, errno, sqlstate = client:query(sql)

    while errmsg == "again" do
        result, errmsg, errno, sqlstate = client:read_result()
    end

    self:close()

    if not result then
        errmsg = "mysql.query_failed:" .. (errno or "nil") .. (errmsg or "nil")
        return false, errmsg, sqlstate
    end

    return true, result, sqlstate
end

return mysql_pool