1if not modules then modules = { } end modules ['util-sql-imp-ffi'] = {
2 version = 1.001,
3 comment = "companion to util-sql.lua",
4 author = "Hans Hagen, PRAGMA-ADE, Hasselt NL",
5 copyright = "PRAGMA ADE / ConTeXt Development Team",
6 license = "see context related readme files"
7}
8
9
10
11
12
13local tonumber = tonumber
14local concat = table.concat
15local format, byte = string.format, string.byte
16local lpegmatch = lpeg.match
17local setmetatable, type = setmetatable, type
18local sleep = os.sleep
19local formatters = string.formatters
20
21local trace_sql = false trackers.register("sql.trace", function(v) trace_sql = v end)
22local trace_queries = false trackers.register("sql.queries",function(v) trace_queries = v end)
23local report_state = logs.reporter("sql","ffi")
24
25if not utilities.sql then
26 require("util-sql")
27end
28
29ffi.cdef [[
30
31 /*
32 This is as lean and mean as possible. After all we just need a connection and
33 a query. The rest is handled already in the Lua code elsewhere.
34 */
35
36 void free(void*ptr);
37 void * malloc(size_t size);
38
39 typedef void MYSQL_instance;
40 typedef void MYSQL_result;
41 typedef char **MYSQL_row;
42 typedef unsigned int MYSQL_offset;
43
44 typedef struct st_mysql_field {
45 char *name;
46 char *org_name;
47 char *table;
48 char *org_table;
49 char *db;
50 char *catalog;
51 char *def;
52 unsigned long length;
53 unsigned long max_length;
54 unsigned int name_length;
55 unsigned int org_name_length;
56 unsigned int table_length;
57 unsigned int org_table_length;
58 unsigned int db_length;
59 unsigned int catalog_length;
60 unsigned int def_length;
61 unsigned int flags;
62 unsigned int decimals;
63 unsigned int charsetnr;
64 int type;
65 void *extension;
66 } MYSQL_field;
67
68 MYSQL_instance * mysql_init (
69 MYSQL_instance *mysql
70 );
71
72 MYSQL_instance * mysql_real_connect (
73 MYSQL_instance *mysql,
74 const char *host,
75 const char *user,
76 const char *passwd,
77 const char *db,
78 unsigned int port,
79 const char *unix_socket,
80 unsigned long clientflag
81 );
82
83 unsigned int mysql_errno (
84 MYSQL_instance *mysql
85 );
86
87 const char *mysql_error (
88 MYSQL_instance *mysql
89 );
90
91 /* int mysql_query (
92 MYSQL_instance *mysql,
93 const char *q
94 ); */
95
96 int mysql_real_query (
97 MYSQL_instance *mysql,
98 const char *q,
99 unsigned long length
100 );
101
102 MYSQL_result * mysql_store_result (
103 MYSQL_instance *mysql
104 );
105
106 void mysql_free_result (
107 MYSQL_result *result
108 );
109
110 unsigned long long mysql_num_rows (
111 MYSQL_result *res
112 );
113
114 MYSQL_row mysql_fetch_row (
115 MYSQL_result *result
116 );
117
118 unsigned int mysql_affected_rows (
119 MYSQL_instance *mysql
120 );
121
122 unsigned int mysql_field_count (
123 MYSQL_instance *mysql
124 );
125
126 unsigned int mysql_num_fields (
127 MYSQL_result *res
128 );
129
130 /* MYSQL_field *mysql_fetch_field (
131 MYSQL_result *result
132 ); */
133
134 MYSQL_field * mysql_fetch_fields (
135 MYSQL_result *res
136 );
137
138 MYSQL_offset mysql_field_seek(
139 MYSQL_result *result,
140 MYSQL_offset offset
141 );
142
143 void mysql_close(
144 MYSQL_instance *sock
145 );
146
147 /* unsigned long * mysql_fetch_lengths(
148 MYSQL_result *result
149 ); */
150
151]]
152
153local sql = utilities.sql
154
155
156local mysql = ffilib(os.name == "windows" and "libmysql" or "libmysql")
157
158if not mysql then
159 report_state("unable to load library")
160end
161
162local nofretries = 5
163local retrydelay = 1
164
165local cache = { }
166local helpers = sql.helpers
167local methods = sql.methods
168local validspecification = helpers.validspecification
169local querysplitter = helpers.querysplitter
170local dataprepared = helpers.preparetemplate
171local serialize = sql.serialize
172
173local mysql_open_session = mysql.mysql_init
174
175local mysql_open_connection = mysql.mysql_real_connect
176local mysql_execute_query = mysql.mysql_real_query
177local mysql_close_connection = mysql.mysql_close
178
179local mysql_affected_rows = mysql.mysql_affected_rows
180local mysql_field_count = mysql.mysql_field_count
181local mysql_field_seek = mysql.mysql_field_seek
182local mysql_num_fields = mysql.mysql_num_fields
183local mysql_fetch_fields = mysql.mysql_fetch_fields
184
185local mysql_num_rows = mysql.mysql_num_rows
186local mysql_fetch_row = mysql.mysql_fetch_row
187
188local mysql_init = mysql.mysql_init
189local mysql_store_result = mysql.mysql_store_result
190local mysql_free_result = mysql.mysql_free_result
191
192local mysql_error_number = mysql.mysql_errno
193local mysql_error_message = mysql.mysql_error
194
195local NULL = ffi.cast("MYSQL_result *",0)
196
197local ffi_tostring = ffi.string
198local ffi_gc = ffi.gc
199
200local instance = mysql.mysql_init(nil)
201
202local mysql_constant_false = false
203local mysql_constant_true = true
204
205local wrapresult do
206
207 local function collect(t)
208 local result = t._result_
209 if result then
210 ffi_gc(result,mysql_free_result)
211 end
212 end
213
214 local function finish(t)
215 local result = t._result_
216 if result then
217 t._result_ = nil
218 ffi_gc(result,mysql_free_result)
219 end
220 end
221
222 local function getcoldata(t)
223 local result = t._result_
224 local nofrows = t.nofrows
225 local noffields = t.noffields
226 local names = { }
227 local types = { }
228 local fields = mysql_fetch_fields(result)
229 for i=1,noffields do
230 local field = fields[i-1]
231 names[i] = ffi_tostring(field.name)
232 types[i] = tonumber(field.type)
233 end
234 t.names = names
235 t.types = types
236 end
237
238 local function getcolnames(t)
239 local names = t.names
240 if names then
241 return names
242 end
243 getcoldata(t)
244 return t.names
245 end
246
247 local function getcoltypes(t)
248 local types = t.types
249 if types then
250 return types
251 end
252 getcoldata(t)
253 return t.types
254 end
255
256 local function numrows(t)
257 return t.nofrows
258 end
259
260
261
262
263
264
265
266
267
268
269
270 local mt = {
271 __gc = collect,
272 __index = {
273 _result_ = nil,
274 close = finish,
275 numrows = numrows,
276 getcolnames = getcolnames,
277 getcoltypes = getcoltypes,
278
279 }
280 }
281
282 wrapresult = function(connection)
283 local result = mysql_store_result(connection)
284 if result ~= NULL then
285 mysql_field_seek(result,0)
286 local t = {
287 _result_ = result,
288 nofrows = tonumber(mysql_num_rows (result) or 0) or 0,
289 noffields = tonumber(mysql_num_fields(result) or 0) or 0,
290 }
291 return setmetatable(t,mt)
292 elseif tonumber(mysql_field_count(connection) or 0) or 0 > 0 then
293 return tonumber(mysql_affected_rows(connection))
294 end
295 end
296
297end
298
299local initializesession do
300
301
302
303 local timeout
304
305
306
307 local function close(t)
308
309 end
310
311 local function execute(t,query)
312 if query and query ~= "" then
313 local connection = t._connection_
314 local result = mysql_execute_query(connection,query,#query)
315 if result == 0 then
316 return wrapresult(connection)
317 else
318
319 return false, ffi_tostring(mysql_error_message(connection))
320 end
321 end
322 return false
323 end
324
325 local mt = {
326 __index = {
327 close = close,
328 execute = execute,
329 }
330 }
331
332
333
334 local function open(t,database,username,password,host,port)
335 local connection = mysql_open_connection(
336 t._session_,
337 host or "localhost",
338 username or "",
339 password or "",
340 database or "",
341 port or 0,
342 NULL,
343 0
344 )
345 if connection ~= NULL then
346 if timeout then
347 execute(connection,formatters["SET SESSION connect_timeout=%s ;"](timeout))
348 end
349 local t = {
350 _connection_ = connection,
351 }
352 return setmetatable(t,mt)
353 end
354 end
355
356 local function message(t)
357 return mysql_error_message(t._session_)
358 end
359
360 local function close(t)
361 local connection = t._connection_
362 if connection and connection ~= NULL then
363 ffi_gc(connection, mysql_close)
364 t.connection = nil
365 end
366 end
367
368 local mt = {
369 __index = {
370 connect = open,
371 close = close,
372 message = message,
373 },
374 }
375
376 initializesession = function()
377 local session = {
378 _session_ = mysql_open_session(instance)
379 }
380 return setmetatable(session,mt)
381 end
382
383end
384
385local executequery do
386
387 local function connect(session,specification)
388 return session:connect(
389 specification.database or "",
390 specification.username or "",
391 specification.password or "",
392 specification.host or "",
393 specification.port
394 )
395 end
396
397 local function fetched(specification,query,converter)
398 if not query or query == "" then
399 report_state("no valid query")
400 return false
401 end
402 local id = specification.id
403 local session, connection
404 if id then
405 local c = cache[id]
406 if c then
407 session = c.session
408 connection = c.connection
409 end
410 if not connection then
411 session = initializesession()
412 if not session then
413 return formatters["no session for %a"](id)
414 end
415 connection = connect(session,specification)
416 if not connection then
417 return formatters["no connection for %a"](id)
418 end
419 cache[id] = { session = session, connection = connection }
420 end
421 else
422 session = initializesession()
423 if not session then
424 return "no session"
425 end
426 connection = connect(session,specification)
427 if not connection then
428 return "no connection"
429 end
430 end
431 if not connection then
432 report_state("error in connection: %s@%s to %s:%s",
433 specification.database or "no database",
434 specification.username or "no username",
435 specification.host or "no host",
436 specification.port or "no port"
437 )
438 return "no connection"
439 end
440 query = lpegmatch(querysplitter,query)
441 local result, okay
442 for i=1,#query do
443 local q = query[i]
444 local r, m = connection:execute(q)
445 if m then
446 report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q or "?"))
447 if m then
448 report_state("message: %s",m)
449 end
450 end
451 local t = type(r)
452 if t == "table" then
453 result = r
454 okay = true
455 elseif t == "number" then
456 okay = true
457 end
458 end
459 if not okay then
460
461 if connection then
462 connection:close()
463 end
464 if session then
465 session:close()
466 end
467 if id then
468 cache[id] = nil
469 end
470 return "execution error"
471 end
472 local data, keys
473 if result then
474 if converter then
475 data = converter.ffi(result)
476 else
477 local _result_ = result._result_
478 local noffields = result.noffields
479 local nofrows = result.nofrows
480 keys = result:getcolnames()
481 data = { }
482 if noffields > 0 and nofrows > 0 then
483 for i=1,nofrows do
484 local cells = { }
485 local row = mysql_fetch_row(_result_)
486 for j=1,noffields do
487 local s = row[j-1]
488 local k = keys[j]
489 if s == NULL then
490 cells[k] = ""
491 else
492 cells[k] = ffi_tostring(s)
493 end
494 end
495 data[i] = cells
496 end
497 end
498 end
499 result:close()
500 end
501
502 if not id then
503 if connection then
504 connection:close()
505 end
506 if session then
507 session:close()
508 end
509 end
510 return false, data, keys
511 end
512
513 local function datafetched(specification,query,converter)
514 local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
515 if not callokay then
516 report_state("call error, retrying")
517 callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
518 elseif connectionerror then
519 report_state("error: %s, retrying",connectionerror)
520 callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
521 end
522 if not callokay then
523 report_state("persistent call error")
524 elseif connectionerror then
525 report_state("persistent error: %s",connectionerror)
526 end
527 return data or { }, keys or { }
528 end
529
530 executequery = function(specification)
531 if trace_sql then
532 report_state("executing library")
533 end
534 if not validspecification(specification) then
535 report_state("error in specification")
536 return
537 end
538 local query = dataprepared(specification)
539 if not query then
540 report_state("error in preparation")
541 return
542 end
543 local data, keys = datafetched(specification,query,specification.converter)
544 if not data then
545 report_state("error in fetching")
546 return
547 end
548 local one = data[1]
549 if one then
550 setmetatable(data,{ __index = one } )
551 end
552 return data, keys
553 end
554
555end
556
557local wraptemplate = [[
558----- mysql = ffi.load(os.name == "windows" and "libmysql" or "libmysqlclient")
559local mysql = ffi.load(os.name == "windows" and "libmysql" or "libmysql")
560
561local mysql_fetch_row = mysql.mysql_fetch_row
562local ffi_tostring = ffi.string
563
564local converters = utilities.sql.converters
565local deserialize = utilities.sql.deserialize
566local fromjson = utilities.sql.fromjson
567
568local tostring = tostring
569local tonumber = tonumber
570local booleanstring = string.booleanstring
571
572local NULL = ffi.cast("MYSQL_result *",0)
573
574%s
575
576return function(result)
577 if not result then
578 return { }
579 end
580 local nofrows = result.nofrows
581 if nofrows == 0 then
582 return { }
583 end
584 local noffields = result.noffields
585 local target = { } -- no %s needed here
586 local _result_ = result._result_
587 -- we can share cells
588 for i=1,nofrows do
589 local cells = { }
590 local row = mysql_fetch_row(_result_)
591 for j=1,noffields do
592 local s = row[j-1]
593 if s == NULL then
594 cells[j] = ""
595 else
596 cells[j] = ffi_tostring(s)
597 end
598 end
599 target[%s] = {
600 %s
601 }
602 end
603 result:close()
604 return target
605end
606]]
607
608local celltemplate = "cells[%s]"
609
610methods.ffi = {
611 runner = function() end,
612 execute = executequery,
613 initialize = initializesession,
614 usesfiles = false,
615 wraptemplate = wraptemplate,
616 celltemplate = celltemplate,
617}
618 |
