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