<?php

class xser_dbo_mysql_sqlbuilder extends xser_dbo_sqlbuilder {
	
	private $db_link_identifier ;	
	static function instance($db_link_identifier){
		if (!is_resource($db_link_identifier))
        	throw new Exception('INVALID db_link_identifier');
		
		static $instance;		
        if (is_null($instance))
            $instance = new xser_dbo_mysql_sqlbuilder();
        $instance->db_link_identifier = $db_link_identifier ;
        return $instance;
	}
	
	public function escape_value($value){
		if (is_int($value) || is_float($value)) { return $value; }
        if (is_bool($value)) { return $value ? 1 : 0; }
        if (is_null($value)) { return 'NULL'; }
        return "'" . mysql_real_escape_string($value, $this->db_link_identifier) . "'";
	}
	
	public function sql_limit($length,$offset = null){
		$sql = '' ;
		if ( !empty($offset) ){
			$sql = sprintf(' limit %d , %d',
				(int)$offset ,empty($length) ? (int)$length : 4294967294) ;
		} else if (!empty($length)){
			$sql = " limit " . (int)$length;
		}
        return $sql ;
	}
	
}

// 简单的通用sql生成器
abstract class xser_dbo_sqlbuilder {
 	
 	// insert into table_name (f1,f2[,..]) values(v1,v2[,..])
 	const tpl_sql_insert = 'insert into %s (%s) values(%s)' ;
 	
 	// select f1,f2[,..] from table_name [sql_conditions][sql_sort] [sql_limit] 
 	const tpl_sql_select = 'select %s from %s %s %s %s' ;

 	// update table_name set pair1,pair2 [sql_conditions]
 	const tpl_sql_update = 'update %s set %s %s' ;
 	
 	protected $meta_table = array();
 	
 	public abstract function escape_value($value);
 	public abstract function sql_limit($length,$offset = null);
 	
 	/**
 	 * 重新初始化表元信息
 	 * @param array $meta_table 表元信息
 	 * @return xser_dbo_sqlbuilder
 	 */
 	public function table($meta_table){
 		$this->meta_table = $meta_table ;return $this ;
 	}
 	
 	public function sql_insert($record){
 		if (xser::varify_provider()->not_null_array($record)){			
 			$_row = array_map(array(& $this,'escape_value'),$record);
 			$columns = implode(',',array_keys($_row)) ;
 			$values = implode(',',array_values($_row)) ;
 			return sprintf(self::tpl_sql_insert,
 				$this->meta_table['table'],$columns,$values);
 		}
 		return '' ;
 	}
 	
 	public function sql_update($record,$conditions=null){
 		if (!empty($record) && is_array($record)){
 			$_row = array_map(array(& $this,'escape_value'),$record);
 			$sql_conditions = empty($conditions)? '' : $this->sql_conditions($conditions);
			$pairs = array();
 			foreach ($_row as $field=>$val){
 				$pairs[] = "{$field}={$val}" ;
 			}
 			$pairs = implode(',',$pairs) ;
 			return sprintf(self::tpl_sql_update,
 				$this->meta_table['table'],$pairs,$sql_conditions);
 		}
 		return '' ;
 	}
 	
 	public function sql_select($conditions = null, $sort = null, $limit = null, $fields = '*'){
 		// 处理排序
        $sql_sort = $sort != '' ? " order by {$sort}" : '';
 		if (is_array($limit)) {
            list($length, $offset) = $limit;
        } else {
            $length = $limit;$offset = null;
        }
        
        $sql_limit = empty($length)? '' : $this->sql_limit($length,$offset);
        $sql_conditions = empty($conditions)? '' : $this->sql_conditions($conditions);
 		
 		return sprintf(self::tpl_sql_select,
 			$fields ,$this->meta_table['table'] ,$sql_conditions,$sql_sort,$sql_limit );
 	}
 	
