#!/usr/bin/perl
#
# $Id: squid,v 1.1 2002/02/07 18:31:12 syatskevich Exp $
#
#   Squid'      proxy_log
#
#       :
#     1)  (   ) 
#     2) ,    
#     3) ,     (  )
#     4)   (GET  POST)
#     5) -         
#     6)   
#
#  " "/" "  
# 1, 2, 3, 4
#

use URI;
use DBI;

$user   = $ENV{"USER"};
$dbase  = $ENV{"DBASE"};
$passwd = $ENV{"PASSWD"};

#  
if (($user eq "") || ($dbase eq "") || ($passwd eq ""))
{
	die "  \n";
}

sub parseLogLine
{
	($time, $duration, $client_address, $result_codes, $bytes, $request_method, $url, $rfc931, $hierarhy_code, $type) = split (/\s+/);

	#      Squid 
	#     Squid'
	if ($result_codes =~ /^NONE/ || $url =~ /^cache_object:/)
	{
		return ("SKIP_LINE", "", "", "", "", 0, 0);
	}

	# Squid      CONNECT ( 
	#   ICQ)  ,   
	#   URI.    .
	if ($request_method eq "CONNECT" && $url !~ /^https:/i)
	{
		$url = "https://$url";
	}

	$uri  = URI->new ($url);
	$uri  = $uri->canonical;
	$host = $uri->host;

	($day, $month, $year) = (localtime ($time)) [3, 4, 5];

	$month++;
	$year += 1900;

	$hit = ($result_codes =~ /_HIT/) ? 1 : 0;

	return ("INSERT", "$year/$month/$day", $client_address, lc $host, $request_method, $hit, $bytes);
}

while (<STDIN>)
{
	($state, $date, $client_address, $host, $request_method, $hit, $bytes) = parseLogLine ($_);

	if ($state eq "INSERT")
	{
		$total_count{"$date $client_address $host $request_method"} ++;
		$total_bytes{"$date $client_address $host $request_method"} += $bytes;

		if ($hit == 1)
		{
			$hit_count{"$date $client_address $host $request_method"} ++;
			$hit_bytes{"$date $client_address $host $request_method"} += $bytes;
		}
	}
}

$dbh = DBI->connect ("dbi:Pg:dbname=$dbase", $user, $passwd, {AutoCommit => 0});

$sth = $dbh->prepare ("INSERT INTO proxy_log VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

foreach $key (keys %total_count)
{
	if ($total_bytes{$key} > 0)
	{
		($date, $client_address, $host, $request_method) = split (/ /, $key);

		($hcount, $hbytes) = (exists $hit_count{$key}) ? ($hit_count{$key}, $hit_bytes{$key}) : (0, 0);

		$sth->execute ($date, $client_address, $host, $request_method, $total_count{$key}, $total_bytes{$key}, $hcount, $hbytes);
		if ($sth->state)
		{
			$dbh->rollback;
			$dbh->disconnect;

			die "  ,    \n";
		}
	}
}

$dbh->commit;
$dbh->disconnect;
