Source Browser ?

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-- todo: templates as table (saves splitting)
10
11-- Of course we could use a library but we don't want another depedency and there is
12-- a bit of flux in these libraries. Also, we want the data back in a way that we
13-- like.
14--
15-- This is the first of set of sql related modules that are providing functionality
16-- for a web based framework that we use for typesetting (related) services. We're
17-- talking of session management, job ticket processing, storage, (xml) file processing
18-- and dealing with data from databases (often ambitiously called database publishing).
19--
20-- There is no generic solution for such services, but from our perspective, as we use
21-- context in a regular tds tree (the standard distribution) it makes sense to put shared
22-- code in the context distribution. That way we don't need to reinvent wheels every time.
23
24-- We use the template mechanism from util-tpl which inturn is just using the dos cq
25-- windows convention of %whatever% variables that I've used for ages.
26
27-- util-sql-imp-client.lua
28-- util-sql-imp-library.lua
29-- util-sql-imp-lmxsql.lua
30
31-- local sql = require("util-sql")
32--
33-- local converter = sql.makeconverter {
34--     { name = "id",  type = "number" },
35--     { name = "data",type = "string" },
36-- }
37--
38-- local execute = sql.methods.library.execute
39-- -- local execute = sql.methods.client.execute
40-- -- local execute = sql.methods.lmxsql.execute
41--
42-- result = execute {
43--     presets = {
44--         host      = "localhost",
45--         username  = "root",
46--         password  = "test",
47--         database  = "test",
48--         id        = "test", -- forces persistent session
49--     },
50--     template  = "select * from `test` where `id` > %criterium% ;",
51--     variables = {
52--         criterium = 2,
53--     },
54--     converter = converter
55-- }
56--
57-- inspect(result)
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-- trace_sql     = true
73-- trace_queries = true
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   -- bonus
94helpers.deserialize   = deserialize -- bonus
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", -- 127.0.0.1 is sometimes more reliable
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-- converters
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            -- hashed instead of indexed
179            key = value
180        elseif kind == "entry" then
181            -- so we can (efficiently) extend the hashed table
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-- helper for libraries:
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----  quoted     = patterns.quoted
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-- I will add a bit more checking.
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-- -- -- we delay setting this -- -- --
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-- helper:
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) -- variables template
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) -- variables template
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-- local data = utilities.sql.prepare {
419--     templatefile = "test.sql",
420--     variables    = { },
421--     host         = "...",
422--     username     = "...",
423--     password     = "...",
424--     database     = "...",
425-- }
426
427-- local presets = {
428--     host     = "...",
429--     username = "...",
430--     password = "...",
431--     database = "...",
432-- }
433--
434-- local data = utilities.sql.prepare {
435--     templatefile = "test.sql",
436--     variables    = { },
437--     presets      = presets,
438-- }
439
440-- local data = utilities.sql.prepare {
441--     templatefile = "test.sql",
442--     variables    = { },
443--     presets      = dofile(...),
444-- }
445
446-- local data = utilities.sql.prepare {
447--     templatefile = "test.sql",
448--     variables    = { },
449--     presets      = "...",
450-- }
451
452-- for i=1,10 do
453--     local dummy = uuid() -- else same every time, don't ask
454-- end
455
456sql.tokens = {
457    length = 42, -- but in practice we will reserve some 50 characters
458    new    = function()
459        return format("%s-%x06",osuuid(),random(0xFFFFF)) -- 36 + 1 + 6 = 42
460    end,
461}
462
463-- -- --
464
465-- local func, code = sql.makeconverter {
466--     { name = "a", type = "number" },
467--     { name = "b", type = "string" },
468--     { name = "c", type = "boolean" },
469--     { name = "d", type = { x = "1" } },
470--     { name = "e", type = os.fulltime },
471-- }
472--
473-- print(code)
474
475-- -- --
476
477if tex and tex.systemmodes then
478
479    local droptable = table.drop
480    local threshold = 16 * 1024 -- use slower but less memory hungry variant
481
482    function sql.prepare(specification,tag)
483        -- could go into tuc if needed
484        -- todo: serialize per column
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