Location: PHPKode > scripts > Nested Set DB Table > tests/1/NestedSetDbTable/Abstract.php
<?php
include_once('NestedSetDbTable/Exception.php');

/**
 * Abstract class that provides API for managing Nested set 
 * database table.
 *
 * @author Nikola Posa <hide@address.com>
 * @license http://opensource.org/licenses/gpl-3.0.html GNU General Public License
 */
abstract class NestedSetDbTable_Abstract
{
	const FIRST_CHILD  = 'firstChild';
	const LAST_CHILD   = 'lastChild';
	const NEXT_SIBLING = 'nextSibling';
	const PREV_SIBLING = 'prevSibling';
	
	/**
     * Database adapter instance, that will be used for 
	 * communication with database.
     *
     * @var NestedSetDbTable_DbAdapter_Interface
     */
    protected $_dbAdapter;
	
	/**
     * Default NestedSetDbTable_DbAdapter_Interface instance.
     *
     * @var NestedSetDbTable_DbAdapter_Interface
     */
    protected static $_defaultDbAdapter;
	
	/**
     * The table name.
     *
     * @var string
     */
    protected $_name;
	
	/**
     * Name of the primary key column.
     *
     * @var string
     */
    protected $_primary;
	
    /**
     * 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 $options are:
     *   dbAdapter - instance of database adapter,
     *   name      - table name,
     *   primary   - primary key,
	 *   addWhere  - additional SQL WHERE clause.
	 *
	 * @param array Array with custom config options.
     * @return NestedSetDbTable
     */
	public function __construct($options = array())
    {
		if (!empty($options)) {
            $this->setOptions($options);
        }
		
		if (!$this->_name) {
			throw new NestedSetDbTable_Exception('You must supply name of your table in database.');
		}
		
		if (!$this->_primary) {
			throw new NestedSetDbTable_Exception('You must supply primary key column name.');
		}
		
		if(!$this->_left || !$this->_right) {
			throw new NestedSetDbTable_Exception('Both "left" and "right" column names must be supplied.');
		}
		
		$this->setupDatabaseAdapter();
		
		$this->prepareAddWhere();
    }
	
	/**
	 * Sets config options.
	 *
	 * @param array Config options.
	 * @return void
	 */
	public function setOptions($options)
    {
        foreach ($options as $key=>$value) {
            switch ($key) {
                case 'dbAdapter':
                    $this->setDbAdapter($value);
                    break;
                case 'name':
                    $this->_name = $value;
                    break;
				case 'primary':
                    $this->_primary = $value;
                    break;
				case 'addWhere':
                     $this->setAddWhere($value);
                    break;
                default:
                    break;
            }
        }
    }
	
	/**
	 * Initializing database adapter.
	 *
	 * @return void
	 */
    protected function setupDatabaseAdapter()
    {
        if (!$this->_dbAdapter) {
            $this->_dbAdapter = self::getDefaultAdapter();
        }
    }
	
	/**
	 * Sets database adapter.
	 *
	 * @param NestedSetDbTable_DbAdapter_Interface Appropriate database adapter instance.
	 * @return void
	 */
	public function setDbAdapter($dbAdapter)
	{
		if (!$dbAdapter instanceof NestedSetDbTable_DbAdapter_Interface) {
			throw new NestedSetDbTable_Exception('Database adapter must implement NestedSetDbTable_DbAdapter interface.');
		}
		
		$this->_dbAdapter = $dbAdapter;
	}
	
	 /**
     * Sets the default NestedSetDbTable_DbAdapter_Interface for 
	 * all objects of this class.
     *
     * @param NestedSetDbTable_DbAdapter_Interface|null Appropriate database adapter instance.
     * @return void
     */
    public static function setDefaultAdapter($dbAdapter = null)
    {
		if ($dbAdapter != null && !$dbAdapter instanceof NestedSetDbTable_DbAdapter_Interface) {
			throw new NestedSetDbTable_Exception('Database adapter must implement NestedSetDbTable_DbAdapter interface.');
		}
		
        self::$_defaultDbAdapter = $dbAdapter;
    }

    /**
     * Gets the default NestedSetDbTable_DbAdapter_Interface for 
	 * all objects of this class.
     *
     * @return NestedSetDbTable_DbAdapter_Interface|null
     */
    public static function getDefaultAdapter()
    {
        return self::$_defaultDbAdapter;
    }
	
