An introduction to SQLite.

SQLite is used by some of the biggest names in IT; Apple, Adobe, and Google to name a few (Apple in the iPhone, Adobe in AIR for building and deploying web applications on the desktop, and Google in the Gears browser extension). It may already be the most widely distributed database in the world. It truly is a zero-cofiguration, almost SQL-92 compliant, public domain database that runs on pretty much every operating system. Little Endian, Big Endian, makes no difference - files can be freely shared. It certainly is not an enterprise database replacement, but there are quite a few situations where SQLite works very well. Dr. Richard Hipp, the primary author of SQLite, provided a Google TechTalk briefing in early 2006; legible slides are found here.

While the SQLite Library core is C code, the number of language bindings available is staggering. As my SQLite introduction, I developed a simple .NET 2.0 assembly that uses SQLite to aggregrate Internet Explorer favorites from all of my PCs. The supplied command-line administration tool provides the capability to quickly generate a consumable html file of aggregated links. What is nice is that I can use the power of SQL to filter the aggregated links by title, date, grouping, etc. No surprise that Mozilla's Firefox 3 is moving to SQLite for storage of their bookmarks among other things.

One of the distinctive SQLite features that did lead to a data defect was column datatype affinity. While traditional databases use static datatyping on columns, SQLite uses the column datatype as a recommendation. In other words, you can store any value of any datatype into any column (except a column that specifies INTEGER PRIMARY KEY). I had improperly formatted the creationTime attribute for insertion which created a problem when attempting to use it in an ORDER BY later on. Besides column type affinity, it's also important to keep in mind that SQLite does not enforce RI and complicates table evolution with limited ALTER TABLE options.

It was extremely simple, and deserves a closer look for use in other endeavors. You can read the source listing in html here.

Posted at 11:24 AM  Permalink