/**
*
* @param ExportModel $Ex
*/
public function ForumExport($Ex)
{
// Determine the character set
$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,\n case when scheme = 'legacy' then 'vbulletin' else 'vbulletin5' end as HashMethod,";
}
$Ex->ExportTable('User', "select 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\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
//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 ut.*, ut.title as title2, 0 as level\n from :_usertitle ut\n order by ut.minposts", $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("select n.* from :_node n\n left join :_contenttype c on n.contenttypeid = c.contenttypeid\n where c.class = 'Channel'");
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', "select 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', 'title' => 'Name', 'userid' => 'InsertUserID', 'rawtext' => 'Body', 'parentid' => 'CategoryID', 'lastcontentid' => 'LastCommentID', 'lastauthorid' => 'LastCommentUserID');
$Ex->ExportTable('Discussion', "select n.*,\n t.rawtext,\n 'BBCode' as Format,\n FROM_UNIXTIME(publishdate) as DateInserted,\n v.count as CountViews,\n convert(ABS(open-1),char(1)) as Closed,\n if(convert(sticky,char(1))>0,2,0) as Announce\n from :_node n\n left join :_contenttype c on n.contenttypeid = c.contenttypeid\n left join :_nodeview v on v.nodeid = n.nodeid\n left join :_text t on t.nodeid = n.nodeid\n where c.class = 'Text'\n and n.showpublished = 1\n and parentid in (" . implode(',', $CategoryIDs) . ")\n ", $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', "select 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', "select 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
/// Poll
// class='Poll'
// 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' => array($this, 'BuildMediaPath')), 'width' => 'ImageWidth', 'height' => 'ImageHeight', 'filesize' => 'Size');
$Ex->ExportTable('Media', "select a.*,\n filename as Path2,\n filename as ThumbPath2,\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 'local' as StorageMethod,\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', "select 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 <> ''", $Conversation_Map);
// Conversation Messages.
$ConversationMessage_Map = array('nodeid' => 'MessageID', 'rawtext' => 'Body', 'userid' => 'InsertUserID');
$Ex->ExportTable('ConversationMessage', "select 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 <> ''", $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', "select s.*\n from :_sentto s\n ;", $UserConversation_Map);
/// Groups
// class='SocialGroup'
// class='SocialGroupDiscussion'
// class='SocialGroupMessage'
$Ex->EndExport();
}
