/**  * The generic DB access Class, Entry of all DB Access  * Only PG is supported -- 201210  *  * @author Anthony  * 2010-2012 reserved  */  class DB {  // Query types  const SELECT = 1;  const INSERT = 2;  const UPDATE = 3;  const DELETE = 4;  /**  * True Value  */  const T = 't';  /**  * False Value  */  const F = 'f';  /**  * Null Value  */  const N = 'N/A'; //NULL Value  /**  * Specilize the value;  * 'f' as False, 't' as TRUE, 'N/A' as NULL value  *  * @param String $s, Orignal Value  *  * @return String, specilized value  */  public static function specializeValue($s){  if($s === self::N){  return NULL;  }  if($s === self::T){  return True;  }  if($s === self::F){  return False;  }  return $s;  }  /**  * Batch insert into table  * @param String $table_name Table Name  * @param Array $cols columns of table  * @param Array $values, values array of data  * @param String/Array $return_cols Return column[s], 'id' is return by default,Array for multi Column  * @param String $db Instance name of DB Connection  *  * @return Resultset return result set of return_cols  */  public static function insert_batch($table_name,$cols,$values,$return_cols='id',$db='default'){  $_sql = 'insert into '.self::quote_table($table_name,$db).'('.self::quote_column($cols,$db).') values ';  $_vsql = array();  foreach ($values as $value){  $_vsql[] = '('.self::quote($value).')';  }  $_sql .= implode(',',$_vsql);  $_sql .= ' returning '.self::quote_column($return_cols);  return self::query(self::SELECT,$_sql)->execute($db)->as_array();  }  /**  * Insert into table from Array Data, and return column[s], ID is return by default  *  * @param String $table_name Table Name  * @param Array $data Array Data Of key value pairs.  * @param String/Array $return_cols Return column[s], 'id' is return by default,Array for multi Column  * @param String $db Instance name of DB Connection  *  * @return Boolean/Resultset True if success without return column, False if failed, value of column[s] if return_cols presented.  */  public static function insert_table($table_name,$data,$return_cols='id',$db='default'){  if (!is_array($data)){  return false;  }  if (is_null($return_cols)){  $_sql = 'insert into '.self::quote_table($table_name,$db).'('.self::quote_column(array_keys($data),$db).') values ('.  self::quote(array_values($data),$db).')';  return self::query(self::INSERT,$_sql)->execute($db);  }  //Specialize value  $data = array_map('self::specializeValue',$data);  if (is_string($return_cols)){  $_sql = 'insert into '.self::quote_table($table_name,$db).'('.self::quote_column(array_keys($data),$db).') values ('.  self::quote(array_values($data),$db).')'." returning ".$return_cols;  $id = self::query(self::SELECT,$_sql)->execute($db)->get($return_cols);  return $id;  }else{  if (is_array($return_cols)){  $ids = implode(',',$return_cols);  $_sql = 'insert into '.self::quote_table($table_name,$db).'('.self::quote_column(array_keys($data),$db).') values ('.  self::quote(array_values($data),$db).')'." returning ".$ids;  $r_ids = self::query(self::SELECT,$_sql)->execute($db)->current();  return $r_ids;  }  }  return false;  }  /**  * Update Table data, and compare with reference data  *  * @param String $table_name Table Name  * @param Integer $id ID of data  * @param Array $data Array Data Of key value pairs.  * @param Array $refdata Reference data  * @param String $id_name Column name of ID  * @param String $db Instance name of DB Connection  *  * @return Integer Affected Rows,False if failed!  */  public static function update_data($table_name,$id,$data,$refdata,$id_name='id',$db='default'){  if (!is_array($data)){  throw new exception('Data should be col=>val pairs array');  }  foreach($data as $k => $v){  if(is_array($refdata)){  if(isset($refdata[$k])){  if($v == $refdata[$k]){  unset($data[$k]);  }  }  }elseif(is_object($refdata)){  if(isset($refdata->$k)){  if($v == $refdata->$k){  unset($data[$k]);  }  }  }else{  throw new exception('refdata type error');  }  }  //Specialize value  $data = array_map('self::specializeValue',$data);  if(count($data)>0){  return self::update_table($table_name,$id,$data,'id',$db);  }else{  return 0;  }  }  /**  * Update table with data without checking the referenced Data  *  * @param String $table_name Table Name  * @param Integer $id ID of data  * @param Array $data Array Data Of key value pairs.  * @param String $id_name Column name of ID  * @param String $db Instance name of DB Connection  *  * @return Integer Affected Rows,False if failed!  */  public static function update_table($table_name,$id,$data,$id_name='id',$db='default'){  if (!is_array($data)){  return false;  }  $_sql = 'update '.self::quote_table($table_name,$db).' set '.self::quote_assoicate($data,'=',',',$db).' where '.  self::quote_column($id_name,$db).'='.self::quote($id,$db);  return self::query(self::UPDATE,$_sql)->execute($db);  }  /**  * quote key value pair of col => values  *  * @param Array $data, col=>value pairs  * @param String $concat, default '='  * @param String Delimiter, default ','  * @param String Database instance  *  * @return String  */  public static function quote_assoicate($data,$concat='=',$delimiter=',',$db='default'){  $_sql = '';  $_sqlArray = array();  foreach ($data as $k => $v){  $_sqlArray[] = self::quote_column($k,$db).$concat.self::quote($v,$db);  }  $_sql = implode($delimiter,$_sqlArray);  return $_sql;  }  /**  * Quote cols  *  * @param String $value, The column[s] name  * @param String $db, Database Instance Name  */  public static function quote_column($value,$db='default'){  if(!is_array($value)){  return self::quote_identifier($value,$db);  }else{ //quote_column array and implode  $_qs = array();  foreach ($value as $ele){  $_qs[] = self::quote_column($ele,$db);  }  $_quote_column_String = implode(',',$_qs);  return $_quote_column_String;  }  }  /**  * Quote the values to escape  *  * @param Scalar/Array $value  *  * @return quote string or array  */  public static function quote($value,$db='default'){  if(!is_array($value)){  return Database::instance($db)->quote($value);  }else{ //Quote array and implode  $_qs = array();  foreach ($value as $ele){  $_qs[] = self::quote($ele,$db);  }  $_quoteString = implode(',',$_qs);  return $_quoteString;  }  }  /**  * Escape string of DB  *  * @param string $s table name  * @param String $db Database instance name  *  * @return String  */  public static function escape($s,$db='default'){  return Database::instance($db)->escape($s);  }  /**  * Quote Table name  *  * @param string $s table name  * @param String $db Database instance name  *  * @return String  */  public static function quote_table($s,$db='default'){  return Database::instance($db)->quote_table($s);  }  /**  * Quote a database identifier, such as a column name.  *  * $column = DB::quote_identifier($column,'default');  *  * You can also use SQL methods within identifiers.  *  * // The value of "column" will be quoted  * $column = DB::quote_identifier('COUNT("column")');  *  * Objects passed to this function will be converted to strings.  * [Database_Query] objects will be compiled and converted to a sub-query.  * All other objects will be converted using the '__toString' method.  *  * @param mixed $value any identifier  * @param String $db, Database instance  * @return string  */  public static function quote_identifier($value,$db='default'){  return Database::instance($db)->quote_identifier($value);  }  /**  * Get Connection for Database instance  *  * @param String $db Database Instance name  *  * @return Connection of Databse  */  public static function getConnection($db = 'default'){  return Database::instance($db)->getConnection();  }  /**  * Get Children of current record  *  * @param String $table Table name  * @param Bollean $returnSql  * @param Integer $pid Parent Id of table record  * @param String $idname ID column name  * @param String $pidname Parent ID column name  * @param String $db Database Instance name  *  * @return Records of Children  */  public static function getChildren($table,$returnSql = false ,$pid= '0',$idname='id',$pidname='pid' ,$db='default'){  $_sql = 'select * from '.self::quote_table($table,$db).' where '.$pidname.'='.self::quote($pid,$db).  " and $idname <>".self::quote($pid,$db);  if($returnSql){  return $_sql;  }  $_res = self::query(self::SELECT,$_sql,true)->execute($db)->as_array();  if($_res){  return $_res;  }else{  return false;  }  }  /**  * Tree query for connect by,traverse all the child records of Data  *  * @param String $tableName Tablename  * @param Boolean $returnSql Return SQL String if TURE  * @param String $startWith Begin valueof traverse  * @param String $idCol ID Column name  * @param String $pidCol Parent ID Column name  * @param String $orderCol Order Column  * @param Integer $maxDepth Depth of traverse,  * @param Integer $level Start Level  * @param String $delimiter Delimiter of branch  * @param String $db Database configuration instance  *  * @return Record/String Return Record array or String of SQL  */  public static function getTree($tableName,$returnSql=false,$startWith='0',$idCol='id',$pidCol='pid', $orderCol='id', $maxDepth=0,$level = 0,$delimiter = ';',$db='default'){  $_funcParas = array();  $_funcParas[] = self::quote($tableName,$db); //Table|View  $_funcParas[] = self::quote($idCol,$db); //ID column  $_funcParas[] = self::quote($pidCol,$db); //Parent ID Column  $_funcParas[] = self::quote($orderCol,$db); //Default Order by ASC  $_funcParas[] = self::quote($startWith,$db); //Begin ID  $_funcParas[] = self::quote($maxDepth,$db); //Depth of traverse  $_funcParas[] = self::quote($delimiter,$db); //Delimitor of Branch,default ';'  $_sql = 'select * from connectby('  .implode(',',$_funcParas).')'  .' as t(id int, pid int, level int, branch text, pos int)';  if($level > 0){  $_sql .= ' where level >='.self::quote($level,$db);  }  if($returnSql) return $_sql;  $_res = self::query(self::SELECT,$_sql,true)->execute($db)->as_array();  if($_res){  return $_res;  }else{  return false;  }  }  /**  * Start transaction  *  * @param String $db Instance name of DB  *  * @return Result set  */  public static function begin($db='default'){  return DB::query(self::UPDATE, "BEGIN")->execute($db);  }  /**  * Define Savepoint  *  * @param String $savepoint  *  * @param String $db  */  public static function savepoint($savepoint, $db='default'){  return DB::query(self::UPDATE, "SAVEPOINT ".$savepoint)->execute($db);  }  /**  * Rollback to Savepoint  *  * @param String $savepoint  *  * @param String $db Database Instance name  */  public static function rollpoint($savepoint, $db='default'){  return DB::query(self::UPDATE, "ROLLBACK TO ".$savepoint)->execute($db);  }  /**  * Commit an transaction  * @param String DB connection  */  public static function commit($db='default'){  return DB::query(self::UPDATE, "COMMIT")->execute($db);  }  public static function rollback($db='default'){  return DB::query(self::UPDATE, "ROLLBACK")->execute($db);  }  /**  * Create a new [Database_Query] of the given type.  *  * // Create a new SELECT query  * $query = DB::query(self::SELECT, 'SELECT * FROM users');  *  * // Create a new DELETE query  * $query = DB::query(self::DELETE, 'DELETE FROM users WHERE id = 5');  *  * Specifying the type changes the returned result. When using  * self::SELECT, a [Database_Query_Result] will be returned.  * self::INSERT queries will return the insert id and number of rows.  * For all other queries, the number of affected rows is returned.  *  * @param integer type: self::SELECT, self::UPDATE, etc  * @param string SQL statement  * @param Boolean $as_object Return Result set as Object if true, default FALSE  * @param Array $params Query parameters of SQL, default array()  * @param String $stmt_name The query is Prepared Statement if TRUE,  * Execute Prepared Statement when $param is Not NULL  * Prepare Statement when $param is NULL  *  * @return Database_Query  */  public static function query($type, $sql = NULL ,$as_object = false,$params = array(),$stmt_name = NULL)  {  return new Database_Query($type, $sql,$as_object,$params,$stmt_name);  }  /**  * Gettting paginated page from Orignal SQL  *  * @param String $sql SQL query  * @param UTL Object &$page UTL object of tempalte  * @param String $orderBy Order by column, default 'updated desc'  * @param String $dataPro Data Property Name, default 'data'  * @param String $pagePro Pagnation Frament property Name, default 'pagination'  * @param Array $config Pagination Configuration Array overider  * @param String $db Database Instance Name, default 'default'  * @param Boolean $as_object Populate Data as Object if TRUE, default TRUE  * @param String $_paginClass Class Name of pagination  * @return True if success  */  public static function getPage($_sql,&$page,$orderBy ='updated desc', $dataPro='data',$pagePro = 'pagination',  $config = NULL,$db = 'default',$as_object= true,$_paginClass='Pagination'){  $_csql = 'select count(1) as c from ('.$_sql.') st';  $_c = DB::query(self::SELECT,$_csql)->execute($db)->get('c');  if($config){  $config['total_items'] = $_c;  $_pagination = new $_paginClass($config);  }else{  $config = array();  $config['total_items'] = $_c;  $_pagination = new $_paginClass($config);  }  $_sql .= ' order by '.$orderBy;  if($_pagination->offset){  $_sql .= ' offset '.$_pagination->offset;  }  $_sql .= ' limit '.$_pagination->items_per_page;  $_data = DB::query(self::SELECT,$_sql,$as_object)->execute($db)->as_array();  if(!$_data){  $page->{$dataPro} = false;  $page->{$pagePro} = false;  return false;  }  $page->{$dataPro} = $_data;  $page->{$pagePro} = $_pagination;  return true;  }  /**  * Get All roles of subordinate  *  * @param Integer $role_id Integer User Role ID  * @param Boolean $quote Quote the SQL if ture, reutrn orignal SQL if false  * @param String $role_table Table of role hierarchy  * @param Integer $level Start Level of tree traverse  * @param String $db Database Instance name  * @return SQL String  */  public static function getRoleTreeSql($role_id,$quote = false,$role_table,$level=0,$db='default'){  $_sql = 'select id from ('.self::getTree($role_table,true,$role_id,'id','pid','id',  0, //Maxdepth  $level, //Level  ';',$db).') utree';  if(!$quote) return $_sql;  else return '('.$_sql.')';  }  /**  * Getting SQL String to query Objects of subordinate and owned objects  * Child User Role Tree[CURT]  *  * @param integer $role_id Role ID of user  * @param integer $user_id User ID  * @param String $role_table Table of Role  * @param Boolean $quote Quote the SQL if ture, reutrn orignal SQL if false  * @param String $roleCol Role ID column name  * @param String $ownerCol Owner ID column name  * @param String $db Database instance name  * @return SQL String  */  public static function getCURTreeSql($role_id,$user_id,$role_table,$quote = true,  $roleCol='role_id',$ownerCol = 'owner_id' ,$db='default'){  $_sql = ' '.$roleCol.' in '.self::getRoleTreeSql($role_id,true,$role_table,  1, //Level start with 1  $db). ' or '.$ownerCol.'='.self::quote($user_id,$db);  if(!$quote) return $_sql;  else return '('.$_sql.')';  }  /**  * Array from tree query to tree  *  * @param Array $eles , the record set from self::getTree  * @param String $elename, element name of node  * @param String $cldname, Child node name  * @param String $delimiter, The delimiter of branch  *  * @return Object , Tree object of data  */  public static function array2tree($eles,$elename,$cldname,$delimiter=';'){  if($elename == $cldname){  throw new Exception('Ele name equals cldname!');  }  $rtree = array();  foreach ($eles as $ele){  $_branch = $ele->branch;  //Log::debug('branch='.$_branch);  //The depth in the array  $_depths = explode($delimiter,$_branch);  if(count($_depths == 1)){  $_root = $_depths[0];  }  $_cur = &$rtree;  foreach ($_depths as $depth){  //Create NODE  if(!isset($_cur[$cldname])){  $_cur[$cldname] = array();  }  if(!isset($_cur[$cldname][$depth])){  $_cur[$cldname][$depth] = array();  $_cur = &$_cur[$cldname][$depth];  }else{  $_cur = &$_cur[$cldname][$depth];  }  }  $_cur[$elename] = $ele;  }  return $rtree[$cldname][$_root];  }  }