Source Browser ?

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 deserialize        = sql.deserialize
83local formatters         = string.formatters
84
85local initialize         = mysql.mysql
86
87local function connect(session,specification)
88    return session:connect(
89        specification.database or "",
90        specification.username or "",
91        specification.password or "",
92        specification.host     or "",
93        specification.port
94    )
95end
96
97local function fetched(specification,query,converter)
98    if not query or query == "" then
99        report_state("no valid query")
100        return false
101    end
102    local id = specification.id
103    local session, connection
104    if id then
105        local c = cache[id]
106        if c then
107            session    = c.session
108            connection = c.connection
109        end
110        if not connection then
111            session = initialize()
112            if not session then
113                return formatters["no session for %a"](id)
114            end
115            connection = connect(session,specification)
116            if not connection then
117                return formatters["no connection for %a"](id)
118            end
119            cache[id] = { session = session, connection = connection }
120        end
121    else
122        session = initialize()
123        if not session then
124            return "no session"
125        end
126        connection = connect(session,specification)
127        if not connection then
128            return "no connection"
129        end
130    end
131    if not connection then
132        report_state("error in connection: %s@%s to %s:%s",
133            specification.database or "no database",
134            specification.username or "no username",
135            specification.host     or "no host",
136            specification.port     or "no port"
137        )
138        return "no connection"
139    end
140    query = lpegmatch(querysplitter,query)
141    local result, okay
142    for i=1,#query do
143        local q = query[i]
144        local r, m = connection:execute(q)
145        if m then
146            report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q or "?"))
147            if m then
148                report_state("message: %s",m)
149            end
150        end
151        local t = type(r)
152        if t == "userdata" then
153            result = r
154            okay = true
155        elseif t == "number" then
156            okay = true
157        end
158    end
159    if not okay then
160        if connection then
161            connection:close()
162        end
163        if session then
164            session:close()
165        end
166        if id then
167            cache[id] = nil
168        end
169        return "execution error"
170    end
171    local data, keys
172    if result then
173        if converter then
174            data = converter.library(result)
175        else
176            keys = result:getcolnames()
177            if keys then
178                data = { }
179                local n = result:numrows() or 0
180                if n > 0 then
181                    local k = #keys
182                    for i=1,n do
183                        local v = { result:fetch() }
184                        local d = { }
185                        for i=1,k do
186                            d[keys[i]] = v[i]
187                        end
188                        data[#data+1] = d
189                    end
190                end
191            end
192        end
193        result:close()
194    end
195    if not id then
196        if connection then
197            connection:close()
198        end
199        if session then
200            session:close()
201        end
202    end
203    return false, data, keys
204end
205
206local function datafetched(specification,query,converter)
207    local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
208    if not callokay then
209        report_state("call error, retrying")
210        callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
211    elseif connectionerror then
212        report_state("error: %s, retrying",connectionerror)
213        callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
214    end
215    if not callokay then
216        report_state("persistent call error")
217    elseif connectionerror then
218        report_state("persistent error: %s",connectionerror)
219    end
220    return data or { }, keys or { }
221end
222
223local function execute(specification)
224    if trace_sql then
225        report_state("executing library")
226    end
227    if not validspecification(specification) then
228        report_state("error in specification")
229        return
230    end
231    local query = dataprepared(specification)
232    if not query then
233        report_state("error in preparation")
234        return
235    end
236    local data, keys = datafetched(specification,query,specification.converter)
237    if not data then
238        report_state("error in fetching")
239        return
240    end
241    local one = data[1]
242    if one then
243        setmetatable(data,{ __index = one } )
244    end
245    return data, keys
246end
247
248-- Here we build the dataset stepwise so we don't use the data hack that
249-- is used in the client variant.
250
251local wraptemplate = [[
252local converters    = utilities.sql.converters
253local deserialize   = utilities.sql.deserialize
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