Not a bug vB4 Import: Support for importing the moderator log
- Thread starter Steffen
- Start date
Steffen
Well-known member
I've spent about two days building an importer for the moderator log and testing it on our forums. The vB4 moderator log is quite messy but as far as we can tell so far the importer seems to be working fine. I think it's a solid foundation (or should at least be useful as a reference) if you want to to build an official importer for the moderator log. (But please note that we are fine with the code below, so please don't waste time building an official moderator log importer just for us!)
Note to other potential users: You have to be able to help yourself if you want to use this code. I cannot provide support.
There are at least the following caveats:
Note to other potential users: You have to be able to help yourself if you want to use this code. I cannot provide support.
There are at least the following caveats:
- Only supports the CLI importer because it doesn't use timers and has huge memory requirements depending on the size of your moderator log. If you implement timers and don't want to save state ($visibleState, $openState, $stickyState) between steps then I think you should use two nested loops where the outer loop loops over all threads.
- Only supports imports with "Retain IDs" enabled because ID lookups aren't implemented
- Only supports moderator actions for forum threads (i.e. no albums, groups, profile posts, ...)
- Only supports vB3+ moderator logs. vB2 moderator log entries (type=0) are skipped because they are text logs with no structure that would require locale-aware parsing. I've actually implemented this for our specific mix of half-english / half-german vB2 moderator log entries but this isn't portable at all and therefore not included in the code below.
- Type 6 entries ("thread_edited_visible_x_open_y_sticky_z") are a mess because they don't log which values have changed but only what their new values are (even if they haven't changed). Sometimes they even directly contradict other log entries for the same thread with exactly the same timestamp. I've done my best to make sense of these entries but maybe something will get lost. Type 6 entries are the (only) reason we need the state arrays $visibleState, $openState, and $stickyState.
- The code is unforgiving if for example an "unserialize" call fails. That hasn't been a problem for us but probably should be handled more gracefully.
- I'm doing nasty things by storing strings (thread titles) in the "ids" field of the "action_params" JSON object. That doesn't seem to be causing any problems at the moment but definetely has the potential to cause problems in future XenForo versions. (The reason for doing this in the first place is that the vB moderator log sometimes only contains thread titles where the XenForo moderator log expects thread IDs.)
- There are some german phrases that should be translated / localized
- Type 1008 seems to be a custom type that I added many years ago and may not be needed by others forums
- Maybe more
PHP:
public function stepModeratorLog(StepState $state, array $stepConfig)
{
\XF::setMemoryLimit(2048 * 1024 * 1024);
$rows = $this->sourceDb->fetchAll($this->prepareImportSql($this->prefix, "
SELECT m.*,
t.title AS t_title, t.postuserid AS t_userid, t.postusername AS t_username,
p.title AS p_title, p.userid AS p_userid, p.username AS p_username
FROM moderatorlog AS m
LEFT JOIN thread AS t USING (threadid)
LEFT JOIN post AS p USING (postid)
ORDER BY m.dateline, m.moderatorlogid
"));
if (!$rows)
{
return $state->complete();
}
$insert = [];
$visibleState = [];
$openState = [];
$stickyState = [];
foreach ($rows as $row)
{
if (!$row['type'])
{
// skip vB2 moderator log entries
continue;
}
if (in_array($row['type'], [17, 18, 19, 20, 24, 25, 26]))
{
$content_type = 'post';
$content_id = $row['postid'];
$content_user_id = (int) $row['p_userid'];
$content_username = (string) $row['p_username'];
$content_title = $row['threadtitle'] ?: (string) $row['t_title'];
$content_url = $row['postid'] ? "posts/$row[postid]/" : '';
if (!$content_title)
{
$content_title = $this->buildThreadTitle($row['p_title']);
}
}
else
{
$content_type = 'thread';
$content_id = $row['threadid'];
$content_user_id = (int) $row['t_userid'];
$content_username = (string) $row['t_username'];
$content_title = $row['threadtitle'] ?: (string) $row['t_title'];
$content_url = $row['threadid'] ? "threads/$row[threadid]/" : '';
}
$discussion_content_type = 'thread';
$discussion_content_id = $row['threadid'];
$actions = [];
$action_params = '';
$thread_id = $row['threadid'];
switch ($row['type'])
{
case 1: // closed_thread
$actions = ['lock'];
$openState[$thread_id] = 0;
break;
case 2: // opened_thread
$actions = ['unlock'];
$openState[$thread_id] = 1;
break;
case 3: // thread_moved_to_x
$actions = ['move'];
$action_params = ['from' => 'Unbekannt'];
break;
case 4: // thread_moved_with_redirect_to_a
$actions = ['move'];
$action_params = ['from' => 'Unbekannt'];
break;
case 5: // thread_copied_to_x
$actions = ['move'];
$action_params = ['from' => 'Unbekannt'];
break;
case 6: // thread_edited_visible_x_open_y_sticky_z
list($visible, $open, $sticky) = unserialize($row['action']);
$prevVisible = $visibleState[$thread_id] ?? 1;
$prevOpen = $openState[$thread_id] ?? 1;
$prevSticky = $stickyState[$thread_id] ?? 0;
if ($visible != $prevVisible)
{
// Seems to cause more harm than good. For example, we have
// type 6 entries that directly contradict entries of type
// 14 (thread_softdeleted) with exactly the same timestamp
//$actions[] = $visible ? 'undelete' : 'delete_soft';
//$visibleState[$thread_id] = $visible;
}
if ($open != $prevOpen)
{
$actions[] = $open ? 'unlock' : 'lock';
$openState[$thread_id] = $open;
}
if ($sticky != $prevSticky)
{
$actions[] = $sticky ? 'stick' : 'unstick';
$stickyState[$thread_id] = $sticky;
}
break;
case 7: // thread_merged_with_x
$actions = ['merge_target'];
$action_params = [
'ids' => $row['action'] ? "'" . html_entity_decode($row['action']) . "'" : 'Unbekannt',
];
break;
case 8: // thread_split_to_x
$actions = ['post_move_source'];
$threadTitle = $this->sourceDb->fetchOne('SELECT title FROM vb_thread
WHERE threadid=?', $row['action']);
$action_params = [
'url' => "threads/$row[action]/",
'title' => html_entity_decode($threadTitle) ?: "Thema $row[action]",
];
break;
case 1008: // thread_split_from_x
$actions = ['post_move_target_existing'];
$action_params = ['ids' => ctype_digit($row['action']) ?
'Thema mit ID ' . $row['action'] : 'Unbekanntes Thema'];
break;
case 9: // unstuck
$actions = ['unstick'];
$stickyState[$thread_id] = 0;
break;
case 10: // stuck
$actions = ['stick'];
$stickyState[$thread_id] = 1;
break;
case 11: // attachment_removed (not supported by XenForo)
break;
case 12: // attachment_uploaded (not supported by XenForo)
break;
case 13: // poll_edited
$actions = ['poll_edit'];
break;
case 14: // thread_softdeleted
$actions = ['delete_soft'];
$action_params = ['reason' => ''];
$visibleState[$thread_id] = 0;
break;
case 15: // thread_removed
$actions = ['delete_hard'];
break;
case 16: // thread_undeleted
$actions = ['undelete'];
$visibleState[$thread_id] = 1;
break;
case 17: // post_x_by_y_softdeleted
$actions = ['delete_soft'];
$action_params = ['reason' => ''];
$_action = unserialize($row['action']);
list($content_title, $content_username) = $_action;
break;
case 18: // post_x_by_y_removed
$actions = ['delete_hard'];
$_action = unserialize($row['action']);
list($content_title, $content_username) = $_action;
break;
case 19: // post_y_by_x_undeleted
$actions = ['undelete'];
$_action = unserialize($row['action']);
list($content_title, $content_username) = $_action;
break;
case 20: // post_x_edited
$actions = ['edit'];
if ($row['action'])
{
$content_title = $this->buildThreadTitle($row['action']);
}
break;
case 21: // approved_thread
$actions = ['approve'];
break;
case 22: // unapproved_thread
$actions = ['unapprove'];
break;
case 23: // thread_merged_from_multiple_threads
$actions = ['merge_target'];
$action_params = ['ids' => 'Mehrere Themen'];
break;
case 24: // unapproved_post
$actions = ['unapprove'];
if ($row['action'])
{
$content_title = $this->buildThreadTitle($row['action']);
}
break;
case 25: // approved_post
$actions = ['approve'];
if ($row['action'])
{
$content_title = $this->buildThreadTitle($row['action']);
}
break;
case 26: // post_merged_from_multiple_posts
$actions = ['merge_target'];
if ($row['action'])
{
$content_title = $this->buildThreadTitle($row['action']);
}
$action_params = ['ids' => 'Mehrere Beiträge'];
break;
case 27: // approved_attachment (not supported by XenForo)
break;
case 28: // unapproved_attachment (not supported by XenForo)
break;
case 29: // thread_title_x_changed
$actions = ['title'];
$action_params = ['old' => html_entity_decode($row['action'])];
break;
case 30: // thread_redirect_removed (not supported by XenForo)
break;
case 31: // posts_copied_to_x
$actions = ['post_copy_target_existing'];
$action_params = ['ids' => '…'];
break;
}
$ip_address = @inet_pton(str_replace('::ffff:', '', $row['ipaddress'])) ?: '';
$action_params = $action_params ? json_encode($action_params) : '';
foreach ($actions as $action)
{
$insert[] = [
'log_date' => $row['dateline'],
'user_id' => $row['userid'],
'ip_address' => $ip_address,
'content_type' => $content_type,
'content_id' => $content_id,
'content_user_id' => $content_user_id,
'content_username' => $content_username,
'content_title' => $content_title,
'content_url' => $content_url,
'discussion_content_type' => $discussion_content_type,
'discussion_content_id' => $discussion_content_id,
'action' => $action,
'action_params' => $action_params,
];
}
if (count($insert) >= 1000)
{
$this->db()->insertBulk('xf_moderator_log', $insert);
$state->imported += count($insert);
$insert = [];
}
}
if (!empty($insert))
{
$this->db()->insertBulk('xf_moderator_log', $insert);
$state->imported += count($insert);
$insert = [];
}
return $state->complete();
}
protected function buildThreadTitle($str)
{
return html_entity_decode(preg_replace('/^(AW|Re): /', '', $str));
}
Last edited:
Steffen
Well-known member
I've turned the code into a CLI Command that can serve as a starting point for people interested in importing their vB3/vB4 moderator log into XenForo 2: https://xenforo.com/community/resources/vbulletin-3-4-moderator-log-import-command.6348/
I think this issue can be closed.
I think this issue can be closed.
Similar threads
- Question
- Question
- Question