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