SQLite 3.38 has been released, a lightweight database management system presented as a dynamic library. The SQLite code is released into the public domain, meaning it can be used freely and without restrictions for any purpose. Financial support for SQLite developers is provided by a specially established consortium, which includes companies such as Adobe, Oracle, Mozilla, Bentley, and Bloomberg.
Key Changes:
- Support for the operators -> and ->>, which simplify data extraction in JSON format, has been added. The syntax of the new operators is compatible with MySQL and PostgreSQL.
- Functions for working with JSON data have been included in the core, which previously required a build with the flag ‘-DSQLITE_ENABLE_JSON1’. A flag ‘-DSQLITE_OMIT_JSON’ has been added to disable JSON support.
- The unixepoch() function has been added, returning epoch time (the number of seconds since January 1, 1970).
- Modifiers ‘auto’ and ‘julianday’ have been implemented for time-related functions.
- The SQL function printf() has been renamed to format() for better compatibility with other DBMS (the old name support has been retained).
- The interface sqlite3_error_offset() has been added to simplify the identification of error locations in queries.
- New software interfaces have been added to the implementation of virtual tables: sqlite3_vtab_distinct(), sqlite3_vtab_rhs_value(), and sqlite3_vtab_in(), as well as new types of operators SQLITE_INDEX_CONSTRAINT_LIMIT and SQLITE_INDEX_CONSTRAINT_OFFSET.
- The command-line interface ensures proper handling of tab and newline characters in text output in multi-column modes. Support for using options ‘—wrap N’, ‘—wordwrap on’, and ‘—quote’ when outputting in multiple columns has been added. The .import command ensures proper column name adjustments.
- In the query planner, a probabilistic structure called a bloom filter has been employed to speed up the execution of large analytical queries, used to determine the existence of an element in a set. For optimizing processing of UNION and UNION ALL blocks that cover SELECT expressions with ORDER BY sections, a balanced merge tree has been implemented.
Additionally, it is worth noting the release of sqlite-utils version 3.24, which includes utilities and a library for manipulating SQLite database files. Supported operations include directly importing JSON, CSV, or TSV data into a database file with automatic schema creation, executing SQL queries on CSV, TSV, and JSON files, performing full-text searches in the database, transforming data and storage schema when ALTER TABLE is not applicable (for example, to change column types), and extracting columns into separate tables.
Source: opennet.ru
