1--- Test sqlite clib functionality.
2--
3-- @copyright Mason Larobina <mason.larobina@gmail.com>
4
5local assert = require "luassert"
6
7local T = {}
8
9T.test_module = function ()
10    assert.is_table(sqlite3)
11end
12
13T.test_open_db = function ()
14    local db = sqlite3{filename=":memory:"}
15    assert.is_equal("sqlite3", type(db))
16
17    -- Should error without constructor table
18    assert.has_error(function () sqlite3() end)
19
20    -- Should error without filename in constructor table
21    assert.has_error(function () sqlite3{} end)
22end
23
24T.test_sqlite3_exec = function ()
25    local ret
26    local db = sqlite3{filename=":memory:"}
27    ret = db:exec([[CREATE TABLE IF NOT EXISTS test (
28        id INTEGER PRIMARY KEY,
29        uri TEXT,
30        created FLOAT
31    )]])
32    assert.is_nil(ret)
33
34    assert.has_no.errors(function () db:exec(";") end)
35
36    ret = db:exec(";")
37    assert.is_nil(ret)
38
39    ret = db:exec([[SELECT * FROM test;]])
40    assert.is_table(ret)
41    assert.is_equal(0, #ret)
42
43    ret = db:exec([[INSERT INTO test VALUES(NULL, "google.com", 1234.45)]])
44    assert.is_nil(ret)
45
46    ret = db:exec([[SELECT * FROM test;]])
47    assert.is_table(ret)
48    assert.is_equal(1, #ret)
49
50    ret = ret[1]
51    assert.is_table(ret)
52    assert.is_equal("google.com", ret.uri)
53    assert.is_equal(1234.45, ret.created)
54
55    ret = db:exec([[INSERT INTO test VALUES(:id, :uri, :created);]],
56        { [":uri"] = "reddit.com", [":created"] = 1000 })
57
58    assert.is_nil(ret)
59
60    ret = db:exec([[SELECT * FROM test;]])
61    assert.is_table(ret)
62    assert.is_equal(2, #ret)
63
64    ret = ret[2]
65    assert.is_table(ret)
66    assert.is_equal("reddit.com", ret.uri)
67    assert.is_equal(1000, ret.created)
68
69--    for i, row in ipairs(ret) do
70--        for k,v in pairs(row) do
71--            print("row", i, k, v)
72--        end
73--    end
74end
75
76T.test_compile_statement = function ()
77    local db = sqlite3{filename=":memory:"}
78    local ret, insert, tail, select_all
79    ret, tail = db:exec([[CREATE TABLE IF NOT EXISTS test (
80        id INTEGER PRIMARY KEY,
81        uri TEXT,
82        created FLOAT
83    )]])
84    assert.is_nil(ret)
85    assert.is_nil(tail)
86
87    -- Compile some statements
88    insert, tail = db:compile([[INSERT INTO test VALUES(:id, :uri, :created);]])
89    assert.is_equal("sqlite3::statement", type(insert))
90    assert.is_nil(tail)
91
92    select_all, tail = db:compile([[SELECT * FROM test;]])
93    assert.is_equal("sqlite3::statement", type(select_all))
94    assert.is_nil(tail)
95
96    ret = insert:exec{ [":uri"] = "google.com", [":created"] = 1000 }
97    assert.is_nil(ret)
98    ret = insert:exec{ [":uri"] = "reddit.com", [":created"] = 12.34 }
99    assert.is_nil(ret)
100
101    ret = select_all:exec()
102    assert.is_table(ret)
103    assert.is_equal(2, #ret)
104
105    assert.is_table(ret[1])
106    assert.is_equal("google.com", ret[1].uri)
107    assert.is_equal(1000, ret[1].created)
108    assert.is_table(ret[2])
109    assert.is_equal("reddit.com", ret[2].uri)
110    assert.is_equal(12.34, ret[2].created)
111
112    -- Re-run last statement with same bound values
113    ret = insert:exec()
114    assert.is_nil(ret)
115
116    ret = select_all:exec()
117    assert.is_table(ret)
118    assert.is_equal(3, #ret)
119
120    assert.is_table(ret[3])
121    assert.is_equal("reddit.com", ret[3].uri)
122    assert.is_equal(12.34, ret[3].created)
123end
124
125return T
126
127-- vim: et:sw=4:ts=8:sts=4:tw=80
128