mysql Lua+ffi binding http://luapower.com/mysql
You can not select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

555 lines
17KB

  1. --mysql test unit (see comments for problems with libmariadb)
  2. --NOTE: create a database called 'test' first to run these tests!
  3. local mysql = require'mysql'
  4. local glue = require'glue'
  5. local pp = require'pp'
  6. local myprint = require'mysql_print'
  7. local ffi = require'ffi'
  8. mysql.bind'mariadb'
  9. --helpers
  10. local print_table = myprint.table
  11. local print_result = myprint.result
  12. local fit = myprint.fit
  13. local function assert_deepequal(t1, t2) --assert the equality of two values
  14. assert(type(t1) == type(t2), type(t1)..' ~= '..type(t2))
  15. if type(t1) == 'table' then
  16. for k,v in pairs(t1) do assert_deepequal(t2[k], v) end
  17. for k,v in pairs(t2) do assert_deepequal(t1[k], v) end
  18. else
  19. assert(t1 == t2, pp.format(t1) .. ' ~= ' .. pp.format(t2))
  20. end
  21. end
  22. local function print_fields(fields_iter)
  23. local fields = {'name', 'type', 'type_flag', 'length', 'max_length', 'decimals', 'charsetnr',
  24. 'org_name', 'table', 'org_table', 'db', 'catalog', 'def', 'extension'}
  25. local rows = {}
  26. local aligns = {}
  27. for i,field in fields_iter do
  28. rows[i] = {}
  29. for j=1,#fields do
  30. local v = field[fields[j]]
  31. rows[i][j] = tostring(v)
  32. aligns[j] = type(v) == 'number' and 'right' or 'left'
  33. end
  34. end
  35. print_table(fields, rows, aligns)
  36. end
  37. --client library
  38. print('mysql.thread_safe() ', '->', pp.format(mysql.thread_safe()))
  39. print('mysql.client_info() ', '->', pp.format(mysql.client_info()))
  40. print('mysql.client_version()', '->', pp.format(mysql.client_version()))
  41. --connections
  42. local t = {
  43. host = 'localhost',
  44. user = 'root',
  45. db = 'test',
  46. options = {
  47. MYSQL_SECURE_AUTH = false, --not supported by libmariadb
  48. MYSQL_OPT_READ_TIMEOUT = 1,
  49. },
  50. flags = {
  51. CLIENT_LONG_PASSWORD = true,
  52. },
  53. }
  54. local conn = mysql.connect(t)
  55. print('mysql.connect ', pp.format(t, ' '), '->', conn)
  56. print('conn:change_user( ', pp.format(t.user), ')', conn:change_user(t.user))
  57. print('conn:select_db( ', pp.format(t.db), ')', conn:select_db(t.db))
  58. print('conn:set_multiple_statements(', pp.format(true), ')', conn:set_multiple_statements(true))
  59. print('conn:set_charset( ', pp.format('utf8'), ')', conn:set_charset('utf8'))
  60. --conn info
  61. print('conn:charset_name() ', '->', pp.format(conn:charset())); assert(conn:charset() == 'utf8')
  62. print('conn:charset_info() ', '->', pp.format(conn:charset_info(), ' ')) --crashes libmariadb
  63. print('conn:ping() ', '->', pp.format(conn:ping()))
  64. print('conn:thread_id() ', '->', pp.format(conn:thread_id()))
  65. print('conn:stat() ', '->', pp.format(conn:stat()))
  66. print('conn:server_info() ', '->', pp.format(conn:server_info()))
  67. print('conn:host_info() ', '->', pp.format(conn:host_info()))
  68. print('conn:server_version() ', '->', pp.format(conn:server_version()))
  69. print('conn:proto_info() ', '->', pp.format(conn:proto_info()))
  70. print('conn:ssl_cipher() ', '->', pp.format(conn:ssl_cipher()))
  71. --transactions
  72. print('conn:commit() ', conn:commit())
  73. print('conn:rollback() ', conn:rollback())
  74. print('conn:set_autocommit() ', conn:set_autocommit(true))
  75. --test types and values
  76. local test_fields = {
  77. 'fdecimal',
  78. 'fnumeric',
  79. 'ftinyint',
  80. 'futinyint',
  81. 'fsmallint',
  82. 'fusmallint',
  83. 'finteger',
  84. 'fuinteger',
  85. 'ffloat',
  86. 'fdouble',
  87. 'fdouble2',
  88. 'fdouble3',
  89. 'fdouble4',
  90. 'freal',
  91. 'fbigint',
  92. 'fubigint',
  93. 'fmediumint',
  94. 'fumediumint',
  95. 'fdate',
  96. 'ftime',
  97. 'ftime2',
  98. 'fdatetime',
  99. 'fdatetime2',
  100. 'ftimestamp',
  101. 'ftimestamp2',
  102. 'fyear',
  103. 'fbit2',
  104. 'fbit22',
  105. 'fbit64',
  106. 'fenum',
  107. 'fset',
  108. 'ftinyblob',
  109. 'fmediumblob',
  110. 'flongblob',
  111. 'ftext',
  112. 'fblob',
  113. 'fvarchar',
  114. 'fvarbinary',
  115. 'fchar',
  116. 'fbinary',
  117. 'fnull',
  118. }
  119. local field_indices = glue.index(test_fields)
  120. local field_types = {
  121. fdecimal = 'decimal(8,2)',
  122. fnumeric = 'numeric(6,4)',
  123. ftinyint = 'tinyint',
  124. futinyint = 'tinyint unsigned',
  125. fsmallint = 'smallint',
  126. fusmallint = 'smallint unsigned',
  127. finteger = 'int',
  128. fuinteger = 'int unsigned',
  129. ffloat = 'float',
  130. fdouble = 'double',
  131. fdouble2 = 'double',
  132. fdouble3 = 'double',
  133. fdouble4 = 'double',
  134. freal = 'real',
  135. fbigint = 'bigint',
  136. fubigint = 'bigint unsigned',
  137. fmediumint = 'mediumint',
  138. fumediumint = 'mediumint unsigned',
  139. fdate = 'date',
  140. ftime = 'time(0)',
  141. ftime2 = 'time(6)',
  142. fdatetime = 'datetime(0)',
  143. fdatetime2 = 'datetime(6)',
  144. ftimestamp = 'timestamp(0) null',
  145. ftimestamp2 = 'timestamp(6) null',
  146. fyear = 'year',
  147. fbit2 = 'bit(2)',
  148. fbit22 = 'bit(22)',
  149. fbit64 = 'bit(64)',
  150. fenum = "enum('yes', 'no')",
  151. fset = "set('e1', 'e2', 'e3')",
  152. ftinyblob = 'tinyblob',
  153. fmediumblob = 'mediumblob',
  154. flongblob = 'longblob',
  155. ftext = 'text',
  156. fblob = 'blob',
  157. fvarchar = 'varchar(200)',
  158. fvarbinary = 'varbinary(200)',
  159. fchar = 'char(200)',
  160. fbinary = 'binary(20)',
  161. fnull = 'int'
  162. }
  163. local test_values = {
  164. fdecimal = '42.12',
  165. fnumeric = '42.1234',
  166. ftinyint = 42,
  167. futinyint = 255,
  168. fsmallint = 42,
  169. fusmallint = 65535,
  170. finteger = 42,
  171. fuinteger = 2^32-1,
  172. ffloat = tonumber(ffi.cast('float', 42.33)),
  173. fdouble = 42.33,
  174. fdouble2 = nil, --null from mysql 5.1.24+
  175. fdouble3 = nil, --null from mysql 5.1.24+
  176. fdouble4 = nil, --null from mysql 5.1.24+
  177. freal = 42.33,
  178. fbigint = 420LL,
  179. fubigint = 0ULL - 1,
  180. fmediumint = 440,
  181. fumediumint = 2^24-1,
  182. fdate = {year = 2013, month = 10, day = 05},
  183. ftime = {hour = 21, min = 30, sec = 15, frac = 0},
  184. ftime2 = {hour = 21, min = 30, sec = 16, frac = 123456},
  185. fdatetime = {year = 2013, month = 10, day = 05, hour = 21, min = 30, sec = 17, frac = 0},
  186. fdatetime2 = {year = 2013, month = 10, day = 05, hour = 21, min = 30, sec = 18, frac = 123456},
  187. ftimestamp = {year = 2013, month = 10, day = 05, hour = 21, min = 30, sec = 19, frac = 0},
  188. ftimestamp2 = {year = 2013, month = 10, day = 05, hour = 21, min = 30, sec = 20, frac = 123456},
  189. fyear = 2013,
  190. fbit2 = 2,
  191. fbit22 = 2 * 2^8 + 2,
  192. fbit64 = 2ULL * 2^(64-8) + 2 * 2^8 + 2,
  193. fenum = 'yes',
  194. fset = 'e2,e3',
  195. ftinyblob = 'tiny tiny blob',
  196. fmediumblob = 'medium blob',
  197. flongblob = 'loong blob',
  198. ftext = 'just a text',
  199. fblob = 'bloob',
  200. fvarchar = 'just a varchar',
  201. fvarbinary = 'a varbinary',
  202. fchar = 'a char',
  203. fbinary = 'a binary char\0\0\0\0\0\0\0',
  204. fnull = nil,
  205. }
  206. local set_values = {
  207. fdecimal = "'42.12'",
  208. fnumeric = "42.1234",
  209. ftinyint = "'42'",
  210. futinyint = "'255'",
  211. fsmallint = "42",
  212. fusmallint = "65535",
  213. finteger = "'42'",
  214. fuinteger = tostring(2^32-1),
  215. ffloat = "42.33",
  216. fdouble = "'42.33'",
  217. fdouble2 = "0/0",
  218. fdouble3 = "1/0",
  219. fdouble4 = "-1/0",
  220. freal = "42.33",
  221. fbigint = "'420'",
  222. fubigint = tostring(0ULL-1):sub(1,-4), --remove 'ULL'
  223. fmediumint = "440",
  224. fumediumint = tostring(2^24-1),
  225. fdate = "'2013-10-05'",
  226. ftime = "'21:30:15'",
  227. ftime2 = "'21:30:16.123456'",
  228. fdatetime = "'2013-10-05 21:30:17'",
  229. fdatetime2 = "'2013-10-05 21:30:18.123456'",
  230. ftimestamp = "'2013-10-05 21:30:19'",
  231. ftimestamp2 = "'2013-10-05 21:30:20.123456'",
  232. fyear = "2013",
  233. fbit2 = "b'10'",
  234. fbit22 = "b'1000000010'",
  235. fbit64 = "b'0000001000000000000000000000000000000000000000000000001000000010'",
  236. fenum = "'yes'",
  237. fset = "('e3,e2')",
  238. ftinyblob = "'tiny tiny blob'",
  239. fmediumblob = "'medium blob'",
  240. flongblob = "'loong blob'",
  241. ftext = "'just a text'",
  242. fblob = "'bloob'",
  243. fvarchar = "'just a varchar'",
  244. fvarbinary = "'a varbinary'",
  245. fchar = "'a char'",
  246. fbinary = "'a binary char'",
  247. fnull = "null"
  248. }
  249. local bind_types = {
  250. fdecimal = 'decimal(20)', --TODO: truncation
  251. fnumeric = 'numeric(20)',
  252. ftinyint = 'tinyint',
  253. futinyint = 'tinyint unsigned',
  254. fsmallint = 'smallint',
  255. fusmallint = 'smallint unsigned',
  256. finteger = 'int',
  257. fuinteger = 'int unsigned',
  258. ffloat = 'float',
  259. fdouble = 'double',
  260. fdouble2 = 'double',
  261. fdouble3 = 'double',
  262. fdouble4 = 'double',
  263. freal = 'real',
  264. fbigint = 'bigint',
  265. fubigint = 'bigint unsigned',
  266. fmediumint = 'mediumint',
  267. fumediumint = 'mediumint unsigned',
  268. fdate = 'date',
  269. ftime = 'time',
  270. ftime2 = 'time',
  271. fdatetime = 'datetime',
  272. fdatetime2 = 'datetime',
  273. ftimestamp = 'timestamp',
  274. ftimestamp2 = 'timestamp',
  275. fyear = 'year',
  276. fbit2 = 'bit(2)',
  277. fbit22 = 'bit(22)',
  278. fbit64 = 'bit(64)',
  279. fenum = 'enum(200)',
  280. fset = 'set(200)',
  281. ftinyblob = 'tinyblob(200)',
  282. fmediumblob = 'mediumblob(200)',
  283. flongblob = 'longblob(200)',
  284. ftext = 'text(200)',
  285. fblob = 'blob(200)',
  286. fvarchar = 'varchar(200)',
  287. fvarbinary = 'varbinary(200)',
  288. fchar = 'char(200)',
  289. fbinary = 'binary(200)',
  290. fnull = 'int',
  291. }
  292. --queries
  293. local esc = "'escape me'"
  294. print('conn:escape( ', pp.format(esc), ')', '->', pp.format(conn:escape(esc)))
  295. local q1 = 'drop table if exists binding_test'
  296. print('conn:query( ', pp.format(q1), ')', conn:query(q1))
  297. local field_defs = ''
  298. for i,field in ipairs(test_fields) do
  299. field_defs = field_defs .. field .. ' ' .. field_types[field] .. (i == #test_fields and '' or ', ')
  300. end
  301. local field_sets = ''
  302. for i,field in ipairs(test_fields) do
  303. field_sets = field_sets .. field .. ' = ' .. set_values[field] .. (i == #test_fields and '' or ', ')
  304. end
  305. conn:query([[
  306. create table binding_test ( ]] .. field_defs .. [[ );
  307. insert into binding_test set ]] .. field_sets .. [[ ;
  308. insert into binding_test values ();
  309. select * from binding_test;
  310. ]])
  311. --query info
  312. print('conn:field_count() ', '->', pp.format(conn:field_count()))
  313. print('conn:affected_rows() ', '->', pp.format(conn:affected_rows()))
  314. print('conn:insert_id() ', '->', conn:insert_id())
  315. print('conn:errno() ', '->', pp.format(conn:errno()))
  316. print('conn:sqlstate() ', '->', pp.format(conn:sqlstate()))
  317. print('conn:warning_count() ', '->', pp.format(conn:warning_count()))
  318. print('conn:info() ', '->', pp.format(conn:info()))
  319. for i=1,3 do
  320. print('conn:more_results() ', '->', pp.format(conn:more_results())); assert(conn:more_results())
  321. print('conn:next_result() ', '->', pp.format(conn:next_result()))
  322. end
  323. assert(not conn:more_results())
  324. --query results
  325. local res = conn:store_result() --TODO: local res = conn:use_result()
  326. print('conn:store_result() ', '->', res)
  327. print('res:row_count() ', '->', pp.format(res:row_count())); assert(res:row_count() == 2)
  328. print('res:field_count() ', '->', pp.format(res:field_count())); assert(res:field_count() == #test_fields)
  329. print('res:eof() ', '->', pp.format(res:eof())); assert(res:eof() == true)
  330. print('res:fields() ', '->') print_fields(res:fields())
  331. print('res:field_info(1) ', '->', pp.format(res:field_info(1)))
  332. --first row: fetch as array and test values
  333. local row = assert(res:fetch'n')
  334. print("res:fetch'n' ", '->', pp.format(row))
  335. for i,field in res:fields() do
  336. assert_deepequal(row[i], test_values[field.name])
  337. end
  338. --first row again: fetch as assoc. array and test values
  339. print('res:seek(1) ', '->', res:seek(1))
  340. local row = assert(res:fetch'a')
  341. print("res:fetch'a' ", '->', pp.format(row))
  342. for i,field in res:fields() do
  343. assert_deepequal(row[field.name], test_values[field.name])
  344. end
  345. --first row again: fetch unpacked and test values
  346. print('res:seek(1) ', '->', res:seek(1))
  347. local function pack(_, ...)
  348. local t = {}
  349. for i=1,select('#', ...) do
  350. t[i] = select(i, ...)
  351. end
  352. return t
  353. end
  354. local row = pack(res:fetch())
  355. print("res:fetch() ", '-> packed: ', pp.format(row))
  356. for i,field in res:fields() do
  357. assert_deepequal(row[i], test_values[field.name])
  358. end
  359. --first row again: print its values parsed and unparsed for comparison
  360. res:seek(1)
  361. local row = assert(res:fetch'n')
  362. res:seek(1)
  363. local row_s = assert(res:fetch'ns')
  364. print()
  365. print(fit('', 4, 'right') .. ' ' .. fit('field', 20) .. fit('unparsed', 40) .. ' ' .. 'parsed')
  366. print(('-'):rep(4 + 2 + 20 + 40 + 40))
  367. for i,field in res:fields() do
  368. print(fit(tostring(i), 4, 'right') .. ' ' .. fit(field.name, 20) .. fit(pp.format(row_s[i]), 40) .. ' ' .. pp.format(row[i]))
  369. end
  370. print()
  371. --second row: all nulls
  372. local row = assert(res:fetch'n')
  373. print("res:fetch'n' ", '->', pp.format(row))
  374. assert(#row == 0)
  375. for i=1,res:field_count() do
  376. assert(row[i] == nil)
  377. end
  378. assert(not res:fetch'n')
  379. --all rows again: test iterator
  380. res:seek(1)
  381. local n = 0
  382. for i,row in res:rows'nas' do
  383. n = n + 1
  384. assert(i == n)
  385. end
  386. print("for i,row in res:rows'nas' do <count-rows>", '->', n); assert(n == 2)
  387. print('res:free() ', res:free())
  388. --reflection
  389. print('res:list_dbs() ', '->'); print_result(conn:list_dbs())
  390. print('res:list_tables() ', '->'); print_result(conn:list_tables())
  391. print('res:list_processes() ', '->'); print_result(conn:list_processes())
  392. --prepared statements
  393. local query = 'select '.. table.concat(test_fields, ', ')..' from binding_test'
  394. local stmt = conn:prepare(query)
  395. print('conn:prepare( ', pp.format(query), ')', '->', stmt)
  396. print('stmt:field_count() ', '->', pp.format(stmt:field_count())); assert(stmt:field_count() == #test_fields)
  397. --we can get the fields and their types before execution so we can create create our bind structures.
  398. --max. length is not computed though, but length is, so we can use that.
  399. print('stmt:fields() ', '->'); print_fields(stmt:fields())
  400. --binding phase
  401. local btypes = {}
  402. for i,field in ipairs(test_fields) do
  403. btypes[i] = bind_types[field]
  404. end
  405. local bind = stmt:bind_result(btypes)
  406. print('stmt:bind_result( ', pp.format(btypes), ')', '->', pp.format(bind))
  407. --execution and loading
  408. print('stmt:exec() ', stmt:exec())
  409. print('stmt:store_result() ', stmt:store_result())
  410. --result info
  411. print('stmt:row_count() ', '->', pp.format(stmt:row_count()))
  412. print('stmt:affected_rows() ', '->', pp.format(stmt:affected_rows()))
  413. print('stmt:insert_id() ', '->', pp.format(stmt:insert_id()))
  414. print('stmt:sqlstate() ', '->', pp.format(stmt:sqlstate()))
  415. --result data (different API since we don't get a result object)
  416. print('stmt:fetch() ', stmt:fetch())
  417. print('stmt:fields() ', '->'); print_fields(stmt:fields())
  418. print('bind:is_truncated(1) ', '->', pp.format(bind:is_truncated(1))); assert(bind:is_truncated(1) == false)
  419. print('bind:is_null(1) ', '->', pp.format(bind:is_null(1))); assert(bind:is_null(1) == false)
  420. print('bind:get(1) ', '->', pp.format(bind:get(1))); assert(bind:get(1) == test_values.fdecimal)
  421. local i = field_indices.fdate
  422. print('bind:get_date( ', i, ')', '->', bind:get_date(i)); assert_deepequal({bind:get_date(i)}, {2013, 10, 5})
  423. local i = field_indices.ftime
  424. print('bind:get_date( ', i, ')', '->', bind:get_date(i)); assert_deepequal({bind:get_date(i)}, {nil, nil, nil, 21, 30, 15, 0})
  425. local i = field_indices.fdatetime
  426. print('bind:get_date( ', '->', bind:get_date(i)); assert_deepequal({bind:get_date(i)}, {2013, 10, 5, 21, 30, 17, 0})
  427. local i = field_indices.ftimestamp
  428. print('bind:get_date( ', '->', bind:get_date(i)); assert_deepequal({bind:get_date(i)}, {2013, 10, 5, 21, 30, 19, 0})
  429. local i = field_indices.ftimestamp2
  430. print('bind:get_date( ', '->', bind:get_date(i)); assert_deepequal({bind:get_date(i)}, {2013, 10, 5, 21, 30, 20, 123456})
  431. print('for i=1,bind.field_count do bind:get(i)', '->')
  432. local function print_bind_buffer(bind)
  433. print()
  434. for i,field in ipairs(test_fields) do
  435. local v = bind:get(i)
  436. assert_deepequal(v, test_values[field])
  437. assert(bind:is_truncated(i) == false)
  438. assert(bind:is_null(i) == (test_values[field] == nil))
  439. print(fit(tostring(i), 4, 'right') .. ' ' .. fit(field, 20) .. pp.format(v))
  440. end
  441. print()
  442. end
  443. print_bind_buffer(bind)
  444. print('stmt:free_result() ', stmt:free_result())
  445. --local next_result = stmt:next_result()
  446. --print('stmt:next_result() ', '->', pp.format(next_result)); assert(next_result == false)
  447. print('stmt:reset() ', stmt:reset())
  448. print('stmt:close() ', stmt:close())
  449. --prepared statements with parameters
  450. for i,field in ipairs(test_fields) do
  451. local query = 'select * from binding_test where '..field..' = ?'
  452. local stmt = conn:prepare(query)
  453. print('conn:prepare( ', pp.format(query), ')')
  454. local param_bind_def = {bind_types[field]}
  455. local bind = stmt:bind_params(param_bind_def)
  456. print('stmt:bind_params ', pp.format(param_bind_def))
  457. local function exec()
  458. print('stmt:exec() ', stmt:exec())
  459. print('stmt:store_result() ', stmt:store_result())
  460. print('stmt:row_count() ', '->', stmt:row_count())
  461. assert(stmt:row_count() == 1) --libmariadb() returns 0
  462. end
  463. local v = test_values[field]
  464. if v ~= nil then
  465. print('bind:set( ', 1, pp.format(v), ')'); bind:set(1, v); exec()
  466. if field:find'date' or field:find'time' then
  467. print('bind:set_date( ', 1, v.year, v.month, v.day, v.hour, v.min, v.sec, v.frac, ')')
  468. bind:set_date(1, v.year, v.month, v.day, v.hour, v.min, v.sec, v.frac)
  469. exec() --libmariadb crashes the server
  470. end
  471. end
  472. print('stmt:close() ', stmt:close())
  473. end
  474. --prepared statements with auto-allocated result bind buffers.
  475. local query = 'select * from binding_test'
  476. local stmt = conn:prepare(query)
  477. local bind = stmt:bind_result()
  478. --pp(stmt:bind_result_types())
  479. stmt:exec()
  480. stmt:store_result()
  481. stmt:fetch()
  482. print_bind_buffer(bind)
  483. stmt:close()
  484. local q = 'drop table binding_test'
  485. print('conn:query( ', pp.format(q), ')', conn:query(q))
  486. print('conn:commit() ', conn:commit())
  487. print('conn:close() ', conn:close())