Syntactic Confectionery Delight | |
PerlMonks |
Then you can use SQL select statements to pull the information back out and generate reports, much more easily (in my opinion) than if you had the data stored in a flat file. For example, you could select all accesses from a particular day with one select statement.
You should change "Foo" to some unique identifier for your particular site; you don't have to do this, but it helps to organize your Perl libraries.PerlLogHandler Foo::DBILogger
Foo::DBILogger will be a Perl module that you'll write and install somewhere in your @INC ("/usr/local/lib/perl5/site_perl/Foo/DBILogger.pm", for example). It must contain a subroutine called "handler"--this sub will be called by Apache for each request.
create table log ( stamp datetime not null, host varchar(255) not null, method varchar(8) not null, url varchar(255) not null, user varchar(50), browser varchar(255), referer varchar(255), status smallint(3), bytes int(8) );
The first says that we're starting the package Foo::DBILogger. This should be, of course, whatever you called your log handler in the "PerlLogHandler" statement above. The next two lines pull in Apache::DBI (for persistent database connections, so that our logger is fast enough to handle many simultaneous accesses), and DBI, which provides our database connectivity. The POSIX line pulls in the strftime function to format the timestamp as MySQL likes to receive its timestamps. And the final line pulls in an Apache-specific constant that we'll want to send back to Apache after we've finished handling the logging.package Foo::DBILogger; use Apache::DBI; use DBI; use POSIX 'strftime'; use Apache::Constants qw/OK/;
Now we'll define our handler subroutine, starting with this:
When Apache invokes the handler subroutine, it gives it as an argument an Apache::Request object, which contains all sorts of information about the request. It's from this information that we'll populate the table.sub handler { my $r = shift;
Next, we set up our database and our statement handle:
Remember, we're using Apache::DBI, which gives us persistent database connections, so we can just set up our database normally. Also note that we're using the prepare_cached method so that we're most likely using a pre-compiled version of our SQL statement.my $dbh = DBI->connect('dsn', 'user', 'password', 'mysql', { RaiseError => 1 }) or die "Can't connect: ", $DBI::errstr; my $sth = $dbh->prepare_cached(<<SQL) or die $dbh->errstr; insert into log (stamp, host, method, url, user, browser, referer, status, bytes) values (?, ?, ?, ?, ?, ?, ?, ?, ?) SQL
Now all that remains is to fetch the information from the request object and insert it into the database by executing the statement. We can do that quite easily:
This should be fairly self-explanatory, considering that the method names that we're calling are quite clear. Then we dispose of the statement handle by calling finish on it.$sth->execute( strftime("%Y-%m-%d %H:%M:%S", localtime), $r->get_remote_host, $r->method, $r->uri, $r->connection->user, $r->header_in('Referer'), $r->header_in('User-agent'), $r->status, $r->bytes_sent ); $sth->finish;
So we're done, and all that's left to do is return the OK message and finish up our module:
Ending, of course, with "1;".return OK; } 1;
package Foo::DBILogger; use Apache::DBI; use DBI; use POSIX 'strftime'; use Apache::Constants qw/OK/; sub handler { my $r = shift; my $dbh = DBI->connect('dsn', 'user', 'password', 'mysql', { RaiseError => 1 }) or die "Can't connect: ", $DBI::errstr; my $sth = $dbh->prepare_cached(<<SQL) or die $dbh->errstr; insert into log (stamp, host, method, url, user, browser, referer, status, bytes) values (?, ?, ?, ?, ?, ?, ?, ?, ?) SQL $sth->execute( strftime("%Y-%m-%d %H:%M:%S", localtime), $r->get_remote_host, $r->method, $r->uri, $r->connection->user, $r->header_in('Referer'), $r->header_in('User-agent'), $r->status, $r->bytes_sent ); $sth->finish; return OK; } 1;
select host, url from log where to_days(now()) - to_days(stamp) <= 1;
|
---|
Replies are listed 'Best First'. | |
---|---|
RE: Web Logs Using DBI
by jjhorner (Hermit) on Jun 16, 2000 at 21:53 UTC | |
Re: Web Logs Using DBI
by BMaximus (Chaplain) on Sep 10, 2001 at 06:25 UTC | |
by btrott (Parson) on Sep 10, 2001 at 12:51 UTC | |
by stonecolddevin (Parson) on Apr 21, 2003 at 03:55 UTC | |
by Anonymous Monk on Apr 29, 2003 at 03:58 UTC | |
Re: Web Logs Using DBI
by Anonymous Monk on Nov 16, 2003 at 03:27 UTC | |
by zunnunreza (Initiate) on Dec 31, 2015 at 04:38 UTC |