Working with PHP UDFs in SQLite

Although SQLite does not have as much built-in functionality as other major RDBMS packages, such as MySQL, SQLite has the capability to register custom user-defined functions (UDFs) that can be used within SQLite SQL statements as if they were internal SQL functions.

Registering user-defined SQL functions in SQLite is done using the sqlite_create_function() function whose syntax is as follows:

 sqlite_create_function($db, $sql_fname, $php_fname [, $num_params]); 

$db is the SQLite database handle resource, $sql_fname is a string representing the name of the function as it would appear in a SQL query, $php_fname is the PHP function name to call when the SQL UDF is executed, and the optional parameter $num_params is the total number of parameters accepted by the SQL UDF. If the UDF was registered successfully, sqlite_create_function() returns a Boolean TRue; otherwise, it returns false.

To illustrate the sqlite_create_function(), Listing 25.7 uses it to create a simple ADD() SQLite function, which returns the sum of two columns:

Listing 25.7. Using sqlite_create_function()

 <?php     function sqlite_udf_add($op1, $op2) {         return $op1 + $op2;     }     $sqlite = sqlite_open(":memory:");     sqlite_query($sqlite, "CREATE TABLE test(" .                           "value_one INTEGER PRIMARY KEY, " .                           "value_two INTEGER)");     for($count = 0; $count <= 10; $count += 2) {         sqlite_unbuffered_query($sqlite, "INSERT INTO test VALUES(NULL, $count)");     }     if(!sqlite_create_function($sqlite, 'my_add', 'sqlite_udf_add', 2)) {         trigger_error("Could not register custom SQLite UDF 'my add'");     }     $result_arr = sqlite_array_query($sqlite,                                      "SELECT MY_ADD(value_one, value_two) " .                                      "AS sum " .                                      "FROM test",                                      SQLITE_ASSOC);     var_dump($result_arr); ?> 

In Listing 25.7, a simple table has been created in memory; it contains two integer columns: value_one and value_two. After the table has been created, a UDF is registered with the SQL function name of MY_ADD, which accepts two parameters (the numbers to add together). This UDF has been associated with the sqlite_udf_add() PHP function (which, of course, also accepts two parameters). After the function has been registered, it can be used in any SQL query the same as you would any other custom SQL function.

NOTE

Note that user-defined functions are not only tied to a specific database handle resource, they will be lost after the database resource has been destroyed. To create persistent SQL UDFs, use sqlite_popen() to create a persistent database handle resource.

In the preceding example, the data passed to the sqlite_udf_add() function was designed to be numeric data. If you would like to create UDFs that process binary data, be aware that SQLite will not automatically encode or decode the binary data going into and coming from your SQL UDF.

When you're creating UDFs that handle binary data, the binary data must be decoded within your PHP UDF function using the sqlite_udf_decode_binary() function and then reencoded using the sqlite_udf_encode_binary() function before it is returned from the function. The syntax for both functions is as follows:

 sqlite_udf_decode_binary($data); sqlite_udf_encode_binary($data); 

In both instances, $data is the data to encode or decode. Likewise, when either function is executed, it will return the passed data properly encoded or decoded. The following sqlite_udf_process_bindata() example illustrates how they would be used:

 function sqlite_udf_process_bindata($data) {       $working = sqlite_udf_decode_binary($data);       /* Process the binary data stored          in the $working variable */       return sqlite_udf_encode_binary($working); } 

Although obviously useful, the sqlite_create_function() does have its limitations, which stem from the user-defined function's inability to process the entire resultset at once (instead of a single row at a time). To provide this more complex feature, SQLite provides a more advanced version of sqlite_create_function(), called sqlite_create_aggregate(). The syntax of this function is as follows:

 sqlite_create_aggregate($db, $sql_fname, $php_step_func,                         $php_finalize_func [, $num_args]); 

As was the case with sqlite_create_function(), the $db parameter is the database handle resource and $sql_fname is the SQL function name to register or override (if already defined). However, unlike sqlite_create_function(), two PHP function names must be provided in the string parameters $php_step_func and $php_finalize_func. The $php_step_func parameter represents the PHP function to call for every row of the resultset, and $php_finalize_func is called after the entire resultset has been processed. As expected, the optional $num_args parameter represents the number of arguments the custom SQL function will accept.

