1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
|
if not modules then modules = { } end modules ['util-sql-imp-sqlite'] = {
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"
}
local next = next
local sql = require("util-sql")
----- sql = utilities.sql
local sqlite = require("swiglib.sqlite.core")
local swighelpers = require("swiglib.helpers.core")
-- sql.sqlite = sqlite -- maybe in the module itself
-- inspect(table.sortedkeys(sqlite))
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","sqlite")
local helpers = sql.helpers
local methods = sql.methods
local validspecification = helpers.validspecification
local preparetemplate = helpers.preparetemplate
local splitdata = helpers.splitdata
local serialize = sql.serialize
local deserialize = sql.deserialize
local getserver = sql.getserver
local setmetatable = setmetatable
local formatters = string.formatters
local get_list_item = sqlite.char_p_array_getitem
local is_okay = sqlite.SQLITE_OK
local execute_query = sqlite.sqlite3_exec_lua_callback
local error_message = sqlite.sqlite3_errmsg
local new_db = sqlite.new_sqlite3_p_array
local open_db = sqlite.sqlite3_open
local get_db = sqlite.sqlite3_p_array_getitem
local close_db = sqlite.sqlite3_close
local dispose_db = sqlite.delete_sqlite3_p_array
local cache = { }
setmetatable(cache, {
__gc = function(t)
for k, v in next, t do
if trace_sql then
report_state("closing session %a",k)
end
close_db(v.dbh)
dispose_db(v.db)
end
end
})
-- synchronous journal_mode locking_mode 1000 logger inserts
--
-- normal normal normal 6.8
-- off off normal 0.1
-- normal off normal 2.1
-- normal persist normal 5.8
-- normal truncate normal 4.2
-- normal truncate exclusive 4.1
local f_preamble = formatters[ [[
ATTACH `%s` AS `%s` ;
PRAGMA `%s`.synchronous = normal ;
PRAGMA journal_mode = truncate ;
]] ]
local function execute(specification)
if trace_sql then
report_state("executing sqlite")
end
if not validspecification(specification) then
report_state("error in specification")
end
local query = preparetemplate(specification)
if not query then
report_state("error in preparation")
return
end
local base = specification.database -- or specification.presets and specification.presets.database
if not base then
report_state("no database specified")
return
end
local filename = file.addsuffix(base,"db")
local result = { }
local keys = { }
local id = specification.id
local db = nil
local dbh = nil
local okay = false
local preamble = nil
if id then
local session = cache[id]
if session then
dbh = session.dbh
okay = is_okay
else
db = new_db(1)
okay = open_db(filename,db)
dbh = get_db(db,0)
preamble = f_preamble(filename,base,base)
if okay ~= is_okay then
report_state("no session database specified")
else
cache[id] = {
name = filename,
db = db,
dbh = dbh,
}
end
end
else
db = new_db(1)
okay = open_db(filename,db)
dbh = get_db(db,0)
preamble = f_preamble(filename,base,base)
end
if okay ~= is_okay then
report_state("no database opened")
else
local converter = specification.converter
local keysdone = false
local nofrows = 0
local callback = nil
if preamble then
query = preamble .. query -- only needed in open
end
if converter then
converter = converter.sqlite
callback = function(data,nofcolumns,values,fields)
local column = { }
for i=0,nofcolumns-1 do
column[i+1] = get_list_item(values,i)
end
nofrows = nofrows + 1
result[nofrows] = converter(column)
return is_okay
end
--
-- callback = converter.sqlite
else
callback = function(data,nofcolumns,values,fields)
local column = { }
for i=0,nofcolumns-1 do
local field
if keysdone then
field = keys[i+1]
else
field = get_list_item(fields,i)
keys[i+1] = field
end
column[field] = get_list_item(values,i)
end
nofrows = nofrows + 1
keysdone = true
result[nofrows] = column
return is_okay
end
end
local okay = execute_query(dbh,query,callback,nil,nil)
if okay ~= is_okay then
report_state("error: %s",error_message(dbh))
-- elseif converter then
-- result = converter.sqlite(result)
end
end
if not id then
close_db(dbh)
dispose_db(db)
end
return result, keys
end
local wraptemplate = [[
local converters = utilities.sql.converters
local deserialize = utilities.sql.deserialize
local tostring = tostring
local tonumber = tonumber
local booleanstring = string.booleanstring
%s
return function(cells)
-- %s (not needed)
-- %s (not needed)
return {
%s
}
end
]]
local celltemplate = "cells[%s]"
-- todo: how to deal with result ... pass via temp global .. bah .. or
-- also pass the execute here ... not now
--
-- local wraptemplate = [[
-- local converters = utilities.sql.converters
-- local deserialize = utilities.sql.deserialize
--
-- local tostring = tostring
-- local tonumber = tonumber
-- local booleanstring = string.booleanstring
--
-- local get_list_item = utilities.sql.sqlite.char_p_array_getitem
-- local is_okay = utilities.sql.sqlite.SQLITE_OK
--
-- %s
--
-- return function(data,nofcolumns,values,fields)
-- -- no %s (data) needed
-- -- no %s (i) needed
-- local cells = { }
-- for i=0,nofcolumns-1 do
-- cells[i+1] = get_list_item(values,i)
-- end
-- result[#result+1] = { %s }
-- return is_okay
-- end
-- ]]
methods.sqlite = {
execute = execute,
usesfiles = false,
wraptemplate = wraptemplate,
celltemplate = celltemplate,
}
|
