wilhelmliao
commented
7 years ago added charset option test case.
Closed wilhelmliao closed 7 years ago
wilhelmliao
commented
7 years ago added charset option test case.
agentzh
commented
7 years ago @wilhelmliao Merged with the following extra patch. Thanks!
diff --git a/README.markdown b/README.markdown
index 0ee94aa..e5dd69b 100644
--- a/README.markdown
+++ b/README.markdown
@@ -92,7 +92,9 @@ Synopsis
database = "ngx_test",
user = "ngx_test",
password = "ngx_test",
- max_packet_size = 1024 * 1024 }
+ charset = "utf8",
+ max_packet_size = 1024 * 1024,
+ }
if not ok then
ngx.say("failed to connect: ", err, ": ", errcode, " ", sqlstate)
@@ -202,6 +204,14 @@ The `options` argument is a Lua table holding the following keys:
* `password`
MySQL account password for login (in clear text).
+* `charset`
+
+ the character set used on the MySQL connection, which can be different from the default charset setting.
+The following values are accepted: `big5`, `dec8`, `cp850`, `hp8`, `koi8r`, `latin1`, `latin2`,
+`swe7`, `ascii`, `ujis`, `sjis`, `hebrew`, `tis620`, `euckr`, `koi8u`, `gb2312`, `greek`,
+`cp1250`, `gbk`, `latin5`, `armscii8`, `utf8`, `ucs2`, `cp866`, `keybcs2`, `macce`,
+`macroman`, `cp852`, `latin7`, `utf8mb4`, `cp1251`, `utf16`, `utf16le`, `cp1256`,
+`cp1257`, `utf32`, `binary`, `geostd8`, `cp932`, `eucjpms`, `gb18030`.
* `max_packet_size`
the upper limit for the reply packets sent from the MySQL server (default to 1MB).
diff --git a/lib/resty/mysql.lua b/lib/resty/mysql.lua
index 3030887..ff77ee8 100644
--- a/lib/resty/mysql.lua
+++ b/lib/resty/mysql.lua
@@ -55,51 +55,53 @@ local SERVER_MORE_RESULTS_EXISTS = 8
-- 16MB - 1, the default max allowed packet size used by libmysqlclient
local FULL_PACKET_SIZE = 16777215
--- add by Wilhelm Liao @ 2017-05-14
--- SELECT CHARACTER_SET_NAME, ID FROM information_schema.collations WHERE IS_DEFAULT = 'Yes' ORDER BY id;
+-- the following charset map is generated from the following mysql query:
+-- SELECT CHARACTER_SET_NAME, ID
+-- FROM information_schema.collations
+-- WHERE IS_DEFAULT = 'Yes' ORDER BY id;
local CHARSET_MAP = {
- _default = 0,
- big5 = 1,
- dec8 = 3,
- cp850 = 4,
- hp8 = 6,
- koi8r = 7,
- latin1 = 8,
- latin2 = 9,
- swe7 = 10,
- ascii = 11,
- ujis = 12,
- sjis = 13,
- hebrew = 16,
- tis620 = 18,
- euckr = 19,
- koi8u = 22,
- gb2312 = 24,
- greek = 25,
- cp1250 = 26,
- gbk = 28,
- latin5 = 30,
- armscii8 = 32,
- utf8 = 33,
- ucs2 = 35,
- cp866 = 36,
- keybcs2 = 37,
- macce = 38,
- macroman = 39,
- cp852 = 40,
- latin7 = 41,
- utf8mb4 = 45,
- cp1251 = 51,
- utf16 = 54,
- utf16le = 56,
- cp1256 = 57,
- cp1257 = 59,
- utf32 = 60,
- binary = 63,
- geostd8 = 92,
- cp932 = 95,
- eucjpms = 97,
- gb18030 = 248
+ _default = 0,
+ big5 = 1,
+ dec8 = 3,
+ cp850 = 4,
+ hp8 = 6,
+ koi8r = 7,
+ latin1 = 8,
+ latin2 = 9,
+ swe7 = 10,
+ ascii = 11,
+ ujis = 12,
+ sjis = 13,
+ hebrew = 16,
+ tis620 = 18,
+ euckr = 19,
+ koi8u = 22,
+ gb2312 = 24,
+ greek = 25,
+ cp1250 = 26,
+ gbk = 28,
+ latin5 = 30,
+ armscii8 = 32,
+ utf8 = 33,
+ ucs2 = 35,
+ cp866 = 36,
+ keybcs2 = 37,
+ macce = 38,
+ macroman = 39,
+ cp852 = 40,
+ latin7 = 41,
+ utf8mb4 = 45,
+ cp1251 = 51,
+ utf16 = 54,
+ utf16le = 56,
+ cp1256 = 57,
+ cp1257 = 59,
+ utf32 = 60,
+ binary = 63,
+ geostd8 = 92,
+ cp932 = 95,
+ eucjpms = 97,
+ gb18030 = 248
}
local mt = { __index = _M }
@@ -553,7 +555,7 @@ function _M.connect(self, opts)
local charset = CHARSET_MAP[opts.charset or "_default"]
if not charset then
- return nil, "charset '" .. opts.charset .. "' is not supported"
+ return nil, "charset '" .. opts.charset .. "' is not supported"
end
local pool = opts.pool
diff --git a/t/charset.t b/t/charset.t
new file mode 100644
index 0000000..2db4ac7
--- /dev/null
+++ b/t/charset.t
@@ -0,0 +1,221 @@
+# vim:set ft= ts=4 sw=4 et:
+
+use Test::Nginx::Socket::Lua;
+use Cwd qw(cwd);
+
+repeat_each(2);
+
+plan tests => repeat_each() * (3 * blocks());
+
+my $pwd = cwd();
+
+our $HttpConfig = qq{
+ resolver \$TEST_NGINX_RESOLVER;
+ lua_package_path "$pwd/lib/?.lua;$pwd/t/lib/?.lua;;";
+ lua_package_cpath "/usr/local/openresty-debug/lualib/?.so;/usr/local/openresty/lualib/?.so;;";
+};
+
+$ENV{TEST_NGINX_RESOLVER} = '8.8.8.8';
+$ENV{TEST_NGINX_MYSQL_PORT} ||= 3306;
+$ENV{TEST_NGINX_MYSQL_HOST} ||= '127.0.0.1';
+$ENV{TEST_NGINX_MYSQL_PATH} ||= '/var/run/mysql/mysql.sock';
+
+#log_level 'warn';
+
+#no_long_string();
+no_shuffle();
+check_accum_error_log();
+
+run_tests();
+
+__DATA__
+
+=== TEST 1: connect db using charset option (utf8)
+--- http_config eval: $::HttpConfig
+--- config
+ location /t {
+ content_by_lua_block {
+ local ljson = require "ljson"
+ local mysql = require "resty.mysql"
+ local db = mysql:new()
+
+ db:set_timeout(1000) -- 1 sec
+
+ local ok, err, errno, sqlstate = db:connect({
+ path = "$TEST_NGINX_MYSQL_PATH",
+ database = "ngx_test",
+ user = "ngx_test",
+ password = "ngx_test",
+ charset = "utf8",
+ pool = "my_pool"})
+
+ if not ok then
+ ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
+ return
+ end
+
+ -- generate test data
+ 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(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ -- add new record with '愛麗絲' by utf8 encoded.
+ res, err, errno, sqlstate = db:query("INSERT INTO cats(name) VALUES (0xe6849be9ba97e7b5b2)")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ res, err, errno, sqlstate = db:query("SELECT * FROM cats")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ db:close()
+
+ ngx.say(ljson.encode(res))
+ }
+ }
+--- request
+GET /t
+--- response_body
+[{"id":"1","name":"愛麗絲"}]
+--- no_error_log
+[error]
+
+
+
+=== TEST 2: connect db using charset option (big5)
+--- http_config eval: $::HttpConfig
+--- config
+ location /t {
+ content_by_lua_block {
+ local ljson = require "ljson"
+ local mysql = require "resty.mysql"
+ local db = mysql:new()
+
+ db:set_timeout(1000) -- 1 sec
+
+ local ok, err, errno, sqlstate = db:connect({
+ path = "$TEST_NGINX_MYSQL_PATH",
+ database = "ngx_test",
+ user = "ngx_test",
+ password = "ngx_test",
+ charset = "big5",
+ pool = "my_pool"})
+
+ if not ok then
+ ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
+ return
+ end
+
+ -- generate test data
+ 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(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ -- add new record with '愛麗絲' by utf8 encoded.
+ res, err, errno, sqlstate = db:query("INSERT INTO cats(name) VALUES (0xe6849be9ba97e7b5b2)")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ res, err, errno, sqlstate = db:query("SELECT * FROM cats")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ db:close()
+
+ ngx.say(ljson.encode(res))
+ }
+ }
+--- request
+GET /t
+--- response_body eval
+qq/[{"id":"1","name":"\x{b7}R\x{c4}R\x{b5}\x{b7}"}]\n/
+--- no_error_log
+[error]
+
+
+
+=== TEST 3: connect db using charset option (gbk)
+--- http_config eval: $::HttpConfig
+--- config
+ location /t {
+ content_by_lua_block {
+ local ljson = require "ljson"
+ local mysql = require "resty.mysql"
+ local db = mysql:new()
+
+ db:set_timeout(1000) -- 1 sec
+
+ local ok, err, errno, sqlstate = db:connect({
+ path = "$TEST_NGINX_MYSQL_PATH",
+ database = "ngx_test",
+ user = "ngx_test",
+ password = "ngx_test",
+ charset = "gbk",
+ pool = "my_pool"})
+
+ if not ok then
+ ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
+ return
+ end
+
+ -- generate test data
+ 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(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ -- add new record with '愛麗絲' by utf8 encoded.
+ res, err, errno, sqlstate = db:query("INSERT INTO cats(name) VALUES (0xe6849be9ba97e7b5b2)")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ res, err, errno, sqlstate = db:query("SELECT * FROM cats")
+ if not res then
+ ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
+ return
+ end
+
+ db:close()
+
+ ngx.say(ljson.encode(res))
+ }
+ }
+--- request
+GET /t
+--- response_body eval
+qq/[{"id":"1","name":"\x{90}\x{db}\x{fb}\x{90}\x{bd}z"}]\n/
+--- no_error_log
+[error]
diff --git a/t/sanity.t b/t/sanity.t
index 7f397a6..f57134d 100644
--- a/t/sanity.t
+++ b/t/sanity.t
@@ -1321,67 +1321,3 @@ success
--- no_error_log
[error]
--- timeout: 10
-
-
-
-=== TEST 20: connect db using charset option
---- http_config eval: $::HttpConfig
---- config
- location /t {
- content_by_lua_block {
- local ljson = require "ljson"
- local mysql = require "resty.mysql"
- local db = mysql:new()
-
- db:set_timeout(1000) -- 1 sec
-
- local ok, err, errno, sqlstate = db:connect({
- path = "$TEST_NGINX_MYSQL_PATH",
- database = "ngx_test",
- user = "ngx_test",
- password = "ngx_test",
- charset = "utf8",
- pool = "my_pool"})
-
- if not ok then
- ngx.say("failed to connect: ", err, ": ", errno, " ", sqlstate)
- return
- end
-
- -- generate test data
- 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(128)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;")
- if not res then
- ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
- return
- end
-
- -- add new record with '愛麗絲' by utf8 encoded.
- res, err, errno, sqlstate = db:query("INSERT INTO cats(name) VALUES (0xe6849be9ba97e7b5b2)")
- if not res then
- ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
- return
- end
-
- res, err, errno, sqlstate = db:query("SELECT * FROM cats")
- if not res then
- ngx.say("bad result: ", err, ": ", errno, ": ", sqlstate, ".")
- return
- end
-
- db:close()
-
- ngx.say( ljson.encode(res) )
- }
- }
---- request
-GET /t
---- response_body
-[{"id":"1","name":"愛麗絲"}]
---- no_error_log
-[error]
@wilhelmliao Thanks! Will you add some test cases to the existing test suite to cover this new feature?