TW Blog :: Archives for March 2004

SQLite Custom Functions In PHP 5

Relates to PHP and Databases

I cannot get enough of the new features in PHP 5, and the SQLite extension is no exception. Besides the speed and simplicity, one of the most interesting features is the ability to define custom functions for direct use in the SQL statement.

In a content based public domain application, dynamic creation generally involves three steps:

1. Retrieve the data from a database
2. Use array functions to format the data for display (beyond what the SQL layer's extended functions offer)
3. Plug the data into the appropriate markup using some form of template system

The sqlite_create_function() effectively removes the need for step 2 altogether - instead define and associate formatting functions with the sqlite_db object and use them in the SQL statement.

For example, regularly I will use an image_manager table to store references to image files across a broad range of content. With MySQL, the CONCAT() function allows concatenation of the image file name (stored data) and the directory path (parameter) within the resultset. But the resultset must still pass through a formatter function to confirm the existence of the image, and get additional information for the file (eg the height and width). In contrast, with SQLite, the formatter function is defined and attached directly to the sqlite_db object.

function get_image_file($data) {
  $file = IMG_DIR . $data . ".jpg";
  if (is_file($file)) {
    $stats = getimagesize($file);
    $str = "<img src=\"/g/$data.jpg\" "
         . "width=\"$stats[0]\" "
         . "height=\"$stats[1]\" />";  
  }
  else {
    $str = "NO FILE";  
  }
  return $str;  
}
$db = new sqlite_db("db.content");
$db->create_function("get_image_file",
                     "get_image_file", 1);

Now the SQL statement can include the user-defined function to retrieve the image files directly into the resultset.:

$res = $db->array_query(
         "SELECT image_desc, "
       . "get_image_file(image_file) as image "
       . "FROM image_manager "
       . "WHERE image_ref = $param", SQLITE_ASSOC);

The resultset $res is now ready to send direct to the appropriate markup for display. This powerful utility has a range of uses from advanced content parsing, for example with PEAR::HTML_BBCodeParser, to just replicating some of the advanced SQL functions of other RDBMS with added user flexibility.

The ability to use PHP from within SQL allows you to simplify the actual script making it accessible to larger audience of developers. This allows PHP to be used as a templating engine that simply populates HTML structures with database data. In many instances this can simplify the code so much that there is no need to place a templating system on top of PHP. Beyond the code simplification, this also improves performance and reduces the script's memory footprint, since no data manipulations need to occur in user-space. SQLite Introduction, By Ilia Alshanetsky

Here are some useful links to more SQLite related pages.

• Zend Intro to SQLite in PHP 5
• SQL Functions in SQLite - list of accepted SQL functions
• SQLite Control Centre - Early release replication of MySQL Control Centre
• When To Use SQLite - When to
• Database Speed Comparisons - SQLite, MySQL, PostrgreSQL

Posted on Monday, Mar 29, 2004 at 00:09:38.