The new version of SQLite 3.33 has already been released and in it they integrate some changes quite good, such as for example the UPDATE FROM support following the syntax by PostgreSQL, in addition, the improve the ieee754 extension to support IEEE 754 binary64 numbers as well as a increased the maximum size of database files to 281 TB.
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.
It is a software package that is known for its construction and operation using an LFS-10.0 platform.
Main new features of SQLite 3.33
One of the most important changes in this new version is the UPDATE FROM implementation to update the table content based on a selection from another table this using the corresponding PostgreSQL syntax.
According to the SQLite team, the idea of UPDATE FROM is a SQL extension that Allows an UPDATE statement to be pushed by other tables in the database.
The "target" table is the specific table that is updated. Other relational database engines also implement UPDATE FROM, but because the construct is not part of the SQL standards, each product implements UPDATE FROM differently.
The SQLite implementation strives to be PostgreSQL compliant. The SQL Server and MySQL implementations of the same idea work a little differently.
On the part of the integrity control of PRAGMA, it is now possible to randomly check only certain related tables and indexes (always checks the entire database first), as PRAGMA performs a database consistency check and low-level formatting. Checks for table or index entries that are out of sequence, CHECK errors, UNIQUE and NOT NULL constraints, free list integrity, and missing or excess index entries, missing pages, and malformed documents.
Regarding the improvements in the extension ieee754, now it is possible to work with IEEE 754 binary64 numbers. The text produced by the form of an argument to the ieee754 () function is human readable, but difficult to use as part of a larger expression.
That is why the routines ieee754_mantissa () and ieee754_exponent () have been added to return the M and E values corresponding to the F value of its only argument.
Enhancements have been made to the query planneras performance has been improved on "SELECT min (x) FROM t WHERE and IN (?,?,?)" when index t (x, y) is present.
In WAL mode (Write-Ahead Logging), if a write operation fails, which generates an inconsistency in the data of the shm file, the following transactions can now restore the integrity of the shm file when there are active read transactions, instead of showing an SQLITE_PROTOCOL error.
Of the other changes that are implemented In this new version, the following stand out:
- On the other hand, another important change is the increase of the maximum size of the database to 281 TB.
- The CLI has built-in decimal extensions and ieee754.
- Added a decimal extension with arbitrary precision decimal arithmetic functions, there are three math functions available including decimal_add, decimal_sub, and decimal_mul (these three functions add, subtract, and multiply their arguments, respectively).
- In addition, new "box", "json", "markdown" and "table" output format modes have also been added to the command line interface (CLI).
- In column output mode, columns are automatically expanded by the content of the longest row.
- The output mode "quote" takes into account the value of the separator set by the command ".separator".
Finally if you are interested in knowing more about it about this new version, you can check the details by going to the following link.