Working with User-Defined Functions

A very powerful feature of the PHP SQLite extension is the ability to define functions in PHP that can be registered and executed within an SQL statement. It is therefore possible for PHP developers to extend the functionality of SQLite using a familiar language.

The example in Listing 5.9 defines a trivial function in PHP that reverses each word in a string. This differs from the usual behavior of strrev(), which reverses the entire string. The example uses sqlite_create_function() to register a user-defined function, word_reverse(), for use within SQLite.

Listing 5.9. Adding a User-Defined Function to SQLite in PHP

 <?php function reverse_words($string) {   $w = explode(" ", $string);   for($i=0; $i<count($w); $i++) {     if ($i > 0)       $ret .= " ";     $ret .= strrev($w[$i]);   }   return($ret); } if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   sqlite_create_function($db, "word_reverse", "reverse_words", 1);   $sql = "SELECT word_reverse('The quick brown fox');";   $res = sqlite_query($db, $sql);   echo sqlite_column($res, 0); } ?>P 

First, we declare the function reverse_words() in PHP. Using explode() to break up the passed-in stringassuming words are separated by a space characterwe then call strrev() on each word in turn and piece the string back together:

 function reverse_words($string) {   $w = explode(" ", $string);   for($i=0; $i<count($w); $i++) {     if ($i > 0)       $ret .= " ";     $ret .= strrev($w[$i]);   }   return($ret); } 

After opening a SQLite connection to webdb as database resource $db, we use sqlite_create_function() to register the user-defined function.

 sqlite_create_function($db, "word_reverse", "reverse_words", 1); 

There are four arguments to sqlite_create_function(). First the database resource, in this case $db, is supplied. The second parameter is the function name that will be used by SQLite, and the third is the name of the PHP function from which it is derived.

In our example, we used word_reverse() for the SQLite function and reverse_words() for the PHP function to tell the two apart. There is actually no reason that they cannot share the same name if desired.

The fourth parameter is optional and indicates the number of parameters that are passed to the UDF. In this case, only a single string parameter is required, so the value is 1. This acts as a hint to the SQLite parser, and an error will be returned if the number of parameters passed when the function is called does not match this value. If the PHP function can accept a variable number of parameters, this value can be omitted.

The output from Listing 5.9 is as you might expect:

 ehT kciuq nworb xof 

A function registered with sqlite_create_function() does not exist outside of the process in which it is executed. Because the function is created within a given database resource, a user-defined function can be called within PHP functions that have access to the same resource, so the scope of a UDF within a script is not an issue. However, the function is not saved to the database permanently.

Where a persistent database connection has been used, sqlite_create_function() must still be called again before the function is available to SQLite. If PHP reattaches to a persistent SQLite connection in which a user-defined function has been declared and you attempt to call that function, the following warning will be displayed rather than a no such function error.

 sqlite_query(): this function has not been correctly defined for this request 

It is possible to use an existing function name as the name of a UDF in the second parameter to sqlite_create_function(), which will overload the behavior of that function without warning. All subsequent calls to that function will execute the PHP function specified rather than the built-in function.

The php() Function

When PHP first opens a SQLite database, it automatically registers the function php(), which provides a quick way to access any PHP function without having to add it as a UDF with sqlite_create_function(). Both built-in PHP functions and those defined in your script code can be referenced this way.

Listing 5.10 shows how the PHP function ucwords() can be called from within SQLite using php() to capitalize the first character of each word in a string.

Listing 5.10. Calling a PHP Function from SQLite Using php()

 <?php if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   $sql = "SELECT php('ucwords', 'The quick brown fox');";   $res = sqlite_query($db, $sql);   echo sqlite_column($res, 0); } ?> 

The output from Listing 5.10 is as expected:

 The Quick Brown Fox 

Creating Aggregating Functions

The process to create an aggregating user-defined functionone that applies a calculation across all the rows returned by a query, or across similar rows indicated by a GROUP BY clauseis slightly different than a regular function.

