<?php

/**
 *--------------------------------------
 * 数据库基类
 *--------------------------------------
 * @project		: pfa
 * @author		: cblee
 * @created		: 2012-9-24
 * @copyright	: (c)2012 AsThis
 *--------------------------------------
 */

class Db
{
	public $debug = false; /* 调试开关 */
	public $dbType = ''; /* 数据库类型 */
	public $dbCfg = ''; /* 数据库配置 */
	public $queryStr = ''; /* 当前SQL指令 */
	public $lastInsID  = null; /* 最后插入ID */
	public $numRows = 0; /* 返回或者影响记录数 */
	public $numCols = 0; /* 返回字段数 */
	public $transTimes = 0; /*事务指令数 */
	public $linkID = array(); /* 数据库连接ID 支持多个连接 */
	public $_linkID = null; /* 当前连接ID */
	public $queryID = null; /* 当前查询ID */
	public $connected = false; /* 是否已经连接数据库 */
	public $comparison = array( /* 数据库表达式 */
		'eq' => '=',
		'neq' => '<>',
		'gt' => '>',
		'egt' => '>=',
		'lt' => '<',
		'elt' => '<=',
		'notlike' => 'NOT LIKE',
		'like' => 'LIKE'
	);
	protected $selectSql = 'SELECT%DISTINCT% %FIELDS% FROM %TABLE%%JOIN%%WHERE%%GROUP%%HAVING%%ORDER%%LIMIT%'; /* 查询表达式 */

	public function __construct(){}

	/* DB工厂 */
	public function get_db($dbCfg = '')
	{
		$dbCfg = $this->parse_cfg($dbCfg);
		if(empty($dbCfg['dbtype']))
		{
			halt(L('_NO_DB_CFG_'));
		}
		$this->dbType = ucwords(strtolower($dbCfg['dbtype']));
		$dbClass = 'Db'.$this->dbType;
		import('lib.ext.db.'.$dbClass, PFA_PATH);
		$db = get_instance($dbClass, $dbCfg);
		if(C('DEBUG.SWITCH'))
		{
			$db->debug = true;
		}
		return $db;
	}

	/* 初始化数据库连接 */
	protected function init_connect($master = true)
	{
		if(1 == C('DB.DEPLOY_TYPE')) /* 采用分布式数据库 */
		{
			$this->_linkID = $this->multi_connect($master);
		}
		else
		{
			if(!$this->connected)
			{
				$this->_linkID = $this->connect(); /* 默认单数据库*/
			}
		}
	}

	/* 连接分布式服务器 $master:主服务器 */
	protected function multi_connect($master = false)
	{
		static $_config = array();
		if(empty($_config))
		{
			/* 缓存分布式数据库配置解析 */
			foreach($this->dbCfg as $key => $val)
			{
				$_config[$key] = explode(',', $val);
			}
		}
		if(C('DB.RW_SEPARATE')) /* 数据库是否读写分离 */
		{
			if($master)
			{
				$r = 0; /* 默认主服务器 */
			}
			else
			{
				$r = floor(mt_rand(1, count($_config['hostname']) - 1)); /* 随机连接读操作数据库 */
			}
		}
		else
		{
			$r = floor(mt_rand(0, count($_config['hostname']) - 1)); /* 读写不区分服务器 */
		}
		$dbCfg = array(
			'username' => isset($_config['username'][$r]) ? $_config['username'][$r] : $_config['username'][0],
			'password' => isset($_config['password'][$r]) ? $_config['password'][$r] : $_config['password'][0],
			'hostname' => isset($_config['hostname'][$r]) ? $_config['hostname'][$r] : $_config['hostname'][0],
			'hostport' => isset($_config['hostport'][$r]) ? $_config['hostport'][$r] : $_config['hostport'][0],
			'database' => isset($_config['database'][$r]) ? $_config['database'][$r] : $_config['database'][0],
			'params' => isset($_config['params'][$r]) ? $_config['params'][$r] : $_config['params'][0],
		);
		return $this->connect($dbCfg, $r);
	}

