summaryrefslogtreecommitdiffstatshomepage
path: root/3rdparty/lsqlite3/test/test.lua
blob: e5e3bfa35f26cbf0e987a85c0e909e54ee0ac8ba (plain) (blame)
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())