##sq_proxy.pl
##this script is run to generate the squid table that is optimized for analysis (sq_proxy).
##the script is structured so it can be run either to update the analysis (sq_proxy) table 
##with the interpolated values based on the records in the accesslog table that are more recent
##than the last update, or to populate the database from scratch.
##


#!/usr/bin/perl

use strict;

use DBI;
use DBD::mysql;
use Date::Manip;
use DBD::Pg;

##initialize scalars
##(some of these may have been deprecated)


my $destination_counter;
my $source_counter;
##used as proxies or flags within the script itself
my $holiday1;
my $holiday2;
my $holiday_today;
my $holiday_yesterday;



##generated proxy variables stored in output table
my $monday;
my $workday;
##used to generate the request's position relative to 7am that morning
my $num_hrs;
my $num_mins;
my $num_secs;

##hold values fomatted for given functions
my $day_format;
my $yday_format;

##quite possibly no longer relevant
my $starttime;

##output table
my $basetime;
my $day;
my $test;
my $code;
my $secs;

my $cyear;

##scalars for connect statement, drivers and hosts should correspond to your own environment
$driverin = "mysql";
$hostin = "raider-ralph";
$driverout="Pg";
$hostout="raider-ralph";

##the following two are blank to protect the identities of the innocent <grin>
$user = '<suerid>';
$password = '<password>;
$database="squidlogs";



my $line;
#dsn statement for source of records
$dsnin = "DBI:$driverin:database=$database:host=$hostin";
#dsn statement for destination transformed data
$dsnout = "dbi:$driverout:dbname=$database;host=$hostout";


#establish connections

$dbhin = DBI->connect($dsnin,$user,$password);
$dbhout = DBI->connect($dsnout,$user);

##prepare sql statements executed within loops
$rec_stmnt=$dbhin->prepare("select * from accesslog where count = ?");
$ins_stmnt=$dbhout->prepare("insert into sq_proxy (time,day,monday,workday,basetime,elapsed,bytes,j_date,code,count) values (?,?,?,?,?,?,?,?,?,?)");

##determine how many records are in the transformed file ...if there are none, initialize the counter scalars to 1
$numdone_stmnt=$dbhout->prepare("select count(time) from sq_proxy");
$numdone_stmnt->execute();
$num_arry=$dbhout->selectrow_array($numdone_stmnt);


if ($num_arry eq 0)
  {
          $destination_counter = 1;
          $source_counter=1;
   }
   

##the following block determines the relative postion of the last record processed in the source and destination 
##tables and sets the counter variables appropriately.
if ($num_arry gt 0)
  {
         ##find the last record in the destination table and assign that value to the destination counter
         $lastdone_stmnt=$dbhout->prepare("select max(to_number(count,'9999999999')) from sq_proxy");
         $lastdone_stmnt->execute;
         @lastdone_arry = $dbhout->selectrow_array($lastdone_stmnt);
         $destination_counter=@lastdone_arry[0];
         
         ##determine the time value for that record
         $lastcnt_stmnt=$dbhout->prepare("select time from sq_proxy where to_number(count,'999999999')=to_number($destination_counter,'9999999999')");
         $lastcnt_stmnt->execute;
         @lastdone_arry=$dbhout->selectrow_array($lastcnt_stmnt);
         $last_done=@lastdone_arry[0];

                   $source_counter=@src_arry[0];
                   $source_counter++;
          }
 }



  
     
###manual specification of the counter variables here allows for the script to be run for predetermined values in appropriate
###circumstances.
##$counter=156169;
##$count=1275146;


##determine the value of the count field in the last record in the accesslog table;
$max_stmnt = $dbhin->prepare("select max(count) from accesslog");
$max_stmnt->execute();

$max_arry = $dbhin->selectrow_array($max_stmnt);



##outer loop for stepping through source file

Edit: chipmunk 2001-09-12