Ejemplo n.º 1
0
 /**
  * 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} &rarr; {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();
 }
Ejemplo n.º 2
0
 /**
  * Forum-specific export format.
  * @param ExportModel $ex
  */
 protected function forumExport($ex)
 {
     $characterSet = $ex->getCharacterSet('posts');
     if ($characterSet) {
         $ex->characterSet = $characterSet;
     }
     $ex->sourcePrefix = 'phpbb_';
     // Begin
     $ex->beginExport('', 'phpBB 3.*', array('HashMethod' => 'phpBB'));
     // Users.
     // Grab the avatar salt.
     $px = $ex->getValue("select config_value from :_config where config_name = 'avatar_salt'", '');
     $cdn = $this->param('cdn', '');
     $user_Map = array('user_id' => 'UserID', 'username' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'user_password' => 'Password', 'user_email' => 'Email', 'user_timezone' => 'HourOffset', 'user_posts' => array('Column' => 'CountComments', 'Type' => 'int'), 'photo' => 'Photo', 'user_rank' => 'RankID', 'user_ip' => 'LastIPAddress');
     $ex->exportTable('User', "select *,\n            case user_avatar_type\n               when 1 then concat('{$cdn}', 'phpbb/', '{$px}', '_', user_id, substr(user_avatar from locate('.', user_avatar)))\n               when 2 then user_avatar\n               else null end as photo,\n            FROM_UNIXTIME(nullif(user_regdate, 0)) as DateFirstVisit,\n            FROM_UNIXTIME(nullif(user_lastvisit, 0)) as DateLastActive,\n            FROM_UNIXTIME(nullif(user_regdate, 0)) as DateInserted,\n            ban_userid is not null as Banned\n         from :_users\n            left join :_banlist bl ON (ban_userid = user_id)\n         ", $user_Map);
     // ":_" will be replace by database prefix
     // Roles
     $role_Map = array('group_id' => 'RoleID', 'group_name' => 'Name', 'group_desc' => 'Description');
     $ex->exportTable('Role', 'select * from :_groups', $role_Map);
     $this->exportUserNotes();
     // Ranks.
     $rank_Map = array('rank_id' => 'RankID', 'level' => array('Column' => 'Level', 'Filter' => function ($value) {
         static $level = 0;
         $level++;
         return $level;
     }), 'rank_title' => 'Name', 'title2' => 'Label', 'rank_min' => array('Column' => 'Attributes', 'Filter' => function ($value, $field, $row) {
         $result = array();
         if ($row['rank_min']) {
             $result['Criteria']['CountPosts'] = $row['rank_min'];
         }
         if ($row['rank_special']) {
             $result['Criteria']['Manual'] = true;
         }
         return serialize($result);
     }));
     $ex->exportTable('Rank', "\n         select r.*, r.rank_title as title2, 0 as level\n         from :_ranks r\n         order by rank_special, rank_min;", $rank_Map);
     // Permissions.
     $ex->exportTable('Permission', "select\n         group_id as RoleID,\n         case\n            when group_name like '%Guest%' or group_name like 'BOTS' then 'View'\n            when group_name like '%Mod%' then 'View,Garden.SignIn.Allow,Garden.Profiles.Edit,Garden.Settings.View,Vanilla.Discussions.Add,Vanilla.Comments.Add,Garden.Moderation.Manage'\n            when group_name like '%Admin%' then 'All'\n            else 'View,Garden.SignIn.Allow,Garden.Profiles.Edit,Vanilla.Discussions.Add,Vanilla.Comments.Add'\n         end as _Permissions\n         from :_groups");
     // UserRoles
     $userRole_Map = array('user_id' => 'UserID', 'group_id' => 'RoleID');
     $ex->exportTable('UserRole', 'select user_id, group_id from :_users
      union
      select user_id, group_id from :_user_group', $userRole_Map);
     // Signatutes.
     $userMeta_Map = array('user_id' => 'UserID', 'name' => 'Name', 'user_sig' => array('Column' => 'Value', 'Filter' => array($this, 'removeBBCodeUIDs')));
     $ex->exportTable('UserMeta', "\n         select user_id, 'Plugin.Signatures.Sig' as name, user_sig, user_sig_bbcode_uid as bbcode_uid\n         from :_users\n         where length(user_sig) > 1\n\n         union\n\n         select user_id, 'Plugin.Signatures.Format', 'BBCode', null\n         from :_users\n         where length(user_sig) > 1\n         ", $userMeta_Map);
     // Categories
     $category_Map = array('forum_id' => 'CategoryID', 'forum_name' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'forum_desc' => 'Description', 'left_id' => 'Sort');
     $ex->exportTable('Category', "select *,\n         nullif(parent_id,0) as ParentCategoryID\n         from :_forums", $category_Map);
     // Discussions
     $discussion_Map = array('topic_id' => 'DiscussionID', 'forum_id' => 'CategoryID', 'topic_poster' => 'InsertUserID', 'topic_title' => 'Name', 'Format' => 'Format', 'topic_views' => 'CountViews', 'topic_first_post_id' => array('Column' => 'FirstCommentID', 'Type' => 'int'), 'type' => 'Type');
     $ex->exportTable('Discussion', "select t.*,\n            'BBCode' as Format,\n            case t.topic_status when 1 then 1 else 0 end as Closed,\n            case t.topic_type when 1 then 1 else 0 end as Announce,\n            case when t.poll_start > 0 then 'poll' else null end as type,\n            FROM_UNIXTIME(t.topic_time) as DateInserted,\n            FROM_UNIXTIME(t.topic_last_post_time) as DateUpdated,\n            FROM_UNIXTIME(t.topic_last_post_time) as DateLastComment\n         from :_topics t", $discussion_Map);
     // Comments
     $comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'post_text' => array('Column' => 'Body', 'Filter' => array($this, 'removeBBCodeUIDs')), 'Format' => 'Format', 'poster_id' => 'InsertUserID', 'poster_ip' => array('Column' => 'InsertIPAddress', 'Filter' => 'forceIP4'), 'post_edit_user' => 'UpdateUserID');
     $ex->exportTable('Comment', "select p.*,\n            'BBCode' as Format,\n            FROM_UNIXTIME(p.post_time) as DateInserted,\n            FROM_UNIXTIME(nullif(p.post_edit_time,0)) as DateUpdated\n         from :_posts p", $comment_Map);
     // UserDiscussion
     $userDiscussion_Map = array('user_id' => 'UserID', 'topic_id' => 'DiscussionID');
     $ex->exportTable('UserDiscussion', "select b.*,\n         1 as Bookmarked\n         from :_bookmarks b", $userDiscussion_Map);
     // Conversations tables.
     $ex->query("drop table if exists z_pmto;");
     $ex->query("create table z_pmto (\nid int unsigned,\nuserid int unsigned,\nprimary key(id, userid));");
     $ex->query("insert ignore z_pmto (id, userid)\nselect msg_id, author_id\nfrom :_privmsgs;");
     $ex->query("insert ignore z_pmto (id, userid)\nselect msg_id, user_id\nfrom :_privmsgs_to;");
     $ex->query("insert ignore z_pmto (id, userid)\nselect msg_id, author_id\nfrom :_privmsgs_to;");
     $ex->query("drop table if exists z_pmto2;");
     $ex->query("create table z_pmto2 (\n  id int unsigned,\n  userids varchar(250),\n  primary key (id)\n);");
     $ex->query("insert ignore z_pmto2 (id, userids)\nselect\n  id,\n  group_concat(userid order by userid)\nfrom z_pmto\ngroup by id;");
     $ex->query("drop table if exists z_pm;");
     $ex->query("create table z_pm (\n  id int unsigned,\n  subject varchar(255),\n  subject2 varchar(255),\n  userids varchar(250),\n  groupid int unsigned\n);");
     $ex->query("insert z_pm (\n  id,\n  subject,\n  subject2,\n  userids\n)\nselect\n  pm.msg_id,\n  pm.message_subject,\n  case when pm.message_subject like 'Re: %' then trim(substring(pm.message_subject, 4)) else pm.message_subject end as subject2,\n  t.userids\nfrom :_privmsgs pm\njoin z_pmto2 t\n  on t.id = pm.msg_id;");
     $ex->query("create index z_idx_pm on z_pm (id);");
     $ex->query("drop table if exists z_pmgroup;");
     $ex->query("create table z_pmgroup (\n  groupid int unsigned,\n  subject varchar(255),\n  userids varchar(250)\n);");
     $ex->query("insert z_pmgroup (\n  groupid,\n  subject,\n  userids\n)\nselect\n  min(pm.id),\n  pm.subject2,\n  pm.userids\nfrom z_pm pm\ngroup by pm.subject2, pm.userids;");
     $ex->query("create index z_idx_pmgroup on z_pmgroup (subject, userids);");
     $ex->query("create index z_idx_pmgroup2 on z_pmgroup (groupid);");
     $ex->query("update z_pm pm\njoin z_pmgroup g\n  on pm.subject2 = g.subject and pm.userids = g.userids\nset pm.groupid = g.groupid;");
     // Polls.
     $poll_Map = array('poll_id' => 'PollID', 'poll_title' => 'Name', 'topic_id' => 'DiscussionID', 'topic_time' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'topic_poster' => 'InsertUserID', 'anonymous' => 'Anonymous');
     $ex->exportTable('Poll', "\n         select distinct\n            t.*,\n            t.topic_id as poll_id,\n            1 as anonymous\n         from :_poll_options po\n         join :_topics t\n            on po.topic_id = t.topic_id", $poll_Map);
     $pollOption_Map = array('id' => 'PollOptionID', 'poll_option_id' => 'Sort', 'topic_id' => 'PollID', 'poll_option_text' => 'Body', 'format' => 'Format', 'poll_option_total' => 'CountVotes', 'topic_time' => array('Column' => 'DateInserted', 'Filter' => 'timestampToDate'), 'topic_poster' => 'InsertUserID');
     $ex->exportTable('PollOption', "\n         select\n            po.*,\n            po.poll_option_id * 1000000 + po.topic_id as id,\n            'Html' as format,\n            t.topic_time,\n            t.topic_poster\n         from :_poll_options po\n         join :_topics t\n            on po.topic_id = t.topic_id", $pollOption_Map);
     $pollVote_Map = array('vote_user_id' => 'UserID', 'id' => 'PollOptionID');
     $ex->exportTable('PollVote', "\n         select v.*, v.poll_option_id * 1000000 + v.topic_id as id\n         from :_poll_votes v", $pollVote_Map);
     // Conversations.
     $conversation_Map = array('msg_id' => 'ConversationID', 'author_id' => 'InsertUserID', 'RealSubject' => array('Column' => 'Subject', 'Type' => 'varchar(250)', 'Filter' => array('Phpbb2', 'EntityDecode')));
     $ex->exportTable('Conversation', "select\n  g.subject as RealSubject,\n  pm.*,\n  from_unixtime(pm.message_time) as DateInserted\nfrom :_privmsgs pm\njoin z_pmgroup g\n  on g.groupid = pm.msg_id", $conversation_Map);
     // Coversation Messages.
     $conversationMessage_Map = array('msg_id' => 'MessageID', 'groupid' => 'ConversationID', 'message_text' => array('Column' => 'Body', 'Filter' => array($this, 'removeBBCodeUIDs')), 'author_id' => 'InsertUserID');
     $ex->exportTable('ConversationMessage', "select\n         pm.*,\n         pm2.groupid,\n         'BBCode' as Format,\n         FROM_UNIXTIME(pm.message_time) as DateInserted\n       from :_privmsgs pm\n       join z_pm pm2\n         on pm.msg_id = pm2.id", $conversationMessage_Map);
     // User Conversation.
     $userConversation_Map = array('userid' => 'UserID', 'groupid' => 'ConversationID');
     $ex->exportTable('UserConversation', "select\n         g.groupid,\n         t.userid\n       from z_pmto t\n       join z_pmgroup g\n         on g.groupid = t.id;", $userConversation_Map);
     $ex->query('drop table if exists z_pmto');
     $ex->query('drop table if exists z_pmto2;');
     $ex->query('drop table if exists z_pm;');
     $ex->query('drop table if exists z_pmgroup;');
     // Media.
     $cdn = $this->param('cdn', '');
     $media_Map = array('attach_id' => 'MediaID', 'real_filename' => 'Name', 'thumb_path' => array('Column' => 'ThumbPath', 'Filter' => array($this, 'filterThumbnailData')), 'thumb_width' => array('Column' => 'ThumbWidth', 'Filter' => array($this, 'filterThumbnailData')), 'post_id' => 'InsertUserID', 'mimetype' => 'Type', 'filesize' => 'Size');
     $ex->exportTable('Media', "select\n  case when a.post_msg_id = t.topic_first_post_id then 'discussion' else 'comment' end as ForeignTable,\n  case when a.post_msg_id = t.topic_first_post_id then a.topic_id else a.post_msg_id end as ForeignID,\n  concat('{$cdn}','FileUpload/', a.physical_filename, '.', a.extension) as Path,\n  concat('{$cdn}','FileUpload/', a.physical_filename, '.', a.extension) as thumb_path,\n  128 as thumb_width,\n  FROM_UNIXTIME(a.filetime) as DateInserted,\n  a.*\nfrom :_attachments a\njoin :_topics t\n  on a.topic_id = t.topic_id", $media_Map);
     $this->exportBanList();
     // End
     $ex->endExport();
 }
Ejemplo n.º 3
0
 /**
  * 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('&nbsp;', ' ', $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} &rarr; {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();
 }