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


}</pre>