LuaSQLite3

Check-in [7d2b88fc17]
Login

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

Overview
Comment:Add functions db:get_ptr() and sqlite3.open_ptr(db_ptr) to pass a db connection between threads.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | trunk
Files: files | file ages | folders
SHA1: 7d2b88fc17aa187ec8c7cd832ffe677fc790da56
User & Date: e 2016-11-12 23:01:53
References
2016-11-12
23:02 Closed ticket [47b3122331]: provide db connection as lightuserdata and recreate from it plus 4 other changes artifact: dd655ce29b user: e
Context
2016-11-13
14:33
Add libraries to rockspec for sqlite3; simplify db_db_filename. check-in: e7aa926110 user: e tags: trunk
2016-11-12
23:01
Add functions db:get_ptr() and sqlite3.open_ptr(db_ptr) to pass a db connection between threads. check-in: 7d2b88fc17 user: e tags: trunk
21:42
Update sqlite3 to 3.15.1 check-in: 9a9a8e3156 user: e tags: trunk
Changes
Hide Diffs Unified Diffs Ignore Whitespace Patch

Changes to HISTORY.

1
2
3
4
5
6

7
8
9

10
11
12
13
14
15
16
2016-November-??

Version "0.9.4"

Since the "0.9.3-devel" release of this Lua library...


Add function db:db_filename(name)

Update sqlite3_open to sqlite3_open_v2 and add open flags constants.


Added second module 'lsqlite3complete' that statically links sqlite.c. Continue to use 
'lsqlite3' for dynamic linking to 'sqlite3.so' or 'sqlite3.dll'.

Added Online Backup API

Added unit tests for NULs in BLOBs and TEXT columns. (Refute defect report.)






>
|
<

>







1
2
3
4
5
6
7
8

9
10
11
12
13
14
15
16
17
2016-November-??

Version "0.9.4"

Since the "0.9.3-devel" release of this Lua library...

Add functions db:get_ptr() and sqlite3.open_ptr(db_ptr) to pass a db connection between threads.
Add function db:db_filename(name).

Update sqlite3_open to sqlite3_open_v2 and add open flags constants.
Thanks to Wolfgang Oertl!

Added second module 'lsqlite3complete' that statically links sqlite.c. Continue to use 
'lsqlite3' for dynamic linking to 'sqlite3.so' or 'sqlite3.dll'.

Added Online Backup API

Added unit tests for NULs in BLOBs and TEXT columns. (Refute defect report.)

Changes to doc/lsqlite3.wiki.

14
15
16
17
18
19
20

21
22
23
24
25
26
27
28
29
30
31
32
33

34
35
36
37
38
39
40
...
222
223
224
225
226
227
228
229








230
231
232
233
234
235
236
...
321
322
323
324
325
326
327










328
329
330
331
332
333
334
	<li><a href="#sqlite3_functions">SQLite3 functions</a></li>
	<ul>

		<li><a href="#sqlite3_complete">sqlite3.complete</a></li>
        <li><a href="#sqlite3_lversion">sqlite3.lversion</a></li>
		<li><a href="#sqlite3_open">sqlite3.open</a></li>
        <li><a href="#sqlite3_open_memory">sqlite3.open_memory</a></li>

        <li><a href="#sqlite3_backup_init">sqlite3.backup_init</a></li>
		<li><a href="#sqlite3_temp_directory">sqlite3.temp_directory</a></li>
		<li><a href="#sqlite3_version">sqlite3.version</a></li>
	</ul>

	<li><a href="#database_methods">Database methods</a></li>
	<ul>

		<li><a href="#db_busy_handler">db:busy_handler</a></li>
		<li><a href="#db_busy_timeout">db:busy_timeout</a></li>
		<li><a href="#db_changes">db:changes</a></li>
		<li><a href="#db_close">db:close</a></li>
		<li><a href="#db_close_vm">db:close_vm</a></li>

        <li><a href="#db_commit_hook">db:commit_hook</a></li>
		<li><a href="#db_create_aggregate">db:create_aggregate</a></li>
		<li><a href="#db_create_collation">db:create_collation</a></li>
		<li><a href="#db_create_function">db:create_function</a></li>
		<li><a href="#db_errcode">db:errcode</a></li>
		<li><a href="#db_errmsg">db:errmsg</a></li>
		<li><a href="#db_exec">db:exec</a></li>
