LuaSQLite3

Check-in [29a180ad4e]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Added tests and example contributed by Dmitry Pashkevich
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | fsl_9 | trunk
Files: files | file ages | folders
SHA1: 29a180ad4e88a053a3020798e86345a4ddbf9ed8
User & Date: e 2013-04-05 17:20:28
References
2013-04-05
17:23 Closed ticket [c7b818d353]: Support for sqlite3_update_hook? plus 3 other changes artifact: cb51c59b4a user: e
Context
2013-04-08
16:52
Updated example and test files from Dmitry check-in: 5282faf755 user: e tags: trunk
2013-04-05
17:20
Added tests and example contributed by Dmitry Pashkevich check-in: 29a180ad4e user: e tags: fsl_9, trunk
2013-04-01
18:02
Add commit_hook and rollback_hook to complement update_hook. check-in: bb4d13eba2 user: e tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added examples/update_hook_advanced.lua.

            1  +
            2  +local sqlite3 = require("lsqlite3")
            3  +local db
            4  +
            5  +optbl = { [sqlite3.UPDATE] = "UPDATE";
            6  +          [sqlite3.INSERT] = "INSERT";
            7  +          [sqlite3.DELETE] = "DELETE"
            8  +        }
            9  +setmetatable(optbl,
           10  +	{__index=function(t,n) return string.format("Unknown op %d",n) end})
           11  +
           12  +
           13  +function update_hook(ud, op, dname, tname, rowid)
           14  +  print("Sqlite Update Hook:", optbl[op], dname, tname, rowid)
           15  +end
           16  +
           17  +local tests = {}
           18  +
           19  +-- runs specified test with all necessary setup
           20  +-- @param string name
           21  +function run_test(name)
           22  +  -- banner
           23  +    print(string.format([[
           24  +
           25  +
           26  +  ==========================
           27  +   %s
           28  +  ==========================
           29  +  ]], name))
           30  +
           31  +  -- setup
           32  +  db = sqlite3.open_memory()
           33  +  local udtbl = {0, 0, 0}
           34  +  db:update_hook(update_hook, udtbl)
           35  +
           36  +  -- run test
           37  +  tests[name]()
           38  +
           39  +  -- destroy
           40  +  db:close()
           41  +end
           42  +
           43  +function tests.insert_select()
           44  +  db:exec[[
           45  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
           46  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY, content VARCHAR );
           47  +
           48  +    INSERT INTO T1 VALUES (NULL, 'Hello World');
           49  +    INSERT INTO T1 VALUES (NULL, 'Hello Lua');
           50  +    INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');
           51  +
           52  +    INSERT INTO T2 SELECT * FROM T1;
           53  +  ]]
           54  +
           55  +  print("\nT1 contents:")
           56  +  for row in db:nrows("SELECT * FROM T1") do
           57  +    print(row.id, row.content)
           58  +  end
           59  +
           60  +  print("\nT2 contents:")
           61  +  for row in db:nrows("SELECT * FROM T2") do
           62  +    print(row.id, row.content)
           63  +  end
           64  +end
           65  +
           66  +function tests.trigger_insert()
           67  +  db:exec[[
           68  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
           69  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY, content VARCHAR );
           70  +
           71  +    CREATE TRIGGER after_insert_T1
           72  +    AFTER INSERT ON T1
           73  +    FOR EACH ROW
           74  +    BEGIN
           75  +      INSERT INTO T2 VALUES(NEW.id, NEW.content);
           76  +    END;
           77  +
           78  +    INSERT INTO T1 VALUES (NULL, 'Hello World');
           79  +    INSERT INTO T1 VALUES (NULL, 'Hello Lua');
           80  +    INSERT INTO T1 VALUES (NULL, 'Hello Sqlite3');
           81  +  ]]
           82  +
           83  +  print("\nT1 contents:")
           84  +  for row in db:nrows("SELECT * FROM T1") do
           85  +    print(row.id, row.content)
           86  +  end
           87  +
           88  +  print("\nT2 contents:")
           89  +  for row in db:nrows("SELECT * FROM T2") do
           90  +    print(row.id, row.content)
           91  +  end
           92  +end
           93  +
           94  +function tests.cascade_delete()
           95  +  db:exec[[
           96  +    PRAGMA foreign_keys = ON;
           97  +
           98  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
           99  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON DELETE CASCADE, content VARCHAR );
          100  +    CREATE TABLE T3 ( id INTEGER PRIMARY KEY REFERENCES T2 ON DELETE CASCADE, content VARCHAR );
          101  +
          102  +    INSERT INTO T1 VALUES (NULL, 'a');
          103  +    INSERT INTO T1 VALUES (NULL, 'b');
          104  +    INSERT INTO T1 VALUES (NULL, 'c');
          105  +    INSERT INTO T1 VALUES (NULL, 'd');
          106  +
          107  +    INSERT INTO T2 SELECT * FROM T1;
          108  +    INSERT INTO T3 SELECT * FROM T2;
          109  +
          110  +    DELETE FROM T1 WHERE id < 3;
          111  +  ]]
          112  +
          113  +  print("\nT1 contents:")
          114  +  for row in db:nrows("SELECT * FROM T1") do
          115  +    print(row.id, row.content)
          116  +  end
          117  +
          118  +  print("\nT2 contents:")
          119  +  for row in db:nrows("SELECT * FROM T2") do
          120  +    print(row.id, row.content)
          121  +  end
          122  +
          123  +  print("\nT3 contents:")
          124  +  for row in db:nrows("SELECT * FROM T3") do
          125  +    print(row.id, row.content)
          126  +  end
          127  +end
          128  +
          129  +function tests.cascade_update_update()
          130  +  db:exec[[
          131  +    PRAGMA foreign_keys = ON;
          132  +
          133  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
          134  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON UPDATE CASCADE, content VARCHAR );
          135  +    CREATE TABLE T3 ( id INTEGER PRIMARY KEY REFERENCES T2 ON UPDATE CASCADE, content VARCHAR );
          136  +
          137  +    INSERT INTO T1 VALUES (NULL, 'a');
          138  +    INSERT INTO T1 VALUES (NULL, 'b');
          139  +    INSERT INTO T1 VALUES (NULL, 'c');
          140  +    INSERT INTO T1 VALUES (NULL, 'd');
          141  +
          142  +    INSERT INTO T2 SELECT * FROM T1;
          143  +    INSERT INTO T3 SELECT * FROM T2;
          144  +
          145  +    UPDATE T1 SET id = id + 10 WHERE id < 3;
          146  +  ]]
          147  +
          148  +  print("\nT1 contents:")
          149  +  for row in db:nrows("SELECT * FROM T1") do
          150  +    print(row.id, row.content)
          151  +  end
          152  +
          153  +  print("\nT2 contents:")
          154  +  for row in db:nrows("SELECT * FROM T2") do
          155  +    print(row.id, row.content)
          156  +  end
          157  +
          158  +  print("\nT3 contents:")
          159  +  for row in db:nrows("SELECT * FROM T3") do
          160  +    print(row.id, row.content)
          161  +  end
          162  +end
          163  +
          164  +-- hook "anomaly"
          165  +-- implicit one-statement transaction rollback demonstration
          166  +function tests.cascade_update_restrict()
          167  +  db:exec[[
          168  +    PRAGMA foreign_keys = ON;
          169  +
          170  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
          171  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON UPDATE RESTRICT, content VARCHAR );
          172  +    CREATE TABLE T3 ( id INTEGER PRIMARY KEY REFERENCES T2 ON UPDATE RESTRICT, content VARCHAR );
          173  +
          174  +    INSERT INTO T1 VALUES (NULL, 'a');
          175  +    INSERT INTO T1 VALUES (NULL, 'b');
          176  +    INSERT INTO T1 VALUES (NULL, 'c');
          177  +    INSERT INTO T1 VALUES (NULL, 'd');
          178  +
          179  +    INSERT INTO T2 SELECT * FROM T1;
          180  +    INSERT INTO T3 SELECT * FROM T2;
          181  +
          182  +    -- this update gets reverted but the update_hook with rowid=11 *DOES* get triggered
          183  +    UPDATE T1 SET id = id + 10 WHERE id < 3;
          184  +  ]]
          185  +
          186  +  print("\nT1 contents:")
          187  +  for row in db:nrows("SELECT * FROM T1") do
          188  +    print(row.id, row.content)
          189  +  end
          190  +
          191  +  print("\nT2 contents:")
          192  +  for row in db:nrows("SELECT * FROM T2") do
          193  +    print(row.id, row.content)
          194  +  end
          195  +
          196  +  print("\nT3 contents:")
          197  +  for row in db:nrows("SELECT * FROM T3") do
          198  +    print(row.id, row.content)
          199  +  end
          200  +end
          201  +
          202  +-- hook "anomaly"
          203  +-- case is analogous to cascade_update_restrict
          204  +function tests.cascade_delete_restrict()
          205  +  db:exec[[
          206  +    PRAGMA foreign_keys = ON;
          207  +
          208  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
          209  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1 ON DELETE RESTRICT, content VARCHAR );
          210  +    CREATE TABLE T3 ( id INTEGER PRIMARY KEY REFERENCES T2 ON DELETE RESTRICT, content VARCHAR );
          211  +
          212  +    INSERT INTO T1 VALUES (NULL, 'a');
          213  +    INSERT INTO T1 VALUES (NULL, 'b');
          214  +    INSERT INTO T1 VALUES (NULL, 'c');
          215  +    INSERT INTO T1 VALUES (NULL, 'd');
          216  +
          217  +    INSERT INTO T2 SELECT * FROM T1;
          218  +    INSERT INTO T3 SELECT * FROM T2;
          219  +
          220  +    DELETE FROM T1 WHERE id < 3;
          221  +  ]]
          222  +
          223  +  print("\nT1 contents:")
          224  +  for row in db:nrows("SELECT * FROM T1") do
          225  +    print(row.id, row.content)
          226  +  end
          227  +
          228  +  print("\nT2 contents:")
          229  +  for row in db:nrows("SELECT * FROM T2") do
          230  +    print(row.id, row.content)
          231  +  end
          232  +
          233  +  print("\nT3 contents:")
          234  +  for row in db:nrows("SELECT * FROM T3") do
          235  +    print(row.id, row.content)
          236  +  end
          237  +end
          238  +
          239  +-- no anomalies here
          240  +function tests.fk_violate_insert()
          241  +  db:exec[[
          242  +    PRAGMA foreign_keys = ON;
          243  +
          244  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
          245  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR);
          246  +
          247  +    INSERT INTO T1 VALUES (NULL, 'a');
          248  +    INSERT INTO T1 VALUES (NULL, 'b');
          249  +
          250  +    INSERT INTO T2 VALUES(99, 'xxx');
          251  +  ]]
          252  +
          253  +  print("\nT1 contents:")
          254  +  for row in db:nrows("SELECT * FROM T1") do
          255  +    print(row.id, row.content)
          256  +  end
          257  +
          258  +  print("\nT2 contents:")
          259  +  for row in db:nrows("SELECT * FROM T2") do
          260  +    print(row.id, row.content)
          261  +  end
          262  +end
          263  +
          264  +-- hook "anomaly"
          265  +function tests.fk_violate_update()
          266  +  db:exec[[
          267  +    PRAGMA foreign_keys = ON;
          268  +
          269  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
          270  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY REFERENCES T1, content VARCHAR);
          271  +
          272  +    INSERT INTO T1 VALUES (NULL, 'a');
          273  +    INSERT INTO T1 VALUES (NULL, 'b');
          274  +    INSERT INTO T2 VALUES(1, 'a');
          275  +
          276  +    -- update doesn't succeed but we get a hook for tuple with rowid=99 in T2
          277  +    UPDATE T2 SET id = 99 WHERE id = 1;
          278  +  ]]
          279  +
          280  +  print("\nT1 contents:")
          281  +  for row in db:nrows("SELECT * FROM T1") do
          282  +    print(row.id, row.content)
          283  +  end
          284  +
          285  +  print("\nT2 contents:")
          286  +  for row in db:nrows("SELECT * FROM T2") do
          287  +    print(row.id, row.content)
          288  +  end
          289  +end
          290  +
          291  +function tests.cascade_update_setnull()
          292  +  db:exec[[
          293  +    PRAGMA foreign_keys = ON;
          294  +
          295  +    CREATE TABLE T1 ( id INTEGER PRIMARY KEY, content VARCHAR );
          296  +    CREATE TABLE T2 ( id INTEGER PRIMARY KEY, content INTEGER REFERENCES T1(id) ON UPDATE SET NULL);
          297  +
          298  +    INSERT INTO T1 VALUES (NULL, 'a');
          299  +    INSERT INTO T1 VALUES (NULL, 'b');
          300  +    INSERT INTO T1 VALUES (NULL, 'c');
          301  +    INSERT INTO T1 VALUES (NULL, 'd');
          302  +
          303  +    INSERT INTO T2 SELECT NULL, id FROM T1;
          304  +
          305  +    UPDATE T1 SET id = id + 10 WHERE id < 3;
          306  +  ]]
          307  +
          308  +  print("\nT1 contents:")
          309  +  for row in db:nrows("SELECT * FROM T1") do
          310  +    print(row.id, row.content)
          311  +  end
          312  +
          313  +  print("\nT2 contents:")
          314  +  for row in db:nrows("SELECT * FROM T2") do
          315  +    print(row.id, row.content)
          316  +  end
          317  +end
          318  +
          319  +
          320  +-- run_test('fk_violate_update')
          321  +
          322  +for k,v in pairs(tests) do
          323  +    run_test(k)
          324  +end

