From bd95a21d2b31a5fab1b4cc7c2b0334823fb3a3e9 Mon Sep 17 00:00:00 2001 From: Hans Hagen Date: Mon, 20 May 2013 02:00:00 +0200 Subject: beta 2013.05.20 02:00 --- tex/context/base/util-sql-imp-client.lua | 512 +++++++++++++++---------------- 1 file changed, 256 insertions(+), 256 deletions(-) (limited to 'tex/context/base/util-sql-imp-client.lua') diff --git a/tex/context/base/util-sql-imp-client.lua b/tex/context/base/util-sql-imp-client.lua index 7c713a899..e09dfde94 100644 --- a/tex/context/base/util-sql-imp-client.lua +++ b/tex/context/base/util-sql-imp-client.lua @@ -1,256 +1,256 @@ -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 - --- 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 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 - p = skipfirst * Ct(p^0) - 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 function dataprepared(specification) - local query = preparetemplate(specification) - if query then - 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,runner) - 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) then - report_state("error in preparation") - return - end - if not datafetched(specification,methods.client.runner) 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 = { - runner = [[mysql --batch --user="%username%" --password="%password%" --host="%host%" --port=%port% --database="%database%" --default-character-set=utf8 < "%queryfile%" > "%resultfile%"]], - execute = execute, - usesfiles = true, - wraptemplate = wraptemplate, - celltemplate = celltemplate, -} +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 + +-- 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 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 + p = skipfirst * Ct(p^0) + 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 function dataprepared(specification) + local query = preparetemplate(specification) + if query then + 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,runner) + 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) then + report_state("error in preparation") + return + end + if not datafetched(specification,methods.client.runner) 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 = { + runner = [[mysql --batch --user="%username%" --password="%password%" --host="%host%" --port=%port% --database="%database%" --default-character-set=utf8 < "%queryfile%" > "%resultfile%"]], + execute = execute, + usesfiles = true, + wraptemplate = wraptemplate, + celltemplate = celltemplate, +} -- cgit v1.2.3