Location: PHPKode > projects > Thought Push PHP Framework > system/utilities/system/Db.php
<?php
///see warning
/**Class details:
	- Class employs lazy loader(Connection only made when needed) and simgleton(Only one db object instantiated) patterns.
	- Class holds db instances in static $con allowing one db instance to use multiple connections: might be useful in master & slave database
	- @warning Class sets sql_mode to ansi sql if mysql db to allow interroperability with postgres.	As such, double quotes " become table and column indicators, ` become useless, and single quotes are used as the primary means to quote strings
	- @note Most of the querying methods are overloaded; there are two forms of possible input:
		- Form 1:	simple sql string; eg "select * from bob where bob = 'bob'"
		- Form 2: 
			@verbatim
			@param	table	the table to be used
			@param	select	a select array.	See the Db::select function
			@endverbatim
	- most private functions are still callable due to __call and __callStatic
*/
class Db{
	/// reference to primary PDO instance
	public $db;	
	/// latest result set returning from $db->query()
	public $result;				// latest result set returns from $db->query()
	/// Name of the primary database connection
	static $primary = 0;
	/// named Db class instances
	static $connections = array();
	/// last SQL statement
	static $lastSql;			
	
	///prevent public instantiation
	private function __construct(){}
	
	///make connection
	/**
	@param	connection	name of the connection
	*/
	function connect($connection=null){
		$dsn = $this->connectionInfo['driver'].':dbname='.$this->connectionInfo['database'].';host='.$this->connectionInfo['host'];
		$this->db = new PDO($dsn,$this->connectionInfo['user'],$this->connectionInfo['password']);
		if($this->db->getAttribute(PDO::ATTR_DRIVER_NAME)=='mysql'){
			$this->query('SET SESSION sql_mode=\'ANSI\'');
			#$this->db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true);
		}
	}
	///lazy load a new db instance; uses singleton base on name.
	/**
	@param	connectionInfo	array:
		@verbatim
array(
	driver => ...,
	database => ...,
	host => ...,
	user => ...,
	password ...
		@endverbatim
	@param	name	name of the connetion
	*/
	static function initialize($connectionInfo,$name=0){
		if(!isset(self::$connections[$name])){
			//set primary if no connections except this one
			if(!self::$connections){
				self::$primary = $name;
			}
			//add connection
			$class = __class__;
			self::$connections[$name] = new $class();
			self::$connections[$name]->connectionInfo = $connectionInfo;
		}
		return self::$connections[$name];
	}

	/// used to translate static calls to the primary database instance
	static function __callStatic($name,$arguments){
		//allow _func to be treated as func
		if(!method_exists(__class__,$name)){
			$name = '_'.$name;
		}
		return call_user_func_array(array(self::$connections[self::$primary],$name),$arguments);
	}
	/// used to translate calls to non existance methods to _method
	function __call($name,$arguments){
		//allow _func to be treated as func
		if(!method_exists(__class__,$name)){
			$name = '_'.$name;
		}
		return call_user_func_array(array($this,$name),$arguments);
	}

	/// returns escaped string with quotes.	Use on values to prevent injection.
	/**
	@param	v	the value to be quoted
	*/
	private function quote($v){
		if(!$this->db){
			$this->connect($this->connectionInfo);
		}
		return $this->db->quote($v);
	}
	
	/// perform database query
	/**
	@param	sql	the sql to be run
	*/
	private function query($sql){
		if(!$this->db){
			$this->connect($this->connectionInfo);
		}
		if($this->result){
			$this->result->closeCursor();
		}
		self::$lastSql = $sql;
		$this->result = $this->db->query($sql);
		if((int)$this->db->errorCode()){
			$error = $this->db->errorInfo();
			$error = "--DATABASE ERROR--\n".' ===ERROR: '.$error[0].'|'.$error[1].'|'.$error[2]."\n ===SQL: ".$sql;
			Debug::throwError($error);
		}
		return $this->result;
	}

	/// Used internally.	Checking number of arguments for functionality
	protected function getOverloadedSql($expected, $actual){
		$overloaded = count($actual) - $expected;
		if($overloaded > 0){
			$overloaderArgs = array_slice($actual,-3);
			return call_user_func_array(array($this,'select'),$overloaderArgs);
			
		}else{
			return $actual[0];
		}
	}
	
