The release of SQLite 3.38, a lightweight DBMS designed as a plug-in library, has been published. The SQLite code is distributed in the public domain, i.e. can be used without restrictions and free of charge for any purpose. Financial support for SQLite developers is provided by a specially created consortium, which includes companies such as Adobe, Oracle, Mozilla, Bentley and Bloomberg.
Major changes:
- Added support for the -> and ->> operators to make it easier to retrieve JSON data. The syntax of the new operators is compatible with MySQL and PostgreSQL.
- The main structure includes functions for working with data in JSON format, which previously required an assembly with the β-DSQLITE_ENABLE_JSON1β flag to connect. Added "-DSQLITE_OMIT_JSON" flag to disable JSON support.
- Added unixepoch() function that returns epoch time (number of seconds since January 1, 1970).
- The "auto" and "julianday" modifiers have been implemented for the functions of working with time.
- The printf() SQL function has been renamed to format() to improve compatibility with other DBMSs (the old name has been retained).
- Added interface sqlite3_error_offset() to make it easier to locate errors in a query.
- New APIs have been added to virtual table implementations: sqlite3_vtab_distinct(), sqlite3_vtab_rhs_value() and sqlite3_vtab_in(), as well as new operator types SQLITE_INDEX_CONSTRAINT_LIMIT and SQLITE_INDEX_CONSTRAINT_OFFSET.
- The command line interface provides correct handling of tabulation and newline characters in text displayed in multi-column modes. Added support for using the "--wrap N", "--wordwrap on" and "--quote" options when displaying in multiple columns. In the .import command, the column names are corrected.
- To speed up the execution of large analytical queries, the query planner uses a probabilistic bloom filter structure to determine if an element is in a set. A balanced merge tree is used to optimize the processing of UNION and UNION ALL blocks that span SELECT statements with ORDER BY clauses.
Additionally, we can note the publication of the version of the sqlite-utils 3.24 suite, which includes utilities and a library for manipulating files from the SQLite database. Operations such as direct uploading of JSON, CSV or TSV data to a database file with automatic creation of the necessary storage schema, executing SQL queries on CSV, TSV and JSON files, performing full-text search in the database, converting data and storage schemas in situations where ALTER is not applicable are supported. TABLE (for example, to change the type of columns), extracting columns into separate tables.
Source: opennet.ru