Postgresql DB的存取類

程式碼不是用來直接使用, 只是提供一個思路. 對PG的各種特性, 包括不限於 樹子查詢, prepared statements, batch insert的各種支援:

程式碼經過了相當長時間的不斷修正, 最終定稿, 將來相當長的時間內, 都不會去修改這個玩意了.

/**
 * 通用資料庫存取類，所有資料庫存取的入口
 * 僅支援 PG -- 201210
 *
 * @author Anthony
 * 2010-2012 保留
*/

class DB {

 // 查詢型別
 const SELECT = 1;
 const INSERT = 2;
 const UPDATE = 3;
 const DELETE = 4;
 / **
 * 真實值
*/
 const T = 't';
 /**
 * 錯誤值
*/
 const F = 'f';
 /**
 * 空值
*/
 const N = 'N/A'; //NULL 值


 /**
 * 指定值；
 * 'f' 為False，'t' 為TRUE，'N/A' 為NULL 值
 *
 * @param String $s，原始值
 *
 * @return 字串，指定值
*/
 public static functionspecializeValue($s){
 if($s === self::N){
 return NULL;
 }

 if($s === self::T){
 return True;
 }

 if($s == = self::F){
 return False;
 }
 return $s;
 }



 }



 /**
 * 批次插入表
 * @param String $table_name 表名
 * @param Array $cols 表的欄位
 * @param Array $values，資料值陣列
 * @ param String /Array $return_cols 回傳列[s]，預設回傳'id'，多列數組
 * @param String $db DB Connection 實例名稱
 *
 * @return 結果集傳回return_cols
 的結果集*/
 公共靜態函數insert_batch($table_name,$cols,$values,$return_cols='id',$db='default'){
 $_sql = '插入'. self::quote_table($table_name,$ db).'('.self::quote_column($cols,$db).') 值';
 $_vsql = array();
 foreach ($values as $value){
 $ _vsql[] = '('.self::quote($value).')';
 }

 $_sql .= implode(',', $_vsql);

 $_sql .= ' 回傳'.self::quote_column($return_cols);

 return self::query(self::SELECT,$_sql)->execute ($db)->as_array ();
 }

 /**
 * 從Array Data插入表，回傳列[s]，預設回傳ID
 *
 * @param String $table_name 表名
 * @param Array $data Array Data Of鍵值對。
 * @param String/Array $return_cols 回傳列[s]，預設回傳'id'，多列的陣列
 * @param String $db DB Connection 實例名稱
 *
 * @return Boolean/Resultset 如果成功但沒有回傳列則為True，如果失敗則為False，如果存在return_cols，則為列[s] 的值。
*/
 公用靜態函數insert_table($table_name,$data,$return_cols='id',$ db='default'){
 if (!is_array($data)){
 return false;
 }

 if (is_null($return_cols)){
 $sql_ = '插入'.self ::quote_table($table_name,$db).'('.self::quote_column(array_keys($data),$db).') 值('.
 self::quote( array_values($data), $db).')';
 return self::query(self::INSERT,$_sql)->execute($db);
 }

 / /特殊化值
 $data = array_map('self::specializeValue',$data);

 if (is_string($return_cols)){
 $_sql = '插入'.self: :quote_table($table_name ,$db).'('.self::quote_column(array_keys($data),$db).') 值('.
 self::quote(array_values($data),$ db).') '." 回傳".$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 = '插入'. self::quote_table($table_name,$db).'('.self::quote_column(array_keys($data),$db).') 值('.
 self::quote(array_values($ data) ,$db).')'." 回傳".$ids;
 $r_ids = self::query(self::SELECT,$_sql)->execute($db)->current();
 return $r_ids;
 }
 }

 return false;
 }


 /**
 * 更新表數據，並與參考資料進行比較
 *
 * @param String $table_name 表名
 * @param Integer $id 數據ID
 * @param Array $data Array鍵值對的資料。
 * @param Array $refdata 參考資料
 * @param String $id_name ID 的列名
 * @param String $db DB Connection 的實例名稱
 *
 * @return 受影響的整數行，如果失敗則回傳False！
*/ publicstatic function update_data($table_name,$id,$data,$refdata,$id_name='id',$db='default'){
 if (!is_array($data)){
 拋出新異常( '資料應該是col=>val 對數組');
 }
 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{ 拋出新的例外('引用資料型別錯誤'); } }

 // 特化值
 $data = array_map('self::specializeValue',$data);

 if(count($data)>0){
 return self::update_table($table_name,$id,$data,'id',$db);
 }else{
 return 0;
 }
 }

 / **
 * 使用資料更新表，而不檢查引用的資料
 *
 * @param String $table_name 表名稱
 * @param Integer $id 資料ID
 * @param Array $data Array鍵值對的資料。
 * @param String $id_name ID
 的列名* @param String $db 資料庫連線的實例名稱
 *
 * @return Integer 受影響的行，如果失敗則為False !
