ryan kennedy’s blog

I’ve been messing around with SQLite a little in the last couple of weeks. SQLite is an embedded SQL database. It stores all of your database in a single file and it’s super light. That nice for doing prototypes or for just coding on the side. The data I pulled together for my 2005 financial year in review is actually in a SQLite database. I didn’t have to install any huge-mongous software packages (no MySQL or Oracle). Even better, if I ever want to back up, transport or replicate the database…it’s just a file copy.

There’s a PHP binding that I’ve been using. It works pretty well for the little one-off projects I’ve been tinkering on lately. I could probably just as easily use XML or some other file format to do this, but SQLite is already available in PHP 5 and it enables me to do some interesting data mashing by providing joins for free. There’s even a command line client that works like the MySQL/Oracle command line clients, allowing you to “connect” to your database and run queries against it. Evidently there’s also transaction support, although I haven’t done anything with that yet.

Update: Another fun trick for you PHP users, combine file_get_contents()/file_put_contents() and serialize()/unserialize(). Using this, you can easily write PHP objects to disk and read them back into memory. This is handy when all you’re storing is a simple object, list or map.

// Get the object from disk.
$obj = unserialize(file_get_contents(”foo.txt”));

// Change the object.
$obj->foo = “bar”;

// Save the object.
file_put_contents(”foo.txt”, serialize($obj));

Just remember, when you get the object you’ll have to read in the entire file. So if you have a large dataset, SQLite might be a better bet since it can selectively pull data from the file. Also, SQLite gives you things like “GROUP BY” and “ORDER BY” that you’d have to implement yourself if you go with the serialize/unserialize solution.

This entry was posted on Sunday, January 15th, 2006 at 11:51 pm and is filed under Development. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.