LuaSQLite3

Check-in [b2441f7aa8]
Login

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

Overview
Comment:Fix issue: auto-gc of db with live statements
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: b2441f7aa8a65a51cad2fc22e10f530f1dbcdc67
User & Date: e 2015-01-05 19:26:56
References
2015-01-05
19:29 Fixed ticket [231e50fced]: Prepared statement do not track db object. plus 5 other changes artifact: a1c520f743 user: e
Context
2015-01-05
20:16
Update wiki doc check-in: 7c2c1f829c user: e tags: trunk
19:26
Fix issue: auto-gc of db with live statements check-in: b2441f7aa8 user: e tags: trunk
02:28
Update HISTORY for 0.9.2 check-in: 8a088f1459 user: e tags: fsl_9u, trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to lsqlite3.c.

1
2
3
4
5
6
7
8
9
10
..
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
...
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
...
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
....
1711
1712
1713
1714
1715
1716
1717
1718
1719
1720
1721
1722
1723
1724
1725
1726
1727
1728
1729
1730
1731
1732
1733
1734
1735
1736
1737
1738
1739
1740
1741
1742
1743
1744
....
1829
1830
1831
1832
1833
1834
1835
1836
1837
1838
1839
1840
1841
1842
1843
1844
1845
1846
1847
1848
1849
1850
1851
1852
1853
1854
1855
1856
1857
1858
1859
1860
1861
/************************************************************************
* lsqlite3                                                              *
* Copyright (C) 2002-2013 Tiago Dionizio, Doug Currie                   *
* All rights reserved.                                                  *
* Author    : Tiago Dionizio <tiago.dionizio@ist.utl.pt>                *
* Author    : Doug Currie <doug.currie@alum.mit.edu>                    *
* Library   : lsqlite3 - a SQLite 3 database binding for Lua 5          *
*                                                                       *
* Permission is hereby granted, free of charge, to any person obtaining *
* a copy of this software and associated documentation files (the       *
................................................................................
#if !defined(SQLITE_OMIT_PROGRESS_CALLBACK)
    #define SQLITE_OMIT_PROGRESS_CALLBACK 0
#endif
#if !defined(LSQLITE_OMIT_UPDATE_HOOK)
    #define LSQLITE_OMIT_UPDATE_HOOK 0
#endif

// #define LSQLITE_USE_LEGACY_PREPARE 1


typedef struct sdb sdb;
typedef struct sdb_vm sdb_vm;
typedef struct sdb_func sdb_func;

/* to use as C user data so i know what function sqlite is calling */
struct sdb_func {
................................................................................
    /* sqlite3_step info */
    int columns;            /* number of columns in result */
    char has_values;        /* true when step succeeds */

    char temp;              /* temporary vm used in db:rows */
};

/* called with sql text on the lua stack */
static sdb_vm *newvm(lua_State *L, sdb *db) {
    sdb_vm *svm = (sdb_vm*)lua_newuserdata(L, sizeof(sdb_vm));

    luaL_getmetatable(L, sqlite_vm_meta);
    lua_setmetatable(L, -2);        /* set metatable */

    svm->db = db;
    svm->columns = 0;
    svm->has_values = 0;
    svm->vm = NULL;
    svm->temp = 0;

    /* add an entry on the database table: svm -> sql text (legacy) OR svm -> true */
    lua_pushlightuserdata(L, db);
    lua_rawget(L, LUA_REGISTRYINDEX);
    lua_pushlightuserdata(L, svm);
#ifdef LSQLITE_USE_LEGACY_PREPARE
    lua_pushvalue(L, -4); /* the sql text */
#else
    lua_pushboolean(L, 1);
#endif
    lua_rawset(L, -3);
    lua_pop(L, 1);

    return svm;
}

static int cleanupvm(lua_State *L, sdb_vm *svm) {
................................................................................

    lua_pushinteger(L, sqlite3_finalize(svm->vm));
    svm->vm = NULL;
    return 1;
}

