SQLite 3.46 has been released, a lightweight database management system presented as a shared library. The SQLite code is released into the public domain, meaning it can be used without restrictions and free of charge for any purpose. Financial support for SQLite developers is provided by a specially created consortium.
Key Changes:
- The option to use the underscore «_» as a separator in numbers for improved readability of numeric literals (for example, 1_234_567) has been added.
- A new SQL function json_pretty() has been introduced to generate more readable output in JSON format, formatted using indentation for block highlighting.
- The capabilities of the "PRAGMA optimize" operation, which optimizes the database and all data schemas for better query performance, have been expanded. An automatically applied limit on database analysis duration has been implemented to exclude excessively long processing times for large databases. A new bitmask "PRAGMA optimize(0x10000)" has been added for separate checking of updates across all tables. Automatic re-analysis of tables without sqlite_stat1 entries is also ensured.
- Support for substitutions %G, %g, %U, and %V has been added to the SQL function strftime().
- New modifiers ceiling and floor have been added to SQL functions for date and time handling to manage ambiguous dates that arise when shifting dates by a set number of months or years. Modifiers utc and localtime are ignored if SQLite knows that the time is already in UTC or localtime representation.
- Optimizations have been made to the query planner. For INSERT expressions containing thousands of values in the VALUES section, the "VALUES-as-coroutine" optimization is enabled by default, allowing for approximately double the speed in parsing such expressions and halving memory consumption. For queries like "SELECT count(DISTINCT col) FROM ...", the use of indexes is permitted even if the size of index entries is not less than that of table entries. The logic for identifying situations where constants are specified in SQL function arguments has been improved. Push-down optimization for WHERE expressions containing unrelated subqueries is applied.
- The logic for handling stack overflow has been changed — when memory for the parser stack is insufficient, it now attempts to allocate additional memory from the heap instead of throwing a "parser stack overflow" error.
- Control characters are allowed in JSON string literals as per JSON5. The logic for the operators "->" and "->>" has been altered so that when the right-hand operand consists only of digits, it is processed as a string, similar to PostgreSQL.
- In the DEFAULT value for table columns, large hexadecimal literals are now permitted.
Source: opennet.ru
