util-sql-imp-client.lua /size: 9474 b    last modification: 2023-12-21 09:44
1if not modules then modules = { } end modules ['util-sql-imp-client'] = {
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-- todo: make a converter
10
11local rawset, setmetatable = rawset, setmetatable
12local 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
13
14local trace_sql          = false  trackers.register("sql.trace",  function(v) trace_sql     = v end)
15local trace_queries      = false  trackers.register("sql.queries",function(v) trace_queries = v end)
16local report_state       = logs.reporter("sql","client")
17
18local sql                = utilities.sql
19local helpers            = sql.helpers
20local methods            = sql.methods
21local validspecification = helpers.validspecification
22local preparetemplate    = helpers.preparetemplate
23local splitdata          = helpers.splitdata
24local replacetemplate    = utilities.templates.replace
25local serialize          = sql.serialize
26local getserver          = sql.getserver
27
28local osclock            = os.gettimeofday
29
30-- Experiments with an p/action demonstrated that there is not much gain. We could do a runtime
31-- capture but creating all the small tables is not faster and it doesn't work well anyway.
32
33local separator    = P("\t")
34local newline      = patterns.newline
35local empty        = Cc("")
36
37local entry        = C((1-separator-newline)^0) -- C 10% faster than Cs
38
39local unescaped    = P("\\n")  / "\n"
40                   + P("\\t")  / "\t"
41                   + P("\\0")  / "\000"
42                   + P("\\\\") / "\\"
43
44local entry        = Cs((unescaped + (1-separator-newline))^0) -- C 10% faster than Cs but Cs needed due to nesting
45
46local getfirst     = Ct( entry * (separator * (entry+empty))^0) + newline
47local skipfirst    = (1-newline)^1 * newline
48local skipdashes   = (P("-")+separator)^1 * newline
49local getfirstline = C((1-newline)^0)
50
51local cache        = { }
52
53local function splitdata(data) -- todo: hash on first line ... maybe move to client module
54    if data == "" then
55        if trace_sql then
56            report_state("no data")
57        end
58        return { }, { }
59    end
60    local first = lpegmatch(getfirstline,data)
61    if not first then
62        if trace_sql then
63            report_state("no data")
64        end
65        return { }, { }
66    end
67    local p = cache[first]
68    if p then
69     -- report_state("reusing: %s",first)
70        local entries = lpegmatch(p.parser,data)
71        return entries or { }, p.keys
72    elseif p == false then
73        return { }, { }
74    elseif p == nil then
75        local keys = lpegmatch(getfirst,first) or { }
76        if #keys == 0 then
77            if trace_sql then
78                report_state("no banner")
79            end
80            cache[first] = false
81            return { }, { }
82        end
83        -- quite generic, could be a helper
84        local n = #keys
85        if n == 0 then
86            report_state("no fields")
87            cache[first] = false
88            return { }, { }
89        end
90        if n == 1 then
91            local key = keys[1]
92            if trace_sql then
93                report_state("one field with name %a",key)
94            end
95            p = Cg(Cc(key) * entry)
96        else
97            for i=1,n do
98                local key = keys[i]
99                if trace_sql then
100                    report_state("field %s has name %a",i,key)
101                end
102                local s = Cg(Cc(key) * entry)
103                if p then
104                    p = p * separator * s
105                else
106                    p = s
107                end
108            end
109        end
110        p = Cf(Ct("") * p,rawset) * newline^1
111     -- p = Ct("") * (p % rawset) * newline^1 -- future
112        if getserver() == "mssql" then
113            p = skipfirst * skipdashes * Ct(p^0)
114        else
115            p = skipfirst * Ct(p^0)
116        end
117        cache[first] = { parser = p, keys = keys }
118        local entries = lpegmatch(p,data)
119        return entries or { }, keys
120    end
121end
122
123local splitter = skipfirst * Ct((Ct(entry * (separator * entry)^0) * newline^1)^0)
124
125local function getdata(data)
126    return lpegmatch(splitter,data)
127end
128
129helpers.splitdata = splitdata
130helpers.getdata   = getdata
131
132local t_runner = {
133    mysql = [[mysql --batch --user="%username%" --password="%password%" --host="%host%" --port=%port% --database="%database%" --default-character-set=utf8 < "%queryfile%" > "%resultfile%"]],
134    mssql = [[sqlcmd -S %host% %?U: -U "%username%" ?% %?P: -P "%password%" ?% -I -W -w 65535 -s"]] .. "\t" .. [[" -m 1 -i "%queryfile%" -o "%resultfile%"]],
135}
136
137local t_runner_login = {
138    mysql = [[mysql --login-path="%login%" --batch --database="%database%" --default-character-set=utf8 < "%queryfile%" > "%resultfile%"]],
139    mssql = [[sqlcmd -S %host% %?U: -U "%username%" ?% %?P: -P "%password%" ?% -I -W -w 65535 -s"]] .. "\t" .. [[" -m 1 -i "%queryfile%" -o "%resultfile%"]],
140}
141
142local t_preamble = {
143    mysql = [[
144SET GLOBAL SQL_MODE=ANSI_QUOTES;
145    ]],
146    mssql = [[
147:setvar SQLCMDERRORLEVEL 1
148SET QUOTED_IDENTIFIER ON;
149SET NOCOUNT ON;
150%?database: USE %database%; ?%
151    ]],
152}
153
154local function dataprepared(specification)
155    local query = preparetemplate(specification)
156    if query then
157        local preamble  = t_preamble[getserver()] or t_preamble.mysql
158        if preamble then
159            preamble = replacetemplate(preamble,specification.variables,'sql')
160            query = preamble .. "\n" .. query
161        end
162        io.savedata(specification.queryfile,query)
163        os.remove(specification.resultfile)
164        if trace_queries then
165            report_state("query: %s",query)
166        end
167        return true
168    else
169        -- maybe push an error
170        os.remove(specification.queryfile)
171        os.remove(specification.resultfile)
172    end
173end
174
175local function datafetched(specification)
176    local runner  = (specification.login and t_runner_login or t_runner)[getserver()] or t_runner.mysql
177    local command = replacetemplate(runner,specification)
178    if trace_sql then
179        local t = osclock()
180        report_state("command: %s",command)
181        -- for now we don't use sandbox.registerrunners as this module is
182        -- also used outside context
183        local okay = os.execute(command)
184        report_state("fetchtime: %.3f sec, return code: %i",osclock()-t,okay) -- not okay under linux
185        return okay == 0
186    else
187        return os.execute(command) == 0
188    end
189end
190
191local function dataloaded(specification)
192    if trace_sql then
193        local t = osclock()
194        local data = io.loaddata(specification.resultfile) or ""
195        report_state("datasize: %.3f MB",#data/1024/1024)
196        report_state("loadtime: %.3f sec",osclock()-t)
197        return data
198    else
199        return io.loaddata(specification.resultfile) or ""
200    end
201end
202
203local function dataconverted(data,converter)
204    if converter then
205        local data = getdata(data)
206        if data then
207            data = converter.client(data)
208        end
209        return data
210    elseif trace_sql then
211        local t = osclock()
212        local data, keys = splitdata(data,target)
213        report_state("converttime: %.3f",osclock()-t)
214        report_state("keys: %s ",#keys)
215        report_state("entries: %s ",#data)
216        return data, keys
217    else
218        return splitdata(data)
219    end
220end
221
222-- todo: new, etc
223
224local function execute(specification)
225    if trace_sql then
226        report_state("executing client")
227    end
228    if not validspecification(specification) then
229        report_state("error in specification")
230        return
231    end
232    if not dataprepared(specification) then
233        report_state("error in preparation")
234        return
235    end
236    if not datafetched(specification) then
237        report_state("error in fetching, query: %s",string.collapsespaces(io.loaddata(specification.queryfile) or "?"))
238        return
239    end
240    local data = dataloaded(specification)
241    if not data then
242        report_state("error in loading")
243        return
244    end
245    local data, keys = dataconverted(data,specification.converter)
246    if not data then
247        report_state("error in converting or no data")
248        return
249    end
250    local one = data[1]
251    if one then
252        setmetatable(data,{ __index = one } )
253    end
254    return data, keys
255end
256
257-- The following is not that (memory) efficient but normally we will use
258-- the lib anyway. Of course we could make a dedicated converter and/or
259-- hook into the splitter code but ... it makes not much sense because then
260-- we can as well move the builder to the library modules.
261--
262-- Here we reuse data as the indexes are the same, unless we hash.
263
264local wraptemplate = [[
265local converters    = utilities.sql.converters
266local deserialize   = utilities.sql.deserialize
267local fromjson      = utilities.sql.fromjson
268
269local tostring      = tostring
270local tonumber      = tonumber
271local booleanstring = string.booleanstring
272
273%s
274
275return function(data)
276    local target = %s -- data or { }
277    for i=1,#data do
278        local cells = data[i]
279        target[%s] = {
280            %s
281        }
282    end
283    return target
284end
285]]
286
287local celltemplate = "cells[%s]"
288
289methods.client = {
290    execute      = execute,
291    usesfiles    = true,
292    wraptemplate = wraptemplate,
293    celltemplate = celltemplate,
294}
295