xiaoben-as2-php/protected/components/Db.php

<?php
/**
 * @author: 郭永磊 
 * @function description: 数据库接口
 * @create date: 2015-8-13
 */
 
class Db{
	private $_conn;
	private $_defaultServer;
	private $_defaultDb;
	private $_useDefaultServer = false;
	private $_condition;
	
	public $server;
	public $db;
	public $dbTable = array();
	public $mappingName = "default"; //默认映射

	function __construct(){
		$this->_defaultServer = Yii::app()->params["default_server"];
		$this->_defaultDb = Yii::app()->params["default_db"];
		
		$this->_resetServer();
		
		$this->dbTable["default"] = " school_id = ".$this->_getSchoolId();
		
		$params = func_get_args();
		if($params){
			if(!empty($params[0]) && isset($params[1])){
				$this->db["name"] = $params[0];
				$this->_useDefaultServer = true;
				
				if(!empty($params[1]))
					$this->db["t_pre"] = $params[1];
			}
			else if(!empty($params[0]))
				$this->mappingName = $params[0];
		}
		else if($this->_getSchoolId() === false){
			$this->_useDefaultServer = true;
		}
		
		$this->_condition = $this->dbTable[$this->mappingName];
	}
	
	private function _getSchoolId(){
		return !empty(Yii::app()->session["school_id"]) ? Yii::app()->session["school_id"] : false;
	}
	
	//重置数据库服务器信息
	private function _resetServer(){
		$this->server = $this->_defaultServer;
		$this->db = $this->_defaultDb;
	}
	
	//连接任意服务器
	private function _connect(){
		$dsn = "mysql:host={$this->server["addr"]};dbname={$this->db["name"]}";
		$this->_conn = new CDbConnection($dsn, $this->server["username"], $this->server["password"]);
		$this->_conn->tablePrefix = $this->db["t_pre"];
		$this->_conn->active = true;
	}
	
	//连接到从数据库读取的目标服务器
	private function _connectTarget(){
		$this->_resetServer();
		$this->_connect();
		$sql = "select * from `{$this->_defaultDb["t_pre"]}{$this->_defaultDb["t_servers"]}` 
			where{$this->_condition}";
		$rs = current($this->_query($sql));
		
		if($rs){
			$this->server = array(
				"addr" => $rs["database_host"],
				"username" => $rs["database_user"],
				"password" => $rs["database_password"],
			);
			$this->db = array(
				"name" => $rs["database_name"],
				"t_pre" => isset($rs["t_pre"]) ? $rs["t_pre"] : "",
			);
			
			$this->_connect();
		}
		else
			$this->_conn->active = false;
	}
	
	//无差别数据库语句执行器
	private function _query($sql){
		if(is_object($this->_conn)){
			$sql_type = $this->getSqlType($sql);
			$commander = $this->_conn->createCommand($sql);
			return ($sql_type === "select" || $sql_type === "show") ? $commander->queryAll() : $commander->execute();
		}
		return false;
	}
	
	private function _connTrigger(){
		if(!is_object($this->_conn))
			$this->connect();
	}
	
	public function connect(){
		if($this->_useDefaultServer){
			$this->_connect();
		}
		else
			$this->_connectTarget();
		
		return $this->_conn;
	}
	
	//判断SQL语句类型
	public function getSqlType($sql){
		$arr = explode(" ", trim(str_replace("　", " ", $sql)), 2);
		return strtolower($arr[0]);
	}
	
	public function doQuery($sql){
		$this->_connTrigger();
		
		return $this->_query($sql);
	}
	
	public function escape($value)
	{
		return str_replace(array("\\", "\0", "\n", "\r", "\x1a", "'", '"'), array("\\\\", "\\0", "\\n", "\\r", "\Z", "\'", '\"'), $value);
	}
	
	public function createQuery($query = null){
		$this->_connTrigger();
	
		return $this->_conn->createCommand($query);
	}
	
	public function UUID($replace = FALSE)
	{
		$this->_connTrigger();
		$query  = $this->_conn->createCommand("SELECT UUID() AS UUID")->query()->read();
		$result = $query['UUID'];
	
		if ($replace)
		{
			$result = str_replace('-', '', $result);
		}
	
		return $result;
	}
	
	public function UUID_SHORT($length = 20)
	{
		$this->_connTrigger();
		$query  = $this->createQuery("SELECT UUID_SHORT() AS UUID_SHORT")->query()->read();
		$result = $query['UUID_SHORT'];
	
		if ($length AND is_int($length))
		{
			$result = substr($result, ($length * -1));
		}
	
		return $result;
	}
	
	public function beginTransaction()
	{
		$this->_connTrigger();
		
		return $this->_conn->beginTransaction();
	}
	
	public function insert($table, $data)
	{
		if ( ! empty($table) AND ! empty($data) AND is_array($data))
		{
			$sql = "INSERT INTO ". $table ." SET ";
			
			foreach ($data AS $field => $value)
			{
				$sql .= "`". $field ."` = '". $value ."', ";
			}
			
			$sql = substr($sql, 0, -2);
			
			$this->createQuery($sql)->query();
		}
	}
	
	public function update($table, $data, $condition = array())
	{
		if ( ! empty($table) AND ! empty($data) AND is_array($data))
		{
			$sql = "UPDATE ". $table ." SET ";
			
			foreach ($data AS $field => $value)
			{
				$sql .= "`". $field ."` = '". $value ."', ";
			}
				
			$sql = substr($sql, 0, -2);
			
			if ( ! empty($condition))
			{
				if (is_array($condition))
				{
					$sql .= " WHERE ";
					
					foreach ($condition AS $field => $value)
					{
						$sql .= "`". $field ."` = '". $value ."' AND ";
					}
					
					$sql = substr($sql, 0, -5);
				}
				else
				{
					$sql .= " WHERE ". $condition;
				}
			}
			
			$this->createQuery($sql)->query();
		}
	}
	
	public function getTotals($table, $condition)
	{
		if ( ! empty($table))
		{
			$sql = "SELECT COUNT(0) AS COUNT FROM ". $table ." ";
			
			if ( ! empty($condition))
			{
				if (is_array($condition))
				{
					$sql .= " WHERE ";
						
					foreach ($condition AS $field => $value)
					{
						$sql .= "`". $field ."` = '". $value ."' AND ";
					}
						
					$sql = substr($sql, 0, -5);
				}
				else
				{
					$sql .= " WHERE ". $condition;
				}
			}
			
			$result = $this->createQuery($sql)->query()->read();
			
			return (int)$result['COUNT'];
		}
		else
		{
			return FALSE;
		}
	}
}