*/
 公共靜態函數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).' 設定'.self::quote_assoicate($data,' =',',',$db)。 '其中'.
 self::quote_column($id_name,$db).'='.self::quote($id,$db);
 return self::query(self::UPDATE,$_sql )->execute($db);
 }

 /**
 * 引用col 的鍵值對=>值
 *
 * @param Array $data, col=>值對
 * @param String $concat，預設'='
 * @param 字串分隔符，預設','
 * @param String 資料庫實例
 *
 * @return String
*/
 公共靜態函數quote_assoicate($data,>*/
 公共靜態函數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;
 }

 /**
 * 引用 cols
 *
 * @param String $value，列名稱
 * @param String $db，資料庫實例名稱
*/
 公共靜態函式$db='default'){
 if(!is_array($value)){
 return self::quote_identifier($value,$db);
 }else{ //quote_column 記憶體並內爆
 $_qs = array();
 foreach ($value as $ele){
 $_qs[] = self::quote_column($ele,$db);
 }

 $_quote_column_String = implode(',', $_qs);
 return $_quote_column_String;
 }
 }
 /**
 * 引用要轉義的值
 *
 * @param Scalar/Array $value
 *
 * @return 引用字串或陣列
*/
 * DB 的轉義字串
 *
 * @param string $s 表名
 * @param String $db 資料庫實例名稱
 *
 * @return String
 公用函數value,$db='default'){
 if(!is_array($value)){
 return Database::instance($db)->quote($value);
 }else{ / /引用數組並內爆
 $_qs = array();
 foreach ($value as $ele){
 $_qs[] = self::quote($ele,$db);
 }

 $_quoteString = implode(',',$_qs);
 return $_quoteString;
 }
 }

 /**
 * Quote 表名稱
 *
 * @param string $s 表名稱
 * @param String $db 資料庫實例名稱
 *
 * @return String
*/🎝>*/
 public static function escape($s, $db='default'){
 return Database::instance($db)->escape($s);
 }

 / **
 * 引用資料庫標識符，例如列名。
 *
 * $column = DB::quote_identifier($column,'default');
 *
 * 您也可以在識別碼中使用 SQL 方法。
 *
 * / / "column" 的值將被引用
 * $column = DB::quote_identifier('COUNT("column")');
 *
 * 傳遞給此函數的物件將轉換為字串.
 * [Database_Query] 物件將被編譯並轉換為子查詢。
 * 所有其他物件將使用 '__toString' 方法進行轉換。
 *
 * @param mix $value任何識別碼
 * @param String $db，資料庫實例
 * @return string
