#!/usr/local/bin/php -q

<?php
##
## mysql log file analyzing(databases query statistics)
##
## <san2(at)linuxchannel.net>, http://linuxchannel.net/
## 2003.07.09 : new build
##

set_time_limit(0);

if($_SERVER[HTTP_USER_AGENT])
{ exit('sorry !!!'); }

if(!file_exists($logfile=$_SERVER[argv][1]))
{
  $T_MR = sprintf('%s',"\033[1;31m"); // bold red
  $T_MD = sprintf('%s',"\033[1;39m"); // bold white
  $T_ME = sprintf('%s',"\033[0;39m"); // normal white

  error_log($T_MR.$logfile.' file is not exits'.$T_ME);
  exit;
}

$contents = `cat $logfile 2>/dev/null`;
$contents = preg_replace
(
  array('/([0-9]+)\sInit DB/','/[0-9]+\s(Init_DB|Query)/'),
  array('\\1 Init_DB','__DIV__\\0'),
  $contents
);

$arr = preg_split('/__DIV__/',$contents);

$size = sizeof($arr);
$init = get_init_args($arr,$size);
$cid = $init[cid];
$sidx = $init[sidx] + 1;
$dbname = $init[dbname];

for($i=$sidx; $i<$size; $i++)
{
  list($nid,$cmd,$args) = preg_split('/[\s]+/',$arr[$i]);

  if($cmd == 'Query') // this Query
  {
	$d[$dbname][(strtolower($args))]++; 
	$s[$dbname]++;
  }
  else $dbname = $args; // change

  //error_log("[$i] => [$dbname] [$cmd] [$args]\n");
}

unset($arr);

$tmp = array_keys($s);
$size = sizeof($tmp);
foreach($tmp AS $dbname)
{
  foreach($d[$dbname] AS $q=>$v)
  { $d[$dbname][$q] .= ' ('.sprintf('%.2f',@($v*100/$s[$dbname])).' %)'; }

}

ksort($d);
ksort($s);

print_r($d);
print_r($s);

function get_init_args($array, $size)
{
  for($i=1; $i<$size; $i++)
  {
	list($cid,$cmd,$args) = preg_split('/[\s]+/',$array[$i]);
	if($cmd == 'Init_DB') break;
  }

  return array('sidx'=>$i,'cid'=>$cid,'dbname'=>$args);
}

exit;
?>