util-sql-imp-ffi.lua /size: 17 Kb    last modification: 2023-12-21 09:44
1if not modules then modules = { } end modules ['util-sql-imp-ffi'] = {
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-- I looked at luajit-mysql to see how the ffi mapping was done but it didn't work
10-- out that well (at least not on windows) but I got the picture. As I have somewhat
11-- different demands I simplified.
12
13local tonumber = tonumber
14local concat = table.concat
15local format, byte = string.format, string.byte
16local lpegmatch = lpeg.match
17local setmetatable, type = setmetatable, type
18local sleep = os.sleep
19local formatters = string.formatters
20
21local trace_sql     = false  trackers.register("sql.trace",  function(v) trace_sql     = v end)
22local trace_queries = false  trackers.register("sql.queries",function(v) trace_queries = v end)
23local report_state  = logs.reporter("sql","ffi")
24
25if not utilities.sql then
26    require("util-sql")
27end
28
29ffi.cdef [[
30
31    /*
32        This is as lean and mean as possible. After all we just need a connection and
33        a query. The rest is handled already in the Lua code elsewhere.
34    */
35
36    void free(void*ptr);
37    void * malloc(size_t size);
38
39    typedef void MYSQL_instance;
40    typedef void MYSQL_result;
41    typedef char **MYSQL_row;
42    typedef unsigned int MYSQL_offset;
43
44    typedef struct st_mysql_field {
45        char *name;
46        char *org_name;
47        char *table;
48        char *org_table;
49        char *db;
50        char *catalog;
51        char *def;
52        unsigned long length;
53        unsigned long max_length;
54        unsigned int name_length;
55        unsigned int org_name_length;
56        unsigned int table_length;
57        unsigned int org_table_length;
58        unsigned int db_length;
59        unsigned int catalog_length;
60        unsigned int def_length;
61        unsigned int flags;
62        unsigned int decimals;
63        unsigned int charsetnr;
64        int type;
65        void *extension;
66    } MYSQL_field;
67
68    MYSQL_instance * mysql_init (
69        MYSQL_instance *mysql
70    );
71
72    MYSQL_instance * mysql_real_connect (
73        MYSQL_instance *mysql,
74        const char *host,
75        const char *user,
76        const char *passwd,
77        const char *db,
78        unsigned int port,
79        const char *unix_socket,
80        unsigned long clientflag
81    );
82
83    unsigned int mysql_errno (
84        MYSQL_instance *mysql
85    );
86
87    const char *mysql_error (
88        MYSQL_instance *mysql
89    );
90
91    /* int mysql_query (
92        MYSQL_instance *mysql,
93        const char *q
94    ); */
95
96    int mysql_real_query (
97        MYSQL_instance *mysql,
98        const char *q,
99        unsigned long length
100    );
101
102    MYSQL_result * mysql_store_result (
103        MYSQL_instance *mysql
104    );
105
106    void mysql_free_result (
107        MYSQL_result *result
108    );
109
110    unsigned long long mysql_num_rows (
111        MYSQL_result *res
112    );
113
114    MYSQL_row mysql_fetch_row (
115        MYSQL_result *result
116    );
117
118    unsigned int mysql_affected_rows (
119        MYSQL_instance *mysql
120    );
121
122    unsigned int mysql_field_count (
123        MYSQL_instance *mysql
124    );
125
126    unsigned int mysql_num_fields (
127        MYSQL_result *res
128    );
129
130    /* MYSQL_field *mysql_fetch_field (
131        MYSQL_result *result
132    ); */
133
134    MYSQL_field * mysql_fetch_fields (
135        MYSQL_result *res
136    );
137
138    MYSQL_offset mysql_field_seek(
139        MYSQL_result *result,
140        MYSQL_offset offset
141    );
142
143    void mysql_close(
144        MYSQL_instance *sock
145    );
146
147    /* unsigned long * mysql_fetch_lengths(
148        MYSQL_result *result
149    ); */
150
151]]
152-- trackers.enable("*lib*")
153local sql                    = utilities.sql
154----- mysql                  = ffi.load(os.name == "windows" and "libmysql" or "libmysqlclient")
155----- mysql                  = ffilib(os.name == "windows" and "libmysql" or "libmysqlclient")
156local mysql                  = ffilib(os.name == "windows" and "libmysql" or "libmysql")
157
158if not mysql then
159    report_state("unable to load library")
160end
161
162local nofretries             = 5
163local retrydelay             = 1
164
165local cache                  = { }
166local helpers                = sql.helpers
167local methods                = sql.methods
168local validspecification     = helpers.validspecification
169local querysplitter          = helpers.querysplitter
170local dataprepared           = helpers.preparetemplate
171local serialize              = sql.serialize
172
173local mysql_open_session     = mysql.mysql_init
174
175local mysql_open_connection  = mysql.mysql_real_connect
176local mysql_execute_query    = mysql.mysql_real_query
177local mysql_close_connection = mysql.mysql_close
178
179local mysql_affected_rows    = mysql.mysql_affected_rows
180local mysql_field_count      = mysql.mysql_field_count
181local mysql_field_seek       = mysql.mysql_field_seek
182local mysql_num_fields       = mysql.mysql_num_fields
183local mysql_fetch_fields     = mysql.mysql_fetch_fields
184----- mysql_fetch_field      = mysql.mysql_fetch_field
185local mysql_num_rows         = mysql.mysql_num_rows
186local mysql_fetch_row        = mysql.mysql_fetch_row
187----- mysql_fetch_lengths    = mysql.mysql_fetch_lengths
188local mysql_init             = mysql.mysql_init
189local mysql_store_result     = mysql.mysql_store_result
190local mysql_free_result      = mysql.mysql_free_result
191
192local mysql_error_number     = mysql.mysql_errno
193local mysql_error_message    = mysql.mysql_error
194
195local NULL                   = ffi.cast("MYSQL_result *",0)
196
197local ffi_tostring           = ffi.string
198local ffi_gc                 = ffi.gc
199
200local instance               = mysql.mysql_init(nil)
201
202local mysql_constant_false   = false
203local mysql_constant_true    = true
204
205local wrapresult  do
206
207    local function collect(t)
208        local result = t._result_
209        if result then
210            ffi_gc(result,mysql_free_result)
211        end
212    end
213
214    local function finish(t)
215        local result = t._result_
216        if result then
217            t._result_ = nil
218            ffi_gc(result,mysql_free_result)
219        end
220    end
221
222    local function getcoldata(t)
223        local result = t._result_
224        local nofrows   = t.nofrows
225        local noffields = t.noffields
226        local names     = { }
227        local types     = { }
228        local fields    = mysql_fetch_fields(result)
229        for i=1,noffields do
230            local field = fields[i-1]
231            names[i] = ffi_tostring(field.name)
232            types[i] = tonumber(field.type) -- todo
233        end
234        t.names = names
235        t.types = types
236    end
237
238    local function getcolnames(t)
239        local names = t.names
240        if names then
241            return names
242        end
243        getcoldata(t)
244        return t.names
245    end
246
247    local function getcoltypes(t)
248        local types = t.types
249        if types then
250            return types
251        end
252        getcoldata(t)
253        return t.types
254    end
255
256    local function numrows(t)
257        return t.nofrows
258    end
259
260    -- local function fetch(t)
261    --     local
262    --     local row    = mysql_fetch_row(result)
263    --     local result = { }
264    --     for i=1,t.noffields do
265    --         result[i] = ffi_tostring(row[i-1])
266    --     end
267    --     return unpack(result)
268    -- end
269
270    local mt = {
271        __gc    = collect,
272        __index = {
273            _result_    = nil,
274            close       = finish,
275            numrows     = numrows,
276            getcolnames = getcolnames,
277            getcoltypes = getcoltypes,
278         -- fetch       = fetch, -- not efficient
279        }
280    }
281
282    wrapresult = function(connection)
283        local result = mysql_store_result(connection)
284        if result ~= NULL then
285            mysql_field_seek(result,0)
286            local t = {
287                _result_  = result,
288                nofrows   = tonumber(mysql_num_rows  (result) or 0) or 0,
289                noffields = tonumber(mysql_num_fields(result) or 0) or 0,
290            }
291            return setmetatable(t,mt)
292        elseif tonumber(mysql_field_count(connection) or 0) or 0 > 0 then
293            return tonumber(mysql_affected_rows(connection))
294        end
295    end
296
297end
298
299local initializesession  do
300
301    -- timeouts = [ connect_timeout |wait_timeout | interactive_timeout ]
302
303    local timeout -- = 3600 -- to be tested
304
305    -- connection
306
307    local function close(t)
308        -- just a struct ?
309    end
310
311    local function execute(t,query)
312        if query and query ~= "" then
313            local connection = t._connection_
314            local result = mysql_execute_query(connection,query,#query)
315            if result == 0 then
316                return wrapresult(connection)
317            else
318             -- mysql_error_number(connection)
319                return false, ffi_tostring(mysql_error_message(connection))
320            end
321        end
322        return false
323    end
324
325    local mt = {
326        __index = {
327            close   = close,
328            execute = execute,
329        }
330    }
331
332    -- session
333
334    local function open(t,database,username,password,host,port)
335        local connection = mysql_open_connection(
336            t._session_,
337            host or "localhost",
338            username or "",
339            password or "",
340            database or "",
341            port or 0,
342            NULL,
343            0
344        )
345        if connection ~= NULL then
346            if timeout then
347                execute(connection,formatters["SET SESSION connect_timeout=%s ;"](timeout))
348            end
349            local t = {
350                _connection_ = connection,
351            }
352            return setmetatable(t,mt)
353        end
354    end
355
356    local function message(t)
357        return mysql_error_message(t._session_)
358    end
359
360    local function close(t)
361        local connection = t._connection_
362        if connection and connection ~= NULL then
363            ffi_gc(connection, mysql_close)
364            t.connection = nil
365        end
366    end
367
368    local mt = {
369        __index = {
370            connect = open,
371            close   = close,
372            message = message,
373        },
374    }
375
376    initializesession = function()
377        local session = {
378            _session_ = mysql_open_session(instance) -- maybe share, single thread anyway
379        }
380        return setmetatable(session,mt)
381    end
382
383end
384
385local executequery  do
386
387    local function connect(session,specification)
388        return session:connect(
389            specification.database or "",
390            specification.username or "",
391            specification.password or "",
392            specification.host     or "",
393            specification.port
394        )
395    end
396
397    local function fetched(specification,query,converter)
398        if not query or query == "" then
399            report_state("no valid query")
400            return false
401        end
402        local id = specification.id
403        local session, connection
404        if id then
405            local c = cache[id]
406            if c then
407                session    = c.session
408                connection = c.connection
409            end
410            if not connection then
411                session = initializesession()
412                if not session then
413                    return formatters["no session for %a"](id)
414                end
415                connection = connect(session,specification)
416                if not connection then
417                    return formatters["no connection for %a"](id)
418                end
419                cache[id] = { session = session, connection = connection }
420            end
421        else
422            session = initializesession()
423            if not session then
424                return "no session"
425            end
426            connection = connect(session,specification)
427            if not connection then
428                return "no connection"
429            end
430        end
431        if not connection then
432            report_state("error in connection: %s@%s to %s:%s",
433                specification.database or "no database",
434                specification.username or "no username",
435                specification.host     or "no host",
436                specification.port     or "no port"
437            )
438            return "no connection"
439        end
440        query = lpegmatch(querysplitter,query)
441        local result, okay
442        for i=1,#query do
443            local q = query[i]
444            local r, m = connection:execute(q)
445            if m then
446                report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q or "?"))
447                if m then
448                    report_state("message: %s",m)
449                end
450            end
451            local t = type(r)
452            if t == "table" then
453                result = r
454                okay = true
455            elseif t == "number" then
456                okay = true
457            end
458        end
459        if not okay then -- can go
460            -- why do we close a session
461            if connection then
462                connection:close()
463            end
464            if session then
465                session:close()
466            end
467            if id then
468                cache[id] = nil
469            end
470            return "execution error"
471        end
472        local data, keys
473        if result then
474            if converter then
475                data = converter.ffi(result)
476            else
477                local _result_  = result._result_
478                local noffields = result.noffields
479                local nofrows   = result.nofrows
480                keys = result:getcolnames()
481                data = { }
482                if noffields > 0 and nofrows > 0 then
483                    for i=1,nofrows do
484                        local cells = { }
485                        local row   = mysql_fetch_row(_result_)
486                        for j=1,noffields do
487                            local s = row[j-1]
488                            local k = keys[j]
489                            if s == NULL then
490                                cells[k] = ""
491                            else
492                                cells[k] = ffi_tostring(s)
493                            end
494                        end
495                        data[i] = cells
496                    end
497                end
498            end
499            result:close()
500        end
501        --
502        if not id then
503            if connection then
504                connection:close()
505            end
506            if session then
507                session:close()
508            end
509        end
510        return false, data, keys
511    end
512
513    local function datafetched(specification,query,converter)
514        local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
515        if not callokay then
516            report_state("call error, retrying")
517            callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
518        elseif connectionerror then
519            report_state("error: %s, retrying",connectionerror)
520            callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
521        end
522        if not callokay then
523            report_state("persistent call error")
524        elseif connectionerror then
525            report_state("persistent error: %s",connectionerror)
526        end
527        return data or { }, keys or { }
528    end
529
530    executequery = function(specification)
531        if trace_sql then
532            report_state("executing library")
533        end
534        if not validspecification(specification) then
535            report_state("error in specification")
536            return
537        end
538        local query = dataprepared(specification)
539        if not query then
540            report_state("error in preparation")
541            return
542        end
543        local data, keys = datafetched(specification,query,specification.converter)
544        if not data then
545            report_state("error in fetching")
546            return
547        end
548        local one = data[1]
549        if one then
550            setmetatable(data,{ __index = one } )
551        end
552        return data, keys
553    end
554
555end
556
557local wraptemplate = [[
558----- mysql           = ffi.load(os.name == "windows" and "libmysql" or "libmysqlclient")
559local mysql           = ffi.load(os.name == "windows" and "libmysql" or "libmysql")
560
561local mysql_fetch_row = mysql.mysql_fetch_row
562local ffi_tostring    = ffi.string
563
564local converters      = utilities.sql.converters
565local deserialize     = utilities.sql.deserialize
566local fromjson        = utilities.sql.fromjson
567
568local tostring        = tostring
569local tonumber        = tonumber
570local booleanstring   = string.booleanstring
571
572local NULL            = ffi.cast("MYSQL_result *",0)
573
574%s
575
576return function(result)
577    if not result then
578        return { }
579    end
580    local nofrows = result.nofrows
581    if nofrows == 0 then
582        return { }
583    end
584    local noffields = result.noffields
585    local target    = { } -- no %s needed here
586    local _result_  = result._result_
587    -- we can share cells
588    for i=1,nofrows do
589        local cells = { }
590        local row   = mysql_fetch_row(_result_)
591        for j=1,noffields do
592            local s = row[j-1]
593            if s == NULL then
594                cells[j] = ""
595            else
596                cells[j] = ffi_tostring(s)
597            end
598        end
599        target[%s] = {
600            %s
601        }
602    end
603    result:close()
604    return target
605end
606]]
607
608local celltemplate = "cells[%s]"
609
610methods.ffi = {
611    runner       = function() end,    -- never called
612    execute      = executequery,
613    initialize   = initializesession, -- returns session
614    usesfiles    = false,
615    wraptemplate = wraptemplate,
616    celltemplate = celltemplate,
617}
618