	/* 分析数据库配置信息 支持数组和DSN */
	protected function parse_cfg($dbCfg = '')
	{
		if(!empty($dbCfg) && is_string($dbCfg))
		{
			$dbCfg = $this->parse_dsn($dbCfg);
		}
		elseif(empty($dbCfg))
		{
			$dbCfg = array (
				'dbtype' => C('DB.TYPE'),
				'username' => C('DB.USER'),
				'password' => C('DB.PWD'),
				'hostname' => C('DB.HOST'),
				'hostport' => C('DB.PORT'),
				'database' => C('DB.NAME'),
				'params' => C('DB.PARAMS'),
			);
		}
		return $dbCfg;
	}

	/* DSN解析 mysql://user:pass@host:port/dbName */
	protected function parse_dsn($dsnStr)
	{
		if(empty($dsnStr))
		{
			return false;
		}
		$info = parse_url($dsnStr);
		if($info['scheme'])
		{
			$dsn = array(
				'dbtype' => $info['scheme'],
				'username' => isset($info['user']) ? $info['user'] : '',
				'password' => isset($info['pass']) ? $info['pass'] : '',
				'hostname' => isset($info['host']) ? $info['host'] : '',
				'hostport' => isset($info['port']) ? $info['port'] : '',
				'database' => isset($info['path']) ? substr($info['path'],1) : ''
			);
		}
		else
		{
			preg_match('/^(.*?)\:\/\/(.*?)\:(.*?)\@(.*?)\:([0-9]{1, 6})\/(.*?)$/', trim($dsnStr), $matches);
			$dsn = array(
				'dbtype' => $matches[1],
				'username' => $matches[2],
				'password' => $matches[3],
				'hostname' => $matches[4],
				'hostport' => $matches[5],
				'database' => $matches[6]
			);
		}
		return $dsn;
	}

	/* set分析 */
	protected function parse_set($data)
	{
		foreach($data as $key => $val){
			$value = $this->parse_value($val);
			if(is_scalar($value)) /* 过滤非标量 */
			{
				$set[] = $this->parse_key($key).'='.$value;
			}
		}
		return ' SET '.implode(',', $set);
	}

	/* value分析 */
	protected function parse_value($value)
	{
		if(is_string($value))
		{
			$value = '\''.$this->escape_string($value).'\'';
		}
		elseif(isset($value[0]) && is_string($value[0]) && strtolower($value[0]) == 'exp')
		{
			$value = $this->escape_string($value[1]);
		}
		elseif(is_array($value))
		{
			$value = array_map(array($this, 'parse_value'), $value);
		}
		elseif(is_null($value))
		{
			$value = 'null';
		}
		return $value;
	}

	/* 字段分析 $fields:'f' | 'f1, f2' | array('f1', 'f2') | array('fld1' => 'f') */
	protected function parse_field($fields)
	{
		if(is_string($fields) && strpos($fields, ','))
		{
			$fields = explode(',', $fields);
		}
		if(is_array($fields))
		{
			$array = array();
			foreach($fields as $key=>$field)
			{
				if(is_numeric($key))
				{
					$array[] = $this->parse_key($field);
				}
				else
				{
					$array[] = $this->parse_key($key).' AS '.$this->parse_key($field);
				}
			}
			$fieldsStr = implode(',', $array);
		}
		elseif(is_string($fields) && !empty($fields))
		{
			$fieldsStr = $this->parse_key($fields);
		}
		else
		{
			$fieldsStr = '*';
		}
		return $fieldsStr;
	}

