PageRenderTime 58ms CodeModel.GetById 27ms RepoModel.GetById 1ms app.codeStats 0ms

/lib/Varien/Db/Tree.php

https://bitbucket.org/claudiu_marginean/magento-hg-mirror
PHP | 561 lines | 367 code | 88 blank | 106 comment | 55 complexity | c7cc99ac052aa5d1ac721619d47506e6 MD5 | raw file
Possible License(s): CC-BY-SA-3.0, LGPL-2.1, GPL-2.0, WTFPL
  1. <?php
  2. /**
  3. * Magento
  4. *
  5. * NOTICE OF LICENSE
  6. *
  7. * This source file is subject to the Open Software License (OSL 3.0)
  8. * that is bundled with this package in the file LICENSE.txt.
  9. * It is also available through the world-wide-web at this URL:
  10. * http://opensource.org/licenses/osl-3.0.php
  11. * If you did not receive a copy of the license and are unable to
  12. * obtain it through the world-wide-web, please send an email
  13. * to license@magentocommerce.com so we can send you a copy immediately.
  14. *
  15. * DISCLAIMER
  16. *
  17. * Do not edit or add to this file if you wish to upgrade Magento to newer
  18. * versions in the future. If you wish to customize Magento for your
  19. * needs please refer to http://www.magentocommerce.com for more information.
  20. *
  21. * @category Varien
  22. * @package Varien_Db
  23. * @copyright Copyright (c) 2008 Irubin Consulting Inc. DBA Varien (http://www.varien.com)
  24. * @license http://opensource.org/licenses/osl-3.0.php Open Software License (OSL 3.0)
  25. */
  26. /**
  27. * Varien Library
  28. *
  29. *
  30. * @category Varien
  31. * @package Varien_Db
  32. * @author Magento Core Team <core@magentocommerce.com>
  33. */
  34. require_once 'Varien/Db/Tree/Exception.php';
  35. Zend_Loader::loadClass('Zend_Db_Select');
  36. Zend_Loader::loadClass('Varien_Db_Tree_Node');
  37. Zend_Loader::loadClass('Varien_Db_Tree_NodeSet');
  38. class Varien_Db_Tree
  39. {
  40. private $_id;
  41. private $_left;
  42. private $_right;
  43. private $_level;
  44. private $_pid;
  45. private $_nodesInfo = array();
  46. /**
  47. * Array of additional tables
  48. *
  49. * array(
  50. * [$tableName] => array(
  51. * ['joinCondition']
  52. * ['fields']
  53. * )
  54. * )
  55. *
  56. * @var array
  57. */
  58. private $_extTables = array();
  59. /**
  60. * Zend_Db_Adapter
  61. *
  62. * @var Zend_Db_Adapter_Abstract
  63. */
  64. private $_db;
  65. private $_table;
  66. function __construct($config = array())
  67. {
  68. // set a Zend_Db_Adapter connection
  69. if (! empty($config['db'])) {
  70. // convenience variable
  71. $db = $config['db'];
  72. // use an object from the registry?
  73. if (is_string($db)) {
  74. $db = Zend::registry($db);
  75. }
  76. // make sure it's a Zend_Db_Adapter
  77. if (! $db instanceof Zend_Db_Adapter_Abstract) {
  78. throw new Varien_Db_Tree_Exception('db object does not implement Zend_Db_Adapter_Abstract');
  79. }
  80. // save the connection
  81. $this->_db = $db;
  82. $conn = $this->_db->getConnection();
  83. if ($conn instanceof PDO) {
  84. $conn->setAttribute (PDO::ATTR_EMULATE_PREPARES, true);
  85. } elseif ($conn instanceof mysqli) {
  86. //TODO: ???
  87. }
  88. } else {
  89. throw new Varien_Db_Tree_Exception('db object is not set in config');
  90. }
  91. if (!empty($config['table'])) {
  92. $this->setTable($config['table']);
  93. }
  94. if (!empty($config['id'])) {
  95. $this->setIdField($config['id']);
  96. } else {
  97. $this->setIdField('id');
  98. }
  99. if (!empty($config['left'])) {
  100. $this->setLeftField($config['left']);
  101. } else {
  102. $this->setLeftField('left_key');
  103. }
  104. if (!empty($config['right'])) {
  105. $this->setRightField($config['right']);
  106. } else {
  107. $this->setRightField('right_key');
  108. }
  109. if (!empty($config['level'])) {
  110. $this->setLevelField($config['level']);
  111. } else {
  112. $this->setLevelField('level');
  113. }
  114. if (!empty($config['pid'])) {
  115. $this->setPidField($config['pid']);
  116. } else {
  117. $this->setPidField('parent_id');
  118. }
  119. }
  120. /**
  121. * set name of id field
  122. *
  123. * @param string $name
  124. * @return Varien_Db_Tree
  125. */
  126. public function setIdField($name) {
  127. $this->_id = $name;
  128. return $this;
  129. }
  130. /**
  131. * set name of left field
  132. *
  133. * @param string $name
  134. * @return Varien_Db_Tree
  135. */
  136. public function setLeftField($name) {
  137. $this->_left = $name;
  138. return $this;
  139. }
  140. /**
  141. * set name of right field
  142. *
  143. * @param string $name
  144. * @return Varien_Db_Tree
  145. */
  146. public function setRightField($name) {
  147. $this->_right = $name;
  148. return $this;
  149. }
  150. /**
  151. * set name of level field
  152. *
  153. * @param string $name
  154. * @return Varien_Db_Tree
  155. */
  156. public function setLevelField($name) {
  157. $this->_level = $name;
  158. return $this;
  159. }
  160. /**
  161. * set name of pid Field
  162. *
  163. * @param string $name
  164. * @return Varien_Db_Tree
  165. */
  166. public function setPidField($name) {
  167. $this->_pid = $name;
  168. return $this;
  169. }
  170. /**
  171. * set table name
  172. *
  173. * @param string $name
  174. * @return Varien_Db_Tree
  175. */
  176. public function setTable($name) {
  177. $this->_table = $name;
  178. return $this;
  179. }
  180. public function getKeys() {
  181. $keys = array();
  182. $keys['id'] = $this->_id;
  183. $keys['left'] = $this->_left;
  184. $keys['right'] = $this->_right;
  185. $keys['pid'] = $this->_pid;
  186. $keys['level'] = $this->_level;
  187. return $keys;
  188. }
  189. /**
  190. * Cleare table and add root element
  191. *
  192. */
  193. public function clear($data = array())
  194. {
  195. // clearing table
  196. $this->_db->query('TRUNCATE '. $this->_table);
  197. //$this->_db->delete($this->_table,'');
  198. // prepare data for root element
  199. $data[$this->_pid] = 0;
  200. $data[$this->_left] = 1;
  201. $data[$this->_right] = 2;
  202. $data[$this->_level] = 0;
  203. try {
  204. $this->_db->insert($this->_table, $data);
  205. } catch (PDOException $e) {
  206. echo $e->getMessage();
  207. }
  208. return $this->_db->lastInsertId();
  209. }
  210. public function getNodeInfo($ID) {
  211. if (empty($this->_nodesInfo[$ID])) {
  212. $sql = 'SELECT * FROM '.$this->_table.' WHERE '.$this->_id.'=:id';
  213. $res = $this->_db->query($sql, array('id' => $ID));
  214. $data = $res->fetch();
  215. $this->_nodesInfo[$ID] = $data;
  216. } else {
  217. $data = $this->_nodesInfo[$ID];
  218. }
  219. return $data;
  220. }
  221. public function appendChild($ID, $data) {
  222. if (!$info = $this->getNodeInfo($ID)) {
  223. return false;
  224. }
  225. $data[$this->_left] = $info[$this->_right];
  226. $data[$this->_right] = $info[$this->_right] + 1;
  227. $data[$this->_level] = $info[$this->_level] + 1;
  228. $data[$this->_pid] = $ID;
  229. // creating a place for the record being inserted
  230. if($ID) {
  231. $this->_db->beginTransaction();
  232. try {
  233. $sql = 'UPDATE '.$this->_table.' SET'
  234. . ' `'.$this->_left.'` = IF( `'.$this->_left.'` > :left, `'.$this->_left.'`+2, `'.$this->_left.'`),'
  235. . ' `'.$this->_right.'` = IF( `'.$this->_right.'`>= :right, `'.$this->_right.'`+2, `'.$this->_right.'`)'
  236. . ' WHERE `'.$this->_right.'` >= :right';
  237. $this->_db->query($sql, array('left'=>$info[$this->_left], 'right'=>$info[$this->_right]));
  238. $this->_db->insert($this->_table, $data);
  239. $this->_db->commit();
  240. } catch (PDOException $p) {
  241. $this->_db->rollBack();
  242. echo $p->getMessage();
  243. exit();
  244. } catch (Exception $e) {
  245. $this->_db->rollBack();
  246. echo $e->getMessage();
  247. echo $sql;
  248. var_dump($data);
  249. exit();
  250. }
  251. // TODO: change to ZEND LIBRARY
  252. $res = $this->_db->fetchOne('select last_insert_id()');
  253. return $res;
  254. //return $this->_db->fetchOne('select last_insert_id()');
  255. //return $this->_db->lastInsertId();
  256. }
  257. return false;
  258. }
  259. public function checkNodes() {
  260. $sql = $this->_db->select();
  261. $sql->from(array('t1'=>$this->_table), array('t1.'.$this->_id, new Zend_Db_Expr('COUNT(t1.'.$this->_id.') AS rep')))
  262. ->from(array('t2'=>$this->_table))
  263. ->from(array('t3'=>$this->_table), new Zend_Db_Expr('MAX(t3.'.$this->_right.') AS max_right'));
  264. $sql->where('t1.'.$this->_left.' <> t2.'.$this->_left)
  265. ->where('t1.'.$this->_left.' <> t2.'.$this->_right)
  266. ->where('t1.'.$this->_right.' <> t2.'.$this->_right);
  267. $sql->group('t1.'.$this->_id);
  268. $sql->having('max_right <> SQRT(4 * rep + 1) + 1');
  269. return $this->_db->fetchAll($sql);
  270. }
  271. public function insertBefore($ID, $data) {
  272. }
  273. public function removeNode($ID) {
  274. if (!$info = $this->getNodeInfo($ID)) {
  275. return false;
  276. }
  277. if($ID) {
  278. $this->_db->beginTransaction();
  279. try {
  280. // DELETE FROM my_tree WHERE left_key >= $left_key AND right_key <= $right_key
  281. $this->_db->delete($this->_table, $this->_left.' >= '.$info[$this->_left].' AND '.$this->_right.' <= '.$info[$this->_right]);
  282. // UPDATE my_tree SET left_key = IF(left_key > $left_key, left_key – ($right_key - $left_key + 1), left_key), right_key = right_key – ($right_key - $left_key + 1) WHERE right_key > $right_key
  283. $sql = 'UPDATE '.$this->_table.'
  284. SET
  285. '.$this->_left.' = IF('.$this->_left.' > '.$info[$this->_left].', '.$this->_left.' - '.($info[$this->_right] - $info[$this->_left] + 1).', '.$this->_left.'),
  286. '.$this->_right.' = '.$this->_right.' - '.($info[$this->_right] - $info[$this->_left] + 1).'
  287. WHERE
  288. '.$this->_right.' > '.$info[$this->_right];
  289. $this->_db->query($sql);
  290. $this->_db->commit();
  291. return new Varien_Db_Tree_Node($info, $this->getKeys());;
  292. } catch (Exception $e) {
  293. $this->_db->rollBack();
  294. echo $e->getMessage();
  295. }
  296. }
  297. }
  298. public function moveNode($eId, $pId, $aId = 0) {
  299. $eInfo = $this->getNodeInfo($eId);
  300. $pInfo = $this->getNodeInfo($pId);
  301. $leftId = $eInfo[$this->_left];
  302. $rightId = $eInfo[$this->_right];
  303. $level = $eInfo[$this->_level];
  304. $leftIdP = $pInfo[$this->_left];
  305. $rightIdP = $pInfo[$this->_right];
  306. $levelP = $pInfo[$this->_level];
  307. if ($eId == $pId || $leftId == $leftIdP || ($leftIdP >= $leftId && $leftIdP <= $rightId) || ($level == $levelP+1 && $leftId > $leftIdP && $rightId < $rightIdP)) {
  308. echo "alert('cant_move_tree');";
  309. return FALSE;
  310. }
  311. if ($leftIdP < $leftId && $rightIdP > $rightId && $levelP < $level - 1) {
  312. $sql = 'UPDATE '.$this->_table.' SET '
  313. . $this->_level . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_level.sprintf('%+d', -($level-1)+$levelP) . ' ELSE ' . $this->_level . ' END, '
  314. . $this->_right . ' = CASE WHEN ' . $this->_right . ' BETWEEN ' . ($rightId+1) . ' AND ' . ($rightIdP-1) . ' THEN ' . $this->_right . '-' . ($rightId-$leftId+1) . ' '
  315. . 'WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_right . '+' . ((($rightIdP-$rightId-$level+$levelP)/2)*2+$level-$levelP-1) . ' ELSE ' . $this->_right . ' END, '
  316. . $this->_left . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . ($rightId+1) . ' AND ' . ($rightIdP-1) . ' THEN ' . $this->_left . '-' . ($rightId-$leftId+1) . ' '
  317. . 'WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_left . '+' . ((($rightIdP-$rightId-$level+$levelP)/2)*2+$level-$levelP-1) . ' ELSE ' . $this->_left . ' END '
  318. . 'WHERE ' . $this->_left . ' BETWEEN ' . ($leftIdP+1) . ' AND ' . ($rightIdP-1);
  319. } elseif ($leftIdP < $leftId) {
  320. $sql = 'UPDATE ' . $this->_table . ' SET '
  321. . $this->_level . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_level.sprintf('%+d', -($level-1)+$levelP) . ' ELSE ' . $this->_level . ' END, '
  322. . $this->_left . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $rightIdP . ' AND ' . ($leftId-1) . ' THEN ' . $this->_left . '+' . ($rightId-$leftId+1) . ' '
  323. . 'WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_left . '-' . ($leftId-$rightIdP) . ' ELSE ' . $this->_left . ' END, '
  324. . $this->_right . ' = CASE WHEN ' . $this->_right . ' BETWEEN ' . $rightIdP . ' AND ' . $leftId . ' THEN ' . $this->_right . '+' . ($rightId-$leftId+1) . ' '
  325. . 'WHEN ' . $this->_right . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_right . '-' . ($leftId-$rightIdP) . ' ELSE ' . $this->_right . ' END '
  326. . 'WHERE (' . $this->_left . ' BETWEEN ' . $leftIdP . ' AND ' . $rightId. ' '
  327. . 'OR ' . $this->_right . ' BETWEEN ' . $leftIdP . ' AND ' . $rightId . ')';
  328. } else {
  329. $sql = 'UPDATE ' . $this->_table . ' SET '
  330. . $this->_level . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_level.sprintf('%+d', -($level-1)+$levelP) . ' ELSE ' . $this->_level . ' END, '
  331. . $this->_left . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $rightId . ' AND ' . $rightIdP . ' THEN ' . $this->_left . '-' . ($rightId-$leftId+1) . ' '
  332. . 'WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_left . '+' . ($rightIdP-1-$rightId) . ' ELSE ' . $this->_left . ' END, '
  333. . $this->_right . ' = CASE WHEN ' . $this->_right . ' BETWEEN ' . ($rightId+1) . ' AND ' . ($rightIdP-1) . ' THEN ' . $this->_right . '-' . ($rightId-$leftId+1) . ' '
  334. . 'WHEN ' . $this->_right . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_right . '+' . ($rightIdP-1-$rightId) . ' ELSE ' . $this->_right . ' END '
  335. . 'WHERE (' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightIdP . ' '
  336. . 'OR ' . $this->_right . ' BETWEEN ' . $leftId . ' AND ' . $rightIdP . ')';
  337. }
  338. $this->_db->beginTransaction();
  339. try {
  340. $this->_db->query($sql);
  341. $this->_db->commit();
  342. echo "alert('node moved');";
  343. return true;
  344. } catch (Exception $e) {
  345. $this->_db->rollBack();
  346. echo "alert('node not moved: fatal error');";
  347. echo $e->getMessage();
  348. echo "<br>\r\n";
  349. echo $sql;
  350. echo "<br>\r\n";
  351. exit();
  352. }
  353. }
  354. public function __moveNode($eId, $pId, $aId = 0) {
  355. $eInfo = $this->getNodeInfo($eId);
  356. if ($pId != 0) {
  357. $pInfo = $this->getNodeInfo($pId);
  358. }
  359. if ($aId != 0) {
  360. $aInfo = $this->getNodeInfo($aId);
  361. }
  362. $level = $eInfo[$this->_level];
  363. $left_key = $eInfo[$this->_left];
  364. $right_key = $eInfo[$this->_right];
  365. if ($pId == 0) {
  366. $level_up = 0;
  367. } else {
  368. $level_up = $pInfo[$this->_level];
  369. }
  370. $right_key_near = 0;
  371. $left_key_near = 0;
  372. if ($pId == 0) { //move to root
  373. $right_key_near = $this->_db->fetchOne('SELECT MAX('.$this->_right.') FROM '.$this->_table);
  374. } elseif ($aId != 0 && $pID == $eInfo[$this->_pid]) { // if we have after ID
  375. $right_key_near = $aInfo[$this->_right];
  376. $left_key_near = $aInfo[$this->_left];
  377. } elseif ($aId == 0 && $pId == $eInfo[$this->_pid]) { // if we do not have after ID
  378. $right_key_near = $pInfo[$this->_left];
  379. } elseif ($pId != $eInfo[$this->_pid]) {
  380. $right_key_near = $pInfo[$this->_right] - 1;
  381. }
  382. $skew_level = $pInfo[$this->_level] - $eInfo[$this->_level] + 1;
  383. $skew_tree = $eInfo[$this->_right] - $eInfo[$this->_left] + 1;
  384. echo "alert('".$right_key_near."');";
  385. if ($right_key_near > $right_key) { // up
  386. echo "alert('move up');";
  387. $skew_edit = $right_key_near - $left_key + 1;
  388. $sql = 'UPDATE '.$this->_table.'
  389. SET
  390. '.$this->_right.' = IF('.$this->_left.' >= '.$eInfo[$this->_left].', '.$this->_right.' + '.$skew_edit.', IF('.$this->_right.' < '.$eInfo[$this->_left].', '.$this->_right.' + '.$skew_tree.', '.$this->_right.')),
  391. '.$this->_level.' = IF('.$this->_left.' >= '.$eInfo[$this->_left].', '.$this->_level.' + '.$skew_level.', '.$this->_level.'),
  392. '.$this->_left.' = IF('.$this->_left.' >= '.$eInfo[$this->_left].', '.$this->_left.' + '.$skew_edit.', IF('.$this->_left.' > '.$right_key_near.', '.$this->_left.' + '.$skew_tree.', '.$this->_left.'))
  393. WHERE '.$this->_right.' > '.$right_key_near.' AND '.$this->_left.' < '.$eInfo[$this->_right];
  394. } elseif ($right_key_near < $right_key) { // down
  395. echo "alert('move down');";
  396. $skew_edit = $right_key_near - $left_key + 1 - $skew_tree;
  397. $sql = 'UPDATE '.$this->_table.'
  398. SET
  399. '.$this->_left.' = IF('.$this->_right.' <= '.$right_key.', '.$this->_left.' + '.$skew_edit.', IF('.$this->_left.' > '.$right_key.', '.$this->_left.' - '.$skew_tree.', '.$this->_left.')),
  400. '.$this->_level.' = IF('.$this->_right.' <= '.$right_key.', '.$this->_level.' + '.$skew_level.', '.$this->_level.'),
  401. '.$this->_right.' = IF('.$this->_right.' <= '.$right_key.', '.$this->_right.' + '.$skew_edit.', IF('.$this->_right.' <= '.$right_key_near.', '.$this->_right.' - '.$skew_tree.', '.$this->_right.'))
  402. WHERE
  403. '.$this->_right.' > '.$left_key.' AND '.$this->_left.' <= '.$right_key_near;
  404. }
  405. $this->_db->beginTransaction();
  406. try {
  407. $this->_db->query($sql);
  408. //$afrows = $this->_db->get
  409. $this->_db->commit();
  410. } catch (Exception $e) {
  411. $this->_db->rollBack();
  412. echo $e->getMessage();
  413. echo "<br>\r\n";
  414. echo $sql;
  415. echo "<br>\r\n";
  416. exit();
  417. }
  418. echo "alert('node added')";
  419. }
  420. public function addTable($tableName, $joinCondition, $fields='*')
  421. {
  422. $this->_extTables[$tableName] = array(
  423. 'joinCondition' => $joinCondition,
  424. 'fields' => $fields
  425. );
  426. }
  427. protected function _addExtTablesToSelect(Zend_Db_Select &$select)
  428. {
  429. foreach ($this->_extTables as $tableName=>$info) {
  430. $select->joinInner($tableName, $info['joinCondition'], $info['fields']);
  431. }
  432. }
  433. public function getChildren($ID, $start_level = 0, $end_level = 0)
  434. {
  435. try {
  436. $info = $this->getNodeInfo($ID);
  437. } catch (Exception $e) {
  438. echo $e->getMessage();
  439. exit;
  440. }
  441. $dbSelect = new Zend_Db_Select($this->_db);
  442. $dbSelect->from($this->_table)
  443. ->where($this->_left . ' >= :left')
  444. ->where($this->_right . ' <= :right')
  445. ->order($this->_left);
  446. $this->_addExtTablesToSelect($dbSelect);
  447. $data = array();
  448. $data['left'] = $info[$this->_left];
  449. $data['right'] = $info[$this->_right];
  450. if (!empty($start_level) && empty($end_level)) {
  451. $dbSelect->where($this->_level . ' = :minLevel');
  452. $data['minLevel'] = $info[$this->_level] + $start_level;
  453. }
  454. //echo $dbSelect->__toString();
  455. $data = $this->_db->fetchAll($dbSelect, $data);
  456. $nodeSet = new Varien_Db_Tree_NodeSet();
  457. foreach ($data as $node) {
  458. $nodeSet->addNode(new Varien_Db_Tree_Node($node, $this->getKeys()));
  459. }
  460. return $nodeSet;
  461. }
  462. public function getNode($nodeId)
  463. {
  464. $dbSelect = new Zend_Db_Select($this->_db);
  465. $dbSelect->from($this->_table)
  466. ->where($this->_table.'.'.$this->_id . ' >= :id');
  467. $this->_addExtTablesToSelect($dbSelect);
  468. $data = array();
  469. $data['id'] = $nodeId;
  470. $data = $this->_db->fetchRow($dbSelect, $data);
  471. return new Varien_Db_Tree_Node($data, $this->getKeys());
  472. }
  473. }