 	public function sql_conditions($conditions = null){
 		if (empty($conditions)) return '' ;
 		$sql = ' where ';
 		if (is_string($conditions) )
 			return $sql . $conditions ;
 		else if (is_array($conditions)){
 			$join_char = '' ;// 第一个条件前面 没有 and 连接符 			
 			foreach ($conditions as $field=>$cond){
 				// 支持 like / or 等操作 例如: 'name' => array('%Bob%','like') 				
 				$op_char = '=' ;
 				if (is_array($cond)){
 					$value = array_shift($cond);
 					// if $value is array , will use "in" [] opchar 					
 					if (is_array($value)){
 						$value = array_map(array(& $this,'escape_value'),$value);
 						$value = '[' . implode(',',$value) . ']' ;
 					}
 					
 					$_op_char = array_shift($cond);
 					if (!empty($_op_char) && is_string($_op_char))
 						$op_char = $_op_char ;
 						 					
 				}else {
 					$value = $cond ; 
 				}
 				// 过滤值	
 				$value = $this->escape_value($value);
 					
 				$sql .= "{$join_char} {$field} {$op_char} {$value} " ; 				
 				$join_char = ' and ' ;
 			}
 			return $sql ;
 		}
 		return '' ;
 	}
 	
 }	

demo如下:

<?php
class xser_rbac_users {

	// 用户表定义参数
	protected $table = 'users' ;
    protected $pk = 'user_id' ;
    protected $username_field = 'username' ;
    protected $email_field = 'email' ;
    protected $password_field = 'password' ;
    protected $roles_field = 'roles' ;
    
    protected $meta_table = null ;
    
    /**
	 * @return xser_dbo_sqlbuilder
	 */
    protected function sqlbuilder(){
    	if (empty($this->meta_table))
    		$this->meta_table = array(
    			'table' => $this->table ,
    			'pk' => $this->pk ,
    			'fields' => xser::dbo_provider()->table_columns($this->table)
    		);
    	return xser::dbo_provider()->sqlbuilder()->table($this->meta_table);
    } 
    
    
    
	public function __construct(){
//		xser::dbo_provider()->open_connect();
//		xser::register_halt_callback(array($this->dbo,'close_connect'));
	}
	
	// find
	public function find_by_userid($pk, $fields = '*'){
		$sql = $this->sqlbuilder()->sql_select(array($this->pk=>$pk),null,1,$fields);		
		return xser::dbo_provider()->find_one($sql,$msg);
	}
	
	public function find_by_username($username, $fields = '*'){
		$sql = $this->sqlbuilder()->sql_select(array($this->username_field=>$username),null,1,$fields);		
		return xser::dbo_provider()->find_one($sql,$msg);
	}
	
	public function find_by_email($email, $fields = '*'){
		$sql = $this->sqlbuilder()->sql_select(array($this->email_field=>$email),null,1,$fields);		
		return xser::dbo_provider()->find_one($sql,$msg);
	}
	
	// exists
	public function userid_exists($pk){
		$sql = $this->sqlbuilder()->sql_select(array($this->pk=>$pk),null,null,'count(*)');		
		$data = xser::dbo_provider()->find_one($sql,$msg);
		return ( (int) array_pop($data) ) > 0 ;
	}
	
	public function username_exists($username){
		$sql = $this->sqlbuilder()->sql_select(array($this->username_field=>$username),null,null,'count(*)');		
		$data = xser::dbo_provider()->find_one($sql,$msg);
		return ( (int) array_pop($data) ) > 0 ;
	}
	
	public function email_exists($email){
		$sql = $this->sqlbuilder()->sql_select(array($this->email_field=>$email),null,null,'count(*)');		
		$data = xser::dbo_provider()->find_one($sql,$msg);
		return ( (int) array_pop($data) ) > 0 ;
	}
	
	// others
	public function change_password($username, $old, $new){
		$user = $this->find_by_username($username,"{$this->pk},{$this->password_field}");
		if (!$user) return false;
		
		if (!$this->check_password($old, $user[$this->password_field])) {
            return false;
        }
        $sql = $this->sqlbuilder()
        	->sql_update( 
        		array($this->password_field => $new) ,array( $this->pk => $user[$this->pk]));

        
	}
	
}