	/* 表名分析 支持别名 */
	protected function parse_table($tables)
	{
		if(is_array($tables))
		{
			$array = array();
			foreach($tables as $table => $alias)
			{
				if(is_numeric($table))
				{
					$array[] = $this->parse_key($alias);
				}
				else
				{
					$array[] = $this->parse_key($table).' '.$this->parse_key($alias);
				}
			}
			$tables = $array;
		}
		elseif(is_string($tables))
		{
			$tables = explode(',', $tables);
			array_walk($tables, array(&$this, 'parse_key'));
		}
		return implode(',', $tables);
	}

	/* distinct分析 */
	protected function parse_distinct($distinct)
	{
		return !empty($distinct) ? ' DISTINCT ' : '';
	}

	/* join分析 */
	protected function parse_join($join)
	{
		$joinStr = '';
		if(!empty($join))
		{
			if(is_array($join))
			{
				foreach($join as $key => $_join)
				{
					if(false !== stripos($_join, 'JOIN'))
					{
						$joinStr .= ' '.$_join;
					}
					else
					{
						$joinStr .= ' LEFT JOIN ' .$_join;
					}
				}
			}
			else
			{
				$joinStr .= ' LEFT JOIN '.$join;
			}
		}
		return $joinStr;
	}

	/* where分析 */
	protected function parse_where($where)
	{
		$whereStr = '';
		if(is_string($where))
		{
			$whereStr = $where;
		}
		else /* 使用数组条件表达式 */
		{
			if(array_key_exists('_logic', $where))
			{
				/* 逻辑运算规则 如:OR XOR AND NOT */
				$operate = ' '.strtoupper($where['_logic']).' ';
				unset($where['_logic']);
			}
			else
			{
				$operate = ' AND '; /* 默认AND运算 */
			}
			foreach($where as $key => $val)
			{
				$whereStr .= "(";
				if(!preg_match('/^[A-Z_\-.a-z0-9]+$/', trim($key))) /* 查询字段安全过滤 */
				{
					halt(L('_EXPRESS_ERROR_').' : '.$key);
				}
				$key = trim($key);
				$whereStr .= $this->parse_whereItem($this->parse_key($key), $val);
				$whereStr .= ')'.$operate;
			}
			$whereStr = substr($whereStr, 0, -strlen($operate));
		}
		return empty($whereStr) ? '' : ' WHERE '.$whereStr;
	}

	/* where子单元分析 */
	protected function parse_whereItem($key, $val)
	{
		$whereStr = '';
		if(is_array($val))
		{
			if(is_string($val[0]))
			{
				if(preg_match('/^(EQ|NEQ|GT|EGT|LT|ELT|NOTLIKE|LIKE)$/i', $val[0])) /* 比较运算 */
				{
					$whereStr .= $key.' '.$this->comparison[strtolower($val[0])].' '.$this->parse_value($val[1]);
				}
				elseif('exp'==strtolower($val[0])) /* 使用表达式 */
				{
					$whereStr .= ' ('.$key.' '.$val[1].') ';
				}
				elseif(preg_match('/INSET/i',$val[0])) /*INSET 运算 */
				{
					$whereStr .= "FIND_IN_SET('{$val[1]}', {$key}) > 0";
				}
				elseif(preg_match('/IN/i', $val[0])) /* IN 运算 */
				{
					if(isset($val[2]) && 'exp'==$val[2])
					{
						$whereStr .= $key.' '.strtoupper($val[0]).' '.$val[1];
					}
					else
					{
						if(is_string($val[1]))
						{
							$val[1] =  explode(',', $val[1]);
						}
						$zone = implode(',', $this->parse_value($val[1]));
						$whereStr .= $key.' '.strtoupper($val[0]).' ('.$zone.')';
					}
				}
				elseif(preg_match('/BETWEEN/i',$val[0])) /* BETWEEN运算 */
				{
					$data = is_string($val[1]) ? explode(',', $val[1]) : $val[1];
					$whereStr .= ' ('.$key.' '.strtoupper($val[0]).' '.$this->parse_value($data[0]).' AND '.$this->parse_value($data[1]).' )';
				}
				else
				{
					halt(L('_EXPRESS_ERROR_').':'.$val[0]);
				}
			}
			else
			{
				$count = count($val);
				if(in_array(strtoupper(trim($val[$count-1])), array('AND', 'OR', 'XOR')))
				{
					$rule = strtoupper(trim($val[$count-1]));
					$count = $count - 1;
				}
				else
				{
					$rule = 'AND';
				}
				for($i=0; $i<$count; $i++)
				{
					$data = is_array($val[$i]) ? $val[$i][1] : $val[$i];
					if('exp' == strtolower($val[$i][0]))
					{
						$whereStr .= '('.$key.' '.$data.') '.$rule.' ';
					}
					else
					{
						$op = is_array($val[$i]) ? $this->comparison[strtolower($val[$i][0])] : '=';
						$whereStr .= '('.$key.' '.$op.' '.$this->parse_value($data).') '.$rule.' ';
					}
				}
				$whereStr = substr($whereStr, 0, -4);
			}
		}
		else
		{
			/* 对字符串类型字段采用模糊匹配 */
			if(C('DB.LIKE_FIELDS') && preg_match('/('.C('DB.LIKE_FIELDS').')/i', $key))
			{
				$val = '%'.$val.'%';
				$whereStr .= $key." LIKE ".$this->parse_value($val);
			}
			else
			{
				$whereStr .= $key." = ".$this->parse_value($val);
			}
		}
		return $whereStr;
	}

