Chinaunix首页 | 论坛 | 博客
  • 博客访问: 891128
  • 博文数量: 286
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1841
  • 用 户 组: 普通用户
  • 注册时间: 2015-05-09 16:26
文章分类

全部博文(286)

文章存档

2016年(38)

2015年(248)

我的朋友

分类: SQLite/嵌入式数据库

2015-06-28 23:16:27

LuaSQLite3


官网资料:http://lua.sqlite.org/index.cgi/home

在openwrt安装sqlite3 和lsqlite3

测试:

点击(此处)折叠或打开

  1. --[[--------------------------------------------------------------------------

  2.     Author: Michael Roth <mroth@nessie.de>

  3.     Copyright (c) 2004, 2005 Michael Roth <mroth@nessie.de>

  4.     Permission is hereby granted, free of charge, to any person
  5.     obtaining a copy of this software and associated documentation
  6.     files (the "Software"), to deal in the Software without restriction,
  7.     including without limitation the rights to use, copy, modify, merge,
  8.     publish, distribute, sublicense, and/or sell copies of the Software,
  9.     and to permit persons to whom the Software is furnished to do so,
  10.     subject to the following conditions:

  11.     The above copyright notice and this permission notice shall be
  12.     included in all copies or substantial portions of the Software.

  13.     THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  14.     EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  15.     MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
  16.     IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
  17.     CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
  18.     TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE
  19.     SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

  20. --]]--------------------------------------------------------------------------

  21. -- extended for LuaSQLite3, and for Lua 5.2 (using lunitx)
  22. -- Copyright (c) 2005-13 Doug Currie
  23. -- Same license as above

  24. local sqlite3 = require "lsqlite3"

  25. local os = os

  26. local lunit = require "lunitx"

  27. local tests_sqlite3

  28. if _VERSION >= 'Lua 5.2' then

  29.     tests_sqlite3 = lunit.module('tests-sqlite3','seeall')
  30.     _ENV = tests_sqlite3

  31. else

  32.     module('tests_sqlite3', lunit.testcase, package.seeall)
  33.     tests_sqlite3 = _M

  34. end

  35. -- compat

  36. function lunit_wrap (name, fcn)
  37.    tests_sqlite3['test_o_'..name] = fcn
  38. end

  39. function lunit_TestCase (name)
  40.    return lunit.module(name,'seeall')
  41. end


  42. -------------------------------
  43. -- Basic open and close test --
  44. -------------------------------

  45. lunit_wrap("open_memory", function()
  46.   local db = assert_userdata( sqlite3.open_memory() )
  47.   assert( db:close() )
  48. end)

  49. lunit_wrap("open", function()
  50.   local filename = "/tmp/__lua-sqlite3-20040906135849." .. os.time()
  51.   local db = assert_userdata( sqlite3.open(filename) )
  52.   assert( db:close() )
  53.   os.remove(filename)
  54. end)



  55. -------------------------------------
  56. -- Presence of db member functions --
  57. -------------------------------------

  58. local db_funcs = lunit_TestCase("Database Member Functions")

  59. function db_funcs.setup()
  60.   db_funcs.db = assert( sqlite3.open_memory() )
  61. end

  62. function db_funcs.teardown()
  63.   assert( db_funcs.db:close() )
  64. end

  65. function db_funcs.test()
  66.   local db = db_funcs.db
  67.   assert_function( db.close )
  68.   assert_function( db.exec )
  69. --e assert_function( db.irows )
  70.   assert_function( db.rows )
  71. --e assert_function( db.cols )
  72. --e assert_function( db.first_irow )
  73. --e assert_function( db.first_row )
  74. --e assert_function( db.first_cols )
  75.   assert_function( db.prepare )
  76.   assert_function( db.interrupt )
  77.   assert_function( db.last_insert_rowid )
  78.   assert_function( db.changes )
  79.   assert_function( db.total_changes )
  80. end



  81. ---------------------------------------
  82. -- Presence of stmt member functions --
  83. ---------------------------------------

  84. local stmt_funcs = lunit_TestCase("Statement Member Functions")

  85. function stmt_funcs.setup()
  86.   stmt_funcs.db = assert( sqlite3.open_memory() )
  87.   stmt_funcs.stmt = assert( stmt_funcs.db:prepare("CREATE TABLE test (id, content)") )
  88. end

  89. function stmt_funcs.teardown()
  90. --e- assert( stmt_funcs.stmt:close() )
  91.   assert( stmt_funcs.stmt:finalize() ) --e+
  92.   assert( stmt_funcs.db:close() )
  93. end

  94. function stmt_funcs.test()
  95.   local stmt = stmt_funcs.stmt
  96. --e assert_function( stmt.close )
  97.   assert_function( stmt.reset )
  98. --e assert_function( stmt.exec )
  99.   assert_function( stmt.bind )
  100. --e assert_function( stmt.irows )
  101. --e assert_function( stmt.rows )
  102. --e assert_function( stmt.cols )
  103. --e assert_function( stmt.first_irow )
  104. --e assert_function( stmt.first_row )
  105. --e assert_function( stmt.first_cols )
  106. --e assert_function( stmt.column_names )
  107. --e assert_function( stmt.column_decltypes )
  108. --e assert_function( stmt.column_count )
  109. --e +
  110.   assert_function( stmt.isopen )
  111.   assert_function( stmt.step )
  112.   assert_function( stmt.reset )
  113.   assert_function( stmt.finalize )
  114.   assert_function( stmt.columns )
  115.   assert_function( stmt.bind )
  116.   assert_function( stmt.bind_values )
  117.   assert_function( stmt.bind_names )
  118.   assert_function( stmt.bind_blob )
  119.   assert_function( stmt.bind_parameter_count )
  120.   assert_function( stmt.bind_parameter_name )
  121.   assert_function( stmt.get_value )
  122.   assert_function( stmt.get_values )
  123.   assert_function( stmt.get_name )
  124.   assert_function( stmt.get_names )
  125.   assert_function( stmt.get_type )
  126.   assert_function( stmt.get_types )
  127.   assert_function( stmt.get_uvalues )
  128.   assert_function( stmt.get_unames )
  129.   assert_function( stmt.get_utypes )
  130.   assert_function( stmt.get_named_values )
  131.   assert_function( stmt.get_named_types )
  132.   assert_function( stmt.idata )
  133.   assert_function( stmt.inames )
  134.   assert_function( stmt.itypes )
  135.   assert_function( stmt.data )
  136.   assert_function( stmt.type )
  137. --e +
  138. end



  139. ------------------
  140. -- Tests basics --
  141. ------------------

  142. local basics = lunit_TestCase("Basics")

  143. function basics.setup()
  144.   basics.db = assert_userdata( sqlite3.open_memory() )
  145. end

  146. function basics.teardown()
  147.   assert_number( basics.db:close() )
  148. end

  149. function basics.create_table()
  150.   assert_number( basics.db:exec("CREATE TABLE test (id, name)") )
  151. end

  152. function basics.drop_table()
  153.   assert_number( basics.db:exec("DROP TABLE test") )
  154. end

  155. function basics.insert(id, name)
  156.   assert_number( basics.db:exec("INSERT INTO test VALUES ("..id..", '"..name.."')") )
  157. end

  158. function basics.update(id, name)
  159.   assert_number( basics.db:exec("UPDATE test SET name = '"..name.."' WHERE id = "..id) )
  160. end

  161. function basics.test_create_drop()
  162.   basics.create_table()
  163.   basics.drop_table()
  164. end

  165. function basics.test_multi_create_drop()
  166.   basics.create_table()
  167.   basics.drop_table()
  168.   basics.create_table()
  169.   basics.drop_table()
  170. end

  171. function basics.test_insert()
  172.   basics.create_table()
  173.   basics.insert(1, "Hello World")
  174.   basics.insert(2, "Hello Lua")
  175.   basics.insert(3, "Hello sqlite3")
  176. end

  177. function basics.test_update()
  178.   basics.create_table()
  179.   basics.insert(1, "Hello Home")
  180.   basics.insert(2, "Hello Lua")
  181.   basics.update(1, "Hello World")
  182. end


  183. ---------------------------------
  184. -- Statement Column Info Tests --
  185. ---------------------------------

  186. lunit_wrap("Column Info Test", function()
  187.   local db = assert_userdata( sqlite3.open_memory() )
  188.   assert_number( db:exec("CREATE TABLE test (id INTEGER, name TEXT)") )
  189.   local stmt = assert_userdata( db:prepare("SELECT * FROM test") )

  190.   assert_equal(2, stmt:columns(), "Wrong number of columns." )

  191.   local names = assert_table( stmt:get_names() )
  192.   assert_equal(2, #(names), "Wrong number of names.")
  193.   assert_equal("id", names[1] )
  194.   assert_equal("name", names[2] )

  195.   local types = assert_table( stmt:get_types() )
  196.   assert_equal(2, #(types), "Wrong number of declaration types.")
  197.   assert_equal("INTEGER", types[1] )
  198.   assert_equal("TEXT", types[2] )

  199.   assert_equal( sqlite3.OK, stmt:finalize() )
  200.   assert_equal( sqlite3.OK, db:close() )
  201. end)



  202. ---------------------
  203. -- Statement Tests --
  204. ---------------------

  205. st = lunit_TestCase("Statement Tests")

  206. function st.setup()
  207.   st.db = assert( sqlite3.open_memory() )
  208.   assert_equal( sqlite3.OK, st.db:exec("CREATE TABLE test (id, name)") )
  209.   assert_equal( sqlite3.OK, st.db:exec("INSERT INTO test VALUES (1, 'Hello World')") )
  210.   assert_equal( sqlite3.OK, st.db:exec("INSERT INTO test VALUES (2, 'Hello Lua')") )
  211.   assert_equal( sqlite3.OK, st.db:exec("INSERT INTO test VALUES (3, 'Hello sqlite3')") )
  212. end

  213. function st.teardown()
  214.   assert_equal( sqlite3.OK, st.db:close() )
  215. end

  216. function st.check_content(expected)
  217.   local stmt = assert( st.db:prepare("SELECT * FROM test ORDER BY id") )
  218.   local i = 0
  219.   for row in stmt:rows() do
  220.     i = i + 1
  221.     assert( i <= #(expected), "Too many rows." )
  222.     assert_equal(2, #(row), "Two result column expected.")
  223.     assert_equal(i, row[1], "Wrong 'id'.")
  224.     assert_equal(expected[i], row[2], "Wrong 'name'.")
  225.   end
  226.   assert_equal( #(expected), i, "Too few rows." )
  227.   assert_number( stmt:finalize() )
  228. end

  229. function st.test_setup()
  230.   assert_pass(function() st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3" } end)
  231.   assert_error(function() st.check_content{ "Hello World", "Hello Lua" } end)
  232.   assert_error(function() st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "To much" } end)
  233.   assert_error(function() st.check_content{ "Hello World", "Hello Lua", "Wrong" } end)
  234.   assert_error(function() st.check_content{ "Hello World", "Wrong", "Hello sqlite3" } end)
  235.   assert_error(function() st.check_content{ "Wrong", "Hello Lua", "Hello sqlite3" } end)
  236. end

  237. function st.test_questionmark_args()
  238.   local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (?, ?)") )
  239.   assert_number( stmt:bind_values(0, "Test") )
  240.   assert_error(function() stmt:bind_values("To few") end)
  241.   assert_error(function() stmt:bind_values(0, "Test", "To many") end)
  242. end

  243. function st.test_questionmark()
  244.   local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (?, ?)") )
  245.   assert_number( stmt:bind_values(4, "Good morning") )
  246.   assert_number( stmt:step() )
  247.   assert_number( stmt:reset() )
  248.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" }
  249.   assert_number( stmt:bind_values(5, "Foo Bar") )
  250.   assert_number( stmt:step() )
  251.   assert_number( stmt:reset() )
  252.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" }
  253.   assert_number( stmt:finalize() )
  254. end

  255. --[===[
  256. function st.test_questionmark_multi()
  257.   local stmt = assert_userdata( st.db:prepare([[
  258.     INSERT INTO test VALUES (?, ?); INSERT INTO test VALUES (?, ?) ]]))
  259.   assert( stmt:bind_values(5, "Foo Bar", 4, "Good morning") )
  260.   assert_number( stmt:step() )
  261.   assert_number( stmt:reset() )
  262.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" }
  263.   assert_number( stmt:finalize() )
  264. end
  265. ]===]

  266. function st.test_identifiers()
  267.   local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, :name)") )
  268.   assert_number( stmt:bind_values(4, "Good morning") )
  269.   assert_number( stmt:step() )
  270.   assert_number( stmt:reset() )
  271.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" }
  272.   assert_number( stmt:bind_values(5, "Foo Bar") )
  273.   assert_number( stmt:step() )
  274.   assert_number( stmt:reset() )
  275.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" }
  276.   assert_number( stmt:finalize() )
  277. end

  278. --[===[
  279. function st.test_identifiers_multi()
  280.   local stmt = assert_table( st.db:prepare([[
  281.     INSERT INTO test VALUES (:id1, :name1); INSERT INTO test VALUES (:id2, :name2) ]]))
  282.   assert( stmt:bind_values(5, "Foo Bar", 4, "Good morning") )
  283.   assert( stmt:exec() )
  284.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" }
  285. end
  286. ]===]

  287. function st.test_identifiers_names()
  288.   --local stmt = assert_userdata( st.db:prepare({"name", "id"}, "INSERT INTO test VALUES (:id, $name)") )
  289.   local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, $name)") )
  290.   assert_number( stmt:bind_names({name="Good morning", id=4}) )
  291.   assert_number( stmt:step() )
  292.   assert_number( stmt:reset() )
  293.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" }
  294.   assert_number( stmt:bind_names({name="Foo Bar", id=5}) )
  295.   assert_number( stmt:step() )
  296.   assert_number( stmt:reset() )
  297.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" }
  298.   assert_number( stmt:finalize() )
  299. end

  300. --[===[
  301. function st:test_identifiers_multi_names()
  302.   local stmt = assert_table( st.db:prepare( {"name", "id1", "id2"},[[
  303.     INSERT INTO test VALUES (:id1, $name); INSERT INTO test VALUES ($id2, :name) ]]))
  304.   assert( stmt:bind_values("Hoho", 4, 5) )
  305.   assert( stmt:exec() )
  306.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" }
  307. end
  308. ]===]

  309. function st.test_colon_identifiers_names()
  310.   local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, :name)") )
  311.   assert_number( stmt:bind_names({name="Good morning", id=4}) )
  312.   assert_number( stmt:step() )
  313.   assert_number( stmt:reset() )
  314.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" }
  315.   assert_number( stmt:bind_names({name="Foo Bar", id=5}) )
  316.   assert_number( stmt:step() )
  317.   assert_number( stmt:reset() )
  318.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" }
  319.   assert_number( stmt:finalize() )
  320. end

  321. --[===[
  322. function st:test_colon_identifiers_multi_names()
  323.   local stmt = assert_table( st.db:prepare( {":name", ":id1", ":id2"},[[
  324.     INSERT INTO test VALUES (:id1, $name); INSERT INTO test VALUES ($id2, :name) ]]))
  325.   assert( stmt:bind_values("Hoho", 4, 5) )
  326.   assert( stmt:exec() )
  327.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" }
  328. end


  329. function st.test_dollar_identifiers_names()
  330.   local stmt = assert_table( st.db:prepare({"$name", "$id"}, "INSERT INTO test VALUES (:id, $name)") )
  331.   assert_table( stmt:bind_values("Good morning", 4) )
  332.   assert_table( stmt:exec() )
  333.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning" }
  334.   assert_table( stmt:bind_values("Foo Bar", 5) )
  335.   assert_table( stmt:exec() )
  336.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Good morning", "Foo Bar" }
  337. end

  338. function st.test_dollar_identifiers_multi_names()
  339.   local stmt = assert_table( st.db:prepare( {"$name", "$id1", "$id2"},[[
  340.     INSERT INTO test VALUES (:id1, $name); INSERT INTO test VALUES ($id2, :name) ]]))
  341.   assert( stmt:bind_values("Hoho", 4, 5) )
  342.   assert( stmt:exec() )
  343.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hoho", "Hoho" }
  344. end
  345. ]===]

  346. function st.test_bind_by_names()
  347.   local stmt = assert_userdata( st.db:prepare("INSERT INTO test VALUES (:id, :name)") )
  348.   local args = { }
  349.   args.id = 5
  350.   args.name = "Hello girls"
  351.   assert( stmt:bind_names(args) )
  352.   assert_number( stmt:step() )
  353.   assert_number( stmt:reset() )
  354.   args.id = 4
  355.   args.name = "Hello boys"
  356.   assert( stmt:bind_names(args) )
  357.   assert_number( stmt:step() )
  358.   assert_number( stmt:reset() )
  359.   st.check_content{ "Hello World", "Hello Lua", "Hello sqlite3", "Hello boys", "Hello girls" }
  360.   assert_number( stmt:finalize() )
  361. end



  362. --------------------------------
  363. -- Tests binding of arguments --
  364. --------------------------------

  365. b = lunit_TestCase("Binding Tests")

  366. function b.setup()
  367.   b.db = assert( sqlite3.open_memory() )
  368.   assert_number( b.db:exec("CREATE TABLE test (id, name, u, v, w, x, y, z)") )
  369. end

  370. function b.teardown()
  371.   assert_number( b.db:close() )
  372. end

  373. function b.test_auto_parameter_names()
  374.   local stmt = assert_userdata( b.db:prepare("INSERT INTO test VALUES(:a, $b, :a2, :b2, $a, :b, $a3, $b3)") )
  375.   local parameters = assert_number( stmt:bind_parameter_count() )
  376.   assert_equal( 8, parameters )
  377.   assert_equal( ":a", stmt:bind_parameter_name(1) )
  378.   assert_equal( "$b", stmt:bind_parameter_name(2) )
  379.   assert_equal( ":a2", stmt:bind_parameter_name(3) )
  380.   assert_equal( ":b2", stmt:bind_parameter_name(4) )
  381.   assert_equal( "$a", stmt:bind_parameter_name(5) )
  382.   assert_equal( ":b", stmt:bind_parameter_name(6) )
  383.   assert_equal( "$a3", stmt:bind_parameter_name(7) )
  384.   assert_equal( "$b3", stmt:bind_parameter_name(8) )
  385. end

  386. function b.test_auto_parameter_names()
  387.   local stmt = assert_userdata( b.db:prepare("INSERT INTO test VALUES($a, $b, $a2, $b2, $a, $b, $a3, $b3)") )
  388.   local parameters = assert_number( stmt:bind_parameter_count() )
  389.   assert_equal( 6, parameters )
  390.   assert_equal( "$a", stmt:bind_parameter_name(1) )
  391.   assert_equal( "$b", stmt:bind_parameter_name(2) )
  392.   assert_equal( "$a2", stmt:bind_parameter_name(3) )
  393.   assert_equal( "$b2", stmt:bind_parameter_name(4) )
  394.   assert_equal( "$a3", stmt:bind_parameter_name(5) )
  395.   assert_equal( "$b3", stmt:bind_parameter_name(6) )
  396. end

  397. function b.test_no_parameter_names_1()
  398.   local stmt = assert_userdata( b.db:prepare([[ SELECT * FROM test ]]))
  399.   local parameters = assert_number( stmt:bind_parameter_count() )
  400.   assert_equal( 0, (parameters) )
  401. end

  402. function b.test_no_parameter_names_2()
  403.   local stmt = assert_userdata( b.db:prepare([[ INSERT INTO test VALUES(?, ?, ?, ?, ?, ?, ?, ?) ]]))
  404.   local parameters = assert_number( stmt:bind_parameter_count() )
  405.   assert_equal( 8, (parameters) )
  406.   assert_nil( stmt:bind_parameter_name(1) )
  407. end




  408. ----------------------------
  409. -- Tests for update_hook --
  410. ----------------------------

  411. uh = lunit_TestCase("Update Hook")

  412. function uh.setup()
  413.   uh.db = assert( sqlite3.open_memory() )
  414.   uh.udtbl = {[sqlite3.INSERT]=0, [sqlite3.UPDATE]=0, [sqlite3.DELETE]=0}
  415.   uh.crtbl = {0, 0}
  416.   assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
  417.     --print("Sqlite Update Hook:", op, dname, tname, rowid)
  418.     ud[op] = ud[op] + 1
  419.   end, uh.udtbl))
  420.   uh.uttblsz = function () local sz = 0; for _,_ in pairs(uh.udtbl) do sz = sz + 1 end return sz end
  421.   -- enable foreign
  422.   assert_number( uh.db:exec("PRAGMA foreign_keys = ON;") )
  423.   assert_number( uh.db:exec("CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR );") )
  424.   assert_number( uh.db:exec("CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );") )
  425.   assert_number( uh.db:exec("CREATE TABLE T2 ( id INTEGER PRIMARY KEY, content VARCHAR );") )
  426. end

  427. function uh.teardown()
  428.   --for k,v in pairs(uh.udtbl) do print(k,v) end
  429.   assert_equal( 3, uh.uttblsz() )
  430.   assert_number( uh.db:close() )
  431. end

  432. function uh.test_insert1()
  433.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  434.   assert_equal( 1, uh.udtbl[sqlite3.INSERT] )
  435. end

  436. function uh.test_insert3()
  437.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  438.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
  439.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
  440.   assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  441.   assert_equal( 0, uh.udtbl[sqlite3.UPDATE] )
  442.   assert_equal( 0, uh.udtbl[sqlite3.DELETE] )

  443. end

  444. function uh.test_insert3_update1()
  445.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  446.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
  447.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
  448.   assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") )
  449.   assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  450.   assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
  451.   assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  452. end

  453. function uh.test_insert3_delete1()
  454.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  455.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
  456.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
  457.   assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") )
  458.   assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  459.   assert_equal( 0, uh.udtbl[sqlite3.UPDATE] )
  460.   assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
  461. end

  462. function uh.test_insert3_update1_delete1()
  463.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
  464.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
  465.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
  466.   assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") )
  467.   assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") )
  468.   assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  469.   assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
  470.   assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
  471. end

  472. function uh.test_insert_select()
  473.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  474.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello Lua');") )
  475.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');") )
  476.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 SELECT * FROM T1;") )
  477.   assert_equal( 6, uh.udtbl[sqlite3.INSERT] )
  478.   assert_equal( 0, uh.udtbl[sqlite3.UPDATE] )
  479.   assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  480. end

  481. function uh.test_trigger_insert()
  482.   assert_equal( sqlite3.OK, uh.db:exec([[
  483.     CREATE TRIGGER after_insert_T1
  484.     AFTER INSERT ON T1
  485.     FOR EACH ROW
  486.     BEGIN
  487.       INSERT INTO T2 VALUES(NEW.id, NEW.content);
  488.     END;
  489.   ]]) )
  490.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  491.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello Lua');") )
  492.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');") )
  493.   assert_equal( 6, uh.udtbl[sqlite3.INSERT] )
  494.   assert_equal( 0, uh.udtbl[sqlite3.UPDATE] )
  495.   assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  496. end

  497. function uh.test_cascade_update()
  498.   assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") )
  499.   assert_equal( sqlite3.OK, uh.db:exec([[
  500.     CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON UPDATE CASCADE, content VARCHAR );
  501.   ]]) )

  502.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") )
  503.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") )
  504.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'c');") )
  505.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'd');") )
  506.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 SELECT * FROM T1;") )
  507.   assert_equal( sqlite3.OK, uh.db:exec("UPDATE T1 SET id = id + 10 WHERE id < 3;") )

  508.   assert_equal( 8, uh.udtbl[sqlite3.INSERT] )
  509.   assert_equal( 4, uh.udtbl[sqlite3.UPDATE] )
  510.   assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  511. end

  512. function uh.test_cascade_update_restrict()
  513.   assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") )
  514.   assert_equal( sqlite3.OK, uh.db:exec([[
  515.     CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON UPDATE RESTRICT, content VARCHAR );
  516.   ]]) )

  517.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") )
  518.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") )
  519.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'c');") )
  520.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'd');") )
  521.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 SELECT * FROM T1;") )
  522.   -- the update_hook with rowid=11 *DOES* get triggered before the RESTRICT constraint is enforced
  523.   assert_equal( sqlite3.CONSTRAINT, uh.db:exec("UPDATE T1 SET id = id + 10 WHERE id < 3;") )

  524.   assert_equal( 8, uh.udtbl[sqlite3.INSERT] )
  525.   assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
  526.   assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  527. end

  528. function uh.test_cascade_delete_restrict()
  529.   assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") )
  530.   assert_equal( sqlite3.OK, uh.db:exec([[
  531.     CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON DELETE RESTRICT, content VARCHAR );
  532.   ]]) )

  533.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") )
  534.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") )
  535.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'c');") )
  536.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'd');") )
  537.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 SELECT * FROM T1;") )
  538.   -- the update_hook with rowid=1 *DOES* get triggered before the RESTRICT constraint is enforced
  539.   assert_equal( sqlite3.CONSTRAINT, uh.db:exec("DELETE FROM T1 WHERE id < 3;") )

  540.   assert_equal( 8, uh.udtbl[sqlite3.INSERT] )
  541.   assert_equal( 0, uh.udtbl[sqlite3.UPDATE] )
  542.   assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
  543. end

  544. function uh.test_fk_violate_insert()
  545.   assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") )
  546.   assert_equal( sqlite3.OK, uh.db:exec([[
  547.     CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR );
  548.   ]]) )

  549.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") )
  550.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") )
  551.   -- no update hook triggered here
  552.   assert_equal( sqlite3.CONSTRAINT, uh.db:exec("INSERT INTO T2 VALUES(99, 'xxx')") )

  553.   assert_equal( 2, uh.udtbl[sqlite3.INSERT] )
  554.   assert_equal( 0, uh.udtbl[sqlite3.UPDATE] )
  555.   assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  556. end

  557. function uh.test_fk_violate_update()
  558.   assert_equal( sqlite3.OK, uh.db:exec("DROP TABLE T2;") )
  559.   assert_equal( sqlite3.OK, uh.db:exec([[
  560.     CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR );
  561.   ]]) )

  562.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") )
  563.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") )
  564.   assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO T2 VALUES (1, 'a');") )
  565.   -- update hook triggered
  566.   assert_equal( sqlite3.CONSTRAINT, uh.db:exec("UPDATE T2 SET id = 99 WHERE id = 1;") )

  567.   assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
  568.   assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
  569.   assert_equal( 0, uh.udtbl[sqlite3.DELETE] )
  570. end

  571. ----------------------------------------------
  572. -- Tests for commit_hook and rollback_hook --
  573. ----------------------------------------------

  574. crh = lunit_TestCase("Commit/Rollback Hook")

  575. function crh.setup()
  576.   crh.db = assert( sqlite3.open_memory() )
  577.   crh.crtbl = {0, 0}
  578.   assert_number( crh.db:exec("PRAGMA foreign_keys = ON;") )
  579.   assert_number( crh.db:exec("CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );") )
  580.   assert_number( crh.db:exec("CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR );") )

  581.   -- reset commit hook return value
  582.   crh.commit_hook_returnvalue = false

  583.   assert_nil( crh.db:commit_hook(function (ud)
  584.     -- print('Commit hook')
  585.     ud[1] = ud[1] + 1;
  586.     return crh.commit_hook_returnvalue
  587.   end, crh.crtbl))

  588.   assert_nil( crh.db:rollback_hook(function (ud)
  589.     -- print('Rollback hook')
  590.     ud[2] = ud[2] + 1
  591.   end, crh.crtbl))
  592. end

  593. function crh.teardown()
  594.   assert_number( crh.db:close() )
  595. end

  596. function crh.test_simple_transaction_commit()
  597.   assert_equal( sqlite3.OK, crh.db:exec("BEGIN;") )
  598.   assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  599.   assert_equal( sqlite3.OK, crh.db:exec("UPDATE T1 SET content = 'Hello Again World' WHERE id = 1;") )
  600.   assert_equal( sqlite3.OK, crh.db:exec("DELETE FROM T1 WHERE id = 2;") )
  601.   assert_equal( sqlite3.OK, crh.db:exec("COMMIT;") )
  602.   assert_equal( 1, crh.crtbl[1] )
  603.   assert_equal( 0, crh.crtbl[2] )
  604. end

  605. function crh.test_simple_transaction_prohibit()
  606.   -- if the commit hook returns anything except false or nil, the commit would turn into rollback
  607.   crh.commit_hook_returnvalue = 1

  608.   assert_equal( sqlite3.OK, crh.db:exec("BEGIN;") )
  609.   assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  610.   assert_equal( sqlite3.OK, crh.db:exec("UPDATE T1 SET content = 'Hello Again World' WHERE id = 1;") )
  611.   assert_equal( sqlite3.OK, crh.db:exec("DELETE FROM T1 WHERE id = 2;") )
  612.   assert_equal( sqlite3.CONSTRAINT, crh.db:exec("COMMIT;") )
  613.   -- commit hook gets called and returns 1 triggering a rollback
  614.   assert_equal( 1, crh.crtbl[1] )
  615.   assert_equal( 1, crh.crtbl[2] )
  616. end

  617. function crh.test_simple_transaction_rollback()
  618.   assert_equal( sqlite3.OK, crh.db:exec("BEGIN;") )
  619.   assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  620.   assert_equal( sqlite3.OK, crh.db:exec("UPDATE T1 SET content = 'Hello Again World' WHERE id = 1;") )
  621.   assert_equal( sqlite3.OK, crh.db:exec("DELETE FROM T1 WHERE id = 2;") )
  622.   assert_equal( sqlite3.OK, crh.db:exec("ROLLBACK;") )
  623.   assert_equal( 0, crh.crtbl[1] )
  624.   assert_equal( 1, crh.crtbl[2] )
  625. end

  626. function crh.test_statement_level_transaction()
  627.   assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  628.   assert_equal( sqlite3.OK, crh.db:exec("UPDATE T1 SET content = 'Hello Again World' WHERE id = 1;") )
  629.   assert_equal( sqlite3.OK, crh.db:exec("DELETE FROM T1 WHERE id = 2;") )
  630.   assert_equal( 3, crh.crtbl[1] )
  631.   assert_equal( 0, crh.crtbl[2] )
  632. end

  633. function crh.test_statement_level_fk_violate_insert()
  634.   assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'a');") )
  635.   assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'b');") )
  636.   -- implicit statement-level transaction rollback + error
  637.   assert_equal( sqlite3.CONSTRAINT, crh.db:exec("INSERT INTO T2 VALUES(99, 'xxx')") )
  638.   assert_equal( 2, crh.crtbl[1] )
  639.   assert_equal( 1, crh.crtbl[2] )
  640. end

  641. function crh.test_transaction_fk_violate_update()
  642.   assert_equal( sqlite3.OK, crh.db:exec("BEGIN;") )
  643.   assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  644.   assert_equal( sqlite3.OK, crh.db:exec("INSERT INTO T2 VALUES (1, 'xyz');") )
  645.   -- Doesn't trigger rollback hook because the implicit update statement transaction
  646.   -- is nested inside our explicit transaction. However we *do* get an error.
  647.   assert_equal( sqlite3.CONSTRAINT, crh.db:exec("UPDATE T2 SET id = 99 WHERE id = 1;") )
  648.   -- rollback explicitly
  649.   assert_equal( sqlite3.OK, crh.db:exec("ROLLBACK;") )

  650.   assert_equal( 0, crh.crtbl[1] )
  651.   assert_equal( 1, crh.crtbl[2] )
  652. end

  653. function crh.test_savepoint_nested_commit()
  654.   assert_equal( sqlite3.OK, crh.db:exec("SAVEPOINT S1;") )
  655.   assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  656.   assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello Lua');") )
  657.   assert_equal( sqlite3.OK, crh.db:exec(" SAVEPOINT S2;") )
  658.   assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');") )
  659.   -- nested transactions don't trigger commit/rollback hooks
  660.   assert_equal( sqlite3.OK, crh.db:exec(" RELEASE S2;") )
  661.   assert_equal( sqlite3.OK, crh.db:exec("RELEASE S1;") )
  662.   assert_equal( 1, crh.crtbl[1] )
  663.   assert_equal( 0, crh.crtbl[2] )
  664. end

  665. function crh.test_savepoint_nested_rollback()
  666.   assert_equal( sqlite3.OK, crh.db:exec("SAVEPOINT S1;") )
  667.   assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello World');") )
  668.   assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello Lua');") )
  669.   assert_equal( sqlite3.OK, crh.db:exec(" SAVEPOINT S2;") )
  670.   assert_equal( sqlite3.OK, crh.db:exec(" INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');") )
  671.   -- nested transactions don't trigger commit/rollback hooks
  672.   assert_equal( sqlite3.OK, crh.db:exec(" ROLLBACK TO S2;") )
  673.   assert_equal( sqlite3.OK, crh.db:exec("RELEASE S1;") )
  674.   assert_equal( 1, crh.crtbl[1] )
  675.   assert_equal( 0, crh.crtbl[2] )
  676. end


  677. --------------------------------------------
  678. -- Tests loop break and statement reusage --
  679. --------------------------------------------



  680. ----------------------------
  681. -- Test for bugs reported --
  682. ----------------------------

  683. bug = lunit_TestCase("Bug-Report Tests")

  684. function bug.setup()
  685.   bug.db = assert( sqlite3.open_memory() )
  686. end

  687. function bug.teardown()
  688.   assert_number( bug.db:close() )
  689. end

  690. --[===[
  691. function bug.test_1()
  692.   bug.db:exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)")

  693.   local query = assert_userdata( bug.db:prepare("SELECT id FROM test WHERE value=?") )

  694.   assert_table ( query:bind_values("1") )
  695.   assert_nil ( query:first_cols() )
  696.   assert_table ( query:bind_values("2") )
  697.   assert_nil ( query:first_cols() )
  698. end
  699. ]===]

  700. function bug.test_nils() -- appeared in lua-5.1 (holes in arrays)
  701.   local function check(arg1, arg2, arg3, arg4, arg5)
  702.     assert_equal(1, arg1)
  703.     assert_equal(2, arg2)
  704.     assert_nil(arg3)
  705.     assert_equal(4, arg4)
  706.     assert_nil(arg5)
  707.   end

  708.   bug.db:create_function("test_nils", 5, function(arg1, arg2, arg3, arg4, arg5)
  709.     check(arg1, arg2, arg3, arg4, arg5)
  710.   end, {})

  711.   assert_number( bug.db:exec([[ SELECT test_nils(1, 2, NULL, 4, NULL) ]]) )

  712.   for arg1, arg2, arg3, arg4, arg5 in bug.db:urows([[ SELECT 1, 2, NULL, 4, NULL ]])
  713.   do check(arg1, arg2, arg3, arg4, arg5)
  714.   end

  715.   for row in bug.db:rows([[ SELECT 1, 2, NULL, 4, NULL ]])
  716.   do assert_table( row )
  717.      check(row[1], row[2], row[3], row[4], row[5])
  718.   end
  719. end

  720. ----------------------------
  721. -- Test for collation fun --
  722. ----------------------------

  723. colla = lunit_TestCase("Collation Tests")

  724. function colla.setup()
  725.     local function collate(s1,s2)
  726.         -- if p then print("collation callback: ",s1,s2) end
  727.         s1=s1:lower()
  728.         s2=s2:lower()
  729.         if s1==s2 then return 0
  730.         elseif s1<s2 then return -1
  731.         else return 1 end
  732.     end
  733.     colla.db = assert( sqlite3.open_memory() )
  734.     assert_nil(colla.db:create_collation('CINSENS',collate))
  735.     colla.db:exec[[
  736.       CREATE TABLE test(id INTEGER PRIMARY KEY,content COLLATE CINSENS);
  737.       INSERT INTO test VALUES(NULL,'hello world');
  738.       INSERT INTO test VALUES(NULL,'Buenos dias');
  739.       INSERT INTO test VALUES(NULL,'HELLO WORLD');
  740.       INSERT INTO test VALUES(NULL,'Guten Tag');
  741.       INSERT INTO test VALUES(NULL,'HeLlO WoRlD');
  742.       INSERT INTO test VALUES(NULL,'Bye for now');
  743.     ]]
  744. end

  745. function colla.teardown()
  746.   assert_number( colla.db:close() )
  747. end

  748. function colla.test()
  749.     --for row in db:nrows('SELECT * FROM test') do
  750.     -- print(row.id,row.content)
  751.     --end
  752.     local n = 0
  753.     for row in colla.db:nrows('SELECT * FROM test WHERE content="hElLo wOrLd"

  

阅读(3997) | 评论(0) | 转发(0) |
0

上一篇:sqlite3 入门

下一篇:lsqlite3 test.lua

给主人留下些什么吧!~~