diff options
Diffstat (limited to '3rdparty/lsqlite3/tests-sqlite3.lua')
-rw-r--r-- | 3rdparty/lsqlite3/tests-sqlite3.lua | 587 |
1 files changed, 587 insertions, 0 deletions
diff --git a/3rdparty/lsqlite3/tests-sqlite3.lua b/3rdparty/lsqlite3/tests-sqlite3.lua new file mode 100644 index 00000000000..83787ca9c18 --- /dev/null +++ b/3rdparty/lsqlite3/tests-sqlite3.lua @@ -0,0 +1,587 @@ + +--[[-------------------------------------------------------------------------- + + 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. + +--]]-------------------------------------------------------------------------- + + +require "lsqlite3" + +require "lunit" + +lunit.setprivfenv() +lunit.import "assertions" +lunit.import "checks" + + + + + + +------------------------------- +-- 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() + self.db = assert( sqlite3.open_memory() ) +end + +function db_funcs:teardown() + assert( self.db:close() ) +end + +function db_funcs:test() + local db = self.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() + self.db = assert( sqlite3.open_memory() ) + self.stmt = assert( self.db:prepare("CREATE TABLE test (id, content)") ) +end + +function stmt_funcs:teardown() +--e- assert( self.stmt:close() ) + assert( self.stmt:finalize() ) --e+ + assert( self.db:close() ) +end + +function stmt_funcs:test() + local stmt = self.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() + self.db = assert_userdata( sqlite3.open_memory() ) +end + +function basics:teardown() + assert_number( self.db:close() ) +end + +function basics:create_table() + assert_number( self.db:exec("CREATE TABLE test (id, name)") ) +end + +function basics:drop_table() + assert_number( self.db:exec("DROP TABLE test") ) +end + +function basics:insert(id, name) + assert_number( self.db:exec("INSERT INTO test VALUES ("..id..", '"..name.."')") ) +end + +function basics:update(id, name) + assert_number( self.db:exec("UPDATE test SET name = '"..name.."' WHERE id = "..id) ) +end + +function basics:test_create_drop() + self:create_table() + self:drop_table() +end + +function basics:test_multi_create_drop() + self:create_table() + self:drop_table() + self:create_table() + self:drop_table() +end + +function basics:test_insert() + self:create_table() + self:insert(1, "Hello World") + self:insert(2, "Hello Lua") + self:insert(3, "Hello sqlite3") +end + +function basics:test_update() + self:create_table() + self:insert(1, "Hello Home") + self:insert(2, "Hello Lua") + self: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() + self.db = assert( sqlite3.open_memory() ) + assert_equal( sqlite3.OK, self.db:exec("CREATE TABLE test (id, name)") ) + assert_equal( sqlite3.OK, self.db:exec("INSERT INTO test VALUES (1, 'Hello World')") ) + assert_equal( sqlite3.OK, self.db:exec("INSERT INTO test VALUES (2, 'Hello Lua')") ) + assert_equal( sqlite3.OK, self.db:exec("INSERT INTO test VALUES (3, 'Hello sqlite3')") ) +end + +function st:teardown() + assert_equal( sqlite3.OK, self.db:close() ) +end + +function st:check_content(expected) + local stmt = assert( self.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() self:check_content{ "Hello World", "Hello Lua", "Hello sqlite3" } end) + assert_error(function() self:check_content{ "Hello World", "Hello Lua" } end) + assert_error(function() self:check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "To much" } end) + assert_error(function() self:check_content{ "Hello World", "Hello Lua", "Wrong" } end) + assert_error(function() self:check_content{ "Hello World", "Wrong", "Hello sqlite3" } end) + assert_error(function() self:check_content{ "Wrong", "Hello Lua", "Hello sqlite3" } end) +end + +function st:test_questionmark_args() + local stmt = assert_userdata( self.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( self.db:prepare("INSERT INTO test VALUES (?, ?)") ) + assert_number( stmt:bind_values(4, "Good morning") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + self: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() ) + self: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( self.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() ) + self: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( self.db:prepare("INSERT INTO test VALUES (:id, :name)") ) + assert_number( stmt:bind_values(4, "Good morning") ) + assert_number( stmt:step() ) + assert_number( stmt:reset() ) + self: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() ) + self: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( self.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() ) + self:check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } +end +]===] + +function st:test_identifiers_names() + --local stmt = assert_userdata( self.db:prepare({"name", "id"}, "INSERT INTO test VALUES (:id, $name)") ) + local stmt = assert_userdata( self.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() ) + self: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() ) + self: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( self.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() ) + self:check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" } +end +]===] + +function st:test_colon_identifiers_names() + local stmt = assert_userdata( self.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() ) + self: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() ) + self: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( self.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() ) + self:check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" } +end + + +function st:test_dollar_identifiers_names() + local stmt = assert_table( self.db:prepare({"$name", "$id"}, "INSERT INTO test VALUES (:id, $name)") ) + assert_table( stmt:bind_values("Good morning", 4) ) + assert_table( stmt:exec() ) + self:check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" } + assert_table( stmt:bind_values("Foo Bar", 5) ) + assert_table( stmt:exec() ) + self:check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" } +end + +function st:test_dollar_identifiers_multi_names() + local stmt = assert_table( self.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() ) + self:check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" } +end +]===] + +function st:test_bind_by_names() + local stmt = assert_userdata( self.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() ) + self: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() + self.db = assert( sqlite3.open_memory() ) + assert_number( self.db:exec("CREATE TABLE test (id, name, u, v, w, x, y, z)") ) +end + +function b:teardown() + assert_number( self.db:close() ) +end + +function b:test_auto_parameter_names() + local stmt = assert_userdata( self.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( self.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( self.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( self.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 loop break and statement reusage -- +-------------------------------------------- + + + +---------------------------- +-- Test for bugs reported -- +---------------------------- + +bug = lunit.TestCase("Bug-Report Tests") + +function bug:setup() + self.db = assert( sqlite3.open_memory() ) +end + +function bug:teardown() + assert_number( self.db:close() ) +end + +--[===[ +function bug:test_1() + self.db:exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)") + + local query = assert_userdata( self.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 + + self.db:create_function("test_nils", 5, function(arg1, arg2, arg3, arg4, arg5) + check(arg1, arg2, arg3, arg4, arg5) + end, {}) + + assert_number( self.db:exec([[ SELECT test_nils(1, 2, NULL, 4, NULL) ]]) ) + + for arg1, arg2, arg3, arg4, arg5 in self.db:urows([[ SELECT 1, 2, NULL, 4, NULL ]]) + do check(arg1, arg2, arg3, arg4, arg5) + end + + for row in self.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 + self.db = assert( sqlite3.open_memory() ) + assert_nil(self.db:create_collation('CINSENS',collate)) + self.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( self.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 self.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 + +lunit.run() |