path: root/tex/context/base/mkiv/util-sql-imp-ffi.lua

if not modules then modules = { } end modules ['util-sql-imp-ffi'] = {
    version   = 1.001,
    comment   = "companion to util-sql.lua",
    author    = "Hans Hagen, PRAGMA-ADE, Hasselt NL",
    copyright = "PRAGMA ADE / ConTeXt Development Team",
    license   = "see context related readme files"
}

-- I looked at luajit-mysql to see how the ffi mapping was done but it didn't work
-- out that well (at least not on windows) but I got the picture. As I have somewhat
-- different demands I simplified.

local tonumber = tonumber
local concat = table.concat
local format, byte = string.format, string.byte
local lpegmatch = lpeg.match
local setmetatable, type = setmetatable, type
local sleep = os.sleep
local formatters = string.formatters

local trace_sql     = false  trackers.register("sql.trace",  function(v) trace_sql     = v end)
local trace_queries = false  trackers.register("sql.queries",function(v) trace_queries = v end)
local report_state  = logs.reporter("sql","ffi")

if not utilities.sql then
    require("util-sql")
end

ffi.cdef [[

    /*
        This is as lean and mean as possible. After all we just need a connection and
        a query. The rest is handled already in the Lua code elsewhere.
    */

    void free(void*ptr);
    void * malloc(size_t size);

    typedef void MYSQL_instance;
    typedef void MYSQL_result;
    typedef char **MYSQL_row;
    typedef unsigned int MYSQL_offset;

    typedef struct st_mysql_field {
        char *name;
        char *org_name;
        char *table;
        char *org_table;
        char *db;
        char *catalog;
        char *def;
        unsigned long length;
        unsigned long max_length;
        unsigned int name_length;
        unsigned int org_name_length;
        unsigned int table_length;
        unsigned int org_table_length;
        unsigned int db_length;
        unsigned int catalog_length;
        unsigned int def_length;
        unsigned int flags;
        unsigned int decimals;
        unsigned int charsetnr;
        int type;
        void *extension;
    } MYSQL_field;

    MYSQL_instance * mysql_init (
        MYSQL_instance *mysql
    );

    MYSQL_instance * mysql_real_connect (
        MYSQL_instance *mysql,
        const char *host,
        const char *user,
        const char *passwd,
        const char *db,
        unsigned int port,
        const char *unix_socket,
        unsigned long clientflag
    );

    unsigned int mysql_errno (
        MYSQL_instance *mysql
    );

    const char *mysql_error (
        MYSQL_instance *mysql
    );

    /* int mysql_query (
        MYSQL_instance *mysql,
        const char *q
    ); */

    int mysql_real_query (
        MYSQL_instance *mysql,
        const char *q,
        unsigned long length
    );

    MYSQL_result * mysql_store_result (
        MYSQL_instance *mysql
    );

    void mysql_free_result (
        MYSQL_result *result
    );

    unsigned long long mysql_num_rows (
        MYSQL_result *res
    );

    MYSQL_row mysql_fetch_row (
        MYSQL_result *result
    );

    unsigned int mysql_affected_rows (
        MYSQL_instance *mysql
    );

    unsigned int mysql_field_count (
        MYSQL_instance *mysql
    );

    unsigned int mysql_num_fields (
        MYSQL_result *res
    );

    /* MYSQL_field *mysql_fetch_field (
        MYSQL_result *result
    ); */

    MYSQL_field * mysql_fetch_fields (
        MYSQL_result *res
    );

    MYSQL_offset mysql_field_seek(
        MYSQL_result *result,
        MYSQL_offset offset
    );

    void mysql_close(
        MYSQL_instance *sock
    );

    /* unsigned long * mysql_fetch_lengths(
        MYSQL_result *result
    ); */

]]
-- trackers.enable("*lib*")
local sql                    = utilities.sql
----- mysql                  = ffi.load(os.name == "windows" and "libmysql" or "libmysqlclient")
----- mysql                  = ffilib(os.name == "windows" and "libmysql" or "libmysqlclient")
local mysql                  = ffilib(os.name == "windows" and "libmysql" or "libmysql")

if not mysql then
    report_state("unable to load library")
end

local nofretries             = 5
local retrydelay             = 1

local cache                  = { }
local helpers                = sql.helpers
local methods                = sql.methods
local validspecification     = helpers.validspecification
local querysplitter          = helpers.querysplitter
local dataprepared           = helpers.preparetemplate
local serialize              = sql.serialize
local deserialize            = sql.deserialize

local mysql_open_session     = mysql.mysql_init

local mysql_open_connection  = mysql.mysql_real_connect
local mysql_execute_query    = mysql.mysql_real_query
local mysql_close_connection = mysql.mysql_close