	/**
	 * 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()
	{
		$addWhere = '';
		if (strlen($this->addWhere) > 0) {
			$addWhere = ' AND ' . $this->addWhere;
		}
			
		$sql = "SELECT node.*, (COUNT(parent.{$this->_primary}) - 1) AS 'depth'
		FROM {$this->_name} node, {$this->_name} parent
		WHERE node.{$this->_left} BETWEEN parent.{$this->_left} AND parent.{$this->_right} $addWhere
		GROUP BY node.{$this->_primary}
		ORDER BY node.{$this->_left}";
		
		return $this->_dbAdapter->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)) {
			throw new NestedSetDbTable_Exception('Invalid node position is supplied.');
		}
		
		$data = array_merge($data, $this->getLftRgt($objectiveNodeId, $position));
		
		return $this->_dbAdapter->insert($this->_name, $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)) {
			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));
		}
		
		$where = "{$this->_primary} = " . $this->_dbAdapter->quote($id);
		
		return $this->_dbAdapter->update($this->_name, $data, $where);
	}
	
	/**
	 * Deletes some node.
	 *
	 * @param mixed Id of a node.
	 * @param bool Whether to perform cascade deleting.
	 * @return void
	 */
	public function deleteNode($id, $cascade = false)
	{
		$id = (int)$id;
		
		if ($cascade == false) {
			$this->reduceWidth($id);
			
			$where = "{$this->_primary} = " . $this->_dbAdapter->quote($id);
		
			//Deleting node.
			return $this->_dbAdapter->delete($this->_name, $where);
		}
		else {
			$sql = "SELECT {$this->_left}, {$this->_right}, ({$this->_right} - {$this->_left} + 1) AS 'width' FROM {$this->_name} WHERE {$this->_primary} = " . $this->_dbAdapter->quote($id);
			$result = $this->_dbAdapter->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 {$this->_name} WHERE ({$this->_left} BETWEEN $lft AND $rgt)" . $addWhere;
			$stmt = $this->_dbAdapter->query($sql);
			$affected = $stmt->rowCount();
			
			$sql = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} - $width WHERE {$this->_right} > $rgt" . $addWhere;
			$this->_dbAdapter->query($sql);
		
			$sql = "UPDATE {$this->_name} SET {$this->_left} = {$this->_left} - $width WHERE {$this->_left} > $lft" . $addWhere;
			$this->_dbAdapter->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();
		
		$addWhere = '';
		
		if ($objectiveNodeId != null) { //User selected some objective node?
			if (strlen($this->addWhere) > 0) {
				$addWhere = ' AND ' . $this->addWhere;
			}
			
			$sql = "SELECT {$this->_left}, {$this->_right} FROM {$this->_name} WHERE {$this->_primary} = " . $this->_dbAdapter->quote($objectiveNodeId);
			$result = $this->_dbAdapter->fetchRow($sql);
			$left = $result[$this->_left];
			$right = $result[$this->_right];
			
			$sql1 = '';
			$sql2 = '';
			switch ($position) {
				case self::FIRST_CHILD :
					$sql1 = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} + 2 WHERE {$this->_right} > $left" . $addWhere;
					$sql2 = "UPDATE {$this->_name} SET {$this->_left} = {$this->_left} + 2 WHERE {$this->_left} > $left" . $addWhere;
					
					$lftRgt[$this->_left] = $left + 1;
					$lftRgt[$this->_right] = $left + 2;
					
					break;
				case self::LAST_CHILD :
					$sql1 = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} + 2 WHERE {$this->_right} >= $right" . $addWhere;
					$sql2 = "UPDATE {$this->_name} SET {$this->_left} = {$this->_left} + 2 WHERE {$this->_left} > $right" . $addWhere;
					
					$lftRgt[$this->_left] = $right;
					$lftRgt[$this->_right] = $right + 1;
			
					break;
				case self::NEXT_SIBLING :
					$sql1 = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} + 2 WHERE {$this->_right} > $right" . $addWhere;
					$sql2 = "UPDATE {$this->_name} SET {$this->_left} = {$this->_left} + 2 WHERE {$this->_left} > $right" . $addWhere;
					
					$lftRgt[$this->_left] = $right + 1;
					$lftRgt[$this->_right] = $right + 2;
					