The function sqlite_create_aggregate() requires two functions to manage the aggregate; one that is called for each row on the result set returnedthe step functionand another that is called once after all the rows have been processedthe finalize function.

A context variable is passed by reference so that both functions can access the data being aggregated.

The example in Listing 5.11 creates and demonstrates a new aggregating function to calculate the median average of a set of numbers. The median is a value such that half the values in the set are above the median and half are below it. By contrast, the built in avg() function computes the mean averagethe sum of all the values divided by the number of items in the dataset.

Listing 5.11. Adding an Aggregating UDF with sqlite_create_aggregate()

 <?php function median_step(&$context, $num) {   $context[] = $num; } function median_finalize(&$context) {   sort($context);   $count = count($context);   if ($count%2 == 1) {  // Odd number of values     $median = $context[floor($count/2)];   }   else { // Even number of values     $median = ($context[$count/2] + $context[($count/2)-1] ) / 2;   }   return($median); } if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   sqlite_create_aggregate($db, "median", "median_step", "median_finalize");   $sql = "SELECT median(num), avg(num) FROM numbers";   $res = sqlite_query($db, $sql);   echo "Median is ";   echo sqlite_column($res, 0);   echo "<br />\n";   echo "Mean is ";   echo sqlite_column($res, 1);   echo "<br />\n"; } ?> 

Before we can use our new function we need to create the table numbers and insert some values. The script in Listing 5.12 creates the table and inserts the squares of numbers one to nine in turn. Because of the geometric nature of this sequence, we can use it to show how the median and mean averages differ using our new function.

Listing 5.12. Inserting a Sequence of Square Numbers

 <?php if (!$db = sqlite_open("webdb", 0666, &$errmessage)) {   echo "Could not open database:<br />\n";   echo $errmessage;   exit; } else {   $sql = "CREATE TABLE numbers (num INTEGER)";   $res = sqlite_query($db, $sql);   for ($i=1; $i<=9; $i++) {     $val = $i * $i;     $sql = "INSERT INTO numbers (num) VALUES ($val)";     $res = sqlite_query($db, $sql);   } } ?> 

The following line of code shows the complete sequence of numbers inserted. From looking at this sequence in numerical order, we can see that the median will be 25, as there are four numbers on either side:

 1 4 9 16 25 36 49 64 81 

Let's look back through Listing 5.11 in more detail. The step function simply builds an array in PHP, adding each value in turn:

 function median_step(&$context, $num) {   $context[] = $num; } 

Notice however that $context is passed by reference in the function parameters. The value does not need to be returned.

The finalize function has to do a little more work. After the array has been created, we sort it into numerical order and find the number of items. Again, $context is passed by reference so that the array built by median_step() is available within this scope:

 function median_finalize(&$context) {   sort($context);   $count = count($context); 

If there are an odd number of values, the median is the middle value itself. If there are an even number of values, the median is the mid-point between the two middle values.

 if ($count%2 == 1) {  // Odd number of values   $median = $context[floor($count/2)]; } else {  // Even number of values   $median = ($context[$count/2] + $context[($count/2)-1] ) / 2; } 

Lastly, we return the computed median value.

   return($median); } 

To register the aggregating function median(), we call sqlite_create_aggregate() as follows to reference the two functions that we just defined:

 sqlite_create_aggregate($db, "median", "median_step", "median_finalize"); 

To compare the median of the values in numbers with its average, this SQL statement is executed:

 $sql = "SELECT median(num), avg(num) FROM numbers"; $res = sqlite_query($db, $sql); 

The values displayed are

 Median is 25 Mean is 31.6666666666667 

Working with Binary Data in UDFs

If the data you are passing to or from a UDF may not be binary safe, you must encode it to avoid possible problems. A NUL byte is a string terminator, so if one could appear anywhere other than the end of a string you may get unexpected results, for instance.

The function sqlite_udf_encode_binary() takes a string parameter and returns a binary-safe encoded version. The reverse is sqlite_udf_decode_binary(). For performance reasons, PHP will not perform such encoding automatically itself.