Changes to test/tests-sqlite3.lua.

     1      1   
     2      2   --[[--------------------------------------------------------------------------
     3      3   
     4      4       Author: Michael Roth <mroth@nessie.de>
     5      5   
     6      6       Copyright (c) 2004, 2005 Michael Roth <mroth@nessie.de>
     7      7   
     8         -    Permission is hereby granted, free of charge, to any person 
            8  +    Permission is hereby granted, free of charge, to any person
     9      9       obtaining a copy of this software and associated documentation
    10     10       files (the "Software"), to deal in the Software without restriction,
    11     11       including without limitation the rights to use, copy, modify, merge,
    12     12       publish, distribute, sublicense, and/or sell copies of the Software,
    13     13       and to permit persons to whom the Software is furnished to do so,
    14     14       subject to the following conditions:
    15     15   
    16         -    The above copyright notice and this permission notice shall be 
           16  +    The above copyright notice and this permission notice shall be
    17     17       included in all copies or substantial portions of the Software.
    18     18   
    19     19       THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
    20     20       EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
    21     21       MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
    22     22       IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY
    23     23       CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT,
................................................................................
    34     34   
    35     35   local os = os
    36     36   
    37     37   local lunit = require "lunitx"
    38     38   
    39     39   local tests_sqlite3
    40     40   
    41         -if _VERSION >= 'Lua 5.2' then 
           41  +if _VERSION >= 'Lua 5.2' then
    42     42   
    43     43       tests_sqlite3 = lunit.module('tests-sqlite3','seeall')
    44     44       _ENV = tests_sqlite3
    45         -    
           45  +
    46     46   else
    47     47   
    48     48       module('tests_sqlite3', lunit.testcase, package.seeall)
    49     49       tests_sqlite3 = _M
    50         -    
           50  +
    51     51   end
    52     52   
    53     53   -- compat
    54     54   
    55     55   function lunit_wrap (name, fcn)
    56     56      tests_sqlite3['test_o_'..name] = fcn
    57     57   end
................................................................................
   238    238   -- Statement Column Info Tests --
   239    239   ---------------------------------
   240    240   
   241    241   lunit_wrap("Column Info Test", function()
   242    242     local db = assert_userdata( sqlite3.open_memory() )
   243    243     assert_number( db:exec("CREATE TABLE test (id INTEGER, name TEXT)") )
   244    244     local stmt = assert_userdata( db:prepare("SELECT * FROM test") )
   245         -  
          245  +
   246    246     assert_equal(2, stmt:columns(), "Wrong number of columns." )
   247         -  
          247  +
   248    248     local names = assert_table( stmt:get_names() )
   249    249     assert_equal(2, #(names), "Wrong number of names.")
   250    250     assert_equal("id", names[1] )
   251    251     assert_equal("name", names[2] )
   252         -  
          252  +
   253    253     local types = assert_table( stmt:get_types() )
   254    254     assert_equal(2, #(types), "Wrong number of declaration types.")
   255    255     assert_equal("INTEGER", types[1] )
   256    256     assert_equal("TEXT", types[2] )
   257         -  
          257  +
   258    258     assert_equal( sqlite3.OK, stmt:finalize() )
   259    259     assert_equal( sqlite3.OK, db:close() )
   260    260   end)
   261    261   
   262    262   
   263    263   
   264    264   ---------------------
................................................................................
   504    504   ----------------------------
   505    505   
   506    506   uh = lunit_TestCase("Update Hook")
   507    507   
   508    508   function uh.setup()
   509    509     uh.db = assert( sqlite3.open_memory() )
   510    510     uh.udtbl = {[sqlite3.INSERT]=0, [sqlite3.UPDATE]=0, [sqlite3.DELETE]=0}
   511         -  uh.crtbl = {0, 0}
   512    511     uh.uttblsz = function () local sz = 0; for _,_ in pairs(uh.udtbl) do sz = sz + 1 end return sz end
   513    512     assert_number( uh.db:exec("CREATE TABLE test ( id INTEGER PRIMARY KEY, content VARCHAR );") )
   514    513   end
   515    514   
   516    515   function uh.teardown()
   517    516     assert_number( uh.db:close() )
   518    517   end
................................................................................
   590    589     assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
   591    590     assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
   592    591     assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
   593    592     assert_equal( 3, uh.uttblsz() )
   594    593   end
   595    594   
   596    595   
   597         -function uh.test_xinsert3update1delete1()
   598         -  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
   599         -    ud[op] = ud[op] + 1
   600         -  end, uh.udtbl))
   601         -  assert_nil( uh.db:commit_hook(function (ud) ud[1] = ud[1] + 1; return false end, uh.crtbl))
   602         -  assert_nil( uh.db:rollback_hook(function (ud) ud[2] = ud[2] + 1 end, uh.crtbl))
   603         -  assert_equal( sqlite3.OK, uh.db:exec("BEGIN;") )
   604         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
   605         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
   606         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
   607         -  assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") )
   608         -  assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") )
   609         -  assert_equal( sqlite3.OK, uh.db:exec("COMMIT;") )
   610         -  --for k,v in pairs(uh.udtbl) do print(k,v) end
   611         -  assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
   612         -  assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
   613         -  assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
   614         -  assert_equal( 3, uh.uttblsz() )
   615         -  assert_equal( 1, uh.crtbl[1] )
   616         -  assert_equal( 0, uh.crtbl[2] )
   617         -end
   618         -
   619         -function uh.test_yinsert3update1delete1()
   620         -  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
   621         -    ud[op] = ud[op] + 1
   622         -  end, uh.udtbl))
   623         -  assert_nil( uh.db:commit_hook(function (ud) ud[1] = ud[1] + 1; return false end, uh.crtbl))
   624         -  assert_nil( uh.db:rollback_hook(function (ud) ud[2] = ud[2] + 1 end, uh.crtbl))
   625         -  assert_equal( sqlite3.OK, uh.db:exec("BEGIN;") )
   626         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
   627         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
   628         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
   629         -  assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") )
   630         -  assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") )
   631         -  assert_equal( sqlite3.OK, uh.db:exec("ROLLBACK;") )
   632         -  --for k,v in pairs(uh.udtbl) do print(k,v) end
   633         -  assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
   634         -  assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
   635         -  assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
   636         -  assert_equal( 3, uh.uttblsz() )
   637         -  assert_equal( 0, uh.crtbl[1] )
   638         -  assert_equal( 1, uh.crtbl[2] )
   639         -end
   640         -
   641         -function uh.test_zinsert3update1delete1()
   642         -  assert_nil(uh.db:update_hook( function(ud, op, dname, tname, rowid)
   643         -    ud[op] = ud[op] + 1
   644         -  end, uh.udtbl))
   645         -  assert_nil( uh.db:commit_hook(function (ud) ud[1] = ud[1] + 1; return false end, uh.crtbl))
   646         -  assert_nil( uh.db:rollback_hook(function (ud) ud[2] = ud[2] + 1 end, uh.crtbl))
   647         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello World');") )
   648         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Lua');") )
   649         -  assert_equal( sqlite3.OK, uh.db:exec("INSERT INTO test VALUES (NULL, 'Hello Sqlite3');") )
   650         -  assert_equal( sqlite3.OK, uh.db:exec("UPDATE test SET content = 'Hello Again World' WHERE id = 1;") )
   651         -  assert_equal( sqlite3.OK, uh.db:exec("DELETE FROM test WHERE id = 2;") )
   652         -  --for k,v in pairs(uh.udtbl) do print(k,v) end
   653         -  assert_equal( 3, uh.udtbl[sqlite3.INSERT] )
   654         -  assert_equal( 1, uh.udtbl[sqlite3.UPDATE] )
   655         -  assert_equal( 1, uh.udtbl[sqlite3.DELETE] )
   656         -  assert_equal( 3, uh.uttblsz() )
   657         -  assert_equal( 5, uh.crtbl[1] )
   658         -  assert_equal( 0, uh.crtbl[2] )
   659         -end
   660    596   
   661    597   
   662    598   
   663    599   --------------------------------------------
   664    600   -- Tests loop break and statement reusage --
   665    601   --------------------------------------------
   666    602   
