There’s a new release of an embedded database that is claimed to be the world’s most widely used. The update brings performance enhancements, bug fixes, and some minor new features.

While you may not be particularly familiar with SQLite as a database, the chances are you use it every day, because it is embedded in so many devices and systems.

According to the SQLite website, SQLite is the most widely deployed database engine in the world, and possibly the most widely deployed software module of any type. Every Android device, iPhone and iOS device, and all Macs have SQLite embedded in them. It’s included in all Firefox, Chrome, and Safari web browsers, and every instance of Skype and iTunes. All Dropbox clients use it, as do most TVs and set-top boxes for cable TV, and most multimedia systems in cars.  

As it is open source it also has forks you may have some across. As we reported in April, Yahoo announced SquiDB, an open source SQLite database layer for Android that is designed to let you work with SQLite databases while still giving the power and flexibility of raw SQL.

SQLite is an in-process library that implements a self-contained, serverless, transactional SQL database engine. It doesn’t have a separate server process, and it reads and writes directly to ordinary files. The entire SQL database with multiple tables, indexes, triggers, and views, is contained in a single disk file.

The code for SQLite is in the public domain and is thus free for use for any purpose, commercial or private. The database file format is cross-platform - you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. The advice is to think of SQLite not as a replacement for Oracle but as a replacement for fopen().  

Some people argue that the clue to SQLite is in the name – it’s not a real database, it’s ‘database lite’, because it locks the whole database for writes, it doesn’t enforce column data types, and once you’ve created a table you can’t drop or rename columns. However, it does support ACID transactions (atomic, consistent, isolated and durable); it supports indexes, constraints, foreign keys, and views, and has partial support for triggers; and you can have multiple concurrent connections to a database.  

The new version of SQLite (3.8.11.1) is itself an update of the recent release of SQLite version 3.8.10. This was a scheduled maintenance release with performance improvements, fixes to several arcane bugs found by the AFL fuzzer, the new "sqldiff.exe" command-line utility, and improvements to the documentation.

This updated version restores an undocumented side-effect of PRAGMA cache_size to force the database schema to be parsed if the database has not been previously accessed. The new version has a set of miscellaneous micro-optimizations that the developers say result in 22.3% more work for the same number of CPU cycles relative to the previous release. This means the new version now runs twice as fast as version 3.8.0 and three times as fast as version 3.3.9, measured using cachegrind on the speedtest1.c workload on Ubuntu 14.04 x64 with gcc 4.8.2 and -Os.

The latest release also has a new version of SQLite’s full-text search extension, FTS5. The new version is included as ‘experimental’, but has been improved with a more sophisticated query language and built-in BM25 result ranking. The query language improvements add support for prefix searches using *, quoted phrases, the NEAR operator, and Set operations using AND, OR and NOT.

Comments

Make a Comment or View Existing Comments Using Disqus

or email your comment to: comments@i-programmer.info