A simple SQL/Flatfile abstraction in Lua

I’ve been wondering if it was possible to make a MySQL/SQLite/Flatfile abstracted interface for Lua for some time now. At first I thought I’d try something like LINQ, but realized that there was really no reasonable way to do that in Lua since we don’t have the power of expression trees. I then considered writing the queries in a simplified pseudo-Lua language, but that would take too much time to parse and no one really wants to learn another language anyways.

What I settled on instead was a very simplified abstraction of the data access. It can’t do everything you can do yourself from SQL or raw file I/O, but I found that it serves up exactly the kind of interface I’d need for all my past projects in Lua that use some form of I/O.

Here’s an example of declaring a table of data:
[cc_lua]users = CreateDataTable( “users”, “steamid”, “string(32)”, “The steamid of the user” )
users:AddKey( “group”, “string(16)”, “The group the user belongs to” )
users:AddKey( “name”, “string(32)”, “The name the player was last seen with” )
users:AddListOfKeyValues( “allow”, “string(16)”, “string(128)”, “The allows for the user” )[/cc_lua]

We’re defining a primary key ‘steamid’ for each of the rows in the table ‘users’. We’re saying that steamid is going to be a string of max length 32 characters, and we define a comment that’s used in MySQL and Flatfiles. We then go on to add regular keys ‘group’ and ‘name’ to the table in a similar fashion, and you should note that regular keys are optional in the table. Finally, we’re making an additional key-value table named ‘allow’ (A key-value table basically means a regular, unrestricted Lua table). So, to make sure you’ve got the idea, the Lua table structure of would look like this:

[cc_lua]users = {
my_steamid1 = {
group = “admin”,
allow = {
slap = “*”,
kick = “*”
}
},
my_steamid2 = {
name = “Bob”,
allow = {}
}
}[/cc_lua]

The API around such a clean representation of data couldn’t be much simpler. You have four operations: insert a new row by primary key, fetch an existing row by primary key, delete a row by primary key, and get the entire table. When inserting a row you can optionally pass in the data for the row. With both the insert and fetch functions you get back a table for the row that’s being “tracked”. When you change any of the contents, the change is immediately reflected into the DB or file.

There’s a caching system built around the system so if you fetch the same row multiple times, it won’t be going out to the DB or reading the file each time. You can request the cache be flushed or disabled altogether if you need to. Unlike file I/O I’ve done in the past, this system doesn’t need to parse the whole file to get a single row, which means it should probably work okay using flatfiles on very large files.

Though I haven’t coded this portion yet, I’m also planning on adding the ability to convert between MySQL/SQLite/flatfiles on the fly. This may be problematic for very large databases, so I’ve also taken care to make sure that this system can be run as a standalone script apart from any specific application.

So… if you’ve read this far, your next question is probably, “Why bother with such a system? Why not just stick with a single format?”. A single format (usually flatfiles) is great for about 95% of my users. The other 5%, however, are the power users who want to do crazy things like hook up a PHP billboard with blinking lights showing how many times a second someone says the word ‘the’. It’s the power users I prefer to cater to, so I’ve always felt guilty in the past that this was one area I couldn’t do much for them in. But now I can!

4 thoughts on “A simple SQL/Flatfile abstraction in Lua”

  1. Hmmm, I didn’t even consider using JSON (like CouchDB), but that’s actually a really good format as far as standardization goes. My main reason for wanting to write to a flatfile is so users can edit the files for themselves, not sure if editing JSON would be easier or harder for them. Of course, the proliferation of editors (even online ones like this: http://braincast.nl/samples/jsoneditor/) might alleviate that concern anyways.

  2. Any chance you’d release this old code in the wild? I ahve something that I’d like to implement with this simple style on an iPad running Lua.

  3. @Antonio
    Thanks for stopping by and asking about this! The source code is all available on Github here: https://github.com/Nayruden/otlib/blob/master/simpledata.lua

    The only problem is that it was not written to be standalone, but part of the otlib library, so it might be more overhead than you’re looking for. But please check it out, and let me know if you have any comments on it! Even though this was written over two years ago now, I still think about it from time to time and would like to eventually implement it somewhere.

Leave a Reply

Your email address will not be published. Required fields are marked *