<?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];
}
}
?>