PHP的PostgreSQL数据库访问类

<?php defined('SYSPATH') or die('No direct script access.');  /**   * 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];   }    }