util-sql-imp-swiglib.lua /size: 16 Kb    last modification: 2020-07-01 14:35
1
if
not
modules
then
modules
=
{
}
end
modules
[
'
util-sql-imp-swiglib
'
]
=
{
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
-- As the regular library is flawed (i.e. there are crashes in the table
10
-- construction code) and also not that efficient, Luigi Scarso looked into
11
-- a swig binding. This is a bit more low level approach but as we stay
12
-- closer to the original library it's also less dependant.
13 14
local
concat
=
table
.
concat
15
local
format
,
byte
=
string
.
format
,
string
.
byte
16
local
lpegmatch
=
lpeg
.
match
17
local
setmetatable
,
type
=
setmetatable
,
type
18
local
sleep
=
os
.
sleep
19 20
local
trace_sql
=
false
trackers
.
register
(
"
sql.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
21
local
trace_queries
=
false
trackers
.
register
(
"
sql.queries
"
,
function
(
v
)
trace_queries
=
v
end
)
22
local
report_state
=
logs
.
reporter
(
"
sql
"
,
"
swiglib
"
)
23 24
local
helpers
=
require
(
"
swiglib.helpers.core
"
)
25
local
sql
=
utilities
.
sql
26
local
mysql
=
require
(
"
swiglib.mysql.core
"
)
-- "5.6.14"
27
----- mysql = swiglib("mysql.core") -- "5.6.14"
28 29
local
new_u_char_array
=
helpers
.
new_u_char_array
or
helpers
.
new_ucharArray
30
local
ucharArray_setitem
=
helpers
.
u_char_array_setitem
or
helpers
.
ucharArray_setitem
31
local
int_p_assign
=
helpers
.
int_p_assign
32
local
ulongArray_getitem
=
helpers
.
u_long_array_getitem
or
helpers
.
ulongArray_getitem
33 34
-- inspect(table.sortedkeys(mysql))
35 36
local
nofretries
=
5
37
local
retrydelay
=
1
38 39
local
cache
=
{
}
40
local
helpers
=
sql
.
helpers
41
local
methods
=
sql
.
methods
42
local
validspecification
=
helpers
.
validspecification
43
local
querysplitter
=
helpers
.
querysplitter
44
local
dataprepared
=
helpers
.
preparetemplate
45
local
serialize
=
sql
.
serialize
46
local
deserialize
=
sql
.
deserialize
47 48
local
mysql_initialize
=
mysql
.
mysql_init
49 50
local
mysql_open_connection
=
mysql
.
mysql_real_connect
51
local
mysql_execute_query
=
mysql
.
mysql_real_query
52
local
mysql_close_connection
=
mysql
.
mysql_close
53 54
local
mysql_field_seek
=
mysql
.
mysql_field_seek
55
local
mysql_num_fields
=
mysql
.
mysql_num_fields
56
local
mysql_fetch_field
=
mysql
.
mysql_fetch_field
57
local
mysql_num_rows
=
mysql
.
mysql_num_rows
58
local
mysql_fetch_row
=
mysql
.
mysql_fetch_row
59
local
mysql_fetch_lengths
=
mysql
.
mysql_fetch_lengths
60
local
mysql_init
=
mysql
.
mysql_init
61
local
mysql_store_result
=
mysql
.
mysql_store_result
62
local
mysql_free_result
=
mysql
.
mysql_free_result
63
local
mysql_use_result
=
mysql
.
mysql_use_result
64 65
local
mysql_error_message
=
mysql
.
mysql_error
66
----- mysql_options_argument = mysql.mysql_options_argument
67 68
local
instance
=
mysql
.
MYSQL
(
)
69 70
local
mysql_constant_false
=
false
71
local
mysql_constant_true
=
true
72 73
----- util_getbytearray = mysql.util_getbytearray
74 75
-- if mysql_options_argument then
76
--
77
-- mysql_constant_false = mysql_options_argument(false) -- 0 "\0"
78
-- mysql_constant_true = mysql_options_argument(true) -- 1 "\1"
79
--
80
-- -- print(swig_type(mysql_constant_false))
81
-- -- print(swig_type(mysql_constant_true))
82
--
83
-- mysql.mysql_options(instance,mysql.MYSQL_OPT_RECONNECT,mysql_constant_true);
84
--
85
-- else
86
--
87
-- print("")
88
-- print("incomplete swiglib.mysql interface")
89
-- print("")
90
--
91
-- end
92 93
-- some helpers:
94 95
function
mysql
.
options_argument
(
arg
)
96
local
targ
=
type
(
arg
)
97
if
targ
=
=
"
boolean
"
then
98
local
o
=
new_u_char_array
(
1
)
99
ucharArray_setitem
(
o
,
0
,
arg
=
=
true
and
64
or
0
)
100
return
o
101
elseif
targ
=
=
"
string
"
then
102
local
o
=
new_u_char_array
(
#
arg
)
103
ucharArray_setitem
(
o
,
0
,
0
)
104
for
i
=
1
,
#
arg
do
105
ucharArray_setitem
(
o
,
i
-1
,
byte
(
arg
,
i
)
)
106
end
107
return
o
108
elseif
targ
=
=
"
number
"
then
109
local
o
=
core
.
new_int_p
(
)
110
int_p_assign
(
o
,
arg
)
111
return
o
112
else
113
return
nil
114
end
115
end
116 117
-- function mysql.util_unpackbytearray(row,noffields,len)
118
-- if row == nil then
119
-- return { }
120
-- elseif noffields < 1 then
121
-- return { }
122
-- else
123
-- local t = { }
124
-- for i=0,noffields-1 do
125
-- local l = ulongArray_getitem(len,i) -- zero based ... element from len array
126
-- local r = util_getbytearray(row,i,l) -- zero based ... element from len array
127
-- t[#t+1]= r
128
-- end
129
-- return t
130
-- end
131
-- end
132 133
--
134 135
-- local typemap = mysql.MYSQL_TYPE_VAR_STRING and {
136
-- [mysql.MYSQL_TYPE_VAR_STRING ] = "string",
137
-- [mysql.MYSQL_TYPE_STRING ] = "string",
138
-- [mysql.MYSQL_TYPE_DECIMAL ] = "number",
139
-- [mysql.MYSQL_TYPE_SHORT ] = "number",
140
-- [mysql.MYSQL_TYPE_LONG ] = "number",
141
-- [mysql.MYSQL_TYPE_FLOAT ] = "number",
142
-- [mysql.MYSQL_TYPE_DOUBLE ] = "number",
143
-- [mysql.MYSQL_TYPE_LONGLONG ] = "number",
144
-- [mysql.MYSQL_TYPE_INT24 ] = "number",
145
-- [mysql.MYSQL_TYPE_YEAR ] = "number",
146
-- [mysql.MYSQL_TYPE_TINY ] = "number",
147
-- [mysql.MYSQL_TYPE_TINY_BLOB ] = "binary",
148
-- [mysql.MYSQL_TYPE_MEDIUM_BLOB] = "binary",
149
-- [mysql.MYSQL_TYPE_LONG_BLOB ] = "binary",
150
-- [mysql.MYSQL_TYPE_BLOB ] = "binary",
151
-- [mysql.MYSQL_TYPE_DATE ] = "date",
152
-- [mysql.MYSQL_TYPE_NEWDATE ] = "date",
153
-- [mysql.MYSQL_TYPE_DATETIME ] = "datetime",
154
-- [mysql.MYSQL_TYPE_TIME ] = "time",
155
-- [mysql.MYSQL_TYPE_TIMESTAMP ] = "time",
156
-- [mysql.MYSQL_TYPE_ENUM ] = "set",
157
-- [mysql.MYSQL_TYPE_SET ] = "set",
158
-- [mysql.MYSQL_TYPE_NULL ] = "null",
159
-- }
160 161
-- real_escape_string
162 163
local
function
finish
(
t
)
164
local
r
=
t
.
_result_
165
if
r
then
166
mysql_free_result
(
r
)
167
end
168
end
169 170
-- will become metatable magic
171 172
-- local function analyze(result)
173
-- mysql_field_seek(result,0)
174
-- local nofrows = mysql_num_rows(result) or 0
175
-- local noffields = mysql_num_fields(result)
176
-- local names = { }
177
-- local types = { }
178
-- for i=1,noffields do
179
-- local field = mysql_fetch_field(result)
180
-- names[i] = field.name
181
-- types[i] = field.type
182
-- end
183
-- return names, types, noffields, nofrows
184
-- end
185 186
local
function
getcolnames
(
t
)
187
return
t
.
names
188
end
189 190
local
function
getcoltypes
(
t
)
191
return
t
.
types
192
end
193 194
local
function
numrows
(
t
)
195
return
t
.
nofrows
196
end
197 198
local
fetch_fields_from_current_row
=
mysql
.
util_mysql_fetch_fields_from_current_row
199
local
fetch_all_rows
=
mysql
.
util_mysql_fetch_all_rows
200 201
-- swig_type
202 203
-- local function list(t)
204
-- local result = t._result_
205
-- local row = mysql_fetch_row(result)
206
-- local len = mysql_fetch_lengths(result)
207
-- local result = { }
208
-- for i=1,t.noffields do
209
-- local r = i - 1 -- zero offset
210
-- result[i] = util_getbytearray(row,r,ulongArray_getitem(len,r))
211
-- end
212
-- return result
213
-- end
214 215
-- local function hash(t)
216
-- local list = fetch_fields_from_current_row(t._result_)
217
-- local result = t._result_
218
-- local fields = t.names
219
-- local row = mysql_fetch_row(result)
220
-- local len = mysql_fetch_lengths(result)
221
-- local result = { }
222
-- for i=1,t.noffields do
223
-- local r = i - 1 -- zero offset
224
-- result[fields[i]] = util_getbytearray(row,r,ulongArray_getitem(len,r))
225
-- end
226
-- return result
227
-- end
228 229
local
function
list
(
t
)
230
return
fetch_fields_from_current_row
(
t
.
_result_
)
231
end
232 233
local
function
hash
(
t
)
234
local
list
=
fetch_fields_from_current_row
(
t
.
_result_
)
235
local
fields
=
t
.
names
236
local
data
=
{
}
237
for
i
=
1
,
t
.
noffields
do
238
data
[
fields
[
i
]
]
=
list
[
i
]
239
end
240
return
data
241
end
242 243
local
function
wholelist
(
t
)
244
return
fetch_all_rows
(
t
.
_result_
)
245
end
246 247
local
mt
=
{
__index
=
{
248
-- regular
249
finish
=
finish
,
250
list
=
list
,
251
hash
=
hash
,
252
wholelist
=
wholelist
,
253
-- compatibility
254
numrows
=
numrows
,
255
getcolnames
=
getcolnames
,
256
getcoltypes
=
getcoltypes
,
257
-- fallback
258
_result_
=
nil
,
259
names
=
{
}
,
260
types
=
{
}
,
261
noffields
=
0
,
262
nofrows
=
0
,
263
}
264
}
265 266
local
nt
=
setmetatable
(
{
}
,
mt
)
267 268
-- session
269 270
local
function
close
(
t
)
271
mysql_close_connection
(
t
.
_connection_
)
272
end
273 274
local
function
execute
(
t
,
query
)
275
if
query
and
query
~
=
"
"
then
276
local
connection
=
t
.
_connection_
277
local
result
=
mysql_execute_query
(
connection
,
query
,
#
query
)
278
if
result
=
=
0
then
279
local
result
=
mysql_store_result
(
connection
)
280
if
result
then
281
mysql_field_seek
(
result
,
0
)
282
local
nofrows
=
mysql_num_rows
(
result
)
or
0
283
local
noffields
=
mysql_num_fields
(
result
)
284
local
names
=
{
}
285
local
types
=
{
}
286
for
i
=
1
,
noffields
do
287
local
field
=
mysql_fetch_field
(
result
)
288
names
[
i
]
=
field
.
name
289
types
[
i
]
=
field
.
type
290
end
291
local
t
=
{
292
_result_
=
result
,
293
names
=
names
,
294
types
=
types
,
295
noffields
=
noffields
,
296
nofrows
=
nofrows
,
297
}
298
return
setmetatable
(
t
,
mt
)
299
else
300
return
nt
301
end
302
end
303
end
304
return
false
305
end
306 307
local
mt
=
{
__index
=
{
308
close
=
close
,
309
execute
=
execute
,
310
}
311
}
312 313
local
function
open
(
t
,
database
,
username
,
password
,
host
,
port
)
314
local
connection
=
mysql_open_connection
(
t
.
_session_
,
host
or
"
localhost
"
,
username
or
"
"
,
password
or
"
"
,
database
or
"
"
,
port
or
0
,
0
,
0
)
315
if
connection
then
316
local
t
=
{
317
_connection_
=
connection
,
318
}
319
return
setmetatable
(
t
,
mt
)
320
end
321
end
322 323
local
function
message
(
t
)
324
return
mysql_error_message
(
t
.
_session_
)
325
end
326 327
local
function
close
(
t
)
328
-- dummy, as we have a global session
329
end
330 331
local
mt
=
{
332
__index
=
{
333
connect
=
open
,
334
close
=
close
,
335
message
=
message
,
336
}
337
}
338 339
local
function
initialize
(
)
340
local
session
=
{
341
_session_
=
mysql_initialize
(
instance
)
-- maybe share, single thread anyway
342
}
343
return
setmetatable
(
session
,
mt
)
344
end
345 346
-- -- -- --
347 348
local
function
connect
(
session
,
specification
)
349
return
session
:
connect
(
350
specification
.
database
or
"
"
,
351
specification
.
username
or
"
"
,
352
specification
.
password
or
"
"
,
353
specification
.
host
or
"
"
,
354
specification
.
port
355
)
356
end
357 358
local
function
error_in_connection
(
specification
,
action
)
359
report_state
(
"
error in connection: [%s] %s@%s to %s:%s
"
,
360
action
or
"
unknown
"
,
361
specification
.
database
or
"
no database
"
,
362
specification
.
username
or
"
no username
"
,
363
specification
.
host
or
"
no host
"
,
364
specification
.
port
or
"
no port
"
365
)
366
end
367 368
local
function
datafetched
(
specification
,
query
,
converter
)
369
if
not
query
or
query
=
=
"
"
then
370
report_state
(
"
no valid query
"
)
371
return
{
}
,
{
}
372
end
373
local
id
=
specification
.
id
374
local
session
,
connection
375
if
id
then
376
local
c
=
cache
[
id
]
377
if
c
then
378
session
=
c
.
session
379
connection
=
c
.
connection
380
end
381
if
not
connection
then
382
session
=
initialize
(
)
383
connection
=
connect
(
session
,
specification
)
384
if
not
connection
then
385
for
i
=
1
,
nofretries
do
386
sleep
(
retrydelay
)
387
report_state
(
"
retrying to connect: [%s.%s] %s@%s to %s:%s
"
,
388
id
,
i
,
389
specification
.
database
or
"
no database
"
,
390
specification
.
username
or
"
no username
"
,
391
specification
.
host
or
"
no host
"
,
392
specification
.
port
or
"
no port
"
393
)
394
connection
=
connect
(
session
,
specification
)
395
if
connection
then
396
break
397
end
398
end
399
end
400
if
connection
then
401
cache
[
id
]
=
{
session
=
session
,
connection
=
connection
}
402
end
403
end
404
else
405
session
=
initialize
(
)
406
connection
=
connect
(
session
,
specification
)
407
if
not
connection
then
408
for
i
=
1
,
nofretries
do
409
sleep
(
retrydelay
)
410
report_state
(
"
retrying to connect: [%s] %s@%s to %s:%s
"
,
411
i
,
412
specification
.
database
or
"
no database
"
,
413
specification
.
username
or
"
no username
"
,
414
specification
.
host
or
"
no host
"
,
415
specification
.
port
or
"
no port
"
416
)
417
connection
=
connect
(
session
,
specification
)
418
if
connection
then
419
break
420
end
421
end
422
end
423
end
424
if
not
connection
then
425
report_state
(
"
error in connection: %s@%s to %s:%s
"
,
426
specification
.
database
or
"
no database
"
,
427
specification
.
username
or
"
no username
"
,
428
specification
.
host
or
"
no host
"
,
429
specification
.
port
or
"
no port
"
430
)
431
return
{
}
,
{
}
432
end
433
query
=
lpegmatch
(
querysplitter
,
query
)
434
local
result
,
message
,
okay
435
for
i
=
1
,
#
query
do
436
local
q
=
query
[
i
]
437
local
r
,
m
=
connection
:
execute
(
q
)
438
if
m
then
439
report_state
(
"
error in query, stage: %s
"
,
string
.
collapsespaces
(
q
or
"
?
"
)
)
440
message
=
message
and
format
(
"
%s\n%s
"
,
message
,
m
)
or
m
441
end
442
if
type
(
r
)
=
=
"
table
"
then
443
result
=
r
444
okay
=
true
445
elseif
not
m
then
446
okay
=
true
447
end
448
end
449
local
data
,
keys
450
if
result
then
451
if
converter
then
452
data
=
converter
.
swiglib
(
result
)
453
else
454
keys
=
result
.
names
455
data
=
{
}
456
for
i
=
1
,
result
.
nofrows
do
457
data
[
i
]
=
result
:
hash
(
)
458
end
459
end
460
result
:
finish
(
)
-- result:close()
461
elseif
message
then
462
report_state
(
"
message %s
"
,
message
)
463
end
464
if
not
keys
then
465
keys
=
{
}
466
end
467
if
not
data
then
468
data
=
{
}
469
end
470
if
not
id
then
471
connection
:
close
(
)
472
session
:
close
(
)
473
end
474
return
data
,
keys
475
end
476 477
local
function
execute
(
specification
)
478
if
trace_sql
then
479
report_state
(
"
executing library
"
)
480
end
481
if
not
validspecification
(
specification
)
then
482
report_state
(
"
error in specification
"
)
483
return
484
end
485
local
query
=
dataprepared
(
specification
)
486
if
not
query
then
487
report_state
(
"
error in preparation
"
)
488
return
489
end
490
local
data
,
keys
=
datafetched
(
specification
,
query
,
specification
.
converter
)
491
if
not
data
then
492
report_state
(
"
error in fetching
"
)
493
return
494
end
495
local
one
=
data
[
1
]
496
if
one
then
497
setmetatable
(
data
,
{
__index
=
one
}
)
498
end
499
return
data
,
keys
500
end
501 502
local
wraptemplate
=
[[
503local mysql = require("swiglib.mysql.core") -- will be stored in method 504 505local fetch_fields = mysql.util_mysql_fetch_fields_from_current_row 506 507local converters = utilities.sql.converters 508local deserialize = utilities.sql.deserialize 509 510local tostring = tostring 511local tonumber = tonumber 512local booleanstring = string.booleanstring 513 514%s 515 516return function(result) 517 if not result then 518 return { } 519 end 520 local nofrows = result.nofrows or 0 521 if nofrows == 0 then 522 return { } 523 end 524 local noffields = result.noffields or 0 525 local target = { } -- no %s needed here 526 result = result._result_ 527 for i=1,nofrows do 528 local cells = fetch_fields(result) 529 target[%s] = { 530 %s 531 } 532 end 533 return target 534end 535
]]
536 537
local
celltemplate
=
"
cells[%s]
"
538 539
methods
.
swiglib
=
{
540
runner
=
function
(
)
end
,
-- never called
541
execute
=
execute
,
542
initialize
=
initialize
,
-- returns session
543
usesfiles
=
false
,
544
wraptemplate
=
wraptemplate
,
545
celltemplate
=
celltemplate
,
546
}
547