Aggregate User Defined Functions

[chinmay.sahoo]

Besides normal UDFs similar to those we used to generate our index from a trigger, it is also possible to
define a UDF for aggregation functions. In the following example, we calculate the average length of the words in our dictionary:

<?php
$db = new SQLiteDatabase("./crm.db", 0666, &$error)
or die("Failed: $error");

After opening the database, we define two functions that will be called during the aggregation. The first one is called for each queried record, and the second one is called when all records have been returned.

function average_length_step(&$ctxt, $string)
{
if (!isset($ctxt['count'])) {
$ctxt['count'] = 0;
}
if (!isset($ctxt['length'])) {
$ctxt['length'] = 0;
}
$ctxt['count']++;
$ctxt['length'] += strlen($string);
}

The $ctxt parameter can be used to maintain state between different records; in this case, we use the parameter as an array to store the number of words and the total lengths of all the words we’ve seen. We also need to initialize the two elements of the array to hide the "Warning: Undefined index: count" warnings that PHP will issue otherwise

function average_length_finalize(&$ctxt)
{
return sprintf(
"Avg. over {$ctxt['count']} words is %.3f chars.",
$ctxt['length'] / $ctxt['count']);
}