SQLite
Sunday, 23 March 2008
We've incorporated SQLite* into a project at work recently. If you haven't come across it before, SQLite is "a software library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine". Pretty good for a library that weighs in at less than 250KB, huh? It's also trivial to embed, with its amalgamated version (where much of the precompilation is done already) consisting of just one C source file and one header file. There are also wrappers for SQLite's C API available for lots of languages, from .html to Tcl/Tk; there's even an ODBC driver for it if you're into that sort of thing.
You can use a command line tool to interact with the database, or more commonly, you can integrate the library into your application directly. It's very full featured, with only a few SQL features missing (right outer joins, updatable views, foreign key enforcement, and a couple of other things), so you can perform some pretty powerful operations on your data right out of the box.
Transactions use a journal file, so if power is lost during an update, SQLite will automatically rollback the incomplete transaction when power is resumed. This robustness, couple with its size, makes SQLite very popular with embedded device and cell phone manufacturers. It's used in many cell phones, and it is embedded into Apple Mail and many other common applications, including Firefox.
The implementation of SQLite is pretty interesting, in that it compiles statements to byte code for its own virtual machine when you prepare them. The command line tool even has an EXPLAIN command that lets you see the byte code that a statement will generate, so you can see how it will run and potentially do some optimization. The code is well written and well documented. The code base has a 60/40 split between test code and source code and its regression tests have 98% code coverage. I wish I could say that about all of the projects that I've worked on over the years.
Here's some resources if you would like to find out more about SQLite:
- Leo Laporte and Randal Schwartz talked to SQLite's author D. Richard Hipp recently on episode 26 of the FLOSS podcast.
- There is an excellent book
on SQLite by Mike Owens. It covers all aspects of SQLite, from its inception to writing extensions for it. There's also a couple of chapters that provide a nice introduction to relational database theory, and SQL. The only bad thing I can say about this book is that its index is next to useless.
- There's also a nice video of D. Richard Hipp presenting "An Introduction to SQLite" at Google in May of 2006:
*Pronounced ess-que-ell-lite according to its author (and he should know).
You can use a command line tool to interact with the database, or more commonly, you can integrate the library into your application directly. It's very full featured, with only a few SQL features missing (right outer joins, updatable views, foreign key enforcement, and a couple of other things), so you can perform some pretty powerful operations on your data right out of the box.
Transactions use a journal file, so if power is lost during an update, SQLite will automatically rollback the incomplete transaction when power is resumed. This robustness, couple with its size, makes SQLite very popular with embedded device and cell phone manufacturers. It's used in many cell phones, and it is embedded into Apple Mail and many other common applications, including Firefox.
The implementation of SQLite is pretty interesting, in that it compiles statements to byte code for its own virtual machine when you prepare them. The command line tool even has an EXPLAIN command that lets you see the byte code that a statement will generate, so you can see how it will run and potentially do some optimization. The code is well written and well documented. The code base has a 60/40 split between test code and source code and its regression tests have 98% code coverage. I wish I could say that about all of the projects that I've worked on over the years.
Here's some resources if you would like to find out more about SQLite:
- Leo Laporte and Randal Schwartz talked to SQLite's author D. Richard Hipp recently on episode 26 of the FLOSS podcast.
- There is an excellent book
on SQLite by Mike Owens. It covers all aspects of SQLite, from its inception to writing extensions for it. There's also a couple of chapters that provide a nice introduction to relational database theory, and SQL. The only bad thing I can say about this book is that its index is next to useless.- There's also a nice video of D. Richard Hipp presenting "An Introduction to SQLite" at Google in May of 2006:
*Pronounced ess-que-ell-lite according to its author (and he should know).