tbssql_mysql.php

<?php

// TbsSql Engine
// Version 3.2, 2010-10-21, Skrol29
// www.tinybutstrong.com

if ( (version_compare(PHP_VERSION,'5')<0) && (!function_exists('clone'))  ) {
	eval('function clone($object) {return $object;}'); // eval is needed because the syntax function clone() is refused in PHP 5
}

define('TBSSQL_SILENT', 0);
define('TBSSQL_NORMAL', 1);
define('TBSSQL_DEBUG', 2);
define('TBSSQL_TRACE', 4);
define('TBSSQL_GRID', 8);
define('TBSSQL_CONSOLE', 16);
define('TBSSQL_1MINUTE', 1);
define('TBSSQL_1HOUR', 60);
define('TBSSQL_1DAY', 24*60);
define('TBSSQL_1WEEK', 7*24*60);
define('TBSSQL_NOCACHE', -1);
define('TBSSQL_DISABLED', false);
define('TBSSQL_ALWAYS', 0);
define('TBSSQL_ARRAY', 'array');
define('TBSSQL_OBJECT', 'object');

class clsTbsSql {

	function __construct($srv='',$uid='',$pwd='',$db='',$drv='',$Mode=TBSSQL_NORMAL) {
		// Default values (defined here to be compatible with both PHP 4 & 5)
		$this->Version = '3.2';
		$this->Id = false;
		$this->SqlNull = 'NULL'; // can be modified by user
		$this->DefaultRowType = TBSSQL_ARRAY;
		$this->CacheDir = '.';
		$this->CacheTimeout = false; // in minutes
		$this->TempCacheTimeout = false;
		$this->CacheAutoClear = TBSSQL_1WEEK; // 1 week in minutes
		$this->CacheSuffix = '';
		$this->InitMsg = true;
		$this->InitCsl = true;
		$this->_CacheCurrSql = false; // is different from false if data as to be saved
		if ($srv==='') {
			$this->Mode = $Mode;
		} else {
			$this->Connect($srv,$uid,$pwd,$db,$drv,$Mode); // Try to connect when instance is created
		}
		$this->_Dbs_Prepare();
		$this->SetTbsKey();
	}

	// Compatibility for PHP 4
	function clsTbsSql($srv='',$uid='',$pwd='',$db='',$drv='',$Mode=TBSSQL_NORMAL) {
		$this->__construct($srv,$uid,$pwd,$db,$drv,$Mode);
	}

// Public methods

	function Connect($srv,$uid='',$pwd='',$db='',$drv='',$Mode=false) {
		if ($Mode!==false) $this->Mode = $Mode;
		$auto = (($srv!='') and ($uid.$pwd.$db.$drv==''));
		if ($auto) {
			// Connection by a global variable
			$var = $srv;
			if (!isset($GLOBALS[$var])) return $this->_Message('error', 'Automatic Connection failed because the global variable \''.$var.'\' is not found.');
			if (!$this->_TakeVar($var,'srv',$srv)) return false;
			if (!$this->_TakeVar($var,'uid',$uid)) return false;
			if (!$this->_TakeVar($var,'pwd',$pwd)) return false;
			if (!$this->_TakeVar($var,'db' ,$db)) return false;
			$this->_TakeVar($var,'drv',$drv,false);
			unset($GLOBALS[$var]);
		}
		$this->_Dbs_Connect($srv,$uid,$pwd,$db,$drv);
		if (is_null($this->Id)) $this->Id = false;
		if ($this->Id===false) return $this->_SqlError(false);
		return true;
	}

	function Close() {
		if ($this->Id!==false) $this->_Dbs_Close();
	}

	function Execute($Sql) {
		// we do not use cache here
		$ArgLst = func_get_args();
		$Sql = $this->_SqlProtect($ArgLst);
		$RsId = $this->_Dbs_RsOpen($Sql, true);
		if ($RsId===false) return $this->_SqlError($this->Id);
		$this->_Dbs_RsClose($RsId);
		return true;
	}

	function GetVal($Sql) {

		$ArgLst = func_get_args();
		$Sql = $this->_SqlProtect($ArgLst);

		$Data = array();
		if (!$this->_CacheTryRetrieve($Sql,$Data)) {
			if (!$this->_GetDataFromDb($Sql, $Data, ($this->_CacheCurrSql===false))) return false; // if CacheSql is false, then we do not need to retrieve all the records from the db
			if ($this->_CacheCurrSql!==false) $this->_CacheUpdate($Data);
		}

		if (count($Data)==0) {
			return false;
		} else {
			return reset($Data[0]);
		}

	}

