Using SQLite as an On-disk File Format, Part 1

 

SQLite is a small but powerful cross-platform database engine that can easily be compiled and linked to native executables. This article introduces a series of articles that will explore the use of SQLite in the role of an on-disk file format.

I was browsing the SQLite website after finding a small reference to it in a Firefox 2.0 story. SQLite is a full database engine that implements most of SQL92. For the small price of 200 kb, a native application is given access to a very powerful data source. IBM has a really nice overview of the software in its Introduction to SQLite.

In the land of Windows client application development, when you need a database you usually think of Jet, MSSQL, or Borland’s DB engines. You can communicate with these engines using ODBC and they all work just fine so long as you have the appropriate DB engine installed (or, in the case of Jet, you install the redistributable package along with your app).

But that’s the clincher: sometimes you don’t want to install DB engines. This may be because you are worried about conflicts (the last two Microsoft products I installed each tried to install SQLExpress), you don’t want your application to modify the system at all, or you are happiest with a single-EXE solution for deployment. Or perhaps you aren’t developing for Windows at all and are required to implement your own solution regardless.

Well, I really like SQLite. It has a rich, but very easy to use API that manages SQL and the loading and unloading of DB files. I find it to be a fantastic alternative to ODBC in terms of simplicity.

In the online documentation for SQLite I noticed an enticing entry, “Appropriate Uses For SQLite”. In that article is this section that I wish to quote verbatim for it really got me excited:

“SQLite has been used with great success as the on-disk file format for desktop applications such as financial analysis tools, CAD packages, record keeping programs, and so forth. The traditional File/Open operation does an sqlite3_open() and executes a BEGIN TRANSACTION to get exclusive access to the content. File/Save does a COMMIT followed by another BEGIN TRANSACTION. The use of transactions guarantees that updates to the application file are atomic, durable, isolated, and consistent.

Temporary triggers can be added to the database to record all changes into a (temporary) undo/redo log table. These changes can then be played back when the user presses the Undo and Redo buttons. Using this technique, a unlimited depth undo/redo implementation can be written in surprising little code.”

A nice example of an application using a database engine as its file format is CityDesk by Fog Creek Software. That software uses the Jet DB engine and does so in a very polished and unobtrusive manner (strange message boxes with SQL arcana have yet to be experienced by this reporter!).

The benefits of using a database as an on-disk format largely come from the transaction model that is used. We can insure that large amounts of data are consistently written to the database by using explicit transactions. This helps alleviate the problems of power loss, program failure, bad writes to disk, etc.

As I have never tried working with a DB as an application file, I decided to write my own application to test the waters. I wanted to know if writing a DB-based system was any more difficult or any better than writing a custom serialization format. Does it offer simplifications or some added amount of robustness? Is setting undo/redo functionality really as simple as coding some triggers? I want to know.

To that end, I would like to introduce you to this article series. I don’t know exactly how many parts there will be (as I have yet to finish the demo app) but I am guessing that there will be somewhere around 5 at this moment.

Please stay tuned, the next article which will lay the groundwork for our application will be posted in just a couple days.