diff options
Diffstat (limited to 'tex/context/base/mkiv/util-sql-imp-client.lua')
| -rw-r--r-- | tex/context/base/mkiv/util-sql-imp-client.lua | 284 |
1 files changed, 284 insertions, 0 deletions
diff --git a/tex/context/base/mkiv/util-sql-imp-client.lua b/tex/context/base/mkiv/util-sql-imp-client.lua new file mode 100644 index 000000000..9a0fbc299 --- /dev/null +++ b/tex/context/base/mkiv/util-sql-imp-client.lua @@ -0,0 +1,284 @@ +if not modules then modules = { } end modules ['util-sql-client'] = { + version = 1.001, + comment = "companion to util-sql.lua", + author = "Hans Hagen, PRAGMA-ADE, Hasselt NL", + copyright = "PRAGMA ADE / ConTeXt Development Team", + license = "see context related readme files" +} + +-- todo: make a converter + +local rawset, setmetatable = rawset, setmetatable +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 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","client") + +local sql = utilities.sql +local helpers = sql.helpers +local methods = sql.methods +local validspecification = helpers.validspecification +local preparetemplate = helpers.preparetemplate +local splitdata = helpers.splitdata +local replacetemplate = utilities.templates.replace +local serialize = sql.serialize +local deserialize = sql.deserialize +local getserver = sql.getserver + +-- Experiments with an p/action demonstrated that there is not much gain. We could do a runtime +-- capture but creating all the small tables is not faster and it doesn't work well anyway. + +local separator = P("\t") +local newline = patterns.newline +local empty = Cc("") + +local entry = C((1-separator-newline)^0) -- C 10% faster than Cs + +local unescaped = P("\\n") / "\n" + + P("\\t") / "\t" + + P("\\0") / "\000" + + P("\\\\") / "\\" + +local entry = Cs((unescaped + (1-separator-newline))^0) -- C 10% faster than Cs but Cs needed due to nesting + +local getfirst = Ct( entry * (separator * (entry+empty))^0) + newline +local skipfirst = (1-newline)^1 * newline +local skipdashes = (P("-")+separator)^1 * newline +local getfirstline = C((1-newline)^0) + +local cache = { } + +local function splitdata(data) -- todo: hash on first line ... maybe move to client module + if data == "" then + if trace_sql then + report_state("no data") + end + return { }, { } + end + local first = lpegmatch(getfirstline,data) + if not first then + if trace_sql then + report_state("no data") + end + return { }, { } + end + local p = cache[first] + if p then + -- report_state("reusing: %s",first) + local entries = lpegmatch(p.parser,data) + return entries or { }, p.keys + elseif p == false then + return { }, { } + elseif p == nil then + local keys = lpegmatch(getfirst,first) or { } + if #keys == 0 then + if trace_sql then + report_state("no banner") + end + cache[first] = false + return { }, { } + end + -- quite generic, could be a helper + local n = #keys + if n == 0 then + report_state("no fields") + cache[first] = false + return { }, { } + end + if n == 1 then + local key = keys[1] + if trace_sql then + report_state("one field with name %a",key) + end + p = Cg(Cc(key) * entry) + else + for i=1,n do + local key = keys[i] + if trace_sql then + report_state("field %s has name %a",i,key) + end + local s = Cg(Cc(key) * entry) + if p then + p = p * separator * s + else + p = s + end + end + end + p = Cf(Ct("") * p,rawset) * newline^1 +if getserver() == "mssql" then + p = skipfirst * skipdashes * Ct(p^0) +else + p = skipfirst * Ct(p^0) +end + cache[first] = { parser = p, keys = keys } + local entries = lpegmatch(p,data) + return entries or { }, keys + end +end + +local splitter = skipfirst * Ct((Ct(entry * (separator * entry)^0) * newline^1)^0) + +local function getdata(data) + return lpegmatch(splitter,data) +end + +helpers.splitdata = splitdata +helpers.getdata = getdata + +local t_runner = { + mysql = [[mysql --batch --user="%username%" --password="%password%" --host="%host%" --port=%port% --database="%database%" --default-character-set=utf8 < "%queryfile%" > "%resultfile%"]], + mssql = [[sqlcmd -S %host% %?U: -U "%username%" ?% %?P: -P "%password%" ?% -I -W -w 65535 -s"]] .. "\t" .. [[" -m 1 -i "%queryfile%" -o "%resultfile%"]], +} + +local t_preamble = { + mysql = [[ +SET GLOBAL SQL_MODE=ANSI_QUOTES; + ]], + mssql = [[ +:setvar SQLCMDERRORLEVEL 1 +SET QUOTED_IDENTIFIER ON; +SET NOCOUNT ON; +%?database: USE %database%; ?% + ]], +} + +local function dataprepared(specification,client) + local query = preparetemplate(specification) + if query then + local preamble = t_preamble[getserver()] or t_preamble.mysql + if preamble then + preamble = replacetemplate(preamble,specification.variables,'sql') + query = preamble .. "\n" .. query + end + io.savedata(specification.queryfile,query) + os.remove(specification.resultfile) + if trace_queries then + report_state("query: %s",query) + end + return true + else + -- maybe push an error + os.remove(specification.queryfile) + os.remove(specification.resultfile) + end +end + +local function datafetched(specification,client) + local runner = t_runner[getserver()] or t_runner.mysql + local command = replacetemplate(runner,specification) + if trace_sql then + local t = osclock() + report_state("command: %s",command) + local okay = os.execute(command) + report_state("fetchtime: %.3f sec",osclock()-t) -- not okay under linux + return okay == 0 + else + return os.execute(command) == 0 + end +end + +local function dataloaded(specification) + if trace_sql then + local t = osclock() + local data = io.loaddata(specification.resultfile) or "" + report_state("datasize: %.3f MB",#data/1024/1024) + report_state("loadtime: %.3f sec",osclock()-t) + return data + else + return io.loaddata(specification.resultfile) or "" + end +end + +local function dataconverted(data,converter) + if converter then + local data = getdata(data) + if data then + data = converter.client(data) + end + return data + elseif trace_sql then + local t = osclock() + local data, keys = splitdata(data,target) + report_state("converttime: %.3f",osclock()-t) + report_state("keys: %s ",#keys) + report_state("entries: %s ",#data) + return data, keys + else + return splitdata(data) + end +end + +-- todo: new, etc + +local function execute(specification) + if trace_sql then + report_state("executing client") + end + if not validspecification(specification) then + report_state("error in specification") + return + end + if not dataprepared(specification,methods.client) then + report_state("error in preparation") + return + end + if not datafetched(specification,methods.client) then + report_state("error in fetching, query: %s",string.collapsespaces(io.loaddata(specification.queryfile))) + return + end + local data = dataloaded(specification) + if not data then + report_state("error in loading") + return + end + local data, keys = dataconverted(data,specification.converter) + if not data then + report_state("error in converting or no data") + return + end + local one = data[1] + if one then + setmetatable(data,{ __index = one } ) + end + return data, keys +end + +-- The following is not that (memory) efficient but normally we will use +-- the lib anyway. Of course we could make a dedicated converter and/or +-- hook into the splitter code but ... it makes not much sense because then +-- we can as well move the builder to the library modules. +-- +-- Here we reuse data as the indexes are the same, unless we hash. + +local wraptemplate = [[ +local converters = utilities.sql.converters +local deserialize = utilities.sql.deserialize + +local tostring = tostring +local tonumber = tonumber +local booleanstring = string.booleanstring + +%s + +return function(data) + local target = %s -- data or { } + for i=1,#data do + local cells = data[i] + target[%s] = { + %s + } + end + return target +end +]] + +local celltemplate = "cells[%s]" + +methods.client = { + execute = execute, + usesfiles = true, + wraptemplate = wraptemplate, + celltemplate = celltemplate, +} |