SQLite is a lightweight relational database engine, accessible through the SQL language . Unlike traditional database servers, such as MySQL or PostgreSQL, its distinguishing feature is not replicating the usual client-server architecture, but rather integrating directly into programs.
The entire database (statements, tables, indexes, and data) is stored in a platform-independent file. Thanks to its extremely lightweight nature, among other reasons, it is used in many consumer programs and is also very popular in embedded systems, including the latest smartphones.
Unlike client-server database management systems, the SQLite engine is not a separate process that the main program communicates with. 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, SQLite version 3.32.0 was announced, which includes several changes, most notably an improved version of the ANALYZE command . This allows for the partial collection of statistics from very large databases without a full index analysis. The limit on the number of records scanned for a single index is set using the new "PRAGMA analysis_limit" directive.
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 VFS checksum layer has been added, appending 8-byte checksums to the end of each data page in the database and verifying them every time data is read from the database. This intermediate layer allows for the detection of database corruption resulting from 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.
The INSERT and UPDATE expressions now always applied fix column type conditions before the CHECK calculation block, and the limit on the number of parameters has been increased from 999 to 32766.
Other notable changes in this new version include:
- 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 learn more about the release of this new version, you can consult the list of changes at the following link.
Download
For those interested in installing this new version of SQLite on their system, the packages can be obtained from its official website in the downloads section, where both the source code (for compilation) and pre-compiled packages are available.