In practice, when a function registered using sqlite_create_aggregate() is called from within a query, SQLite will call the function whose name is stored in the $php_step_func parameter for every row within the resultset. The syntax for this step function must be as follows:

 function my_sqlite_step_func(&$context [, $param1 [, ...]]) 

$context is a reference parameter and every subsequent parameter is the parameter(s) (if any) passed to the SQLite UDF in the query. The responsibility of the step function is to process the current row's parameters and store any data that will be needed for the next row's processing in the $context reference. The value stored in the $context reference variable will then be available the next time the step function is called (except this time with different values for the function's parameters).

After the entire resultset has been exhausted, SQLite will make a final function call to the function defined by the $php_finalize_func parameter. This function must be defined in a manner similar to the following:

 function my_sqlite_finalize_func(&$context) 

$context is the last value stored in the $context reference variable from the step function. The finalize function serves as the last step before the SQL UDF function returns its result and should return the appropriate value for the SQL UDF function to be used within the query.

To illustrate the use of the sqlite_create_aggregate() function, Listing 25.8 creates a UDF function that concatenates all the columns passed to it and returns a capitalized version of the entire string:

Listing 25.8. Using sqlite_create_aggregate()

 <?php     $values = array("Hello", "SQLite", "and", "PHP!");     function sqlite_udf_step_concat(&$context, $strval) {         $context .= sqlite_udf_decode_binary($strval)." ";     }     function sqlite_udf_finalize_concat(&$context) {         return strtoupper(trim($context));     }     $sqlite = sqlite_open(":memory:");     sqlite_query($sqlite, "CREATE TABLE str_values(value VARCHAR(255))");     foreach($values as $val) {         $strval = sqlite_escape_string($val);         sqlite_unbuffered_query($sqlite,                                 "INSERT INTO str_values VALUES('$strval')");     }     sqlite_create_aggregate($sqlite, 'cap_and_concat',                             'sqlite_udf_step_concat',                             'sqlite_udf_finalize_concat', 1);     $result_arr = sqlite_array_query($sqlite,                                      "SELECT CAP_AND_CONCAT(value) " .                                      "FROM str_values",                                      SQLITE_ASSOC);     var_dump($result_arr); ?> 

In the preceding example, a simple table containing a series of strings is created in memory. An aggregate function CAP_AND_CONCAT() is then created with a step function of sqlite_udf_step_concat() and a finalize function of sqlite_udf_finalize_concat(). When this SQL UDF function is executed, the step function is called for each row that concatenates the passed parameter into its $context parameter. This process continues until there are no more rows (at which point $context contains a string with each value within it separated by a space). SQLite then calls the finalize function, which capitalizes the entire string and returns it back to the SQL query that called it. Although Listing 25.8 is a fairly trivial example of the usefulness of aggregate UDF functions, when used wisely, aggregate functions can provide an incredible amount of power and flexibility to your SQL queries.

Calling PHP Functions in SQL Queries

Along with registering user-defined SQL functions within SQLite, SQLite can also call PHP functions within queries by making use of the PHP() SQLite function. This SQLite function has the following syntax:

 PHP(function_name [, param1 [, param2 [, ...]]]) 

function_name is the name of the PHP function to call, followed by the values to use as parameters for that PHP function. This SQL function is particularly useful as an alternative to taking advantage of internal PHP functions that don't require the complexity of a user-defined wrapper. Listing 25.9 demonstrates the use of the PHP() SQLite function to call PHP's strtoupper() function:

Listing 25.9. Using the PHP() SQLite Function

 <?php     $values = array("Using", "SQLite", "with", "PHP", "Functions");     $sqlite = sqlite_open(":memory:");     sqlite_query($sqlite, "CREATE TABLE str_values(value VARCHAR(255))");     foreach($values as $val) {         $strval = sqlite_escape_string($val);         sqlite_unbuffered_query($sqlite,                                 "INSERT INTO str_values VALUES('$strval')");     }     $result_arr = sqlite_array_query($sqlite,                                      "SELECT PHP('strtoupper', value) " .                                      "AS value " .                                      "FROM str_values",                                      SQLITE_ASSOC);     var_dump($result_arr); ?>