#!/usr/bin/perl -w
#
# $Id: squid-access-into-mysql,v 1.1 2004/06/19 11:50:37 suter Exp $
# Copyright (C) 2004 Mark Suter <suter@humbug.org.au>
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.

# Here's the table structure, based on the squid access.log format
# http://squid.visolve.com/squid/squid24s1/glossary.htm#access.log
#
# CREATE TABLE logs (
#     timestamp datetime default NULL,
#     elapsed int(10) unsigned NOT NULL default '0',
#     ip text NOT NULL,
#     code text NOT NULL,
#     size int(10) unsigned NOT NULL default '0',
#     method text NOT NULL,
#     uri text NOT NULL,
#     user text NOT NULL,
#     hierarchy text NOT NULL,
#     type text NOT NULL,
#     KEY timestamp (timestamp),
#     KEY user (user),
#     KEY ip (ip),
#     FULLTEXT KEY uri (uri),
# ) TYPE=MyISAM COMMENT='Squid Access logs';

use strict;
use POSIX;
use DBI;

## Connect to our database
my $dbh = DBI->connect("DBI:mysql:database=squid", "squid", "secret", { RaiseError => 1, PrintError => 0, ShowErrorStatement => 1 });

## Purge data older than 90 days
$dbh->do(sprintf "delete from logs where timestamp < \"%s\"", strftime("%Y-%m-%d %H:%M:%S", gmtime (time - 90 * 86400)));

## Prepare my insert
my $insert = $dbh->prepare("insert into logs values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");

## Do the insert for each entry, with a formatted datetime string
my @fields = ();
while (<>) {
    @fields = split;
    $insert->execute( strftime("%Y-%m-%d %H:%M:%S", gmtime $fields[0]), @fields[1..9]);
}

## Disconnect from the database.
$dbh->disconnect();