Verzeichnisstruktur phpBB-3.2.0
- Veröffentlicht
- 06.01.2017
So funktioniert es
|
|
Auf das letzte Element klicken. Dies geht jeweils ein Schritt zurück |
Auf das Icon klicken, dies öffnet das Verzeichnis. Nochmal klicken schließt das Verzeichnis. |
|
|
(Beispiel Datei-Icons)
|
Auf das Icon klicken um den Quellcode anzuzeigen |
nestedset.php
001 <?php
002 /**
003 *
004 * This file is part of the phpBB Forum Software package.
005 *
006 * @copyright (c) phpBB Limited <https://www.phpbb.com>
007 * @license GNU General Public License, version 2 (GPL-2.0)
008 *
009 * For full copyright and license information, please see
010 * the docs/CREDITS.txt file.
011 *
012 */
013
014 namespace phpbb\tree;
015
016 abstract class nestedset implements \phpbb\tree\tree_interface
017 {
018 /** @var \phpbb\db\driver\driver_interface */
019 protected $db;
020
021 /** @var \phpbb\lock\db */
022 protected $lock;
023
024 /** @var string */
025 protected $table_name;
026
027 /**
028 * Prefix for the language keys returned by exceptions
029 * @var string
030 */
031 protected $message_prefix = '';
032
033 /**
034 * Column names in the table
035 * @var string
036 */
037 protected $column_item_id = 'item_id';
038 protected $column_left_id = 'left_id';
039 protected $column_right_id = 'right_id';
040 protected $column_parent_id = 'parent_id';
041 protected $column_item_parents = 'item_parents';
042
043 /**
044 * Additional SQL restrictions
045 * Allows to have multiple nested sets in one table
046 * @var string
047 */
048 protected $sql_where = '';
049
050 /**
051 * List of item properties to be cached in the item_parents column
052 * @var array
053 */
054 protected $item_basic_data = array('*');
055
056 /**
057 * Construct
058 *
059 * @param \phpbb\db\driver\driver_interface $db Database connection
060 * @param \phpbb\lock\db $lock Lock class used to lock the table when moving forums around
061 * @param string $table_name Table name
062 * @param string $message_prefix Prefix for the messages thrown by exceptions
063 * @param string $sql_where Additional SQL restrictions for the queries
064 * @param array $item_basic_data Array with basic item data that is stored in item_parents
065 * @param array $columns Array with column names to overwrite
066 */
067 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())
068 {
069 $this->db = $db;
070 $this->lock = $lock;
071
072 $this->table_name = $table_name;
073 $this->message_prefix = $message_prefix;
074 $this->sql_where = $sql_where;
075 $this->item_basic_data = (!empty($item_basic_data)) ? $item_basic_data : array('*');
076
077 if (!empty($columns))
078 {
079 foreach ($columns as $column => $name)
080 {
081 $column_name = 'column_' . $column;
082 $this->$column_name = $name;
083 }
084 }
085 }
086
087 /**
088 * Returns additional sql where restrictions
089 *
090 * @param string $operator SQL operator that needs to be prepended to sql_where,
091 * if it is not empty.
092 * @param string $column_prefix Prefix that needs to be prepended to column names
093 * @return string Returns additional where statements to narrow down the tree,
094 * prefixed with operator and prepended column_prefix to column names
095 */
096 public function get_sql_where($operator = 'AND', $column_prefix = '')
097 {
098 return (!$this->sql_where) ? '' : $operator . ' ' . sprintf($this->sql_where, $column_prefix);
099 }
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 = ' + ' . ($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 = sizeof($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 = sizeof($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 }
876