<?php
/**
 * Library for basic performance monitoring and tuning
 *
 * This file is part of ADOdb, a Database Abstraction Layer library for PHP.
 *
 * @package ADOdb
 * @link https://adodb.org Project's web site and documentation
 * @link https://github.com/ADOdb/ADOdb Source code and issue tracker
 *
 * The ADOdb Library is dual-licensed, released under both the BSD 3-Clause
 * and the GNU Lesser General Public Licence (LGPL) v2.1 or, at your option,
 * any later version. This means you can use it in proprietary products.
 * See the LICENSE.md file distributed with this source code for details.
 * @license BSD-3-Clause
 * @license LGPL-2.1-or-later
 *
 * @copyright 2000-2013 John Lim
 * @copyright 2014 Damien Regad, Mark Newnham and the ADOdb community
 */

// security - hide paths
if (!defined('ADODB_DIR')) die();

class perf_mysql extends adodb_perf{

	var $tablesSQL = 'show table status';

	var $createTableSQL = "CREATE TABLE adodb_logsql (
		  created datetime NOT NULL,
		  sql0 varchar(250) NOT NULL,
		  sql1 text NOT NULL,
		  params text NOT NULL,
		  tracer text NOT NULL,
		  timer decimal(16,6) NOT NULL
		)";

	var $settings = array(
	'Ratios',
		'MyISAM cache hit ratio' => array('RATIO',
			'=GetKeyHitRatio',
			'=WarnCacheRatio'),
		'InnoDB cache hit ratio' => array('RATIO',
			'=GetInnoDBHitRatio',
			'=WarnCacheRatio'),
		'data cache hit ratio' => array('HIDE', # only if called
			'=FindDBHitRatio',
			'=WarnCacheRatio'),
		'sql cache hit ratio' => array('RATIO',
			'=GetQHitRatio',
			''),
	'IO',
		'data reads' => array('IO',
			'=GetReads',
			'Number of selects (Key_reads is not accurate)'),
		'data writes' => array('IO',
			'=GetWrites',
			'Number of inserts/updates/deletes * coef (Key_writes is not accurate)'),

	'Data Cache',
		'MyISAM data cache size' => array('DATAC',
			array("show variables", 'key_buffer_size'),
			'' ),
		'BDB data cache size' => array('DATAC',
			array("show variables", 'bdb_cache_size'),
			'' ),
		'InnoDB data cache size' => array('DATAC',
			array("show variables", 'innodb_buffer_pool_size'),
			'' ),
	'Memory Usage',
		'read buffer size' => array('CACHE',
			array("show variables", 'read_buffer_size'),
			'(per session)'),
		'sort buffer size' => array('CACHE',
			array("show variables", 'sort_buffer_size'),
			'Size of sort buffer (per session)' ),
		'table cache' => array('CACHE',
			array("show variables", 'table_cache'),
			'Number of tables to keep open'),
	'Connections',
		'current connections' => array('SESS',
			array('show status','Threads_connected'),
			''),
		'max connections' => array( 'SESS',
			array("show variables",'max_connections'),
			''),

		false
	);

	function __construct(&$conn)
	{
		$this->conn = $conn;
	}

	function Explain($sql,$partial=false)
	{

		if (strtoupper(substr(trim($sql),0,6)) !== 'SELECT') return '<p>Unable to EXPLAIN non-select statement</p>';
		$save = $this->conn->LogSQL(false);
		if ($partial) {
			$sqlq = $this->conn->qstr($sql.'%');
			$arr = $this->conn->GetArray("select distinct sql1 from adodb_logsql where sql1 like $sqlq");
			if ($arr) {
				foreach($arr as $row) {
					$sql = reset($row);
					if (crc32($sql) == $partial) break;
				}
			}
		}
		$sql = str_replace('?',"''",$sql);

		if ($partial) {
			$sqlq = $this->conn->qstr($sql.'%');
			$sql = $this->conn->GetOne("select sql1 from adodb_logsql where sql1 like $sqlq");
		}

		$s = '<p><b>Explain</b>: '.htmlspecialchars($sql).'</p>';
		$rs = $this->conn->Execute('EXPLAIN '.$sql);
		$s .= rs2html($rs,false,false,false,false);
		$this->conn->LogSQL($save);
		$s .= $this->Tracer($sql);
		return $s;
	}

	/**
	 * Returns a list of table statuses.
	 * 
	 * @param  string  $orderby  Unused (compatibility with parent method)
	 * @return string A formatted set of recordsets
	 */
	function tables($orderby='1')
	{
		if (!$this->tablesSQL) return false;

		$rs = $this->conn->Execute($this->tablesSQL);
		if (!$rs) return false;

		$html = rs2html($rs,false,false,false,false);
		return $html;
	}

	function GetReads()
	{
	global $ADODB_FETCH_MODE;
		$save = $ADODB_FETCH_MODE;
		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);

		$rs = $this->conn->Execute('show status');

		if (isset($savem)) $this->conn->SetFetchMode($savem);
		$ADODB_FETCH_MODE = $save;

		if (!$rs) return 0;
		$val = 0;
		while (!$rs->EOF) {
			switch($rs->fields[0]) {
			case 'Com_select':
				$val = $rs->fields[1];
				$rs->Close();
				return $val;
			}
			$rs->MoveNext();
		}

		$rs->Close();

		return $val;
	}

	function GetWrites()
	{
	global $ADODB_FETCH_MODE;
		$save = $ADODB_FETCH_MODE;
		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);

		$rs = $this->conn->Execute('show status');

		if (isset($savem)) $this->conn->SetFetchMode($savem);
		$ADODB_FETCH_MODE = $save;

		if (!$rs) return 0;
		$val = 0.0;
		while (!$rs->EOF) {
			switch($rs->fields[0]) {
			case 'Com_insert':
				$val += $rs->fields[1]; break;
			case 'Com_delete':
				$val += $rs->fields[1]; break;
			case 'Com_update':
				$val += $rs->fields[1]/2;
				$rs->Close();
				return $val;
			}
			$rs->MoveNext();
		}

		$rs->Close();

		return $val;
	}

	function FindDBHitRatio()
	{
		// first find out type of table
		//$this->conn->debug=1;

		global $ADODB_FETCH_MODE;
		$save = $ADODB_FETCH_MODE;
		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);

		$rs = $this->conn->Execute('show table status');

		if (isset($savem)) $this->conn->SetFetchMode($savem);
		$ADODB_FETCH_MODE = $save;

		if (!$rs) return '';
		$type = strtoupper($rs->fields[1]);
		$rs->Close();
		switch($type){
		case 'MYISAM':
		case 'ISAM':
			return $this->DBParameter('MyISAM cache hit ratio').' (MyISAM)';
		case 'INNODB':
			return $this->DBParameter('InnoDB cache hit ratio').' (InnoDB)';
		default:
			return $type.' not supported';
		}

	}

	function GetQHitRatio()
	{
		//Total number of queries = Qcache_inserts + Qcache_hits + Qcache_not_cached
		$hits = $this->_DBParameter(array("show status","Qcache_hits"));
		$total = $this->_DBParameter(array("show status","Qcache_inserts"));
		$total += $this->_DBParameter(array("show status","Qcache_not_cached"));

		$total += $hits;
		if ($total) return round(($hits*100)/$total,2);
		return 0;
	}

	/*
		Use session variable to store Hit percentage, because MySQL
		does not remember last value of SHOW INNODB STATUS hit ratio

		# 1st query to SHOW INNODB STATUS
		0.00 reads/s, 0.00 creates/s, 0.00 writes/s
		Buffer pool hit rate 1000 / 1000

		# 2nd query to SHOW INNODB STATUS
		0.00 reads/s, 0.00 creates/s, 0.00 writes/s
		No buffer pool activity since the last printout
	*/
	function GetInnoDBHitRatio()
	{
	global $ADODB_FETCH_MODE;

		$save = $ADODB_FETCH_MODE;
		$ADODB_FETCH_MODE = ADODB_FETCH_NUM;
		if ($this->conn->fetchMode !== false) $savem = $this->conn->SetFetchMode(false);

		$rs = $this->conn->Execute('show engine innodb status');

		if (isset($savem)) $this->conn->SetFetchMode($savem);
		$ADODB_FETCH_MODE = $save;

		if (!$rs || $rs->EOF) return 0;
		$stat = $rs->fields[0];
		$rs->Close();
		$at = strpos($stat,'Buffer pool hit rate');
		$stat = substr($stat,$at,200);
		if (preg_match('!Buffer pool hit rate\s*([0-9]*) / ([0-9]*)!',$stat,$arr)) {
			$val = 100*$arr[1]/$arr[2];
			$_SESSION['INNODB_HIT_PCT'] = $val;
			return round($val,2);
		} else {
			if (isset($_SESSION['INNODB_HIT_PCT'])) return $_SESSION['INNODB_HIT_PCT'];
			return 0;
		}
		return 0;
	}

	function GetKeyHitRatio()
	{
		$hits = $this->_DBParameter(array("show status","Key_read_requests"));
		$reqs = $this->_DBParameter(array("show status","Key_reads"));
		if ($reqs == 0) return 0;

		return round(($hits/($reqs+$hits))*100,2);
	}

    // start hack
    var $optimizeTableLow = 'CHECK TABLE %s FAST QUICK';
    var $optimizeTableHigh = 'OPTIMIZE TABLE %s';

    /**

<      * @see adodb_perf#optimizeTable



>      * @see adodb_perf::optimizeTable()


     */
     function optimizeTable( $table, $mode = ADODB_OPT_LOW)
     {
        if ( !is_string( $table)) return false;

        $conn = $this->conn;
        if ( !$conn) return false;

        $sql = '';
        switch( $mode) {
            case ADODB_OPT_LOW : $sql = $this->optimizeTableLow; break;
            case ADODB_OPT_HIGH : $sql = $this->optimizeTableHigh; break;
            default :
                // May don't use __FUNCTION__ constant for BC (__FUNCTION__ Added in PHP 4.3.0)
                ADOConnection::outp( sprintf( "<p>%s: '%s' using of undefined mode '%s'</p>", __CLASS__, __FUNCTION__, $mode));
                return false;
        }
        $sql = sprintf( $sql, $table);

        return $conn->Execute( $sql) !== false;
     }
    // end hack
}