local mysql_affected_rows    = mysql.mysql_affected_rows
local mysql_field_count      = mysql.mysql_field_count
local mysql_field_seek       = mysql.mysql_field_seek
local mysql_num_fields       = mysql.mysql_num_fields
local mysql_fetch_fields     = mysql.mysql_fetch_fields
----- mysql_fetch_field      = mysql.mysql_fetch_field
local mysql_num_rows         = mysql.mysql_num_rows
local mysql_fetch_row        = mysql.mysql_fetch_row
----- mysql_fetch_lengths    = mysql.mysql_fetch_lengths
local mysql_init             = mysql.mysql_init
local mysql_store_result     = mysql.mysql_store_result
local mysql_free_result      = mysql.mysql_free_result

local mysql_error_number     = mysql.mysql_errno
local mysql_error_message    = mysql.mysql_error

local NULL                   = ffi.cast("MYSQL_result *",0)

local ffi_tostring           = ffi.string
local ffi_gc                 = ffi.gc

local instance               = mysql.mysql_init(nil)

local mysql_constant_false   = false
local mysql_constant_true    = true

local wrapresult  do

    local function collect(t)
        local result = t._result_
        if result then
            ffi_gc(result,mysql_free_result)
        end
    end

    local function finish(t)
        local result = t._result_
        if result then
            t._result_ = nil
            ffi_gc(result,mysql_free_result)
        end
    end

    local function getcoldata(t)
        local result = t._result_
        local nofrows   = t.nofrows
        local noffields = t.noffields
        local names     = { }
        local types     = { }
        local fields    = mysql_fetch_fields(result)
        for i=1,noffields do
            local field = fields[i-1]
            names[i] = ffi_tostring(field.name)
            types[i] = tonumber(field.type) -- todo
        end
        t.names = names
        t.types = types
    end

    local function getcolnames(t)
        local names = t.names
        if names then
            return names
        end
        getcoldata(t)
        return t.names
    end

    local function getcoltypes(t)
        local types = t.types
        if types then
            return types
        end
        getcoldata(t)
        return t.types
    end

    local function numrows(t)
        return t.nofrows
    end

    -- local function fetch(t)
    --     local
    --     local row    = mysql_fetch_row(result)
    --     local result = { }
    --     for i=1,t.noffields do
    --         result[i] = ffi_tostring(row[i-1])
    --     end
    --     return unpack(result)
    -- end

    local mt = {
        __gc    = collect,
        __index = {
            _result_    = nil,
            close       = finish,
            numrows     = numrows,
            getcolnames = getcolnames,
            getcoltypes = getcoltypes,
         -- fetch       = fetch, -- not efficient
        }
    }

    wrapresult = function(connection)
        local result = mysql_store_result(connection)
        if result ~= NULL then
            mysql_field_seek(result,0)
            local t = {
                _result_  = result,
                nofrows   = tonumber(mysql_num_rows  (result) or 0) or 0,
                noffields = tonumber(mysql_num_fields(result) or 0) or 0,
            }
            return setmetatable(t,mt)
        elseif tonumber(mysql_field_count(connection) or 0) or 0 > 0 then
            return tonumber(mysql_affected_rows(connection))
        end
    end

end

