util-sql-imp-ffi.lua /size: 17 Kb    last modification: 2021-10-28 13:50
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
172local deserialize            = sql.deserialize
173
174local mysql_open_session     = mysql.mysql_init
175
176local mysql_open_connection  = mysql.mysql_real_connect
177local mysql_execute_query    = mysql.mysql_real_query
178local mysql_close_connection = mysql.mysql_close
179
180local mysql_affected_rows    = mysql.mysql_affected_rows
181local mysql_field_count      = mysql.mysql_field_count
182local mysql_field_seek       = mysql.mysql_field_seek
183local mysql_num_fields       = mysql.mysql_num_fields
184local mysql_fetch_fields     = mysql.mysql_fetch_fields
185----- mysql_fetch_field      = mysql.mysql_fetch_field
186local mysql_num_rows         = mysql.mysql_num_rows
187local mysql_fetch_row        = mysql.mysql_fetch_row
188----- mysql_fetch_lengths    = mysql.mysql_fetch_lengths
189local mysql_init             = mysql.mysql_init
190local mysql_store_result     = mysql.mysql_store_result
191local mysql_free_result      = mysql.mysql_free_result
192
193local mysql_error_number     = mysql.mysql_errno
194local mysql_error_message    = mysql.mysql_error
195
196local NULL                   = ffi.cast("MYSQL_result *",0)
197
198local ffi_tostring           = ffi.string
199local ffi_gc                 = ffi.gc
200
201local instance               = mysql.mysql_init(nil)
202
203local mysql_constant_false   = false
204local mysql_constant_true    = true
205
206local wrapresult  do
207
208    local function collect(t)
209        local result = t._result_
210        if result then
211            ffi_gc(result,mysql_free_result)
212        end
213    end
214
215    local function finish(t)
216        local result = t._result_
217        if result then
218            t._result_ = nil
219            ffi_gc(result,mysql_free_result)
220        end
221    end
222
223    local function getcoldata(t)
224        local result = t._result_
225        local nofrows   = t.nofrows
226        local noffields = t.noffields
227        local names     = { }
228        local types     = { }
229        local fields    = mysql_fetch_fields(result)
230        for i=1,noffields do
231            local field = fields[i-1]
232            names[i] = ffi_tostring(field.name)
233            types[i] = tonumber(field.type) -- todo
234        end
235        t.names = names
236        t.types = types
237    end
238
239    local function getcolnames(t)
240        local names = t.names
241        if names then
242            return names
243        end
244        getcoldata(t)
245        return t.names
246    end
247
248    local function getcoltypes(t)
249        local types = t.types
250        if types then
251            return types
252        end
253        getcoldata(t)
254        return t.types
255    end
256
257    local function numrows(t)
258        return t.nofrows
259    end
260
261    -- local function fetch(t)
262    --     local
263    --     local row    = mysql_fetch_row(result)
264    --     local result = { }
265    --     for i=1,t.noffields do
266    --         result[i] = ffi_tostring(row[i-1])
267    --     end
268    --     return unpack(result)
269    -- end
270
271    local mt = {
272        __gc    = collect,
273        __index = {
274            _result_    = nil,
275            close       = finish,
276            numrows     = numrows,
277            getcolnames = getcolnames,
278            getcoltypes = getcoltypes,
279         -- fetch       = fetch, -- not efficient
280        }
281    }
282
283    wrapresult = function(connection)
284        local result = mysql_store_result(connection)
285        if result ~= NULL then
286            mysql_field_seek(result,0)
287            local t = {
288                _result_  = result,
289                nofrows   = tonumber(mysql_num_rows  (result) or 0) or 0,
290                noffields = tonumber(mysql_num_fields(result) or 0) or 0,
291            }
292            return setmetatable(t,mt)
293        elseif tonumber(mysql_field_count(connection) or 0) or 0 > 0 then
294            return tonumber(mysql_affected_rows(connection))
295        end
296    end
297
298end
299
300local initializesession  do
301
302    -- timeouts = [ connect_timeout |wait_timeout | interactive_timeout ]
303
304    local timeout -- = 3600 -- to be tested
305
306    -- connection
307
308    local function close(t)
309        -- just a struct ?
310    end
311
312    local function execute(t,query)
313        if query and query ~= "" then
314            local connection = t._connection_
315            local result = mysql_execute_query(connection,query,#query)
316            if result == 0 then
317                return wrapresult(connection)
318            else
319             -- mysql_error_number(connection)
320                return false, ffi_tostring(mysql_error_message(connection))
321            end
322        end
323        return false
324    end
325
326    local mt = {
327        __index = {
328            close   = close,
329            execute = execute,
330        }
331    }
332
333    -- session
334
335    local function open(t,database,username,password,host,port)
336        local connection = mysql_open_connection(
337            t._session_,
338            host or "localhost",
339            username or "",
340            password or "",
341            database or "",
342            port or 0,
343            NULL,
344            0
345        )
346        if connection ~= NULL then
347            if timeout then
348                execute(connection,formatters["SET SESSION connect_timeout=%s ;"](timeout))
349            end
350            local t = {
351                _connection_ = connection,
352            }
353            return setmetatable(t,mt)
354        end
355    end
356
357    local function message(t)
358        return mysql_error_message(t._session_)
359    end
360
361    local function close(t)
362        local connection = t._connection_
363        if connection and connection ~= NULL then
364            ffi_gc(connection, mysql_close)
365            t.connection = nil
366        end
367    end
368
369    local mt = {
370        __index = {
371            connect = open,
372            close   = close,
373            message = message,
374        },
375    }
376
377    initializesession = function()
378        local session = {
379            _session_ = mysql_open_session(instance) -- maybe share, single thread anyway
380        }
381        return setmetatable(session,mt)
382    end
383
384end
385
386local executequery  do
387
388    local function connect(session,specification)
389        return session:connect(
390            specification.database or "",
391            specification.username or "",
392            specification.password or "",
393            specification.host     or "",
394            specification.port
395        )
396    end
397
398    local function fetched(specification,query,converter)
399        if not query or query == "" then
400            report_state("no valid query")
401            return false
402        end
403        local id = specification.id
404        local session, connection
405        if id then
406            local c = cache[id]
407            if c then
408                session    = c.session
409                connection = c.connection
410            end
411            if not connection then
412                session = initializesession()
413                if not session then
414                    return formatters["no session for %a"](id)
415                end
416                connection = connect(session,specification)
417                if not connection then
418                    return formatters["no connection for %a"](id)
419                end
420                cache[id] = { session = session, connection = connection }
421            end
422        else
423            session = initializesession()
424            if not session then
425                return "no session"
426            end
427            connection = connect(session,specification)
428            if not connection then
429                return "no connection"
430            end
431        end
432        if not connection then
433            report_state("error in connection: %s@%s to %s:%s",
434                specification.database or "no database",
435                specification.username or "no username",
436                specification.host     or "no host",
437                specification.port     or "no port"
438            )
439            return "no connection"
440        end
441        query = lpegmatch(querysplitter,query)
442        local result, okay
443        for i=1,#query do
444            local q = query[i]
445            local r, m = connection:execute(q)
446            if m then
447                report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q or "?"))
448                if m then
449                    report_state("message: %s",m)
450                end
451            end
452            local t = type(r)
453            if t == "table" then
454                result = r
455                okay = true
456            elseif t == "number" then
457                okay = true
458            end
459        end
460        if not okay then -- can go
461            -- why do we close a session
462            if connection then
463                connection:close()
464            end
465            if session then
466                session:close()
467            end
468            if id then
469                cache[id] = nil
470            end
471            return "execution error"
472        end
473        local data, keys
474        if result then
475            if converter then
476                data = converter.ffi(result)
477            else
478                local _result_  = result._result_
479                local noffields = result.noffields
480                local nofrows   = result.nofrows
481                keys = result:getcolnames()
482                data = { }
483                if noffields > 0 and nofrows > 0 then
484                    for i=1,nofrows do
485                        local cells = { }
486                        local row   = mysql_fetch_row(_result_)
487                        for j=1,noffields do
488                            local s = row[j-1]
489                            local k = keys[j]
490                            if s == NULL then
491                                cells[k] = ""
492                            else
493                                cells[k] = ffi_tostring(s)
494                            end
495                        end
496                        data[i] = cells
497                    end
498                end
499            end
500            result:close()
501        end
502        --
503        if not id then
504            if connection then
505                connection:close()
506            end
507            if session then
508                session:close()
509            end
510        end
511        return false, data, keys
512    end
513
514    local function datafetched(specification,query,converter)
515        local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
516        if not callokay then
517            report_state("call error, retrying")
518            callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
519        elseif connectionerror then
520            report_state("error: %s, retrying",connectionerror)
521            callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
522        end
523        if not callokay then
524            report_state("persistent call error")
525        elseif connectionerror then
526            report_state("persistent error: %s",connectionerror)
527        end
528        return data or { }, keys or { }
529    end
530
531    executequery = function(specification)
532        if trace_sql then
533            report_state("executing library")
534        end
535        if not validspecification(specification) then
536            report_state("error in specification")
537            return
538        end
539        local query = dataprepared(specification)
540        if not query then
541            report_state("error in preparation")
542            return
543        end
544        local data, keys = datafetched(specification,query,specification.converter)
545        if not data then
546            report_state("error in fetching")
547            return
548        end
549        local one = data[1]
550        if one then
551            setmetatable(data,{ __index = one } )
552        end
553        return data, keys
554    end
555
556end
557
558local wraptemplate = [[
559----- mysql           = ffi.load(os.name == "windows" and "libmysql" or "libmysqlclient")
560local mysql           = ffi.load(os.name == "windows" and "libmysql" or "libmysql")
561
562local mysql_fetch_row = mysql.mysql_fetch_row
563local ffi_tostring    = ffi.string
564
565local converters      = utilities.sql.converters
566local deserialize     = utilities.sql.deserialize
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