Code w/o DBI

use DBI;
use Logs;
use Net::FTP;
use Time::Local;
use Time::Timezone;
use POSIX;
use File::Temp qw/ tempfile /;


open (IN, "/home/reports/ftp/WSB/test.txt");
#open (IN, "gzip -dc /tmp/arc/0620.gz |");
($cntfh, $cntfn) = tempfile('WSB_count_XXXX', DIR => '/tmp');
($urlfh, $urlfn) = tempfile('WSB_urls_XXXX', DIR => '/tmp');

chmod 0666, $urlfn;
chmod 0666, $cntfn;

my $url_str = "mysql -u root -prep0rts reports -e\"load data infile '"
+ . $urlfn . "' into table tmpurl fields terminated by '|' (tst
amp,url,count)\"";
my $cnt_str = "mysql -u root -prep0rts reports -e\"load data infile '"
+ . $cntfn . "' into table tmpwsb fields terminated by '|' (tst
amp,op100,op700,total)\"";

print "$url_str\n";
print "$cnt_str\n";
@lines = <IN>;
$lines = @lines;
print "lines: $lines\n";

# parse records and increment counters


foreach $line (@lines)
{
  ($time, $opcode, $url) = parse($line);

  if ($opcode == "100")
  {
    $tstamp{$time}++;
    $op100{$time}++;
    if (length $url > 0)
    {
      my $tmpkey = $url . "<::>" . $time;
      $urls{$tmpkey}++;
    }
  }

  elsif ($opcode == "700")
  {
    $tstamp{$time}++;
    $op700{$time}++;
    if (length $url > 0)
    {
      my $tmpkey = $url . "::" . $time;
      $urls{$tmpkey}++;
    }
  }

}

@time = sort keys %tstamp;
print "Done parsing: @time[0]: @time[$#time]\n";

for ($ii = @time[0]; $ii <= @time[$#time]; $ii++)
{
  $tmpTime = strftime "%Y-%m-%d %H:%M:%S", localtime($ii);
  $tmp100 = $op100{$ii} || 0;
  $tmp700 = $op700{$ii} || 0;
  $tmpTotal = $tstamp{$ii} || 0;
  print $cntfh "$tmpTime|$tmp100|$tmp700|$tmpTotal\n";
}
print "Done writing counts\n";

# enter URLS into database

foreach $key (sort keys %urls)
{
  my ($url,$tstamp) = split (/<::>/, $key);
  $tmpTime = strftime "%Y-%m-%d %H:%M:%S", localtime($tstamp);
  print $urlfh "$tmpTime|$url|$urls{$key}\n";
}

print "Done writing urls\n";
`$cnt_str`;
print "Done writing count DB\n";
`$url_str`;
print "Done writing url DB\n";
[download]

Code with DBI:

use Logs;
use Net::FTP;
use Time::Local;
use Time::Timezone;
use POSIX;
use File::Temp qw/ tempfile /;


open (IN, "/home/reports/ftp/WSB/test.txt");
#open (IN, "gzip -dc /tmp/arc/0620.gz |");
($cntfh, $cntfn) = tempfile('WSB_count_XXXX', DIR => '/tmp');
($urlfh, $urlfn) = tempfile('WSB_urls_XXXX', DIR => '/tmp');

chmod 0666, $urlfn;
chmod 0666, $cntfn;

my $dbh = DBI->connect('dbi:mysql:reports', 'root','rep0rts', {RaiseEr
+ror => 1,PrintError => 0}) or Die ("load data","WSB","Could n
t connect to database" . $dbh->errstr);
my $url_str = "load data infile '" . $urlfn . "' into table tmpurl fie
+lds terminated by '|' (tstamp,url,count)";
my $cnt_str = "load data infile '" . $cntfn . "' into table tmpwsb fie
+lds terminated by '|' (tstamp,op100,op700,total)";

print "$url_str\n";
print "$cnt_str\n";
@lines = <IN>;
$lines = @lines;
print "lines: $lines\n";

# parse records and increment counters


foreach $line (@lines)
{
  ($time, $opcode, $url) = parse($line);

  if ($opcode == "100")
  {
    $tstamp{$time}++;
    $op100{$time}++;
    if (length $url > 0)
    {
      my $tmpkey = $url . "<::>" . $time;
      $urls{$tmpkey}++;
    }
  }

  elsif ($opcode == "700")
  {
    $tstamp{$time}++;
    $op700{$time}++;
    if (length $url > 0)
    {
      my $tmpkey = $url . "::" . $time;
      $urls{$tmpkey}++;
    }
  }

}

@time = sort keys %tstamp;
print "Done parsing: @time[0]: @time[$#time]\n";

for ($ii = @time[0]; $ii <= @time[$#time]; $ii++)
{
  $tmpTime = strftime "%Y-%m-%d %H:%M:%S", localtime($ii);
  $tmp100 = $op100{$ii} || 0;
  $tmp700 = $op700{$ii} || 0;
  $tmpTotal = $tstamp{$ii} || 0;
  print $cntfh "$tmpTime|$tmp100|$tmp700|$tmpTotal\n";
}
print "Done writing counts\n";

# enter URLS into database

foreach $key (sort keys %urls)
{
  my ($url,$tstamp) = split (/<::>/, $key);
  $tmpTime = strftime "%Y-%m-%d %H:%M:%S", localtime($tstamp);
  print $urlfh "$tmpTime|$url|$urls{$key}\n";
}

print "Done writing urls\n";
$dbh->do($cnt_str);
print "Done writing count DB\n";
$dbh->do($url_str);
print "Done writing url DB\n";

$dbh->disconnect();

print "just done\n";
[download]

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.