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
90local json = require("util-jsn")
91local tojson = json.tostring
92local fromjson = json.tolua
93
94sql.serialize = serialize
95sql.deserialize = deserialize
96
97helpers.serialize = serialize
98helpers.deserialize = deserialize
99
100sql.tojson = tojson
101sql.fromjson = fromjson
102
103helpers.tojson = tojson
104helpers.fromjson = fromjson
105
106local defaults = { __index =
107 {
108 resultfile = "result.dat",
109 templatefile = "template.sql",
110 queryfile = "query.sql",
111 variables = { },
112 username = "default",
113 password = "default",
114 host = "localhost",
115 port = 3306,
116 database = "default",
117 },
118}
119
120if optional then
121
122 local methods = {
123 ffi = "mysql",
124 library = "mysql",
125 postgress = "postgress",
126 sqlite = "sqlite",
127 sqlite3 = "sqlite",
128 }
129
130 setmetatableindex(sql.methods,function(t,k)
131 local m = methods[k]
132 if m then
133 report_state("start loading method %a as %a",k,m)
134 require("libs-imp-" .. m .. ".lmt")
135 report_state("loading method %a done",k)
136 return rawget(t,m)
137 else
138 report_state("invalid method %a",tostring(k))
139 end
140 end)
141
142else
143
144 setmetatableindex(sql.methods,function(t,k)
145 if type(k) == "string" then
146 report_state("start loading method %a",k)
147 require("util-sql-imp-" .. k)
148 report_state("loading method %a done",k)
149 return rawget(t,k)
150 else
151 report_state("invalid method %a",tostring(k))
152 end
153 end)
154
155end
156
157
158
159local converters = { }
160sql.converters = converters
161
162local function makeconverter(entries,celltemplate,wraptemplate)
163 local shortcuts = { }
164 local assignments = { }
165 local key = false
166 for i=1,#entries do
167 local entry = entries[i]
168 local name = entry.name
169 local kind = entry.type or entry.kind
170 local value = format(celltemplate,i,i)
171 if kind == "boolean" then
172 assignments[#assignments+1] = format("[%q] = booleanstring(%s),",name,value)
173 elseif kind == "number" then
174 assignments[#assignments+1] = format("[%q] = tonumber(%s),",name,value)
175 elseif type(kind) == "function" then
176 local c = #converters + 1
177 converters[c] = kind
178 shortcuts[#shortcuts+1] = format("local fun_%s = converters[%s]",c,c)
179 assignments[#assignments+1] = format("[%q] = fun_%s(%s),",name,c,value)
180 elseif type(kind) == "table" then
181 local c = #converters + 1
182 converters[c] = kind
183 shortcuts[#shortcuts+1] = format("local tab_%s = converters[%s]",c,c)
184 assignments[#assignments+1] = format("[%q] = tab_%s[%s],",name,#converters,value)
185 elseif kind == "deserialize" then
186 assignments[#assignments+1] = format("[%q] = deserialize(%s),",name,value)
187 elseif kind == "fromjson" then
188 assignments[#assignments+1] = format("[%q] = fromjson(%s),",name,value)
189 elseif kind == "key" then
190
191 key = value
192 elseif kind == "entry" then
193
194 local default = entry.default or ""
195 if type(default) == "string" then
196 assignments[#assignments+1] = format("[%q] = %q,",name,default)
197 else
198 assignments[#assignments+1] = format("[%q] = %s,",name,tostring(default))
199 end
200 else
201 assignments[#assignments+1] = format("[%q] = %s,",name,value)
202 end
203 end
204 local code = format(wraptemplate,concat(shortcuts,"\n"),key and "{ }" or "data",key or "i",concat(assignments,"\n "))
205 local func = load(code)
206 return func and func()
207end
208
209function sql.makeconverter(entries)
210 local fields = { }
211 for i=1,#entries do
212 fields[i] = format("`%s`",entries[i].name)
213 end
214 fields = concat(fields, ", ")
215 local converter = {
216 fields = fields
217 }
218 setmetatableindex(converter, function(t,k)
219 local sqlmethod = methods[k]
220 local v = makeconverter(entries,sqlmethod.celltemplate,sqlmethod.wraptemplate)
221 t[k] = v
222 return v
223 end)
224 return converter, fields
225end
226
227
228
229local function validspecification(specification)
230 local presets = specification.presets
231 if type(presets) == "string" then
232 presets = dofile(presets)
233 end
234 if type(presets) == "table" then
235 setmetatable(presets,defaults)
236 setmetatable(specification,{ __index = presets })
237 else
238 setmetatable(specification,defaults)
239 end
240 return true
241end
242
243helpers.validspecification = validspecification
244
245local whitespace = patterns.whitespace^0
246local eol = patterns.eol
247local separator = P(";")
248local escaped = patterns.escaped
249local dquote = patterns.dquote
250local squote = patterns.squote
251local dsquote = squote * squote
252
253local quoted = dquote * (escaped + (1-dquote))^0 * dquote
254 + squote * (escaped + dsquote + (1-squote))^0 * squote
255local comment = P("--") * (1-eol) / ""
256local query = whitespace
257 * Cs((quoted + comment + 1 - separator)^1 * Cc(";"))
258 * whitespace
259local splitter = Ct(query * (separator * query)^0)
260
261helpers.querysplitter = splitter
262
263
264
265local function validspecification(specification)
266 local presets = specification.presets
267 if type(presets) == "string" then
268 presets = dofile(presets)
269 end
270 if type(presets) == "table" then
271 local m = getmetatable(presets)
272 if m then
273 setmetatable(m,defaults)
274 else
275 setmetatable(presets,defaults)
276 end
277 setmetatable(specification,{ __index = presets })
278 else
279 setmetatable(specification,defaults)
280 end
281 local templatefile = specification.templatefile or "query"
282 local name = file.nameonly(templatefile)
283 local queryfile = specification.queryfile or presets.queryfile or format("%s-temp.sql",name)
284 local resultfile = specification.resultfile or presets.resultfile or format("%s-temp.dat",name)
285 specification.queryfile = queryfile
286 specification.resultfile = resultfile
287 if trace_sql then
288 report_state("template file: %s",templatefile or "<none>")
289 report_state("query file: %s",queryfile)
290 report_state("result file: %s",resultfile)
291 end
292 return true
293end
294
295local function preparetemplate(specification)
296 local template = specification.template
297 if template then
298 local query = replacetemplate(template,specification.variables,'sql')
299 if not query then
300 report_state("error in template: %s",template)
301 elseif trace_queries then
302 report_state("query from template: %s",query)
303 end
304 return query
305 end
306 local templatefile = specification.templatefile
307 if templatefile then
308 local query = loadtemplate(templatefile,specification.variables,'sql')
309 if not query then
310 report_state("error in template file %a",templatefile)
311 elseif trace_queries then
312 report_state("query from template file %a: %s",templatefile,query)
313 end
314 return query
315 end
316 report_state("no query template or templatefile")
317end
318
319helpers.preparetemplate = preparetemplate
320
321
322
323local currentmethod
324local currentserver
325
326local function firstexecute(...)
327 local method = methods[currentmethod]
328 if not method then
329 report_state("invalid sql method")
330 sql.execute = function() end
331 return nil
332 end
333 local execute = method.execute
334 sql.execute = execute
335 return execute(...)
336end
337
338function sql.setmethod(method)
339 currentmethod = method
340 sql.execute = firstexecute
341end
342
343function sql.setserver(server)
344 currentserver = server
345end
346
347function sql.getmethod()
348 return currentmethod
349end
350
351function sql.getserver()
352 return currentserver
353end
354
355sql.setmethod("library")
356sql.setserver("mysql")
357
358
359
360local sqlmethods = sql.methods
361
362function sql.usedatabase(presets,datatable)
363 local name = datatable or presets.datatable
364 if name then
365 local usedmethod = presets.method
366 local method = usedmethod and sqlmethods[usedmethod]
367 if not method then
368 usedmethod = currentmethod
369 method = usedmethod and sqlmethods[usedmethod]
370 end
371 if not method then
372 usedmethod = sql.methods.client
373 method = usedmethod and sqlmethods[usedmethod]
374 if not method then
375 report_state("invalid method")
376 return
377 end
378 end
379 local base = presets.database or "test"
380 local basename = format("`%s`.`%s`",base,name)
381 local execute = nil
382 local m_execute = method.execute
383 if not m_execute then
384 execute = function()
385 report_state("no valid execute handler")
386 end
387 elseif method.usesfiles then
388 local queryfile = presets.queryfile or format("%s-temp.sql",name)
389 local resultfile = presets.resultfile or format("%s-temp.dat",name)
390 execute = function(specification)
391 if not specification.presets then specification.presets = presets end
392 if not specification.queryfile then specification.queryfile = queryfile end
393 if not specification.resultfile then specification.resultfile = resultfile end
394 return m_execute(specification)
395 end
396 else
397 execute = function(specification)
398 if not specification.presets then specification.presets = presets end
399 return m_execute(specification)
400 end
401 end
402 local function unpackdata(records,name)
403 if records then
404 name = name or "data"
405 for i=1,#records do
406 local record = records[i]
407 local data = record[name]
408 if data then
409 record[name] = deserialize(data)
410 end
411 end
412 end
413 end
414 local function unpackjson(records,name)
415 if records then
416 name = name or "json"
417 for i=1,#records do
418 local record = records[i]
419 local data = record[name]
420 if data then
421 record[name] = fromjson(data)
422 end
423 end
424 end
425 end
426 return {
427 usedmethod = usedmethod,
428 presets = preset,
429 base = base,
430 name = name,
431 basename = basename,
432 execute = execute,
433 serialize = serialize,
434 deserialize = deserialize,
435 tojson = tojson,
436 fromjson = fromjson,
437 unpackdata = unpackdata,
438 unpackjson = unpackjson,
439 }
440 else
441 report_state("missing name in usedatabase specification")
442 end
443end
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483sql.tokens = {
484 length = 42,
485 new = function()
486 return format("%s-%x06",osuuid(),random(0xFFFFF))
487 end,
488}
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504if tex and tex.systemmodes then
505
506 local droptable = table.drop
507 local threshold = 16 * 1024
508
509 function sql.prepare(specification,tag)
510
511
512 local tag = tag or specification.tag or "last"
513 local filename = format("%s-sql-result-%s.tuc",tex.jobname,tag)
514 if tex.systemmodes["first"] then
515 local data, keys = sql.execute(specification)
516 if not data then
517 data = { }
518 end
519 if not keys then
520 keys = { }
521 end
522 io.savedata(filename,droptable({ data = data, keys = keys },#keys*#data>threshold))
523 return data, keys
524 else
525 local result = table.load(filename)
526 return result.data, result.keys
527 end
528 end
529
530else
531
532 sql.prepare = sql.execute
533
534end
535
536return sql
537 |