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
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
49
50
51
52
53
54
55
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
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
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
144
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 |