/** * Forum-specific export format. * @param ExportModel $Ex */ protected function ForumExport($Ex) { // Begin $Ex->BeginExport('', 'phpBB 2.*', array('HashMethod' => 'phpBB')); // Users $User_Map = array('user_id' => 'UserID', 'username' => 'Name', 'user_password' => 'Password', 'user_email' => 'Email', 'user_timezone' => 'HourOffset', 'user_posts' => array('Column' => 'CountComments', 'Type' => 'int')); $Ex->ExportTable('User', "select *,\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 from :_users", $User_Map); // ":_" will be replace by database prefix // Roles $Role_Map = array('group_id' => 'RoleID', 'group_name' => 'Name', 'group_description' => 'Description'); // Skip single-user groups $Ex->ExportTable('Role', 'select * from :_groups where group_single_user = 0', $Role_Map); // UserRoles $UserRole_Map = array('user_id' => 'UserID', 'group_id' => 'RoleID'); // Skip pending memberships $Ex->ExportTable('UserRole', 'select user_id, group_id from :_users union select user_id, group_id from :_user_group where user_pending = 0', $UserRole_Map); // Categories $Category_Map = array('forum_id' => 'CategoryID', 'forum_name' => 'Name', 'forum_desc' => 'Description', 'forum_order' => 'Sort'); $Ex->ExportTable('Category', "select * 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')); $Ex->ExportTable('Discussion', "select t.*,\n 'BBCode' as Format,\n t.topic_replies+1 as CountComments,\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 FROM_UNIXTIME(t.topic_time) as DateInserted,\n FROM_UNIXTIME(p.post_time) as DateUpdated,\n FROM_UNIXTIME(p.post_time) as DateLastComment\n from :_topics t inner join :_posts p on t.topic_last_post_id = p.post_id", $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'); $Ex->ExportTable('Comment', "select p.*, pt.post_text,\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 inner join :_posts_text pt on p.post_id = pt.post_id", $Comment_Map); // End $Ex->EndExport(); }
public function Export() { $this->Permission('Garden.Export'); // This permission doesn't exist, so only users with Admin == '1' will succeed. set_time_limit(60*2); $Ex = new ExportModel(); $Ex->PDO(Gdn::Database()->Connection()); $Ex->Prefix = Gdn::Database()->DatabasePrefix; /// 2. Do the export. /// $Ex->UseCompression = TRUE; $Ex->BeginExport(PATH_ROOT.DS.'uploads'.DS.'export '.date('Y-m-d His').'.txt.gz', 'Vanilla 2.0'); $Ex->ExportTable('User', 'select * from :_User'); // ":_" will be replace by database prefix $Ex->ExportTable('Role', 'select * from :_Role'); $Ex->ExportTable('UserRole', 'select * from :_UserRole'); $Ex->ExportTable('Category', 'select * from :_Category'); $Ex->ExportTable('Discussion', 'select * from :_Discussion'); $Ex->ExportTable('Comment', 'select * from :_Comment'); $Ex->ExportTable('Conversation', 'select * from :_Conversation'); $Ex->ExportTable('UserConversation', 'select * from :_UserConversation'); $Ex->ExportTable('ConversationMessage', 'select * from :_ConversationMessage'); $Ex->EndExport(); }
/** * Forum-specific export format. * @param ExportModel $Ex */ protected function ForumExport($Ex) { // 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'); $Ex->ExportTable('User', "select m.*, u.user_pass, u.user_email\n from :_users u\n join :_sfmembers m\n on u.ID = m.user_id", $User_Map); // ":_" will be replace by database prefix // Roles $Role_Map = array('usergroup_id' => 'RoleID', 'usergroup_name' => 'Name', 'usergroup_desc' => 'Description'); $Ex->ExportTable('Role', "select * from :_sfusergroups", $Role_Map); // UserRoles $UserRole_Map = array('user_id' => 'UserID', 'usergroup_id' => 'RoleID'); $Ex->ExportTable('UserRole', "select * from :_sfmemberships", $UserRole_Map); // Categories $Category_Map = array('forum_id' => 'CategoryID', 'forum_name' => 'Name', 'forum_desc' => 'Description', 'form_slug' => 'UrlCode'); $Ex->ExportTable('Category', "select *,\n nullif(parent,0) as ParentCategoryID\n from :_sfforums", $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'); $Ex->ExportTable('Discussion', "select t.*,\n\t\t\t\t'Html' as Format\n from :_sftopics t", $Discussion_Map); // Comments $Comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'post_content' => 'Body', 'Format' => 'Format', 'user_id' => 'InsertUserID', 'post_date' => 'DateInserted'); $Ex->ExportTable('Comment', "select p.*,\n\t\t\t\t'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(); }
/** * Forum-specific export format. * @param ExportModel $Ex */ protected function ForumExport($Ex) { // Begin $Ex->BeginExport('', 'SMF 2.*', array('HashMethod' => 'smf')); // Users $User_Map = array('id_member' => 'UserID', 'member_name' => 'Name', 'passwd' => 'Password', 'email_address' => 'Email', 'timezone_offset' => 'HourOffset', 'posts' => array('Column' => 'CountComments', 'Type' => 'int'), 'birthdate' => 'DateOfBirth'); $Ex->ExportTable('User', "select m.*,\n FROM_UNIXTIME(nullif(m.date_registered, 0)) as DateFirstVisit,\n FROM_UNIXTIME(nullif(m.date_registered, 0)) as DateInserted,\n FROM_UNIXTIME(nullif(m.last_login,0)) as DateLastActive,\n case a.file_hash\n when '' then concat('userpics/',nullif(a.filename,m.avatar))\n else concat('userpics/',a.id_attach,'_',a.file_hash,'.',fileext)\n end as Photo\n from :_members m left join :_attachments a on m.id_member = a.id_member", $User_Map); // ":_" will be replace by database prefix // Roles $Role_Map = array('id_group' => 'RoleID', 'group_name' => 'Name', 'description' => 'Description'); $Ex->ExportTable('Role', "select * from :_membergroups", $Role_Map); // UserRoles $UserRole_Map = array('id_member' => 'UserID', 'id_group' => 'RoleID'); $Ex->ExportTable('UserRole', "select id_member, id_group from :_members where id_group !=0\n union select m.id_member,g.id_group from :_members m join :_membergroups g on find_in_set(g.id_group,m.additional_groups)", $UserRole_Map); // Categories $Category_Map = array('id_board' => 'CategoryID', 'id_parent' => 'ParentCategoryID', 'name' => 'Name', 'description' => 'Description', 'board_order' => 'Sort'); $Ex->ExportTable('Category', "select name,'' description, cat_order board_order, id_cat id_board, 0 id_parent from :_categories\n union select name, description, board_order, id_board+(select max(id_cat) from :_categories) id_board,\n case id_parent when 0 then id_cat else id_parent+(select max(id_cat) from :_categories) end id_parent\n from :_boards b", $Category_Map); // Discussions $Discussion_Map = array('id_topic' => 'DiscussionID', 'id_member_started' => 'InsertUserID', 'num_views' => 'CountViews', 'id_first_msg' => array('Column' => 'FirstCommentID', 'Type' => 'int')); $Ex->ExportTable('Discussion', "select t.*,\n t.id_board+(select max(id_cat) from :_categories) as CategoryID,\n\t\t\t'BBCode' as Format,\n t.num_replies+1 as CountComments,\n case t.locked when 1 then 1 else 0 end as Closed,\n case t.is_sticky when 1 then 1 else 0 end as Announce,\n fm.subject as Name,\n fm.body as Body,\n FROM_UNIXTIME(fm.poster_time) as DateInserted,\n FROM_UNIXTIME(lm.poster_time) as DateUpdated,\n FROM_UNIXTIME(lm.poster_time) as DateLastComment\n from :_topics t\n inner join :_messages fm on t.id_first_msg = fm.id_msg\n inner join :_messages lm on t.id_last_msg = lm.id_msg", $Discussion_Map); // Comments $Comment_Map = array('id_msg' => 'CommentID', 'id_topic' => 'DiscussionID', 'body' => 'Body', 'id_member' => 'InsertUserID'); $Ex->ExportTable('Comment', "select m.*,\n\t\t\t'BBCode' as Format,\n\t\t\tmm.id_member as UpdateUserID,\n FROM_UNIXTIME(m.poster_time) as DateInserted,\n FROM_UNIXTIME(nullif(m.modified_time,0)) as DateUpdated\n from :_messages m left join :_members mm on m.modified_name = mm.member_name\n where m.id_msg not in (select id_first_msg from :_topics)", $Comment_Map); //Media $Media_Map = array('id_attach' => 'MediaID', 'id_msg' => 'ForeignID', 'filename' => 'Name', 'file_hash' => array('Column' => 'Path', 'Filter' => array($this, 'BuildMediaPath')), 'size' => 'Size'); $Ex->ExportTable('Media', "select a.*,\n case fileext\n when 'jpg' then 'image/jpeg'\n when 'jpeg' then 'image/jpeg'\n when 'gif' then 'image/gif'\n when 'png' then 'image/png'\n when 'bmp' then 'image/bmp'\n when 'txt' then 'text/plan'\n when 'htm' then 'text/html'\n when 'html' then 'text/html'\n else 'application/octet-stream'\n end Type,\n m.id_member InsertUserID,\n from_unixtime(m.poster_time) DateInserted,\n 'discussion' ForeignTable\n from :_attachments a join :_messages m on m.id_msg = a.id_msg join :_topics t on a.id_msg = t.id_first_msg\n where attachment_type = 0\n union select a.*,\n case fileext\n when 'jpg' then 'image/jpeg'\n when 'jpeg' then 'image/jpeg'\n when 'gif' then 'image/gif'\n when 'png' then 'image/png'\n when 'bmp' then 'image/bmp'\n when 'txt' then 'text/plan'\n when 'htm' then 'text/html'\n when 'html' then 'text/html'\n else 'application/octet-stream'\n end Type,\n m.id_member InsertUserID,\n from_unixtime(poster_time) DateInserted,\n 'comment' ForeignTable\n from :_attachments a join :_messages m on m.id_msg = a.id_msg\n where a.id_msg not in (select id_first_msg from :_topics t) and attachment_type = 0\n ", $Media_Map); // Conversations $Conversation_Map = array('id_pm_head' => 'ConversationID', 'id_pm' => 'FirstMessageID', 'id_member_from' => 'InsertUserID'); $Ex->ExportTable('Conversation', "select pm.id_pm_head, pm.id_pm,\n from_unixtime(pm.msgtime) DateInserted,\n pm.id_member_from,\n pm_last.id_member_from UpdateUserID,\n from_unixtime(pm_last.msgtime) DateUpdated\n from :_personal_messages pm\n join (select pm.id_pm_head,pm.id_member_from, max(pm.msgtime) msgtime\n from :_personal_messages pm\n group by pm.id_pm_head,pm.id_member_from) pm_last\n on pm_last.id_pm_head = pm.id_pm_head\n where pm.id_pm = pm.id_pm_head", $Conversation_Map); // Conversation Messages $ConversationMessage_Map = array('id_pm' => 'MessageID', 'id_pm_head' => 'ConversationID', 'body' => 'Body', 'id_member_from' => 'InsertUserID'); $Ex->ExportTable('ConversationMessage', "select pm.*,\n 'BBCode' Format,\n from_unixtime(pm.msgtime) DateInserted\n from :_personal_messages pm", $ConversationMessage_Map); // User Conversation $UserConversation_Map = array('id_member_from' => 'UserID', 'id_pm_head' => 'ConversationID', 'id_pm' => 'LastMessageID'); $Ex->ExportTable('UserConversation', "\n select pm.id_member_from,\n pm.id_pm_head, pm.deleted_by_sender Deleted, pm_agr.id_pm, pm_agr.count CountReadMessages\n from :_personal_messages pm\n join (select id_pm_head, max(id_pm) id_pm, count(id_pm) count\n from :_personal_messages\n group by id_pm_head) pm_agr\n on pm.id_pm_head = pm_agr.id_pm_head\n union\n select pmr.id_member id_member_from,pm.id_pm_head,\n pmr.deleted Deleted, pm_agr.id_pm, pm_agr.count CountReadMessages\n from :_personal_messages pm\n join :_pm_recipients pmr on pm.id_pm = pmr.id_pm\n join (select id_pm_head, max(id_pm) id_pm, count(id_pm) count\n from :_personal_messages\n group by id_pm_head) pm_agr\n on pm.id_pm_head = pm_agr.id_pm_head", $UserConversation_Map); // End $Ex->EndExport(); }
/** * Forum-specific export format. * @param ExportModel $Ex */ protected function ForumExport($Ex) { // Begin $Ex->BeginExport('', 'phpBB 3.*', array('HashMethod' => 'phpBB')); // Users $User_Map = array( 'user_id'=>'UserID', 'username'=>'Name', 'user_password'=>'Password', 'user_email'=>'Email', 'user_timezone'=>'HourOffset', 'user_posts'=>array('Column' => 'CountComments', 'Type' => 'int') ); $Ex->ExportTable('User', "select *, FROM_UNIXTIME(nullif(user_regdate, 0)) as DateFirstVisit, FROM_UNIXTIME(nullif(user_lastvisit, 0)) as DateLastActive, FROM_UNIXTIME(nullif(user_regdate, 0)) as DateInserted from :_users", $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); // 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); // Categories $Category_Map = array( 'forum_id'=>'CategoryID', 'forum_name'=>'Name', 'forum_desc'=>'Description', 'left_id'=>'Sort' ); $Ex->ExportTable('Category', "select *, nullif(parent_id,0) as ParentCategoryID 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') ); $Ex->ExportTable('Discussion', "select t.*, 'BBCode' as Format, topic_replies+1 as CountComments, case t.topic_status when 1 then 1 else 0 end as Closed, case t.topic_type when 1 then 1 else 0 end as Announce, FROM_UNIXTIME(t.topic_time) as DateInserted, FROM_UNIXTIME(t.topic_last_post_time) as DateUpdated, FROM_UNIXTIME(t.topic_last_post_time) as DateLastComment 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', 'post_edit_user' => 'UpdateUserID' ); $Ex->ExportTable('Comment', "select p.*, 'BBCode' as Format, FROM_UNIXTIME(p.post_time) as DateInserted, FROM_UNIXTIME(nullif(p.post_edit_time,0)) as DateUpdated from :_posts p", $Comment_Map); // UserDiscussion $UserDiscussion_Map = array( 'user_id' => 'UserID', 'topic_id' => 'DiscussionID'); $Ex->ExportTable('UserDiscussion', "select b.*, 1 as Bookmarked from :_bookmarks b", $UserDiscussion_Map); // Media if ($Ex->Exists('attachments')) { $Media_Map = array( 'attach_id' => 'MediaID', 'real_filename' => 'Name', 'mimetype' => 'Type', 'filesize' => 'Size', 'physical_filename' => array('Column' => 'Path', 'Filter' => array($this, 'BuildMediaPath')), 'poster_id' => 'InsertUserID' ); $Ex->ExportTable('Media', "select a.*, p.post_id as post_id, 'local' as StorageMethod, IF(p.post_id != p.topic_id, 'comment', 'discussion') as ForeignTable, IF(p.post_id != p.topic_id, post_id, a.topic_id) as ForeignID, FROM_UNIXTIME(a.filetime) as DateInserted from :_attachments a left join :_posts p ON a.post_msg_id = p.post_id", $Media_Map); } // End $Ex->EndExport(); }
/** * Forum-specific export format. * @param ExportModel $Ex */ protected function ForumExport($Ex) { // Begin $Ex->BeginExport('', 'bbPress 1.*', array('HashMethod' => 'Vanilla')); // Users $User_Map = array('ID' => 'UserID', 'user_nicename' => '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', 'form_slug' => 'UrlCode', 'left_order' => 'Sort'); $Ex->ExportTable('Category', "select *,\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\t\t\t\t'Html' as Format,\n case t.topic_open when 0 then 1 else 0 end as Closed\n from :_topics t", $Discussion_Map); // Comments $Comment_Map = array('post_id' => 'CommentID', 'topic_id' => 'DiscussionID', 'post_text' => 'Body', 'Format' => 'Format', 'Body' => array('Column' => 'Body', 'Filter' => 'bbPressTrim'), 'poster_id' => 'InsertUserID', 'post_time' => 'DateInserted'); $Ex->ExportTable('Comment', "select p.*,\n\t\t\t\t'Html' as Format\n from :_posts p", $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 bb_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 bb_bbpm union select distinct pm_thread, pm_to, del_reciever from bb_bbpm', $ConUser_Map); } } // 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) { // Get the characterset for the comments. $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); // 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 FROM :_Discussion d\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 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 table for conversations. // This cannot be a temporary table because of some of the union selects it is used in below. $Ex->Query("create table :_V1Conversation (ConversationID int auto_increment primary key, DiscussionID int, UserID1 int, UserID2 int, DateCreated datetime, EditUserID int, DateEdited datetime)"); $Ex->Query("insert :_V1Conversation (DiscussionID, UserID1, UserID2, DateCreated, EditUserID, DateEdited)\n select\n DiscussionID,\n AuthUserID as UserID1,\n WhisperUserID as UserID2,\n min(DateCreated),\n max(EditUserID),\n max(DateEdited)\n from :_Comment\n where coalesce(WhisperUserID, 0) <> 0\n group by DiscussionID, AuthUserID, WhisperUserID\n\n union\n\n select\n DiscussionID,\n AuthUserID as UserID1,\n WhisperUserID as UserID2,\n DateCreated,\n WhisperFromLastUserID,\n DateLastWhisper\n from :_Discussion\n where coalesce(WhisperUserID, 0) <> 0"); // Delete redundant conversations. $Ex->Query("create index ix_V1UserID1 on :_V1Conversation (DiscussionID, UserID1)"); // for speed $Ex->Query("delete t.*\n from :_V1Conversation t\n inner join :_Comment c\n on c.DiscussionID = t.DiscussionID\n and c.AuthUserID = t.UserID2\n and c.WhisperUserID = t.UserID1\n and c.AuthUserID < c.WhisperUserID"); $Conversation_Map = array('UserID1' => 'InsertUserID', 'DateCreated' => 'DateInserted', 'EditUserID' => 'UpdateUserID', 'DateEdited' => 'DateUpdated'); $Ex->ExportTable('Conversation', "select * from :_V1Conversation", $Conversation_Map); // ConversationMessage /* 'MessageID' => 'int', 'ConversationID' => 'int', 'Body' => 'text', 'InsertUserID' => 'int', 'DateInserted' => 'datetime' */ $ConversationMessage_Map = array('CommentID' => 'MessageID', 'DiscussionID' => 'ConversationID', 'Body' => 'Body', 'AuthUserID' => 'InsertUserID', 'DateCreated' => 'DateInserted'); $Ex->ExportTable('ConversationMessage', "\n select c.CommentID, t.ConversationID, c.AuthUserID, c.DateCreated, c.Body\n from :_Comment c\n join :_V1Conversation t\n on t.DiscussionID = c.DiscussionID\n and c.WhisperUserID in (t.UserID1, t.UserID2)\n and c.AuthUserID in (t.UserID1, t.UserID2)\n where c.WhisperUserID > 0\n\n union\n\n select c.CommentID, t.ConversationID, c.AuthUserID, c.DateCreated, c.Body\n from :_Comment c\n join :_Discussion d\n on c.DiscussionID = d.DiscussionID\n join :_V1Conversation t\n on t.DiscussionID = d.DiscussionID\n and d.WhisperUserID in (t.UserID1, t.UserID2)\n and d.AuthUserID in (t.UserID1, t.UserID2)\n where d.WhisperUserID > 0", $ConversationMessage_Map); // UserConversation /* 'UserID' => 'int', 'ConversationID' => 'int', 'LastMessageID' => 'int' */ $UserConversation_Map = array('UserID' => 'UserID', 'ConversationID' => 'ConversationID'); $Ex->ExportTable('UserConversation', "select UserID1 as UserID, ConversationID\n from :_V1Conversation\n\n union\n\n select UserID2 as UserID, ConversationID\n from :_V1Conversation", $UserConversation_Map); $Ex->Query("drop table :_V1Conversation"); // 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.*, 'local' as StorageMethod, 'comment' as ForeignTable from :_Attachment a", $Media_Map); } // End $Ex->EndExport(); }
/** * * @param ExportModel $Ex */ public function ForumExport($Ex) { $CharacterSet = $Ex->GetCharacterSet('yaf_Topic'); if ($CharacterSet) { $Ex->CharacterSet = $CharacterSet; } $Ex->BeginExport('', 'YAF.NET (Yet Another Forum)'); $Ex->SourcePrefix = 'yaf_'; // User. $User_Map = array('UserID' => 'UserID', 'Name' => 'Name', 'Email' => 'Email', 'Joined' => 'DateInserted', 'LastVisit' => array('Column' => 'DateLastVisit', 'Type' => 'datetime'), 'IP' => 'InsertIPAddress', 'Avatar' => 'Photo', 'RankID' => array('Column' => 'RankID', 'Type' => 'int'), 'Points' => array('Column' => 'Points', 'Type' => 'int'), 'LastActivity' => 'DateLastActive', 'Password2' => array('Column' => 'Password', 'Filter' => array($this, 'ConvertPassword')), 'HashMethod' => 'HashMethod'); $Ex->ExportTable('User', "\n select\n u.*,\n m.Password as Password2,\n m.PasswordSalt,\n m.PasswordFormat,\n m.LastActivity,\n 'yaf' as HashMethod\n from yaf_User u\n left join yaf_prov_Membership m\n on u.ProviderUserKey = m.UserID;", $User_Map); // Role. $Role_Map = array('GroupID' => 'RoleID', 'Name' => 'Name'); $Ex->ExportTable('Role', "\n select *\n from yaf_Group;", $Role_Map); // UserRole. $UserRole_Map = array('UserID' => 'UserID', 'GroupID' => 'RoleID'); $Ex->ExportTable('UserRole', 'select * from yaf_UserGroup', $UserRole_Map); // Rank. $Rank_Map = array('RankID' => 'RankID', 'Level' => 'Level', 'Name' => 'Name', 'Label' => 'Label'); $Ex->ExportTable('Rank', "\n select\n r.*,\n RankID as Level,\n Name as Label\n from yaf_Rank r;", $Rank_Map); // Signatures. $Ex->ExportTable('UserMeta', "\n select\n UserID,\n 'Plugin.Signatures.Sig' as `Name`,\n Signature as `Value`\n from yaf_User\n where Signature <> ''\n\n union all\n\n select\n UserID,\n 'Plugin.Signatures.Format' as `Name`,\n 'BBCode' as `Value`\n from yaf_User\n where Signature <> '';"); // Category. $Category_Map = array('ForumID' => 'CategoryID', 'ParentID' => 'ParentCategoryID', 'Name' => 'Name', 'Description' => 'Description', 'SortOrder' => 'Sort'); $Ex->ExportTable('Category', "\n select\n f.ForumID,\n case when f.ParentID = 0 then f.CategoryID * 1000 else f.ParentID end as ParentID,\n f.Name,\n f.Description,\n f.SortOrder\n from yaf_Forum f\n\n union all\n\n select\n c.CategoryID * 1000,\n null,\n c.Name,\n null,\n c.SortOrder\n from yaf_Category c;", $Category_Map); // Discussion. $Discussion_Map = array('TopicID' => 'DiscussionID', 'ForumID' => 'CategoryID', 'UserID' => 'InsertUserID', 'Posted' => 'DateInserted', 'Topic' => 'Name', 'Views' => 'CountViews', 'Announce' => 'Announce'); $Ex->ExportTable('Discussion', "\n select\n case when t.Priority > 0 then 1 else 0 end as Announce,\n t.Flags & 1 as Closed,\n t.*\n from yaf_Topic t\n where t.IsDeleted = 0;", $Discussion_Map); // Comment. $Comment_Map = array('MessageID' => 'CommentID', 'TopicID' => 'DiscussionID', 'ReplyTo' => array('Column' => 'ReplyToCommentID', 'Type' => 'int'), 'UserID' => 'InsertUserID', 'Posted' => 'DateInserted', 'Message' => 'Body', 'Format' => 'Format', 'IP' => 'InsertIPAddress', 'Edited' => array('Column' => 'DateUpdated', 'Filter' => array($this, 'CleanDate')), 'EditedBy' => 'UpdateUserID'); $Ex->ExportTable('Comment', "\n select\n case when m.Flags & 1 = 1 then 'Html' else 'BBCode' end as Format,\n m.*\n from yaf_Message m\n where IsDeleted = 0;", $Comment_Map); // Conversation. $this->_ExportConversationTemps(); $Conversation_Map = array('PMessageID' => 'ConversationID', 'FromUserID' => 'InsertUserID', 'Created' => 'DateInserted', 'Title' => array('Column' => 'Subject', 'Type' => 'varchar(512)')); $Ex->ExportTable('Conversation', "\n select\n pm.*,\n g.Title\n from z_pmgroup g\n join yaf_PMessage pm\n on g.Group_ID = pm.PMessageID;", $Conversation_Map); // UserConversation. $UserConversation_Map = array('PM_ID' => 'ConversationID', 'User_ID' => 'UserID', 'Deleted' => 'Deleted'); $Ex->ExportTable('UserConversation', "\n select pto.*\n from z_pmto pto\n join z_pmgroup g\n on pto.PM_ID = g.Group_ID;", $UserConversation_Map); // ConversationMessage. $ConversationMessage_Map = array('PMessageID' => 'MessageID', 'Group_ID' => 'ConversationID', 'FromUserID' => 'InsertUserID', 'Created' => 'DateInserted', 'Body' => 'Body', 'Format' => 'Format'); $Ex->ExportTable('ConversationMessage', "\n select\n pm.*,\n case when pm.Flags & 1 = 1 then 'Html' else 'BBCode' end as Format,\n t.Group_ID\n from yaf_PMessage pm\n join z_pmtext t\n on t.PM_ID = pm.PMessageID;", $ConversationMessage_Map); $Ex->EndExport(); }