Access classes for Postgresql DB

The code is not for direct use, but just to provide an idea. Various support for various features of PG, including but not limited to tree subqueries, prepared statements, and batch insert:

The code has been continuously revised for a long time and has been finalized. This thing will not be modified for a long time in the future.

/**
 * 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];
	}

}