Examples of creating aggregate functions

[isNotOkay]

Hello,

Are there any good examples of creating aggregate functions?

We would like to create a loadable extension with both custom scalar and aggregate functions as described in the following blog post:
https://ricardoanderegg.com/posts/extending-sqlite-with-rust/

[gwenn]

Currently, rusqlite doesn't support loadable extension as shared library (see #910) but sqlite3_auto_extension should be fine.
And there are examples here:

rusqlite/src/functions.rs

Lines 949 to 1027 in 38bf316

	struct Sum;
	struct Count;
	impl Aggregate<i64, Option<i64>> for Sum {
	fn init(&self, _: &mut Context<'_>) -> Result<i64> {
	Ok(0)
	}
	fn step(&self, ctx: &mut Context<'_>, sum: &mut i64) -> Result<()> {
	*sum += ctx.get::<i64>(0)?;
	Ok(())
	}
	fn finalize(&self, _: &mut Context<'_>, sum: Option<i64>) -> Result<Option<i64>> {
	Ok(sum)
	}
	}
	impl Aggregate<i64, i64> for Count {
	fn init(&self, _: &mut Context<'_>) -> Result<i64> {
	Ok(0)
	}
	fn step(&self, _ctx: &mut Context<'_>, sum: &mut i64) -> Result<()> {
	*sum += 1;
	Ok(())
	}
	fn finalize(&self, _: &mut Context<'_>, sum: Option<i64>) -> Result<i64> {
	Ok(sum.unwrap_or(0))
	}
	}
	#[test]
	fn test_sum() -> Result<()> {
	let db = Connection::open_in_memory()?;
	db.create_aggregate_function(
	"my_sum",
	1,
	FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
	Sum,
	)?;
	// sum should return NULL when given no columns (contrast with count below)
	let no_result = "SELECT my_sum(i) FROM (SELECT 2 AS i WHERE 1 <> 1)";
	let result: Option<i64> = db.one_column(no_result)?;
	assert!(result.is_none());
	let single_sum = "SELECT my_sum(i) FROM (SELECT 2 AS i UNION ALL SELECT 2)";
	let result: i64 = db.one_column(single_sum)?;
	assert_eq!(4, result);
	let dual_sum = "SELECT my_sum(i), my_sum(j) FROM (SELECT 2 AS i, 1 AS j UNION ALL SELECT \
	2, 1)";
	let result: (i64, i64) = db.query_row(dual_sum, [], |r| Ok((r.get(0)?, r.get(1)?)))?;
	assert_eq!((4, 2), result);
	Ok(())
	}
	#[test]
	fn test_count() -> Result<()> {
	let db = Connection::open_in_memory()?;
	db.create_aggregate_function(
	"my_count",
	-1,
	FunctionFlags::SQLITE_UTF8 | FunctionFlags::SQLITE_DETERMINISTIC,
	Count,
	)?;
	// count should return 0 when given no columns (contrast with sum above)
	let no_result = "SELECT my_count(i) FROM (SELECT 2 AS i WHERE 1 <> 1)";
	let result: i64 = db.one_column(no_result)?;
	assert_eq!(result, 0);
	let single_sum = "SELECT my_count(i) FROM (SELECT 2 AS i UNION ALL SELECT 2)";
	let result: i64 = db.one_column(single_sum)?;
	assert_eq!(2, result);
	Ok(())
	}

[isNotOkay]

Thanks for the quick reply!

We are using SQLiteStudio and are looking for a way to add custom scalar/aggregate functions via loadable extensions. Do you think this could be achieved with the usage of sqlite3_auto_extension somehow?

[gwenn]

I don't think so, you need to follow these instructions from https://ricardoanderegg.com/posts/extending-sqlite-with-rust/ :

Using this rusqlite fork from Genomicsplc. For more context why this is needed, see #910.
UPDATE: I’ve created a new fork which merges the changes in that PR with the current latest rusqlite release (v0.27.0) here.

or any alternative like sqlite-loadable
or adapt something like this.

We hope that, one day, rusqlite will support generating loadable extension directly...

[isNotOkay]

Thanks for the links👍. That would be great!

[gwenn]

There is also sqlite3_ext.