libs-imp-sqlite.lmt /size: 5881 b    last modification: 2023-12-21 09:52
1if not modules then modules = { } end modules ['libs-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
9-- c:/data/develop/tex-context/tex/texmf-win64/bin/lib/luametatex/lua/copies/sqlite/sqlite3.dll
10
11local libname = "sqlite"
12local libfile = "sqlite3"
13
14local sqlitelib = resolvers.libraries.validoptional(libname)
15
16if not sqlitelib then return end
17
18local function okay()
19    if resolvers.libraries.optionalloaded(libname,libfile) then
20        okay = function() return true end
21    else
22        okay = function() return false end
23    end
24    return okay()
25end
26
27local next, tonumber = next, tonumber
28local setmetatable = setmetatable
29local formatters = string.formatters
30
31local sql                = utilities.sql or require("util-sql")
32local report             = logs.reporter(libname)
33
34local trace_sql          = false  trackers.register("sql.trace",  function(v) trace_sql     = v end)
35local trace_queries      = false  trackers.register("sql.queries",function(v) trace_queries = v end)
36
37local sqlite_open        = sqlitelib.open
38local sqlite_close       = sqlitelib.close
39local sqlite_execute     = sqlitelib.execute
40local sqlite_getmessage  = sqlitelib.getmessage
41
42local helpers            = sql.helpers
43local methods            = sql.methods
44local validspecification = helpers.validspecification
45local preparetemplate    = helpers.preparetemplate
46local cache              = { }
47
48-- synchronous  journal_mode  locking_mode    1000 logger inserts
49--
50-- normal       normal        normal          6.8
51-- off          off           normal          0.1
52-- normal       off           normal          2.1
53-- normal       persist       normal          5.8
54-- normal       truncate      normal          4.2
55-- normal       truncate      exclusive       4.1
56
57local f_preamble = formatters[ [[
58ATTACH `%s` AS `%s` ;
59PRAGMA `%s`.synchronous = normal ;
60]] ]
61
62local function execute(specification)
63    if okay() then
64        if trace_sql then
65            report("executing sqlite")
66        end
67        if not validspecification(specification) then
68            report("error in specification")
69        end
70        local query = preparetemplate(specification)
71        if not query then
72            report("error in preparation")
73            return
74        end
75        local base = specification.database -- or specification.presets and specification.presets.database
76        if not base then
77            report("no database specified")
78            return
79        end
80        local filename = file.addsuffix(base,"db")
81        local result   = { }
82        local keys     = { }
83        local id       = specification.id
84        local db       = nil
85        local preamble = nil
86        if id then
87            local session = cache[id]
88            if session then
89                db = session.db
90            else
91                db       = sqlite_open(filename)
92                preamble = f_preamble(filename,base,base)
93                if not db then
94                    report("no session database specified")
95                else
96                    cache[id] = {
97                        name = filename,
98                        db   = db,
99                    }
100                end
101            end
102        else
103            db       = sqlite_open(filename)
104            preamble = f_preamble(filename,base,base)
105        end
106        if not db then
107            report("no database opened")
108        else
109            local converter = specification.converter
110            local nofrows   = 0
111            local callback  = nil
112            if preamble then
113                query = preamble .. query -- only needed in open
114            end
115            if converter then
116                local convert = converter.sqlite
117                callback = function(nofcolumns,values,fields)
118                    nofrows = nofrows + 1
119                    result[nofrows] = convert(values)
120                end
121            else
122                callback = function(nofcolumns,values,fields)
123                    local column = { }
124                    for i=1,nofcolumns do
125                        local field
126                        if fields then
127                            field = fields[i]
128                            keys[i] = field
129                        else
130                            field = keys[i]
131                        end
132                        if field then
133                            column[field] = values[i]
134                        end
135                    end
136                    nofrows  = nofrows + 1
137                    result[nofrows] = column
138                end
139            end
140            local okay = sqlite_execute(db,query,callback)
141            if not okay then
142                report("error: %s",sqlite_getmessage(db))
143         -- elseif converter then
144         --     result = converter.sqlite(result)
145            end
146        end
147        if db and not id then
148            sqlite_close(db)
149        end
150        return result, keys
151    else
152        report("error: ","no library loaded")
153    end
154end
155
156local wraptemplate = [[
157local converters    = utilities.sql.converters
158local deserialize   = utilities.sql.deserialize
159local fromjson      = utilities.sql.fromjson
160
161local tostring      = tostring
162local tonumber      = tonumber
163local booleanstring = string.booleanstring
164
165%s
166
167return function(cells)
168    -- %s (not needed)
169    -- %s (not needed)
170    return {
171        %s
172    }
173end
174]]
175
176local celltemplate = "cells[%s]"
177
178methods.sqlite = {
179    execute      = execute,
180    usesfiles    = false,
181    wraptemplate = wraptemplate,
182    celltemplate = celltemplate,
183}
184
185package.loaded["util-sql-imp-sqlite"] = methods.sqlite
186package.loaded[libname]               = methods.sqlite
187
188return methods.sqlite
189