*/
 public static function quote_table($s,$db='default'){
 return Database::instance($db)->quote_table($s);
 }

 /**
 * 取得資料庫執行個體的連線
 *
 * @param String $db 資料庫執行個體名稱
 *
 * @return 資料庫連線
*/
 public static function quote_identifier($value,$db='default'){
 return Database::instance($db)->quote_identifier($ value);
 }

 /***/
 public static function getConnection($db = 'default'){
 return Database::instance($db)- >getConnection();
 }

 /**
 * 取得目前記錄的子項
 *
 * @param String $table 表名稱
 * @param Bollean $returnSql
 * @param Integer $pid 表記錄的父ID
 * @param String $idname ID 欄位名稱
 * @param String $pidname 父級ID 欄位名稱
 * @param String $db 資料庫實例名稱
 *
 * @return 子級記錄
*/

 公共靜態函數getChildren($table,$returnSql = false ,$pid= '0',$idname='id',$pidname= 'pid' ,$db='default'){
 $_sql = '從'.self::quote_table($table,$db) 選擇*。 '其中'.$pidname.'='.self::quote($pid,$db).
 " 和$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;
 }
 }

 /**
 * 連接方式的樹形查詢，遍歷Data 的所有子記錄
 *
 * @param String $tableName 表名
 * @param Boolean $returnSql 如果TURE
 傳回SQL 字串* @ param String $startWith 遍歷開始值
 * @param String $idCol ID 欄位名稱
 * @param String $pidCol 父親ID 欄位名稱
 * @param String $orderCol 訂單列
 * @ param Integer $maxDepth 遍歷深度，
 * @param Integer $level 起始等級
 * @param String $delimiter 分支分隔符號
 * @param String $db 資料庫設定實例
 *
 *
 *
 * @傳回記錄/字串傳回SQL 的記錄數組或字串
*/
 公共靜態函數getTree($tableName,$returnSql=false,$startWith='0',$idCol='id',$pidCol='pid', $orderCol=' id', $maxDepth= 0,$level = 0,$delimiter = ';',$db='default'){
 $_funcParas = array();
 $_funcParas[] = self::quote ($表名,$ db); //表格|檢視
 $_funcParas[] = self::quote($idCol,$db); //ID欄位
 $_funcParas[] = self::quote($pidCol,$db ); //父ID欄位
 $_funcParas[] = self::quote($orderCol,$db); //預設順序ASC
 $_funcParas[] = self::quote($startWith,$db ); //開始ID
 $_funcParas[] = self::quote($maxDepth,$db); //遍歷深度
 $_funcParas[] = self::quote($delimiter,$db); //分支分隔符，預設';'

 $_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;
 }
 }









 /**
 * 開始交易
 *
 * @param String $db DB 實例名稱
 *
 * @return 結果集
*/
 public static function begin($db='default'){
 return DB::query(self::UPDATE, "BEGIN")-> ;execute($db) ;
 }

 /**
 * 定義保存點
 *
 * @param String $savepoint
 *
 * @param String $db
*/
 公共靜態函數savepoint($savepoint, $db='default'){
 回傳DB: :query(self:: UPDATE, "SAVEPOINT ".$savepoint)->execute($db);
 }

 /**
 * 回滾到保存點
 *
 * @param String $savepoint
 *
 * @param String $db 資料庫實例名稱
*/

 public static function rollpoint($saveave, $ db='default'){
 return DB::query(self::UPDATE, "ROLLBACK TO ".$savepoint)->execute($db);
 }

 /**
 * 提交交易 * @param String 資料庫連線*/ 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") ->執行($db); }


 /**
 * 建立給定類型的新 [Database_Query]。
 *
 * // 建立新的SELECT 查詢
 * $query = DB::query(self::SELECT, 'SELECT * FROM users');
 *
 * //建立一個新的DELETE 查詢
 * $query = DB::query(self::DELETE, 'DELETE FROM users WHERE id = 5');
 *
 * 指定類型會改變傳回的結果。當使用
 * self::SELECT 時，將會傳回 [Database_Query_Result]。
 * self::INSERT 查詢將傳回插入 id 和行數。
 * 對於所有其他查詢，將傳回插入的行數。傳回受影響的行。
 *
 * @param 整數類型：self::SELECT、self::UPDATE 等
 * @param string SQL 語句
 * @param Boolean $as_object 傳回結果集as Object if true, 預設FALSE
 * @param Array $params SQL 的查詢參數，默認array()
 * @param String $stmt_name 查詢為Prepared Statement if TRUE,
 * 執行Prepared Statement when $param is Not NULL
 * 當$param 為NULL 時準備語句
 *
 * @return Database_Query
*/
 公共靜態函數查詢($type, $sql = NULL ,$as_object = false,$params = array(),$stmt_name = NULL)
 {
 return new Database_Query($type, $sql,$as_object,$params,$stmt_name);
 }


 /**
 * 從原始SQL 取得分頁頁面
 *
 * @param String $sql SQL 查詢
 * @param UTL 物件&$page UTL 範本物件
 * @param String $orderBy Order按列，預設'updated desc'
 * @param String $dataPro 資料屬性名稱，預設'data'
 * @param String $pagePro Pagnation Frament 屬性名稱，預設'pagination'
 * @param Array $ config 分頁配置數組overider
 * @param String $db 資料庫實例名稱，預設'default'
 * @param Boolean $as_object 如果為TRUE，則將資料填入對象，預設為TRUE
 * @ param String $_paginClass分頁的類別名稱
 * @return 如果成功則回傳True
*/
 * 取得下屬的所有角色
 *
 * @param Integer $role_id Integer 使用者角色ID
 * @param Boolean $quote 如果為true則引用SQL，如果為false則傳回原SQL
 * @param String $role_table 角色層級表
 * @param Integer $level 樹遍歷的起始層級
 * @param String $db 資料庫實例名稱
 * @return SQL String
