diff options
Diffstat (limited to '3rdparty/lsqlite3/test/tests-sqlite3.lua')
-rw-r--r-- | 3rdparty/lsqlite3/test/tests-sqlite3.lua | 1208 |
1 files changed, 1208 insertions, 0 deletions
diff --git a/3rdparty/lsqlite3/test/tests-sqlite3.lua b/3rdparty/lsqlite3/test/tests-sqlite3.lua new file mode 100644 index 00000000000..4a276b0bc29 --- /dev/null +++ b/3rdparty/lsqlite3/test/tests-sqlite3.lua @@ -0,0 +1,1208 @@ + +--[[-------------------------------------------------------------------------- + + Author: Michael Roth <mroth@nessie.de> + + Copyright (c) 2004, 2005 Michael Roth <mroth@nessie.de> + + Permission is hereby granted, free of charge, to any person + obtaining a copy of this software and associated documentation + files (the "Software"), to deal in the Software without restriction, + including without limitation the rights to use, copy, modify, merge, + publish, distribute, sublicense, and/or sell copies of the Software, + and to permit persons to whom the Software is furnished to do so, + subject to the following conditions: + + The above copyright notice and this permission notice shall be + included in all copies or substantial portions of the Software. + + THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, + EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF + MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. + IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY + CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, + TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE + SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. + +--]]-------------------------------------------------------------------------- + +-- extended for LuaSQLite3, and for Lua 5.2 (using lunitx) +-- Copyright (c) 2005-13 Doug Currie +-- Same license as above + +local sqlite3 = require(arg[1]) -- "lsqlite3complete" or "lsqlite3" + +local os = os + +--local lunit = require "lunitx" +local lunit = require "lunit" + +local tests_sqlite3 + +if _VERSION >= 'Lua 5.2' then + + tests_sqlite3 = lunit.module('tests-sqlite3','seeall') + _ENV = tests_sqlite3 + +else + + module('tests_sqlite3', lunit.testcase, package.seeall) + tests_sqlite3 = _M + +end + +-- compat + +function lunit_wrap (name, fcn) + tests_sqlite3['test_o_'..name] = fcn +end + +function lunit_TestCase (name) + return lunit.module(name,'seeall') +end + +------------------------------- +-- Print library versions -- +------------------------------- + +print ("SQLite v", sqlite3.version()) +print ("lsqlite v", sqlite3.lversion()) + +------------------------------- +-- Basic open and close test -- +------------------------------- + +lunit_wrap("open_memory", function() + local db = assert_userdata( sqlite3.open_memory() ) + assert( db:close() ) +end) + +lunit_wrap("open", function() + local filename = "/tmp/__lua-sqlite3-20040906135849." .. os.time() + local db = assert_userdata( sqlite3.open(filename) ) + assert( db:close() ) + os.remove(filename) +end) + +------------------------------------- +-- Presence of db member functions -- +------------------------------------- + +local db_funcs = lunit_TestCase("Database Member Functions") + +function db_funcs.setup() + db_funcs.db = assert( sqlite3.open_memory() ) +end + +function db_funcs.teardown() + assert( db_funcs.db:close() ) +end + +function db_funcs.test() + local db = db_funcs.db + assert_function( db.close ) + assert_function( db.exec ) +--e assert_function( db.irows ) + assert_function( db.rows ) +--e assert_function( db.cols ) +--e assert_function( db.first_irow ) +--e assert_function( db.first_row ) +--e assert_function( db.first_cols ) + assert_function( db.prepare ) + assert_function( db.interrupt ) + assert_function( db.last_insert_rowid ) + assert_function( db.changes ) + assert_function( db.total_changes ) +end + + +--------------------------------------- +-- Presence of stmt member functions -- +--------------------------------------- + +local stmt_funcs = lunit_TestCase("Statement Member Functions") + +function stmt_funcs.setup() + stmt_funcs.db = assert( sqlite3.open_memory() ) + stmt_funcs.stmt = assert( stmt_funcs.db:prepare("CREATE TABLE test (id, content)") ) +end + +function stmt_funcs.teardown() +--e- assert( stmt_funcs.stmt:close() ) + assert( stmt_funcs.stmt:finalize() ) --e+ + assert( stmt_funcs.db:close() ) +end + +function stmt_funcs.test() + local stmt = stmt_funcs.stmt +--e assert_function( stmt.close ) + assert_function( stmt.reset ) +--e assert_function( stmt.exec ) + assert_function( stmt.bind ) +--e assert_function( stmt.irows ) +--e assert_function( stmt.rows ) +--e assert_function( stmt.cols ) +--e assert_function( stmt.first_irow ) +--e assert_function( stmt.first_row ) +--e assert_function( stmt.first_cols ) +--e assert_function( stmt.column_names ) +--e assert_function( stmt.column_decltypes ) +--e assert_function( stmt.column_count ) +--e + + assert_function( stmt.isopen ) + assert_function( stmt.step ) + assert_function( stmt.reset ) + assert_function( stmt.finalize ) + assert_function( stmt.columns ) + assert_function( stmt.bind ) + assert_function( stmt.bind_values ) + assert_function( stmt.bind_names ) + assert_function( stmt.bind_blob ) + assert_function( stmt.bind_parameter_count ) + assert_function( stmt.bind_parameter_name ) + assert_function( stmt.get_value ) + assert_function( stmt.get_values ) + assert_function( stmt.get_name ) + assert_function( stmt.get_names ) + assert_function( stmt.get_type ) + assert_function( stmt.get_types ) + assert_function( stmt.get_uvalues ) + assert_function( stmt.get_unames ) + assert_function( stmt.get_utypes ) + assert_function( stmt.get_named_values ) + assert_function( stmt.get_named_types ) + assert_function( stmt.idata ) + assert_function( stmt.inames ) + assert_function( stmt.itypes ) + assert_function( stmt.data ) + assert_function( stmt.type ) +--e + +end + + + +------------------ +-- Tests basics -- +------------------ + +local basics = lunit_TestCase("Basics") + +function basics.setup() + basics.db = assert_userdata( sqlite3.open_memory() ) +end + +function basics.teardown() + assert_number( basics.db:close() ) +end + +function basics.create_table() + assert_number( basics.db:exec("CREATE TABLE test (id, name)") ) +end + +function basics.drop_table() + assert_number( basics.db:exec("DROP TABLE test") ) +end + +function basics.insert(id, name) + assert_number( basics.db:exec("INSERT INTO test VALUES ("..id..", '"..name.."')") ) +end + +function basics.update(id, name) + assert_number( basics.db:exec("UPDATE test SET name = '"..name.."' WHERE id = "..id) ) +end + +function basics.test_create_drop() + basics.create_table() + basics.drop_table() +end + +function basics.test_multi_create_drop() + basics.create_table() + basics.drop_table() + basics.create_table() + basics.drop_table() +end + +function basics.test_insert() + basics.create_table() + basics.insert(1, "Hello World") + basics.insert(2, "Hello Lua") + basics.insert(3, "Hello sqlite3") +end + +function basics.test_update() + basics.create_table() + basics.insert(1, "Hello Home") + basics.insert(2, "Hello Lua") + basics.update(1, "Hello World") +end + + +--------------------------------- +-- Statement Column Info Tests -- +--------------------------------- + +lunit_wrap("Column Info Test", function() + local db = assert_userdata( sqlite3.open_memory() ) + assert_number( db:exec("CREATE TABLE test (id INTEGER, name TEXT)") ) + local stmt = assert_userdata( db:prepare("SELECT * FROM test") ) + + assert_equal(2, stmt:columns(), "Wrong number of columns." ) + + local names = assert_table( stmt:get_names() ) + assert_equal(2, #(names), "Wrong number of names.") + assert_equal("id", names[1] ) + assert_equal("name", names[2] ) + + local types = assert_table( stmt:get_types() ) + assert_equal(2, #(types), "Wrong number of declaration types.") + assert_equal("INTEGER", types[1] ) + assert_equal("TEXT", types[2] ) + + assert_equal( sqlite3.OK, stmt:finalize() ) + assert_equal( sqlite3.OK, db:close() ) +end) + + + +--------------------- +-- Statement Tests -- +--------------------- + +st = lunit_TestCase("Statement Tests") + +function st.setup() + st.db = assert( sqlite3.open_memory() ) + assert_equal( sqlite3.OK, st.db:exec("CREATE TABLE test (id, name)") ) + assert_equal( sqlite3.OK, st.db:exec("INSERT INTO test VALUES (1, 'Hello World')") ) + assert_equal( sqlite3.OK, st.db:exec("INSERT INTO test VALUES (2, 'Hello Lua')") ) + assert_equal( sqlite3.OK, st.db:exec("INSERT INTO test VALUES (3, 'Hello sqlite3')") ) +end + +function st.teardown() + assert_equal( sqlite3.OK, st.db:close() ) +end + +function st.check_content(expected) + local stmt = assert( st.db:prepare("SELECT * FROM test ORDER BY id") ) + local i = 0 + for row in stmt:rows() do + i = i + 1 + assert( i <= #(expected), "Too many rows." ) + assert_equal(2, #(row), "Two result column expected.") + assert_equal(i, row[1], "Wrong 'id'.") + assert_equal(expected[i], row[2], "Wrong 'name'.") + end + assert_equal( #(expected), i, "Too few rows." ) + assert_number( stmt:finalize() ) +end + +function st.test_setup() + assert_pass(function() st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3" } end) + assert_error(function() st.check_content{ "Hello World", "Hello Lua" } end) + assert_error(function() st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "To much" } end) + assert_error(function() st.check_content{ "Hello World", "Hello Lua", "Wrong" } end) + assert_error(function() st.check_content{ "Hello World", "Wrong", "Hello sqlite3" } end) + assert_error(function() st.check_content{ "Wrong", "Hello Lua", "Hello sqlite3" } end) +end + +function st.test_questionmark_args() + local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (?, ?)") ) + assert_number( stmt:bind_values(0, "Test") ) + assert_error(function() stmt:bind_values("To few") end) + assert_error(function() stmt:bind_values(0, "Test", "To many") end) +end + +function st.test_questionmark() + local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (?, ?)") ) + assert_number( stmt:bind_values(4, "Good morning") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" } + assert_number( stmt:bind_values(5, "Foo Bar") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } + assert_number( stmt:finalize() ) +end + +--[===[ +function st.test_questionmark_multi() + local stmt = assert_userdata( st.db:prepare([[ + INSERT INTO test VALUES (?, ?); INSERT INTO test VALUES (?, ?) ]])) + assert( stmt:bind_values(5, "Foo Bar", 4, "Good morning") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } + assert_number( stmt:finalize() ) +end +]===] + +function st.test_identifiers() + local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, :name)") ) + assert_number( stmt:bind_values(4, "Good morning") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" } + assert_number( stmt:bind_values(5, "Foo Bar") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } + assert_number( stmt:finalize() ) +end + +--[===[ +function st.test_identifiers_multi() + local stmt = assert_table( st.db:prepare([[ + INSERT INTO test VALUES (:id1, :name1); INSERT INTO test VALUES (:id2, :name2) ]])) + assert( stmt:bind_values(5, "Foo Bar", 4, "Good morning") ) + assert( stmt:exec() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } +end +]===] + +function st.test_identifiers_names() + --local stmt = assert_userdata( st.db:prepare({"name", "id"}, "INSERT INTO test VALUES (:id, $name)") ) + local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, $name)") ) + assert_number( stmt:bind_names({name="Good morning", id=4}) ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" } + assert_number( stmt:bind_names({name="Foo Bar", id=5}) ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } + assert_number( stmt:finalize() ) +end + +--[===[ +function st:test_identifiers_multi_names() + local stmt = assert_table( st.db:prepare( {"name", "id1", "id2"},[[ + INSERT INTO test VALUES (:id1, $name); INSERT INTO test VALUES ($id2, :name) ]])) + assert( stmt:bind_values("Hoho", 4, 5) ) + assert( stmt:exec() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" } +end +]===] + +function st.test_colon_identifiers_names() + local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, :name)") ) + assert_number( stmt:bind_names({name="Good morning", id=4}) ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" } + assert_number( stmt:bind_names({name="Foo Bar", id=5}) ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } + assert_number( stmt:finalize() ) +end + +--[===[ +function st:test_colon_identifiers_multi_names() + local stmt = assert_table( st.db:prepare( {":name", ":id1", ":id2"},[[ + INSERT INTO test VALUES (:id1, $name); INSERT INTO test VALUES ($id2, :name) ]])) + assert( stmt:bind_values("Hoho", 4, 5) ) + assert( stmt:exec() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" } +end + + +function st.test_dollar_identifiers_names() + local stmt = assert_table( st.db:prepare({"$name", "$id"}, "INSERT INTO test VALUES (:id, $name)") ) + assert_table( stmt:bind_values("Good morning", 4) ) + assert_table( stmt:exec() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" } + assert_table( stmt:bind_values("Foo Bar", 5) ) + assert_table( stmt:exec() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } +end + +function st.test_dollar_identifiers_multi_names() + local stmt = assert_table( st.db:prepare( {"$name", "$id1", "$id2"},[[ + INSERT INTO test VALUES (:id1, $name); INSERT INTO test VALUES ($id2, :name) ]])) + assert( stmt:bind_values("Hoho", 4, 5) ) + assert( stmt:exec() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" } +end +]===] + +function st.test_bind_by_names() + local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, :name)") ) + local args = { } + args.id = 5 + args.name = "Hello girls" + assert( stmt:bind_names(args) ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + args.id = 4 + args.name = "Hello boys" + assert( stmt:bind_names(args) ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hello boys", "Hello girls" } + assert_number( stmt:finalize() ) +end + +function st.test_last_insert_rowid() + local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, :name)") ) + local args = { } + args.id = 5 + args.name = "Hello girls" + assert( stmt:bind_names(args) ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + assert_number( stmt:last_insert_rowid() ) + assert_equal( stmt:last_insert_rowid(), 4 ) + args.id = 4 + args.name = "Hello boys" + assert( stmt:bind_names(args) ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + assert_number( stmt:last_insert_rowid() ) + assert_equal( stmt:last_insert_rowid(), 5 ) + st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hello boys", "Hello girls" } + assert_number( stmt:finalize() ) +end + + + +-------------------------------- +-- Tests binding of arguments -- +-------------------------------- + +b = lunit_TestCase("Binding Tests") + +function b.setup() + b.db = assert( sqlite3.open_memory() ) + assert_number( b.db:exec("CREATE TABLE test (id, name, u, v, w, x, y, z)") ) +end + +function b.teardown() + assert_number( b.db:close() ) +end + +function b.test_auto_parameter_names() + local stmt = assert_userdata( b.db:prepare("INSERT INTO test VALUES(:a, $b, :a2, :b2, $a, :b, $a3, $b3)") ) + local parameters = assert_number( stmt:bind_parameter_count() ) + assert_equal( 8, parameters ) + assert_equal( ":a", stmt:bind_parameter_name(1) ) + assert_equal( "$b", stmt:bind_parameter_name(2) ) + assert_equal( ":a2", stmt:bind_parameter_name(3) ) + assert_equal( ":b2", stmt:bind_parameter_name(4) ) + assert_equal( "$a", stmt:bind_parameter_name(5) ) + assert_equal( ":b", stmt:bind_parameter_name(6) ) + assert_equal( "$a3", stmt:bind_parameter_name(7) ) + assert_equal( "$b3", stmt:bind_parameter_name(8) ) +end + +function b.test_auto_parameter_names() + local stmt = assert_userdata( b.db:prepare("INSERT INTO test VALUES($a, $b, $a2, $b2, $a, $b, $a3, $b3)") ) + local parameters = assert_number( stmt:bind_parameter_count() ) + assert_equal( 6, parameters ) + assert_equal( "$a", stmt:bind_parameter_name(1) ) + assert_equal( "$b", stmt:bind_parameter_name(2) ) + assert_equal( "$a2", stmt:bind_parameter_name(3) ) + assert_equal( "$b2", stmt:bind_parameter_name(4) ) + assert_equal( "$a3", stmt:bind_parameter_name(5) ) + assert_equal( "$b3", stmt:bind_parameter_name(6) ) +end + +function b.test_no_parameter_names_1() + local stmt = assert_userdata( b.db:prepare([[ SELECT * FROM test ]])) + local parameters = assert_number( stmt:bind_parameter_count() ) + assert_equal( 0, (parameters) ) +end + +function b.test_no_parameter_names_2() + local stmt = assert_userdata( b.db:prepare([[ INSERT INTO test VALUES(?, ?, ?, ?, ?, ?, ?, ?) ]])) + local parameters = assert_number( stmt:bind_parameter_count() ) + assert_equal( 8, (parameters) ) + assert_nil( stmt:bind_parameter_name(1) ) +end + + + + +---------------------------- +-- Tests for update_hook -- +---------------------------- + +uh = lunit_TestCase("Update Hook") + +function uh.setup() + uh.db = assert( sqlite3.open_memory() ) + uh.udtbl = {[sqlite3.INSERT]=0, [sqlite3.UPDATE]=0, [sqlite3.DELETE]=0} + uh.crtbl = {0, 0} + assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid) + --print("Sqlite Update Hook:", op, dname, tname, rowid) + ud[op] = ud[op] + 1 + end, uh.udtbl)) + uh.uttblsz = function () local sz = 0; for _,_ in pairs(uh.udtbl) do sz = sz + 1 end return sz end + -- enable foreign keys! + assert_number( uh.db:exec("PRAGMA foreign_keys = ON;") ) + assert_number( uh.db:exec("CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR );") ) + assert_number( uh.db:exec("CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );") ) + assert_number( uh.db:exec("CREATE TABLE T2 ( id INTEGER PRIMARY KEY, content VARCHAR );") ) +end + +function uh.teardown() + --for k,v in pairs(uh.udtbl) do print(k,v) end + assert_equal( 3, uh.uttblsz() ) + assert_number( uh.db:close() ) +end + +function uh.test_insert1() + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") ) + assert_equal( 1, uh.udtbl[sqlite3.INSERT] ) +end + +function uh.test_insert3() + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") ) + assert_equal( 3, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 0, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 0, uh.udtbl[sqlite3.DELETE] ) + +end + +function uh.test_insert3_update1() + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") ) + assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") ) + assert_equal( 3, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 1, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 0, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_insert3_delete1() + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") ) + assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") ) + assert_equal( 3, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 0, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 1, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_insert3_update1_delete1() + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") ) + assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") ) + assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") ) + assert_equal( 3, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 1, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 1, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_insert_select() + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello Lua');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 SELECT * FROM T1;") ) + assert_equal( 6, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 0, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 0, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_trigger_insert() + assert_equal( sqlite3.OK, uh.db:exec([[ + CREATE TRIGGER after_insert_T1 + AFTER INSERT ON T1 + FOR EACH ROW + BEGIN + INSERT INTO T2 VALUES(NEW.id, NEW.content); + END; + ]]) ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello Lua');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');") ) + assert_equal( 6, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 0, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 0, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_cascade_update() + assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") ) + assert_equal( sqlite3.OK, uh.db:exec([[ + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON UPDATE CASCADE, content VARCHAR ); + ]]) ) + + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'c');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'd');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 SELECT * FROM T1;") ) + assert_equal( sqlite3.OK, uh.db:exec("UPDATE T1 SET id = id + 10 WHERE id < 3;") ) + + assert_equal( 8, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 4, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 0, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_cascade_update_restrict() + assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") ) + assert_equal( sqlite3.OK, uh.db:exec([[ + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON UPDATE RESTRICT, content VARCHAR ); + ]]) ) + + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'c');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'd');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 SELECT * FROM T1;") ) + -- the update_hook with rowid=11 *DOES* get triggered before the RESTRICT constraint is enforced + assert_equal( sqlite3.CONSTRAINT, uh.db:exec("UPDATE T1 SET id = id + 10 WHERE id < 3;") ) + + assert_equal( 8, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 1, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 0, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_cascade_delete_restrict() + assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") ) + assert_equal( sqlite3.OK, uh.db:exec([[ + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON DELETE RESTRICT, content VARCHAR ); + ]]) ) + + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'c');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'd');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 SELECT * FROM T1;") ) + -- the update_hook with rowid=1 *DOES* get triggered before the RESTRICT constraint is enforced + assert_equal( sqlite3.CONSTRAINT, uh.db:exec("DELETE FROM T1 WHERE id < 3;") ) + + assert_equal( 8, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 0, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 1, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_fk_violate_insert() + assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") ) + assert_equal( sqlite3.OK, uh.db:exec([[ + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR ); + ]]) ) + + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") ) + -- no update hook triggered here + assert_equal( sqlite3.CONSTRAINT, uh.db:exec("INSERT INTO T2 VALUES(99, 'xxx')") ) + + assert_equal( 2, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 0, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 0, uh.udtbl[sqlite3.DELETE] ) +end + +function uh.test_fk_violate_update() + assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") ) + assert_equal( sqlite3.OK, uh.db:exec([[ + CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR ); + ]]) ) + + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") ) + assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 VALUES (1, 'a');") ) + -- update hook triggered + assert_equal( sqlite3.CONSTRAINT, uh.db:exec("UPDATE T2 SET id = 99 WHERE id = 1;") ) + + assert_equal( 3, uh.udtbl[sqlite3.INSERT] ) + assert_equal( 1, uh.udtbl[sqlite3.UPDATE] ) + assert_equal( 0, uh.udtbl[sqlite3.DELETE] ) +end + +---------------------------------------------- +-- Tests for commit_hook and rollback_hook -- +---------------------------------------------- + +crh = lunit_TestCase("Commit/Rollback Hook") + +function crh.setup() + crh.db = assert( sqlite3.open_memory() ) + crh.crtbl = {0, 0} + assert_number( crh.db:exec("PRAGMA foreign_keys = ON;") ) + assert_number( crh.db:exec("CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );") ) + assert_number( crh.db:exec("CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR );") ) + + -- reset commit hook return value + crh.commit_hook_returnvalue = false + + assert_nil( crh.db:commit_hook(function (ud) + -- print('Commit hook') + ud[1] = ud[1] + 1; + return crh.commit_hook_returnvalue + end, crh.crtbl)) + + assert_nil( crh.db:rollback_hook(function (ud) + -- print('Rollback hook') + ud[2] = ud[2] + 1 + end, crh.crtbl)) +end + +function crh.teardown() + assert_number( crh.db:close() ) +end + +function crh.test_simple_transaction_commit() + assert_equal( sqlite3.OK, crh.db:exec("BEGIN;") ) + assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, crh.db:exec("UPDATE T1 SET content = 'Hello Again World' WHERE id = 1;") ) + assert_equal( sqlite3.OK, crh.db:exec("DELETE FROM T1 WHERE id = 2;") ) + assert_equal( sqlite3.OK, crh.db:exec("COMMIT;") ) + assert_equal( 1, crh.crtbl[1] ) + assert_equal( 0, crh.crtbl[2] ) +end + +function crh.test_simple_transaction_prohibit() + -- if the commit hook returns anything except false or nil, the commit would turn into rollback + crh.commit_hook_returnvalue = 1 + + assert_equal( sqlite3.OK, crh.db:exec("BEGIN;") ) + assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, crh.db:exec("UPDATE T1 SET content = 'Hello Again World' WHERE id = 1;") ) + assert_equal( sqlite3.OK, crh.db:exec("DELETE FROM T1 WHERE id = 2;") ) + assert_equal( sqlite3.CONSTRAINT, crh.db:exec("COMMIT;") ) + -- commit hook gets called and returns 1 triggering a rollback + assert_equal( 1, crh.crtbl[1] ) + assert_equal( 1, crh.crtbl[2] ) +end + +function crh.test_simple_transaction_rollback() + assert_equal( sqlite3.OK, crh.db:exec("BEGIN;") ) + assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, crh.db:exec("UPDATE T1 SET content = 'Hello Again World' WHERE id = 1;") ) + assert_equal( sqlite3.OK, crh.db:exec("DELETE FROM T1 WHERE id = 2;") ) + assert_equal( sqlite3.OK, crh.db:exec("ROLLBACK;") ) + assert_equal( 0, crh.crtbl[1] ) + assert_equal( 1, crh.crtbl[2] ) +end + +function crh.test_statement_level_transaction() + assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, crh.db:exec("UPDATE T1 SET content = 'Hello Again World' WHERE id = 1;") ) + assert_equal( sqlite3.OK, crh.db:exec("DELETE FROM T1 WHERE id = 2;") ) + assert_equal( 3, crh.crtbl[1] ) + assert_equal( 0, crh.crtbl[2] ) +end + +function crh.test_statement_level_fk_violate_insert() + assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") ) + assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") ) + -- implicit statement-level transaction rollback + error + assert_equal( sqlite3.CONSTRAINT, crh.db:exec("INSERT INTO T2 VALUES(99, 'xxx')") ) + assert_equal( 2, crh.crtbl[1] ) + assert_equal( 1, crh.crtbl[2] ) +end + +function crh.test_transaction_fk_violate_update() + assert_equal( sqlite3.OK, crh.db:exec("BEGIN;") ) + assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T2 VALUES (1, 'xyz');") ) + -- Doesn't trigger rollback hook because the implicit update statement transaction + -- is nested inside our explicit transaction. However we *do* get an error. + assert_equal( sqlite3.CONSTRAINT, crh.db:exec("UPDATE T2 SET id = 99 WHERE id = 1;") ) + -- rollback explicitly + assert_equal( sqlite3.OK, crh.db:exec("ROLLBACK;") ) + + assert_equal( 0, crh.crtbl[1] ) + assert_equal( 1, crh.crtbl[2] ) +end + +function crh.test_savepoint_nested_commit() + assert_equal( sqlite3.OK, crh.db:exec("SAVEPOINT S1;") ) + assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello Lua');") ) + assert_equal( sqlite3.OK, crh.db:exec(" SAVEPOINT S2;") ) + assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');") ) + -- nested transactions don't trigger commit/rollback hooks + assert_equal( sqlite3.OK, crh.db:exec(" RELEASE S2;") ) + assert_equal( sqlite3.OK, crh.db:exec("RELEASE S1;") ) + assert_equal( 1, crh.crtbl[1] ) + assert_equal( 0, crh.crtbl[2] ) +end + +function crh.test_savepoint_nested_rollback() + assert_equal( sqlite3.OK, crh.db:exec("SAVEPOINT S1;") ) + assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello World');") ) + assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello Lua');") ) + assert_equal( sqlite3.OK, crh.db:exec(" SAVEPOINT S2;") ) + assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');") ) + -- nested transactions don't trigger commit/rollback hooks + assert_equal( sqlite3.OK, crh.db:exec(" ROLLBACK TO S2;") ) + assert_equal( sqlite3.OK, crh.db:exec("RELEASE S1;") ) + assert_equal( 1, crh.crtbl[1] ) + assert_equal( 0, crh.crtbl[2] ) +end + + +-------------------------------------------- +-- Tests loop break and statement reusage -- +-------------------------------------------- + + +---------------------------------------------------- +-- Test garbage collected db with live statements -- +---------------------------------------------------- + +gco = lunit_TestCase("Bug-Report: GC'd db with live prepared statements") + +function gco.setup() + gco.db = assert( sqlite3.open_memory() ) + assert_equal( sqlite3.OK, gco.db:exec("CREATE TABLE test (id, name)") ) + assert_equal( sqlite3.OK, gco.db:exec("INSERT INTO test VALUES (1, 'Hello World')") ) + assert_equal( sqlite3.OK, gco.db:exec("INSERT INTO test VALUES (2, 'Hello Lua')") ) + assert_equal( sqlite3.OK, gco.db:exec("INSERT INTO test VALUES (3, 'Hello sqlite3')") ) +end + +-- gco.db:close() +function gco.test_gc() + local stmt = assert_userdata( gco.db:prepare("INSERT INTO test VALUES (?, ?)") ) + assert_number( stmt:bind_values(4, "Good morning") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + --st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" } + gco.db = nil -- reap the db if unreferenced from stmt + collectgarbage() + assert_number( stmt:bind_values(5, "Foo Bar") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + --st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } + assert_number( stmt:finalize() ) +end + +function gco.teardown() + assert_true( (gco.db == nil) or (gco.db:close() == sqlite3.OK) ) +end + + +if _VERSION >= 'Lua 5.3' then + +-- this hack is avoid syntax errors in Lua < 5.3 +local bitand = (load "return function (x, y) return x & y end")() +local bitshr = (load "return function (x, y) return x >> y end")() + +l53 = lunit_TestCase("Lua 5.3 integers") + +function l53.setup() + l53.db = assert( sqlite3.open_memory() ) + assert_equal( sqlite3.OK, l53.db:exec("CREATE TABLE test (id, val)") ) + assert_equal( sqlite3.OK, l53.db:exec("INSERT INTO test VALUES (1, 0x12345678abcdef09)") ) + assert_equal( sqlite3.OK, l53.db:exec("INSERT INTO test VALUES (2, 5.1234567890123456)") ) + assert_equal( sqlite3.OK, l53.db:exec("INSERT INTO test VALUES (3, 42)") ) +end + +-- l53.db:close() +function l53.test_intfloat() + local db = l53.db + for row in db:nrows("SELECT val FROM test WHERE id = 1") do + assert_equal (row.val, 0x12345678abcdef09) + assert_equal (bitand(row.val, 0xffffffff), 0xabcdef09) + assert_equal (bitshr(row.val, 32), 0x12345678) + end + for row in db:nrows("SELECT val FROM test WHERE id = 2") do + assert_equal (row.val, 5.1234567890123456) + end + for row in db:nrows("SELECT val FROM test WHERE id = 3") do + assert_equal (row.val, 42) + end +end + +function l53.teardown() + assert_true( (l53.db == nil) or (l53.db:close() == sqlite3.OK) ) +end + +end + +---------------------------- +-- Test for bugs reported -- +---------------------------- + +bug = lunit_TestCase("Bug-Report Tests") + +function bug.setup() + bug.db = assert( sqlite3.open_memory() ) +end + +function bug.teardown() + assert_number( bug.db:close() ) +end + +--[===[ +function bug.test_1() + bug.db:exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)") + + local query = assert_userdata( bug.db:prepare("SELECT id FROM test WHERE value=?") ) + + assert_table ( query:bind_values("1") ) + assert_nil ( query:first_cols() ) + assert_table ( query:bind_values("2") ) + assert_nil ( query:first_cols() ) +end +]===] + +function bug.test_nils() -- appeared in lua-5.1 (holes in arrays) + local function check(arg1, arg2, arg3, arg4, arg5) + assert_equal(1, arg1) + assert_equal(2, arg2) + assert_nil(arg3) + assert_equal(4, arg4) + assert_nil(arg5) + end + + bug.db:create_function("test_nils", 5, function(arg1, arg2, arg3, arg4, arg5) + check(arg1, arg2, arg3, arg4, arg5) + end, {}) + + assert_number( bug.db:exec([[ SELECT test_nils(1, 2, NULL, 4, NULL) ]]) ) + + for arg1, arg2, arg3, arg4, arg5 in bug.db:urows([[ SELECT 1, 2, NULL, 4, NULL ]]) + do check(arg1, arg2, arg3, arg4, arg5) + end + + for row in bug.db:rows([[ SELECT 1, 2, NULL, 4, NULL ]]) + do assert_table( row ) + check(row[1], row[2], row[3], row[4], row[5]) + end +end + +---------------------------- +-- Test for collation fun -- +---------------------------- + +colla = lunit_TestCase("Collation Tests") + +function colla.setup() + local function collate(s1,s2) + -- if p then print("collation callback: ",s1,s2) end + s1=s1:lower() + s2=s2:lower() + if s1==s2 then return 0 + elseif s1<s2 then return -1 + else return 1 end + end + colla.db = assert( sqlite3.open_memory() ) + assert_nil(colla.db:create_collation('CINSENS',collate)) + colla.db:exec[[ + CREATE TABLE test(id INTEGER PRIMARY KEY,content COLLATE CINSENS); + INSERT INTO test VALUES(NULL,'hello world'); + INSERT INTO test VALUES(NULL,'Buenos dias'); + INSERT INTO test VALUES(NULL,'HELLO WORLD'); + INSERT INTO test VALUES(NULL,'Guten Tag'); + INSERT INTO test VALUES(NULL,'HeLlO WoRlD'); + INSERT INTO test VALUES(NULL,'Bye for now'); + ]] +end + +function colla.teardown() + assert_number( colla.db:close() ) +end + +function colla.test() + --for row in db:nrows('SELECT * FROM test') do + -- print(row.id,row.content) + --end + local n = 0 + for row in colla.db:nrows('SELECT * FROM test WHERE content="hElLo wOrLd"') do + -- print(row.id,row.content) + assert_equal (row.content:lower(), "hello world") + n = n + 1 + end + assert_equal (n, 3) +end + +-------------------------------------- +-- Tests for NULs in BLOBs and TEXT -- +-------------------------------------- + +local db_blobNULs = lunit_TestCase("Blob NULs") + +function db_blobNULs.setup() + db_blobNULs.db = assert( sqlite3.open_memory() ) + assert_equal( sqlite3.OK, db_blobNULs.db:exec("CREATE TABLE test (id, name blob)") ) + assert_equal( sqlite3.OK, db_blobNULs.db:exec("INSERT INTO test VALUES (1, CAST('Hello' || x'00' || 'World' || x'00' AS BLOB))") ) + assert_equal( sqlite3.OK, db_blobNULs.db:exec("INSERT INTO test VALUES (2, CAST('Hello' || x'00' || 'Lua' || x'00' AS BLOB))") ) + assert_equal( sqlite3.OK, db_blobNULs.db:exec("INSERT INTO test VALUES (3, CAST('Hello' || x'00' || 'SQLite' || x'00' AS BLOB))") ) +end + +function db_blobNULs.teardown() + assert( db_blobNULs.db:close() ) +end + +function db_blobNULs.test() + local db = db_blobNULs.db + for row in db:nrows("SELECT id as val FROM test WHERE name=CAST('Hello' || x'00' || 'World' || x'00' AS BLOB)") do + assert_equal (row.val, 1) + end + for row in db:nrows("SELECT substr(name,7,4) as val FROM test WHERE id = 2") do + assert_equal (row.val,'Lua\0') + assert_equal (#row.val, 4) + end + for row in db:nrows("SELECT name as val FROM test WHERE id = 3") do + assert_equal (row.val, 'Hello\0SQLite\0') + assert_equal (#row.val, 13) + end +end + +local db_textNULs = lunit_TestCase("Text NULs") + +function db_textNULs.setup() + db_textNULs.db = assert( sqlite3.open_memory() ) + assert_equal( sqlite3.OK, db_textNULs.db:exec("CREATE TABLE test (id, name text)") ) + assert_equal( sqlite3.OK, db_textNULs.db:exec("INSERT INTO test VALUES (1, CAST('Hello' || x'00' || 'World' || x'00' AS TEXT))") ) + assert_equal( sqlite3.OK, db_textNULs.db:exec("INSERT INTO test VALUES (2, CAST('Hello' || x'00' || 'Lua' || x'00' AS TEXT))") ) + assert_equal( sqlite3.OK, db_textNULs.db:exec("INSERT INTO test VALUES (3, CAST('Hello' || x'00' || 'SQLite' || x'00' AS TEXT))") ) +end + +function db_textNULs.teardown() + assert( db_textNULs.db:close() ) +end + +function db_textNULs.test() + local db = db_textNULs.db + for row in db:nrows("SELECT id as val FROM test WHERE name=CAST('Hello' || x'00' || 'World' || x'00' AS TEXT)") do + assert_equal (row.val, 1) + end + for row in db:nrows("SELECT substr(CAST(name AS BLOB),7,4) as val FROM test WHERE id = 2") do + assert_equal (row.val,'Lua\0') + assert_equal (#row.val, 4) + end + for row in db:nrows("SELECT name as val FROM test WHERE id = 3") do + assert_equal (row.val, 'Hello\0SQLite\0') + assert_equal (#row.val, 13) + end +end + +------------------------------------- +-- Tests for Online Backup API -- +------------------------------------- + +local db_bu = lunit_TestCase("Online Backup API") + +function db_bu.setup() + db_bu.db_src = assert( sqlite3.open_memory() ) + assert_equal( sqlite3.OK, db_bu.db_src:exec("CREATE TABLE test (id, name text)") ) + assert_equal( sqlite3.OK, db_bu.db_src:exec("INSERT INTO test VALUES (1, 'Hello World')") ) + assert_equal( sqlite3.OK, db_bu.db_src:exec("INSERT INTO test VALUES (2, 'Hello Lua')") ) + assert_equal( sqlite3.OK, db_bu.db_src:exec("INSERT INTO test VALUES (3, 'Hello SQLite')") ) + db_bu.filename = "/tmp/__lua-sqlite3-20161102233049." .. os.time() + db_bu.db_tgt = assert_userdata( sqlite3.open(db_bu.filename) ) +end + +function db_bu.teardown() + assert( db_bu.db_src:close() ) + assert( db_bu.db_tgt:close() ) + os.remove(db_bu.filename) +end + +function db_bu.test() + + assert_function( sqlite3.backup_init ) + + local bu = assert_userdata( sqlite3.backup_init(db_bu.db_tgt, 'main', db_bu.db_src, 'main') ) + + assert_function ( bu.step ) + assert_function ( bu.remaining ) + assert_function ( bu.pagecount ) + assert_function ( bu.finish ) + + if true then + bu = nil + collectgarbage() + collectgarbage() + else + bu:finish() + end + + bu = assert_userdata( sqlite3.backup_init(db_bu.db_tgt, 'main', db_bu.db_src, 'main') ) + + assert_equal( sqlite3.DONE, bu:step(-1) ) + assert_equal( sqlite3.OK, bu:finish() ) + bu = nil + + local db = db_bu.db_tgt + for row in db:nrows("SELECT id as val FROM test WHERE name='Hello World'") do + assert_equal (row.val, 1) + end + for row in db:nrows("SELECT substr(name,7,3) as val FROM test WHERE id = 2") do + assert_equal (row.val,'Lua') + assert_equal (#row.val, 3) + end + for row in db:nrows("SELECT name as val FROM test WHERE id = 3") do + assert_equal (row.val, 'Hello SQLite') + assert_equal (#row.val, 12) + end + +end + +local db_bu_gc = lunit_TestCase("Online Backup API GC") + +function db_bu_gc.setup() + db_bu_gc.db_src = assert( sqlite3.open_memory() ) + assert_equal( sqlite3.OK, db_bu_gc.db_src:exec("CREATE TABLE test (id, name text)") ) + assert_equal( sqlite3.OK, db_bu_gc.db_src:exec("INSERT INTO test VALUES (1, 'Hello World')") ) + assert_equal( sqlite3.OK, db_bu_gc.db_src:exec("INSERT INTO test VALUES (2, 'Hello Lua')") ) + assert_equal( sqlite3.OK, db_bu_gc.db_src:exec("INSERT INTO test VALUES (3, 'Hello SQLite')") ) + db_bu_gc.filename = "/tmp/__lua-sqlite3-20161103120909." .. os.time() + db_bu_gc.db_tgt = assert_userdata( sqlite3.open(db_bu_gc.filename) ) +end + +function db_bu_gc.teardown() + os.remove(db_bu_gc.filename) +end + +function db_bu_gc.test() + + local bu = assert_userdata( sqlite3.backup_init(db_bu_gc.db_tgt, 'main', db_bu_gc.db_src, 'main') ) + + db_bu_gc.db_src = nil + db_bu_gc.db_tgt = nil + + collectgarbage() + collectgarbage() -- should not close dbs even though db_bu_gc refs nil'd since they are referenced by bu + + assert_equal( sqlite3.DONE, bu:step(-1) ) + assert_equal( sqlite3.OK, bu:finish() ) + bu = nil + + collectgarbage() + collectgarbage() -- should now close dbs + + local db = assert_userdata( sqlite3.open(db_bu_gc.filename) ) + + for row in db:nrows("SELECT id as val FROM test WHERE name='Hello World'") do + assert_equal (row.val, 1) + end + for row in db:nrows("SELECT substr(name,7,3) as val FROM test WHERE id = 2") do + assert_equal (row.val,'Lua') + assert_equal (#row.val, 3) + end + for row in db:nrows("SELECT name as val FROM test WHERE id = 3") do + assert_equal (row.val, 'Hello SQLite') + assert_equal (#row.val, 12) + end + + assert( db:close() ) + +end + +local db_bu_null = lunit_TestCase("Online Backup API NULL") + +function db_bu_null.setup() + db_bu_null.db = assert( sqlite3.open_memory() ) +end + +function db_bu_null.teardown() + assert( db_bu_null.db:close() ) +end + +function db_bu_null.test() + + local bu = assert_nil( sqlite3.backup_init(db_bu_null.db, 'main', db_bu_null.db, 'main') ) + +end + +lunit.main() + |