Updating the world’s most widely deployed SQL database engine: welcome to SQLite 4

A new version of SQLite is in preparation. If you are not a developer, you might not have heard of SQLite, but you have almost certainly used it. It is built into Mac OS X and numerous web browsers, used by many applications which run on Adobe’s Flash runtime, and is the obvious choice if you want a small, fast and reliable database engine to embed into an application. It is open source and as free as you can get:

Anyone is free to copy, modify, publish, use, compile, sell, or distribute the original SQLite code, either in source code form or as a compiled binary, for any purpose, commercial or non-commercial, and by any means.

SQLite3 is the current version; but now there is an update to version 4:

SQLite4 is an alternative, not a replacement, for SQLite3. SQLite3 is not going away. SQLite3 and SQLite4 will be supported in parallel. The SQLite3 legacy will not be abandoned. SQLite3 will continue to be maintained and improved. But designers of new systems will now have the option to select SQLite4 instead of SQLite3 if desired.

The reason for the new version is that some issues in version 3 could not be fixed without breaking compatibility.

So what is new? On a quick read, these seem to be the highlights:

  • A global configuration object (sqlite4_env) which eliminates all use of global and static variables.
  • A new key/value storage engine which has a “greatly simplified” interface and which is pluggable, so you can use a different one if required. The default storage engine is described as a “log-structured merge database”. A B-Tree engine may also be offered later.
  • Primary keys are now real primary keys, as opposed to unique constraints. This speeds up primary key searches.
  • Decimal maths. “All numeric values are represented internally as an 18-digit decimal number with a 3-digit base-10 exponent.” This is advantageous for currency calculations and for cross-platform consistency.
  • Foreign key constraints and recursive triggers on by default
  • Covering support in indexes (when required), to increase the number of queries that can be resolved by querying the indexes alone, at the expense of greater duplication of data

When will SQLite 4 be ready? Code is available but Author D Richard Hipp says:

Everything is still pretty makefile-touchy. Remember, this is like pre-alpha code. It works, but just barely. And things are changing rapidly.

Porting an application from SQLite3 to SQLite4 should be straightforward, according to the author. “An hour or two search-and-replace.”