	/* group分析 */
	protected function parse_group($group)
	{
		return !empty($group) ? ' GROUP BY '.$group : '';
	}

	/* having分析 */
	protected function parse_having($having)
	{
		return  !empty($having) ? ' HAVING '.$having : '';
	}

	/* order分析 */
	protected function parse_order($order)
	{
		if(is_array($order))
		{
			$array = array();
			foreach($order as $key => $val)
			{
				if(is_numeric($key))
				{
					$array[] = $this->parse_key($val);
				}
				else
				{
					$array[] = $this->parse_key($key).' '.$val;
				}
			}
			$order = implode(',', $array);
		}
		return !empty($order) ? ' ORDER BY '.$order : '';
	}

	/* limit分析 */
	protected function parse_limit($limit)
	{
		return !empty($limit) ? ' LIMIT '.$limit.' ' : '';
	}

	/* 设置锁机制 */
	protected function parse_lock($lock = false)
	{
		if(!$lock)
		{
			return '';
		}
		if('ORACLE' == $this->dbType)
		{
			return ' FOR UPDATE NOWAIT ';
		}
		return ' FOR UPDATE ';
	}

	/* 插入记录 */
	public function insert($data, $options = array(), $replace = false)
	{
		$values = $fields = array();
		foreach($data as $key=>$val)
		{
			$value = $this->parse_value($val);
			if(is_scalar($value)) /* 过滤非标量 */
			{
				$values[] = $value;
				$fields[] = $this->parse_key($key);
			}
		}
		$sql = ($replace ? 'REPLACE' : 'INSERT').' INTO '.$this->parse_table($options['table']).' ('.implode(',', $fields).') VALUES ('.implode(',', $values).')';
		$sql .= $this->parse_lock(isset($options['lock']) ? $options['lock'] : false);
		return $this->execute($sql);
	}

	/* 更新记录 */
	public function update($data, $options)
	{
		$sql = 'UPDATE '
		.$this->parse_table($options['table'])
		.$this->parse_set($data)
		.$this->parse_where(isset($options['where']) ? $options['where'] : '')
		.$this->parse_order(isset($options['order']) ? $options['order'] : '')
		.$this->parse_limit(isset($options['limit']) ? $options['limit'] : '')
		.$this->parse_lock(isset($options['lock']) ? $options['lock'] : false);
		/* 将__TABLE_NAME__这样的字符串替换成正规的表名,并且带上前缀和后缀 */
		$sql = preg_replace("/__([A-Z_-]+)__/esU", "C('DB.PREFIX').strtolower('$1').C('DB.SUFFIX')", $sql);
		return $this->execute($sql);
	}

