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 |
functions_admin.php
0001 <?php
0002 /**
0003 *
0004 * This file is part of the phpBB Forum Software package.
0005 *
0006 * @copyright (c) phpBB Limited <https://www.phpbb.com>
0007 * @license GNU General Public License, version 2 (GPL-2.0)
0008 *
0009 * For full copyright and license information, please see
0010 * the docs/CREDITS.txt file.
0011 *
0012 */
0013
0014 /**
0015 * @ignore
0016 */
0017 if (!defined('IN_PHPBB'))
0018 {
0019 exit;
0020 }
0021
0022 /**
0023 * Recalculate Nested Sets
0024 *
0025 * @param int $new_id first left_id (should start with 1)
0026 * @param string $pkey primary key-column (containing the id for the parent_id of the children)
0027 * @param string $table constant or fullname of the table
0028 * @param int $parent_id parent_id of the current set (default = 0)
0029 * @param array $where contains strings to compare closer on the where statement (additional)
0030 */
0031 function recalc_nested_sets(&$new_id, $pkey, $table, $parent_id = 0, $where = array())
0032 {
0033 global $db;
0034
0035 $sql = 'SELECT *
0036 FROM ' . $table . '
0037 WHERE parent_id = ' . (int) $parent_id .
0038 ((!empty($where)) ? ' AND ' . implode(' AND ', $where) : '') . '
0039 ORDER BY left_id ASC';
0040 $result = $db->sql_query($sql);
0041 while ($row = $db->sql_fetchrow($result))
0042 {
0043 // First we update the left_id for this module
0044 if ($row['left_id'] != $new_id)
0045 {
0046 $db->sql_query('UPDATE ' . $table . ' SET ' . $db->sql_build_array('UPDATE', array('left_id' => $new_id)) . " WHERE $pkey = {$row[$pkey]}");
0047 }
0048 $new_id++;
0049
0050 // Then we go through any children and update their left/right id's
0051 recalc_nested_sets($new_id, $pkey, $table, $row[$pkey], $where);
0052
0053 // Then we come back and update the right_id for this module
0054 if ($row['right_id'] != $new_id)
0055 {
0056 $db->sql_query('UPDATE ' . $table . ' SET ' . $db->sql_build_array('UPDATE', array('right_id' => $new_id)) . " WHERE $pkey = {$row[$pkey]}");
0057 }
0058 $new_id++;
0059 }
0060 $db->sql_freeresult($result);
0061 }
0062
0063 /**
0064 * Simple version of jumpbox, just lists authed forums
0065 */
0066 function make_forum_select($select_id = false, $ignore_id = false, $ignore_acl = false, $ignore_nonpost = false, $ignore_emptycat = true, $only_acl_post = false, $return_array = false)
0067 {
0068 global $db, $user, $auth;
0069
0070 // This query is identical to the jumpbox one
0071 $sql = 'SELECT forum_id, forum_name, parent_id, forum_type, forum_flags, forum_options, left_id, right_id
0072 FROM ' . FORUMS_TABLE . '
0073 ORDER BY left_id ASC';
0074 $result = $db->sql_query($sql, 600);
0075
0076 $right = 0;
0077 $padding_store = array('0' => '');
0078 $padding = '';
0079 $forum_list = ($return_array) ? array() : '';
0080
0081 // Sometimes it could happen that forums will be displayed here not be displayed within the index page
0082 // This is the result of forums not displayed at index, having list permissions and a parent of a forum with no permissions.
0083 // If this happens, the padding could be "broken"
0084
0085 while ($row = $db->sql_fetchrow($result))
0086 {
0087 if ($row['left_id'] < $right)
0088 {
0089 $padding .= ' ';
0090 $padding_store[$row['parent_id']] = $padding;
0091 }
0092 else if ($row['left_id'] > $right + 1)
0093 {
0094 $padding = (isset($padding_store[$row['parent_id']])) ? $padding_store[$row['parent_id']] : '';
0095 }
0096
0097 $right = $row['right_id'];
0098 $disabled = false;
0099
0100 if (!$ignore_acl && $auth->acl_gets(array('f_list', 'a_forum', 'a_forumadd', 'a_forumdel'), $row['forum_id']))
0101 {
0102 if ($only_acl_post && !$auth->acl_get('f_post', $row['forum_id']) || (!$auth->acl_get('m_approve', $row['forum_id']) && !$auth->acl_get('f_noapprove', $row['forum_id'])))
0103 {
0104 $disabled = true;
0105 }
0106 }
0107 else if (!$ignore_acl)
0108 {
0109 continue;
0110 }
0111
0112 if (
0113 ((is_array($ignore_id) && in_array($row['forum_id'], $ignore_id)) || $row['forum_id'] == $ignore_id)
0114 ||
0115 // Non-postable forum with no subforums, don't display
0116 ($row['forum_type'] == FORUM_CAT && ($row['left_id'] + 1 == $row['right_id']) && $ignore_emptycat)
0117 ||
0118 ($row['forum_type'] != FORUM_POST && $ignore_nonpost)
0119 )
0120 {
0121 $disabled = true;
0122 }
0123
0124 if ($return_array)
0125 {
0126 // Include some more information...
0127 $selected = (is_array($select_id)) ? ((in_array($row['forum_id'], $select_id)) ? true : false) : (($row['forum_id'] == $select_id) ? true : false);
0128 $forum_list[$row['forum_id']] = array_merge(array('padding' => $padding, 'selected' => ($selected && !$disabled), 'disabled' => $disabled), $row);
0129 }
0130 else
0131 {
0132 $selected = (is_array($select_id)) ? ((in_array($row['forum_id'], $select_id)) ? ' selected="selected"' : '') : (($row['forum_id'] == $select_id) ? ' selected="selected"' : '');
0133 $forum_list .= '<option value="' . $row['forum_id'] . '"' . (($disabled) ? ' disabled="disabled" class="disabled-option"' : $selected) . '>' . $padding . $row['forum_name'] . '</option>';
0134 }
0135 }
0136 $db->sql_freeresult($result);
0137 unset($padding_store);
0138
0139 return $forum_list;
0140 }
0141
0142 /**
0143 * Generate size select options
0144 */
0145 function size_select_options($size_compare)
0146 {
0147 global $user;
0148
0149 $size_types_text = array($user->lang['BYTES'], $user->lang['KIB'], $user->lang['MIB']);
0150 $size_types = array('b', 'kb', 'mb');
0151
0152 $s_size_options = '';
0153
0154 for ($i = 0, $size = sizeof($size_types_text); $i < $size; $i++)
0155 {
0156 $selected = ($size_compare == $size_types[$i]) ? ' selected="selected"' : '';
0157 $s_size_options .= '<option value="' . $size_types[$i] . '"' . $selected . '>' . $size_types_text[$i] . '</option>';
0158 }
0159
0160 return $s_size_options;
0161 }
0162
0163 /**
0164 * Generate list of groups (option fields without select)
0165 *
0166 * @param int $group_id The default group id to mark as selected
0167 * @param array $exclude_ids The group ids to exclude from the list, false (default) if you whish to exclude no id
0168 * @param int $manage_founder If set to false (default) all groups are returned, if 0 only those groups returned not being managed by founders only, if 1 only those groups returned managed by founders only.
0169 *
0170 * @return string The list of options.
0171 */
0172 function group_select_options($group_id, $exclude_ids = false, $manage_founder = false)
0173 {
0174 global $db, $user, $config;
0175
0176 $exclude_sql = ($exclude_ids !== false && sizeof($exclude_ids)) ? 'WHERE ' . $db->sql_in_set('group_id', array_map('intval', $exclude_ids), true) : '';
0177 $sql_and = (!$config['coppa_enable']) ? (($exclude_sql) ? ' AND ' : ' WHERE ') . "group_name <> 'REGISTERED_COPPA'" : '';
0178 $sql_founder = ($manage_founder !== false) ? (($exclude_sql || $sql_and) ? ' AND ' : ' WHERE ') . 'group_founder_manage = ' . (int) $manage_founder : '';
0179
0180 $sql = 'SELECT group_id, group_name, group_type
0181 FROM ' . GROUPS_TABLE . "
0182 $exclude_sql
0183 $sql_and
0184 $sql_founder
0185 ORDER BY group_type DESC, group_name ASC";
0186 $result = $db->sql_query($sql);
0187
0188 $s_group_options = '';
0189 while ($row = $db->sql_fetchrow($result))
0190 {
0191 $selected = ($row['group_id'] == $group_id) ? ' selected="selected"' : '';
0192 $s_group_options .= '<option' . (($row['group_type'] == GROUP_SPECIAL) ? ' class="sep"' : '') . ' value="' . $row['group_id'] . '"' . $selected . '>' . (($row['group_type'] == GROUP_SPECIAL) ? $user->lang['G_' . $row['group_name']] : $row['group_name']) . '</option>';
0193 }
0194 $db->sql_freeresult($result);
0195
0196 return $s_group_options;
0197 }
0198
0199 /**
0200 * Obtain authed forums list
0201 */
0202 function get_forum_list($acl_list = 'f_list', $id_only = true, $postable_only = false, $no_cache = false)
0203 {
0204 global $db, $auth;
0205 static $forum_rows;
0206
0207 if (!isset($forum_rows))
0208 {
0209 // This query is identical to the jumpbox one
0210 $expire_time = ($no_cache) ? 0 : 600;
0211
0212 $sql = 'SELECT forum_id, forum_name, parent_id, forum_type, left_id, right_id
0213 FROM ' . FORUMS_TABLE . '
0214 ORDER BY left_id ASC';
0215 $result = $db->sql_query($sql, $expire_time);
0216
0217 $forum_rows = array();
0218
0219 $right = $padding = 0;
0220 $padding_store = array('0' => 0);
0221
0222 while ($row = $db->sql_fetchrow($result))
0223 {
0224 if ($row['left_id'] < $right)
0225 {
0226 $padding++;
0227 $padding_store[$row['parent_id']] = $padding;
0228 }
0229 else if ($row['left_id'] > $right + 1)
0230 {
0231 // Ok, if the $padding_store for this parent is empty there is something wrong. For now we will skip over it.
0232 // @todo digging deep to find out "how" this can happen.
0233 $padding = (isset($padding_store[$row['parent_id']])) ? $padding_store[$row['parent_id']] : $padding;
0234 }
0235
0236 $right = $row['right_id'];
0237 $row['padding'] = $padding;
0238
0239 $forum_rows[] = $row;
0240 }
0241 $db->sql_freeresult($result);
0242 unset($padding_store);
0243 }
0244
0245 $rowset = array();
0246 foreach ($forum_rows as $row)
0247 {
0248 if ($postable_only && $row['forum_type'] != FORUM_POST)
0249 {
0250 continue;
0251 }
0252
0253 if ($acl_list == '' || ($acl_list != '' && $auth->acl_gets($acl_list, $row['forum_id'])))
0254 {
0255 $rowset[] = ($id_only) ? (int) $row['forum_id'] : $row;
0256 }
0257 }
0258
0259 return $rowset;
0260 }
0261
0262 /**
0263 * Get forum branch
0264 */
0265 function get_forum_branch($forum_id, $type = 'all', $order = 'descending', $include_forum = true)
0266 {
0267 global $db;
0268
0269 switch ($type)
0270 {
0271 case 'parents':
0272 $condition = 'f1.left_id BETWEEN f2.left_id AND f2.right_id';
0273 break;
0274
0275 case 'children':
0276 $condition = 'f2.left_id BETWEEN f1.left_id AND f1.right_id';
0277 break;
0278
0279 default:
0280 $condition = 'f2.left_id BETWEEN f1.left_id AND f1.right_id OR f1.left_id BETWEEN f2.left_id AND f2.right_id';
0281 break;
0282 }
0283
0284 $rows = array();
0285
0286 $sql = 'SELECT f2.*
0287 FROM ' . FORUMS_TABLE . ' f1
0288 LEFT JOIN ' . FORUMS_TABLE . " f2 ON ($condition)
0289 WHERE f1.forum_id = $forum_id
0290 ORDER BY f2.left_id " . (($order == 'descending') ? 'ASC' : 'DESC');
0291 $result = $db->sql_query($sql);
0292
0293 while ($row = $db->sql_fetchrow($result))
0294 {
0295 if (!$include_forum && $row['forum_id'] == $forum_id)
0296 {
0297 continue;
0298 }
0299
0300 $rows[] = $row;
0301 }
0302 $db->sql_freeresult($result);
0303
0304 return $rows;
0305 }
0306
0307 /**
0308 * Copies permissions from one forum to others
0309 *
0310 * @param int $src_forum_id The source forum we want to copy permissions from
0311 * @param array $dest_forum_ids The destination forum(s) we want to copy to
0312 * @param bool $clear_dest_perms True if destination permissions should be deleted
0313 * @param bool $add_log True if log entry should be added
0314 *
0315 * @return bool False on error
0316 */
0317 function copy_forum_permissions($src_forum_id, $dest_forum_ids, $clear_dest_perms = true, $add_log = true)
0318 {
0319 global $db;
0320
0321 // Only one forum id specified
0322 if (!is_array($dest_forum_ids))
0323 {
0324 $dest_forum_ids = array($dest_forum_ids);
0325 }
0326
0327 // Make sure forum ids are integers
0328 $src_forum_id = (int) $src_forum_id;
0329 $dest_forum_ids = array_map('intval', $dest_forum_ids);
0330
0331 // No source forum or no destination forums specified
0332 if (empty($src_forum_id) || empty($dest_forum_ids))
0333 {
0334 return false;
0335 }
0336
0337 // Check if source forum exists
0338 $sql = 'SELECT forum_name
0339 FROM ' . FORUMS_TABLE . '
0340 WHERE forum_id = ' . $src_forum_id;
0341 $result = $db->sql_query($sql);
0342 $src_forum_name = $db->sql_fetchfield('forum_name');
0343 $db->sql_freeresult($result);
0344
0345 // Source forum doesn't exist
0346 if (empty($src_forum_name))
0347 {
0348 return false;
0349 }
0350
0351 // Check if destination forums exists
0352 $sql = 'SELECT forum_id, forum_name
0353 FROM ' . FORUMS_TABLE . '
0354 WHERE ' . $db->sql_in_set('forum_id', $dest_forum_ids);
0355 $result = $db->sql_query($sql);
0356
0357 $dest_forum_ids = $dest_forum_names = array();
0358 while ($row = $db->sql_fetchrow($result))
0359 {
0360 $dest_forum_ids[] = (int) $row['forum_id'];
0361 $dest_forum_names[] = $row['forum_name'];
0362 }
0363 $db->sql_freeresult($result);
0364
0365 // No destination forum exists
0366 if (empty($dest_forum_ids))
0367 {
0368 return false;
0369 }
0370
0371 // From the mysql documentation:
0372 // Prior to MySQL 4.0.14, the target table of the INSERT statement cannot appear
0373 // in the FROM clause of the SELECT part of the query. This limitation is lifted in 4.0.14.
0374 // Due to this we stay on the safe side if we do the insertion "the manual way"
0375
0376 // Rowsets we're going to insert
0377 $users_sql_ary = $groups_sql_ary = array();
0378
0379 // Query acl users table for source forum data
0380 $sql = 'SELECT user_id, auth_option_id, auth_role_id, auth_setting
0381 FROM ' . ACL_USERS_TABLE . '
0382 WHERE forum_id = ' . $src_forum_id;
0383 $result = $db->sql_query($sql);
0384
0385 while ($row = $db->sql_fetchrow($result))
0386 {
0387 $row = array(
0388 'user_id' => (int) $row['user_id'],
0389 'auth_option_id' => (int) $row['auth_option_id'],
0390 'auth_role_id' => (int) $row['auth_role_id'],
0391 'auth_setting' => (int) $row['auth_setting'],
0392 );
0393
0394 foreach ($dest_forum_ids as $dest_forum_id)
0395 {
0396 $users_sql_ary[] = $row + array('forum_id' => $dest_forum_id);
0397 }
0398 }
0399 $db->sql_freeresult($result);
0400
0401 // Query acl groups table for source forum data
0402 $sql = 'SELECT group_id, auth_option_id, auth_role_id, auth_setting
0403 FROM ' . ACL_GROUPS_TABLE . '
0404 WHERE forum_id = ' . $src_forum_id;
0405 $result = $db->sql_query($sql);
0406
0407 while ($row = $db->sql_fetchrow($result))
0408 {
0409 $row = array(
0410 'group_id' => (int) $row['group_id'],
0411 'auth_option_id' => (int) $row['auth_option_id'],
0412 'auth_role_id' => (int) $row['auth_role_id'],
0413 'auth_setting' => (int) $row['auth_setting'],
0414 );
0415
0416 foreach ($dest_forum_ids as $dest_forum_id)
0417 {
0418 $groups_sql_ary[] = $row + array('forum_id' => $dest_forum_id);
0419 }
0420 }
0421 $db->sql_freeresult($result);
0422
0423 $db->sql_transaction('begin');
0424
0425 // Clear current permissions of destination forums
0426 if ($clear_dest_perms)
0427 {
0428 $sql = 'DELETE FROM ' . ACL_USERS_TABLE . '
0429 WHERE ' . $db->sql_in_set('forum_id', $dest_forum_ids);
0430 $db->sql_query($sql);
0431
0432 $sql = 'DELETE FROM ' . ACL_GROUPS_TABLE . '
0433 WHERE ' . $db->sql_in_set('forum_id', $dest_forum_ids);
0434 $db->sql_query($sql);
0435 }
0436
0437 $db->sql_multi_insert(ACL_USERS_TABLE, $users_sql_ary);
0438 $db->sql_multi_insert(ACL_GROUPS_TABLE, $groups_sql_ary);
0439
0440 if ($add_log)
0441 {
0442 add_log('admin', 'LOG_FORUM_COPIED_PERMISSIONS', $src_forum_name, implode(', ', $dest_forum_names));
0443 }
0444
0445 $db->sql_transaction('commit');
0446
0447 return true;
0448 }
0449
0450 /**
0451 * Get physical file listing
0452 */
0453 function filelist($rootdir, $dir = '', $type = 'gif|jpg|jpeg|png')
0454 {
0455 $matches = array($dir => array());
0456
0457 // Remove initial / if present
0458 $rootdir = (substr($rootdir, 0, 1) == '/') ? substr($rootdir, 1) : $rootdir;
0459 // Add closing / if not present
0460 $rootdir = ($rootdir && substr($rootdir, -1) != '/') ? $rootdir . '/' : $rootdir;
0461
0462 // Remove initial / if present
0463 $dir = (substr($dir, 0, 1) == '/') ? substr($dir, 1) : $dir;
0464 // Add closing / if not present
0465 $dir = ($dir && substr($dir, -1) != '/') ? $dir . '/' : $dir;
0466
0467 if (!is_dir($rootdir . $dir))
0468 {
0469 return $matches;
0470 }
0471
0472 $dh = @opendir($rootdir . $dir);
0473
0474 if (!$dh)
0475 {
0476 return $matches;
0477 }
0478
0479 while (($fname = readdir($dh)) !== false)
0480 {
0481 if (is_file("$rootdir$dir$fname"))
0482 {
0483 if (filesize("$rootdir$dir$fname") && preg_match('#\.' . $type . '$#i', $fname))
0484 {
0485 $matches[$dir][] = $fname;
0486 }
0487 }
0488 else if ($fname[0] != '.' && is_dir("$rootdir$dir$fname"))
0489 {
0490 $matches += filelist($rootdir, "$dir$fname", $type);
0491 }
0492 }
0493 closedir($dh);
0494
0495 return $matches;
0496 }
0497
0498 /**
0499 * Move topic(s)
0500 */
0501 function move_topics($topic_ids, $forum_id, $auto_sync = true)
0502 {
0503 global $db;
0504
0505 if (empty($topic_ids))
0506 {
0507 return;
0508 }
0509
0510 $forum_ids = array($forum_id);
0511
0512 if (!is_array($topic_ids))
0513 {
0514 $topic_ids = array($topic_ids);
0515 }
0516
0517 $sql = 'DELETE FROM ' . TOPICS_TABLE . '
0518 WHERE ' . $db->sql_in_set('topic_moved_id', $topic_ids) . '
0519 AND forum_id = ' . $forum_id;
0520 $db->sql_query($sql);
0521
0522 if ($auto_sync)
0523 {
0524 $sql = 'SELECT DISTINCT forum_id
0525 FROM ' . TOPICS_TABLE . '
0526 WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
0527 $result = $db->sql_query($sql);
0528
0529 while ($row = $db->sql_fetchrow($result))
0530 {
0531 $forum_ids[] = $row['forum_id'];
0532 }
0533 $db->sql_freeresult($result);
0534 }
0535
0536 $table_ary = array(TOPICS_TABLE, POSTS_TABLE, LOG_TABLE, DRAFTS_TABLE, TOPICS_TRACK_TABLE);
0537 foreach ($table_ary as $table)
0538 {
0539 $sql = "UPDATE $table
0540 SET forum_id = $forum_id
0541 WHERE " . $db->sql_in_set('topic_id', $topic_ids);
0542 $db->sql_query($sql);
0543 }
0544 unset($table_ary);
0545
0546 if ($auto_sync)
0547 {
0548 sync('forum', 'forum_id', $forum_ids, true, true);
0549 unset($forum_ids);
0550 }
0551 }
0552
0553 /**
0554 * Move post(s)
0555 */
0556 function move_posts($post_ids, $topic_id, $auto_sync = true)
0557 {
0558 global $db;
0559
0560 if (!is_array($post_ids))
0561 {
0562 $post_ids = array($post_ids);
0563 }
0564
0565 $forum_ids = array();
0566 $topic_ids = array($topic_id);
0567
0568 $sql = 'SELECT DISTINCT topic_id, forum_id
0569 FROM ' . POSTS_TABLE . '
0570 WHERE ' . $db->sql_in_set('post_id', $post_ids);
0571 $result = $db->sql_query($sql);
0572
0573 while ($row = $db->sql_fetchrow($result))
0574 {
0575 $forum_ids[] = (int) $row['forum_id'];
0576 $topic_ids[] = (int) $row['topic_id'];
0577 }
0578 $db->sql_freeresult($result);
0579
0580 $sql = 'SELECT forum_id
0581 FROM ' . TOPICS_TABLE . '
0582 WHERE topic_id = ' . $topic_id;
0583 $result = $db->sql_query($sql);
0584 $forum_row = $db->sql_fetchrow($result);
0585 $db->sql_freeresult($result);
0586
0587 if (!$forum_row)
0588 {
0589 trigger_error('NO_TOPIC');
0590 }
0591
0592 $sql = 'UPDATE ' . POSTS_TABLE . '
0593 SET forum_id = ' . (int) $forum_row['forum_id'] . ", topic_id = $topic_id
0594 WHERE " . $db->sql_in_set('post_id', $post_ids);
0595 $db->sql_query($sql);
0596
0597 $sql = 'UPDATE ' . ATTACHMENTS_TABLE . "
0598 SET topic_id = $topic_id, in_message = 0
0599 WHERE " . $db->sql_in_set('post_msg_id', $post_ids);
0600 $db->sql_query($sql);
0601
0602 if ($auto_sync)
0603 {
0604 $forum_ids[] = (int) $forum_row['forum_id'];
0605
0606 sync('topic_reported', 'topic_id', $topic_ids);
0607 sync('topic_attachment', 'topic_id', $topic_ids);
0608 sync('topic', 'topic_id', $topic_ids, true);
0609 sync('forum', 'forum_id', $forum_ids, true, true);
0610 }
0611
0612 // Update posted information
0613 update_posted_info($topic_ids);
0614 }
0615
0616 /**
0617 * Remove topic(s)
0618 */
0619 function delete_topics($where_type, $where_ids, $auto_sync = true, $post_count_sync = true, $call_delete_posts = true)
0620 {
0621 global $db, $config, $phpbb_container;
0622
0623 $approved_topics = 0;
0624 $forum_ids = $topic_ids = array();
0625
0626 if ($where_type === 'range')
0627 {
0628 $where_clause = $where_ids;
0629 }
0630 else
0631 {
0632 $where_ids = (is_array($where_ids)) ? array_unique($where_ids) : array($where_ids);
0633
0634 if (!sizeof($where_ids))
0635 {
0636 return array('topics' => 0, 'posts' => 0);
0637 }
0638
0639 $where_clause = $db->sql_in_set($where_type, $where_ids);
0640 }
0641
0642 // Making sure that delete_posts does not call delete_topics again...
0643 $return = array(
0644 'posts' => ($call_delete_posts) ? delete_posts($where_type, $where_ids, false, true, $post_count_sync, false) : 0,
0645 );
0646
0647 $sql = 'SELECT topic_id, forum_id, topic_visibility, topic_moved_id
0648 FROM ' . TOPICS_TABLE . '
0649 WHERE ' . $where_clause;
0650 $result = $db->sql_query($sql);
0651
0652 while ($row = $db->sql_fetchrow($result))
0653 {
0654 $forum_ids[] = $row['forum_id'];
0655 $topic_ids[] = $row['topic_id'];
0656
0657 if ($row['topic_visibility'] == ITEM_APPROVED && !$row['topic_moved_id'])
0658 {
0659 $approved_topics++;
0660 }
0661 }
0662 $db->sql_freeresult($result);
0663
0664 $return['topics'] = sizeof($topic_ids);
0665
0666 if (!sizeof($topic_ids))
0667 {
0668 return $return;
0669 }
0670
0671 $db->sql_transaction('begin');
0672
0673 $table_ary = array(BOOKMARKS_TABLE, TOPICS_TRACK_TABLE, TOPICS_POSTED_TABLE, POLL_VOTES_TABLE, POLL_OPTIONS_TABLE, TOPICS_WATCH_TABLE, TOPICS_TABLE);
0674
0675 foreach ($table_ary as $table)
0676 {
0677 $sql = "DELETE FROM $table
0678 WHERE " . $db->sql_in_set('topic_id', $topic_ids);
0679 $db->sql_query($sql);
0680 }
0681 unset($table_ary);
0682
0683 $moved_topic_ids = array();
0684
0685 // update the other forums
0686 $sql = 'SELECT topic_id, forum_id
0687 FROM ' . TOPICS_TABLE . '
0688 WHERE ' . $db->sql_in_set('topic_moved_id', $topic_ids);
0689 $result = $db->sql_query($sql);
0690
0691 while ($row = $db->sql_fetchrow($result))
0692 {
0693 $forum_ids[] = $row['forum_id'];
0694 $moved_topic_ids[] = $row['topic_id'];
0695 }
0696 $db->sql_freeresult($result);
0697
0698 if (sizeof($moved_topic_ids))
0699 {
0700 $sql = 'DELETE FROM ' . TOPICS_TABLE . '
0701 WHERE ' . $db->sql_in_set('topic_id', $moved_topic_ids);
0702 $db->sql_query($sql);
0703 }
0704
0705 $db->sql_transaction('commit');
0706
0707 if ($auto_sync)
0708 {
0709 sync('forum', 'forum_id', array_unique($forum_ids), true, true);
0710 sync('topic_reported', $where_type, $where_ids);
0711 }
0712
0713 if ($approved_topics)
0714 {
0715 set_config_count('num_topics', $approved_topics * (-1), true);
0716 }
0717
0718 $phpbb_notifications = $phpbb_container->get('notification_manager');
0719
0720 $phpbb_notifications->delete_notifications(array(
0721 'notification.type.topic',
0722 'notification.type.approve_topic',
0723 'notification.type.topic_in_queue',
0724 ), $topic_ids);
0725
0726 return $return;
0727 }
0728
0729 /**
0730 * Remove post(s)
0731 */
0732 function delete_posts($where_type, $where_ids, $auto_sync = true, $posted_sync = true, $post_count_sync = true, $call_delete_topics = true)
0733 {
0734 global $db, $config, $phpbb_root_path, $phpEx, $auth, $user, $phpbb_container, $phpbb_dispatcher;
0735
0736 // Notifications types to delete
0737 $delete_notifications_types = array(
0738 'notification.type.quote',
0739 'notification.type.approve_post',
0740 'notification.type.post_in_queue',
0741 );
0742
0743 /**
0744 * Perform additional actions before post(s) deletion
0745 *
0746 * @event core.delete_posts_before
0747 * @var string where_type Variable containing posts deletion mode
0748 * @var mixed where_ids Array or comma separated list of posts ids to delete
0749 * @var bool auto_sync Flag indicating if topics/forums should be synchronized
0750 * @var bool posted_sync Flag indicating if topics_posted table should be resynchronized
0751 * @var bool post_count_sync Flag indicating if posts count should be resynchronized
0752 * @var bool call_delete_topics Flag indicating if topics having no posts should be deleted
0753 * @var array delete_notifications_types Array with notifications types to delete
0754 * @since 3.1.0-a4
0755 */
0756 $vars = array(
0757 'where_type',
0758 'where_ids',
0759 'auto_sync',
0760 'posted_sync',
0761 'post_count_sync',
0762 'call_delete_topics',
0763 'delete_notifications_types',
0764 );
0765 extract($phpbb_dispatcher->trigger_event('core.delete_posts_before', compact($vars)));
0766
0767 if ($where_type === 'range')
0768 {
0769 $where_clause = $where_ids;
0770 }
0771 else
0772 {
0773 if (is_array($where_ids))
0774 {
0775 $where_ids = array_unique($where_ids);
0776 }
0777 else
0778 {
0779 $where_ids = array($where_ids);
0780 }
0781
0782 if (!sizeof($where_ids))
0783 {
0784 return false;
0785 }
0786
0787 $where_ids = array_map('intval', $where_ids);
0788
0789 /* Possible code for splitting post deletion
0790 if (sizeof($where_ids) >= 1001)
0791 {
0792 // Split into chunks of 1000
0793 $chunks = array_chunk($where_ids, 1000);
0794
0795 foreach ($chunks as $_where_ids)
0796 {
0797 delete_posts($where_type, $_where_ids, $auto_sync, $posted_sync, $post_count_sync, $call_delete_topics);
0798 }
0799
0800 return;
0801 }*/
0802
0803 $where_clause = $db->sql_in_set($where_type, $where_ids);
0804 }
0805
0806 $approved_posts = 0;
0807 $post_ids = $topic_ids = $forum_ids = $post_counts = $remove_topics = array();
0808
0809 $sql = 'SELECT post_id, poster_id, post_visibility, post_postcount, topic_id, forum_id
0810 FROM ' . POSTS_TABLE . '
0811 WHERE ' . $where_clause;
0812 $result = $db->sql_query($sql);
0813
0814 while ($row = $db->sql_fetchrow($result))
0815 {
0816 $post_ids[] = (int) $row['post_id'];
0817 $poster_ids[] = (int) $row['poster_id'];
0818 $topic_ids[] = (int) $row['topic_id'];
0819 $forum_ids[] = (int) $row['forum_id'];
0820
0821 if ($row['post_postcount'] && $post_count_sync && $row['post_visibility'] == ITEM_APPROVED)
0822 {
0823 $post_counts[$row['poster_id']] = (!empty($post_counts[$row['poster_id']])) ? $post_counts[$row['poster_id']] + 1 : 1;
0824 }
0825
0826 if ($row['post_visibility'] == ITEM_APPROVED)
0827 {
0828 $approved_posts++;
0829 }
0830 }
0831 $db->sql_freeresult($result);
0832
0833 if (!sizeof($post_ids))
0834 {
0835 return false;
0836 }
0837
0838 $db->sql_transaction('begin');
0839
0840 $table_ary = array(POSTS_TABLE, REPORTS_TABLE);
0841
0842 foreach ($table_ary as $table)
0843 {
0844 $sql = "DELETE FROM $table
0845 WHERE " . $db->sql_in_set('post_id', $post_ids);
0846 $db->sql_query($sql);
0847 }
0848 unset($table_ary);
0849
0850 // Adjust users post counts
0851 if (sizeof($post_counts) && $post_count_sync)
0852 {
0853 foreach ($post_counts as $poster_id => $substract)
0854 {
0855 $sql = 'UPDATE ' . USERS_TABLE . '
0856 SET user_posts = 0
0857 WHERE user_id = ' . $poster_id . '
0858 AND user_posts < ' . $substract;
0859 $db->sql_query($sql);
0860
0861 $sql = 'UPDATE ' . USERS_TABLE . '
0862 SET user_posts = user_posts - ' . $substract . '
0863 WHERE user_id = ' . $poster_id . '
0864 AND user_posts >= ' . $substract;
0865 $db->sql_query($sql);
0866 }
0867 }
0868
0869 // Remove topics now having no posts?
0870 if (sizeof($topic_ids))
0871 {
0872 $sql = 'SELECT topic_id
0873 FROM ' . POSTS_TABLE . '
0874 WHERE ' . $db->sql_in_set('topic_id', $topic_ids) . '
0875 GROUP BY topic_id';
0876 $result = $db->sql_query($sql);
0877
0878 while ($row = $db->sql_fetchrow($result))
0879 {
0880 $remove_topics[] = $row['topic_id'];
0881 }
0882 $db->sql_freeresult($result);
0883
0884 // Actually, those not within remove_topics should be removed. ;)
0885 $remove_topics = array_diff($topic_ids, $remove_topics);
0886 }
0887
0888 // Remove the message from the search index
0889 $search_type = $config['search_type'];
0890
0891 if (!class_exists($search_type))
0892 {
0893 trigger_error('NO_SUCH_SEARCH_MODULE');
0894 }
0895
0896 $error = false;
0897 $search = new $search_type($error, $phpbb_root_path, $phpEx, $auth, $config, $db, $user);
0898
0899 if ($error)
0900 {
0901 trigger_error($error);
0902 }
0903
0904 $search->index_remove($post_ids, $poster_ids, $forum_ids);
0905
0906 delete_attachments('post', $post_ids, false);
0907
0908 /**
0909 * Perform additional actions during post(s) deletion
0910 *
0911 * @event core.delete_posts_in_transaction
0912 * @var array post_ids Array with deleted posts' ids
0913 * @var array poster_ids Array with deleted posts' author ids
0914 * @var array topic_ids Array with deleted posts' topic ids
0915 * @var array forum_ids Array with deleted posts' forum ids
0916 * @var string where_type Variable containing posts deletion mode
0917 * @var mixed where_ids Array or comma separated list of posts ids to delete
0918 * @var array delete_notifications_types Array with notifications types to delete
0919 * @since 3.1.0-a4
0920 */
0921 $vars = array(
0922 'post_ids',
0923 'poster_ids',
0924 'topic_ids',
0925 'forum_ids',
0926 'where_type',
0927 'where_ids',
0928 'delete_notifications_types',
0929 );
0930 extract($phpbb_dispatcher->trigger_event('core.delete_posts_in_transaction', compact($vars)));
0931
0932 $db->sql_transaction('commit');
0933
0934 /**
0935 * Perform additional actions after post(s) deletion
0936 *
0937 * @event core.delete_posts_after
0938 * @var array post_ids Array with deleted posts' ids
0939 * @var array poster_ids Array with deleted posts' author ids
0940 * @var array topic_ids Array with deleted posts' topic ids
0941 * @var array forum_ids Array with deleted posts' forum ids
0942 * @var string where_type Variable containing posts deletion mode
0943 * @var mixed where_ids Array or comma separated list of posts ids to delete
0944 * @var array delete_notifications_types Array with notifications types to delete
0945 * @since 3.1.0-a4
0946 */
0947 $vars = array(
0948 'post_ids',
0949 'poster_ids',
0950 'topic_ids',
0951 'forum_ids',
0952 'where_type',
0953 'where_ids',
0954 'delete_notifications_types',
0955 );
0956 extract($phpbb_dispatcher->trigger_event('core.delete_posts_after', compact($vars)));
0957
0958 // Resync topics_posted table
0959 if ($posted_sync)
0960 {
0961 update_posted_info($topic_ids);
0962 }
0963
0964 if ($auto_sync)
0965 {
0966 sync('topic_reported', 'topic_id', $topic_ids);
0967 sync('topic', 'topic_id', $topic_ids, true);
0968 sync('forum', 'forum_id', $forum_ids, true, true);
0969 }
0970
0971 if ($approved_posts && $post_count_sync)
0972 {
0973 set_config_count('num_posts', $approved_posts * (-1), true);
0974 }
0975
0976 // We actually remove topics now to not be inconsistent (the delete_topics function calls this function too)
0977 if (sizeof($remove_topics) && $call_delete_topics)
0978 {
0979 delete_topics('topic_id', $remove_topics, $auto_sync, $post_count_sync, false);
0980 }
0981
0982 $phpbb_notifications = $phpbb_container->get('notification_manager');
0983
0984 $phpbb_notifications->delete_notifications($delete_notifications_types, $post_ids);
0985
0986 return sizeof($post_ids);
0987 }
0988
0989 /**
0990 * Delete Attachments
0991 *
0992 * @param string $mode can be: post|message|topic|attach|user
0993 * @param mixed $ids can be: post_ids, message_ids, topic_ids, attach_ids, user_ids
0994 * @param bool $resync set this to false if you are deleting posts or topics
0995 */
0996 function delete_attachments($mode, $ids, $resync = true)
0997 {
0998 global $db, $config;
0999
1000 // 0 is as bad as an empty array
1001 if (empty($ids))
1002 {
1003 return false;
1004 }
1005
1006 if (is_array($ids))
1007 {
1008 $ids = array_unique($ids);
1009 $ids = array_map('intval', $ids);
1010 }
1011 else
1012 {
1013 $ids = array((int) $ids);
1014 }
1015
1016 $sql_where = '';
1017
1018 switch ($mode)
1019 {
1020 case 'post':
1021 case 'message':
1022 $sql_id = 'post_msg_id';
1023 $sql_where = ' AND in_message = ' . ($mode == 'message' ? 1 : 0);
1024 break;
1025
1026 case 'topic':
1027 $sql_id = 'topic_id';
1028 break;
1029
1030 case 'user':
1031 $sql_id = 'poster_id';
1032 break;
1033
1034 case 'attach':
1035 default:
1036 $sql_id = 'attach_id';
1037 $mode = 'attach';
1038 break;
1039 }
1040
1041 $post_ids = $message_ids = $topic_ids = $physical = array();
1042
1043 // Collect post and topic ids for later use if we need to touch remaining entries (if resync is enabled)
1044 $sql = 'SELECT post_msg_id, topic_id, in_message, physical_filename, thumbnail, filesize, is_orphan
1045 FROM ' . ATTACHMENTS_TABLE . '
1046 WHERE ' . $db->sql_in_set($sql_id, $ids);
1047
1048 $sql .= $sql_where;
1049
1050 $result = $db->sql_query($sql);
1051
1052 while ($row = $db->sql_fetchrow($result))
1053 {
1054 // We only need to store post/message/topic ids if resync is enabled and the file is not orphaned
1055 if ($resync && !$row['is_orphan'])
1056 {
1057 if (!$row['in_message'])
1058 {
1059 $post_ids[] = $row['post_msg_id'];
1060 $topic_ids[] = $row['topic_id'];
1061 }
1062 else
1063 {
1064 $message_ids[] = $row['post_msg_id'];
1065 }
1066 }
1067
1068 $physical[] = array('filename' => $row['physical_filename'], 'thumbnail' => $row['thumbnail'], 'filesize' => $row['filesize'], 'is_orphan' => $row['is_orphan']);
1069 }
1070 $db->sql_freeresult($result);
1071
1072 // Delete attachments
1073 $sql = 'DELETE FROM ' . ATTACHMENTS_TABLE . '
1074 WHERE ' . $db->sql_in_set($sql_id, $ids);
1075
1076 $sql .= $sql_where;
1077
1078 $db->sql_query($sql);
1079 $num_deleted = $db->sql_affectedrows();
1080
1081 if (!$num_deleted)
1082 {
1083 return 0;
1084 }
1085
1086 // Delete attachments from filesystem
1087 $space_removed = $files_removed = 0;
1088 foreach ($physical as $file_ary)
1089 {
1090 if (phpbb_unlink($file_ary['filename'], 'file', true) && !$file_ary['is_orphan'])
1091 {
1092 // Only non-orphaned files count to the file size
1093 $space_removed += $file_ary['filesize'];
1094 $files_removed++;
1095 }
1096
1097 if ($file_ary['thumbnail'])
1098 {
1099 phpbb_unlink($file_ary['filename'], 'thumbnail', true);
1100 }
1101 }
1102
1103 if ($space_removed || $files_removed)
1104 {
1105 set_config_count('upload_dir_size', $space_removed * (-1), true);
1106 set_config_count('num_files', $files_removed * (-1), true);
1107 }
1108
1109 // If we do not resync, we do not need to adjust any message, post, topic or user entries
1110 if (!$resync)
1111 {
1112 return $num_deleted;
1113 }
1114
1115 // No more use for the original ids
1116 unset($ids);
1117
1118 // Now, we need to resync posts, messages, topics. We go through every one of them
1119 $post_ids = array_unique($post_ids);
1120 $message_ids = array_unique($message_ids);
1121 $topic_ids = array_unique($topic_ids);
1122
1123 // Update post indicators for posts now no longer having attachments
1124 if (sizeof($post_ids))
1125 {
1126 // Just check which posts are still having an assigned attachment not orphaned by querying the attachments table
1127 $sql = 'SELECT post_msg_id
1128 FROM ' . ATTACHMENTS_TABLE . '
1129 WHERE ' . $db->sql_in_set('post_msg_id', $post_ids) . '
1130 AND in_message = 0
1131 AND is_orphan = 0';
1132 $result = $db->sql_query($sql);
1133
1134 $remaining_ids = array();
1135 while ($row = $db->sql_fetchrow($result))
1136 {
1137 $remaining_ids[] = $row['post_msg_id'];
1138 }
1139 $db->sql_freeresult($result);
1140
1141 // Now only unset those ids remaining
1142 $post_ids = array_diff($post_ids, $remaining_ids);
1143
1144 if (sizeof($post_ids))
1145 {
1146 $sql = 'UPDATE ' . POSTS_TABLE . '
1147 SET post_attachment = 0
1148 WHERE ' . $db->sql_in_set('post_id', $post_ids);
1149 $db->sql_query($sql);
1150 }
1151 }
1152
1153 // Update message table if messages are affected
1154 if (sizeof($message_ids))
1155 {
1156 // Just check which messages are still having an assigned attachment not orphaned by querying the attachments table
1157 $sql = 'SELECT post_msg_id
1158 FROM ' . ATTACHMENTS_TABLE . '
1159 WHERE ' . $db->sql_in_set('post_msg_id', $message_ids) . '
1160 AND in_message = 1
1161 AND is_orphan = 0';
1162 $result = $db->sql_query($sql);
1163
1164 $remaining_ids = array();
1165 while ($row = $db->sql_fetchrow($result))
1166 {
1167 $remaining_ids[] = $row['post_msg_id'];
1168 }
1169 $db->sql_freeresult($result);
1170
1171 // Now only unset those ids remaining
1172 $message_ids = array_diff($message_ids, $remaining_ids);
1173
1174 if (sizeof($message_ids))
1175 {
1176 $sql = 'UPDATE ' . PRIVMSGS_TABLE . '
1177 SET message_attachment = 0
1178 WHERE ' . $db->sql_in_set('msg_id', $message_ids);
1179 $db->sql_query($sql);
1180 }
1181 }
1182
1183 // Now update the topics. This is a bit trickier, because there could be posts still having attachments within the topic
1184 if (sizeof($topic_ids))
1185 {
1186 // Just check which topics are still having an assigned attachment not orphaned by querying the attachments table (much less entries expected)
1187 $sql = 'SELECT topic_id
1188 FROM ' . ATTACHMENTS_TABLE . '
1189 WHERE ' . $db->sql_in_set('topic_id', $topic_ids) . '
1190 AND is_orphan = 0';
1191 $result = $db->sql_query($sql);
1192
1193 $remaining_ids = array();
1194 while ($row = $db->sql_fetchrow($result))
1195 {
1196 $remaining_ids[] = $row['topic_id'];
1197 }
1198 $db->sql_freeresult($result);
1199
1200 // Now only unset those ids remaining
1201 $topic_ids = array_diff($topic_ids, $remaining_ids);
1202
1203 if (sizeof($topic_ids))
1204 {
1205 $sql = 'UPDATE ' . TOPICS_TABLE . '
1206 SET topic_attachment = 0
1207 WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1208 $db->sql_query($sql);
1209 }
1210 }
1211
1212 return $num_deleted;
1213 }
1214
1215 /**
1216 * Deletes shadow topics pointing to a specified forum.
1217 *
1218 * @param int $forum_id The forum id
1219 * @param string $sql_more Additional WHERE statement, e.g. t.topic_time < (time() - 1234)
1220 * @param bool $auto_sync Will call sync() if this is true
1221 *
1222 * @return array Array with affected forums
1223 */
1224 function delete_topic_shadows($forum_id, $sql_more = '', $auto_sync = true)
1225 {
1226 global $db;
1227
1228 if (!$forum_id)
1229 {
1230 // Nothing to do.
1231 return;
1232 }
1233
1234 // Set of affected forums we have to resync
1235 $sync_forum_ids = array();
1236
1237 // Amount of topics we select and delete at once.
1238 $batch_size = 500;
1239
1240 do
1241 {
1242 $sql = 'SELECT t2.forum_id, t2.topic_id
1243 FROM ' . TOPICS_TABLE . ' t2, ' . TOPICS_TABLE . ' t
1244 WHERE t2.topic_moved_id = t.topic_id
1245 AND t.forum_id = ' . (int) $forum_id . '
1246 ' . (($sql_more) ? 'AND ' . $sql_more : '');
1247 $result = $db->sql_query_limit($sql, $batch_size);
1248
1249 $topic_ids = array();
1250 while ($row = $db->sql_fetchrow($result))
1251 {
1252 $topic_ids[] = (int) $row['topic_id'];
1253
1254 $sync_forum_ids[(int) $row['forum_id']] = (int) $row['forum_id'];
1255 }
1256 $db->sql_freeresult($result);
1257
1258 if (!empty($topic_ids))
1259 {
1260 $sql = 'DELETE FROM ' . TOPICS_TABLE . '
1261 WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1262 $db->sql_query($sql);
1263 }
1264 }
1265 while (sizeof($topic_ids) == $batch_size);
1266
1267 if ($auto_sync)
1268 {
1269 sync('forum', 'forum_id', $sync_forum_ids, true, true);
1270 }
1271
1272 return $sync_forum_ids;
1273 }
1274
1275 /**
1276 * Update/Sync posted information for topics
1277 */
1278 function update_posted_info(&$topic_ids)
1279 {
1280 global $db, $config;
1281
1282 if (empty($topic_ids) || !$config['load_db_track'])
1283 {
1284 return;
1285 }
1286
1287 // First of all, let us remove any posted information for these topics
1288 $sql = 'DELETE FROM ' . TOPICS_POSTED_TABLE . '
1289 WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1290 $db->sql_query($sql);
1291
1292 // Now, let us collect the user/topic combos for rebuilding the information
1293 $sql = 'SELECT poster_id, topic_id
1294 FROM ' . POSTS_TABLE . '
1295 WHERE ' . $db->sql_in_set('topic_id', $topic_ids) . '
1296 AND poster_id <> ' . ANONYMOUS . '
1297 GROUP BY poster_id, topic_id';
1298 $result = $db->sql_query($sql);
1299
1300 $posted = array();
1301 while ($row = $db->sql_fetchrow($result))
1302 {
1303 // Add as key to make them unique (grouping by) and circumvent empty keys on array_unique
1304 $posted[$row['poster_id']][] = $row['topic_id'];
1305 }
1306 $db->sql_freeresult($result);
1307
1308 // Now add the information...
1309 $sql_ary = array();
1310 foreach ($posted as $user_id => $topic_row)
1311 {
1312 foreach ($topic_row as $topic_id)
1313 {
1314 $sql_ary[] = array(
1315 'user_id' => (int) $user_id,
1316 'topic_id' => (int) $topic_id,
1317 'topic_posted' => 1,
1318 );
1319 }
1320 }
1321 unset($posted);
1322
1323 $db->sql_multi_insert(TOPICS_POSTED_TABLE, $sql_ary);
1324 }
1325
1326 /**
1327 * Delete attached file
1328 */
1329 function phpbb_unlink($filename, $mode = 'file', $entry_removed = false)
1330 {
1331 global $db, $phpbb_root_path, $config;
1332
1333 // Because of copying topics or modifications a physical filename could be assigned more than once. If so, do not remove the file itself.
1334 $sql = 'SELECT COUNT(attach_id) AS num_entries
1335 FROM ' . ATTACHMENTS_TABLE . "
1336 WHERE physical_filename = '" . $db->sql_escape(utf8_basename($filename)) . "'";
1337 $result = $db->sql_query($sql);
1338 $num_entries = (int) $db->sql_fetchfield('num_entries');
1339 $db->sql_freeresult($result);
1340
1341 // Do not remove file if at least one additional entry with the same name exist.
1342 if (($entry_removed && $num_entries > 0) || (!$entry_removed && $num_entries > 1))
1343 {
1344 return false;
1345 }
1346
1347 $filename = ($mode == 'thumbnail') ? 'thumb_' . utf8_basename($filename) : utf8_basename($filename);
1348 return @unlink($phpbb_root_path . $config['upload_path'] . '/' . $filename);
1349 }
1350
1351 /**
1352 * All-encompasing sync function
1353 *
1354 * Exaples:
1355 * <code>
1356 * sync('topic', 'topic_id', 123); // resync topic #123
1357 * sync('topic', 'forum_id', array(2, 3)); // resync topics from forum #2 and #3
1358 * sync('topic'); // resync all topics
1359 * sync('topic', 'range', 'topic_id BETWEEN 1 AND 60'); // resync a range of topics/forums (only available for 'topic' and 'forum' modes)
1360 * </code>
1361 *
1362 * Modes:
1363 * - forum Resync complete forum
1364 * - topic Resync topics
1365 * - topic_moved Removes topic shadows that would be in the same forum as the topic they link to
1366 * - topic_visibility Resyncs the topic_visibility flag according to the status of the first post
1367 * - post_reported Resyncs the post_reported flag, relying on actual reports
1368 * - topic_reported Resyncs the topic_reported flag, relying on post_reported flags
1369 * - post_attachement Same as post_reported, but with attachment flags
1370 * - topic_attachement Same as topic_reported, but with attachment flags
1371 */
1372 function sync($mode, $where_type = '', $where_ids = '', $resync_parents = false, $sync_extra = false)
1373 {
1374 global $db;
1375
1376 if (is_array($where_ids))
1377 {
1378 $where_ids = array_unique($where_ids);
1379 $where_ids = array_map('intval', $where_ids);
1380 }
1381 else if ($where_type != 'range')
1382 {
1383 $where_ids = ($where_ids) ? array((int) $where_ids) : array();
1384 }
1385
1386 if ($mode == 'forum' || $mode == 'topic' || $mode == 'topic_visibility' || $mode == 'topic_reported' || $mode == 'post_reported')
1387 {
1388 if (!$where_type)
1389 {
1390 $where_sql = '';
1391 $where_sql_and = 'WHERE';
1392 }
1393 else if ($where_type == 'range')
1394 {
1395 // Only check a range of topics/forums. For instance: 'topic_id BETWEEN 1 AND 60'
1396 $where_sql = 'WHERE (' . $mode[0] . ".$where_ids)";
1397 $where_sql_and = $where_sql . "\n\tAND";
1398 }
1399 else
1400 {
1401 // Do not sync the "global forum"
1402 $where_ids = array_diff($where_ids, array(0));
1403
1404 if (!sizeof($where_ids))
1405 {
1406 // Empty array with IDs. This means that we don't have any work to do. Just return.
1407 return;
1408 }
1409
1410 // Limit the topics/forums we are syncing, use specific topic/forum IDs.
1411 // $where_type contains the field for the where clause (forum_id, topic_id)
1412 $where_sql = 'WHERE ' . $db->sql_in_set($mode[0] . '.' . $where_type, $where_ids);
1413 $where_sql_and = $where_sql . "\n\tAND";
1414 }
1415 }
1416 else
1417 {
1418 if (!sizeof($where_ids))
1419 {
1420 return;
1421 }
1422
1423 // $where_type contains the field for the where clause (forum_id, topic_id)
1424 $where_sql = 'WHERE ' . $db->sql_in_set($mode[0] . '.' . $where_type, $where_ids);
1425 $where_sql_and = $where_sql . "\n\tAND";
1426 }
1427
1428 switch ($mode)
1429 {
1430 case 'topic_moved':
1431 $db->sql_transaction('begin');
1432 switch ($db->get_sql_layer())
1433 {
1434 case 'mysql4':
1435 case 'mysqli':
1436 $sql = 'DELETE FROM ' . TOPICS_TABLE . '
1437 USING ' . TOPICS_TABLE . ' t1, ' . TOPICS_TABLE . " t2
1438 WHERE t1.topic_moved_id = t2.topic_id
1439 AND t1.forum_id = t2.forum_id";
1440 $db->sql_query($sql);
1441 break;
1442
1443 default:
1444 $sql = 'SELECT t1.topic_id
1445 FROM ' .TOPICS_TABLE . ' t1, ' . TOPICS_TABLE . " t2
1446 WHERE t1.topic_moved_id = t2.topic_id
1447 AND t1.forum_id = t2.forum_id";
1448 $result = $db->sql_query($sql);
1449
1450 $topic_id_ary = array();
1451 while ($row = $db->sql_fetchrow($result))
1452 {
1453 $topic_id_ary[] = $row['topic_id'];
1454 }
1455 $db->sql_freeresult($result);
1456
1457 if (!sizeof($topic_id_ary))
1458 {
1459 return;
1460 }
1461
1462 $sql = 'DELETE FROM ' . TOPICS_TABLE . '
1463 WHERE ' . $db->sql_in_set('topic_id', $topic_id_ary);
1464 $db->sql_query($sql);
1465
1466 break;
1467 }
1468
1469 $db->sql_transaction('commit');
1470 break;
1471
1472 case 'topic_visibility':
1473
1474 $db->sql_transaction('begin');
1475
1476 $sql = 'SELECT t.topic_id, p.post_visibility
1477 FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
1478 $where_sql_and p.topic_id = t.topic_id
1479 AND p.post_visibility = " . ITEM_APPROVED;
1480 $result = $db->sql_query($sql);
1481
1482 $topics_approved = array();
1483 while ($row = $db->sql_fetchrow($result))
1484 {
1485 $topics_approved[] = (int) $row['topic_id'];
1486 }
1487 $db->sql_freeresult($result);
1488
1489 $sql = 'SELECT t.topic_id, p.post_visibility
1490 FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
1491 $where_sql_and " . $db->sql_in_set('t.topic_id', $topics_approved, true, true) . '
1492 AND p.topic_id = t.topic_id
1493 AND p.post_visibility = ' . ITEM_DELETED;
1494 $result = $db->sql_query($sql);
1495
1496 $topics_softdeleted = array();
1497 while ($row = $db->sql_fetchrow($result))
1498 {
1499 $topics_softdeleted[] = (int) $row['topic_id'];
1500 }
1501 $db->sql_freeresult($result);
1502
1503 $topics_softdeleted = array_diff($topics_softdeleted, $topics_approved);
1504 $topics_not_unapproved = array_merge($topics_softdeleted, $topics_approved);
1505
1506 $update_ary = array(
1507 ITEM_UNAPPROVED => (!empty($topics_not_unapproved)) ? $where_sql_and . ' ' . $db->sql_in_set('topic_id', $topics_not_unapproved, true) : '',
1508 ITEM_APPROVED => (!empty($topics_approved)) ? ' WHERE ' . $db->sql_in_set('topic_id', $topics_approved) : '',
1509 ITEM_DELETED => (!empty($topics_softdeleted)) ? ' WHERE ' . $db->sql_in_set('topic_id', $topics_softdeleted) : '',
1510 );
1511
1512 foreach ($update_ary as $visibility => $sql_where)
1513 {
1514 if ($sql_where)
1515 {
1516 $sql = 'UPDATE ' . TOPICS_TABLE . '
1517 SET topic_visibility = ' . $visibility . '
1518 ' . $sql_where;
1519 $db->sql_query($sql);
1520 }
1521 }
1522
1523 $db->sql_transaction('commit');
1524 break;
1525
1526 case 'post_reported':
1527 $post_ids = $post_reported = array();
1528
1529 $db->sql_transaction('begin');
1530
1531 $sql = 'SELECT p.post_id, p.post_reported
1532 FROM ' . POSTS_TABLE . " p
1533 $where_sql
1534 GROUP BY p.post_id, p.post_reported";
1535 $result = $db->sql_query($sql);
1536
1537 while ($row = $db->sql_fetchrow($result))
1538 {
1539 $post_ids[$row['post_id']] = $row['post_id'];
1540 if ($row['post_reported'])
1541 {
1542 $post_reported[$row['post_id']] = 1;
1543 }
1544 }
1545 $db->sql_freeresult($result);
1546
1547 $sql = 'SELECT DISTINCT(post_id)
1548 FROM ' . REPORTS_TABLE . '
1549 WHERE ' . $db->sql_in_set('post_id', $post_ids) . '
1550 AND report_closed = 0';
1551 $result = $db->sql_query($sql);
1552
1553 $post_ids = array();
1554 while ($row = $db->sql_fetchrow($result))
1555 {
1556 if (!isset($post_reported[$row['post_id']]))
1557 {
1558 $post_ids[] = $row['post_id'];
1559 }
1560 else
1561 {
1562 unset($post_reported[$row['post_id']]);
1563 }
1564 }
1565 $db->sql_freeresult($result);
1566
1567 // $post_reported should be empty by now, if it's not it contains
1568 // posts that are falsely flagged as reported
1569 foreach ($post_reported as $post_id => $void)
1570 {
1571 $post_ids[] = $post_id;
1572 }
1573
1574 if (sizeof($post_ids))
1575 {
1576 $sql = 'UPDATE ' . POSTS_TABLE . '
1577 SET post_reported = 1 - post_reported
1578 WHERE ' . $db->sql_in_set('post_id', $post_ids);
1579 $db->sql_query($sql);
1580 }
1581
1582 $db->sql_transaction('commit');
1583 break;
1584
1585 case 'topic_reported':
1586 if ($sync_extra)
1587 {
1588 sync('post_reported', $where_type, $where_ids);
1589 }
1590
1591 $topic_ids = $topic_reported = array();
1592
1593 $db->sql_transaction('begin');
1594
1595 $sql = 'SELECT DISTINCT(t.topic_id)
1596 FROM ' . POSTS_TABLE . " t
1597 $where_sql_and t.post_reported = 1";
1598 $result = $db->sql_query($sql);
1599
1600 while ($row = $db->sql_fetchrow($result))
1601 {
1602 $topic_reported[$row['topic_id']] = 1;
1603 }
1604 $db->sql_freeresult($result);
1605
1606 $sql = 'SELECT t.topic_id, t.topic_reported
1607 FROM ' . TOPICS_TABLE . " t
1608 $where_sql";
1609 $result = $db->sql_query($sql);
1610
1611 while ($row = $db->sql_fetchrow($result))
1612 {
1613 if ($row['topic_reported'] ^ isset($topic_reported[$row['topic_id']]))
1614 {
1615 $topic_ids[] = $row['topic_id'];
1616 }
1617 }
1618 $db->sql_freeresult($result);
1619
1620 if (sizeof($topic_ids))
1621 {
1622 $sql = 'UPDATE ' . TOPICS_TABLE . '
1623 SET topic_reported = 1 - topic_reported
1624 WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1625 $db->sql_query($sql);
1626 }
1627
1628 $db->sql_transaction('commit');
1629 break;
1630
1631 case 'post_attachment':
1632 $post_ids = $post_attachment = array();
1633
1634 $db->sql_transaction('begin');
1635
1636 $sql = 'SELECT p.post_id, p.post_attachment
1637 FROM ' . POSTS_TABLE . " p
1638 $where_sql
1639 GROUP BY p.post_id, p.post_attachment";
1640 $result = $db->sql_query($sql);
1641
1642 while ($row = $db->sql_fetchrow($result))
1643 {
1644 $post_ids[$row['post_id']] = $row['post_id'];
1645 if ($row['post_attachment'])
1646 {
1647 $post_attachment[$row['post_id']] = 1;
1648 }
1649 }
1650 $db->sql_freeresult($result);
1651
1652 $sql = 'SELECT DISTINCT(post_msg_id)
1653 FROM ' . ATTACHMENTS_TABLE . '
1654 WHERE ' . $db->sql_in_set('post_msg_id', $post_ids) . '
1655 AND in_message = 0';
1656 $result = $db->sql_query($sql);
1657
1658 $post_ids = array();
1659 while ($row = $db->sql_fetchrow($result))
1660 {
1661 if (!isset($post_attachment[$row['post_msg_id']]))
1662 {
1663 $post_ids[] = $row['post_msg_id'];
1664 }
1665 else
1666 {
1667 unset($post_attachment[$row['post_msg_id']]);
1668 }
1669 }
1670 $db->sql_freeresult($result);
1671
1672 // $post_attachment should be empty by now, if it's not it contains
1673 // posts that are falsely flagged as having attachments
1674 foreach ($post_attachment as $post_id => $void)
1675 {
1676 $post_ids[] = $post_id;
1677 }
1678
1679 if (sizeof($post_ids))
1680 {
1681 $sql = 'UPDATE ' . POSTS_TABLE . '
1682 SET post_attachment = 1 - post_attachment
1683 WHERE ' . $db->sql_in_set('post_id', $post_ids);
1684 $db->sql_query($sql);
1685 }
1686
1687 $db->sql_transaction('commit');
1688 break;
1689
1690 case 'topic_attachment':
1691 if ($sync_extra)
1692 {
1693 sync('post_attachment', $where_type, $where_ids);
1694 }
1695
1696 $topic_ids = $topic_attachment = array();
1697
1698 $db->sql_transaction('begin');
1699
1700 $sql = 'SELECT DISTINCT(t.topic_id)
1701 FROM ' . POSTS_TABLE . " t
1702 $where_sql_and t.post_attachment = 1";
1703 $result = $db->sql_query($sql);
1704
1705 while ($row = $db->sql_fetchrow($result))
1706 {
1707 $topic_attachment[$row['topic_id']] = 1;
1708 }
1709 $db->sql_freeresult($result);
1710
1711 $sql = 'SELECT t.topic_id, t.topic_attachment
1712 FROM ' . TOPICS_TABLE . " t
1713 $where_sql";
1714 $result = $db->sql_query($sql);
1715
1716 while ($row = $db->sql_fetchrow($result))
1717 {
1718 if ($row['topic_attachment'] ^ isset($topic_attachment[$row['topic_id']]))
1719 {
1720 $topic_ids[] = $row['topic_id'];
1721 }
1722 }
1723 $db->sql_freeresult($result);
1724
1725 if (sizeof($topic_ids))
1726 {
1727 $sql = 'UPDATE ' . TOPICS_TABLE . '
1728 SET topic_attachment = 1 - topic_attachment
1729 WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
1730 $db->sql_query($sql);
1731 }
1732
1733 $db->sql_transaction('commit');
1734
1735 break;
1736
1737 case 'forum':
1738
1739 $db->sql_transaction('begin');
1740
1741 // 1: Get the list of all forums
1742 $sql = 'SELECT f.*
1743 FROM ' . FORUMS_TABLE . " f
1744 $where_sql";
1745 $result = $db->sql_query($sql);
1746
1747 $forum_data = $forum_ids = $post_ids = $last_post_id = $post_info = array();
1748 while ($row = $db->sql_fetchrow($result))
1749 {
1750 if ($row['forum_type'] == FORUM_LINK)
1751 {
1752 continue;
1753 }
1754
1755 $forum_id = (int) $row['forum_id'];
1756 $forum_ids[$forum_id] = $forum_id;
1757
1758 $forum_data[$forum_id] = $row;
1759 if ($sync_extra)
1760 {
1761 $forum_data[$forum_id]['posts_approved'] = 0;
1762 $forum_data[$forum_id]['posts_unapproved'] = 0;
1763 $forum_data[$forum_id]['posts_softdeleted'] = 0;
1764 $forum_data[$forum_id]['topics_approved'] = 0;
1765 $forum_data[$forum_id]['topics_unapproved'] = 0;
1766 $forum_data[$forum_id]['topics_softdeleted'] = 0;
1767 }
1768 $forum_data[$forum_id]['last_post_id'] = 0;
1769 $forum_data[$forum_id]['last_post_subject'] = '';
1770 $forum_data[$forum_id]['last_post_time'] = 0;
1771 $forum_data[$forum_id]['last_poster_id'] = 0;
1772 $forum_data[$forum_id]['last_poster_name'] = '';
1773 $forum_data[$forum_id]['last_poster_colour'] = '';
1774 }
1775 $db->sql_freeresult($result);
1776
1777 if (!sizeof($forum_ids))
1778 {
1779 break;
1780 }
1781
1782 $forum_ids = array_values($forum_ids);
1783
1784 // 2: Get topic counts for each forum (optional)
1785 if ($sync_extra)
1786 {
1787 $sql = 'SELECT forum_id, topic_visibility, COUNT(topic_id) AS total_topics
1788 FROM ' . TOPICS_TABLE . '
1789 WHERE ' . $db->sql_in_set('forum_id', $forum_ids) . '
1790 GROUP BY forum_id, topic_visibility';
1791 $result = $db->sql_query($sql);
1792
1793 while ($row = $db->sql_fetchrow($result))
1794 {
1795 $forum_id = (int) $row['forum_id'];
1796
1797 if ($row['topic_visibility'] == ITEM_APPROVED)
1798 {
1799 $forum_data[$forum_id]['topics_approved'] = $row['total_topics'];
1800 }
1801 else if ($row['topic_visibility'] == ITEM_UNAPPROVED || $row['topic_visibility'] == ITEM_REAPPROVE)
1802 {
1803 $forum_data[$forum_id]['topics_unapproved'] = $row['total_topics'];
1804 }
1805 else if ($row['topic_visibility'] == ITEM_DELETED)
1806 {
1807 $forum_data[$forum_id]['topics_softdeleted'] = $row['total_topics'];
1808 }
1809 }
1810 $db->sql_freeresult($result);
1811 }
1812
1813 // 3: Get post count for each forum (optional)
1814 if ($sync_extra)
1815 {
1816 if (sizeof($forum_ids) == 1)
1817 {
1818 $sql = 'SELECT SUM(t.topic_posts_approved) AS forum_posts_approved, SUM(t.topic_posts_unapproved) AS forum_posts_unapproved, SUM(t.topic_posts_softdeleted) AS forum_posts_softdeleted
1819 FROM ' . TOPICS_TABLE . ' t
1820 WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
1821 AND t.topic_status <> ' . ITEM_MOVED;
1822 }
1823 else
1824 {
1825 $sql = 'SELECT t.forum_id, SUM(t.topic_posts_approved) AS forum_posts_approved, SUM(t.topic_posts_unapproved) AS forum_posts_unapproved, SUM(t.topic_posts_softdeleted) AS forum_posts_softdeleted
1826 FROM ' . TOPICS_TABLE . ' t
1827 WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
1828 AND t.topic_status <> ' . ITEM_MOVED . '
1829 GROUP BY t.forum_id';
1830 }
1831
1832 $result = $db->sql_query($sql);
1833
1834 while ($row = $db->sql_fetchrow($result))
1835 {
1836 $forum_id = (sizeof($forum_ids) == 1) ? (int) $forum_ids[0] : (int) $row['forum_id'];
1837
1838 $forum_data[$forum_id]['posts_approved'] = (int) $row['forum_posts_approved'];
1839 $forum_data[$forum_id]['posts_unapproved'] = (int) $row['forum_posts_unapproved'];
1840 $forum_data[$forum_id]['posts_softdeleted'] = (int) $row['forum_posts_softdeleted'];
1841 }
1842 $db->sql_freeresult($result);
1843 }
1844
1845 // 4: Get last_post_id for each forum
1846 if (sizeof($forum_ids) == 1)
1847 {
1848 $sql = 'SELECT MAX(t.topic_last_post_id) as last_post_id
1849 FROM ' . TOPICS_TABLE . ' t
1850 WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
1851 AND t.topic_visibility = ' . ITEM_APPROVED;
1852 }
1853 else
1854 {
1855 $sql = 'SELECT t.forum_id, MAX(t.topic_last_post_id) as last_post_id
1856 FROM ' . TOPICS_TABLE . ' t
1857 WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
1858 AND t.topic_visibility = ' . ITEM_APPROVED . '
1859 GROUP BY t.forum_id';
1860 }
1861
1862 $result = $db->sql_query($sql);
1863
1864 while ($row = $db->sql_fetchrow($result))
1865 {
1866 $forum_id = (sizeof($forum_ids) == 1) ? (int) $forum_ids[0] : (int) $row['forum_id'];
1867
1868 $forum_data[$forum_id]['last_post_id'] = (int) $row['last_post_id'];
1869
1870 $post_ids[] = $row['last_post_id'];
1871 }
1872 $db->sql_freeresult($result);
1873
1874 // 5: Retrieve last_post infos
1875 if (sizeof($post_ids))
1876 {
1877 $sql = 'SELECT p.post_id, p.poster_id, p.post_subject, p.post_time, p.post_username, u.username, u.user_colour
1878 FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
1879 WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
1880 AND p.poster_id = u.user_id';
1881 $result = $db->sql_query($sql);
1882
1883 while ($row = $db->sql_fetchrow($result))
1884 {
1885 $post_info[$row['post_id']] = $row;
1886 }
1887 $db->sql_freeresult($result);
1888
1889 foreach ($forum_data as $forum_id => $data)
1890 {
1891 if ($data['last_post_id'])
1892 {
1893 if (isset($post_info[$data['last_post_id']]))
1894 {
1895 $forum_data[$forum_id]['last_post_subject'] = $post_info[$data['last_post_id']]['post_subject'];
1896 $forum_data[$forum_id]['last_post_time'] = $post_info[$data['last_post_id']]['post_time'];
1897 $forum_data[$forum_id]['last_poster_id'] = $post_info[$data['last_post_id']]['poster_id'];
1898 $forum_data[$forum_id]['last_poster_name'] = ($post_info[$data['last_post_id']]['poster_id'] != ANONYMOUS) ? $post_info[$data['last_post_id']]['username'] : $post_info[$data['last_post_id']]['post_username'];
1899 $forum_data[$forum_id]['last_poster_colour'] = $post_info[$data['last_post_id']]['user_colour'];
1900 }
1901 else
1902 {
1903 // For some reason we did not find the post in the db
1904 $forum_data[$forum_id]['last_post_id'] = 0;
1905 $forum_data[$forum_id]['last_post_subject'] = '';
1906 $forum_data[$forum_id]['last_post_time'] = 0;
1907 $forum_data[$forum_id]['last_poster_id'] = 0;
1908 $forum_data[$forum_id]['last_poster_name'] = '';
1909 $forum_data[$forum_id]['last_poster_colour'] = '';
1910 }
1911 }
1912 }
1913 unset($post_info);
1914 }
1915
1916 // 6: Now do that thing
1917 $fieldnames = array('last_post_id', 'last_post_subject', 'last_post_time', 'last_poster_id', 'last_poster_name', 'last_poster_colour');
1918
1919 if ($sync_extra)
1920 {
1921 array_push($fieldnames, 'posts_approved', 'posts_unapproved', 'posts_softdeleted', 'topics_approved', 'topics_unapproved', 'topics_softdeleted');
1922 }
1923
1924 foreach ($forum_data as $forum_id => $row)
1925 {
1926 $sql_ary = array();
1927
1928 foreach ($fieldnames as $fieldname)
1929 {
1930 if ($row['forum_' . $fieldname] != $row[$fieldname])
1931 {
1932 if (preg_match('#(name|colour|subject)$#', $fieldname))
1933 {
1934 $sql_ary['forum_' . $fieldname] = (string) $row[$fieldname];
1935 }
1936 else
1937 {
1938 $sql_ary['forum_' . $fieldname] = (int) $row[$fieldname];
1939 }
1940 }
1941 }
1942
1943 if (sizeof($sql_ary))
1944 {
1945 $sql = 'UPDATE ' . FORUMS_TABLE . '
1946 SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
1947 WHERE forum_id = ' . $forum_id;
1948 $db->sql_query($sql);
1949 }
1950 }
1951
1952 $db->sql_transaction('commit');
1953 break;
1954
1955 case 'topic':
1956 $topic_data = $post_ids = $resync_forums = $delete_topics = $delete_posts = $moved_topics = array();
1957
1958 $db->sql_transaction('begin');
1959
1960 $sql = 'SELECT t.topic_id, t.forum_id, t.topic_moved_id, t.topic_visibility, ' . (($sync_extra) ? 't.topic_attachment, t.topic_reported, ' : '') . 't.topic_poster, t.topic_time, t.topic_posts_approved, t.topic_posts_unapproved, t.topic_posts_softdeleted, t.topic_first_post_id, t.topic_first_poster_name, t.topic_first_poster_colour, t.topic_last_post_id, t.topic_last_post_subject, t.topic_last_poster_id, t.topic_last_poster_name, t.topic_last_poster_colour, t.topic_last_post_time
1961 FROM ' . TOPICS_TABLE . " t
1962 $where_sql";
1963 $result = $db->sql_query($sql);
1964
1965 while ($row = $db->sql_fetchrow($result))
1966 {
1967 if ($row['topic_moved_id'])
1968 {
1969 $moved_topics[] = $row['topic_id'];
1970 continue;
1971 }
1972
1973 $topic_id = (int) $row['topic_id'];
1974 $topic_data[$topic_id] = $row;
1975 $topic_data[$topic_id]['visibility'] = ITEM_UNAPPROVED;
1976 $topic_data[$topic_id]['posts_approved'] = 0;
1977 $topic_data[$topic_id]['posts_unapproved'] = 0;
1978 $topic_data[$topic_id]['posts_softdeleted'] = 0;
1979 $topic_data[$topic_id]['first_post_id'] = 0;
1980 $topic_data[$topic_id]['last_post_id'] = 0;
1981 unset($topic_data[$topic_id]['topic_id']);
1982
1983 // This array holds all topic_ids
1984 $delete_topics[$topic_id] = '';
1985
1986 if ($sync_extra)
1987 {
1988 $topic_data[$topic_id]['reported'] = 0;
1989 $topic_data[$topic_id]['attachment'] = 0;
1990 }
1991 }
1992 $db->sql_freeresult($result);
1993
1994 // Use "t" as table alias because of the $where_sql clause
1995 // NOTE: 't.post_visibility' in the GROUP BY is causing a major slowdown.
1996 $sql = 'SELECT t.topic_id, t.post_visibility, COUNT(t.post_id) AS total_posts, MIN(t.post_id) AS first_post_id, MAX(t.post_id) AS last_post_id
1997 FROM ' . POSTS_TABLE . " t
1998 $where_sql
1999 GROUP BY t.topic_id, t.post_visibility";
2000 $result = $db->sql_query($sql);
2001
2002 while ($row = $db->sql_fetchrow($result))
2003 {
2004 $topic_id = (int) $row['topic_id'];
2005
2006 $row['first_post_id'] = (int) $row['first_post_id'];
2007 $row['last_post_id'] = (int) $row['last_post_id'];
2008
2009 if (!isset($topic_data[$topic_id]))
2010 {
2011 // Hey, these posts come from a topic that does not exist
2012 $delete_posts[$topic_id] = '';
2013 }
2014 else
2015 {
2016 // Unset the corresponding entry in $delete_topics
2017 // When we'll be done, only topics with no posts will remain
2018 unset($delete_topics[$topic_id]);
2019
2020 if ($row['post_visibility'] == ITEM_APPROVED)
2021 {
2022 $topic_data[$topic_id]['posts_approved'] = $row['total_posts'];
2023 }
2024 else if ($row['post_visibility'] == ITEM_UNAPPROVED || $row['post_visibility'] == ITEM_REAPPROVE)
2025 {
2026 $topic_data[$topic_id]['posts_unapproved'] = $row['total_posts'];
2027 }
2028 else if ($row['post_visibility'] == ITEM_DELETED)
2029 {
2030 $topic_data[$topic_id]['posts_softdeleted'] = $row['total_posts'];
2031 }
2032
2033 if ($row['post_visibility'] == ITEM_APPROVED)
2034 {
2035 $topic_data[$topic_id]['visibility'] = ITEM_APPROVED;
2036 $topic_data[$topic_id]['first_post_id'] = $row['first_post_id'];
2037 $topic_data[$topic_id]['last_post_id'] = $row['last_post_id'];
2038 }
2039 else if ($topic_data[$topic_id]['visibility'] != ITEM_APPROVED)
2040 {
2041 // If there is no approved post, we take the min/max of the other visibilities
2042 // for the last and first post info, because it is only visible to moderators anyway
2043 $topic_data[$topic_id]['first_post_id'] = (!empty($topic_data[$topic_id]['first_post_id'])) ? min($topic_data[$topic_id]['first_post_id'], $row['first_post_id']) : $row['first_post_id'];
2044 $topic_data[$topic_id]['last_post_id'] = max($topic_data[$topic_id]['last_post_id'], $row['last_post_id']);
2045
2046 if ($topic_data[$topic_id]['visibility'] == ITEM_UNAPPROVED || $topic_data[$topic_id]['visibility'] == ITEM_REAPPROVE)
2047 {
2048 // Soft delete status is stronger than unapproved.
2049 $topic_data[$topic_id]['visibility'] = $row['post_visibility'];
2050 }
2051 }
2052 }
2053 }
2054 $db->sql_freeresult($result);
2055
2056 foreach ($topic_data as $topic_id => $row)
2057 {
2058 $post_ids[] = $row['first_post_id'];
2059 if ($row['first_post_id'] != $row['last_post_id'])
2060 {
2061 $post_ids[] = $row['last_post_id'];
2062 }
2063 }
2064
2065 // Now we delete empty topics and orphan posts
2066 if (sizeof($delete_posts))
2067 {
2068 delete_posts('topic_id', array_keys($delete_posts), false);
2069 unset($delete_posts);
2070 }
2071
2072 if (!sizeof($topic_data))
2073 {
2074 // If we get there, topic ids were invalid or topics did not contain any posts
2075 delete_topics($where_type, $where_ids, true);
2076 return;
2077 }
2078
2079 if (sizeof($delete_topics))
2080 {
2081 $delete_topic_ids = array();
2082 foreach ($delete_topics as $topic_id => $void)
2083 {
2084 unset($topic_data[$topic_id]);
2085 $delete_topic_ids[] = $topic_id;
2086 }
2087
2088 delete_topics('topic_id', $delete_topic_ids, false);
2089 unset($delete_topics, $delete_topic_ids);
2090 }
2091
2092 $sql = 'SELECT p.post_id, p.topic_id, p.post_visibility, p.poster_id, p.post_subject, p.post_username, p.post_time, u.username, u.user_colour
2093 FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
2094 WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
2095 AND u.user_id = p.poster_id';
2096 $result = $db->sql_query($sql);
2097
2098 $post_ids = array();
2099 while ($row = $db->sql_fetchrow($result))
2100 {
2101 $topic_id = intval($row['topic_id']);
2102
2103 if ($row['post_id'] == $topic_data[$topic_id]['first_post_id'])
2104 {
2105 $topic_data[$topic_id]['time'] = $row['post_time'];
2106 $topic_data[$topic_id]['poster'] = $row['poster_id'];
2107 $topic_data[$topic_id]['first_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
2108 $topic_data[$topic_id]['first_poster_colour'] = $row['user_colour'];
2109 }
2110
2111 if ($row['post_id'] == $topic_data[$topic_id]['last_post_id'])
2112 {
2113 $topic_data[$topic_id]['last_poster_id'] = $row['poster_id'];
2114 $topic_data[$topic_id]['last_post_subject'] = $row['post_subject'];
2115 $topic_data[$topic_id]['last_post_time'] = $row['post_time'];
2116 $topic_data[$topic_id]['last_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
2117 $topic_data[$topic_id]['last_poster_colour'] = $row['user_colour'];
2118 }
2119 }
2120 $db->sql_freeresult($result);
2121
2122 // Make sure shadow topics do link to existing topics
2123 if (sizeof($moved_topics))
2124 {
2125 $delete_topics = array();
2126
2127 $sql = 'SELECT t1.topic_id, t1.topic_moved_id
2128 FROM ' . TOPICS_TABLE . ' t1
2129 LEFT JOIN ' . TOPICS_TABLE . ' t2 ON (t2.topic_id = t1.topic_moved_id)
2130 WHERE ' . $db->sql_in_set('t1.topic_id', $moved_topics) . '
2131 AND t2.topic_id IS NULL';
2132 $result = $db->sql_query($sql);
2133
2134 while ($row = $db->sql_fetchrow($result))
2135 {
2136 $delete_topics[] = $row['topic_id'];
2137 }
2138 $db->sql_freeresult($result);
2139
2140 if (sizeof($delete_topics))
2141 {
2142 delete_topics('topic_id', $delete_topics, false);
2143 }
2144 unset($delete_topics);
2145
2146 // Make sure shadow topics having no last post data being updated (this only rarely happens...)
2147 $sql = 'SELECT topic_id, topic_moved_id, topic_last_post_id, topic_first_post_id
2148 FROM ' . TOPICS_TABLE . '
2149 WHERE ' . $db->sql_in_set('topic_id', $moved_topics) . '
2150 AND topic_last_post_time = 0';
2151 $result = $db->sql_query($sql);
2152
2153 $shadow_topic_data = $post_ids = array();
2154 while ($row = $db->sql_fetchrow($result))
2155 {
2156 $shadow_topic_data[$row['topic_moved_id']] = $row;
2157 $post_ids[] = $row['topic_last_post_id'];
2158 $post_ids[] = $row['topic_first_post_id'];
2159 }
2160 $db->sql_freeresult($result);
2161
2162 $sync_shadow_topics = array();
2163 if (sizeof($post_ids))
2164 {
2165 $sql = 'SELECT p.post_id, p.topic_id, p.post_visibility, p.poster_id, p.post_subject, p.post_username, p.post_time, u.username, u.user_colour
2166 FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
2167 WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
2168 AND u.user_id = p.poster_id';
2169 $result = $db->sql_query($sql);
2170
2171 $post_ids = array();
2172 while ($row = $db->sql_fetchrow($result))
2173 {
2174 $topic_id = (int) $row['topic_id'];
2175
2176 // Ok, there should be a shadow topic. If there isn't, then there's something wrong with the db.
2177 // However, there's not much we can do about it.
2178 if (!empty($shadow_topic_data[$topic_id]))
2179 {
2180 if ($row['post_id'] == $shadow_topic_data[$topic_id]['topic_first_post_id'])
2181 {
2182 $orig_topic_id = $shadow_topic_data[$topic_id]['topic_id'];
2183
2184 if (!isset($sync_shadow_topics[$orig_topic_id]))
2185 {
2186 $sync_shadow_topics[$orig_topic_id] = array();
2187 }
2188
2189 $sync_shadow_topics[$orig_topic_id]['topic_time'] = $row['post_time'];
2190 $sync_shadow_topics[$orig_topic_id]['topic_poster'] = $row['poster_id'];
2191 $sync_shadow_topics[$orig_topic_id]['topic_first_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
2192 $sync_shadow_topics[$orig_topic_id]['topic_first_poster_colour'] = $row['user_colour'];
2193 }
2194
2195 if ($row['post_id'] == $shadow_topic_data[$topic_id]['topic_last_post_id'])
2196 {
2197 $orig_topic_id = $shadow_topic_data[$topic_id]['topic_id'];
2198
2199 if (!isset($sync_shadow_topics[$orig_topic_id]))
2200 {
2201 $sync_shadow_topics[$orig_topic_id] = array();
2202 }
2203
2204 $sync_shadow_topics[$orig_topic_id]['topic_last_poster_id'] = $row['poster_id'];
2205 $sync_shadow_topics[$orig_topic_id]['topic_last_post_subject'] = $row['post_subject'];
2206 $sync_shadow_topics[$orig_topic_id]['topic_last_post_time'] = $row['post_time'];
2207 $sync_shadow_topics[$orig_topic_id]['topic_last_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
2208 $sync_shadow_topics[$orig_topic_id]['topic_last_poster_colour'] = $row['user_colour'];
2209 }
2210 }
2211 }
2212 $db->sql_freeresult($result);
2213
2214 $shadow_topic_data = array();
2215
2216 // Update the information we collected
2217 if (sizeof($sync_shadow_topics))
2218 {
2219 foreach ($sync_shadow_topics as $sync_topic_id => $sql_ary)
2220 {
2221 $sql = 'UPDATE ' . TOPICS_TABLE . '
2222 SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
2223 WHERE topic_id = ' . $sync_topic_id;
2224 $db->sql_query($sql);
2225 }
2226 }
2227 }
2228
2229 unset($sync_shadow_topics, $shadow_topic_data);
2230 }
2231
2232 // These are fields that will be synchronised
2233 $fieldnames = array('time', 'visibility', 'posts_approved', 'posts_unapproved', 'posts_softdeleted', 'poster', 'first_post_id', 'first_poster_name', 'first_poster_colour', 'last_post_id', 'last_post_subject', 'last_post_time', 'last_poster_id', 'last_poster_name', 'last_poster_colour');
2234
2235 if ($sync_extra)
2236 {
2237 // This routine assumes that post_reported values are correct
2238 // if they are not, use sync('post_reported') first
2239 $sql = 'SELECT t.topic_id, p.post_id
2240 FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
2241 $where_sql_and p.topic_id = t.topic_id
2242 AND p.post_reported = 1
2243 GROUP BY t.topic_id, p.post_id";
2244 $result = $db->sql_query($sql);
2245
2246 $fieldnames[] = 'reported';
2247 while ($row = $db->sql_fetchrow($result))
2248 {
2249 $topic_data[intval($row['topic_id'])]['reported'] = 1;
2250 }
2251 $db->sql_freeresult($result);
2252
2253 // This routine assumes that post_attachment values are correct
2254 // if they are not, use sync('post_attachment') first
2255 $sql = 'SELECT t.topic_id, p.post_id
2256 FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
2257 $where_sql_and p.topic_id = t.topic_id
2258 AND p.post_attachment = 1
2259 GROUP BY t.topic_id, p.post_id";
2260 $result = $db->sql_query($sql);
2261
2262 $fieldnames[] = 'attachment';
2263 while ($row = $db->sql_fetchrow($result))
2264 {
2265 $topic_data[intval($row['topic_id'])]['attachment'] = 1;
2266 }
2267 $db->sql_freeresult($result);
2268 }
2269
2270 foreach ($topic_data as $topic_id => $row)
2271 {
2272 $sql_ary = array();
2273
2274 foreach ($fieldnames as $fieldname)
2275 {
2276 if (isset($row[$fieldname]) && isset($row['topic_' . $fieldname]) && $row['topic_' . $fieldname] != $row[$fieldname])
2277 {
2278 $sql_ary['topic_' . $fieldname] = $row[$fieldname];
2279 }
2280 }
2281
2282 if (sizeof($sql_ary))
2283 {
2284 $sql = 'UPDATE ' . TOPICS_TABLE . '
2285 SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
2286 WHERE topic_id = ' . $topic_id;
2287 $db->sql_query($sql);
2288
2289 $resync_forums[$row['forum_id']] = $row['forum_id'];
2290 }
2291 }
2292 unset($topic_data);
2293
2294 $db->sql_transaction('commit');
2295
2296 // if some topics have been resync'ed then resync parent forums
2297 // except when we're only syncing a range, we don't want to sync forums during
2298 // batch processing.
2299 if ($resync_parents && sizeof($resync_forums) && $where_type != 'range')
2300 {
2301 sync('forum', 'forum_id', array_values($resync_forums), true, true);
2302 }
2303 break;
2304 }
2305
2306 return;
2307 }
2308
2309 /**
2310 * Prune function
2311 */
2312 function prune($forum_id, $prune_mode, $prune_date, $prune_flags = 0, $auto_sync = true)
2313 {
2314 global $db;
2315
2316 if (!is_array($forum_id))
2317 {
2318 $forum_id = array($forum_id);
2319 }
2320
2321 if (!sizeof($forum_id))
2322 {
2323 return;
2324 }
2325
2326 $sql_and = '';
2327
2328 if (!($prune_flags & FORUM_FLAG_PRUNE_ANNOUNCE))
2329 {
2330 $sql_and .= ' AND topic_type <> ' . POST_ANNOUNCE;
2331 $sql_and .= ' AND topic_type <> ' . POST_GLOBAL;
2332 }
2333
2334 if (!($prune_flags & FORUM_FLAG_PRUNE_STICKY))
2335 {
2336 $sql_and .= ' AND topic_type <> ' . POST_STICKY;
2337 }
2338
2339 if ($prune_mode == 'posted')
2340 {
2341 $sql_and .= " AND topic_last_post_time < $prune_date";
2342 }
2343
2344 if ($prune_mode == 'viewed')
2345 {
2346 $sql_and .= " AND topic_last_view_time < $prune_date";
2347 }
2348
2349 if ($prune_mode == 'shadow')
2350 {
2351 $sql_and .= ' AND topic_status = ' . ITEM_MOVED . " AND topic_last_post_time < $prune_date";
2352 }
2353
2354 $sql = 'SELECT topic_id
2355 FROM ' . TOPICS_TABLE . '
2356 WHERE ' . $db->sql_in_set('forum_id', $forum_id) . "
2357 AND poll_start = 0
2358 $sql_and";
2359 $result = $db->sql_query($sql);
2360
2361 $topic_list = array();
2362 while ($row = $db->sql_fetchrow($result))
2363 {
2364 $topic_list[] = $row['topic_id'];
2365 }
2366 $db->sql_freeresult($result);
2367
2368 if ($prune_flags & FORUM_FLAG_PRUNE_POLL)
2369 {
2370 $sql = 'SELECT topic_id
2371 FROM ' . TOPICS_TABLE . '
2372 WHERE ' . $db->sql_in_set('forum_id', $forum_id) . "
2373 AND poll_start > 0
2374 AND poll_last_vote < $prune_date
2375 $sql_and";
2376 $result = $db->sql_query($sql);
2377
2378 while ($row = $db->sql_fetchrow($result))
2379 {
2380 $topic_list[] = $row['topic_id'];
2381 }
2382 $db->sql_freeresult($result);
2383
2384 $topic_list = array_unique($topic_list);
2385 }
2386
2387 return delete_topics('topic_id', $topic_list, $auto_sync, false);
2388 }
2389
2390 /**
2391 * Function auto_prune(), this function now relies on passed vars
2392 */
2393 function auto_prune($forum_id, $prune_mode, $prune_flags, $prune_days, $prune_freq)
2394 {
2395 global $db;
2396
2397 $sql = 'SELECT forum_name
2398 FROM ' . FORUMS_TABLE . "
2399 WHERE forum_id = $forum_id";
2400 $result = $db->sql_query($sql, 3600);
2401 $row = $db->sql_fetchrow($result);
2402 $db->sql_freeresult($result);
2403
2404 if ($row)
2405 {
2406 $prune_date = time() - ($prune_days * 86400);
2407 $next_prune = time() + ($prune_freq * 86400);
2408
2409 prune($forum_id, $prune_mode, $prune_date, $prune_flags, true);
2410
2411 $sql = 'UPDATE ' . FORUMS_TABLE . "
2412 SET prune_next = $next_prune
2413 WHERE forum_id = $forum_id";
2414 $db->sql_query($sql);
2415
2416 add_log('admin', 'LOG_AUTO_PRUNE', $row['forum_name']);
2417 }
2418
2419 return;
2420 }
2421
2422 /**
2423 * Cache moderators. Called whenever permissions are changed
2424 * via admin_permissions. Changes of usernames and group names
2425 * must be carried through for the moderators table.
2426 *
2427 * @param \phpbb\db\driver\driver_interface $db Database connection
2428 * @param \phpbb\cache\driver\driver_interface Cache driver
2429 * @param \phpbb\auth\auth $auth Authentication object
2430 * @return null
2431 */
2432 function phpbb_cache_moderators($db, $cache, $auth)
2433 {
2434 // Remove cached sql results
2435 $cache->destroy('sql', MODERATOR_CACHE_TABLE);
2436
2437 // Clear table
2438 switch ($db->get_sql_layer())
2439 {
2440 case 'sqlite':
2441 case 'sqlite3':
2442 $db->sql_query('DELETE FROM ' . MODERATOR_CACHE_TABLE);
2443 break;
2444
2445 default:
2446 $db->sql_query('TRUNCATE TABLE ' . MODERATOR_CACHE_TABLE);
2447 break;
2448 }
2449
2450 // We add moderators who have forum moderator permissions without an explicit ACL_NEVER setting
2451 $hold_ary = $ug_id_ary = $sql_ary = array();
2452
2453 // Grab all users having moderative options...
2454 $hold_ary = $auth->acl_user_raw_data(false, 'm_%', false);
2455
2456 // Add users?
2457 if (sizeof($hold_ary))
2458 {
2459 // At least one moderative option warrants a display
2460 $ug_id_ary = array_keys($hold_ary);
2461
2462 // Remove users who have group memberships with DENY moderator permissions
2463 $sql_ary_deny = array(
2464 'SELECT' => 'a.forum_id, ug.user_id, g.group_id',
2465
2466 'FROM' => array(
2467 ACL_OPTIONS_TABLE => 'o',
2468 USER_GROUP_TABLE => 'ug',
2469 GROUPS_TABLE => 'g',
2470 ACL_GROUPS_TABLE => 'a',
2471 ),
2472
2473 'LEFT_JOIN' => array(
2474 array(
2475 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'),
2476 'ON' => 'a.auth_role_id = r.role_id',
2477 ),
2478 ),
2479
2480 'WHERE' => '(o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id)
2481 AND ((a.auth_setting = ' . ACL_NEVER . ' AND r.auth_setting IS NULL)
2482 OR r.auth_setting = ' . ACL_NEVER . ')
2483 AND a.group_id = ug.group_id
2484 AND g.group_id = ug.group_id
2485 AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
2486 AND ' . $db->sql_in_set('ug.user_id', $ug_id_ary) . "
2487 AND ug.user_pending = 0
2488 AND o.auth_option " . $db->sql_like_expression('m_' . $db->get_any_char()),
2489 );
2490 $sql = $db->sql_build_query('SELECT', $sql_ary_deny);
2491 $result = $db->sql_query($sql);
2492
2493 while ($row = $db->sql_fetchrow($result))
2494 {
2495 if (isset($hold_ary[$row['user_id']][$row['forum_id']]))
2496 {
2497 unset($hold_ary[$row['user_id']][$row['forum_id']]);
2498 }
2499 }
2500 $db->sql_freeresult($result);
2501
2502 if (sizeof($hold_ary))
2503 {
2504 // Get usernames...
2505 $sql = 'SELECT user_id, username
2506 FROM ' . USERS_TABLE . '
2507 WHERE ' . $db->sql_in_set('user_id', array_keys($hold_ary));
2508 $result = $db->sql_query($sql);
2509
2510 $usernames_ary = array();
2511 while ($row = $db->sql_fetchrow($result))
2512 {
2513 $usernames_ary[$row['user_id']] = $row['username'];
2514 }
2515
2516 foreach ($hold_ary as $user_id => $forum_id_ary)
2517 {
2518 // Do not continue if user does not exist
2519 if (!isset($usernames_ary[$user_id]))
2520 {
2521 continue;
2522 }
2523
2524 foreach ($forum_id_ary as $forum_id => $auth_ary)
2525 {
2526 $sql_ary[] = array(
2527 'forum_id' => (int) $forum_id,
2528 'user_id' => (int) $user_id,
2529 'username' => (string) $usernames_ary[$user_id],
2530 'group_id' => 0,
2531 'group_name' => ''
2532 );
2533 }
2534 }
2535 }
2536 }
2537
2538 // Now to the groups...
2539 $hold_ary = $auth->acl_group_raw_data(false, 'm_%', false);
2540
2541 if (sizeof($hold_ary))
2542 {
2543 $ug_id_ary = array_keys($hold_ary);
2544
2545 // Make sure not hidden or special groups are involved...
2546 $sql = 'SELECT group_name, group_id, group_type
2547 FROM ' . GROUPS_TABLE . '
2548 WHERE ' . $db->sql_in_set('group_id', $ug_id_ary);
2549 $result = $db->sql_query($sql);
2550
2551 $groupnames_ary = array();
2552 while ($row = $db->sql_fetchrow($result))
2553 {
2554 if ($row['group_type'] == GROUP_HIDDEN || $row['group_type'] == GROUP_SPECIAL)
2555 {
2556 unset($hold_ary[$row['group_id']]);
2557 }
2558
2559 $groupnames_ary[$row['group_id']] = $row['group_name'];
2560 }
2561 $db->sql_freeresult($result);
2562
2563 foreach ($hold_ary as $group_id => $forum_id_ary)
2564 {
2565 // If there is no group, we do not assign it...
2566 if (!isset($groupnames_ary[$group_id]))
2567 {
2568 continue;
2569 }
2570
2571 foreach ($forum_id_ary as $forum_id => $auth_ary)
2572 {
2573 $flag = false;
2574 foreach ($auth_ary as $auth_option => $setting)
2575 {
2576 // Make sure at least one ACL_YES option is set...
2577 if ($setting == ACL_YES)
2578 {
2579 $flag = true;
2580 break;
2581 }
2582 }
2583
2584 if (!$flag)
2585 {
2586 continue;
2587 }
2588
2589 $sql_ary[] = array(
2590 'forum_id' => (int) $forum_id,
2591 'user_id' => 0,
2592 'username' => '',
2593 'group_id' => (int) $group_id,
2594 'group_name' => (string) $groupnames_ary[$group_id]
2595 );
2596 }
2597 }
2598 }
2599
2600 $db->sql_multi_insert(MODERATOR_CACHE_TABLE, $sql_ary);
2601 }
2602
2603 /**
2604 * View log
2605 *
2606 * @param string $mode The mode defines which log_type is used and from which log the entry is retrieved
2607 * @param array &$log The result array with the logs
2608 * @param mixed &$log_count If $log_count is set to false, we will skip counting all entries in the database.
2609 * Otherwise an integer with the number of total matching entries is returned.
2610 * @param int $limit Limit the number of entries that are returned
2611 * @param int $offset Offset when fetching the log entries, f.e. when paginating
2612 * @param mixed $forum_id Restrict the log entries to the given forum_id (can also be an array of forum_ids)
2613 * @param int $topic_id Restrict the log entries to the given topic_id
2614 * @param int $user_id Restrict the log entries to the given user_id
2615 * @param int $log_time Only get log entries newer than the given timestamp
2616 * @param string $sort_by SQL order option, e.g. 'l.log_time DESC'
2617 * @param string $keywords Will only return log entries that have the keywords in log_operation or log_data
2618 *
2619 * @return int Returns the offset of the last valid page, if the specified offset was invalid (too high)
2620 */
2621 function view_log($mode, &$log, &$log_count, $limit = 0, $offset = 0, $forum_id = 0, $topic_id = 0, $user_id = 0, $limit_days = 0, $sort_by = 'l.log_time DESC', $keywords = '')
2622 {
2623 global $phpbb_log;
2624
2625 $count_logs = ($log_count !== false);
2626
2627 $log = $phpbb_log->get_logs($mode, $count_logs, $limit, $offset, $forum_id, $topic_id, $user_id, $limit_days, $sort_by, $keywords);
2628 $log_count = $phpbb_log->get_log_count();
2629
2630 return $phpbb_log->get_valid_offset();
2631 }
2632
2633 /**
2634 * Removes moderators and administrators from foe lists.
2635 *
2636 * @param \phpbb\db\driver\driver_interface $db Database connection
2637 * @param \phpbb\auth\auth $auth Authentication object
2638 * @param array|bool $group_id If an array, remove all members of this group from foe lists, or false to ignore
2639 * @param array|bool $user_id If an array, remove this user from foe lists, or false to ignore
2640 * @return null
2641 */
2642 function phpbb_update_foes($db, $auth, $group_id = false, $user_id = false)
2643 {
2644 // update foes for some user
2645 if (is_array($user_id) && sizeof($user_id))
2646 {
2647 $sql = 'DELETE FROM ' . ZEBRA_TABLE . '
2648 WHERE ' . $db->sql_in_set('zebra_id', $user_id) . '
2649 AND foe = 1';
2650 $db->sql_query($sql);
2651 return;
2652 }
2653
2654 // update foes for some group
2655 if (is_array($group_id) && sizeof($group_id))
2656 {
2657 // Grab group settings...
2658 $sql_ary = array(
2659 'SELECT' => 'a.group_id',
2660
2661 'FROM' => array(
2662 ACL_OPTIONS_TABLE => 'ao',
2663 ACL_GROUPS_TABLE => 'a',
2664 ),
2665
2666 'LEFT_JOIN' => array(
2667 array(
2668 'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'),
2669 'ON' => 'a.auth_role_id = r.role_id',
2670 ),
2671 ),
2672
2673 'WHERE' => '(ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id)
2674 AND ' . $db->sql_in_set('a.group_id', $group_id) . "
2675 AND ao.auth_option IN ('a_', 'm_')",
2676
2677 'GROUP_BY' => 'a.group_id',
2678 );
2679 $sql = $db->sql_build_query('SELECT', $sql_ary);
2680 $result = $db->sql_query($sql);
2681
2682 $groups = array();
2683 while ($row = $db->sql_fetchrow($result))
2684 {
2685 $groups[] = (int) $row['group_id'];
2686 }
2687 $db->sql_freeresult($result);
2688
2689 if (!sizeof($groups))
2690 {
2691 return;
2692 }
2693
2694 switch ($db->get_sql_layer())
2695 {
2696 case 'mysqli':
2697 case 'mysql4':
2698 $sql = 'DELETE ' . (($db->get_sql_layer() === 'mysqli' || version_compare($db->sql_server_info(true), '4.1', '>=')) ? 'z.*' : ZEBRA_TABLE) . '
2699 FROM ' . ZEBRA_TABLE . ' z, ' . USER_GROUP_TABLE . ' ug
2700 WHERE z.zebra_id = ug.user_id
2701 AND z.foe = 1
2702 AND ' . $db->sql_in_set('ug.group_id', $groups);
2703 $db->sql_query($sql);
2704 break;
2705
2706 default:
2707 $sql = 'SELECT user_id
2708 FROM ' . USER_GROUP_TABLE . '
2709 WHERE ' . $db->sql_in_set('group_id', $groups);
2710 $result = $db->sql_query($sql);
2711
2712 $users = array();
2713 while ($row = $db->sql_fetchrow($result))
2714 {
2715 $users[] = (int) $row['user_id'];
2716 }
2717 $db->sql_freeresult($result);
2718
2719 if (sizeof($users))
2720 {
2721 $sql = 'DELETE FROM ' . ZEBRA_TABLE . '
2722 WHERE ' . $db->sql_in_set('zebra_id', $users) . '
2723 AND foe = 1';
2724 $db->sql_query($sql);
2725 }
2726 break;
2727 }
2728
2729 return;
2730 }
2731
2732 // update foes for everyone
2733 $perms = array();
2734 foreach ($auth->acl_get_list(false, array('a_', 'm_'), false) as $forum_id => $forum_ary)
2735 {
2736 foreach ($forum_ary as $auth_option => $user_ary)
2737 {
2738 $perms = array_merge($perms, $user_ary);
2739 }
2740 }
2741
2742 if (sizeof($perms))
2743 {
2744 $sql = 'DELETE FROM ' . ZEBRA_TABLE . '
2745 WHERE ' . $db->sql_in_set('zebra_id', array_unique($perms)) . '
2746 AND foe = 1';
2747 $db->sql_query($sql);
2748 }
2749 unset($perms);
2750 }
2751
2752 /**
2753 * Lists inactive users
2754 */
2755 function view_inactive_users(&$users, &$user_count, $limit = 0, $offset = 0, $limit_days = 0, $sort_by = 'user_inactive_time DESC')
2756 {
2757 global $db, $user;
2758
2759 $sql = 'SELECT COUNT(user_id) AS user_count
2760 FROM ' . USERS_TABLE . '
2761 WHERE user_type = ' . USER_INACTIVE .
2762 (($limit_days) ? " AND user_inactive_time >= $limit_days" : '');
2763 $result = $db->sql_query($sql);
2764 $user_count = (int) $db->sql_fetchfield('user_count');
2765 $db->sql_freeresult($result);
2766
2767 if ($user_count == 0)
2768 {
2769 // Save the queries, because there are no users to display
2770 return 0;
2771 }
2772
2773 if ($offset >= $user_count)
2774 {
2775 $offset = ($offset - $limit < 0) ? 0 : $offset - $limit;
2776 }
2777
2778 $sql = 'SELECT *
2779 FROM ' . USERS_TABLE . '
2780 WHERE user_type = ' . USER_INACTIVE .
2781 (($limit_days) ? " AND user_inactive_time >= $limit_days" : '') . "
2782 ORDER BY $sort_by";
2783 $result = $db->sql_query_limit($sql, $limit, $offset);
2784
2785 while ($row = $db->sql_fetchrow($result))
2786 {
2787 $row['inactive_reason'] = $user->lang['INACTIVE_REASON_UNKNOWN'];
2788 switch ($row['user_inactive_reason'])
2789 {
2790 case INACTIVE_REGISTER:
2791 $row['inactive_reason'] = $user->lang['INACTIVE_REASON_REGISTER'];
2792 break;
2793
2794 case INACTIVE_PROFILE:
2795 $row['inactive_reason'] = $user->lang['INACTIVE_REASON_PROFILE'];
2796 break;
2797
2798 case INACTIVE_MANUAL:
2799 $row['inactive_reason'] = $user->lang['INACTIVE_REASON_MANUAL'];
2800 break;
2801
2802 case INACTIVE_REMIND:
2803 $row['inactive_reason'] = $user->lang['INACTIVE_REASON_REMIND'];
2804 break;
2805 }
2806
2807 $users[] = $row;
2808 }
2809
2810 return $offset;
2811 }
2812
2813 /**
2814 * Lists warned users
2815 */
2816 function view_warned_users(&$users, &$user_count, $limit = 0, $offset = 0, $limit_days = 0, $sort_by = 'user_warnings DESC')
2817 {
2818 global $db;
2819
2820 $sql = 'SELECT user_id, username, user_colour, user_warnings, user_last_warning
2821 FROM ' . USERS_TABLE . '
2822 WHERE user_warnings > 0
2823 ' . (($limit_days) ? "AND user_last_warning >= $limit_days" : '') . "
2824 ORDER BY $sort_by";
2825 $result = $db->sql_query_limit($sql, $limit, $offset);
2826 $users = $db->sql_fetchrowset($result);
2827 $db->sql_freeresult($result);
2828
2829 $sql = 'SELECT count(user_id) AS user_count
2830 FROM ' . USERS_TABLE . '
2831 WHERE user_warnings > 0
2832 ' . (($limit_days) ? "AND user_last_warning >= $limit_days" : '');
2833 $result = $db->sql_query($sql);
2834 $user_count = (int) $db->sql_fetchfield('user_count');
2835 $db->sql_freeresult($result);
2836
2837 return;
2838 }
2839
2840 /**
2841 * Get database size
2842 * Currently only mysql and mssql are supported
2843 */
2844 function get_database_size()
2845 {
2846 global $db, $user, $table_prefix;
2847
2848 $database_size = false;
2849
2850 // This code is heavily influenced by a similar routine in phpMyAdmin 2.2.0
2851 switch ($db->get_sql_layer())
2852 {
2853 case 'mysql':
2854 case 'mysql4':
2855 case 'mysqli':
2856 $sql = 'SELECT VERSION() AS mysql_version';
2857 $result = $db->sql_query($sql);
2858 $row = $db->sql_fetchrow($result);
2859 $db->sql_freeresult($result);
2860
2861 if ($row)
2862 {
2863 $version = $row['mysql_version'];
2864
2865 if (preg_match('#(3\.23|[45]\.)#', $version))
2866 {
2867 $db_name = (preg_match('#^(?:3\.23\.(?:[6-9]|[1-9]{2}))|[45]\.#', $version)) ? "`{$db->get_db_name()}`" : $db->get_db_name();
2868
2869 $sql = 'SHOW TABLE STATUS
2870 FROM ' . $db_name;
2871 $result = $db->sql_query($sql, 7200);
2872
2873 $database_size = 0;
2874 while ($row = $db->sql_fetchrow($result))
2875 {
2876 if ((isset($row['Type']) && $row['Type'] != 'MRG_MyISAM') || (isset($row['Engine']) && ($row['Engine'] == 'MyISAM' || $row['Engine'] == 'InnoDB')))
2877 {
2878 if ($table_prefix != '')
2879 {
2880 if (strpos($row['Name'], $table_prefix) !== false)
2881 {
2882 $database_size += $row['Data_length'] + $row['Index_length'];
2883 }
2884 }
2885 else
2886 {
2887 $database_size += $row['Data_length'] + $row['Index_length'];
2888 }
2889 }
2890 }
2891 $db->sql_freeresult($result);
2892 }
2893 }
2894 break;
2895
2896 case 'sqlite':
2897 case 'sqlite3':
2898 global $dbhost;
2899
2900 if (file_exists($dbhost))
2901 {
2902 $database_size = filesize($dbhost);
2903 }
2904
2905 break;
2906
2907 case 'mssql':
2908 case 'mssql_odbc':
2909 case 'mssqlnative':
2910 $sql = 'SELECT @@VERSION AS mssql_version';
2911 $result = $db->sql_query($sql);
2912 $row = $db->sql_fetchrow($result);
2913 $db->sql_freeresult($result);
2914
2915 $sql = 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize
2916 FROM sysfiles';
2917
2918 if ($row)
2919 {
2920 // Azure stats are stored elsewhere
2921 if (strpos($row['mssql_version'], 'SQL Azure') !== false)
2922 {
2923 $sql = 'SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize
2924 FROM sys.dm_db_partition_stats';
2925 }
2926 }
2927
2928 $result = $db->sql_query($sql, 7200);
2929 $database_size = ($row = $db->sql_fetchrow($result)) ? $row['dbsize'] : false;
2930 $db->sql_freeresult($result);
2931 break;
2932
2933 case 'postgres':
2934 $sql = "SELECT proname
2935 FROM pg_proc
2936 WHERE proname = 'pg_database_size'";
2937 $result = $db->sql_query($sql);
2938 $row = $db->sql_fetchrow($result);
2939 $db->sql_freeresult($result);
2940
2941 if ($row['proname'] == 'pg_database_size')
2942 {
2943 $database = $db->get_db_name();
2944 if (strpos($database, '.') !== false)
2945 {
2946 list($database, ) = explode('.', $database);
2947 }
2948
2949 $sql = "SELECT oid
2950 FROM pg_database
2951 WHERE datname = '$database'";
2952 $result = $db->sql_query($sql);
2953 $row = $db->sql_fetchrow($result);
2954 $db->sql_freeresult($result);
2955
2956 $oid = $row['oid'];
2957
2958 $sql = 'SELECT pg_database_size(' . $oid . ') as size';
2959 $result = $db->sql_query($sql);
2960 $row = $db->sql_fetchrow($result);
2961 $db->sql_freeresult($result);
2962
2963 $database_size = $row['size'];
2964 }
2965 break;
2966
2967 case 'oracle':
2968 $sql = 'SELECT SUM(bytes) as dbsize
2969 FROM user_segments';
2970 $result = $db->sql_query($sql, 7200);
2971 $database_size = ($row = $db->sql_fetchrow($result)) ? $row['dbsize'] : false;
2972 $db->sql_freeresult($result);
2973 break;
2974 }
2975
2976 $database_size = ($database_size !== false) ? get_formatted_filesize($database_size) : $user->lang['NOT_AVAILABLE'];
2977
2978 return $database_size;
2979 }
2980
2981 /**
2982 * Retrieve contents from remotely stored file
2983 */
2984 function get_remote_file($host, $directory, $filename, &$errstr, &$errno, $port = 80, $timeout = 6)
2985 {
2986 global $user;
2987
2988 if ($fsock = @fsockopen($host, $port, $errno, $errstr, $timeout))
2989 {
2990 @fputs($fsock, "GET $directory/$filename HTTP/1.0\r\n");
2991 @fputs($fsock, "HOST: $host\r\n");
2992 @fputs($fsock, "Connection: close\r\n\r\n");
2993
2994 $timer_stop = time() + $timeout;
2995 stream_set_timeout($fsock, $timeout);
2996
2997 $file_info = '';
2998 $get_info = false;
2999
3000 while (!@feof($fsock))
3001 {
3002 if ($get_info)
3003 {
3004 $file_info .= @fread($fsock, 1024);
3005 }
3006 else
3007 {
3008 $line = @fgets($fsock, 1024);
3009 if ($line == "\r\n")
3010 {
3011 $get_info = true;
3012 }
3013 else if (stripos($line, '404 not found') !== false)
3014 {
3015 $errstr = $user->lang('FILE_NOT_FOUND', $filename);
3016 return false;
3017 }
3018 }
3019
3020 $stream_meta_data = stream_get_meta_data($fsock);
3021
3022 if (!empty($stream_meta_data['timed_out']) || time() >= $timer_stop)
3023 {
3024 $errstr = $user->lang['FSOCK_TIMEOUT'];
3025 return false;
3026 }
3027 }
3028 @fclose($fsock);
3029 }
3030 else
3031 {
3032 if ($errstr)
3033 {
3034 $errstr = utf8_convert_message($errstr);
3035 return false;
3036 }
3037 else
3038 {
3039 $errstr = $user->lang['FSOCK_DISABLED'];
3040 return false;
3041 }
3042 }
3043
3044 return $file_info;
3045 }
3046
3047 /*
3048 * Tidy Warnings
3049 * Remove all warnings which have now expired from the database
3050 * The duration of a warning can be defined by the administrator
3051 * This only removes the warning and reduces the associated count,
3052 * it does not remove the user note recording the contents of the warning
3053 */
3054 function tidy_warnings()
3055 {
3056 global $db, $config;
3057
3058 $expire_date = time() - ($config['warnings_expire_days'] * 86400);
3059 $warning_list = $user_list = array();
3060
3061 $sql = 'SELECT * FROM ' . WARNINGS_TABLE . "
3062 WHERE warning_time < $expire_date";
3063 $result = $db->sql_query($sql);
3064
3065 while ($row = $db->sql_fetchrow($result))
3066 {
3067 $warning_list[] = $row['warning_id'];
3068 $user_list[$row['user_id']] = isset($user_list[$row['user_id']]) ? ++$user_list[$row['user_id']] : 1;
3069 }
3070 $db->sql_freeresult($result);
3071
3072 if (sizeof($warning_list))
3073 {
3074 $db->sql_transaction('begin');
3075
3076 $sql = 'DELETE FROM ' . WARNINGS_TABLE . '
3077 WHERE ' . $db->sql_in_set('warning_id', $warning_list);
3078 $db->sql_query($sql);
3079
3080 foreach ($user_list as $user_id => $value)
3081 {
3082 $sql = 'UPDATE ' . USERS_TABLE . " SET user_warnings = user_warnings - $value
3083 WHERE user_id = $user_id";
3084 $db->sql_query($sql);
3085 }
3086
3087 $db->sql_transaction('commit');
3088 }
3089
3090 set_config('warnings_last_gc', time(), true);
3091 }
3092
3093 /**
3094 * Tidy database, doing some maintanance tasks
3095 */
3096 function tidy_database()
3097 {
3098 global $db;
3099
3100 // Here we check permission consistency
3101
3102 // Sometimes, it can happen permission tables having forums listed which do not exist
3103 $sql = 'SELECT forum_id
3104 FROM ' . FORUMS_TABLE;
3105 $result = $db->sql_query($sql);
3106
3107 $forum_ids = array(0);
3108 while ($row = $db->sql_fetchrow($result))
3109 {
3110 $forum_ids[] = $row['forum_id'];
3111 }
3112 $db->sql_freeresult($result);
3113
3114 // Delete those rows from the acl tables not having listed the forums above
3115 $sql = 'DELETE FROM ' . ACL_GROUPS_TABLE . '
3116 WHERE ' . $db->sql_in_set('forum_id', $forum_ids, true);
3117 $db->sql_query($sql);
3118
3119 $sql = 'DELETE FROM ' . ACL_USERS_TABLE . '
3120 WHERE ' . $db->sql_in_set('forum_id', $forum_ids, true);
3121 $db->sql_query($sql);
3122
3123 set_config('database_last_gc', time(), true);
3124 }
3125
3126 /**
3127 * Add permission language - this will make sure custom files will be included
3128 */
3129 function add_permission_language()
3130 {
3131 global $user, $phpEx, $phpbb_extension_manager;
3132
3133 // add permission language files from extensions
3134 $finder = $phpbb_extension_manager->get_finder();
3135
3136 $lang_files = $finder
3137 ->prefix('permissions_')
3138 ->suffix(".$phpEx")
3139 ->core_path('language/' . $user->lang_name . '/')
3140 ->extension_directory('/language/' . $user->lang_name)
3141 ->find();
3142
3143 foreach ($lang_files as $lang_file => $ext_name)
3144 {
3145 if ($ext_name === '/')
3146 {
3147 $user->add_lang($lang_file);
3148 }
3149 else
3150 {
3151 $user->add_lang_ext($ext_name, $lang_file);
3152 }
3153 }
3154 }
3155
3156 /**
3157 * Enables a particular flag in a bitfield column of a given table.
3158 *
3159 * @param string $table_name The table to update
3160 * @param string $column_name The column containing a bitfield to update
3161 * @param int $flag The binary flag which is OR-ed with the current column value
3162 * @param string $sql_more This string is attached to the sql query generated to update the table.
3163 *
3164 * @return null
3165 */
3166 function enable_bitfield_column_flag($table_name, $column_name, $flag, $sql_more = '')
3167 {
3168 global $db;
3169
3170 $sql = 'UPDATE ' . $table_name . '
3171 SET ' . $column_name . ' = ' . $db->sql_bit_or($column_name, $flag) . '
3172 ' . $sql_more;
3173 $db->sql_query($sql);
3174 }
3175