diff options
Diffstat (limited to 'tex/context/base/util-sql.lua')
| -rw-r--r-- | tex/context/base/util-sql.lua | 425 |
1 files changed, 425 insertions, 0 deletions
diff --git a/tex/context/base/util-sql.lua b/tex/context/base/util-sql.lua new file mode 100644 index 000000000..70c7d4be6 --- /dev/null +++ b/tex/context/base/util-sql.lua @@ -0,0 +1,425 @@ +if not modules then modules = { } end modules ['util-sql'] = { + version = 1.001, + comment = "companion to m-sql.mkiv", + author = "Hans Hagen, PRAGMA-ADE, Hasselt NL", + copyright = "PRAGMA ADE / ConTeXt Development Team", + license = "see context related readme files" +} + +-- Of course we could use a library but we don't want another depedency and there is +-- a bit of flux in these libraries. Also, we want the data back in a way that we +-- like. +-- +-- This is the first of set of sql related modules that are providing functionality +-- for a web based framework that we use for typesetting (related) services. We're +-- talking of session management, job ticket processing, storage, (xml) file processing +-- and dealing with data from databases (often ambitiously called database publishing). +-- +-- There is no generic solution for such services, but from our perspective, as we use +-- context in a regular tds tree (the standard distribution) it makes sense to put shared +-- code in the context distribution. That way we don't need to reinvent wheels every time. + +-- We use the template mechanism from util-tpl which inturn is just using the dos cq +-- windows convention of %whatever% variables that I've used for ages. + +-- util-sql-imp-client.lua +-- util-sql-imp-library.lua +-- util-sql-imp-swiglib.lua +-- util-sql-imp-lmxsql.lua + +-- local sql = require("util-sql") +-- +-- local converter = sql.makeconverter { +-- { name = "id", type = "number" }, +-- { name = "data",type = "string" }, +-- } +-- +-- local execute = sql.methods.swiglib.execute +-- -- local execute = sql.methods.library.execute +-- -- local execute = sql.methods.client.execute +-- -- local execute = sql.methods.lmxsql.execute +-- +-- result = execute { +-- presets = { +-- host = "localhost", +-- username = "root", +-- password = "test", +-- database = "test", +-- id = "test", -- forces persistent session +-- }, +-- template = "select * from `test` where `id` > %criterium% ;", +-- variables = { +-- criterium = 2, +-- }, +-- converter = converter +-- } +-- +-- inspect(result) + +local format, match = string.format, string.match +local random = math.random +local rawset, setmetatable, getmetatable, loadstring, type = rawset, setmetatable, getmetatable, loadstring, type +local 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 +local concat = table.concat + +local osuuid = os.uuid +local osclock = os.clock or os.time +local ostime = os.time + +local trace_sql = false trackers.register("sql.trace", function(v) trace_sql = v end) +local trace_queries = false trackers.register("sql.queries",function(v) trace_queries = v end) +local report_state = logs.reporter("sql") + +-- trace_sql = true +-- trace_queries = true + +utilities.sql = utilities.sql or { } +local sql = utilities.sql + +local replacetemplate = utilities.templates.replace +local loadtemplate = utilities.templates.load + +local methods = { } +sql.methods = methods + +local helpers = { } +sql.helpers = helpers + +local serialize = table.fastserialize +local deserialize = table.deserialize + +sql.serialize = serialize +sql.deserialize = deserialize + +helpers.serialize = serialize -- bonus +helpers.deserialize = deserialize -- bonus + +local defaults = { __index = + { + resultfile = "result.dat", + templatefile = "template.sql", + queryfile = "query.sql", + variables = { }, + username = "default", + password = "default", + host = "localhost", + port = 3306, + database = "default", + }, +} + +table.setmetatableindex(sql.methods,function(t,k) + require("util-sql-imp-"..k) + return rawget(t,k) +end) + +-- converters + +local converters = { } +sql.converters = converters + +local function makeconverter(entries,celltemplate,wraptemplate) + local shortcuts = { } + local assignments = { } + local function cell(i) + return format(celltemplate,i,i) + end + for i=1,#entries do + local entry = entries[i] + local nam = entry.name + local typ = entry.type + if typ == "boolean" then + assignments[i] = format("[%q] = booleanstring(%s),",nam,cell(i)) + elseif typ == "number" then + assignments[i] = format("[%q] = tonumber(%s),",nam,cell(i)) + elseif type(typ) == "function" then + local c = #converters + 1 + converters[c] = typ + shortcuts[#shortcuts+1] = format("local fun_%s = converters[%s]",c,c) + assignments[i] = format("[%q] = fun_%s(%s),",nam,c,cell(i)) + elseif type(typ) == "table" then + local c = #converters + 1 + converters[c] = typ + shortcuts[#shortcuts+1] = format("local tab_%s = converters[%s]",c,c) + assignments[i] = format("[%q] = tab_%s[%s],",nam,#converters,cell(i)) + elseif typ == "deserialize" then + assignments[i] = format("[%q] = deserialize(%s),",nam,cell(i)) + else + assignments[i] = format("[%q] = %s,",nam,cell(i)) + end + end + local code = format(wraptemplate,concat(shortcuts,"\n"),concat(assignments,"\n ")) + local func = loadstring(code) + return func and func() +end + +function sql.makeconverter(entries) + local fields = { } + for i=1,#entries do + fields[i] = format("`%s`",entries[i].name) + end + fields = concat(fields, ", ") + local converter = { + fields = fields + } + table.setmetatableindex(converter, function(t,k) + local sqlmethod = methods[k] + local v = makeconverter(entries,sqlmethod.celltemplate,sqlmethod.wraptemplate) + t[k] = v + return v + end) + return converter, fields +end + +-- helper for libraries: + +local function validspecification(specification) + local presets = specification.presets + if type(presets) == "string" then + presets = dofile(presets) + end + if type(presets) == "table" then + setmetatable(presets,defaults) + setmetatable(specification,{ __index = presets }) + else + setmetatable(specification,defaults) + end + return true +end + +helpers.validspecification = validspecification + +local whitespace = patterns.whitespace^0 +local eol = patterns.eol +local separator = P(";") +local escaped = patterns.escaped +local dquote = patterns.dquote +local squote = patterns.squote +local dsquote = squote * squote +---- quoted = patterns.quoted +local quoted = dquote * (escaped + (1-dquote))^0 * dquote + + squote * (escaped + dsquote + (1-squote))^0 * squote +local comment = P("--") * (1-eol) / "" +local query = whitespace + * Cs((quoted + comment + 1 - separator)^1 * Cc(";")) + * whitespace +local splitter = Ct(query * (separator * query)^0) + +helpers.querysplitter = splitter + +-- I will add a bit more checking. + +local function validspecification(specification) + local presets = specification.presets + if type(presets) == "string" then + presets = dofile(presets) + end + if type(presets) == "table" then + local m = getmetatable(presets) + if m then + setmetatable(m,defaults) + else + setmetatable(presets,defaults) + end + setmetatable(specification,{ __index = presets }) + else + setmetatable(specification,defaults) + end + local templatefile = specification.templatefile or "query" + local queryfile = specification.queryfile or presets.queryfile or file.nameonly(templatefile) .. "-temp.sql" + local resultfile = specification.resultfile or presets.resultfile or file.nameonly(templatefile) .. "-temp.dat" + specification.queryfile = queryfile + specification.resultfile = resultfile + if trace_sql then + report_state("template file: %q",templatefile or "<none>") + report_state("query file: %q",queryfile) + report_state("result file: %q",resultfile) + end + return true +end + +local function preparetemplate(specification) + local template = specification.template + if template then + local query = replacetemplate(template,specification.variables,'sql') + if not query then + report_state("error in template: %s",template) + elseif trace_queries then + report_state("query from template: %s",query) + end + return query + end + local templatefile = specification.templatefile + if templatefile then + local query = loadtemplate(templatefile,specification.variables,'sql') + if not query then + report_state("error in template file %q",templatefile) + elseif trace_queries then + report_state("query from template file %q: %s",templatefile,query) + end + return query + end + report_state("no query template or templatefile") +end + +helpers.preparetemplate = preparetemplate + +-- -- -- we delay setting this -- -- -- + +local currentmethod + +local function firstexecute(...) + local execute = methods[currentmethod].execute + sql.execute = execute + return execute(...) +end + +function sql.setmethod(method) + currentmethod = method + sql.execute = firstexecute +end + +sql.setmethod("client") + +-- helper: + +function sql.usedatabase(presets,datatable) + local name = datatable or presets.datatable + if name then + local method = presets.method and sql.methods[presets.method] or sql.methods.client + local base = presets.database or "test" + local basename = format("`%s`.`%s`",base,name) + local execute = nil + local m_execute = method.execute + if method.usesfiles then + local queryfile = presets.queryfile or format("%s-temp.sql",name) + local resultfile = presets.resultfile or format("%s-temp.dat",name) + execute = function(specification) -- variables template + if not specification.presets then specification.presets = presets end + if not specification.queryfile then specification.queryfile = queryfile end + if not specification.resultfile then specification.resultfile = queryfile end + return m_execute(specification) + end + else + execute = function(specification) -- variables template + if not specification.presets then specification.presets = presets end + return m_execute(specification) + end + end + local function unpackdata(records,name) + if records then + name = name or "data" + for i=1,#records do + local record = records[i] + local data = record[name] + if data then + record[name] = deserialize(data) + end + end + end + end + return { + presets = preset, + base = base, + name = name, + basename = basename, + execute = execute, + serialize = serialize, + deserialize = deserialize, + unpackdata = unpackdata, + } + end +end + + +-- local data = utilities.sql.prepare { +-- templatefile = "test.sql", +-- variables = { }, +-- host = "...", +-- username = "...", +-- password = "...", +-- database = "...", +-- } + +-- local presets = { +-- host = "...", +-- username = "...", +-- password = "...", +-- database = "...", +-- } +-- +-- local data = utilities.sql.prepare { +-- templatefile = "test.sql", +-- variables = { }, +-- presets = presets, +-- } + +-- local data = utilities.sql.prepare { +-- templatefile = "test.sql", +-- variables = { }, +-- presets = dofile(...), +-- } + +-- local data = utilities.sql.prepare { +-- templatefile = "test.sql", +-- variables = { }, +-- presets = "...", +-- } + +-- for i=1,10 do +-- local dummy = uuid() -- else same every time, don't ask +-- end + +sql.tokens = { + length = 42, -- but in practice we will reserve some 50 characters + new = function() + return format("%s-%x06",osuuid(),random(0xFFFFF)) -- 36 + 1 + 6 = 42 + end, +} + +-- -- -- + +-- local func, code = sql.makeconverter { +-- { name = "a", type = "number" }, +-- { name = "b", type = "string" }, +-- { name = "c", type = "boolean" }, +-- { name = "d", type = { x = "1" } }, +-- { name = "e", type = os.fulltime }, +-- } +-- +-- print(code) + +-- -- -- + +if tex and tex.systemmodes then + + local droptable = table.drop + local threshold = 16 * 1024 -- use slower but less memory hungry variant + + function sql.prepare(specification,tag) + -- could go into tuc if needed + -- todo: serialize per column + local filename = format("%s-sql-result-%s.tuc",tex.jobname,tag or "last") + if tex.systemmodes["first"] then + local data, keys = sql.execute(specification) + if not data then + data = { } + end + if not keys then + keys = { } + end + io.savedata(filename,droptable({ data = data, keys = keys },#keys*#data>threshold)) + return data, keys + else + local result = table.load(filename) + return result.data, result.keys + end + end + +else + + sql.prepare = sql.execute + +end + +return sql |