{"id":99469,"date":"2021-03-18T10:23:26","date_gmt":"2021-03-18T08:23:26","guid":{"rendered":"https:\/\/prohoster.info\/blog\/novosti-interneta\/vypusk-subd-sqlite-3-35"},"modified":"2021-03-18T10:23:26","modified_gmt":"2021-03-18T08:23:26","slug":"vypusk-subd-sqlite-3-35","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/news\/vypusk-subd-sqlite-3-35","title":{"rendered":"Release of SQLite Database 3.35","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"
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. <\/p>\n
Key Changes: <\/p>\n
\n
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.\n
Support for the 'ALTER TABLE DROP COLUMN' expression has been implemented to remove columns from a table and clear any previously stored data in that column.\n
The implementation of the UPSERT (insert-or-update) operation has been expanded, allowing through expressions like 'INSERT \u2026 ON CONFLICT DO NOTHING\/UPDATE' to ignore errors or perform an update instead of an insert in case of data addition failure through 'INSERT' (for example, if the record already exists, an UPDATE can be performed instead of an INSERT). In the new version, multiple 'ON CONFLICT' blocks can be specified, which will be processed in the order they appear. In the last 'ON CONFLICT' block, specifying the conflict definition parameter for using 'DO UPDATE' is allowed to be omitted.\n
In DELETE, INSERT, and UPDATE operations, support for the RETURNING expression has been implemented, which allows displaying the contents of a deleted, inserted, or modified record. For example, the expression 'insert into \u2026 returning id' will return the identifier of the added row, and 'update \u2026 set price = price * 1.10 returning price' will return the modified price value.\n
For Common Table Expressions (CTE), which allow the use of temporary named result sets defined by the WITH operator, the selection of 'MATERIALIZED' and 'NOT MATERIALIZED' modes is now permitted. 'MATERIALIZED' means caching the query specified in the view into a separate physical table with subsequent data retrieval from this table, while 'NOT MATERIALIZED' will involve repeated queries every time the view is accessed. Initially, SQLite used 'NOT MATERIALIZED' mode by default, but now for CTEs used more than once, it has changed to 'MATERIALIZED'.\n
Reduced memory consumption during VACUUM operations for databases containing very large values of types TEXT or BLOB.\n
Work has been done to improve the performance of the optimizer and query planner:\n
\n
Optimizations have been added for using min and max functions with the 'IN' expression.\n
The execution of the EXISTS operator has been accelerated.\n
Subquery deployment from UNION ALL expressions used in JOINs has been implemented.\n
An index has been utilized for IS NOT NULL expressions.\n
Conversion of 'x IS NULL' and 'x IS NOT NULL' to FALSE or TRUE has been ensured for columns that have the 'NOT NULL' attribute.\n
Foreign key checks have been bypassed in UPDATE if the operation does not change columns related to the foreign key.\n
It is permitted to move parts of the WHERE clause into subqueries that contain window functions, provided that these parts are restricted to working with constants and copies of expressions from the \"PARTITION BY\" clauses used in window functions. <\/ul>\n
Changes to the command line interface:\n
\n
The command \".filectrl data_version\" has been added.\n
Support for passing output to a handler invoked using unnamed pipes (\"|\") has been added to the \".once\" and \".output\" commands.\n
Arguments \"stmt\" and \"vmstep\" have been added to the \".stats\" command to output statistics on expressions and virtual machine counters. <\/ul>\n<\/ul>\n