xyzshop/core/framework/db/mysqli.php

<?php
/**
 * mysqli驱动
 *
 *
 * @package    
 */


defined('InShopNC') or exit('Access Invalid!');

require_once(BASE_CORE_PATH . '/framework/function/statistic_tm.php');

class Db
{
    const ErrUnConnect = 2006;
    const ErrLock = 1205;

	private static $link = array();
	private static $ifTransacting = false;

	private function __construct()
	{
		if (!extension_loaded('mysqli')) {
			throw_exception("Db Error: mysqli is not install");
		}
		else {
            self::init_link();
        }
	}

	private static function init_link()
    {
        if(!isset(self::$link['master']) || (is_object(self::$link['master']) == false)) {
            self::connect('master');
        }
        if(!isset(self::$link['slave']) || (is_object(self::$link['slave']) == false)) {
            self::connect('slave');
        }
    }

	private static function connect($host = 'slave')
	{
        self::closeLink($host);

        $conf = C('db.'.$host);
        self::$link[$host] = @new mysqli($conf['dbhost'], $conf['dbuser'], $conf['dbpwd'], $conf['dbname'], $conf['dbport']);
		if (mysqli_connect_errno()) {
		    $err_no = mysqli_connect_errno();
		    $error  = mysqli_connect_error();
		    throw_exception("Db Error: database connect failed errno={$err_no} err={$error}");
        }

        switch (strtoupper($conf['dbcharset']))
		{
			case 'UTF-8':
				$query_string = "
		                 SET CHARACTER_SET_CLIENT = utf8mb4,
		                 CHARACTER_SET_CONNECTION = utf8mb4,
		                 CHARACTER_SET_DATABASE = utf8mb4,
		                 CHARACTER_SET_RESULTS = utf8mb4,
		                 CHARACTER_SET_SERVER = utf8mb4,
		                 COLLATION_CONNECTION = utf8mb4_unicode_ci,
		                 COLLATION_DATABASE = utf8mb4_unicode_ci,
		                 COLLATION_SERVER = utf8mb4_unicode_ci,
		                 sql_mode=''";
//                $query_string = "
//		                 SET CHARACTER_SET_CLIENT = utf8,
//		                 CHARACTER_SET_CONNECTION = utf8,
//		                 CHARACTER_SET_DATABASE = utf8,
//		                 CHARACTER_SET_RESULTS = utf8,
//		                 CHARACTER_SET_SERVER = utf8,
//		                 COLLATION_CONNECTION = utf8_general_ci,
//		                 COLLATION_DATABASE = utf8_general_ci,
//		                 COLLATION_SERVER = utf8_general_ci,
//		                 sql_mode=''";
				break;
			case 'GBK':
				$query_string = "
		   			    SET CHARACTER_SET_CLIENT = gbk,
		                 CHARACTER_SET_CONNECTION = gbk,
		                 CHARACTER_SET_DATABASE = gbk,
		                 CHARACTER_SET_RESULTS = gbk,
		                 CHARACTER_SET_SERVER = gbk,
		                 COLLATION_CONNECTION = gbk_chinese_ci,
		                 COLLATION_DATABASE = gbk_chinese_ci,
		                 COLLATION_SERVER = gbk_chinese_ci,
		                 sql_mode=''";
				break;
			default:
				$error = "Db Error: charset is Invalid";
				throw_exception($error);
		}

		//进行编码声明
		if (!self::$link[$host]->query($query_string)) {
			throw_exception("Db Error: ".mysqli_error(self::$link[$host]));
		}
	}

	private static function closeLink($host)
	{
		if(isset(self::$link[$host]) && is_object(self::$link[$host])) {
			self::$link[$host]->close();
		}
        self::$link[$host] = null;
    }

    public static function ping($host = 'master')
    {
        if (is_object(self::$link[$host])) {
            return self::$link[$host]->ping();
        }
    }

