A Database By Any Other Name…Posted: May 24, 2012
I’ve created a LuaJIT ffi interface to SQLite.
A couple years back I played around with SQLite as a quick and dirty database. In that particular case, I was actually translating data from large formats of data (gigabytes), and putting them into a form that was more relational, and easy to deal with.
Now I have need for a quick and dirty database that is capable of dealing with SQL Commands, in-memory. So, I looked at SQLite again. The advantages are that it’s ubiquitous, available on every platform known to man. If push comes to shove, it can be recompiled, fairly easily, because there is a nice ‘amalgamation’ file, which is all the source for the thing in a single ANSI C file.
At any rate, this is not the only Lua interface to SQLite. In fact, there have probably been about 10 of them over the past few years. Each of them has its benefits and weaknesses. So, why not create another one? There is this one for sqlite3 and ffi: lsqlite3-ffi
lsqlite3-ffi is one of the few Lua interfaces to sqlite3 which is specifically for LuaJIT. It presents a good model for interacting with the database.
I take a slightly different approach to things, but not dramatically different.
The way I went about it was to look at the operations I perform, and think about how I wanted to type them with the least amount of keystrokes. Here is the sequence of events needed to: Create a in-memory database, create a table, insert some values, select all those values, using an interator in a for loop:
-- Establish a database connection to an in memory database local dbconn,err = sqlite3_conn.Open(":memory:"); -- Create a table in the 'main' database local tbl, rc, errormsg = dbconn:CreateTable("People", "First, Middle, Last"); -- A simple function to report errors -- This will hault the program if there -- is an error -- Use this when you consider an error to -- be an exception -- But really, it's just to test things out function dbcheck(rc, errormsg) if rc ~= SQLITE_OK then print("Error Code: ", rc) error(errormsg) end return rc, errormsg end -- Insert some rows into the table dbcheck(tbl:InsertValues("'Bill', 'Albert', 'Gates'")); dbcheck(tbl:InsertValues("'Larry', 'Devon', 'Ellison'")); dbcheck(tbl:InsertValues("'Steve', 'Jahangir', 'Jobs'")); dbcheck(tbl:InsertValues("'Jack', '', 'Sprat'")); dbcheck(tbl:InsertValues("'Marry', '', 'Lamb'")); dbcheck(tbl:InsertValues("'Peter', '', 'Piper'")); -- Using prepared statements, do the same connect again stmt, rc = dbconn:Prepare("SELECT * from People"); -- A simple utility routine to print out the values of a row function printRow(row) local cols = #row; for i,value in ipairs(row) do io.write(value); if i < cols then io.write(','); end end io.write('\n'); end -- Using the Results() iterator to return individual -- rows as Lua tables. for row in stmt:Results() do printRow(row); end -- Finish off the statement stmt:Finish(); -- Close the database connection dbconn:Close();
So, it’s a slightly different flavor from those which have gone before, but not dramatically so. There are a couple of gems in the way the myriad constants for SQLite are defined. You can fairly easily determine if you want them as an enum, const int, whithin a ffi.cdef, or use the default, taking them as Lua number values, which is the easiest to use in my opinion.
Having the power of a relational database in memory, with zero install, and a fairly small footprint, is a useful thing in certain situations. For me, it’s just gotten a little bit easier to deal with.