SourceBrowser

util-sql-imp-swiglib.lua /size: 16 Kb last modification: 2021-10-28 13:50
1if not modules then modules = { } end modules ['util-sql-imp-swiglib'] = {
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
9if true then
10    logs.report("warning","swiglib is no longer supported")
11end
12
13-- As the regular library is flawed (i.e. there are crashes in the table
14-- construction code) and also not that efficient, Luigi Scarso looked into
15-- a swig binding. This is a bit more low level approach but as we stay
16-- closer to the original library it's also less dependant.
17
18local concat = table.concat
19local format, byte = string.format, string.byte
20local lpegmatch = lpeg.match
21local setmetatable, type = setmetatable, type
22local sleep = os.sleep
23
24local trace_sql              = false  trackers.register("sql.trace",  function(v) trace_sql     = v end)
25local trace_queries          = false  trackers.register("sql.queries",function(v) trace_queries = v end)
26local report_state           = logs.reporter("sql","swiglib")
27
28local helpers                = require("swiglib.helpers.core")
29local sql                    = utilities.sql
30local mysql                  = require("swiglib.mysql.core") -- "5.6.14"
31----- mysql                  = swiglib("mysql.core") -- "5.6.14"
32
33local new_u_char_array       = helpers.new_u_char_array     or helpers.new_ucharArray
34local ucharArray_setitem     = helpers.u_char_array_setitem or helpers.ucharArray_setitem
35local int_p_assign           = helpers.int_p_assign
36local ulongArray_getitem     = helpers.u_long_array_getitem or helpers.ulongArray_getitem
37
38-- inspect(table.sortedkeys(mysql))
39
40local nofretries             = 5
41local retrydelay             = 1
42
43local cache                  = { }
44local helpers                = sql.helpers
45local methods                = sql.methods
46local validspecification     = helpers.validspecification
47local querysplitter          = helpers.querysplitter
48local dataprepared           = helpers.preparetemplate
49local serialize              = sql.serialize
50local deserialize            = sql.deserialize
51
52local mysql_initialize       = mysql.mysql_init
53
54local mysql_open_connection  = mysql.mysql_real_connect
55local mysql_execute_query    = mysql.mysql_real_query
56local mysql_close_connection = mysql.mysql_close
57
58local mysql_field_seek       = mysql.mysql_field_seek
59local mysql_num_fields       = mysql.mysql_num_fields
60local mysql_fetch_field      = mysql.mysql_fetch_field
61local mysql_num_rows         = mysql.mysql_num_rows
62local mysql_fetch_row        = mysql.mysql_fetch_row
63local mysql_fetch_lengths    = mysql.mysql_fetch_lengths
64local mysql_init             = mysql.mysql_init
65local mysql_store_result     = mysql.mysql_store_result
66local mysql_free_result      = mysql.mysql_free_result
67local mysql_use_result       = mysql.mysql_use_result
68
69local mysql_error_message    = mysql.mysql_error
70----- mysql_options_argument = mysql.mysql_options_argument
71
72local instance               = mysql.MYSQL()
73
74local mysql_constant_false   = false
75local mysql_constant_true    = true
76
77----- util_getbytearray      = mysql.util_getbytearray
78
79-- if mysql_options_argument then
80--
81--     mysql_constant_false = mysql_options_argument(false) -- 0 "\0"
82--     mysql_constant_true  = mysql_options_argument(true)  -- 1 "\1"
83--
84--     -- print(swig_type(mysql_constant_false))
85--     -- print(swig_type(mysql_constant_true))
86--
87--     mysql.mysql_options(instance,mysql.MYSQL_OPT_RECONNECT,mysql_constant_true);
88--
89-- else
90--
91--     print("")
92--     print("incomplete swiglib.mysql interface")
93--     print("")
94--
95-- end
96
97-- some helpers:
98
99function mysql.options_argument(arg)
100    local targ = type(arg)
101    if targ == "boolean" then
102        local o = new_u_char_array(1)
103        ucharArray_setitem(o,0,arg == true and 64 or 0)
104        return o
105    elseif targ == "string" then
106        local o = new_u_char_array(#arg)
107        ucharArray_setitem(o,0,0)
108        for i=1,#arg do
109            ucharArray_setitem(o,i-1,byte(arg,i))
110        end
111        return o
112    elseif targ == "number" then
113        local o = core.new_int_p()
114        int_p_assign(o, arg)
115        return o
116    else
117        return nil
118    end
119end
120
121-- function mysql.util_unpackbytearray(row,noffields,len)
122--     if row == nil then
123--         return { }
124--     elseif noffields < 1 then
125--         return { }
126--     else
127--         local t = { }
128--         for i=0,noffields-1 do
129--             local l = ulongArray_getitem(len,i)  -- zero based ... element from len array
130--             local r = util_getbytearray(row,i,l) -- zero based ... element from len array
131--             t[#t+1]= r
132--         end
133--         return t
134--     end
135-- end
136
137--
138
139-- local typemap = mysql.MYSQL_TYPE_VAR_STRING and {
140--     [mysql.MYSQL_TYPE_VAR_STRING ]  = "string",
141--     [mysql.MYSQL_TYPE_STRING     ]  = "string",
142--     [mysql.MYSQL_TYPE_DECIMAL    ]  = "number",
143--     [mysql.MYSQL_TYPE_SHORT      ]  = "number",
144--     [mysql.MYSQL_TYPE_LONG       ]  = "number",
145--     [mysql.MYSQL_TYPE_FLOAT      ]  = "number",
146--     [mysql.MYSQL_TYPE_DOUBLE     ]  = "number",
147--     [mysql.MYSQL_TYPE_LONGLONG   ]  = "number",
148--     [mysql.MYSQL_TYPE_INT24      ]  = "number",
149--     [mysql.MYSQL_TYPE_YEAR       ]  = "number",
150--     [mysql.MYSQL_TYPE_TINY       ]  = "number",
151--     [mysql.MYSQL_TYPE_TINY_BLOB  ]  = "binary",
152--     [mysql.MYSQL_TYPE_MEDIUM_BLOB]  = "binary",
153--     [mysql.MYSQL_TYPE_LONG_BLOB  ]  = "binary",
154--     [mysql.MYSQL_TYPE_BLOB       ]  = "binary",
155--     [mysql.MYSQL_TYPE_DATE       ]  = "date",
156--     [mysql.MYSQL_TYPE_NEWDATE    ]  = "date",
157--     [mysql.MYSQL_TYPE_DATETIME   ]  = "datetime",
158--     [mysql.MYSQL_TYPE_TIME       ]  = "time",
159--     [mysql.MYSQL_TYPE_TIMESTAMP  ]  = "time",
160--     [mysql.MYSQL_TYPE_ENUM       ]  = "set",
161--     [mysql.MYSQL_TYPE_SET        ]  = "set",
162--     [mysql.MYSQL_TYPE_NULL       ]  = "null",
163-- }
164
165-- real_escape_string
166
167local function finish(t)
168    local r = t._result_
169    if r then
170        mysql_free_result(r)
171    end
172end
173
174-- will become metatable magic
175
176-- local function analyze(result)
177--     mysql_field_seek(result,0)
178--     local nofrows = mysql_num_rows(result) or 0
179--     local noffields = mysql_num_fields(result)
180--     local names = { }
181--     local types = { }
182--     for i=1,noffields do
183--         local field = mysql_fetch_field(result)
184--         names[i] = field.name
185--         types[i] = field.type
186--     end
187--     return names, types, noffields, nofrows
188-- end
189
190local function getcolnames(t)
191    return t.names
192end
193
194local function getcoltypes(t)
195    return t.types
196end
197
198local function numrows(t)
199    return t.nofrows
200end
201
202local fetch_fields_from_current_row = mysql.util_mysql_fetch_fields_from_current_row
203local fetch_all_rows                = mysql.util_mysql_fetch_all_rows
204
205-- swig_type
206
207-- local function list(t)
208--     local result = t._result_
209--     local row = mysql_fetch_row(result)
210--     local len = mysql_fetch_lengths(result)
211--     local result = { }
212--     for i=1,t.noffields do
213--         local r = i - 1 -- zero offset
214--         result[i] = util_getbytearray(row,r,ulongArray_getitem(len,r))
215--     end
216--     return result
217-- end
218
219-- local function hash(t)
220--     local list = fetch_fields_from_current_row(t._result_)
221--     local result = t._result_
222--     local fields = t.names
223--     local row = mysql_fetch_row(result)
224--     local len = mysql_fetch_lengths(result)
225--     local result = { }
226--     for i=1,t.noffields do
227--         local r = i - 1 -- zero offset
228--         result[fields[i]] = util_getbytearray(row,r,ulongArray_getitem(len,r))
229--     end
230--     return result
231-- end
232
233local function list(t)
234    return fetch_fields_from_current_row(t._result_)
235end
236
237local function hash(t)
238    local list = fetch_fields_from_current_row(t._result_)
239    local fields = t.names
240    local data = { }
241    for i=1,t.noffields do
242        data[fields[i]] = list[i]
243    end
244    return data
245end
246
247local function wholelist(t)
248    return fetch_all_rows(t._result_)
249end
250
251local mt = { __index = {
252        -- regular
253        finish      = finish,
254        list        = list,
255        hash        = hash,
256        wholelist   = wholelist,
257        -- compatibility
258        numrows     = numrows,
259        getcolnames = getcolnames,
260        getcoltypes = getcoltypes,
261        -- fallback
262        _result_    = nil,
263        names       = { },
264        types       = { },
265        noffields   = 0,
266        nofrows     = 0,
267    }
268}
269
270local nt = setmetatable({},mt)
271
272-- session
273
274local function close(t)
275    mysql_close_connection(t._connection_)
276end
277
278local function execute(t,query)
279    if query and query ~= "" then
280        local connection = t._connection_
281        local result = mysql_execute_query(connection,query,#query)
282        if result == 0 then
283            local result = mysql_store_result(connection)
284            if result then
285                mysql_field_seek(result,0)
286                local nofrows   = mysql_num_rows(result) or 0
287                local noffields = mysql_num_fields(result)
288                local names     = { }
289                local types     = { }
290                for i=1,noffields do
291                    local field = mysql_fetch_field(result)
292                    names[i] = field.name
293                    types[i] = field.type
294                end
295                local t = {
296                    _result_  = result,
297                    names     = names,
298                    types     = types,
299                    noffields = noffields,
300                    nofrows   = nofrows,
301                }
302                return setmetatable(t,mt)
303            else
304                return nt
305            end
306        end
307    end
308    return false
309end
310
311local mt = { __index = {
312        close   = close,
313        execute = execute,
314    }
315}
316
317local function open(t,database,username,password,host,port)
318    local connection = mysql_open_connection(t._session_,host or "localhost",username or "",password or "",database or "",port or 0,0,0)
319    if connection then
320        local t = {
321            _connection_ = connection,
322        }
323        return setmetatable(t,mt)
324    end
325end
326
327local function message(t)
328    return mysql_error_message(t._session_)
329end
330
331local function close(t)
332    -- dummy, as we have a global session
333end
334
335local mt = {
336    __index = {
337        connect = open,
338        close   = close,
339        message = message,
340    }
341}
342
343local function initialize()
344    local session = {
345        _session_ = mysql_initialize(instance) -- maybe share, single thread anyway
346    }
347    return setmetatable(session,mt)
348end
349
350-- -- -- --
351
352local function connect(session,specification)
353    return session:connect(
354        specification.database or "",
355        specification.username or "",
356        specification.password or "",
357        specification.host     or "",
358        specification.port
359    )
360end
361
362local function error_in_connection(specification,action)
363    report_state("error in connection: [%s] %s@%s to %s:%s",
364            action or "unknown",
365            specification.database or "no database",
366            specification.username or "no username",
367            specification.host     or "no host",
368            specification.port     or "no port"
369        )
370end
371
372local function datafetched(specification,query,converter)
373    if not query or query == "" then
374        report_state("no valid query")
375        return { }, { }
376    end
377    local id = specification.id
378    local session, connection
379    if id then
380        local c = cache[id]
381        if c then
382            session    = c.session
383            connection = c.connection
384        end
385        if not connection then
386            session = initialize()
387            connection = connect(session,specification)
388            if not connection then
389                for i=1,nofretries do
390                    sleep(retrydelay)
391                    report_state("retrying to connect: [%s.%s] %s@%s to %s:%s",
392                            id,i,
393                            specification.database or "no database",
394                            specification.username or "no username",
395                            specification.host     or "no host",
396                            specification.port     or "no port"
397                        )
398                    connection = connect(session,specification)
399                    if connection then
400                        break
401                    end
402                end
403            end
404            if connection then
405                cache[id] = { session = session, connection = connection }
406            end
407        end
408    else
409        session = initialize()
410        connection = connect(session,specification)
411        if not connection then
412            for i=1,nofretries do
413                sleep(retrydelay)
414                report_state("retrying to connect: [%s] %s@%s to %s:%s",
415                        i,
416                        specification.database or "no database",
417                        specification.username or "no username",
418                        specification.host     or "no host",
419                        specification.port     or "no port"
420                    )
421                connection = connect(session,specification)
422                if connection then
423                    break
424                end
425            end
426        end
427    end
428    if not connection then
429        report_state("error in connection: %s@%s to %s:%s",
430                specification.database or "no database",
431                specification.username or "no username",
432                specification.host     or "no host",
433                specification.port     or "no port"
434            )
435        return { }, { }
436    end
437    query = lpegmatch(querysplitter,query)
438    local result, message, okay
439    for i=1,#query do
440        local q = query[i]
441        local r, m = connection:execute(q)
442        if m then
443            report_state("error in query, stage: %s",string.collapsespaces(q or "?"))
444            message = message and format("%s\n%s",message,m) or m
445        end
446        if type(r) == "table" then
447            result = r
448            okay = true
449        elseif not m  then
450            okay = true
451        end
452    end
453    local data, keys
454    if result then
455        if converter then
456            data = converter.swiglib(result)
457        else
458            keys = result.names
459            data = { }
460            for i=1,result.nofrows do
461                data[i] = result:hash()
462            end
463        end
464        result:finish() -- result:close()
465    elseif message then
466        report_state("message %s",message)
467    end
468    if not keys then
469        keys = { }
470    end
471    if not data then
472        data = { }
473    end
474    if not id then
475        connection:close()
476        session:close()
477    end
478    return data, keys
479end
480
481local function execute(specification)
482    if trace_sql then
483        report_state("executing library")
484    end
485    if not validspecification(specification) then
486        report_state("error in specification")
487        return
488    end
489    local query = dataprepared(specification)
490    if not query then
491        report_state("error in preparation")
492        return
493    end
494    local data, keys = datafetched(specification,query,specification.converter)
495    if not data then
496        report_state("error in fetching")
497        return
498    end
499    local one = data[1]
500    if one then
501        setmetatable(data,{ __index = one } )
502    end
503    return data, keys
504end
505
506local wraptemplate = [[
507local mysql         = require("swiglib.mysql.core") -- will be stored in method
508
509local fetch_fields  = mysql.util_mysql_fetch_fields_from_current_row
510
511local converters    = utilities.sql.converters
512local deserialize   = utilities.sql.deserialize
513
514local tostring      = tostring
515local tonumber      = tonumber
516local booleanstring = string.booleanstring
517
518%s
519
520return function(result)
521    if not result then
522        return { }
523    end
524    local nofrows = result.nofrows or 0
525    if nofrows == 0 then
526        return { }
527    end
528    local noffields = result.noffields or 0
529    local target = { } -- no %s needed here
530    result = result._result_
531    for i=1,nofrows do
532        local cells = fetch_fields(result)
533        target[%s] = {
534            %s
535        }
536    end
537    return target
538end
539]]
540
541local celltemplate = "cells[%s]"
542
543methods.swiglib = {
544    runner       = function() end, -- never called
545    execute      = execute,
546    initialize   = initialize, -- returns session
547    usesfiles    = false,
548    wraptemplate = wraptemplate,
549    celltemplate = celltemplate,
550}
551
Source Browser ?
