diff options
Diffstat (limited to 'tex/context/base/util-sql-imp-library.lua')
| -rw-r--r-- | tex/context/base/util-sql-imp-library.lua | 578 |
1 files changed, 289 insertions, 289 deletions
diff --git a/tex/context/base/util-sql-imp-library.lua b/tex/context/base/util-sql-imp-library.lua index 15754e26a..8a83b06d2 100644 --- a/tex/context/base/util-sql-imp-library.lua +++ b/tex/context/base/util-sql-imp-library.lua @@ -1,289 +1,289 @@ -if not modules then modules = { } end modules ['util-sql-library'] = { - 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" -} - --- local function pcall(f,...) return true, f(...) end - --- For some reason the sql lib partially fails in luatex when creating hashed row. So far --- we couldn't figure it out (some issue with adapting the table that is passes as first --- argument in the fetch routine. Apart from this it looks like the mysql binding has some --- efficiency issues (like creating a keys and types table for each row) but that could be --- optimized. Anyhow, fecthing results can be done as follows: - --- local function collect_1(r) --- local t = { } --- for i=1,r:numrows() do --- t[#t+1] = r:fetch({},"a") --- end --- return t --- end --- --- local function collect_2(r) --- local keys = r:getcolnames() --- local n = #keys --- local t = { } --- for i=1,r:numrows() do --- local v = { r:fetch() } --- local r = { } --- for i=1,n do --- r[keys[i]] = v[i] --- end --- t[#t+1] = r --- end --- return t --- end --- --- local function collect_3(r) --- local keys = r:getcolnames() --- local n = #keys --- local t = { } --- for i=1,r:numrows() do --- local v = r:fetch({},"n") --- local r = { } --- for i=1,n do --- r[keys[i]] = v[i] --- end --- t[#t+1] = r --- end --- return t --- end --- --- On a large table with some 8 columns (mixed text and numbers) we get the following --- timings (the 'a' alternative is already using the more efficient variant in the --- binding). --- --- collect_1 : 1.31 --- collect_2 : 1.39 --- collect_3 : 1.75 --- --- Some, as a workaround for this 'bug' the second alternative can be used. - -local format = string.format -local lpegmatch = lpeg.match -local setmetatable, type = setmetatable, type - -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","library") - -local sql = utilities.sql -local mysql = require("luasql.mysql") -local cache = { } -local helpers = sql.helpers -local methods = sql.methods -local validspecification = helpers.validspecification -local querysplitter = helpers.querysplitter -local dataprepared = helpers.preparetemplate -local serialize = sql.serialize -local deserialize = sql.deserialize -local formatters = string.formatters - -local initialize = mysql.mysql - -local function connect(session,specification) - return session:connect( - specification.database or "", - specification.username or "", - specification.password or "", - specification.host or "", - specification.port - ) -end - -local function fetched(specification,query,converter) - if not query or query == "" then - report_state("no valid query") - return false - end - local id = specification.id - local session, connection - if id then - local c = cache[id] - if c then - session = c.session - connection = c.connection - end - if not connection then - session = initialize() - if not session then - return formatters["no session for %a"](id) - end - connection = connect(session,specification) - if not connection then - return formatters["no connection for %a"](id) - end - cache[id] = { session = session, connection = connection } - end - else - session = initialize() - if not session then - return "no session" - end - connection = connect(session,specification) - if not connection then - return "no connection" - end - end - if not connection then - report_state("error in connection: %s@%s to %s:%s", - specification.database or "no database", - specification.username or "no username", - specification.host or "no host", - specification.port or "no port" - ) - return "no connection" - end - query = lpegmatch(querysplitter,query) - local result, okay - for i=1,#query do - local q = query[i] - local r, m = connection:execute(q) - if m then - report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q)) - if m then - report_state("message: %s",m) - end - end - local t = type(r) - if t == "userdata" then - result = r - okay = true - elseif t == "number" then - okay = true - end - end - if not okay then -- can go - if session then - session:close() - end - if connection then - connection:close() - end - if id then - cache[id] = nil - end - return "execution error" - end - local data, keys - if result then - if converter then - data = converter.library(result) - else - keys = result:getcolnames() - if keys then - data = { } - local n = result:numrows() or 0 - if n > 0 then - local k = #keys - for i=1,n do - local v = { result:fetch() } - local d = { } - for i=1,k do - d[keys[i]] = v[i] - end - data[#data+1] = d - end - end - end - end - result:close() - end - if not id then - if connection then - connection:close() - end - if session then - session:close() - end - end - return false, data, keys -end - -local function datafetched(specification,query,converter) - local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter) - if not callokay then - report_state("call error, retrying") - callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter) - elseif connectionerror then - report_state("error: %s, retrying",connectionerror) - callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter) - end - if not callokay then - report_state("persistent call error") - elseif connectionerror then - report_state("persistent error: %s",connectionerror) - end - return data or { }, keys or { } -end - -local function execute(specification) - if trace_sql then - report_state("executing library") - end - if not validspecification(specification) then - report_state("error in specification") - return - end - local query = dataprepared(specification) - if not query then - report_state("error in preparation") - return - end - local data, keys = datafetched(specification,query,specification.converter) - if not data then - report_state("error in fetching") - return - end - local one = data[1] - if one then - setmetatable(data,{ __index = one } ) - end - return data, keys -end - --- Here we build the dataset stepwise so we don't use the data hack that --- is used in the client variant. - -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(result) - if not result then - return { } - end - local nofrows = result:numrows() or 0 - if nofrows == 0 then - return { } - end - local target = { } -- no %s needed here - for i=1,nofrows do - local cells = { result:fetch() } - target[%s] = { - %s - } - end - return target -end -]] - -local celltemplate = "cells[%s]" - -methods.library = { - runner = function() end, -- never called - execute = execute, - initialize = initialize, -- returns session - usesfiles = false, - wraptemplate = wraptemplate, - celltemplate = celltemplate, -} +if not modules then modules = { } end modules ['util-sql-library'] = {
+ 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"
+}
+
+-- local function pcall(f,...) return true, f(...) end
+
+-- For some reason the sql lib partially fails in luatex when creating hashed row. So far
+-- we couldn't figure it out (some issue with adapting the table that is passes as first
+-- argument in the fetch routine. Apart from this it looks like the mysql binding has some
+-- efficiency issues (like creating a keys and types table for each row) but that could be
+-- optimized. Anyhow, fecthing results can be done as follows:
+
+-- local function collect_1(r)
+-- local t = { }
+-- for i=1,r:numrows() do
+-- t[#t+1] = r:fetch({},"a")
+-- end
+-- return t
+-- end
+--
+-- local function collect_2(r)
+-- local keys = r:getcolnames()
+-- local n = #keys
+-- local t = { }
+-- for i=1,r:numrows() do
+-- local v = { r:fetch() }
+-- local r = { }
+-- for i=1,n do
+-- r[keys[i]] = v[i]
+-- end
+-- t[#t+1] = r
+-- end
+-- return t
+-- end
+--
+-- local function collect_3(r)
+-- local keys = r:getcolnames()
+-- local n = #keys
+-- local t = { }
+-- for i=1,r:numrows() do
+-- local v = r:fetch({},"n")
+-- local r = { }
+-- for i=1,n do
+-- r[keys[i]] = v[i]
+-- end
+-- t[#t+1] = r
+-- end
+-- return t
+-- end
+--
+-- On a large table with some 8 columns (mixed text and numbers) we get the following
+-- timings (the 'a' alternative is already using the more efficient variant in the
+-- binding).
+--
+-- collect_1 : 1.31
+-- collect_2 : 1.39
+-- collect_3 : 1.75
+--
+-- Some, as a workaround for this 'bug' the second alternative can be used.
+
+local format = string.format
+local lpegmatch = lpeg.match
+local setmetatable, type = setmetatable, type
+
+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","library")
+
+local sql = utilities.sql
+local mysql = require("luasql.mysql")
+local cache = { }
+local helpers = sql.helpers
+local methods = sql.methods
+local validspecification = helpers.validspecification
+local querysplitter = helpers.querysplitter
+local dataprepared = helpers.preparetemplate
+local serialize = sql.serialize
+local deserialize = sql.deserialize
+local formatters = string.formatters
+
+local initialize = mysql.mysql
+
+local function connect(session,specification)
+ return session:connect(
+ specification.database or "",
+ specification.username or "",
+ specification.password or "",
+ specification.host or "",
+ specification.port
+ )
+end
+
+local function fetched(specification,query,converter)
+ if not query or query == "" then
+ report_state("no valid query")
+ return false
+ end
+ local id = specification.id
+ local session, connection
+ if id then
+ local c = cache[id]
+ if c then
+ session = c.session
+ connection = c.connection
+ end
+ if not connection then
+ session = initialize()
+ if not session then
+ return formatters["no session for %a"](id)
+ end
+ connection = connect(session,specification)
+ if not connection then
+ return formatters["no connection for %a"](id)
+ end
+ cache[id] = { session = session, connection = connection }
+ end
+ else
+ session = initialize()
+ if not session then
+ return "no session"
+ end
+ connection = connect(session,specification)
+ if not connection then
+ return "no connection"
+ end
+ end
+ if not connection then
+ report_state("error in connection: %s@%s to %s:%s",
+ specification.database or "no database",
+ specification.username or "no username",
+ specification.host or "no host",
+ specification.port or "no port"
+ )
+ return "no connection"
+ end
+ query = lpegmatch(querysplitter,query)
+ local result, okay
+ for i=1,#query do
+ local q = query[i]
+ local r, m = connection:execute(q)
+ if m then
+ report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q))
+ if m then
+ report_state("message: %s",m)
+ end
+ end
+ local t = type(r)
+ if t == "userdata" then
+ result = r
+ okay = true
+ elseif t == "number" then
+ okay = true
+ end
+ end
+ if not okay then -- can go
+ if session then
+ session:close()
+ end
+ if connection then
+ connection:close()
+ end
+ if id then
+ cache[id] = nil
+ end
+ return "execution error"
+ end
+ local data, keys
+ if result then
+ if converter then
+ data = converter.library(result)
+ else
+ keys = result:getcolnames()
+ if keys then
+ data = { }
+ local n = result:numrows() or 0
+ if n > 0 then
+ local k = #keys
+ for i=1,n do
+ local v = { result:fetch() }
+ local d = { }
+ for i=1,k do
+ d[keys[i]] = v[i]
+ end
+ data[#data+1] = d
+ end
+ end
+ end
+ end
+ result:close()
+ end
+ if not id then
+ if connection then
+ connection:close()
+ end
+ if session then
+ session:close()
+ end
+ end
+ return false, data, keys
+end
+
+local function datafetched(specification,query,converter)
+ local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
+ if not callokay then
+ report_state("call error, retrying")
+ callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
+ elseif connectionerror then
+ report_state("error: %s, retrying",connectionerror)
+ callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
+ end
+ if not callokay then
+ report_state("persistent call error")
+ elseif connectionerror then
+ report_state("persistent error: %s",connectionerror)
+ end
+ return data or { }, keys or { }
+end
+
+local function execute(specification)
+ if trace_sql then
+ report_state("executing library")
+ end
+ if not validspecification(specification) then
+ report_state("error in specification")
+ return
+ end
+ local query = dataprepared(specification)
+ if not query then
+ report_state("error in preparation")
+ return
+ end
+ local data, keys = datafetched(specification,query,specification.converter)
+ if not data then
+ report_state("error in fetching")
+ return
+ end
+ local one = data[1]
+ if one then
+ setmetatable(data,{ __index = one } )
+ end
+ return data, keys
+end
+
+-- Here we build the dataset stepwise so we don't use the data hack that
+-- is used in the client variant.
+
+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(result)
+ if not result then
+ return { }
+ end
+ local nofrows = result:numrows() or 0
+ if nofrows == 0 then
+ return { }
+ end
+ local target = { } -- no %s needed here
+ for i=1,nofrows do
+ local cells = { result:fetch() }
+ target[%s] = {
+ %s
+ }
+ end
+ return target
+end
+]]
+
+local celltemplate = "cells[%s]"
+
+methods.library = {
+ runner = function() end, -- never called
+ execute = execute,
+ initialize = initialize, -- returns session
+ usesfiles = false,
+ wraptemplate = wraptemplate,
+ celltemplate = celltemplate,
+}
|