................................................................................
        sqlite3.open_memory()</pre>
<p>Opens an SQLite database <strong>in memory</strong> and returns its handle as
userdata. In case of an error, the function returns nil, an error code
and an error message. (In-memory databases are volatile as they are
never stored on disk.)</p>
<p>
</p>









<h2><a name="sqlite3_backup_init">sqlite3.backup_init</a></h2>
<pre>
        sqlite3.backup_init(target_db, target_name, source_db, source_name)</pre>
<p>Starts an SQLite Online Backup from <code>source_db</code> to <code>target_db</code> and 
returns its handle as userdata. The <code>source_db</code> and <code>target_db</code> are
open databases; they may be in-memory or file-based databases. The <code>target_name</code> and
<code>source_name</code> are "main" for the main database, "temp" for the temporary database, or 
................................................................................
<h2><a name="db_close_vm">db:close_vm</a></h2>
<pre>
        db:close_vm(temponly)</pre>
<p>Finalizes all statements that have not been explicitly finalized. If
<code>temponly</code> is true, only internal, temporary statements are finalized.
This function returns nothing.</p>
<p>










</p>
<h2><a name="db_commit_hook">db:commit_hook</a></h2>
<pre>
        db:commit_hook(func,udata)</pre>
<p>This function installs a commit_hook callback handler. <code>func</code> is a Lua function 
that is invoked by SQLite3 whenever a transaction is commited. This callback receives one argument: 
the <code>udata</code> argument used when the callback was installed. If <code>func</code> returns <code>false</code>







>













>







 







<
>
>
>
>
>
>
>
>







 







>
>
>
>
>
>
>
>
>
>







14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
...
224
225
226
227
228
229
230

231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
...
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
	<li><a href="#sqlite3_functions">SQLite3 functions</a></li>
	<ul>

		<li><a href="#sqlite3_complete">sqlite3.complete</a></li>
        <li><a href="#sqlite3_lversion">sqlite3.lversion</a></li>
		<li><a href="#sqlite3_open">sqlite3.open</a></li>
        <li><a href="#sqlite3_open_memory">sqlite3.open_memory</a></li>
        <li><a href="#sqlite3_open_ptr">sqlite3.open_ptr</a></li>
        <li><a href="#sqlite3_backup_init">sqlite3.backup_init</a></li>
		<li><a href="#sqlite3_temp_directory">sqlite3.temp_directory</a></li>
		<li><a href="#sqlite3_version">sqlite3.version</a></li>
	</ul>

	<li><a href="#database_methods">Database methods</a></li>
	<ul>

		<li><a href="#db_busy_handler">db:busy_handler</a></li>
		<li><a href="#db_busy_timeout">db:busy_timeout</a></li>
		<li><a href="#db_changes">db:changes</a></li>
		<li><a href="#db_close">db:close</a></li>
		<li><a href="#db_close_vm">db:close_vm</a></li>
        <li><a href="#db_get_ptr">db:get_ptr</a></li>
        <li><a href="#db_commit_hook">db:commit_hook</a></li>
		<li><a href="#db_create_aggregate">db:create_aggregate</a></li>
		<li><a href="#db_create_collation">db:create_collation</a></li>
		<li><a href="#db_create_function">db:create_function</a></li>
		<li><a href="#db_errcode">db:errcode</a></li>
		<li><a href="#db_errmsg">db:errmsg</a></li>
		<li><a href="#db_exec">db:exec</a></li>
................................................................................
        sqlite3.open_memory()</pre>
<p>Opens an SQLite database <strong>in memory</strong> and returns its handle as
userdata. In case of an error, the function returns nil, an error code
and an error message. (In-memory databases are volatile as they are
never stored on disk.)</p>
<p>
</p>

<h2><a name="sqlite3_open_ptr">sqlite3.open_ptr</a></h2>
<pre>
        sqlite3.open_ptr(db_ptr)</pre>
<p>Opens the SQLite database corresponding to the light userdata <code>db_ptr</code> and returns 
its handle as userdata. Use <a href="#db_get_ptr"><code>db:get_ptr</code></a> to get 
a <code>db_ptr</code> for an open database.</p>
<p>
</p>
<h2><a name="sqlite3_backup_init">sqlite3.backup_init</a></h2>
<pre>
        sqlite3.backup_init(target_db, target_name, source_db, source_name)</pre>
