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.

sqlite

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.

sqlite

More Information

SQLite Site

Related Articles

SQLite 3.43 Released

SQLite Improves Nulls Support

SQLite 3.27 Introduces Vacuum Into

SQLite Adds Zipfile Support

SQLite 3.20 Improves Query Planner

To be informed about new articles on I Programmer, sign up for our weekly newsletter, subscribe to the RSS feed and follow us on Twitter, Facebook or Linkedin.

Banner


Huawei Intends To Challenge iOS and Android
24/04/2024

Huawei has just changed its mind and decided to push its HarmonyOS to the rest of the world. A challenger to iOS and Android would be nice, but it is possible?



Pharo 12 Adds New Breakpoint System
09/05/2024

The latest version of Pharo, the open-source Smalltalk-inspired language and core library adds a new breakpoint model based on the debug point system.


More News

raspberry pi books

 

Comments




or email your comment to: comments@i-programmer.info