Logging to a Database

A frequently requested feature in Zeus has been the ability to log to a database. With the Perl extensions this can be done in quite a small amount of code. Here's how.

Consider a simple database schema containing three fields: requested URL, remote host, and user-agent. The following code, when installed on the logging stage from the Administration Server user interface, will insert a row into this database after each request.

package DBLog;

use strict;
use Zeus::ModPerl;
use Zeus::ModPerl::Constants qw( OK );
use DBI;

use constant SQL_LOG_ACCESS =>
  q|INSERT INTO access (url, remote_host, user_agent) VALUES (?, ?, ?)|;


sub handler
{
   eval
   {
      my $r           = shift( @_ );
      my $db          = $r->dir_config( 'LogDatabase' );
      my $dbuser      = $r->dir_config( 'LogUser' );
      my $dbpassword  = $r->dir_config( 'LogPassword' );
      my $url         = $r->uri();
      my $remote_host = $r->get_remote_host();
      my $user_agent  = $r->header_in( 'User-Agent' );

      my $dbh = DBI->connect( $db, $dbuser, $dbpassword, { AutoCommit => 1    } );
      my $sth = $dbh->prepare( SQL_LOG_ACCESS )
        or die( $dbh->errstr() );
      $sth->execute( $url, $remote_host, $user_agent ) or die( $dbh->errstr() );
   };
   # Print a warning if there is one.
   warn $@ if $@; 
   return OK;
}

1;

To install this example using the Administration Server user interface, put the above code in $ZEUSHOME/zperl/local/lib/site_perl/DBLog.pm, enable Perl Extensions in the user interface, add the paths that should be controlled by this, and add DBLog as a logging handler for those paths.

This handler uses the dir_config method to retrieve its configuration. You will need to set Perl configuration variables called LogDatabase, LogUser, and LogPassword; this can be done either using an .htaccess file or through the Admin Server user interface.

The eval{} block lets the code fail/die without causing the web server's log hook to abort in the event the database becomes inaccessible.

Content Manager [Administrator] 16 December 2005