1if 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
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59local format, match = string.format, string.match
60local random = math.random
61local rawset, rawget, setmetatable, getmetatable, load, type = rawset, rawget, setmetatable, getmetatable, load, type
62local 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
63local concat = table.concat
64
65local osuuid = os.uuid
66local setmetatableindex = table.setmetatableindex
67
68local trace_sql = false trackers.register("sql.trace", function(v) trace_sql = v end)
69local trace_queries = false trackers.register("sql.queries",function(v) trace_queries = v end)
70local report_state = logs.reporter("sql")
71
72
73
74
75utilities.sql = utilities.sql or { }
76local sql = utilities.sql
77
78local replacetemplate = utilities.templates.replace
79local loadtemplate = utilities.templates.load
80
81local methods = { }
82sql.methods = methods
83
84local helpers = { }
85sql.helpers = helpers
86
87local serialize = table.fastserialize
88local deserialize = table.deserialize
89
90sql.serialize = serialize
91sql.deserialize = deserialize
92
93helpers.serialize = serialize
94helpers.deserialize = deserialize
95
96local 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",
105 port = 3306,
106 database = "default",
107 },
108}
109
110if 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
132else
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
145end
146
147
148
149local converters = { }
150sql.converters = converters
151
152local 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
179 key = value
180 elseif kind == "entry" then
181
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()
195end
196
197function 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
213end
214
215
216
217local 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
229end
230
231helpers.validspecification = validspecification
232
233local whitespace = patterns.whitespace^0
234local eol = patterns.eol
235local separator = P(";")
236local escaped = patterns.escaped
237local dquote = patterns.dquote
238local squote = patterns.squote
239local dsquote = squote * squote
240
241local quoted = dquote * (escaped + (1-dquote))^0 * dquote
242 + squote * (escaped + dsquote + (1-squote))^0 * squote
243local comment = P("--") * (1-eol) / ""
244local query = whitespace
245 * Cs((quoted + comment + 1 - separator)^1 * Cc(";"))
246 * whitespace
247local splitter = Ct(query * (separator * query)^0)
248
249helpers.querysplitter = splitter
250
251
252
253local 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
281end
282
283local 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")
305end
306
307helpers.preparetemplate = preparetemplate
308
309
310
311local currentmethod
312local currentserver
313
314local 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(...)
324end
325
326function sql.setmethod(method)
327 currentmethod = method
328 sql.execute = firstexecute
329end
330
331function sql.setserver(server)
332 currentserver = server
333end
334
335function sql.getmethod()
336 return currentmethod
337end
338
339function sql.getserver()
340 return currentserver
341end
342
343sql.setmethod("library")
344sql.setserver("mysql")
345
346
347
348local sqlmethods = sql.methods
349
350function 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)
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)
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
416end
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456sql.tokens = {
457 length = 42,
458 new = function()
459 return format("%s-%x06",osuuid(),random(0xFFFFF))
460 end,
461}
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477if tex and tex.systemmodes then
478
479 local droptable = table.drop
480 local threshold = 16 * 1024
481
482 function sql.prepare(specification,tag)
483
484
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
503else
504
505 sql.prepare = sql.execute
506
507end
508
509return sql
510 |