util-sql-imp-ffi.lua /size: 17 Kb    last modification: 2020-07-01 14:35
1
if
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
-- I looked at luajit-mysql to see how the ffi mapping was done but it didn't work
10
-- out that well (at least not on windows) but I got the picture. As I have somewhat
11
-- different demands I simplified / redid the ffi bit and just took the swiglib
12
-- variant and adapted that.
13 14
local
tonumber
=
tonumber
15
local
concat
=
table
.
concat
16
local
format
,
byte
=
string
.
format
,
string
.
byte
17
local
lpegmatch
=
lpeg
.
match
18
local
setmetatable
,
type
=
setmetatable
,
type
19
local
sleep
=
os
.
sleep
20
local
formatters
=
string
.
formatters
21 22
local
trace_sql
=
false
trackers
.
register
(
"
sql.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
23
local
trace_queries
=
false
trackers
.
register
(
"
sql.queries
"
,
function
(
v
)
trace_queries
=
v
end
)
24
local
report_state
=
logs
.
reporter
(
"
sql
"
,
"
ffi
"
)
25 26
if
not
utilities
.
sql
then
27
require
(
"
util-sql
"
)
28
end
29 30
ffi
.
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
-- trackers.enable("*lib*")
154
local
sql
=
utilities
.
sql
155
----- mysql = ffi.load(os.name == "windows" and "libmysql" or "libmysqlclient")
156
----- mysql = ffilib(os.name == "windows" and "libmysql" or "libmysqlclient")
157
local
mysql
=
ffilib
(
os
.
name
=
=
"
windows
"
and
"
libmysql
"
or
"
libmysql
"
)
158 159
if
not
mysql
then
160
report_state
(
"
unable to load library
"
)
161
end
162 163
local
nofretries
=
5
164
local
retrydelay
=
1
165 166
local
cache
=
{
}
167
local
helpers
=
sql
.
helpers
168
local
methods
=
sql
.
methods
169
local
validspecification
=
helpers
.
validspecification
170
local
querysplitter
=
helpers
.
querysplitter
171
local
dataprepared
=
helpers
.
preparetemplate
172
local
serialize
=
sql
.
serialize
173
local
deserialize
=
sql
.
deserialize
174 175
local
mysql_open_session
=
mysql
.
mysql_init
176 177
local
mysql_open_connection
=
mysql
.
mysql_real_connect
178
local
mysql_execute_query
=
mysql
.
mysql_real_query
179
local
mysql_close_connection
=
mysql
.
mysql_close
180 181
local
mysql_affected_rows
=
mysql
.
mysql_affected_rows
182
local
mysql_field_count
=
mysql
.
mysql_field_count
183
local
mysql_field_seek
=
mysql
.
mysql_field_seek
184
local
mysql_num_fields
=
mysql
.
mysql_num_fields
185
local
mysql_fetch_fields
=
mysql
.
mysql_fetch_fields
186
----- mysql_fetch_field = mysql.mysql_fetch_field
187
local
mysql_num_rows
=
mysql
.
mysql_num_rows
188
local
mysql_fetch_row
=
mysql
.
mysql_fetch_row
189
----- mysql_fetch_lengths = mysql.mysql_fetch_lengths
190
local
mysql_init
=
mysql
.
mysql_init
191
local
mysql_store_result
=
mysql
.
mysql_store_result
192
local
mysql_free_result
=
mysql
.
mysql_free_result
193 194
local
mysql_error_number
=
mysql
.
mysql_errno
195
local
mysql_error_message
=
mysql
.
mysql_error
196 197
local
NULL
=
ffi
.
cast
(
"
MYSQL_result *
"
,
0
)
198 199
local
ffi_tostring
=
ffi
.
string
200
local
ffi_gc
=
ffi
.
gc
201 202
local
instance
=
mysql
.
mysql_init
(
nil
)
203 204
local
mysql_constant_false
=
false
205
local
mysql_constant_true
=
true
206 207
local
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
)
-- todo
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
-- local function fetch(t)
263
-- local
264
-- local row = mysql_fetch_row(result)
265
-- local result = { }
266
-- for i=1,t.noffields do
267
-- result[i] = ffi_tostring(row[i-1])
268
-- end
269
-- return unpack(result)
270
-- end
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
-- fetch = fetch, -- not efficient
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 299
end
300 301
local
initializesession
do
302 303
-- timeouts = [ connect_timeout |wait_timeout | interactive_timeout ]
304 305
local
timeout
-- = 3600 -- to be tested
306 307
-- connection
308 309
local
function
close
(
t
)
310
-- just a struct ?
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
-- mysql_error_number(connection)
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
-- session
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
)
-- maybe share, single thread anyway
381
}
382
return
setmetatable
(
session
,
mt
)
383
end
384 385
end
386 387
local
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
-- can go
462
-- why do we close a session
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 557
end
558 559
local
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 609
local
celltemplate
=
"
cells[%s]
"
610 611
methods
.
ffi
=
{
612
runner
=
function
(
)
end
,
-- never called
613
execute
=
executequery
,
614
initialize
=
initializesession
,
-- returns session
615
usesfiles
=
false
,
616
wraptemplate
=
wraptemplate
,
617
celltemplate
=
celltemplate
,
618
}
619