*/
 public static function getPage($_sql,&$page,$orderBy ='更新的desc', $dataPro='data',$pagePro = '分頁',
 $config = NULL,$db = 'default' , $as_object= true,$_paginClass='分頁'){

 $_csql = '從('.$_sql.') st 選擇count(1) as c';
 $_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;
 回傳false;
 }

 $page->{$dataPro} = $_data;
 $page->{$pagePro} = $_pagination;
 回傳 true;
 }

 /**
 * 取得SQL 字串來查詢從屬物件和擁有物件的物件
 * 子使用者角色樹[CURT]
 *
 * @param integer $role_id 使用者的角色ID
 * @ param integer $user_id 使用者ID
 * @param String $role_table 角色表
 * @param Boolean $quote 如果為ture，則引用SQL，如果為false，則傳回原始SQL
 * @param String $ roleCol 角色ID 欄位名稱
 * @param String $ownerCol 擁有者ID 欄位名稱
 * @param String $db 資料庫執行個體名稱
 * @return SQL String
*/

 公共靜態函數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, //最大深度
 $level, / /Level
 ';',$db).') utree';
 if(!$quote) return $_sql;
 else return '('.$_sql.')';
 }

 /***/
 公用靜態函式getCURTreeSql($role_id,$user_id,$role_table,$quote = true,
 $roleCol='role_id',$owner =id',$owner = 'owner_id' ,$db='default'){
 $_sql = ' '.$roleCol.'在'.self::getRoleTreeSql($role_id,true,$role_table,
 1, //等級從1 開始
 $db)。 '('.$_sql.') ';
 }


 /**
 * 樹查詢到樹
的陣列*
 * @param Array $eles ，來自self::getTree
 的記錄集* @param String $elename，節點
 的元素名稱* @param String $cldname, 子節點名稱
 * @param String $delimiter, 分支分隔符號
 *
 * @return Object , 資料的Tree物件
*/
 public static function array2tree($eles,$elename,$cldname,$delimiter=';'){
 if($elename = = $cldname){
 throw new Exception('Ele 名稱等於cldname!');
 }
 $rtree = array();
 foreach ($eles as $ele){
 $_branch = $ele->branch;
 //Log::debug('branch='.$_branch);
 //陣列中的深度
 $_depths =explode($delimiter, $_branch );
 if(count($_depths == 1)){
 $_root = $_depths[0];
 }
 $_cur = &$rtree;
 foreach ( $_深度為$深度){
 //建立NODE
 if(!isset($_cur[$cldname])){
 $_cur[$cldname] = array();
 }

 if(!isset($_cur[$cldname][$深度])){
 $_cur[$cldname][$深度] = array();
 $_cur = &$_cur [ $cldname][$深度];
 }else{
 $_cur = &$_cur[$cldname][$深度];
 }
 }
 $_cur[$elename] = $ele;
 }
 return $rtree[$cldname][$_root];
 }

}