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