[ Index ]

PHP Cross Reference of phpBB-3.2.8-deutsch

title

Body

[close]

/phpbb/tree/ -> nestedset.php (source)

   1  <?php
   2  /**
   3  *
   4  * This file is part of the phpBB Forum Software package.
   5  *
   6  * @copyright (c) phpBB Limited <https://www.phpbb.com>
   7  * @license GNU General Public License, version 2 (GPL-2.0)
   8  *
   9  * For full copyright and license information, please see
  10  * the docs/CREDITS.txt file.
  11  *
  12  */
  13  
  14  namespace phpbb\tree;
  15  
  16  abstract class nestedset implements \phpbb\tree\tree_interface
  17  {
  18      /** @var \phpbb\db\driver\driver_interface */
  19      protected $db;
  20  
  21      /** @var \phpbb\lock\db */
  22      protected $lock;
  23  
  24      /** @var string */
  25      protected $table_name;
  26  
  27      /**
  28      * Prefix for the language keys returned by exceptions
  29      * @var string
  30      */
  31      protected $message_prefix = '';
  32  
  33      /**
  34      * Column names in the table
  35      * @var string
  36      */
  37      protected $column_item_id = 'item_id';
  38      protected $column_left_id = 'left_id';
  39      protected $column_right_id = 'right_id';
  40      protected $column_parent_id = 'parent_id';
  41      protected $column_item_parents = 'item_parents';
  42  
  43      /**
  44      * Additional SQL restrictions
  45      * Allows to have multiple nested sets in one table
  46      * @var string
  47      */
  48      protected $sql_where = '';
  49  
  50      /**
  51      * List of item properties to be cached in the item_parents column
  52      * @var array
  53      */
  54      protected $item_basic_data = array('*');
  55  
  56      /**
  57      * Construct
  58      *
  59      * @param \phpbb\db\driver\driver_interface    $db        Database connection
  60      * @param \phpbb\lock\db        $lock    Lock class used to lock the table when moving forums around
  61      * @param string            $table_name            Table name
  62      * @param string            $message_prefix        Prefix for the messages thrown by exceptions
  63      * @param string            $sql_where            Additional SQL restrictions for the queries
  64      * @param array            $item_basic_data    Array with basic item data that is stored in item_parents
  65      * @param array            $columns            Array with column names to overwrite
  66      */
  67  	public function __construct(\phpbb\db\driver\driver_interface $db, \phpbb\lock\db $lock, $table_name, $message_prefix = '', $sql_where = '', $item_basic_data = array(), $columns = array())
  68      {
  69          $this->db = $db;
  70          $this->lock = $lock;
  71  
  72          $this->table_name = $table_name;
  73          $this->message_prefix = $message_prefix;
  74          $this->sql_where = $sql_where;
  75          $this->item_basic_data = (!empty($item_basic_data)) ? $item_basic_data : array('*');
  76  
  77          if (!empty($columns))
  78          {
  79              foreach ($columns as $column => $name)
  80              {
  81                  $column_name = 'column_' . $column;
  82                  $this->$column_name = $name;
  83              }
  84          }
  85      }
  86  
  87      /**
  88      * Returns additional sql where restrictions
  89      *
  90      * @param string        $operator        SQL operator that needs to be prepended to sql_where,
  91      *                                    if it is not empty.
  92      * @param string        $column_prefix    Prefix that needs to be prepended to column names
  93      * @return string        Returns additional where statements to narrow down the tree,
  94      *                        prefixed with operator and prepended column_prefix to column names
  95      */
  96  	public function get_sql_where($operator = 'AND', $column_prefix = '')
  97      {
  98          return (!$this->sql_where) ? '' : $operator . ' ' . sprintf($this->sql_where, $column_prefix);
  99      }
 100  
 101      /**
 102      * Acquires a lock on the item table
 103      *
 104      * @return bool    True if the lock was acquired, false if it has been acquired previously
 105      *
 106      * @throws \RuntimeException If the lock could not be acquired
 107      */
 108  	protected function acquire_lock()
 109      {
 110          if ($this->lock->owns_lock())
 111          {
 112              return false;
 113          }
 114  
 115          if (!$this->lock->acquire())
 116          {
 117              throw new \RuntimeException($this->message_prefix . 'LOCK_FAILED_ACQUIRE');
 118          }
 119  
 120          return true;
 121      }
 122  
 123      /**
 124      * {@inheritdoc}
 125      */
 126  	public function insert(array $additional_data)
 127      {
 128          $item_data = $this->reset_nestedset_values($additional_data);
 129  
 130          $sql = 'INSERT INTO ' . $this->table_name . ' ' . $this->db->sql_build_array('INSERT', $item_data);
 131          $this->db->sql_query($sql);
 132  
 133          $item_data[$this->column_item_id] = (int) $this->db->sql_nextid();
 134  
 135          return array_merge($item_data, $this->add_item_to_nestedset($item_data[$this->column_item_id]));
 136      }
 137  
 138      /**
 139      * Add an item which already has a database row at the end of the tree
 140      *
 141      * @param int    $item_id    The item to be added
 142      * @return array        Array with updated data, if the item was added successfully
 143      *                    Empty array otherwise
 144      */
 145  	protected function add_item_to_nestedset($item_id)
 146      {
 147          $sql = 'SELECT MAX(' . $this->column_right_id . ') AS ' . $this->column_right_id . '
 148              FROM ' . $this->table_name . '
 149              ' . $this->get_sql_where('WHERE');
 150          $result = $this->db->sql_query($sql);
 151          $current_max_right_id = (int) $this->db->sql_fetchfield($this->column_right_id);
 152          $this->db->sql_freeresult($result);
 153  
 154          $update_item_data = array(
 155              $this->column_parent_id        => 0,
 156              $this->column_left_id        => $current_max_right_id + 1,
 157              $this->column_right_id        => $current_max_right_id + 2,
 158              $this->column_item_parents    => '',
 159          );
 160  
 161          $sql = 'UPDATE ' . $this->table_name . '
 162              SET ' . $this->db->sql_build_array('UPDATE', $update_item_data) . '
 163              WHERE ' . $this->column_item_id . ' = ' . (int) $item_id . '
 164                  AND ' . $this->column_parent_id . ' = 0
 165                  AND ' . $this->column_left_id . ' = 0
 166                  AND ' . $this->column_right_id . ' = 0';
 167          $this->db->sql_query($sql);
 168  
 169          return ($this->db->sql_affectedrows() == 1) ? $update_item_data : array();
 170      }
 171  
 172      /**
 173      * Remove an item from the tree without deleting it from the database
 174      *
 175      * Also removes all subitems from the tree without deleting them from the database either
 176      *
 177      * @param int    $item_id    The item to be deleted
 178      * @return array        Item ids that have been removed
 179      * @throws \OutOfBoundsException
 180      */
 181  	protected function remove_item_from_nestedset($item_id)
 182      {
 183          $item_id = (int) $item_id;
 184          if (!$item_id)
 185          {
 186              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
 187          }
 188  
 189          $items = $this->get_subtree_data($item_id);
 190          $item_ids = array_keys($items);
 191  
 192          if (empty($items) || !isset($items[$item_id]))
 193          {
 194              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
 195          }
 196  
 197          $this->remove_subset($item_ids, $items[$item_id]);
 198  
 199          return $item_ids;
 200      }
 201  
 202      /**
 203      * {@inheritdoc}
 204      */
 205  	public function delete($item_id)
 206      {
 207          $removed_items = $this->remove_item_from_nestedset($item_id);
 208  
 209          $sql = 'DELETE FROM ' . $this->table_name . '
 210              WHERE ' . $this->db->sql_in_set($this->column_item_id, $removed_items) . '
 211              ' . $this->get_sql_where('AND');
 212          $this->db->sql_query($sql);
 213  
 214          return $removed_items;
 215      }
 216  
 217      /**
 218      * {@inheritdoc}
 219      */
 220  	public function move($item_id, $delta)
 221      {
 222          if ($delta == 0)
 223          {
 224              return false;
 225          }
 226  
 227          $this->acquire_lock();
 228  
 229          $action = ($delta > 0) ? 'move_up' : 'move_down';
 230          $delta = abs($delta);
 231  
 232          // Keep $this->get_sql_where() here, to ensure we are in the right tree.
 233          $sql = 'SELECT *
 234              FROM ' . $this->table_name . '
 235              WHERE ' . $this->column_item_id . ' = ' . (int) $item_id . '
 236                  ' . $this->get_sql_where();
 237          $result = $this->db->sql_query_limit($sql, $delta);
 238          $item = $this->db->sql_fetchrow($result);
 239          $this->db->sql_freeresult($result);
 240  
 241          if (!$item)
 242          {
 243              $this->lock->release();
 244              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
 245          }
 246  
 247          /**
 248          * Fetch all the siblings between the item's current spot
 249          * and where we want to move it to. If there are less than $delta
 250          * siblings between the current spot and the target then the
 251          * item will move as far as possible
 252          */
 253          $sql = "SELECT {$this->column_item_id}, {$this->column_parent_id}, {$this->column_left_id}, {$this->column_right_id}, {$this->column_item_parents}
 254              FROM " . $this->table_name . '
 255              WHERE ' . $this->column_parent_id . ' = ' . (int) $item[$this->column_parent_id] . '
 256                  ' . $this->get_sql_where() . '
 257                  AND ';
 258  
 259          if ($action == 'move_up')
 260          {
 261              $sql .= $this->column_right_id . ' < ' . (int) $item[$this->column_right_id] . ' ORDER BY ' . $this->column_right_id . ' DESC';
 262          }
 263          else
 264          {
 265              $sql .= $this->column_left_id . ' > ' . (int) $item[$this->column_left_id] . ' ORDER BY ' . $this->column_left_id . ' ASC';
 266          }
 267  
 268          $result = $this->db->sql_query_limit($sql, $delta);
 269  
 270          $target = false;
 271          while ($row = $this->db->sql_fetchrow($result))
 272          {
 273              $target = $row;
 274          }
 275          $this->db->sql_freeresult($result);
 276  
 277          if (!$target)
 278          {
 279              $this->lock->release();
 280              // The item is already on top or bottom
 281              return false;
 282          }
 283  
 284          /**
 285          * $left_id and $right_id define the scope of the items that are affected by the move.
 286          * $diff_up and $diff_down are the values to substract or add to each item's left_id
 287          * and right_id in order to move them up or down.
 288          * $move_up_left and $move_up_right define the scope of the items that are moving
 289          * up. Other items in the scope of ($left_id, $right_id) are considered to move down.
 290          */
 291          if ($action == 'move_up')
 292          {
 293              $left_id = (int) $target[$this->column_left_id];
 294              $right_id = (int) $item[$this->column_right_id];
 295  
 296              $diff_up = (int) $item[$this->column_left_id] - (int) $target[$this->column_left_id];
 297              $diff_down = (int) $item[$this->column_right_id] + 1 - (int) $item[$this->column_left_id];
 298  
 299              $move_up_left = (int) $item[$this->column_left_id];
 300              $move_up_right = (int) $item[$this->column_right_id];
 301          }
 302          else
 303          {
 304              $left_id = (int) $item[$this->column_left_id];
 305              $right_id = (int) $target[$this->column_right_id];
 306  
 307              $diff_up = (int) $item[$this->column_right_id] + 1 - (int) $item[$this->column_left_id];
 308              $diff_down = (int) $target[$this->column_right_id] - (int) $item[$this->column_right_id];
 309  
 310              $move_up_left = (int) $item[$this->column_right_id] + 1;
 311              $move_up_right = (int) $target[$this->column_right_id];
 312          }
 313  
 314          // Now do the dirty job
 315          $sql = 'UPDATE ' . $this->table_name . '
 316              SET ' . $this->column_left_id . ' = ' . $this->column_left_id . ' + CASE
 317                  WHEN ' . $this->column_left_id . " BETWEEN {$move_up_left} AND {$move_up_right} THEN -{$diff_up}
 318                  ELSE {$diff_down}
 319              END,
 320              " . $this->column_right_id . ' = ' . $this->column_right_id . ' + CASE
 321                  WHEN ' . $this->column_right_id . " BETWEEN {$move_up_left} AND {$move_up_right} THEN -{$diff_up}
 322                  ELSE {$diff_down}
 323              END
 324              WHERE
 325                  " . $this->column_left_id . " BETWEEN {$left_id} AND {$right_id}
 326                  AND " . $this->column_right_id . " BETWEEN {$left_id} AND {$right_id}
 327                  " . $this->get_sql_where();
 328          $this->db->sql_query($sql);
 329  
 330          $this->lock->release();
 331  
 332          return true;
 333      }
 334  
 335      /**
 336      * {@inheritdoc}
 337      */
 338  	public function move_down($item_id)
 339      {
 340          return $this->move($item_id, -1);
 341      }
 342  
 343      /**
 344      * {@inheritdoc}
 345      */
 346  	public function move_up($item_id)
 347      {
 348          return $this->move($item_id, 1);
 349      }
 350  
 351      /**
 352      * {@inheritdoc}
 353      */
 354  	public function move_children($current_parent_id, $new_parent_id)
 355      {
 356          $current_parent_id = (int) $current_parent_id;
 357          $new_parent_id = (int) $new_parent_id;
 358  
 359          if ($current_parent_id == $new_parent_id)
 360          {
 361              return false;
 362          }
 363  
 364          if (!$current_parent_id)
 365          {
 366              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
 367          }
 368  
 369          $this->acquire_lock();
 370  
 371          $item_data = $this->get_subtree_data($current_parent_id);
 372          if (!isset($item_data[$current_parent_id]))
 373          {
 374              $this->lock->release();
 375              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
 376          }
 377  
 378          $current_parent = $item_data[$current_parent_id];
 379          unset($item_data[$current_parent_id]);
 380          $move_items = array_keys($item_data);
 381  
 382          if (($current_parent[$this->column_right_id] - $current_parent[$this->column_left_id]) <= 1)
 383          {
 384              $this->lock->release();
 385              return false;
 386          }
 387  
 388          if (in_array($new_parent_id, $move_items))
 389          {
 390              $this->lock->release();
 391              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT');
 392          }
 393  
 394          $sql_exclude_moved_items = $this->db->sql_in_set($this->column_item_id, $move_items, true);
 395  
 396          $this->db->sql_transaction('begin');
 397  
 398          $this->remove_subset($move_items, $current_parent, false, true);
 399  
 400          if ($new_parent_id)
 401          {
 402              // Retrieve new-parent again, it may have been changed...
 403              $sql = 'SELECT *
 404                  FROM ' . $this->table_name . '
 405                  WHERE ' . $this->column_item_id . ' = ' . $new_parent_id;
 406              $result = $this->db->sql_query($sql);
 407              $new_parent = $this->db->sql_fetchrow($result);
 408              $this->db->sql_freeresult($result);
 409  
 410              if (!$new_parent)
 411              {
 412                  $this->db->sql_transaction('rollback');
 413                  $this->lock->release();
 414                  throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT');
 415              }
 416  
 417              $new_right_id = $this->prepare_adding_subset($move_items, $new_parent, true);
 418  
 419              if ($new_right_id > $current_parent[$this->column_right_id])
 420              {
 421                  $diff = ' + ' . ($new_right_id - $current_parent[$this->column_right_id]);
 422              }
 423              else
 424              {
 425                  $diff = ' - ' . abs($new_right_id - $current_parent[$this->column_right_id]);
 426              }
 427          }
 428          else
 429          {
 430              $sql = 'SELECT MAX(' . $this->column_right_id . ') AS ' . $this->column_right_id . '
 431                  FROM ' . $this->table_name . '
 432                  WHERE ' . $sql_exclude_moved_items . '
 433                      ' . $this->get_sql_where('AND');
 434              $result = $this->db->sql_query($sql);
 435              $row = $this->db->sql_fetchrow($result);
 436              $this->db->sql_freeresult($result);
 437  
 438              $diff = ' + ' . ($row[$this->column_right_id] - $current_parent[$this->column_left_id]);
 439          }
 440  
 441          $sql = 'UPDATE ' . $this->table_name . '
 442              SET ' . $this->column_left_id . ' = ' . $this->column_left_id . $diff . ',
 443                  ' . $this->column_right_id . ' = ' . $this->column_right_id . $diff . ',
 444                  ' . $this->column_parent_id . ' = ' . $this->db->sql_case($this->column_parent_id . ' = ' . $current_parent_id, $new_parent_id, $this->column_parent_id) . ',
 445                  ' . $this->column_item_parents . " = ''
 446              WHERE " . $this->db->sql_in_set($this->column_item_id, $move_items) . '
 447                  ' . $this->get_sql_where('AND');
 448          $this->db->sql_query($sql);
 449  
 450          $this->db->sql_transaction('commit');
 451          $this->lock->release();
 452  
 453          return true;
 454      }
 455  
 456      /**
 457      * {@inheritdoc}
 458      */
 459  	public function change_parent($item_id, $new_parent_id)
 460      {
 461          $item_id = (int) $item_id;
 462          $new_parent_id = (int) $new_parent_id;
 463  
 464          if ($item_id == $new_parent_id)
 465          {
 466              return false;
 467          }
 468  
 469          if (!$item_id)
 470          {
 471              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
 472          }
 473  
 474          $this->acquire_lock();
 475  
 476          $item_data = $this->get_subtree_data($item_id);
 477          if (!isset($item_data[$item_id]))
 478          {
 479              $this->lock->release();
 480              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
 481          }
 482  
 483          $item = $item_data[$item_id];
 484          $move_items = array_keys($item_data);
 485  
 486          if (in_array($new_parent_id, $move_items))
 487          {
 488              $this->lock->release();
 489              throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT');
 490          }
 491  
 492          $sql_exclude_moved_items = $this->db->sql_in_set($this->column_item_id, $move_items, true);
 493  
 494          $this->db->sql_transaction('begin');
 495  
 496          $this->remove_subset($move_items, $item, false, true);
 497  
 498          if ($new_parent_id)
 499          {
 500              // Retrieve new-parent again, it may have been changed...
 501              $sql = 'SELECT *
 502                  FROM ' . $this->table_name . '
 503                  WHERE ' . $this->column_item_id . ' = ' . $new_parent_id;
 504              $result = $this->db->sql_query($sql);
 505              $new_parent = $this->db->sql_fetchrow($result);
 506              $this->db->sql_freeresult($result);
 507  
 508              if (!$new_parent)
 509              {
 510                  $this->db->sql_transaction('rollback');
 511                  $this->lock->release();
 512                  throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT');
 513              }
 514  
 515              $new_right_id = $this->prepare_adding_subset($move_items, $new_parent, true);
 516  
 517              if ($new_right_id > (int) $item[$this->column_right_id])
 518              {
 519                  $diff = ' + ' . ($new_right_id - (int) $item[$this->column_right_id] - 1);
 520              }
 521              else
 522              {
 523                  $diff = ' - ' . abs($new_right_id - (int) $item[$this->column_right_id] - 1);
 524              }
 525          }
 526          else
 527          {
 528              $sql = 'SELECT MAX(' . $this->column_right_id . ') AS ' . $this->column_right_id . '
 529                  FROM ' . $this->table_name . '
 530                  WHERE ' . $sql_exclude_moved_items . '
 531                      ' . $this->get_sql_where('AND');
 532              $result = $this->db->sql_query($sql);
 533              $row = $this->db->sql_fetchrow($result);
 534              $this->db->sql_freeresult($result);
 535  
 536              $diff = ' + ' . ((int) $row[$this->column_right_id] - (int) $item[$this->column_left_id] + 1);
 537          }
 538  
 539          $sql = 'UPDATE ' . $this->table_name . '
 540              SET ' . $this->column_left_id . ' = ' . $this->column_left_id . $diff . ',
 541                  ' . $this->column_right_id . ' = ' . $this->column_right_id . $diff . ',
 542                  ' . $this->column_parent_id . ' = ' . $this->db->sql_case($this->column_item_id . ' = ' . $item_id, $new_parent_id, $this->column_parent_id) . ',
 543                  ' . $this->column_item_parents . " = ''
 544              WHERE " . $this->db->sql_in_set($this->column_item_id, $move_items) . '
 545                  ' . $this->get_sql_where('AND');
 546          $this->db->sql_query($sql);
 547  
 548          $this->db->sql_transaction('commit');
 549          $this->lock->release();
 550  
 551          return true;
 552      }
 553  
 554      /**
 555      * {@inheritdoc}
 556      */
 557  	public function get_path_and_subtree_data($item_id, $order_asc = true, $include_item = true)
 558      {
 559          $condition = 'i2.' . $this->column_left_id . ' BETWEEN i1.' . $this->column_left_id . ' AND i1.' . $this->column_right_id . '
 560              OR i1.' . $this->column_left_id . ' BETWEEN i2.' . $this->column_left_id . ' AND i2.' . $this->column_right_id;
 561  
 562          return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item);
 563      }
 564  
 565      /**
 566      * {@inheritdoc}
 567      */
 568  	public function get_path_data($item_id, $order_asc = true, $include_item = true)
 569      {
 570          $condition = 'i1.' . $this->column_left_id . ' BETWEEN i2.' . $this->column_left_id . ' AND i2.' . $this->column_right_id . '';
 571  
 572          return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item);
 573      }
 574  
 575      /**
 576      * {@inheritdoc}
 577      */
 578  	public function get_subtree_data($item_id, $order_asc = true, $include_item = true)
 579      {
 580          $condition = 'i2.' . $this->column_left_id . ' BETWEEN i1.' . $this->column_left_id . ' AND i1.' . $this->column_right_id . '';
 581  
 582          return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item);
 583      }
 584  
 585      /**
 586      * Get items that are related to the given item by the condition
 587      *
 588      * @param int        $item_id        Id of the item to retrieve the node set from
 589      * @param string        $condition        Query string restricting the item list
 590      * @param bool        $order_asc        Order the items ascending by their left_id
 591      * @param bool        $include_item    Should the item matching the given item id be included in the list as well
 592      * @return array            Array of items (containing all columns from the item table)
 593      *                            ID => Item data
 594      */
 595  	protected function get_set_of_nodes_data($item_id, $condition, $order_asc = true, $include_item = true)
 596      {
 597          $rows = array();
 598  
 599          $sql = 'SELECT i2.*
 600              FROM ' . $this->table_name . ' i1
 601              LEFT JOIN ' . $this->table_name . " i2
 602                  ON (($condition) " . $this->get_sql_where('AND', 'i2.') . ')
 603              WHERE i1.' . $this->column_item_id . ' = ' . (int) $item_id . '
 604                  ' . $this->get_sql_where('AND', 'i1.') . '
 605              ORDER BY i2.' . $this->column_left_id . ' ' . ($order_asc ? 'ASC' : 'DESC');
 606          $result = $this->db->sql_query($sql);
 607  
 608          while ($row = $this->db->sql_fetchrow($result))
 609          {
 610              if (!$include_item && $item_id == $row[$this->column_item_id])
 611              {
 612                  continue;
 613              }
 614  
 615              $rows[(int) $row[$this->column_item_id]] = $row;
 616          }
 617          $this->db->sql_freeresult($result);
 618  
 619          return $rows;
 620      }
 621  
 622      /**
 623      * Get basic data of all parent items
 624      *
 625      * Basic data is defined in the $item_basic_data property.
 626      * Data is cached in the item_parents column in the item table
 627      *
 628      * @param array    $item        The item to get the path from
 629      * @return array            Array of items (containing basic columns from the item table)
 630      *                            ID => Item data
 631      */
 632  	public function get_path_basic_data(array $item)
 633      {
 634          $parents = array();
 635          if ($item[$this->column_parent_id])
 636          {
 637              if (!$item[$this->column_item_parents])
 638              {
 639                  $sql = 'SELECT ' . implode(', ', $this->item_basic_data) . '
 640                      FROM ' . $this->table_name . '
 641                      WHERE ' . $this->column_left_id . ' < ' . (int) $item[$this->column_left_id] . '
 642                          AND ' . $this->column_right_id . ' > ' . (int) $item[$this->column_right_id] . '
 643                          ' . $this->get_sql_where('AND') . '
 644                      ORDER BY ' . $this->column_left_id . ' ASC';
 645                  $result = $this->db->sql_query($sql);
 646  
 647                  while ($row = $this->db->sql_fetchrow($result))
 648                  {
 649                      $parents[$row[$this->column_item_id]] = $row;
 650                  }
 651                  $this->db->sql_freeresult($result);
 652  
 653                  $item_parents = serialize($parents);
 654  
 655                  $sql = 'UPDATE ' . $this->table_name . '
 656                      SET ' . $this->column_item_parents . " = '" . $this->db->sql_escape($item_parents) . "'
 657                      WHERE " . $this->column_parent_id . ' = ' . (int) $item[$this->column_parent_id];
 658                  $this->db->sql_query($sql);
 659              }
 660              else
 661              {
 662                  $parents = unserialize($item[$this->column_item_parents]);
 663              }
 664          }
 665  
 666          return $parents;
 667      }
 668  
 669      /**
 670      * Get all items from the tree
 671      *
 672      * @param bool        $order_asc        Order the items ascending by their left_id
 673      * @return array            Array of items (containing all columns from the item table)
 674      *                            ID => Item data
 675      */
 676  	public function get_all_tree_data($order_asc = true)
 677      {
 678          $rows = array();
 679  
 680          $sql = 'SELECT *
 681              FROM ' . $this->table_name . ' ' .
 682              $this->get_sql_where('WHERE') . '
 683              ORDER BY ' . $this->column_left_id . ' ' . ($order_asc ? 'ASC' : 'DESC');
 684          $result = $this->db->sql_query($sql);
 685  
 686          while ($row = $this->db->sql_fetchrow($result))
 687          {
 688              $rows[(int) $row[$this->column_item_id]] = $row;
 689          }
 690          $this->db->sql_freeresult($result);
 691  
 692          return $rows;
 693      }
 694  
 695      /**
 696      * Remove a subset from the nested set
 697      *
 698      * @param array    $subset_items        Subset of items to remove
 699      * @param array    $bounding_item        Item containing the right bound of the subset
 700      * @param bool    $set_subset_zero    Should the parent, left and right id of the items be set to 0, or kept unchanged?
 701      *                                    In case of removing an item from the tree, we should the values to 0
 702      *                                    In case of moving an item, we shouldkeep the original values, in order to allow "+ diff" later
 703      * @return    null
 704      */
 705  	protected function remove_subset(array $subset_items, array $bounding_item, $set_subset_zero = true)
 706      {
 707          $acquired_new_lock = $this->acquire_lock();
 708  
 709          $diff = count($subset_items) * 2;
 710          $sql_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items);
 711          $sql_not_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items, true);
 712  
 713          $sql_is_parent = $this->column_left_id . ' <= ' . (int) $bounding_item[$this->column_right_id] . '
 714              AND ' . $this->column_right_id . ' >= ' . (int) $bounding_item[$this->column_right_id];
 715  
 716          $sql_is_right = $this->column_left_id . ' > ' . (int) $bounding_item[$this->column_right_id];
 717  
 718          $set_left_id = $this->db->sql_case($sql_is_right, $this->column_left_id . ' - ' . $diff, $this->column_left_id);
 719          $set_right_id = $this->db->sql_case($sql_is_parent . ' OR ' . $sql_is_right, $this->column_right_id . ' - ' . $diff, $this->column_right_id);
 720  
 721          if ($set_subset_zero)
 722          {
 723              $set_left_id = $this->db->sql_case($sql_subset_items, 0, $set_left_id);
 724              $set_right_id = $this->db->sql_case($sql_subset_items, 0, $set_right_id);
 725          }
 726  
 727          $sql = 'UPDATE ' . $this->table_name . '
 728              SET ' . (($set_subset_zero) ? $this->column_parent_id . ' = ' . $this->db->sql_case($sql_subset_items, 0, $this->column_parent_id) . ',' : '') . '
 729                  ' . $this->column_left_id . ' = ' . $set_left_id . ',
 730                  ' . $this->column_right_id . ' = ' . $set_right_id . '
 731              ' . ((!$set_subset_zero) ? ' WHERE ' . $sql_not_subset_items . ' ' . $this->get_sql_where('AND') : $this->get_sql_where('WHERE'));
 732          $this->db->sql_query($sql);
 733  
 734          if ($acquired_new_lock)
 735          {
 736              $this->lock->release();
 737          }
 738      }
 739  
 740      /**
 741      * Prepare adding a subset to the nested set
 742      *
 743      * @param array    $subset_items        Subset of items to add
 744      * @param array    $new_parent    Item containing the right bound of the new parent
 745      * @return    int        New right id of the parent item
 746      */
 747  	protected function prepare_adding_subset(array $subset_items, array $new_parent)
 748      {
 749          $diff = count($subset_items) * 2;
 750          $sql_not_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items, true);
 751  
 752          $set_left_id = $this->db->sql_case($this->column_left_id . ' > ' . (int) $new_parent[$this->column_right_id], $this->column_left_id . ' + ' . $diff, $this->column_left_id);
 753          $set_right_id = $this->db->sql_case($this->column_right_id . ' >= ' . (int) $new_parent[$this->column_right_id], $this->column_right_id . ' + ' . $diff, $this->column_right_id);
 754  
 755          $sql = 'UPDATE ' . $this->table_name . '
 756              SET ' . $this->column_left_id . ' = ' . $set_left_id . ',
 757                  ' . $this->column_right_id . ' = ' . $set_right_id . '
 758              WHERE ' . $sql_not_subset_items . '
 759                  ' . $this->get_sql_where('AND');
 760          $this->db->sql_query($sql);
 761  
 762          return $new_parent[$this->column_right_id] + $diff;
 763      }
 764  
 765      /**
 766      * Resets values required for the nested set system
 767      *
 768      * @param array    $item        Original item data
 769      * @return    array        Original item data + nested set defaults
 770      */
 771  	protected function reset_nestedset_values(array $item)
 772      {
 773          $item_data = array_merge($item, array(
 774              $this->column_parent_id        => 0,
 775              $this->column_left_id        => 0,
 776              $this->column_right_id        => 0,
 777              $this->column_item_parents    => '',
 778          ));
 779  
 780          unset($item_data[$this->column_item_id]);
 781  
 782          return $item_data;
 783      }
 784  
 785      /**
 786      * Regenerate left/right ids from parent/child relationship
 787      *
 788      * This method regenerates the left/right ids for the tree based on
 789      * the parent/child relations. This function executes three queries per
 790      * item, so it should only be called, when the set has one of the following
 791      * problems:
 792      *    - The set has a duplicated value inside the left/right id chain
 793      *    - The set has a missing value inside the left/right id chain
 794      *    - The set has items that do not have a left/right id set
 795      *
 796      * When regenerating the items, the items are sorted by parent id and their
 797      * current left id, so the current child/parent relationships are kept
 798      * and running the function on a working set will not change the order.
 799      *
 800      * @param int    $new_id        First left_id to be used (should start with 1)
 801      * @param int    $parent_id    parent_id of the current set (default = 0)
 802      * @param bool    $reset_ids    Should we reset all left_id/right_id on the first call?
 803      * @return    int        $new_id        The next left_id/right_id that should be used
 804      */
 805  	public function regenerate_left_right_ids($new_id, $parent_id = 0, $reset_ids = false)
 806      {
 807          if ($acquired_new_lock = $this->acquire_lock())
 808          {
 809              $this->db->sql_transaction('begin');
 810  
 811              if (!$reset_ids)
 812              {
 813                  $sql = 'UPDATE ' . $this->table_name . '
 814                      SET ' . $this->column_item_parents . " = ''
 815                      " . $this->get_sql_where('WHERE');
 816                  $this->db->sql_query($sql);
 817              }
 818          }
 819  
 820          if ($reset_ids)
 821          {
 822              $sql = 'UPDATE ' . $this->table_name . '
 823                  SET ' . $this->db->sql_build_array('UPDATE', array(
 824                      $this->column_left_id        => 0,
 825                      $this->column_right_id        => 0,
 826                      $this->column_item_parents    => '',
 827                  )) . '
 828                  ' . $this->get_sql_where('WHERE');
 829              $this->db->sql_query($sql);
 830          }
 831  
 832          $sql = 'SELECT *
 833              FROM ' . $this->table_name . '
 834              WHERE ' . $this->column_parent_id . ' = ' . (int) $parent_id . '
 835                  ' . $this->get_sql_where('AND') . '
 836              ORDER BY ' . $this->column_left_id . ', ' . $this->column_item_id . ' ASC';
 837          $result = $this->db->sql_query($sql);
 838          $rows = $this->db->sql_fetchrowset($result);
 839          $this->db->sql_freeresult($result);
 840  
 841          foreach ($rows as $row)
 842          {
 843              // First we update the left_id for this module
 844              if ($row[$this->column_left_id] != $new_id)
 845              {
 846                  $sql = 'UPDATE ' . $this->table_name . '
 847                      SET ' . $this->db->sql_build_array('UPDATE', array($this->column_left_id => $new_id)) . '
 848                      WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id];
 849                  $this->db->sql_query($sql);
 850              }
 851              $new_id++;
 852  
 853              // Then we go through any children and update their left/right id's
 854              $new_id = $this->regenerate_left_right_ids($new_id, $row[$this->column_item_id]);
 855  
 856              // Then we come back and update the right_id for this module
 857              if ($row[$this->column_right_id] != $new_id)
 858              {
 859                  $sql = 'UPDATE ' . $this->table_name . '
 860                      SET ' . $this->db->sql_build_array('UPDATE', array($this->column_right_id => $new_id)) . '
 861                      WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id];
 862                  $this->db->sql_query($sql);
 863              }
 864              $new_id++;
 865          }
 866  
 867          if ($acquired_new_lock)
 868          {
 869              $this->db->sql_transaction('commit');
 870              $this->lock->release();
 871          }
 872  
 873          return $new_id;
 874      }
 875  }


Generated: Tue Apr 7 19:42:26 2020 Cross-referenced by PHPXref 0.7.1