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

Конкурс дизайнеров на 15000 рублей
Сервис интернет рекламы
Размер файла: 13.23Kb 
  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\SQL;
  17.  
  18. //! SQL data mapper
  19. class Mapper extends \DB\Cursor {
  20.  
  21. 	//@{ Error messages
  22. 	const
  23. 		E_Adhoc='Unable to process ad hoc field %s';
  24. 	//@}
  25.  
  26. 	protected
  27. 		//! PDO wrapper
  28. 		$db,
  29. 		//! Database engine
  30. 		$engine,
  31. 		//! SQL table
  32. 		$source,
  33. 		//! SQL table (quoted)
  34. 		$table,
  35. 		//! Last insert ID
  36. 		$_id,
  37. 		//! Defined fields
  38. 		$fields,
  39. 		//! Adhoc fields
  40. 		$adhoc=array();
  41.  
  42. 	/**
  43. 	*	Return database type
  44. 	*	@return string
  45. 	**/
  46. 	function dbtype() {
  47. 		return 'SQL';
  48. 	}
  49.  
  50. 	/**
  51. 	*	Return TRUE if field is defined
  52. 	*	@return bool
  53. 	*	@param $key string
  54. 	**/
  55. 	function exists($key) {
  56. 		return array_key_exists($key,$this->fields+$this->adhoc);
  57. 	}
  58.  
  59. 	/**
  60. 	*	Assign value to field
  61. 	*	@return scalar
  62. 	*	@param $key string
  63. 	*	@param $val scalar
  64. 	**/
  65. 	function set($key,$val) {
  66. 		if (array_key_exists($key,$this->fields)) {
  67. 			$val=is_null($val) && $this->fields[$key]['nullable']?
  68. 				NULL:$this->db->value($this->fields[$key]['pdo_type'],$val);
  69. 			if ($this->fields[$key]['value']!==$val ||
  70. 				$this->fields[$key]['default']!==$val && is_null($val))
  71. 				$this->fields[$key]['changed']=TRUE;
  72. 			return $this->fields[$key]['value']=$val;
  73. 		}
  74. 		// Parenthesize expression in case it's a subquery
  75. 		$this->adhoc[$key]=array('expr'=>'('.$val.')','value'=>NULL);
  76. 		return $val;
  77. 	}
  78.  
  79. 	/**
  80. 	*	Retrieve value of field
  81. 	*	@return scalar
  82. 	*	@param $key string
  83. 	**/
  84. 	function get($key) {
  85. 		if ($key=='_id')
  86. 			return $this->_id;
  87. 		elseif (array_key_exists($key,$this->fields))
  88. 			return $this->fields[$key]['value'];
  89. 		elseif (array_key_exists($key,$this->adhoc))
  90. 			return $this->adhoc[$key]['value'];
  91. 		user_error(sprintf(self::E_Field,$key));
  92. 	}
  93.  
  94. 	/**
  95. 	*	Clear value of field
  96. 	*	@return NULL
  97. 	*	@param $key string
  98. 	**/
  99. 	function clear($key) {
  100. 		if (array_key_exists($key,$this->adhoc))
  101. 			unset($this->adhoc[$key]);
  102. 	}
  103.  
  104. 	/**
  105. 	*	Get PHP type equivalent of PDO constant
  106. 	*	@return string
  107. 	*	@param $pdo string
  108. 	**/
  109. 	function type($pdo) {
  110. 		switch ($pdo) {
  111. 			case \PDO::PARAM_NULL:
  112. 				return 'unset';
  113. 			case \PDO::PARAM_INT:
  114. 				return 'int';
  115. 			case \PDO::PARAM_BOOL:
  116. 				return 'bool';
  117. 			case \PDO::PARAM_STR:
  118. 				return 'string';
  119. 		}
  120. 	}
  121.  
  122. 	/**
  123. 	*	Convert array to mapper object
  124. 	*	@return object
  125. 	*	@param $row array
  126. 	**/
  127. 	protected function factory($row) {
  128. 		$mapper=clone($this);
  129. 		$mapper->reset();
  130. 		foreach ($row as $key=>$val) {
  131. 			if (array_key_exists($key,$this->fields))
  132. 				$var='fields';
  133. 			elseif (array_key_exists($key,$this->adhoc))
  134. 				$var='adhoc';
  135. 			else
  136. 				continue;
  137. 			$mapper->{$var}[$key]['value']=$val;
  138. 			if ($var=='fields' && $mapper->{$var}[$key]['pkey'])
  139. 				$mapper->{$var}[$key]['previous']=$val;
  140. 		}
  141. 		$mapper->query=array(clone($mapper));
  142. 		if (isset($mapper->trigger['load']))
  143. 			\Base::instance()->call($mapper->trigger['load'],$mapper);
  144. 		return $mapper;
  145. 	}
  146.  
  147. 	/**
  148. 	*	Return fields of mapper object as an associative array
  149. 	*	@return array
  150. 	*	@param $obj object
  151. 	**/
  152. 	function cast($obj=NULL) {
  153. 		if (!$obj)
  154. 			$obj=$this;
  155. 		return array_map(
  156. 			function($row) {
  157. 				return $row['value'];
  158. 			},
  159. 			$obj->fields+$obj->adhoc
  160. 		);
  161. 	}
  162.  
  163. 	/**
  164. 	*	Build query string and execute
  165. 	*	@return array
  166. 	*	@param $fields string
  167. 	*	@param $filter string|array
  168. 	*	@param $options array
  169. 	*	@param $ttl int
  170. 	**/
  171. 	function select($fields,$filter=NULL,array $options=NULL,$ttl=0) {
  172. 		if (!$options)
  173. 			$options=array();
  174. 		$options+=array(
  175. 			'group'=>NULL,
  176. 			'order'=>NULL,
  177. 			'limit'=>0,
  178. 			'offset'=>0
  179. 		);
  180. 		$sql='SELECT '.$fields.' FROM '.$this->table;
  181. 		$args=array();
  182. 		if ($filter) {
  183. 			if (is_array($filter)) {
  184. 				$args=isset($filter[1]) && is_array($filter[1])?
  185. 					$filter[1]:
  186. 					array_slice($filter,1,NULL,TRUE);
  187. 				$args=is_array($args)?$args:array(1=>$args);
  188. 				list($filter)=$filter;
  189. 			}
  190. 			$sql.=' WHERE '.$filter;
  191. 		}
  192. 		$db=$this->db;
  193. 		if ($options['group'])
  194. 			$sql.=' GROUP BY '.implode(',',array_map(
  195. 				function($str) use($db) {
  196. 					return preg_match('/^(\w+)(?:\h+HAVING|\h*(?:,|$))/i',
  197. 						$str,$parts)?
  198. 						($db->quotekey($parts[1]).
  199. 						(isset($parts[2])?(' '.$parts[2]):'')):$str;
  200. 				},
  201. 				explode(',',$options['group'])));
  202. 		if ($options['order']) {
  203. 			$sql.=' ORDER BY '.implode(',',array_map(
  204. 				function($str) use($db) {
  205. 					return preg_match('/^(\w+)(?:\h+(ASC|DESC))?\h*(?:,|$)/i',
  206. 						$str,$parts)?
  207. 						($db->quotekey($parts[1]).
  208. 						(isset($parts[2])?(' '.$parts[2]):'')):$str;
  209. 				},
  210. 				explode(',',$options['order'])));
  211. 		}
  212. 		if ($options['limit'])
  213. 			$sql.=' LIMIT '.(int)$options['limit'];
  214. 		if ($options['offset'])
  215. 			$sql.=' OFFSET '.(int)$options['offset'];
  216. 		$result=$this->db->exec($sql,$args,$ttl);
  217. 		$out=array();
  218. 		foreach ($result as &$row) {
  219. 			foreach ($row as $field=>&$val) {
  220. 				if (array_key_exists($field,$this->fields)) {
  221. 					if (!is_null($val) || !$this->fields[$field]['nullable'])
  222. 						$val=$this->db->value(
  223. 							$this->fields[$field]['pdo_type'],$val);
  224. 				}
  225. 				elseif (array_key_exists($field,$this->adhoc))
  226. 					$this->adhoc[$field]['value']=$val;
  227. 				unset($val);
  228. 			}
  229. 			$out[]=$this->factory($row);
  230. 			unset($row);
  231. 		}
  232. 		return $out;
  233. 	}
  234.  
  235. 	/**
  236. 	*	Return records that match criteria
  237. 	*	@return array
  238. 	*	@param $filter string|array
  239. 	*	@param $options array
  240. 	*	@param $ttl int
  241. 	**/
  242. 	function find($filter=NULL,array $options=NULL,$ttl=0) {
  243. 		if (!$options)
  244. 			$options=array();
  245. 		$options+=array(
  246. 			'group'=>NULL,
  247. 			'order'=>NULL,
  248. 			'limit'=>0,
  249. 			'offset'=>0
  250. 		);
  251. 		$adhoc='';
  252. 		foreach ($this->adhoc as $key=>$field)
  253. 			$adhoc.=','.$field['expr'].' AS '.$this->db->quotekey($key);
  254. 		return $this->select(implode(',',
  255. 			array_map(array($this->db,'quotekey'),array_keys($this->fields))).
  256. 			$adhoc,$filter,$options,$ttl);
  257. 	}
  258.  
  259. 	/**
  260. 	*	Count records that match criteria
  261. 	*	@return int
  262. 	*	@param $filter string|array
  263. 	*	@param $ttl int
  264. 	**/
  265. 	function count($filter=NULL,$ttl=0) {
  266. 		$sql='SELECT COUNT(*) AS '.
  267. 			$this->db->quotekey('rows').' FROM '.$this->table;
  268. 		$args=array();
  269. 		if ($filter) {
  270. 			if (is_array($filter)) {
  271. 				$args=isset($filter[1]) && is_array($filter[1])?
  272. 					$filter[1]:
  273. 					array_slice($filter,1,NULL,TRUE);
  274. 				$args=is_array($args)?$args:array(1=>$args);
  275. 				list($filter)=$filter;
  276. 			}
  277. 			$sql.=' WHERE '.$filter;
  278. 		}
  279. 		$result=$this->db->exec($sql,$args,$ttl);
  280. 		return $result[0]['rows'];
  281. 	}
  282.  
  283. 	/**
  284. 	*	Return record at specified offset using same criteria as
  285. 	*	previous load() call and make it active
  286. 	*	@return array
  287. 	*	@param $ofs int
  288. 	**/
  289. 	function skip($ofs=1) {
  290. 		$out=parent::skip($ofs);
  291. 		$dry=$this->dry();
  292. 		foreach ($this->fields as $key=>&$field) {
  293. 			$field['value']=$dry?NULL:$out->fields[$key]['value'];
  294. 			$field['changed']=FALSE;
  295. 			if ($field['pkey'])
  296. 				$field['previous']=$dry?NULL:$out->fields[$key]['value'];
  297. 			unset($field);
  298. 		}
  299. 		foreach ($this->adhoc as $key=>&$field) {
  300. 			$field['value']=$dry?NULL:$out->adhoc[$key]['value'];
  301. 			unset($field);
  302. 		}
  303. 		if (isset($this->trigger['load']))
  304. 			\Base::instance()->call($this->trigger['load'],$this);
  305. 		return $out;
  306. 	}
  307.  
  308. 	/**
  309. 	*	Insert new record
  310. 	*	@return object
  311. 	**/
  312. 	function insert() {
  313. 		$args=array();
  314. 		$ctr=0;
  315. 		$fields='';
  316. 		$values='';
  317. 		$filter='';
  318. 		$pkeys=array();
  319. 		$nkeys=array();
  320. 		$ckeys=array();
  321. 		$inc=NULL;
  322. 		foreach ($this->fields as $key=>$field)
  323. 			if ($field['pkey'])
  324. 				$pkeys[$key]=$field['previous'];
  325. 		if (isset($this->trigger['beforeinsert']))
  326. 			\Base::instance()->call($this->trigger['beforeinsert'],
  327. 				array($this,$pkeys));
  328. 		foreach ($this->fields as $key=>&$field) {
  329. 			if ($field['pkey']) {
  330. 				$field['previous']=$field['value'];
  331. 				if (!$inc && $field['pdo_type']==\PDO::PARAM_INT &&
  332. 					empty($field['value']) && !$field['nullable'])
  333. 					$inc=$key;
  334. 				$filter.=($filter?' AND ':'').$this->db->quotekey($key).'=?';
  335. 				$nkeys[$ctr+1]=array($field['value'],$field['pdo_type']);
  336. 			}
  337. 			if ($field['changed'] && $key!=$inc) {
  338. 				$fields.=($ctr?',':'').$this->db->quotekey($key);
  339. 				$values.=($ctr?',':'').'?';
  340. 				$args[$ctr+1]=array($field['value'],$field['pdo_type']);
  341. 				$ctr++;
  342. 				$ckeys[]=$key;
  343. 			}
  344. 			$field['changed']=FALSE;
  345. 			unset($field);
  346. 		}
  347. 		if ($fields) {
  348. 			$this->db->exec(
  349. 				(preg_match('/mssql|dblib|sqlsrv/',$this->engine) &&
  350. 				array_intersect(array_keys($pkeys),$ckeys)?
  351. 					'SET IDENTITY_INSERT '.$this->table.' ON;':'').
  352. 				'INSERT INTO '.$this->table.' ('.$fields.') '.
  353. 				'VALUES ('.$values.')',$args
  354. 			);
  355. 			$seq=NULL;
  356. 			if ($this->engine=='pgsql') {
  357. 				$names=array_keys($pkeys);
  358. 				$seq=$this->source.'_'.end($names).'_seq';
  359. 			}
  360. 			if ($this->engine!='oci')
  361. 				$this->_id=$this->db->lastinsertid($seq);
  362. 			// Reload to obtain default and auto-increment field values
  363. 			$this->load($inc?
  364. 				array($inc.'=?',$this->db->value(
  365. 					$this->fields[$inc]['pdo_type'],$this->_id)):
  366. 				array($filter,$nkeys));
  367. 			if (isset($this->trigger['afterinsert']))
  368. 				\Base::instance()->call($this->trigger['afterinsert'],
  369. 					array($this,$pkeys));
  370. 		}
  371. 		return $this;
  372. 	}
  373.  
  374. 	/**
  375. 	*	Update current record
  376. 	*	@return object
  377. 	**/
  378. 	function update() {
  379. 		$args=array();
  380. 		$ctr=0;
  381. 		$pairs='';
  382. 		$filter='';
  383. 		$pkeys=array();
  384. 		foreach ($this->fields as $key=>$field)
  385. 			if ($field['pkey'])
  386. 				$pkeys[$key]=$field['previous'];
  387. 		if (isset($this->trigger['beforeupdate']))
  388. 			\Base::instance()->call($this->trigger['beforeupdate'],
  389. 				array($this,$pkeys));
  390. 		foreach ($this->fields as $key=>$field)
  391. 			if ($field['changed']) {
  392. 				$pairs.=($pairs?',':'').$this->db->quotekey($key).'=?';
  393. 				$args[$ctr+1]=array($field['value'],$field['pdo_type']);
  394. 				$ctr++;
  395. 			}
  396. 		foreach ($this->fields as $key=>$field)
  397. 			if ($field['pkey']) {
  398. 				$filter.=($filter?' AND ':'').$this->db->quotekey($key).'=?';
  399. 				$args[$ctr+1]=array($field['previous'],$field['pdo_type']);
  400. 				$ctr++;
  401. 			}
  402. 		if ($pairs) {
  403. 			$sql='UPDATE '.$this->table.' SET '.$pairs;
  404. 			if ($filter)
  405. 				$sql.=' WHERE '.$filter;
  406. 			$this->db->exec($sql,$args);
  407. 			if (isset($this->trigger['afterupdate']))
  408. 				\Base::instance()->call($this->trigger['afterupdate'],
  409. 					array($this,$pkeys));
  410. 		}
  411. 		return $this;
  412. 	}
  413.  
  414. 	/**
  415. 	*	Delete current record
  416. 	*	@return int
  417. 	*	@param $filter string|array
  418. 	**/
  419. 	function erase($filter=NULL) {
  420. 		if ($filter) {
  421. 			$args=array();
  422. 			if (is_array($filter)) {
  423. 				$args=isset($filter[1]) && is_array($filter[1])?
  424. 					$filter[1]:
  425. 					array_slice($filter,1,NULL,TRUE);
  426. 				$args=is_array($args)?$args:array(1=>$args);
  427. 				list($filter)=$filter;
  428. 			}
  429. 			return $this->db->
  430. 				exec('DELETE FROM '.$this->table.' WHERE '.$filter.';',$args);
  431. 		}
  432. 		$args=array();
  433. 		$ctr=0;
  434. 		$filter='';
  435. 		$pkeys=array();
  436. 		foreach ($this->fields as $key=>&$field) {
  437. 			if ($field['pkey']) {
  438. 				$filter.=($filter?' AND ':'').$this->db->quotekey($key).'=?';
  439. 				$args[$ctr+1]=array($field['previous'],$field['pdo_type']);
  440. 				$pkeys[$key]=$field['previous'];
  441. 				$ctr++;
  442. 			}
  443. 			$field['value']=NULL;
  444. 			$field['changed']=(bool)$field['default'];
  445. 			if ($field['pkey'])
  446. 				$field['previous']=NULL;
  447. 			unset($field);
  448. 		}
  449. 		foreach ($this->adhoc as &$field) {
  450. 			$field['value']=NULL;
  451. 			unset($field);
  452. 		}
  453. 		parent::erase();
  454. 		$this->skip(0);
  455. 		if (isset($this->trigger['beforeerase']))
  456. 			\Base::instance()->call($this->trigger['beforeerase'],
  457. 				array($this,$pkeys));
  458. 		$out=$this->db->
  459. 			exec('DELETE FROM '.$this->table.' WHERE '.$filter.';',$args);
  460. 		if (isset($this->trigger['aftererase']))
  461. 			\Base::instance()->call($this->trigger['aftererase'],
  462. 				array($this,$pkeys));
  463. 		return $out;
  464. 	}
  465.  
  466. 	/**
  467. 	*	Reset cursor
  468. 	*	@return NULL
  469. 	**/
  470. 	function reset() {
  471. 		foreach ($this->fields as &$field) {
  472. 			$field['value']=NULL;
  473. 			$field['changed']=FALSE;
  474. 			if ($field['pkey'])
  475. 				$field['previous']=NULL;
  476. 			unset($field);
  477. 		}
  478. 		foreach ($this->adhoc as &$field) {
  479. 			$field['value']=NULL;
  480. 			unset($field);
  481. 		}
  482. 		parent::reset();
  483. 	}
  484.  
  485. 	/**
  486. 	*	Hydrate mapper object using hive array variable
  487. 	*	@return NULL
  488. 	*	@param $key string
  489. 	*	@param $func callback
  490. 	**/
  491. 	function copyfrom($key,$func=NULL) {
  492. 		$var=\Base::instance()->get($key);
  493. 		if ($func)
  494. 			$var=$func($var);
  495. 		foreach ($var as $key=>$val)
  496. 			if (in_array($key,array_keys($this->fields))) {
  497. 				$field=&$this->fields[$key];
  498. 				if ($field['value']!==$val) {
  499. 					$field['value']=$val;
  500. 					$field['changed']=TRUE;
  501. 				}
  502. 				unset($field);
  503. 			}
  504. 	}
  505.  
  506. 	/**
  507. 	*	Populate hive array variable with mapper fields
  508. 	*	@return NULL
  509. 	*	@param $key string
  510. 	**/
  511. 	function copyto($key) {
  512. 		$var=&\Base::instance()->ref($key);
  513. 		foreach ($this->fields+$this->adhoc as $key=>$field)
  514. 			$var[$key]=$field['value'];
  515. 	}
  516.  
  517. 	/**
  518. 	*	Return schema
  519. 	*	@return array
  520. 	**/
  521. 	function schema() {
  522. 		return $this->fields;
  523. 	}
  524.  
  525. 	/**
  526. 	*	Return field names
  527. 	*	@return array
  528. 	*	@param $adhoc bool
  529. 	**/
  530. 	function fields($adhoc=TRUE) {
  531. 		return array_keys($this->fields+($adhoc?$this->adhoc:array()));
  532. 	}
  533.  
  534. 	/**
  535. 	*	Instantiate class
  536. 	*	@param $db object
  537. 	*	@param $table string
  538. 	*	@param $fields array|string
  539. 	*	@param $ttl int
  540. 	**/
  541. 	function __construct(\DB\SQL $db,$table,$fields=NULL,$ttl=60) {
  542. 		$this->db=$db;
  543. 		$this->engine=$db->driver();
  544. 		if ($this->engine=='oci')
  545. 			$table=strtoupper($table);
  546. 		$this->source=$table;
  547. 		$this->table=$this->db->quotekey($table);
  548. 		$this->fields=$db->schema($table,$fields,$ttl);
  549. 		$this->reset();
  550. 	}
  551.  
  552. }