PHP Classes
elePHPant
Icontem

File: libs/NestedSetDbTable/Abstract.php

Recommend this page to a friend!
Stumble It! Stumble It! Bookmark in del.icio.us Bookmark in del.icio.us
  Classes of Nikola Posa  >  Nested Set DB Table  >  libs/NestedSetDbTable/Abstract.php  >  Download  
File: libs/NestedSetDbTable/Abstract.php
Role: Class source
Content type: text/plain
Description: Abstract class that provides API for managing Nested set database table.
Class: Nested Set DB Table
Manage nested sets of data stored in a database
Author: By
Last change: Removing _reduceWidth() method.
Date: 2 years ago
Size: 19,639 bytes
 

Contents

Class file image Download
<?php
/**
 * NestedSetDbTable
 *
 * Copyright (C) 2009 Nikola Posa (http://www.nikolaposa.in.rs)
 *
 * This file is part of NestedSetDbTable.
 *
 * NestedSetDbTable is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * NestedSetDbTable is distributed in the hope that it will be useful, but
 * WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with NestedSetDbTable. If not, see <http://www.gnu.org/licenses/>.
 */

/**
 * Abstract class that provides API for managing Nested set 
 * database table.
 *
 * @author Nikola Posa <posa.nikola@gmail.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 the database.
     *
     * @var PDO
     */
    protected $_dbAdapter;

    /**
     * Default NestedSetDbTable_DbAdapter_Interface instance.
     * 
     * @var PDO
     */
    protected static $_defaultDbAdapter;

    /**
     * The table name.
     *
     * Must be overriden by the extending class.
     *
     * @var string
     */
    protected $_name;

    /**
     * Name of the primary key column.
     *
     * Must be overriden by the extending class.
     *
     * @var string
     */
    protected $_primary;
	
    /**
     * Left column name in nested table.
     *
     * Must be overriden by the extending class.
     *
     * @var string
     */
    protected $_left;
 
    /**
     * Right column name in nested table.
     *
     * Must be overriden by the extending class.
     *
     * @var string
     */
    protected $_right;

    /**
     * Constructor.
     * 
     * @param PDO $dbAdapter
     * @return void
     */
    public function __construct(PDO $dbAdapter = null)
    {
        if (!$this->_name) {
            require_once('NestedSetDbTable/Exception.php');
            throw new NestedSetDbTable_Exception('You must supply name of your table in database.');
        }
        elseif (!$this->_primary) {
            require_once('NestedSetDbTable/Exception.php');
            throw new NestedSetDbTable_Exception('You must supply primary key column name.');
        }
        elseif(!$this->_left || !$this->_right) {
            require_once('NestedSetDbTable/Exception.php');
            throw new NestedSetDbTable_Exception('Both "left" and "right" column names must be supplied.');
        }

        if ($dbAdapter) {
            $this->_setDbAdapter($dbAdapter);
        }
        $this->_setupDatabaseAdapter();
    }
	
    /**
     * Initializing database adapter.
     *
     * @return void
     */
    protected function _setupDatabaseAdapter()
    {
        if (!$this->_dbAdapter) {
            $this->_dbAdapter = self::getDefaultAdapter();

            if (!$this->_dbAdapter instanceof PDO) {
                require_once('NestedSetDbTable/Exception.php');
                throw new NestedSetDbTable_Exception('No adapter found for ' . get_class($this));
            }
        }

        //always use exceptions
        $this->_dbAdapter->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        
        //Because we are using double qoutes for the identifiers
        switch ($this->_dbAdapter->getAttribute(PDO::ATTR_DRIVER_NAME)) {
            case 'mysql':
                $this->_dbAdapter->query("SET sql_mode='ANSI_QUOTES'");
                break;
            case 'mssql': case 'sybase': case 'dblib':
                $this->_dbAdapter->query("SET QUOTED_IDENTIFIER ON");
                break;
        }
    }
	
    /**
     * Sets database adapter.
     *
     * @param PDO $dbAdapter
     * @return void
     */
    protected function _setDbAdapter(PDO $dbAdapter)
    {
        $this->_dbAdapter = $dbAdapter;
    }
	
    /**
     * Gets database adapter.
     *
     * @return PDO
     */
    public function getAdapter()
    {
        return $this->_dbAdapter;
    }

    /**
     * Sets the default database adapter.
     *
     * @param PDO|null $dbAdapter
     * @return void
     */
    public static function setDefaultAdapter(PDO $dbAdapter = null)
    {
        self::$_defaultDbAdapter = $dbAdapter;
    }

    /**
     * Gets the default database adapter.
     *
     * @return PDO|null
     */
    public static function getDefaultAdapter()
    {
        return self::$_defaultDbAdapter;
    }

    /**
     * Quotes identifier.
     *
     * @param string $identifier
     * @return string
     */
    protected function _quoteIdentifier($identifier)
    {
        return '"' . $identifier . '"';
    }
	
    /**
     * Gets whole tree, including depth information.
     *
     * @param string An SQL WHERE clause.
     * @return array
     */
    public function getTree($where = null)
    {
        $where = (string)$where;

        if (strlen($where) > 0) {
            $where = ' AND ' . $where;
        }

        $name = $this->_quoteIdentifier($this->_name);
        $primary = $this->_quoteIdentifier($this->_primary);
        $left = $this->_quoteIdentifier($this->_left);
        $right = $this->_quoteIdentifier($this->_right);

        $sql = 'SELECT node.*, (COUNT(parent.' . $primary . ') - 1) AS "depth"
        FROM ' . $name . ' AS node , ' . $name . ' AS parent
        WHERE node.' . $left . ' BETWEEN parent.' . $left . ' AND parent.' . $right . $where . '
        GROUP BY node.' . $primary . '
        ORDER BY node.' . $left;

        $stmt = $this->_dbAdapter->prepare($sql);
        $stmt->execute();

        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }
	
    /**
     * Method for adding new node.
     *
     * @param array $data
     * @param int|null $objectiveNodeId
     * @param string $position Position regarding on objective node.
     * @return int The number of affected rows.
     */
    public function insert($data, $objectiveNodeId = null, $position = self::LAST_CHILD)
	{
        if (!$this->_checkNodePosition($position)) {
            require_once('NestedSetDbTable/Exception.php');
            throw new NestedSetDbTable_Exception('Invalid node position is supplied.');
        }
		
        $data = array_merge($data, $this->_getLftRgt($objectiveNodeId, $position));
        $cols = array_keys($data);
        $vals = array();
        foreach ($cols as $i=>$col) {
            $cols[$i] = $this->_quoteIdentifier($col);
            $vals[] = '?';
        }

        $sql = 'INSERT INTO '
            . $this->_quoteIdentifier($this->_name)
            . ' (' . implode(', ', $cols) . ')
            VALUES (' . implode(', ', $vals) . ')';

        $stmt = $this->_dbAdapter->prepare($sql);
        $stmt->execute(array_values($data));

        return $stmt->rowCount();
	}
	
    /**
     * Updates info of some node.
     *
     * @param array $data
     * @param int $id Id of a node that is being updated.
     * @param int|null $objectiveNodeId
     * @param string $position Position regarding on objective node.
     * @return int The number of affected rows.
     */
    public function updateNode($data, $id, $objectiveNodeId, $position = self::LAST_CHILD)
	{
        $id = (int)$id;
        $objectiveNodeId = (int)$objectiveNodeId;

        if (!$this->_checkNodePosition($position)) {
            require_once('NestedSetDbTable/Exception.php');
            throw new NestedSetDbTable_Exception('Invalid node position is supplied.');
        }

        //Only if the objective id differs.
        if ($objectiveNodeId != $this->_getCurrentObjectiveId($id, $position)) { 
            $data = array_merge($data, $this->_getLftRgt($objectiveNodeId, $position, $id));
        }

        $set = array();
        foreach ($data as $col=>$val) {
            $set[] = $this->_quoteIdentifier($col) . ' = ?';
        }

        $retval = 0;
        if (!empty($set)) { //Has some data to update?
            $name = $this->_quoteIdentifier($this->_name);
            $primary = $this->_quoteIdentifier($this->_primary);
            $where = $primary . ' = ' . $this->_dbAdapter->quote($id, PDO::PARAM_INT);

            $stmt = $this->_dbAdapter->prepare(
                'UPDATE '
                . $name
                . 'SET ' . implode(', ', $set) . '
                WHERE ' . $where);
            $stmt->execute(array_values($data));

            $retval = $stmt->rowCount();
        }

        return $retval;
	}

    /**
     * Checks whether valid node position is supplied.
     *
     * @param string $position 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;
    }

	/**
	 * Deletes some node.
	 *
	 * @param mixed $id Id of a node.
	 * @param bool $cascade Whether to delete all child nodes.
	 * @return int The number of affected rows.
	 */
	public function deleteNode($id, $cascade = false)
	{
        $id = (int)$id;
        $name = $this->_quoteIdentifier($this->_name);
        $primary = $this->_quoteIdentifier($this->_primary);

        if ($cascade == false) {
            //Deleting node.
            $stmt = $this->_dbAdapter->prepare(
            'DELETE FROM  '
            . $name . '
            WHERE ' . $primary . ' = ?');
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->execute();
            
            return $stmt->rowCount();
        }
        else {
            $retval = 0;
            $leftCol = $this->_quoteIdentifier($this->_left);
            $rightCol = $this->_quoteIdentifier($this->_right);

            $sql = 'SELECT ' . $leftCol . ',' . $rightCol . ',' . '(' . $rightCol . ' - ' . $leftCol . ' + 1) AS "width" FROM ' . $name . ' WHERE ' . $primary . ' = ?';
            $stmt = $this->_dbAdapter->prepare($sql);
            $stmt->bindParam(1, $id, PDO::PARAM_INT);
            $stmt->execute();

            if ($stmt->rowCount() > 0) {
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $lft = $result[$this->_left];
                $rgt = $result[$this->_right];
                $width = $result['width'];

                //Deleting items.
                $stmt = $this->_dbAdapter->prepare(
                'DELETE FROM  '
                . $name . '
                WHERE ' . $leftCol . 'BETWEEN ' . $lft . ' AND ' . $rgt);
                $stmt->execute();
                $retval += $stmt->rowCount();

                $stmt = $this->_dbAdapter->prepare(
                'UPDATE '
                . $name
                . 'SET ' . $leftCol . ' = ' . $leftCol . '-' . $width . ' WHERE ' . $leftCol . '>' . $lft);
                $stmt->execute();
                $retval += $stmt->rowCount();

                $stmt = $this->_dbAdapter->prepare(
                'UPDATE '
                . $name
                . 'SET ' . $rightCol . ' = ' . $rightCol . '-' . $width . ' WHERE ' . $rightCol . '>' . $rgt);
                $stmt->execute();
                $retval += $stmt->rowCount();
            }

            return $retval;
        }
	}
	
    /**
     * 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, $id = null)
	{
        $lftRgt = array();

        $name = $this->_quoteIdentifier($this->_name);
        $primary = $this->_quoteIdentifier($this->_primary);
        $left = $this->_quoteIdentifier($this->_left);
        $right = $this->_quoteIdentifier($this->_right);

        $lft = null;
        $rgt = null;
        
        if ($objectiveNodeId) {
            $sql = "SELECT $left, $right FROM $name WHERE $primary = ?";
            $stmt = $this->_dbAdapter->prepare($sql);
            $stmt->bindParam(1, $objectiveNodeId, PDO::PARAM_INT);
            $stmt->execute();
            if ($stmt->rowCount() > 0) {
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $lft = (int)$result[$this->_left];
                $rgt = (int)$result[$this->_right];
            }
        }
        
		if ($lft !== null && $rgt !== null) { //Existing objective id?
            $sql1 = '';
            $sql2 = '';
            switch ($position) {
                case self::FIRST_CHILD :
                    $sql1 = "UPDATE $name SET $right = $right + 2 WHERE $right > $lft";
                    $sql2 = "UPDATE $name SET $left = $left + 2 WHERE $left > $lft";

                    $lftRgt[$this->_left] = $lft + 1;
                    $lftRgt[$this->_right] = $lft + 2;

                    break;
                case self::LAST_CHILD :
                    $sql1 = "UPDATE $name SET $right = $right + 2 WHERE $right >= $rgt";
                    $sql2 = "UPDATE $name SET $left = $left + 2 WHERE $left > $rgt";

                    $lftRgt[$this->_left] = $rgt;
                    $lftRgt[$this->_right] = $rgt + 1;

                    break;
                case self::NEXT_SIBLING :
                    $sql1 = "UPDATE $name SET $right = $right + 2 WHERE $right > $rgt";
                    $sql2 = "UPDATE $name SET $left = $left + 2 WHERE $left > $rgt";

                    $lftRgt[$this->_left] = $rgt + 1;
                    $lftRgt[$this->_right] = $rgt + 2;

                    break;
                case self::PREV_SIBLING :
                    $sql1 = "UPDATE $name SET $right = $right + 2 WHERE $right > $lft";
                    $sql2 = "UPDATE $name SET $left = $left + 2 WHERE $left >= $lft";

                    $lftRgt[$this->_left] = $lft;
                    $lftRgt[$this->_right] = $lft + 1;

                    break;
            }

            $this->_dbAdapter->query($sql1);
            $this->_dbAdapter->query($sql2);
        }
        else {
            $sql = "SELECT MAX($right) AS \"max_rgt\" FROM $name";
            if ($id !== null) {
                $sql .= " WHERE $primary != ?";
            }
            $stmt = $this->_dbAdapter->prepare($sql);
            if ($id !== null) {
                $id = (int)$id;
                $stmt->bindParam(1, $id, PDO::PARAM_INT);
            }

            $stmt->execute();

            if ($stmt->rowCount() > 0) {
                $result = $stmt->fetch(PDO::FETCH_ASSOC);
                $lftRgt[$this->_left] = $result['max_rgt'] + 1;
            }
            else {
                //No data? First node...
                $lftRgt[$this->_left] = 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 int $id Id of a node.
	 * @return void
	 */
    /*protected function _reduceWidth($id)
    {
        $name = $this->_quoteIdentifier($this->_name);
        $primary = $this->_quoteIdentifier($this->_primary);
        $leftCol = $this->_quoteIdentifier($this->_left);
        $rightCol = $this->_quoteIdentifier($this->_right);

        $sql = "SELECT $leftCol, $rightCol, ($rightCol - $leftCol + 1) AS \"width\" FROM $name WHERE $primary = ?";
        $stmt = $this->_dbAdapter->prepare($sql);
        $stmt->bindParam(1, $id, PDO::PARAM_INT);
        $stmt->execute();
        
        if ($stmt->rowCount() > 0) { //Only if supplied node exists.
            $result = $stmt->fetch(PDO::FETCH_ASSOC);

            $left = $result[$this->_left];
            $right = $result[$this->_right];
            $width = $result['width'];

            if ((int)$width > 2) { //Some node that has childs.
                //Updating children.
                $sql = "UPDATE $name SET $rightCol = $rightCol - 1, $leftCol = $leftCol - 1 WHERE $leftCol BETWEEN $left AND $right";
                $this->_dbAdapter->query($sql);
            }

            //Updating parent nodes and nodes on next levels.
            $sql = "UPDATE $name SET $leftCol = $leftCol - 2 WHERE $leftCol > $left AND $rightCol > $right";
            $this->_dbAdapter->query($sql);

            $sql = "UPDATE $name SET $rightCol = $rightCol - 2 WHERE $rightCol > $right";
            $this->_dbAdapter->query($sql);
        }
    }*/
	
	/**
	 * Gets id of some node's current objective node.
	 *
	 * @param mixed Node id.
	 * @param string Position in tree.
	 * @return int|null
	 */
    protected function _getCurrentObjectiveId($nodeId, $position)
    {
        $sql = '';

        $nodeId = $this->_dbAdapter->quote($nodeId, PDO::PARAM_INT);
        $name = $this->_quoteIdentifier($this->_name);
        $primary = $this->_quoteIdentifier($this->_primary);
        $leftCol = $this->_quoteIdentifier($this->_left);
        $rightCol = $this->_quoteIdentifier($this->_right);

        switch ($position) {
            case self::FIRST_CHILD :
                $sql = "SELECT node.$primary
                FROM $name node, (SELECT $leftCol, $rightCol FROM $name WHERE $primary = $nodeId) AS current
                WHERE current.$leftCol BETWEEN node.$leftCol+1 AND node.$rightCol AND current.$leftCol - node.$leftCol = 1
                ORDER BY node.$leftCol DESC";

                break;
            case self::LAST_CHILD :
                $sql = "SELECT node.$primary
                FROM $name node, (SELECT $leftCol, $rightCol FROM $name WHERE $primary = $nodeId) AS current
                WHERE current.$leftCol BETWEEN node.$leftCol+1 AND node.$rightCol AND node.$rightCol - current.$rightCol = 1
                ORDER BY node.$leftCol DESC";

                break;
            case self::NEXT_SIBLING :
                $sql = "SELECT node.$primary
                FROM $name node, (SELECT $leftCol FROM $name WHERE $primary = $nodeId) AS current
                WHERE current.$leftCol - node.$rightCol = 1";

                break;
            case self::PREV_SIBLING :
                $sql = "SELECT node.$primary
                FROM $name node, (SELECT $rightCol FROM $name WHERE $primary = $nodeId) AS current
                WHERE node.$leftCol - current.$rightCol = 1";

                break;
        }

        $stmt = $this->_dbAdapter->prepare($sql);
        $stmt->execute();
        if ($stmt->rowCount() > 0) {
            $result = $stmt->fetch(PDO::FETCH_ASSOC);
            
            return (int)$result[$this->_primary];
        }
        else {
            return null;
        }
    }
}