	/**
	 * 执行查询
	 *
	 * @param string $sql
	 * @return mixed
	 */
	public static function query($sql, $host = 'master')
	{
	    self::init_link();

	    $count = 0;
	    do
        {
            $staer = new statistic_tm();
            $query = self::$link[$host]->query($sql);
            $staer->end();

            if ($query === false)
            {
                $eno  = mysqli_errno(self::$link[$host]);
                $emsg = mysqli_error(self::$link[$host]);
                $error = "Db Error eno={$eno} msg={$emsg}";
                Log::record("{$error} \r\n sql={$sql}",Log::ERR);

                if(self::$ifTransacting) {
                    throw_exception($error);
                }
                else
                {
                    if($eno == self::ErrUnConnect)
                    {
                        if($count > 0) return false;
                        self::connect($host);
                        $count++;
                    }
                    else
                    {
                        if (C('debug')) {
                            throw_exception($error.'<br/>'.$sql);
                            return false;
                        } else {
                            return false;
                        }
                    }
                }
            }
            else
            {
                Log::record($sql . " [ RunTime:" . $staer->elapsed(6) . "s ]", Log::SQL);
                return $query;
            }
        }
        while(true);
	}

	/**
	 * 取得数组
	 *
	 * @param string $sql
	 * @return bool/null/array
	 */
	public static function getAll($sql, $host = 'slave')
    {
        $result = self::query($sql, $host);
        if ($result === false) return array();
		$array = array();
		while ($tmp=mysqli_fetch_array($result,MYSQLI_ASSOC)){
			$array[] = $tmp;
		}
		return !empty($array) ? $array : null;
	}

    /**
     * SELECT查询
     *
     * @param array $param 参数
     * @param object $obj_page 分类对象
     * @return array
     */
	public static function select($param, $obj_page='', $host = 'slave')
    {
		static $_cache = array();
		if (empty($param)) throw_exception('Db Error: select param is empty!');

		if (empty($param['field'])) {
			$param['field'] = '*';
		}
		if (empty($param['count'])) {
			$param['count'] = 'count(*)';
		}

		if (isset($param['index'])) {
			$param['index'] = 'USE INDEX ('.$param['index'].')';
		}

		if (trim($param['where']) != '')
		{
			if (strtoupper(substr(trim($param['where']),0,5)) != 'WHERE')
			{
				if (strtoupper(substr(trim($param['where']),0,3)) == 'AND'){
					$param['where'] = substr(trim($param['where']),3);
				}
				$param['where'] = 'WHERE '.$param['where'];
			}
		} else {
			$param['where'] = '';
		}

		$param['where_group'] = '';
		if (!empty($param['group'])) {
			$param['where_group'] .= ' group by '.$param['group'];
		}
		$param['where_order'] = '';
		if (!empty($param['order'])){
			$param['where_order'] .= ' order by '.$param['order'];
		}

		//判断是否是联表
		$tmp_table = explode(',',$param['table']);
		if (!empty($tmp_table) && count($tmp_table) > 1)
		{
			//判断join表数量和join条件是否一致
			if ((count($tmp_table)-1) != count($param['join_on'])){
				throw_exception('Db Error: join number is wrong!');
			}

			//trim 掉空白字符
			foreach($tmp_table as $key=>$val){
				$tmp_table[$key] = trim($val) ;
			}

			//拼join on 语句
			$tmp_sql = '';
			for ($i=1;$i<count($tmp_table);$i++){
				$tmp_sql .= $param['join_type'].' `'.DBPRE.$tmp_table[$i].'` as `'.$tmp_table[$i].'` ON '.$param['join_on'][$i-1].' ';
			}
			$sql = 'SELECT '.$param['field'].' FROM `'.DBPRE.$tmp_table[0].'` as `'.$tmp_table[0].'` '.$tmp_sql.' '.$param['where'].$param['where_group'].$param['where_order'];

			//如果有分页，那么计算信息总数
			$count_sql = 'SELECT '.$param['count'].' as count FROM `'.DBPRE.$tmp_table[0].'` as `'.$tmp_table[0].'` '.$tmp_sql.' '.$param['where'].$param['where_group'];
		}
		else {
			$sql = 'SELECT '.$param['field'].' FROM `'.DBPRE.$param['table'].'` as `'.$param['table'].'` '.$param['index'].' '.$param['where'].$param['where_group'].$param['where_order'];
			$count_sql = 'SELECT '.$param['count'].' as count FROM `'.DBPRE.$param['table'].'` as `'.$param['table'].'` '.$param['index'].' '.$param['where'];
		}

		//limit ，如果有分页对象的话，那么优先分页对象
		if ($obj_page instanceof Page )
		{
			$count_query = self::query($count_sql,$host);
			$count_fetch = mysqli_fetch_array($count_query,MYSQLI_ASSOC);
			$obj_page->setTotalNum($count_fetch['count']);
			$param['limit'] = $obj_page->getLimitStart().",".$obj_page->getEachNum();
		}

		if ($param['limit'] != '') {
			$sql .= ' limit '.$param['limit'];
		}
		if ($param['cache'] !== false)
		{
			$key =  is_string($param['cache_key'])?$param['cache_key']:md5($sql);
			if (isset($_cache[$key])) return $_cache[$key];
		}

		$result = self::query($sql,$host);
		if ($result === false) $result = array();
		while ($tmp=mysqli_fetch_array($result,MYSQLI_ASSOC)) {
			$array[] = $tmp;
		}
		if ($param['cache'] !== false && !isset($_cache[$key])) {
			$_cache[$key] = $array;
		}
		return $array;
	}

