Postgresql DB的访问类

代码不是用来直接使用, 只是提供一个思路. 对PG的各种特性, 包括不限于 树子查询, prepared statements, batch insert的各种支持:

代码经过了相当长时间的不断修正, 最终定稿, 将来相当长的时间内, 都不会去修改这个玩意了.

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

}