Просмотр файла news/libraries/db/sql.php

Конкурс дизайнеров на 15000 рублей
Свежие Креативы и море позитива !!!
Размер файла: 9.86Kb 
  1. <?php
  2.  
  3. /*
  4. 	Copyright (c) 2009-2014 F3::Factory/Bong Cosca, All rights reserved.
  5.  
  6. 	This file is part of the Fat-Free Framework (http://fatfree.sf.net).
  7.  
  8. 	THE SOFTWARE AND DOCUMENTATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF
  9. 	ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE
  10. 	IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR
  11. 	PURPOSE.
  12.  
  13. 	Please see the license.txt file for more information.
  14. */
  15.  
  16. namespace DB;
  17.  
  18. //! PDO wrapper
  19. class SQL extends \PDO {
  20.  
  21. 	protected
  22. 		//! UUID
  23. 		$uuid,
  24. 		//! Data source name
  25. 		$dsn,
  26. 		//! Database engine
  27. 		$engine,
  28. 		//! Database name
  29. 		$dbname,
  30. 		//! Transaction flag
  31. 		$trans=FALSE,
  32. 		//! Number of rows affected by query
  33. 		$rows=0,
  34. 		//! SQL log
  35. 		$log;
  36.  
  37. 	/**
  38. 	*	Begin SQL transaction
  39. 	*	@return bool
  40. 	**/
  41. 	function begin() {
  42. 		$out=parent::begintransaction();
  43. 		$this->trans=TRUE;
  44. 		return $out;
  45. 	}
  46.  
  47. 	/**
  48. 	*	Rollback SQL transaction
  49. 	*	@return bool
  50. 	**/
  51. 	function rollback() {
  52. 		$out=parent::rollback();
  53. 		$this->trans=FALSE;
  54. 		return $out;
  55. 	}
  56.  
  57. 	/**
  58. 	*	Commit SQL transaction
  59. 	*	@return bool
  60. 	**/
  61. 	function commit() {
  62. 		$out=parent::commit();
  63. 		$this->trans=FALSE;
  64. 		return $out;
  65. 	}
  66.  
  67. 	/**
  68. 	*	Map data type of argument to a PDO constant
  69. 	*	@return int
  70. 	*	@param $val scalar
  71. 	**/
  72. 	function type($val) {
  73. 		switch (gettype($val)) {
  74. 			case 'NULL':
  75. 				return \PDO::PARAM_NULL;
  76. 			case 'boolean':
  77. 				return \PDO::PARAM_BOOL;
  78. 			case 'integer':
  79. 				return \PDO::PARAM_INT;
  80. 			default:
  81. 				return \PDO::PARAM_STR;
  82. 		}
  83. 	}
  84.  
  85. 	/**
  86. 	*	Cast value to PHP type
  87. 	*	@return scalar
  88. 	*	@param $type string
  89. 	*	@param $val scalar
  90. 	**/
  91. 	function value($type,$val) {
  92. 		switch ($type) {
  93. 			case \PDO::PARAM_NULL:
  94. 				return (unset)$val;
  95. 			case \PDO::PARAM_INT:
  96. 				return (int)$val;
  97. 			case \PDO::PARAM_BOOL:
  98. 				return (bool)$val;
  99. 			case \PDO::PARAM_STR:
  100. 				return (string)$val;
  101. 		}
  102. 	}
  103.  
  104. 	/**
  105. 	*	Execute SQL statement(s)
  106. 	*	@return array|int|FALSE
  107. 	*	@param $cmds string|array
  108. 	*	@param $args string|array
  109. 	*	@param $ttl int
  110. 	*	@param $log bool
  111. 	**/
  112. 	function exec($cmds,$args=NULL,$ttl=0,$log=TRUE) {
  113. 		$auto=FALSE;
  114. 		if (is_null($args))
  115. 			$args=array();
  116. 		elseif (is_scalar($args))
  117. 			$args=array(1=>$args);
  118. 		if (is_array($cmds)) {
  119. 			if (count($args)<($count=count($cmds)))
  120. 				// Apply arguments to SQL commands
  121. 				$args=array_fill(0,$count,$args);
  122. 			if (!$this->trans) {
  123. 				$this->begin();
  124. 				$auto=TRUE;
  125. 			}
  126. 		}
  127. 		else {
  128. 			$cmds=array($cmds);
  129. 			$args=array($args);
  130. 		}
  131. 		$fw=\Base::instance();
  132. 		$cache=\Cache::instance();
  133. 		$result=FALSE;
  134. 		foreach (array_combine($cmds,$args) as $cmd=>$arg) {
  135. 			if (!preg_replace('/(^\s+|[\s;]+$)/','',$cmd))
  136. 				continue;
  137. 			$now=microtime(TRUE);
  138. 			$keys=$vals=array();
  139. 			if ($fw->get('CACHE') && $ttl && ($cached=$cache->exists(
  140. 				$hash=$fw->hash($this->dsn.$cmd.
  141. 				$fw->stringify($arg)).'.sql',$result)) &&
  142. 				$cached[0]+$ttl>microtime(TRUE)) {
  143. 				foreach ($arg as $key=>$val) {
  144. 					$vals[]=$fw->stringify(is_array($val)?$val[0]:$val);
  145. 					$keys[]='/'.(is_numeric($key)?'\?':preg_quote($key)).'/';
  146. 				}
  147. 			}
  148. 			elseif (is_object($query=$this->prepare($cmd))) {
  149. 				foreach ($arg as $key=>$val) {
  150. 					if (is_array($val)) {
  151. 						// User-specified data type
  152. 						$query->bindvalue($key,$val[0],$val[1]);
  153. 						$vals[]=$fw->stringify($this->value($val[1],$val[0]));
  154. 					}
  155. 					else {
  156. 						// Convert to PDO data type
  157. 						$query->bindvalue($key,$val,
  158. 							$type=$this->type($val));
  159. 						$vals[]=$fw->stringify($this->value($type,$val));
  160. 					}
  161. 					$keys[]='/'.(is_numeric($key)?'\?':preg_quote($key)).'/';
  162. 				}
  163. 				$query->execute();
  164. 				$error=$query->errorinfo();
  165. 				if ($error[0]!=\PDO::ERR_NONE) {
  166. 					// Statement-level error occurred
  167. 					if ($this->trans)
  168. 						$this->rollback();
  169. 					user_error('PDOStatement: '.$error[2]);
  170. 				}
  171. 				if (preg_match('/^\s*'.
  172. 					'(?:CALL|EXPLAIN|SELECT|PRAGMA|SHOW|RETURNING|EXEC)\b/is',
  173. 					$cmd)) {
  174. 					$result=$query->fetchall(\PDO::FETCH_ASSOC);
  175. 					// Work around SQLite quote bug
  176. 					if (preg_match('/sqlite2?/',$this->engine))
  177. 						foreach ($result as $pos=>$rec) {
  178. 							unset($result[$pos]);
  179. 							$result[$pos]=array();
  180. 							foreach ($rec as $key=>$val)
  181. 								$result[$pos][trim($key,'\'"[]`')]=$val;
  182. 						}
  183. 					$this->rows=count($result);
  184. 					if ($fw->get('CACHE') && $ttl)
  185. 						// Save to cache backend
  186. 						$cache->set($hash,$result,$ttl);
  187. 				}
  188. 				else
  189. 					$this->rows=$result=$query->rowcount();
  190. 				$query->closecursor();
  191. 				unset($query);
  192. 			}
  193. 			else {
  194. 				$error=$this->errorinfo();
  195. 				if ($error[0]!=\PDO::ERR_NONE) {
  196. 					// PDO-level error occurred
  197. 					if ($this->trans)
  198. 						$this->rollback();
  199. 					user_error('PDO: '.$error[2]);
  200. 				}
  201. 			}
  202. 			if ($log)
  203. 				$this->log.=date('r').' ('.
  204. 					sprintf('%.1f',1e3*(microtime(TRUE)-$now)).'ms) '.
  205. 					(empty($cached)?'':'[CACHED] ').
  206. 					preg_replace($keys,$vals,$cmd,1).PHP_EOL;
  207. 		}
  208. 		if ($this->trans && $auto)
  209. 			$this->commit();
  210. 		return $result;
  211. 	}
  212.  
  213. 	/**
  214. 	*	Return number of rows affected by last query
  215. 	*	@return int
  216. 	**/
  217. 	function count() {
  218. 		return $this->rows;
  219. 	}
  220.  
  221. 	/**
  222. 	*	Return SQL profiler results
  223. 	*	@return string
  224. 	**/
  225. 	function log() {
  226. 		return $this->log;
  227. 	}
  228.  
  229. 	/**
  230. 	*	Retrieve schema of SQL table
  231. 	*	@return array|FALSE
  232. 	*	@param $table string
  233. 	*	@param $fields array|string
  234. 	*	@param $ttl int
  235. 	**/
  236. 	function schema($table,$fields=NULL,$ttl=0) {
  237. 		// Supported engines
  238. 		$cmd=array(
  239. 			'sqlite2?'=>array(
  240. 				'PRAGMA table_info("'.$table.'");',
  241. 				'name','type','dflt_value','notnull',0,'pk',TRUE),
  242. 			'mysql'=>array(
  243. 				'SHOW columns FROM `'.$this->dbname.'`.`'.$table.'`;',
  244. 				'Field','Type','Default','Null','YES','Key','PRI'),
  245. 			'mssql|sqlsrv|sybase|dblib|pgsql|odbc'=>array(
  246. 				'SELECT '.
  247. 					'c.column_name AS field,'.
  248. 					'c.data_type AS type,'.
  249. 					'c.column_default AS defval,'.
  250. 					'c.is_nullable AS nullable,'.
  251. 					't.constraint_type AS pkey '.
  252. 				'FROM information_schema.columns AS c '.
  253. 				'LEFT OUTER JOIN '.
  254. 					'information_schema.key_column_usage AS k '.
  255. 					'ON '.
  256. 						'c.table_name=k.table_name AND '.
  257. 						'c.column_name=k.column_name AND '.
  258. 						'c.table_schema=k.table_schema '.
  259. 						($this->dbname?
  260. 							('AND c.table_catalog=k.table_catalog '):'').
  261. 				'LEFT OUTER JOIN '.
  262. 					'information_schema.table_constraints AS t ON '.
  263. 						'k.table_name=t.table_name AND '.
  264. 						'k.constraint_name=t.constraint_name '.
  265. 						'k.table_schema=t.table_schema '.
  266. 						($this->dbname?
  267. 							('AND k.table_catalog=t.table_catalog '):'').
  268. 				'WHERE '.
  269. 					'c.table_name='.$this->quote($table).' '.
  270. 					($this->dbname?
  271. 						('AND c.table_catalog='.
  272. 							$this->quote($this->dbname)):'').
  273. 				';',
  274. 				'field','type','defval','nullable','YES','pkey','PRIMARY KEY'),
  275. 			'oci'=>array(
  276. 				'SELECT c.column_name AS field, '.
  277. 					'c.data_type AS type, '.
  278. 					'c.data_default AS defval, '.
  279. 					'c.nullable AS nullable, '.
  280. 					'(SELECT t.constraint_type '.
  281. 						'FROM all_cons_columns acc '.
  282. 						'LEFT OUTER JOIN all_constraints t '.
  283. 						'ON acc.constraint_name=t.constraint_name '.
  284. 						'WHERE acc.table_name='.$this->quote($table).' '.
  285. 						'AND acc.column_name=c.column_name '.
  286. 						'AND constraint_type='.$this->quote('P').') AS pkey '.
  287. 				'FROM all_tab_cols c '.
  288. 				'WHERE c.table_name='.$this->quote($table),
  289. 				'FIELD','TYPE','DEFVAL','NULLABLE','Y','PKEY','P')
  290. 		);
  291. 		if (is_string($fields))
  292. 			$fields=\Base::instance()->split($fields);
  293. 		foreach ($cmd as $key=>$val)
  294. 			if (preg_match('/'.$key.'/',$this->engine)) {
  295. 				// Improve InnoDB performance on MySQL with
  296. 				// SET GLOBAL innodb_stats_on_metadata=0;
  297. 				// This requires SUPER privilege!
  298. 				$rows=array();
  299. 				foreach ($this->exec($val[0],NULL,$ttl) as $row) {
  300. 					if (!$fields || in_array($row[$val[1]],$fields))
  301. 						$rows[$row[$val[1]]]=array(
  302. 							'type'=>$row[$val[2]],
  303. 							'pdo_type'=>
  304. 								preg_match('/int\b|int(?=eger)|bool/i',
  305. 									$row[$val[2]],$parts)?
  306. 								constant('\PDO::PARAM_'.
  307. 									strtoupper($parts[0])):
  308. 								\PDO::PARAM_STR,
  309. 							'default'=>$row[$val[3]],
  310. 							'nullable'=>$row[$val[4]]==$val[5],
  311. 							'pkey'=>$row[$val[6]]==$val[7]
  312. 						);
  313. 				}
  314. 				return $rows;
  315. 			}
  316. 		return FALSE;
  317. 	}
  318.  
  319. 	/**
  320. 	*	Quote string
  321. 	*	@return string
  322. 	*	@param $val mixed
  323. 	*	@param $type int
  324. 	**/
  325. 	function quote($val,$type=\PDO::PARAM_STR) {
  326. 		return $this->engine=='odbc'?
  327. 			(is_string($val)?
  328. 				\Base::instance()->stringify(str_replace('\'','\'\'',$val)):
  329. 				$val):
  330. 			parent::quote($val,$type);
  331. 	}
  332.  
  333. 	/**
  334. 	*	Return UUID
  335. 	*	@return string
  336. 	**/
  337. 	function uuid() {
  338. 		return $this->uuid;
  339. 	}
  340.  
  341. 	/**
  342. 	*	Return database engine
  343. 	*	@return string
  344. 	**/
  345. 	function driver() {
  346. 		return $this->engine;
  347. 	}
  348.  
  349. 	/**
  350. 	*	Return server version
  351. 	*	@return string
  352. 	**/
  353. 	function version() {
  354. 		return parent::getattribute(parent::ATTR_SERVER_VERSION);
  355. 	}
  356.  
  357. 	/**
  358. 	*	Return database name
  359. 	*	@return string
  360. 	**/
  361. 	function name() {
  362. 		return $this->dbname;
  363. 	}
  364.  
  365. 	/**
  366. 	*	Return quoted identifier name
  367. 	*	@return string
  368. 	*	@param $key
  369. 	**/
  370. 	function quotekey($key) {
  371. 		if ($this->engine=='mysql')
  372. 			$key="`".implode('`.`',explode('.',$key))."`";
  373. 		elseif (preg_match('/sybase|dblib/',$this->engine))
  374. 			$key="'".implode("'.'",explode('.',$key))."'";
  375. 		elseif (preg_match('/sqlite2?|pgsql|oci/',$this->engine))
  376. 			$key='"'.implode('"."',explode('.',$key)).'"';
  377. 		elseif (preg_match('/mssql|sqlsrv|odbc/',$this->engine))
  378. 			$key="[".implode('].[',explode('.',$key))."]";
  379. 		return $key;
  380. 	}
  381.  
  382. 	/**
  383. 	*	Instantiate class
  384. 	*	@param $dsn string
  385. 	*	@param $user string
  386. 	*	@param $pw string
  387. 	*	@param $options array
  388. 	**/
  389. 	function __construct($dsn,$user=NULL,$pw=NULL,array $options=NULL) {
  390. 		$fw=\Base::instance();
  391. 		$this->uuid=$fw->hash($this->dsn=$dsn);
  392. 		if (preg_match('/^.+?(?:dbname|database)=(.+?)(?=;|$)/i',$dsn,$parts))
  393. 			$this->dbname=$parts[1];
  394. 		if (!$options)
  395. 			$options=array();
  396. 		if (isset($parts[0]) && strstr($parts[0],':',TRUE)=='mysql')
  397. 			$options+=array(\PDO::MYSQL_ATTR_INIT_COMMAND=>'SET NAMES '.
  398. 				strtolower(str_replace('-','',$fw->get('ENCODING'))).';');
  399. 		parent::__construct($dsn,$user,$pw,$options);
  400. 		$this->engine=parent::getattribute(parent::ATTR_DRIVER_NAME);
  401. 	}
  402.  
  403. }