................................................................................
   679    615   function bug.teardown()
   680    616     assert_number( bug.db:close() )
   681    617   end
   682    618   
   683    619   --[===[
   684    620   function bug.test_1()
   685    621     bug.db:exec("CREATE TABLE test (id INTEGER PRIMARY KEY, value TEXT)")
   686         -  
          622  +
   687    623     local query = assert_userdata( bug.db:prepare("SELECT id FROM test WHERE value=?") )
   688         -  
          624  +
   689    625     assert_table ( query:bind_values("1") )
   690    626     assert_nil   ( query:first_cols() )
   691    627     assert_table ( query:bind_values("2") )
   692    628     assert_nil   ( query:first_cols() )
   693    629   end
   694    630   ]===]
   695    631   
................................................................................
   697    633     local function check(arg1, arg2, arg3, arg4, arg5)
   698    634       assert_equal(1, arg1)
   699    635       assert_equal(2, arg2)
   700    636       assert_nil(arg3)
   701    637       assert_equal(4, arg4)
   702    638       assert_nil(arg5)
   703    639     end
   704         -  
          640  +
   705    641     bug.db:create_function("test_nils", 5, function(arg1, arg2, arg3, arg4, arg5)
   706    642       check(arg1, arg2, arg3, arg4, arg5)
   707    643     end, {})
   708         -  
          644  +
   709    645     assert_number( bug.db:exec([[ SELECT test_nils(1, 2, NULL, 4, NULL) ]]) )
   710         -  
          646  +
   711    647     for arg1, arg2, arg3, arg4, arg5 in bug.db:urows([[ SELECT 1, 2, NULL, 4, NULL ]])
   712         -  do check(arg1, arg2, arg3, arg4, arg5) 
          648  +  do check(arg1, arg2, arg3, arg4, arg5)
   713    649     end
   714         -  
          650  +
   715    651     for row in bug.db:rows([[ SELECT 1, 2, NULL, 4, NULL ]])
   716         -  do assert_table( row ) 
          652  +  do assert_table( row )
   717    653        check(row[1], row[2], row[3], row[4], row[5])
   718    654     end
   719    655   end
   720    656   
   721    657   ----------------------------
   722    658   -- Test for collation fun --
   723    659   ----------------------------