SQLite for Mac

• Fix the WAL-reset database corruption bug.
• Enhance ALTER TABLE to permit adding and removing NOT NULL and CHECK constraints.
• Add the Query Result Formatter (QRF) library for formatting the results of SQL queries for human readability on a fixed-pitch font screen.
• Add the format method to the TCL Interface so that QRF is accessible from TCL.
• QRF is used for result formatting in the CLI, resulting in improved display capabilities.
• New built-in SQL functions: json_array_insert(), and jsonb_array_insert().

Renovations to the CLI:

• Major enhancements to the .mode command.
• Improved result formatting, due to the addition of the QRF extension. For example, numeric values are now right-justified by default in tabular output modes.
• The default output mode for interactive CLI sessions now uses QRF to display query results in boxes formed using Unicode box-drawing characters, for improved legibility. Batch CLI sessions use the legacy output format for compatibility.
• Bare (unquoted) semicolons at the end of dot-commands are silently ignored.  ← Potential incompatibility!
• Fix the .testcase and .check commands so that they actually work, and use those commands in scripts that part of the standard SQLite test suite included with the source tree.
• Command-line arguments that match *.sql or *.txt and are the names of non-empty files are read and interpreted as scripts of SQL statements and/or dot-commands.
• The argument to the ".timer" command can now be "once", to run the timer on only the next SQL statement.
• The new "--timeout S" option to the ".progress" dot-command causes SQL statements to interrupt after S seconds.

New C-language interfaces:

• sqlite3_str_truncate()
• sqlite3_str_free()
• sqlite3_carray_bind_v2()
• Add the SQLITE_PREPARE_FROM_DDL option to sqlite3_prepare_v3() which permits virtual table implementations to safely prepare SQL statements that are derived from the database schema.
• Added the SQLITE_UTF8_ZT constant which can be used as the encoding parameter to sqlite3_result_text64() or sqlite3_bind_text64() to indicate that the value is UTF-8 encoded and zero terminated.
• The SQLITE_LIMIT_PARSER_DEPTH option is added to sqlite3_limit().
• The SQLITE_DBCONFIG_FP_DIGITS option is added to sqlite3_db_config(). See also item 8b below.

Query planner improvements:

• Always use a sort-and-merge algorithm for EXCEPT, INTERSECT, and UNION, since this is almost always faster than using a hash table.
• Improvements to join order selection in large multi-way joins on a star schema.
• Enhance the EXISTS-to-JOIN optimization so that the inserted JOIN terms are not required to be on the inner-most loops, as long as all dependencies for the EXISTS-to-JOIN loops are in outer loops.
• Enhance the omit-noop-join optimization so that it is able to omit a chain of joins that do not affect the output.
• Allow queries that use "GROUP BY e1 ORDER BY e2" where e1 and e2 are identical apart from ASC/DESC sort-orders to be optimized using a single index.
• Allow virtual tables to optimize DISTINCT in cases where the result-set of a query does not exactly match the ORDER BY clause.

Improvements to floating-point ↔ text conversions.

• Reimplemented to improve performance.
• Rounding is now done by default to 17 significant digits, instead of 15, as was the case for all prior versions. The sqlite3_db_config(SQLITE_DBCONFIG_FP_DIGITS) API (item 6g above) can change this, if desired.
• The body of TEMP triggers may now modify and/or query tables in the main schema.
• Enhance VACUUM INTO so that if a URI filename is used as the target and that filename has a reserve=N query parameter with N between 0 and 255, then the reserve amount for the generated database copy is set to N.
• Add the "-p|--port" option to sqlite3_rsync.
• Discontinue support for Windows RT.