Source Browser ?

1if not modules then modules = { } end modules ['util-sql-imp-sqlite'] = {
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
9local next, tonumber = next, tonumber
10
11local sql                = utilities.sql or require("util-sql")
12
13local trace_sql          = false  trackers.register("sql.trace",  function(v) trace_sql     = v end)
14local trace_queries      = false  trackers.register("sql.queries",function(v) trace_queries = v end)
15local report_state       = logs.reporter("sql","sqlite")
16
17local helpers            = sql.helpers
18local methods            = sql.methods
19local validspecification = helpers.validspecification
20local preparetemplate    = helpers.preparetemplate
21
22local setmetatable       = setmetatable
23local formatters         = string.formatters
24
25local ffi = require("ffi")
26
27ffi.cdef [[
28
29    typedef struct sqlite3 sqlite3;
30
31    int sqlite3_initialize (
32        void
33    ) ;
34
35    int sqlite3_open (
36        const char *filename,
37        sqlite3 **ppDb
38    ) ;
39
40    int sqlite3_close (
41        sqlite3 *
42    ) ;
43
44    int sqlite3_exec (
45        sqlite3*,
46        const char *sql,
47        int (*callback)(void*,int,char**,char**),
48        void *,
49        char **errmsg
50    ) ;
51
52    const char *sqlite3_errmsg (
53        sqlite3*
54    );
55
56]]
57
58local ffi_tostring = ffi.string
59
60----- sqlite = ffi.load("sqlite3")
61local sqlite = ffilib("sqlite3")
62
63sqlite.sqlite3_initialize();
64
65local c_errmsg = sqlite.sqlite3_errmsg
66local c_open   = sqlite.sqlite3_open
67local c_close  = sqlite.sqlite3_close
68local c_exec   = sqlite.sqlite3_exec
69
70local is_okay       = 0
71local open_db       = c_open
72local close_db      = c_close
73local execute_query = c_exec
74
75local function error_message(db)
76    return ffi_tostring(c_errmsg(db))
77end
78
79local function new_db(n)
80    return ffi.new("sqlite3*["..n.."]")
81end
82
83local function dispose_db(db)
84end
85
86local function get_db(db,n)
87    return db[n]
88end
89
90-- local function execute_query(dbh,query,callback)
91--     local c = ffi.cast("int (*callback)(void*,int,char**,char**)",callback)
92--     c_exec(dbh,query,c,nil,nil)
93--     c:free()
94-- end
95
96local cache = { }
97
98setmetatable(cache, {
99    __gc = function(t)
100        for k, v in next, t do
101            if trace_sql then
102                report_state("closing session %a",k)
103            end
104            close_db(v.dbh)
105            dispose_db(v.db)
106        end
107    end
108})
109
110-- synchronous  journal_mode  locking_mode    1000 logger inserts
111--
112-- normal       normal        normal          6.8
113-- off          off           normal          0.1
114-- normal       off           normal          2.1
115-- normal       persist       normal          5.8
116-- normal       truncate      normal          4.2
117-- normal       truncate      exclusive       4.1
118
119local f_preamble = formatters[ [[
120ATTACH `%s` AS `%s` ;
121PRAGMA `%s`.synchronous = normal ;
122]] ]
123
124local function execute(specification)
125    if trace_sql then
126        report_state("executing sqlite")
127    end
128    if not validspecification(specification) then
129        report_state("error in specification")
130    end
131    local query = preparetemplate(specification)
132    if not query then
133        report_state("error in preparation")
134        return
135    end
136    local base = specification.database -- or specification.presets and specification.presets.database
137    if not base then
138        report_state("no database specified")
139        return
140    end
141    local filename = file.addsuffix(base,"db")
142    local result   = { }
143    local keys     = { }
144    local id       = specification.id
145    local db       = nil
146    local dbh      = nil
147    local okay     = false
148    local preamble = nil
149    if id then
150        local session = cache[id]
151        if session then
152            dbh  = session.dbh
153            okay = is_okay
154        else
155            db       = new_db(1)
156            okay     = open_db(filename,db)
157            dbh      = get_db(db,0)
158            preamble = f_preamble(filename,base,base)
159            if okay ~= is_okay then
160                report_state("no session database specified")
161            else
162                cache[id] = {
163                    name = filename,
164                    db   = db,
165                    dbh  = dbh,
166                }
167            end
168        end
169    else
170        db       = new_db(1)
171        okay     = open_db(filename,db)
172        dbh      = get_db(db,0)
173        preamble = f_preamble(filename,base,base)
174    end
175    if okay ~= is_okay then
176        report_state("no database opened")
177    else
178        local converter = specification.converter
179        local keysdone  = false
180        local nofrows   = 0
181        local callback  = nil
182        if preamble then
183            query = preamble .. query -- only needed in open
184        end
185        if converter then
186            local convert = converter.sqlite
187            local column  = { }
188            callback = function(data,nofcolumns,values,fields)
189                for i=1,nofcolumns do
190                 -- column[i] = get_list_item(values,i-1)
191                    column[i] = ffi_tostring(values[i-1])
192                end
193                nofrows = nofrows + 1
194                result[nofrows] = convert(column)
195                return is_okay
196            end
197        else
198            local column = { }
199            callback = function(data,nofcolumns,values,fields)
200                for i=1,nofcolumns do
201                    local field
202                    if keysdone then
203                        field = keys[i]
204                    else
205                     -- field = get_list_item(fields,i)
206                        field = ffi_tostring(fields[i-1])
207                        keys[i+1] = field
208                    end
209                    if field then
210                     -- column[field] = get_list_item(values,i)
211                        column[field] = ffi_tostring(values[i-1])
212                    end
213                end
214                nofrows  = nofrows + 1
215                keysdone = true
216                result[nofrows] = column
217                return is_okay
218            end
219        end
220        local okay = execute_query(dbh,query,callback,nil,nil)
221        if okay ~= is_okay then
222            report_state("error: %s",error_message(dbh))
223     -- elseif converter then
224     --     result = converter.sqlite(result)
225        end
226    end
227    if not id then
228        close_db(dbh)
229        dispose_db(db)
230    end
231    return result, keys
232end
233
234local wraptemplate = [[
235local converters    = utilities.sql.converters
236local deserialize   = utilities.sql.deserialize
237local fromjson      = utilities.sql.fromjson
238
239local tostring      = tostring
240local tonumber      = tonumber
241local booleanstring = string.booleanstring
242
243%s
244
245return function(cells)
246    -- %s (not needed)
247    -- %s (not needed)
248    return {
249        %s
250    }
251end
252]]
253
254local celltemplate = "cells[%s]"
255
256methods.sqlite = {
257    execute      = execute,
258    usesfiles    = false,
259    wraptemplate = wraptemplate,
260    celltemplate = celltemplate,
261}
262