SourceBrowser

util-sql.lua /size: 16 Kb last modification: 2025-02-21 11:03
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
90local json            = require("json")
91local tojson          = json.tostring
92local fromjson        = json.tolua
93
94sql.serialize         = serialize
95sql.deserialize       = deserialize
96
97helpers.serialize     = serialize   -- bonus
98helpers.deserialize   = deserialize -- bonus
99
100sql.tojson            = tojson
101sql.fromjson          = fromjson
102
103helpers.tojson        = tojson   -- bonus
104helpers.fromjson      = fromjson -- bonus
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", -- 127.0.0.1 is sometimes more reliable
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") -- brrr
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-- converters
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            -- hashed instead of indexed
191            key = value
192        elseif kind == "entry" then
193            -- so we can (efficiently) extend the hashed table
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-- helper for libraries:
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----  quoted     = patterns.quoted
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-- I will add a bit more checking.
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-- -- -- we delay setting this -- -- --
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-- helper:
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) -- variables template
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) -- variables template
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-- local data = utilities.sql.prepare {
446--     templatefile = "test.sql",
447--     variables    = { },
448--     host         = "...",
449--     username     = "...",
450--     password     = "...",
451--     database     = "...",
452-- }
453
454-- local presets = {
455--     host     = "...",
456--     username = "...",
457--     password = "...",
458--     database = "...",
459-- }
460--
461-- local data = utilities.sql.prepare {
462--     templatefile = "test.sql",
463--     variables    = { },
464--     presets      = presets,
465-- }
466
467-- local data = utilities.sql.prepare {
468--     templatefile = "test.sql",
469--     variables    = { },
470--     presets      = dofile(...),
471-- }
472
473-- local data = utilities.sql.prepare {
474--     templatefile = "test.sql",
475--     variables    = { },
476--     presets      = "...",
477-- }
478
479-- for i=1,10 do
480--     local dummy = uuid() -- else same every time, don't ask
481-- end
482
483sql.tokens = {
484    length = 42, -- but in practice we will reserve some 50 characters
485    new    = function()
486        return format("%s-%x06",osuuid(),random(0xFFFFF)) -- 36 + 1 + 6 = 42
487    end,
488}
489
490-- -- --
491
492-- local func, code = sql.makeconverter {
493--     { name = "a", type = "number" },
494--     { name = "b", type = "string" },
495--     { name = "c", type = "boolean" },
496--     { name = "d", type = { x = "1" } },
497--     { name = "e", type = os.fulltime },
498-- }
499--
500-- print(code)
501
502-- -- --
503
504if tex and tex.systemmodes then
505
506    local droptable = table.drop
507    local threshold = 16 * 1024 -- use slower but less memory hungry variant
508
509    function sql.prepare(specification,tag)
510        -- could go into tuc if needed
511        -- todo: serialize per column
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
Source Browser ?
