view release on metacpan or  search on metacpan

DBILogger.pm  view on Meta::CPAN

package Apache::DBILogger;

require 5.004;
use strict;
use Apache::Constants qw( :common );
use DBI;
use Date::Format;

$Apache::DBILogger::revision = sprintf("%d.%02d", q$Revision: 1.20 $ =~ /(\d+)\.(\d+)/o);
	$Apache::DBILogger::VERSION = "0.93";

sub reconnect($$) {
	my ($dbhref, $r) = @_;

	$$dbhref->disconnect;

	$r->log_error("Reconnecting to DBI server");

	$$dbhref = DBI->connect($r->dir_config("DBILogger_data_source"), $r->dir_config("DBILogger_username"), $r->dir_config("DBILogger_password"));
  
  	unless ($$dbhref) { 
  		$r->log_error("Apache::DBILogger could not connect to ".$r->dir_config("DBILogger_data_source")." - ".$DBI::errstr);
  		return DECLINED;
  	}
}

sub logger {
	my $r = shift->last;

	my $s = $r->server;
	my $c = $r->connection;

	my %data = (
		    'server'	=> $s->server_hostname,
		    'bytes'     => $r->bytes_sent,
		    'filename'	=> $r->filename || '',
		    'remotehost'=> $c->remote_host || '',
		    'remoteip'  => $c->remote_ip || '',
		    'status'    => $r->status || '',
		    'urlpath'	=> $r->uri || '',
		    'referer'	=> $r->header_in("Referer") || '',	
		    'useragent'	=> $r->header_in('User-Agent') || '',
		    'timeserved'=> time2str("%Y-%m-%d %X", time),
		    'contenttype' => $r->content_type || ''
	);

	if (my $user = $c->user) {
		$data{user} = $user;
	}

	$data{usertrack} = $r->notes('cookie') || '';

	my $dbh = DBI->connect($r->dir_config("DBILogger_data_source"), $r->dir_config("DBILogger_username"), $r->dir_config("DBILogger_password"));
  
  	unless ($dbh) { 
  		$r->log_error("Apache::DBILogger could not connect to ".$r->dir_config("DBILogger_data_source")." - ".$DBI::errstr);
  		return DECLINED;
  	}
  	
  	my @valueslist;
  	
  	foreach (keys %data) {
		$data{$_} = $dbh->quote($data{$_});
		push @valueslist, $data{$_};
	}

	my $table = $r->dir_config("DBILogger_table") || 'requests';

	my $statement = "insert into $table (". join(',', keys %data) .") VALUES (". join(',', @valueslist) .")";

	my $tries = 0;
	
  	TRYAGAIN: my $sth = $dbh->prepare($statement);
  	
  	unless ($sth) {
  		$r->log_error("Apache::DBILogger could not prepare sql query ($statement): $DBI::errstr");	
  		return DECLINED;
  	}

	my $rv = $sth->execute;

	unless ($rv) {
		$r->log_error("Apache::DBILogger had problems executing query ($statement): $DBI::errstr");
	#	unless ($tries++ > 1) {
	#		&reconnect(\$dbh, $r);
	#		goto TRYAGAIN;
	#	}
	}
	
	$sth->finish;


	$dbh->disconnect;

	OK;
}

# #perl pun: <q[merlyn]> windows is for users who can't handle the power of the mac.

sub handler { 
	shift->post_connection(\&logger);
	return OK;
}

1;
__END__

=head1 NAME

Apache::DBILogger - Tracks what's being transferred in a DBI database

=head1 SYNOPSIS

  # Place this in your Apache's httpd.conf file
  PerlLogHandler Apache::DBILogger

  PerlSetVar DBILogger_data_source    DBI:mysql:httpdlog
  PerlSetVar DBILogger_username       httpduser
  PerlSetVar DBILogger_password       secret
  PerlSetvar DBILogger_table          requests
  
Create a database with a table named B<requests> like this:

CREATE TABLE requests (
  server varchar(127) DEFAULT '' NOT NULL,
  bytes mediumint(9) DEFAULT '0' NOT NULL,
  user varchar(15) DEFAULT '' NOT NULL,
  filename varchar(200) DEFAULT '' NOT NULL,
  remotehost varchar(150) DEFAULT '' NOT NULL,
  remoteip varchar(15) DEFAULT '' NOT NULL,
  status smallint(6) DEFAULT '0' NOT NULL,
  timeserved datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  contenttype varchar(50) DEFAULT '' NOT NULL,
  urlpath varchar(200) DEFAULT '' NOT NULL,
  referer varchar(250) DEFAULT '' NOT NULL,
  useragent varchar(250) DEFAULT '' NOT NULL,
  usertrack varchar(100) DEFAULT '' NOT NULL,
  KEY server_idx (server),
  KEY timeserved_idx (timeserved)
);

Its recommended that you include

use Apache::DBI;
use DBI;
use Apache::DBILogger;

in your startup.pl script. Please read the Apache::DBI documentation for
further information.

=head1 DESCRIPTION

This module tracks what's being transfered by the Apache web server in a 
SQL database (everything with a DBI/DBD driver).  This allows to get 
statistics (of almost everything) without having to parse the log
files (like the Apache::Traffic module, just in a "real" database, and with
a lot more logged information).

Apache::DBILogger will track the cookie from 'mod_usertrack' if it's there.

After installation, follow the instructions in the synopsis and restart 
the server.
	
The statistics are then available in the database. See the section VIEWING
STATISTICS for more details.

=head1 PREREQUISITES

You need to have compiled mod_perl with the LogHandler hook in order
to use this module. Additionally, the following modules are required:

	o DBI
	o Date::Format

=head1 INSTALLATION

To install this module, move into the directory where this file is
located and type the following: