[Hi -- I've just joined this list. I'm a Lua newbie, I've been occasionally kicking the tires for a few months now. I like the language but haven't had time to really dive in yet.]
I've made some improvements to LuaSQL's sqlite driver, starting from the sources in the Kepler 1.0b distribution that I downloaded yesterday (I think this is LuaSQL 2.0, according to the website.)
* I ported the code to use sqlite 3. This has been out for about a year and has a number of major improvements over version 2. (It's also the version that ships with Mac OS X 10.4.) The API has changed in some incompatible ways, however. The Lua sqlite driver could be made source-compatible with either version by adding a whole lot of #ifdefs, but in my opinion it'd be better to just go with sqlite 3.
* I fixed a bug: create_connection() didn't initialize the cur_counter field of the conn_data struct to zero, so it could contain garbage. In this case conn_close would fail with a "there are open cursors" error. This was always happening to me until I fixed the bug -- since this hasn't been caught before, I'm guessing that on some other platforms lua_newuserdata always (or almost always) returns zeroed memory?
I don't know what the process is for contributing patches; please email me if you'd like a copy of the modified source.
In addition I see some opportunities to improve performance of the code:
* conn_execute unnecessarily runs SELECT queries twice. To check whether a query returns columns and needs a cursor, it steps the query, checks the return value and column count, and if a cursor is called for, it then resets the query. The first call to cur_fetch will then repeat that work. Instead, cur_fetch could be optimized to skip the sqlite3_step call the first time it's called.
* sqlite3 includes new functions to fetch column values as types other than strings. So an int-valued column can be directly fetched as an int. This would allow the driver to put typed values in the result tables instead of just strings, which should be faster and probably more convenient for the programmer. It might cause backward-compatibility problems with existing client code that expects strings, however.
* Also new in sqlite3 are pluggable query parameters. A query string can contain placeholders, which can be filled in separately. (Similar concept to printf.) This makes your code more reliable (and less prone to security holes) by eliminating the need to quote strings. You can also re-use a compiled statement multiple times with varying parameters, which saves the overhead of compilation. This is great stuff, but I think it would require extensions to the LuaSQL API to expose this to developers.
I've seen an alternate SQLite API, Lua-Sqlite3, but it doesn't build on Mac OS X. I may poke at the makefile and see what I can do. Right now I was focusing on LuaSQL because it's part of Kepler, which I want to try out...