/**
* Forum-specific export format.
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
// Begin
$ex->beginExport('', 'bbPress 2.*', array('HashMethod' => 'Vanilla'));
// Users
$ex->query("drop table if exists z_user;");
$ex->query("\n create table `z_user` (\n `ID` bigint(20) unsigned not null AUTO_INCREMENT,\n `user_login` varchar(60) NOT NULL DEFAULT '',\n `user_pass` varchar(255) NOT NULL DEFAULT '',\n `hash_method` varchar(10) DEFAULT NULL,\n `user_email` varchar(100) NOT NULL DEFAULT '',\n `user_registered` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',\n primary key (`ID`),\n KEY `user_email` (`user_email`)\n )\n ;");
$userQuery = "\n select\n ID,\n user_login,\n user_pass,\n 'Vanilla' AS hash_method,\n user_email,\n user_registered\n from :_users\n ";
$ex->query("insert into z_user {$userQuery}");
$guestUserQuery = "\n select\n user_login,\n 'JL2AC3ORF2ZHDU00Z8V0Z1LFC58TY6NWA6IC5M1MIGGDCHNE7K' AS user_pass,\n 'Random' AS hash_method,\n user_email,\n user_registered\n from (\n select\n max(if(pm.meta_key = \"_bbp_anonymous_name\", pm.meta_value, null)) as user_login,\n max(if(pm.meta_key = \"_bbp_anonymous_email\", pm.meta_value, null)) as user_email,\n p.post_date as user_registered\n from :_posts as p\n inner join :_postmeta as pm on pm.post_id = p.ID\n where p.post_author = 0\n and pm.meta_key in ('_bbp_anonymous_name', '_bbp_anonymous_email')\n group by\n pm.post_id\n ) as u\n where user_email not in (select user_email from z_user group by user_email)\n group by user_email\n ";
$ex->query("\n insert into z_user(\n /* ID auto_increment yay! */\n user_login,\n user_pass,\n hash_method,\n user_email,\n user_registered\n ) {$guestUserQuery}");
$user_Map = array('ID' => 'UserID', 'user_login' => 'Name', 'user_pass' => 'Password', 'hash_method' => 'HashMethod', 'user_email' => 'Email', 'user_registered' => 'DateInserted');
$ex->exportTable('User', "select * from z_user;", $user_Map);
// Roles
$ex->exportTable('Role', "\n select\n 1 as RoleID,\n 'Guest' as Name\n union select 2, 'Administrator'\n union select 3, 'Moderator'\n union select 4, 'Member'\n union select 5, 'Blocked'\n ;");
// UserRoles
$userRole_Map = array('user_id' => 'UserID');
$ex->exportTable('UserRole', "\n select\n distinct(user_id) as user_id,\n case\n when locate('bbp_keymaster', meta_value) != 0 then 2\n when locate('bbp_moderator', meta_value) != 0 then 3\n when locate('bbp_participant', meta_value) != 0 then 4\n when locate('bbp_blocked', meta_value) != 0 then 5\n else 1 /* should be bbp_spectator or non-handled roles if that's even possible */\n end as RoleID\n from :_usermeta\n where meta_key = 'wp_capabilities'\n\n union all\n\n select\n ID as user_id,\n 1 as RoleID\n from z_user\n where hash_method = 'Random'\n ;", $userRole_Map);
// Categories
$category_Map = array('ID' => 'CategoryID', 'post_title' => 'Name', 'post_content' => 'Description', 'post_name' => 'UrlCode', 'menu_order' => 'Sort');
$ex->exportTable('Category', "\n select\n *,\n lower(post_name) as forum_slug,\n nullif(post_parent, 0) as ParentCategoryID\n from :_posts\n where post_type = 'forum'\n ;", $category_Map);
// Discussions
$discussion_Map = array('ID' => 'DiscussionID', 'post_parent' => 'CategoryID', 'post_author' => 'InsertUserID', 'post_title' => 'Name', 'Format' => 'Format', 'post_date' => 'DateInserted', 'menu_order' => 'Announce');
$ex->exportTable('Discussion', "\n select\n p.*,\n if (p.post_author > 0, p.post_author, z_user.ID) as post_author, /* override post_author value from p.* */\n 'Html' as Format,\n 0 as Closed\n from :_posts as p\n left join :_postmeta as pm on pm.post_id = p.ID AND pm.meta_key = '_bbp_anonymous_email'\n left join z_user on z_user.user_email = pm.meta_value\n where post_type = 'topic'\n ;", $discussion_Map);
// Comments
$comment_Map = array('ID' => 'CommentID', 'post_parent_id' => 'DiscussionID', 'post_content' => 'Body', 'Format' => 'Format', 'post_author' => 'InsertUserID', 'post_date' => 'DateInserted');
$ex->exportTable('Comment', "\n select\n p.*,\n if (p.post_author > 0, p.post_author, z_user.ID) as post_author, /* override post_author value from p.* */\n case\n when p.post_type = 'topic' then p.ID\n else p.post_parent\n end as post_parent_id,\n 'Html' as format\n from :_posts p\n left join :_postmeta as pm on pm.post_id = p.ID AND pm.meta_key = '_bbp_anonymous_email'\n left join z_user on z_user.user_email = pm.meta_value\n where post_type = 'topic'\n or post_type = 'reply'\n ;", $comment_Map);
// Cleanup
$ex->query("drop table if exists z_user;");
// End
$ex->endExport();
}
/**
* Main export process.
*
* @param ExportModel $ex
* @see $_Structures in ExportModel for allowed destination tables & columns.
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('node');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'Advanced Forum 7.x-2.*');
$filePath = $cdn = $this->param('filepath', '');
// User.
$user_Map = array();
$ex->exportTable('User', "\n select `u`.`uid` as `UserID`, `u`.`name` as `Name`, `u`.`mail` as `Email`, `u`.`pass` as `Password`,\n 'drupal' as `HashMethod`, from_unixtime(`created`) as `DateInserted`,\n if(`fm`.`filename` is not null, concat('{$filePath}', `fm`.`filename`), NULL) as `Photo`\n from `:_users` `u`\n left join `:_file_managed` `fm` on `u`.`picture` = `fm`.`fid`", $user_Map);
// Role.
$role_Map = array();
$ex->exportTable('Role', "\n SELECT `name` AS `Name`, `rid` AS `RoleID`\n FROM `:_role` `r`\n ORDER BY `weight` ASC", $role_Map);
// User Role.
$userRole_Map = array();
$ex->exportTable('UserRole', "\n SELECT `rid` AS `RoleID`, `uid` AS `UserID`\n FROM `:_users_roles` `ur`", $userRole_Map);
// Category.
$category_Map = array();
$ex->exportTable('Category', "\n SELECT `ttd`.`tid` AS `CategoryID`, `tth`.`parent` AS `ParentCategoryID`,\n `ttd`.`name` AS `Name`, `ttd`.`weight` AS `Sort`\n FROM `:_taxonomy_term_data` `ttd`\n LEFT JOIN `:_taxonomy_vocabulary` `tv` USING (`vid`)\n LEFT JOIN `:_taxonomy_term_hierarchy` `tth` USING (`tid`)\n WHERE `tv`.`name` = 'Forums'\n ORDER BY `ttd`.`weight` ASC", $category_Map);
// Discussion.
$discussion_Map = array('body_format' => array('Column' => 'Format', 'Filter' => array(__CLASS__, 'translateFormatType')));
$ex->exportTable('Discussion', "\n SELECT `fi`.`nid` AS `DiscussionID`, `fi`.`tid` AS `CategoryID`, `fi`.`title` AS `Name`,\n `fi`.`comment_count` AS `CountComments`, `fdb`.`body_value` AS `Body`,\n from_unixtime(`n`.`created`) AS `DateInserted`,\n if (`n`.`created`< `n`.`changed`, from_unixtime(`n`.`changed`), NULL) AS `DateUpdated`,\n if (`fi`.`sticky` > 0,2,0) AS `Announce`,\n `n`.`uid` AS `InsertUserID`, `fdb`.`body_format`\n FROM `:_forum_index` `fi`\n JOIN `:_field_data_body` `fdb` ON (`fdb`.`bundle` = 'forum' AND `fi`.`nid`=`fdb`.`entity_id`)\n LEFT JOIN `:_node` `n` USING (`nid`)\n ", $discussion_Map);
// Comment.
$comment_Map = array('comment_body_format' => array('Column' => 'Format', 'Filter' => array(__CLASS__, 'translateFormatType')));
$ex->exportTable('Comment', "\n SELECT `c`.`cid` AS `CommentID`, `c`.`nid` AS `DiscussionID`, `c`.`uid` AS `InsertUserID`,\n from_unixtime(`c`.`created`) AS `DateInserted`,\n if(`c`.`created` < `c`.`changed`, from_unixtime(`c`.`changed`), NULL) AS `DateUpdated`,\n `fdcb`.`comment_body_value` AS `Body`, `fdcb`.`comment_body_format`\n FROM `:_comment` `c` JOIN `:_field_data_comment_body` `fdcb` ON (`c`.`cid` = `fdcb`.`entity_id`)\n ORDER BY `cid` ASC", $comment_Map);
$ex->endExport();
}
/**
* Main export process.
*
* @param ExportModel $ex
* @see $_Structures in ExportModel for allowed destination tables & columns.
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('posts');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Reiterate the platform name here to be included in the porter file header.
$ex->beginExport('', 'MyBB');
// User.
$user_Map = array('uid' => 'UserID', 'username' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'avatar' => 'Photo', 'regdate2' => 'DateInserted', 'regdate3' => 'DateFirstVisit', 'email' => 'Email');
$ex->exportTable('User', "\n select u.*,\n FROM_UNIXTIME(regdate) as regdate2,\n FROM_UNIXTIME(regdate) as regdate3,\n FROM_UNIXTIME(lastactive) as DateLastActive,\n concat(password, salt) as Password,\n 'mybb' as HashMethod\n from :_users u\n ", $user_Map);
// Role.
$role_Map = array('gid' => 'RoleID', 'title' => 'Name', 'description' => 'Description');
$ex->exportTable('Role', "\n select *\n from :_usergroups", $role_Map);
// User Role.
$userRole_Map = array('uid' => 'UserID', 'usergroup' => 'RoleID');
$ex->exportTable('UserRole', "\n select u.uid, u.usergroup\n from :_users u", $userRole_Map);
// Category.
$category_Map = array('fid' => 'CategoryID', 'pid' => 'ParentCategoryID', 'disporder' => 'Sort', 'name' => 'Name', 'description' => 'Description');
$ex->exportTable('Category', "\n select *\n from :_forums f\n ", $category_Map);
// Discussion.
$discussion_Map = array('tid' => 'DiscussionID', 'fid' => 'CategoryID', 'uid' => 'InsertUserID', 'subject' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'views' => 'CountViews', 'replies' => 'CountComments');
$ex->exportTable('Discussion', "\n select *,\n FROM_UNIXTIME(dateline) as DateInserted,\n 'BBCode' as Format\n from :_threads t", $discussion_Map);
// Comment.
$comment_Map = array('pid' => 'CommentID', 'tid' => 'DiscussionID', 'uid' => 'InsertUserID', 'message' => array('Column' => 'Body'));
$ex->exportTable('Comment', "\n select p.*,\n FROM_UNIXTIME(dateline) as DateInserted,\n 'BBCode' as Format\n from :_posts p", $comment_Map);
// UserDiscussion.
$userDiscussion_Map = array('tid' => 'DiscussionID', 'uid' => 'UserID');
$ex->exportTable('UserDiscussion', "\n select *,\n 1 as Bookmarked\n from :_threadsubscriptions t", $userDiscussion_Map);
$ex->endExport();
}
/**
* Forum-specific export format
*
* @todo Project file size / export time and possibly break into multiple files
*
* @param ExportModel $ex
*
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('posts');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'PunBB 1.*', array('HashMethod' => 'punbb'));
$this->cdn = $this->param('cdn', '');
if ($avatarPath = $this->param('avatarpath', false)) {
if (!($avatarPath = realpath($avatarPath))) {
echo "Unable to access path to avatars: {$avatarPath}\n";
exit(1);
}
$this->avatarPath = $avatarPath;
}
unset($avatarPath);
// User.
$user_Map = array('AvatarID' => array('Column' => 'Photo', 'Filter' => array($this, 'getAvatarByID')), 'id' => 'UserID', 'username' => 'Name', 'email' => 'Email', 'timezone' => 'HourOffset', 'registration_ip' => 'InsertIPAddress', 'PasswordHash' => 'Password');
$ex->exportTable('User', "\n SELECT\n u.*, u.id AS AvatarID,\n concat(u.password, '\$', u.salt) AS PasswordHash,\n from_unixtime(registered) AS DateInserted,\n from_unixtime(last_visit) AS DateLastActive\n FROM :_users u\n WHERE group_id <> 2", $user_Map);
// Role.
$role_Map = array('g_id' => 'RoleID', 'g_title' => 'Name');
$ex->exportTable('Role', "SELECT * FROM :_groups", $role_Map);
// Permission.
$permission_Map = array('g_id' => 'RoleID', 'g_modertor' => 'Garden.Moderation.Manage', 'g_mod_edit_users' => 'Garden.Users.Edit', 'g_mod_rename_users' => 'Garden.Users.Delete', 'g_read_board' => 'Vanilla.Discussions.View', 'g_view_users' => 'Garden.Profiles.View', 'g_post_topics' => 'Vanilla.Discussions.Add', 'g_post_replies' => 'Vanilla.Comments.Add', 'g_pun_attachment_allow_download' => 'Plugins.Attachments.Download.Allow', 'g_pun_attachment_allow_upload' => 'Plugins.Attachments.Upload.Allow');
$permission_Map = $ex->fixPermissionColumns($permission_Map);
$ex->exportTable('Permission', "\n SELECT\n g.*,\n g_post_replies AS `Garden.SignIn.Allow`,\n g_mod_edit_users AS `Garden.Users.Add`,\n CASE WHEN g_title = 'Administrators' THEN 'All' ELSE NULL END AS _Permissions\n FROM :_groups g", $permission_Map);
// UserRole.
$userRole_Map = array('id' => 'UserID', 'group_id' => 'RoleID');
$ex->exportTable('UserRole', "SELECT\n CASE u.group_id WHEN 2 THEN 0 ELSE id END AS id,\n u.group_id\n FROM :_users u", $userRole_Map);
// Signatures.
$ex->exportTable('UserMeta', "\n SELECT\n id,\n 'Plugin.Signatures.Sig' AS Name,\n signature\n FROM :_users u\n WHERE u.signature IS NOT NULL", array('id ' => 'UserID', 'signature' => 'Value'));
// Category.
$category_Map = array('id' => 'CategoryID', 'forum_name' => 'Name', 'forum_desc' => 'Description', 'disp_position' => 'Sort', 'parent_id' => 'ParentCategoryID');
$ex->exportTable('Category', "\n SELECT\n id,\n forum_name,\n forum_desc,\n disp_position,\n cat_id * 1000 AS parent_id\n FROM :_forums f\n UNION\n\n SELECT\n id * 1000,\n cat_name,\n '',\n disp_position,\n NULL\n FROM :_categories", $category_Map);
// Discussion.
$discussion_Map = array('id' => 'DiscussionID', 'poster_id' => 'InsertUserID', 'poster_ip' => 'InsertIPAddress', 'closed' => 'Closed', 'sticky' => 'Announce', 'forum_id' => 'CategoryID', 'subject' => 'Name', 'message' => 'Body');
$ex->exportTable('Discussion', "\n SELECT t.*,\n from_unixtime(p.posted) AS DateInserted,\n p.poster_id,\n p.poster_ip,\n p.message,\n from_unixtime(p.edited) AS DateUpdated,\n eu.id AS UpdateUserID,\n 'BBCode' AS Format\n FROM :_topics t\n LEFT JOIN :_posts p\n ON t.first_post_id = p.id\n LEFT JOIN :_users eu\n ON eu.username = p.edited_by", $discussion_Map);
// Comment.
$comment_Map = array('id' => 'CommentID', 'topic_id' => 'DiscussionID', 'poster_id' => 'InsertUserID', 'poster_ip' => 'InsertIPAddress', 'message' => 'Body');
$ex->exportTable('Comment', "\n SELECT p.*,\n 'BBCode' AS Format,\n from_unixtime(p.posted) AS DateInserted,\n from_unixtime(p.edited) AS DateUpdated,\n eu.id AS UpdateUserID\n FROM :_topics t\n JOIN :_posts p\n ON t.id = p.topic_id\n LEFT JOIN :_users eu\n ON eu.username = p.edited_by\n WHERE p.id <> t.first_post_id;", $comment_Map);
if ($ex->exists('tags')) {
// Tag.
$tag_Map = array('id' => 'TagID', 'tag' => 'Name');
$ex->exportTable('Tag', "SELECT * FROM :_tags", $tag_Map);
// TagDisucssion.
$tagDiscussionMap = array('topic_id' => 'DiscussionID', 'tag_id' => 'TagID');
$ex->exportTable('TagDiscussion', "SELECT * FROM :_topic_tags", $tagDiscussionMap);
}
if ($ex->exists('attach_files')) {
// Media.
$media_Map = array('id' => 'MediaID', 'filename' => 'Name', 'file_mime_type' => 'Type', 'size' => 'Size', 'owner_id' => 'InsertUserID', 'thumb_path' => array('Column' => 'ThumbPath', 'Filter' => array($this, 'filterThumbnailData')), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')));
$ex->exportTable('Media', "\n select f.*,\n concat({$this->cdn}, 'FileUpload/', f.file_path) as Path,\n concat({$this->cdn}, 'FileUpload/', f.file_path) as thumb_path,\n 128 as thumb_width,\n from_unixtime(f.uploaded_at) as DateInserted,\n case when post_id is null then 'Discussion' else 'Comment' end as ForeignTable,\n coalesce(post_id, topic_id) as ForieignID\n from :_attach_files f\n ", $media_Map);
}
// End
$ex->endExport();
}
/**
*
* @param ExportModel $ex
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('Threads');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'User Voice');
$ex->sourcePrefix = 'cs_';
// User.
$user_Map = array('LastActivity' => array('Column' => 'DateLastActive'), 'UserName' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'CreateDate' => array('Column' => 'DateInserted'));
$ex->exportTable('User', "\n select u.*,\n concat('sha1\$', m.PasswordSalt, '\$', m.Password) as Password,\n 'django' as HashMethod,\n if(a.Content is not null, concat('import/userpics/avatar',u.UserID,'.jpg'), NULL) as Photo\n from :_Users u\n left join aspnet_Membership m on m.UserId = u.MembershipID\n left join :_UserAvatar a on a.UserID = u.UserID", $user_Map);
// Role.
$role_Map = array('RoleId' => array('Column' => 'RoleID', 'Filter' => array($this, 'roleIDConverter')), 'RoleName' => 'Name');
$ex->exportTable('Role', "\n select *\n from aspnet_Roles", $role_Map);
// User Role.
$userRole_Map = array('RoleId' => array('Column' => 'RoleID', 'Filter' => array($this, 'roleIDConverter')));
$ex->exportTable('UserRole', "\n select u.UserID, ur.RoleId\n from aspnet_UsersInRoles ur\n left join :_Users u on ur.UserId = u.MembershipID\n ", $userRole_Map);
// Category.
$category_Map = array('SectionID' => 'CategoryID', 'ParentID' => 'ParentCategoryID', 'SortOrder' => 'Sort', 'DateCreated' => 'DateInserted');
$ex->exportTable('Category', "\n select s.*\n from :_Sections s", $category_Map);
// Discussion.
$discussion_Map = array('ThreadID' => 'DiscussionID', 'SectionID' => 'CategoryID', 'UserID' => 'InsertUserID', 'PostDate' => 'DateInserted', 'ThreadDate' => 'DateLastComment', 'TotalViews' => 'CountViews', 'TotalReplies' => 'CountComments', 'IsLocked' => 'Closed', 'MostRecentPostAuthorID' => 'LastCommentUserID', 'MostRecentPostID' => 'LastCommentID', 'Subject' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'Body' => array('Column' => 'Body', 'Filter' => 'HTMLDecoder'), 'IPAddress' => 'InsertIPAddress');
$ex->exportTable('Discussion', "\n select t.*,\n p.Subject,\n p.Body,\n 'Html' as Format,\n p.IPAddress as InsertIPAddress,\n if(t.IsSticky > 0, 2, 0) as Announce\n from :_Threads t\n left join :_Posts p on p.ThreadID = t.ThreadID\n where p.SortOrder = 1", $discussion_Map);
// Comment.
$comment_Map = array('PostID' => 'CommentID', 'ThreadID' => 'DiscussionID', 'UserID' => 'InsertUserID', 'IPAddress' => 'InsertIPAddress', 'Body' => array('Column' => 'Body', 'Filter' => 'HTMLDecoder'), 'PostDate' => 'DateInserted');
$ex->exportTable('Comment', "\n select p.*\n from :_Posts p\n where SortOrder > 1", $comment_Map);
// Bookmarks
$userDiscussion_Map = array('ThreadID' => 'DiscussionID');
$ex->exportTable('UserDiscussion', "\n select t.*,\n '1' as Bookmarked,\n NOW() as DateLastViewed\n from :_TrackedThreads t", $userDiscussion_Map);
// Media.
/*$Media_Map = array(
'FileName' => 'Name',
'ContentType' => 'Type',
'ContentSize' => 'Size',
'UserID' => 'InsertUserID',
'Created' => 'DateInserted'
);
$ex->ExportTable('Media', "
select a.*,
if(p.SortOrder = 1, 'Discussion', 'Comment') as ForeignTable,
if(p.SortOrder = 1, p.ThreadID, a.PostID) as ForeignID,
concat('import/attach/', a.FileName) as Path
from :_PostAttachments a
left join :_Posts p on p.PostID = a.PostID
where IsRemote = 0", $Media_Map);
*/
// Decode files in database.
$this->exportHexAvatars();
//$this->ExportHexAttachments();
// El fin.
$ex->endExport();
}
/**
* Main export process.
*
* @param ExportModel $ex
* @see $_Structures in ExportModel for allowed destination tables & columns.
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('post');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Reiterate the platform name here to be included in the porter file header.
$ex->beginExport('', 'esotalk');
// User.
$user_Map = array('memberId' => 'UserID', 'username' => 'Name', 'email' => 'Email', 'confirmed' => 'Verified', 'password' => 'Password');
$ex->exportTable('User', "\n select u.*, 'crypt' as HashMethod,\n FROM_UNIXTIME(joinTime) as DateInserted,\n FROM_UNIXTIME(lastActionTime) as DateLastActive,\n if(account='suspended',1,0) as Banned\n from :_member u", $user_Map);
// Role.
$role_Map = array('groupId' => 'RoleID', 'name' => 'Name');
$ex->exportTable('Role', "\n select groupId, name\n from :_group\n union select max(groupId)+1, 'Member' from :_group\n union select max(groupId)+2, 'Administrator' from :_group\n ", $role_Map);
// User Role.
$userRole_Map = array('memberId' => 'UserID', 'groupId' => 'RoleID');
// Create fake 'member' and 'administrator' roles to account for them being set separately on member table.
$ex->exportTable('UserRole', "\n select u.memberId, u.groupId\n from :_member_group u\n union all\n select memberId, (select max(groupId)+1 from :_group) from :_member where account='member'\n union all\n select memberId, (select max(groupId)+2 from :_group) from :_member where account='administrator'\n ", $userRole_Map);
// Category.
$category_Map = array('channelId' => 'CategoryID', 'title' => 'Name', 'slug' => 'UrlCode', 'description' => 'Description', 'parentId' => 'ParentCategoryID', 'countConversations' => 'CountDiscussions');
$ex->exportTable('Category', "\n select *\n from :_channel c", $category_Map);
// Discussion.
$discussion_Map = array('conversationId' => 'DiscussionID', 'title' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'channelId' => 'CategoryID', 'memberId' => 'InsertUserID', 'sticky' => 'Announce', 'locked' => 'Closed', 'lastPostMemberId' => 'LastCommentUserID', 'content' => 'Body');
// The body of the OP is in the post table.
$ex->exportTable('Discussion', "\n\t\t\tselect\n\t\t\t\tc.conversationId,\n\t\t\t\tc.title,\n\t\t\t\tc.channelId,\n\t\t\t\tp.memberId,\n\t\t\t\tc.sticky,\n\t\t\t\tc.locked,\n\t\t\t\tc.lastPostMemberId,\n\t\t\t\tp.content,\n\t\t\t\t'BBCode' as Format,\n\t\t\t\tfrom_unixtime(startTime) as DateInserted,\n\t\t\t\tfrom_unixtime(lastPostTime) as DateLastComment\n\t\t\tfrom :_conversation c\n\t\t\tleft join :_post p\n\t\t\t\ton p.conversationId = c.conversationId\n\t\t\twhere private = 0\n\t\t\tgroup by c.conversationId\n\t\t\tgroup by p.time", $discussion_Map);
// Comment.
$comment_Map = array('postId' => 'CommentID', 'conversationId' => 'DiscussionID', 'content' => 'Body', 'memberId' => 'InsertUserID', 'editMemberId' => 'UpdateUserID');
// Now we need to omit the comments we used as the OP.
$ex->exportTable('Comment', "\n\t\tselect p.*,\n\t\t\t\t'BBCode' as Format,\n\t\t\t\tfrom_unixtime(time) as DateInserted,\n\t\t\t\tfrom_unixtime(editTime) as DateUpdated\n\t\tfrom :_post p\n\t\tinner join :_conversation c ON c.conversationId = p.conversationId\n\t\tand c.private = 0\n\t\tjoin\n\t\t\t( select conversationId,\n\t\t\t\tmin(postId) as m\n\t\t\tfrom :_post\n\t\t\tgroup by conversationId) r on r.conversationId = c.conversationId\n\t\twhere p.postId<>r.m", $comment_Map);
// UserDiscussion.
$userDiscussion_Map = array('id' => 'UserID', 'conversationId' => 'DiscussionID');
$ex->exportTable('UserDiscussion', "\n select *\n from :_member_conversation\n where starred = 1", $userDiscussion_Map);
// Permission.
// :_channel_group
// Media.
// :_attachment
// Conversation.
$conversation_map = array('conversationId' => 'ConversationID', 'countPosts' => 'CountMessages', 'startMemberId' => 'InsertUserID', 'countPosts' => 'CountMessages');
$ex->exportTable('Conversation', "\n select p.*,\n 'BBCode' as Format,\n from_unixtime(time) as DateInserted,\n from_unixtime(lastposttime) as DateUpdated\n from :_post p\n inner join :_conversation c on c.conversationId = p.conversationId\n and c.private = 1", $conversation_map);
$userConversation_map = array('conversationId' => 'ConversationID', 'memberId' => 'UserID');
$ex->exportTable('UserConversation', "\n select distinct a.fromMemberId as memberId, a.type, c.private, c.conversationId from :_activity a\n inner join :_conversation c on c.conversationId = a.conversationId\n and c.private = 1 and a.type = 'privateAdd'\n union all\n select distinct a.memberId as memberId, a.type, c.private, c.conversationId from :_activity a\n inner join :_conversation c on c.conversationId = a.conversationId\n and c.private = 1 and a.type = 'privateAdd'", $userConversation_map);
$userConversationMessage_map = array('postId' => 'MessageID', 'conversationId' => 'ConversationID', 'content' => 'Body', 'memberId' => 'InsertUserID');
$ex->exportTable('ConversationMessage', "\n select p.*,\n 'BBCode' as Format,\n from_unixtime(time) as DateInserted\n from :_post p\n inner join :_conversation c on c.conversationId = p.conversationId and c.private = 1", $userConversationMessage_map);
$ex->endExport();
}
/**
*
* @param ExportModel $ex
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('Topic');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'YAF.NET (Yet Another Forum)');
$ex->sourcePrefix = 'yaf_';
// User.
$user_Map = array('UserID' => 'UserID', 'Name' => 'Name', 'Email' => 'Email', 'Joined' => 'DateInserted', 'LastVisit' => array('Column' => 'DateLastVisit', 'Type' => 'datetime'), 'IP' => 'InsertIPAddress', 'Avatar' => 'Photo', 'RankID' => array('Column' => 'RankID', 'Type' => 'int'), 'Points' => array('Column' => 'Points', 'Type' => 'int'), 'LastActivity' => 'DateLastActive', 'Password2' => array('Column' => 'Password', 'Filter' => array($this, 'convertPassword')), 'HashMethod' => 'HashMethod');
$ex->exportTable('User', "\n select\n u.*,\n m.Password as Password2,\n m.PasswordSalt,\n m.PasswordFormat,\n m.LastActivity,\n 'yaf' as HashMethod\n from :_User u\n left join :_prov_Membership m\n on u.ProviderUserKey = m.UserID;", $user_Map);
// Role.
$role_Map = array('GroupID' => 'RoleID', 'Name' => 'Name');
$ex->exportTable('Role', "\n select *\n from :_Group;", $role_Map);
// UserRole.
$userRole_Map = array('UserID' => 'UserID', 'GroupID' => 'RoleID');
$ex->exportTable('UserRole', 'select * from :_UserGroup', $userRole_Map);
// Rank.
$rank_Map = array('RankID' => 'RankID', 'Level' => 'Level', 'Name' => 'Name', 'Label' => 'Label');
$ex->exportTable('Rank', "\n select\n r.*,\n RankID as Level,\n Name as Label\n from :_Rank r;", $rank_Map);
// Signatures.
$ex->exportTable('UserMeta', "\n select\n UserID,\n 'Plugin.Signatures.Sig' as `Name`,\n Signature as `Value`\n from :_User\n where Signature <> ''\n\n union all\n\n select\n UserID,\n 'Plugin.Signatures.Format' as `Name`,\n 'BBCode' as `Value`\n from :_User\n where Signature <> '';");
// Category.
$category_Map = array('ForumID' => 'CategoryID', 'ParentID' => 'ParentCategoryID', 'Name' => 'Name', 'Description' => 'Description', 'SortOrder' => 'Sort');
$ex->exportTable('Category', "\n select\n f.ForumID,\n case when f.ParentID = 0 then f.CategoryID * 1000 else f.ParentID end as ParentID,\n f.Name,\n f.Description,\n f.SortOrder\n from :_Forum f\n\n union all\n\n select\n c.CategoryID * 1000,\n null,\n c.Name,\n null,\n c.SortOrder\n from :_Category c;", $category_Map);
// Discussion.
$discussion_Map = array('TopicID' => 'DiscussionID', 'ForumID' => 'CategoryID', 'UserID' => 'InsertUserID', 'Posted' => 'DateInserted', 'Topic' => 'Name', 'Views' => 'CountViews', 'Announce' => 'Announce');
$ex->exportTable('Discussion', "\n select\n case when t.Priority > 0 then 1 else 0 end as Announce,\n t.Flags & 1 as Closed,\n t.*\n from :_Topic t\n where t.IsDeleted = 0;", $discussion_Map);
// Comment.
$comment_Map = array('MessageID' => 'CommentID', 'TopicID' => 'DiscussionID', 'ReplyTo' => array('Column' => 'ReplyToCommentID', 'Type' => 'int'), 'UserID' => 'InsertUserID', 'Posted' => 'DateInserted', 'Message' => 'Body', 'Format' => 'Format', 'IP' => 'InsertIPAddress', 'Edited' => array('Column' => 'DateUpdated', 'Filter' => array($this, 'cleanDate')), 'EditedBy' => 'UpdateUserID');
$ex->exportTable('Comment', "\n select\n case when m.Flags & 1 = 1 then 'Html' else 'BBCode' end as Format,\n m.*\n from :_Message m\n where IsDeleted = 0;", $comment_Map);
// Conversation.
$this->_exportConversationTemps();
$conversation_Map = array('PMessageID' => 'ConversationID', 'FromUserID' => 'InsertUserID', 'Created' => 'DateInserted', 'Title' => array('Column' => 'Subject', 'Type' => 'varchar(512)'));
$ex->exportTable('Conversation', "\n select\n pm.*,\n g.Title\n from z_pmgroup g\n join :_PMessage pm\n on g.Group_ID = pm.PMessageID;", $conversation_Map);
// UserConversation.
$userConversation_Map = array('PM_ID' => 'ConversationID', 'User_ID' => 'UserID', 'Deleted' => 'Deleted');
$ex->exportTable('UserConversation', "\n select pto.*\n from z_pmto pto\n join z_pmgroup g\n on pto.PM_ID = g.Group_ID;", $userConversation_Map);
// ConversationMessage.
$conversationMessage_Map = array('PMessageID' => 'MessageID', 'Group_ID' => 'ConversationID', 'FromUserID' => 'InsertUserID', 'Created' => 'DateInserted', 'Body' => 'Body', 'Format' => 'Format');
$ex->exportTable('ConversationMessage', "\n select\n pm.*,\n case when pm.Flags & 1 = 1 then 'Html' else 'BBCode' end as Format,\n t.Group_ID\n from :_PMessage pm\n join z_pmtext t\n on t.PM_ID = pm.PMessageID;", $conversationMessage_Map);
$ex->endExport();
}
/**
*
* @param ExportModel $ex
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('topics');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'Expression Engine');
$ex->sourcePrefix = 'forum_';
$this->exportConversations();
// Permissions.
$permission_Map = array('group_id' => 'RoleID', 'can_access_cp' => 'Garden.Settings.View', 'can_access_edit' => 'Vanilla.Discussions.Edit', 'can_edit_all_comments' => 'Vanilla.Comments.Edit', 'can_access_admin' => 'Garden.Settings.Manage', 'can_admin_members' => 'Garden.Users.Edit', 'can_moderate_comments' => 'Garden.Moderation.Manage', 'can_view_profiles' => 'Garden.Profiles.View', 'can_post_comments' => 'Vanilla.Comments.Add', 'can_view_online_system' => 'Vanilla.Discussions.View', 'can_sign_in' => 'Garden.SignIn.Allow', 'can_view_profiles3' => 'Garden.Activity.View', 'can_post_comments2' => 'Vanilla.Discussions.Add');
$permission_Map = $ex->fixPermissionColumns($permission_Map);
foreach ($permission_Map as $column => &$info) {
if (is_array($info) && isset($info['Column'])) {
$info['Filter'] = array($this, 'YNBool');
}
}
$ex->exportTable('Permission', "\n SELECT\n g.can_view_profiles AS can_view_profiles2,\n g.can_view_profiles AS can_view_profiles3,\n g.can_post_comments AS can_post_comments2,\n g.can_post_comments AS can_sign_in,\n CASE WHEN can_access_admin = 'y' THEN 'all' WHEN can_view_online_system = 'y' THEN 'view' END AS _Permissions,\n g.*\n FROM forum_member_groups g\n ", $permission_Map);
// User.
$user_Map = array('member_id' => 'UserID', 'username' => array('Column' => 'Username', 'Type' => 'varchar(50)'), 'screen_name' => array('Column' => 'Name', 'Filter' => array($ex, 'HTMLDecoder')), 'Password2' => 'Password', 'email' => 'Email', 'ipaddress' => 'InsertIPAddress', 'join_date' => array('Column' => 'DateInserted', 'Filter' => array($ex, 'timestampToDate')), 'last_activity' => array('Column' => 'DateLastActive', 'Filter' => array($ex, 'timestampToDate')), 'timezone' => 'HourOffset', 'location' => 'Location');
$ex->exportTable('User', "\n SELECT\n 'django' AS HashMethod,\n concat('sha1\$\$', password) AS Password2,\n CASE WHEN bday_y > 1900 THEN concat(bday_y, '-', bday_m, '-', bday_d) ELSE NULL END AS DateOfBirth,\n from_unixtime(join_date) AS DateFirstVisit,\n ip_address AS LastIPAddress,\n CASE WHEN avatar_filename = '' THEN NULL ELSE concat('imported/', avatar_filename) END AS Photo,\n u.*\n FROM forum_members u", $user_Map);
// Role.
$role_Map = array('group_id' => 'RoleID', 'group_title' => 'Name', 'group_description' => 'Description');
$ex->exportTable('Role', "\n SELECT *\n FROM forum_member_groups", $role_Map);
// User Role.
$userRole_Map = array('member_id' => 'UserID', 'group_id' => 'RoleID');
$ex->exportTable('UserRole', "\n SELECT *\n FROM forum_members u", $userRole_Map);
// UserMeta
$ex->exportTable('UserMeta', "\n SELECT\n member_id AS UserID,\n 'Plugin.Signatures.Sig' AS Name,\n signature AS Value\n FROM forum_members\n WHERE signature <> ''");
// Category.
$category_Map = array('forum_id' => 'CategoryID', 'forum_name' => 'Name', 'forum_description' => 'Description', 'forum_parent' => 'ParentCategoryID', 'forum_order' => 'Sort');
$ex->exportTable('Category', "\n SELECT * FROM forum_forums", $category_Map);
// Discussion.
$discussion_Map = array('topic_id' => 'DiscussionID', 'forum_id' => 'CategoryID', 'author_id' => 'InsertUserID', 'title' => array('Column' => 'Name', 'Filter' => array($ex, 'HTMLDecoder')), 'ip_address' => 'InsertIPAddress', 'body' => array('Column' => 'Body', 'Filter' => array($this, 'cleanBodyBrackets')), 'body2' => array('Column' => 'Format', 'Filter' => array($this, 'guessFormat')), 'topic_date' => array('Column' => 'DateInserted', 'Filter' => array($ex, 'timestampToDate')), 'topic_edit_date' => array('Column' => 'DateUpdated', 'Filter' => array($ex, 'timestampToDate')), 'topic_edit_author' => 'UpdateUserID');
$ex->exportTable('Discussion', "\n SELECT\n CASE WHEN announcement = 'y' THEN 1 WHEN sticky = 'y' THEN 2 ELSE 0 END AS Announce,\n CASE WHEN status = 'c' THEN 1 ELSE 0 END AS Closed,\n t.body AS body2,\n t.*\n FROM forum_forum_topics t", $discussion_Map);
// Comment.
$comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'author_id' => 'InsertUserID', 'ip_address' => 'InsertIPAddress', 'body' => array('Column' => 'Body', 'Filter' => array($this, 'cleanBodyBrackets')), 'body2' => array('Column' => 'Format', 'Filter' => array($this, 'guessFormat')), 'post_date' => array('Column' => 'DateInserted', 'Filter' => array($ex, 'timestampToDate')), 'post_edit_date' => array('Column' => 'DateUpdated', 'Filter' => array($ex, 'timestampToDate')), 'post_edit_author' => 'UpdateUserID');
$ex->exportTable('Comment', "\n SELECT\n 'Html' AS Format,\n p.body AS body2,\n p.*\n FROM forum_forum_posts p", $comment_Map);
// Media.
$media_Map = array('filename' => 'Name', 'extension' => array('Column' => 'Type', 'Filter' => 'mimeTypeFromExtension'), 'thumb_path' => array('Column' => 'ThumbPath', 'Filter' => array($this, 'filterThumbnailData')), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')), 'filesize' => 'Size', 'member_id' => 'InsertUserID', 'attachment_date' => array('Column' => 'DateInserted', 'Filter' => array($ex, 'timestampToDate')), 'filehash' => array('Column' => 'FileHash', 'Type' => 'varchar(100)'));
$ex->exportTable('Media', "\n SELECT\n concat('imported/', filename) AS Path,\n concat('imported/', filename) as thumb_path,\n 128 as thumb_width,\n CASE WHEN post_id > 0 THEN post_id ELSE topic_id END AS ForeignID,\n CASE WHEN post_id > 0 THEN 'comment' ELSE 'discussion' END AS ForeignTable,\n a.*\n FROM forum_forum_attachments a", $media_Map);
$ex->endExport();
}
/**
* Forum-specific export format.
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('messages');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Begin
$ex->beginExport('', 'SMF 2.*', array('HashMethod' => 'Django'));
// Users
$user_Map = array('id_member' => 'UserID', 'member_name' => 'Name', 'password' => 'Password', 'email_address' => 'Email', 'DateInserted' => 'DateInserted', 'timeOffset' => 'HourOffset', 'posts' => 'CountComments', 'Photo' => 'Photo', 'birthdate' => 'DateOfBirth', 'DateFirstVisit' => 'DateFirstVisit', 'DateLastActive' => 'DateLastActive', 'DateUpdated' => 'DateUpdated');
$ex->exportTable('User', "\n select m.*,\n from_unixtime(date_registered) as DateInserted,\n from_unixtime(date_registered) as DateFirstVisit,\n from_unixtime(last_login) as DateLastActive,\n from_unixtime(last_login) as DateUpdated,\n concat('sha1\$', lower(member_name), '\$', passwd) as `password`,\n if(m.avatar <> '', m.avatar, concat('attachments/', a.filename)) as Photo\n from :_members m\n left join :_attachments a on a.id_member = m.id_member ", $user_Map);
// Roles
$role_Map = array('id_group' => 'RoleID', 'group_name' => 'Name');
$ex->exportTable('Role', "select * from :_membergroups", $role_Map);
// UserRoles
$userRole_Map = array('id_member' => 'UserID', 'id_group' => 'RoleID');
$ex->exportTable('UserRole', "select * from :_members", $userRole_Map);
// Categories
$category_Map = array('Name' => array('Column' => 'Name', 'Filter' => array($this, 'decodeNumericEntity')));
$ex->exportTable('Category', "\n select\n (`id_cat` + 1000000) as `CategoryID`,\n `name` as `Name`,\n '' as `Description`,\n null as `ParentCategoryID`,\n `cat_order` as `Sort`\n from :_categories\n\n union\n\n select\n b.`id_board` as `CategoryID`,\n\n b.`name` as `Name`,\n b.`description` as `Description`,\n (CASE WHEN b.`id_parent` = 0 THEN (`id_cat` + 1000000) ELSE `id_parent` END) as `ParentCategoryID`,\n b.`board_order` as `Sort`\n from :_boards b\n\n ", $category_Map);
// Discussions
$discussion_Map = array('id_topic' => 'DiscussionID', 'subject' => array('Column' => 'Name', 'Filter' => array($this, 'decodeNumericEntity')), 'body' => array('Column' => 'Body'), 'Format' => 'Format', 'id_board' => 'CategoryID', 'DateInserted' => 'DateInserted', 'DateUpdated' => 'DateUpdated', 'id_member' => 'InsertUserID', 'DateLastComment' => 'DateLastComment', 'UpdateUserID' => 'UpdateUserID', 'locked' => 'Closed', 'isSticky' => 'Announce', 'CountComments' => 'CountComments', 'numViews' => 'CountViews', 'LastCommentUserID' => 'LastCommentUserID', 'id_last_msg' => 'LastCommentID');
$ex->exportTable('Discussion', "\n select t.*,\n (t.num_replies + 1) as CountComments,\n m.subject,\n m.body,\n from_unixtime(m.poster_time) as DateInserted,\n from_unixtime(m.modified_time) as DateUpdated,\n m.id_member,\n from_unixtime(m_end.poster_time) AS DateLastComment,\n m_end.id_member AS UpdateUserID,\n m_end.id_member AS LastCommentUserID,\n 'BBCode' as Format\n from :_topics t\n join :_messages as m on t.id_first_msg = m.id_msg\n join :_messages as m_end on t.id_last_msg = m_end.id_msg\n\n -- where t.spam = 0 AND m.spam = 0;\n\n ", $discussion_Map);
// Comments
$comment_Map = array('id_msg' => 'CommentID', 'id_topic' => 'DiscussionID', 'Format' => 'Format', 'body' => array('Column' => 'Body'), 'id_member' => 'InsertUserID', 'DateInserted' => 'DateInserted');
$ex->exportTable('Comment', "select m.*,\n from_unixtime(m.poster_time) AS DateInserted,\n 'BBCode' as Format\n from :_messages m\n join :_topics t on m.id_topic = t.id_topic\n where m.id_msg <> t.id_first_msg;\n ", $comment_Map);
// Media
$media_Map = array('ID_ATTACH' => 'MediaID', 'id_msg' => 'ForeignID', 'size' => 'Size', 'height' => 'ImageHeight', 'width' => 'ImageWidth', 'extract_mimetype' => array('Column' => 'Type', 'Filter' => function ($value, $field, $row) {
return $this->getMimeTypeFromFileName($row['Path']);
}), 'thumb_path' => array('Column' => 'ThumbPath', 'Filter' => array($this, 'filterThumbnailData')), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')));
$ex->exportTable('Media', "\n select a.*,\n concat('attachments/', a.filename) as Path,\n IF(b.filename is not null, concat('attachments/', b.filename), null) as thumb_path,\n null as extract_mimetype,\n b.width as thumb_width,\n if(t.id_topic is null, 'Comment', 'Discussion') as ForeignTable\n from :_attachments a\n left join :_attachments b on b.ID_ATTACH = a.ID_THUMB\n left join :_topics t on a.id_msg = t.id_first_msg\n where a.attachment_type = 0\n and a.id_msg > 0\n ", $media_Map);
// Conversations
$conversation_Map = array('id_pm_head' => 'ConversationID', 'subject' => 'Subject', 'id_member_from' => 'InsertUserID', 'unixmsgtime' => 'DateInserted');
$ex->exportTable('Conversation', "select\n pm.*,\n from_unixtime(pm.msgtime) as unixmsgtime\n from :_personal_messages pm\n ", $conversation_Map);
$convMsg_Map = array('id_pm' => 'MessageID', 'id_pm_head' => 'ConversationID', 'body' => 'Body', 'format' => 'Format', 'id_member_from' => 'InsertUserID', 'unixmsgtime' => 'DateInserted');
$ex->exportTable('ConversationMessage', "select\n pm.*,\n from_unixtime(pm.msgtime) as unixmsgtime ,\n 'BBCode' as format\n from :_personal_messages pm\n ", $convMsg_Map);
$userConv_Map = array('id_member2' => 'UserId', 'id_pm_head' => 'ConversationID', 'deleted2' => 'Deleted');
$ex->exportTable('UserConversation', "(select\n pm.id_member_from as id_member2,\n pm.id_pm_head,\n pm.deleted_by_sender as deleted2\n from :_personal_messages pm )\n UNION ALL\n (select\n pmr.id_member as id_member2,\n pm.id_pm_head,\n pmr.deleted as deleted2\n from :_personal_messages pm join :_pm_recipients pmr on pmr.id_pm = pm.id_pm\n )\n ", $userConv_Map);
// End
$ex->endExport();
}
/**
* @param ExportModel $ex
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('mbox');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->destPrefix = 'jos';
$ex->beginExport('', 'Joomla Kunena', array('HashMethod' => 'joomla'));
// User.
$user_Map = array('id' => 'UserID', 'name' => 'Name', 'email' => 'Email', 'registerDate' => 'DateInserted', 'lastvisitDate' => 'DateLastActive', 'password' => 'Password', 'showemail' => 'ShowEmail', 'birthdate' => 'DateOfBirth', 'banned' => 'Banned', 'admin' => array('Column' => 'Admin', 'Type' => 'tinyint(1)'), 'Photo' => 'Photo');
$ex->exportTable('User', "\n SELECT\n u.*,\n case when ku.avatar <> '' then concat('kunena/avatars/', ku.avatar) else null end as `Photo`,\n case u.usertype when 'superadministrator' then 1 else 0 end as admin,\n coalesce(ku.banned, 0) as banned,\n ku.birthdate,\n !ku.hideemail as showemail\n FROM :_users u\n left join :_kunena_users ku\n on ku.userid = u.id", $user_Map);
// Role.
$role_Map = array('rank_id' => 'RoleID', 'rank_title' => 'Name');
$ex->exportTable('Role', "select * from :_kunena_ranks", $role_Map);
// UserRole.
$userRole_Map = array('id' => 'UserID', 'rank' => 'RoleID');
$ex->exportTable('UserRole', "\n select *\n from :_users u", $userRole_Map);
// Permission.
// $ex->ExportTable('Permission',
// "select 2 as RoleID, 'View' as _Permissions
// union
// select 3 as RoleID, 'View' as _Permissions
// union
// select 16 as RoleID, 'All' as _Permissions", array('_Permissions' => array('Column' => '_Permissions', 'Type' => 'varchar(20)')));
// Category.
$category_Map = array('id' => 'CategoryID', 'parent' => 'ParentCategoryID', 'name' => 'Name', 'ordering' => 'Sort', 'description' => 'Description');
$ex->exportTable('Category', "\n select * from :_kunena_categories", $category_Map);
// Discussion.
$discussion_Map = array('id' => 'DiscussionID', 'catid' => 'CategoryID', 'userid' => 'InsertUserID', 'subject' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'time' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'ip' => 'InsertIPAddress', 'locked' => 'Closed', 'hits' => 'CountViews', 'modified_by' => 'UpdateUserID', 'modified_time' => array('Column' => 'DateUpdated', 'Filter' => 'timestampToDate'), 'message' => 'Body', 'Format' => 'Format');
$ex->exportTable('Discussion', "\n select\n t.*,\n txt.message,\n 'BBCode' as Format\n from :_kunena_messages t\n left join :_kunena_messages_text txt\n on t.id = txt.mesid\n where t.thread = t.id", $discussion_Map);
// Comment.
$comment_Map = array('id' => 'CommentID', 'thread' => 'DiscussionID', 'userid' => 'InsertUserID', 'time' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'ip' => 'InsertIPAddress', 'modified_by' => 'UpdateUserID', 'modified_time' => array('Column' => 'DateUpdated', 'Filter' => 'timestampToDate'), 'message' => 'Body', 'Format' => 'Format');
$ex->exportTable('Comment', "\n select\n t.*,\n txt.message,\n 'BBCode' as Format\n from :_kunena_messages t\n left join :_kunena_messages_text txt\n on t.id = txt.mesid\n where t.thread <> t.id", $comment_Map);
// UserDiscussion.
$userDiscussion_Map = array('thread' => 'DiscussionID', 'userid' => 'UserID');
$ex->exportTable('UserDiscussion', "\n select t.*, 1 as Bookmarked\n from :_kunena_subscriptions t", $userDiscussion_Map);
// Media.
$media_Map = array('id' => 'MediaID', 'mesid' => 'ForeignID', 'userid' => 'InsertUserID', 'size' => 'Size', 'path2' => array('Column' => 'Path', 'Filter' => 'urlDecode'), 'thumb_path' => array('Column' => 'ThumbPath', 'Filter' => array($this, 'filterThumbnailData')), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')), 'filetype' => 'Type', 'filename' => array('Column' => 'Name', 'Filter' => 'urlDecode'), 'time' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'));
$ex->exportTable('Media', "\n select\n a.*,\n concat(a.folder, '/', a.filename) as path2,\n case when m.id = m.thread then 'discussion' else 'comment' end as ForeignTable,\n m.time,\n concat(a.folder, '/', a.filename) as thumb_path,\n 128 as thumb_width\n from :_kunena_attachments a\n join :_kunena_messages m\n on m.id = a.mesid", $media_Map);
$ex->endExport();
}
/**
*
* @param ExportModel $ex
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('Post');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'Toast Forum');
$ex->sourcePrefix = 'tstdb_';
// User.
$user_Map = array('ID' => 'UserID', 'Username' => 'Name', 'Email' => 'Email', 'LastLoginDate' => array('Column' => 'DateLastActive', 'Type' => 'datetime'), 'IP' => 'LastIPAddress');
$ex->exportTable('User', "\n select\n *,\n NOW() as DateInserted\n from :_Member u", $user_Map);
// Determine safe RoleID to use for non-existant Member role
$lastRoleID = 1001;
$lastRoleResult = $ex->query("select max(ID) as LastID from :_Group");
if ($lastRoleResult) {
$lastRole = mysql_fetch_array($lastRoleResult);
$lastRoleID = $lastRole['LastID'] + 1;
}
// Role.
// Add default Member role.
$role_Map = array('ID' => 'RoleID', 'Name' => 'Name');
$ex->exportTable('Role', "\n select\n ID,\n Name\n from :_Group\n\n union all\n\n select\n {$lastRoleID} as ID,\n 'Member' as Name\n from :_Group;", $role_Map);
// UserRole.
// Users without roles get put into new Member role.
$userRole_Map = array('MemberID' => 'UserID', 'GroupID' => 'RoleID');
$ex->exportTable('UserRole', "\n select\n GroupID,\n MemberID\n from :_MemberGroupLink\n\n union all\n\n select\n {$lastRoleID} as GroupID,\n m.ID as MemberID\n from :_Member m\n left join :_MemberGroupLink l\n on l.MemberID = m.ID\n where l.GroupID is null", $userRole_Map);
// Signatures.
$ex->exportTable('UserMeta', "\n select\n ID as UserID,\n 'Plugin.Signatures.Sig' as `Name`,\n Signature as `Value`\n from :_Member\n where Signature <> ''\n\n union all\n\n select\n ID as UserID,\n 'Plugin.Signatures.Format' as `Name`,\n 'BBCode' as `Value`\n from :_Member\n where Signature <> '';");
// Category.
$category_Map = array('ID' => 'CategoryID', 'CategoryID' => 'ParentCategoryID', 'ForumName' => 'Name', 'Description' => 'Description');
$ex->exportTable('Category', "\n select\n f.ID,\n f.CategoryID * 1000 as CategoryID,\n f.ForumName,\n f.Description\n from :_Forum f\n\n union all\n\n select\n c.ID * 1000 as ID,\n -1 as CategoryID,\n c.Name as ForumName,\n null as Description\n from :_Category c;", $category_Map);
// Discussion.
$discussion_Map = array('ID' => 'DiscussionID', 'ForumID' => 'CategoryID', 'MemberID' => 'InsertUserID', 'PostDate' => 'DateInserted', 'ModifyDate' => 'DateUpdated', 'LastPostDate' => 'DateLastComment', 'Subject' => 'Name', 'Message' => 'Body', 'Hits' => 'CountViews', 'ReplyCount' => 'CountComments');
$ex->exportTable('Discussion', "\n select p.*,\n 'Html' as Format\n from :_Post p\n where p.Topic = 1\n and p.Deleted = 0;", $discussion_Map);
// Comment.
$comment_Map = array('ID' => 'CommentID', 'TopicID' => 'DiscussionID', 'MemberID' => 'InsertUserID', 'PostDate' => 'DateInserted', 'ModifyDate' => 'DateUpdated', 'Message' => 'Body');
$ex->exportTable('Comment', "\n select *,\n 'Html' as Format\n from :_Post p\n where Topic = 0 and Deleted = 0;", $comment_Map);
$ex->endExport();
}
/**
* Main export process.
*
* @param ExportModel $ex
* @see $_structures in ExportModel for allowed destination tables & columns.
*/
public function forumExport($ex)
{
// Get the characterset for the comments.
// Usually the comments table is the best target for this.
$characterSet = $ex->getCharacterSet('network6_nodes');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Reiterate the platform name here to be included in the porter file header.
$ex->beginExport('', 'AnswerHub');
$result = $ex->query("select c_reserved as lastID from :_id_generators where c_identifier = 'AUTHORITABLE'", true);
if ($row = mysql_fetch_assoc($result)) {
$lastID = $row['lastID'];
}
if (!isset($lastID)) {
die('Something went wrong :S' . PHP_EOL);
}
// User.
$user_Map = array('c_email' => array('Column' => 'Email', 'Filter' => array($this, 'generateEmail')));
$ex->exportTable('User', "\n select\n user.c_id as UserID,\n user.c_name as Name,\n sha2(concat(user.c_name, now()), 256) as Password,\n 'Reset' as HashMethod,\n user.c_creation_date as DateInserted,\n user.c_birthday as DateOfBirth,\n user.c_last_seen as DateLastActive,\n user_email.c_email,\n 0 as Admin\n from :_network6_authoritables as user\n left join :_network6_user_emails as user_email on user_email.c_user = user.c_id\n where user.c_type = 'user'\n and user.c_name != '\$\$ANON_USER\$\$'\n\n union all\n\n select\n su.c_id + {$lastID},\n su.c_username,\n sha2(concat(su.c_username, now()), 256),\n 'Reset',\n su.c_creation_date,\n null,\n null,\n su.c_email,\n 1 as Admin\n from :_system_users as su\n where su.c_active = 1\n\n ", $user_Map);
// Role.
$role_Map = array();
$ex->exportTable('Role', "\n select\n groups.c_id as RoleID,\n groups.c_name as Name,\n groups.c_description as Description\n from :_network6_authoritables as groups\n where groups.c_type = 'group'\n\n union all\n\n select\n {$lastID} + 1,\n 'System Administrator',\n 'System users from AnswerHub'\n from dual\n ", $role_Map);
// User Role.
$userRole_Map = array();
$ex->exportTable('UserRole', "\n select\n user_role.c_groups as RoleID,\n user_role.c_members as UserID\n from :_network6_authoritable_groups as user_role\n\n union all\n\n select\n {$lastID} + 1,\n su.c_id + {$lastID}\n from :_system_users as su\n where su.c_active = 1\n ", $userRole_Map);
// Category.
$category_Map = array();
$ex->exportTable('Category', "\n select\n containers.c_id as CategoryID,\n case\n when parents.c_type = 'space' then containers.c_parent\n else null\n end as ParentCategoryID,\n containers.c_name as Name\n from :_containers as containers\n left join :_containers as parents on parents.c_id = containers.c_parent\n where containers.c_type = 'space'\n and containers.c_active = 1\n ", $category_Map);
// Discussion.
$discussion_Map = array();
// The query works fine but it will probably be slow for big tables
$ex->exportTable('Discussion', "\n select\n questions.c_id as DiscussionID,\n 'Question' as Type,\n questions.c_primaryContainer as CategoryID,\n questions.c_author as InsertUserID,\n questions.c_creation_date as DateInserted,\n questions.c_title as Name,\n coalesce(nullif(questions.c_body, ''), questions.c_title) as Body,\n 'HTML' as Format,\n if(locate('[closed]', questions.c_normalized_state) > 0, 1, 0) as Closed,\n if(count(answers.c_id) > 0,\n if (locate('[accepted]', group_concat(ifnull(answers.c_normalized_state, ''))) = 0,\n if (locate('[rejected]', group_concat(ifnull(answers.c_normalized_state, ''))) = 0,\n 'Answered',\n 'Rejected'\n ),\n 'Accepted'\n ),\n 'Unanswered'\n ) as QnA\n from :_network6_nodes as questions\n\t left join :_network6_nodes as answers on\n\t answers.c_parent = questions.c_id\n\t and answers.c_type = 'answer'\n\t and answers.c_visibility = 'full'\n where questions.c_type = 'question'\n and questions.c_visibility = 'full'\n group by questions.c_id\n ", $discussion_Map);
// Comment.
$comment_Map = array();
$ex->exportTable('Comment', "\n select\n answers.c_id as CommentID,\n answers.c_parent as DiscussionID,\n answers.c_author as InsertUserID,\n answers.c_body as Body,\n 'Html' as Format,\n answers.c_creation_date as DateInserted,\n if(locate('[accepted]', answers.c_normalized_state) = 0,\n if(locate('[rejected]', answers.c_normalized_state) = 0,\n null,\n 'Rejected'\n ),\n 'Accepted'\n ) as QnA\n from :_network6_nodes as answers\n where answers.c_type = 'answer'\n and answers.c_visibility = 'full'\n ", $comment_Map);
$ex->endExport();
}
/**
* Export core Vanilla and Conversations tables.
*
* @since 2.0.0
* @access public
*/
public function export()
{
$this->permission('Garden.Export');
// This permission doesn't exist, so only users with Admin == '1' will succeed.
set_time_limit(60 * 2);
$Ex = new ExportModel();
$Ex->pdo(Gdn::database()->connection());
$Ex->Prefix = Gdn::database()->DatabasePrefix;
/// 2. Do the export. ///
$Ex->UseCompression = true;
$Ex->beginExport(PATH_ROOT . DS . 'uploads' . DS . 'export ' . date('Y-m-d His') . '.txt.gz', 'Vanilla 2.0');
$Ex->exportTable('User', 'select * from :_User');
// ":_" will be replace by database prefix
$Ex->exportTable('Role', 'select * from :_Role');
$Ex->exportTable('UserRole', 'select * from :_UserRole');
$Ex->exportTable('Category', 'select * from :_Category');
$Ex->exportTable('Discussion', 'select * from :_Discussion');
$Ex->exportTable('Comment', 'select * from :_Comment');
$Ex->exportTable('Conversation', 'select * from :_Conversation');
$Ex->exportTable('UserConversation', 'select * from :_UserConversation');
$Ex->exportTable('ConversationMessage', 'select * from :_ConversationMessage');
$Ex->endExport();
}
/**
*
* @param ExportModel $ex
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('Topic');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'Web Wiz Forums');
$ex->sourcePrefix = 'tbl';
// // Permissions.
// $Permission_Map = array(
// 'group_id' => 'RoleID',
// 'can_access_cp' => 'Garden.Settings.View',
// 'can_access_edit' => 'Vanilla.Discussions.Edit',
// 'can_edit_all_comments' => 'Vanilla.Comments.Edit',
// 'can_access_admin' => 'Garden.Settings.Manage',
// 'can_admin_members' => 'Garden.Users.Edit',
// 'can_moderate_comments' => 'Garden.Moderation.Manage',
// 'can_view_profiles' => 'Garden.Profiles.View',
// 'can_post_comments' => 'Vanilla.Comments.Add',
// 'can_view_online_system' => 'Vanilla.Discussions.View',
// 'can_sign_in' => 'Garden.SignIn.Allow',
// 'can_view_profiles3' => 'Garden.Activity.View',
// 'can_post_comments2' => 'Vanilla.Discussions.Add'
// );
// $Permission_Map = $ex->FixPermissionColumns($Permission_Map);
// foreach ($Permission_Map as $Column => &$Info) {
// if (is_array($Info) && isset($Info['Column']))
// $Info['Filter'] = array($this, 'Bool');
// }
//
// $ex->ExportTable('Permission', "
// select
// g.can_view_profiles as can_view_profiles2,
// g.can_view_profiles as can_view_profiles3,
// g.can_post_comments as can_post_comments2,
// g.can_post_comments as can_sign_in,
// case when can_access_admin = 'y' then 'all' when can_view_online_system = 'y' then 'view' end as _Permissions,
// g.*
// from forum_member_groups g
// ", $Permission_Map);
// User.
$user_Map = array('Author_ID' => 'UserID', 'Username' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'Real_name' => array('Column' => 'FullName', 'Type' => 'varchar(50)', 'Filter' => 'HTMLDecoder'), 'Password2' => 'Password', 'Gender2' => 'Gender', 'Author_email' => 'Email', 'Photo2' => array('Column' => 'Photo', 'Filter' => 'HTMLDecoder'), 'Login_IP' => 'LastIPAddress', 'Banned' => 'Banned', 'Join_date' => array('Column' => 'DateInserted'), 'Last_visit' => array('Column' => 'DateLastActive'), 'Location' => array('Column' => 'Location', 'Filter' => 'HTMLDecoder'), 'DOB' => 'DateOfBirth', 'Show_email' => 'ShowEmail');
$ex->exportTable('User', "\n select\n concat(Salt, '\$', Password) as Password2,\n case u.Gender when 'Male' then 'm' when 'Female' then 'f' else 'u' end as Gender2,\n case when Avatar like 'http%' then Avatar when Avatar > '' then concat('webwiz/', Avatar) else null end as Photo2,\n 'webwiz' as HashMethod,\n u.*\n from :_Author u\n ", $user_Map);
// Role.
$role_Map = array('Group_ID' => 'RoleID', 'Name' => 'Name');
$ex->exportTable('Role', "\n select *\n from :_Group", $role_Map);
// User Role.
$userRole_Map = array('Author_ID' => 'UserID', 'Group_ID' => 'RoleID');
$ex->exportTable('UserRole', "\n select u.*\n from :_Author u", $userRole_Map);
// UserMeta
$ex->exportTable('UserMeta', "\n select\n Author_ID as UserID,\n 'Plugin.Signatures.Sig' as `Name`,\n Signature as `Value`\n from :_Author\n where Signature <> ''");
// Category.
$category_Map = array('Forum_ID' => 'CategoryID', 'Forum_name' => 'Name', 'Forum_description' => 'Description', 'Parent_ID' => 'ParentCategoryID', 'Forum_order' => 'Sort');
$ex->exportTable('Category', "\n select\n f.Forum_ID,\n f.Cat_ID * 1000 as Parent_ID,\n f.Forum_order,\n f.Forum_name,\n f.Forum_description\n from :_Forum f\n\n union all\n\n select\n c.Cat_ID * 1000,\n null,\n c.Cat_order,\n c.Cat_name,\n null\n from :_Category c\n ", $category_Map);
// Discussion.
$discussion_Map = array('Topic_ID' => 'DiscussionID', 'Forum_ID' => 'CategoryID', 'Author_ID' => 'InsertUserID', 'Subject' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'IP_addr' => 'InsertIPAddress', 'Message' => array('Column' => 'Body'), 'Format' => 'Format', 'Message_date' => array('Column' => 'DateInserted'), 'No_of_views' => 'CountViews', 'Locked' => 'Closed');
$ex->exportTable('Discussion', "\n select\n th.Author_ID,\n th.Message,\n th.Message_date,\n th.IP_addr,\n 'Html' as Format,\n t.*\n from :_Topic t\n join :_Thread th\n on t.Start_Thread_ID = th.Thread_ID", $discussion_Map);
// Comment.
$comment_Map = array('Thread_ID' => 'CommentID', 'Topic_ID' => 'DiscussionID', 'Author_ID' => 'InsertUserID', 'IP_addr' => 'InsertIPAddress', 'Message' => array('Column' => 'Body'), 'Format' => 'Format', 'Message_date' => array('Column' => 'DateInserted'));
$ex->exportTable('Comment', "\n select\n th.*,\n 'Html' as Format\n from :_Thread th\n join :_Topic t\n on t.Topic_ID = th.Topic_ID\n where th.Thread_ID <> t.Start_Thread_ID", $comment_Map);
$this->exportConversations();
$ex->endExport();
}
/**
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('topic');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'NodeBB 0.*', array('HashMethod' => 'Vanilla'));
// Users
$user_Map = array('uid' => 'UserID', 'username' => 'Name', 'password' => 'Password', 'email' => 'Email', 'confirmed' => 'Confirmed', 'showemail' => 'ShowEmail', 'joindate' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'lastonline' => array('Column' => 'DateLastActive', 'Filter' => array($this, 'tsToDate')), 'lastposttime' => array('Column' => 'DateUpdated', 'Filter' => array($this, 'tsToDate')), 'banned' => 'Banned', 'admin' => 'Admin', 'hm' => 'HashMethod');
$ex->exportTable('User', "\n\n select uid, username, password, email, `email:confirmed` as confirmed, showemail, joindate, lastonline, lastposttime, banned, 0 as admin, 'crypt' as hm\n from :_user\n\n ", $user_Map);
//Roles
$role_Map = array('_num' => 'RoleID', '_key' => array('Column' => 'Name', 'Filter' => array($this, 'roleNameFromKey')), 'description' => 'Description');
$ex->exportTable('Role', "\n\n select gm._key as _key, gm._num as _num, g.description as description\n from :_group_members gm left join :_group g\n on gm._key like concat(g._key, '%')\n\n ", $role_Map);
$userRole_Map = array('id' => 'RoleID', 'members' => 'UserID');
$ex->exportTable('UserRole', "\n\n select *, g._num as id\n from :_group_members g join :_group_members__members m\n on g._id = m._parentid\n\n ", $userRole_Map);
// Signatutes.
$userMeta_Map = array('uid' => 'UserID', 'name' => 'Name', 'signature' => 'Value');
$ex->exportTable('UserMeta', "\n\n select uid, 'Plugin.Signatures.Sig' as name, signature\n from :_user\n where length(signature) > 1\n\n union\n\n select uid, 'Plugin.Signatures.Format', 'Markdown'\n from :_user\n where length(signature) > 1\n\n union\n\n select uid, 'Profile.Website' as name, website\n from :_user\n where length(website) > 7\n\n union\n\n select uid, 'Profile.Location' as name, location\n from :_user\n where length(location) > 1\n\n ", $userMeta_Map);
// Categories
$category_Map = array('cid' => 'CategoryID', 'name' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'description' => 'Description', 'order' => 'Sort', 'parentCid' => 'ParentCategoryID', 'slug' => array('Column' => 'UrlCode', 'Filter' => array($this, 'removeNumId')), 'image' => 'Photo', 'disabled' => 'Archived');
$ex->exportTable('Category', "\n\n select *\n from :_category\n\n ", $category_Map);
if (!$ex->indexExists('z_idx_topic', ':_topic')) {
$ex->query("create index z_idx_topic on :_topic(mainPid);");
}
if (!$ex->indexExists('z_idx_post', ':_post')) {
$ex->query("create index z_idx_post on :_post(pid);");
}
if (!$ex->indexExists('z_idx_poll', ':_poll')) {
$ex->query("create index z_idx_poll on :_poll(tid);");
}
$ex->query("drop table if exists z_discussionids;");
$ex->query("\n\n create table z_discussionids (\n tid int unsigned,\n primary key(tid)\n );\n\n ");
$ex->query("\n\n insert ignore z_discussionids (\n tid\n )\n select mainPid\n from :_topic\n where mainPid is not null\n and deleted != 1;\n\n ");
$ex->query("drop table if exists z_reactiontotalsupvote;");
$ex->query("\n\n create table z_reactiontotalsupvote (\n value varchar(50),\n total int,\n primary key (value)\n );\n\n ");
$ex->query("drop table if exists z_reactiontotalsdownvote;");
$ex->query("\n\n create table z_reactiontotalsdownvote (\n value varchar(50),\n total int,\n primary key (value)\n );\n\n ");
$ex->query("drop table if exists z_reactiontotals;");
$ex->query("\n\n create table z_reactiontotals (\n value varchar(50),\n upvote int,\n downvote int,\n primary key (value)\n );\n\n ");
$ex->query("\n\n insert z_reactiontotalsupvote\n select value, count(*) as totals\n from :_uid_upvote\n group by value;\n\n ");
$ex->query("\n\n insert z_reactiontotalsdownvote\n select value, count(*) as totals\n from :_uid_downvote\n group by value;\n\n ");
$ex->query("\n\n insert z_reactiontotals\n select *\n from (\n select u.value, u.total as up, d.total as down\n from z_reactiontotalsupvote u\n left join z_reactiontotalsdownvote d\n on u.value = d.value\n\n union\n\n select d.value, u.total as up, d.total as down\n from z_reactiontotalsdownvote d\n left join z_reactiontotalsupvote u\n on u.value = d.value\n ) as reactions\n\n ");
//Discussions
$discussion_Map = array('tid' => 'DiscussionID', 'cid' => 'CategoryID', 'title' => 'Name', 'content' => 'Body', 'uid' => 'InsertUserID', 'locked' => 'Closed', 'pinned' => 'Announce', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'edited' => array('Column' => 'DateUpdated', 'Filter' => array($this, 'tsToDate')), 'editor' => 'UpdateUserID', 'viewcount' => 'CountViews', 'format' => 'Format', 'votes' => 'Score', 'attributes' => array('Column' => 'Attributes', 'Filter' => array($this, 'serializeReactions')), 'poll' => array('Column' => 'Type', 'Filter' => array($this, 'isPoll')));
$ex->exportTable('Discussion', "\n\n select p.tid, cid, title, content, p.uid, locked, pinned, p.timestamp, p.edited, p.editor, viewcount, votes, poll._id as poll, 'Markdown' as format, concat(ifnull(u.total, 0), ':', ifnull(d.total, 0)) as attributes\n from :_topic t\n left join :_post p\n on t.mainPid = p.pid\n left join z_reactiontotalsupvote u\n on u.value = t.mainPid\n left join z_reactiontotalsdownvote d\n on d.value = t.mainPid\n left join :_poll poll\n on p.tid = poll.tid\n where t.deleted != 1\n\n ", $discussion_Map);
$ex->query("drop table if exists z_comments;");
$ex->query("\n\n create table z_comments (\n pid int,\n content text,\n uid varchar(255),\n tid varchar(255),\n timestamp double,\n edited varchar(255),\n editor varchar(255),\n votes int,\n upvote int,\n downvote int,\n primary key(pid)\n );\n\n ");
$ex->query("\n\n insert ignore z_comments (\n pid,\n content,\n uid,\n tid,\n timestamp,\n edited,\n editor,\n votes\n )\n select p.pid, p.content, p.uid, p.tid, p.timestamp, p.edited, p.editor, p.votes\n from :_post p\n left join z_discussionids t\n on t.tid = p.pid\n where p.deleted != 1 and t.tid is null;\n\n ");
$ex->query("\n\n update z_comments as c\n join z_reactiontotals r\n on r.value = c.pid\n set c.upvote = r.upvote, c.downvote = r.downvote;\n\n ");
// Comments
$comment_Map = array('content' => 'Body', 'uid' => 'InsertUserID', 'tid' => 'DiscussionID', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'edited' => array('Column' => 'DateUpdated', 'Filter' => array($this, 'tsToDate')), 'editor' => 'UpdateUserID', 'votes' => 'Score', 'format' => 'Format', 'attributes' => array('Column' => 'Attributes', 'Filter' => array($this, 'serializeReactions')));
$ex->exportTable('Comment', "\n\n select content, uid, tid, timestamp, edited, editor, votes, 'Markdown' as format, concat(ifnull(upvote, 0), ':', ifnull(downvote, 0)) as attributes\n from z_comments\n\n ", $comment_Map);
//Polls
$poll_Map = array('pollid' => 'PollID', 'title' => 'Name', 'tid' => 'DiscussionID', 'votecount' => 'CountVotes', 'uid' => 'InsertUserID', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')));
$ex->exportTable('Poll', "\n\n select *\n from :_poll p left join :_poll_settings ps\n on ps._key like concat(p._key, ':', '%')\n\n ", $poll_Map);
$pollOption_Map = array('_num' => 'PollOptionID', '_key' => array('Column' => 'PollID', 'Filter' => array($this, 'idFromKey')), 'title' => 'Body', 'sort' => 'Sort', 'votecount' => array('Column' => 'CountVotes', 'Filter' => array($this, 'makeNullZero')), 'format' => 'Format');
$ex->exportTable('PollOption', "\n\n select _num, _key, title, id+1 as sort, votecount, 'Html' as format\n from :_poll_options\n where title is not null\n\n ", $pollOption_Map);
$pollVote_Map = array('userid' => 'UserID', 'poll_option_id' => 'PollOptionID');
$ex->exportTable('PollVote', "\n\n select povm.members as userid, po._num as poll_option_id\n from :_poll_options_votes__members povm\n left join :_poll_options_votes pov\n on povm._parentid = pov._id\n left join :_poll_options po\n on pov._key like concat(po._key, ':', '%')\n where po.title is not null\n\n ", $pollVote_Map);
//Tags
if (!$ex->indexExists('z_idx_topic_key', ':_topic')) {
$ex->query("create index z_idx_topic_key on :_topic (_key);");
}
$tag_Map = array('slug' => array('Column' => 'Name', 'Filter' => array($this, 'nameToSlug')), 'fullname' => 'FullName', 'count' => 'CountDiscussions', 'tagid' => 'TagID', 'cid' => 'CategoryID', 'type' => 'Type', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'uid' => 'InsertUserID');
$now = time();
$ex->query("set @rownr=1000;");
$ex->exportTable('Tag', "\n\n select @rownr:=@rownr+1 as tagid, members as fullname, members as slug, '' as type, count, timestamp, uid, cid\n from (\n select members, count(*) as count, _parentid\n from :_topic_tags__members\n group by members\n ) as tags\n join :_topic_tags tt\n on tt._id = _parentid\n left join :_topic t\n on substring(tt._key, 1, length(tt._key) - 5) = t._key\n\n ", $tag_Map);
$tagDiscussion_Map = array('tagid' => 'TagID', 'tid' => 'DiscussionID', 'cid' => 'CategoryID', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')));
$ex->query("set @rownr=1000;");
$ex->exportTable('TagDiscussion', "\n\n select tagid, cid, tid, timestamp\n from :_topic_tags__members two\n join (\n select @rownr:=@rownr+1 as tagid, members as fullname, members as slug, count\n from (\n select members, count(*) as count\n from :_topic_tags__members\n group by members\n ) as tags\n ) as tagids\n on two.members = tagids.fullname\n join :_topic_tags tt\n on tt._id = _parentid\n left join :_topic t\n on substring(tt._key, 1, length(tt._key) - 5) = t._key\n\n ", $tagDiscussion_Map);
//Conversations
if (!$ex->indexExists('z_idx_message_key', ':_message')) {
$ex->query("create index z_idx_message_key on :_message(_key);");
}
$ex->query("drop table if exists z_pmto;");
$ex->query("\n\n create table z_pmto (\n pmid int unsigned,\n userid int,\n groupid int,\n primary key(pmid, userid)\n );\n\n ");
$ex->query("\n\n insert ignore z_pmto (\n pmid,\n userid\n )\n select substring_index(_key, ':', -1), fromuid\n from :_message;\n\n ");
$ex->query("\n\n insert ignore z_pmto (\n pmid,\n userid\n )\n select substring_index(_key, ':', -1), touid\n from :_message;\n\n ");
$ex->query("drop table if exists z_pmto2;");
$ex->query("\n\n create table z_pmto2 (\n pmid int unsigned,\n userids varchar(250),\n groupid int unsigned,\n primary key (pmid)\n );\n\n ");
$ex->query("\n\n replace z_pmto2 (\n pmid,\n userids\n )\n select pmid, group_concat(userid order by userid)\n from z_pmto\n group by pmid;\n\n ");
$ex->query("drop table if exists z_pmgroup;");
$ex->query("\n\n create table z_pmgroup (\n userids varchar(250),\n groupid varchar(255),\n firstmessageid int,\n lastmessageid int,\n countmessages int,\n primary key (userids, groupid)\n );\n\n ");
$ex->query("\n\n insert z_pmgroup\n select userids, concat('message:', min(pmid)), min(pmid), max(pmid), count(*)\n from z_pmto2\n group by userids;\n\n ");
$ex->query("\n\n update z_pmto2 as p\n left join z_pmgroup g\n on p.userids = g.userids\n set p.groupid = g.firstmessageid;\n\n ");
$ex->query("\n\n update z_pmto as p\n left join z_pmto2 p2\n on p.pmid = p2.pmid\n set p.groupid = p2.groupid;\n\n ");
$ex->query("create index z_idx_pmto_cid on z_pmto(groupid);");
$ex->query("create index z_idx_pmgroup_cid on z_pmgroup(firstmessageid);");
$conversation_Map = array('conversationid' => 'ConversationID', 'firstmessageid' => 'FirstMessageID', 'lastmessageid' => 'LastMessageID', 'countparticipants' => 'CountParticipants', 'countmessages' => 'CountMessages');
$ex->exportTable('Conversation', "\n\n select *, firstmessageid as conversationid, 2 as countparticipants\n from z_pmgroup\n left join :_message\n on groupid = _key;\n\n ", $conversation_Map);
$conversationMessage_Map = array('messageid' => 'MessageID', 'conversationid' => 'ConversationID', 'content' => 'Body', 'format' => 'Format', 'fromuid' => 'InsertUserID', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')));
$ex->exportTable('ConversationMessage', "\n\n select groupid as conversationid, pmid as messageid, content, 'Text' as format, fromuid, timestamp\n from z_pmto2\n left join :_message\n on concat('message:', pmid) = _key\n\n ", $conversationMessage_Map);
$userConversationMap = array('conversationid' => 'ConversationID', 'userid' => 'UserID', 'lastmessageid' => 'LastMessageID');
$ex->exportTable('UserConversation', "\n\n select p.groupid as conversationid, userid, lastmessageid\n from z_pmto p\n left join z_pmgroup\n on firstmessageid = p.groupid;\n\n ", $userConversationMap);
//Bookmarks (watch)
$userDiscussion_Map = array('members' => 'UserID', '_key' => array('Column' => 'DiscussionID', 'Filter' => array($this, 'idFromKey')), 'bookmarked' => 'Bookmarked');
$ex->exportTable('UserDiscussion', "\n select members, _key, 1 as bookmarked\n from :_tid_followers__members\n left join :_tid_followers\n on _parentid = _id\n ", $userDiscussion_Map);
//Reactions
if (!$ex->indexExists('z_idx_topic_mainpid', ':_topic')) {
$ex->query("create index z_idx_topic_mainpid on :_topic(mainPid);");
}
if (!$ex->indexExists('z_idx_uid_downvote', ':_uid_downvote')) {
$ex->query("create index z_idx_uid_downvote on :_uid_downvote(value);");
}
if (!$ex->indexExists('z_idx_uid_upvote', ':_uid_upvote')) {
$ex->query("create index z_idx_uid_upvote on :_uid_upvote(value);");
}
$userTag_Map = array('tagid' => 'TagID', 'recordtype' => 'RecordType', '_key' => array('Column' => 'UserID', 'Filter' => array($this, 'idFromKey')), 'value' => 'RecordID', 'score' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'total' => 'Total');
$ex->exportTable('UserTag', "\n\n select 11 as tagid, 'Discussion' as recordtype, u._key, u.value, score, total\n from :_uid_upvote u\n left join z_discussionids t\n on u.value = t.tid\n left join z_reactiontotalsupvote r\n on r.value = u.value\n where u._key != 'uid:NaN:upvote'\n and t.tid is not null\n\n union\n\n select 11 as tagid, 'Comment' as recordtype, u._key, u.value, score, total\n from :_uid_upvote u\n left join z_discussionids t\n on u.value = t.tid\n left join z_reactiontotalsupvote r\n on r.value = u.value\n where u._key != 'uid:NaN:upvote'\n and t.tid is null\n\n union\n\n select 10 as tagid, 'Discussion' as recordtype, u._key, u.value, score, total\n from :_uid_downvote u\n left join z_discussionids t\n on u.value = t.tid\n left join z_reactiontotalsdownvote r\n on r.value = u.value\n where u._key != 'uid:NaN:downvote'\n and t.tid is not null\n\n union\n\n select 10 as tagid, 'Comment' as recordtype, u._key, u.value, score, total\n from :_uid_downvote u\n left join z_discussionids t\n on u.value = t.tid\n left join z_reactiontotalsdownvote r\n on r.value = u.value\n where u._key != 'uid:NaN:downvote'\n and t.tid is null\n\n ", $userTag_Map);
//TODO: Permissions
$ex->endExport();
}
/**
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('comment');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Begin
$ex->beginExport('', 'Drupal');
// Users
$user_Map = array('uid' => 'UserID', 'name' => 'Name', 'Password' => 'Password', 'mail' => 'Email', 'photo' => 'Photo', 'created' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'login' => array('Column' => 'DateLastActive', 'Filter' => 'timestampToDate'));
$ex->exportTable('User', "\n select u.*,\n nullif(concat('drupal/', u.picture), 'drupal/') as photo,\n concat('md5\$\$', u.pass) as Password,\n 'Django' as HashMethod\n from :_users u\n where uid > 0", $user_Map);
// Signatures.
$userMeta_Map = array('uid' => 'UserID', 'Name' => 'Name', 'signature' => 'Value');
$ex->exportTable('UserMeta', "\n select u.*, 'Plugins.Signatures.Sig' as Name\n from :_users u\n where uid > 0", $userMeta_Map);
// Roles.
$role_Map = array('rid' => 'RoleID', 'name' => 'Name');
$ex->exportTable('Role', "select r.* from :_role r", $role_Map);
// User Role.
$userRole_Map = array('uid' => 'UserID', 'rid' => 'RoleID');
$ex->exportTable('UserRole', "\n select * from :_users_roles", $userRole_Map);
// Categories (sigh)
$category_Map = array('tid' => 'CategoryID', 'name' => 'Name', 'description' => 'description', 'parent' => 'ParentCategoryID');
$ex->exportTable('Category', "\n select t.*, nullif(h.parent, 0) as parent\n from :_term_data t\n join :_term_hierarchy h\n on t.tid = h.tid", $category_Map);
// Discussions.
$discussion_Map = array('nid' => 'DiscussionID', 'title' => 'Name', 'body' => 'Body', 'uid' => 'InsertUserID', 'created' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'DateUpdated' => array('Column' => 'DateUpdated', 'Filter' => 'timestampToDate'), 'sticky' => 'Announce', 'tid' => 'CategoryID');
$ex->exportTable('Discussion', "\n select n.*, nullif(n.changed, n.created) as DateUpdated, f.tid, r.body\n from nodeforum f\n left join node n\n on f.nid = n.nid\n left join node_revisions r\n on r.nid = n.nid", $discussion_Map);
// Comments.
$comment_Map = array('cid' => 'CommentID', 'uid' => 'InsertUserID', 'body' => array('Column' => 'Body'), 'hostname' => 'InsertIPAddress', 'created' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'));
$ex->exportTable('Comment', "\n select\n n.created,\n n.uid,\n r.body,\n c.nid as DiscussionID,\n n.title,\n 'Html' as Format,\n nullif(n.changed, n.created) as DateUpdated\n from node n\n left join node_comments c\n on c.cid = n.nid\n left join node_revisions r\n on r.nid = n.nid\n where n.type = 'forum_reply'", $comment_Map);
// Conversations.
$conversation_Map = array('thread_id' => 'ConversationID', 'author' => 'InsertUserID', 'title' => 'Subject');
$ex->exportTable('Conversation', "\n select\n pmi.thread_id,\n pmm.author,\n pmm.subject as title,\n FROM_UNIXTIME(pmm.timestamp) as DateInserted\n from pm_message as pmm\n inner join pm_index as pmi on pmi.mid = pmm.mid and pmm.author = pmi.uid and pmi.deleted = 0 and pmi.uid > 0\n group by pmi.thread_id\n ;", $conversation_Map);
// Conversation Messages.
$conversationMessage_Map = array('mid' => 'MessageID', 'thread_id' => 'ConversationID', 'author' => 'InsertUserID');
$ex->exportTable('ConversationMessage', "\n select\n pmm.mid,\n pmi.thread_id,\n pmm.author,\n FROM_UNIXTIME(pmm.timestamp) as DateInserted,\n pmm.body as Body,\n 'Html' as Format\n from pm_message as pmm\n inner join pm_index as pmi on pmi.mid = pmm.mid AND pmi.deleted = 0 and pmi.uid > 0\n ;", $conversationMessage_Map);
// User Conversation.
$userConversation_Map = array('uid' => 'UserID', 'thread_id' => 'ConversationID');
$ex->exportTable('UserConversation', "\n select\n pmi.uid,\n pmi.thread_id,\n 0 as Deleted\n from pm_index as pmi\n inner join pm_message as pmm ON pmm.mid = pmi.mid\n where pmi.deleted = 0\n and pmi.uid > 0\n group by\n pmi.uid,\n pmi.thread_id\n ;", $userConversation_Map);
// Comments.
/*$comment_Map = array(
'cid' => 'CommentID',
'nid' => 'DiscussionID',
'uid' => 'InsertUserID',
'comment' => array('Column' => 'Body'),
'hostname' => 'InsertIPAddress',
'timeatamp' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate')
);
$ex->ExportTable('Comment', "
select c.*,
n.title,
'Html' as Format
from comments c
join node n
on c.nid = n.nid", $comment_Map);
*/
// Media.
/*$Media_Map = array(
'fid' => 'MediaID',
'nid' => 'ForeignID',
'filename' => 'Name',
'path' => 'Path',
'filemime' => 'Type',
'filesize' => 'Size',
'uid' => 'InsertUserID',
'created' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate')
);
$ex->ExportTable('Media', "
select f.*,
nullif(concat('drupal/', f.filepath), 'drupal/') as path,
n.uid,
n.created,
'discussion' as ForeignTable
from files f
join node n
on f.nid = n.nid
where n.type = 'forum'", $Media_Map);
*/
$ex->endExport();
}
/**
* Forum-specific export format.
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('mbox');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Begin
$ex->beginExport('', 'Mbox', array());
// Temporary user table
$ex->query('create table :_mbox_user (UserID int AUTO_INCREMENT, Name varchar(255), Email varchar(255), PRIMARY KEY (UserID))');
$result = $ex->query('select Sender from :_mbox group by Sender', true);
// Users, pt 1: Build ref array; Parse name & email out - strip quotes, <, >
$users = array();
while ($row = mysql_fetch_assoc($result)) {
// Most senders are "Name <Email>"
$nameParts = explode('<', trim($row['Sender'], '"'));
// Sometimes the sender is just <email>
if ($nameParts[0] == '') {
$name = trim(str_replace('>', '', $nameParts[1]));
} else {
$name = trim(str_replace('\\', '', $nameParts[0]));
}
if (strstr($name, '@') !== false) {
// Only wound up with an email
$name = explode('@', $name);
$name = $name[0];
}
$email = $this->parseEmail($row['Sender']);
// Compile by unique email
$users[$email] = $name;
}
// Users, pt 2: loop thru unique emails
foreach ($users as $email => $name) {
$ex->query('insert into :_mbox_user (Name, Email)
values ("' . mysql_real_escape_string($name) . '", "' . mysql_real_escape_string($email) . '")');
$userID = mysql_insert_id();
// Overwrite user list with new UserID instead of name
$users[$email] = $userID;
}
// Temporary category table
$ex->query('create table :_mbox_category (CategoryID int AUTO_INCREMENT, Name varchar(255),
PRIMARY KEY (CategoryID))');
$result = $ex->query('select Folder from :_mbox group by Folder', true);
// Parse name out & build ref array
$categories = array();
while ($row = mysql_fetch_assoc($result)) {
$ex->query('insert into :_mbox_category (Name)
values ("' . mysql_real_escape_string($row["Folder"]) . '")');
$categoryID = mysql_insert_id();
$categories[$row["Folder"]] = $categoryID;
}
// Temporary post table
$ex->query('create table :_mbox_post (PostID int AUTO_INCREMENT, DiscussionID int,
IsDiscussion tinyint default 0, InsertUserID int, Name varchar(255), Body text, DateInserted datetime,
CategoryID int, PRIMARY KEY (PostID))');
$result = $ex->query('select * from :_mbox', true);
// Parse name, body, date, userid, categoryid
while ($row = mysql_fetch_assoc($result)) {
// Assemble posts into a format we can actually export.
// Subject: trim quotes, 're: ', 'fwd: ', 'fw: ', [category]
$name = trim(preg_replace('#^(re:)|(fwd?:) #i', '', trim($row['Subject'], '"')));
$name = trim(preg_replace('#^\\[[0-9a-zA-Z_-]*] #', '', $name));
$email = $this->parseEmail($row['Sender']);
$userID = isset($users[$email]) ? $users[$email] : 0;
$ex->query('insert into :_mbox_post (Name, InsertUserID, CategoryID, DateInserted, Body)
values ("' . mysql_real_escape_string($name) . '",
' . $userID . ',
' . $categories[$row['Folder']] . ',
from_unixtime(' . strtotime($row['Date']) . '),
"' . mysql_real_escape_string($this->parseBody($row['Body'])) . '")');
}
// Decide which posts are OPs
$result = $ex->query('select PostID from (select * from :_mbox_post order by DateInserted asc) x group by Name', true);
$discussions = array();
while ($row = mysql_fetch_assoc($result)) {
$discussions[] = $row['PostID'];
}
$ex->query('update :_mbox_post set IsDiscussion = 1 where PostID in (' . implode(",", $discussions) . ')');
// Thread the comments
$result = $ex->query('select c.PostID, d.PostID as DiscussionID from :_mbox_post c
left join :_mbox_post d on c.Name like d.Name and d.IsDiscussion = 1
where c.IsDiscussion = 0', true);
while ($row = mysql_fetch_assoc($result)) {
$ex->query('update :_mbox_post set DiscussionID = ' . $row['DiscussionID'] . ' where PostID = ' . $row['PostID']);
}
// Users
$user_Map = array();
$ex->exportTable('User', "\n select u.*,\n NOW() as DateInserted,\n 'Reset' as HashMethod\n from :_mbox_user u", $user_Map);
// Categories
$category_Map = array();
$ex->exportTable('Category', "\n select *\n from :_mbox_category", $category_Map);
// Discussions
$discussion_Map = array('PostID' => 'DiscussionID');
$ex->exportTable('Discussion', "\n select p.PostID, p.DateInserted, p.Name, p.Body, p.InsertUserID, p.CategoryID,\n 'Html' as Format\n from :_mbox_post p where IsDiscussion = 1", $discussion_Map);
// Comments
$comment_Map = array('PostID' => 'CommentID');
$ex->exportTable('Comment', "select p.*,\n 'Html' as Format\n from :_mbox_post p\n where IsDiscussion = 0", $comment_Map);
// Remove Temporary tables
//$ex->Query('drop table :_mbox_post');
//$ex->Query('drop table :_mbox_category');
//$ex->Query('drop table :_mbox_user');
// End
$ex->endExport();
// echo implode("\n\n", $ex->Queries);
}
/**
* Forum-specific export format.
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('posts_text');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->sourcePrefix = 'phpbb_';
// Begin
$ex->beginExport('', 'phpBB 2.*', array('HashMethod' => 'phpBB'));
// Users
$user_Map = array('user_id' => 'UserID', 'username' => 'Name', 'user_password' => 'Password', 'user_email' => 'Email', 'user_timezone' => 'HourOffset', 'user_posts' => array('Column' => 'CountComments', 'Type' => 'int'));
$ex->exportTable('User', "select *,\n FROM_UNIXTIME(nullif(user_regdate, 0)) as DateFirstVisit,\n FROM_UNIXTIME(nullif(user_lastvisit, 0)) as DateLastActive,\n FROM_UNIXTIME(nullif(user_regdate, 0)) as DateInserted\n from :_users", $user_Map);
// ":_" will be replace by database prefix
// Roles
$role_Map = array('group_id' => 'RoleID', 'group_name' => 'Name', 'group_description' => 'Description');
// Skip single-user groups
$ex->exportTable('Role', 'select * from :_groups where group_single_user = 0', $role_Map);
// UserRoles
$userRole_Map = array('user_id' => 'UserID', 'group_id' => 'RoleID');
// Skip pending memberships
$ex->exportTable('UserRole', '
select
user_id,
group_id
from :_user_group
where user_pending = 0
;', $userRole_Map);
// Categories
$category_Map = array('id' => 'CategoryID', 'cat_title' => 'Name', 'description' => 'Description', 'parentid' => 'ParentCategoryID');
$ex->exportTable('Category', "select\n c.cat_id * 1000 as id,\n c.cat_title,\n c.cat_order * 1000 as Sort,\n null as parentid,\n '' as description\nfrom :_categories c\n\nunion all\n\nselect\n f.forum_id,\n f.forum_name,\n c.cat_order * 1000 + f.forum_order,\n c.cat_id * 1000 as parentid,\n f.forum_desc\nfrom :_forums f\nleft join :_categories c\n on f.cat_id = c.cat_id", $category_Map);
// Discussions
$discussion_Map = array('topic_id' => 'DiscussionID', 'forum_id' => 'CategoryID', 'topic_poster' => 'InsertUserID', 'topic_title' => 'Name', 'Format' => 'Format', 'topic_views' => 'CountViews');
$ex->exportTable('Discussion', "select t.*,\n 'BBCode' as Format,\n case t.topic_status when 1 then 1 else 0 end as Closed,\n case t.topic_type when 1 then 1 else 0 end as Announce,\n FROM_UNIXTIME(t.topic_time) as DateInserted\n from :_topics t", $discussion_Map);
// Comments
$comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'post_text' => array('Column' => 'Body', 'Filter' => array($this, 'removeBBCodeUIDs')), 'Format' => 'Format', 'poster_id' => 'InsertUserID');
$ex->exportTable('Comment', "select p.*, pt.post_text, pt.bbcode_uid,\n 'BBCode' as Format,\n FROM_UNIXTIME(p.post_time) as DateInserted,\n FROM_UNIXTIME(nullif(p.post_edit_time,0)) as DateUpdated\n from :_posts p inner join :_posts_text pt on p.post_id = pt.post_id", $comment_Map);
// Conversations tables.
$ex->query("drop table if exists z_pmto;");
$ex->query("create table z_pmto (\nid int unsigned,\nuserid int unsigned,\nprimary key(id, userid));");
$ex->query("insert ignore z_pmto (id, userid)\nselect privmsgs_id, privmsgs_from_userid\nfrom :_privmsgs;");
$ex->query("insert ignore z_pmto (id, userid)\nselect privmsgs_id, privmsgs_to_userid\nfrom :_privmsgs;");
$ex->query("drop table if exists z_pmto2;");
$ex->query("create table z_pmto2 (\n id int unsigned,\n userids varchar(250),\n primary key (id)\n);");
$ex->query("insert ignore z_pmto2 (id, userids)\nselect\n id,\n group_concat(userid order by userid)\nfrom z_pmto\ngroup by id;");
$ex->query("drop table if exists z_pm;");
$ex->query("create table z_pm (\n id int unsigned,\n subject varchar(255),\n subject2 varchar(255),\n userids varchar(250),\n groupid int unsigned\n);");
$ex->query("insert z_pm (\n id,\n subject,\n subject2,\n userids\n)\nselect\n pm.privmsgs_id,\n pm.privmsgs_subject,\n case when pm.privmsgs_subject like 'Re: %' then trim(substring(pm.privmsgs_subject, 4)) else pm.privmsgs_subject end as subject2,\n t.userids\nfrom :_privmsgs pm\njoin z_pmto2 t\n on t.id = pm.privmsgs_id;");
$ex->query("create index z_idx_pm on z_pm (id);");
$ex->query("drop table if exists z_pmgroup;");
$ex->query("create table z_pmgroup (\n groupid int unsigned,\n subject varchar(255),\n userids varchar(250)\n);");
$ex->query("insert z_pmgroup (\n groupid,\n subject,\n userids\n)\nselect\n min(pm.id),\n pm.subject2,\n pm.userids\nfrom z_pm pm\ngroup by pm.subject2, pm.userids;");
$ex->query("create index z_idx_pmgroup on z_pmgroup (subject, userids);");
$ex->query("create index z_idx_pmgroup2 on z_pmgroup (groupid);");
$ex->query("update z_pm pm\njoin z_pmgroup g\n on pm.subject2 = g.subject and pm.userids = g.userids\nset pm.groupid = g.groupid;");
// Conversations.
$conversation_Map = array('privmsgs_id' => 'ConversationID', 'privmsgs_from_userid' => 'InsertUserID', 'RealSubject' => array('Column' => 'Subject', 'Type' => 'varchar(250)', 'Filter' => array('Phpbb2', 'EntityDecode')));
$ex->exportTable('Conversation', "select\n g.subject as RealSubject,\n pm.*,\n from_unixtime(pm.privmsgs_date) as DateInserted\nfrom :_privmsgs pm\njoin z_pmgroup g\n on g.groupid = pm.privmsgs_id", $conversation_Map);
// Coversation Messages.
$conversationMessage_Map = array('privmsgs_id' => 'MessageID', 'groupid' => 'ConversationID', 'privmsgs_text' => array('Column' => 'Body', 'Filter' => array($this, 'removeBBCodeUIDs')), 'privmsgs_from_userid' => 'InsertUserID');
$ex->exportTable('ConversationMessage', "select\n pm.*,\n txt.*,\n txt.privmsgs_bbcode_uid as bbcode_uid,\n pm2.groupid,\n 'BBCode' as Format,\n FROM_UNIXTIME(pm.privmsgs_date) as DateInserted\n from :_privmsgs pm\n join :_privmsgs_text txt\n on pm.privmsgs_id = txt.privmsgs_text_id\n join z_pm pm2\n on pm.privmsgs_id = pm2.id", $conversationMessage_Map);
// User Conversation.
$userConversation_Map = array('userid' => 'UserID', 'groupid' => 'ConversationID');
$ex->exportTable('UserConversation', "select\n g.groupid,\n t.userid\n from z_pmto t\n join z_pmgroup g\n on g.groupid = t.id;", $userConversation_Map);
$ex->query('drop table if exists z_pmto');
$ex->query('drop table if exists z_pmto2;');
$ex->query('drop table if exists z_pm;');
$ex->query('drop table if exists z_pmgroup;');
// End
$ex->endExport();
}
/**
* Export each table one at a time.
*
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
// Allow limited export of 1 category via ?forumid=ID
$forumID = $this->param('forumid');
if ($forumID) {
$forumWhere = ' and t.forumid ' . (strpos($forumID, ', ') === false ? "= {$forumID}" : "in ({$forumID})");
} else {
$forumWhere = '';
}
$characterSet = $ex->getCharacterSet('post');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Begin
$ex->beginExport('', 'vBulletin 3.* and 4.*');
$this->exportBlobs($this->param('files'), $this->param('avatars'), $forumWhere);
if ($this->param('noexport')) {
$ex->comment('Skipping the export.');
$ex->endExport();
return;
}
// Check to see if there is a max date.
$minDate = $this->param('mindate');
if ($minDate) {
$minDate = strtotime($minDate);
$ex->comment("Min topic date ({$minDate}): " . date('c', $minDate));
}
$now = time();
$cdn = $this->param('cdn', '');
// Grab all of the ranks.
$ranks = $ex->get("select * from :_usertitle order by minposts desc", 'usertitleid');
// Users
$user_Map = array('userid' => 'UserID', 'username' => 'Name', 'password2' => 'Password', 'email' => 'Email', 'referrerid' => 'InviteUserID', 'timezoneoffset' => 'HourOffset', 'ipaddress' => 'LastIPAddress', 'ipaddress2' => 'InsertIPAddress', 'usertitle' => array('Column' => 'Title', 'Filter' => function ($value) {
return trim(strip_tags(str_replace(' ', ' ', $value)));
}), 'posts' => array('Column' => 'RankID', 'Filter' => function ($value) use($ranks) {
// Look up the posts in the ranks table.
foreach ($ranks as $rankID => $row) {
if ($value >= $row['minposts']) {
return $rankID;
}
}
return null;
}));
// Use file avatar or the result of our blob export?
if ($this->getConfig('usefileavatar')) {
$user_Map['filephoto'] = 'Photo';
} else {
$user_Map['customphoto'] = 'Photo';
}
$ex->exportTable('User', "select u.*,\n ipaddress as ipaddress2,\n concat(`password`, salt) as password2,\n DATE_FORMAT(birthday_search,GET_FORMAT(DATE,'ISO')) as DateOfBirth,\n FROM_UNIXTIME(joindate) as DateFirstVisit,\n FROM_UNIXTIME(lastvisit) as DateLastActive,\n FROM_UNIXTIME(joindate) as DateInserted,\n FROM_UNIXTIME(lastactivity) as DateUpdated,\n case when avatarrevision > 0 then concat('{$cdn}', 'userpics/avatar', u.userid, '_', avatarrevision, '.gif')\n when av.avatarpath is not null then av.avatarpath\n else null\n end as filephoto,\n {$this->avatarSelect},\n case when ub.userid is not null then 1 else 0 end as Banned,\n 'vbulletin' as HashMethod\n from :_user u\n left join :_customavatar a\n on u.userid = a.userid\n left join :_avatar av\n on u.avatarid = av.avatarid\n left join :_userban ub\n on u.userid = ub.userid and ub.liftdate <= now() ", $user_Map);
// ":_" will be replace by database prefix
// Roles
$role_Map = array('usergroupid' => 'RoleID', 'title' => 'Name', 'description' => 'Description');
$ex->exportTable('Role', 'select * from :_usergroup', $role_Map);
// UserRoles
$userRole_Map = array('userid' => 'UserID', 'usergroupid' => 'RoleID');
$ex->query("CREATE TEMPORARY TABLE VbulletinRoles (userid INT UNSIGNED NOT NULL, usergroupid INT UNSIGNED NOT NULL)");
# Put primary groups into tmp table
$ex->query("insert into VbulletinRoles (userid, usergroupid) select userid, usergroupid from :_user");
# Put stupid CSV column into tmp table
$secondaryRoles = $ex->query("select userid, usergroupid, membergroupids from :_user", true);
if (is_resource($secondaryRoles)) {
while (($row = @mysql_fetch_assoc($secondaryRoles)) !== false) {
if ($row['membergroupids'] != '') {
$groups = explode(',', $row['membergroupids']);
foreach ($groups as $groupID) {
if (!$groupID) {
continue;
}
$ex->query("insert into VbulletinRoles (userid, usergroupid) values({$row['userid']},{$groupID})", true);
}
}
}
}
# Export from our tmp table and drop
$ex->exportTable('UserRole', 'select distinct userid, usergroupid from VbulletinRoles', $userRole_Map);
$ex->query("DROP TABLE IF EXISTS VbulletinRoles");
// Permissions.
$permissions_Map = array('usergroupid' => 'RoleID', 'title' => array('Column' => 'Garden.SignIn.Allow', 'Filter' => array($this, 'signInPermission')), 'genericpermissions' => array('Column' => 'GenericPermissions', 'type' => 'int'), 'forumpermissions' => array('Column' => 'ForumPermissions', 'type' => 'int'));
$this->addPermissionColumns(self::$permissions, $permissions_Map);
$ex->exportTable('Permission', 'select * from :_usergroup', $permissions_Map);
// $ex->EndExport();
// return;
// UserMeta
$ex->query("CREATE TEMPORARY TABLE VbulletinUserMeta (`UserID` INT NOT NULL ,`Name` VARCHAR( 255 ) NOT NULL ,`Value` text NOT NULL)");
# Standard vB user data
$userFields = array('usertitle' => 'Title', 'homepage' => 'Website', 'skype' => 'Skype', 'styleid' => 'StyleID');
foreach ($userFields as $field => $insertAs) {
$ex->query("insert into VbulletinUserMeta (UserID, Name, Value) select userid, 'Profile.{$insertAs}', {$field} from :_user where {$field} != ''");
}
# Dynamic vB user data (userfield)
$profileFields = $ex->query("select varname, text from :_phrase where product='vbulletin' and fieldname='cprofilefield' and varname like 'field%_title'");
if (is_resource($profileFields)) {
$profileQueries = array();
while ($field = @mysql_fetch_assoc($profileFields)) {
$column = str_replace('_title', '', $field['varname']);
$name = preg_replace('/[^a-zA-Z0-9\\s_-]/', '', $field['text']);
$profileQueries[] = "insert into VbulletinUserMeta (UserID, Name, Value)\n select userid, 'Profile." . $name . "', " . $column . " from :_userfield where " . $column . " != ''";
}
foreach ($profileQueries as $query) {
$ex->query($query);
}
}
// Signatures
$sql = "\n select\n userid as UserID,\n 'Plugin.Signatures.Sig' as Name,\n signature as Value\n from :_usertextfield\n where nullif(signature, '') is not null\n\n union\n\n select\n userid,\n 'Plugin.Signatures.Format',\n 'BBCode'\n from :_usertextfield\n where nullif(signature, '') is not null";
$ex->exportTable('UserMeta', $sql);
// Ranks
$rank_Map = array('usertitleid' => 'RankID', 'title' => 'Name', 'title2' => 'Label', 'minposts' => array('Column' => 'Attributes', 'Filter' => function ($value) {
$result = array('Criteria' => array('CountPosts' => $value));
return serialize($result);
}), 'level' => array('Column' => 'Level', 'Filter' => function ($value) {
static $level = 1;
return $level++;
}));
$ex->exportTable('Rank', "\n select ut.*, ut.title as title2, 0 as level\n from :_usertitle ut\n order by ut.minposts", $rank_Map);
// Categories
$category_Map = array('forumid' => 'CategoryID', 'description' => 'Description', 'Name2' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'displayorder' => array('Column' => 'Sort', 'Type' => 'int'), 'parentid' => 'ParentCategoryID');
$ex->exportTable('Category', "select f.*, title as Name2\n from :_forum f\n where 1 = 1 {$forumWhere}", $category_Map);
$minDiscussionID = false;
$minDiscussionWhere = false;
if ($minDate) {
$minDiscussionID = $ex->getValue("\n select max(threadid)\n from :_thread\n where dateline < {$minDate}\n ", false);
$minDiscussionID2 = $ex->getValue("\n select min(threadid)\n from :_thread\n where dateline >= {$minDate}\n ", false);
// The two discussion IDs should be the same, but let's average them.
$minDiscussionID = floor(($minDiscussionID + $minDiscussionID2) / 2);
$ex->comment('Min topic id: ' . $minDiscussionID);
}
// Discussions
$discussion_Map = array('threadid' => 'DiscussionID', 'forumid' => 'CategoryID', 'postuserid' => 'InsertUserID', 'postuserid2' => 'UpdateUserID', 'title' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'Format' => 'Format', 'views' => 'CountViews', 'ipaddress' => 'InsertIPAddress');
if ($ex->destination == 'database') {
// Remove the filter from the title so that this doesn't take too long.
$ex->HTMLDecoderDb('thread', 'title', 'threadid');
unset($discussion_Map['title']['Filter']);
}
if ($minDiscussionID) {
$minDiscussionWhere = "and t.threadid > {$minDiscussionID}";
}
$ex->exportTable('Discussion', "select t.*,\n t.postuserid as postuserid2,\n p.ipaddress,\n p.pagetext as Body,\n 'BBCode' as Format,\n replycount+1 as CountComments,\n convert(ABS(open-1),char(1)) as Closed,\n if(convert(sticky,char(1))>0,2,0) as Announce,\n FROM_UNIXTIME(t.dateline) as DateInserted,\n FROM_UNIXTIME(lastpost) as DateUpdated,\n FROM_UNIXTIME(lastpost) as DateLastComment\n from :_thread t\n left join :_deletionlog d on (d.type='thread' and d.primaryid=t.threadid)\n left join :_post p on p.postid = t.firstpostid\n where d.primaryid is null\n and t.visible = 1\n {$minDiscussionWhere}\n {$forumWhere}", $discussion_Map);
// Comments
$comment_Map = array('postid' => 'CommentID', 'threadid' => 'DiscussionID', 'pagetext' => 'Body', 'Format' => 'Format', 'ipaddress' => 'InsertIPAddress');
if ($minDiscussionID) {
$minDiscussionWhere = "and p.threadid > {$minDiscussionID}";
}
$ex->exportTable('Comment', "select p.*,\n 'BBCode' as Format,\n p.userid as InsertUserID,\n p.userid as UpdateUserID,\n FROM_UNIXTIME(p.dateline) as DateInserted,\n FROM_UNIXTIME(p.dateline) as DateUpdated\n from :_post p\n inner join :_thread t\n on p.threadid = t.threadid\n left join :_deletionlog d\n on (d.type='post' and d.primaryid=p.postid)\n where p.postid <> t.firstpostid\n and d.primaryid is null\n and p.visible = 1\n {$minDiscussionWhere}\n {$forumWhere}", $comment_Map);
// UserDiscussion
if ($minDiscussionID) {
$minDiscussionWhere = "where st.threadid > {$minDiscussionID}";
}
$ex->exportTable('UserDiscussion', "select\n st.userid as UserID,\n st.threadid as DiscussionID,\n '1' as Bookmarked,\n FROM_UNIXTIME(tr.readtime) as DateLastViewed\n from :_subscribethread st\n left join :_threadread tr on tr.userid = st.userid and tr.threadid = st.threadid\n {$minDiscussionWhere}");
/*$ex->exportTable('UserDiscussion', "select
tr.userid as UserID,
tr.threadid as DiscussionID,
FROM_UNIXTIME(tr.readtime) as DateLastViewed,
case when st.threadid is not null then 1 else 0 end as Bookmarked
from :_threadread tr
left join :_subscribethread st on tr.userid = st.userid and tr.threadid = st.threadid");*/
// Activity (from visitor messages in vBulletin 3.8+)
if ($ex->exists('visitormessage')) {
if ($minDiscussionID) {
$minDiscussionWhere = "and dateline > {$minDiscussionID}";
}
$activity_Map = array('postuserid' => 'RegardingUserID', 'userid' => 'ActivityUserID', 'pagetext' => 'Story', 'NotifyUserID' => 'NotifyUserID', 'Format' => 'Format');
$ex->exportTable('Activity', "select *,\n '{RegardingUserID,you} → {ActivityUserID,you}' as HeadlineFormat,\n FROM_UNIXTIME(dateline) as DateInserted,\n FROM_UNIXTIME(dateline) as DateUpdated,\n INET_NTOA(ipaddress) as InsertIPAddress,\n postuserid as InsertUserID,\n -1 as NotifyUserID,\n 'BBCode' as Format,\n 'WallPost' as ActivityType\n from :_visitormessage\n where state='visible'\n {$minDiscussionWhere}", $activity_Map);
}
$this->_exportConversations($minDate);
$this->_exportPolls();
// Media
if ($ex->exists('attachment')) {
$this->exportMedia($minDiscussionID);
}
// IP Ban list
$ipBanlist = $this->param('ipbanlist');
if ($ipBanlist) {
$ex->query("DROP TABLE IF EXISTS `z_ipbanlist` ");
$ex->query("CREATE TABLE `z_ipbanlist` (\n `id` int(11) unsigned NOT NULL AUTO_INCREMENT,\n `ipaddress` varchar(50) DEFAULT NULL,\n PRIMARY KEY (`id`),\n UNIQUE KEY `ipaddress` (`ipaddress`)\n\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8");
$result = $ex->query("select value from :_setting where varname = 'banip'");
$row = mysql_fetch_assoc($result);
if ($row) {
$insertSql = 'INSERT IGNORE INTO `z_ipbanlist` (`ipaddress`) values ';
$ipString = str_replace("\r", "", $row['value']);
$IPs = explode("\n", $ipString);
foreach ($IPs as $IP) {
$IP = trim($IP);
if (empty($IP)) {
continue;
}
$insertSql .= '(\'' . mysql_real_escape_string($IP) . '\'), ';
}
$insertSql = substr($insertSql, 0, -2);
$ex->query($insertSql);
$ban_Map = array();
$ex->exportTable('Ban', "select 'IPAddress' as BanType, ipaddress as BanValue, 'Imported ban' as Notes, NOW() as DateInserted\n FROM `z_ipbanlist`", $ban_Map);
$ex->query('DROP table if exists `z_ipbanlist` ');
}
}
// End
$ex->endExport();
}
/**
* Forum-specific export format.
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('messages');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Begin
$ex->beginExport('', 'SMF 1.*', array('HashMethod' => 'Django'));
// Users
$user_Map = array('ID_MEMBER' => 'UserID', 'memberName' => 'Name', 'password' => 'Password', 'emailAddress' => 'Email', 'DateInserted' => 'DateInserted', 'timeOffset' => 'HourOffset', 'posts' => 'CountComments', 'birthdate' => 'DateOfBirth', 'DateFirstVisit' => 'DateFirstVisit', 'DateLastActive' => 'DateLastActive', 'DateUpdated' => 'DateUpdated');
$ex->exportTable('User', "\n select m.*,\n from_unixtime(dateRegistered) as DateInserted,\n from_unixtime(dateRegistered) as DateFirstVisit,\n from_unixtime(lastLogin) as DateLastActive,\n from_unixtime(lastLogin) as DateUpdated,\n concat('sha1\$', lower(memberName), '\$', passwd) as `password`,\n if(m.avatar <> '', m.avatar, concat('attachments/', a.filename)) as Photo\n from :_members m\n left join :_attachments a on a.ID_MEMBER = m.ID_MEMBER ", $user_Map);
// Roles
$role_Map = array('ID_GROUP' => 'RoleID', 'groupName' => 'Name');
$ex->exportTable('Role', "select * from :_membergroups", $role_Map);
// UserRoles
$userRole_Map = array('ID_MEMBER' => 'UserID', 'ID_GROUP' => 'RoleID');
$ex->exportTable('UserRole', "select * from :_members", $userRole_Map);
// Categories
$category_Map = array('Name' => array('Column' => 'Name', 'Filter' => array($this, 'decodeNumericEntity')));
$ex->exportTable('Category', "\n select\n (`ID_CAT` + 1000000) as `CategoryID`,\n `name` as `Name`,\n '' as `Description`,\n null as `ParentCategoryID`,\n `catOrder` as `Sort`\n from :_categories\n\n union\n\n select\n b.`ID_BOARD` as `CategoryID`,\n\n b.`name` as `Name`,\n b.`description` as `Description`,\n (CASE WHEN b.`ID_PARENT` = 0 THEN (`ID_CAT` + 1000000) ELSE `ID_PARENT` END) as `ParentCategoryID`,\n b.`boardOrder` as `Sort`\n from :_boards b\n\n ", $category_Map);
// Discussions
$discussion_Map = array('ID_TOPIC' => 'DiscussionID', 'subject' => array('Column' => 'Name', 'Filter' => array($this, 'decodeNumericEntity')), 'body' => array('Column' => 'Body'), 'Format' => 'Format', 'ID_BOARD' => 'CategoryID', 'DateInserted' => 'DateInserted', 'DateUpdated' => 'DateUpdated', 'ID_MEMBER' => 'InsertUserID', 'DateLastComment' => 'DateLastComment', 'UpdateUserID' => 'UpdateUserID', 'locked' => 'Closed', 'isSticky' => 'Announce', 'CountComments' => 'CountComments', 'numViews' => 'CountViews', 'LastCommentUserID' => 'LastCommentUserID', 'ID_LAST_MSG' => 'LastCommentID');
$ex->exportTable('Discussion', "\n select t.*,\n (t.numReplies + 1) as CountComments,\n m.subject,\n m.body,\n from_unixtime(m.posterTime) as DateInserted,\n from_unixtime(m.modifiedTime) as DateUpdated,\n m.ID_MEMBER,\n from_unixtime(m_end.posterTime) AS DateLastComment,\n m_end.ID_MEMBER AS UpdateUserID,\n m_end.ID_MEMBER AS LastCommentUserID,\n 'BBCode' as Format\n from :_topics t\n join :_messages as m on t.ID_FIRST_MSG = m.ID_MSG\n join :_messages as m_end on t.ID_LAST_MSG = m_end.ID_MSG\n\n -- where t.spam = 0 AND m.spam = 0;\n\n ", $discussion_Map);
// Comments
$comment_Map = array('ID_MSG' => 'CommentID', 'ID_TOPIC' => 'DiscussionID', 'Format' => 'Format', 'body' => array('Column' => 'Body'), 'ID_MEMBER' => 'InsertUserID', 'DateInserted' => 'DateInserted');
$ex->exportTable('Comment', "select m.*,\n from_unixtime(m.posterTime) AS DateInserted,\n 'BBCode' as Format\n from :_messages m\n join :_topics t on m.ID_TOPIC = t.ID_TOPIC\n where m.ID_MSG <> t.ID_FIRST_MSG;\n ", $comment_Map);
// Media
$media_Map = array('ID_ATTACH' => 'MediaID', 'ID_MSG' => 'ForeignID', 'size' => 'Size', 'height' => 'ImageHeight', 'width' => 'ImageWidth', 'extract_mimetype' => array('Column' => 'Type', 'Filter' => function ($value, $field, $row) {
return $this->getMimeTypeFromFileName($row['Path']);
}), 'thumb_path' => array('Column' => 'ThumbPath', 'Filter' => array($this, 'filterThumbnailData')), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')));
$ex->exportTable('Media', "\n select\n a.*,\n concat('attachments/', a.filename) as Path,\n IF(b.filename is not null, concat('attachments/', b.filename), null) as thumb_path,\n null as extract_mimetype,\n b.width as thumb_width,\n if(t.ID_TOPIC is null, 'Comment', 'Discussion') as ForeignTable\n from :_attachments a\n left join :_attachments b on b.ID_ATTACH = a.ID_THUMB\n left join :_topics t on a.ID_MSG = t.ID_FIRST_MSG\n where a.attachmentType = 0\n and a.ID_MSG > 0\n ", $media_Map);
// Conversations need a bit more conversion so execute a series of queries for that.
$ex->query('create table :_smfpmto (
id int,
to_id int,
deleted tinyint,
primary key(id, to_id)
)');
$ex->query('insert :_smfpmto (
id,
to_id,
deleted
)
select
ID_PM,
ID_MEMBER_FROM,
deletedBySender
from :_personal_messages');
$ex->query('insert ignore :_smfpmto (
id,
to_id,
deleted
)
select
ID_PM,
ID_MEMBER,
deleted
from :_pm_recipients');
$ex->query('create table :_smfpmto2 (
id int,
to_ids varchar(255),
primary key(id)
)');
$ex->query('insert :_smfpmto2 (
id,
to_ids
)
select
id,
group_concat(to_id order by to_id)
from :_smfpmto
group by id');
$ex->query('create table :_smfpm (
id int,
group_id int,
subject varchar(200),
subject2 varchar(200),
from_id int,
to_ids varchar(255))');
$ex->query('create index :_idx_smfpm2 on :_smfpm (subject2, from_id)');
$ex->query('create index :_idx_smfpmg on :_smfpm (group_id)');
$ex->query('insert :_smfpm (
id,
subject,
subject2,
from_id,
to_ids
)
select
ID_PM,
subject,
case when subject like \'Re: %\' then trim(substring(subject, 4)) else subject end as subject2,
ID_MEMBER_FROM,
to2.to_ids
from :_personal_messages pm
join :_smfpmto2 to2
on pm.ID_PM = to2.id');
$ex->query('create table :_smfgroups (
id int primary key,
subject2 varchar(200),
to_ids varchar(255)
)');
$ex->query('insert :_smfgroups
select
min(id) as group_id, subject2, to_ids
from :_smfpm
group by subject2, to_ids');
$ex->query('create index :_idx_smfgroups on :_smfgroups (subject2, to_ids)');
$ex->query('update :_smfpm pm
join :_smfgroups g
on pm.subject2 = g.subject2 and pm.to_ids = g.to_ids
set pm.group_id = g.id');
// Conversation.
$conv_Map = array('id' => 'ConversationID', 'from_id' => 'InsertUserID', 'DateInserted' => 'DateInserted', 'subject2' => array('Column' => 'Subject', 'Type' => 'varchar(255)'));
$ex->exportTable('Conversation', "select\n pm.group_id,\n pm.from_id,\n pm.subject2,\n from_unixtime(pm2.msgtime) as DateInserted\nfrom :_smfpm pm\njoin :_personal_messages pm2\n on pm.id = pm2.ID_PM\nwhere pm.id = pm.group_id", $conv_Map);
// ConversationMessage.
$convMessage_Map = array('id' => 'MessageID', 'group_id' => 'ConversationID', 'DateInserted' => 'DateInserted', 'from_id' => 'InsertUserID', 'body' => array('Column' => 'Body'));
$ex->exportTable('ConversationMessage', "select\n pm.id,\n pm.group_id,\n from_unixtime(pm2.msgtime) as DateInserted,\n pm.from_id,\n 'BBCode' as Format,\n case when pm.subject = pm.subject2 then concat(pm.subject, '\n\n', pm2.body) else pm2.body end as body\nfrom :_smfpm pm\njoin :_personal_messages pm2\n on pm.id = pm2.ID_PM", $convMessage_Map);
// UserConversation.
$userConv_Map = array('to_id' => 'UserID', 'group_id' => 'ConversationID', 'deleted' => 'Deleted');
$ex->exportTable('UserConversation', "select\n pm.group_id,\n t.to_id,\n t.deleted\n from :_smfpmto t\n join :_smfpm pm\n on t.id = pm.group_id", $userConv_Map);
$ex->query('drop table :_smfpm');
$ex->query('drop table :_smfpmto');
$ex->query('drop table :_smfpmto2');
$ex->query('drop table :_smfgroups');
// End
$ex->endExport();
// echo implode("\n\n", $ex->Queries);
}
/**
* Main export process.
*
* @param ExportModel $ex
* @see $_Structures in ExportModel for allowed destination tables & columns.
*/
public function forumExport($ex)
{
// Get the characterset for the comments.
// Usually the comments table is the best target for this.
$characterSet = $ex->getCharacterSet('messages');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Reiterate the platform name here to be included in the porter file header.
$ex->beginExport('', 'FuseTalk');
$ex->comment("Creating indexes... ");
$result = $ex->query('show index from :_users where Key_name = "ix_users_userid"', true);
if (!mysql_num_rows($result)) {
$ex->query('create index ix_users_userid on :_users (iuserid)');
}
$result = $ex->query('show index from :_banning where Key_name = "ix_banning_banstring"', true);
if (!mysql_num_rows($result)) {
$ex->query('create index ix_banning_banstring on :_banning (vchbanstring)');
}
$result = $ex->query('show index from :_forumusers where Key_name = "ix_forumusers_userid"', true);
if (!mysql_num_rows($result)) {
$ex->query('create index ix_forumusers_userid on :_forumusers (iuserid)');
}
$result = $ex->query('show index from :_groupusers where Key_name = "ix_groupusers_userid"', true);
if (!mysql_num_rows($result)) {
$ex->query('create index ix_groupusers_userid on :_groupusers (iuserid)');
}
$result = $ex->query('show index from :_privatemessages where Key_name = "ix_privatemessages_vchusagestatus"', true);
if (!mysql_num_rows($result)) {
$ex->query('create index ix_privatemessages_vchusagestatus on :_privatemessages (vchusagestatus)');
}
$result = $ex->query('show index from :_threads where Key_name = "ix_threads_id_pollflag"', true);
if (!mysql_num_rows($result)) {
$ex->query('create index ix_threads_id_pollflag on :_threads (ithreadid, vchpollflag)');
}
$result = $ex->query('show index from :_threads where Key_name = "ix_threads_poll"', true);
if (!mysql_num_rows($result)) {
$ex->query('create index ix_threads_poll on :_threads (vchpollflag)');
}
$ex->comment("Indexes done!");
// Users
$user_Map = array();
$ex->exportTable('User', "\n select\n user.iuserid as UserID,\n user.vchnickname as Name,\n user.vchemailaddress as Email,\n user.vchpassword as Password,\n 'md5' as HashMethod,\n if (forumusers.vchauthoricon is not null, concat('authoricons/', forumusers.vchauthoricon), null) as Photo,\n user.dtinsertdate as DateInserted,\n user.dtlastvisiteddate as DateLastActive,\n user.bapproved as Confirmed,\n if (user.iuserlevel = 0, 1, 0) as Admin,\n if (coalesce(bemail.vchbanstring, bname.vchbanstring, 0) != 0, 1, 0) as Banned\n from :_users as user\n left join :_forumusers as forumusers using (iuserid)\n left join :_banning as bemail on b.vchbanstring = user.vchemailaddress\n left join :_banning as bname on b.vchbanstring = user.vchnickname\n group by user.iuserid\n ;", $user_Map);
// ":_" will be replaced by database prefix
$memberRoleID = 1;
$result = $ex->query("select max(igroupid) as maxRoleID from :_groups", true);
if ($row = mysql_fetch_assoc($result)) {
$memberRoleID += $row['maxRoleID'];
}
// UserMeta. (Signatures)
$ex->exportTable('UserMeta', "\n select\n user.iuserid as UserID,\n 'Plugin.Signatures.Sig' as Name,\n user.txsignature as Value\n from :_users as user\n where nullif(nullif(user.txsignature, ''), char(0)) is not null\n\n union all\n\n select\n user.iuserid,\n 'Plugin.Signatures.Format',\n 'Html'\n from :_users as user\n where nullif(nullif(user.txsignature, ''), char(0)) is not null\n ");
// Role.
$role_Map = array();
$ex->exportTable('Role', "\n select\n groups.igroupid as RoleID,\n groups.vchgroupname as Name\n from :_groups as groups\n\n union all\n\n select\n {$memberRoleID} as RoleID,\n 'Members'\n from dual\n ", $role_Map);
// User Role.
$userRole_Map = array();
$ex->exportTable('UserRole', "\n select\n user.iuserid as UserID,\n ifnull (user_role.igroupid, {$memberRoleID}) as RoleID\n from :_users as user\n left join :_groupusers as user_role using (iuserid)\n ", $userRole_Map);
$ex->query("drop table if exists zConversations;");
$ex->query("\n create table zConversations(\n `ConversationID` int(11) not null AUTO_INCREMENT,\n `User1` int(11) not null,\n `User2` int(11) not null,\n `DateInserted` datetime not null,\n primary key (`ConversationID`),\n key `IX_zConversation_User1_User2` (`User1`,`User2`)\n );\n ");
$ex->query("\n insert into zConversations(`User1`, `User2`, `DateInserted`)\n select\n if (pm.iuserid < pm.iownerid, pm.iuserid, pm.iownerid) as User1,\n if (pm.iuserid < pm.iownerid, pm.iownerid, pm.iuserid) as User2,\n min(pm.dtinsertdate)\n from :_privatemessages as pm\n group by\n User1,\n User2\n ");
// Conversations.
$conversation_Map = array();
$ex->exportTable('Conversation', "\n select\n c.ConversationID as ConversationID,\n c.User1 as InsertUserID,\n c.DateInserted as DateInserted\n from zConversations as c\n ;", $conversation_Map);
// Conversation Messages.
$conversationMessage_Map = array('txmessage' => array('Column' => 'Body', 'Filter' => array($this, 'fixSmileysURL')));
$ex->exportTable('ConversationMessage', "\n select\n pm.imessageid as MessageID,\n c.ConversationID,\n pm.txmessage,\n 'Html' as Format,\n pm.iownerid as InsertUserID,\n pm.dtinsertdate as DateInserted\n from zConversations as c\n inner join :_privatemessages as pm on pm.iuserid = c.User1 and pm.iownerid = c.User2\n where vchusagestatus = 'sent'\n\n union all\n\n select\n pm.imessageid as MessageID,\n c.ConversationID,\n pm.txmessage,\n 'Html' as Format,\n pm.iownerid as InsertUserID,\n pm.dtinsertdate as DateInserted\n from zConversations as c\n inner join :_privatemessages as pm on pm.iuserid = c.User2 and pm.iownerid = c.User1\n where vchusagestatus = 'sent'\n ;", $conversationMessage_Map);
// User Conversation.
$userConversation_Map = array();
$ex->exportTable('UserConversation', "\n select\n c.ConversationID,\n c.User1 as UserID,\n now() as DateLastViewed\n from zConversations as c\n\n union all\n\n select\n c.ConversationID,\n c.User2 as UserID,\n now() as DateLastViewed\n from zConversations as c\n ;", $userConversation_Map);
// Category.
$category_Map = array();
$ex->exportTable('Category', "\n select\n categories.icategoryid as CategoryID,\n categories.vchcategoryname as Name,\n categories.vchdescription as Description,\n -1 as ParentCategoryID\n from :_categories as categories\n ", $category_Map);
// Discussion.
/* Skip "Body". It will be fixed at import.
* The first comment is going to be used to fill the missing data and will then be deleted
*/
$discussion_Map = array();
$ex->exportTable('Discussion', "\n select\n threads.ithreadid as DiscussionID,\n threads.vchthreadname as Name,\n threads.icategoryid as CategoryID,\n threads.iuserid as InsertUserID,\n threads.dtinsertdate as DateInserted,\n 'HTML' as Format,\n if (threads.vchalertthread = 'Yes' and threads.dtstaydate > now(), 2, 0) as Announce,\n if (threads.vchthreadlock = 'Locked', 1, 0) as Closed\n from :_threads as threads\n ", $discussion_Map);
// Comment.
/*
* The iparentid column doesn't make any sense since the display is ordered by date only (there are no "sub" comment)
*/
$comment_Map = array('txmessage' => array('Column' => 'Body', 'Filter' => array($this, 'fixSmileysURL')));
$ex->exportTable('Comment', "\n select\n messages.imessageid as CommentID,\n messages.ithreadid as DiscussionID,\n messages.iuserid as InsertUserID,\n messages.txmessage,\n 'Html' as Format,\n messages.dtmessagedate as DateInserted\n from :_messages as messages\n ", $comment_Map);
$ex->endExport();
}
/**
* Main export process.
*
* @param ExportModel $ex
* @see $_Structures in ExportModel for allowed destination tables & columns.
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('posts');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Reiterate the platform name here to be included in the porter file header.
$ex->beginExport('', 'jforum');
// User.
$user_Map = array();
$ex->exportTable('User', "\n select\n u.user_id as UserID,\n u.username as Name,\n 'Reset' as HashMethod,\n u.user_email as Email,\n u.user_regdate as DateInserted,\n u.user_regdate as DateFirstVisit,\n u.user_posts as CountComments,\n u.user_avatar as Photo,\n u.deleted as Deleted,\n u.user_from as Location,\n u.user_biography as About\n from :_users as u\n ", $user_Map);
// Role.
$role_Map = array();
$ex->exportTable('Role', "\n select\n g.group_id as RoleID,\n g.group_name as Name,\n g.group_description as Description\n from :_groups as g\n ", $role_Map);
// User Role.
$userRole_Map = array();
$ex->exportTable('UserRole', "\n select\n u.user_id as UserID,\n u.group_id as RoleID\n from :_user_groups as u\n ", $userRole_Map);
// UserMeta.
$ex->exportTable('UserMeta', "\n select\n user_id as UserID,\n 'Profile.Website' as `Name`,\n user_website as `Value`\n from :_users\n where user_website is not null\n\n union\n\n select\n user_id,\n 'Plugins.Signatures.Sig',\n user_sig\n from :_users\n where user_sig is not null\n\n union\n\n select\n user_id,\n 'Plugins.Signatures.Format',\n 'BBCode'\n from :_users\n where user_sig is not null\n\n union\n\n select\n user_id,\n 'Profile.Occupation',\n user_occ\n from :_users\n where user_occ is not null\n\n union\n\n select\n user_id,\n 'Profile.Interests',\n user_interests\n from :_users\n where user_interests is not null\n ");
// Category.
// _categories is tier 1, _forum is tier 2.
// Overlapping IDs, so fast-forward _categories by 1000.
$category_Map = array();
$ex->exportTable('Category', "\n select\n c.categories_id+1000 as CategoryID,\n -1 as ParentCategoryID,\n c.title as Name,\n null as Description,\n 1 as Depth,\n c.display_order as Sort\n from :_categories as c\n\n union\n\n select\n f.forum_id as CategoryID,\n f.categories_id+1000 as ParentCategoryID,\n f.forum_name as Name,\n f.forum_desc as Description,\n 2 as Depth,\n null as Sort\n from :_forums as f\n ", $category_Map);
if ($ex->exists(':_posts_text')) {
$postTextColumm = 't.post_text as Body';
$postTextSource = 'left join :_posts_text t on p.post_id = t.post_id';
} else {
$postTextColumm = 'p.post_text as Body';
$postTextSource = '';
}
// Discussion.
$discussion_Map = array('topic_title' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'));
// It's easier to convert between Unix time and MySQL datestamps during the db query.
$ex->exportTable('Discussion', "\n select\n t.topic_id as DiscussionID,\n t.forum_id as CategoryID,\n t.user_id as InsertUserID,\n t.topic_time as DateInserted,\n t.topic_title,\n t.topic_views as CountViews,\n t.topic_replies as CountComments,\n t.topic_status as Closed,\n if (t.topic_type > 0, 1, 0) as Announce,\n {$postTextColumm},\n 'BBCode' as Format\n from :_topics as t\n left join :_posts p on t.topic_first_post_id = p.post_id\n {$postTextSource}\n ", $discussion_Map);
// Comment.
$comment_Map = array();
$ex->exportTable('Comment', "\n select\n p.post_id as CommentID,\n p.topic_id as DiscussionID,\n p.user_id as InsertUserID,\n p.poster_ip as InsertIPAddress,\n p.post_time as DateInserted,\n p.post_edit_time as DateUpdated,\n 'BBCode' as Format,\n {$postTextColumm}\n from :_posts as p\n {$postTextSource}\n left join jforum_topics as t on t.topic_first_post_id = p.post_id\n where t.topic_first_post_id is null\n ", $comment_Map);
// UserDiscussion.
// Guessing table is called "_watch" because they are all bookmarks.
$userDiscussion_Map = array('topic_id' => 'DiscussionID', 'user_id' => 'UserID');
$ex->exportTable('UserDiscussion', "\n select\n w.topic_id as DiscussionID,\n w.user_id as UserID,\n 1 as Bookmarked,\n if (w.is_read, now(), null) as DateLastViewed\n from :_topics_watch as w\n ", $userDiscussion_Map);
// Conversation.
// Thread using tmp table based on the pair of users talking.
$result = $ex->query('show index from :_privmsgs where Key_name = "ix_zconversation_from_to"', true);
if (!mysql_num_rows($result)) {
$ex->query('create index ix_zconversation_from_to on :_privmsgs (privmsgs_from_userid, privmsgs_to_userid)');
}
$ex->query("drop table if exists z_conversation;");
$ex->query("\n create table z_conversation (\n ConversationID int unsigned not null auto_increment,\n LowUserID int unsigned,\n HighUserID int unsigned,\n primary key (ConversationID),\n index idx_lowuser_highuser (LowUserID, HighUserID)\n ) engine=InnoDB default charset=utf8 collate=utf8_unicode_ci;\n ");
$ex->query("\n insert into z_conversation (LowUserID, HighUserID)\n select\n least(privmsgs_from_userid, privmsgs_to_userid),\n greatest(privmsgs_from_userid, privmsgs_to_userid)\n from :_privmsgs\n group by\n least(privmsgs_from_userid, privmsgs_to_userid),\n greatest(privmsgs_from_userid, privmsgs_to_userid)\n ");
// Replying on /dba/counts to rebuild most of this data later.
$conversation_Map = array('privmsgs_from_userid' => 'InsertUserID', 'privmsgs_date' => 'DateInserted', 'privmsgs_subject' => 'Subject');
$ex->exportTable('Conversation', "\n select\n p.privmsgs_from_userid as InsertUserID,\n p.privmsgs_date as DateInserted,\n p.privmsgs_subject as Subject,\n c.ConversationID\n from :_privmsgs as p\n left join z_conversation as c on c.HighUserID = greatest(p.privmsgs_from_userid, p.privmsgs_to_userid)\n and c.LowUserID = least(p.privmsgs_from_userid, p.privmsgs_to_userid)\n group by\n least(privmsgs_from_userid, privmsgs_to_userid),\n greatest(privmsgs_from_userid, privmsgs_to_userid)\n ", $conversation_Map);
// Conversation Message.
// Messages with the same timestamps are sent/received copies.
// Yes that'd probably break down on huge sites but it's too convenient to pass up for now.
$message_Map = array('privmsgs_id' => 'MessageID', 'privmsgs_from_userid' => 'InsertUserID', 'privmsgs_date' => 'DateInserted', 'privmsgs_text' => 'Body');
$ex->exportTable('ConversationMessage', "\n select\n p.privmsgs_id as MessageID,\n p.privmsgs_from_userid as InsertUserID,\n p.privmsgs_date as DateInserted,\n t.privmsgs_text as Body,\n c.ConversationID,\n 'BBCode' as Format\n from :_privmsgs p\n left join :_privmsgs_text t on t.privmsgs_id = p.privmsgs_id\n left join z_conversation c on c.LowUserID = least(privmsgs_from_userid, privmsgs_to_userid)\n and c.HighUserID = greatest(privmsgs_from_userid, privmsgs_to_userid)\n group by privmsgs_date\n ", $message_Map);
// UserConversation
$ex->exportTable('UserConversation', "\n select\n ConversationID,\n LowUserID as UserID,\n now() as DateLastViewed\n from z_conversation\n\n union\n\n select\n ConversationID,\n HighUserID as UserID,\n now() as DateLastViewed\n from z_conversation\n ");
$ex->comment('Run the following query after the import: ');
$ex->comment('update GDN_UserConversation set CountReadMessages = (select count(MessageID) from GDN_ConversationMessage where GDN_ConversationMessage.ConversationID = GDN_UserConversation.ConversationID)');
$ex->endExport();
}
/**
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
// $ex->TestMode = FALSE;
// $ex->TestLimit = FALSE;
// $ex->Destination = 'database';
// $ex->DestDb = 'unknownworlds';
// $ex->CaptureOnly = TRUE;
// $ex->ScriptCreateTable = FALSE;
// $ex->DestPrefix = 'GDN_';
$ex->sourcePrefix = ':_';
$characterSet = $ex->getCharacterSet('posts');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Decode all of the necessary fields.
// $ex->HTMLDecoderDb('members', 'members_display_name', 'member_id');
// $ex->HTMLDecoderDb('members', 'name', 'member_id');
// $ex->HTMLDecoderDb('members', 'title', 'member_id');
// $ex->HtmlDecoderDb('groups', 'g_title', 'g_id');
// $ex->HtmlDecoderDb('topics', 'title', 'tid');
// $ex->HtmlDecoderDb('topics', 'description', 'tid');
// Begin
$ex->beginExport('', 'IPB 3.*', array('HashMethod' => 'ipb'));
// Export avatars
if ($this->param('avatars')) {
$this->doAvatars();
}
if ($ex->exists('members', 'member_id') === true) {
$memberID = 'member_id';
} else {
$memberID = 'id';
}
// Users.
$user_Map = array($memberID => 'UserID', 'members_display_name' => array('Column' => 'Name', 'Filter' => 'HtmlDecoder'), 'email' => 'Email', 'joined' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'firstvisit' => array('Column' => 'DateFirstVisit', 'SourceColumn' => 'joined', 'Filter' => 'timestampToDate'), 'ip_address' => 'InsertIPAddress', 'title' => 'Title', 'time_offset' => 'HourOffset', 'last_activity' => array('Column' => 'DateLastActive', 'Filter' => 'timestampToDate'), 'member_banned' => 'Banned', 'Photo' => 'Photo', 'title' => 'Title', 'location' => 'Location');
$from = '';
$select = '';
if ($ex->exists('members', 'members_pass_hash') === true) {
$select = ",concat(m.members_pass_hash, '\$', m.members_pass_salt) as Password";
} else {
$select = ",concat(mc.converge_pass_hash, '\$', mc.converge_pass_salt) as Password";
$from = "left join :_members_converge mc\n on m.{$memberID} = mc.converge_id";
}
if ($ex->exists('members', 'hide_email') === true) {
$showEmail = '!hide_email';
} else {
$showEmail = '0';
}
$cdn = $this->cdnPrefix();
if ($ex->exists('member_extra') === true) {
$sql = "select\n m.*,\n m.joined as firstvisit,\n 'ipb' as HashMethod,\n {$showEmail} as ShowEmail,\n case when x.avatar_location in ('noavatar', '') then null\n when x.avatar_location like 'upload:%' then concat('{$cdn}ipb/', right(x.avatar_location, length(x.avatar_location) - 7))\n when x.avatar_type = 'upload' then concat('{$cdn}ipb/', x.avatar_location)\n when x.avatar_type = 'url' then x.avatar_location\n when x.avatar_type = 'local' then concat('{$cdn}style_avatars/', x.avatar_location)\n else null\n end as Photo,\n x.location\n {$select}\n from :_members m\n left join :_member_extra x\n on m.{$memberID} = x.id\n {$from}";
} else {
$sql = "select\n m.*,\n joined as firstvisit,\n 'ipb' as HashMethod,\n {$showEmail} as ShowEmail,\n case when length(p.pp_main_photo) <= 3 or p.pp_main_photo is null then null\n when p.pp_main_photo like '%//%' then p.pp_main_photo\n else concat('{$cdn}ipb/', p.pp_main_photo)\n end as Photo\n {$select}\n from :_members m\n left join :_profile_portal p\n on m.{$memberID} = p.pp_member_id\n {$from}";
}
$this->clearFilters('members', $user_Map, $sql, 'm');
$ex->exportTable('User', $sql, $user_Map);
// ":_" will be replaced by database prefix
// Roles.
$role_Map = array('g_id' => 'RoleID', 'g_title' => 'Name');
$ex->exportTable('Role', "select * from :_groups", $role_Map);
// Permissions.
$permission_Map = array('g_id' => 'RoleID', 'g_view_board' => 'Garden.SignIn.Allow', 'g_view_board2' => 'Garden.Profiles.View', 'g_view_board3' => 'Garden.Activity.View', 'g_view_board4' => 'Vanilla.Discussions.View', 'g_edit_profile' => 'Garden.Profiles.Edit', 'g_post_new_topics' => 'Vanilla.Discussions.Add', 'g_reply_other_topics' => 'Vanilla.Comments.Add', 'g_open_close_posts' => 'Vanilla.Discussions.Close', 'g_is_supmod' => 'Garden.Moderation.Manage', 'g_access_cp' => 'Garden.Settings.View');
$permission_Map = $ex->fixPermissionColumns($permission_Map);
$ex->exportTable('Permission', "\n select r.*,\n r.g_view_board as g_view_board2,\n r.g_view_board as g_view_board3,\n r.g_view_board as g_view_board4\n from :_groups r", $permission_Map);
// User Role.
if ($ex->exists('members', 'member_group_id') === true) {
$groupID = 'member_group_id';
} else {
$groupID = 'mgroup';
}
$userRole_Map = array($memberID => 'UserID', $groupID => 'RoleID');
$sql = "\n select\n m.{$memberID}, m.{$groupID}\n from :_members m";
if ($ex->exists('members', 'mgroup_others')) {
$sql .= "\n union all\n\n select m.{$memberID}, g.g_id\n from :_members m\n join :_groups g\n on find_in_set(g.g_id, m.mgroup_others)";
}
$ex->exportTable('UserRole', $sql, $userRole_Map);
// UserMeta.
$userMeta_Map = array('UserID' => 'UserID', 'Name' => 'Name', 'Value' => 'Value');
if ($ex->exists('profile_portal', 'signature') === true) {
$sql = "\n select\n pp_member_id as UserID,\n 'Plugin.Signatures.Sig' as Name,\n signature as Value\n from :_profile_portal\n where length(signature) > 1\n\n union all\n\n select\n pp_member_id as UserID,\n 'Plugin.Signatures.Format' as Name,\n 'IPB' as Value\n from :_profile_portal\n where length(signature) > 1\n ";
} elseif ($ex->exists('member_extra', array('id', 'signature')) === true) {
$sql = "\n select\n id as UserID,\n 'Plugin.Signatures.Sig' as Name,\n signature as Value\n from :_member_extra\n where length(signature) > 1\n\n union all\n\n select\n id as UserID,\n 'Plugin.Signatures.Format' as Name,\n 'IPB' as Value\n from :_member_extra\n where length(signature) > 1";
} else {
$sql = false;
}
if ($sql) {
$ex->exportTable('UserMeta', $sql, $userMeta_Map);
}
// Category.
$category_Map = array('id' => 'CategoryID', 'name' => array('Column' => 'Name', 'Filter' => 'HtmlDecoder'), 'name_seo' => 'UrlCode', 'description' => 'Description', 'parent_id' => 'ParentCategoryID', 'position' => 'Sort');
$ex->exportTable('Category', "select * from :_forums", $category_Map);
// Discussion.
$descriptionSQL = 'p.post';
$hasTopicDescription = $ex->exists('topics', array('description')) === true;
if ($hasTopicDescription || $ex->exists('posts', array('description')) === true) {
$description = $hasTopicDescription ? 't.description' : 'p.description';
$descriptionSQL = "case\n when {$description} <> '' and p.post is not null then concat('<div class=\"IPBDescription\">', {$description}, '</div>', p.post)\n when {$description} <> '' then {$description}\n else p.post\n end";
}
$discussion_Map = array('tid' => 'DiscussionID', 'title' => 'Name', 'description' => array('Column' => 'SubName', 'Type' => 'varchar(255)'), 'forum_id' => 'CategoryID', 'starter_id' => 'InsertUserID', 'start_date' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'ip_address' => 'InsertIPAddress', 'edit_time' => array('Column' => 'DateUpdated', 'Filter' => 'timestampToDate'), 'posts' => 'CountComments', 'views' => 'CountViews', 'pinned' => 'Announce', 'post' => 'Body', 'closed' => 'Closed');
$sql = "\n select\n t.*,\n {$descriptionSQL} as post,\n case when t.state = 'closed' then 1 else 0 end as closed,\n 'BBCode' as Format,\n p.ip_address,\n p.edit_time\n from :_topics t\n left join :_posts p\n on t.topic_firstpost = p.pid\n where t.tid between {from} and {to}";
$this->clearFilters('topics', $discussion_Map, $sql, 't');
$ex->exportTable('Discussion', $sql, $discussion_Map);
// Tags
$ex->query("DROP TABLE IF EXISTS `z_tag` ");
$ex->query("CREATE TABLE `z_tag` (\n `TagID` int(11) unsigned NOT NULL AUTO_INCREMENT,\n `FullName` varchar(50) DEFAULT NULL,\n PRIMARY KEY (`TagID`),\n UNIQUE KEY `FullName` (`FullName`)\n ) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
$ex->query("insert into z_tag (FullName) (select distinct t.tag_text as FullName from :_core_tags t)");
$tagDiscussion_Map = array('tag_added' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'));
$sql = "select TagID, '0' as CategoryID, tag_meta_id as DiscussionID, t.tag_added\n from :_core_tags t\n left join z_tag zt on t.tag_text = zt.FullName";
$ex->exportTable('TagDiscussion', $sql, $tagDiscussion_Map);
$tag_Map = array('FullName' => 'FullName', 'FullNameToName' => array('Column' => 'Name', 'Filter' => 'formatUrl'));
$sql = "select TagID, FullName, FullName as FullNameToName\n from z_tag zt";
$ex->exportTable('Tag', $sql, $tag_Map);
// Comments.
$comment_Map = array('pid' => 'CommentID', 'topic_id' => 'DiscussionID', 'author_id' => 'InsertUserID', 'ip_address' => 'InsertIPAddress', 'post_date' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'edit_time' => array('Column' => 'DateUpdated', 'Filter' => 'timestampToDate'), 'post' => 'Body');
$sql = "\n select\n p.*,\n 'BBCode' as Format\n from :_posts p\n join :_topics t\n on p.topic_id = t.tid\n where p.pid between {from} and {to}\n and p.pid <> t.topic_firstpost";
$this->clearFilters('Comment', $comment_Map, $sql, 'p');
$ex->exportTable('Comment', $sql, $comment_Map);
// Media.
$media_Map = array('attach_id' => 'MediaID', 'atype_mimetype' => 'Type', 'attach_file' => 'Name', 'attach_path' => 'Path', 'attach_date' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'thumb_path' => array('Column' => 'ThumbPath', 'Filter' => array($this, 'filterThumbnailData')), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')), 'attach_member_id' => 'InsertUserID', 'attach_filesize' => 'Size', 'ForeignID' => 'ForeignID', 'ForeignTable' => 'ForeignTable', 'img_width' => 'ImageWidth', 'img_height' => 'ImageHeight');
$sql = "select\n a.*,\n concat('~cf/ipb/', a.attach_location) as attach_path,\n concat('~cf/ipb/', a.attach_location) as thumb_path,\n 128 as thumb_width,\n ty.atype_mimetype,\n case when p.pid = t.topic_firstpost then 'discussion' else 'comment' end as ForeignTable,\n case when p.pid = t.topic_firstpost then t.tid else p.pid end as ForeignID,\n case a.attach_img_width when 0 then a.attach_thumb_width else a.attach_img_width end as img_width,\n case a.attach_img_height when 0 then a.attach_thumb_height else a.attach_img_height end as img_height\nfrom :_attachments a\njoin :_posts p\n on a.attach_rel_id = p.pid and a.attach_rel_module = 'post'\njoin :_topics t\n on t.tid = p.topic_id\nleft join :_attachments_type ty\n on a.attach_ext = ty.atype_extension";
$this->clearFilters('Media', $media_Map, $sql);
$ex->exportTable('Media', $sql, $media_Map);
if ($ex->exists('message_topic_user_map')) {
$this->_exportConversationsV3();
} else {
$this->_exportConversationsV2();
}
$ex->endExport();
}
/**
* Forum-specific export format
* @todo Project file size / export time and possibly break into multiple files
* @param ExportModel $ex
*
*/
protected function forumExport($ex)
{
$this->ex = $ex;
$characterSet = $ex->getCharacterSet('Comment');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Begin
$ex->beginExport('', 'Vanilla 1.*');
// Users
$user_Map = array('UserID' => 'UserID', 'Name' => 'Name', 'Password' => 'Password', 'Email' => 'Email', 'Icon' => 'Photo', 'CountComments' => 'CountComments', 'Discovery' => 'DiscoveryText');
$ex->exportTable('User', "SELECT * FROM :_User", $user_Map);
// ":_" will be replaced by database prefix
// Roles
// Since the zero role is a valid role in Vanilla 1 then we'll have to reassign it.
$r = $ex->query('select max(RoleID) as RoleID from :_Role');
$zeroRoleID = 0;
if (is_resource($r)) {
while (($row = @mysql_fetch_assoc($r)) !== false) {
$zeroRoleID = $row['RoleID'];
}
}
$zeroRoleID++;
/*
'RoleID' => 'int',
'Name' => 'varchar(100)',
'Description' => 'varchar(200)'
*/
$role_Map = array('RoleID' => 'RoleID', 'Name' => 'Name', 'Description' => 'Description');
$ex->exportTable('Role', "select RoleID, Name, Description from :_Role union all select {$zeroRoleID}, 'Applicant', 'Created by the Vanilla Porter'", $role_Map);
$permission_Map = array('RoleID' => 'RoleID', 'PERMISSION_SIGN_IN' => 'Garden.SignIn.Allow', 'Permissions' => array('Column' => 'Vanilla.Comments.Add', 'Type' => 'tinyint', 'Filter' => array($this, 'filterPermissions')), 'PERMISSION_START_DISCUSSION' => array('Column' => 'Vanilla.Discussions.Add', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')), 'PERMISSION_SINK_DISCUSSION' => array('Column' => 'Vanilla.Discussions.Sink', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')), 'PERMISSION_STICK_DISCUSSIONS' => array('Column' => 'Vanilla.Discussions.Announce', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')), 'PERMISSION_CLOSE_DISCUSSIONS' => array('Column' => 'Vanilla.Discussions.Close', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')), 'PERMISSION_EDIT_DISCUSSIONS' => array('Column' => 'Vanilla.Discussions.Edit', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')), 'PERMISSION_EDIT_COMMENTS' => array('Column' => 'Vanilla.Comments.Edit', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')), 'PERMISSION_APPROVE_APPLICANTS' => array('Column' => 'Garden.Moderation.Manage', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')), 'PERMISSION_EDIT_USERS' => array('Column' => 'Garden.Users.Edit', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')), 'PERMISSION_CHANGE_APPLICATION_SETTINGS' => array('Column' => 'Garden.Settings.Manage', 'Type' => 'tinyint', 'Filter' => array($this, 'forceBool')));
$ex->exportTable('Permission', "select * from :_Role", $permission_Map);
// UserRoles
/*
'UserID' => 'int',
'RoleID' => 'int'
*/
$userRole_Map = array('UserID' => 'UserID', 'RoleID' => 'RoleID');
$ex->exportTable('UserRole', "select UserID, case RoleID when 0 then {$zeroRoleID} else RoleID end as RoleID from :_User", $userRole_Map);
// Categories
/*
'CategoryID' => 'int',
'Name' => 'varchar(30)',
'Description' => 'varchar(250)',
'ParentCategoryID' => 'int',
'DateInserted' => 'datetime',
'InsertUserID' => 'int',
'DateUpdated' => 'datetime',
'UpdateUserID' => 'int'
*/
$category_Map = array('CategoryID' => 'CategoryID', 'Name' => 'Name', 'Description' => 'Description');
$ex->exportTable('Category', "select CategoryID, Name, Description from :_Category", $category_Map);
// Discussions
/*
'DiscussionID' => 'int',
'Name' => 'varchar(100)',
'CategoryID' => 'int',
'Body' => 'text',
'Format' => 'varchar(20)',
'DateInserted' => 'datetime',
'InsertUserID' => 'int',
'DateUpdated' => 'datetime',
'UpdateUserID' => 'int',
'Score' => 'float',
'Announce' => 'tinyint',
'Closed' => 'tinyint'
*/
$discussion_Map = array('DiscussionID' => 'DiscussionID', 'Name' => 'Name', 'CategoryID' => 'CategoryID', 'DateCreated' => 'DateInserted', 'DateCreated2' => 'DateUpdated', 'AuthUserID' => 'InsertUserID', 'DateLastActive' => 'DateLastComment', 'AuthUserID2' => 'UpdateUserID', 'Closed' => 'Closed', 'Sticky' => 'Announce', 'CountComments' => 'CountComments', 'Sink' => 'Sink', 'LastUserID' => 'LastCommentUserID');
$ex->exportTable('Discussion', "SELECT d.*,\n d.LastUserID as LastCommentUserID,\n d.DateCreated as DateCreated2, d.AuthUserID as AuthUserID2,\n c.Body,\n c.FormatType as Format\n FROM :_Discussion d\n LEFT JOIN :_Comment c\n ON d.FirstCommentID = c.CommentID\n WHERE coalesce(d.WhisperUserID, 0) = 0 and d.Active = 1", $discussion_Map);
// Comments
/*
'CommentID' => 'int',
'DiscussionID' => 'int',
'DateInserted' => 'datetime',
'InsertUserID' => 'int',
'DateUpdated' => 'datetime',
'UpdateUserID' => 'int',
'Format' => 'varchar(20)',
'Body' => 'text',
'Score' => 'float'
*/
$comment_Map = array('CommentID' => 'CommentID', 'DiscussionID' => 'DiscussionID', 'AuthUserID' => 'InsertUserID', 'DateCreated' => 'DateInserted', 'EditUserID' => 'UpdateUserID', 'DateEdited' => 'DateUpdated', 'Body' => 'Body', 'FormatType' => 'Format');
$ex->exportTable('Comment', "\n SELECT\n c.*\n FROM :_Comment c\n JOIN :_Discussion d\n ON c.DiscussionID = d.DiscussionID\n WHERE d.FirstCommentID <> c.CommentID\n AND c.Deleted = '0'\n AND coalesce(d.WhisperUserID, 0) = 0\n AND coalesce(c.WhisperUserID, 0) = 0", $comment_Map);
$ex->exportTable('UserDiscussion', "\n SELECT\n w.UserID,\n w.DiscussionID,\n w.CountComments,\n w.LastViewed as DateLastViewed,\n case when b.UserID is not null then 1 else 0 end AS Bookmarked\n FROM :_UserDiscussionWatch w\n LEFT JOIN :_UserBookmark b\n ON w.DiscussionID = b.DiscussionID AND w.UserID = b.UserID");
// Conversations
// Create a mapping tables for conversations.
// These mapping tables are used to group comments that a) are in the same discussion and b) are from and to the same users.
$ex->query("drop table if exists z_pmto");
$ex->query("create table z_pmto (\n CommentID int,\n UserID int,\n primary key(CommentID, UserID)\n )");
$ex->query("insert ignore z_pmto (\n CommentID,\n UserID\n)\nselect distinct\n CommentID,\n AuthUserID\nfrom :_Comment\nwhere coalesce(WhisperUserID, 0) <> 0");
$ex->query("insert ignore z_pmto (\n CommentID,\n UserID\n)\nselect distinct\n CommentID,\n WhisperUserID\nfrom :_Comment\nwhere coalesce(WhisperUserID, 0) <> 0");
$ex->query("insert ignore z_pmto (\n CommentID,\n UserID\n)\nselect distinct\n c.CommentID,\n d.AuthUserID\nfrom :_Discussion d\njoin :_Comment c\n on c.DiscussionID = d.DiscussionID\nwhere coalesce(d.WhisperUserID, 0) <> 0");
$ex->query("insert ignore z_pmto (\n CommentID,\n UserID\n)\nselect distinct\n c.CommentID,\n d.WhisperUserID\nfrom :_Discussion d\njoin :_Comment c\n on c.DiscussionID = d.DiscussionID\nwhere coalesce(d.WhisperUserID, 0) <> 0");
$ex->query("insert ignore z_pmto (\n CommentID,\n UserID\n)\nselect distinct\n c.CommentID,\n c.AuthUserID\nfrom :_Discussion d\njoin :_Comment c\n on c.DiscussionID = d.DiscussionID\nwhere coalesce(d.WhisperUserID, 0) <> 0");
$ex->query("drop table if exists z_pmto2");
$ex->query("create table z_pmto2 (\n CommentID int,\n UserIDs varchar(250),\n primary key (CommentID)\n)");
$ex->query("insert z_pmto2 (\n CommentID,\n UserIDs\n)\nselect\n CommentID,\n group_concat(UserID order by UserID)\nfrom z_pmto\ngroup by CommentID");
$ex->query("drop table if exists z_pm");
$ex->query("create table z_pm (\n CommentID int,\n DiscussionID int,\n UserIDs varchar(250),\n GroupID int\n)");
$ex->query("insert ignore z_pm (\n CommentID,\n DiscussionID\n)\nselect\n CommentID,\n DiscussionID\nfrom :_Comment\nwhere coalesce(WhisperUserID, 0) <> 0");
$ex->query("insert ignore z_pm (\n CommentID,\n DiscussionID\n)\nselect\n c.CommentID,\n c.DiscussionID\nfrom :_Discussion d\njoin :_Comment c\n on c.DiscussionID = d.DiscussionID\nwhere coalesce(d.WhisperUserID, 0) <> 0");
$ex->query("update z_pm pm\njoin z_pmto2 t\n on t.CommentID = pm.CommentID\nset pm.UserIDs = t.UserIDs");
$ex->query("drop table if exists z_pmgroup");
$ex->query("create table z_pmgroup (\n GroupID int,\n DiscussionID int,\n UserIDs varchar(250)\n)");
$ex->query("insert z_pmgroup (\n GroupID,\n DiscussionID,\n UserIDs\n)\nselect\n min(pm.CommentID),\n pm.DiscussionID,\n t2.UserIDs\nfrom z_pm pm\njoin z_pmto2 t2\n on pm.CommentID = t2.CommentID\ngroup by pm.DiscussionID, t2.UserIDs");
$ex->query("create index z_idx_pmgroup on z_pmgroup (DiscussionID, UserIDs)");
$ex->query("create index z_idx_pmgroup2 on z_pmgroup (GroupID)");
$ex->query("update z_pm pm\njoin z_pmgroup g\n on pm.DiscussionID = g.DiscussionID and pm.UserIDs = g.UserIDs\nset pm.GroupID = g.GroupID");
$conversation_Map = array('AuthUserID' => 'InsertUserID', 'DateCreated' => 'DateInserted', 'DiscussionID' => array('Column' => 'DiscussionID', 'Type' => 'int'), 'CommentID' => 'ConversationID', 'Name' => array('Column' => 'Subject', 'Type' => 'varchar(255)'));
$ex->exportTable('Conversation', "select c.*, d.Name\nfrom :_Comment c\njoin :_Discussion d\n on d.DiscussionID = c.DiscussionID\njoin z_pmgroup g\n on g.GroupID = c.CommentID;", $conversation_Map);
// ConversationMessage.
$conversationMessage_Map = array('CommentID' => 'MessageID', 'GroupID' => 'ConversationID', 'Body' => 'Body', 'FormatType' => 'Format', 'AuthUserID' => 'InsertUserID', 'DateCreated' => 'DateInserted');
$ex->exportTable('ConversationMessage', "select c.*, pm.GroupID\nfrom z_pm pm\njoin :_Comment c\n on pm.CommentID = c.CommentID", $conversationMessage_Map);
// UserConversation
/*
'UserID' => 'int',
'ConversationID' => 'int',
'LastMessageID' => 'int'
*/
$userConversation_Map = array('UserID' => 'UserID', 'GroupID' => 'ConversationID');
$ex->exportTable('UserConversation', "select distinct\n pm.GroupID,\n t.UserID\nfrom z_pmto t\njoin z_pm pm\n on pm.CommentID = t.CommentID", $userConversation_Map);
$ex->query("drop table z_pmto");
$ex->query("drop table z_pmto2");
$ex->query("drop table z_pm");
$ex->query("drop table z_pmgroup");
// Media
if ($ex->exists('Attachment')) {
$media_Map = array('AttachmentID' => 'MediaID', 'Name' => 'Name', 'MimeType' => 'Type', 'Size' => 'Size', 'Path' => array('Column' => 'Path', 'Filter' => array($this, 'stripMediaPath')), 'UserID' => 'InsertUserID', 'DateCreated' => 'DateInserted', 'CommentID' => 'ForeignID');
$ex->exportTable('Media', "select a.*, 'comment' as ForeignTable from :_Attachment a", $media_Map);
}
// End
$ex->endExport();
}
/**
* Forum-specific export format.
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('posts');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->sourcePrefix = 'phpbb_';
// Begin
$ex->beginExport('', 'phpBB 3.*', array('HashMethod' => 'phpBB'));
// Users.
// Grab the avatar salt.
$px = $ex->getValue("select config_value from :_config where config_name = 'avatar_salt'", '');
$cdn = $this->param('cdn', '');
$user_Map = array('user_id' => 'UserID', 'username' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'user_password' => 'Password', 'user_email' => 'Email', 'user_timezone' => 'HourOffset', 'user_posts' => array('Column' => 'CountComments', 'Type' => 'int'), 'photo' => 'Photo', 'user_rank' => 'RankID', 'user_ip' => 'LastIPAddress');
$ex->exportTable('User', "select *,\n case user_avatar_type\n when 1 then concat('{$cdn}', 'phpbb/', '{$px}', '_', user_id, substr(user_avatar from locate('.', user_avatar)))\n when 2 then user_avatar\n else null end as photo,\n FROM_UNIXTIME(nullif(user_regdate, 0)) as DateFirstVisit,\n FROM_UNIXTIME(nullif(user_lastvisit, 0)) as DateLastActive,\n FROM_UNIXTIME(nullif(user_regdate, 0)) as DateInserted,\n ban_userid is not null as Banned\n from :_users\n left join :_banlist bl ON (ban_userid = user_id)\n ", $user_Map);
// ":_" will be replace by database prefix
// Roles
$role_Map = array('group_id' => 'RoleID', 'group_name' => 'Name', 'group_desc' => 'Description');
$ex->exportTable('Role', 'select * from :_groups', $role_Map);
$this->exportUserNotes();
// Ranks.
$rank_Map = array('rank_id' => 'RankID', 'level' => array('Column' => 'Level', 'Filter' => function ($value) {
static $level = 0;
$level++;
return $level;
}), 'rank_title' => 'Name', 'title2' => 'Label', 'rank_min' => array('Column' => 'Attributes', 'Filter' => function ($value, $field, $row) {
$result = array();
if ($row['rank_min']) {
$result['Criteria']['CountPosts'] = $row['rank_min'];
}
if ($row['rank_special']) {
$result['Criteria']['Manual'] = true;
}
return serialize($result);
}));
$ex->exportTable('Rank', "\n select r.*, r.rank_title as title2, 0 as level\n from :_ranks r\n order by rank_special, rank_min;", $rank_Map);
// Permissions.
$ex->exportTable('Permission', "select\n group_id as RoleID,\n case\n when group_name like '%Guest%' or group_name like 'BOTS' then 'View'\n when group_name like '%Mod%' then 'View,Garden.SignIn.Allow,Garden.Profiles.Edit,Garden.Settings.View,Vanilla.Discussions.Add,Vanilla.Comments.Add,Garden.Moderation.Manage'\n when group_name like '%Admin%' then 'All'\n else 'View,Garden.SignIn.Allow,Garden.Profiles.Edit,Vanilla.Discussions.Add,Vanilla.Comments.Add'\n end as _Permissions\n from :_groups");
// UserRoles
$userRole_Map = array('user_id' => 'UserID', 'group_id' => 'RoleID');
$ex->exportTable('UserRole', 'select user_id, group_id from :_users
union
select user_id, group_id from :_user_group', $userRole_Map);
// Signatutes.
$userMeta_Map = array('user_id' => 'UserID', 'name' => 'Name', 'user_sig' => array('Column' => 'Value', 'Filter' => array($this, 'removeBBCodeUIDs')));
$ex->exportTable('UserMeta', "\n select user_id, 'Plugin.Signatures.Sig' as name, user_sig, user_sig_bbcode_uid as bbcode_uid\n from :_users\n where length(user_sig) > 1\n\n union\n\n select user_id, 'Plugin.Signatures.Format', 'BBCode', null\n from :_users\n where length(user_sig) > 1\n ", $userMeta_Map);
// Categories
$category_Map = array('forum_id' => 'CategoryID', 'forum_name' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'forum_desc' => 'Description', 'left_id' => 'Sort');
$ex->exportTable('Category', "select *,\n nullif(parent_id,0) as ParentCategoryID\n from :_forums", $category_Map);
// Discussions
$discussion_Map = array('topic_id' => 'DiscussionID', 'forum_id' => 'CategoryID', 'topic_poster' => 'InsertUserID', 'topic_title' => 'Name', 'Format' => 'Format', 'topic_views' => 'CountViews', 'topic_first_post_id' => array('Column' => 'FirstCommentID', 'Type' => 'int'), 'type' => 'Type');
$ex->exportTable('Discussion', "select t.*,\n 'BBCode' as Format,\n case t.topic_status when 1 then 1 else 0 end as Closed,\n case t.topic_type when 1 then 1 else 0 end as Announce,\n case when t.poll_start > 0 then 'poll' else null end as type,\n FROM_UNIXTIME(t.topic_time) as DateInserted,\n FROM_UNIXTIME(t.topic_last_post_time) as DateUpdated,\n FROM_UNIXTIME(t.topic_last_post_time) as DateLastComment\n from :_topics t", $discussion_Map);
// Comments
$comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'post_text' => array('Column' => 'Body', 'Filter' => array($this, 'removeBBCodeUIDs')), 'Format' => 'Format', 'poster_id' => 'InsertUserID', 'poster_ip' => array('Column' => 'InsertIPAddress', 'Filter' => 'forceIP4'), 'post_edit_user' => 'UpdateUserID');
$ex->exportTable('Comment', "select p.*,\n 'BBCode' as Format,\n FROM_UNIXTIME(p.post_time) as DateInserted,\n FROM_UNIXTIME(nullif(p.post_edit_time,0)) as DateUpdated\n from :_posts p", $comment_Map);
// UserDiscussion
$userDiscussion_Map = array('user_id' => 'UserID', 'topic_id' => 'DiscussionID');
$ex->exportTable('UserDiscussion', "select b.*,\n 1 as Bookmarked\n from :_bookmarks b", $userDiscussion_Map);
// Conversations tables.
$ex->query("drop table if exists z_pmto;");
$ex->query("create table z_pmto (\nid int unsigned,\nuserid int unsigned,\nprimary key(id, userid));");
$ex->query("insert ignore z_pmto (id, userid)\nselect msg_id, author_id\nfrom :_privmsgs;");
$ex->query("insert ignore z_pmto (id, userid)\nselect msg_id, user_id\nfrom :_privmsgs_to;");
$ex->query("insert ignore z_pmto (id, userid)\nselect msg_id, author_id\nfrom :_privmsgs_to;");
$ex->query("drop table if exists z_pmto2;");
$ex->query("create table z_pmto2 (\n id int unsigned,\n userids varchar(250),\n primary key (id)\n);");
$ex->query("insert ignore z_pmto2 (id, userids)\nselect\n id,\n group_concat(userid order by userid)\nfrom z_pmto\ngroup by id;");
$ex->query("drop table if exists z_pm;");
$ex->query("create table z_pm (\n id int unsigned,\n subject varchar(255),\n subject2 varchar(255),\n userids varchar(250),\n groupid int unsigned\n);");
$ex->query("insert z_pm (\n id,\n subject,\n subject2,\n userids\n)\nselect\n pm.msg_id,\n pm.message_subject,\n case when pm.message_subject like 'Re: %' then trim(substring(pm.message_subject, 4)) else pm.message_subject end as subject2,\n t.userids\nfrom :_privmsgs pm\njoin z_pmto2 t\n on t.id = pm.msg_id;");
$ex->query("create index z_idx_pm on z_pm (id);");
$ex->query("drop table if exists z_pmgroup;");
$ex->query("create table z_pmgroup (\n groupid int unsigned,\n subject varchar(255),\n userids varchar(250)\n);");
$ex->query("insert z_pmgroup (\n groupid,\n subject,\n userids\n)\nselect\n min(pm.id),\n pm.subject2,\n pm.userids\nfrom z_pm pm\ngroup by pm.subject2, pm.userids;");
$ex->query("create index z_idx_pmgroup on z_pmgroup (subject, userids);");
$ex->query("create index z_idx_pmgroup2 on z_pmgroup (groupid);");
$ex->query("update z_pm pm\njoin z_pmgroup g\n on pm.subject2 = g.subject and pm.userids = g.userids\nset pm.groupid = g.groupid;");
// Polls.
$poll_Map = array('poll_id' => 'PollID', 'poll_title' => 'Name', 'topic_id' => 'DiscussionID', 'topic_time' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'topic_poster' => 'InsertUserID', 'anonymous' => 'Anonymous');
$ex->exportTable('Poll', "\n select distinct\n t.*,\n t.topic_id as poll_id,\n 1 as anonymous\n from :_poll_options po\n join :_topics t\n on po.topic_id = t.topic_id", $poll_Map);
$pollOption_Map = array('id' => 'PollOptionID', 'poll_option_id' => 'Sort', 'topic_id' => 'PollID', 'poll_option_text' => 'Body', 'format' => 'Format', 'poll_option_total' => 'CountVotes', 'topic_time' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'topic_poster' => 'InsertUserID');
$ex->exportTable('PollOption', "\n select\n po.*,\n po.poll_option_id * 1000000 + po.topic_id as id,\n 'Html' as format,\n t.topic_time,\n t.topic_poster\n from :_poll_options po\n join :_topics t\n on po.topic_id = t.topic_id", $pollOption_Map);
$pollVote_Map = array('vote_user_id' => 'UserID', 'id' => 'PollOptionID');
$ex->exportTable('PollVote', "\n select v.*, v.poll_option_id * 1000000 + v.topic_id as id\n from :_poll_votes v", $pollVote_Map);
// Conversations.
$conversation_Map = array('msg_id' => 'ConversationID', 'author_id' => 'InsertUserID', 'RealSubject' => array('Column' => 'Subject', 'Type' => 'varchar(250)', 'Filter' => array('Phpbb2', 'EntityDecode')));
$ex->exportTable('Conversation', "select\n g.subject as RealSubject,\n pm.*,\n from_unixtime(pm.message_time) as DateInserted\nfrom :_privmsgs pm\njoin z_pmgroup g\n on g.groupid = pm.msg_id", $conversation_Map);
// Coversation Messages.
$conversationMessage_Map = array('msg_id' => 'MessageID', 'groupid' => 'ConversationID', 'message_text' => array('Column' => 'Body', 'Filter' => array($this, 'removeBBCodeUIDs')), 'author_id' => 'InsertUserID');
$ex->exportTable('ConversationMessage', "select\n pm.*,\n pm2.groupid,\n 'BBCode' as Format,\n FROM_UNIXTIME(pm.message_time) as DateInserted\n from :_privmsgs pm\n join z_pm pm2\n on pm.msg_id = pm2.id", $conversationMessage_Map);
// User Conversation.
$userConversation_Map = array('userid' => 'UserID', 'groupid' => 'ConversationID');
$ex->exportTable('UserConversation', "select\n g.groupid,\n t.userid\n from z_pmto t\n join z_pmgroup g\n on g.groupid = t.id;", $userConversation_Map);
$ex->query('drop table if exists z_pmto');
$ex->query('drop table if exists z_pmto2;');
$ex->query('drop table if exists z_pm;');
$ex->query('drop table if exists z_pmgroup;');
// Media.
$cdn = $this->param('cdn', '');
$media_Map = array('attach_id' => 'MediaID', 'real_filename' => 'Name', 'thumb_path' => array('Column' => 'ThumbPath', 'Filter' => array($this, 'filterThumbnailData')), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')), 'post_id' => 'InsertUserID', 'mimetype' => 'Type', 'filesize' => 'Size');
$ex->exportTable('Media', "select\n case when a.post_msg_id = t.topic_first_post_id then 'discussion' else 'comment' end as ForeignTable,\n case when a.post_msg_id = t.topic_first_post_id then a.topic_id else a.post_msg_id end as ForeignID,\n concat('{$cdn}','FileUpload/', a.physical_filename, '.', a.extension) as Path,\n concat('{$cdn}','FileUpload/', a.physical_filename, '.', a.extension) as thumb_path,\n 128 as thumb_width,\n FROM_UNIXTIME(a.filetime) as DateInserted,\n a.*\nfrom :_attachments a\njoin :_topics t\n on a.topic_id = t.topic_id", $media_Map);
$this->exportBanList();
// End
$ex->endExport();
}
/**
* Export each table one at a time.
*
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
// Allow limited export of 1 category via ?forumid=ID
$forumID = $this->param('forumid');
if ($forumID) {
$forumWhere = ' and t.forumid ' . (strpos($forumID, ', ') === false ? "= {$forumID}" : "in ({$forumID})");
} else {
$forumWhere = '';
}
$characterSet = $ex->getCharacterSet('post');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Begin
$ex->beginExport('', 'vBulletin 3.* and 4.*');
$this->exportBlobs($this->param('files'), $this->param('avatars'));
if ($this->param('noexport')) {
$ex->comment('Skipping the export.');
$ex->endExport();
return;
}
// Check to see if there is a max date.
$minDate = $this->param('mindate');
if ($minDate) {
$minDate = strtotime($minDate);
$ex->comment("Min topic date ({$minDate}): " . date('c', $minDate));
}
$now = time();
$cdn = $this->param('cdn', '');
// Grab all of the ranks.
$ranks = $ex->get("select * from :_usertitle order by minposts desc", 'usertitleid');
// Users
$user_Map = array('usertitle' => array('Column' => 'Title', 'Filter' => function ($value) {
return trim(strip_tags(str_replace(' ', ' ', $value)));
}), 'posts' => array('Column' => 'RankID', 'Filter' => function ($value) use($ranks) {
// Look up the posts in the ranks table.
foreach ($ranks as $rankID => $row) {
if ($value >= $row['minposts']) {
return $rankID;
}
}
return null;
}));
// Use file avatar or the result of our blob export?
if ($this->getConfig('usefileavatar')) {
$user_Map['filephoto'] = 'Photo';
} else {
$user_Map['customphoto'] = 'Photo';
}
$ex->exportTable('User', "\n select\n u.userid as UserID,\n u.username as Name,\n u.email as Email,\n u.referrerid as InviteUserID,\n u.timezoneoffset as HourOffset,\n u.timezoneoffset as HourOffset,\n u.ipaddress as LastIPAddress,\n u.ipaddress as InsertIPAddress,\n u.usertitle,\n u.posts,\n concat(`password`, salt) as Password,\n date_format(birthday_search, get_format(DATE, 'ISO')) as DateOfBirth,\n from_unixtime(joindate) as DateFirstVisit,\n from_unixtime(lastvisit) as DateLastActive,\n from_unixtime(joindate) as DateInserted,\n from_unixtime(lastactivity) as DateUpdated,\n case when avatarrevision > 0 then concat('{$cdn}', 'userpics/avatar', u.userid, '_', avatarrevision, '.gif')\n when av.avatarpath is not null then av.avatarpath\n else null\n end as filephoto,\n {$this->avatarSelect},\n case when ub.userid is not null then 1 else 0 end as Banned,\n 'vbulletin' as HashMethod\n from :_user u\n left join :_customavatar a on u.userid = a.userid\n left join :_avatar av on u.avatarid = av.avatarid\n left join :_userban ub on u.userid = ub.userid and ub.liftdate <= now()\n ", $user_Map);
// ":_" will be replace by database prefix
// Roles
$role_Map = array('usergroupid' => 'RoleID', 'title' => 'Name', 'description' => 'Description');
$ex->exportTable('Role', 'select * from :_usergroup', $role_Map);
// UserRoles
$userRole_Map = array('userid' => 'UserID', 'usergroupid' => 'RoleID');
$ex->query("drop table if exists VbulletinRoles");
$ex->query("create table VbulletinRoles (userid int unsigned not null, usergroupid int unsigned not null)");
# Put primary groups into tmp table
$ex->query("insert into VbulletinRoles (userid, usergroupid) select userid, usergroupid from :_user");
# Put stupid CSV column into tmp table
$secondaryRoles = $ex->query("select userid, usergroupid, membergroupids from :_user");
if (is_resource($secondaryRoles)) {
while (($row = @mysql_fetch_assoc($secondaryRoles)) !== false) {
if ($row['membergroupids'] != '') {
$groups = explode(',', $row['membergroupids']);
foreach ($groups as $groupID) {
if (!$groupID) {
continue;
}
$ex->query("insert into VbulletinRoles (userid, usergroupid) values({$row['userid']},{$groupID})", true);
}
}
}
}
# Export from our tmp table and drop
$ex->exportTable('UserRole', 'select distinct userid, usergroupid from VbulletinRoles', $userRole_Map);
$ex->query("drop table if exists VbulletinRoles");
// Permissions.
$permissions_Map = array('usergroupid' => 'RoleID', 'title' => array('Column' => 'Garden.SignIn.Allow', 'Filter' => array($this, 'signInPermission')), 'genericpermissions' => array('Column' => 'GenericPermissions', 'type' => 'int'), 'forumpermissions' => array('Column' => 'ForumPermissions', 'type' => 'int'));
$this->addPermissionColumns(self::$permissions, $permissions_Map);
$ex->exportTable('Permission', 'select * from :_usergroup', $permissions_Map);
$ex->query("drop table if exists VbulletinUserMeta");
// UserMeta
$ex->query("\n create table VbulletinUserMeta(\n `UserID` int not null,\n `Name` varchar(255) not null,\n `Value` text not null\n );\n ");
# Standard vB user data
$userFields = array('usertitle' => 'Title', 'homepage' => 'Website', 'styleid' => 'StyleID');
if ($ex->exists('user', array('skype')) === true) {
$userFields['skype'] = 'Skype';
}
foreach ($userFields as $field => $insertAs) {
$ex->query("\n insert into VbulletinUserMeta (UserID, Name, Value)\n select\n userid,\n 'Profile.{$insertAs}',\n {$field}\n from :_user where {$field} != '' and {$field} != 'http://'\n ");
}
if ($ex->exists('phrase', array('product', 'fieldname')) === true) {
# Dynamic vB user data (userfield)
$profileFields = $ex->query("\n select\n varname,\n text\n from :_phrase\n where product='vbulletin'\n and fieldname='cprofilefield'\n and varname like 'field%_title'\n ");
if (is_resource($profileFields)) {
$profileQueries = array();
while ($field = @mysql_fetch_assoc($profileFields)) {
$column = str_replace('_title', '', $field['varname']);
$name = preg_replace('/[^a-zA-Z0-9\\s_-]/', '', $field['text']);
$profileQueries[] = "\n insert into VbulletinUserMeta(UserID, Name, Value)\n select\n userid,\n 'Profile." . $name . "',\n " . $column . "\n from :_userfield\n where " . $column . " != ''\n ";
}
foreach ($profileQueries as $query) {
$ex->query($query);
}
}
}
// Users meta informations
$ex->exportTable('UserMeta', "\n select\n userid as UserID,\n 'Plugin.Signatures.Sig' as Name,\n signature as Value\n from :_usertextfield\n where nullif(signature, '') is not null\n\n union\n\n select\n userid,\n 'Plugin.Signatures.Format',\n 'BBCode'\n from :_usertextfield\n where nullif(signature, '') is not null\n\n union\n\n select\n *\n from VbulletinUserMeta\n ");
// Ranks
$rank_Map = array('usertitleid' => 'RankID', 'title' => 'Name', 'title2' => 'Label', 'minposts' => array('Column' => 'Attributes', 'Filter' => function ($value) {
$result = array('Criteria' => array('CountPosts' => $value));
return serialize($result);
}), 'level' => array('Column' => 'Level', 'Filter' => function ($value) {
static $level = 1;
return $level++;
}));
$ex->exportTable('Rank', "\n select\n ut.*,\n ut.title as title2,\n 0 as level\n from :_usertitle as ut\n order by ut.minposts\n ", $rank_Map);
// Categories
$category_Map = array('title' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'displayorder' => array('Column' => 'Sort', 'Type' => 'int'));
$ex->exportTable('Category', "\n select\n f.forumid as CategoryID,\n f.description as Description,\n f.parentid as ParentCategoryID,\n f.title,\n f.displayorder\n from :_forum as f\n where 1 = 1\n {$forumWhere}\n ", $category_Map);
$minDiscussionID = false;
$minDiscussionWhere = false;
if ($minDate) {
$minDiscussionID = $ex->getValue("\n select max(threadid)\n from :_thread\n where dateline < {$minDate}\n ", false);
$minDiscussionID2 = $ex->getValue("\n select min(threadid)\n from :_thread\n where dateline >= {$minDate}\n ", false);
// The two discussion IDs should be the same, but let's average them.
$minDiscussionID = floor(($minDiscussionID + $minDiscussionID2) / 2);
$ex->comment('Min topic id: ' . $minDiscussionID);
}
// Discussions
$discussion_Map = array('title' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'));
if ($ex->destination == 'database') {
// Remove the filter from the title so that this doesn't take too long.
$ex->HTMLDecoderDb('thread', 'title', 'threadid');
unset($discussion_Map['title']['Filter']);
}
if ($minDiscussionID) {
$minDiscussionWhere = "and t.threadid > {$minDiscussionID}";
}
$ex->exportTable('Discussion', "\n select\n t.threadid as DiscussionID,\n t.forumid as CategoryID,\n t.postuserid as InsertUserID,\n t.postuserid as UpdateUserID,\n t.views as CountViews,\n t.title,\n p.postid as ForeignID,\n p.ipaddress as InsertIPAddress,\n p.pagetext as Body,\n 'BBCode' as Format,\n replycount+1 as CountComments,\n convert(ABS(open-1), char(1)) as Closed,\n if(convert(sticky, char(1)) > 0, 2, 0) as Announce,\n from_unixtime(t.dateline) as DateInserted,\n from_unixtime(lastpost) as DateLastComment,\n if (t.pollid > 0, 'Poll', null) as Type\n from :_thread as t\n left join :_deletionlog as d on d.type='thread' and d.primaryid=t.threadid\n left join :_post as p on p.postid = t.firstpostid\n where d.primaryid is null\n and t.visible = 1\n {$minDiscussionWhere}\n {$forumWhere}\n ", $discussion_Map);
// Comments
$comment_Map = array();
if ($minDiscussionID) {
$minDiscussionWhere = "and p.threadid > {$minDiscussionID}";
}
$ex->exportTable('Comment', "\n select\n p.postid as CommentID,\n p.threadid as DiscussionID,\n p.pagetext as Body,\n p.ipaddress as InsertIPAddress,\n 'BBCode' as Format,\n p.userid as InsertUserID,\n p.userid as UpdateUserID,\n from_unixtime(p.dateline) as DateInserted\n from :_post as p\n inner join :_thread as t on p.threadid = t.threadid\n left join :_deletionlog as d on (d.type='post' and d.primaryid=p.postid)\n where p.postid <> t.firstpostid\n and d.primaryid is null\n and p.visible = 1\n {$minDiscussionWhere}\n {$forumWhere}\n ", $comment_Map);
// UserDiscussion
if ($minDiscussionID) {
$minDiscussionWhere = "where st.threadid > {$minDiscussionID}";
}
if ($ex->exists('threadread', array('readtime')) === true) {
$threadReadTime = 'from_unixtime(tr.readtime)';
$threadReadJoin = 'left join :_threadread as tr on tr.userid = st.userid and tr.threadid = st.threadid';
} else {
$threadReadTime = 'now()';
$threadReadJoin = null;
}
$ex->exportTable('UserDiscussion', "\n select\n st.userid as UserID,\n st.threadid as DiscussionID,\n {$threadReadTime} as DateLastViewed,\n '1' as Bookmarked\n from :_subscribethread as st\n {$threadReadJoin}\n {$minDiscussionWhere}\n ");
/*$ex->exportTable('UserDiscussion', "
select
tr.userid as UserID,
tr.threadid as DiscussionID,
from_unixtime(tr.readtime) as DateLastViewed,
case
when st.threadid is not null then 1
else 0
end as Bookmarked
from :_threadread tr
left join :_subscribethread st on tr.userid = st.userid and tr.threadid = st.threadid
");*/
// Activity (from visitor messages in vBulletin 3.8+)
if ($ex->exists('visitormessage') === true) {
if ($minDiscussionID) {
$minDiscussionWhere = "and dateline > {$minDiscussionID}";
}
$activity_Map = array('postuserid' => 'RegardingUserID', 'userid' => 'ActivityUserID', 'pagetext' => 'Story', 'NotifyUserID' => 'NotifyUserID', 'Format' => 'Format');
$ex->exportTable('Activity', "\n select\n vm.*,\n '{RegardingUserID,you} → {ActivityUserID,you}' as HeadlineFormat,\n from_unixtime(vm.dateline) as DateInserted,\n from_unixtime(vm.dateline) as DateUpdated,\n inet_ntoa(vm.ipaddress) as InsertIPAddress,\n vm.postuserid as InsertUserID,\n -1 as NotifyUserID,\n 'BBCode' as Format,\n 'WallPost' as ActivityType\n from :_visitormessage as vm\n where state='visible'\n {$minDiscussionWhere}\n ", $activity_Map);
}
$this->_exportConversations($minDate);
$this->_exportPolls();
// Media
if ($ex->exists('attachment') === true) {
$this->exportMedia($minDiscussionID);
}
// IP Ban list
$ipBanlist = $this->param('ipbanlist');
if ($ipBanlist) {
$ex->query("drop table if exists z_ipbanlist");
$ex->query("\n create table z_ipbanlist(\n id int(11) unsigned not null auto_increment,\n ipaddress varchar(50) default null,\n primary key (id),\n unique key ipaddress (ipaddress)\n ) engine=InnoDB default charset=utf8\n ");
$result = $ex->query("select value from :_setting where varname = 'banip'");
$row = mysql_fetch_assoc($result);
if ($row) {
$insertSql = 'insert ignore into z_ipbanlist(ipaddress) values ';
$ipString = str_replace("\r", "", $row['value']);
$IPs = explode("\n", $ipString);
foreach ($IPs as $IP) {
$IP = trim($IP);
if (empty($IP)) {
continue;
}
$insertSql .= '(\'' . mysql_real_escape_string($IP) . '\'), ';
}
$insertSql = substr($insertSql, 0, -2);
$ex->query($insertSql);
$ban_Map = array();
$ex->exportTable('Ban', "\n select\n 'IPAddress' as BanType,\n ipaddress as BanValue,\n 'Imported ban' as Notes,\n NOW() as DateInserted\n from z_ipbanlist\n ", $ban_Map);
$ex->query('drop table if exists z_ipbanlist');
}
}
// End
$ex->endExport();
}
/**
*
* @param ExportModel $ex
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('nodes');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$ex->beginExport('', 'vBulletin 5 Connect');
$this->exportBlobs($this->param('files'), $this->param('avatars'));
if ($this->param('noexport')) {
$ex->comment('Skipping the export.');
$ex->endExport();
return;
}
$cdn = $this->param('cdn', '');
// Grab all of the ranks.
$ranks = $ex->get("select * from :_usertitle order by minposts desc", 'usertitleid');
// Users
$user_Map = array('userid' => 'UserID', 'username' => 'Name', 'password2' => 'Password', 'email' => 'Email', 'referrerid' => 'InviteUserID', 'timezoneoffset' => 'HourOffset', 'ipaddress' => 'LastIPAddress', 'ipaddress2' => 'InsertIPAddress', 'usertitle' => 'Title', 'posts' => array('Column' => 'RankID', 'Filter' => function ($value) use($ranks) {
// Look up the posts in the ranks table.
foreach ($ranks as $rankID => $row) {
if ($value >= $row['minposts']) {
return $rankID;
}
}
return null;
}));
// Use file avatar or the result of our blob export?
if ($this->getConfig('usefileavatar')) {
$user_Map['filephoto'] = 'Photo';
} else {
$user_Map['customphoto'] = 'Photo';
}
// vBulletin 5.1 changes the hash to crypt(md5(password), hash).
// Switches from password & salt to token (and scheme & secret).
// The scheme appears to be crypt()'s default and secret looks uselessly redundant.
if ($ex->exists('user', 'token') !== true) {
$passwordSQL = "concat(`password`, salt) as password2, 'vbulletin' as HashMethod,";
} else {
// vB 5.1 already concats the salt to the password as token, BUT ADDS A SPACE OF COURSE.
$passwordSQL = "replace(token, ' ', '') as password2, case when scheme = 'legacy' then 'vbulletin' else 'vbulletin5' end as HashMethod,";
}
$ex->exportTable('User', "\n select\n u.*,\n ipaddress as ipaddress2,\n {$passwordSQL}\n DATE_FORMAT(birthday_search,GET_FORMAT(DATE,'ISO')) as DateOfBirth,\n FROM_UNIXTIME(joindate) as DateFirstVisit,\n FROM_UNIXTIME(lastvisit) as DateLastActive,\n FROM_UNIXTIME(joindate) as DateInserted,\n FROM_UNIXTIME(lastactivity) as DateUpdated,\n case when avatarrevision > 0 then concat('{$cdn}', 'userpics/avatar', u.userid, '_', avatarrevision, '.gif')\n when av.avatarpath is not null then av.avatarpath\n else null\n end as filephoto,\n {$this->avatarSelect},\n case when ub.userid is not null then 1 else 0 end as Banned\n from :_user u\n left join :_customavatar a on u.userid = a.userid\n left join :_avatar av on u.avatarid = av.avatarid\n left join :_userban ub\n on u.userid = ub.userid\n and ub.liftdate <= now()\n ;", $user_Map);
// ":_" will be replace by database prefix
//ipdata - contains all IP records for user actions: view,visit,register,logon,logoff
// Roles
$role_Map = array('usergroupid' => 'RoleID', 'title' => 'Name', 'description' => 'Description');
$ex->exportTable('Role', 'select * from :_usergroup', $role_Map);
// UserRoles
$userRole_Map = array('userid' => 'UserID', 'usergroupid' => 'RoleID');
$ex->query("CREATE TEMPORARY TABLE VbulletinRoles (userid INT UNSIGNED not null, usergroupid INT UNSIGNED not null)");
# Put primary groups into tmp table
$ex->query("insert into VbulletinRoles (userid, usergroupid) select userid, usergroupid from :_user");
# Put stupid CSV column into tmp table
$secondaryRoles = $ex->query("select userid, usergroupid, membergroupids from :_user", true);
if (is_resource($secondaryRoles)) {
while (($row = @mysql_fetch_assoc($secondaryRoles)) !== false) {
if ($row['membergroupids'] != '') {
$groups = explode(',', $row['membergroupids']);
foreach ($groups as $groupID) {
$ex->query("insert into VbulletinRoles (userid, usergroupid) values({$row['userid']},{$groupID})", true);
}
}
}
}
# Export from our tmp table and drop
$ex->exportTable('UserRole', 'select distinct userid, usergroupid from VbulletinRoles', $userRole_Map);
$ex->query("DROP TABLE IF EXISTS VbulletinRoles");
// Permissions.
$permissions_Map = array('usergroupid' => 'RoleID', 'title' => array('Column' => 'Garden.SignIn.Allow', 'Filter' => array($this, 'signInPermission')), 'genericpermissions' => array('Column' => 'GenericPermissions', 'type' => 'int'), 'forumpermissions' => array('Column' => 'ForumPermissions', 'type' => 'int'));
$this->addPermissionColumns(self::$permissions, $permissions_Map);
$ex->exportTable('Permission', 'select * from :_usergroup', $permissions_Map);
// UserMeta
/*$ex->Query("CREATE TEMPORARY TABLE VbulletinUserMeta (`UserID` INT not null ,`Name` VARCHAR( 255 ) not null ,`Value` text not null)");
# Standard vB user data
$UserFields = array('usertitle' => 'Title', 'homepage' => 'Website', 'skype' => 'Skype', 'styleid' => 'StyleID');
foreach($UserFields as $Field => $InsertAs)
$ex->Query("insert into VbulletinUserMeta (UserID, Name, Value) select userid, 'Profile.$InsertAs', $Field from :_user where $Field != ''");
# Dynamic vB user data (userfield)
$ProfileFields = $ex->Query("select varname, text from :_phrase where product='vbulletin' and fieldname='cprofilefield' and varname like 'field%_title'");
if (is_resource($ProfileFields)) {
$ProfileQueries = array();
while ($Field = @mysql_fetch_assoc($ProfileFields)) {
$Column = str_replace('_title', '', $Field['varname']);
$Name = preg_replace('/[^a-zA-Z0-9_-\s]/', '', $Field['text']);
$ProfileQueries[] = "insert into VbulletinUserMeta (UserID, Name, Value)
select userid, 'Profile.".$Name."', ".$Column." from :_userfield where ".$Column." != ''";
}
foreach ($ProfileQueries as $Query) {
$ex->Query($Query);
}
}*/
// Ranks
$rank_Map = array('usertitleid' => 'RankID', 'title' => 'Name', 'title2' => 'Label', 'minposts' => array('Column' => 'Attributes', 'Filter' => function ($value) {
$result = array('Criteria' => array('CountPosts' => $value));
return serialize($result);
}), 'level' => array('Column' => 'Level', 'Filter' => function ($value) {
static $level = 1;
return $level++;
}));
$ex->exportTable('Rank', "\n select\n ut.*,\n ut.title as title2,\n 0 as level\n from :_usertitle ut\n order by ut.minposts\n ;", $rank_Map);
/// Signatures
// usertextfields.signature
// Ignore
// usertextfields.ignorelist
/// Notes
/// Warnings
/// Activity (Wall)
// Category.
$channels = array();
$categoryIDs = array();
$homeID = 0;
$privateMessagesID = 0;
// Filter Channels down to Forum tree
$channelResult = $ex->query("\n select\n n.*\n from :_node n\n left join :_contenttype ct on n.contenttypeid = ct.contenttypeid\n where ct.class = 'Channel'\n ;");
while ($channel = mysql_fetch_array($channelResult)) {
$channels[$channel['nodeid']] = $channel;
if ($channel['title'] == 'Forum') {
$homeID = $channel['nodeid'];
}
if ($channel['title'] == 'Private Messages') {
$privateMessagesID = $channel['nodeid'];
}
}
if (!$homeID) {
exit("Missing node 'Forum'");
}
// Go thru the category list 6 times to build a (up to) 6-deep hierarchy
$categoryIDs[] = $homeID;
for ($i = 0; $i < 6; $i++) {
foreach ($channels as $channel) {
if (in_array($channel['nodeid'], $categoryIDs)) {
continue;
}
if (in_array($channel['parentid'], $categoryIDs)) {
$categoryIDs[] = $channel['nodeid'];
}
}
}
// Drop 'Forum' from the tree
if (($key = array_search($homeID, $categoryIDs)) !== false) {
unset($categoryIDs[$key]);
}
$category_Map = array('nodeid' => 'CategoryID', 'title' => 'Name', 'description' => 'Description', 'userid' => 'InsertUserID', 'parentid' => 'ParentCategoryID', 'urlident' => 'UrlCode', 'displayorder' => array('Column' => 'Sort', 'Type' => 'int'), 'lastcontentid' => 'LastDiscussionID', 'textcount' => 'CountComments', 'totalcount' => 'CountDiscussions');
// Categories are Channels that were found in the Forum tree
// If parent was 'Forum' set the parent to Root instead (-1)
$ex->exportTable('Category', "\n select\n n.*,\n FROM_UNIXTIME(publishdate) as DateInserted,\n if(parentid={$homeID},-1,parentid) as parentid\n from :_node n\n where nodeid in (" . implode(',', $categoryIDs) . ")\n ;", $category_Map);
/// Permission
//permission - nodeid,(user)groupid, and it gets worse from there.
// Discussion.
$discussion_Map = array('nodeid' => 'DiscussionID', 'type' => 'Type', 'title' => 'Name', 'userid' => 'InsertUserID', 'rawtext' => 'Body', 'parentid' => 'CategoryID', 'lastcontentid' => 'LastCommentID', 'lastauthorid' => 'LastCommentUserID');
$discussionQuery = "\n select\n n.nodeid,\n null as type,\n n.title,\n n.userid,\n t.rawtext,\n n.parentid,\n n.lastcontentid,\n n.lastauthorid,\n 'BBCode' as Format,\n FROM_UNIXTIME(publishdate) as DateInserted,\n v.count as CountViews,\n convert(ABS(n.open-1),char(1)) as Closed,\n if(convert(n.sticky,char(1))>0,2,0) as Announce,\n null as PollID\n from :_node n\n left join :_contenttype ct on n.contenttypeid = ct.contenttypeid\n left join :_nodeview v on v.nodeid = n.nodeid\n left join :_text t on t.nodeid = n.nodeid\n where ct.class = 'Text'\n and n.showpublished = 1\n and parentid in (" . implode(',', $categoryIDs) . ")\n ;";
// Polls need to be wrapped in a discussion so we are gonna need to postpone discussion creations
if ($this->_getPollsCount()) {
// NOTE: Only polls that are directly under a channel (discussion) will be exported.
// Vanilla poll plugin does not support polls as comments.
$ex->query("drop table if exists vBulletinDiscussionTable;");
// Create a temporary table to hold old discussions and to create new discussions for polls
$ex->query("\n create table `vBulletinDiscussionTable` (\n `nodeid` int(10) unsigned not null AUTO_INCREMENT,\n `type` varchar(10) default null,\n `title` varchar(255) default null,\n `userid` int(10) unsigned default null,\n `rawtext` mediumtext,\n `parentid` int(11) not null,\n `lastcontentid` int(11) not null default '0',\n `lastauthorid` int(10) unsigned not null default '0',\n `Format` varchar(10) not null,\n `DateInserted` datetime not null,\n `CountViews` int(11) not null default '1',\n `Closed` tinyint(4) not null default '0',\n `Announce` tinyint(4) not null default '0',\n `PollID` int(10) unsigned, /* used to create poll->discussion mapping */\n primary key (`nodeid`)\n )\n ;");
$ex->query("insert into vBulletinDiscussionTable {$discussionQuery}");
$this->_generatePollsDiscussion();
// Export discussions
$sql = "\n select\n nodeid,\n type,\n title,\n userid,\n rawtext,\n parentid,\n lastcontentid,\n lastauthorid,\n Format,\n DateInserted,\n CountViews,\n Closed,\n Announce\n from vBulletinDiscussionTable\n ;";
$ex->exportTable('Discussion', $sql, $discussion_Map);
// Export polls
$this->_exportPolls();
// Cleanup tmp table
$ex->query("drop table vBulletinDiscussionTable;");
} else {
$ex->exportTable('Discussion', $discussionQuery, $discussion_Map);
}
// UserDiscussion
$userDiscussion_Map = array('discussionid' => 'DiscussionID', 'userid' => 'InsertUserID');
// Should be able to inner join `discussionread` for DateLastViewed
// but it's blank in my sample data so I don't trust it.
$ex->exportTable('UserDiscussion', "\n select\n s.*,\n 1 as Bookmarked,\n NOW() as DateLastViewed\n from :_subscribediscussion s\n ;", $userDiscussion_Map);
// Comment.
$comment_Map = array('nodeid' => 'CommentID', 'rawtext' => 'Body', 'userid' => 'InsertUserID', 'parentid' => 'DiscussionID');
$ex->exportTable('Comment', "\n select\n n.*,\n t.rawtext,\n 'BBCode' as Format,\n FROM_UNIXTIME(publishdate) as DateInserted\n from :_node n\n left join :_contenttype c on n.contenttypeid = c.contenttypeid\n left join :_text t on t.nodeid = n.nodeid\n where c.class = 'Text'\n and n.showpublished = 1\n and parentid not in (" . implode(',', $categoryIDs) . ")\n ;", $comment_Map);
/// Drafts
// autosavetext table
$instance = $this;
// Media
$media_Map = array('nodeid' => 'MediaID', 'filename' => 'Name', 'extension' => array('Column' => 'Type', 'Filter' => array($this, 'buildMimeType')), 'Path2' => array('Column' => 'Path', 'Filter' => array($this, 'buildMediaPath')), 'ThumbPath2' => array('Column' => 'ThumbPath', 'Filter' => function ($value, $field, $row) use($instance) {
$filteredData = $this->filterThumbnailData($value, $field, $row);
if ($filteredData) {
return $instance->buildMediaPath($value, $field, $row);
} else {
return null;
}
}), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')), 'width' => 'ImageWidth', 'height' => 'ImageHeight', 'filesize' => 'Size');
$ex->exportTable('Media', "\n select\n a.*,\n filename as Path2,\n filename as ThumbPath2,\n 128 as thumb_width,\n FROM_UNIXTIME(f.dateline) as DateInserted,\n f.userid as userid,\n f.userid as InsertUserID,\n if (f.width,f.width,1) as width,\n if (f.height,f.height,1) as height,\n n.parentid as ForeignID,\n f.extension,\n f.filesize,\n if(n2.parentid in (" . implode(',', $categoryIDs) . "),'discussion','comment') as ForeignTable\n from :_attach a\n left join :_node n on n.nodeid = a.nodeid\n left join :_filedata f on f.filedataid = a.filedataid\n left join :_node n2 on n.parentid = n2.nodeid\n where a.visible = 1\n ;", $media_Map);
// left join :_contenttype c on n.contenttypeid = c.contenttypeid
// Conversations.
$conversation_Map = array('nodeid' => 'ConversationID', 'userid' => 'InsertUserID', 'totalcount' => 'CountMessages', 'title' => 'Subject');
$ex->exportTable('Conversation', "\n select\n n.*,\n n.nodeid as FirstMessageID,\n FROM_UNIXTIME(n.publishdate) as DateInserted\n from :_node n\n left join :_text t on t.nodeid = n.nodeid\n where parentid = {$privateMessagesID}\n and t.rawtext <> ''\n ;", $conversation_Map);
// Conversation Messages.
$conversationMessage_Map = array('nodeid' => 'MessageID', 'rawtext' => 'Body', 'userid' => 'InsertUserID');
$ex->exportTable('ConversationMessage', "\n select\n n.*,\n t.rawtext,\n 'BBCode' as Format,\n if(n.parentid<>{$privateMessagesID},n.parentid,n.nodeid) as ConversationID,\n FROM_UNIXTIME(n.publishdate) as DateInserted\n from :_node n\n left join :_contenttype c on n.contenttypeid = c.contenttypeid\n left join :_text t on t.nodeid = n.nodeid\n where c.class = 'PrivateMessage'\n and t.rawtext <> ''\n ;", $conversationMessage_Map);
// User Conversation.
$userConversation_Map = array('userid' => 'UserID', 'nodeid' => 'ConversationID', 'deleted' => 'Deleted');
// would be nicer to do an intermediary table to sum s.msgread for uc.CountReadMessages
$ex->exportTable('UserConversation', "\n select\n s.*\n from :_sentto s\n ;", $userConversation_Map);
/// Groups
// class='SocialGroup'
// class='SocialGroupDiscussion'
// class='SocialGroupMessage'
$ex->endExport();
}
/**
* Main export process.
*
* @param ExportModel $ex
* @see $_Structures in ExportModel for allowed destination tables & columns.
*/
public function forumExport($ex)
{
// Get the characterset for the comments.
// Usually the comments table is the best target for this.
$characterSet = $ex->getCharacterSet('CommentsTableNameGoesHere');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Reiterate the platform name here to be included in the porter file header.
$ex->beginExport('', 'Proper Platform Name Goes Here');
// It's usually a good idea to do the porting in the approximate order laid out here.
// User.
// Map as much as possible using the $x_Map array for clarity.
// Key is always the source column name.
// Value is either the destination column or an array of meta data, usually Column & Filter.
// If it's a meta array, 'Column' is the destination column name and 'Filter' is a method name to run it thru.
// Here, 'HTMLDecoder' is a method in ExportModel. Check there for available filters.
// Assume no filter is needed and only use one if you encounter issues.
$user_Map = array('Author_ID' => 'UserID', 'Username' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'));
// This is the query that the x_Map array above will be mapped against.
// Therefore, our select statement must cover all the "source" columns.
// It's frequently necessary to add joins, where clauses, and more to get the data we want.
// The :_ before the table name is the placeholder for the prefix designated. It gets swapped on the fly.
$ex->exportTable('User', "\n select u.*\n from :_User u\n ", $user_Map);
// Role.
// The Vanilla roles table will be wiped by any import. If your current platform doesn't have roles,
// you can hard code new ones into the select statement. See Vanilla's defaults for a good example.
$role_Map = array('Group_ID' => 'RoleID', 'Name' => 'Name');
$ex->exportTable('Role', "\n select *\n from :_tblGroup", $role_Map);
// User Role.
// Really simple matchup.
// Note that setting Admin=1 on the User table trumps all roles & permissions with "owner" privileges.
// Whatever account you select during the import will get the Admin=1 flag to prevent permissions issues.
$userRole_Map = array('Author_ID' => 'UserID', 'Group_ID' => 'RoleID');
$ex->exportTable('UserRole', "\n select u.*\n from :_tblAuthor u", $userRole_Map);
// Permission.
// Feel free to add a permission export if this is a major platform or it will see reuse.
// For small or custom jobs, it's usually not worth it. Just fix them afterward.
// UserMeta.
// This is an example of pulling Signatures into Vanilla's UserMeta table.
// This is often a good place for any extraneous data on the User table too.
// The Profile Extender addon uses the namespace "Profile.[FieldName]"
// You can add the appropriately-named fields after the migration and profiles will auto-populate with the migrated data.
$ex->exportTable('UserMeta', "\n select\n Author_ID as UserID,\n 'Plugin.Signatures.Sig' as `Name`,\n Signature as `Value`\n from :_tblAuthor\n where Signature <> ''");
// Category.
// Be careful to not import hundreds of categories. Try translating huge schemas to Tags instead.
// Numeric category slugs aren't allowed in Vanilla, so be careful to sidestep those.
// Don't worry about rebuilding the TreeLeft & TreeRight properties. Vanilla can fix this afterward
// if you just get the Sort and ParentIDs correct.
$category_Map = array('Forum_ID' => 'CategoryID', 'Forum_name' => 'Name');
$ex->exportTable('Category', "\n select *\n from :_tblCategory c\n ", $category_Map);
// Discussion.
// A frequent issue is for the OPs content to be on the comment/post table, so you may need to join it.
$discussion_Map = array('Topic_ID' => 'DiscussionID', 'Forum_ID' => 'CategoryID', 'Author_ID' => 'InsertUserID', 'Subject' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'));
// It's easier to convert between Unix time and MySQL datestamps during the db query.
$ex->exportTable('Discussion', "\n select *,\n FROM_UNIXTIME(Message_date) as Message_date\n from :_tblTopic t\n join :_tblThread th\n on t.Start_Thread_ID = th.Thread_ID", $discussion_Map);
// Comment.
// This is where big migrations are going to get bogged down.
// Be sure you have indexes created for any columns you are joining on.
$comment_Map = array('Thread_ID' => 'CommentID', 'Topic_ID' => 'DiscussionID', 'Author_ID' => 'InsertUserID', 'IP_addr' => 'InsertIPAddress', 'Message' => array('Column' => 'Body'), 'Format' => 'Format', 'Message_date' => array('Column' => 'DateInserted'));
$ex->exportTable('Comment', "\n select th.*\n from :_tblThread th", $comment_Map);
// UserDiscussion.
// This is the table for assigning bookmarks/subscribed threads.
// Media.
// Attachment data goes here. Vanilla attachments are files under the /uploads folder.
// This is usually the trickiest step because you need to translate file paths.
// If you need to export blobs from the database, see the vBulletin porter.
// Conversations.
// Private messages often involve the most data manipulation.
// If you need a large number of complex SQL statements, consider making it a separate method
// to keep the main process easy to understand. Pass $ex as a parameter if you do.
$ex->endExport();
}
/**
* Main export process.
*
* @param ExportModel $ex
* @see $_Structures in ExportModel for allowed destination tables & columns.
*/
public function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('posts_text');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Reiterate the platform name here to be included in the porter file header.
$ex->beginExport('', 'jforum');
// User.
$user_Map = array('user_id' => 'UserID', 'username' => 'Name', 'user_email' => 'Email', 'user_regdate' => 'DateInserted', 'user_regdate2' => 'DateFirstVisit', 'user_posts' => 'CountComments', 'user_avatar' => 'Photo', 'deleted' => 'Deleted', 'user_from' => 'Location', 'user_biography' => 'About');
$ex->exportTable('User', "\n select u.*,\n 'Reset' as HashMethod,\n user_regdate as user_regdate2\n from :_users u\n ", $user_Map);
// Role.
$role_Map = array('group_id' => 'RoleID', 'group_name' => 'Name', 'group_description' => 'Description');
$ex->exportTable('Role', "\n select *\n from :_groups", $role_Map);
// User Role.
$userRole_Map = array('user_id' => 'UserID', 'group_id' => 'RoleID');
$ex->exportTable('UserRole', "\n select u.*\n from :_user_groups u", $userRole_Map);
// UserMeta.
$ex->exportTable('UserMeta', "\n select user_id as UserID,\n 'Profile.Website' as `Name`,\n user_website as `Value`\n from :_users\n where user_website is not null\n\n union\n\n select user_id, 'Plugins.Signatures.Sig', user_sig\n from :_users where user_sig is not null\n\n union\n\n select user_id, 'Plugins.Signatures.Format', 'BBCode'\n from :_users where user_sig is not null\n\n union\n\n select user_id, 'Profile.Occupation', user_occ\n from :_users where user_occ is not null\n\n union\n\n select user_id, 'Profile.Interests', user_interests\n from :_users where user_interests is not null\n ");
// Category.
// _categories is tier 1, _forum is tier 2.
// Overlapping IDs, so fast-forward _categories by 1000.
$category_Map = array();
$ex->exportTable('Category', "\n select\n c.categories_id+1000 as CategoryID,\n -1 as ParentCategoryID,\n c.title as Name,\n null as Description,\n 1 as Depth,\n c.display_order as Sort\n from :_categories c\n\n union\n\n select\n f.forum_id as CategoryID,\n categories_id+1000 as ParentCategoryID,\n forum_name as Name,\n forum_desc as Description,\n 2 as Depth,\n null as Sort\n from :_forums f\n ", $category_Map);
// Discussion.
$discussion_Map = array('topic_id' => 'DiscussionID', 'forum_id' => 'CategoryID', 'user_id' => 'InsertUserID', 'topic_time' => 'DateInserted', 'topic_title' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'topic_views' => 'CountViews', 'topic_replies' => 'CountComments', 'topic_status' => 'Closed', 'topic_type' => 'Announce', 'post_text' => 'Body');
// It's easier to convert between Unix time and MySQL datestamps during the db query.
$ex->exportTable('Discussion', "\n select *,\n t.forum_id as forum_id,\n if(t.topic_type>0,1,0) as topic_type,\n 'BBCode' as Format\n from :_topics t\n left join :_posts_text p\n on t.topic_first_post_id = p.post_id", $discussion_Map);
// Comment.
$comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'user_id' => 'InsertUserID', 'poster_ip' => 'InsertIPAddress', 'post_text' => 'Body', 'post_time' => 'DateInserted', 'post_edit_time' => 'DateUpdated');
$ex->exportTable('Comment', "\n select p.*, t.post_text, 'BBCode' as Format\n from :_posts p\n left join :_posts_text t\n on p.post_id = t.post_id\n where p.post_id not in (select topic_first_post_id from :_topics)", $comment_Map);
// UserDiscussion.
// Guessing table is called "_watch" because they are all bookmarks.
$userDiscussion_Map = array('topic_id' => 'DiscussionID', 'user_id' => 'UserID');
$ex->exportTable('UserDiscussion', "\n select *,\n 1 as Bookmarked,\n if(is_read,NOW(),null) as DateLastViewed\n from :_topics_watch w", $userDiscussion_Map);
// Conversation.
// Thread using tmp table based on the pair of users talking.
$ex->query('drop table if exists z_conversation;');
$ex->query('create table z_conversation (
ConversationID int unsigned NOT NULL AUTO_INCREMENT,
LowUserID int unsigned,
HighUserID int unsigned,
PRIMARY KEY (ConversationID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;');
$ex->query('insert into z_conversation (LowUserID, HighUserID)
select least(privmsgs_from_userid, privmsgs_to_userid),
greatest(privmsgs_from_userid, privmsgs_to_userid)
from :_privmsgs
group by least(privmsgs_from_userid, privmsgs_to_userid),
greatest(privmsgs_from_userid, privmsgs_to_userid)');
// Replying on /dba/counts to rebuild most of this data later.
$conversation_Map = array('privmsgs_from_userid' => 'InsertUserID', 'privmsgs_date' => 'DateInserted', 'privmsgs_subject' => 'Subject');
$ex->exportTable('Conversation', "\n select p.*, c.ConversationID\n from :_privmsgs p\n left join z_conversation c on c.HighUserID = greatest(p.privmsgs_from_userid, p.privmsgs_to_userid)\n and c.LowUserID = least(p.privmsgs_from_userid, p.privmsgs_to_userid)\n group by least(privmsgs_from_userid, privmsgs_to_userid),\n greatest(privmsgs_from_userid, privmsgs_to_userid)", $conversation_Map);
// Conversation Message.
// Messages with the same timestamps are sent/received copies.
// Yes that'd probably break down on huge sites but it's too convenient to pass up for now.
$message_Map = array('privmsgs_id' => 'MessageID', 'privmsgs_from_userid' => 'InsertUserID', 'privmsgs_date' => 'DateInserted', 'privmsgs_text' => 'Body');
$ex->exportTable('ConversationMessage', "\n select *, c.ConversationID, 'BBCode' as Format\n from :_privmsgs p\n left join :_privmsgs_text t on t.privmsgs_id = p.privmsgs_id\n left join z_conversation c on c.LowUserID = least(privmsgs_from_userid, privmsgs_to_userid)\n and c.HighUserID = greatest(privmsgs_from_userid, privmsgs_to_userid)\n group by privmsgs_date", $message_Map);
// UserConversation
$ex->exportTable('UserConversation', "\n select ConversationID, LowUserID as UserID, NOW() as DateLastViewed from z_conversation\n union\n select ConversationID, HighUserID as UserID, NOW() as DateLastViewed from z_conversation\n ");
// Needs afterward: update GDN_UserConversation set CountReadMessages = (select count(MessageID) from GDN_ConversationMessage where GDN_ConversationMessage.ConversationID = GDN_UserConversation.ConversationID)
$ex->endExport();
}
/**
* Forum-specific export format.
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('posts');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
// Begin
$ex->beginExport('', 'bbPress 1.*', array('HashMethod' => 'Vanilla'));
// Users
$user_Map = array('ID' => 'UserID', 'user_login' => 'Name', 'user_pass' => 'Password', 'user_email' => 'Email', 'user_registered' => 'DateInserted');
$ex->exportTable('User', "select * from :_users", $user_Map);
// ":_" will be replace by database prefix
// Roles
$ex->exportTable('Role', "select 1 as RoleID, 'Guest' as Name\n union select 2, 'Key Master'\n union select 3, 'Administrator'\n union select 4, 'Moderator'\n union select 5, 'Member'\n union select 6, 'Inactive'\n union select 7, 'Blocked'");
// UserRoles
$userRole_Map = array('user_id' => 'UserID');
$ex->exportTable('UserRole', "select distinct\n user_id,\n case when locate('keymaster', meta_value) <> 0 then 2\n when locate('administrator', meta_value) <> 0 then 3\n when locate('moderator', meta_value) <> 0 then 4\n when locate('member', meta_value) <> 0 then 5\n when locate('inactive', meta_value) <> 0 then 6\n when locate('blocked', meta_value) <> 0 then 7\n else 1 end as RoleID\n from :_usermeta\n where meta_key = 'bb_capabilities'", $userRole_Map);
// Categories
$category_Map = array('forum_id' => 'CategoryID', 'forum_name' => 'Name', 'forum_desc' => 'Description', 'forum_slug' => 'UrlCode', 'left_order' => 'Sort');
$ex->exportTable('Category', "select *,\n lower(forum_slug) as forum_slug,\n nullif(forum_parent,0) as ParentCategoryID\n from :_forums", $category_Map);
// Discussions
$discussion_Map = array('topic_id' => 'DiscussionID', 'forum_id' => 'CategoryID', 'topic_poster' => 'InsertUserID', 'topic_title' => 'Name', 'Format' => 'Format', 'topic_start_time' => 'DateInserted', 'topic_sticky' => 'Announce');
$ex->exportTable('Discussion', "select t.*,\n 'Html' as Format,\n case t.topic_open when 0 then 1 else 0 end as Closed\n from :_topics t\n where topic_status = 0", $discussion_Map);
// Comments
$comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'post_text' => array('Column' => 'Body', 'Filter' => 'bbPressTrim'), 'Format' => 'Format', 'poster_id' => 'InsertUserID', 'post_time' => 'DateInserted');
$ex->exportTable('Comment', "select p.*,\n 'Html' as Format\n from :_posts p\n where post_status = 0", $comment_Map);
// Conversations.
// The export is different depending on the table layout.
$PM = $ex->exists('bbpm', array('ID', 'pm_title', 'pm_from', 'pm_to', 'pm_text', 'sent_on', 'pm_thread'));
$conversationVersion = '';
if ($PM === true) {
// This is from an old version of the plugin.
$conversationVersion = 'old';
} elseif (is_array($PM) && count(array_intersect(array('ID', 'pm_from', 'pm_text', 'sent_on', 'pm_thread'), $PM)) == 0) {
// This is from a newer version of the plugin.
$conversationVersion = 'new';
}
if ($conversationVersion) {
// Conversation.
$conv_Map = array('pm_thread' => 'ConversationID', 'pm_from' => 'InsertUserID');
$ex->exportTable('Conversation', "select *, from_unixtime(sent_on) as DateInserted\n from :_bbpm\n where thread_depth = 0", $conv_Map);
// ConversationMessage.
$convMessage_Map = array('ID' => 'MessageID', 'pm_thread' => 'ConversationID', 'pm_from' => 'InsertUserID', 'pm_text' => array('Column' => 'Body', 'Filter' => 'bbPressTrim'));
$ex->exportTable('ConversationMessage', 'select *, from_unixtime(sent_on) as DateInserted
from :_bbpm', $convMessage_Map);
// UserConversation.
$ex->query("create temporary table bbpmto (UserID int, ConversationID int)");
if ($conversationVersion == 'new') {
$to = $ex->query("select object_id, meta_value from :_meta where object_type = 'bbpm_thread' and meta_key = 'to'", true);
if (is_resource($to)) {
while (($row = @mysql_fetch_assoc($to)) !== false) {
$thread = $row['object_id'];
$tos = explode(',', trim($row['meta_value'], ','));
$toIns = '';
foreach ($tos as $toID) {
$toIns .= "({$toID},{$thread}),";
}
$toIns = trim($toIns, ',');
$ex->query("insert bbpmto (UserID, ConversationID) values {$toIns}", true);
}
mysql_free_result($to);
$ex->exportTable('UserConversation', 'select * from bbpmto');
}
} else {
$conUser_Map = array('pm_thread' => 'ConversationID', 'pm_from' => 'UserID');
$ex->exportTable('UserConversation', 'select distinct
pm_thread,
pm_from,
del_sender as Deleted
from :_bbpm
union
select distinct
pm_thread,
pm_to,
del_reciever
from :_bbpm', $conUser_Map);
}
}
// End
$ex->endExport();
}
