/** * 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 * @param string $tableName */ protected function exportTable($ex, $tableName) { // Make sure the table exists. if (!$ex->exists($tableName)) { return; } $ex->exportTable($tableName, "select * from :_{$tableName}"); }
/** * 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(); }
/** * 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(); }
/** * 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; } // 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(); }
/** * * @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) { $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. * @param ExportModel $ex */ protected function forumExport($ex) { $ex->sourcePrefix = 'wp_'; $characterSet = $ex->getCharacterSet('posts'); if ($characterSet) { $ex->characterSet = $characterSet; } // Begin $ex->beginExport('', 'SimplePress 1.*', array('HashMethod' => 'Vanilla')); // Users $user_Map = array('user_id' => 'UserID', 'display_name' => 'Name', 'user_pass' => 'Password', 'user_email' => 'Email', 'user_registered' => 'DateInserted', 'lastvisit' => 'DateLastActive'); $ex->exportTable('User', "select m.*, u.user_pass, u.user_email, u.user_registered\n from :_users u\n join :_sfmembers m\n on u.ID = m.user_id;", $user_Map); // Roles $role_Map = array('usergroup_id' => 'RoleID', 'usergroup_name' => 'Name', 'usergroup_desc' => 'Description'); $ex->exportTable('Role', "select\n usergroup_id,\n usergroup_name,\n usergroup_desc\n from :_sfusergroups\n\n union\n\n select\n 100,\n 'Administrators',\n ''", $role_Map); // Permissions. $ex->exportTable('Permission', "select\n usergroup_id as RoleID,\ncase\n when usergroup_name like 'Guest%' then 'View'\n when usergroup_name like 'Member%' then 'View,Garden.SignIn.Allow,Garden.Profiles.Edit,Vanilla.Discussions.Add,Vanilla.Comments.Add'\n when usergroup_name like 'Mod%' then 'View,Garden.SignIn.Allow,Garden.Profiles.Edit,Garden.Settings.View,Vanilla.Discussions.Add,Vanilla.Comments.Add,Garden.Moderation.Manage'\nend as _Permissions\n from :_sfusergroups\n\n union\n\n select 100, 'All'"); // UserRoles $userRole_Map = array('user_id' => 'UserID', 'usergroup_id' => 'RoleID'); $ex->exportTable('UserRole', "select\n m.user_id,\n m.usergroup_id\n from :_sfmemberships m\n\n union\n\n select\n um.user_id,\n 100\n from :_usermeta um\n where um.meta_key = 'wp_capabilities'\n and um.meta_value like '%PF Manage Forums%'", $userRole_Map); // Categories $category_Map = array('forum_id' => 'CategoryID', 'forum_name' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'forum_desc' => 'Description', 'forum_seq' => 'Sort', 'form_slug' => 'UrlCode', 'parent_id' => 'ParentCategoryID'); $ex->exportTable('Category', "\n select\n f.forum_id,\n f.forum_name,\n f.forum_seq,\n f.forum_desc,\n lower(f.forum_slug) as forum_slug,\n case when f.parent = 0 then f.group_id + 1000 else f.parent end as parent_id\n from :_sfforums f\n\n union\n\n select\n 1000 + g.group_id,\n g.group_name,\n g.group_seq,\n g.group_desc,\n null,\n null\n from :_sfgroups g", $category_Map); // Discussions $discussion_Map = array('topic_id' => 'DiscussionID', 'forum_id' => 'CategoryID', 'user_id' => 'InsertUserID', 'topic_name' => 'Name', 'Format' => 'Format', 'topic_date' => 'DateInserted', 'topic_pinned' => 'Announce', 'topic_slug' => array('Column' => 'Slug', 'Type' => 'varchar(200)')); $ex->exportTable('Discussion', "select t.*,\n 'Html' as Format\n from :_sftopics t", $discussion_Map); if ($ex->exists('sftags')) { // Tags $tag_Map = array('tag_id' => 'TagID', 'tag_name' => 'Name'); $ex->exportTable('Tag', "select * from :_sftags", $tag_Map); if ($ex->exists('sftagmeta')) { $tagDiscussion_Map = array('tag_id' => 'TagID', 'topic_id' => 'DiscussionID'); $ex->exportTable('TagDiscussion', "select * from :_sftagmeta", $tagDiscussion_Map); } } // Comments $comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'post_content' => 'Body', 'Format' => 'Format', 'user_id' => 'InsertUserID', 'post_date' => 'DateInserted', 'poster_ip' => 'InsertIPAddress'); $ex->exportTable('Comment', "select p.*,\n 'Html' as Format\n from :_sfposts p", $comment_Map); // Conversation. $conv_Map = array('message_id' => 'ConversationID', 'from_id' => 'InsertUserID', 'sent_date' => 'DateInserted'); $ex->exportTable('Conversation', "select *\n from :_sfmessages\n where is_reply = 0", $conv_Map); // ConversationMessage. $convMessage_Map = array('message_id' => 'MessageID', 'from_id' => 'InsertUserID', 'message' => array('Column' => 'Body')); $ex->exportTable('ConversationMessage', 'select c.message_id as ConversationID, m.* from :_sfmessages c join :_sfmessages m on (m.is_reply = 0 and m.message_id = c.message_id) or (m.is_reply = 1 and c.is_reply = 0 and m.message_slug = c.message_slug and m.from_id in (c.from_id, c.to_id) and m.to_id in (c.from_id, c.to_id));', $convMessage_Map); // UserConversation $userConv_Map = array('message_id' => 'ConversationID', 'from_id' => 'UserID'); $ex->exportTable('UserConversation', 'select message_id, from_id from :_sfmessages where is_reply = 0 union select message_id, to_id from :_sfmessages where is_reply = 0', $userConv_Map); // End $ex->endExport(); }