	function GetRow($Arg0) {
		
		$ArgLst = func_get_args();

		$ObjConv = $this->_ObjCheck($Arg0, $ArgLst);
		$SqlPos = ($ObjConv) ? 1 : 0;
		$Sql = $this->_SqlProtect($ArgLst, $SqlPos);
		
		$Data = array();
		if (!$this->_CacheTryRetrieve($Sql,$Data)) {
			if (!$this->_GetDataFromDb($Sql, $Data, ($this->_CacheCurrSql===false))) return false; // if CacheSql is false, then we do not need to retrieve all the records from the db
			if ($this->_CacheCurrSql!==false) $this->_CacheUpdate($Data);
		}

		if (count($Data)==0) return false;
		
		$Data = array($Data[0]);

		// conversion to object		
		if ($ObjConv) {
			$this->_ObjConversion($Data, $Arg0);
		} elseif ($this->DefaultRowType!==TBSSQL_ARRAY) {
			$this->_ObjConversion($Data, $this->DefaultRowType);
		}

		return $Data[0];

	}

	function GetRows($Arg0) {

		$ArgLst = func_get_args();

		$ObjConv = $this->_ObjCheck($Arg0, $ArgLst);
		$SqlPos = ($ObjConv) ? 1 : 0;
		$Sql = $this->_SqlProtect($ArgLst, $SqlPos);

		$Data = array();
		if (!$this->_CacheTryRetrieve($Sql,$Data)) {
			if (!$this->_GetDataFromDb($Sql, $Data)) return false;
			if ($this->_CacheCurrSql!==false) $this->_CacheUpdate($Data);
		}

		// conversion to object		
		if ($ObjConv) {
			$this->_ObjConversion($Data, $Arg0);
		} elseif ($this->DefaultRowType!==TBSSQL_ARRAY) {
			$this->_ObjConversion($Data, $this->DefaultRowType);
		}
		
		return $Data;
		
	}

	function GetList($Sql) {
		
		$ArgLst = func_get_args();
		$Sql = $this->_SqlProtect($ArgLst);
		
		$Data = array();
		$col1 = false;
		$col2 = false;

		if (!$this->_CacheTryRetrieve($Sql,$Data)) {
			if ($this->_CacheCurrSql===false) {
				// no cache
				$RsId = $this->_Dbs_RsOpen($Sql);
				if ($RsId===false) return $this->_SqlError($this->Id);
				$r = $this->_Dbs_RsFetch($RsId); // First row
				if ($r===false) return $Data;
				$this->_ItemFoundCol($r, $col1, $col2);
				$this->_ItemAdd($Data, $r, $col1, $col2);
				while ($r = $this->_Dbs_RsFetch($RsId)) $this->_ItemAdd($Data, $r, $col1, $col2); // other rows
				$this->_Dbs_RsClose($RsId);
				return $Data;
			} else {
				if (!$this->_GetDataFromDb($Sql, $Data)) return false;
				$this->_CacheUpdate($Data);
			}
		}
		
		// At this point, $Data contain bold data.
		$Data2 = array();
		if (!isset($Data[0])) return $Data2;
		$this->_ItemFoundCol($Data[0], $col1, $col2);
		foreach ($Data as $r) $this->_ItemAdd($Data2, $r, $col1, $col2);
		return $Data2;
		
	}

	function GetSql($Sql) {
		$ArgLst = func_get_args();
		return $this->_SqlProtect($ArgLst);
	}

	function AffectedRows() {
		return $this->_Dbs_AffectedRows();
	}

	function LastRowId($Info=false) {
		return $this->_Dbs_LastRowId($Info);
	}

	function SetTbsKey($Key='') {
		// Define the key (or a new key) for the TinyButStrong Template Engine
		global $_TBS_UserFctLst;
		if ($Key=='') {
			// Search for an free key
			$Key = 'tbssql'; // default key
			$i = 1;
			while (isset($_TBS_UserFctLst['k:'.$Key])) {
				$i++;
				$Key = 'tbssql'.$i;
			}
		}
		$this->TbsKey = $Key;
		$this->_Tbs_FetchFct = '_Tbs_RsFetch_Default'; // saved in a property in order to be passed by reference and then be modified when cache is enabled
		$_TBS_UserFctLst['k:'.$Key] = array('type'=>4,'open'=>array(&$this,'_Tbs_RsOpen'),'fetch'=>array(&$this,&$this->_Tbs_FetchFct),'close'=>array(&$this,'_Tbs_RsClose'));
	}

	function CacheTimestamp($Sql) {
	// return the timestamp of the cache file
		$x = $this->_CacheFilePath($Sql);
		if (file_exists($x)) {
			return filemtime($x);
		} else {
			return false;
		}
	}

