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
172local deserialize = sql.deserialize
173
174local mysql_open_session = mysql.mysql_init
175
176local mysql_open_connection = mysql.mysql_real_connect
177local mysql_execute_query = mysql.mysql_real_query
178local mysql_close_connection = mysql.mysql_close
179
180local mysql_affected_rows = mysql.mysql_affected_rows
181local mysql_field_count = mysql.mysql_field_count
182local mysql_field_seek = mysql.mysql_field_seek
183local mysql_num_fields = mysql.mysql_num_fields
184local mysql_fetch_fields = mysql.mysql_fetch_fields
185
186local mysql_num_rows = mysql.mysql_num_rows
187local mysql_fetch_row = mysql.mysql_fetch_row
188
189local mysql_init = mysql.mysql_init
190local mysql_store_result = mysql.mysql_store_result
191local mysql_free_result = mysql.mysql_free_result
192
193local mysql_error_number = mysql.mysql_errno
194local mysql_error_message = mysql.mysql_error
195
196local NULL = ffi.cast("MYSQL_result *",0)
197
198local ffi_tostring = ffi.string
199local ffi_gc = ffi.gc
200
201local instance = mysql.mysql_init(nil)
202
203local mysql_constant_false = false
204local mysql_constant_true = true
205
206local wrapresult do
207
208 local function collect(t)
209 local result = t._result_
210 if result then
211 ffi_gc(result,mysql_free_result)
212 end
213 end
214
215 local function finish(t)
216 local result = t._result_
217 if result then
218 t._result_ = nil
219 ffi_gc(result,mysql_free_result)
220 end
221 end
222
223 local function getcoldata(t)
224 local result = t._result_
225 local nofrows = t.nofrows
226 local noffields = t.noffields
227 local names = { }
228 local types = { }
229 local fields = mysql_fetch_fields(result)
230 for i=1,noffields do
231 local field = fields[i-1]
232 names[i] = ffi_tostring(field.name)
233 types[i] = tonumber(field.type)
234 end
235 t.names = names
236 t.types = types
237 end
238
239 local function getcolnames(t)
240 local names = t.names
241 if names then
242 return names
243 end
244 getcoldata(t)
245 return t.names
246 end
247
248 local function getcoltypes(t)
249 local types = t.types
250 if types then
251 return types
252 end
253 getcoldata(t)
254 return t.types
255 end
256
257 local function numrows(t)
258 return t.nofrows
259 end
260
261
262
263
264
265
266
267
268
269
270
271 local mt = {
272 __gc = collect,
273 __index = {
274 _result_ = nil,
275 close = finish,
276 numrows = numrows,
277 getcolnames = getcolnames,
278 getcoltypes = getcoltypes,
279
280 }
281 }
282
283 wrapresult = function(connection)
284 local result = mysql_store_result(connection)
285 if result ~= NULL then
286 mysql_field_seek(result,0)
287 local t = {
288 _result_ = result,
289 nofrows = tonumber(mysql_num_rows (result) or 0) or 0,
290 noffields = tonumber(mysql_num_fields(result) or 0) or 0,
291 }
292 return setmetatable(t,mt)
293 elseif tonumber(mysql_field_count(connection) or 0) or 0 > 0 then
294 return tonumber(mysql_affected_rows(connection))
295 end
296 end
297
298end
299
300local initializesession do
301
302
303
304 local timeout
305
306
307
308 local function close(t)
309
310 end
311
312 local function execute(t,query)
313 if query and query ~= "" then
314 local connection = t._connection_
315 local result = mysql_execute_query(connection,query,#query)
316 if result == 0 then
317 return wrapresult(connection)
318 else
319
320 return false, ffi_tostring(mysql_error_message(connection))
321 end
322 end
323 return false
324 end
325
326 local mt = {
327 __index = {
328 close = close,
329 execute = execute,
330 }
331 }
332
333
334
335 local function open(t,database,username,password,host,port)
336 local connection = mysql_open_connection(
337 t._session_,
338 host or "localhost",
339 username or "",
340 password or "",
341 database or "",
342 port or 0,
343 NULL,
344 0
345 )
346 if connection ~= NULL then
347 if timeout then
348 execute(connection,formatters["SET SESSION connect_timeout=%s ;"](timeout))
349 end
350 local t = {
351 _connection_ = connection,
352 }
353 return setmetatable(t,mt)
354 end
355 end
356
357 local function message(t)
358 return mysql_error_message(t._session_)
359 end
360
361 local function close(t)
362 local connection = t._connection_
363 if connection and connection ~= NULL then
364 ffi_gc(connection, mysql_close)
365 t.connection = nil
366 end
367 end
368
369 local mt = {
370 __index = {
371 connect = open,
372 close = close,
373 message = message,
374 },
375 }
376
377 initializesession = function()
378 local session = {
379 _session_ = mysql_open_session(instance)
380 }
381 return setmetatable(session,mt)
382 end
383
384end
385
386local executequery do
387
388 local function connect(session,specification)
389 return session:connect(
390 specification.database or "",
391 specification.username or "",
392 specification.password or "",
393 specification.host or "",
394 specification.port
395 )
396 end
397
398 local function fetched(specification,query,converter)
399 if not query or query == "" then
400 report_state("no valid query")
401 return false
402 end
403 local id = specification.id
404 local session, connection
405 if id then
406 local c = cache[id]
407 if c then
408 session = c.session
409 connection = c.connection
410 end
411 if not connection then
412 session = initializesession()
413 if not session then
414 return formatters["no session for %a"](id)
415 end
416 connection = connect(session,specification)
417 if not connection then
418 return formatters["no connection for %a"](id)
419 end
420 cache[id] = { session = session, connection = connection }
421 end
422 else
423 session = initializesession()
424 if not session then
425 return "no session"
426 end
427 connection = connect(session,specification)
428 if not connection then
429 return "no connection"
430 end
431 end
432 if not connection then
433 report_state("error in connection: %s@%s to %s:%s",
434 specification.database or "no database",
435 specification.username or "no username",
436 specification.host or "no host",
437 specification.port or "no port"
438 )
439 return "no connection"
440 end
441 query = lpegmatch(querysplitter,query)
442 local result, okay
443 for i=1,#query do
444 local q = query[i]
445 local r, m = connection:execute(q)
446 if m then
447 report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q or "?"))
448 if m then
449 report_state("message: %s",m)
450 end
451 end
452 local t = type(r)
453 if t == "table" then
454 result = r
455 okay = true
456 elseif t == "number" then
457 okay = true
458 end
459 end
460 if not okay then
461
462 if connection then
463 connection:close()
464 end
465 if session then
466 session:close()
467 end
468 if id then
469 cache[id] = nil
470 end
471 return "execution error"
472 end
473 local data, keys
474 if result then
475 if converter then
476 data = converter.ffi(result)
477 else
478 local _result_ = result._result_
479 local noffields = result.noffields
480 local nofrows = result.nofrows
481 keys = result:getcolnames()
482 data = { }
483 if noffields > 0 and nofrows > 0 then
484 for i=1,nofrows do
485 local cells = { }
486 local row = mysql_fetch_row(_result_)
487 for j=1,noffields do
488 local s = row[j-1]
489 local k = keys[j]
490 if s == NULL then
491 cells[k] = ""
492 else
493 cells[k] = ffi_tostring(s)
494 end
495 end
496 data[i] = cells
497 end
498 end
499 end
500 result:close()
501 end
502
503 if not id then
504 if connection then
505 connection:close()
506 end
507 if session then
508 session:close()
509 end
510 end
511 return false, data, keys
512 end
513
514 local function datafetched(specification,query,converter)
515 local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
516 if not callokay then
517 report_state("call error, retrying")
518 callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
519 elseif connectionerror then
520 report_state("error: %s, retrying",connectionerror)
521 callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
522 end
523 if not callokay then
524 report_state("persistent call error")
525 elseif connectionerror then
526 report_state("persistent error: %s",connectionerror)
527 end
528 return data or { }, keys or { }
529 end
530
531 executequery = function(specification)
532 if trace_sql then
533 report_state("executing library")
534 end
535 if not validspecification(specification) then
536 report_state("error in specification")
537 return
538 end
539 local query = dataprepared(specification)
540 if not query then
541 report_state("error in preparation")
542 return
543 end
544 local data, keys = datafetched(specification,query,specification.converter)
545 if not data then
546 report_state("error in fetching")
547 return
548 end
549 local one = data[1]
550 if one then
551 setmetatable(data,{ __index = one } )
552 end
553 return data, keys
554 end
555
556end
557
558local wraptemplate = [[
559----- mysql = ffi.load(os.name == "windows" and "libmysql" or "libmysqlclient")
560local mysql = ffi.load(os.name == "windows" and "libmysql" or "libmysql")
561
562local mysql_fetch_row = mysql.mysql_fetch_row
563local ffi_tostring = ffi.string
564
565local converters = utilities.sql.converters
566local deserialize = utilities.sql.deserialize
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 |
