0, 'time' => 0, 'success' => 0, 'failed' => 0, 'count' => 0 ); var $log_slow_threshold = '0.05'; // 1 - Index by query hash, 2 - index by backtrace var $log_stats_index_mode = 2; var $root_folder; var $query_thresholds = array( '0.0001' => '#00ff00', '0.001' => '#55ff00', '0.005' => '#aaff00', '0.05' => '#ffff00', '0.5' => '#ffaa00', '1' => '#ff5500', '99999' => '#ff0000' ); // // THIS METHOD CONNECTS TO THE SERVER AND SELECTS THE DATABASE // // INPUT: // $database_host REPRESENTING THE DATABASE HOST // $database_username REPRESENTING THE DATABASE USERNAME // $database_password REPRESENTING THE DATABASE PASSWORD // $database_name REPRESENTING THE DATABASE NAME // // OUTPUT: // void // function __construct($database_host, $database_username, $database_password, $database_name) { global $user, $global_plugins; // GET THE SOCIALENGINE ROOT $this->root_folder = dirname(dirname(realpath(__FILE__))); // FORCE TIME AND QUERY LOGGING (IF ENABLED) if( is_null($this->log_stats) ) $this->log_stats = ( defined('SE_DEBUG') ? SE_DEBUG : FALSE ); $this->log_options = ( $this->log_options | SE_DATABASE_LOGOPTS_TIME | SE_DATABASE_LOGOPTS_QUERY ); $this->database_connection = $this->database_connect($database_host, $database_username, $database_password, $database_name); //$this->database_select($database_name) or die($this->database_error()); // This will prevent some problems on MySQL5+ servers //mysqli_query($this->database_connection,"SET sql_mode='MYSQL40'") or die(mysqli_error($this->database_connection)); } // END se_database() METHOD // // THIS METHOD CONNECTS TO THE SERVER AND SELECTS THE DATABASE // // INPUT: // void // // OUTPUT: // instance of this class // public static function &getInstance() { global $database, $database_host, $database_username, $database_password, $database_name; static $db; if( !is_a($db, 'SEDatabase') ) { // Backwards compatibility if( is_a($database, 'SEDatabase') ) { $db =& $database; } // Instantiate else { $db = new SEDatabase($database_host, $database_username, $database_password, $database_name); $database =& $db; } } return $db; } // END getInstance() METHOD // // THIS METHOD CONNECTS TO A DATABASE SERVER // // INPUT: // $database_host REPRESENTING THE DATABASE HOST // $database_username REPRESENTING THE DATABASE USERNAME // $database_password REPRESENTING THE DATABASE PASSWORD // // OUTPUT: // RETURNS A DATABASE LINK IDENTIFIER // function database_connect($database_host, $database_username, $database_password, $database_name) { $data=mysqli_connect($database_host, $database_username, $database_password, $database_name); if (mysqli_connect_errno()) { //printf("Connect failed: %s\n", mysqli_connect_error()); exit(); } if (mysqli_connect_errno()) die(sprintf("[%d] %s\n", mysqli_connect_errno(), mysqli_connect_error())); /* change character set to utf8 */ /*if (!mysqli_set_charset($this->database_connection, "utf8")) { printf("Error loading character set utf8: %s\n". mysqli_error($this->database_connection)); } else { echo ""; //printf("Current character set: %s\n", mysqli_character_set_name($DBlink)); }*/ return $data; } // END database_connect() METHOD // // THIS METHOD SELECTS A DATABASE // // INPUT: // $database_name REPRESENTING THE DATABASE NAME // // OUTPUT: // RETURNS OUTPUT FOR DATABASE SELECTION // function database_select($database_name) { return mysqli_select_db($this->database_connection, $database_name); } // END database_select() METHOD function database_ping() { return mysqli_ping($this->database_connection); } // THIS METHOD QUERIES A DATABASE // INPUT: $database_query REPRESENTING THE DATABASE QUERY TO RUN // OUTPUT: RETURNS A DATABASE QUERY RESULT RESOURCE function database_query($database_query, $log=true) { // EXECUTE QUERY global $user; $query_timer_start = getmicrotime(); $time1=time(); try{ $query_result = mysqli_query($this->database_connection,$database_query); } catch(exception $e) { error_log(var_export(debug_backtrace(), true)); error_log($e->getMessage()); error_log("SQL that called SQL error: ".$database_query); } // or error_log($database_query); /* try { // Log the query before executing $query_result = mysqli_query($this->database_connection,$database_query);// or error_log($database_query); // If query fails, handle it if (!$result) { throw new Exception(mysqli_error($this->database_connection)); } } catch (Exception $e) { error_log("Query Error: " . $e->getMessage()." ".$database_query);// Log MySQL error die(); // Stop execution with a message }*/ $time2=time(); $time3=getmicrotime(); if(($time3-$query_timer_start)>3&&$log) { /*$myfile = fopen($_SERVER["DOCUMENT_ROOT"]."/log/mysql-log.txt", "a"); $txt = $_SERVER["PHP_SELF"]."\n ".($time3-$query_timer_start)."\n ".$database_query."\n".$user->user_info["user_id"]."\n\n"; fwrite($myfile, $txt); fclose($myfile);*/ /*mail("leprakhauns@gmail.com", "SQL QUERY LONGER 3 seconds", $database_query." ".$_SERVER["PHP_SELF"]."?".$_SERVER["QUERY_STRING"]." ".($time2-$time1));*/ } $query_timer_end = getmicrotime(); $query_timer_total = round($query_timer_end-$query_timer_start, 7); // LAST QUERY INFO $this->_last_query = $database_query; $this->_last_resource = $query_result; // RETURN IF NOT LOGGING STATS switch( TRUE ) { case (!$this->log_stats): case ( $query_result && (SE_DATABASE_LOG_SUCCESS & ~ $this->log_trigger)): case (!$query_result && (SE_DATABASE_LOG_FAIL & ~ $this->log_trigger)): case ($query_timer_total< $this->log_slow_threshold && (SE_DATABASE_LOG_FAST & ~ $this->log_trigger)): case ($query_timer_total>=$this->log_slow_threshold && (SE_DATABASE_LOG_SLOW & ~ $this->log_trigger)): return $query_result; break; } // STATS $log_data = array('index' => count($this->log_data)); $this->log_data_totals['total']++; // QUERY if( $this->log_options & SE_DATABASE_LOGOPTS_QUERY ) { // When making hash, remove timestamps $log_data['query_hash'] = md5(preg_replace('/\d{10}/', '', $database_query)); $log_data['query'] = $database_query; } // TIME if( $this->log_options & SE_DATABASE_LOGOPTS_TIME ) { $log_data['time'] = $query_timer_total; $this->log_data_totals['time'] += $query_timer_total; } // BACKTRACE if( $this->log_options & SE_DATABASE_LOGOPTS_BACKTRACE ) { $backtrace = debug_backtrace(); foreach( $backtrace as $backtrace_index=>$single_backtrace ) if( !empty($backtrace[$backtrace_index]['file']) ) $backtrace[$backtrace_index]['file_short'] = str_replace($this->root_folder, '', $backtrace[$backtrace_index]['file']); $log_data['backtrace'] = &$backtrace; } // RESULT if( $this->log_options & SE_DATABASE_LOGOPTS_RESULT ) { $log_data['result'] = ( $query_result ? TRUE : FALSE ); if( $query_result ) $this->log_data_totals['success']++; else $this->log_data_totals['failed']++; } // COUNT if( $this->log_options & SE_DATABASE_LOGOPTS_COUNT ) { $result_count = 0; if( $query_result && !$result_count ) $result_count = $this->database_affected_rows(); if( $query_result && !$result_count ) $result_count = $this->database_num_rows($query_result); $log_data['count'] = $result_count; } // GET ERROR if( $this->log_options & SE_DATABASE_LOGOPTS_ERROR ) { $log_data['error'] = ( $query_result ? FALSE : $this->database_error() ); } // GET THRESHOLD COLOR foreach( $this->query_thresholds as $threshold_time=>$threshold_color ) { if( (float)$query_timer_total>(float)$threshold_time ) continue; $log_data['color'] = $threshold_color; break; } // ADD TO LOG $this->log_data[] = $log_data; // RETURN return $query_result; } // END database_query() METHOD // // THIS METHOD FETCHES A ROW AS A NUMERIC ARRAY // // INPUT: // $database_result REPRESENTING A DATABASE QUERY RESULT RESOURCE // // OUTPUT: // RETURNS A NUMERIC ARRAY FOR A DATABASE ROW // function database_fetch_array($database_result) { if (!$database_result instanceof mysqli_result) { return FALSE;} try{ return mysqli_fetch_array($database_result); } catch(exception $e) { return; } } // END database_fetch_array() METHOD function database_fetch_all($database_result) { if (!$database_result instanceof mysqli_result) { return FALSE;} return mysqli_fetch_all($database_result, MYSQLI_ASSOC); } function database_result($result,$row,$field=0) { if ($result===false) return false; if ($row>=mysqli_num_rows($result)) return false; if (is_string($field) && !(strpos($field,".")===false)) { $t_field=explode(".",$field); $field=-1; $t_fields=mysqli_fetch_fields($result); for ($id=0;$idtable==$t_field[0] && $t_fields[$id]->name==$t_field[1]) { $field=$id; break; } } if ($field==-1) return false; } mysqli_data_seek($result,$row); $line=mysqli_fetch_array($result); return isset($line[$field])?$line[$field]:false; } // // THIS METHOD FETCHES A ROW AS AN ASSOCIATIVE ARRAY // // INPUT: // $database_result REPRESENTING A DATABASE QUERY RESULT RESOURCE // // OUTPUT: // RETURNS AN ASSOCIATIVE ARRAY FOR A DATABASE ROW // function database_fetch_assoc($database_result) { if (!$database_result instanceof mysqli_result) { return FALSE;} return mysqli_fetch_assoc($database_result); } // END database_fetch_assoc() METHOD // // THIS METHOD RETURNS THE NUMBER OF ROWS IN A RESULT // // INPUT: // $database_result REPRESENTING A DATABASE QUERY RESULT RESOURCE // // OUTPUT: // RETURNS THE NUMBER OF ROWS IN A RESULT // function database_num_rows($database_result) { if (!$database_result instanceof mysqli_result) { return FALSE;} return mysqli_num_rows($database_result); } // END database_num_rows() METHOD // // THIS METHOD RETURNS THE NUMBER OF ROWS IN A RESULT // // INPUT: // $database_result REPRESENTING A DATABASE QUERY RESULT RESOURCE // // OUTPUT: // RETURNS THE NUMBER OF ROWS IN A RESULT // function database_affected_rows() { return mysqli_affected_rows($this->database_connection); } // END database_affected_rows() METHOD // // THIS METHOD FREES THE RESULT // // INPUT: // $database_result REPRESENTING A DATABASE QUERY RESULT RESOURCE // // OUTPUT: // TRUE on success, else FALSE // function database_free_result($database_result) { return mysqli_free_result($database_result); } function database_fetch_row($database_result) { return mysqli_fetch_row($database_result); } // END database_free_result() METHOD // // THIS METHOD SETS THE CLIENT CHARACTER SET FOR THE CURRENT CONNECTION // // INPUT: // $charset REPRESENTING A VALID CHARACTER SET NAME // // OUTPUT: // RESOURCE OR FALSE // function database_set_charset($charset) { if( function_exists('mysqli_set_charset') === TRUE ) { return mysqli_set_charset($this->database_connection, $charset); } else { return $this->database_query('SET NAMES "'.$charset.'"'); } } // END database_set_charset() METHOD // // THIS METHOD ESCAPES SPECIAL CHARACTERS IN A STRING FOR USE IN AN SQL STATEMENT // // INPUT: // $unescaped_string REPRESENTING THE STRING TO ESCAPE // // OUTPUT: // Escaped string // function database_real_escape_string($unescaped_string) { return mysqli_real_escape_string($this->database_connection, $unescaped_string); } // END database_real_escape_string() METHOD // // THIS METHOD RETURNS THE ID GENERATED FROM THE PREVIOUS INSERT OPERATION // // INPUT: // void // // OUTPUT: // RETURNS THE ID GENERATED FROM THE PREVIOUS INSERT OPERATION // function database_insert_id() { return mysqli_insert_id($this->database_connection); } // END database_insert_id() METHOD // // THIS METHOD RETURNS THE DATABASE ERROR // // INPUT: // void // // OUTPUT: // The error message for the last failed query // function database_error() { return mysqli_error($this->database_connection); } // END database_error() METHOD // // THIS METHOD RETURNS ALL RETURNED DATA FOR THE LAST QUERY // // INPUT: // void // // OUTPUT: // An array of all returned data for the last query // function database_load_all() { if( !is_resource($this->_last_resource) ) { return FALSE; } $resource = $this->_last_resource; $return_data = array(); while( $data = $this->database_fetch_assoc($resource) ) { $return_data[] = $data; } return $return_data; } // END database_load_all() METHOD // // THIS METHOD RETURNS ALL RETURNED DATA FOR THE LAST QUERY IN AN ASSOC // ARRAY USING THE COLUMN SPECIFIED AS THE KEY // // INPUT: // $key_column - to use as assoc index // // OUTPUT: // The error message for the last failed query // function database_load_all_assoc($key_column) { if( !is_resource($this->_last_resource) && $this->database_num_rows($this->_last_resource) == 0 ) { return FALSE; } $resource = $this->_last_resource; $return_data = array(); while( $data = $this->database_fetch_assoc($resource) ) { $return_data[$data[$key_column]] = $data; } return $return_data; } // END database_load_all_assoc() METHOD // // THIS METHOD CLOSES A CONNECTION TO THE DATABASE SERVER // // INPUT: // void // // OUTPUT: // Connection closure result // function database_close() { // DO DATABASE QUERY LOGGING //if(!$this->database_ping()) { // Connection is active, safe to close if( $this->log_stats ) $this->database_log_stats(); if( $this->log_stats ) $this->database_log_stats_cleanup(); return mysqli_close($this->database_connection); } // return mysqli_close($this->database_connection); } // END database_close() METHOD // // THIS METHOD SORT THE BENCHMARKS BY TIME // // INPUT: // void // // OUTPUT: // void // function database_benchmark_sort() { if( !function_exists('dbtimecmp') ) { function dbtimecmp($a, $b) { //return ( $a['time']==$b['time'] ? 0 : ($a['time']<$b['time'] ? -1 : 1) ); return ( (float)$a['time']==(float)$b['time'] ? 0 : ((float)$a['time']>(float)$b['time'] ? -1 : 1) ); } } usort($this->log_data, 'dbtimecmp'); return ''; } // // THIS METHOD GETS MYSQL CLIENT INFO // // INPUT: // void // // OUTPUT: // http://us2.php.net/manual/en/function.mysql-get-client-info.php // function database_get_client_info() { return ( function_exists('mysqli_get_client_info') ? mysqli_get_client_info() : FALSE ); } // END database_get_client_info() METHOD // // THIS METHOD GETS MYSQL HOST INFO // // INPUT: // void // // OUTPUT: // http://us2.php.net/manual/en/function.mysql-get-host-info.php // function database_get_host_info() { return ( function_exists('mysqli_get_host_info') ? mysqli_get_host_info($this->database_connection) : FALSE ); } // END database_get_host_info() METHOD // // THIS METHOD GETS MYSQL PROTOCOL INFO // // INPUT: // void // // OUTPUT: // http://us2.php.net/manual/en/function.mysql-get-proto-info.php // function database_get_proto_info() { return ( function_exists('mysqli_get_proto_info') ? mysqli_get_proto_info($this->database_connection) : FALSE ); } // END database_get_proto_info() METHOD // // THIS METHOD GETS MYSQL SERVER INFO // // INPUT: // void // // OUTPUT: // http://us2.php.net/manual/en/function.mysql-get-server-info.php // function database_get_server_info() { return ( function_exists('mysqli_get_server_info') ? mysqli_get_server_info($this->database_connection) : FALSE ); } // END database_get_server_info() METHOD // // THIS METHOD LOGS STUFF // // INPUT: // void // // OUTPUT: // void // function database_log_stats() { $query_timer_start = getmicrotime(); // DO STUFF $time = time(); $do_insert = FALSE; $insert_query = " INSERT INTO se_debug_querylog ( debug_querylog_query, debug_querylog_queryhash, debug_querylog_querylocation, debug_querylog_benchmark, debug_querylog_backtrace, debug_querylog_result, debug_querylog_count, debug_querylog_error, debug_querylog_time ) VALUES "; foreach( $this->log_data as $log_index=>$log_data ) { // LOG SINGLE QUERY if( $do_insert ) $insert_query .= ", "; $query_location = substr($log_data['backtrace'][0]['file_short']." [".$log_data['backtrace'][0]['line']."]", 0, 254); $insert_query .= "( '".$this->database_real_escape_string($log_data['query'])."', '{$log_data['query_hash']}', '{$query_location}', '{$log_data['time']}', '', /* TODO */ '".( $log_data['result'] ? '1' : '0' )."', '{$log_data['count']}', '".$this->database_real_escape_string($log_data['error'])."', '{$time}' )"; $do_insert = TRUE; // LOG STATS $sql = " INSERT INTO se_debug_querystats ( debug_querystats_query_hash, debug_querystats_query_location, debug_querystats_query, debug_querystats_count, debug_querystats_count_failed, debug_querystats_count_slow, debug_querystats_time_total, debug_querystats_time_avg ) VALUES ( '{$log_data['query_hash']}', '{$query_location}', '".$this->database_real_escape_string($log_data['query'])."', '1', '".( !$log_data['result'] ? '1' : '0' )."', '".( $log_data['time']>$this->log_slow_threshold ? '1' : '0' )."', '{$log_data['time']}', '{$log_data['time']}' ) ON DUPLICATE KEY UPDATE debug_querystats_count=debug_querystats_count+1, debug_querystats_count_failed=debug_querystats_count_failed+".( !$log_data['result'] ? '1' : '0' ).", debug_querystats_count_slow=debug_querystats_count_slow+".( $log_data['time']>$this->log_slow_threshold ? '1' : '0' ).", debug_querystats_time_total=debug_querystats_time_total+".( $log_data['time'] ? $log_data['time'] : '0' ).", debug_querystats_time_avg=(debug_querystats_count*debug_querystats_time_avg+".( is_numeric($log_data['time']) ? $log_data['time'] : '0' ).")/(debug_querystats_count+1) "; mysqli_query($this->database_connection,$sql) or die(mysqli_error($this->database_connection)); } if( $do_insert ) mysqli_query($this->database_connection,$insert_query) or die(mysqli_error($this->database_connection)); $query_timer_end = getmicrotime(); $query_timer_total = round($query_timer_end-$query_timer_start, 7); } // END database_log_stats() METHOD // // THIS METHOD CLEANS UP LOG STUFF // // INPUT: // void // // OUTPUT: // void // function database_log_stats_cleanup($not_slow=TRUE) { $age_limit = time() - 7200; //$age_limit = time() - 86400; $sql = "DELETE FROM se_debug_querylog WHERE debug_querylog_time<$age_limit"; if( $not_slow ) $sql .= " && debug_querylog_benchmark<{$this->log_slow_threshold}"; $resource = mysqli_query($this->database_connection,$sql) or die(mysqli_error($this->database_connection)); } function database_field_name($result, $field_offset) { $properties = mysqli_fetch_field_direct($result, $field_offset); return is_object($properties) ? $properties->name : null; } function database_num_fields($result) { return mysqli_num_fields($result); } function database_unbuffered_query($query) { return mysqli_query($this->database_connection, $query, MYSQLI_USE_RESULT); } // END database_log_stats_cleanup() METHOD } } if(!function_exists("getmicrotime")) { function getmicrotime(){ list($usec, $sec) = explode(" ",microtime()); return ((float)$usec + (float)$sec); } } ?>