squid2msql unofficial update

Hi All,
Eugene Chernyshev wrote this script a while back, and I made some
changes that others may find interesting. I can't email Eugene at the
moment to let him know, but I thought I would post this anyhow. I
really wanted MySQL logging, and this seemed the easiest way, but it
wasn't fast enough to keep up with traffic. The script wasn't taking
data out of the pipe fast enough to keep the pipe from filling up. I
added some code to get it to fork a process that feeds MySQL
dynamically faster. This speeds up how fast this script is able to
get data from the pipe. It took some doing, but it seems to be doing
it's job. I opened up 21 browsers simulteneously using the
proxy/squid2mysql/mysql combo to test it, and it seems to be holding.
Use it, abuse it, and if you have any ideas to make it better, I would
definitely be interested. Anyone should I post this to a Perl group
as well?

~Schnibitz

#!/usr/bin/perl
# Squid2MySQL 1.0.0
#
# This is a part of Squid2MySQL accounting system.
# CopyRight 2001 by Eugene V. Chernyshev mailto:bicc@mail.ru
#
# setup
#

$mysqluser="squidroot"; # MySQL user name
$mysqlpass="sqroot"; # MySQL user password
$mysqlserv="localhost"; # MySQL server
$mysqlport="3306"; # MySQL server port
$mysqldbas="squidlog"; # MySQL database name
$mysqltabl="logger"; # MySQL database table
$backuplog="/var/log/squid/backup.log"; # Backup log if couldnt
connect to MySQL
# billing setup
$valuepermeg=4; # four roubles per meg
$valueperhour=0; # none per hour, leased line
+2880p
# billing method
# 0 for user accounting, 1 for hosts accounting $usehosts=0;

use DBI;
use DBI::DBD;
use File::Tail;


#system ("/sbin/squid2mysql < /var/log/squid/access.log");
# waitpid($pid,0);

#$dbh=DBI->connect("DBI:mysql:database=$mysqldbas;host=$mysq lserv;port=$mysqlport",$mysqluser,$mysqlpass,{Auto Commit=>1,RaiseError=>0,PrintError=>1})||&errorcon ;


#$sth=$dbh->prepare("INSERT INTO logger(date,time,elapsed,bill,code,\
#status,bytes,url,userident,host)\
# VALUES(?,?,?,?,?,?,?,?,?,?)");


open(FBLOGRET,$backuplog);

close(FBLOGRET);


while(<>) {

#
# 0 timestamp.millisec
# 1 duration
# 2 remotehost
# 3 code/status
# 4 bytes
# 5 method
# 6 URL
# 7 username
# 8 peerstatus/peerhost
# 9 type
#
#
chop; # here we now get a string!
# splitting string onto subdata
@lines=split(' ');

# timestamp
$lines[0]=~tr/./ /;
@_timestamp=split(' ',$lines[0]);
$cts=$_timestamp[0];

# date=
($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isd st) =
gmtime($cts);
$year=$year+1900;
$mon=$mon+1;
$hour=$hour+7;
if ($hour>24) {
$hour=$hour-24;
$mday=$mday+1;
}

$cdate="$year-$mon-$mday";

# time=
$ctime="$hour:$min:$sec";

# elapsed=
$duration=$lines[1];
# host=
$remotehost=$lines[2];

@codestatus=split('/',$lines[3]);
if ($codestatus[0] eq "TCP_HIT") { $_code=0; }
if ($codestatus[0] eq "TCP_MISS") { $_code=1; }
if ($codestatus[0] eq "TCP_REFRESH_HIT") { $_code=2; }
if ($codestatus[0] eq "TCP_REF_FAIL_HIT") { $_code=3; }
if ($codestatus[0] eq "TCP_REFRESH_MISS") { $_code=4; }
if ($codestatus[0] eq "TCP_CLIENT_REFRESH_MISS") { $_code=5; }
if ($codestatus[0] eq "TCP_IMS_HIT") { $_code=6; }
if ($codestatus[0] eq "TCP_SWAPFILE_MISS") { $_code=7; }
if ($codestatus[0] eq "TCP_NEGATIVE_HIT") { $_code=8; }
if ($codestatus[0] eq "TCP_MEM_HIT") { $_code=9; }
if ($codestatus[0] eq "TCP_DENIED") { $_code=10; }
if ($codestatus[0] eq "TCP_OFFLINE_HIT") { $_code=11; }
if ($codestatus[0] eq "UDP_HIT") { $_code=12; }
if ($codestatus[0] eq "UDP_MISS") { $_code=13; }
if ($codestatus[0] eq "UDP_DENIED") { $_code=14; }
if ($codestatus[0] eq "UDP_INVALID") { $_code=15; }
if ($codestatus[0] eq "UDP_MISS_NOFETCH") { $_code=16; }
if ($codestatus[0] eq "NONE") { $_code=17; }
# status=
$_status=$codestatus[1];
# bytes=
$objectsize=$lines[4];
# bill=
$bill=(($duration/60000)*($valueperhour/60))+(($objectsize/1048576)*$valuepermeg);
# method=
$fetchmethod=$lines[5];
#
$URLlink=$lines[6];
$username=$lines[7];

@peerstatus_host=split('/',$lines[8]);
$peerstatus=$peerstatus_host[0];
$peerhost=$peerstatus_host[1];

$objecttype=$lines[9];



################################################## ##########
##
## transfer data to mysql table
##
################################################## ##########
#$SIG{CHLD} = sub {wait ()};
$pid = fork();
$SIG{CHLD} = 'IGNORE';
die "Cannot fork: $!" unless defined($pid);
if ($pid == 0) {

$dbh=DBI->connect("DBI:mysql:database=$mysqldbas;host=$mysq lserv;port=$mysqlport",$mysqluser,$mysqlpass,{Auto Commit=>1,RaiseError=>0,PrintError=>1})||&errorcon ;

$sth=$dbh->prepare("INSERT INTO logger(date,time,elapsed,bill,code,\
status,bytes,url,userident,host)\
VALUES(?,?,?,?,?,?,?,?,?,?)");

if (($_code ne "10")&&($username ne "-")&&($_status ne
"404")&&($_status ne "400")) {

$sth->execute($cdate,
$ctime,
$duration,
$bill,
$_code,
$_status,
$objectsize,
$URLlink,
$username,
$remotehost) || die "cannot transfer data";


$aff="SELECT * FROM rdnload WHERE userident='$username' AND
date='$cdate'";
$rows_affected=$dbh->do($aff);
if ($rows_affected < 1) {
$stn=$dbh->prepare("INSERT INTO
rdnload(userident,date,download) VALUES (?,?,?)");
$stn->execute($username,$cdate,$objectsize);
}
$aff="UPDATE rdnload SET download=download+$objectsize WHERE
userident='$username' AND date='$cdate'";
$rows_affected=$dbh->do($aff);
}
$dbh->disconnect;
exit (0);
}

}
#exit 1;

sub errorconn {
while(<>) {
open(FBLOG,">>".$backuplog);
print FBLOG $_;
close(FBLOG);
}
die "cannot log to MySQL -- data buffered"; }