SQLite 3.35 release has been released and in this new release of this database manager mathematical function additions are highlighted, as well as support for the ALTER TABLE DROP COLUMN expression to drop columns from a table, improved operations, and more.
For those unfamiliar with the SQLite package it is a lightweight DBMS, designed as a plugin library. The SQLite code is distributed as public domain, that is, it can be used without restrictions and free of charge for any purpose.
Main new features of SQLite 3.35
As mentioned at the beginning, in this new version built-in math functions added (log2 (), cos (), tg (), exp (), ln (), pow (), etc.) that can be used in SQL. To enable the built-in functions, an assembly with the "-DSQLITE_ENABLE_MATH_FUNCTIONS" option is required.
Expression "ALTER TABLE DROP COLUMN" now supports to drop columns from a table and delete data previously stored in this column.
The implementation of the UPSERT operation (add or modify), which allows you to use expressions such as "INSERT ON CONFLICT DO NOTHING / UPDATE" to ignore an error or perform an update instead of inserting if it is impossible to add data via "INSERT" (for example, if a record, UPDATE can be done instead of INSERT).
In the new version, it is allowed to specify several blocks «IN CONFLICT«, Which will be processed in order. In the last "ON CONFLICT" block, it is allowed not to specify the conflict definition parameter to use "DO UPDATE".
The operations DELETE, INSERT and UPDATE support the RETURNING expression, that can be used to display the content of a deleted, inserted, or modified record. For example, "insert into ... returning ID" will return the added row identifier, and "update ... set price = price * 1.10 returning price" will return the value of the updated price.
For generalized table expressions (Common Table Expression, CTE), which allows the use of temporally named result sets, using WITH asked the operator, approved the choice of the modes «MATERIALIZED» and «NOT MATERIALIZED».
- "MATERIALIZED" means to cache the query specified in the view in a separate physical table with subsequent retrieval of data from this table.
- And with "NOT MATERIALIZED", repeated queries will be performed every time the view is accessed. Initially, SQLite defaulted to "NOT MATERIALIZED" but has now been changed to "MATERIALIZED" for CTEs used more than once.
Of the other changes that stand out from this new version:
- Reduced memory consumption when performing VACUUM operations for databases that include very large values with TEXT or BLOB types.
- Work has been done to increase the performance of the optimizer and query scheduler.
- Optimizations were added when using the min and max functions with the expression "IN".
- Execution of the EXISTS statement has been sped up.
- The expansion of the subqueries of the UNION ALL expressions used in JOIN is implemented.
- Index used for IS NOT NULL expressions.
- Conversion of "x IS NULL" and "x IS NOT NULL" to FALSE or TRUE was provided for columns with the "NOT NULL" flag.
- The foreign key check in UPDATE is skipped if the operation does not change the columns associated with the foreign key.
- Moving parts of the WHERE clause to subqueries containing window functions is allowed if these parts are limited to working with constants and copies of "PARTITION BY" clause expressions used in window functions.
Changes to the command line interface:
- Added ".filectrl data_version" command.
- The ".once" and ".output" commands added support for passing the output to a called controller using unnamed pipes ("|").
- The ".stats" command added the "stmt" and "vmstep" arguments to display statistics on virtual machine counters and expressions.
Finally if you are interested in knowing more about it about this new version of SQLite, you can check the details by going to the following link.