<?php
/**
* Abstract class that extends capabilities of Zend_Db_Table
* class, providing API for managing some Nested set table in
* database.
*
* @author Nikola Posa <hide@address.com>
* @license http://opensource.org/licenses/gpl-3.0.html GNU General Public License
*/
abstract class NestedSetDbTable_Abstract extends Zend_Db_Table
{
const FIRST_CHILD = 'firstChild';
const LAST_CHILD = 'lastChild';
const NEXT_SIBLING = 'nextSibling';
const PREV_SIBLING = 'prevSibling';
/**
* Left column name in nested table.
*
* @var string
*/
protected $_left;
/**
* Right column name in nested table.
*
* @var string
*/
protected $_right;
/**
* Additional SQL WHERE clause to be used in process of
* determining left and right column values, getting
* whole tree, etc.
*
* @var string
*/
protected $addWhere = null;
/**
* Constructor.
*
* Supported params for $config are:
* left - left column name,
* right - right column name,
* addWhere - additional SQL WHERE clause.
*
* @param array An array of user-specified config options.
* @return Zend_Db_Table
*/
public function __construct($config = array())
{
if (!empty($config)) {
$this->setNestedOptions($config);
}
if (!$this->_left || !$this->_right) {
include_once('NestedSetDbTable/Exception.php');
throw new NestedSetDbTable_Exception('Both "left" and "right" column names must be supplied.');
}
parent::__construct($config);
$this->_setupPrimaryKey();
$this->prepareAddWhere();
}
/**
* Sets config options.
*
* @param array Config options.
* @return void
*/
public function setNestedOptions($options)
{
foreach ($options as $key => $value) {
switch ($key) {
case 'left':
$this->_left = (string)$value;
break;
case 'right':
$this->_right = (string)$value;
break;
case 'addWhere':
$this->setAddWhere($value);
break;
default:
break;
}
}
}
/**
* Prepares additional WHERE clause.
*
* @return void
*/
protected function prepareAddWhere()
{
if ($this->addWhere != null) {
$this->addWhere = (string)$this->addWhere;
$this->addWhere = preg_replace('/^where/i', '', trim($this->addWhere));
}
else {
$this->addWhere = '';
}
}
/**
* Sets additional WHERE clause.
*
* @param string Additional SQL WHERE clause.
* @return void
*/
public function setAddWhere($addWhere)
{
$this->addWhere = $addWhere;
$this->prepareAddWhere();
}
/**
* Checks whether valid node position is supplied.
*
* @param string Position regarding on objective node.
* @return bool
*/
private function checkNodePosition($position)
{
$r = new ReflectionClass($this);
if (!in_array($position, $r->getConstants())) {
return false;
}
return true;
}
/**
* Gets whole tree, including depth information.
*
* @return array
*/
public function getTree()
{
$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
$addWhere = '';
if (strlen($this->addWhere) > 0) {
$addWhere = ' AND ' . $this->addWhere;
}
$sql = "SELECT node.*, (COUNT(parent.$primary) - 1) AS 'depth'
FROM $tableName node, $tableName parent
WHERE node.$leftCol BETWEEN parent.$leftCol AND parent.$rightCol $addWhere
GROUP BY node.$primary
ORDER BY node.$leftCol";
return $this->getAdapter()->fetchAll($sql);
}
/**
* Function for adding new node.
*
* @param array Submitted data.
* @param int|null Objective node id.
* @param string Position regarding on objective node.
* @return mixed
*/
public function insertNode($data, $objectiveNodeId = null, $position = self::LAST_CHILD)
{
if (!$this->checkNodePosition($position)) {
include_once('NestedSetDbTable/Exception.php');
throw new NestedSetDbTable_Exception('Invalid node position is supplied.');
}
$data = array_merge($data, $this->getLftRgt($objectiveNodeId, $position));
return $this->insert($data);
}
/**
* Updates info of some node.
*
* @param array Submitted data.
* @param int Id of a node that is being updated.
* @param int Objective node id.
* @param string Position regarding on objective node.
* @return mixed
*/
public function updateNode($data, $id, $objectiveNodeId, $position)
{
$id = (int)$id;
if (!$this->checkNodePosition($position)) {
include_once('NestedSetDbTable/Exception.php');
throw new NestedSetDbTable_Exception('Invalid node position is supplied.');
}
if ($objectiveNodeId != $this->getCurrentObjectiveId($id, $position)) {
$this->reduceWidth($id);
$data = array_merge($data, $this->getLftRgt($objectiveNodeId, $position));
}
$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
$where = $this->getAdapter()->quoteInto($primary . ' = ?', $id, Zend_Db::INT_TYPE);
return $this->update($data, $where);
}
/**
* Deletes some node.
*
* @param mixed Id of a node.
* @param bool Whether to perform cascade deleting.
* @return int
*/
public function deleteNode($id, $cascade = false)
{
$id = (int)$id;
$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
if ($cascade == false) {
$this->reduceWidth($id);
$where = $this->getAdapter()->quoteInto($primary . ' = ?', $id, Zend_Db::INT_TYPE);
//Deleting node.
return $this->delete($where);
}
else {
$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
$sql = "SELECT $leftCol, $rightCol, ($rightCol - $leftCol + 1) AS 'width' FROM $tableName WHERE $primary = " . $this->getAdapter()->quote($id, Zend_Db::INT_TYPE);
$result = $this->getAdapter()->fetchRow($sql);
$lft = $result[$this->_left];
$rgt = $result[$this->_right];
$width = $result['width'];
$addWhere = '';
if (strlen($this->addWhere) > 0) {
$addWhere = ' AND ' . $this->addWhere;
}
//Deleting items.
$sql = "DELETE FROM $tableName WHERE ($leftCol BETWEEN $lft AND $rgt)" . $addWhere;
$stmt = $this->getAdapter()->query($sql);
$affected = $stmt->rowCount();
$sql = "UPDATE $tableName SET $rightCol = $rightCol - $width WHERE $rightCol > $rgt" . $addWhere;
$this->getAdapter()->query($sql);
$sql = "UPDATE $tableName SET $leftCol = $leftCol - $width WHERE $leftCol > $lft" . $addWhere;
$this->getAdapter()->query($sql);
return $affected;
}
}
/**
* Generates left and right column value, based on id of a
* objective node.
*
* @param mixed Id of a objective node.
* @param string Position in tree.
* @return array
*/
protected function getLftRgt($objectiveNodeId, $position)
{
$lftRgt = array();
$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
$addWhere = '';
if ($objectiveNodeId != null) { //User selected some objective node?
if (strlen($this->addWhere) > 0) {
$addWhere = ' AND ' . $this->addWhere;
}
$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
$sql = $this->getAdapter()->quoteInto("SELECT $leftCol, $rightCol FROM $tableName WHERE $primary = ?", $objectiveNodeId, Zend_Db::INT_TYPE);
$result = $this->getAdapter()->fetchRow($sql);
$left = $result[$this->_left];
$right = $result[$this->_right];
$sql1 = '';
$sql2 = '';
switch ($position) {
case self::FIRST_CHILD :
$sql1 = "UPDATE $tableName SET $rightCol = $rightCol + 2 WHERE $rightCol > $left" . $addWhere;
$sql2 = "UPDATE $tableName SET $leftCol = $leftCol + 2 WHERE $leftCol > $left" . $addWhere;
$lftRgt[$this->_left] = $left + 1;
$lftRgt[$this->_right] = $left + 2;
break;
case self::LAST_CHILD :
$sql1 = "UPDATE $tableName SET $rightCol = $rightCol + 2 WHERE $rightCol >= $right" . $addWhere;
$sql2 = "UPDATE $tableName SET $leftCol = $leftCol + 2 WHERE $leftCol > $right" . $addWhere;
$lftRgt[$this->_left] = $right;
$lftRgt[$this->_right] = $right + 1;
break;
case self::NEXT_SIBLING :
$sql1 = "UPDATE $tableName SET $rightCol = $rightCol + 2 WHERE $rightCol > $right" . $addWhere;
$sql2 = "UPDATE $tableName SET $leftCol = $leftCol + 2 WHERE $leftCol > $right" . $addWhere;
$lftRgt[$this->_left] = $right + 1;
$lftRgt[$this->_right] = $right + 2;
break;
case self::PREV_SIBLING :
$sql1 = "UPDATE $tableName SET $rightCol = $rightCol + 2 WHERE $rightCol > $left" . $addWhere;
$sql2 = "UPDATE $tableName SET $leftCol = $leftCol + 2 WHERE $leftCol >= $left" . $addWhere;
$lftRgt[$this->_left] = $left;
$lftRgt[$this->_right] = $left + 1;
break;
}
$this->getAdapter()->query($sql1);
$this->getAdapter()->query($sql2);
}
else {
if (strlen($this->addWhere) > 0) {
$addWhere = ' WHERE ' . $this->addWhere;
}
$sql = "SELECT MAX($rightCol) AS 'max_rgt' FROM $tableName" . $addWhere;
$result = $this->getAdapter()->fetchRow($sql);
if ($result == null) { //No data? First node...
$lftRgt[$this->_left] = 1;
}
else {
$lftRgt[$this->_left] = $result['max_rgt'] + 1;
}
$lftRgt[$this->_right] = $lftRgt[$this->_left] + 1;
}
return $lftRgt;
}
/**
* Reduces lft and rgt values of some nodes, on which some
* node that is changing position in tree, or being deleted,
* has effect.
*
* @param mixed Id of a node.
* @return void
*/
protected function reduceWidth($id)
{
$addWhere = '';
if (strlen($this->addWhere) > 0) {
$addWhere = ' AND ' . $this->addWhere;
}
$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
$sql = "SELECT $leftCol, $rightCol, ($rightCol - $leftCol + 1) AS 'width' FROM $tableName WHERE $primary = " . $this->getAdapter()->quote($id, Zend_Db::INT_TYPE);
$result = $this->getAdapter()->fetchRow($sql);
$left = $result[$this->_left];
$right = $result[$this->_right];
$width = $result['width'];
if ((int)$width > 2) { //Some node that has childs.
//Updating childs.
$sql = "UPDATE $tableName SET $rightCol = $rightCol - 1, $leftCol = $leftCol - 1 WHERE ($leftCol BETWEEN $left AND $right)" . $addWhere;
$this->getAdapter()->query($sql);
}
//Updating parent nodes and nodes on next levels.
$sql = "UPDATE $tableName SET $rightCol = $rightCol - 2 WHERE $rightCol > $right" . $addWhere;
$this->getAdapter()->query($sql);
$sql = "UPDATE $tableName SET $leftCol = $leftCol - 2 WHERE $leftCol > $left" . $addWhere;
$this->getAdapter()->query($sql);
}
/**
* Gets id of some node's current objective node.
*
* @param mixed Node id.
* @param string Position in tree.
* @return string|null
*/
protected function getCurrentObjectiveId($nodeId, $position)
{
$addWhere = '';
if (strlen($this->addWhere) > 0) {
$addWhere = ' AND ' . $this->addWhere;
}
$sql = '';
$tableName = $this->getAdapter()->quoteIdentifier($this->_name);
$primary = $this->getAdapter()->quoteIdentifier($this->_primary[1]);
$leftCol = $this->getAdapter()->quoteIdentifier($this->_left);
$rightCol = $this->getAdapter()->quoteIdentifier($this->_right);
switch ($position) {
case self::FIRST_CHILD :
$sql = $this->getAdapter()->quoteInto("SELECT node.$primary
FROM $tableName node, (SELECT $leftCol, $rightCol FROM $tableName WHERE $primary = ?) AS current
WHERE current.$leftCol BETWEEN node.$leftCol+1 AND node.$rightCol AND current.$leftCol - node.$leftCol = 1" . $addWhere . "
ORDER BY node.$leftCol DESC", $nodeId, Zend_Db::INT_TYPE);
break;
case self::LAST_CHILD :
$sql = $this->getAdapter()->quoteInto("SELECT node.$primary
FROM $tableName node, (SELECT $leftCol, $rightCol FROM $tableName WHERE $primary = ?) AS current
WHERE current.$leftCol BETWEEN node.$leftCol+1 AND node.$rightCol AND node.$rightCol - current.$rightCol = 1" . $addWhere . "
ORDER BY node.$leftCol DESC", $nodeId, Zend_Db::INT_TYPE);
break;
case self::NEXT_SIBLING :
$sql = $this->getAdapter()->quoteInto("SELECT node.$primary
FROM $tableName node, (SELECT $leftCol FROM $tableName WHERE $primary = ?) AS current
WHERE current.$leftCol - node.$rightCol = 1" . $addWhere, $nodeId, Zend_Db::INT_TYPE);
break;
case self::PREV_SIBLING :
$sql = $this->getAdapter()->quoteInto("SELECT node.$primary
FROM $tableName node, (SELECT $rightCol FROM $tableName WHERE $primary = ?) AS current
WHERE node.$leftCol - current.$rightCol = 1" . $addWhere, $nodeId, Zend_Db::INT_TYPE);
break;
}
$result = $this->getAdapter()->fetchRow($sql);
return $result[$this->_primary[1]];
}
}
?>