/** * @param ExportModel $Ex */ protected function ForumExport($Ex) { $Ex->BeginExport('', 'NodeBB 0.*', array('HashMethod' => 'Vanilla')); // Users $User_Map = array('uid' => 'UserID', 'username' => 'Name', 'password' => 'Password', 'email' => 'Email', 'confirmed' => 'Confirmed', 'showemail' => 'ShowEmail', 'joindate' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'lastonline' => array('Column' => 'DateLastActive', 'Filter' => array($this, 'tsToDate')), 'lastposttime' => array('Column' => 'DateUpdated', 'Filter' => array($this, 'tsToDate')), 'banned' => 'Banned', 'admin' => 'Admin', 'hm' => 'HashMethod'); $Ex->ExportTable('User', "\n\n select uid, username, password, email, `email:confirmed` as confirmed, showemail, joindate, lastonline, lastposttime, banned, 0 as admin, 'crypt' as hm\n from :_user\n\n ", $User_Map); //Roles $Role_Map = array('_num' => 'RoleID', '_key' => array('Column' => 'Name', 'Filter' => array($this, 'roleNameFromKey')), 'description' => 'Description'); $Ex->ExportTable('Role', "\n\n select gm._key as _key, gm._num as _num, g.description as description\n from :_group_members gm left join :_group g\n on gm._key like concat(g._key, '%')\n\n ", $Role_Map); $UserRole_Map = array('id' => 'RoleID', 'members' => 'UserID'); $Ex->ExportTable('UserRole', "\n\n select *, g._num as id\n from :_group_members g join :_group_members__members m\n on g._id = m._parentid\n\n ", $UserRole_Map); // Signatutes. $UserMeta_Map = array('uid' => 'UserID', 'name' => 'Name', 'signature' => 'Value'); $Ex->ExportTable('UserMeta', "\n\n select uid, 'Plugin.Signatures.Sig' as name, signature\n from :_user\n where length(signature) > 1\n\n union\n\n select uid, 'Plugin.Signatures.Format', 'Markdown'\n from :_user\n where length(signature) > 1\n\n union\n\n select uid, 'Profile.Website' as name, website\n from :_user\n where length(website) > 7\n\n union\n\n select uid, 'Profile.Location' as name, location\n from :_user\n where length(location) > 1\n\n ", $UserMeta_Map); // Categories $Category_Map = array('cid' => 'CategoryID', 'name' => array('Column' => 'Name', 'Filter' => 'HTMLDecoder'), 'description' => 'Description', 'order' => 'Sort', 'parentCid' => 'ParentCategoryID', 'slug' => array('Column' => 'UrlCode', 'Filter' => array($this, 'removeNumId')), 'image' => 'Photo', 'disabled' => 'Archived'); $Ex->ExportTable('Category', "\n\n select *\n from :_category\n\n ", $Category_Map); if (!$Ex->IndexExists('z_idx_topic', ':_topic')) { $Ex->Query("create index z_idx_topic on :_topic(mainPid);"); } if (!$Ex->IndexExists('z_idx_post', ':_post')) { $Ex->Query("create index z_idx_post on :_post(pid);"); } if (!$Ex->IndexExists('z_idx_poll', ':_poll')) { $Ex->Query("create index z_idx_poll on :_poll(tid);"); } $Ex->Query("drop table if exists z_discussionids;"); $Ex->Query("\n\n create table z_discussionids (\n tid int unsigned,\n primary key(tid)\n );\n\n "); $Ex->Query("\n\n insert ignore z_discussionids (\n tid\n )\n select mainPid\n from :_topic\n where mainPid is not null\n and deleted != 1;\n\n "); $Ex->Query("drop table if exists z_reactiontotalsupvote;"); $Ex->Query("\n\n create table z_reactiontotalsupvote (\n value varchar(50),\n total int,\n primary key (value)\n );\n\n "); $Ex->Query("drop table if exists z_reactiontotalsdownvote;"); $Ex->Query("\n\n create table z_reactiontotalsdownvote (\n value varchar(50),\n total int,\n primary key (value)\n );\n\n "); $Ex->Query("drop table if exists z_reactiontotals;"); $Ex->Query("\n\n create table z_reactiontotals (\n value varchar(50),\n upvote int,\n downvote int,\n primary key (value)\n );\n\n "); $Ex->Query("\n\n insert z_reactiontotalsupvote\n select value, count(*) as totals\n from :_uid_upvote\n group by value;\n\n "); $Ex->Query("\n\n insert z_reactiontotalsdownvote\n select value, count(*) as totals\n from :_uid_downvote\n group by value;\n\n "); $Ex->Query("\n\n insert z_reactiontotals\n select *\n from (\n select u.value, u.total as up, d.total as down\n from z_reactiontotalsupvote u\n left join z_reactiontotalsdownvote d\n on u.value = d.value\n\n union\n\n select d.value, u.total as up, d.total as down\n from z_reactiontotalsdownvote d\n left join z_reactiontotalsupvote u\n on u.value = d.value\n ) as reactions\n\n "); //Discussions $Discussion_Map = array('tid' => 'DiscussionID', 'cid' => 'CategoryID', 'title' => 'Name', 'content' => 'Body', 'uid' => 'InsertUserID', 'locked' => 'Closed', 'pinned' => 'Announce', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'edited' => array('Column' => 'DateUpdated', 'Filter' => array($this, 'tsToDate')), 'editor' => 'UpdateUserID', 'viewcount' => 'CountViews', 'format' => 'Format', 'votes' => 'Score', 'attributes' => array('Column' => 'Attributes', 'Filter' => array($this, 'serializeReactions')), 'poll' => array('Column' => 'Type', 'Filter' => array($this, 'isPoll'))); $Ex->ExportTable('Discussion', "\n\n select p.tid, cid, title, content, p.uid, locked, pinned, p.timestamp, p.edited, p.editor, viewcount, votes, poll._id as poll, 'Markdown' as format, concat(ifnull(u.total, 0), ':', ifnull(d.total, 0)) as attributes\n from :_topic t\n left join :_post p\n on t.mainPid = p.pid\n left join z_reactiontotalsupvote u\n on u.value = t.mainPid\n left join z_reactiontotalsdownvote d\n on d.value = t.mainPid\n left join :_poll poll\n on p.tid = poll.tid\n where t.deleted != 1\n\n ", $Discussion_Map); $Ex->Query("drop table if exists z_comments;"); $Ex->Query("\n\n create table z_comments (\n pid int,\n content text,\n uid varchar(255),\n tid varchar(255),\n timestamp double,\n edited varchar(255),\n editor varchar(255),\n votes int,\n upvote int,\n downvote int,\n primary key(pid)\n );\n\n "); $Ex->Query("\n\n insert ignore z_comments (\n pid,\n content,\n uid,\n tid,\n timestamp,\n edited,\n editor,\n votes\n )\n select p.pid, p.content, p.uid, p.tid, p.timestamp, p.edited, p.editor, p.votes\n from :_post p\n left join z_discussionids t\n on t.tid = p.pid\n where p.deleted != 1 and t.tid is null;\n\n "); $Ex->Query("\n\n update z_comments as c\n join z_reactiontotals r\n on r.value = c.pid\n set c.upvote = r.upvote, c.downvote = r.downvote;\n\n "); // Comments $Comment_Map = array('content' => 'Body', 'uid' => 'InsertUserID', 'tid' => 'DiscussionID', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'edited' => array('Column' => 'DateUpdated', 'Filter' => array($this, 'tsToDate')), 'editor' => 'UpdateUserID', 'votes' => 'Score', 'format' => 'Format', 'attributes' => array('Column' => 'Attributes', 'Filter' => array($this, 'serializeReactions'))); $Ex->ExportTable('Comment', "\n\n select content, uid, tid, timestamp, edited, editor, votes, 'Markdown' as format, concat(ifnull(upvote, 0), ':', ifnull(downvote, 0)) as attributes\n from z_comments\n\n ", $Comment_Map); //Polls $Poll_Map = array('pollid' => 'PollID', 'title' => 'Name', 'tid' => 'DiscussionID', 'votecount' => 'CountVotes', 'uid' => 'InsertUserID', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate'))); $Ex->ExportTable('Poll', "\n\n select *\n from :_poll p left join :_poll_settings ps\n on ps._key like concat(p._key, ':', '%')\n\n ", $Poll_Map); $PollOption_Map = array('_num' => 'PollOptionID', '_key' => array('Column' => 'PollID', 'Filter' => array($this, 'idFromKey')), 'title' => 'Body', 'sort' => 'Sort', 'votecount' => array('Column' => 'CountVotes', 'Filter' => array($this, 'makeNullZero')), 'format' => 'Format'); $Ex->ExportTable('PollOption', "\n\n select _num, _key, title, id+1 as sort, votecount, 'Html' as format\n from :_poll_options\n where title is not null\n\n ", $PollOption_Map); $PollVote_Map = array('userid' => 'UserID', 'poll_option_id' => 'PollOptionID'); $Ex->ExportTable('PollVote', "\n\n select povm.members as userid, po._num as poll_option_id\n from :_poll_options_votes__members povm\n left join :_poll_options_votes pov\n on povm._parentid = pov._id\n left join :_poll_options po\n on pov._key like concat(po._key, ':', '%')\n where po.title is not null\n\n ", $PollVote_Map); //Tags if (!$Ex->IndexExists('z_idx_topic_key', ':_topic')) { $Ex->Query("create index z_idx_topic_key on :_topic (_key);"); } $Tag_Map = array('slug' => array('Column' => 'Name', 'Filter' => array($this, 'nameToSlug')), 'fullname' => 'FullName', 'count' => 'CountDiscussions', 'tagid' => 'TagID', 'cid' => 'CategoryID', 'type' => 'Type', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'uid' => 'InsertUserID'); $Now = time(); $Ex->Query("set @rownr=1000;"); $Ex->ExportTable('Tag', "\n\n select @rownr:=@rownr+1 as tagid, members as fullname, members as slug, '' as type, count, timestamp, uid, cid\n from (\n select members, count(*) as count, _parentid\n from :_topic_tags__members\n group by members\n ) as tags\n join :_topic_tags tt\n on tt._id = _parentid\n left join :_topic t\n on substring(tt._key, 1, length(tt._key) - 5) = t._key\n\n ", $Tag_Map); $TagDiscussion_Map = array('tagid' => 'TagID', 'tid' => 'DiscussionID', 'cid' => 'CategoryID', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate'))); $Ex->Query("set @rownr=1000;"); $Ex->ExportTable('TagDiscussion', "\n\n select tagid, cid, tid, timestamp\n from :_topic_tags__members two\n join (\n select @rownr:=@rownr+1 as tagid, members as fullname, members as slug, count\n from (\n select members, count(*) as count\n from :_topic_tags__members\n group by members\n ) as tags\n ) as tagids\n on two.members = tagids.fullname\n join :_topic_tags tt\n on tt._id = _parentid\n left join :_topic t\n on substring(tt._key, 1, length(tt._key) - 5) = t._key\n\n ", $TagDiscussion_Map); //Conversations if (!$Ex->IndexExists('z_idx_message_key', ':_message')) { $Ex->Query("create index z_idx_message_key on :_message(_key);"); } $Ex->Query("drop table if exists z_pmto;"); $Ex->Query("\n\n create table z_pmto (\n pmid int unsigned,\n userid int,\n groupid int,\n primary key(pmid, userid)\n );\n\n "); $Ex->Query("\n\n insert ignore z_pmto (\n pmid,\n userid\n )\n select substring_index(_key, ':', -1), fromuid\n from :_message;\n\n "); $Ex->Query("\n\n insert ignore z_pmto (\n pmid,\n userid\n )\n select substring_index(_key, ':', -1), touid\n from :_message;\n\n "); $Ex->Query("drop table if exists z_pmto2;"); $Ex->Query("\n\n create table z_pmto2 (\n pmid int unsigned,\n userids varchar(250),\n groupid int unsigned,\n primary key (pmid)\n );\n\n "); $Ex->Query("\n\n replace z_pmto2 (\n pmid,\n userids\n )\n select pmid, group_concat(userid order by userid)\n from z_pmto\n group by pmid;\n\n "); $Ex->Query("drop table if exists z_pmgroup;"); $Ex->Query("\n\n create table z_pmgroup (\n userids varchar(250),\n groupid varchar(255),\n firstmessageid int,\n lastmessageid int,\n countmessages int,\n primary key (userids, groupid)\n );\n\n "); $Ex->Query("\n\n insert z_pmgroup\n select userids, concat('message:', min(pmid)), min(pmid), max(pmid), count(*)\n from z_pmto2\n group by userids;\n\n "); $Ex->Query("\n\n update z_pmto2 as p\n left join z_pmgroup g\n on p.userids = g.userids\n set p.groupid = g.firstmessageid;\n\n "); $Ex->Query("\n\n update z_pmto as p\n left join z_pmto2 p2\n on p.pmid = p2.pmid\n set p.groupid = p2.groupid;\n\n "); $Ex->Query("create index z_idx_pmto_cid on z_pmto(groupid);"); $Ex->Query("create index z_idx_pmgroup_cid on z_pmgroup(firstmessageid);"); $Conversation_Map = array('conversationid' => 'ConversationID', 'firstmessageid' => 'FirstMessageID', 'lastmessageid' => 'LastMessageID', 'countparticipants' => 'CountParticipants', 'countmessages' => 'CountMessages'); $Ex->ExportTable('Conversation', "\n\n select *, firstmessageid as conversationid, 2 as countparticipants\n from z_pmgroup\n left join :_message\n on groupid = _key;\n\n ", $Conversation_Map); $ConversationMessage_Map = array('messageid' => 'MessageID', 'conversationid' => 'ConversationID', 'content' => 'Body', 'format' => 'Format', 'fromuid' => 'InsertUserID', 'timestamp' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate'))); $Ex->ExportTable('ConversationMessage', "\n\n select groupid as conversationid, pmid as messageid, content, 'Text' as format, fromuid, timestamp\n from z_pmto2\n left join :_message\n on concat('message:', pmid) = _key\n\n ", $ConversationMessage_Map); $UserConversationMap = array('conversationid' => 'ConversationID', 'userid' => 'UserID', 'lastmessageid' => 'LastMessageID'); $Ex->ExportTable('UserConversation', "\n\n select p.groupid as conversationid, userid, lastmessageid\n from z_pmto p\n left join z_pmgroup\n on firstmessageid = p.groupid;\n\n ", $UserConversationMap); //Bookmarks (watch) $UserDiscussion_Map = array('members' => 'UserID', '_key' => array('Column' => 'DiscussionID', 'Filter' => array($this, 'idFromKey')), 'bookmarked' => 'Bookmarked'); $Ex->ExportTable('UserDiscussion', "\n select members, _key, 1 as bookmarked\n from :_tid_followers__members\n left join :_tid_followers\n on _parentid = _id\n ", $UserDiscussion_Map); //Reactions if (!$Ex->IndexExists('z_idx_topic_mainpid', ':_topic')) { $Ex->Query("create index z_idx_topic_mainpid on :_topic(mainPid);"); } if (!$Ex->IndexExists('z_idx_uid_downvote', ':_uid_downvote')) { $Ex->Query("create index z_idx_uid_downvote on :_uid_downvote(value);"); } if (!$Ex->IndexExists('z_idx_uid_upvote', ':_uid_upvote')) { $Ex->Query("create index z_idx_uid_upvote on :_uid_upvote(value);"); } $UserTag_Map = array('tagid' => 'TagID', 'recordtype' => 'RecordType', '_key' => array('Column' => 'UserID', 'Filter' => array($this, 'idFromKey')), 'value' => 'RecordID', 'score' => array('Column' => 'DateInserted', 'Filter' => array($this, 'tsToDate')), 'total' => 'Total'); $Ex->ExportTable('UserTag', "\n\n select 11 as tagid, 'Discussion' as recordtype, u._key, u.value, score, total\n from :_uid_upvote u\n left join z_discussionids t\n on u.value = t.tid\n left join z_reactiontotalsupvote r\n on r.value = u.value\n where u._key != 'uid:NaN:upvote'\n and t.tid is not null\n\n union\n\n select 11 as tagid, 'Comment' as recordtype, u._key, u.value, score, total\n from :_uid_upvote u\n left join z_discussionids t\n on u.value = t.tid\n left join z_reactiontotalsupvote r\n on r.value = u.value\n where u._key != 'uid:NaN:upvote'\n and t.tid is null\n\n union\n\n select 10 as tagid, 'Discussion' as recordtype, u._key, u.value, score, total\n from :_uid_downvote u\n left join z_discussionids t\n on u.value = t.tid\n left join z_reactiontotalsdownvote r\n on r.value = u.value\n where u._key != 'uid:NaN:downvote'\n and t.tid is not null\n\n union\n\n select 10 as tagid, 'Comment' as recordtype, u._key, u.value, score, total\n from :_uid_downvote u\n left join z_discussionids t\n on u.value = t.tid\n left join z_reactiontotalsdownvote r\n on r.value = u.value\n where u._key != 'uid:NaN:downvote'\n and t.tid is null\n\n ", $UserTag_Map); //TODO: Permissions $Ex->EndExport(); }