	function CacheDelete($Sql) {
	// delete the cache file
		$x = $this->_CacheFilePath($Sql);
		if (file_exists($x)) {
			$ok = unlink($x);
			if ($ok) {
				if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('cache', 'CacheDelete() has deleted the cache file: '.$x);
			} else {
				$this->_Message('error', 'CacheDelete() unexpected failure. Check permissions for deleting cache file '.$x);
			}
			return $ok;
		} else {
			if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('cache', 'CacheDelete() has not found the cache file '.$x);
			return false;
		}
	}

	function CacheFile($Sql) {
	// return the full path of the cache file
		$x = $this->_CacheFilePath($Sql);
		if (file_exists($x)) {
			return $x;
		} else {
			return false;
		}
	}

	function ConfInfo() {
		
		$this->InitMsg = false; // prevent from displaying the info twice
		
		// version
		$this->_Message('conf', 'TbsSql version '.$this->Version);
		
		// mode
		$x = $this->_ModeDecompose($this->Mode, array(TBSSQL_SILENT=>'TBSSQL_SILENT', TBSSQL_NORMAL=>'TBSSQL_NORMAL', TBSSQL_DEBUG=>'TBSSQL_DEBUG', TBSSQL_TRACE=>'TBSSQL_TRACE', TBSSQL_GRID=>'TBSSQL_GRID', TBSSQL_CONSOLE=>'TBSSQL_CONSOLE'));
		$this->_Message('conf', 'property Mode = '.$x);
		
		// DefaultRowType
		$x = $this->_ModeDecompose($this->DefaultRowType, array(TBSSQL_ARRAY=>'TBSSQL_ARRAY', TBSSQL_OBJECT=>'TBSSQL_OBJECT'));
		$this->_Message('conf', 'property DefaultRowType = '.$x);

		// CacheTimeout
		$x = $this->_ModeDecompose($this->CacheTimeout, false);
		$this->_Message('conf', 'property CacheTimeout = '.$x);

		// TempCacheTimeout
		$x = $this->_ModeDecompose($this->TempCacheTimeout, false);
		$this->_Message('conf', 'property TempCacheTimeout = '.$x);

		// CacheAutoClear
		$x = $this->_ModeDecompose($this->CacheAutoClear, false);
		$this->_Message('conf', 'property CacheAutoClear = '.$x);

		// CacheDir and CacheSuffix
		$this->_Message('conf', 'property CacheDir = '.var_export($this->CacheDir,true));
		$this->_Message('conf', 'property CacheSuffix = '.var_export($this->CacheSuffix,true));
		
	}

// Private methods

	// Special for TinyButStrong
	// -------------------------
	function _Tbs_RsOpen($Src,$Sql) {
		$Sql = $this->_SqlProtect(array($Sql)); // just in order to manage the Mode when it is TRACE or DEBUG
		$this->_CacheTbsData = array(); // default value in case of data to be saved
		if ($this->_CacheTbsOk=$this->_CacheTryRetrieve($Sql,$this->_CacheTbsData)) {
			// Data is retrieved from the cache
			$this->_CacheTbsEnd = count($this->_CacheTbsData);
			$this->_CacheTbsCurr = -1;
			$this->_Tbs_FetchFct = '_Tbs_RsFetch_FromCache'; // from cache
			$RecSet = true;
		} else {
			// No cache
			$RecSet = $this->_Dbs_RsOpen($Sql);
			if ($RecSet===false) $this->_SqlError(false);
			if ($this->_CacheCurrSql===false) {
				$this->_Tbs_FetchFct = '_Tbs_RsFetch_Default'; // no cache
			} else {
				$this->_Tbs_FetchFct = '_Tbs_RsFetch_ToBeSaved'; // cache to be saved
			}
		}
		return $RecSet;
	}

	function _Tbs_RsFetch_Default(&$RsId) {
		// no cache
		return $this->_Dbs_RsFetch($RsId);
	}
	function _Tbs_RsFetch_FromCache(&$RsId) {
		// read data from cache
		$this->_CacheTbsCurr++;
		if ($this->_CacheTbsCurr>=$this->_CacheTbsEnd) {
			return false;
		} else {
			return $this->_CacheTbsData[$this->_CacheTbsCurr];
		}
	}	

	function _Tbs_RsFetch_ToBeSaved(&$RsId) {
		// data to be saved
		$Rec = $this->_Dbs_RsFetch($RsId);
		if ($Rec!==false) $this->_CacheTbsData[] = $Rec;
		return $Rec;
	}

	function _Tbs_RsClose(&$RsId) {
		if ($this->_CacheTbsOk) {
			unset($this->_CacheTbsData); // free memory
			return true;
		} else {
			if ($this->_CacheCurrSql!==false) $this->_CacheUpdate($this->_CacheTbsData);
			return $this->_Dbs_RsClose($RsId);
		}
	}

