The release of SQLite 3.45 has been published, a lightweight DBMS designed as a dynamic library. The SQLite code is released into the public domain, meaning it can be used freely and without restrictions for any purpose. Financial support for the SQLite developers is provided by a specially formed consortium, which includes companies such as Bentley, Bloomberg, Expensify, and Navigation Data Standard.
Key Changes:
- All SQL functions for working with JSON format have been rewritten and translated into a new internal JSONB parse tree format, which is serialized and can be stored in the database to avoid re-parsing when using JSON values. The new versions of the functions that generate JSON now produce a binary JSONB format instead of text. The function json_valid() has been enhanced with an optional second argument, allowing for a bitmask to specify valid JSON characteristics (for example, specifying 0x04 will recognize JSONB blobs as valid, while 0x01 will validate text JSON in RFC-8259 format, and 0x05 will consider both formats valid).
- The virtual table FTS5, used for full-text search, has added the tokendata option, allowing for custom tokenizers.
- By default, the SQLITE_DIRECT_OVERFLOW_READ optimization is enabled, where overflow pages larger than the standard b-tree page size are read directly from the file, bypassing the cache. To disable this during compilation, the option "-DSQLITE_DIRECT_OVERFLOW_READ=0" can be used.
- The query planner has improved the efficiency of transitive constraint optimization and enhanced index ignoring for those deemed low-quality during the ANALYZE operation.
- The command line interface has improved UTF-8 content display on Windows. Automatic detection of the CLI interface has been implemented when executing .dump scripts, making the necessary adjustments to settings such as ".dbconfig defensive off" and ".dbconfig dqs_dll on."
- User-defined SQL functions now include the SQLITE_RESULT_SUBTYPE property, allowing for invocation from the sqlite3_result_subtype() function to inspect argument subtypes.
Additionally, it is worth noting the development in experimental builds of SQLite of a new transaction logging mode — WAL2 ("PRAGMA journal_mode = wal2"), where two WAL files ("database-wal" and "database-wal2") are used instead of one. When writing data to the database, new data is added to the first WAL file. If the first WAL file reaches a sufficiently large size, it switches to writing to the second file. After switching to the second WAL file, the first becomes available for committing changes (checkpoint) and overwriting. Consequently, when the second WAL file reaches a large size, it switches back to the first one, and so on, which resolves the issue of uncontrolled growth of the WAL file in situations where a transaction isn't committed for a long time or long reading processes are performed.
Source: opennet.ru