	/* 删除记录 */
	public function delete($options = array())
	{
		$sql = 'DELETE FROM '
		.$this->parse_table($options['table'])
		.$this->parse_where(isset($options['where']) ? $options['where'] : '')
		.$this->parse_order(isset($options['order']) ? $options['order'] : '')
		.$this->parse_limit(isset($options['limit']) ? $options['limit'] : '')
		.$this->parse_lock(isset($options['lock']) ? $options['lock'] : false);
		/* 将__TABLE_NAME__这样的字符串替换成正规的表名,并且带上前缀和后缀 */
		$sql = preg_replace("/__([A-Z_-]+)__/esU", "C('DB.PREFIX').strtolower('$1').C('DB.SUFFIX')", $sql);
		return $this->execute($sql);
	}

	/* 查询记录 */
	public function select($options=array())
	{
		$sql = $this->build_selectSql($options);
		$result = $this->query($sql);
		return $result;
	}

	/* 生成查询SQL */
	protected function build_selectSql($options=array())
	{
		if(isset($options['page']))
		{
			/* 根据页数计算limit */
			if(strpos($options['page'], ','))
			{
				list($page, $listRows) =  explode(',', $options['page']);
			}
			else
			{
				$page = $options['page'];
			}
			$page = $page ? $page : 1;
			$listRows = isset($listRows) ? $listRows : (is_numeric($options['limit']) ? $options['limit'] : 20);
			$offset = $listRows * ((int)$page - 1);
			$options['limit'] = $offset.','.$listRows;
		}
		$sql = str_replace(
			array(
				'%TABLE%',
				'%DISTINCT%',
				'%FIELDS%',
				'%JOIN%',
				'%WHERE%',
				'%GROUP%',
				'%HAVING%',
				'%ORDER%',
				'%LIMIT%'),
			array(
				$this->parse_table($options['table']),
				$this->parse_distinct(isset($options['distinct']) ? $options['distinct'] : false),
				$this->parse_field(isset($options['field']) ? $options['field'] : '*'),
				$this->parse_join(isset($options['join']) ? $options['join'] : ''),
				$this->parse_where(isset($options['where']) ? $options['where'] : ''),
				$this->parse_group(isset($options['group']) ? $options['group'] : ''),
				$this->parse_having(isset($options['having']) ? $options['having'] : ''),
				$this->parse_order(isset($options['order']) ? $options['order'] : ''),
				$this->parse_limit(isset($options['limit']) ? $options['limit'] : '')),
			$this->selectSql);
		$sql .= $this->parse_lock(isset($options['lock']) ? $options['lock'] : false);
		/* 将__TABLE_NAME__这样的字符串替换成正规的表名,并且带上前缀和后缀 */
		$sql = preg_replace("/__([A-Z_-]+)__/esU", "C('DB.PREFIX').strtolower('$1').C('DB.SUFFIX')", $sql);
		return $sql;
	}

	/* 获取最后执行的SQL语句 */
	public function get_lastSql()
	{
		return $this->queryStr;
	}

	/* 数据库调试 记录当前SQL */
	protected function debug()
	{
		if($this->debug)
		{
			G('queryEndTime'); /* 记录操作结束时间 */
			Log::record($this->queryStr." [RunTime:".G('queryStartTime','queryEndTime', 6)."s]");
		}
	}

	/* 字段名和表名处理(由驱动类定义) */
	protected function parse_key(&$key){}
	/* 转义SQL特殊字符(由驱动类定义) */
	protected function escape_string($str){}
	/* 查询(由驱动类定义) */
	protected function query($sql){}
	/* 执行(由驱动类定义) */
	protected function execute($sql){}
	/* 关闭数据库(由驱动类定义) */
	protected function close(){}

	public function __destruct()
	{
		$this->close();
	}
}

?>