1if 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
10
11local rawset, setmetatable = rawset, setmetatable
12local 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
14local trace_sql = false trackers.register("sql.trace", function(v) trace_sql = v end)
15local trace_queries = false trackers.register("sql.queries",function(v) trace_queries = v end)
16local report_state = logs.reporter("sql","client")
17
18local sql = utilities.sql
19local helpers = sql.helpers
20local methods = sql.methods
21local validspecification = helpers.validspecification
22local preparetemplate = helpers.preparetemplate
23local splitdata = helpers.splitdata
24local replacetemplate = utilities.templates.replace
25local serialize = sql.serialize
26local getserver = sql.getserver
27
28local osclock = os.gettimeofday
29
30
31
32
33local separator = P("\t")
34local newline = patterns.newline
35local empty = Cc("")
36
37local entry = C((1-separator-newline)^0)
38
39local unescaped = P("\\n") / "\n"
40 + P("\\t") / "\t"
41 + P("\\0") / "\000"
42 + P("\\\\") / "\\"
43
44local entry = Cs((unescaped + (1-separator-newline))^0)
45
46local getfirst = Ct( entry * (separator * (entry+empty))^0) + newline
47local skipfirst = (1-newline)^1 * newline
48local skipdashes = (P("-")+separator)^1 * newline
49local getfirstline = C((1-newline)^0)
50
51local cache = { }
52
53local function splitdata(data)
54 if data == "" then
55 if trace_sql then
56 report_state("no data")
57 end
58 return { }, { }
59 end
60 local first = lpegmatch(getfirstline,data)
61 if not first then
62 if trace_sql then
63 report_state("no data")
64 end
65 return { }, { }
66 end
67 local p = cache[first]
68 if p then
69
70 local entries = lpegmatch(p.parser,data)
71 return entries or { }, p.keys
72 elseif p == false then
73 return { }, { }
74 elseif p == nil then
75 local keys = lpegmatch(getfirst,first) or { }
76 if #keys == 0 then
77 if trace_sql then
78 report_state("no banner")
79 end
80 cache[first] = false
81 return { }, { }
82 end
83
84 local n = #keys
85 if n == 0 then
86 report_state("no fields")
87 cache[first] = false
88 return { }, { }
89 end
90 if n == 1 then
91 local key = keys[1]
92 if trace_sql then
93 report_state("one field with name %a",key)
94 end
95 p = Cg(Cc(key) * entry)
96 else
97 for i=1,n do
98 local key = keys[i]
99 if trace_sql then
100 report_state("field %s has name %a",i,key)
101 end
102 local s = Cg(Cc(key) * entry)
103 if p then
104 p = p * separator * s
105 else
106 p = s
107 end
108 end
109 end
110 p = Cf(Ct("") * p,rawset) * newline^1
111
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
121end
122
123local splitter = skipfirst * Ct((Ct(entry * (separator * entry)^0) * newline^1)^0)
124
125local function getdata(data)
126 return lpegmatch(splitter,data)
127end
128
129helpers.splitdata = splitdata
130helpers.getdata = getdata
131
132local 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
137local 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
142local 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
154local 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
170 os.remove(specification.queryfile)
171 os.remove(specification.resultfile)
172 end
173end
174
175local 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
182
183 local okay = os.execute(command)
184 report_state("fetchtime: %.3f sec, return code: %i",osclock()-t,okay)
185 return okay == 0
186 else
187 return os.execute(command) == 0
188 end
189end
190
191local 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
201end
202
203local 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
220end
221
222
223
224local 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
255end
256
257
258
259
260
261
262
263
264local wraptemplate = [[
265local converters = utilities.sql.converters
266local deserialize = utilities.sql.deserialize
267local fromjson = utilities.sql.fromjson
268
269local tostring = tostring
270local tonumber = tonumber
271local booleanstring = string.booleanstring
272
273%s
274
275return function(data)
276 local target = %s -- data or { }
277 for i=1,#data do
278 local cells = data[i]
279 target[%s] = {
280 %s
281 }
282 end
283 return target
284end
285]]
286
287local celltemplate = "cells[%s]"
288
289methods.client = {
290 execute = execute,
291 usesfiles = true,
292 wraptemplate = wraptemplate,
293 celltemplate = celltemplate,
294}
295 |