	/**
	 * 插入操作
	 *
	 * @param string $table_name 表名
	 * @param array $insert_array 待插入数据
	 * @return mixed
	 */
	public static function insert($table_name, $insert_array=array(), $host = 'master')
    {
		if (!is_array($insert_array)) return false;
		$fields = array();
		$value = array();
		foreach ($insert_array as $key => $val) {
			$fields[] = self::parseKey($key);
			$value[] = self::parseValue($val);
		}
		$sql = 'INSERT INTO `'.DBPRE.$table_name.'` ('.implode(',',$fields).') VALUES('.implode(',',$value).')';

		//当数据库没有自增ID的情况下，返回 是否成功
		$result = self::query($sql, $host);
		$insert_id = self::getLastId($host);
		return $insert_id ? $insert_id : $result;
	}

	/**
	 * 批量插入
	 *
	 * @param string $table_name 表名
	 * @param array $insert_array 待插入数据
	 * @return mixed
	 */
	public static function insertAll($table_name, $insert_array=array(), $host = 'master')
    {
		if (!is_array($insert_array[0])) return false;
		$fields = array_keys($insert_array[0]);
		array_walk($fields,array(self,'parseKey'));
		$values = array();
		foreach ($insert_array as $data)
		{
			$value = array();
			foreach ($data as $key=>$val) {
				$val = self::parseValue($val);
				if (is_scalar($val)){
					$value[] = $val;
				}
			}
			$values[] = '('.implode(',',$value).')';
		}

		$sql = 'INSERT INTO `'.DBPRE.$table_name.'` ('.implode(',',$fields).') VALUES '.implode(',',$values);
		$result = self::query($sql,$host);
		$insert_id = self::getLastId($host);
		return $insert_id ? $insert_id : $result;
	}

	/**
	 * 更新操作
	 *
	 * @param string $table_name 表名
	 * @param array $update_array 待更新数据
	 * @param string $where 执行条件
	 * @return bool
	 */
	public static function update($table_name, $update_array = array(), $where = '', $host = 'master')
    {
		if (!is_array($update_array)) return false;
		$string_value = '';
		foreach ($update_array as $k => $v)
		{
			if (is_array($v)){
				switch ($v['sign']){
					case 'increase': $string_value .= " $k = $k + ". $v['value'] .","; break;
					case 'decrease': $string_value .= " $k = $k - ". $v['value'] .","; break;
					case 'calc': $string_value .= " $k = ". $v['value'] .","; break;
					default: $string_value .= " $k = ". self::parseValue($v['value']) .",";
				}
			} else {
				$string_value .= " $k = ". self::parseValue($v) .",";
			}
		}

		$string_value = trim(trim($string_value),',');
		if (trim($where) != '')
		{
			if (strtoupper(substr(trim($where),0,5)) != 'WHERE')
			{
				if (strtoupper(substr(trim($where),0,3)) == 'AND'){
					$where = substr(trim($where),3);
				}
				$where = ' WHERE '.$where;
			}
		}
		$sql = 'UPDATE `'.DBPRE.$table_name.'` AS `'.$table_name.'` SET '.$string_value.' '.$where;
		$result = self::query($sql, $host);
		return $result;
	}