					break;
				case self::PREV_SIBLING :
					$sql1 = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} + 2 WHERE {$this->_right} > $left" . $addWhere;
					$sql2 = "UPDATE {$this->_name} SET {$this->_left} = {$this->_left} + 2 WHERE {$this->_left} >= $left" . $addWhere;
					
					$lftRgt[$this->_left] = $left;
					$lftRgt[$this->_right] = $left + 1;
					
					break;
			}
			
			$this->_dbAdapter->query($sql1);

			$this->_dbAdapter->query($sql2);
		}
		else {
			if (strlen($this->addWhere) > 0) {
				$addWhere = ' WHERE ' . $this->addWhere;
			}
				
			$sql = "SELECT MAX({$this->_right}) AS 'max_rgt' FROM {$this->_name}" . $addWhere;
			$result = $this->_dbAdapter->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;
		}
		
		$sql = "SELECT {$this->_left}, {$this->_right}, ({$this->_right} - {$this->_left} + 1) AS 'width' FROM {$this->_name} WHERE {$this->_primary} = " . $this->_dbAdapter->quote($id);
		$result = $this->_dbAdapter->fetchRow($sql);
		
		$left = $result[$this->_left];
		$right = $result[$this->_right];
		$width = $result['width'];
		
		if ((int)$width > 2) { //Some node that has childs.
			//Updating parent nodes.
			$sql = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} - 2 WHERE {$this->_left} < $left AND {$this->_right} > $right" . $addWhere;
			$this->_dbAdapter->query($sql);
			
			//Updating childs.
			$sql = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} - 1, {$this->_left} = {$this->_left} - 1 WHERE ({$this->_left} BETWEEN $left AND $right)" . $addWhere;
			$this->_dbAdapter->query($sql);
			
			//Updating nodes on next levels.
			$sql = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} - 2, {$this->_left} = {$this->_left} - 2 WHERE {$this->_left} > $left AND {$this->_right} > $right" . $addWhere;
			$this->_dbAdapter->query($sql);
		}
		else {
			$sql = "UPDATE {$this->_name} SET {$this->_right} = {$this->_right} - 2 WHERE {$this->_right} > $right" . $addWhere;
			$this->_dbAdapter->query($sql);
		
			$sql = "UPDATE {$this->_name} SET {$this->_left} = {$this->_left} - 2 WHERE {$this->_left} > $left" . $addWhere;
			$this->_dbAdapter->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 = '';
		
		$nodeId = $this->_dbAdapter->quote($nodeId);
		
		switch ($position) {
			case self::FIRST_CHILD :
				$sql = "SELECT node.{$this->_primary} 
				FROM {$this->_name} node, (SELECT {$this->_left}, {$this->_right} FROM {$this->_name} WHERE {$this->_primary} = $nodeId) AS current 
				WHERE current.{$this->_left} BETWEEN node.{$this->_left}+1 AND node.{$this->_right} AND current.{$this->_left} - node.{$this->_left} = 1" . $addWhere . "
				ORDER BY node.{$this->_left} DESC";
				
				break;
			case self::LAST_CHILD :
				$sql = "SELECT node.{$this->_primary} 
				FROM {$this->_name} node, (SELECT {$this->_left}, {$this->_right} FROM {$this->_name} WHERE {$this->_primary} = $nodeId) AS current 
				WHERE current.{$this->_left} BETWEEN node.{$this->_left}+1 AND node.{$this->_right} AND node.{$this->_right} - current.{$this->_right} = 1" . $addWhere . "
				ORDER BY node.{$this->_left} DESC";
				
				break;
			case self::NEXT_SIBLING :
				$sql = "SELECT node.{$this->_primary}
				FROM {$this->_name} node, (SELECT {$this->_left} FROM {$this->_name} WHERE {$this->_primary} = $nodeId) AS current 
				WHERE current.{$this->_left} - node.{$this->_right} = 1" . $addWhere;
				
				break;
			case self::PREV_SIBLING :
				$sql = "SELECT node.{$this->_primary}
				FROM {$this->_name} node, (SELECT {$this->_right} FROM {$this->_name} WHERE {$this->_primary} = $nodeId) AS current 
				WHERE node.{$this->_left} - current.{$this->_right} = 1" . $addWhere;
				
				break;
		}
		
		$result = $this->_dbAdapter->fetchRow($sql);
		
		return $result[$this->_primary];
	}
}
?>
Return current item: Nested Set DB Table