openresty(nginx)、lua、drizzle测试

# 添加MySQL配置(drizzle) 
upstream backend { 
    drizzle_server 127.0.0.1:3306 dbname=test user=root password=123456 protocol=mysql; 
    drizzle_keepalive max=200 overflow=ignore mode=single; 
}server { 
    listen       80; 
    server_name  localhost; 
    #access_log  logs/host.access.log  main; 
        root   html; 
        index  index.html index.htm; 
    } 
        default_type text/plain; 
        content_by_lua 'ngx.say("hello, lua")'; 
    }    location /lua_redis { 
        default_type text/plain; 
        content_by_lua_file /usr/local/lua_test/redis_test.lua; 
    }  
            default_type text/plain; 
            content_by_lua_file /usr/local/lua_test/mysql_test.lua; 
    }    location @cats-by-name { 
        set_unescape_uri $name $arg_name; 
        set_quote_sql_str $name; 
        drizzle_query 'select * from cats where name=$name'; 
        drizzle_pass backend; 
        rds_json on; 
    } 
        set_quote_sql_str $id $arg_id; 
        drizzle_query 'select * from cats where id=$id'; 
        drizzle_pass backend; 
        rds_json on; 
    } 
        access_by_lua ' 
            if ngx.var.arg_name then 
                return ngx.exec("@cats-by-name") 
            end 
                return ngx.exec("@cats-by-id") 
            end 
        '; 
    } 
    location ~ '^/mysql/(.*)' { 
        set $name $1; 
        set_quote_sql_str $quote_name $name; 
        set $sql "SELECT * FROM cats WHERE name=$quote_name"; 
        drizzle_query $sql; 
        drizzle_pass backend; 
        rds_json on; 
    } 
    location /mysql-status { 
        drizzle_status; 
    } 
}

local redis = require "resty.redis"
local cache = redis.new()
cache.connect(cache, '127.0.0.1', '6379')
local res = cache:get("foo")
if res==ngx.null then
    ngx.say("This is Null")
    return
end
ngx.say(res)

local mysql = require "resty.mysql"
local db, err = mysql:new()
if not db then
    ngx.say("failed to instantiate mysql: ", err)
    return
end

db:set_timeout(1000) -- 1 sec

-- or connect to a unix domain socket file listened
-- by a mysql server:
--     local ok, err, errno, sqlstate =
--           db:connect{
--              path = "/path/to/mysql.sock",
--              database = "ngx_test",
--              user = "ngx_test",
--              password = "ngx_test" }

local ok, err, errno, sqlstate = db:connect{
    host = "127.0.0.1",
    port = 3306,
    database = "test",
    user = "root",
    password = "123456",
    max_packet_size = 1024 * 1024 }

if not ok then
    ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
    return
end

ngx.say("connected to mysql.")

local res, err, errno, sqlstate =
    db:query("drop table if exists cats")
if not res then
    ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
    return
end

res, err, errno, sqlstate =
    db:query("create table cats "
             .. "(id serial primary key, "
             .. "name varchar(5))")
if not res then
    ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
    return
end

ngx.say("table cats created.")

res, err, errno, sqlstate =
    db:query("insert into cats (name) "
             .. "values (\'Bob\'),(\'\'),(null)")
if not res then
    ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
    return
end

ngx.say(res.affected_rows, " rows inserted into table cats ",
        "(last insert id: ", res.insert_id, ")")

-- run a select query, expected about 10 rows in
-- the result set:
res, err, errno, sqlstate =
    db:query("select * from cats order by id asc", 10)
if not res then
    ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
    return
end

local cjson = require "cjson"
ngx.say("result: ", cjson.encode(res))

-- put it into the connection pool of size 100,
-- with 10 seconds max idle timeout
local ok, err = db:set_keepalive(10000, 100)
if not ok then
    ngx.say("failed to set keepalive: ", err)
    return
end

-- or just close the connection right away:
-- local ok, err = db:close()
-- if not ok then
--     ngx.say("failed to close: ", err)
--     return
-- end
';

$ curl 'http://127.0.0.1/lua_test' 
hello, lua$ redis-cli set foo 'hello,lua-redis' OK 
$ curl 'http://127.0.0.1/lua_redis' 
hello,lua-redis$ curl 'http://127.0.0.1/lua_mysql' 
connected to mysql. 
table cats created. 
3 rows inserted into table cats (last insert id: 1) 
result: [{"name":"Bob","id":"1"},{"name":"","id":"2"},{"name":null,"id":"3"}]$ curl 'http://127.0.0.1/cats' 
{"errcode":400,"errstr":"expecting \"name\" or \"id\" query arguments"}$ curl 'http://127.0.0.1/cats?name=bob' 
[{"id":1,"name":"Bob"}]$ curl 'http://127.0.0.1/cats?id=2' 
[{"id":2,"name":""}]$ curl 'http://127.0.0.1/mysql/bob' 
[{"id":1,"name":"Bob"}]$ curl 'http://127.0.0.1/mysql-status' 
worker process: 32261upstream backend 
  active connections: 0 
  connection pool capacity: 0 
  servers: 1 
  peers: 1