Processing Web Input with MySQL - MySQL - page 719

agent VARCHAR ( 255 ) # user agent
);
Most of the string columns use VARCHAR and are not case sensitive. The exception,
url , is declared with a case-sensitive collation as is appropriate for a server running on
a system with case-sensitive filenames. For notes on choosing the character set and
collation for the path column, see Recipe 20.12 .
The httpdlog table definition shown here includes no indexes. If you plan to run sum‐
mary queries, add appropriate indexes to the table. Otherwise, the summaries slow
dramatically as table size increases. The columns to index depend on the types of state‐
ments you intend to run to analyze the table contents. For example, statements that
analyze the distribution of client host values benefit from an index on the host column.
Next, you need a program to process log lines produced by Apache and insert them into
the httpdlog table. The following script, httpdlog.pl , opens a connection to the MySQL
server, then loops to read input lines. It parses each line into column values and inserts
the result into the database. When Apache exits, it closes the pipe to the logging program.
httpdlog.pl sees end of file on its input, terminates the loop, and disconnects from
MySQL:
#!/usr/bin/perl
# httpdlog.pl: Log Apache requests to httpdlog table
# path to directory containing Cookbook.pm (*** CHANGE AS NECESSARY ***)
use lib qw(/usr/local/lib/mcb) ;
use strict ;
use warnings ;
use Cookbook ;
my $dbh = Cookbook:: connect ();
my $sth = $dbh -> prepare ( qq{
INSERT INTO httpdlog
(dt,host,method,url,status,size,referer,agent)
VALUES (?,?,?,?,?,?,?,?)
} );
while ( <> ) # loop while there is input to read
{
chomp ;
my ( $dt , $host , $method , $url , $status , $size , $refer , $agent )
= split ( /\t/ , $_ );
# map "-" to NULL for some columns
$size = undef if $size eq "-" ;
$agent = undef if $agent eq "-" ;
$sth -> execute ( $dt , $host , $method , $url ,
$status , $size , $refer , $agent );
}
$dbh -> disconnect ();