/**
* @param ExportModel $ex
*/
protected function forumExport($ex)
{
$characterSet = $ex->getCharacterSet('topic');
if ($characterSet) {
$ex->characterSet = $characterSet;
}
$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();
}
