Varien_Db_Tree Class Reference

List of all members.

Public Member Functions

 __construct ($config=array())
 setIdField ($name)
 setLeftField ($name)
 setRightField ($name)
 setLevelField ($name)
 setPidField ($name)
 setTable ($name)
 getKeys ()
 clear ($data=array())
 getNodeInfo ($ID)
 appendChild ($ID, $data)
 checkNodes ()
 insertBefore ($ID, $data)
 removeNode ($ID)
 moveNode ($eId, $pId, $aId=0)
 __moveNode ($eId, $pId, $aId=0)
 addTable ($tableName, $joinCondition, $fields='*')
 getChildren ($ID, $start_level=0, $end_level=0)
 getNode ($nodeId)

Protected Member Functions

 _addExtTablesToSelect (Zend_Db_Select &$select)


Detailed Description

Definition at line 42 of file Tree.php.


Constructor & Destructor Documentation

__construct ( config = array()  ) 

Definition at line 75 of file Tree.php.

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     }


Member Function Documentation

__moveNode ( eId,
pId,
aId = 0 
)

Definition at line 417 of file Tree.php.

00417                                                      {
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     }

_addExtTablesToSelect ( Zend_Db_Select &$  select  )  [protected]

Definition at line 503 of file Tree.php.

00504     {
00505         foreach ($this->_extTables as $tableName=>$info) {
00506             $select->joinInner($tableName, $info['joinCondition'], $info['fields']);
00507         }
00508     }

addTable ( tableName,
joinCondition,
fields = '*' 
)

Definition at line 495 of file Tree.php.

00496     {
00497         $this->_extTables[$tableName] = array(
00498            'joinCondition' => $joinCondition,
00499            'fields'        => $fields
00500         );
00501     }

appendChild ( ID,
data 
)

Definition at line 255 of file Tree.php.

00255                                             {
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     }

checkNodes (  ) 

Definition at line 299 of file Tree.php.

00299                                  {
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     }

clear ( data = array()  ) 

Cleare table and add root element

Definition at line 223 of file Tree.php.

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     }

getChildren ( ID,
start_level = 0,
end_level = 0 
)

Definition at line 510 of file Tree.php.

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     }

getKeys (  ) 

Definition at line 209 of file Tree.php.

00209                               {
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     }

getNode ( nodeId  ) 

Definition at line 546 of file Tree.php.

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     }

getNodeInfo ( ID  ) 

Definition at line 243 of file Tree.php.

00243                                      {
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     }

insertBefore ( ID,
data 
)

Definition at line 318 of file Tree.php.

00318                                              {
00319 
00320     }

moveNode ( eId,
pId,
aId = 0 
)

Definition at line 352 of file Tree.php.

00352                                                    {
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     }

removeNode ( ID  ) 

Definition at line 322 of file Tree.php.

00322                                     {
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     }

setIdField ( name  ) 

set name of id field

Parameters:
string $name
Returns:
Varien_Db_Tree

Definition at line 149 of file Tree.php.

00149                                       {
00150         $this->_id = $name;
00151         return $this;
00152     }

setLeftField ( name  ) 

set name of left field

Parameters:
string $name
Returns:
Varien_Db_Tree

Definition at line 160 of file Tree.php.

00160                                         {
00161         $this->_left = $name;
00162         return $this;
00163     }

setLevelField ( name  ) 

set name of level field

Parameters:
string $name
Returns:
Varien_Db_Tree

Definition at line 182 of file Tree.php.

00182                                          {
00183         $this->_level = $name;
00184         return $this;
00185     }

setPidField ( name  ) 

set name of pid Field

Parameters:
string $name
Returns:
Varien_Db_Tree

Definition at line 193 of file Tree.php.

00193                                        {
00194         $this->_pid = $name;
00195         return $this;
00196     }

setRightField ( name  ) 

set name of right field

Parameters:
string $name
Returns:
Varien_Db_Tree

Definition at line 171 of file Tree.php.

00171                                          {
00172         $this->_right = $name;
00173         return $this;
00174     }

setTable ( name  ) 

set table name

Parameters:
string $name
Returns:
Varien_Db_Tree

Definition at line 204 of file Tree.php.

00204                                     {
00205         $this->_table = $name;
00206         return $this;
00207     }


The documentation for this class was generated from the following file:

Generated on Sat Jul 4 17:25:01 2009 for Magento by  doxygen 1.5.8