php的Postgresql DB的访问类

代码不是用来直接使用, 只是提供一个思路. 对PG的各种特性, 包括不限于 树子查询, prepared statements, batch insert的各种支持:代码经过了相当长时间的不断修正, 最终定稿, 将来相当长的时间内, 都不会去修改这个玩意了.

[PHP]代码

<?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];
    }

}