Mon. Feb 17th, 2020

SQLite 3.31.1 released: the most widely deployed database

3 min read

SQLite is an in-process library that implements a self-containedserverlesszero-configurationtransactional SQL database engine. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.

SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. 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. SQLite database files are a recommended storage format by the US Library of Congress. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()

SQLite Remote Code Execution Vulnerability
Image: Wiki Common

SQLite is a compact library. With all features enabled, the library size can be less than 600KiB, depending on the target platform and compiler optimization settings. (64-bit code is larger. And some compiler optimizations such as aggressive function inlining and loop unrolling can cause the object code to be much larger.) There is a tradeoff between memory usage and speed. SQLite generally runs faster the more memory you give it. Nevertheless, performance is usually quite good even in low-memory environments. Depending on how it is used, SQLite can be faster than direct filesystem I/O.

Changelog v3.31

  1. Revert the data layout for an internal-use-only SQLite data structure. Applications that use SQLite should never reference internal SQLite data structures, but some do anyhow, and a change to one such data structure in 3.30.0 broke a popular and widely-deployed application. Reverting that change in SQLite, at least temporarily, gives developers of misbehaving applications time to fix their code.
  2. Fix a typos in the sqlite3ext.h header file that prevented the sqlite3_stmt_isexplain() and sqlite3_value_frombind() interfaces from being called from run-time loadable extensions.

Changes carried forward from version 3.31.0 (2020-01-22):

  1. Add support for generated columns.
  2. Add the sqlite3_hard_heap_limit64() interface and the corresponding PRAGMA hard_heap_limit command.
  3. Enhance the function_list pragma to show the number of arguments on each function, the type of function (scalar, aggregate, window), and the function property flags SQLITE_DETERMINISTICSQLITE_DIRECTONLYSQLITE_INNOCUOUS, and/or SQLITE_SUBTYPE.
  4. Add the aggregated mode feature to the DBSTAT virtual table.
  5. Add the SQLITE_OPEN_NOFOLLOW option to sqlite3_open_v2() that prevents SQLite from opening symbolic links.
  6. Added the “#-N” array notation for JSON function path arguments.
  7. Added the SQLITE_DBCONFIG_TRUSTED_SCHEMA connection setting which is also controllable via the new trusted_schema pragma and at compile-time using the -DSQLITE_TRUSTED_SCHEMA compile-time option.
  8. Added APIs sqlite3_filename_database()sqlite3_filename_journal(), and sqlite3_filename_wal() which are useful for specialized extensions.
  9. Add the sqlite3_uri_key() interface.
  10. Upgraded the sqlite3_uri_parameter() function so that it works with the rollback journal or WAL filename in addition to the database filename.
  11. Provide the ability to tag application-defined SQL functions with new properties SQLITE_INNOCUOUS or SQLITE_DIRECTONLY.
  12. Add new verbs to sqlite3_vtab_config() so that the xConnect method of virtual tables can declare the virtual table as SQLITE_VTAB_INNOCUOUS or SQLITE_VTAB_DIRECTONLY.
  13. Faster response to sqlite3_interrupt().
  14. Added the uuid.c extension module implementing functions for processing RFC-4122 UUIDs.
  15. The lookaside memory allocator is enhanced to support two separate memory pools with different sized allocations in each pool. This allows more memory allocations to be covered by lookaside while at the same time reducing the heap memory usage to 48KB per connection, down from 120KB.
  16. The legacy_file_format pragma is deactivated. It is now a no-op. In its place, the SQLITE_DBCONFIG_LEGACY_FILE_FORMAT option to sqlite3_db_config() is provided. The legacy_file_format pragma is deactivated because (1) it is rarely useful and (2) it is incompatible with VACUUM in schemas that have tables with both generated columns and descending indexes. Ticket 6484e6ce678fffab