<?php                             

/*
    File: $Id: mysql4.php,v 1.1.2.34 2007/03/18 14:33:36 surg30n Exp $
    Project: Skill-Z project, SurSoft (C) 2005
    Author: j4ck 

    Переработанный класс mysql
    + поддержка кэширования.
    Для функционирования создать папку /cache/sql и установить права записа вебсервером
    
    NOTE: alpha version, for test only!
    
    (C) based on phpBB dbal engine
*/


if (!function_exists('file_put_contents')) {

function file_put_contents($fname, $data) {
$fl=fopen($fname,'w');
fwrite($fl,$data);
fclose($fl);
}

}

if(!defined("SQL_LAYER"))
{
#define('SQL_DEBUG','1'); // if defined, dump queries to logs/sql.log
define("SQL_LAYER","mysql4"); 

class sql_db {
    
/*
base
*/

	var $db_connect_id;
	var $query_result;
	var $row = array();
	var $rowset = array();
	var $num_queries = 0;
	var $in_transaction = 0;
	var $sql_time = 0; // SQL excution time

    var $_debug_file = 'logs/sql.log.html';

// cache
	var $cache_path = 'cache/sql/';
	var $cache_enabled;

    function syslog ($sz) {
             static $logflushed = 0; // log file write indicator
   			 $fl = fopen($this->_debug_file,(($logflushed)?"a":"w") );
             $buff = "<span style='font-family: Verdana, Arial;font-size:10px'>{$sz}</span><br>\n";
             fwrite($fl, $buff);
			 fclose($fl);
			 $logflushed = 1;
    }
    
    //
    //  instance
    //
    
    function &get_instance() {
        return $GLOBALS['db'];
    }
    
    //
    //  escape
    //    
    
	function sql_escape($msg)
	{
		if (!isset($this) || !$this->db_connect_id)
		{
			return @mysql_real_escape_string($msg);
		}

		return @mysql_real_escape_string($msg, $this->db_connect_id);
	}
    
	//
	// Constructor
	//
	function sql_db($sqlserver, $sqluser, $sqlpassword, $database, $persistency = true)
	{
	    $this->cache_path = $GLOBALS['root_path'] . $this->cache_path;
        $this->_debug_file = $GLOBALS['root_path'] . $this->_debug_file;
        
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		$this->persistency = $persistency;
		$this->user = $sqluser;
		$this->password = $sqlpassword;
		$this->server = $sqlserver;
		$this->dbname = $database;
		$this->prefix = $GLOBALS['table_prefix'];
		$this->root	  = $GLOBALS['root_path'];

		$this->cache_enabled = (isset($GLOBALS['config']['disable_sql_cache'])?false:true);

		$this->db_connect_id = ($this->persistency) ? @mysql_pconnect($this->server, $this->user, $this->password) : @mysql_connect($this->server, $this->user, $this->password);

		if( $this->db_connect_id )
		{
			if( $database != "" )
			{
				$this->dbname = $database;
				$dbselect = mysql_select_db($this->dbname);

				if( !$dbselect )
				{
					mysql_close($this->db_connect_id);
					$this->db_connect_id = $dbselect;
				}
			}

			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return $this->db_connect_id;
		}
		else
		{
			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return false;
		}
	}

	//
	// Other base methods
	//
	function sql_close()
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( $this->db_connect_id )
		{
			//
			// Commit any remaining transactions
			//
			if( $this->in_transaction )
			{
				mysql_query("COMMIT", $this->db_connect_id);
			}

			return mysql_close($this->db_connect_id);

			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return $result;
		}
		else
		{
			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return false;
		}
	}


//

function cache_make_id($q){
    $data['q']=$q;
    
    $table='';
    $m=array('','','');
    
    // (?:DELETE|SELECT|)
    if (preg_match('/^(DELETE|SELECT).*FROM[\s\(]*'.$this->prefix.'([^\s\,\;]+)[\s]*/ims',$q,$m))
        $table=@$m[2];
    else 
    if (preg_match('/^(MODIFY|INSERT INTO|INSERT|UPDATE|TRUNCATE|DROP|CREATE TABLE)[\s]*'.$this->prefix.'([^\s\,\;]+)[\s]*/ims',$q,$m))
        $table=@$m[2];

    $this->cache_last_table = $table; 
    
#    core_c::cprint("[color=blue]{$m[1]} - {$table}[/color]");

    return ( $this->dbname . '_' .$table. '_'.sprintf("%u",crc32(serialize($data))));
}

/*
*   CACHE EM ALL!
*/


var $cache_except_tables = array('sessions','banlist','confirm','users'); //'news_export_news'
var $cache_hits = array();

var $cache_last_id;
var $cache_last_table;    /* make id do this */

/* configs */
var $cache_cache_1items_queries = true;
var $cache_ignore_another_tables = true; /* ugnore tables with another prefix */


var $cache;

var $cache_table_chains = array(
  'blog'=>array('config'),
  'blog_com'=>array('blog'),
  't_categories'=>array('config')
);

/*
    игнорировать таблицу для кэширования
*/

function cache_is_ignore($table) {
    return in_array($table, $this->cache_except_tables);
}


function cache_fetchrowset($res) {
  if (!isset($this->cache[$res])) return -1;
  // inc index
  $data  = $this->cache[$res]['data'];
  return (!empty($data)?$data:false);
}


/*
	-1 - no cache
   false - out of increment  
*/

function cache_fetchrow($res) {
  if (!isset($this->cache[$res])) return -1;
  // inc index
  $data  = $this->cache[$res]['data'];
  $index = $this->cache[$res]['index']++;
  return (isset($data[$index])?$data[$index]:false);
}

/*
  Вызывается до реального обращения к sql_query
  Если существует кэш, то он отдается в последствие методом cache_get  

  @return:
  -1 - cancel cache
  false - cache not exists
  true - found
*/

function cache_check_by_id($id=false) {
    if ($id===false) $id=$this->cache_last_id;
    return (isset($this->cache[$id]));
}

function cache_check($sql) {

  if (!$this->cache_enabled) return -1;

  $this->cache_last_id = $this->cache_make_id($sql);
  $fname = $this->cache_make_fname($this->cache_last_id);

  // check nasty queries (UPDATE, MODIFY, DELETE, INSERT)

  if (preg_match('#^(?!SELECT)#i',$sql,$m)) {
	// skip it
#core_c::cprint('SKIP>'.$sql);
    /* убиваем кэш, если это не селект      
       hack: одиночное изменение
    */
    $single =  (preg_match("/WHERE[\s]*id[\s]*=/smi",$sql))?$this->cache_last_id:false;   
    $this->cache_drop(false,$single);
    return -1;
  }
  else {
  /*    skip sone tables, suck as phpbb messages
  */
    if ($this->cache_ignore_another_tables && preg_match('#FROM[\s\(]*([^\s\)]*)#ism',$sql,$m)) {
        if (substr($m[1],0,strlen($this->prefix))!=$this->prefix) {
            return -1;
        }
    }
  }

  // cache hit
  if (isset($this->cache[$this->cache_last_id]) || file_exists($fname)) {
	 return true;
  }

  return false;
}

/*

*/

function cache_drop($table=false, $single=false) {
    
    if ($table===false)  $table = $this->cache_last_table;
    
    if (!empty($table) && !$this->cache_is_ignore($table)) {
        // дропаем
        $dir = $this->cache_path;
        $pref_ = $this->dbname . '_' .$table. '_';
        
        /*
        if ($single!==false) {
            core_c::cprint('kill single : '.$single);
            unlink($dir.$single);
            return true;
        }
        */
        
#        core_c::cprint("[DROP] {$pref_}" );
        
        if ($dh = opendir($dir)) {
#            core_c::cprint('DROP=='.$table);
            while (($file = readdir($dh)) !== false) {
                
                if (preg_match("#^{$pref_}#",$file)) {
#                    core_c::cprint("[SQL DROP] Файл: $file : тип: " . filetype($dir . $file) );
                    unlink($dir.$file);
                }
            }
            closedir($dh);
        //         
        }

	  // chain delete
	  if (isset($this->cache_table_chains[$table])) {
		foreach($this->cache_table_chains[$table] as $t_) {
		  $this->cache_drop($t_);
		}
	  }

    }
}

function cache_rowcount($id=false) {
    if ($id===false) $id=$this->cache_last_id;
    return ($this->cache[$id]['count']);
}

/*
  cache is exists, get it
*/

function cache_get($id=false) {
      if ($id===false) $id=$this->cache_last_id;
      $fname = $this->cache_make_fname($id);

    #var_dump('[SET_RES]',$id);

      if (!isset($this->cache[$id])) {

      //$data = file_get_contents($fname);

    //var_dump($data);
      $data = include($fname); //unserialize($data);
      $this->cache[$id]=$data;
    }
    else {
      $data = $this->cache[$id];
    }

    // reset!
    $this->cache[$id]['index']=0;

    // if fail, return fail query flag!
    #return ($data['result']) ? $id : false;
    #core_c::cprint('[cache_get]');
    return $id;
}

/*
    путь до файла кэша
*/

function cache_make_fname($t) {
  return $this->cache_path.$t;
}

function cache_query($sql,$res) {
  $this->cache_last_id = $this->cache_make_id($sql);
  preg_match('/FROM[\s]*'.$this->prefix.'([^\s\,\;]+)[\s]*/i',$sql,$m);
  $table=@$m[1];

  if ($table && $this->cache_is_ignore($table)) {
#core_c::cprint("[sql_cache] deny {$table}");
	return $res;
    // do not cache this shit	
  }

  $data = $this->sql_fetchrowset($res);

  $cache = array(
	'data' 	=> $data,
	'result'=> $res,
    'table' => $table,
    'index' => 0,
    'count' => count($data)
  );

#core_c::cprint('cache write #' .$this->cache_last_id);

  file_put_contents(
	$this->cache_make_fname($this->cache_last_id),
	"<?php\nreturn ".var_export($cache,true).";\n ?>" //serialize($cache)
  );
  
  $this->cache[$this->cache_last_id]=$cache;
  return $this->cache_last_id;
}

	//
	// Base query method
	//
	function sql_query($query = "", $transaction = FALSE)
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		//
		// Remove any pre-existing queries
		//
		unset($this->query_result);

		if( $query != "" )
		{
           // core_c::cprint('[color=darkgreen]'.$query.'[/color]');
			

			$cache_ret = $this->cache_check($query);

#core_c::cprint("[code type=php]{$query}[/code]");
			
			if ($cache_ret!==true) {


			$this->num_queries++;

			if( $transaction == BEGIN_TRANSACTION && !$this->in_transaction )
			{
				$result = mysql_query("BEGIN", $this->db_connect_id);
				if(!$result)
				{
					return false;
				}
				$this->in_transaction = TRUE;
			}


				$this->query_result = mysql_query($query, $this->db_connect_id);

  				if ($cache_ret!==-1
                &&  ($this->cache_cache_1items_queries ||
                    (!$this->cache_cache_1items_queries && $this->sql_numrows($this->query_result)>1))
                )
  				  $this->query_result = $this->cache_query($query,$this->query_result);
			}
			else {
			    $this->query_result = $this->cache_get();
				return $this->query_result;
			}
		

		}
		else
		{
			if( $transaction == END_TRANSACTION && $this->in_transaction )
			{
				$result = mysql_query("COMMIT", $this->db_connect_id);
			}
		}

		if( $this->query_result )
		{
			unset($this->row[$this->query_result]);
			unset($this->rowset[$this->query_result]);

			if( $transaction == END_TRANSACTION && $this->in_transaction )
			{
				$this->in_transaction = FALSE;

				if ( !mysql_query("COMMIT", $this->db_connect_id) )
				{
					mysql_query("ROLLBACK", $this->db_connect_id);
					return false;
				}
			}

			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

            if (defined('SQL_DEBUG')) {
	            $this->syslog('<b>' . $this->num_queries.')  '.htmlspecialchars($query) . ' [' . round($this->sql_time,4) . 'ms]</b>');

                // debug code idea by jovani (phpbbguru.net/community/profile.php?mode=viewprofile&u=12)
                if(strtoupper(substr($query, 0, 6)) == "SELECT")
	            {
	                    $x = mysql_query("EXPLAIN $query", $this->db_connect_id);
	                    $z = array();

	                    while($y = mysql_fetch_assoc($x))
	                    {
	                            $this->syslog ("&nbsp;&nbsp;&raquo; tbl = " . $y['table'] . " type = " . $y['type'] . " possible = " . $y['possible_keys'] . " used = " . $y['key'] . " len = " . $y['key_len'] . " ref = " . $y['ref'] . " rows = " . $y['rows'] . " extra = " . $y['Extra'] );
	                    }

	                    mysql_free_result($x);
	            }
	        }

			return $this->query_result;
		}
		else
		{
			if( $this->in_transaction )
			{
				mysql_query("ROLLBACK", $this->db_connect_id);
				$this->in_transaction = FALSE;
			}

			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;
            
             if (defined('SQL_DEBUG')) { 
                 core_c::cprint('[color=red]MYSQL ERROR : '.$query.'[/color]');
                 $err = $this->sql_error();
                 core_c::cprint('[color=red]MYSQL : '.$err['message'].' ('.$err['code'].')[/color]');
                 
             } 

			return false;
		}
	}

	//
	// Other query methods
	//
	function sql_numrows($query_id = 0)
	{
        
        
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}
        
        // cache num rows
        if ($this->cache_check_by_id($query_id)) return $this->cache_rowcount($query_id);

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;

		$this->sql_time += $endtime - $starttime;

		return ( $query_id ) ? mysql_num_rows($query_id) : false;
	}

	function sql_affectedrows()
	{
        // cache num rows
        if ($this->cache_check_by_id()) return $this->cache_rowcount();
        
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;

		$this->sql_time += $endtime - $starttime;

		return ( $this->db_connect_id ) ? mysql_affected_rows($this->db_connect_id) : false;
	}

	function sql_numfields($query_id = 0)
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;

		$this->sql_time += $endtime - $starttime;

		return ( $query_id ) ? mysql_num_fields($query_id) : false;
	}

	function sql_fieldname($offset, $query_id = 0)
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;

		$this->sql_time += $endtime - $starttime;

		return ( $query_id ) ? mysql_field_name($query_id, $offset) : false;
	}

	function sql_fieldtype($offset, $query_id = 0)
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;

		$this->sql_time += $endtime - $starttime;

		return ( $query_id ) ? mysql_field_type($query_id, $offset) : false;
	}

    /*
        SQL: fetchrow
    */
    
	function sql_fetchrow($query_id = 0)
	{


#core_c::cprint('[FETCHROW] '.$query_id);

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}


		if( $query_id )
		{

// cached?
if (-1!==($tmp_=$this->cache_fetchrow($query_id))) return $tmp_;


			$this->row[intval($query_id)] = mysql_fetch_assoc($query_id);

			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return $this->row[intval($query_id)];
		}
		else
		{
			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return false;
		}
	}

	function sql_fetchrowset($query_id = 0)
	{

		$result = array();

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}

		if( $query_id )
		{

		// cached?
		if (-1!==($tmp_=$this->cache_fetchrowset($query_id))) return $tmp_;


			unset($this->rowset[$query_id]);
			unset($this->row[$query_id]);

			while($this->rowset[intval($query_id)] = mysql_fetch_assoc($query_id))
			{
				$result[] = $this->rowset[$query_id];
			}

			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return $result;
		}
		else
		{
			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return false;
		}
	}

	function sql_fetchfield($field, $rownum = -1, $query_id = 0)
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}

		if( $query_id )
		{
			if( $rownum > -1 )
			{
				$result = mysql_result($query_id, $rownum, $field);
			}
			else
			{
				if( empty($this->row[$query_id]) && empty($this->rowset[$query_id]) )
				{
					if( $this->sql_fetchrow() )
					{
						$result = $this->row[$query_id][$field];
					}
				}
				else
				{
					if( $this->rowset[$query_id] )
					{
						$result = $this->rowset[$query_id][0][$field];
					}
					else if( $this->row[$query_id] )
					{
						$result = $this->row[$query_id][$field];
					}
				}
			}

			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return $result;
		}
		else
		{
			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return false;
		}
	}

	function sql_rowseek($rownum, $query_id = 0)
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;

		$this->sql_time += $endtime - $starttime;

		return ( $query_id ) ? mysql_data_seek($query_id, $rownum) : false;
	}

	function sql_nextid()
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;

		$this->sql_time += $endtime - $starttime;

		return ( $this->db_connect_id ) ? mysql_insert_id($this->db_connect_id) : false;
	}

	function sql_freeresult($query_id = 0)
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		if( !$query_id )
		{
			$query_id = $this->query_result;
		}

		if ( $query_id )
		{
			unset($this->row[$query_id]);
			unset($this->rowset[$query_id]);

			mysql_free_result($query_id);

			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return true;
		}
		else
		{
			$mtime = microtime();
			$mtime = explode(" ",$mtime);
			$mtime = $mtime[1] + $mtime[0];
			$endtime = $mtime;

			$this->sql_time += $endtime - $starttime;

			return false;
		}
	}

	function sql_error()
	{
		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$starttime = $mtime;

		$result['message'] = mysql_error($this->db_connect_id);
		$result['code'] = mysql_errno($this->db_connect_id);

		$mtime = microtime();
		$mtime = explode(" ",$mtime);
		$mtime = $mtime[1] + $mtime[0];
		$endtime = $mtime;

		$this->sql_time += $endtime - $starttime;

		return $result;
	}

	function get_version() {
		$version = $this->sql_fetchrow($this->sql_query("SELECT VERSION() as version"));
		return $version['version'];
	}

} // class sql_db


/**
*	sql cache stuff
*/


} // if ... define

?>