	/// query returning a row
	/**See class note for input
	@warning "limit 1" is appended to the sql input
	@return	a single row, or, if one column, return that columns value
	*/
	private function row(){
		$sql = $this->getOverloadedSql(1,func_get_args());
		#function implies only 1 retured row
		$sql .= ' limit 1';
		if($res = $this->query($sql)){
			if($res->columnCount()==1){
				return	$res->fetchColumn();
			}
			return $res->fetch(PDO::FETCH_ASSOC);
		}
	}

	/// query returning multiple rows
	/**See class note for input
	@return	a sequential array of rows
	*/
	private function rows($sql){
		$sql = $this->getOverloadedSql(1,func_get_args());
		$res2 = array();
		if($res = $this->query($sql)){
			$i = 0;
			while($row=$res->fetch(PDO::FETCH_ASSOC)){
				foreach($row as $k=>$v){
					$res2[$i][$k]=$v;
				}
				$i++;
			}
		}
		return $res2;
	}
	
	/// query returning a column
	/**
	See class note for input
	@return	array where each element is the column value of each row
	*/
	private function column($sql){
		$sql = $this->getOverloadedSql(1,func_get_args());
		$res = $this->query($sql);
		while($row=$res->fetch(PDO::FETCH_NUM)){$res2[]=$row[0];}
		if(!is_array($res2)){
			return array();
		}
		return $res2;
	}

	/// query returning a list
	/**See class note for input
	@return	row as numerically indexed array for potential use by php list function
	*/
	private function _list($sql){
		$sql = $this->getOverloadedSql(1,func_get_args());
		$sql .= ' limit 1';
		return $this->query($sql)->fetch(PDO::FETCH_NUM);
	}
	
	
	/// query returning a column with keys
	/**See class note for input
	@param	key	the column key to be used for each element.	If they key is an array, the first array element is taken as the key, the second is taken as the mapped value column
	@return	array where one column serves as a key pointing to either another column or another set of columns
	*/
	
	private function columnKey($key,$sql){
		array_shift($arguments = func_get_args());
		$rows = call_user_func_array(array($this,'rows'),$arguments);
		if(is_array($key)){
			return Arrays::addKey($rows,$key['key'] ? $key['key'] : $key[0], $key['value'] ? $key['value'] : $key[1]);
		}else{
			return Arrays::addKey($rows,$key);
		}
	}
	
	/// internal use.	Key to value formatter (used for where clauses and updates)
	/**
	@param	kvA	various special syntax is applied:
		- normally, sets key = to value, like "key = 'value'" with the value escaped
		- if "?" is in the key, the part before the "?" will server as the "equator", ("<>?bob"=>'sue') -> "bob <> 'sue'"
		- if key starts with ":", value is not escaped
			- if value is "null", on where prefix with "is".	
			- if value = null (php null), set string to null
		- if value = null, set value to unescaped "null"
	@param	type	1 = where, 2 = update
	*/
	private function ktvf($kvA,$type=1){		
		foreach($kvA as $k=>$v){
			if(strpos($k,'?')!==false){
				preg_match('@(^[^?]+)\?([^?]+)$@',$k,$match);
				$k = $match[1];
				$equator = $match[2];
			}else{
				$equator = '=';
			}
			
			if($k[0]==':'){
				$k = substr($k,1);
				if($v == 'null' || $v === null){
					if($type == 1){
						$equator = 'is';
					}
					$v = 'null';
				}
			}elseif($v === null){
				if($type == 1){
					$equator = 'is';
				}
				$v = 'null';
			}else{
				$v = $this->quote($v);
			}
			$k = '"'.$k.'"';
			#Fields like user.id to "user"."id"
			if(strpos($k,'.')!==false){
				$k = implode('"."',explode('.',$k));
			}
			$kvtA[] = $k.' '.$equator.' '.$v;
		}
		return $kvtA;
	}

	/// construct where clause from array or string
	/**
	@param	where	various forms:
		- either plain sql statement "bob = 'sue'"
		- single identifier "fj93" translated to "id = 'fj93'"
		- key to value array.	See self::ktvf()
	@return	where string
	@note if the where clause does not exist, function will just return nothing; this generally leads to an error
	*/
	private function where($where){
		if(is_array($where)){
			$where = implode(' AND ',$this->ktvf($where));
		}elseif(!$where){
			return;
		}elseif(!preg_match('@[ =<>]@',$where)){//ensures where is not long where string (bob=sue, bob is null), but simple item.
			if((string)(int)$where != $where){
				$where = $this->quote($where);
			}
			$where = 'id = '.$where;
		}
		return ' WHERE '.$where;
	}