	/**
	 * 删除操作
	 *
	 * @param string $table_name 表名
	 * @param string $where 执行条件
	 * @return bool
	 */
	public static function delete($table_name, $where = '', $host = 'master')
    {
		if (trim($where) != '')
		{
			if (strtoupper(substr(trim($where),0,5)) != 'WHERE')
			{
				if (strtoupper(substr(trim($where),0,3)) == 'AND'){
					$where = substr(trim($where),3);
				}
				$where = ' WHERE '.$where;
			}
			$sql = 'DELETE FROM `'.DBPRE.$table_name.'` '.$where;
			return self::query($sql, $host);
		} else {
			throw_exception('Db Error: the condition of delete is empty!');
		}
	}

	/**
	 * 取得上一步插入产生的ID
	 *
	 * @return int
	 */
	public static function getLastId($host = 'master')
    {
		$id = mysqli_insert_id(self::$link[$host]);
		if (!$id){
		    $result = self::query('SELECT last_insert_id() as id',$host);
		    if ($result === false) return false;
			$id = mysqli_fetch_array($result,MYSQLI_ASSOC);
			$id = $id['id'];
		}
		return $id;
	}

	/**
	 * 取得一行信息
	 *
	 * @param array $param
	 * @param string $fields
	 * @return array
	 */
	public static function getRow($param, $fields = '*', $host = 'slave')
    {
		$table = $param['table'];
		$wfield = $param['field'];
		$value = $param['value'];

		if (is_array($wfield))
		{
			$where = array();
			foreach ($wfield as $k => $v){
				$where[] = $v."='".$value[$k]."'";
			}
			$where = implode(' and ',$where);
		}
		else {
			$where = $wfield."='".$value."'";
		}

		$sql = "SELECT ".$fields." FROM `".DBPRE.$table."` WHERE ".$where;
		$result = self::query($sql,$host);
		if ($result === false) return array();
		return mysqli_fetch_array($result,MYSQLI_ASSOC);
	}

	/**
	 * 执行REPLACE操作
	 *
	 * @param string $table_name 表名
	 * @param array $replace_array 待更新的数据
	 * @return bool
	 */
	public static function replace($table_name, $replace_array = array(), $host = 'master')
    {
		if (!empty($replace_array))
		{
			$string_field = '';
			$string_value = '';
			foreach ($replace_array as $k => $v){
				$string_field .= " $k ,";
				$string_value .= " '". $v ."',";
			}
			$sql = 'REPLACE INTO `'.DBPRE.$table_name.'` ('.trim($string_field,', ').') VALUES('.trim($string_value,', ').')';
			return self::query($sql,$host);
		}else {
			return false;
		}
	}

	/**
	 * 返回单表查询记录数量
	 *
	 * @param string $table 表名
	 * @param $condition mixed 查询条件，可以为空，也可以为数组或字符串
	 * @return int
	 */
	public static function getCount($table, $condition = null, $host = 'slave')
    {
		if (!empty($condition) && is_array($condition))
		{
			$where = '';
			foreach ($condition as $key=>$val) {
				self::parseKey($key);
				$val = self::parseValue($val);
				$where .= ' AND '.$key.'='.$val;
			}
			$where = ' WHERE '.substr($where,4);
		}
		elseif(is_string($condition))
        {
			if (strtoupper(substr(trim($condition),0,3)) == 'AND'){
				$where = ' WHERE '.substr(trim($condition),4);
			}else{
				$where = ' WHERE '.$condition;
			}
		}

		$sql = 'SELECT COUNT(*) as `count` FROM `'.DBPRE.$table.'` as `'.$table.'` '.(isset($where) ? $where : '');
		$result = self::query($sql,$host);
		if ($result === false) return 0;
		$result = mysqli_fetch_array($result,MYSQLI_ASSOC);
		return $result['count'];
	}

