Magento: lib/Varien/Db/Tree.php Source File

00001 <?php
00002 /**
00003  * Magento
00004  *
00005  * NOTICE OF LICENSE
00006  *
00007  * This source file is subject to the Open Software License (OSL 3.0)
00008  * that is bundled with this package in the file LICENSE.txt.
00009  * It is also available through the world-wide-web at this URL:
00010  * http://opensource.org/licenses/osl-3.0.php
00011  * If you did not receive a copy of the license and are unable to
00012  * obtain it through the world-wide-web, please send an email
00013  * to license@magentocommerce.com so we can send you a copy immediately.
00014  *
00015  * DISCLAIMER
00016  *
00017  * Do not edit or add to this file if you wish to upgrade Magento to newer
00018  * versions in the future. If you wish to customize Magento for your
00019  * needs please refer to http://www.magentocommerce.com for more information.
00020  *
00021  * @category   Varien
00022  * @package    Varien_Db
00023  * @copyright  Copyright (c) 2008 Irubin Consulting Inc. DBA Varien (http://www.varien.com)
00024  * @license    http://opensource.org/licenses/osl-3.0.php  Open Software License (OSL 3.0)
00025  */
00026 
00027 /**
00028  * Varien Library
00029  *
00030  *
00031  * @category   Varien
00032  * @package    Varien_Db
00033  * @author      Magento Core Team <core@magentocommerce.com>
00034  */
00035 
00036 
00037 require_once 'Varien/Db/Tree/Exception.php';
00038 Zend_Loader::loadClass('Zend_Db_Select');
00039 Zend_Loader::loadClass('Varien_Db_Tree_Node');
00040 Zend_Loader::loadClass('Varien_Db_Tree_NodeSet');
00041 
00042 class Varien_Db_Tree
00043 {
00044 
00045     private $_id;
00046     private $_left;
00047     private $_right;
00048     private $_level;
00049     private $_pid;
00050     private $_nodesInfo = array();
00051 
00052     /**
00053      * Array of additional tables
00054      *
00055      * array(
00056      *  [$tableName] => array(
00057      *              ['joinCondition']
00058      *              ['fields']
00059      *          )
00060      * )
00061      *
00062      * @var array
00063      */
00064     private $_extTables = array();
00065 
00066     /**
00067      * Zend_Db_Adapter
00068      *
00069      * @var Zend_Db_Adapter_Abstract
00070      */
00071     private $_db;
00072 
00073     private $_table;
00074 
00075     function __construct($config = array())
00076     {
00077         // set a Zend_Db_Adapter connection
00078         if (! empty($config['db'])) {
00079 
00080             // convenience variable
00081             $db = $config['db'];
00082 
00083             // use an object from the registry?
00084             if (is_string($db)) {
00085                 $db = Zend::registry($db);
00086             }
00087 
00088             // make sure it's a Zend_Db_Adapter
00089             if (! $db instanceof Zend_Db_Adapter_Abstract) {
00090                 throw new Varien_Db_Tree_Exception('db object does not implement Zend_Db_Adapter_Abstract');
00091             }
00092 
00093             // save the connection
00094             $this->_db = $db;
00095             $conn = $this->_db->getConnection();
00096             if ($conn instanceof PDO) {
00097                 $conn->setAttribute (PDO::ATTR_EMULATE_PREPARES, true);
00098             } elseif ($conn instanceof mysqli) {
00099                 //TODO: ???
00100             }
00101         } else {
00102             throw new Varien_Db_Tree_Exception('db object is not set in config');
00103         }
00104 
00105 
00106         if (!empty($config['table'])) {
00107             $this->setTable($config['table']);
00108         }
00109 
00110         if (!empty($config['id'])) {
00111             $this->setIdField($config['id']);
00112         } else {
00113             $this->setIdField('id');
00114         }
00115 
00116         if (!empty($config['left'])) {
00117             $this->setLeftField($config['left']);
00118         } else {
00119             $this->setLeftField('left_key');
00120         }
00121 
00122         if (!empty($config['right'])) {
00123             $this->setRightField($config['right']);
00124         } else {
00125             $this->setRightField('right_key');
00126         }
00127 
00128         if (!empty($config['level'])) {
00129             $this->setLevelField($config['level']);
00130         } else {
00131             $this->setLevelField('level');
00132         }
00133 
00134 
00135         if (!empty($config['pid'])) {
00136             $this->setPidField($config['pid']);
00137         } else {
00138             $this->setPidField('parent_id');
00139         }
00140 
00141     }
00142 
00143     /**
00144      * set name of id field
00145      *
00146      * @param string $name
00147      * @return Varien_Db_Tree
00148      */
00149     public function setIdField($name) {
00150         $this->_id = $name;
00151         return $this;
00152     }
00153 
00154     /**
00155      * set name of left field
00156      *
00157      * @param string $name
00158      * @return Varien_Db_Tree
00159      */
00160     public function setLeftField($name) {
00161         $this->_left = $name;
00162         return $this;
00163     }
00164 
00165     /**
00166      * set name of right field
00167      *
00168      * @param string $name
00169      * @return Varien_Db_Tree
00170      */
00171     public function setRightField($name) {
00172         $this->_right = $name;
00173         return $this;
00174     }
00175 
00176     /**
00177      * set name of level field
00178      *
00179      * @param string $name
00180      * @return Varien_Db_Tree
00181      */
00182     public function setLevelField($name) {
00183         $this->_level = $name;
00184         return $this;
00185     }
00186 
00187     /**
00188      * set name of pid Field
00189      *
00190      * @param string $name
00191      * @return Varien_Db_Tree
00192      */
00193     public function setPidField($name) {
00194         $this->_pid = $name;
00195         return $this;
00196     }
00197 
00198     /**
00199      * set table name
00200      *
00201      * @param string $name
00202      * @return Varien_Db_Tree
00203      */
00204     public function setTable($name) {
00205         $this->_table = $name;
00206         return $this;
00207     }
00208 
00209     public function getKeys() {
00210         $keys = array();
00211         $keys['id'] = $this->_id;
00212         $keys['left'] = $this->_left;
00213         $keys['right'] = $this->_right;
00214         $keys['pid'] = $this->_pid;
00215         $keys['level'] = $this->_level;
00216         return $keys;
00217     }
00218 
00219     /**
00220      * Cleare table and add root element
00221      *
00222      */
00223     public function clear($data = array())
00224     {
00225         // clearing table
00226         $this->_db->query('TRUNCATE '. $this->_table);
00227         //$this->_db->delete($this->_table,'');
00228 
00229         // prepare data for root element
00230         $data[$this->_pid] = 0;
00231         $data[$this->_left] = 1;
00232         $data[$this->_right] = 2;
00233         $data[$this->_level] = 0;
00234 
00235         try  {
00236             $this->_db->insert($this->_table, $data);
00237         } catch (PDOException $e) {
00238             echo $e->getMessage();
00239         }
00240         return $this->_db->lastInsertId();
00241     }
00242 
00243     public function getNodeInfo($ID) {
00244         if (empty($this->_nodesInfo[$ID])) {
00245             $sql = 'SELECT * FROM '.$this->_table.' WHERE '.$this->_id.'=:id';
00246             $res = $this->_db->query($sql, array('id' => $ID));
00247             $data = $res->fetch();
00248             $this->_nodesInfo[$ID] = $data;
00249         } else {
00250             $data = $this->_nodesInfo[$ID];
00251         }
00252         return $data;
00253     }
00254 
00255     public function appendChild($ID, $data) {
00256 
00257         if (!$info = $this->getNodeInfo($ID)) {
00258             return false;
00259         }
00260 
00261         $data[$this->_left]  = $info[$this->_right];
00262         $data[$this->_right] = $info[$this->_right] + 1;
00263         $data[$this->_level] = $info[$this->_level] + 1;
00264         $data[$this->_pid]   = $ID;
00265 
00266         // creating a place for the record being inserted
00267         if($ID) {
00268             $this->_db->beginTransaction();
00269             try {
00270                 $sql = 'UPDATE '.$this->_table.' SET'
00271                     . ' `'.$this->_left.'` = IF( `'.$this->_left.'` > :left, `'.$this->_left.'`+2, `'.$this->_left.'`),'
00272                     . ' `'.$this->_right.'` = IF( `'.$this->_right.'`>= :right, `'.$this->_right.'`+2, `'.$this->_right.'`)'
00273                     . ' WHERE `'.$this->_right.'` >= :right';
00274 
00275                 $this->_db->query($sql, array('left'=>$info[$this->_left], 'right'=>$info[$this->_right]));
00276 
00277                 $this->_db->insert($this->_table, $data);
00278                 $this->_db->commit();
00279             } catch (PDOException $p) {
00280                 $this->_db->rollBack();
00281                 echo $p->getMessage();
00282                 exit();
00283             } catch (Exception $e) {
00284                 $this->_db->rollBack();
00285                 echo $e->getMessage();
00286                 echo $sql;
00287                 var_dump($data);
00288                 exit();
00289             }
00290             // TODO: change to ZEND LIBRARY
00291             $res =  $this->_db->fetchOne('select last_insert_id()');
00292             return $res;
00293            //return $this->_db->fetchOne('select last_insert_id()');
00294             //return $this->_db->lastInsertId();
00295         }
00296         return  false;
00297     }
00298 
00299     public function checkNodes() {
00300         $sql = $this->_db->select();
00301 
00302         $sql->from(array('t1'=>$this->_table), array('t1.'.$this->_id, new Zend_Db_Expr('COUNT(t1.'.$this->_id.') AS rep')))
00303         ->from(array('t2'=>$this->_table))
00304         ->from(array('t3'=>$this->_table), new Zend_Db_Expr('MAX(t3.'.$this->_right.') AS max_right'));
00305 
00306 
00307         $sql->where('t1.'.$this->_left.' <> t2.'.$this->_left)
00308         ->where('t1.'.$this->_left.' <> t2.'.$this->_right)
00309         ->where('t1.'.$this->_right.' <> t2.'.$this->_right);
00310 
00311         $sql->group('t1.'.$this->_id);
00312         $sql->having('max_right <> SQRT(4 * rep + 1) + 1');
00313 
00314 
00315         return $this->_db->fetchAll($sql);
00316     }
00317 
00318     public function insertBefore($ID, $data) {
00319 
00320     }
00321 
00322     public function removeNode($ID) {
00323 
00324         if (!$info = $this->getNodeInfo($ID)) {
00325             return false;
00326         }
00327 
00328         if($ID) {
00329             $this->_db->beginTransaction();
00330             try {
00331                 // DELETE FROM my_tree WHERE left_key >= $left_key AND right_key <= $right_key
00332                 $this->_db->delete($this->_table, $this->_left.' >= '.$info[$this->_left].' AND '.$this->_right.' <= '.$info[$this->_right]);
00333 
00334                 // 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
00335                 $sql = 'UPDATE '.$this->_table.'
00336                     SET
00337                         '.$this->_left.' = IF('.$this->_left.' > '.$info[$this->_left].', '.$this->_left.' - '.($info[$this->_right] - $info[$this->_left] + 1).', '.$this->_left.'),
00338                         '.$this->_right.' = '.$this->_right.' - '.($info[$this->_right] - $info[$this->_left] + 1).'
00339                     WHERE
00340                         '.$this->_right.' > '.$info[$this->_right];
00341                 $this->_db->query($sql);
00342                 $this->_db->commit();
00343                 return new Varien_Db_Tree_Node($info, $this->getKeys());;
00344             } catch (Exception $e) {
00345                 $this->_db->rollBack();
00346                 echo $e->getMessage();
00347             }
00348         }
00349     }
00350 
00351 
00352     public function moveNode($eId, $pId, $aId = 0) {
00353 
00354         $eInfo = $this->getNodeInfo($eId);
00355         $pInfo = $this->getNodeInfo($pId);
00356 
00357 
00358         $leftId = $eInfo[$this->_left];
00359         $rightId = $eInfo[$this->_right];
00360         $level = $eInfo[$this->_level];
00361 
00362         $leftIdP = $pInfo[$this->_left];
00363         $rightIdP = $pInfo[$this->_right];
00364         $levelP = $pInfo[$this->_level];
00365 
00366         if ($eId == $pId || $leftId == $leftIdP || ($leftIdP >= $leftId && $leftIdP <= $rightId) || ($level == $levelP+1 && $leftId > $leftIdP && $rightId < $rightIdP)) {
00367             echo "alert('cant_move_tree');";
00368             return FALSE;
00369         }
00370 
00371         if ($leftIdP < $leftId && $rightIdP > $rightId && $levelP < $level - 1) {
00372             $sql = 'UPDATE '.$this->_table.' SET '
00373             . $this->_level . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_level.sprintf('%+d', -($level-1)+$levelP) . ' ELSE ' . $this->_level . ' END, '
00374             . $this->_right . ' = CASE WHEN ' . $this->_right . ' BETWEEN ' . ($rightId+1) . ' AND ' . ($rightIdP-1) . ' THEN ' . $this->_right . '-' . ($rightId-$leftId+1) . ' '
00375             . 'WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_right . '+' . ((($rightIdP-$rightId-$level+$levelP)/2)*2+$level-$levelP-1) . ' ELSE ' . $this->_right . ' END, '
00376             . $this->_left . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . ($rightId+1) . ' AND ' . ($rightIdP-1) . ' THEN ' . $this->_left . '-' . ($rightId-$leftId+1) . ' '
00377             . 'WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_left . '+' . ((($rightIdP-$rightId-$level+$levelP)/2)*2+$level-$levelP-1) . ' ELSE ' . $this->_left . ' END '
00378             . 'WHERE ' . $this->_left . ' BETWEEN ' . ($leftIdP+1) . ' AND ' . ($rightIdP-1);
00379         } elseif ($leftIdP < $leftId) {
00380             $sql = 'UPDATE ' . $this->_table . ' SET '
00381             . $this->_level . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_level.sprintf('%+d', -($level-1)+$levelP) . ' ELSE ' . $this->_level . ' END, '
00382             . $this->_left . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $rightIdP . ' AND ' . ($leftId-1) . ' THEN ' . $this->_left . '+' . ($rightId-$leftId+1) . ' '
00383             . 'WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_left . '-' . ($leftId-$rightIdP) . ' ELSE ' . $this->_left . ' END, '
00384             . $this->_right . ' = CASE WHEN ' . $this->_right . ' BETWEEN ' . $rightIdP . ' AND ' . $leftId . ' THEN ' . $this->_right . '+' . ($rightId-$leftId+1) . ' '
00385             . 'WHEN ' . $this->_right . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_right . '-' . ($leftId-$rightIdP) . ' ELSE ' . $this->_right . ' END '
00386             . 'WHERE (' . $this->_left . ' BETWEEN ' . $leftIdP . ' AND ' . $rightId. ' '
00387             . 'OR ' . $this->_right . ' BETWEEN ' . $leftIdP . ' AND ' . $rightId . ')';
00388         } else {
00389             $sql = 'UPDATE ' . $this->_table . ' SET '
00390             . $this->_level . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_level.sprintf('%+d', -($level-1)+$levelP) . ' ELSE ' . $this->_level . ' END, '
00391             . $this->_left . ' = CASE WHEN ' . $this->_left . ' BETWEEN ' . $rightId . ' AND ' . $rightIdP . ' THEN ' . $this->_left . '-' . ($rightId-$leftId+1) . ' '
00392             . 'WHEN ' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_left . '+' . ($rightIdP-1-$rightId) . ' ELSE ' . $this->_left . ' END, '
00393             . $this->_right . ' = CASE WHEN ' . $this->_right . ' BETWEEN ' . ($rightId+1) . ' AND ' . ($rightIdP-1) . ' THEN ' . $this->_right . '-' . ($rightId-$leftId+1) . ' '
00394             . 'WHEN ' . $this->_right . ' BETWEEN ' . $leftId . ' AND ' . $rightId . ' THEN ' . $this->_right . '+' . ($rightIdP-1-$rightId) . ' ELSE ' . $this->_right . ' END '
00395             . 'WHERE (' . $this->_left . ' BETWEEN ' . $leftId . ' AND ' . $rightIdP . ' '
00396             . 'OR ' . $this->_right . ' BETWEEN ' . $leftId . ' AND ' . $rightIdP . ')';
00397         }
00398 
00399         $this->_db->beginTransaction();
00400         try {
00401             $this->_db->query($sql);
00402             $this->_db->commit();
00403             echo "alert('node moved');";
00404             return true;
00405         } catch (Exception $e) {
00406             $this->_db->rollBack();
00407             echo "alert('node not moved: fatal error');";
00408             echo $e->getMessage();
00409             echo "<br>\r\n";
00410             echo $sql;
00411             echo "<br>\r\n";
00412             exit();
00413         }
00414     }
00415 
00416 
00417     public function __moveNode($eId, $pId, $aId = 0) {
00418 
00419         $eInfo = $this->getNodeInfo($eId);
00420         if ($pId != 0) {
00421             $pInfo = $this->getNodeInfo($pId);
00422         }
00423         if ($aId != 0) {
00424             $aInfo = $this->getNodeInfo($aId);
00425         }
00426 
00427         $level = $eInfo[$this->_level];
00428         $left_key = $eInfo[$this->_left];
00429         $right_key = $eInfo[$this->_right];
00430         if ($pId == 0) {
00431             $level_up = 0;
00432         } else {
00433             $level_up = $pInfo[$this->_level];
00434         }
00435 
00436         $right_key_near = 0;
00437         $left_key_near = 0;
00438 
00439         if ($pId == 0) { //move to root
00440             $right_key_near = $this->_db->fetchOne('SELECT MAX('.$this->_right.') FROM '.$this->_table);
00441         } elseif ($aId != 0 && $pID == $eInfo[$this->_pid]) { // if we have after ID
00442             $right_key_near = $aInfo[$this->_right];
00443             $left_key_near = $aInfo[$this->_left];
00444         } elseif ($aId == 0 && $pId == $eInfo[$this->_pid]) { // if we do not have after ID
00445             $right_key_near = $pInfo[$this->_left];
00446         } elseif ($pId != $eInfo[$this->_pid]) {
00447             $right_key_near = $pInfo[$this->_right] - 1;
00448         }
00449 
00450 
00451         $skew_level = $pInfo[$this->_level] - $eInfo[$this->_level] + 1;
00452         $skew_tree = $eInfo[$this->_right] - $eInfo[$this->_left] + 1;
00453 
00454         echo "alert('".$right_key_near."');";
00455 
00456         if ($right_key_near > $right_key) { // up
00457             echo "alert('move up');";
00458             $skew_edit = $right_key_near - $left_key + 1;
00459             $sql = 'UPDATE '.$this->_table.'
00460                 SET
00461                 '.$this->_right.' = IF('.$this->_left.' >= '.$eInfo[$this->_left].', '.$this->_right.' + '.$skew_edit.', IF('.$this->_right.' < '.$eInfo[$this->_left].', '.$this->_right.' + '.$skew_tree.', '.$this->_right.')),
00462                 '.$this->_level.' = IF('.$this->_left.' >= '.$eInfo[$this->_left].', '.$this->_level.' + '.$skew_level.', '.$this->_level.'),
00463                 '.$this->_left.' = IF('.$this->_left.' >= '.$eInfo[$this->_left].', '.$this->_left.' + '.$skew_edit.', IF('.$this->_left.' > '.$right_key_near.', '.$this->_left.' + '.$skew_tree.', '.$this->_left.'))
00464                 WHERE '.$this->_right.' > '.$right_key_near.' AND '.$this->_left.' < '.$eInfo[$this->_right];
00465         } elseif ($right_key_near < $right_key) { // down
00466             echo "alert('move down');";
00467             $skew_edit = $right_key_near - $left_key + 1 - $skew_tree;
00468             $sql = 'UPDATE '.$this->_table.'
00469                 SET
00470                     '.$this->_left.' = IF('.$this->_right.' <= '.$right_key.', '.$this->_left.' + '.$skew_edit.', IF('.$this->_left.' > '.$right_key.', '.$this->_left.' - '.$skew_tree.', '.$this->_left.')),
00471                     '.$this->_level.' = IF('.$this->_right.' <= '.$right_key.', '.$this->_level.' + '.$skew_level.', '.$this->_level.'),
00472                     '.$this->_right.' = IF('.$this->_right.' <= '.$right_key.', '.$this->_right.' + '.$skew_edit.', IF('.$this->_right.' <= '.$right_key_near.', '.$this->_right.' - '.$skew_tree.', '.$this->_right.'))
00473                 WHERE
00474                     '.$this->_right.' > '.$left_key.' AND '.$this->_left.' <= '.$right_key_near;
00475         }
00476 
00477 
00478         $this->_db->beginTransaction();
00479         try {
00480            $this->_db->query($sql);
00481            //$afrows = $this->_db->get
00482            $this->_db->commit();
00483 
00484         } catch (Exception $e) {
00485             $this->_db->rollBack();
00486             echo $e->getMessage();
00487             echo "<br>\r\n";
00488             echo $sql;
00489             echo "<br>\r\n";
00490             exit();
00491         }
00492         echo "alert('node added')";
00493     }
00494 
00495     public function addTable($tableName, $joinCondition, $fields='*')
00496     {
00497         $this->_extTables[$tableName] = array(
00498            'joinCondition' => $joinCondition,
00499            'fields'        => $fields
00500         );
00501     }
00502 
00503     protected function _addExtTablesToSelect(Zend_Db_Select &$select)
00504     {
00505         foreach ($this->_extTables as $tableName=>$info) {
00506             $select->joinInner($tableName, $info['joinCondition'], $info['fields']);
00507         }
00508     }
00509 
00510     public function getChildren($ID, $start_level = 0, $end_level = 0)
00511     {
00512         try {
00513             $info = $this->getNodeInfo($ID);
00514         } catch (Exception $e) {
00515             echo $e->getMessage();
00516             exit;
00517         }
00518 
00519         $dbSelect = new Zend_Db_Select($this->_db);
00520         $dbSelect->from($this->_table)
00521             ->where($this->_left  . ' >= :left')
00522             ->where($this->_right . ' <= :right')
00523             ->order($this->_left);
00524 
00525         $this->_addExtTablesToSelect($dbSelect);
00526 
00527         $data = array();
00528         $data['left'] = $info[$this->_left];
00529         $data['right'] = $info[$this->_right];
00530 
00531         if (!empty($start_level) && empty($end_level)) {
00532             $dbSelect->where($this->_level . ' = :minLevel');
00533             $data['minLevel'] = $info[$this->_level] + $start_level;
00534         }
00535 
00536         //echo $dbSelect->__toString();
00537         $data = $this->_db->fetchAll($dbSelect, $data);
00538 
00539         $nodeSet = new Varien_Db_Tree_NodeSet();
00540         foreach ($data as $node) {
00541              $nodeSet->addNode(new Varien_Db_Tree_Node($node, $this->getKeys()));
00542         }
00543         return $nodeSet;
00544     }
00545 
00546     public function getNode($nodeId)
00547     {
00548         $dbSelect = new Zend_Db_Select($this->_db);
00549         $dbSelect->from($this->_table)
00550             ->where($this->_table.'.'.$this->_id  . ' >= :id');
00551 
00552         $this->_addExtTablesToSelect($dbSelect);
00553 
00554         $data = array();
00555         $data['id'] = $nodeId;
00556 
00557         $data = $this->_db->fetchRow($dbSelect, $data);
00558 
00559         return new Varien_Db_Tree_Node($data, $this->getKeys());
00560     }
00561 }