diff options
Diffstat (limited to 'tex/context/base/util-sql.lua')
| -rw-r--r-- | tex/context/base/util-sql.lua | 425 |
1 files changed, 0 insertions, 425 deletions
diff --git a/tex/context/base/util-sql.lua b/tex/context/base/util-sql.lua deleted file mode 100644 index e2edf0ffe..000000000 --- a/tex/context/base/util-sql.lua +++ /dev/null @@ -1,425 +0,0 @@ -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-%x05",osuuid(),random(0xFFFFF)) -- 36 + 1 + 5 = 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 |