local initializesession  do

    -- timeouts = [ connect_timeout |wait_timeout | interactive_timeout ]

    local timeout -- = 3600 -- to be tested

    -- connection

    local function close(t)
        -- just a struct ?
    end

    local function execute(t,query)
        if query and query ~= "" then
            local connection = t._connection_
            local result = mysql_execute_query(connection,query,#query)
            if result == 0 then
                return wrapresult(connection)
            else
             -- mysql_error_number(connection)
                return false, ffi_tostring(mysql_error_message(connection))
            end
        end
        return false
    end

    local mt = {
        __index = {
            close   = close,
            execute = execute,
        }
    }

    -- session

    local function open(t,database,username,password,host,port)
        local connection = mysql_open_connection(
            t._session_,
            host or "localhost",
            username or "",
            password or "",
            database or "",
            port or 0,
            NULL,
            0
        )
        if connection ~= NULL then
            if timeout then
                execute(connection,formatters["SET SESSION connect_timeout=%s ;"](timeout))
            end
            local t = {
                _connection_ = connection,
            }
            return setmetatable(t,mt)
        end
    end

    local function message(t)
        return mysql_error_message(t._session_)
    end

    local function close(t)
        local connection = t._connection_
        if connection and connection ~= NULL then
            ffi_gc(connection, mysql_close)
            t.connection = nil
        end
    end

    local mt = {
        __index = {
            connect = open,
            close   = close,
            message = message,
        },
    }

    initializesession = function()
        local session = {
            _session_ = mysql_open_session(instance) -- maybe share, single thread anyway
        }
        return setmetatable(session,mt)
    end

end

local executequery  do

    local function connect(session,specification)
        return session:connect(
            specification.database or "",
            specification.username or "",
            specification.password or "",
            specification.host     or "",
            specification.port
        )
    end

    local function fetched(specification,query,converter)
        if not query or query == "" then
            report_state("no valid query")
            return false
        end
        local id = specification.id
        local session, connection
        if id then
            local c = cache[id]
            if c then
                session    = c.session
                connection = c.connection
            end
            if not connection then
                session = initializesession()
                if not session then
                    return formatters["no session for %a"](id)
                end
                connection = connect(session,specification)
                if not connection then
                    return formatters["no connection for %a"](id)
                end
                cache[id] = { session = session, connection = connection }
            end
        else
            session = initializesession()
            if not session then
                return "no session"
            end
            connection = connect(session,specification)
            if not connection then
                return "no connection"
            end
        end
        if not connection then
            report_state("error in connection: %s@%s to %s:%s",
                specification.database or "no database",
                specification.username or "no username",
                specification.host     or "no host",
                specification.port     or "no port"
            )
            return "no connection"
        end
        query = lpegmatch(querysplitter,query)
        local result, okay
        for i=1,#query do
            local q = query[i]
            local r, m = connection:execute(q)
            if m then
                report_state("error in query to host %a: %s",specification.host,string.collapsespaces(q or "?"))
                if m then
                    report_state("message: %s",m)
                end
            end
            local t = type(r)
            if t == "table" then
                result = r
                okay = true
            elseif t == "number" then
                okay = true
            end
        end
        if not okay then -- can go
            -- why do we close a session
            if connection then
                connection:close()
            end
            if session then
                session:close()
            end
            if id then
                cache[id] = nil
            end
            return "execution error"
        end
        local data, keys
        if result then
            if converter then
                data = converter.ffi(result)
            else
                local _result_  = result._result_
                local noffields = result.noffields
                local nofrows   = result.nofrows
                keys = result:getcolnames()
                data = { }
                if noffields > 0 and nofrows > 0 then
                    for i=1,nofrows do
                        local cells = { }
                        local row   = mysql_fetch_row(_result_)
                        for j=1,noffields do
                            local s = row[j-1]
                            local k = keys[j]
                            if s == NULL then
                                cells[k] = ""
                            else
                                cells[k] = ffi_tostring(s)
                            end
                        end
                        data[i] = cells
                    end
                end
            end
            result:close()
        end
        --
        if not id then
            if connection then
                connection:close()
            end
            if session then
                session:close()
            end
        end
        return false, data, keys
    end

    local function datafetched(specification,query,converter)
        local callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
        if not callokay then
            report_state("call error, retrying")
            callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
        elseif connectionerror then
            report_state("error: %s, retrying",connectionerror)
            callokay, connectionerror, data, keys = pcall(fetched,specification,query,converter)
        end
        if not callokay then
            report_state("persistent call error")
        elseif connectionerror then
            report_state("persistent error: %s",connectionerror)
        end
        return data or { }, keys or { }
    end

    executequery = function(specification)
        if trace_sql then
            report_state("executing library")
        end
        if not validspecification(specification) then
            report_state("error in specification")
            return
        end
        local query = dataprepared(specification)
        if not query then
            report_state("error in preparation")
            return
        end
        local data, keys = datafetched(specification,query,specification.converter)
        if not data then
            report_state("error in fetching")
            return
        end
        local one = data[1]
        if one then
            setmetatable(data,{ __index = one } )
        end
        return data, keys
    end

end

local wraptemplate = [[
----- mysql           = ffi.load(os.name == "windows" and "libmysql" or "libmysqlclient")
local mysql           = ffi.load(os.name == "windows" and "libmysql" or "libmysql")

local mysql_fetch_row = mysql.mysql_fetch_row
local ffi_tostring    = ffi.string

local converters      = utilities.sql.converters
local deserialize     = utilities.sql.deserialize

local tostring        = tostring
local tonumber        = tonumber
local booleanstring   = string.booleanstring

local NULL            = ffi.cast("MYSQL_result *",0)

%s

return function(result)
    if not result then
        return { }
    end
    local nofrows = result.nofrows
    if nofrows == 0 then
        return { }
    end
    local noffields = result.noffields
    local target    = { } -- no %s needed here
    local _result_  = result._result_
    -- we can share cells
    for i=1,nofrows do
        local cells = { }
        local row   = mysql_fetch_row(_result_)
        for j=1,noffields do
            local s = row[j-1]
            if s == NULL then
                cells[j] = ""
            else
                cells[j] = ffi_tostring(s)
            end
        end
        target[%s] = {
            %s
        }
    end
    result:close()
    return target
end
]]

local celltemplate = "cells[%s]"

methods.ffi = {
    runner       = function() end,    -- never called
    execute      = executequery,
    initialize   = initializesession, -- returns session
    usesfiles    = false,
    wraptemplate = wraptemplate,
    celltemplate = celltemplate,
}