* sync('topic', 'topic_id', 123); // resync topic #123
* sync('topic', 'forum_id', array(2, 3)); // resync topics from forum #2 and #3
* sync('topic'); // resync all topics
* sync('topic', 'range', 'topic_id BETWEEN 1 AND 60'); // resync a range of topics/forums (only available for 'topic' and 'forum' modes)
*
*
* Modes:
* - forum Resync complete forum
* - topic Resync topics
* - topic_moved Removes topic shadows that would be in the same forum as the topic they link to
* - topic_visibility Resyncs the topic_visibility flag according to the status of the first post
* - post_reported Resyncs the post_reported flag, relying on actual reports
* - topic_reported Resyncs the topic_reported flag, relying on post_reported flags
* - post_attachement Same as post_reported, but with attachment flags
* - topic_attachement Same as topic_reported, but with attachment flags
*/
function sync($mode, $where_type = '', $where_ids = '', $resync_parents = false, $sync_extra = false)
{
global $db;
if (is_array($where_ids))
{
$where_ids = array_unique($where_ids);
$where_ids = array_map('intval', $where_ids);
}
else if ($where_type != 'range')
{
$where_ids = ($where_ids) ? array((int) $where_ids) : array();
}
if ($mode == 'forum' || $mode == 'topic' || $mode == 'topic_visibility' || $mode == 'topic_reported' || $mode == 'post_reported')
{
if (!$where_type)
{
$where_sql = '';
$where_sql_and = 'WHERE';
}
else if ($where_type == 'range')
{
// Only check a range of topics/forums. For instance: 'topic_id BETWEEN 1 AND 60'
$where_sql = 'WHERE (' . $mode[0] . ".$where_ids)";
$where_sql_and = $where_sql . "\n\tAND";
}
else
{
// Do not sync the "global forum"
$where_ids = array_diff($where_ids, array(0));
if (!sizeof($where_ids))
{
// Empty array with IDs. This means that we don't have any work to do. Just return.
return;
}
// Limit the topics/forums we are syncing, use specific topic/forum IDs.
// $where_type contains the field for the where clause (forum_id, topic_id)
$where_sql = 'WHERE ' . $db->sql_in_set($mode[0] . '.' . $where_type, $where_ids);
$where_sql_and = $where_sql . "\n\tAND";
}
}
else
{
if (!sizeof($where_ids))
{
return;
}
// $where_type contains the field for the where clause (forum_id, topic_id)
$where_sql = 'WHERE ' . $db->sql_in_set($mode[0] . '.' . $where_type, $where_ids);
$where_sql_and = $where_sql . "\n\tAND";
}
switch ($mode)
{
case 'topic_moved':
$db->sql_transaction('begin');
switch ($db->get_sql_layer())
{
case 'mysql4':
case 'mysqli':
$sql = 'DELETE FROM ' . TOPICS_TABLE . '
USING ' . TOPICS_TABLE . ' t1, ' . TOPICS_TABLE . " t2
WHERE t1.topic_moved_id = t2.topic_id
AND t1.forum_id = t2.forum_id";
$db->sql_query($sql);
break;
default:
$sql = 'SELECT t1.topic_id
FROM ' .TOPICS_TABLE . ' t1, ' . TOPICS_TABLE . " t2
WHERE t1.topic_moved_id = t2.topic_id
AND t1.forum_id = t2.forum_id";
$result = $db->sql_query($sql);
$topic_id_ary = array();
while ($row = $db->sql_fetchrow($result))
{
$topic_id_ary[] = $row['topic_id'];
}
$db->sql_freeresult($result);
if (!sizeof($topic_id_ary))
{
return;
}
$sql = 'DELETE FROM ' . TOPICS_TABLE . '
WHERE ' . $db->sql_in_set('topic_id', $topic_id_ary);
$db->sql_query($sql);
break;
}
$db->sql_transaction('commit');
break;
case 'topic_visibility':
$db->sql_transaction('begin');
$sql = 'SELECT t.topic_id, p.post_visibility
FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
$where_sql_and p.topic_id = t.topic_id
AND p.post_visibility = " . ITEM_APPROVED;
$result = $db->sql_query($sql);
$topics_approved = array();
while ($row = $db->sql_fetchrow($result))
{
$topics_approved[] = (int) $row['topic_id'];
}
$db->sql_freeresult($result);
$sql = 'SELECT t.topic_id, p.post_visibility
FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
$where_sql_and " . $db->sql_in_set('t.topic_id', $topics_approved, true, true) . '
AND p.topic_id = t.topic_id
AND p.post_visibility = ' . ITEM_DELETED;
$result = $db->sql_query($sql);
$topics_softdeleted = array();
while ($row = $db->sql_fetchrow($result))
{
$topics_softdeleted[] = (int) $row['topic_id'];
}
$db->sql_freeresult($result);
$topics_softdeleted = array_diff($topics_softdeleted, $topics_approved);
$topics_not_unapproved = array_merge($topics_softdeleted, $topics_approved);
$update_ary = array(
ITEM_UNAPPROVED => (!empty($topics_not_unapproved)) ? $where_sql_and . ' ' . $db->sql_in_set('topic_id', $topics_not_unapproved, true) : '',
ITEM_APPROVED => (!empty($topics_approved)) ? ' WHERE ' . $db->sql_in_set('topic_id', $topics_approved) : '',
ITEM_DELETED => (!empty($topics_softdeleted)) ? ' WHERE ' . $db->sql_in_set('topic_id', $topics_softdeleted) : '',
);
foreach ($update_ary as $visibility => $sql_where)
{
if ($sql_where)
{
$sql = 'UPDATE ' . TOPICS_TABLE . '
SET topic_visibility = ' . $visibility . '
' . $sql_where;
$db->sql_query($sql);
}
}
$db->sql_transaction('commit');
break;
case 'post_reported':
$post_ids = $post_reported = array();
$db->sql_transaction('begin');
$sql = 'SELECT p.post_id, p.post_reported
FROM ' . POSTS_TABLE . " p
$where_sql
GROUP BY p.post_id, p.post_reported";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$post_ids[$row['post_id']] = $row['post_id'];
if ($row['post_reported'])
{
$post_reported[$row['post_id']] = 1;
}
}
$db->sql_freeresult($result);
$sql = 'SELECT DISTINCT(post_id)
FROM ' . REPORTS_TABLE . '
WHERE ' . $db->sql_in_set('post_id', $post_ids) . '
AND report_closed = 0';
$result = $db->sql_query($sql);
$post_ids = array();
while ($row = $db->sql_fetchrow($result))
{
if (!isset($post_reported[$row['post_id']]))
{
$post_ids[] = $row['post_id'];
}
else
{
unset($post_reported[$row['post_id']]);
}
}
$db->sql_freeresult($result);
// $post_reported should be empty by now, if it's not it contains
// posts that are falsely flagged as reported
foreach ($post_reported as $post_id => $void)
{
$post_ids[] = $post_id;
}
if (sizeof($post_ids))
{
$sql = 'UPDATE ' . POSTS_TABLE . '
SET post_reported = 1 - post_reported
WHERE ' . $db->sql_in_set('post_id', $post_ids);
$db->sql_query($sql);
}
$db->sql_transaction('commit');
break;
case 'topic_reported':
if ($sync_extra)
{
sync('post_reported', $where_type, $where_ids);
}
$topic_ids = $topic_reported = array();
$db->sql_transaction('begin');
$sql = 'SELECT DISTINCT(t.topic_id)
FROM ' . POSTS_TABLE . " t
$where_sql_and t.post_reported = 1";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$topic_reported[$row['topic_id']] = 1;
}
$db->sql_freeresult($result);
$sql = 'SELECT t.topic_id, t.topic_reported
FROM ' . TOPICS_TABLE . " t
$where_sql";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
if ($row['topic_reported'] ^ isset($topic_reported[$row['topic_id']]))
{
$topic_ids[] = $row['topic_id'];
}
}
$db->sql_freeresult($result);
if (sizeof($topic_ids))
{
$sql = 'UPDATE ' . TOPICS_TABLE . '
SET topic_reported = 1 - topic_reported
WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
$db->sql_query($sql);
}
$db->sql_transaction('commit');
break;
case 'post_attachment':
$post_ids = $post_attachment = array();
$db->sql_transaction('begin');
$sql = 'SELECT p.post_id, p.post_attachment
FROM ' . POSTS_TABLE . " p
$where_sql
GROUP BY p.post_id, p.post_attachment";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$post_ids[$row['post_id']] = $row['post_id'];
if ($row['post_attachment'])
{
$post_attachment[$row['post_id']] = 1;
}
}
$db->sql_freeresult($result);
$sql = 'SELECT DISTINCT(post_msg_id)
FROM ' . ATTACHMENTS_TABLE . '
WHERE ' . $db->sql_in_set('post_msg_id', $post_ids) . '
AND in_message = 0';
$result = $db->sql_query($sql);
$post_ids = array();
while ($row = $db->sql_fetchrow($result))
{
if (!isset($post_attachment[$row['post_msg_id']]))
{
$post_ids[] = $row['post_msg_id'];
}
else
{
unset($post_attachment[$row['post_msg_id']]);
}
}
$db->sql_freeresult($result);
// $post_attachment should be empty by now, if it's not it contains
// posts that are falsely flagged as having attachments
foreach ($post_attachment as $post_id => $void)
{
$post_ids[] = $post_id;
}
if (sizeof($post_ids))
{
$sql = 'UPDATE ' . POSTS_TABLE . '
SET post_attachment = 1 - post_attachment
WHERE ' . $db->sql_in_set('post_id', $post_ids);
$db->sql_query($sql);
}
$db->sql_transaction('commit');
break;
case 'topic_attachment':
if ($sync_extra)
{
sync('post_attachment', $where_type, $where_ids);
}
$topic_ids = $topic_attachment = array();
$db->sql_transaction('begin');
$sql = 'SELECT DISTINCT(t.topic_id)
FROM ' . POSTS_TABLE . " t
$where_sql_and t.post_attachment = 1";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$topic_attachment[$row['topic_id']] = 1;
}
$db->sql_freeresult($result);
$sql = 'SELECT t.topic_id, t.topic_attachment
FROM ' . TOPICS_TABLE . " t
$where_sql";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
if ($row['topic_attachment'] ^ isset($topic_attachment[$row['topic_id']]))
{
$topic_ids[] = $row['topic_id'];
}
}
$db->sql_freeresult($result);
if (sizeof($topic_ids))
{
$sql = 'UPDATE ' . TOPICS_TABLE . '
SET topic_attachment = 1 - topic_attachment
WHERE ' . $db->sql_in_set('topic_id', $topic_ids);
$db->sql_query($sql);
}
$db->sql_transaction('commit');
break;
case 'forum':
$db->sql_transaction('begin');
// 1: Get the list of all forums
$sql = 'SELECT f.*
FROM ' . FORUMS_TABLE . " f
$where_sql";
$result = $db->sql_query($sql);
$forum_data = $forum_ids = $post_ids = $last_post_id = $post_info = array();
while ($row = $db->sql_fetchrow($result))
{
if ($row['forum_type'] == FORUM_LINK)
{
continue;
}
$forum_id = (int) $row['forum_id'];
$forum_ids[$forum_id] = $forum_id;
$forum_data[$forum_id] = $row;
if ($sync_extra)
{
$forum_data[$forum_id]['posts_approved'] = 0;
$forum_data[$forum_id]['posts_unapproved'] = 0;
$forum_data[$forum_id]['posts_softdeleted'] = 0;
$forum_data[$forum_id]['topics_approved'] = 0;
$forum_data[$forum_id]['topics_unapproved'] = 0;
$forum_data[$forum_id]['topics_softdeleted'] = 0;
}
$forum_data[$forum_id]['last_post_id'] = 0;
$forum_data[$forum_id]['last_post_subject'] = '';
$forum_data[$forum_id]['last_post_time'] = 0;
$forum_data[$forum_id]['last_poster_id'] = 0;
$forum_data[$forum_id]['last_poster_name'] = '';
$forum_data[$forum_id]['last_poster_colour'] = '';
}
$db->sql_freeresult($result);
if (!sizeof($forum_ids))
{
break;
}
$forum_ids = array_values($forum_ids);
// 2: Get topic counts for each forum (optional)
if ($sync_extra)
{
$sql = 'SELECT forum_id, topic_visibility, COUNT(topic_id) AS total_topics
FROM ' . TOPICS_TABLE . '
WHERE ' . $db->sql_in_set('forum_id', $forum_ids) . '
GROUP BY forum_id, topic_visibility';
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$forum_id = (int) $row['forum_id'];
if ($row['topic_visibility'] == ITEM_APPROVED)
{
$forum_data[$forum_id]['topics_approved'] = $row['total_topics'];
}
else if ($row['topic_visibility'] == ITEM_UNAPPROVED || $row['topic_visibility'] == ITEM_REAPPROVE)
{
$forum_data[$forum_id]['topics_unapproved'] = $row['total_topics'];
}
else if ($row['topic_visibility'] == ITEM_DELETED)
{
$forum_data[$forum_id]['topics_softdeleted'] = $row['total_topics'];
}
}
$db->sql_freeresult($result);
}
// 3: Get post count for each forum (optional)
if ($sync_extra)
{
if (sizeof($forum_ids) == 1)
{
$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
FROM ' . TOPICS_TABLE . ' t
WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
AND t.topic_status <> ' . ITEM_MOVED;
}
else
{
$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
FROM ' . TOPICS_TABLE . ' t
WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
AND t.topic_status <> ' . ITEM_MOVED . '
GROUP BY t.forum_id';
}
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$forum_id = (sizeof($forum_ids) == 1) ? (int) $forum_ids[0] : (int) $row['forum_id'];
$forum_data[$forum_id]['posts_approved'] = (int) $row['forum_posts_approved'];
$forum_data[$forum_id]['posts_unapproved'] = (int) $row['forum_posts_unapproved'];
$forum_data[$forum_id]['posts_softdeleted'] = (int) $row['forum_posts_softdeleted'];
}
$db->sql_freeresult($result);
}
// 4: Get last_post_id for each forum
if (sizeof($forum_ids) == 1)
{
$sql = 'SELECT MAX(t.topic_last_post_id) as last_post_id
FROM ' . TOPICS_TABLE . ' t
WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
AND t.topic_visibility = ' . ITEM_APPROVED;
}
else
{
$sql = 'SELECT t.forum_id, MAX(t.topic_last_post_id) as last_post_id
FROM ' . TOPICS_TABLE . ' t
WHERE ' . $db->sql_in_set('t.forum_id', $forum_ids) . '
AND t.topic_visibility = ' . ITEM_APPROVED . '
GROUP BY t.forum_id';
}
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$forum_id = (sizeof($forum_ids) == 1) ? (int) $forum_ids[0] : (int) $row['forum_id'];
$forum_data[$forum_id]['last_post_id'] = (int) $row['last_post_id'];
$post_ids[] = $row['last_post_id'];
}
$db->sql_freeresult($result);
// 5: Retrieve last_post infos
if (sizeof($post_ids))
{
$sql = 'SELECT p.post_id, p.poster_id, p.post_subject, p.post_time, p.post_username, u.username, u.user_colour
FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
AND p.poster_id = u.user_id';
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$post_info[$row['post_id']] = $row;
}
$db->sql_freeresult($result);
foreach ($forum_data as $forum_id => $data)
{
if ($data['last_post_id'])
{
if (isset($post_info[$data['last_post_id']]))
{
$forum_data[$forum_id]['last_post_subject'] = $post_info[$data['last_post_id']]['post_subject'];
$forum_data[$forum_id]['last_post_time'] = $post_info[$data['last_post_id']]['post_time'];
$forum_data[$forum_id]['last_poster_id'] = $post_info[$data['last_post_id']]['poster_id'];
$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'];
$forum_data[$forum_id]['last_poster_colour'] = $post_info[$data['last_post_id']]['user_colour'];
}
else
{
// For some reason we did not find the post in the db
$forum_data[$forum_id]['last_post_id'] = 0;
$forum_data[$forum_id]['last_post_subject'] = '';
$forum_data[$forum_id]['last_post_time'] = 0;
$forum_data[$forum_id]['last_poster_id'] = 0;
$forum_data[$forum_id]['last_poster_name'] = '';
$forum_data[$forum_id]['last_poster_colour'] = '';
}
}
}
unset($post_info);
}
// 6: Now do that thing
$fieldnames = array('last_post_id', 'last_post_subject', 'last_post_time', 'last_poster_id', 'last_poster_name', 'last_poster_colour');
if ($sync_extra)
{
array_push($fieldnames, 'posts_approved', 'posts_unapproved', 'posts_softdeleted', 'topics_approved', 'topics_unapproved', 'topics_softdeleted');
}
foreach ($forum_data as $forum_id => $row)
{
$sql_ary = array();
foreach ($fieldnames as $fieldname)
{
if ($row['forum_' . $fieldname] != $row[$fieldname])
{
if (preg_match('#(name|colour|subject)$#', $fieldname))
{
$sql_ary['forum_' . $fieldname] = (string) $row[$fieldname];
}
else
{
$sql_ary['forum_' . $fieldname] = (int) $row[$fieldname];
}
}
}
if (sizeof($sql_ary))
{
$sql = 'UPDATE ' . FORUMS_TABLE . '
SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
WHERE forum_id = ' . $forum_id;
$db->sql_query($sql);
}
}
$db->sql_transaction('commit');
break;
case 'topic':
$topic_data = $post_ids = $resync_forums = $delete_topics = $delete_posts = $moved_topics = array();
$db->sql_transaction('begin');
$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
FROM ' . TOPICS_TABLE . " t
$where_sql";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
if ($row['topic_moved_id'])
{
$moved_topics[] = $row['topic_id'];
continue;
}
$topic_id = (int) $row['topic_id'];
$topic_data[$topic_id] = $row;
$topic_data[$topic_id]['visibility'] = ITEM_UNAPPROVED;
$topic_data[$topic_id]['posts_approved'] = 0;
$topic_data[$topic_id]['posts_unapproved'] = 0;
$topic_data[$topic_id]['posts_softdeleted'] = 0;
$topic_data[$topic_id]['first_post_id'] = 0;
$topic_data[$topic_id]['last_post_id'] = 0;
unset($topic_data[$topic_id]['topic_id']);
// This array holds all topic_ids
$delete_topics[$topic_id] = '';
if ($sync_extra)
{
$topic_data[$topic_id]['reported'] = 0;
$topic_data[$topic_id]['attachment'] = 0;
}
}
$db->sql_freeresult($result);
// Use "t" as table alias because of the $where_sql clause
// NOTE: 't.post_visibility' in the GROUP BY is causing a major slowdown.
$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
FROM ' . POSTS_TABLE . " t
$where_sql
GROUP BY t.topic_id, t.post_visibility";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$topic_id = (int) $row['topic_id'];
$row['first_post_id'] = (int) $row['first_post_id'];
$row['last_post_id'] = (int) $row['last_post_id'];
if (!isset($topic_data[$topic_id]))
{
// Hey, these posts come from a topic that does not exist
$delete_posts[$topic_id] = '';
}
else
{
// Unset the corresponding entry in $delete_topics
// When we'll be done, only topics with no posts will remain
unset($delete_topics[$topic_id]);
if ($row['post_visibility'] == ITEM_APPROVED)
{
$topic_data[$topic_id]['posts_approved'] = $row['total_posts'];
}
else if ($row['post_visibility'] == ITEM_UNAPPROVED || $row['post_visibility'] == ITEM_REAPPROVE)
{
$topic_data[$topic_id]['posts_unapproved'] = $row['total_posts'];
}
else if ($row['post_visibility'] == ITEM_DELETED)
{
$topic_data[$topic_id]['posts_softdeleted'] = $row['total_posts'];
}
if ($row['post_visibility'] == ITEM_APPROVED)
{
$topic_data[$topic_id]['visibility'] = ITEM_APPROVED;
$topic_data[$topic_id]['first_post_id'] = $row['first_post_id'];
$topic_data[$topic_id]['last_post_id'] = $row['last_post_id'];
}
else if ($topic_data[$topic_id]['visibility'] != ITEM_APPROVED)
{
// If there is no approved post, we take the min/max of the other visibilities
// for the last and first post info, because it is only visible to moderators anyway
$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'];
$topic_data[$topic_id]['last_post_id'] = max($topic_data[$topic_id]['last_post_id'], $row['last_post_id']);
if ($topic_data[$topic_id]['visibility'] == ITEM_UNAPPROVED || $topic_data[$topic_id]['visibility'] == ITEM_REAPPROVE)
{
// Soft delete status is stronger than unapproved.
$topic_data[$topic_id]['visibility'] = $row['post_visibility'];
}
}
}
}
$db->sql_freeresult($result);
foreach ($topic_data as $topic_id => $row)
{
$post_ids[] = $row['first_post_id'];
if ($row['first_post_id'] != $row['last_post_id'])
{
$post_ids[] = $row['last_post_id'];
}
}
// Now we delete empty topics and orphan posts
if (sizeof($delete_posts))
{
delete_posts('topic_id', array_keys($delete_posts), false);
unset($delete_posts);
}
if (!sizeof($topic_data))
{
// If we get there, topic ids were invalid or topics did not contain any posts
delete_topics($where_type, $where_ids, true);
return;
}
if (sizeof($delete_topics))
{
$delete_topic_ids = array();
foreach ($delete_topics as $topic_id => $void)
{
unset($topic_data[$topic_id]);
$delete_topic_ids[] = $topic_id;
}
delete_topics('topic_id', $delete_topic_ids, false);
unset($delete_topics, $delete_topic_ids);
}
$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
FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
AND u.user_id = p.poster_id';
$result = $db->sql_query($sql);
$post_ids = array();
while ($row = $db->sql_fetchrow($result))
{
$topic_id = intval($row['topic_id']);
if ($row['post_id'] == $topic_data[$topic_id]['first_post_id'])
{
$topic_data[$topic_id]['time'] = $row['post_time'];
$topic_data[$topic_id]['poster'] = $row['poster_id'];
$topic_data[$topic_id]['first_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
$topic_data[$topic_id]['first_poster_colour'] = $row['user_colour'];
}
if ($row['post_id'] == $topic_data[$topic_id]['last_post_id'])
{
$topic_data[$topic_id]['last_poster_id'] = $row['poster_id'];
$topic_data[$topic_id]['last_post_subject'] = $row['post_subject'];
$topic_data[$topic_id]['last_post_time'] = $row['post_time'];
$topic_data[$topic_id]['last_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
$topic_data[$topic_id]['last_poster_colour'] = $row['user_colour'];
}
}
$db->sql_freeresult($result);
// Make sure shadow topics do link to existing topics
if (sizeof($moved_topics))
{
$delete_topics = array();
$sql = 'SELECT t1.topic_id, t1.topic_moved_id
FROM ' . TOPICS_TABLE . ' t1
LEFT JOIN ' . TOPICS_TABLE . ' t2 ON (t2.topic_id = t1.topic_moved_id)
WHERE ' . $db->sql_in_set('t1.topic_id', $moved_topics) . '
AND t2.topic_id IS NULL';
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$delete_topics[] = $row['topic_id'];
}
$db->sql_freeresult($result);
if (sizeof($delete_topics))
{
delete_topics('topic_id', $delete_topics, false);
}
unset($delete_topics);
// Make sure shadow topics having no last post data being updated (this only rarely happens...)
$sql = 'SELECT topic_id, topic_moved_id, topic_last_post_id, topic_first_post_id
FROM ' . TOPICS_TABLE . '
WHERE ' . $db->sql_in_set('topic_id', $moved_topics) . '
AND topic_last_post_time = 0';
$result = $db->sql_query($sql);
$shadow_topic_data = $post_ids = array();
while ($row = $db->sql_fetchrow($result))
{
$shadow_topic_data[$row['topic_moved_id']] = $row;
$post_ids[] = $row['topic_last_post_id'];
$post_ids[] = $row['topic_first_post_id'];
}
$db->sql_freeresult($result);
$sync_shadow_topics = array();
if (sizeof($post_ids))
{
$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
FROM ' . POSTS_TABLE . ' p, ' . USERS_TABLE . ' u
WHERE ' . $db->sql_in_set('p.post_id', $post_ids) . '
AND u.user_id = p.poster_id';
$result = $db->sql_query($sql);
$post_ids = array();
while ($row = $db->sql_fetchrow($result))
{
$topic_id = (int) $row['topic_id'];
// Ok, there should be a shadow topic. If there isn't, then there's something wrong with the db.
// However, there's not much we can do about it.
if (!empty($shadow_topic_data[$topic_id]))
{
if ($row['post_id'] == $shadow_topic_data[$topic_id]['topic_first_post_id'])
{
$orig_topic_id = $shadow_topic_data[$topic_id]['topic_id'];
if (!isset($sync_shadow_topics[$orig_topic_id]))
{
$sync_shadow_topics[$orig_topic_id] = array();
}
$sync_shadow_topics[$orig_topic_id]['topic_time'] = $row['post_time'];
$sync_shadow_topics[$orig_topic_id]['topic_poster'] = $row['poster_id'];
$sync_shadow_topics[$orig_topic_id]['topic_first_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
$sync_shadow_topics[$orig_topic_id]['topic_first_poster_colour'] = $row['user_colour'];
}
if ($row['post_id'] == $shadow_topic_data[$topic_id]['topic_last_post_id'])
{
$orig_topic_id = $shadow_topic_data[$topic_id]['topic_id'];
if (!isset($sync_shadow_topics[$orig_topic_id]))
{
$sync_shadow_topics[$orig_topic_id] = array();
}
$sync_shadow_topics[$orig_topic_id]['topic_last_poster_id'] = $row['poster_id'];
$sync_shadow_topics[$orig_topic_id]['topic_last_post_subject'] = $row['post_subject'];
$sync_shadow_topics[$orig_topic_id]['topic_last_post_time'] = $row['post_time'];
$sync_shadow_topics[$orig_topic_id]['topic_last_poster_name'] = ($row['poster_id'] == ANONYMOUS) ? $row['post_username'] : $row['username'];
$sync_shadow_topics[$orig_topic_id]['topic_last_poster_colour'] = $row['user_colour'];
}
}
}
$db->sql_freeresult($result);
$shadow_topic_data = array();
// Update the information we collected
if (sizeof($sync_shadow_topics))
{
foreach ($sync_shadow_topics as $sync_topic_id => $sql_ary)
{
$sql = 'UPDATE ' . TOPICS_TABLE . '
SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
WHERE topic_id = ' . $sync_topic_id;
$db->sql_query($sql);
}
}
}
unset($sync_shadow_topics, $shadow_topic_data);
}
// These are fields that will be synchronised
$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');
if ($sync_extra)
{
// This routine assumes that post_reported values are correct
// if they are not, use sync('post_reported') first
$sql = 'SELECT t.topic_id, p.post_id
FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
$where_sql_and p.topic_id = t.topic_id
AND p.post_reported = 1
GROUP BY t.topic_id, p.post_id";
$result = $db->sql_query($sql);
$fieldnames[] = 'reported';
while ($row = $db->sql_fetchrow($result))
{
$topic_data[intval($row['topic_id'])]['reported'] = 1;
}
$db->sql_freeresult($result);
// This routine assumes that post_attachment values are correct
// if they are not, use sync('post_attachment') first
$sql = 'SELECT t.topic_id, p.post_id
FROM ' . TOPICS_TABLE . ' t, ' . POSTS_TABLE . " p
$where_sql_and p.topic_id = t.topic_id
AND p.post_attachment = 1
GROUP BY t.topic_id, p.post_id";
$result = $db->sql_query($sql);
$fieldnames[] = 'attachment';
while ($row = $db->sql_fetchrow($result))
{
$topic_data[intval($row['topic_id'])]['attachment'] = 1;
}
$db->sql_freeresult($result);
}
foreach ($topic_data as $topic_id => $row)
{
$sql_ary = array();
foreach ($fieldnames as $fieldname)
{
if (isset($row[$fieldname]) && isset($row['topic_' . $fieldname]) && $row['topic_' . $fieldname] != $row[$fieldname])
{
$sql_ary['topic_' . $fieldname] = $row[$fieldname];
}
}
if (sizeof($sql_ary))
{
$sql = 'UPDATE ' . TOPICS_TABLE . '
SET ' . $db->sql_build_array('UPDATE', $sql_ary) . '
WHERE topic_id = ' . $topic_id;
$db->sql_query($sql);
$resync_forums[$row['forum_id']] = $row['forum_id'];
}
}
unset($topic_data);
$db->sql_transaction('commit');
// if some topics have been resync'ed then resync parent forums
// except when we're only syncing a range, we don't want to sync forums during
// batch processing.
if ($resync_parents && sizeof($resync_forums) && $where_type != 'range')
{
sync('forum', 'forum_id', array_values($resync_forums), true, true);
}
break;
}
return;
}
/**
* Prune function
*/
function prune($forum_id, $prune_mode, $prune_date, $prune_flags = 0, $auto_sync = true, $prune_limit = 0)
{
global $db, $phpbb_dispatcher;
if (!is_array($forum_id))
{
$forum_id = array($forum_id);
}
if (!sizeof($forum_id))
{
return;
}
$sql_and = '';
if (!($prune_flags & FORUM_FLAG_PRUNE_ANNOUNCE))
{
$sql_and .= ' AND topic_type <> ' . POST_ANNOUNCE;
$sql_and .= ' AND topic_type <> ' . POST_GLOBAL;
}
if (!($prune_flags & FORUM_FLAG_PRUNE_STICKY))
{
$sql_and .= ' AND topic_type <> ' . POST_STICKY;
}
if ($prune_mode == 'posted')
{
$sql_and .= " AND topic_last_post_time < $prune_date";
}
if ($prune_mode == 'viewed')
{
$sql_and .= " AND topic_last_view_time < $prune_date";
}
if ($prune_mode == 'shadow')
{
$sql_and .= ' AND topic_status = ' . ITEM_MOVED . " AND topic_last_post_time < $prune_date";
}
/**
* Use this event to modify the SQL that selects topics to be pruned
*
* @event core.prune_sql
* @var string forum_id The forum id
* @var string prune_mode The prune mode
* @var string prune_date The prune date
* @var int prune_flags The prune flags
* @var bool auto_sync Whether or not to perform auto sync
* @var string sql_and SQL text appended to where clause
* @var int prune_limit The prune limit
* @since 3.1.3-RC1
* @changed 3.1.10-RC1 Added prune_limit
*/
$vars = array(
'forum_id',
'prune_mode',
'prune_date',
'prune_flags',
'auto_sync',
'sql_and',
'prune_limit',
);
extract($phpbb_dispatcher->trigger_event('core.prune_sql', compact($vars)));
$sql = 'SELECT topic_id
FROM ' . TOPICS_TABLE . '
WHERE ' . $db->sql_in_set('forum_id', $forum_id) . "
AND poll_start = 0
$sql_and";
$result = $db->sql_query_limit($sql, $prune_limit);
$topic_list = array();
while ($row = $db->sql_fetchrow($result))
{
$topic_list[] = $row['topic_id'];
}
$db->sql_freeresult($result);
if ($prune_flags & FORUM_FLAG_PRUNE_POLL)
{
$sql = 'SELECT topic_id
FROM ' . TOPICS_TABLE . '
WHERE ' . $db->sql_in_set('forum_id', $forum_id) . "
AND poll_start > 0
AND poll_last_vote < $prune_date
$sql_and";
$result = $db->sql_query_limit($sql, $prune_limit);
while ($row = $db->sql_fetchrow($result))
{
$topic_list[] = $row['topic_id'];
}
$db->sql_freeresult($result);
$topic_list = array_unique($topic_list);
}
return delete_topics('topic_id', $topic_list, $auto_sync, false);
}
/**
* Function auto_prune(), this function now relies on passed vars
*/
function auto_prune($forum_id, $prune_mode, $prune_flags, $prune_days, $prune_freq)
{
global $db;
$sql = 'SELECT forum_name
FROM ' . FORUMS_TABLE . "
WHERE forum_id = $forum_id";
$result = $db->sql_query($sql, 3600);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
if ($row)
{
$prune_date = time() - ($prune_days * 86400);
$next_prune = time() + ($prune_freq * 86400);
$result = prune($forum_id, $prune_mode, $prune_date, $prune_flags, true, 300);
if ($result['topics'] == 0 && $result['posts'] == 0)
{
$sql = 'UPDATE ' . FORUMS_TABLE . "
SET prune_next = $next_prune
WHERE forum_id = $forum_id";
$db->sql_query($sql);
}
add_log('admin', 'LOG_AUTO_PRUNE', $row['forum_name']);
}
return;
}
/**
* Cache moderators. Called whenever permissions are changed
* via admin_permissions. Changes of usernames and group names
* must be carried through for the moderators table.
*
* @param \phpbb\db\driver\driver_interface $db Database connection
* @param \phpbb\cache\driver\driver_interface Cache driver
* @param \phpbb\auth\auth $auth Authentication object
* @return null
*/
function phpbb_cache_moderators($db, $cache, $auth)
{
// Remove cached sql results
$cache->destroy('sql', MODERATOR_CACHE_TABLE);
// Clear table
switch ($db->get_sql_layer())
{
case 'sqlite':
case 'sqlite3':
$db->sql_query('DELETE FROM ' . MODERATOR_CACHE_TABLE);
break;
default:
$db->sql_query('TRUNCATE TABLE ' . MODERATOR_CACHE_TABLE);
break;
}
// We add moderators who have forum moderator permissions without an explicit ACL_NEVER setting
$hold_ary = $ug_id_ary = $sql_ary = array();
// Grab all users having moderative options...
$hold_ary = $auth->acl_user_raw_data(false, 'm_%', false);
// Add users?
if (sizeof($hold_ary))
{
// At least one moderative option warrants a display
$ug_id_ary = array_keys($hold_ary);
// Remove users who have group memberships with DENY moderator permissions
$sql_ary_deny = array(
'SELECT' => 'a.forum_id, ug.user_id, g.group_id',
'FROM' => array(
ACL_OPTIONS_TABLE => 'o',
USER_GROUP_TABLE => 'ug',
GROUPS_TABLE => 'g',
ACL_GROUPS_TABLE => 'a',
),
'LEFT_JOIN' => array(
array(
'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'),
'ON' => 'a.auth_role_id = r.role_id',
),
),
'WHERE' => '(o.auth_option_id = a.auth_option_id OR o.auth_option_id = r.auth_option_id)
AND ((a.auth_setting = ' . ACL_NEVER . ' AND r.auth_setting IS NULL)
OR r.auth_setting = ' . ACL_NEVER . ')
AND a.group_id = ug.group_id
AND g.group_id = ug.group_id
AND NOT (ug.group_leader = 1 AND g.group_skip_auth = 1)
AND ' . $db->sql_in_set('ug.user_id', $ug_id_ary) . "
AND ug.user_pending = 0
AND o.auth_option " . $db->sql_like_expression('m_' . $db->get_any_char()),
);
$sql = $db->sql_build_query('SELECT', $sql_ary_deny);
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
if (isset($hold_ary[$row['user_id']][$row['forum_id']]))
{
unset($hold_ary[$row['user_id']][$row['forum_id']]);
}
}
$db->sql_freeresult($result);
if (sizeof($hold_ary))
{
// Get usernames...
$sql = 'SELECT user_id, username
FROM ' . USERS_TABLE . '
WHERE ' . $db->sql_in_set('user_id', array_keys($hold_ary));
$result = $db->sql_query($sql);
$usernames_ary = array();
while ($row = $db->sql_fetchrow($result))
{
$usernames_ary[$row['user_id']] = $row['username'];
}
$db->sql_freeresult($result);
foreach ($hold_ary as $user_id => $forum_id_ary)
{
// Do not continue if user does not exist
if (!isset($usernames_ary[$user_id]))
{
continue;
}
foreach ($forum_id_ary as $forum_id => $auth_ary)
{
$sql_ary[] = array(
'forum_id' => (int) $forum_id,
'user_id' => (int) $user_id,
'username' => (string) $usernames_ary[$user_id],
'group_id' => 0,
'group_name' => ''
);
}
}
}
}
// Now to the groups...
$hold_ary = $auth->acl_group_raw_data(false, 'm_%', false);
if (sizeof($hold_ary))
{
$ug_id_ary = array_keys($hold_ary);
// Make sure not hidden or special groups are involved...
$sql = 'SELECT group_name, group_id, group_type
FROM ' . GROUPS_TABLE . '
WHERE ' . $db->sql_in_set('group_id', $ug_id_ary);
$result = $db->sql_query($sql);
$groupnames_ary = array();
while ($row = $db->sql_fetchrow($result))
{
if ($row['group_type'] == GROUP_HIDDEN || $row['group_type'] == GROUP_SPECIAL)
{
unset($hold_ary[$row['group_id']]);
}
$groupnames_ary[$row['group_id']] = $row['group_name'];
}
$db->sql_freeresult($result);
foreach ($hold_ary as $group_id => $forum_id_ary)
{
// If there is no group, we do not assign it...
if (!isset($groupnames_ary[$group_id]))
{
continue;
}
foreach ($forum_id_ary as $forum_id => $auth_ary)
{
$flag = false;
foreach ($auth_ary as $auth_option => $setting)
{
// Make sure at least one ACL_YES option is set...
if ($setting == ACL_YES)
{
$flag = true;
break;
}
}
if (!$flag)
{
continue;
}
$sql_ary[] = array(
'forum_id' => (int) $forum_id,
'user_id' => 0,
'username' => '',
'group_id' => (int) $group_id,
'group_name' => (string) $groupnames_ary[$group_id]
);
}
}
}
$db->sql_multi_insert(MODERATOR_CACHE_TABLE, $sql_ary);
}
/**
* View log
*
* @param string $mode The mode defines which log_type is used and from which log the entry is retrieved
* @param array &$log The result array with the logs
* @param mixed &$log_count If $log_count is set to false, we will skip counting all entries in the database.
* Otherwise an integer with the number of total matching entries is returned.
* @param int $limit Limit the number of entries that are returned
* @param int $offset Offset when fetching the log entries, f.e. when paginating
* @param mixed $forum_id Restrict the log entries to the given forum_id (can also be an array of forum_ids)
* @param int $topic_id Restrict the log entries to the given topic_id
* @param int $user_id Restrict the log entries to the given user_id
* @param int $log_time Only get log entries newer than the given timestamp
* @param string $sort_by SQL order option, e.g. 'l.log_time DESC'
* @param string $keywords Will only return log entries that have the keywords in log_operation or log_data
*
* @return int Returns the offset of the last valid page, if the specified offset was invalid (too high)
*/
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 = '')
{
global $phpbb_log;
$count_logs = ($log_count !== false);
$log = $phpbb_log->get_logs($mode, $count_logs, $limit, $offset, $forum_id, $topic_id, $user_id, $limit_days, $sort_by, $keywords);
$log_count = $phpbb_log->get_log_count();
return $phpbb_log->get_valid_offset();
}
/**
* Removes moderators and administrators from foe lists.
*
* @param \phpbb\db\driver\driver_interface $db Database connection
* @param \phpbb\auth\auth $auth Authentication object
* @param array|bool $group_id If an array, remove all members of this group from foe lists, or false to ignore
* @param array|bool $user_id If an array, remove this user from foe lists, or false to ignore
* @return null
*/
function phpbb_update_foes($db, $auth, $group_id = false, $user_id = false)
{
// update foes for some user
if (is_array($user_id) && sizeof($user_id))
{
$sql = 'DELETE FROM ' . ZEBRA_TABLE . '
WHERE ' . $db->sql_in_set('zebra_id', $user_id) . '
AND foe = 1';
$db->sql_query($sql);
return;
}
// update foes for some group
if (is_array($group_id) && sizeof($group_id))
{
// Grab group settings...
$sql_ary = array(
'SELECT' => 'a.group_id',
'FROM' => array(
ACL_OPTIONS_TABLE => 'ao',
ACL_GROUPS_TABLE => 'a',
),
'LEFT_JOIN' => array(
array(
'FROM' => array(ACL_ROLES_DATA_TABLE => 'r'),
'ON' => 'a.auth_role_id = r.role_id',
),
),
'WHERE' => '(ao.auth_option_id = a.auth_option_id OR ao.auth_option_id = r.auth_option_id)
AND ' . $db->sql_in_set('a.group_id', $group_id) . "
AND ao.auth_option IN ('a_', 'm_')",
'GROUP_BY' => 'a.group_id',
);
$sql = $db->sql_build_query('SELECT', $sql_ary);
$result = $db->sql_query($sql);
$groups = array();
while ($row = $db->sql_fetchrow($result))
{
$groups[] = (int) $row['group_id'];
}
$db->sql_freeresult($result);
if (!sizeof($groups))
{
return;
}
switch ($db->get_sql_layer())
{
case 'mysqli':
case 'mysql4':
$sql = 'DELETE ' . (($db->get_sql_layer() === 'mysqli' || version_compare($db->sql_server_info(true), '4.1', '>=')) ? 'z.*' : ZEBRA_TABLE) . '
FROM ' . ZEBRA_TABLE . ' z, ' . USER_GROUP_TABLE . ' ug
WHERE z.zebra_id = ug.user_id
AND z.foe = 1
AND ' . $db->sql_in_set('ug.group_id', $groups);
$db->sql_query($sql);
break;
default:
$sql = 'SELECT user_id
FROM ' . USER_GROUP_TABLE . '
WHERE ' . $db->sql_in_set('group_id', $groups);
$result = $db->sql_query($sql);
$users = array();
while ($row = $db->sql_fetchrow($result))
{
$users[] = (int) $row['user_id'];
}
$db->sql_freeresult($result);
if (sizeof($users))
{
$sql = 'DELETE FROM ' . ZEBRA_TABLE . '
WHERE ' . $db->sql_in_set('zebra_id', $users) . '
AND foe = 1';
$db->sql_query($sql);
}
break;
}
return;
}
// update foes for everyone
$perms = array();
foreach ($auth->acl_get_list(false, array('a_', 'm_'), false) as $forum_id => $forum_ary)
{
foreach ($forum_ary as $auth_option => $user_ary)
{
$perms = array_merge($perms, $user_ary);
}
}
if (sizeof($perms))
{
$sql = 'DELETE FROM ' . ZEBRA_TABLE . '
WHERE ' . $db->sql_in_set('zebra_id', array_unique($perms)) . '
AND foe = 1';
$db->sql_query($sql);
}
unset($perms);
}
/**
* Lists inactive users
*/
function view_inactive_users(&$users, &$user_count, $limit = 0, $offset = 0, $limit_days = 0, $sort_by = 'user_inactive_time DESC')
{
global $db, $user;
$sql = 'SELECT COUNT(user_id) AS user_count
FROM ' . USERS_TABLE . '
WHERE user_type = ' . USER_INACTIVE .
(($limit_days) ? " AND user_inactive_time >= $limit_days" : '');
$result = $db->sql_query($sql);
$user_count = (int) $db->sql_fetchfield('user_count');
$db->sql_freeresult($result);
if ($user_count == 0)
{
// Save the queries, because there are no users to display
return 0;
}
if ($offset >= $user_count)
{
$offset = ($offset - $limit < 0) ? 0 : $offset - $limit;
}
$sql = 'SELECT *
FROM ' . USERS_TABLE . '
WHERE user_type = ' . USER_INACTIVE .
(($limit_days) ? " AND user_inactive_time >= $limit_days" : '') . "
ORDER BY $sort_by";
$result = $db->sql_query_limit($sql, $limit, $offset);
while ($row = $db->sql_fetchrow($result))
{
$row['inactive_reason'] = $user->lang['INACTIVE_REASON_UNKNOWN'];
switch ($row['user_inactive_reason'])
{
case INACTIVE_REGISTER:
$row['inactive_reason'] = $user->lang['INACTIVE_REASON_REGISTER'];
break;
case INACTIVE_PROFILE:
$row['inactive_reason'] = $user->lang['INACTIVE_REASON_PROFILE'];
break;
case INACTIVE_MANUAL:
$row['inactive_reason'] = $user->lang['INACTIVE_REASON_MANUAL'];
break;
case INACTIVE_REMIND:
$row['inactive_reason'] = $user->lang['INACTIVE_REASON_REMIND'];
break;
}
$users[] = $row;
}
$db->sql_freeresult($result);
return $offset;
}
/**
* Lists warned users
*/
function view_warned_users(&$users, &$user_count, $limit = 0, $offset = 0, $limit_days = 0, $sort_by = 'user_warnings DESC')
{
global $db;
$sql = 'SELECT user_id, username, user_colour, user_warnings, user_last_warning
FROM ' . USERS_TABLE . '
WHERE user_warnings > 0
' . (($limit_days) ? "AND user_last_warning >= $limit_days" : '') . "
ORDER BY $sort_by";
$result = $db->sql_query_limit($sql, $limit, $offset);
$users = $db->sql_fetchrowset($result);
$db->sql_freeresult($result);
$sql = 'SELECT count(user_id) AS user_count
FROM ' . USERS_TABLE . '
WHERE user_warnings > 0
' . (($limit_days) ? "AND user_last_warning >= $limit_days" : '');
$result = $db->sql_query($sql);
$user_count = (int) $db->sql_fetchfield('user_count');
$db->sql_freeresult($result);
return;
}
/**
* Get database size
* Currently only mysql and mssql are supported
*/
function get_database_size()
{
global $db, $user, $table_prefix;
$database_size = false;
// This code is heavily influenced by a similar routine in phpMyAdmin 2.2.0
switch ($db->get_sql_layer())
{
case 'mysql':
case 'mysql4':
case 'mysqli':
$sql = 'SELECT VERSION() AS mysql_version';
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
if ($row)
{
$version = $row['mysql_version'];
if (preg_match('#(3\.23|[45]\.|10\.[0-9]\.[0-9]{1,2}-+Maria)#', $version))
{
$db_name = (preg_match('#^(?:3\.23\.(?:[6-9]|[1-9]{2}))|[45]\.|10\.[0-9]\.[0-9]{1,2}-+Maria#', $version)) ? "`{$db->get_db_name()}`" : $db->get_db_name();
$sql = 'SHOW TABLE STATUS
FROM ' . $db_name;
$result = $db->sql_query($sql, 7200);
$database_size = 0;
while ($row = $db->sql_fetchrow($result))
{
if ((isset($row['Type']) && $row['Type'] != 'MRG_MyISAM') || (isset($row['Engine']) && ($row['Engine'] == 'MyISAM' || $row['Engine'] == 'InnoDB' || $row['Engine'] == 'Aria')))
{
if ($table_prefix != '')
{
if (strpos($row['Name'], $table_prefix) !== false)
{
$database_size += $row['Data_length'] + $row['Index_length'];
}
}
else
{
$database_size += $row['Data_length'] + $row['Index_length'];
}
}
}
$db->sql_freeresult($result);
}
}
break;
case 'sqlite':
case 'sqlite3':
global $dbhost;
if (file_exists($dbhost))
{
$database_size = filesize($dbhost);
}
break;
case 'mssql':
case 'mssql_odbc':
case 'mssqlnative':
$sql = 'SELECT @@VERSION AS mssql_version';
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
$sql = 'SELECT ((SUM(size) * 8.0) * 1024.0) as dbsize
FROM sysfiles';
if ($row)
{
// Azure stats are stored elsewhere
if (strpos($row['mssql_version'], 'SQL Azure') !== false)
{
$sql = 'SELECT ((SUM(reserved_page_count) * 8.0) * 1024.0) as dbsize
FROM sys.dm_db_partition_stats';
}
}
$result = $db->sql_query($sql, 7200);
$database_size = ($row = $db->sql_fetchrow($result)) ? $row['dbsize'] : false;
$db->sql_freeresult($result);
break;
case 'postgres':
$sql = "SELECT proname
FROM pg_proc
WHERE proname = 'pg_database_size'";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
if ($row['proname'] == 'pg_database_size')
{
$database = $db->get_db_name();
if (strpos($database, '.') !== false)
{
list($database, ) = explode('.', $database);
}
$sql = "SELECT oid
FROM pg_database
WHERE datname = '$database'";
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
$oid = $row['oid'];
$sql = 'SELECT pg_database_size(' . $oid . ') as size';
$result = $db->sql_query($sql);
$row = $db->sql_fetchrow($result);
$db->sql_freeresult($result);
$database_size = $row['size'];
}
break;
case 'oracle':
$sql = 'SELECT SUM(bytes) as dbsize
FROM user_segments';
$result = $db->sql_query($sql, 7200);
$database_size = ($row = $db->sql_fetchrow($result)) ? $row['dbsize'] : false;
$db->sql_freeresult($result);
break;
}
$database_size = ($database_size !== false) ? get_formatted_filesize($database_size) : $user->lang['NOT_AVAILABLE'];
return $database_size;
}
/**
* Retrieve contents from remotely stored file
*
* @deprecated 3.1.2 Use file_downloader instead
*/
function get_remote_file($host, $directory, $filename, &$errstr, &$errno, $port = 80, $timeout = 6)
{
global $phpbb_container;
// Get file downloader and assign $errstr and $errno
$file_downloader = $phpbb_container->get('file_downloader');
$file_data = $file_downloader->get($host, $directory, $filename, $port, $timeout);
$errstr = $file_downloader->get_error_string();
$errno = $file_downloader->get_error_number();
return $file_data;
}
/*
* Tidy Warnings
* Remove all warnings which have now expired from the database
* The duration of a warning can be defined by the administrator
* This only removes the warning and reduces the associated count,
* it does not remove the user note recording the contents of the warning
*/
function tidy_warnings()
{
global $db, $config;
$expire_date = time() - ($config['warnings_expire_days'] * 86400);
$warning_list = $user_list = array();
$sql = 'SELECT * FROM ' . WARNINGS_TABLE . "
WHERE warning_time < $expire_date";
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
$warning_list[] = $row['warning_id'];
$user_list[$row['user_id']] = isset($user_list[$row['user_id']]) ? ++$user_list[$row['user_id']] : 1;
}
$db->sql_freeresult($result);
if (sizeof($warning_list))
{
$db->sql_transaction('begin');
$sql = 'DELETE FROM ' . WARNINGS_TABLE . '
WHERE ' . $db->sql_in_set('warning_id', $warning_list);
$db->sql_query($sql);
foreach ($user_list as $user_id => $value)
{
$sql = 'UPDATE ' . USERS_TABLE . " SET user_warnings = user_warnings - $value
WHERE user_id = $user_id";
$db->sql_query($sql);
}
$db->sql_transaction('commit');
}
set_config('warnings_last_gc', time(), true);
}
/**
* Tidy database, doing some maintanance tasks
*/
function tidy_database()
{
global $db;
// Here we check permission consistency
// Sometimes, it can happen permission tables having forums listed which do not exist
$sql = 'SELECT forum_id
FROM ' . FORUMS_TABLE;
$result = $db->sql_query($sql);
$forum_ids = array(0);
while ($row = $db->sql_fetchrow($result))
{
$forum_ids[] = $row['forum_id'];
}
$db->sql_freeresult($result);
// Delete those rows from the acl tables not having listed the forums above
$sql = 'DELETE FROM ' . ACL_GROUPS_TABLE . '
WHERE ' . $db->sql_in_set('forum_id', $forum_ids, true);
$db->sql_query($sql);
$sql = 'DELETE FROM ' . ACL_USERS_TABLE . '
WHERE ' . $db->sql_in_set('forum_id', $forum_ids, true);
$db->sql_query($sql);
set_config('database_last_gc', time(), true);
}
/**
* Add permission language - this will make sure custom files will be included
*/
function add_permission_language()
{
global $config, $user, $phpEx, $phpbb_extension_manager;
// add permission language files from extensions
$finder = $phpbb_extension_manager->get_finder();
// We grab the language files from the default, English and user's language.
// So we can fall back to the other files like we do when using add_lang()
$default_lang_files = $english_lang_files = $user_lang_files = array();
// Search for board default language if it's not the user language
if ($config['default_lang'] != $user->lang_name)
{
$default_lang_files = $finder
->prefix('permissions_')
->suffix(".$phpEx")
->core_path('language/' . basename($config['default_lang']) . '/')
->extension_directory('/language/' . basename($config['default_lang']))
->find();
}
// Search for english, if its not the default or user language
if ($config['default_lang'] != 'en' && $user->lang_name != 'en')
{
$english_lang_files = $finder
->prefix('permissions_')
->suffix(".$phpEx")
->core_path('language/en/')
->extension_directory('/language/en')
->find();
}
// Find files in the user's language
$user_lang_files = $finder
->prefix('permissions_')
->suffix(".$phpEx")
->core_path('language/' . $user->lang_name . '/')
->extension_directory('/language/' . $user->lang_name)
->find();
$lang_files = array_merge($english_lang_files, $default_lang_files, $user_lang_files);
foreach ($lang_files as $lang_file => $ext_name)
{
if ($ext_name === '/')
{
$user->add_lang($lang_file);
}
else
{
$user->add_lang_ext($ext_name, $lang_file);
}
}
}
/**
* Enables a particular flag in a bitfield column of a given table.
*
* @param string $table_name The table to update
* @param string $column_name The column containing a bitfield to update
* @param int $flag The binary flag which is OR-ed with the current column value
* @param string $sql_more This string is attached to the sql query generated to update the table.
*
* @return null
*/
function enable_bitfield_column_flag($table_name, $column_name, $flag, $sql_more = '')
{
global $db;
$sql = 'UPDATE ' . $table_name . '
SET ' . $column_name . ' = ' . $db->sql_bit_or($column_name, $flag) . '
' . $sql_more;
$db->sql_query($sql);
}
+SV T]p+ wL<J$囿}V :Ir%Nq!T^8,T+ 6X;s,8d<<g7Looh
!-\ YxT݇ ?Dg% I*dFi4x髖%@mA>6 N%>|BV.gO1e 4@4 ic:c50Ψ$Вi6t܄ى֒F%G Q|GFh YWm:4^CEJTWuٷtR2YTrzymCq̈́ VLo/KҞ$H"ȑc#'OHH''3E,ԖWbE0~ѮG k!71_ c0JZ]ĔJk×Os/ p J<|O,B+/!ms Q7e}~2 qrL5?RXS*Zc<Qlbҵ%9Ubn-: ԭA=\ 0<%MHLjcsePc?ˇw=3p?oQXfDWZ Hǎg
KzqCLvC =Or Ť"ƞ* ~/;H >i^<9±_vACav(fҍ Zb oAf,b%I-
|\-$DggjW̲ ˕${U?ڄ)NYjw*0 ?_'}DM-EBy[q9>n UUSoH
sKR9 }@Qk@qGh|@9"%^@V14s;`ɧC#yn!xՓnO1a!sF|*Yq<ٮ\ MV݆n]T17'/ ]@Y7լV*}b0}N #yHwugW'gZ-t nڎ̣ l;%;V(
uN!*QJD'M{ 8:| 02 f'ex0k\F䦂
UaS*(J8} 8*m5WlAhTk%p*Ke*֖F(F|-xK='Z2"$%൞>[E6quk>Wc `G̊魡
H`r o /-4iX%LM.hɛZv?8_ F M~}6vq=Kֈ:-5@jMj{:2:b5N41v-G5K)n} AUx1ZfD &U*"S 7 Hr|S,[[.pKFQ/5+!eV k"c X y"rƬխDfir@G{?GrE]'n6'َ5UhO$;֝VI~^|irt/y'~5X73P ڜyF RGdBsh
bLƎ n*qn35ok4뤬y?C_ ȑ\鳬 00<^3rYAUhSP45 @zj@a -*ҀؤW-/
hVM6!1 ; BAPfēnP@|{1 @a|n)Oq
ΓɟH:#=kZ( _٩hd Sa؈^qcbww(%C՟e9l[xLgo ET >̓ao_< 3p*9l `Ly\hy,Ud )maO^14@C]Y0pNp_&)/9lݛ v*)=7Øϊen@[OCǧx{ /iH& QF
hqoW`|B%4pq^塚8n+Pb qZ*zquqTegyyq'C=}%eGoP ?ྲkVO=#RCC$aubr';LAYa7lS I47Kؽs<zp.7u**ZCSҷW^}P
ALUeZτ)&:<4uu:2ya⫚*myGyLPgP>ad#A²^WuR3!'̹\lڴ(* hM'.~h OieG9>ߋa?fnA~`kyS3|,X2p7: [d S"" [ j&.P(Ũ{ ߴ
<DBYqIsm9Xp*T-2q8;Akϙ' 薪dsr>lPЛ!swFuЙүzm-Rzo$) ELi
:BDpG
ع|%ȵ 1B9ARn+cmÆl?rR+#u,g6hBsc85 X%OO?弡3NRa{%S1
_4’ 2pˆK/80SijHU ,3ה͐JBrQXi?Lw>P%FlwB1jt>7PE r1x-[c p-O]UD/)H(,umh"?e儠L}x(ulݐI6i]y1 _Q%7}A >5+E\Wsv;Ċ1 NnЬmp)c،ߧNQա˨Q0]Cr垞0+L
&G\gfd~|z6 wKs3.W'@0s蓬ӖAO݉jxg^l Ailn'RcՐΐ_M."^@ԳTjϲ1~ls %; ºKڒ9v˟n[QTZw. l Iؔ9Tu!ZgDf LXD-sm.B2/Xc]U+UV q?_(ʲڧ
tvOk#HmBL{;idC(Ye[Ę& iYMٓ!+8@t3^lNY(.=-VjIjDs LYkȗR%>sl3Z ^CO({(V2=A`FVcev=ʝqUTE
v崓+L7 JzgԺGYoe2ƐX~gRG/P8h~-7)]1sau=I1) .h7OB ̻Å_ܟʸ\ǵ~ :oek 94 WoLiVAeub\]}ߠҪ@Fj1X !"47W Q I2_=劶YU^2#WrsY9[+ S
PlFg?qn :t`" FaTԙakVf-qp9dS!N?%_=4 4t(A ֚nw}đҰpZ7e*y 5ԣ]%&Tm|V un m/q{a\ !J
H:cYܷR8SHh8]fg(T*}WM9,oaK"Fh'?3/{V)b$6 R${n8=؛o:KCuWdy/\7Tu2ӻ^$~-|Lv0#}UK}T ohV'ǯJ<*_}<]O,4 ~m=aMpQ}/Ɖ)SoH;hGՇshg1хкdFR%Ȫ%!t|-
O {7}@Yi^32UNTB])N@qJoը2tHY83&Ĩ ^xI'W33jHmutD`Df&ޱgn+ 7ݺN)H2'5d:sqꊑk%%pcXVx!cX5x`V|@zt1_#t5 c/~{<] )>F ?VmO ܹC0R%v$fXU/p;9egg.*cZڂN̰(ԋr 4 HAhX
r(
[eZrF j{D_R3x?g+Zo4HP
\6|x\łx ~BKZ_ǁPdD/j ͤzLk Apjߙ%iuihſ5IH·ۂ/aNkC2|Â*Oԯ#Uê )B$/?֥P CqezڧI9㎾AO
H[&Ie @6[9 3Vx. j Nl^ hUI)a4$Xz`|d ɘ\ oDqVUxR ?i!ZuKep"oXV6a(2h:RH5[ԤfaSUvXT=Ĩ4͠VF-PL[#
88cSs ;j#qhRC}ɄFt1KJ.W*'^M52 +aT`#Ǚ!D- ΜNy5 dK
Y%1r_ ڷ ,2rVb@i$lMU|}KnE$Us< ? ݰLCRswqYݹfj͆u7(č> 5rŁ&^X&_X)m [}Βt9@zBѧSu(
>WRWz/CyU%!DYxػx`lCгZk\7B'92>&:
1h(o3{}wmةx:}N*LK ܣзvxD -?}tޑ* 5
rG}5H (
yC fA$ZQW8)V)gjGuV)$bJUdd䞞X3 yU{q*SCU/z= q耈` ssJR`5 {!l{s,q
N6 ޖ[vҠh3yL`1c0d) &PDn+<,xweb>$,G/ f{b$ _!!p)ݓ `E]*Ֆ$ʎЃZ̕knF ÚвXbvȨ a r-~iDo)q6:6n6 ,#eVLu5my96.gTFݘ, 0Q=kC p_"wE.J_ _.
$'rta Zu)x~ͯ67~AVO=+8!zBj l Ze6zxV,lGa\/LEܰ$D)>mOpO7ڇ;>VY{-x!khP阧]l8K[oR[7jKRevJk{,UYeQ͒MnnJߥh3q=t4n.XCت/Xq˂.
T2U$~4_JzX NVǎ/[&y7 0/Y (>%0*'di{ buV"mX@" {c|e&9V1V52g8.(O Esf,W"+\A2 XBi4칠b%vĩue7T?,W#ʌ@/`Rk\hUy8DJiyZ01Ph=Y noFiuNUE}3UJ}lg`jnqb3y>e!`KR͊} \Cޖ91̋
jK'yݓzrPʤMYⳝeI ]_֗K.Kg_q/'*L'ΆRsIo OJv|
(}cB: ; Q +U IG{V
Ϋ6 'EaP`FB/Tꛒ`]zNlbc."oX
0q)֖ɿ=n]g֜*r v
pS 3H >/Z& S<%ZߘEcMT2
=jm{pT)vh-hŜDύ+K_N0 &664,V)nmTl.@iD¢| <!pY&._g>aخIL{"o*"ntXtf[=W($*6
1{N{ebMUx#RSZa _ 2Z TG ErP/ItX-قJ.澄_RNce=|^`ƒݺ/o^7,O P{c% #ճ Pn^ieB:T=m=_Ƹ?⦖S>*uף N'm ?t&8^ŖuϮZcZ:a`RD:RIi\"Mr|O z5A2 ^N$ V3 #F֦!Ow`Wι"W"D u va_/a^J L: E&} 1AnC)NKSp594ѥ]K^#ⶩ5IYJIe&: #uK/;$KiSa1H-"$UY7>͚xfs
& y\{JByX( ;V l> C ([ M'Q0EV$Hx˒ +Cg, 絳EHMf⃐Y+2yO/z2-@CК]::)h_TcMH =\CKZ)%T4 xyTm,Pc!+k (-`'ijXT+%z-yr-G)>5ăuBf wf*mdlozM;ߓ`If҅#䡈OV|0 yYJ{N: _0FB Xw}X#TWUlt\
iF'V[$ykH=V,ɓ^Gfb@eLwXdR ozf< % 0ERlQ!k.=Eǝ<;(R$ 3 J Z!56F&Xex@@|)fh@P'>h6A F
ivZ\c@l&,Wjrucl7WA"JxD0irYMqԩiM $#O-4SFWU=ؠ-a ߇4 *#eUU'x6"~bvlCn1Y -N'gE;٬g*k`D(91/Q 'NEbx=j2\h].;;e0 O3^pX
x6 Hًo?'Vvu~ ;8dNw$Pv] u_ ל7[B L$#уu5K'U%:TQDtȭO#b<{W&u5~@)A=nu}`~Yؘ~i#X4h9aLSFZ W`ڬ {k {"KBf :œnfu*A+*Zr"8C;"?ok q_8qj P
ujS8^h9f ve9p
Շ](Xr̵ R01K5Rs)U/emc\?rV&K8)N=*fS Pl]6:W8( /
*G%XD-$34aCHMZg
MP.8SSz`X40ѣ'ϝM$
r5)fzL;ZFN~P nLACPJ|L_FVb:C
I͆ %q
:T u5 " ͺ֭";EJ1 Mxn 3H1Et|o!3RࠟV?ReYYӾr/ /UKEgqy&T&}Gxٻxp3f? Y ;a %w㝌stQt&2@mD+XYnw:(!hF`Nc%"IAkTc><521YЎ Fmd_h?mg8K7xB>ksM a ⃬}g W ZY< Jԩm `uZ
8d15bA"$b|zz+ ϻxeHF2ut Lz"zgI.
9h<,UnL]pj'[H VyWx|f%*R{Wi2S{̭ȗ7) 4蓡/Ɏ DVMcW34bSu%f+$z
` c@;}ó7;h>s@wr? (㧉!h?a1ۓ~b2Z;˹.HɢdYrPb*1uh"o9Md5fa=St9\݆ބ5Y Ct=[ bz' ۨ3{3UR bQ[ktd*\lq6ݝ ><иX2c2 {_CVbur*^60R)YEϬ%$C8I T:fWRN\6
5v8Eq69Y{jCш9>IX8u
LY'aEX%}r*y0ȎW!Uc=7`#T1<3Eifimjm++ LnXe4 8v\
?U=xq9L=6Y2z~7:ZUW LjWK( U $DQty "Z4!w<D 4*$4^퓃
B'2{je :ر]_&'( ûY*|8!+%!dݙqk/YxFw)p/ v|J
G (F;BC)v
be*FlEbΗ. ,D^Dʹ̂H; ńr ]c:+zA W
q2/R;WP.k
ާ-:r
X<ҥ*3or x?
p!4_SAD^c?OUZ
{
4!@$2!ߤ%r _ QZ֯C<V2t^Lץ(|۟)MY#U|Q0ȅ t" wK'%X}br6 Z|dnY^čڮE&_)YY}U
ڸ։Vi_)/xV;xI"wIpֺpe j L Ψ:aG
0ԥFb}
K$1|@o $* N npZ.kBX>>@-$뾝ӟ;yy۳\ܐc}\ ЩNtQdd+*bOv-h83UԖ84kA) *&H.5W_[ćրcŊW {&n6' Y^\=/yvf$L-Az-Y^=$"/w4 O 2߃1&gz~3GQVN :SN [#zФ"kZ%Ueۧɛa FCpXL86ߦ"^:ޞѾoy]÷'}ݎվJ= T`$ ,T}Du>r'anSM'SbVҝD8Uh
dq;)x[7 n >Ię-
A{ # _${z(7!5d2ޣ8!fk7qYmk"13 Luxȱv+gͦRP`jʨ l|&c2,xpJa)"lqyM71tH.*9r3 !G SohaDMNsxr^o*>oW[h:̈
2 iQF=C(x:syGj'qZFhr$H8e,zٻ`mMKKA_҈:]o$"<_\Jҁ. 躣\
wJ Owxk_s?VrS
~
!&:El
YI}I~{zȼb5i
T7a^8;C C)E}5Lŕ39xtg7Ys馿Yxlor
irC"LF+wl=)d5Զ4tVu.CG@SX$,E\'kIq]f*1Gô%*ǢKn Fq=&$Ԏ. " Ht2|[7M֢Ha;vqMaH9>/A * >f]o }>řU 6@uTՊR^2,[u>5Q Zc`l&d08wYĔt${ [- >r`o Q 6BM~:c6}XwKVa!1F#;ĘT
1+f?+A MqndKEkDc7y̒\q@_1Yb X r>8n
2┐%ԍyaB ٢ƫdt4( i#zXE,öE 1c j:BP r"7 ԱscDB%MW3 UMOC
elT~me j!Cv.C~ A/֗Zvj?9(ml"w]O ?zfֺ-pWMh+ҟF";yM]LRǬA,OyNF[a-(#6*t>{9>P0<AE2 ]RJ˾<,A1#:><~L fV4E]#c):ʬ R"UtL7A~*[h?̘ UÆ>k]dYy? )Ƅ0LMb',Xd?)X`6[i Yo^D݄A6÷̖2(Xh׳PDD q5zΣ;9p6b/*fK>0ECI]Mja}|V=4ӂ;hwl܋QD