CHAPTER 6 – SQLite – Aggregate User Defined Functions

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 initial- ize 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']); } The finalize function returns a string containing the text "Avg. over x words is y chars.", where x and y are filled in dependent on the data. $db->createAggregate( 'average_length', 'average_length_step', 'average_length_finalize' ); The createAggregate() method creates our aggregate function. The first parameter is the name of the function that can be used from SQL queries; the second one is the function that is executed for each record (also called step); and the third parameter is the name of the function that is run when all records are selected. $avg = $db->singleQuery( "SELECT average_length(word) FROM dictionary"); echo "$avgn"; ?> Here, we simply execute the query using our newly defined function and echo the result, which should look like something like this: Average over 28089 words is 10.038 chars.