SQLite Release 3.22.0 On 2018-01-22
- The output of sqlite3_trace_v2() now shows each individual SQL statement run within a trigger.
- Add the ability to read from WAL mode databases even if the application lacks write permission on the database and its containing directory, as long as the -shm and -wal files exist in that directory.
- Added the rtreecheck() scalar SQL function to the R-Tree extension.
- Added the sqlite3_vtab_nochange() and sqlite3_value_nochange() interfaces to help virtual table implementations optimize UPDATE operations.
- Added the sqlite3_vtab_collation() interface.
- Added support for the "^" initial token syntax in FTS5.
- New extensions:
- The Zipfile virtual table can read and write a ZIP Archive.
- Added the fsdir(PATH) table-valued function to the fileio.c extension, for listing the files in a directory.
- The sqlite_btreeinfo eponymous virtual table for introspecting and estimating the sizes of the btrees in a database.
- The Append VFS is a VFS shim that allows an SQLite database to be appended to some other file. This allows (for example) a database to be appended to an executable that then opens and reads the database.
- Query planner enhancements:
- The optimization that uses an index to quickly compute an aggregate min() or max() is extended to work with indexes on expressions.
- The decision of whether to implement a FROM-clause subquery as a co-routine or using query flattening now considers whether the result set of the outer query is "complex" (if it contains functions or expression subqueries). A complex result set biases the decision toward the use of co-routines.
- The planner avoids query plans that use indexes with unknown collating functions.
- The planner omits unused LEFT JOINs even if they are not the right-most joins of a query.
- Other performance optimizations:
- A smaller and faster implementation of text to floating-point conversion subroutine: sqlite3AtoF().
- The Lemon parser generator creates a faster parser.
- Use the strcspn() C-library routine to speed up the LIKE and GLOB operators.
- Improvements to the command-line shell:
- The ".schema" command shows the structure of virtual tables.
- Added support for reading and writing SQL Archive files using the .archive command.
- Added the experimental .expert command
- Added the ".eqp trigger" variant of the ".eqp" command
- Enhance the ".lint fkey-indexes" command so that it works with WITHOUT ROWID tables.
- If the filename argument to the shell is a ZIP archive rather than an SQLite database, then the shell automatically opens that ZIP archive using the Zipfile virtual table.
- Added the edit() SQL function.
- Added the .excel command to simplify exporting database content to a spreadsheet.
- Databases are opened using Append VFS when the --append flag is used on the command line or with the .open command.
- Enhance the SQLITE_ENABLE_UPDATE_DELETE_LIMIT compile-time option so that it works for WITHOUT ROWID tables.
- Provide the sqlite_offset(X) SQL function that returns the byte offset into the database file to the beginning of the record holding value X, when compiling with -DSQLITE_ENABLE_OFFSET_SQL_FUNC.
- Bug fixes:
- Infinite loop on an UPDATE that uses an OR operator in the WHERE clause. Problem introduced with 3.17.0 and reported on the mailing list about one year later. Ticket47b2581aa9bfecec.
- Incorrect query results when the skip-ahead-distinct optimization is used. Ticket ef9318757b152e3a.
- Incorrect query results on a join with a ORDER BY DESC. Ticket 123c9ba32130a6c9.
- Inconsistent result set column names between CREATE TABLE AS and a simple SELECT. Ticket 3b4450072511e621
- Assertion fault when doing REPLACE on an index on an expression. Ticket dc3f932f5a147771
- Assertion fault when doing an IN operator on a constant index. Ticket aa98619ad08ddcab