Creating a custom SQLite Function in PHP

SQLite is available in PHP5 either by compiling PHP5 with SQLite support or enabling the SQLite extension dynamically from the PHP configuration (PHP.ini). A distinct feature of SQLite is that it is an embedded database, and thus offers some features a Server/Hosted database such as the popular MySQL database doesn't.

Creating Custom Functions in SQLite

One of the really cool features of SQLite in PHP is that you can create custom PHP functions, that will be called by SQLite in your queries. Thus you can extend the SQLite functions using PHP.

A custom Regexp function for SQLite in PHP

// create a regex match function for sqlite
sqlite_create_function($db, 'REGEX_MATCH', 'sqlite_regex_match', 2);
function sqlite_regex_match($str, $regex) {
 if (preg_match($regex, $str, $matches)) {
  return $matches[0];
 }
 return false;
}

The above PHP code will create a custom function called REGEX_MATCH for the SQLite connection referenced by $db. The REGEX_MATCH SQLite function is implemented by the sqlite_regex_match user function we define in PHP.

Here is an example query that makes use of the custom function we created. Notice that in the SQLite query, we call our custom function REGEX_MATCH:

$query = 'SELECT REGEX_MATCH(link, \'|http://[^/]+/|i\') AS domain, link, COUNT(link) AS total'
 .' FROM links WHERE domain != 0'
 .' GROUP BY domain'
 .' LIMIT 10';
$result = sqlite_query($db, $query);

This will make SQLite call the PHP function sqlite_regex_match for each database table row that is goes over when performing the select query, sending it the link field value as the first parameter, and the regular expression string as the second parameter. PHP will then process the function and return its results to SQLite, which continues to the next table row.

Custom Functions in SQLite compared to MySQL

In comparison with MySQL, you cannot create a custom function in PHP that mysql will use. MySQL allows creation of custom functions, but they have to be written in MySQL. Thus you cannot extend MySQL's query functionality with PHP.

I believe the reason for this is simply because having a callback function called on the client, by the database, over a Client-Server model for each row that has to be processed would be just inefficient. Imaging processing 100,000 rows in a MySQL database and having MySQL make a callback to PHP over a TCP connection, the overhead of sending the data back and forth for the callback would be way too much.
With an embedded database like SQLite, this isn't the case since making the actual communication between the language and the embedded database does not pose such a high overhead.