Verzeichnisstruktur phpBB-3.1.0
- Veröffentlicht
- 27.10.2014
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 $diff = sizeof($move_items) * 2;
395 $sql_exclude_moved_items = $this->db->sql_in_set($this->column_item_id, $move_items, true);
396
397 $this->db->sql_transaction('begin');
398
399 $this->remove_subset($move_items, $current_parent, false, true);
400
401 if ($new_parent_id)
402 {
403 // Retrieve new-parent again, it may have been changed...
404 $sql = 'SELECT *
405 FROM ' . $this->table_name . '
406 WHERE ' . $this->column_item_id . ' = ' . $new_parent_id;
407 $result = $this->db->sql_query($sql);
408 $new_parent = $this->db->sql_fetchrow($result);
409 $this->db->sql_freeresult($result);
410
411 if (!$new_parent)
412 {
413 $this->db->sql_transaction('rollback');
414 $this->lock->release();
415 throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT');
416 }
417
418 $new_right_id = $this->prepare_adding_subset($move_items, $new_parent, true);
419
420 if ($new_right_id > $current_parent[$this->column_right_id])
421 {
422 $diff = ' + ' . ($new_right_id - $current_parent[$this->column_right_id]);
423 }
424 else
425 {
426 $diff = ' - ' . abs($new_right_id - $current_parent[$this->column_right_id]);
427 }
428 }
429 else
430 {
431 $sql = 'SELECT MAX(' . $this->column_right_id . ') AS ' . $this->column_right_id . '
432 FROM ' . $this->table_name . '
433 WHERE ' . $sql_exclude_moved_items . '
434 ' . $this->get_sql_where('AND');
435 $result = $this->db->sql_query($sql);
436 $row = $this->db->sql_fetchrow($result);
437 $this->db->sql_freeresult($result);
438
439 $diff = ' + ' . ($row[$this->column_right_id] - $current_parent[$this->column_left_id]);
440 }
441
442 $sql = 'UPDATE ' . $this->table_name . '
443 SET ' . $this->column_left_id . ' = ' . $this->column_left_id . $diff . ',
444 ' . $this->column_right_id . ' = ' . $this->column_right_id . $diff . ',
445 ' . $this->column_parent_id . ' = ' . $this->db->sql_case($this->column_parent_id . ' = ' . $current_parent_id, $new_parent_id, $this->column_parent_id) . ',
446 ' . $this->column_item_parents . " = ''
447 WHERE " . $this->db->sql_in_set($this->column_item_id, $move_items) . '
448 ' . $this->get_sql_where('AND');
449 $this->db->sql_query($sql);
450
451 $this->db->sql_transaction('commit');
452 $this->lock->release();
453
454 return true;
455 }
456
457 /**
458 * {@inheritdoc}
459 */
460 public function change_parent($item_id, $new_parent_id)
461 {
462 $item_id = (int) $item_id;
463 $new_parent_id = (int) $new_parent_id;
464
465 if ($item_id == $new_parent_id)
466 {
467 return false;
468 }
469
470 if (!$item_id)
471 {
472 throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
473 }
474
475 $this->acquire_lock();
476
477 $item_data = $this->get_subtree_data($item_id);
478 if (!isset($item_data[$item_id]))
479 {
480 $this->lock->release();
481 throw new \OutOfBoundsException($this->message_prefix . 'INVALID_ITEM');
482 }
483
484 $item = $item_data[$item_id];
485 $move_items = array_keys($item_data);
486
487 if (in_array($new_parent_id, $move_items))
488 {
489 $this->lock->release();
490 throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT');
491 }
492
493 $diff = sizeof($move_items) * 2;
494 $sql_exclude_moved_items = $this->db->sql_in_set($this->column_item_id, $move_items, true);
495
496 $this->db->sql_transaction('begin');
497
498 $this->remove_subset($move_items, $item, false, true);
499
500 if ($new_parent_id)
501 {
502 // Retrieve new-parent again, it may have been changed...
503 $sql = 'SELECT *
504 FROM ' . $this->table_name . '
505 WHERE ' . $this->column_item_id . ' = ' . $new_parent_id;
506 $result = $this->db->sql_query($sql);
507 $new_parent = $this->db->sql_fetchrow($result);
508 $this->db->sql_freeresult($result);
509
510 if (!$new_parent)
511 {
512 $this->db->sql_transaction('rollback');
513 $this->lock->release();
514 throw new \OutOfBoundsException($this->message_prefix . 'INVALID_PARENT');
515 }
516
517 $new_right_id = $this->prepare_adding_subset($move_items, $new_parent, true);
518
519 if ($new_right_id > (int) $item[$this->column_right_id])
520 {
521 $diff = ' + ' . ($new_right_id - (int) $item[$this->column_right_id] - 1);
522 }
523 else
524 {
525 $diff = ' - ' . abs($new_right_id - (int) $item[$this->column_right_id] - 1);
526 }
527 }
528 else
529 {
530 $sql = 'SELECT MAX(' . $this->column_right_id . ') AS ' . $this->column_right_id . '
531 FROM ' . $this->table_name . '
532 WHERE ' . $sql_exclude_moved_items . '
533 ' . $this->get_sql_where('AND');
534 $result = $this->db->sql_query($sql);
535 $row = $this->db->sql_fetchrow($result);
536 $this->db->sql_freeresult($result);
537
538 $diff = ' + ' . ($row[$this->column_right_id] - (int) $item[$this->column_left_id] + 1);
539 }
540
541 $sql = 'UPDATE ' . $this->table_name . '
542 SET ' . $this->column_left_id . ' = ' . $this->column_left_id . $diff . ',
543 ' . $this->column_right_id . ' = ' . $this->column_right_id . $diff . ',
544 ' . $this->column_parent_id . ' = ' . $this->db->sql_case($this->column_item_id . ' = ' . $item_id, $new_parent_id, $this->column_parent_id) . ',
545 ' . $this->column_item_parents . " = ''
546 WHERE " . $this->db->sql_in_set($this->column_item_id, $move_items) . '
547 ' . $this->get_sql_where('AND');
548 $this->db->sql_query($sql);
549
550 $this->db->sql_transaction('commit');
551 $this->lock->release();
552
553 return true;
554 }
555
556 /**
557 * {@inheritdoc}
558 */
559 public function get_path_and_subtree_data($item_id, $order_asc = true, $include_item = true)
560 {
561 $condition = 'i2.' . $this->column_left_id . ' BETWEEN i1.' . $this->column_left_id . ' AND i1.' . $this->column_right_id . '
562 OR i1.' . $this->column_left_id . ' BETWEEN i2.' . $this->column_left_id . ' AND i2.' . $this->column_right_id;
563
564 return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item);
565 }
566
567 /**
568 * {@inheritdoc}
569 */
570 public function get_path_data($item_id, $order_asc = true, $include_item = true)
571 {
572 $condition = 'i1.' . $this->column_left_id . ' BETWEEN i2.' . $this->column_left_id . ' AND i2.' . $this->column_right_id . '';
573
574 return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item);
575 }
576
577 /**
578 * {@inheritdoc}
579 */
580 public function get_subtree_data($item_id, $order_asc = true, $include_item = true)
581 {
582 $condition = 'i2.' . $this->column_left_id . ' BETWEEN i1.' . $this->column_left_id . ' AND i1.' . $this->column_right_id . '';
583
584 return $this->get_set_of_nodes_data($item_id, $condition, $order_asc, $include_item);
585 }
586
587 /**
588 * Get items that are related to the given item by the condition
589 *
590 * @param int $item_id Id of the item to retrieve the node set from
591 * @param string $condition Query string restricting the item list
592 * @param bool $order_asc Order the items ascending by their left_id
593 * @param bool $include_item Should the item matching the given item id be included in the list as well
594 * @return array Array of items (containing all columns from the item table)
595 * ID => Item data
596 */
597 protected function get_set_of_nodes_data($item_id, $condition, $order_asc = true, $include_item = true)
598 {
599 $rows = array();
600
601 $sql = 'SELECT i2.*
602 FROM ' . $this->table_name . ' i1
603 LEFT JOIN ' . $this->table_name . " i2
604 ON (($condition) " . $this->get_sql_where('AND', 'i2.') . ')
605 WHERE i1.' . $this->column_item_id . ' = ' . (int) $item_id . '
606 ' . $this->get_sql_where('AND', 'i1.') . '
607 ORDER BY i2.' . $this->column_left_id . ' ' . ($order_asc ? 'ASC' : 'DESC');
608 $result = $this->db->sql_query($sql);
609
610 while ($row = $this->db->sql_fetchrow($result))
611 {
612 if (!$include_item && $item_id == $row[$this->column_item_id])
613 {
614 continue;
615 }
616
617 $rows[(int) $row[$this->column_item_id]] = $row;
618 }
619 $this->db->sql_freeresult($result);
620
621 return $rows;
622 }
623
624 /**
625 * Get basic data of all parent items
626 *
627 * Basic data is defined in the $item_basic_data property.
628 * Data is cached in the item_parents column in the item table
629 *
630 * @param array $item The item to get the path from
631 * @return array Array of items (containing basic columns from the item table)
632 * ID => Item data
633 */
634 public function get_path_basic_data(array $item)
635 {
636 $parents = array();
637 if ($item[$this->column_parent_id])
638 {
639 if (!$item[$this->column_item_parents])
640 {
641 $sql = 'SELECT ' . implode(', ', $this->item_basic_data) . '
642 FROM ' . $this->table_name . '
643 WHERE ' . $this->column_left_id . ' < ' . (int) $item[$this->column_left_id] . '
644 AND ' . $this->column_right_id . ' > ' . (int) $item[$this->column_right_id] . '
645 ' . $this->get_sql_where('AND') . '
646 ORDER BY ' . $this->column_left_id . ' ASC';
647 $result = $this->db->sql_query($sql);
648
649 while ($row = $this->db->sql_fetchrow($result))
650 {
651 $parents[$row[$this->column_item_id]] = $row;
652 }
653 $this->db->sql_freeresult($result);
654
655 $item_parents = serialize($parents);
656
657 $sql = 'UPDATE ' . $this->table_name . '
658 SET ' . $this->column_item_parents . " = '" . $this->db->sql_escape($item_parents) . "'
659 WHERE " . $this->column_parent_id . ' = ' . (int) $item[$this->column_parent_id];
660 $this->db->sql_query($sql);
661 }
662 else
663 {
664 $parents = unserialize($item[$this->column_item_parents]);
665 }
666 }
667
668 return $parents;
669 }
670
671 /**
672 * Get all items from the tree
673 *
674 * @param bool $order_asc Order the items ascending by their left_id
675 * @return array Array of items (containing all columns from the item table)
676 * ID => Item data
677 */
678 public function get_all_tree_data($order_asc = true)
679 {
680 $rows = array();
681
682 $sql = 'SELECT *
683 FROM ' . $this->table_name . ' ' .
684 $this->get_sql_where('WHERE') . '
685 ORDER BY ' . $this->column_left_id . ' ' . ($order_asc ? 'ASC' : 'DESC');
686 $result = $this->db->sql_query($sql);
687
688 while ($row = $this->db->sql_fetchrow($result))
689 {
690 $rows[(int) $row[$this->column_item_id]] = $row;
691 }
692 $this->db->sql_freeresult($result);
693
694 return $rows;
695 }
696
697 /**
698 * Remove a subset from the nested set
699 *
700 * @param array $subset_items Subset of items to remove
701 * @param array $bounding_item Item containing the right bound of the subset
702 * @param bool $set_subset_zero Should the parent, left and right id of the items be set to 0, or kept unchanged?
703 * In case of removing an item from the tree, we should the values to 0
704 * In case of moving an item, we shouldkeep the original values, in order to allow "+ diff" later
705 * @return null
706 */
707 protected function remove_subset(array $subset_items, array $bounding_item, $set_subset_zero = true)
708 {
709 $acquired_new_lock = $this->acquire_lock();
710
711 $diff = sizeof($subset_items) * 2;
712 $sql_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items);
713 $sql_not_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items, true);
714
715 $sql_is_parent = $this->column_left_id . ' <= ' . (int) $bounding_item[$this->column_right_id] . '
716 AND ' . $this->column_right_id . ' >= ' . (int) $bounding_item[$this->column_right_id];
717
718 $sql_is_right = $this->column_left_id . ' > ' . (int) $bounding_item[$this->column_right_id];
719
720 $set_left_id = $this->db->sql_case($sql_is_right, $this->column_left_id . ' - ' . $diff, $this->column_left_id);
721 $set_right_id = $this->db->sql_case($sql_is_parent . ' OR ' . $sql_is_right, $this->column_right_id . ' - ' . $diff, $this->column_right_id);
722
723 if ($set_subset_zero)
724 {
725 $set_left_id = $this->db->sql_case($sql_subset_items, 0, $set_left_id);
726 $set_right_id = $this->db->sql_case($sql_subset_items, 0, $set_right_id);
727 }
728
729 $sql = 'UPDATE ' . $this->table_name . '
730 SET ' . (($set_subset_zero) ? $this->column_parent_id . ' = ' . $this->db->sql_case($sql_subset_items, 0, $this->column_parent_id) . ',' : '') . '
731 ' . $this->column_left_id . ' = ' . $set_left_id . ',
732 ' . $this->column_right_id . ' = ' . $set_right_id . '
733 ' . ((!$set_subset_zero) ? ' WHERE ' . $sql_not_subset_items . ' ' . $this->get_sql_where('AND') : $this->get_sql_where('WHERE'));
734 $this->db->sql_query($sql);
735
736 if ($acquired_new_lock)
737 {
738 $this->lock->release();
739 }
740 }
741
742 /**
743 * Prepare adding a subset to the nested set
744 *
745 * @param array $subset_items Subset of items to add
746 * @param array $new_parent Item containing the right bound of the new parent
747 * @return int New right id of the parent item
748 */
749 protected function prepare_adding_subset(array $subset_items, array $new_parent)
750 {
751 $diff = sizeof($subset_items) * 2;
752 $sql_not_subset_items = $this->db->sql_in_set($this->column_item_id, $subset_items, true);
753
754 $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);
755 $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);
756
757 $sql = 'UPDATE ' . $this->table_name . '
758 SET ' . $this->column_left_id . ' = ' . $set_left_id . ',
759 ' . $this->column_right_id . ' = ' . $set_right_id . '
760 WHERE ' . $sql_not_subset_items . '
761 ' . $this->get_sql_where('AND');
762 $this->db->sql_query($sql);
763
764 return $new_parent[$this->column_right_id] + $diff;
765 }
766
767 /**
768 * Resets values required for the nested set system
769 *
770 * @param array $item Original item data
771 * @return array Original item data + nested set defaults
772 */
773 protected function reset_nestedset_values(array $item)
774 {
775 $item_data = array_merge($item, array(
776 $this->column_parent_id => 0,
777 $this->column_left_id => 0,
778 $this->column_right_id => 0,
779 $this->column_item_parents => '',
780 ));
781
782 unset($item_data[$this->column_item_id]);
783
784 return $item_data;
785 }
786
787 /**
788 * Regenerate left/right ids from parent/child relationship
789 *
790 * This method regenerates the left/right ids for the tree based on
791 * the parent/child relations. This function executes three queries per
792 * item, so it should only be called, when the set has one of the following
793 * problems:
794 * - The set has a duplicated value inside the left/right id chain
795 * - The set has a missing value inside the left/right id chain
796 * - The set has items that do not have a left/right id set
797 *
798 * When regenerating the items, the items are sorted by parent id and their
799 * current left id, so the current child/parent relationships are kept
800 * and running the function on a working set will not change the order.
801 *
802 * @param int $new_id First left_id to be used (should start with 1)
803 * @param int $parent_id parent_id of the current set (default = 0)
804 * @param bool $reset_ids Should we reset all left_id/right_id on the first call?
805 * @return int $new_id The next left_id/right_id that should be used
806 */
807 public function regenerate_left_right_ids($new_id, $parent_id = 0, $reset_ids = false)
808 {
809 if ($acquired_new_lock = $this->acquire_lock())
810 {
811 $this->db->sql_transaction('begin');
812
813 if (!$reset_ids)
814 {
815 $sql = 'UPDATE ' . $this->table_name . '
816 SET ' . $this->column_item_parents . " = ''
817 " . $this->get_sql_where('WHERE');
818 $this->db->sql_query($sql);
819 }
820 }
821
822 if ($reset_ids)
823 {
824 $sql = 'UPDATE ' . $this->table_name . '
825 SET ' . $this->db->sql_build_array('UPDATE', array(
826 $this->column_left_id => 0,
827 $this->column_right_id => 0,
828 $this->column_item_parents => '',
829 )) . '
830 ' . $this->get_sql_where('WHERE');
831 $this->db->sql_query($sql);
832 }
833
834 $sql = 'SELECT *
835 FROM ' . $this->table_name . '
836 WHERE ' . $this->column_parent_id . ' = ' . (int) $parent_id . '
837 ' . $this->get_sql_where('AND') . '
838 ORDER BY ' . $this->column_left_id . ', ' . $this->column_item_id . ' ASC';
839 $result = $this->db->sql_query($sql);
840 while ($row = $this->db->sql_fetchrow($result))
841 {
842 // First we update the left_id for this module
843 if ($row[$this->column_left_id] != $new_id)
844 {
845 $sql = 'UPDATE ' . $this->table_name . '
846 SET ' . $this->db->sql_build_array('UPDATE', array($this->column_left_id => $new_id)) . '
847 WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id];
848 $this->db->sql_query($sql);
849 }
850 $new_id++;
851
852 // Then we go through any children and update their left/right id's
853 $new_id = $this->regenerate_left_right_ids($new_id, $row[$this->column_item_id]);
854
855 // Then we come back and update the right_id for this module
856 if ($row[$this->column_right_id] != $new_id)
857 {
858 $sql = 'UPDATE ' . $this->table_name . '
859 SET ' . $this->db->sql_build_array('UPDATE', array($this->column_right_id => $new_id)) . '
860 WHERE ' . $this->column_item_id . ' = ' . (int) $row[$this->column_item_id];
861 $this->db->sql_query($sql);
862 }
863 $new_id++;
864 }
865 $this->db->sql_freeresult($result);
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