#!/usr/sbin/php -q
<?php
##
## mysqld log files analyzing
## databases, tables query statistics
##
## [author]
##  - Chilbong Kim <san2(at)linuxchannel.net>
##  - http://linuxchannel.net/
##
## [changes]
##  - 2003.07.27 : fixed, check_args()
##  - 2003.07.18 : fixed, memory_limit by fopen(), it's maximum file size
##  - 2003.07.11 : bug fixed and add options
##  - 2003.07.09 : new build
##
## [download & online source view]
##  - http://ftp.linuxchannel.net/devel/php_mysql_log/
##
## [requirements]
##  - PHP CGI
##  - some mysqld log files
##
## [usage]
##  shell> php -q this.file.name --help
##  or
##  shell> chmod u+x this.file.name
##  shell> ./this.file.name --help
##

## print usage to stderr
##
function usage($errstr='')
{
  error_log(
	'usage  : '.T_MD.'php -q '.basename(__FILE__).
	' [OPTIONS] [mysqld.log [mysqld.log2 ...]]'.T_ME."\n".
	'options:'."\n".
	'  -h, --help        print this message and exit'."\n".
	'  -r, --print_r     print by print_r() style, this default'."\n".
	'  -p, --php         print by associative array style'."\n".
	'  -s, --serialize   print by serialize() style'."\n"
	);

  if($errstr) error_log(T_MR.$errstr.T_ME);
  exit;
}

function check_args($argc, $argv, &$pr, &$logfiles)
{
  global $_SERVER; // for PHP/4.0.x

  ## check user-agent, only php execute on command line shell
  ##
  if($_SERVER[HTTP_USER_AGENT])
  { usage('sorry !!!'); }

  ## reset
  ##
  $pr = $logfiles = array();

  for($i=1; $i<$argc; $i++) // skip $i=0
  {
	$cw = preg_replace('/^[-]+(?)/','\\1',&$argv[$i]);
	$cw = ($cw[0].$cw[1] == 'pr') ? 'r' : $cw[0];

	if($cw == 'h')
	{ usage(); break; }

	else if($cw == 'r' || $cw == 'p' || $cw == 's')
	{ $pr[$cw] = TRUE; }

	else
	{
		if(file_exists(&$argv[$i]) && is_file(&$argv[$i]))
		{ $logfiles[] = &$argv[$i]; }
		else
		{ error_log(T_MR.$argv[$i].' file not exists'.T_ME); }
	}
  }

  if(sizeof($logfiles)<1) usage();
  if(!$pr[r] && !$pr[p] && !$pr[s]) $pr[r] = TRUE;
}

## Direct IO access get file
## 2002.08.23
##
function get_diofile($file)
{
  if($fp = @dio_open($file,O_RDONLY))
  {
	$contents = dio_read($fp,filesize($file));
	dio_close($fp);
  }

  return $contents;
}

## common get file
## 2002.08.23
##
function get_file($file)
{
  if(!file_exists($file)) return '';
  if(function_exists(dio_open)) return get_diofile($file);

  if($fp = @fopen($file,'r'))
  {
	$contents = fread($fp,filesize($file));
	fclose($fp);
  }

  return $contents;
}

## get percents
##
function add_per($sums, $types, $s2=array(), $q2=array())
{
  $tmp = array_keys($sums);
  foreach($tmp AS $dt)
  {
	foreach($types[$dt] AS $q=>$v)
	{
		$types[$dt][$q] .= ' ('.
			sprintf('%.2f',@($v*100/$sums[$dt])).'%)';
	}
	if($s2[$dt] && $q2[$dt]) add_per(&$s2[$dt], &$q2[$dt]);
  }
}

## get table name in each line
##
function get_table_name($line, $splitw)
{
  if($splitw)
  {
	list(,$cwords) = preg_split("/$splitw/i",$line);
	list($table) = preg_split('/[\s]+/',$cwords);
	$table = preg_replace('/[(,\'"`].*$/','',$table);

	if(preg_match('/\./',$table))
	{ list($refdb,$table) = preg_split('/\./',$table); }
  }

  return array(trim($table),trim($refdb));
}

## for printing
##
function arr2print($array, $name, $title='', $int=0)
{
  if(!$array || !is_array($array)) return;
  if($title) echo "\n".'## '.$title."\n".'##'."\n";
  if(!$int) $pt = '\'';

  $keys = array_keys($array);
  $size = sizeof($array);

  for($i=0; $i<$size; $i++)
  {
	$key = &$keys[$i];
	$var = $name.'[\''.$key.'\']';

	if(is_array($array[$key]))
	{ arr2print($array[$key],$var,0,$int); }
	else
	{ echo "\$$var\t= ${pt}$array[$key]${pt};\n"; }
  }
}

######################################################
######################################################

set_time_limit(0);
ini_set('memory_limit','50M'); // it's maximum file size by fopen()

global $_SERVER; // force to set
unset($argc,$argv,$dbq,$dbs,$tableq,$tables,$pr,$logfiles); // reset

## for xterm, color-terminal
##
define('T_MR', sprintf('%s',"\033[1;31m")); // bold red
define('T_MD', sprintf('%s',"\033[1;39m")); // bold white
define('T_ME', sprintf('%s',"\033[0;39m")); // normal white

