util-sql.lua /size: 15 Kb    last modification: 2021-10-28 13:50
1
if
not
modules
then
modules
=
{
}
end
modules
[
'
util-sql
'
]
=
{
2
version
=
1
.
001
,
3
comment
=
"
companion to m-sql.mkiv
"
,
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
-- todo: templates as table (saves splitting)
10 11
-- Of course we could use a library but we don't want another depedency and there is
12
-- a bit of flux in these libraries. Also, we want the data back in a way that we
13
-- like.
14
--
15
-- This is the first of set of sql related modules that are providing functionality
16
-- for a web based framework that we use for typesetting (related) services. We're
17
-- talking of session management, job ticket processing, storage, (xml) file processing
18
-- and dealing with data from databases (often ambitiously called database publishing).
19
--
20
-- There is no generic solution for such services, but from our perspective, as we use
21
-- context in a regular tds tree (the standard distribution) it makes sense to put shared
22
-- code in the context distribution. That way we don't need to reinvent wheels every time.
23 24
-- We use the template mechanism from util-tpl which inturn is just using the dos cq
25
-- windows convention of %whatever% variables that I've used for ages.
26 27
-- util-sql-imp-client.lua
28
-- util-sql-imp-library.lua
29
-- util-sql-imp-lmxsql.lua
30 31
-- local sql = require("util-sql")
32
--
33
-- local converter = sql.makeconverter {
34
-- { name = "id", type = "number" },
35
-- { name = "data",type = "string" },
36
-- }
37
--
38
-- local execute = sql.methods.library.execute
39
-- -- local execute = sql.methods.client.execute
40
-- -- local execute = sql.methods.lmxsql.execute
41
--
42
-- result = execute {
43
-- presets = {
44
-- host = "localhost",
45
-- username = "root",
46
-- password = "test",
47
-- database = "test",
48
-- id = "test", -- forces persistent session
49
-- },
50
-- template = "select * from `test` where `id` > %criterium% ;",
51
-- variables = {
52
-- criterium = 2,
53
-- },
54
-- converter = converter
55
-- }
56
--
57
-- inspect(result)
58 59
local
format
,
match
=
string
.
format
,
string
.
match
60
local
random
=
math
.
random
61
local
rawset
,
rawget
,
setmetatable
,
getmetatable
,
load
,
type
=
rawset
,
rawget
,
setmetatable
,
getmetatable
,
load
,
type
62
local
P
,
S
,
V
,
C
,
Cs
,
Ct
,
Cc
,
Cg
,
Cf
,
patterns
,
lpegmatch
=
lpeg
.
P
,
lpeg
.
S
,
lpeg
.
V
,
lpeg
.
C
,
lpeg
.
Cs
,
lpeg
.
Ct
,
lpeg
.
Cc
,
lpeg
.
Cg
,
lpeg
.
Cf
,
lpeg
.
patterns
,
lpeg
.
match
63
local
concat
=
table
.
concat
64 65
local
osuuid
=
os
.
uuid
66
local
setmetatableindex
=
table
.
setmetatableindex
67 68
local
trace_sql
=
false
trackers
.
register
(
"
sql.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
69
local
trace_queries
=
false
trackers
.
register
(
"
sql.queries
"
,
function
(
v
)
trace_queries
=
v
end
)
70
local
report_state
=
logs
.
reporter
(
"
sql
"
)
71 72
-- trace_sql = true
73
-- trace_queries = true
74 75
utilities
.
sql
=
utilities
.
sql
or
{
}
76
local
sql
=
utilities
.
sql
77 78
local
replacetemplate
=
utilities
.
templates
.
replace
79
local
loadtemplate
=
utilities
.
templates
.
load
80 81
local
methods
=
{
}
82
sql
.
methods
=
methods
83 84
local
helpers
=
{
}
85
sql
.
helpers
=
helpers
86 87
local
serialize
=
table
.
fastserialize
88
local
deserialize
=
table
.
deserialize
89 90
sql
.
serialize
=
serialize
91
sql
.
deserialize
=
deserialize
92 93
helpers
.
serialize
=
serialize
-- bonus
94
helpers
.
deserialize
=
deserialize
-- bonus
95 96
local
defaults
=
{
__index
=
97
{
98
resultfile
=
"
result.dat
"
,
99
templatefile
=
"
template.sql
"
,
100
queryfile
=
"
query.sql
"
,
101
variables
=
{
}
,
102
username
=
"
default
"
,
103
password
=
"
default
"
,
104
host
=
"
localhost
"
,
-- 127.0.0.1 is sometimes more reliable
105
port
=
3306
,
106
database
=
"
default
"
,
107
}
,
108
}
109 110
if
optional
then
111 112
local
methods
=
{
113
ffi
=
"
mysql
"
,
114
library
=
"
mysql
"
,
115
postgress
=
"
postgress
"
,
116
sqlite
=
"
sqlite
"
,
117
sqlite3
=
"
sqlite
"
,
118
}
119 120
setmetatableindex
(
sql
.
methods
,
function
(
t
,
k
)
121
local
m
=
methods
[
k
]
122
if
m
then
123
report_state
(
"
start loading method %a as %a
"
,
k
,
m
)
124
require
(
"
libs-imp-
"
.
.
m
)
125
report_state
(
"
loading method %a done
"
,
k
)
126
return
rawget
(
t
,
m
)
127
else
128
report_state
(
"
invalid method %a
"
,
tostring
(
k
)
)
129
end
130
end
)
131 132
else
133 134
setmetatableindex
(
sql
.
methods
,
function
(
t
,
k
)
135
if
type
(
k
)
=
=
"
string
"
then
136
report_state
(
"
start loading method %a
"
,
k
)
137
require
(
"
util-sql-imp-
"
.
.
k
)
138
report_state
(
"
loading method %a done
"
,
k
)
139
return
rawget
(
t
,
k
)
140
else
141
report_state
(
"
invalid method %a
"
,
tostring
(
k
)
)
142
end
143
end
)
144 145
end
146 147
-- converters
148 149
local
converters
=
{
}
150
sql
.
converters
=
converters
151 152
local
function
makeconverter
(
entries
,
celltemplate
,
wraptemplate
)
153
local
shortcuts
=
{
}
154
local
assignments
=
{
}
155
local
key
=
false
156
for
i
=
1
,
#
entries
do
157
local
entry
=
entries
[
i
]
158
local
name
=
entry
.
name
159
local
kind
=
entry
.
type
or
entry
.
kind
160
local
value
=
format
(
celltemplate
,
i
,
i
)
161
if
kind
=
=
"
boolean
"
then
162
assignments
[
#
assignments
+
1
]
=
format
(
"
[%q] = booleanstring(%s),
"
,
name
,
value
)
163
elseif
kind
=
=
"
number
"
then
164
assignments
[
#
assignments
+
1
]
=
format
(
"
[%q] = tonumber(%s),
"
,
name
,
value
)
165
elseif
type
(
kind
)
=
=
"
function
"
then
166
local
c
=
#
converters
+
1
167
converters
[
c
]
=
kind
168
shortcuts
[
#
shortcuts
+
1
]
=
format
(
"
local fun_%s = converters[%s]
"
,
c
,
c
)
169
assignments
[
#
assignments
+
1
]
=
format
(
"
[%q] = fun_%s(%s),
"
,
name
,
c
,
value
)
170
elseif
type
(
kind
)
=
=
"
table
"
then
171
local
c
=
#
converters
+
1
172
converters
[
c
]
=
kind
173
shortcuts
[
#
shortcuts
+
1
]
=
format
(
"
local tab_%s = converters[%s]
"
,
c
,
c
)
174
assignments
[
#
assignments
+
1
]
=
format
(
"
[%q] = tab_%s[%s],
"
,
name
,
#
converters
,
value
)
175
elseif
kind
=
=
"
deserialize
"
then
176
assignments
[
#
assignments
+
1
]
=
format
(
"
[%q] = deserialize(%s),
"
,
name
,
value
)
177
elseif
kind
=
=
"
key
"
then
178
-- hashed instead of indexed
179
key
=
value
180
elseif
kind
=
=
"
entry
"
then
181
-- so we can (efficiently) extend the hashed table
182
local
default
=
entry
.
default
or
"
"
183
if
type
(
default
)
=
=
"
string
"
then
184
assignments
[
#
assignments
+
1
]
=
format
(
"
[%q] = %q,
"
,
name
,
default
)
185
else
186
assignments
[
#
assignments
+
1
]
=
format
(
"
[%q] = %s,
"
,
name
,
tostring
(
default
)
)
187
end
188
else
189
assignments
[
#
assignments
+
1
]
=
format
(
"
[%q] = %s,
"
,
name
,
value
)
190
end
191
end
192
local
code
=
format
(
wraptemplate
,
concat
(
shortcuts
,
"
\n
"
)
,
key
and
"
{ }
"
or
"
data
"
,
key
or
"
i
"
,
concat
(
assignments
,
"
\n
"
)
)
193
local
func
=
load
(
code
)
194
return
func
and
func
(
)
195
end
196 197
function
sql
.
makeconverter
(
entries
)
198
local
fields
=
{
}
199
for
i
=
1
,
#
entries
do
200
fields
[
i
]
=
format
(
"
`%s`
"
,
entries
[
i
]
.
name
)
201
end
202
fields
=
concat
(
fields
,
"
,
"
)
203
local
converter
=
{
204
fields
=
fields
205
}
206
setmetatableindex
(
converter
,
function
(
t
,
k
)
207
local
sqlmethod
=
methods
[
k
]
208
local
v
=
makeconverter
(
entries
,
sqlmethod
.
celltemplate
,
sqlmethod
.
wraptemplate
)
209
t
[
k
]
=
v
210
return
v
211
end
)
212
return
converter
,
fields
213
end
214 215
-- helper for libraries:
216 217
local
function
validspecification
(
specification
)
218
local
presets
=
specification
.
presets
219
if
type
(
presets
)
=
=
"
string
"
then
220
presets
=
dofile
(
presets
)
221
end
222
if
type
(
presets
)
=
=
"
table
"
then
223
setmetatable
(
presets
,
defaults
)
224
setmetatable
(
specification
,
{
__index
=
presets
}
)
225
else
226
setmetatable
(
specification
,
defaults
)
227
end
228
return
true
229
end
230 231
helpers
.
validspecification
=
validspecification
232 233
local
whitespace
=
patterns
.
whitespace
^
0
234
local
eol
=
patterns
.
eol
235
local
separator
=
P
(
"
;
"
)
236
local
escaped
=
patterns
.
escaped
237
local
dquote
=
patterns
.
dquote
238
local
squote
=
patterns
.
squote
239
local
dsquote
=
squote
*
squote
240
---- quoted = patterns.quoted
241
local
quoted
=
dquote
*
(
escaped
+
(
1
-
dquote
)
)
^
0
*
dquote
242
+
squote
*
(
escaped
+
dsquote
+
(
1
-
squote
)
)
^
0
*
squote
243
local
comment
=
P
(
"
--
"
)
*
(
1
-
eol
)
/
"
"
244
local
query
=
whitespace
245
*
Cs
(
(
quoted
+
comment
+
1
-
separator
)
^
1
*
Cc
(
"
;
"
)
)
246
*
whitespace
247
local
splitter
=
Ct
(
query
*
(
separator
*
query
)
^
0
)
248 249
helpers
.
querysplitter
=
splitter
250 251
-- I will add a bit more checking.
252 253
local
function
validspecification
(
specification
)
254
local
presets
=
specification
.
presets
255
if
type
(
presets
)
=
=
"
string
"
then
256
presets
=
dofile
(
presets
)
257
end
258
if
type
(
presets
)
=
=
"
table
"
then
259
local
m
=
getmetatable
(
presets
)
260
if
m
then
261
setmetatable
(
m
,
defaults
)
262
else
263
setmetatable
(
presets
,
defaults
)
264
end
265
setmetatable
(
specification
,
{
__index
=
presets
}
)
266
else
267
setmetatable
(
specification
,
defaults
)
268
end
269
local
templatefile
=
specification
.
templatefile
or
"
query
"
270
local
name
=
file
.
nameonly
(
templatefile
)
271
local
queryfile
=
specification
.
queryfile
or
presets
.
queryfile
or
format
(
"
%s-temp.sql
"
,
name
)
272
local
resultfile
=
specification
.
resultfile
or
presets
.
resultfile
or
format
(
"
%s-temp.dat
"
,
name
)
273
specification
.
queryfile
=
queryfile
274
specification
.
resultfile
=
resultfile
275
if
trace_sql
then
276
report_state
(
"
template file: %s
"
,
templatefile
or
"
<none>
"
)
277
report_state
(
"
query file: %s
"
,
queryfile
)
278
report_state
(
"
result file: %s
"
,
resultfile
)
279
end
280
return
true
281
end
282 283
local
function
preparetemplate
(
specification
)
284
local
template
=
specification
.
template
285
if
template
then
286
local
query
=
replacetemplate
(
template
,
specification
.
variables
,
'
sql
'
)
287
if
not
query
then
288
report_state
(
"
error in template: %s
"
,
template
)
289
elseif
trace_queries
then
290
report_state
(
"
query from template: %s
"
,
query
)
291
end
292
return
query
293
end
294
local
templatefile
=
specification
.
templatefile
295
if
templatefile
then
296
local
query
=
loadtemplate
(
templatefile
,
specification
.
variables
,
'
sql
'
)
297
if
not
query
then
298
report_state
(
"
error in template file %a
"
,
templatefile
)
299
elseif
trace_queries
then
300
report_state
(
"
query from template file %a: %s
"
,
templatefile
,
query
)
301
end
302
return
query
303
end
304
report_state
(
"
no query template or templatefile
"
)
305
end
306 307
helpers
.
preparetemplate
=
preparetemplate
308 309
-- -- -- we delay setting this -- -- --
310 311
local
currentmethod
312
local
currentserver
313 314
local
function
firstexecute
(
...
)
315
local
method
=
methods
[
currentmethod
]
316
if
not
method
then
317
report_state
(
"
invalid sql method
"
)
318
sql
.
execute
=
function
(
)
end
319
return
nil
320
end
321
local
execute
=
method
.
execute
322
sql
.
execute
=
execute
323
return
execute
(
...
)
324
end
325 326
function
sql
.
setmethod
(
method
)
327
currentmethod
=
method
328
sql
.
execute
=
firstexecute
329
end
330 331
function
sql
.
setserver
(
server
)
332
currentserver
=
server
333
end
334 335
function
sql
.
getmethod
(
)
336
return
currentmethod
337
end
338 339
function
sql
.
getserver
(
)
340
return
currentserver
341
end
342 343
sql
.
setmethod
(
"
library
"
)
344
sql
.
setserver
(
"
mysql
"
)
345 346
-- helper:
347 348
local
sqlmethods
=
sql
.
methods
349 350
function
sql
.
usedatabase
(
presets
,
datatable
)
351
local
name
=
datatable
or
presets
.
datatable
352
if
name
then
353
local
usedmethod
=
presets
.
method
354
local
method
=
usedmethod
and
sqlmethods
[
usedmethod
]
355
if
not
method
then
356
usedmethod
=
currentmethod
357
method
=
usedmethod
and
sqlmethods
[
usedmethod
]
358
end
359
if
not
method
then
360
usedmethod
=
sql
.
methods
.
client
361
method
=
usedmethod
and
sqlmethods
[
usedmethod
]
362
if
not
method
then
363
report_state
(
"
invalid method
"
)
364
return
365
end
366
end
367
local
base
=
presets
.
database
or
"
test
"
368
local
basename
=
format
(
"
`%s`.`%s`
"
,
base
,
name
)
369
local
execute
=
nil
370
local
m_execute
=
method
.
execute
371
if
not
m_execute
then
372
execute
=
function
(
)
373
report_state
(
"
no valid execute handler
"
)
374
end
375
elseif
method
.
usesfiles
then
376
local
queryfile
=
presets
.
queryfile
or
format
(
"
%s-temp.sql
"
,
name
)
377
local
resultfile
=
presets
.
resultfile
or
format
(
"
%s-temp.dat
"
,
name
)
378
execute
=
function
(
specification
)
-- variables template
379
if
not
specification
.
presets
then
specification
.
presets
=
presets
end
380
if
not
specification
.
queryfile
then
specification
.
queryfile
=
queryfile
end
381
if
not
specification
.
resultfile
then
specification
.
resultfile
=
resultfile
end
382
return
m_execute
(
specification
)
383
end
384
else
385
execute
=
function
(
specification
)
-- variables template
386
if
not
specification
.
presets
then
specification
.
presets
=
presets
end
387
return
m_execute
(
specification
)
388
end
389
end
390
local
function
unpackdata
(
records
,
name
)
391
if
records
then
392
name
=
name
or
"
data
"
393
for
i
=
1
,
#
records
do
394
local
record
=
records
[
i
]
395
local
data
=
record
[
name
]
396
if
data
then
397
record
[
name
]
=
deserialize
(
data
)
398
end
399
end
400
end
401
end
402
return
{
403
usedmethod
=
usedmethod
,
404
presets
=
preset
,
405
base
=
base
,
406
name
=
name
,
407
basename
=
basename
,
408
execute
=
execute
,
409
serialize
=
serialize
,
410
deserialize
=
deserialize
,
411
unpackdata
=
unpackdata
,
412
}
413
else
414
report_state
(
"
missing name in usedatabase specification
"
)
415
end
416
end
417 418
-- local data = utilities.sql.prepare {
419
-- templatefile = "test.sql",
420
-- variables = { },
421
-- host = "...",
422
-- username = "...",
423
-- password = "...",
424
-- database = "...",
425
-- }
426 427
-- local presets = {
428
-- host = "...",
429
-- username = "...",
430
-- password = "...",
431
-- database = "...",
432
-- }
433
--
434
-- local data = utilities.sql.prepare {
435
-- templatefile = "test.sql",
436
-- variables = { },
437
-- presets = presets,
438
-- }
439 440
-- local data = utilities.sql.prepare {
441
-- templatefile = "test.sql",
442
-- variables = { },
443
-- presets = dofile(...),
444
-- }
445 446
-- local data = utilities.sql.prepare {
447
-- templatefile = "test.sql",
448
-- variables = { },
449
-- presets = "...",
450
-- }
451 452
-- for i=1,10 do
453
-- local dummy = uuid() -- else same every time, don't ask
454
-- end
455 456
sql
.
tokens
=
{
457
length
=
42
,
-- but in practice we will reserve some 50 characters
458
new
=
function
(
)
459
return
format
(
"
%s-%x06
"
,
osuuid
(
)
,
random
(
0xFFFFF
)
)
-- 36 + 1 + 6 = 42
460
end
,
461
}
462 463
-- -- --
464 465
-- local func, code = sql.makeconverter {
466
-- { name = "a", type = "number" },
467
-- { name = "b", type = "string" },
468
-- { name = "c", type = "boolean" },
469
-- { name = "d", type = { x = "1" } },
470
-- { name = "e", type = os.fulltime },
471
-- }
472
--
473
-- print(code)
474 475
-- -- --
476 477
if
tex
and
tex
.
systemmodes
then
478 479
local
droptable
=
table
.
drop
480
local
threshold
=
16
*
1024
-- use slower but less memory hungry variant
481 482
function
sql
.
prepare
(
specification
,
tag
)
483
-- could go into tuc if needed
484
-- todo: serialize per column
485
local
tag
=
tag
or
specification
.
tag
or
"
last
"
486
local
filename
=
format
(
"
%s-sql-result-%s.tuc
"
,
tex
.
jobname
,
tag
)
487
if
tex
.
systemmodes
[
"
first
"
]
then
488
local
data
,
keys
=
sql
.
execute
(
specification
)
489
if
not
data
then
490
data
=
{
}
491
end
492
if
not
keys
then
493
keys
=
{
}
494
end
495
io
.
savedata
(
filename
,
droptable
(
{
data
=
data
,
keys
=
keys
}
,
#
keys
*
#
data
>
threshold
)
)
496
return
data
,
keys
497
else
498
local
result
=
table
.
load
(
filename
)
499
return
result
.
data
,
result
.
keys
500
end
501
end
502 503
else
504 505
sql
.
prepare
=
sql
.
execute
506 507
end
508 509
return
sql
510