The SQLite 3.42 release has been published, a lightweight database system presented as a dynamic library. The SQLite code is released into the public domain, meaning it can be used without restrictions and freely for any purpose. Financial support for SQLite developers is provided by a specially formed consortium that includes companies such as Adobe, Oracle, Mozilla, Bentley, and Bloomberg.
Key Changes:
- For full-text indexes FTS5, the secure-delete command has been implemented, which performs a complete cleanup of all residual data after deletion.
- In functions for processing JSON data, support for JSON5 extensions has been implemented.
- The query planner has been improved. By default, the count-of-view optimization is enabled. Processing of unused columns in subqueries has been excluded. The push-down optimization implementation has been improved.
- An option ‘—unsafe-testing’ has been added to the command line interface, allowing the use of certain dangerous commands such as ‘.testctrl’, intended for testing purposes. In safe mode (‘—safe’), the commands ‘.log on’ and ‘.log off’ are permitted. Support for the delimiter ‘—‘ has been added, after which option processing is disabled for arguments. Parameters ‘:inf’ and ‘:nan’ related to infinity and NaN values have been added.
- In the names of SQL functions defined on the application side, overlap with the keywords CROSS, FULL, INNER, LEFT, NATURAL, OUTER, and RIGHT is allowed.
- The capabilities of PRAGMA integrity_check have been expanded. A check for specifying NaN values in columns with the NOT NULL condition has been added. The error messages have been improved for better clarity.
- In the session extension, the interception of changes from ROWID-less tables has been allowed.
- In the time and date functions, a ‘subsec’ modifier has been added for the use of fractions of seconds.
- The default recursion depth for arrays and JSON objects has been reduced from 2000 to 1000.
In addition, a release of the DuckDB database management system version 0.8.0 has been created, an enhanced variant of SQLite, optimized for executing analytical queries that cover a significant portion of stored data, such as aggregating all table contents or merging several large tables. It provides an extended SQL dialect that includes additional capabilities for processing very complex and time-consuming queries, as well as supporting complex types (arrays, structures, unions), concurrent execution of multiple queries, and executing queries directly from files in CSV, JSON, and Parquet formats. There is also the ability to import from PostgreSQL databases.
In addition to the SQLite shell code, the project uses a separate parser from PostgreSQL, a Date Math component from MonetDB, its own implementation of window functions (based on the Segment Tree Aggregation algorithm), a regular expression processor based on the RE2 library, its own query optimizer, a Multi-Version Concurrency Control (MVCC) mechanism for managing concurrent task execution, as well as a vectorized query execution engine based on the Hyper-Pipelining Query Execution algorithm, which allows processing large sets of values in a single operation. The project code is distributed under the MIT license.
In the new version of DuckDB:
- New expressions ``PIVOT`` and ``UNPIVOT`` have been added for transforming rows into columns and vice versa.
- Parallelism during data import and export has been improved. By default, reading from CSV files is implemented in multiple threads and multi-threaded writing is available when using Parquet, CSV, and JSON formats.
- An operator ``**`` has been added for recursive directory traversal when determining file paths (for example, FROM ``'data/glob/crawl/stackoverflow/**/*.csv';``).
- Support for merge (JOIN) operations on time series data (slices of parameter values over specified time intervals) has been added, where the criteria for merging records is based on approximate rather than exact matching of values in the time field (for example, records can be merged if their timestamps differ by no more than 1 minute).
- A deferred loading of database metadata has been implemented, significantly speeding up the startup time of the database management system.
- Support for connecting custom functions in Python has been added.
- Support for ADBC (Arrow Database Connectivity) API has been added for data transfer using Apache Arrow.
- Binding for integration with Swift code has been implemented.
- The behavior of the division operator ("/") has been changed to perform floating-point calculations by default instead of integer operations. A new operator "//" has been proposed for integer division. The old behavior can be restored using the setting "SET integer_division=true;".
- The method of handling null values in sorting has been changed from "NULLS FIRST" to "NULLS LAST", i.e., NULL values will now appear at the end of the list instead of the beginning. The old behavior can be restored using the setting "SET default_null_order='nulls_first';".
Additionally, the CG/SQL project developed by Facebook has been noted, providing a code generator for stored procedures using SQLite. CG/SQL allows the creation of stored procedures in a special T-SQL dialect (Transact-SQL) that permits calling standard C library functions and accessing data in SQLite. The created stored procedures are compiled into C code that uses the SQLite C API to perform specified actions and handle complex queries. Compiled stored procedures can be connected to programs in C, Java, and Objective-C. The project code is written in C and distributed under the MIT license.
Source: opennet.ru