	// Other private methods

	function _SqlError($ObjId) {
		if ($this->Mode===TBSSQL_SILENT) return;
		$x =  'Database error message: '.$this->_Dbs_Error($ObjId);
		if ($this->_ModeHas(TBSSQL_DEBUG)) $x .= "\r\nSQL = ".$this->LastSql;
		$this->_Message('error', $x);
		return false;
	}

	function _Message($Type, $Txt) {
	// display a message right now in the Html page or in the console. Must return false. Default color is red, which means error.

		if ($this->Mode===TBSSQL_SILENT) return false;

		if ($Type==='error') {
			$Color = '#FF0000';
			$Prefix = '[Error]: ';
		} elseif ($Type==='sql') {
			$Color = '#663399';
			$Prefix = '[SQL]: ';
		} elseif ($Type==='connection') {
			$Color = '#663399';
			$Prefix = '[Connection]: ';
		} elseif ($Type==='cache') {
			$Color = '#060';
			$Prefix = '[Cache]: ';
		} elseif ($Type==='conf') {
			$Color = '#060';
			$Prefix = '[Configuration]: ';
		}

		if ( ($Type!=='error') && $this->InitMsg ) {
			// display the TbsSql version the very first time and continue
			$this->ConfInfo();
		}

		if (is_array($Txt)) {
			$Html =  $this->_HtmlGrid($Txt);
		} else {
			$Html = '<div style="color: '.$Color.';">[TbsSql]'.nl2br(htmlentities($Prefix.$Txt)).'</div>'."\r\n";
		}

		if ($this->_ModeHas(TBSSQL_CONSOLE)) {
			$Html = addslashes($Html); // apply to ('), ("), (\) and (null)
			$Html = str_replace(array("\n","\r","\t"),array('\n','\r','\t'),$Html);
			if ($this->InitCsl) {
				$this->InitCsl = false;
				$m1 = 100;
				$Html = '
<script type="text/javascript"> /* code generated by TbsSql */
var TbsSqlConsole = window.open("","TbsSql_Console","top='.$m1.',left="+(screen.width-screen.height+'.$m1.')+",width="+(screen.height-'.(2*$m1).')+",height="+(screen.height-'.(2*$m1).')+",resizable=yes,scrollbars=yes,location=no,status=no,toolbar=no,menubar=no,directories=no");
TbsSqlConsole.document.body.innerHTML = ""; // clear the console when the parent window is actualized
TbsSqlConsole.document.write(\'<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=utf-8" /><title>TbsSQL Console</title></head><body>'.$Html.'</body></html>\');
TbsSqlConsole.focus();
</script>
';
			} else {
				$Html = '<script type="text/javascript">/* code generated by TbsSql */ TbsSqlConsole.document.body.innerHTML += "'.$Html.'";</script>';
			}
		}

		echo $Html;
		flush();
		return false;
		
	}

	function _ModeHas($Option) {
		return (($this->Mode & $Option)===$Option);
	}

	function _ModeDecompose($Mode, $Options ) {
	// decompose the mode in options

		// string mode
		if (is_string($Mode)) {
			if (isset($Options[$Mode])) {
				return $Options[$Mode];
			} else {
				return $Mode;
			}
		}
		
		// duration
		if ($Options===false) {
			$Options = array('TBSSQL_1WEEK'=>TBSSQL_1WEEK, 'TBSSQL_1DAY'=>TBSSQL_1DAY, 'TBSSQL_1HOUR'=>TBSSQL_1HOUR, 'TBSSQL_1MINUTE'=>TBSSQL_1MINUTE, 'TBSSQL_ALWAYS'=>TBSSQL_ALWAYS, 'TBSSQL_NOCACHE'=>TBSSQL_NOCACHE, 'TBSSQL_DISABLED'=>TBSSQL_DISABLED); // order must be respected
			if (in_array($Mode, $Options, true)) return array_search($Mode, $Options, true);
			$x = array();
			$remain = $Mode;
			foreach ($Options as $name=>$duration) {
				if ( ($duration!==false) && ($duration>0) && ($remain>=$duration) ) {
					$r = ($remain % $duration);
					$n = intval(($remain-$r)/$duration);
					$remain = $remain - ($n*$duration);
					$x[] = ($n==1) ? $name : $n.'*'.$name;
				}
			}
			if (count($x)==0) return $Mode.' (minutes)';
			if ($remain>0) $x[] = $remain.' (minutes)';
			return implode(' + ', $x);
		}
		
		// numeric
		$x = array();
		$remain = $Mode;
		foreach ($Options as $opt=>$name) {
			if ( ($opt!=0) && (($Mode & $opt)===$opt) ) {
				$x[] = $name;
				$remain = $remain - $opt;
			}
		}
		if ($remain!=0) $x[] = $remain;
		if (count($x)==0) {
			if (isset($Options[0])) {
				$x[] = $Options[0];
			} else {
				$x[] = 0;
			} 
		}
		return implode(' + ', $x);
		
	}

	function _TakeVar($VarName,$Key,&$Target,$MustBe=true) {
	// Read gloabl variables for automatic connexion
		if (isset($GLOBALS[$VarName][$Key])) {
			$Target = $GLOBALS[$VarName][$Key];
		} elseif ($MustBe) {
			$this->_Message('error', 'Automatic Connection failed because the item \''.$Key.'\' is not found in the global variable \''.$VarName.'\'.');
			return false;
		}
		return true;
	}

	function _ObjCheck($Arg0, $ArgLst) {
	// Check if an object conversion is asked
		if (is_string($Arg0)) {
			if( (strpos($Arg0,' ')===false) && (strpos($Arg0,"\r")===false) && (strpos($Arg0,"\n")===false) && (count($ArgLst)>1) ) return true;
		} elseif (is_object($Arg0)) {
			return true;
		}
		return false;
	}

	function _ObjConversion(&$Data, $ObjRef) {
	// Convert the records from arrays to objects
	// Note: foreach() is twice faster than array_walk() for both record and column loops.
		if (is_object($ObjRef)) {
			foreach ($Data as $idx => $rec) {
				$obj = clone($ObjRef);
				foreach ($rec as $col => $val) {$obj->$col = $val;}
				$Data[$idx] = $obj;
			}
		} else {
			if ($ObjRef==='array') return;
			if (($ObjRef==='') || ($ObjRef==='object')) $ObjRef = 'stdClass';
			foreach ($Data as $idx => $rec) {
				$obj = new $ObjRef; // the class name
				foreach ($rec as $col => $val) {$obj->$col = $val;}
				$Data[$idx] = $obj;
			}
		}
	}

	function _SqlDateFrmDb($Date,$FrmMode) {
	// Prepare the date item before to merge it in an SQL query

		if (is_string($Date)) {
			if (!isset($this->_SqlDateParseOk)) $this->_SqlDateParseOk = function_exists('date_parse'); // date_parse() is available since PHP 5.2
			if ($this->_SqlDateParseOk) {
				$x = date_parse($Date);
				if (($x===false) || ($x['warning_count']>0) || ($x['error_count']>0)) {
					$this->_Message('error', 'the date argument (string) \''.$Date.'\' is not recognized as a valide date. It will be merged as is in the SQL query.');
					$Mode = 0; // The date will be passed as is to the SQL
					$x = $Date;
				} else {
					$x = array( 'y'=>$x['year'] , 'm'=>$x['month'] , 'd'=>$x['day'] , 'h'=>intval($x['hour']) , 'i'=>intval($x['minute']) , 's'=>intval($x['second']), 'f'=>intval($x['fraction'])); // intval() helps to force false to 0
				}
			} else {
				$x = strtotime($Date);
				if (($x===-1) || ($x===false)) {
					$this->_Message('error', 'the date argument (string) \''.$Date.'\' is not recognized as a valide date. This can happens on 32bit systems for dates over 2038-01-19. TbsSQL can workaround this date limit if you use PHP>=5.2');
					$Mode = 0; // The date will be passed as is to the SQL
					$x = $Date;
				} else {
					$x = $this->_SqlDateInfo($x);
				}
			}
		} elseif (is_int($Date) || is_float($Date)) {
			// It's a timestamp
			$x = $this->_SqlDateInfo($Date);
		} elseif (is_array($Date)) {
			// It's an array supported by TbsSQL
			$x = $Date;
			if (!isset($x['y'])) {
				$this->_Message('eror', 'the date argument is an array but has no key \'y\'. The value will be replaced with the current date-time.');
				$x = $this->_SqlDateInfo(time());
			}
			if (!isset($x['m'])) $x['m'] = 1;
			if (!isset($x['d'])) $x['d'] = 1;
			if (!isset($x['h'])) $x['h'] = 0;
			if (!isset($x['i'])) $x['i'] = 0;
			if (!isset($x['s'])) $x['s'] = 0;
		} else {
			$x = ''; // avoid a PHP error notice
			$this->_Message('error', 'the date argument can not be recognized as date: '.var_export($Date,true));
		}

		return $this->_Dbs_Date($x,$FrmMode);

	}

	function _SqlDateInfo($Timestamp) {
	// return a date info array from a timestamp
		$x = getdate($Timestamp);
		return array( 'y'=>$x['year'] , 'm'=>$x['mon'] , 'd'=>$x['mday'] , 'h'=>$x['hours'] , 'i'=>$x['minutes'] , 's'=>$x['seconds'], 'f'=>0.0);
	}

	function _SqlDateFrmStd($d, $s_date, $s_ent=false, $s_time=false, $s_frac=false, $s_frac_nbr=3) {
	// useful function for common date formating, to be used in method _Dbs_Date();
		$x = $d['y'].$s_date.str_pad($d['m'],2,'0',STR_PAD_LEFT).$s_date.str_pad($d['d'],2,'0',STR_PAD_LEFT);
		if ($s_ent!==false) {
			// time is added to the date
			$x .= $s_ent.str_pad($d['h'],2,'0',STR_PAD_LEFT).$s_time.str_pad($d['i'],2,'0',STR_PAD_LEFT).$s_time.str_pad($d['s'],2,'0',STR_PAD_LEFT);
			if ($s_frac!==false) $x .= $s_frac.substr(number_format($d['f'],$s_frac_nbr,'.',''),2); // fraction of seconds is added to the date-time
		}
		return $x;
	}

	function _SqlProtect($ArgLst, $SqlPos=0, $Normal=true) {
	// Replace items (%i% , @i@ , #i#, ~i~) with the corresponding protected values
		$Sql = $ArgLst[$SqlPos];
		$IdxMax = count($ArgLst) - 1;
		$ChrLst = array('%','@','#','~');
		for ($i=$SqlPos+1;$i<=$IdxMax;$i++) {
			for ($t=0;$t<=1;$t++) {
				if ($t==0) {
					// Scann normal tags
					$ValIsNull = false;
					$iz = ''.$i;
				} else {
					// Scann nullable tags 
					$ValIsNull = ( is_null($ArgLst[$i]) or ($ArgLst[$i]===false) );					
					$iz = '['.$i.']';
				}
				foreach ($ChrLst As $Chr) {
					$tag = $Chr.$iz.$Chr;
					if (strpos($Sql,$tag)!==false) {
						if ($ValIsNull) {
							$x = $this->SqlNull;
						} elseif ($Chr==='%') {
							$x = $this->_Dbs_ProtectStr(''.$ArgLst[$i]); // Simple value
						} elseif ($Chr==='@') {
							$x = $x = '\''.$this->_Dbs_ProtectStr(''.$ArgLst[$i]).'\''; // String value
						} elseif ($Chr==='#') {
							$x = $x = $this->_SqlDateFrmDb($ArgLst[$i],1); // Date value
						} elseif ($Chr==='~') {
							$x = $x = $this->_SqlDateFrmDb($ArgLst[$i],2); // Date and time value
						}
						$Sql = str_replace($tag,$x,$Sql) ;
					}
				}
			}
		}
		if ($Normal) {
			if ($this->_ModeHas(TBSSQL_DEBUG)) {
				$this->LastSql = $Sql;
			} elseif ($this->_ModeHas(TBSSQL_TRACE)) {
				$this->_Message('sql', $Sql);
			}
		}
		return $Sql;
	}

	function _GetDataFromDb($Sql, &$Data, $OnlyFirstRow=false) {

		$RsId = $this->_Dbs_RsOpen($Sql);
		if ($RsId===false) return $this->_SqlError($this->Id);

		$Data = array();
		if ($OnlyFirstRow) {
			$r =  $this->_Dbs_RsFetch($RsId);
			if ($r!==false) $Data[] = $r;
		} else {
			while ($r = $this->_Dbs_RsFetch($RsId)) {
				$Data[] = $r;
			}
		}

		$this->_Dbs_RsClose($RsId);
		
		if ($this->_ModeHas(TBSSQL_GRID)) $this->_Message('data', $Data);

		return true;
	}

	function _ItemFoundCol($Row, &$col1, &$col2) {
		if ($Row===false) return false;
		$col_lst = array_keys($Row);
		$col1 = $col_lst[0];
		$col2 = isset($col_lst[1]) ? $col_lst[1] : false;
		return true;
	}

	function _ItemAdd(&$Data, $Row, $col1, $col2) {
		if ($col2===false) {
			$Data[] = $Row[$col1];	
		} else {
			$Data[$Row[$col1]] = $Row[$col2];	
		}
	}

// trace functions
// ---------------

	function _HtmlGrid($Data) {

		$nl = "\n";
		$nbsp = '&nbsp;';
		$firstrow = true;
		$row_bg = array(0=>'#faebd7', 1=>'#fff8dc');
		$row_idx = 0;

		$html = $nl.'<table border="1" cellspacing="0" cellpadding="2">';
		
		foreach ($Data as $id => $row) {
			if ($firstrow) {
				$html .= $nl.' <tr bgcolor="#999999"><td>#</td>';
				foreach ($row as $col => $val) $html .= '<td>'.str_replace(' ',$nbsp,$col).'</td>';
				$html .= '</tr>';
				$firstrow = false;
			}
			$html .= $nl.' <tr bgcolor="'.$row_bg[$row_idx].'"><td>'.$id.'</td>';
			foreach ($row as $col => $val) {
				$x = str_replace(' ',$nbsp,strval($val));
				if ($x==='') $x = $nbsp;
				$html .= '<td>'.$x.'</td>';
			}
			$html .= '</tr>';
			$row_idx++;
			if ($row_idx>1) $row_idx=0;
		}
		
		if ($firstrow) {
			$html .= $nl.' <tr bgcolor="'.$row_bg[0].'"><td>No data</td></tr>';
		}
		
		$html .= $nl.'</table>';

		return $html;

	}

	function _TraceGetTime() {
	// return the current timer in milli-seconds
		$x = microtime();
		$p = strpos($x,' ');
		if ($p===false) return (float)0;
		$x = substr($x,$p+1).substr($x,1,$p);
		return (float)$x;
	}

// cache functions
// ---------------

	function _CacheFilePath($Sql) {
		return $this->CacheDir.'/cache_tbssql_'.md5($Sql).$this->CacheSuffix.'.php';  // we save it as a PHP file in order to hide the contents from web users
	}

	function _CacheTryRetrieve($Sql, &$Data) {
	// Try to retrieve data from the cache file. Return true if $Data contains the data from the cache.

		// at this point $this->_CacheCurrSql is always false
		$this->_CacheCurrSql = false; // for security

		// check if cache is enabled
		if ($this->TempCacheTimeout===false) {
			if (($this->CacheTimeout===false) || ($this->CacheTimeout===TBSSQL_NOCACHE)) return false;
			$timeout = $this->CacheTimeout;
		} else {
			$timeout = $this->TempCacheTimeout;
			$this->TempCacheTimeout = false;
			if ($timeout===TBSSQL_NOCACHE) return false;
		}

		// 
		$this->_CacheCurrFile = $this->_CacheFilePath($Sql);
		$now = time();
		//echo 'debug CacheTryRetrieve : timout= '.$timeout.', cache file='.date('Y-m-d h:i:s',@filemtime($this->_CacheCurrFile)).' , limit='.date('Y-m-d h:i:s',@filemtime($this->_CacheCurrFile)+60*$timeout).' , now='.date('Y-m-d h:i:s',$now)."<br>\r\n";
		if ( file_exists($this->_CacheCurrFile) && ($now<=(filemtime($this->_CacheCurrFile)+60*$timeout)) ) {
			// retrieve the data
			// echo 'debug CacheTryRetrieve : cache still current'."<br>\r\n";
			include($this->_CacheCurrFile); // set $CacheSql and $Data
			if ($Sql===$CacheSql) {
				if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('cache', 'Data retrieved from cache file '.$this->_CacheCurrFile);
				if ($this->_ModeHas(TBSSQL_GRID))  $this->_Message('data', $Data);
				return true;
			} else {
				// It can happens very rarely that two different SQL queries have the same md5, with this chech we are sure to have to good result
				if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('cache', 'Data not retrieved from cache file '.$this->_CacheCurrFile.' because SQL is different.');
				return false;
			}
		}
		
		//echo 'debug CacheTryRetrieve : cache to be updated.'."<br>\r\n";
		$this->_CacheCurrSql = $Sql;
		return false;
		
	}

	function _CacheUpdate($Data) {
	// Update the cache

		$fid = @fopen($this->_CacheCurrFile, 'w');
		if ($fid===false) {
			$this->_Message('error', 'The cache file '.$this->_CacheCurrFile.' cannot be saved.');
			$ok = false;
		} else {
			flock($fid,2); // acquire an exlusive lock
			fwrite($fid,'<?php $CacheSql='.var_export($this->_CacheCurrSql,true).'; ');
			fwrite($fid,'$Data='.var_export($Data,true).';');
			flock($fid,3); // release the lock
			fclose($fid);
			//echo 'debug CacheTryUpdate : cache file='.date('Y-m-d h:i:s',filemtime($this->_CacheCurrFile)).' end='.date('Y-m-d h:i:s',$cache_end).' , now='.date('Y-m-d h:i:s',time()).' , ok='.var_export($ok,true)."<br>\r\n";
			if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('cache', 'Data saved in cache file '.$this->_CacheCurrFile);
			$ok = true;
		}
		$this->_CacheCurrSql = false;
		if ($this->CacheAutoClear!==false) {
			$this->_CacheTryClearDir();
			$this->CacheAutoClear = false; // only one try per script call is enought, no need to check for each SQL query
		}
		return $ok;
	}

	function _CacheTryClearDir() {
	// Try to delete too old cache files

		$check_file = $this->CacheDir.'/cache_tbssql_info.php';
		
		// if the check file does not exist yet, we create it
		if (!file_exists($check_file)) {
			touch($check_file);
			return;
		}

		// if the check file is too young, we do not clear
		$limit = time()-60*$this->CacheAutoClear;
		if (filemtime($check_file)>$limit) return;
		
		// clear the directory
		$lst = array();
		$dir = opendir($this->CacheDir);
		$pref = 'cache_tbssql_';
		$suff = $this->CacheSuffix.'.php';
		$pref_len = strlen($pref);
		$suff_len = strlen($suff);
		$file_len = $pref_len+strlen(md5(''))+$suff_len;
		while ($file = readdir($dir)) {
			if ( (strlen($file)==$file_len) && (substr($file,0,$pref_len)===$pref) && (substr($file,-$suff_len)===$suff) ) {
				$fullpath = $this->CacheDir.'/'.$file;
				if (filemtime($fullpath)<=$limit) $lst[] = $fullpath;
			}
		}
		closedir($dir);		

		foreach ($lst as $fullpath) {
			unlink($fullpath);
		}

		touch($check_file);

		if ($this->_ModeHas(TBSSQL_TRACE)) $this->_Message('cache', 'CacheAutoClear has deleted '.count($lst).' old cache files from directory '.$this->CacheDir);

	}

// -------------------------------
// Specific to the Database System
// -------------------------------

// Database Engine: MySQL, using mysql functions
// Version 1.04, 2010-10-21, Skrol29
	
	function _Dbs_Prepare() {
		// If no connexion parameters are given, try to link to the current MySQL connection
		$this->Version .= ' for MySQL';
		if (!is_resource($this->Id)) {
			if (@mysql_ping()) $this->Id = true;
		}
	}

	function _Dbs_Connect($srv,$uid,$pwd,$db,$drv) {
	// Should set $this->Id, value false means connection failed.

		// Information, must be the same for any database type	
		if ($this->Mode==TBSSQL_DEBUG) $this->LastSql = 'Connection String='.$srv;
		if ($this->Mode==TBSSQL_TRACE) $this->_Message('connection', 'Connection String: '.$srv);

		$this->Id = @mysql_connect($srv,$uid,$pwd,true); // Force a new connection in order to manage connections to two different DB in the same script. Doesn't work if SQL Safe Mode is activated.
		
		// Chnage the current database
		if (($this->Id!==false) and ($db!=='')) {
			if (!@mysql_select_db($db, $this->Id)) $this->_SqlError(false);
		}

	}

	function _Dbs_Close() {
		if (is_resource($this->Id)) {
			return @mysql_close($this->Id);
		} else {
			return @mysql_close();
		}
	}

	function _Dbs_Error($ObjId) {
		if (is_resource($this->Id)) {
			return @mysql_error($ObjId);
		} else {
			return @mysql_error();
		}
	}
	
	function _Dbs_RsOpen($Sql, $Exec=false) {
		if (is_resource($this->Id)) {
			return @mysql_query($Sql,$this->Id);
		} else {
			return @mysql_query($Sql);
		}
	}

	function _Dbs_RsFetch(&$RsId) {
		return mysql_fetch_assoc($RsId);
	}

	function _Dbs_RsClose(&$RsId) {
		if ($RsId===true)  return true;
		return @mysql_free_result($RsId);
	}
	
	function _Dbs_ProtectStr($Txt) {
		return mysql_escape_string($Txt);
	}
	
	function _Dbs_Date($DateInfo,$FrmMode) {
		switch ($FrmMode) {
		case 1:
			// Date only
			return '\''.$this->_SqlDateFrmStd($DateInfo, '-').'\'';
		case 2:
			// Date and time
			return '\''.$this->_SqlDateFrmStd($DateInfo, '-', ' ', ':').'\'';
		case 0:
			// Value is a string
			return '\''.$this->_Dbs_ProtectStr($DateInfo).'\'';
		default:
			// Error in date recognization
			return '\'0000-00-00\'';
		}
	}

	function _Dbs_LastRowId($Info=false) {
		return $this->GetVal('SELECT LAST_INSERT_ID()');
	}
	
	function _Dbs_AffectedRows() {
		if (is_resource($this->Id)) {
			return mysql_affected_rows($this->Id);
		} else {
			return mysql_affected_rows();
		}
	}

}