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