A Database By Any Other Name…

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.

Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s