util-sql-imp-library.lua /size: 8574 b    last modification: 2020-07-01 14:35
1
if
not
modules
then
modules
=
{
}
end
modules
[
'
util-sql-imp-library
'
]
=
{
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
-- local function pcall(f,...) return true, f(...) end
10 11
-- For some reason the sql lib partially fails in luatex when creating hashed row. So far
12
-- we couldn't figure it out (some issue with adapting the table that is passes as first
13
-- argument in the fetch routine. Apart from this it looks like the mysql binding has some
14
-- efficiency issues (like creating a keys and types table for each row) but that could be
15
-- optimized. Anyhow, fetching results can be done as follows:
16 17
-- local function collect_1(r)
18
-- local t = { }
19
-- for i=1,r:numrows() do
20
-- t[#t+1] = r:fetch({},"a")
21
-- end
22
-- return t
23
-- end
24
--
25
-- local function collect_2(r)
26
-- local keys = r:getcolnames()
27
-- local n = #keys
28
-- local t = { }
29
-- for i=1,r:numrows() do
30
-- local v = { r:fetch() }
31
-- local r = { }
32
-- for i=1,n do
33
-- r[keys[i]] = v[i]
34
-- end
35
-- t[#t+1] = r
36
-- end
37
-- return t
38
-- end
39
--
40
-- local function collect_3(r)
41
-- local keys = r:getcolnames()
42
-- local n = #keys
43
-- local t = { }
44
-- for i=1,r:numrows() do
45
-- local v = r:fetch({},"n")
46
-- local r = { }
47
-- for i=1,n do
48
-- r[keys[i]] = v[i]
49
-- end
50
-- t[#t+1] = r
51
-- end
52
-- return t
53
-- end
54
--
55
-- On a large table with some 8 columns (mixed text and numbers) we get the following
56
-- timings (the 'a' alternative is already using the more efficient variant in the
57
-- binding).
58
--
59
-- collect_1 : 1.31
60
-- collect_2 : 1.39
61
-- collect_3 : 1.75
62
--
63
-- Some, as a workaround for this 'bug' the second alternative can be used.
64 65
local
format
=
string
.
format
66
local
lpegmatch
=
lpeg
.
match
67
local
setmetatable
,
type
=
setmetatable
,
type
68 69
local
trace_sql
=
false
trackers
.
register
(
"
sql.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
70
local
trace_queries
=
false
trackers
.
register
(
"
sql.queries
"
,
function
(
v
)
trace_queries
=
v
end
)
71
local
report_state
=
logs
.
reporter
(
"
sql
"
,
"
library
"
)
72 73
local
sql
=
utilities
.
sql
74
local
mysql
=
require
(
"
luasql.mysql
"
)
75
local
cache
=
{
}
76
local
helpers
=
sql
.
helpers
77
local
methods
=
sql
.
methods
78
local
validspecification
=
helpers
.
validspecification
79
local
querysplitter
=
helpers
.
querysplitter
80
local
dataprepared
=
helpers
.
preparetemplate
81
local
serialize
=
sql
.
serialize
82
local
deserialize
=
sql
.
deserialize
83
local
formatters
=
string
.
formatters
84 85
local
initialize
=
mysql
.
mysql
86 87
local
function
connect
(
session
,
specification
)
88
return
session
:
connect
(
89
specification
.
database
or
"
"
,
90
specification
.
username
or
"
"
,
91
specification
.
password
or
"
"
,
92
specification
.
host
or
"
"
,
93
specification
.
port
94
)
95
end
96 97
local
function
fetched
(
specification
,
query
,
converter
)
98
if
not
query
or
query
=
=
"
"
then
99
report_state
(
"
no valid query
"
)
100
return
false
101
end
102
local
id
=
specification
.
id
103
local
session
,
connection
104
if
id
then
105
local
c
=
cache
[
id
]
106
if
c
then
107
session
=
c
.
session
108
connection
=
c
.
connection
109
end
110
if
not
connection
then
111
session
=
initialize
(
)
112
if
not
session
then
113
return
formatters
[
"
no session for %a
"
]
(
id
)
114
end
115
connection
=
connect
(
session
,
specification
)
116
if
not
connection
then
117
return
formatters
[
"
no connection for %a
"
]
(
id
)
118
end
119
cache
[
id
]
=
{
session
=
session
,
connection
=
connection
}
120
end
121
else
122
session
=
initialize
(
)
123
if
not
session
then
124
return
"
no session
"
125
end
126
connection
=
connect
(
session
,
specification
)
127
if
not
connection
then
128
return
"
no connection
"
129
end
130
end
131
if
not
connection
then
132
report_state
(
"
error in connection: %s@%s to %s:%s
"
,
133
specification
.
database
or
"
no database
"
,
134
specification
.
username
or
"
no username
"
,
135
specification
.
host
or
"
no host
"
,
136
specification
.
port
or
"
no port
"
137
)
138
return
"
no connection
"
139
end
140
query
=
lpegmatch
(
querysplitter
,
query
)
141
local
result
,
okay
142
for
i
=
1
,
#
query
do
143
local
q
=
query
[
i
]
144
local
r
,
m
=
connection
:
execute
(
q
)
145
if
m
then
146
report_state
(
"
error in query to host %a: %s
"
,
specification
.
host
,
string
.
collapsespaces
(
q
or
"
?
"
)
)
147
if
m
then
148
report_state
(
"
message: %s
"
,
m
)
149
end
150
end
151
local
t
=
type
(
r
)
152
if
t
=
=
"
userdata
"
then
153
result
=
r
154
okay
=
true
155
elseif
t
=
=
"
number
"
then
156
okay
=
true
157
end
158
end
159
if
not
okay
then
160
if
connection
then
161
connection
:
close
(
)
162
end
163
if
session
then
164
session
:
close
(
)
165
end
166
if
id
then
167
cache
[
id
]
=
nil
168
end
169
return
"
execution error
"
170
end
171
local
data
,
keys
172
if
result
then
173
if
converter
then
174
data
=
converter
.
library
(
result
)
175
else
176
keys
=
result
:
getcolnames
(
)
177
if
keys
then
178
data
=
{
}
179
local
n
=
result
:
numrows
(
)
or
0
180
if
n
>
0
then
181
local
k
=
#
keys
182
for
i
=
1
,
n
do
183
local
v
=
{
result
:
fetch
(
)
}
184
local
d
=
{
}
185
for
i
=
1
,
k
do
186
d
[
keys
[
i
]
]
=
v
[
i
]
187
end
188
data
[
#
data
+
1
]
=
d
189
end
190
end
191
end
192
end
193
result
:
close
(
)
194
end
195
if
not
id
then
196
if
connection
then
197
connection
:
close
(
)
198
end
199
if
session
then
200
session
:
close
(
)
201
end
202
end
203
return
false
,
data
,
keys
204
end
205 206
local
function
datafetched
(
specification
,
query
,
converter
)
207
local
callokay
,
connectionerror
,
data
,
keys
=
pcall
(
fetched
,
specification
,
query
,
converter
)
208
if
not
callokay
then
209
report_state
(
"
call error, retrying
"
)
210
callokay
,
connectionerror
,
data
,
keys
=
pcall
(
fetched
,
specification
,
query
,
converter
)
211
elseif
connectionerror
then
212
report_state
(
"
error: %s, retrying
"
,
connectionerror
)
213
callokay
,
connectionerror
,
data
,
keys
=
pcall
(
fetched
,
specification
,
query
,
converter
)
214
end
215
if
not
callokay
then
216
report_state
(
"
persistent call error
"
)
217
elseif
connectionerror
then
218
report_state
(
"
persistent error: %s
"
,
connectionerror
)
219
end
220
return
data
or
{
}
,
keys
or
{
}
221
end
222 223
local
function
execute
(
specification
)
224
if
trace_sql
then
225
report_state
(
"
executing library
"
)
226
end
227
if
not
validspecification
(
specification
)
then
228
report_state
(
"
error in specification
"
)
229
return
230
end
231
local
query
=
dataprepared
(
specification
)
232
if
not
query
then
233
report_state
(
"
error in preparation
"
)
234
return
235
end
236
local
data
,
keys
=
datafetched
(
specification
,
query
,
specification
.
converter
)
237
if
not
data
then
238
report_state
(
"
error in fetching
"
)
239
return
240
end
241
local
one
=
data
[
1
]
242
if
one
then
243
setmetatable
(
data
,
{
__index
=
one
}
)
244
end
245
return
data
,
keys
246
end
247 248
-- Here we build the dataset stepwise so we don't use the data hack that
249
-- is used in the client variant.
250 251
local
wraptemplate
=
[[
252local converters = utilities.sql.converters 253local deserialize = utilities.sql.deserialize 254 255local tostring = tostring 256local tonumber = tonumber 257local booleanstring = string.booleanstring 258 259%s 260 261return function(result) 262 if not result then 263 return { } 264 end 265 local nofrows = result:numrows() or 0 266 if nofrows == 0 then 267 return { } 268 end 269 local target = { } -- no %s needed here 270 for i=1,nofrows do 271 local cells = { result:fetch() } 272 target[%s] = { 273 %s 274 } 275 end 276 return target 277end 278
]]
279 280
local
celltemplate
=
"
cells[%s]
"
281 282
methods
.
library
=
{
283
runner
=
function
(
)
end
,
-- never called
284
execute
=
execute
,
285
initialize
=
initialize
,
-- returns session
286
usesfiles
=
false
,
287
wraptemplate
=
wraptemplate
,
288
celltemplate
=
celltemplate
,
289
}
290