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