The SQLite 3.35 release has been published, a lightweight database available as a shared library. SQLite code is released as public domain, meaning it can be used unrestricted and free of charge for any purpose. Financial support for SQLite developers is provided by a specially formed consortium, which includes companies such as Adobe, Oracle, Mozilla, Bentley, and Bloomberg.
Key Changes:
- Built-in mathematical functions (log2(), cos(), tan(), exp(), ln(), pow(), etc.) have been added that can be used in SQL. To enable the built-in functions, a build with the option "-DSQLITE_ENABLE_MATH_FUNCTIONS" is required.
- Support for the "ALTER TABLE DROP COLUMN" statement has been implemented, allowing columns to be removed from a table and clearing previously stored data in that column.
- The implementation of the UPSERT operation (insert-or-modify) has been expanded, allowing through expressions like "INSERT … ON CONFLICT DO NOTHING/UPDATE" to ignore the error or to perform an update instead of an insert when it is not possible to add data through "INSERT" (for example, if a record already exists, an UPDATE can be performed instead of INSERT). In the new version, specifying multiple "ON CONFLICT" blocks is allowed, which will be processed in order. In the last "ON CONFLICT" block, it is permitted not to specify the conflict definition parameter for using "DO UPDATE".
- Support for the RETURNING expression has been implemented in DELETE, INSERT, and UPDATE operations, which allows outputting the content of the deleted, inserted, or modified record. For example, the expression "INSERT INTO … RETURNING id" will return the identifier of the added row, and "UPDATE … SET price = price * 1.10 RETURNING price" will return the updated price value.
- For Common Table Expressions (CTE), which allow the use of temporary named result sets defined using the WITH clause, the modes "MATERIALIZED" and "NOT MATERIALIZED" have been enabled. "MATERIALIZED" implies caching the specified view of the query in a separate physical table with subsequent data retrieval from that table, while in the case of "NOT MATERIALIZED", repeated queries will be made each time the view is accessed. Initially, SQLite defaulted to "NOT MATERIALIZED", but now for CTEs used more than once, it has been changed to "MATERIALIZED".
- Reduced memory consumption during VACUUM operations for databases containing very large values of types TEXT or BLOB.
- Work has been done to improve the performance of the optimizer and query planner:
- Optimizations have been added for using the min and max functions with the expression 'IN'.
- The execution of the EXISTS operator has been accelerated.
- Subquery deployment from UNION ALL expressions used in JOINs has been implemented.
- An index has been utilized for IS NOT NULL expressions.
- Conversion of 'x IS NULL' and 'x IS NOT NULL' to FALSE or TRUE is provided for columns with the 'NOT NULL' attribute.
- Foreign key checks have been bypassed in UPDATE if the operation does not change columns related to the foreign key.
- Moving parts of the WHERE clause into subqueries containing window functions is allowed if these parts are limited to working with constants and copies of expressions from the 'PARTITION BY' clauses used in window functions.
- Changes to the command line interface:
- The command '.filectrl data_version' has been added.
- Support for redirecting output to a handler called with unnamed pipes ('|') has been added to the commands '.once' and '.output'.
- The command '.stats' has been enhanced with the 'stmt' and 'vmstep' arguments for outputting statistics on expressions and virtual machine counters.
Source: opennet.ru
