It has been said that you can learn a lot from reading other people’s high-quality software. So I gave it a try this weekend by reading the SQLite source.
SQLite is a lightweight embedded database that does not require a standalone server. It is designed to be reliable, highly portable, and require minimal administration.
SQLite has some of the most rigorous testing methodology I have seen in an open source project. Thus its true defect count is likely to be extremely low, making it high quality.
Such a high quality product probably contains useful patterns and techniques, some of which I could reuse in my own programs. So I decided to dive into the SQLite source and see what gems I could pull out. Here are my notes:
I started by reading the high-level design documentation. SQLite has exceptionally good documentation of this type.
Documentation Index - Root of all documentation.
Architecture Documentation - Excellent overview of the architecture.
Query Planner Documentation - Describes how prepared statements are executed.
Distributed version control system used by SQLite. I’ve never heard of this. Beyond version control also provides bug tracking, a wiki, and a blog.
A custom parser generator used by SQLite. Alternative to the old bison/yacc combination which has some improvements:
OS abstraction layer. Despite the acronym (Virtual Filesystem), it is more than just filesystem manipulation routines.
This is the main entry point into the SQLite API. It executes a SQL statement and returns the result.
First impression is that most methods have a lot of malloc-failed handling. And indeed if you read the testing procedures, they involve causing random malloc errors throughout the code, so there has to be handling for such errors.
Hungarion notation is used as well. Ick. But at least it is consistent.
/* Assume 32-bit assignment is atomic */
This is the legacy entry point into the SQLite API.
It still exists and is actually used internally by the currently
recommended API entry point (
There are some odd syntactic conventions.
assert) but not all (
It appears that functions that begin with
sqlite3_ are public,
whereas functions starting with
sqlite3 (no underscore) are private.
This function executes one or more instructions from the instruction list inside of a prepared SQL statement. The instruction is executed inside of the SQLite virtual database engine (VDBE), which is a virtual machine.
Contains own mutex implementation. (
Deals with a somewhat crazy circumstance: If the database schema changes in the middle of executing a (prepared) SQL statement, the statement is reprepared and rerun automatically.
Generally there is good commenting for weird and backward-compatibility behaviors.
Practically every function can fail. They all return an integer error code.
This is a very high-quality C library.
And its design requirements show through in the implementation:
INTEGER PRIMARY KEY column).
Of course being simple sacrifices certain other properties such as high concurrency and the ability to perform fine-grained access control (which requires administrability).