node:sqlite: `DatabaseSync.function` wrapper for `sqlite3_create_function_v2` should allow use of `xStep` and `xFinal` parameters

[turbocrime]

i want to create user-defined aggregate functions when using my sqlite database. sqlite expects aggregate functions to be defined with the same api as typical user-defined functions, plus additional aggregation parameters.

node:sqlite provides access to the sqlite function definition api with a wrapper for sqlite3_create_function_v2 as the instance method DatabaseSync.function

but, this wrapper always passes null for the aggregation parameter refs.

node/src/node_sqlite.cc

Lines 669 to 677 in 3b5f235

	int r = sqlite3_create_function_v2(db->connection_,
	*name,
	argc,
	text_rep,
	user_data,
	UserDefinedFunction::xFunc,
	nullptr,
	nullptr,
	UserDefinedFunction::xDestroy);

implementation suggestions

• a separate wrapper function, possibly called aggregate or reduce, for user-defined functions that use these additional callback parameters
• optional parameters for function that accept the callbacks, in the same order as the sqlite api
• additional attributes on the function options parameter object

[turbocrime]

i created this issue from the line highlight menu on node_sqlite.cc, so i didn't get the template prompt or label selection. sorry if this is out-of-order

[cjihrig]

a separate wrapper function, possibly called aggregate or reduce, for user-defined functions that use these additional callback parameters

That is the plan - similar to how it is done in better-sqlite3.

[turbocrime]

is there a place for collected discussion and public work on this builtin?

[cjihrig]

Not that I am aware of.

[geeksilva97]

better-sqlite3 uses sqlite3_create_window_function though.

I think implementing aggregate like this would end up solving #56511 as well.

[turbocrime]

better-sqlite3 uses sqlite3_create_window_function though.

sqlite api treats window aggregate functions as distinct from other types of aggregate functions. a wrapper that combines the metaphors must use the api conditionally.

your linked example also uses the v2 api on line 316

[geeksilva97]

better-sqlite3 uses sqlite3_create_window_function though.

sqlite api treats window aggregate functions as distinct from other types of aggregate functions. a wrapper that combines the metaphors must use the api conditionally.

your linked example also uses the v2 api on line 316

I see.

The linked example is for aggregate function specifically.

From other issues, I can see that node:sqlite tries to be compatible with better-sqlite3. So, I think that, from this comment, using the sqlite3_create_window_function would be suitable.

[cjihrig]

Fixed by #56600