Mon. Nov 18th, 2019

SQLite 3.30 released: the most widely deployed database

2 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.30

  1. Add support for the FILTER clause on aggregate functions.
  2. Add support for the NULLS FIRST and NULLS LAST syntax in ORDER BY clauses.
  3. The index_info and index_xinfo pragmas are enhanced to provide information about the on-disk representation of WITHOUT ROWID tables.
  4. Add the sqlite3_drop_modules() interface, allowing applications to disable automatically loaded virtual tables that they do not need.
  5. Improvements to the .recover dot-command in the CLI so that it recovers more content from corrupt database files.
  6. Enhance the RBU extension to support indexes on expressions.
  7. Change the schema parser so that it will error out if any of the type, name, and tbl_name columns of the sqlite_master table have been corrupted and the database connection is not in writable_schema mode.
  8. The PRAGMA function_listPRAGMA module_list, and PRAGMA pragma_list commands are now enabled in all builds by default. Disable them using -DSQLITE_OMIT_INTROSPECTION_PRAGMAS.
  9. Add the SQLITE_DBCONFIG_ENABLE_VIEW option for sqlite3_db_config().
  10. Added the TCL Interface config method in order to be able to disable SQLITE_DBCONFIG_ENABLE_VIEW as well as control other sqlite3_db_config() options from TCL.
  11. Added the SQLITE_DIRECTONLY flag for application-defined SQL functions to prevent those functions from being used inside triggers and views.
  12. The legacy SQLITE_ENABLE_STAT3 compile-time option is now a no-op.