$argc = $_SERVER[argc];
$argv = $_SERVER[argv]; // array

## check arguments
##
check_args($argc,$argv,&$pr,&$logfiles); // $pr and $logfile is reference

## split words of each query, for get_table_name()
##
$splits = array
(
  'select'	=> 'FROM\s+',
  'insert'	=> 'INSERT\s+(LOW_PRIORITY|DELAYED)*\s*(IGNORE)*\s*(INTO)*\s*',
  'update'	=> 'UPDATE\s+(LOW_PRIORITY)*\s*(IGNORE)*\s*',
  'delete'	=> 'FROM\s+',
  'show'	=> 'CREATE\s+TABLE\s+',
  'desc'	=> 'DESC\s+',
  'describe'	=> 'DESCRIBE\s+',
  'explain'	=> 'EXPLAIN\s+(SELECT.+FROM\s+)*',
  'lock'	=> 'TABLES\s+',
  'create'	=> 'TABLE\s+(IF\s+NOT\s+EXISTS)*\s*',
  'replace'	=> 'REPLACE\s+(LOW_PRIORITY|DELAYED)*\s*(INTO)*\s*',
  'alter'	=> 'TABLE\s+',
  'analyze'	=> 'TABLE\s+',
  'optimize'	=> 'TABLE\s+',
  'rename'	=> 'TABLE\s+',
  'drop'	=> 'TABLE\s+(IF\s+EXISTS)*\s*',
  'backup'	=> 'TABLE\s+',
  'restore'	=> 'TABLE\s+',
  'check'	=> 'TABLE\s+',
  'repair'	=> 'TABLE\s+',
  'load'	=> 'INTO\s+TABLE\s+',
  'handler'	=> 'HANDLER\s+',
  'turncate'	=> 'TABLE\s+',
);

## get contents
##
//$contents = `cat $logfile 2>/dev/null`;

$size = sizeof($logfiles);
for($i=0; $i<$size; $i++)
{ $contents .= get_file(&$logfiles[$i]); }

## parsing
##
$contents = preg_replace
(
  array('/([0-9]+)\sInit DB/','/[0-9]+\s(Init_DB|Query|Connect)/'),
  array('\\1 Init_DB','__DIV__\\0'),
  $contents
);

## get array(splited by '__DIV__')
##
$arr = preg_split('/__DIV__/',$contents);

$size = sizeof($arr);
$term = (int)($size/10);

## get query statistics each databases, tables
##
for($i=1; $i<$size; $i++) // skip 0
{
  if(!($i%$term)) error_log(sprintf('%03.2f',$i*100/$size).'% done');
  flush();

  $line = preg_replace('/[\r\n]+/',' ',&$arr[$i]);
  list($nid,$cmd,$args,$on,$db) = preg_split('/[\s]+/',$line);

  if($cmd == 'Connect')
  {
	if($db && $on == 'on' && !preg_match('/^(for|[\d]+)$/',$db))
	{ $dbname = $db; }	// change dbname
	else
	{ $dbname = ''; }	// this false
	continue;
  }
  else if($cmd == 'Init_DB')
  {
	if(!$db && !$on)
	{ $dbname = $args; }	// change dbname
	else
	{ $dbname = ''; }	// this false
	continue;
  }
  else if($cmd == 'Query') // this Query
  {
	$query = strtolower($args);	// query command
	list($table,$refdb) = get_table_name($line,&$splits[$query]);
	$tmpdb = $refdb ? $refdb : $dbname;

	if($tmpdb)
	{
		$dbq[$tmpdb][$query]++;	// db query statistics
		$dbs[$tmpdb]++;		// db sum statistics

		if($table)
		{
			$tableq[$tmpdb][$table][$query]++; // table statistics
			$tables[$tmpdb][$table]++; // table sum statistics
		}
	}
  }
}

error_log('100.00% done'."\n".'preparing to print. wait...');
sleep(1);

unset($arr);
@add_per(&$dbs, &$dbq, &$tables, &$tableq); // use array-references

@ksort($dbq);
@ksort($dbs);
@ksort($tableq);
@ksort($tables);

## print print_r() style
##
if($pr[r])
{
  echo "\n".'STATISTICS OF DATABASES(QUERY) :'."\n";
  print_r($dbq);
  print_r($dbs);

  echo "\n".'STATISTICS OF TABLES(QUERY) :'."\n";
  print_r($tableq);
  print_r($tables);
}

## print associative-array style
##
if($pr[p])
{
  echo '<?php'."\n\n";
  arr2print($dbq,'databas_query','database query statistics :');
  arr2print($dbs,'databas_sum','database sum statistics :',1);
  arr2print($tableq,'table_query','table query statistics :');
  arr2print($tables,'table_sum','table sum statistics :',1);
  echo "\n".'?>';
}

## print serialize style
##
if($pr[s])
{
  echo serialize($dbq)."\n";
  echo serialize($dbs)."\n";
  echo serialize($tableq)."\n";
  echo serialize($tables)."\n";
}


## last
##
unset($dbq,$dbs,$tableq,$tables);
flush();

exit;
?>