<?php
require_once(URL_CFSHARED_PHP_PHPSQLPARSER);

/*
	SQL Logger
	
	Depends on:
	genericDBControls.php
	errorHanlder.php
	- a database connection should already be made
	
	Logs all SQL queries to a table in the connected database.
	Call with sql_log(data,table) where data is an array whose keys are
	field names and whose values are the values associated with the
	fields. Returns true if successful, false if an error occurs.
	
	Ex:
	sql_log(new array('person_name' => 'ted', 'person_height' => 54), 'myTable')
*/

define('SQLLogger_Only_Errors', false);	// log only if mysql returned an error
define('SQLLogger_Only_Affected', true);	// log only if at least one affected row 
// for detecting runaway scripts
define('SQLLogger_Max_Events', 1000000);
$GLOBALS['SQL_LOGGER_EVENTS'] = 0;		// count every attempt to log

function sql_log($statement = '', $otherFields = array(), $table = ''){
	++$GLOBALS['SQL_LOGGER_EVENTS'];
	preventSQLLogRunAway();
	$isSuccess = true;
	$loggerCtrl = getLoggerControl($statement, $otherFields, $table);
	if ((SQLLogger_Only_Affected && mysql_affected_rows()) || (!SQLLogger_Only_Affected)){
		if ((SQLLogger_Only_Errors && mysql_error()) || (!SQLLogger_Only_Errors)){
			$loggerCtrl->writeRows();
		}
	}
	if ($loggerCtrl->errorText != ''){
		$isSuccess = false;
		trigger_error('Failed to log MySQL action. '.$loggerCtrl->errorText, E_USER_WARNING);
	}
	return $isSuccess;
}
function preventSQLLogRunAway(){
	if ($GLOBALS['SQL_LOGGER_EVENTS'] >= SQLLogger_Max_Events){
		// possible runaway script attempting infinite logs
		trigger_error('Maximum logging events exceeded. Possible runaway script.', E_USER_ERROR);
	}
}
function getLoggerControl($statement = '', $otherFields = array(), $table = ''){
	$options = array();
	// this is extremely important! If we create a genericTableWriter
	// with logging on, our log request triggers an sql insertion, which
	// triggers a log request, which triggers an sql insertion, etc.
	// An infinite logging loop would result!!!! It would only be stopped
	// by max_execution_time in php.ini
	// To further prevent this, we used preventSQLLogRunAway() above
	$options['doLogging'] = false;
	$options['tableName'] = $table;
	$options['action'] = 'insert';
	
	$fieldsAndValues = getFieldsAndValues($statement, $otherFields);
	$options['fieldsToWrite'] = $fieldsAndValues;
	
	$loggerCtrl = new genericTableWriter($options);
	return $loggerCtrl;
}
function getFieldsAndValues($statement = '', $otherFields = array()){
	$fieldsAndValues = array();
	// only want 5 stack frames, but ignore first 3 (getFieldsAndValues, getLoggerControl, and sql_log calls)
	$backtrace = array_slice(debug_backtrace(), 3, 7, true);	
	$fieldsAndValues['action'] = getActionFromStatement($statement);
	$fieldsAndValues['table'] = getTableFromStatement($statement);
	$fieldsAndValues['affectedRows'] = mysql_affected_rows();
	$fieldsAndValues['error'] = mysql_error();
	$fieldsAndValues['statement'] = $statement;
	$fieldsAndValues['functions'] = implode(';',getClassesWithFunctionsFromBacktrace($backtrace));
	$fieldsAndValues['files'] = implode(';',getFilesWithLinesFromBacktrace($backtrace));
	foreach ($otherFields as $field => $value){
		$fieldsAndValues[$field] = $value;	// custom table fields
	}
	return $fieldsAndValues;
}
function getTableFromStatement($statement){
	$parser = new PHPSQLParser($statement);
	$parsedSQL = $parser->parsed;
	$tableNames = searchSQLArrayForTables($parsedSQL);
	$tableName = implode(',',$tableNames);
	return $tableName;
}
function searchSQLArrayForTables($arrayToSearch){
	$tableNames = array();
	foreach ($arrayToSearch as $key => $value){
		if ($key === 'table'){
			$tableNames[] = $value;
		}
		elseif (is_array($value)){
			// recursively check sub arrays for table names
			$tableNames = array_merge($tableNames, searchSQLArrayForTables($value));
		}
	}
	return $tableNames;
}
function getActionFromStatement($statement){
	$action = '';
	if (preg_match('/^INSERT.*$/i',$statement)){ $action = 'INSERT';}
	if (preg_match('/^SELECT.*$/i',$statement)){ $action = 'SELECT';}
	if (preg_match('/^UPDATE.*$/i',$statement)){ $action = 'UPDATE';}
	return $action;
}
function getClassesWithFunctionsFromBacktrace($backtrace){
	$classesWithFunctions = array();
	foreach ($backtrace as $frameIndex => $stackFrame){
		if (isset($stackFrame['class'])){
			$class = $stackFrame['class'];
		} else{
			$class = '';
		}
		if (isset($stackFrame['function'])){
			$function = $stackFrame['function'];
		} else{
			$function = '';
		}
		$classesWithFunctions[] = $class . '.' . $function;
	}
	// nice to have order as: 1stCall->2ndCall->3rdCall->lastCall
	$classesWithFunctions = array_reverse($classesWithFunctions);
	return $classesWithFunctions;
}
function getFilesWithLinesFromBacktrace($backtrace){
	$filesWithLines = array();
	$pathPattern =  '/^.*[\/]/';
	foreach ($backtrace as $frameIndex => $stackFrame){
		if (isset($stackFrame['file'])){
			$file = preg_replace($pathPattern,'',$stackFrame['file']);	// strip full path
		} else{
			$file = '';
		}
		if (isset($stackFrame['line'])){
			$line = $stackFrame['line'];
		} else{
			$line = '';
		}
		$filesWithLines[] = $file . ':' . $line;
	}
	// nice to have order as: 1stCall->2ndCall->3rdCall->lastCall
	$filesWithLines = array_reverse($filesWithLines);
	return $filesWithLines;
}
	
?>