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 8a83b06d2..15754e26a 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, +} |