util-sql-imp-client.lua /size: 9416 b    last modification: 2020-07-01 14:35
1
if
not
modules
then
modules
=
{
}
end
modules
[
'
util-sql-imp-client
'
]
=
{
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
-- todo: make a converter
10 11
local
rawset
,
setmetatable
=
rawset
,
setmetatable
12
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
13 14
local
trace_sql
=
false
trackers
.
register
(
"
sql.trace
"
,
function
(
v
)
trace_sql
=
v
end
)
15
local
trace_queries
=
false
trackers
.
register
(
"
sql.queries
"
,
function
(
v
)
trace_queries
=
v
end
)
16
local
report_state
=
logs
.
reporter
(
"
sql
"
,
"
client
"
)
17 18
local
sql
=
utilities
.
sql
19
local
helpers
=
sql
.
helpers
20
local
methods
=
sql
.
methods
21
local
validspecification
=
helpers
.
validspecification
22
local
preparetemplate
=
helpers
.
preparetemplate
23
local
splitdata
=
helpers
.
splitdata
24
local
replacetemplate
=
utilities
.
templates
.
replace
25
local
serialize
=
sql
.
serialize
26
local
deserialize
=
sql
.
deserialize
27
local
getserver
=
sql
.
getserver
28 29
local
osclock
=
os
.
gettimeofday
30 31
-- Experiments with an p/action demonstrated that there is not much gain. We could do a runtime
32
-- capture but creating all the small tables is not faster and it doesn't work well anyway.
33 34
local
separator
=
P
(
"
\t
"
)
35
local
newline
=
patterns
.
newline
36
local
empty
=
Cc
(
"
"
)
37 38
local
entry
=
C
(
(
1
-
separator
-
newline
)
^
0
)
-- C 10% faster than Cs
39 40
local
unescaped
=
P
(
"
\\n
"
)
/
"
\n
"
41
+
P
(
"
\\t
"
)
/
"
\t
"
42
+
P
(
"
\\0
"
)
/
"
\000
"
43
+
P
(
"
\\\\
"
)
/
"
\\
"
44 45
local
entry
=
Cs
(
(
unescaped
+
(
1
-
separator
-
newline
)
)
^
0
)
-- C 10% faster than Cs but Cs needed due to nesting
46 47
local
getfirst
=
Ct
(
entry
*
(
separator
*
(
entry
+
empty
)
)
^
0
)
+
newline
48
local
skipfirst
=
(
1
-
newline
)
^
1
*
newline
49
local
skipdashes
=
(
P
(
"
-
"
)
+
separator
)
^
1
*
newline
50
local
getfirstline
=
C
(
(
1
-
newline
)
^
0
)
51 52
local
cache
=
{
}
53 54
local
function
splitdata
(
data
)
-- todo: hash on first line ... maybe move to client module
55
if
data
=
=
"
"
then
56
if
trace_sql
then
57
report_state
(
"
no data
"
)
58
end
59
return
{
}
,
{
}
60
end
61
local
first
=
lpegmatch
(
getfirstline
,
data
)
62
if
not
first
then
63
if
trace_sql
then
64
report_state
(
"
no data
"
)
65
end
66
return
{
}
,
{
}
67
end
68
local
p
=
cache
[
first
]
69
if
p
then
70
-- report_state("reusing: %s",first)
71
local
entries
=
lpegmatch
(
p
.
parser
,
data
)
72
return
entries
or
{
}
,
p
.
keys
73
elseif
p
=
=
false
then
74
return
{
}
,
{
}
75
elseif
p
=
=
nil
then
76
local
keys
=
lpegmatch
(
getfirst
,
first
)
or
{
}
77
if
#
keys
=
=
0
then
78
if
trace_sql
then
79
report_state
(
"
no banner
"
)
80
end
81
cache
[
first
]
=
false
82
return
{
}
,
{
}
83
end
84
-- quite generic, could be a helper
85
local
n
=
#
keys
86
if
n
=
=
0
then
87
report_state
(
"
no fields
"
)
88
cache
[
first
]
=
false
89
return
{
}
,
{
}
90
end
91
if
n
=
=
1
then
92
local
key
=
keys
[
1
]
93
if
trace_sql
then
94
report_state
(
"
one field with name %a
"
,
key
)
95
end
96
p
=
Cg
(
Cc
(
key
)
*
entry
)
97
else
98
for
i
=
1
,
n
do
99
local
key
=
keys
[
i
]
100
if
trace_sql
then
101
report_state
(
"
field %s has name %a
"
,
i
,
key
)
102
end
103
local
s
=
Cg
(
Cc
(
key
)
*
entry
)
104
if
p
then
105
p
=
p
*
separator
*
s
106
else
107
p
=
s
108
end
109
end
110
end
111
p
=
Cf
(
Ct
(
"
"
)
*
p
,
rawset
)
*
newline
^
1
112
if
getserver
(
)
=
=
"
mssql
"
then
113
p
=
skipfirst
*
skipdashes
*
Ct
(
p
^
0
)
114
else
115
p
=
skipfirst
*
Ct
(
p
^
0
)
116
end
117
cache
[
first
]
=
{
parser
=
p
,
keys
=
keys
}
118
local
entries
=
lpegmatch
(
p
,
data
)
119
return
entries
or
{
}
,
keys
120
end
121
end
122 123
local
splitter
=
skipfirst
*
Ct
(
(
Ct
(
entry
*
(
separator
*
entry
)
^
0
)
*
newline
^
1
)
^
0
)
124 125
local
function
getdata
(
data
)
126
return
lpegmatch
(
splitter
,
data
)
127
end
128 129
helpers
.
splitdata
=
splitdata
130
helpers
.
getdata
=
getdata
131 132
local
t_runner
=
{
133
mysql
=
[[
mysql --batch --user="%username%" --password="%password%" --host="%host%" --port=%port% --database="%database%" --default-character-set=utf8 < "%queryfile%" > "%resultfile%"
]]
,
134
mssql
=
[[
sqlcmd -S %host% %?U: -U "%username%" ?% %?P: -P "%password%" ?% -I -W -w 65535 -s"
]]
.
.
"
\t
"
.
.
[[
" -m 1 -i "%queryfile%" -o "%resultfile%"
]]
,
135
}
136 137
local
t_runner_login
=
{
138
mysql
=
[[
mysql --login-path="%login%" --batch --database="%database%" --default-character-set=utf8 < "%queryfile%" > "%resultfile%"
]]
,
139
mssql
=
[[
sqlcmd -S %host% %?U: -U "%username%" ?% %?P: -P "%password%" ?% -I -W -w 65535 -s"
]]
.
.
"
\t
"
.
.
[[
" -m 1 -i "%queryfile%" -o "%resultfile%"
]]
,
140
}
141 142
local
t_preamble
=
{
143
mysql
=
[[
144SET GLOBAL SQL_MODE=ANSI_QUOTES; 145
]]
,
146
mssql
=
[[
147:setvar SQLCMDERRORLEVEL 1 148SET QUOTED_IDENTIFIER ON; 149SET NOCOUNT ON; 150%?database: USE %database%; ?% 151
]]
,
152
}
153 154
local
function
dataprepared
(
specification
)
155
local
query
=
preparetemplate
(
specification
)
156
if
query
then
157
local
preamble
=
t_preamble
[
getserver
(
)
]
or
t_preamble
.
mysql
158
if
preamble
then
159
preamble
=
replacetemplate
(
preamble
,
specification
.
variables
,
'
sql
'
)
160
query
=
preamble
.
.
"
\n
"
.
.
query
161
end
162
io
.
savedata
(
specification
.
queryfile
,
query
)
163
os
.
remove
(
specification
.
resultfile
)
164
if
trace_queries
then
165
report_state
(
"
query: %s
"
,
query
)
166
end
167
return
true
168
else
169
-- maybe push an error
170
os
.
remove
(
specification
.
queryfile
)
171
os
.
remove
(
specification
.
resultfile
)
172
end
173
end
174 175
local
function
datafetched
(
specification
)
176
local
runner
=
(
specification
.
login
and
t_runner_login
or
t_runner
)
[
getserver
(
)
]
or
t_runner
.
mysql
177
local
command
=
replacetemplate
(
runner
,
specification
)
178
if
trace_sql
then
179
local
t
=
osclock
(
)
180
report_state
(
"
command: %s
"
,
command
)
181
-- for now we don't use sandbox.registerrunners as this module is
182
-- also used outside context
183
local
okay
=
os
.
execute
(
command
)
184
report_state
(
"
fetchtime: %.3f sec, return code: %i
"
,
osclock
(
)
-
t
,
okay
)
-- not okay under linux
185
return
okay
=
=
0
186
else
187
return
os
.
execute
(
command
)
=
=
0
188
end
189
end
190 191
local
function
dataloaded
(
specification
)
192
if
trace_sql
then
193
local
t
=
osclock
(
)
194
local
data
=
io
.
loaddata
(
specification
.
resultfile
)
or
"
"
195
report_state
(
"
datasize: %.3f MB
"
,
#
data
/
1024
/
1024
)
196
report_state
(
"
loadtime: %.3f sec
"
,
osclock
(
)
-
t
)
197
return
data
198
else
199
return
io
.
loaddata
(
specification
.
resultfile
)
or
"
"
200
end
201
end
202 203
local
function
dataconverted
(
data
,
converter
)
204
if
converter
then
205
local
data
=
getdata
(
data
)
206
if
data
then
207
data
=
converter
.
client
(
data
)
208
end
209
return
data
210
elseif
trace_sql
then
211
local
t
=
osclock
(
)
212
local
data
,
keys
=
splitdata
(
data
,
target
)
213
report_state
(
"
converttime: %.3f
"
,
osclock
(
)
-
t
)
214
report_state
(
"
keys: %s
"
,
#
keys
)
215
report_state
(
"
entries: %s
"
,
#
data
)
216
return
data
,
keys
217
else
218
return
splitdata
(
data
)
219
end
220
end
221 222
-- todo: new, etc
223 224
local
function
execute
(
specification
)
225
if
trace_sql
then
226
report_state
(
"
executing client
"
)
227
end
228
if
not
validspecification
(
specification
)
then
229
report_state
(
"
error in specification
"
)
230
return
231
end
232
if
not
dataprepared
(
specification
)
then
233
report_state
(
"
error in preparation
"
)
234
return
235
end
236
if
not
datafetched
(
specification
)
then
237
report_state
(
"
error in fetching, query: %s
"
,
string
.
collapsespaces
(
io
.
loaddata
(
specification
.
queryfile
)
or
"
?
"
)
)
238
return
239
end
240
local
data
=
dataloaded
(
specification
)
241
if
not
data
then
242
report_state
(
"
error in loading
"
)
243
return
244
end
245
local
data
,
keys
=
dataconverted
(
data
,
specification
.
converter
)
246
if
not
data
then
247
report_state
(
"
error in converting or no data
"
)
248
return
249
end
250
local
one
=
data
[
1
]
251
if
one
then
252
setmetatable
(
data
,
{
__index
=
one
}
)
253
end
254
return
data
,
keys
255
end
256 257
-- The following is not that (memory) efficient but normally we will use
258
-- the lib anyway. Of course we could make a dedicated converter and/or
259
-- hook into the splitter code but ... it makes not much sense because then
260
-- we can as well move the builder to the library modules.
261
--
262
-- Here we reuse data as the indexes are the same, unless we hash.
263 264
local
wraptemplate
=
[[
265local converters = utilities.sql.converters 266local deserialize = utilities.sql.deserialize 267 268local tostring = tostring 269local tonumber = tonumber 270local booleanstring = string.booleanstring 271 272%s 273 274return function(data) 275 local target = %s -- data or { } 276 for i=1,#data do 277 local cells = data[i] 278 target[%s] = { 279 %s 280 } 281 end 282 return target 283end 284
]]
285 286
local
celltemplate
=
"
cells[%s]
"
287 288
methods
.
client
=
{
289
execute
=
execute
,
290
usesfiles
=
true
,
291
wraptemplate
=
wraptemplate
,
292
celltemplate
=
celltemplate
,
293
}
294