	/// Key value formatter (used for insert like statements)
	/**
	@param	kva	array('key' => 'value',...)	special syntax is applied:
		- normally, sets (key) values (value) with the value escaped
		- if key starts with ":", value is not escaped
		- if value = null (php null), set string to null
	*/
	private function kvf($kvA){
		foreach($kvA as $k=>$v){
			if($k[0]==':'){
				$k = substr($k,1);
				if($v === null){
					$v = 'null';
				}
			}elseif($v === null){
				$v = 'null';
			}else{
				$v = $this->quote($v);
			}
			$keys[] = '"'.$k.'"';
			$values[] = $v;
		}
		return ' ('.implode(',',$keys).') VALUES ('.implode(',',$values).') ';
	}

	
	/// Insert into a table
	/**
	@param	table	table to insert on
	@param	kva	see self::kvf() function
	*/
	private function insert($table,$kvA,$ignore=false){
		return $this->into('INSERT '.($ignore?'IGNORE':null),$table,$kvA);
	}
	/// Insert with a table and ignore if duplicate key found
	/**
	@param	table	table to insert on
	@param	kva	see self::kvf() function
	@return	insert row id
	*/
	private function insertIgnore($table,$kvA){
		return $this->into('INSERT IGNORE',$table,$kvA);
	}
	/// insert into table; on duplicate key update
	/**
	@param	table	table to insert on
	@param	kva	see self::kvf() function
	@param	update	either plain sql or null; if null, defaults to updating all values to $kvA input
	@return	insert row id
	*/
	private function insertUpdate($table,$kvA,$update=null){
		if(!$update){
			$update .= implode(', ',$this->ktvf($kvA,2));
		}
		return $this->into('INSERT',$table,$kvA,'ON DUPLICATE KEY UPDATE '.$update);
	}

	/// replace on a table
	/**
	@param	table	table to replace on
	@param	kva	see self::kvf() function
	@return	row count
	*/
	private function replace($table,$kvA){
		return $this->into('REPLACE',$table,$kvA);
	}

	/// internal use; perform insert into [called from in(), inUp()]
	private function into($type,$table,$kvA,$update=''){
		$res = $this->query($type.' INTO "'.$table.'"'.$this->kvf($kvA).$update);
		if($this->db->lastInsertId()){
			return $this->db->lastInsertId();
		}elseif($kvA['id']){
			return $kvA['id'];
		}else{
			return $res->rowCount();
		}
	}

	/// perform update, returns number of affected rows
	/**
	@param	table	table to update
	@param	update	see self::ktvf() function
	@param	where	see self::where() function
	@return	row count
	*/
	private function update($table,$update,$where){
		$vf=implode(', ',$this->ktvf($update,2));
		return $this->query('UPDATE "'.$table.'" SET '.$vf.$this->where($where));
	}
	
	/// perform delete
	/**
	@param	table	table to replace on
	@param	where	see self::where() function
	@return	row count
	@note as a precaution, to delete all must use $where = '1 = 1'
	*/
	private function delete($table,$where){
		return $this->query('DELETE FROM "'.$table.'"'.$this->where($where))->rowCount();
	}
	
	/// perform a count and select rows; doesn't work with all sql
	/**
	See class note for input
	@return	array($count,$results)
	*/
	private function countAndRows($sql){
		$sql = $this->getOverloadedSql(1,func_get_args());
		$fromWhere = preg_split('@[\s]from[\s]@i',$sql,2);
		$fromWhere = preg_split('@[\s]order by[\s]|[\s]limit @i',$fromWhere[1],2);
		$count = $this->row('select count(*) from '.$fromWhere[0]);
		$results = $this->rows($sql);
		return array($count,$results);
	}
	///generate sql
	/**
	Ex: 
		- row('select * from user where id = 20') vs row('user',20);
		- rows('select name from user where id > 20') vs sRows('user',array('id?>'=>20),'name')
	@param	from	table
	@param	where	see self::$where()
	@param	columns	list of columns; either string or array.	"*" default.
	@return sql string
	@note	this function is just designed for simple queries
	*/
	private function select($from,$where,$columns='*'){
		$from = '"'.(is_array($from) ? implode('", "',$from) : $from).'"';
		if(is_array($columns)){
			$columns = '"'.implode('", "',$columns).'"';
		}
		return 'select '.$columns.' from '.$from.$this->where($where);
	}
}
?>
Return current item: Thought Push PHP Framework