SourceBrowser

util-sql-imp-library.lua /size: 8576 b last modification: 2023-12-21 09:44
1if not modules then modules = { } end modules ['util-sql-imp-library'] = {
2    version   = 1.001,
3    comment   = "companion to util-sql.lua",
4    author    = "Hans Hagen, PRAGMA-ADE, Hasselt NL",
5    copyright = "PRAGMA ADE / ConTeXt Development Team",
6    license   = "see context related readme files"
7}
8
9-- local function pcall(f,...) return true, f(...) end
10
11-- For some reason the sql lib partially fails in luatex when creating hashed row. So far
12-- we couldn't figure it out (some issue with adapting the table that is passes as first
13-- argument in the fetch routine. Apart from this it looks like the mysql binding has some
14-- efficiency issues (like creating a keys and types table for each row) but that could be
15-- optimized. Anyhow, fetching results can be done as follows:
16
17-- local function collect_1(r)
18--     local t = { }
19--     for i=1,r:numrows() do
20--         t[#t+1] = r:fetch({},"a")
21--     end
22--     return t
23-- end
24--
25-- local function collect_2(r)
26--     local keys   = r:getcolnames()
27--     local n      = #keys
28--     local t      = { }
29--     for i=1,r:numrows() do
30--         local v = { r:fetch() }
31--         local r = { }
32--         for i=1,n do
33--             r[keys[i]] = v[i]
34--         end
35--         t[#t+1] = r
36--     end
37--     return t
38-- end
39--
40-- local function collect_3(r)
41--     local keys   = r:getcolnames()
42--     local n      = #keys
43--     local t      = { }
44--     for i=1,r:numrows() do
45--         local v = r:fetch({},"n")
46--         local r = { }
47--         for i=1,n do
48--             r[keys[i]] = v[i]
49--         end
50--         t[#t+1] = r
51--     end
52--     return t
53-- end
54--
55-- On a large table with some 8 columns (mixed text and numbers) we get the following
56-- timings (the 'a' alternative is already using the more efficient variant in the
57-- binding).
58--
59-- collect_1 : 1.31
60-- collect_2 : 1.39
61-- collect_3 : 1.75
62--
63-- Some, as a workaround for this 'bug' the second alternative can be used.
64
65local format = string.format
66local lpegmatch = lpeg.match
67local setmetatable, type = setmetatable, type
68
69local trace_sql          = false  trackers.register("sql.trace",  function(v) trace_sql     = v end)
70local trace_queries      = false  trackers.register("sql.queries",function(v) trace_queries = v end)
71local report_state       = logs.reporter("sql","library")
72
73local sql                = utilities.sql
74local mysql              = require("luasql.mysql")
75local cache              = { }
76local helpers            = sql.helpers
77local methods            = sql.methods
78local validspecification = helpers.validspecification
79local querysplitter      = helpers.querysplitter
80local dataprepared       = helpers.preparetemplate
81local serialize          = sql.serialize
82local formatters         = string.formatters
83
84local initialize         = mysql.mysql
85
86local function connect(session,specification)
87    return session:connect(
88        specification.database or "",
89        specification.username or "",
90        specification.password or "",
91        specification.host     or "",
92        specification.port
93    )
94end
95
96local function fetched(specification,query,converter)
97    if not query or query == "" then
98        report_state("no valid query")
99        return false
100    end
101    local id = specification.id
102    local session, connection
103    if id then
104        local c = cache[id]
105        if c then
106            session    = c.session
107            connection = c.connection
108        end
109        if not connection then
110            session = initialize()
111            if not session then
112                return formatters["no session for %a"](id)
113            end
114            connection = connect(session,specification)
115            if not connection then
116                return formatters["no connection for %a"](id)
117            end
118            cache[id] = { session = session, connection = connection }
119        end
120    else
121        session = initialize()
122        if not session then
123            return "no session"
124        end
125        connection = connect(session,specification)
126        if not connection then
127            return "no connection"
128        end
129    end
130    if not connection then
131        report_state("error in connection: %s@%s to %s:%s",
132            specification.database or "no database",
133            specification.username or "no username",
134            specification.host     or "no host",
135            specification.port     or "no port"
136        )
137        return "no connection"
138    end
139    query = lpegmatch(querysplitter,query)
140    local result, okay
141    for i=1,#query do
142        local q = query[i]
143        local r, m = connection:execute(q)
144        if m then
145            report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q or "?"))
146            if m then
147                report_state("message: %s",m)
148            end
149        end
150        local t = type(r)
151        if t == "userdata" then
152            result = r
153            okay = true
154        elseif t == "number" then
155            okay = true
156        end
157    end
158    if not okay then
159        if connection then
160            connection:close()
161        end
162        if session then
163            session:close()
164        end
165        if id then
166            cache[id] = nil
167        end
168        return "execution error"
169    end
170    local data, keys
171    if result then
172        if converter then
173            data = converter.library(result)
174        else
175            keys = result:getcolnames()
176            if keys then
177                data = { }
178                local n = result:numrows() or 0
179                if n > 0 then
180                    local k = #keys
181                    for i=1,n do
182                        local v = { result:fetch() }
183                        local d = { }
184                        for i=1,k do
185                            d[keys[i]] = v[i]
186                        end
187                        data[#data+1] = d
188                    end
189                end
190            end
191        end
192        result:close()
193    end
194    if not id then
195        if connection then
196            connection:close()
197        end
198        if session then
199            session:close()
200        end
201    end
202    return false, data, keys
203end
204
205local function datafetched(specification,query,converter)
206    local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
207    if not callokay then
208        report_state("call error, retrying")
209        callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
210    elseif connectionerror then
211        report_state("error: %s, retrying",connectionerror)
212        callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
213    end
214    if not callokay then
215        report_state("persistent call error")
216    elseif connectionerror then
217        report_state("persistent error: %s",connectionerror)
218    end
219    return data or { }, keys or { }
220end
221
222local function execute(specification)
223    if trace_sql then
224        report_state("executing library")
225    end
226    if not validspecification(specification) then
227        report_state("error in specification")
228        return
229    end
230    local query = dataprepared(specification)
231    if not query then
232        report_state("error in preparation")
233        return
234    end
235    local data, keys = datafetched(specification,query,specification.converter)
236    if not data then
237        report_state("error in fetching")
238        return
239    end
240    local one = data[1]
241    if one then
242        setmetatable(data,{ __index = one } )
243    end
244    return data, keys
245end
246
247-- Here we build the dataset stepwise so we don't use the data hack that
248-- is used in the client variant.
249
250local wraptemplate = [[
251local converters    = utilities.sql.converters
252local deserialize   = utilities.sql.deserialize
253local fromjson      = utilities.sql.fromjson
254
255local tostring      = tostring
256local tonumber      = tonumber
257local booleanstring = string.booleanstring
258
259%s
260
261return function(result)
262    if not result then
263        return { }
264    end
265    local nofrows = result:numrows() or 0
266    if nofrows == 0 then
267        return { }
268    end
269    local target = { } -- no %s needed here
270    for i=1,nofrows do
271        local cells = { result:fetch() }
272        target[%s] = {
273            %s
274        }
275    end
276    return target
277end
278]]
279
280local celltemplate = "cells[%s]"
281
282methods.library = {
283    runner       = function() end, -- never called
284    execute      = execute,
285    initialize   = initialize,     -- returns session
286    usesfiles    = false,
287    wraptemplate = wraptemplate,
288    celltemplate = celltemplate,
289}
290
Source Browser ?
