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
|
local sqlite3 = require("lsqlite3")
local width = 78
local function line(pref, suff)
pref = pref or ''
suff = suff or ''
local len = width - 2 - string.len(pref) - string.len(suff)
print(pref .. string.rep('-', len) .. suff)
end
local db, vm
local assert_, assert = assert, function (test)
if (not test) then
error(db:errmsg(), 2)
end
end
line(sqlite3.version())
os.remove('test.db')
db = sqlite3.open('test.db')
line(nil, 'db:exec')
db:exec('CREATE TABLE t(a, b)')
line(nil, 'prepare')
vm = db:prepare('insert into t values(?, :bork)')
assert(vm, db:errmsg())
assert(vm:bind_parameter_count() == 2)
assert(vm:bind_values(2, 4) == sqlite3.OK)
assert(vm:step() == sqlite3.DONE)
assert(vm:reset() == sqlite3.OK)
assert(vm:bind_names{ 'pork', bork = 'nono' } == sqlite3.OK)
assert(vm:step() == sqlite3.DONE)
assert(vm:reset() == sqlite3.OK)
assert(vm:bind_names{ bork = 'sisi' } == sqlite3.OK)
assert(vm:step() == sqlite3.DONE)
assert(vm:reset() == sqlite3.OK)
assert(vm:bind_names{ 1 } == sqlite3.OK)
assert(vm:step() == sqlite3.DONE)
assert(vm:finalize() == sqlite3.OK)
line("select * from t", 'db:exec')
assert(db:exec('select * from t', function (ud, ncols, values, names)
--table.setn(values, 2)
print(table.unpack(values))
return sqlite3.OK
end) == sqlite3.OK)
line("select * from t", 'db:prepare')
vm = db:prepare('select * from t')
assert(vm, db:errmsg())
print(vm:get_unames())
while (vm:step() == sqlite3.ROW) do
print(vm:get_uvalues())
end
assert(vm:finalize() == sqlite3.OK)
line('udf', 'scalar')
local function do_query(sql)
local r
local vm = db:prepare(sql)
assert(vm, db:errmsg())
print('====================================')
print(vm:get_unames())
print('------------------------------------')
r = vm:step()
while (r == sqlite3.ROW) do
print(vm:get_uvalues())
r = vm:step()
end
assert(r == sqlite3.DONE)
assert(vm:finalize() == sqlite3.OK)
print('====================================')
end
local function udf1_scalar(ctx, v)
local ud = ctx:user_data()
ud.r = (ud.r or '') .. tostring(v)
ctx:result_text(ud.r)
end
db:create_function('udf1', 1, udf1_scalar, { })
do_query('select udf1(a) from t')
line('udf', 'aggregate')
local function udf2_aggregate(ctx, ...)
local ud = ctx:get_aggregate_data()
if (not ud) then
ud = {}
ctx:set_aggregate_data(ud)
end
ud.r = (ud.r or 0) + 2
end
local function udf2_aggregate_finalize(ctx, v)
local ud = ctx:get_aggregate_data()
ctx:result_number(ud and ud.r or 0)
end
db:create_aggregate('udf2', 1, udf2_aggregate, udf2_aggregate_finalize, { })
do_query('select udf2(a) from t')
if (true) then
line(nil, '100 insert exec')
db:exec('delete from t')
local t = os.time()
for i = 1, 100 do
db:exec('insert into t values('..i..', '..(i * 2 * -1^i)..')')
end
print('elapsed: '..(os.time() - t))
do_query('select count(*) from t')
line(nil, '100000 insert exec T')
db:exec('delete from t')
local t = os.time()
db:exec('begin')
for i = 1, 100000 do
db:exec('insert into t values('..i..', '..(i * 2 * -1^i)..')')
end
db:exec('commit')
print('elapsed: '..(os.time() - t))
do_query('select count(*) from t')
line(nil, '100000 insert prepare/bind T')
db:exec('delete from t')
local t = os.time()
local vm = db:prepare('insert into t values(?, ?)')
db:exec('begin')
for i = 1, 100000 do
vm:bind_values(i, i * 2 * -1^i)
vm:step()
vm:reset()
end
vm:finalize()
db:exec('commit')
print('elapsed: '..(os.time() - t))
do_query('select count(*) from t')
end
line(nil, "db:close")
assert(db:close() == sqlite3.OK)
line(sqlite3.version())
|
