SQLite 3.38 released: the most widely deployed database
SQLite is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed database in the world with more applications than we can count, including several high-profile projects.
SQLite is an embedded SQL database engine. Unlike most other SQL databases, SQLite does not have a separate server process. SQLite reads and writes directly to ordinary disk files. A complete SQL database with multiple tables, indices, triggers, and views, is contained in a single disk file. The database file format is cross-platform – you can freely copy a database between 32-bit and 64-bit systems or between big-endian and little-endian architectures. These features make SQLite a popular choice as an Application File Format. SQLite database files are a recommended storage format by the US Library of Congress. Think of SQLite not as a replacement for Oracle but as a replacement for fopen()
SQLite is a compact library. With all features enabled, the library size can be less than 600KiB, depending on the target platform and compiler optimization settings. (64-bit code is larger. And some compiler optimizations such as aggressive function inlining and loop unrolling can cause the object code to be much larger.) There is a tradeoff between memory usage and speed. It generally runs faster the more memory you give it. Nevertheless, performance is usually quite good even in low-memory environments. Depending on how it is used, SQLite can be faster than direct filesystem I/O.
Changelog v3.38
- Added the -> and ->> operators for easier processing of JSON. The new operators are compatible with MySQL and PostgreSQL.
- The JSON functions are now built-ins. It is no longer necessary to use the -DSQLITE_ENABLE_JSON1 compile-time option to enable JSON support. JSON is on by default. Disable the JSON interface using the new -DSQLITE_OMIT_JSON compile-time option.
- Enhancements to date and time functions:
- Added the unixepoch() function.
- Added the auto modifier and the julianday modifier.
- Rename the printf() SQL function to format() for better compatibility. The original printf() name is retained as an alias for backwards compatibility.
- Added the sqlite3_error_offset() interface, which can sometimes help to localize an SQL error to a specific character in the input SQL text, so that applications can provide better error messages.
- Enhanced the interface to virtual tables as follows:
- Added the sqlite3_vtab_distinct() interface.
- Added the sqlite3_vtab_rhs_value() interface.
- Added new operator types SQLITE_INDEX_CONSTRAINT_LIMIT and SQLITE_INDEX_CONSTRAINT_OFFSET.
- Added the sqlite3_vtab_in() interface (and related) to enable a virtual table to process IN operator constraints all at once, rather than processing each value of the right-hand side of the IN operator separately.
- CLI enhancements:
- Columnar output modes are enhanced to correctly handle tabs and newlines embedded in text.
- Added options like “–wrap N”, “–wordwrap on”, and “–quote” to the columnar output modes.
- Added the .mode qbox alias.
- The .import command automatically disambiguates column names.
- Use the new sqlite3_error_offset() interface to provide better error messages.
- Query planner enhancements:
- Use a Bloom filter to speed up large analytic queries.
- Use a balanced merge tree to evaluate UNION or UNION ALL compound SELECT statements that have an ORDER BY clause.
- The ALTER TABLE statement is changed to silently ignores entries in the sqlite_schema table that do not parse when PRAGMA writable_schema=ON.