Code Coverage |
||||||||||
Classes and Traits |
Functions and Methods |
Lines |
||||||||
| Total | |
0.00% |
0 / 1 |
|
86.36% |
19 / 22 |
CRAP | |
98.77% |
401 / 406 |
| nestedset | |
0.00% |
0 / 1 |
|
86.36% |
19 / 22 |
74 | |
98.77% |
401 / 406 |
| __construct | |
100.00% |
1 / 1 |
4 | |
100.00% |
14 / 14 |
|||
| get_sql_where | |
100.00% |
1 / 1 |
2 | |
100.00% |
1 / 1 |
|||
| acquire_lock | |
0.00% |
0 / 1 |
3.03 | |
85.71% |
6 / 7 |
|||
| insert | |
100.00% |
1 / 1 |
1 | |
100.00% |
5 / 5 |
|||
| add_item_to_nestedset | |
100.00% |
1 / 1 |
2 | |
100.00% |
19 / 19 |
|||
| remove_item_from_nestedset | |
100.00% |
1 / 1 |
4 | |
100.00% |
11 / 11 |
|||
| delete | |
100.00% |
1 / 1 |
1 | |
100.00% |
6 / 6 |
|||
| move | |
0.00% |
0 / 1 |
8 | |
95.31% |
61 / 64 |
|||
| move_down | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| move_up | |
100.00% |
1 / 1 |
1 | |
100.00% |
1 / 1 |
|||
| move_children | |
100.00% |
1 / 1 |
9 | |
100.00% |
65 / 65 |
|||
| change_parent | |
0.00% |
0 / 1 |
8 | |
98.33% |
59 / 60 |
|||
| get_path_and_subtree_data | |
100.00% |
1 / 1 |
1 | |
100.00% |
3 / 3 |
|||
| get_path_data | |
100.00% |
1 / 1 |
1 | |
100.00% |
2 / 2 |
|||
| get_subtree_data | |
100.00% |
1 / 1 |
1 | |
100.00% |
2 / 2 |
|||
| get_set_of_nodes_data | |
100.00% |
1 / 1 |
5 | |
100.00% |
16 / 16 |
|||
| get_path_basic_data | |
100.00% |
1 / 1 |
4 | |
100.00% |
25 / 25 |
|||
| get_all_tree_data | |
100.00% |
1 / 1 |
3 | |
100.00% |
10 / 10 |
|||
| remove_subset | |
100.00% |
1 / 1 |
5 | |
100.00% |
23 / 23 |
|||
| prepare_adding_subset | |
100.00% |
1 / 1 |
1 | |
100.00% |
11 / 11 |
|||
| reset_nestedset_values | |
100.00% |
1 / 1 |
1 | |
100.00% |
8 / 8 |
|||
| regenerate_left_right_ids | |
100.00% |
1 / 1 |
8 | |
100.00% |
52 / 52 |
|||
| <?php | |
| /** | |
| * | |
| * This file is part of the phpBB Forum Software package. | |
| * | |
| * @copyright (c) phpBB Limited <https://www.phpbb.com> | |
| * @license GNU General Public License, version 2 (GPL-2.0) | |
| * | |
| * For full copyright and license information, please see | |
| * the docs/CREDITS.txt file. | |
| * | |
| */ | |
| namespace phpbb\tree; | |
| abstract class nestedset implements \phpbb\tree\tree_interface | |
| { | |
| /** @var \phpbb\db\driver\driver_interface */ | |
| protected $db; | |
| /** @var \phpbb\lock\db */ | |
| protected $lock; | |
| /** @var string */ | |
| protected $table_name; | |
| /** | |
| * Prefix for the language keys returned by exceptions | |
| * @var string | |
| */ | |
| protected $message_prefix = ''; | |
| /** | |
| * Column names in the table | |
| * @var string | |
| */ | |
| protected $column_item_id = 'item_id'; | |
| protected $column_left_id = 'left_id'; | |
| protected $column_right_id = 'right_id'; | |
| protected $column_parent_id = 'parent_id'; | |
| protected $column_item_parents = 'item_parents'; | |
| /** | |
| * Additional SQL restrictions | |
| * Allows to have multiple nested sets in one table | |
| * @var string | |
| */ | |
| protected $sql_where = ''; | |
| /** | |
| * List of item properties to be cached in the item_parents column | |
| * @var array | |
| */ | |
| protected $item_basic_data = array('*'); | |
| /** | |
| * Construct | |
| * | |
| * @param \phpbb\db\driver\driver_interface $db Database connection | |
| * @param \phpbb\lock\db $lock Lock class used to lock the table when moving forums around | |
| * @param string $table_name Table name | |
| * @param string $message_prefix Prefix for the messages thrown by exceptions | |
| * @param string $sql_where Additional SQL restrictions for the queries | |
| * @param array $item_basic_data Array with basic item data that is stored in item_parents | |
| * @param array $columns Array with column names to overwrite | |
| */ | |
| 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()) | |
| { | |
| $this->db = $db; | |
| $this->lock = $lock; | |
| $this->table_name = $table_name; | |
| $this->message_prefix = $message_prefix; | |
| $this->sql_where = $sql_where; | |
| $this->item_basic_data = (!empty($item_basic_data)) ? $item_basic_data : array('*'); | |
| if (!empty($columns)) | |
| { | |
| foreach ($columns as $column => $name) | |
| { | |
| $column_name = 'column_' . $column; | |
| $this->$column_name = $name; | |
| } | |
| } | |
| } | |
| /** | |
| * Returns additional sql where restrictions | |
| * | |
| * @param string $operator SQL operator that needs to be prepended to sql_where, | |
| * if it is not empty. | |
| * @param string $column_prefix Prefix that needs to be prepended to column names | |
| * @return string Returns additional where statements to narrow down the tree, | |
| * prefixed with operator and prepended column_prefix to column names | |
| */ | |
| public function get_sql_where($operator = 'AND', $column_prefix = '') | |
| { | |
| return (!$this->sql_where) ? '' : $operator . ' ' . sprintf($this->sql_where, $column_prefix); | |
| } | |
| /** | |
| * Acquires a lock on the item table | |
| * | |
| * @return bool True if the lock was acquired, false if it has been acquired previously | |
| * | |
| * @throws \RuntimeException If the lock could not be acquired | |
| */ | |
| protected function acquire_lock() | |
| { | |
| if ($this->lock->owns_lock()) | |
| { | |
| return false; | |
| } | |
| if (!$this->lock->acquire()) | |
| { | |
| throw new \RuntimeException($this->message_prefix . 'LOCK_FAILED_ACQUIRE'); | |
| } | |
| return true; | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function insert(array $additional_data) | |
| { | |
| $item_data = $this->reset_nestedset_values($additional_data); | |
| $sql = 'INSERT INTO ' . $this->table_name . ' ' . $this->db->sql_build_array('INSERT', $item_data); | |
| $this->db->sql_query($sql); | |
| $item_data[$this->column_item_id] = (int) $this->db->sql_nextid(); | |
| return array_merge($item_data, $this->add_item_to_nestedset($item_data[$this->column_item_id])); | |
| } | |
| /** | |
| * Add an item which already has a database row at the end of the tree | |
| * | |
| * @param int $item_id The item to be added | |
| * @return array Array with updated data, if the item was added successfully | |
| * Empty array otherwise | |
| */ | |
| protected function add_item_to_nestedset($item_id) | |
| { | |
| $sql = 'SELECT MAX(' . $this->column_right_id . ') AS ' . $this->column_right_id . ' | |
| FROM ' . $this->table_name . ' | |
| ' . $this->get_sql_where('WHERE'); | |
| $result = $this->db->sql_query($sql); | |
| $current_max_right_id = (int) $this->db->sql_fetchfield($this->column_right_id); | |
| $this->db->sql_freeresult($result); | |
| $update_item_data = array( | |
| $this->column_parent_id => 0, | |
| $this->column_left_id => $current_max_right_id + 1, | |
| $this->column_right_id => $current_max_right_id + 2, | |
| $this->column_item_parents => '', | |
| ); | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->db->sql_build_array('UPDATE', $update_item_data) . ' | |
| WHERE ' . $this->column_item_id . ' = ' . (int) $item_id . ' | |
| AND ' . $this->column_parent_id . ' = 0 | |
| AND ' . $this->column_left_id . ' = 0 | |
| AND ' . $this->column_right_id . ' = 0'; | |
| $this->db->sql_query($sql); | |
| return ($this->db->sql_affectedrows() == 1) ? $update_item_data : array(); | |
| } | |
| /** | |
| * Remove an item from the tree without deleting it from the database | |
| * | |
| * Also removes all subitems from the tree without deleting them from the database either | |
| * | |
| * @param int $item_id The item to be deleted | |
| * @return array Item ids that have been removed | |
| * @throws \OutOfBoundsException | |
| */ | |
| protected function remove_item_from_nestedset($item_id) | |
| { | |
| $item_id = (int) $item_id; | |
| if (!$item_id) | |
| { | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM'); | |
| } | |
| $items = $this->get_subtree_data($item_id); | |
| $item_ids = array_keys($items); | |
| if (empty($items) || !isset($items[$item_id])) | |
| { | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM'); | |
| } | |
| $this->remove_subset($item_ids, $items[$item_id]); | |
| return $item_ids; | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function delete($item_id) | |
| { | |
| $removed_items = $this->remove_item_from_nestedset($item_id); | |
| $sql = 'DELETE FROM ' . $this->table_name . ' | |
| WHERE ' . $this->db->sql_in_set($this->column_item_id, $removed_items) . ' | |
| ' . $this->get_sql_where('AND'); | |
| $this->db->sql_query($sql); | |
| return $removed_items; | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function move($item_id, $delta) | |
| { | |
| if ($delta == 0) | |
| { | |
| return false; | |
| } | |
| $this->acquire_lock(); | |
| $action = ($delta > 0) ? 'move_up' : 'move_down'; | |
| $delta = abs($delta); | |
| // Keep $this->get_sql_where() here, to ensure we are in the right tree. | |
| $sql = 'SELECT * | |
| FROM ' . $this->table_name . ' | |
| WHERE ' . $this->column_item_id . ' = ' . (int) $item_id . ' | |
| ' . $this->get_sql_where(); | |
| $result = $this->db->sql_query_limit($sql, $delta); | |
| $item = $this->db->sql_fetchrow($result); | |
| $this->db->sql_freeresult($result); | |
| if (!$item) | |
| { | |
| $this->lock->release(); | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM'); | |
| } | |
| /** | |
| * Fetch all the siblings between the item's current spot | |
| * and where we want to move it to. If there are less than $delta | |
| * siblings between the current spot and the target then the | |
| * item will move as far as possible | |
| */ | |
| $sql = "SELECT {$this->column_item_id}, {$this->column_parent_id}, {$this->column_left_id}, {$this->column_right_id}, {$this->column_item_parents} | |
| FROM " . $this->table_name . ' | |
| WHERE ' . $this->column_parent_id . ' = ' . (int) $item[$this->column_parent_id] . ' | |
| ' . $this->get_sql_where() . ' | |
| AND '; | |
| if ($action == 'move_up') | |
| { | |
| $sql .= $this->column_right_id . ' < ' . (int) $item[$this->column_right_id] . ' ORDER BY ' . $this->column_right_id . ' DESC'; | |
| } | |
| else | |
| { | |
| $sql .= $this->column_left_id . ' > ' . (int) $item[$this->column_left_id] . ' ORDER BY ' . $this->column_left_id . ' ASC'; | |
| } | |
| $result = $this->db->sql_query_limit($sql, $delta); | |
| $target = false; | |
| while ($row = $this->db->sql_fetchrow($result)) | |
| { | |
| $target = $row; | |
| } | |
| $this->db->sql_freeresult($result); | |
| if (!$target) | |
| { | |
| $this->lock->release(); | |
| // The item is already on top or bottom | |
| return false; | |
| } | |
| /** | |
| * $left_id and $right_id define the scope of the items that are affected by the move. | |
| * $diff_up and $diff_down are the values to substract or add to each item's left_id | |
| * and right_id in order to move them up or down. | |
| * $move_up_left and $move_up_right define the scope of the items that are moving | |
| * up. Other items in the scope of ($left_id, $right_id) are considered to move down. | |
| */ | |
| if ($action == 'move_up') | |
| { | |
| $left_id = (int) $target[$this->column_left_id]; | |
| $right_id = (int) $item[$this->column_right_id]; | |
| $diff_up = (int) $item[$this->column_left_id] - (int) $target[$this->column_left_id]; | |
| $diff_down = (int) $item[$this->column_right_id] + 1 - (int) $item[$this->column_left_id]; | |
| $move_up_left = (int) $item[$this->column_left_id]; | |
| $move_up_right = (int) $item[$this->column_right_id]; | |
| } | |
| else | |
| { | |
| $left_id = (int) $item[$this->column_left_id]; | |
| $right_id = (int) $target[$this->column_right_id]; | |
| $diff_up = (int) $item[$this->column_right_id] + 1 - (int) $item[$this->column_left_id]; | |
| $diff_down = (int) $target[$this->column_right_id] - (int) $item[$this->column_right_id]; | |
| $move_up_left = (int) $item[$this->column_right_id] + 1; | |
| $move_up_right = (int) $target[$this->column_right_id]; | |
| } | |
| // Now do the dirty job | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->column_left_id . ' = ' . $this->column_left_id . ' + CASE | |
| WHEN ' . $this->column_left_id . " BETWEEN {$move_up_left} AND {$move_up_right} THEN -{$diff_up} | |
| ELSE {$diff_down} | |
| END, | |
| " . $this->column_right_id . ' = ' . $this->column_right_id . ' + CASE | |
| WHEN ' . $this->column_right_id . " BETWEEN {$move_up_left} AND {$move_up_right} THEN -{$diff_up} | |
| ELSE {$diff_down} | |
| END | |
| WHERE | |
| " . $this->column_left_id . " BETWEEN {$left_id} AND {$right_id} | |
| AND " . $this->column_right_id . " BETWEEN {$left_id} AND {$right_id} | |
| " . $this->get_sql_where(); | |
| $this->db->sql_query($sql); | |
| $this->lock->release(); | |
| return true; | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function move_down($item_id) | |
| { | |
| return $this->move($item_id, -1); | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function move_up($item_id) | |
| { | |
| return $this->move($item_id, 1); | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function move_children($current_parent_id, $new_parent_id) | |
| { | |
| $current_parent_id = (int) $current_parent_id; | |
| $new_parent_id = (int) $new_parent_id; | |
| if ($current_parent_id == $new_parent_id) | |
| { | |
| return false; | |
| } | |
| if (!$current_parent_id) | |
| { | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM'); | |
| } | |
| $this->acquire_lock(); | |
| $item_data = $this->get_subtree_data($current_parent_id); | |
| if (!isset($item_data[$current_parent_id])) | |
| { | |
| $this->lock->release(); | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM'); | |
| } | |
| $current_parent = $item_data[$current_parent_id]; | |
| unset($item_data[$current_parent_id]); | |
| $move_items = array_keys($item_data); | |
| if (($current_parent[$this->column_right_id] - $current_parent[$this->column_left_id]) <= 1) | |
| { | |
| $this->lock->release(); | |
| return false; | |
| } | |
| if (in_array($new_parent_id, $move_items)) | |
| { | |
| $this->lock->release(); | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT'); | |
| } | |
| $sql_exclude_moved_items = $this->db->sql_in_set($this->column_item_id, $move_items, true); | |
| $this->db->sql_transaction('begin'); | |
| $this->remove_subset($move_items, $current_parent, false, true); | |
| if ($new_parent_id) | |
| { | |
| // Retrieve new-parent again, it may have been changed... | |
| $sql = 'SELECT * | |
| FROM ' . $this->table_name . ' | |
| WHERE ' . $this->column_item_id . ' = ' . $new_parent_id; | |
| $result = $this->db->sql_query($sql); | |
| $new_parent = $this->db->sql_fetchrow($result); | |
| $this->db->sql_freeresult($result); | |
| if (!$new_parent) | |
| { | |
| $this->db->sql_transaction('rollback'); | |
| $this->lock->release(); | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT'); | |
| } | |
| $new_right_id = $this->prepare_adding_subset($move_items, $new_parent, true); | |
| if ($new_right_id > $current_parent[$this->column_right_id]) | |
| { | |
| $diff = ' + ' . ($new_right_id - $current_parent[$this->column_right_id]); | |
| } | |
| else | |
| { | |
| $diff = ' - ' . abs($new_right_id - $current_parent[$this->column_right_id]); | |
| } | |
| } | |
| else | |
| { | |
| $sql = 'SELECT MAX(' . $this->column_right_id . ') AS ' . $this->column_right_id . ' | |
| FROM ' . $this->table_name . ' | |
| WHERE ' . $sql_exclude_moved_items . ' | |
| ' . $this->get_sql_where('AND'); | |
| $result = $this->db->sql_query($sql); | |
| $row = $this->db->sql_fetchrow($result); | |
| $this->db->sql_freeresult($result); | |
| $diff = ' + ' . ($row[$this->column_right_id] - $current_parent[$this->column_left_id]); | |
| } | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->column_left_id . ' = ' . $this->column_left_id . $diff . ', | |
| ' . $this->column_right_id . ' = ' . $this->column_right_id . $diff . ', | |
| ' . $this->column_parent_id . ' = ' . $this->db->sql_case($this->column_parent_id . ' = ' . $current_parent_id, $new_parent_id, $this->column_parent_id) . ', | |
| ' . $this->column_item_parents . " = '' | |
| WHERE " . $this->db->sql_in_set($this->column_item_id, $move_items) . ' | |
| ' . $this->get_sql_where('AND'); | |
| $this->db->sql_query($sql); | |
| $this->db->sql_transaction('commit'); | |
| $this->lock->release(); | |
| return true; | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function change_parent($item_id, $new_parent_id) | |
| { | |
| $item_id = (int) $item_id; | |
| $new_parent_id = (int) $new_parent_id; | |
| if ($item_id == $new_parent_id) | |
| { | |
| return false; | |
| } | |
| if (!$item_id) | |
| { | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM'); | |
| } | |
| $this->acquire_lock(); | |
| $item_data = $this->get_subtree_data($item_id); | |
| if (!isset($item_data[$item_id])) | |
| { | |
| $this->lock->release(); | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM'); | |
| } | |
| $item = $item_data[$item_id]; | |
| $move_items = array_keys($item_data); | |
| if (in_array($new_parent_id, $move_items)) | |
| { | |
| $this->lock->release(); | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT'); | |
| } | |
| $sql_exclude_moved_items = $this->db->sql_in_set($this->column_item_id, $move_items, true); | |
| $this->db->sql_transaction('begin'); | |
| $this->remove_subset($move_items, $item, false, true); | |
| if ($new_parent_id) | |
| { | |
| // Retrieve new-parent again, it may have been changed... | |
| $sql = 'SELECT * | |
| FROM ' . $this->table_name . ' | |
| WHERE ' . $this->column_item_id . ' = ' . $new_parent_id; | |
| $result = $this->db->sql_query($sql); | |
| $new_parent = $this->db->sql_fetchrow($result); | |
| $this->db->sql_freeresult($result); | |
| if (!$new_parent) | |
| { | |
| $this->db->sql_transaction('rollback'); | |
| $this->lock->release(); | |
| throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT'); | |
| } | |
| $new_right_id = $this->prepare_adding_subset($move_items, $new_parent, true); | |
| if ($new_right_id > (int) $item[$this->column_right_id]) | |
| { | |
| $diff = ' + ' . ($new_right_id - (int) $item[$this->column_right_id] - 1); | |
| } | |
| else | |
| { | |
| $diff = ' - ' . abs($new_right_id - (int) $item[$this->column_right_id] - 1); | |
| } | |
| } | |
| else | |
| { | |
| $sql = 'SELECT MAX(' . $this->column_right_id . ') AS ' . $this->column_right_id . ' | |
| FROM ' . $this->table_name . ' | |
| WHERE ' . $sql_exclude_moved_items . ' | |
| ' . $this->get_sql_where('AND'); | |
| $result = $this->db->sql_query($sql); | |
| $row = $this->db->sql_fetchrow($result); | |
| $this->db->sql_freeresult($result); | |
| $diff = ' + ' . ((int) $row[$this->column_right_id] - (int) $item[$this->column_left_id] + 1); | |
| } | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->column_left_id . ' = ' . $this->column_left_id . $diff . ', | |
| ' . $this->column_right_id . ' = ' . $this->column_right_id . $diff . ', | |
| ' . $this->column_parent_id . ' = ' . $this->db->sql_case($this->column_item_id . ' = ' . $item_id, $new_parent_id, $this->column_parent_id) . ', | |
| ' . $this->column_item_parents . " = '' | |
| WHERE " . $this->db->sql_in_set($this->column_item_id, $move_items) . ' | |
| ' . $this->get_sql_where('AND'); | |
| $this->db->sql_query($sql); | |
| $this->db->sql_transaction('commit'); | |
| $this->lock->release(); | |
| return true; | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function get_path_and_subtree_data($item_id, $order_asc = true, $include_item = true) | |
| { | |
| $condition = 'i2.' . $this->column_left_id . ' BETWEEN i1.' . $this->column_left_id . ' AND i1.' . $this->column_right_id . ' | |
| OR i1.' . $this->column_left_id . ' BETWEEN i2.' . $this->column_left_id . ' AND i2.' . $this->column_right_id; | |
| return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item); | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function get_path_data($item_id, $order_asc = true, $include_item = true) | |
| { | |
| $condition = 'i1.' . $this->column_left_id . ' BETWEEN i2.' . $this->column_left_id . ' AND i2.' . $this->column_right_id . ''; | |
| return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item); | |
| } | |
| /** | |
| * {@inheritdoc} | |
| */ | |
| public function get_subtree_data($item_id, $order_asc = true, $include_item = true) | |
| { | |
| $condition = 'i2.' . $this->column_left_id . ' BETWEEN i1.' . $this->column_left_id . ' AND i1.' . $this->column_right_id . ''; | |
| return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item); | |
| } | |
| /** | |
| * Get items that are related to the given item by the condition | |
| * | |
| * @param int $item_id Id of the item to retrieve the node set from | |
| * @param string $condition Query string restricting the item list | |
| * @param bool $order_asc Order the items ascending by their left_id | |
| * @param bool $include_item Should the item matching the given item id be included in the list as well | |
| * @return array Array of items (containing all columns from the item table) | |
| * ID => Item data | |
| */ | |
| protected function get_set_of_nodes_data($item_id, $condition, $order_asc = true, $include_item = true) | |
| { | |
| $rows = array(); | |
| $sql = 'SELECT i2.* | |
| FROM ' . $this->table_name . ' i1 | |
| LEFT JOIN ' . $this->table_name . " i2 | |
| ON (($condition) " . $this->get_sql_where('AND', 'i2.') . ') | |
| WHERE i1.' . $this->column_item_id . ' = ' . (int) $item_id . ' | |
| ' . $this->get_sql_where('AND', 'i1.') . ' | |
| ORDER BY i2.' . $this->column_left_id . ' ' . ($order_asc ? 'ASC' : 'DESC'); | |
| $result = $this->db->sql_query($sql); | |
| while ($row = $this->db->sql_fetchrow($result)) | |
| { | |
| if (!$include_item && $item_id == $row[$this->column_item_id]) | |
| { | |
| continue; | |
| } | |
| $rows[(int) $row[$this->column_item_id]] = $row; | |
| } | |
| $this->db->sql_freeresult($result); | |
| return $rows; | |
| } | |
| /** | |
| * Get basic data of all parent items | |
| * | |
| * Basic data is defined in the $item_basic_data property. | |
| * Data is cached in the item_parents column in the item table | |
| * | |
| * @param array $item The item to get the path from | |
| * @return array Array of items (containing basic columns from the item table) | |
| * ID => Item data | |
| */ | |
| public function get_path_basic_data(array $item) | |
| { | |
| $parents = array(); | |
| if ($item[$this->column_parent_id]) | |
| { | |
| if (!$item[$this->column_item_parents]) | |
| { | |
| $sql = 'SELECT ' . implode(', ', $this->item_basic_data) . ' | |
| FROM ' . $this->table_name . ' | |
| WHERE ' . $this->column_left_id . ' < ' . (int) $item[$this->column_left_id] . ' | |
| AND ' . $this->column_right_id . ' > ' . (int) $item[$this->column_right_id] . ' | |
| ' . $this->get_sql_where('AND') . ' | |
| ORDER BY ' . $this->column_left_id . ' ASC'; | |
| $result = $this->db->sql_query($sql); | |
| while ($row = $this->db->sql_fetchrow($result)) | |
| { | |
| $parents[$row[$this->column_item_id]] = $row; | |
| } | |
| $this->db->sql_freeresult($result); | |
| $item_parents = serialize($parents); | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->column_item_parents . " = '" . $this->db->sql_escape($item_parents) . "' | |
| WHERE " . $this->column_parent_id . ' = ' . (int) $item[$this->column_parent_id]; | |
| $this->db->sql_query($sql); | |
| } | |
| else | |
| { | |
| $parents = unserialize($item[$this->column_item_parents]); | |
| } | |
| } | |
| return $parents; | |
| } | |
| /** | |
| * Get all items from the tree | |
| * | |
| * @param bool $order_asc Order the items ascending by their left_id | |
| * @return array Array of items (containing all columns from the item table) | |
| * ID => Item data | |
| */ | |
| public function get_all_tree_data($order_asc = true) | |
| { | |
| $rows = array(); | |
| $sql = 'SELECT * | |
| FROM ' . $this->table_name . ' ' . | |
| $this->get_sql_where('WHERE') . ' | |
| ORDER BY ' . $this->column_left_id . ' ' . ($order_asc ? 'ASC' : 'DESC'); | |
| $result = $this->db->sql_query($sql); | |
| while ($row = $this->db->sql_fetchrow($result)) | |
| { | |
| $rows[(int) $row[$this->column_item_id]] = $row; | |
| } | |
| $this->db->sql_freeresult($result); | |
| return $rows; | |
| } | |
| /** | |
| * Remove a subset from the nested set | |
| * | |
| * @param array $subset_items Subset of items to remove | |
| * @param array $bounding_item Item containing the right bound of the subset | |
| * @param bool $set_subset_zero Should the parent, left and right id of the items be set to 0, or kept unchanged? | |
| * In case of removing an item from the tree, we should the values to 0 | |
| * In case of moving an item, we shouldkeep the original values, in order to allow "+ diff" later | |
| * @return null | |
| */ | |
| protected function remove_subset(array $subset_items, array $bounding_item, $set_subset_zero = true) | |
| { | |
| $acquired_new_lock = $this->acquire_lock(); | |
| $diff = count($subset_items) * 2; | |
| $sql_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items); | |
| $sql_not_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items, true); | |
| $sql_is_parent = $this->column_left_id . ' <= ' . (int) $bounding_item[$this->column_right_id] . ' | |
| AND ' . $this->column_right_id . ' >= ' . (int) $bounding_item[$this->column_right_id]; | |
| $sql_is_right = $this->column_left_id . ' > ' . (int) $bounding_item[$this->column_right_id]; | |
| $set_left_id = $this->db->sql_case($sql_is_right, $this->column_left_id . ' - ' . $diff, $this->column_left_id); | |
| $set_right_id = $this->db->sql_case($sql_is_parent . ' OR ' . $sql_is_right, $this->column_right_id . ' - ' . $diff, $this->column_right_id); | |
| if ($set_subset_zero) | |
| { | |
| $set_left_id = $this->db->sql_case($sql_subset_items, 0, $set_left_id); | |
| $set_right_id = $this->db->sql_case($sql_subset_items, 0, $set_right_id); | |
| } | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . (($set_subset_zero) ? $this->column_parent_id . ' = ' . $this->db->sql_case($sql_subset_items, 0, $this->column_parent_id) . ',' : '') . ' | |
| ' . $this->column_left_id . ' = ' . $set_left_id . ', | |
| ' . $this->column_right_id . ' = ' . $set_right_id . ' | |
| ' . ((!$set_subset_zero) ? ' WHERE ' . $sql_not_subset_items . ' ' . $this->get_sql_where('AND') : $this->get_sql_where('WHERE')); | |
| $this->db->sql_query($sql); | |
| if ($acquired_new_lock) | |
| { | |
| $this->lock->release(); | |
| } | |
| } | |
| /** | |
| * Prepare adding a subset to the nested set | |
| * | |
| * @param array $subset_items Subset of items to add | |
| * @param array $new_parent Item containing the right bound of the new parent | |
| * @return int New right id of the parent item | |
| */ | |
| protected function prepare_adding_subset(array $subset_items, array $new_parent) | |
| { | |
| $diff = count($subset_items) * 2; | |
| $sql_not_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items, true); | |
| $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); | |
| $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); | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->column_left_id . ' = ' . $set_left_id . ', | |
| ' . $this->column_right_id . ' = ' . $set_right_id . ' | |
| WHERE ' . $sql_not_subset_items . ' | |
| ' . $this->get_sql_where('AND'); | |
| $this->db->sql_query($sql); | |
| return $new_parent[$this->column_right_id] + $diff; | |
| } | |
| /** | |
| * Resets values required for the nested set system | |
| * | |
| * @param array $item Original item data | |
| * @return array Original item data + nested set defaults | |
| */ | |
| protected function reset_nestedset_values(array $item) | |
| { | |
| $item_data = array_merge($item, array( | |
| $this->column_parent_id => 0, | |
| $this->column_left_id => 0, | |
| $this->column_right_id => 0, | |
| $this->column_item_parents => '', | |
| )); | |
| unset($item_data[$this->column_item_id]); | |
| return $item_data; | |
| } | |
| /** | |
| * Regenerate left/right ids from parent/child relationship | |
| * | |
| * This method regenerates the left/right ids for the tree based on | |
| * the parent/child relations. This function executes three queries per | |
| * item, so it should only be called, when the set has one of the following | |
| * problems: | |
| * - The set has a duplicated value inside the left/right id chain | |
| * - The set has a missing value inside the left/right id chain | |
| * - The set has items that do not have a left/right id set | |
| * | |
| * When regenerating the items, the items are sorted by parent id and their | |
| * current left id, so the current child/parent relationships are kept | |
| * and running the function on a working set will not change the order. | |
| * | |
| * @param int $new_id First left_id to be used (should start with 1) | |
| * @param int $parent_id parent_id of the current set (default = 0) | |
| * @param bool $reset_ids Should we reset all left_id/right_id on the first call? | |
| * @return int $new_id The next left_id/right_id that should be used | |
| */ | |
| public function regenerate_left_right_ids($new_id, $parent_id = 0, $reset_ids = false) | |
| { | |
| if ($acquired_new_lock = $this->acquire_lock()) | |
| { | |
| $this->db->sql_transaction('begin'); | |
| if (!$reset_ids) | |
| { | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->column_item_parents . " = '' | |
| " . $this->get_sql_where('WHERE'); | |
| $this->db->sql_query($sql); | |
| } | |
| } | |
| if ($reset_ids) | |
| { | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->db->sql_build_array('UPDATE', array( | |
| $this->column_left_id => 0, | |
| $this->column_right_id => 0, | |
| $this->column_item_parents => '', | |
| )) . ' | |
| ' . $this->get_sql_where('WHERE'); | |
| $this->db->sql_query($sql); | |
| } | |
| $sql = 'SELECT * | |
| FROM ' . $this->table_name . ' | |
| WHERE ' . $this->column_parent_id . ' = ' . (int) $parent_id . ' | |
| ' . $this->get_sql_where('AND') . ' | |
| ORDER BY ' . $this->column_left_id . ', ' . $this->column_item_id . ' ASC'; | |
| $result = $this->db->sql_query($sql); | |
| $rows = $this->db->sql_fetchrowset($result); | |
| $this->db->sql_freeresult($result); | |
| foreach ($rows as $row) | |
| { | |
| // First we update the left_id for this module | |
| if ($row[$this->column_left_id] != $new_id) | |
| { | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->db->sql_build_array('UPDATE', array($this->column_left_id => $new_id)) . ' | |
| WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id]; | |
| $this->db->sql_query($sql); | |
| } | |
| $new_id++; | |
| // Then we go through any children and update their left/right id's | |
| $new_id = $this->regenerate_left_right_ids($new_id, $row[$this->column_item_id]); | |
| // Then we come back and update the right_id for this module | |
| if ($row[$this->column_right_id] != $new_id) | |
| { | |
| $sql = 'UPDATE ' . $this->table_name . ' | |
| SET ' . $this->db->sql_build_array('UPDATE', array($this->column_right_id => $new_id)) . ' | |
| WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id]; | |
| $this->db->sql_query($sql); | |
| } | |
| $new_id++; | |
| } | |
| if ($acquired_new_lock) | |
| { | |
| $this->db->sql_transaction('commit'); | |
| $this->lock->release(); | |
| } | |
| return $new_id; | |
| } | |
| } |