[置顶] xser php5 framework v0.11 测试版 新增的功能 -- sqlbuilder

<?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_table {
 // 表
 protected $table = null ;
    protected $pk = null ;
    
    //当前表为主表,及当前表主键在子表中的外键字段
    protected $link = null ;
    protected $link_fk = null ;
    
    //当前表为子表,及当前表主键在主表中的外键字段
    protected $belong = null ;
    protected $belong_fk = null ;
    
    //多对多
    protected $more_to_more = null ;
    
 protected $meta_table = null ;
 
 function create($row,$msg=null,$affected_rows=0){
  $this->_before_create($row);
  $sql = $this->sqlbuilder()->sql_insert($row);
  xser::dbo_provider()->execute($sql,$msg,$affected_rows);
        return $affected_rows > 0 ;
 }
 
 function update($row,$conditions=null,$msg=null,$affected_rows=0){
  $sql = $this->sqlbuilder()->sql_update($row,$conditions);
  xser::dbo_provider()->execute($sql,$msg,$affected_rows);
  return $affected_rows > 0 ;
 }
 
 function find_one($conditions=null, $fields = '*',$msg=null){
  $sql = $this->sqlbuilder()->sql_select($conditions,null,1,$fields);  
  return xser::dbo_provider()->find_one($sql,$msg);
 }
 
 // 1:more , 仅仅支持单主键
 function more_links($current,$fields = '*',$msg=null){
  $sql = "select {$fields} from {$this->link}" ;
  $conditions = array($this->link_fk => $current[$this->pk]);
  $sql .= $this->sqlbuilder()->sql_conditions($conditions);  
  return xser::dbo_provider()->find_all($sql,$msg);
 }
 
 // 1:one , 仅仅支持单主键
 function one_link($current,$fields = '*',$msg=null){
  $sql = "select {$fields} from {$this->link}" ;
  $conditions = array($this->link_fk => $current[$this->pk]);
  $sql .= $this->sqlbuilder()->sql_conditions($conditions);  
  return xser::dbo_provider()->find_one($sql,$msg);
 }
 
 // 定位,当前对象从属于哪个对象 
 function belongs_to($current,$fields = '*',$msg=null){
  $sql = "select {$fields} from {$this->belong}" ;
  $conditions = array($this->belong_fk => $current[$this->pk]);
  $sql .= $this->sqlbuilder()->sql_conditions($conditions);  
  return xser::dbo_provider()->find_one($sql,$msg);
 }
 
 function find_more($conditions=null, $fields = '*',$msg=null){
  $sql = $this->sqlbuilder()->sql_select($conditions,null,null,$fields);  
  return xser::dbo_provider()->find_all($sql,$msg);
 }
 
 function _before_create(&$row){}
 
 /**
  * @return xser_dbo_sqlbuilder
  */
    protected function sqlbuilder($table){
     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);
    }
}