SQLite 3.53.0

On April 9th, version 3.53.0 of the compact cross-platform embedded database management system was released. SQLite. The project code is written in C and is distributed as public domain.

Changes:

• Bug fixed WAL-reset database corruption bug. See previous news SQLite 3.51.3 includes a fix for a potential database corruption issue in WAL mode..
• A library for formatting SQL query results has been added Query Result Formatter (QRF) to ensure readability on screen with a monospaced font.
• A method has been added format to the TCL interface for accessing QRF from TCL.
  • QRF is now utilized in the sqlite command-line utility to format query results, enhancing their display.
• New SQL features:
  • The ALTER TABLE command has been improved to allow for adding and removing NOT NULL and CHECK constraints.
  • The REINDEX EXPRESSIONS command performs reindexing of expression indexes. Useful for recovering obsolete expression indexes.
  • Now TEMP triggers can modify and/or query data from the main schema tables.
  • The VACUUM INTO: when used with a file name in URI format that contains the query parameter reserve=N (where N is a number from 0 to 255), the reserved size for the created database copy is set to N.
• New SQL functions have been added:
  • json_array_insert()
  • jsonb_array_insert()
• Updates in the command-line utility:
  • Significant improvements to the .mode.
  • Thanks to the QRF extension result formatting has been enhanced. For example, numbers are now right-aligned by default in table output mode..
  • By default, QRF is now used during interactive use of the command-line utility to display query results in blocks formed with Unicode block-drawing characters, improving readability. The deprecated output format is used for batch command execution to ensure compatibility.
  • Single (non-quoted) semicolons at the end of dot commands are ignored without warning. This may cause incompatibility!
  • The .testcase and .check commands have been fixed. They now work correctly and are used in scripts included in the standard SQLite test suite that comes with the source code.
  • Command-line arguments that match the *.sql or *.txt patterns and represent non-empty file names are read and interpreted as scripts containing SQL statements and/or [dot commands]((https://sqlite.org/cli.html#dotcmd).
  • Now the argument for the .timer command can specify once to trigger the timer only when the following SQL statement is executed.
  • The new parameter —timeout S for the .progress command causes SQL statements to be interrupted after S seconds.
  • The .indexes command has been changed so that the PATTERN argument now matches the index name instead of the indexed table name (making the PATTERN argument truly useful). Additionally, several new options have been added to the .indexes command.
• Improvements in the C API:
  • sqlite3_str_truncate()
  • sqlite3_str_free()
  • sqlite3_carray_bind_v2()
  • In the function sqlite3_prepare_v3() an option has been added SQLITE_PREPARE_FROM_DDL, which allows virtual table implementations to safely prepare SQL statements obtained from the database schema.
  • A constant has been added SQLITE_UTF8_ZT, which can be used as an encoding parameter in the functions sqlite3_result_text64() or sqlite3_bind_text64() to indicate that the value is encoded in UTF-8 and null-terminated.
  • In the function sqlite3_limit() an option has been added SQLITE_LIMIT_PARSER_DEPTH.
  • In the function sqlite3_db_config() an option has been added SQLITE_DBCONFIG_FP_DIGITS. See section 9b below.
• Improvements to the query planner:
  • For EXCEPT, INTERSECT, and UNION, the sort-and-merge algorithm is always used, as it almost always performs faster than using hash tables.
  • Improvements in the join order selection mechanism for executing large multi-way joins in a star schema.
  • Enhanced optimization of converting EXISTS to JOIN such that the inserted JOIN conditions do not necessarily need to be within inner loops, provided that all dependencies for the EXISTS-to-JOIN transformations are in outer loops.
  • The omit-noop-join optimization has been improved to skip a chain of joins that do not affect the result.
  • Query optimization for queries using GROUP BY e1 ORDER BY e2, where e1 and e2 are identical except for the sort order ASC/DESC, using a single index.
  • Optimization of the DISTINCT operator in virtual tables in cases where the result set does not fully match the ORDER BY condition.
• In session extension new interfaces have been added, allowing the application to make changes one at a time in the sqlite3_changegroup object:
  • sqlite3changegroup_change_begin()
  • sqlite3changegroup_change_blob()
  • sqlite3changegroup_change_double()
  • sqlite3changegroup_change_int64()
  • sqlite3changegroup_change_null()
  • sqlite3changegroup_change_text()
  • sqlite3changegroup_change_finish()
  • sqlite3changegroup_config()
• Improvements in converting floating-point numbers ↔ text.
  • The code has been completely rewritten for performance enhancement.
  • Rounding is now performed by default to 17 significant digits instead of 15, as was the case in all previous versions. This value can be changed if necessary using the function sqlite3_db_config with the option (see section 6g above there). SQLITE_DBCONFIG_FP_DIGITS The ability to
• self-repair the index to resolve the issue In the console utility obsolete expression indexes.
• sqlite3_rsync the -p|--port option has been added. Support has been discontinued for
• Windows RT JavaScript/WASM.
• A VFS opfs-wl has been added, functionally identical to opfs, but using Web Locks for locking, allowing fairer distribution of locks than the opfs protocol. opfs-wl requires the Atomics.waitAsync() function; hence newer browser versions are needed for its operation than for opfs.
  • Critical vulnerabilities in GStreamer, CUPS, wolfSSL, OpenSSL, OpenClaw, Nix, and the Linux kernel.

Source: linux.org.ru