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