SQLite is a lightweight database engine
Announced the launch of the new version of SQLite 3.44, which features improved support for new features, as well as optimizations, various enhancements, and more.
SQLite is a lightweight relational database engine accessible through the SQL language. Unlike traditional database servers, such as MySQL or PostgreSQL, its particularity is not to reproduce the usual client-server schema, but to integrate directly into programs. In fact, the entire database (statements, tables, indexes, and data) is stored in a platform-independent file.
Thanks to its extreme lightness, SQLite is one of the most used database engines in the world. It is used in many consumer programs and is also very popular in embedded systems, including most modern smartphones.
Main new features of SQLite 3.44
In this new version of SQLite 3.44 that is presented, the support for new features aggregated that allow an expression «ORDER BY« after its last parameter to process the function arguments in the specified order. This can be important for functions like string_agg() and json_group_array() .
Another of the changes that stands out in the new version are the query planner optimizations with partial index scans when specifying a constant value for a table column in the WHERE clause, this increases the probability that the partial index is a covering index. It is mentioned that due to identified regressions, the view scanning optimization added in version 3.42.0 has been disabled.
In SQLite 3.44, the command “PRAGMA integrity_check” now implements a content consistency check on multiple integrated virtual tables using the new xIntegrity method. This works for extensions FTS3, FTS4, FTS5, RTREE and GEOPOLY.
In addition to this, in the configuration SQLITE_DBCONFIG_DEFENSIVE protection is provided against enabling writable_schema PRAGMA mode, since previously writable_schema could be activated but it didn't actually allow the schema to be writable and now it simply cannot be activated.
In the version of SQLite 3.44 for Windows it stands out that when compiled with the Microsoft C compiler, the configuration SQLITE_USE_SEH (Structured Exception Handling) is now enabled by default. It can be disabled using -DSQLITE_USE_SEH=0. Also the CLI for Windows now defaults to UTF-8 for both input and output on platforms that support it. The –no-utf8 option is available to disable UTF8 support.
Furthermore, SQLite now performs runtime detection of whether or not the underlying hardware supports long double with a higher precision than double and uses appropriate floating point routines.
Of the other changes that stand out from this new version:
- Added support for scalar SQL functions concat() and concat_ws(), compatible with PostgreSQL, MS SQL Server and MySQL.
- Added support for aggregate function string_agg(), compatible with PostgreSQL and MS SQL Server.
The strftime() SQL function now supports specifiers "%e", "%F", "%I", "%k", "%l", "%p", "%P", "%R", "%T" and "%u". - Many error messages associated with the instruction CREATE TABLE now displayed after executing the statement CREATE TABLE, rather than after the table is used for the first time.
- Built-in virtual tables were tagged FTS3, FTS4, FTS5, RTREE and GEOPOLY as SQLITE_VTAB_INNOCUOUS so they can be used within triggers in high security implementations.
- The instruction PRAGMA case_SENSITIVE_like is deprecated, as its use when the schema contains LIKE operators can result in reports of database corruption by PRAGMA Integrity_check .
- Disabled view scan optimization as it was causing multiple performance regressions.
Finally, if you are interested in knowing more about it You should know that the SQLite code is distributed in the public domain, that is, it can be used without restrictions and free of charge for any purpose.
You can check more details about this new release at the following link.