static int stepvm(lua_State *L, sdb_vm *svm) {
    int result;
#ifdef LSQLITE_USE_LEGACY_PREPARE
    int loop_limit = 3;
    while ( loop_limit-- ) {
        result = sqlite3_step(svm->vm);
        if ( result==SQLITE_ERROR ) {
          result = sqlite3_reset (svm->vm);
        }
        if ( result==SQLITE_SCHEMA ) {
            sqlite3_stmt *vn;
            const char *sql;
            /* recover sql text */
            lua_pushlightuserdata(L, svm->db);
            lua_rawget(L, LUA_REGISTRYINDEX);
            lua_pushlightuserdata(L, svm);
            lua_rawget(L, -2); /* sql text */
            sql = luaL_checkstring(L, -1);
            /* re-prepare */
            result = sqlite3_prepare(svm->db->db, sql, -1, &vn, NULL);
            if (result != SQLITE_OK) break;
            sqlite3_transfer_bindings(svm->vm, vn);
            sqlite3_finalize(svm->vm);
            svm->vm = vn;
            lua_pop(L,2);
        } else {
          break;
        }
    }
#else
    result = sqlite3_step(svm->vm);
//    if ( result==SQLITE_ERROR ) {
//        sqlite3_reset (svm->vm);
//    }
#endif
    return result;
}

static sdb_vm *lsqlite_getvm(lua_State *L, int index) {
    sdb_vm *svm = (sdb_vm*)luaL_checkudata(L, index, sqlite_vm_meta);
    if (svm == NULL) luaL_argerror(L, index, "bad sqlite virtual machine");
    return svm;
}
................................................................................
*/
static int db_prepare(lua_State *L) {
    sdb *db = lsqlite_checkdb(L, 1);
    const char *sql = luaL_checkstring(L, 2);
    int sql_len = lua_strlen(L, 2);
    const char *sqltail;
    sdb_vm *svm;
    lua_settop(L,2); /* sql is on top of stack for call to newvm */
    svm = newvm(L, db);

#ifdef LSQLITE_USE_LEGACY_PREPARE
    if (sqlite3_prepare(db->db, sql, sql_len, &svm->vm, &sqltail) != SQLITE_OK) {
        cleanupvm(L, svm);

        lua_pushnil(L);
        lua_pushinteger(L, sqlite3_errcode(db->db));
        return 2;
    }
#else
    if (sqlite3_prepare_v2(db->db, sql, sql_len, &svm->vm, &sqltail) != SQLITE_OK) {
        lua_pushnil(L);
        lua_pushinteger(L, sqlite3_errcode(db->db));
        if (cleanupvm(L, svm) == 1)
            lua_pop(L, 1); // this should not happen since sqlite3_prepare_v2 will not set ->vm on error
        return 2;
    }
#endif

    /* vm already in the stack */
    lua_pushstring(L, sqltail);
    return 2;
}

static int db_do_next_row(lua_State *L, int packed) {
................................................................................
    return dbvm_do_rows(L, db_next_row);
}

static int db_do_rows(lua_State *L, int(*f)(lua_State *)) {
    sdb *db = lsqlite_checkdb(L, 1);
    const char *sql = luaL_checkstring(L, 2);
    sdb_vm *svm;
    lua_settop(L,2); /* sql is on top of stack for call to newvm */
    svm = newvm(L, db);
    svm->temp = 1;

#ifdef LSQLITE_USE_LEGACY_PREPARE
    if (sqlite3_prepare(db->db, sql, -1, &svm->vm, NULL) != SQLITE_OK) {
        cleanupvm(L, svm);

        lua_pushstring(L, sqlite3_errmsg(svm->db->db));
        lua_error(L);
    }
#else
    if (sqlite3_prepare_v2(db->db, sql, -1, &svm->vm, NULL) != SQLITE_OK) {
        lua_pushstring(L, sqlite3_errmsg(svm->db->db));
        if (cleanupvm(L, svm) == 1)
            lua_pop(L, 1); // this should not happen since sqlite3_prepare_v2 will not set ->vm on error
        lua_error(L);
    }
#endif

    lua_pushcfunction(L, f);
    lua_insert(L, -2);
    return 2;
}