<p>Starts an SQLite Online Backup from <code>source_db</code> to <code>target_db</code> and 
returns its handle as userdata. The <code>source_db</code> and <code>target_db</code> are
open databases; they may be in-memory or file-based databases. The <code>target_name</code> and
<code>source_name</code> are "main" for the main database, "temp" for the temporary database, or 
................................................................................
<h2><a name="db_close_vm">db:close_vm</a></h2>
<pre>
        db:close_vm(temponly)</pre>
<p>Finalizes all statements that have not been explicitly finalized. If
<code>temponly</code> is true, only internal, temporary statements are finalized.
This function returns nothing.</p>
<p>
</p>
<h2><a name="db_get_ptr">db:get_ptr</a></h2>
<pre>
        db:get_ptr()</pre>
<p>Returns a lightuserdata corresponding to the open <code>db</code>. Use with 
<a href="#sqlite3_open_ptr"><code>sqlite3.open_ptr</code></a> to pass a database connection
between threads. (When using lsqlite3 in a multithreaded environment, each thread has a separate 
Lua environment; full userdata structures can't be passed from one thread to another, but this 
is possible with lightuserdata.)</p>
<p>
</p>
<h2><a name="db_commit_hook">db:commit_hook</a></h2>
<pre>
        db:commit_hook(func,udata)</pre>
<p>This function installs a commit_hook callback handler. <code>func</code> is a Lua function 
that is invoked by SQLite3 whenever a transaction is commited. This callback receives one argument: 
the <code>udata</code> argument used when the callback was installed. If <code>func</code> returns <code>false</code>

Changes to lsqlite3.c.

2013
2014
2015
2016
2017
2018
2019











2020
2021
2022
2023
2024
2025
2026
....
2088
2089
2090
2091
2092
2093
2094
























2095
2096
2097
2098
2099
2100
2101
....
2223
2224
2225
2226
2227
2228
2229

2230
2231
2232
2233
2234
2235
2236
....
2319
2320
2321
2322
2323
2324
2325

2326
2327
2328
2329
2330
2331
2332
        }

        /* leave key in the stack */
        lua_pop(L, 1);
    }
    return 0;
}












static int db_gc(lua_State *L) {
    sdb *db = lsqlite_getdb(L, 1);
    if (db->db != NULL)  /* ignore closed databases */
        cleanupdb(L, db);
    return 0;
}
................................................................................
    int flags = luaL_optinteger(L, 2, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
    return lsqlite_do_open(L, filename, flags);
}

static int lsqlite_open_memory(lua_State *L) {
    return lsqlite_do_open(L, ":memory:", SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
}

























static int lsqlite_newindex(lua_State *L) {
    lua_pushliteral(L, "attempt to change readonly table");
    lua_error(L);
    return 0;
}

................................................................................
    {"urows",               db_urows                },
    {"nrows",               db_nrows                },

    {"exec",                db_exec                 },
    {"execute",             db_exec                 },
    {"close",               db_close                },
    {"close_vm",            db_close_vm             },


    {"__tostring",          db_tostring             },
    {"__gc",                db_gc                   },

    {NULL, NULL}
};

................................................................................
    {"version",         lsqlite_version         },
    {"complete",        lsqlite_complete        },
#ifndef WIN32
    {"temp_directory",  lsqlite_temp_directory  },
#endif
    {"open",            lsqlite_open            },
    {"open_memory",     lsqlite_open_memory     },


    {"backup_init",     lsqlite_backup_init     },

    {"__newindex",      lsqlite_newindex        },
    {NULL, NULL}
};








>
>
>
>
>
>
>
>
>
>
>







 







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







 







>







 







>







2013
2014
2015
2016
2017
2018
2019
2020
2021
2022
2023
2024
2025
2026
2027
2028
2029
2030
2031
2032
2033
2034
2035
2036
2037
....
2099
2100
2101
2102
2103
2104
2105
2106
2107
2108
2109
2110
2111
2112
2113
2114
2115
2116
2117
2118
2119
2120
2121
2122
2123
2124
2125
2126
2127
2128
2129
2130
2131
2132
2133
2134
2135
2136
....
2258
2259
2260
2261
2262
2263
2264
2265
2266
2267
2268
2269
2270
2271
2272
....
2355
2356
2357
2358
2359
2360
2361
2362
2363
2364
2365
2366
2367
2368
2369
        }

        /* leave key in the stack */
        lua_pop(L, 1);
    }
    return 0;
}

