|SQLite 3.44 Extends Aggregate Functions|
|Written by Kay Ewbank|
|Monday, 06 November 2023|
SQLite 3.44 has been released with new C-language APIs and extensions to aggregate functions.
SQLite is a widely deployed database, and is compact, with a library size of under 600KB with all features enabled. It is an in-process library that implements a self-contained, serverless, zero-configuration, transactional SQL database engine. In addition, the developers describe it as a language-agnostic development environment, with high-quality language kernels for languages including Python, C++, R and Julia.
The new C-language APIs are both aimed at database connection client handling, for get and set clientdata. The functions are used to associate one or more named pointers with a database connection.
The improvements to the aggregate functions handling mean you can now include an optional ORDER BY as the final parameter, to define the order in which the function processes its arguments. The arguments to the function are processed in the order specified, which can be important for functions like string_agg() and json_group_array().
String concatenation has been overhauled in this release. Until now, SQLite has used a group_concat function for string concatenation, which is similar to the same function in MySQL. Other SQL databases such as SQL Server and PostgreSQL have an alternative function called string_agg. SQLite now accepts this as an alias for group_concat, so bringing it into line with the other databases. Two new scalar string functions have also been added in this release. Concat(arg, ...) joins non-null arguments without a separator, while concat_ws(sep, arg, ...) joins non-null arguments using the separator.
The other improvement worth noting is more options for data formatting, with support for ISO 8601 dates and times of the formats YYYY-MM-DD, HH:MM:SS and HH:MM. There's also new support for 12-hour clock format dates with and without leading zeros, and more options for day of the month and weekday.
Elsewhere, integrity checking now works with virtual tables, you can use full-text search tables in triggers, and optimizations have been added for query planning for partial index scans.
SQL 3.44 is available now.
or email your comment to: email@example.com