/**
* 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();
}
/**
* 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();
}
/**
*
* @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();
}