Thursday, September 22, 2005

sqlite: a flyswatter to kill flies

I'm now using sqlite to support an Oracle production database. I love it!

When I first went to download sqlite, I went away frustrated. I found an executable described as "A command-line program for accessing and modifying SQLite databases", and thought, "OK, so that's my SQL*Plus equivalent, but where's the actual server? The part that keeps the database process running?"

Because I have been an Oracle-only person so long, I didn't understand that sqlite doesn't need anything like that. It's simply this:
  1. a single executable program that creates and modifies a database file
  2. A database file
And that's it. Really! You can't imagine how dizzying and refreshing that is to someone who's spent hours before a certification exam memorizing Oracle architecture diagrams.

Anyway, there are other simple database engines, of course, but sqlite has gotten a lot of attention recently (like an Open Source Award) for its efficiency and its support across many languages. There's an excellent and honest rundown of its powers and limitations here.

So, anyway - why sqlite to support Oracle? Well, one of my Oracle instances has a bunch of logic applied to it by a nightly batch job. For every data record, a series of decisions are made, and our users ask questions like, "For record #12945, why did it decide X instead of Y last Tuesday?" And I have to be able to answer, "Well, the seventh of the nine tests conducted on that record determined that, since column 'product' was 'lutefisk' and 'quantity_kg' was 22, blah blah blah..." So all those decisions need to be logged every night.

That generates a quantity of data far outweighing the application data itself. It can be discarded after a week or two, but all that inserting and deleting was causing out-of-control generation of archive log files and making a mess of my disk space allocation. By moving that data out into a sqlite database, it becomes a single simple file that can be moved or deleted as easily as any other file.

So, thanks to sqlite, I'm living happily ever after. Hooray!

No comments: