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.
|