static int db_rows(lua_State *L) {


|







 







<
<







 







|












|



<
|
<
<
<







 







<
<
<
<
|
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<
<







 







|


<
<
<
<
<
<
<
<
<







<







 







|



<
<
<
<
<
<
<
<






<







1
2
3
4
5
6
7
8
9
10
..
61
62
63
64
65
66
67


68
69
70
71
72
73
74
...
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210

211



212
213
214
215
216
217
218
...
232
233
234
235
236
237
238




239






























240
241
242
243
244
245
246
....
1671
1672
1673
1674
1675
1676
1677
1678
1679
1680









1681
1682
1683
1684
1685
1686
1687

1688
1689
1690
1691
1692
1693
1694
....
1779
1780
1781
1782
1783
1784
1785
1786
1787
1788
1789








1790
1791
1792
1793
1794
1795

1796
1797
1798
1799
1800
1801
1802
/************************************************************************
* lsqlite3                                                              *
* Copyright (C) 2002-2015 Tiago Dionizio, Doug Currie                   *
* All rights reserved.                                                  *
* Author    : Tiago Dionizio <tiago.dionizio@ist.utl.pt>                *
* Author    : Doug Currie <doug.currie@alum.mit.edu>                    *
* Library   : lsqlite3 - a SQLite 3 database binding for Lua 5          *
*                                                                       *
* Permission is hereby granted, free of charge, to any person obtaining *
* a copy of this software and associated documentation files (the       *
................................................................................
#if !defined(SQLITE_OMIT_PROGRESS_CALLBACK)
    #define SQLITE_OMIT_PROGRESS_CALLBACK 0
#endif
#if !defined(LSQLITE_OMIT_UPDATE_HOOK)
    #define LSQLITE_OMIT_UPDATE_HOOK 0
#endif




typedef struct sdb sdb;
typedef struct sdb_vm sdb_vm;
typedef struct sdb_func sdb_func;

/* to use as C user data so i know what function sqlite is calling */
struct sdb_func {
................................................................................
    /* sqlite3_step info */
    int columns;            /* number of columns in result */
    char has_values;        /* true when step succeeds */

    char temp;              /* temporary vm used in db:rows */
};

/* called with db,sql text on the lua stack */
static sdb_vm *newvm(lua_State *L, sdb *db) {
    sdb_vm *svm = (sdb_vm*)lua_newuserdata(L, sizeof(sdb_vm));

    luaL_getmetatable(L, sqlite_vm_meta);
    lua_setmetatable(L, -2);        /* set metatable */

    svm->db = db;
    svm->columns = 0;
    svm->has_values = 0;
    svm->vm = NULL;
    svm->temp = 0;

    /* add an entry on the database table: svm -> db to keep db live while svm is live */
    lua_pushlightuserdata(L, db);
    lua_rawget(L, LUA_REGISTRYINDEX);
    lua_pushlightuserdata(L, svm);

    lua_pushvalue(L, -5); /* the db for this vm */



    lua_rawset(L, -3);
    lua_pop(L, 1);

    return svm;
}

static int cleanupvm(lua_State *L, sdb_vm *svm) {
................................................................................

    lua_pushinteger(L, sqlite3_finalize(svm->vm));
    svm->vm = NULL;
    return 1;
}

static int stepvm(lua_State *L, sdb_vm *svm) {




    return sqlite3_step(svm->vm);






























}

static sdb_vm *lsqlite_getvm(lua_State *L, int index) {
    sdb_vm *svm = (sdb_vm*)luaL_checkudata(L, index, sqlite_vm_meta);
    if (svm == NULL) luaL_argerror(L, index, "bad sqlite virtual machine");
    return svm;
}
................................................................................
*/
static int db_prepare(lua_State *L) {
    sdb *db = lsqlite_checkdb(L, 1);
    const char *sql = luaL_checkstring(L, 2);
    int sql_len = lua_strlen(L, 2);
    const char *sqltail;
    sdb_vm *svm;
    lua_settop(L,2); /* db,sql is on top of stack for call to newvm */
    svm = newvm(L, db);










    if (sqlite3_prepare_v2(db->db, sql, sql_len, &svm->vm, &sqltail) != SQLITE_OK) {
        lua_pushnil(L);
        lua_pushinteger(L, sqlite3_errcode(db->db));
        if (cleanupvm(L, svm) == 1)
            lua_pop(L, 1); // this should not happen since sqlite3_prepare_v2 will not set ->vm on error
        return 2;
    }


    /* vm already in the stack */
    lua_pushstring(L, sqltail);
    return 2;
}

static int db_do_next_row(lua_State *L, int packed) {
................................................................................
    return dbvm_do_rows(L, db_next_row);
}

static int db_do_rows(lua_State *L, int(*f)(lua_State *)) {
    sdb *db = lsqlite_checkdb(L, 1);
    const char *sql = luaL_checkstring(L, 2);
    sdb_vm *svm;
    lua_settop(L,2); /* db,sql is on top of stack for call to newvm */
    svm = newvm(L, db);
    svm->temp = 1;









    if (sqlite3_prepare_v2(db->db, sql, -1, &svm->vm, NULL) != SQLITE_OK) {
        lua_pushstring(L, sqlite3_errmsg(svm->db->db));
        if (cleanupvm(L, svm) == 1)
            lua_pop(L, 1); // this should not happen since sqlite3_prepare_v2 will not set ->vm on error
        lua_error(L);
    }


    lua_pushcfunction(L, f);
    lua_insert(L, -2);
    return 2;
}

static int db_rows(lua_State *L) {

Changes to test/tests-sqlite3.lua.

30
31
32
33
34
35
36
37

38
39
40
41
42
43
44
..
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
..
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
...
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
...
835
836
837
838
839
840
841



































842
843
844
845
846
847
848
...
932
933
934
935
936
937
938
939


-- Copyright (c) 2005-13 Doug Currie
-- Same license as above

local sqlite3 = require "lsqlite3"

local os = os

local lunit = require "lunitx"


local tests_sqlite3

if _VERSION >= 'Lua 5.2' then

    tests_sqlite3 = lunit.module('tests-sqlite3','seeall')
    _ENV = tests_sqlite3
................................................................................
   tests_sqlite3['test_o_'..name] = fcn
end

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


-------------------------------
-- Basic open and close test --
-------------------------------

lunit_wrap("open_memory", function()
  local db = assert_userdata( sqlite3.open_memory() )
  assert( db:close() )
................................................................................

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")

................................................................................
--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")
................................................................................


--------------------------------------------
-- Tests loop break and statement reusage --
--------------------------------------------






































----------------------------
-- Test for bugs reported --
----------------------------

bug = lunit_TestCase("Bug-Report Tests")

................................................................................
    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










|
>







 







<







 







<
<







 







<







 







>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>







 








>
>
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
..
57
58
59
60
61
62
63

64
65
66
67
68
69
70
..
72
73
74
75
76
77
78


79
80
81
82
83
84
85
...
103
104
105
106
107
108
109

110
111
112
113
114
115
116
...
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
...
964
965
966
967
968
969
970
971
972
973
-- Copyright (c) 2005-13 Doug Currie
-- Same license as above

local sqlite3 = require "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
................................................................................
   tests_sqlite3['test_o_'..name] = fcn
end

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


-------------------------------
-- Basic open and close test --
-------------------------------

lunit_wrap("open_memory", function()
  local db = assert_userdata( sqlite3.open_memory() )
  assert( db:close() )
................................................................................

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")

................................................................................
--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")
................................................................................


--------------------------------------------
-- 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



----------------------------
-- Test for bugs reported --
----------------------------

bug = lunit_TestCase("Bug-Report Tests")

................................................................................
    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

lunit.main(arg)