<?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);
}
}
?>