	/**
	 * 执行SQL语句
	 *
	 * @param string $sql 待执行的SQL
	 * @return
	 */
	public static function execute($sql, $host = 'master'){
		$result = self::query($sql,$host);
		return $result;
	}

	/**
	 * 列出所有表
	 *
	 * @return array
	 */
	public static function showTables($host = 'slave')
    {
		$sql = 'SHOW TABLES';
		$result = self::query($sql,$host);
		if ($result === false) return array();
		$array = array();
		while ($tmp=mysqli_fetch_array($result,MYSQLI_ASSOC)){
			$array[] = $tmp;
		}
		return $array;
	}

	/**
	 * 显示建表语句
	 *
	 * @param string $table
	 * @return string
	 */
	public static function showCreateTable($table, $host = 'slave')
    {
		$sql = 'SHOW CREATE TABLE `'.DBPRE.$table.'`';
		$result = self::query($sql,$host);
		if ($result === false) return '';
		$result = mysqli_fetch_array($result,MYSQLI_ASSOC);
		return $result['Create Table'];
	}

	/**
	 * 显示表结构信息
	 *
	 * @param string $table
	 * @return array
	 */
	public static function showColumns($table, $host = 'slave')
    {
		$sql = 'SHOW COLUMNS FROM `'.DBPRE.$table.'`';
		$result = self::query($sql,$host);
		if ($result === false) return array();
		$array = array();

		while ($tmp=mysqli_fetch_array($result,MYSQLI_ASSOC))
        {
            $array[$tmp['Field']] = array(
                'name'    => $tmp['Field'],
                'type'    => $tmp['Type'],
                'null' 	  => $tmp['Null'],
                'default' => $tmp['Default'],
                'primary' => (strtolower($tmp['Key']) == 'pri'),
                'autoinc' => (strtolower($tmp['Extra']) == 'auto_increment'),
            );
		}
		return $array;
	}

	/**
	 * 取得服务器信息
	 *
	 * @return string
	 */
	public static function getServerInfo($host = 'slave'){
        self::init_link();
		$result = mysqli_get_server_info(self::$link[$host]);
		return $result;
	}

	/**
	 * 格式化字段
	 *
	 * @param string $key 字段名
	 * @return string
	 */
    public static function parseKey(&$key){
        $key   =  trim($key);
        if(!preg_match('/[,\'\"\*\(\)`.\s]/',$key)) {
           $key = '`'.$key.'`';
        }
        return $key;
    }

    /**
     * 格式化值
     *
     * @param mixed $value
     * @return mixed
     */
    public static function parseValue($value){
    	$value = addslashes(stripslashes($value));//重新加斜线，防止从数据库直接读取出错
    	return "'".$value."'";
    }

    public static function beginTransaction($host = 'master')
    {
        self::init_link();
        if (!self::$ifTransacting)
        {
            $result = self::$link[$host]->autocommit(false);
    		if($result == false)
    		{
                self::connect($host);
                self::$ifTransacting = false;

                Log::record("autocommit start 2",Log::DEBUG);
                self::$link[$host]->autocommit(false);
            } else {
                Log::record("autocommit start 1",Log::DEBUG);
            }
    	}
    	self::$ifTransacting = true;
    }

    public static function commit($host = 'master')
    {
        if (self::$ifTransacting)
        {
    		$result = self::$link[$host]->commit();
            Log::record("autocommit end commit",Log::DEBUG);
            self::$link[$host]->autocommit(true);
    		self::$ifTransacting = false;

    		if (!$result) {
    		    $err = mysqli_error(self::$link[$host]);
    		    throw_exception("Db Error: {$err}");
            }
    	}
    }

    public static function rollback($host = 'master')
    {
        if (self::$ifTransacting){
    		$result = self::$link[$host]->rollback();
            Log::record("autocommit end rollback",Log::DEBUG);
            $fsuccess = self::$link[$host]->autocommit(true);
    		self::$ifTransacting = false;

    		if (!$result || !$fsuccess) {
                $err = mysqli_error(self::$link[$host]);
                self::connect($host);
                throw_exception("Db Error: {$err}");
    		}
    	}
    }

	public static function affected_rows($host = 'master')
    {
		return self::$link[$host]->affected_rows;
	}
}