/* From: Wolfgang Oertl
When using lsqlite3 in a multithreaded environment, each thread has a separate Lua 
environment, but full userdata structures can't be passed from one thread to another.
This is possible with lightuserdata, however. See: lsqlite_open_ptr().
*/
static int db_get_ptr(lua_State *L) {
    sdb *db = lsqlite_checkdb(L, 1);
    lua_pushlightuserdata(L, db->db);
    return 1;
}

static int db_gc(lua_State *L) {
    sdb *db = lsqlite_getdb(L, 1);
    if (db->db != NULL)  /* ignore closed databases */
        cleanupdb(L, db);
    return 0;
}
................................................................................
    int flags = luaL_optinteger(L, 2, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
    return lsqlite_do_open(L, filename, flags);
}

static int lsqlite_open_memory(lua_State *L) {
    return lsqlite_do_open(L, ":memory:", SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE);
}

/* From: Wolfgang Oertl
When using lsqlite3 in a multithreaded environment, each thread has a separate Lua 
environment, but full userdata structures can't be passed from one thread to another.
This is possible with lightuserdata, however. See: db_get_ptr().
*/
static int lsqlite_open_ptr(lua_State *L) {
    sqlite3 *db_ptr;
    sdb *db;
    int rc;

    luaL_checktype(L, 1, LUA_TLIGHTUSERDATA);
    db_ptr = lua_touserdata(L, 1);
    /* This is the only API function that runs sqlite3SafetyCheck regardless of
     * SQLITE_ENABLE_API_ARMOR and does almost nothing (without an SQL
     * statement) */
    rc = sqlite3_exec(db_ptr, NULL, NULL, NULL, NULL);
    if (rc != SQLITE_OK)
        luaL_argerror(L, 1, "not a valid SQLite3 pointer");

    db = newdb(L); /* create and leave in stack */
    db->db = db_ptr;
    return 1;
}

static int lsqlite_newindex(lua_State *L) {
    lua_pushliteral(L, "attempt to change readonly table");
    lua_error(L);
    return 0;
}

................................................................................
    {"urows",               db_urows                },
    {"nrows",               db_nrows                },

    {"exec",                db_exec                 },
    {"execute",             db_exec                 },
    {"close",               db_close                },
    {"close_vm",            db_close_vm             },
    {"get_ptr",             db_get_ptr              },

    {"__tostring",          db_tostring             },
    {"__gc",                db_gc                   },

    {NULL, NULL}
};

................................................................................
    {"version",         lsqlite_version         },
    {"complete",        lsqlite_complete        },
#ifndef WIN32
    {"temp_directory",  lsqlite_temp_directory  },
#endif
    {"open",            lsqlite_open            },
    {"open_memory",     lsqlite_open_memory     },
    {"open_ptr",        lsqlite_open_ptr        },

    {"backup_init",     lsqlite_backup_init     },

    {"__newindex",      lsqlite_newindex        },
    {NULL, NULL}
};

Changes to test/tests-sqlite3.lua.

1225
1226
1227
1228
1229
1230
1231










1232

1233

1234
1235
1236

  assert_nil( r094.db:db_filename("frob") )
  assert_equal( '', r094.db:db_filename("main") )

  assert_nil( r094.db_fn:db_filename("frob") )
  assert_equal( r094.filename, r094.db_fn:db_filename("main") )











end




lunit.main()








>
>
>
>
>
>
>
>
>
>
|
>

>



1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248

  assert_nil( r094.db:db_filename("frob") )
  assert_equal( '', r094.db:db_filename("main") )

  assert_nil( r094.db_fn:db_filename("frob") )
  assert_equal( r094.filename, r094.db_fn:db_filename("main") )

  -- from Wolfgang Oertl
  local db_ptr = assert_userdata( r094.db:get_ptr() )
  local db2    = assert_userdata( sqlite3.open_ptr(db_ptr) )
  -- do something via connection 1
  r094.db:exec("CREATE TABLE test1(a, b)")
  r094.db:exec("INSERT INTO test1 VALUES(1, 2)")
  -- see result via connection 2
  for a, b in db2:urows("SELECT * FROM test1 ORDER BY a") do
        assert_equal(a, 1)
        assert_equal(b, 2)
  end
  assert_number( db2:close() )

end

lunit.main()