SQLite is a lightweight relational database engine, accessible through the SQL language. Unlike traditional database servers, such as MySQL or PostgreSQL, its peculiarity is not to reproduce the usual client-server scheme, but to integrate directly into programs.
The complete database (declarations, tables, indexes and data) it is stored in a platform independent file. Thanks to its extreme lightness, among others, it is used in many consumer programs and is also very popular in embedded systems, including the most modern smartphones.
Unlike the client-server database management system, the SQLite engine is not a standalone process with which the main program communicates. Instead, the SQLite library is linked to the program becoming an integral part of it.
The program uses the functionality of SQLite through simple calls to subroutines and functions. This reduces the latency in accessing the database, since function calls are more efficient than inter-process communication.
The entire database (definitions, tables, indexes, and the data itself) are saved as a single standard file on the host machine. This simple design is achieved by locking the entire database file at the beginning of each transaction.
About the new version of SQLite 3.32.0
Recently, the new version of SQLite 3.32.0 was announced, in which several changes have been implemented and among them a rough version of the ANALYZE command is highlighted, which allows several very large databases to deal with a partial collection of statistics and without a full analysis of the indices. The limit on the number of records when scanning a single index is set using the new directive "PRAGMA analysis_limit".
Another change that comes to this new version of SQLite is a new virtual table "Bytecode", which provides information about the bytecode of prepared statements.
In addition, a checksum VFS layer has been added, adding 8-byte checksums to the end of each page of data in the database and checking each time it is read from the database. The middle layer can detect database corruption as a result of random bit distortion on storage devices.
On the other hand, a new SQL function iif (X, Y, Z) was added, returning the value Y if the expression X is true, or Z otherwise.
INSERT and UPDATE expressions now always applied pinning column type conditions before the CHECK calculation block and the limit on the number of parameters has been increased from 999 to 32766.
Of the other changes that stand out of this new version:
- Added the UINT sort sequence extension with the implementation of sort sequences that take integers into the text into account to sort this text in numerical order.
- In the command line interface, the options "–csv", "–ascii" and "–skip" were added to the command ".import".
- The ".dump" command allows the use of multiple LIKE templates with merging in the output of all tables corresponding to the specified masks. Added ".oom" command for debug builds.
- The –bom option has been added to the ".excel", ".output" and ".once" commands. Added –schema option to ".filectrl" command.
- The ESCAPE expression specified with the LIKE operator now overrides wildcards, which is consistent with PostgreSQL behavior.
Finally if you want to know more about it about the release of this new version, you can check the list of changes In the following link.
For those who are interested in being able to install this new version of SQLite on their system, they will be able to get the packages from their official website in its download section where both the source code (for compilation), as well as precompiled packages are available.