$RootCategoryInserted = TRUE;
}
if ($Drop) {
$SQL->Insert('Category', array('ParentCategoryID' => -1, 'TreeLeft' => 2, 'TreeRight' => 3, 'InsertUserID' => 1, 'UpdateUserID' => 1, 'DateInserted' => Gdn_Format::ToDateTime(), 'DateUpdated' => Gdn_Format::ToDateTime(), 'Name' => 'General', 'UrlCode' => 'general', 'Description' => 'General discussions', 'PermissionCategoryID' => -1));
} elseif ($CategoryExists && !$PermissionCategoryIDExists) {
if (!C('Garden.Permissions.Disabled.Category')) {
// Existing installations need to be set up with per/category permissions.
$SQL->Update('Category')->Set('PermissionCategoryID', 'CategoryID', FALSE)->Put();
$SQL->Update('Permission')->Set('JunctionColumn', 'PermissionCategoryID')->Where('JunctionColumn', 'CategoryID')->Put();
}
}
if ($CategoryExists) {
$CategoryModel = new CategoryModel();
$CategoryModel->RebuildTree();
unset($CategoryModel);
}
// Construct the discussion table.
$Construct->Table('Discussion');
$FirstCommentIDExists = $Construct->ColumnExists('FirstCommentID');
$BodyExists = $Construct->ColumnExists('Body');
$LastCommentIDExists = $Construct->ColumnExists('LastCommentID');
$LastCommentUserIDExists = $Construct->ColumnExists('LastCommentUserID');
$CountBookmarksExists = $Construct->ColumnExists('CountBookmarks');
$Construct
->PrimaryKey('DiscussionID')
->Column('Type', 'varchar(10)', NULL, 'index')
public function UpdateCounts() {
// This option could take a while so set the timeout.
set_time_limit(60*5);
// Define the necessary SQL.
$Sqls = array();
if(!$this->ImportExists('Discussion', 'CountComments'))
$Sqls['Discussion.CountComments'] = $this->GetCountSQL('count', 'Discussion', 'Comment');
if(!$this->ImportExists('Discussion', 'LastCommentID'))
$Sqls['Discussion.LastCommentID'] = $this->GetCountSQL('max', 'Discussion', 'Comment');
if(!$this->ImportExists('Discussion', 'DateLastComment')) {
$Sqls['Discussion.DateLastComment'] = "update :_Discussion d
join :_Comment c
on d.LastCommentID = c.CommentID
set d.DateLastComment = c.DateInserted";
}
if (!$this->ImportExists('Discussion', 'CountBookmarks')) {
$Sqls['Discussion.CountBookmarks'] = "update :_Discussion d
set CountBookmarks = (
select count(ud.DiscussionID)
from :_UserDiscussion ud
where ud.Bookmarked = 1
and ud.DiscussionID = d.DiscussionID
)";
}
if(!$this->ImportExists('Discussion', 'LastCommentUserID')) {
$Sqls['Discussion.LastCommentUseID'] = "update :_Discussion d
join :_Comment c
on d.LastCommentID = c.CommentID
set d.LastCommentUserID = c.InsertUserID";
}
if (!$this->ImportExists('Discussion', 'Body')) {
// Update the body of the discussion if it isn't there.
if (!$this->ImportExists('Discussion', 'FirstCommentID'))
$Sqls['Discussion.FirstCommentID'] = $this->GetCountSQL('min', 'Discussion', 'Comment', 'FirstCommentID', 'CommentID');
$Sqls['Discussion.Body'] = "update :_Discussion d
join :_Comment c
on d.FirstCommentID = c.CommentID
set d.Body = c.Body, d.Format = c.Format";
if ($this->ImportExists('Media') && Gdn::Structure()->TableExists('Media')) {
// Comment Media has to go onto the discussion.
$Sqls['Media.Foreign'] = "update :_Media m
join :_Discussion d
on d.FirstCommentID = m.ForeignID and m.ForeignTable = 'comment'
set m.ForeignID = d.DiscussionID, m.ForeignTable = 'discussion'";
}
$Sqls['Comment.FirstComment.Delete'] = "delete :_Comment c
from :_Comment c
inner join :_Discussion d
on d.FirstCommentID = c.CommentID";
}
if ($this->ImportExists('UserDiscussion') && !$this->ImportExists('UserDiscussion', 'CountComments') && $this->ImportExists('UserDiscussion', 'DateLastViewed')) {
$Sqls['UserDiscussuion.CountComments'] = "update :_UserDiscussion ud
set CountComments = (
select count(c.CommentID)
from :_Comment c
where c.DiscussionID = ud.DiscussionID
and c.DateInserted <= ud.DateLastViewed)";
}
$Sqls['Category.CountDiscussions'] = $this->GetCountSQL('count', 'Category', 'Discussion');
$Sqls['Category.CountComments'] = $this->GetCountSQL('sum', 'Category', 'Discussion', 'CountComments', 'CountComments');
if($this->ImportExists('Conversation') && $this->ImportExists('ConversationMessage')) {
$Sqls['Conversation.FirstMessageID'] = $this->GetCountSQL('min', 'Conversation', 'ConversationMessage', 'FirstMessageID', 'MessageID');
if(!$this->ImportExists('Conversation', 'CountMessages'))
$Sqls['Conversation.CountMessages'] = $this->GetCountSQL('count', 'Conversation', 'ConversationMessage', 'CountMessages', 'MessageID');
if(!$this->ImportExists('Conversation', 'LastMessageID'))
$Sqls['Conversation.LastMessageID'] = $this->GetCountSQL('max', 'Conversation', 'ConversationMessage', 'LastMessageID', 'MessageID');
if (!$this->ImportExists('Conversation', 'DateUpdated'))
$Sqls['Converstation.DateUpdated'] = "update :_Conversation c join :_ConversationMessage m on c.LastMessageID = m.MessageID set c.DateUpdated = m.DateInserted";
if($this->ImportExists('UserConversation')) {
if(!$this->ImportExists('UserConversation', 'LastMessageID')) {
if($this->ImportExists('UserConversation', 'DateLastViewed')) {
// Get the value from the DateLastViewed.
$Sqls['UserConversation.LastMessageID'] =
"update :_UserConversation uc
set LastMessageID = (
select max(MessageID)
from :_ConversationMessage m
where m.ConversationID = uc.ConversationID
and m.DateInserted >= uc.DateLastViewed)";
} else {
// Get the value from the conversation.
// In this case just mark all of the messages read.
$Sqls['UserConversation.LastMessageID'] =
"update :_UserConversation uc
join :_Conversation c
on c.ConversationID = uc.ConversationID
set uc.CountReadMessages = c.CountMessages,
uc.LastMessageID = c.LastMessageID";
}
} elseif(!$this->ImportExists('UserConversation', 'DateLastViewed')) {
// We have the last message so grab the date from that.
$Sqls['UserConversation.DateLastViewed'] =
"update :_UserConversation uc
join :_ConversationMessage m
on m.ConversationID = uc.ConversationID
and m.MessageID = uc.LastMessageID
set uc.DateLastViewed = m.DateInserted";
}
}
}
// User counts.
if (!$this->ImportExists('User', 'CountDiscussions')) {
$Sqls['User.CountDiscussions'] = $this->GetCountSQL('count', 'User', 'Discussion', 'CountDiscussions', 'DiscussionID', 'UserID', 'InsertUserID');
}
if (!$this->ImportExists('User', 'CountComments')) {
$Sqls['User.CountComments'] = $this->GetCountSQL('count', 'User', 'Comment', 'CountComments', 'CommentID', 'UserID', 'InsertUserID');
}
if (!$this->ImportExists('User', 'CountBookmarks')) {
$Sqls['User.CountBookmarks'] = "update :_User u
set CountBookmarks = (
select count(ud.DiscussionID)
from :_UserDiscussion ud
where ud.Bookmarked = 1
and ud.UserID = u.UserID
)";
}
// if (!$this->ImportExists('User', 'CountUnreadConversations')) {
// $Sqls['User.CountUnreadConversations'] =
// 'update :_User u
// set u.CountUnreadConversations = (
// select count(c.ConversationID)
// from :_Conversation c
// inner join :_UserConversation uc
// on c.ConversationID = uc.ConversationID
// where uc.UserID = u.UserID
// and uc.CountReadMessages < c.CountMessages
// )';
// }
// The updates start here.
$CurrentSubstep = GetValue('CurrentSubstep', $this->Data, 0);
if($CurrentSubstep == 0) {
// Add the FirstCommentID to the discussion table.
Gdn::Structure()->Table('Discussion')->Column('FirstCommentID', 'int', NULL, 'index')->Set(FALSE, FALSE);
}
// $Sqls2 = array();
// $i = 1;
// foreach ($Sqls as $Name => $Sql) {
// $Sqls2[] = "/* $i. $Name */\n"
// .str_replace(':_', $this->Database->DatabasePrefix, $Sql)
// .";\n";
// $i++;
// }
// throw new Exception(implode("\n", $Sqls2));
// Execute the SQL.
$Keys = array_keys($Sqls);
for($i = $CurrentSubstep; $i < count($Keys); $i++) {
$this->Data['CurrentStepMessage'] = sprintf(T('%s of %s'), $CurrentSubstep + 1, count($Keys));
$Sql = $Sqls[$Keys[$i]];
$this->Query($Sql);
if($this->Timer->ElapsedTime() > $this->MaxStepTime) {
$this->Data['CurrentSubstep'] = $i + 1;
return FALSE;
}
}
if(isset($this->Data['CurrentSubstep']))
unset($this->Data['CurrentSubstep']);
// Remove the FirstCommentID from the discussion table.
Gdn::Structure()->Table('Discussion')->DropColumn('FirstCommentID');
$this->Data['CurrentStepMessage'] = '';
// Update the url codes of categories.
if (!$this->ImportExists('Category', 'UrlCode')) {
$Categories = Gdn::SQL()->Get('Category')->ResultArray();
foreach ($Categories as $Category) {
$UrlCode = Gdn_Format::Url($Category['Name']);
if (strlen($UrlCode) > 50)
$UrlCode = $Category['CategoryID'];
Gdn::SQL()->Put(
'Category',
array('UrlCode' => $UrlCode),
array('CategoryID' => $Category['CategoryID']));
}
}
// Rebuild the category tree.
$CategoryModel = new CategoryModel();
$CategoryModel->RebuildTree();
return TRUE;
}
public function UpdateCounts()
{
// This option could take a while so set the timeout.
set_time_limit(60 * 5);
// Define the necessary SQL.
$Sqls = array();
if (!$this->ImportExists('Discussion', 'LastCommentID')) {
$Sqls['Discussion.LastCommentID'] = $this->GetCountSQL('max', 'Discussion', 'Comment');
}
if (!$this->ImportExists('Discussion', 'DateLastComment')) {
$Sqls['Discussion.DateLastComment'] = "update :_Discussion d\r\n left join :_Comment c\r\n on d.LastCommentID = c.CommentID\r\n set d.DateLastComment = coalesce(c.DateInserted, d.DateInserted)";
}
if (!$this->ImportExists('Discussion', 'CountBookmarks')) {
$Sqls['Discussion.CountBookmarks'] = "update :_Discussion d\r\n set CountBookmarks = (\r\n select count(ud.DiscussionID)\r\n from :_UserDiscussion ud\r\n where ud.Bookmarked = 1\r\n and ud.DiscussionID = d.DiscussionID\r\n )";
}
if (!$this->ImportExists('Discussion', 'LastCommentUserID')) {
$Sqls['Discussion.LastCommentUseID'] = "update :_Discussion d\r\n join :_Comment c\r\n on d.LastCommentID = c.CommentID\r\n set d.LastCommentUserID = c.InsertUserID";
}
if (!$this->ImportExists('Discussion', 'Body')) {
// Update the body of the discussion if it isn't there.
if (!$this->ImportExists('Discussion', 'FirstCommentID')) {
$Sqls['Discussion.FirstCommentID'] = $this->GetCountSQL('min', 'Discussion', 'Comment', 'FirstCommentID', 'CommentID');
}
$Sqls['Discussion.Body'] = "update :_Discussion d\r\n join :_Comment c\r\n on d.FirstCommentID = c.CommentID\r\n set d.Body = c.Body, d.Format = c.Format";
if ($this->ImportExists('Media') && Gdn::Structure()->TableExists('Media')) {
// Comment Media has to go onto the discussion.
$Sqls['Media.Foreign'] = "update :_Media m\r\n join :_Discussion d\r\n on d.FirstCommentID = m.ForeignID and m.ForeignTable = 'comment'\r\n set m.ForeignID = d.DiscussionID, m.ForeignTable = 'discussion'";
}
$Sqls['Comment.FirstComment.Delete'] = "delete c.*\r\n from :_Comment c\r\n inner join :_Discussion d\r\n on d.FirstCommentID = c.CommentID";
}
if (!$this->ImportExists('Discussion', 'CountComments')) {
$Sqls['Discussion.CountComments'] = $this->GetCountSQL('count', 'Discussion', 'Comment');
}
if ($this->ImportExists('UserDiscussion') && !$this->ImportExists('UserDiscussion', 'CountComments') && $this->ImportExists('UserDiscussion', 'DateLastViewed')) {
$Sqls['UserDiscussuion.CountComments'] = "update :_UserDiscussion ud\r\n set CountComments = (\r\n select count(c.CommentID)\r\n from :_Comment c\r\n where c.DiscussionID = ud.DiscussionID\r\n and c.DateInserted <= ud.DateLastViewed)";
}
if ($this->ImportExists('Tag') && $this->ImportExists('TagDiscussion')) {
$Sqls['Tag.CoundDiscussions'] = $this->GetCountSQL('count', 'Tag', 'TagDiscussion', 'CountDiscussions', 'TagID');
}
if ($this->ImportExists('Poll') && Gdn::Structure()->TableExists('Poll')) {
$Sqls['PollOption.CountVotes'] = $this->GetCountSQL('count', 'PollOption', 'PollVote', 'CountVotes', 'PollOptionID');
$Sqls['Poll.CountOptions'] = $this->GetCountSQL('count', 'Poll', 'PollOption', 'CountOptions', 'PollID');
$Sqls['Poll.CountVotes'] = $this->GetCountSQL('sum', 'Poll', 'PollOption', 'CountVotes', 'CountVotes', 'PollID');
}
if ($this->ImportExists('Activity', 'ActivityType')) {
$Sqls['Activity.ActivityTypeID'] = "\r\n update :_Activity a\r\n join :_ActivityType t\r\n on a.ActivityType = t.Name\r\n set a.ActivityTypeID = t.ActivityTypeID";
}
if ($this->ImportExists('Tag') && $this->ImportExists('TagDiscussion')) {
$Sqls['Tag.CoundDiscussions'] = $this->GetCountSQL('count', 'Tag', 'TagDiscussion', 'CountDiscussions', 'TagID');
}
$Sqls['Category.CountDiscussions'] = $this->GetCountSQL('count', 'Category', 'Discussion');
$Sqls['Category.CountComments'] = $this->GetCountSQL('sum', 'Category', 'Discussion', 'CountComments', 'CountComments');
if (!$this->ImportExists('Category', 'PermissionCategoryID')) {
$Sqls['Category.PermissionCategoryID'] = "update :_Category set PermissionCategoryID = -1";
}
if ($this->ImportExists('Conversation') && $this->ImportExists('ConversationMessage')) {
$Sqls['Conversation.FirstMessageID'] = $this->GetCountSQL('min', 'Conversation', 'ConversationMessage', 'FirstMessageID', 'MessageID');
if (!$this->ImportExists('Conversation', 'CountMessages')) {
$Sqls['Conversation.CountMessages'] = $this->GetCountSQL('count', 'Conversation', 'ConversationMessage', 'CountMessages', 'MessageID');
}
if (!$this->ImportExists('Conversation', 'LastMessageID')) {
$Sqls['Conversation.LastMessageID'] = $this->GetCountSQL('max', 'Conversation', 'ConversationMessage', 'LastMessageID', 'MessageID');
}
if (!$this->ImportExists('Conversation', 'DateUpdated')) {
$Sqls['Converstation.DateUpdated'] = "update :_Conversation c join :_ConversationMessage m on c.LastMessageID = m.MessageID set c.DateUpdated = m.DateInserted";
}
if ($this->ImportExists('UserConversation')) {
if (!$this->ImportExists('UserConversation', 'LastMessageID')) {
if ($this->ImportExists('UserConversation', 'DateLastViewed')) {
// Get the value from the DateLastViewed.
$Sqls['UserConversation.LastMessageID'] = "update :_UserConversation uc\r\n set LastMessageID = (\r\n select max(MessageID)\r\n from :_ConversationMessage m\r\n where m.ConversationID = uc.ConversationID\r\n and m.DateInserted >= uc.DateLastViewed)";
} else {
// Get the value from the conversation.
// In this case just mark all of the messages read.
$Sqls['UserConversation.LastMessageID'] = "update :_UserConversation uc\r\n join :_Conversation c\r\n on c.ConversationID = uc.ConversationID\r\n set uc.CountReadMessages = c.CountMessages,\r\n uc.LastMessageID = c.LastMessageID";
}
} elseif (!$this->ImportExists('UserConversation', 'DateLastViewed')) {
// We have the last message so grab the date from that.
$Sqls['UserConversation.DateLastViewed'] = "update :_UserConversation uc\r\n join :_ConversationMessage m\r\n on m.ConversationID = uc.ConversationID\r\n and m.MessageID = uc.LastMessageID\r\n set uc.DateLastViewed = m.DateInserted";
}
}
}
// User counts.
if (!$this->ImportExists('User', 'DateFirstVisit')) {
$Sqls['User.DateFirstVisit'] = 'update :_User set DateFirstVisit = DateInserted';
}
if (!$this->ImportExists('User', 'CountDiscussions')) {
$Sqls['User.CountDiscussions'] = $this->GetCountSQL('count', 'User', 'Discussion', 'CountDiscussions', 'DiscussionID', 'UserID', 'InsertUserID');
}
if (!$this->ImportExists('User', 'CountComments')) {
$Sqls['User.CountComments'] = $this->GetCountSQL('count', 'User', 'Comment', 'CountComments', 'CommentID', 'UserID', 'InsertUserID');
}
if (!$this->ImportExists('User', 'CountBookmarks')) {
$Sqls['User.CountBookmarks'] = "update :_User u\r\n set CountBookmarks = (\r\n select count(ud.DiscussionID)\r\n from :_UserDiscussion ud\r\n where ud.Bookmarked = 1\r\n and ud.UserID = u.UserID\r\n )";
}
// if (!$this->ImportExists('User', 'CountUnreadConversations')) {
// $Sqls['User.CountUnreadConversations'] =
// 'update :_User u
// set u.CountUnreadConversations = (
// select count(c.ConversationID)
// from :_Conversation c
// inner join :_UserConversation uc
// on c.ConversationID = uc.ConversationID
// where uc.UserID = u.UserID
// and uc.CountReadMessages < c.CountMessages
// )';
// }
// The updates start here.
$CurrentSubstep = GetValue('CurrentSubstep', $this->Data, 0);
// $Sqls2 = array();
// $i = 1;
// foreach ($Sqls as $Name => $Sql) {
// $Sqls2[] = "/* $i. $Name */\n"
// .str_replace(':_', $this->Database->DatabasePrefix, $Sql)
// .";\n";
// $i++;
// }
// throw new Exception(implode("\n", $Sqls2));
// Execute the SQL.
$Keys = array_keys($Sqls);
for ($i = $CurrentSubstep; $i < count($Keys); $i++) {
$this->Data['CurrentStepMessage'] = sprintf(T('%s of %s'), $CurrentSubstep + 1, count($Keys));
$Sql = $Sqls[$Keys[$i]];
$this->Query($Sql);
if ($this->Timer->ElapsedTime() > $this->MaxStepTime) {
$this->Data['CurrentSubstep'] = $i + 1;
return FALSE;
}
}
if (isset($this->Data['CurrentSubstep'])) {
unset($this->Data['CurrentSubstep']);
}
$this->Data['CurrentStepMessage'] = '';
// Update the url codes of categories.
if (!$this->ImportExists('Category', 'UrlCode')) {
$Categories = CategoryModel::Categories();
$TakenCodes = array();
foreach ($Categories as $Category) {
$UrlCode = urldecode(Gdn_Format::Url($Category['Name']));
if (strlen($UrlCode) > 50) {
$UrlCode = $Category['CategoryID'];
}
if (in_array($UrlCode, $TakenCodes)) {
$ParentCategory = CategoryModel::Categories($Category['ParentCategoryID']);
if ($ParentCategory && $ParentCategory['CategoryID'] != -1) {
$UrlCode = Gdn_Format::Url($ParentCategory['Name']) . '-' . $UrlCode;
}
if (in_array($UrlCode, $TakenCodes)) {
$UrlCode = $Category['CategoryID'];
}
}
$TakenCodes[] = $UrlCode;
Gdn::SQL()->Put('Category', array('UrlCode' => $UrlCode), array('CategoryID' => $Category['CategoryID']));
}
}
// Rebuild the category tree.
$CategoryModel = new CategoryModel();
$CategoryModel->RebuildTree();
$this->SetCategoryPermissionIDs();
return TRUE;
}
/**
* Grab second forum's data and merge with current forum.
*
* Merge Users on email address. Keeps this forum's username/password.
* Merge Roles, Tags, and Categories on precise name matches.
*
* @todo Compare column names between forums and use intersection
*/
public function MergeForums($OldDatabase, $OldPrefix, $LegacySlug)
{
$NewPrefix = C('Database.DatabasePrefix');
$this->OldDatabase = $OldDatabase;
$this->OldPrefix = $OldPrefix;
$DoLegacy = !empty($LegacySlug);
// USERS //
if ($this->OldTableExists('User')) {
$UserColumns = $this->GetColumns('User', $OldDatabase, $OldPrefix);
// Merge IDs of duplicate users
Gdn::SQL()->Query('update ' . $NewPrefix . 'User u set u.OldID =
(select u2.UserID from `' . $OldDatabase . '`.' . $OldPrefix . 'User u2 where u2.Email = u.Email limit 1)');
// Copy non-duplicate users
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'User (' . $UserColumns . ', OldID)
select ' . $UserColumns . ', UserID
from `' . $OldDatabase . '`.' . $OldPrefix . 'User
where Email not in (select Email from ' . $NewPrefix . 'User)');
// UserMeta
if ($this->OldTableExists('UserMeta')) {
Gdn::SQL()->Query('insert ignore into ' . $NewPrefix . 'UserMeta (UserID, Name, Value)
select u.UserID, um.Name, um.Value
from ' . $NewPrefix . 'User u, `' . $OldDatabase . '`.' . $OldPrefix . 'UserMeta um
where u.OldID = um.UserID');
}
}
// ROLES //
if ($this->OldTableExists('Role')) {
$RoleColumns = $this->GetColumns('Role', $OldDatabase, $OldPrefix);
// Merge IDs of duplicate roles
Gdn::SQL()->Query('update ' . $NewPrefix . 'Role r set r.OldID =
(select r2.RoleID from `' . $OldDatabase . '`.' . $OldPrefix . 'Role r2 where r2.Name = r.Name)');
// Copy non-duplicate roles
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Role (' . $RoleColumns . ', OldID)
select ' . $RoleColumns . ', RoleID
from `' . $OldDatabase . '`.' . $OldPrefix . 'Role
where Name not in (select Name from ' . $NewPrefix . 'Role)');
// UserRole
if ($this->OldTableExists('UserRole')) {
Gdn::SQL()->Query('insert ignore into ' . $NewPrefix . 'UserRole (RoleID, UserID)
select r.RoleID, u.UserID
from ' . $NewPrefix . 'User u, ' . $NewPrefix . 'Role r, `' . $OldDatabase . '`.' . $OldPrefix . 'UserRole ur
where u.OldID = (ur.UserID) and r.OldID = (ur.RoleID)');
}
}
// CATEGORIES //
if ($this->OldTableExists('Category')) {
$CategoryColumnOptions = array('Legacy' => $DoLegacy);
$CategoryColumns = $this->GetColumns('Category', $OldDatabase, $OldPrefix, $CategoryColumnOptions);
/*if ($this->MergeCategories) {
// Merge IDs of duplicate category names
Gdn::SQL()->Query('update '.$NewPrefix.'Category c set c.OldID =
(select c2.CategoryID from `'.$OldDatabase.'`.'.$OldPrefix.'Category c2 where c2.Name = c.Name)');
// Copy non-duplicate categories
Gdn::SQL()->Query('insert into '.$NewPrefix.'Category ('.$CategoryColumns.', OldID)
select '.$CategoryColumns.', CategoryID
from `'.$OldDatabase.'`.'.$OldPrefix.'Category
where Name not in (select Name from '.$NewPrefix.'Category)');
}
else {*/
// Import categories
if ($DoLegacy) {
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Category (' . $CategoryColumns . ', OldID, ForeignID)
select ' . $CategoryColumns . ', CategoryID, concat(\'' . $LegacySlug . '-\', CategoryID)
from `' . $OldDatabase . '`.' . $OldPrefix . 'Category
where Name <> "Root"');
} else {
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Category (' . $CategoryColumns . ', OldID)
select ' . $CategoryColumns . ', CategoryID
from `' . $OldDatabase . '`.' . $OldPrefix . 'Category
where Name <> "Root"');
}
// Remap hierarchy in the ugliest way possible
$CategoryMap = array();
$Categories = Gdn::SQL()->Select('CategoryID')->Select('ParentCategoryID')->Select('OldID')->From('Category')->Where(array('OldID >' => 0))->Get()->Result(DATASET_TYPE_ARRAY);
foreach ($Categories as $Category) {
$CategoryMap[$Category['OldID']] = $Category['CategoryID'];
}
foreach ($Categories as $Category) {
if ($Category['ParentCategoryID'] > 0 && !empty($CategoryMap[$Category['ParentCategoryID']])) {
$ParentID = $CategoryMap[$Category['ParentCategoryID']];
Gdn::SQL()->Update('Category')->Set(array('ParentCategoryID' => $ParentID))->Where(array('CategoryID' => $Category['CategoryID']))->Put();
}
}
$CategoryModel = new CategoryModel();
$CategoryModel->RebuildTree();
//}
// UserCategory
}
// DISCUSSIONS //
if ($this->OldTableExists('Discussion')) {
$DiscussionColumnOptions = array('Legacy' => $DoLegacy);
$DiscussionColumns = $this->GetColumns('Discussion', $OldDatabase, $OldPrefix, $DiscussionColumnOptions);
// Copy over all discussions
if ($DoLegacy) {
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Discussion (' . $DiscussionColumns . ', OldID, ForeignID)
select ' . $DiscussionColumns . ', DiscussionID, concat(\'' . $LegacySlug . '-\', DiscussionID)
from `' . $OldDatabase . '`.' . $OldPrefix . 'Discussion');
} else {
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Discussion (' . $DiscussionColumns . ', OldID)
select ' . $DiscussionColumns . ', DiscussionID
from `' . $OldDatabase . '`.' . $OldPrefix . 'Discussion');
}
// Convert imported discussions to use new UserIDs
Gdn::SQL()->Query('update ' . $NewPrefix . 'Discussion d
set d.InsertUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = d.InsertUserID)
where d.OldID > 0');
Gdn::SQL()->Query('update ' . $NewPrefix . 'Discussion d
set d.UpdateUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = d.UpdateUserID)
where d.OldID > 0');
Gdn::SQL()->Query('update ' . $NewPrefix . 'Discussion d
set d.CategoryID = (SELECT c.CategoryID from ' . $NewPrefix . 'Category c where c.OldID = d.CategoryID)
where d.OldID > 0');
// UserDiscussion
if ($this->OldTableExists('UserDiscussion')) {
Gdn::SQL()->Query('insert ignore into ' . $NewPrefix . 'UserDiscussion
(DiscussionID, UserID, Score, CountComments, DateLastViewed, Dismissed, Bookmarked)
select d.DiscussionID, u.UserID, ud.Score, ud.CountComments, ud.DateLastViewed, ud.Dismissed, ud.Bookmarked
from ' . $NewPrefix . 'User u, ' . $NewPrefix . 'Discussion d, `' . $OldDatabase . '`.' . $OldPrefix . 'UserDiscussion ud
where u.OldID = (ud.UserID) and d.OldID = (ud.DiscussionID)');
}
}
// COMMENTS //
if ($this->OldTableExists('Comment')) {
$CommentColumnOptions = array('Legacy' => $DoLegacy);
$CommentColumns = $this->GetColumns('Comment', $OldDatabase, $OldPrefix, $CommentColumnOptions);
// Copy over all comments
if ($DoLegacy) {
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Comment (' . $CommentColumns . ', OldID, ForeignID)
select ' . $CommentColumns . ', CommentID, concat(\'' . $LegacySlug . '-\', CommentID)
from `' . $OldDatabase . '`.' . $OldPrefix . 'Comment');
} else {
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Comment (' . $CommentColumns . ', OldID)
select ' . $CommentColumns . ', CommentID
from `' . $OldDatabase . '`.' . $OldPrefix . 'Comment');
}
// Convert imported comments to use new UserIDs
Gdn::SQL()->Query('update ' . $NewPrefix . 'Comment c
set c.InsertUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = c.InsertUserID)
where c.OldID > 0');
Gdn::SQL()->Query('update ' . $NewPrefix . 'Comment c
set c.UpdateUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = c.UpdateUserID)
where c.OldID > 0');
// Convert imported comments to use new DiscussionIDs
Gdn::SQL()->Query('update ' . $NewPrefix . 'Comment c
set c.DiscussionID = (SELECT d.DiscussionID from ' . $NewPrefix . 'Discussion d where d.OldID = c.DiscussionID)
where c.OldID > 0');
}
// MEDIA //
if ($this->OldTableExists('Media')) {
$MediaColumns = $this->GetColumns('Media', $OldDatabase, $OldPrefix);
// Copy over all media
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Media (' . $MediaColumns . ', OldID)
select ' . $MediaColumns . ', MediaID
from `' . $OldDatabase . '`.' . $OldPrefix . 'Media');
// InsertUserID
Gdn::SQL()->Query('update ' . $NewPrefix . 'Media m
set m.InsertUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = m.InsertUserID)
where m.OldID > 0');
// ForeignID / ForeignTable
//Gdn::SQL()->Query('update '.$NewPrefix.'Media m
// set m.ForeignID = (SELECT c.CommentID from '.$NewPrefix.'Comment c where c.OldID = m.ForeignID)
// where m.OldID > 0 and m.ForeignTable = \'comment\'');
Gdn::SQL()->Query('update ' . $NewPrefix . 'Media m
set m.ForeignID = (SELECT d.DiscussionID from ' . $NewPrefix . 'Discussion d where d.OldID = m.ForeignID)
where m.OldID > 0 and m.ForeignTable = \'discussion\'');
}
// CONVERSATION //
if ($this->OldTableExists('Conversation')) {
$ConversationColumns = $this->GetColumns('Conversation', $OldDatabase, $OldPrefix);
// Copy over all Conversations
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Conversation (' . $ConversationColumns . ', OldID)
select ' . $ConversationColumns . ', ConversationID
from `' . $OldDatabase . '`.' . $OldPrefix . 'Conversation');
// InsertUserID
Gdn::SQL()->Query('update ' . $NewPrefix . 'Conversation c
set c.InsertUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = c.InsertUserID)
where c.OldID > 0');
// UpdateUserID
Gdn::SQL()->Query('update ' . $NewPrefix . 'Conversation c
set c.UpdateUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = c.UpdateUserID)
where c.OldID > 0');
// Contributors
// a. Build userid lookup
$Users = Gdn::SQL()->Query('select UserID, OldID from ' . $NewPrefix . 'User');
$UserIDLookup = array();
foreach ($Users->Result() as $User) {
$OldID = GetValue('OldID', $User);
$UserIDLookup[$OldID] = GetValue('UserID', $User);
}
// b. Translate contributor userids
$Conversations = Gdn::SQL()->Query('select ConversationID, Contributors
from ' . $NewPrefix . 'Conversation
where Contributors <> ""');
foreach ($Conversations->Result() as $Conversation) {
$Contributors = unserialize(GetValue('Contributors', $Conversation));
if (!is_array($Contributors)) {
continue;
}
$UpdatedContributors = array();
foreach ($Contributors as $UserID) {
if (isset($UserIDLookup[$UserID])) {
$UpdatedContributors[] = $UserIDLookup[$UserID];
}
}
// c. Update each conversation
$ConversationID = GetValue('ConversationID', $Conversation);
Gdn::SQL()->Query('update ' . $NewPrefix . 'Conversation
set Contributors = "' . mysql_real_escape_string(serialize($UpdatedContributors)) . '"
where ConversationID = ' . $ConversationID);
}
// ConversationMessage
// Copy over all ConversationMessages
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'ConversationMessage (ConversationID,Body,Format,
InsertUserID,DateInserted,InsertIPAddress,OldID)
select ConversationID,Body,Format,InsertUserID,DateInserted,InsertIPAddress,MessageID
from `' . $OldDatabase . '`.' . $OldPrefix . 'ConversationMessage');
// ConversationID
Gdn::SQL()->Query('update ' . $NewPrefix . 'ConversationMessage cm
set cm.ConversationID =
(SELECT c.ConversationID from ' . $NewPrefix . 'Conversation c where c.OldID = cm.ConversationID)
where cm.OldID > 0');
// InsertUserID
Gdn::SQL()->Query('update ' . $NewPrefix . 'ConversationMessage c
set c.InsertUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = c.InsertUserID)
where c.OldID > 0');
// Conversation FirstMessageID
Gdn::SQL()->Query('update ' . $NewPrefix . 'Conversation c
set c.FirstMessageID =
(SELECT cm.MessageID from ' . $NewPrefix . 'ConversationMessage cm where cm.OldID = c.FirstMessageID)
where c.OldID > 0');
// Conversation LastMessageID
Gdn::SQL()->Query('update ' . $NewPrefix . 'Conversation c
set c.LastMessageID =
(SELECT cm.MessageID from ' . $NewPrefix . 'ConversationMessage cm where cm.OldID = c.LastMessageID)
where c.OldID > 0');
// UserConversation
Gdn::SQL()->Query('insert ignore into ' . $NewPrefix . 'UserConversation
(ConversationID, UserID, CountReadMessages, DateLastViewed, DateCleared,
Bookmarked, Deleted, DateConversationUpdated)
select c.ConversationID, u.UserID, uc.CountReadMessages, uc.DateLastViewed, uc.DateCleared,
uc.Bookmarked, uc.Deleted, uc.DateConversationUpdated
from ' . $NewPrefix . 'User u, ' . $NewPrefix . 'Conversation c, `' . $OldDatabase . '`.' . $OldPrefix . 'UserConversation uc
where u.OldID = (uc.UserID) and c.OldID = (uc.ConversationID)');
}
// POLLS //
if ($this->OldTableExists('Poll')) {
$PollColumns = $this->GetColumns('Poll', $OldDatabase, $OldPrefix);
$PollOptionColumns = $this->GetColumns('PollOption', $OldDatabase, $OldPrefix);
// Copy over all polls & options
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Poll (' . $PollColumns . ', OldID)
select ' . $PollColumns . ', PollID
from `' . $OldDatabase . '`.' . $OldPrefix . 'Poll');
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'PollOption (' . $PollOptionColumns . ', OldID)
select ' . $PollOptionColumns . ', PollOptionID
from `' . $OldDatabase . '`.' . $OldPrefix . 'PollOption');
// Convert imported options to use new PollIDs
Gdn::SQL()->Query('update ' . $NewPrefix . 'PollOption o
set o.PollID = (SELECT p.DiscussionID from ' . $NewPrefix . 'Poll p where p.OldID = o.PollID)
where o.OldID > 0');
// Convert imported polls & options to use new UserIDs
Gdn::SQL()->Query('update ' . $NewPrefix . 'Poll p
set p.InsertUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = p.InsertUserID)
where p.OldID > 0');
Gdn::SQL()->Query('update ' . $NewPrefix . 'PollOption o
set o.InsertUserID = (SELECT u.UserID from ' . $NewPrefix . 'User u where u.OldID = o.InsertUserID)
where o.OldID > 0');
}
// TAGS //
if ($this->OldTableExists('Tag')) {
$TagColumns = $this->GetColumns('Tag', $OldDatabase, $OldPrefix);
$TagDiscussionColumns = $this->GetColumns('TagDiscussion', $OldDatabase, $OldPrefix);
// Record reference of source forum tag ID
Gdn::SQL()->Query('update ' . $NewPrefix . 'Tag t set t.OldID =
(select t2.TagID from `' . $OldDatabase . '`.' . $OldPrefix . 'Tag t2 where t2.Name = t.Name limit 1)');
// Import tags not present in destination forum
Gdn::SQL()->Query('insert into ' . $NewPrefix . 'Tag (' . $TagColumns . ', OldID)
select ' . $TagColumns . ', TagID
from `' . $OldDatabase . '`.' . $OldPrefix . 'Tag
where Name not in (select Name from ' . $NewPrefix . 'Tag)');
// TagDiscussion
if ($this->OldTableExists('TagDiscussion')) {
// Insert source tag:discussion mapping
Gdn::SQL()->Query('insert ignore into ' . $NewPrefix . 'TagDiscussion (TagID, DiscussionID, OldCategoryID)
select t.TagID, d.DiscussionID, td.CategoryID
from ' . $NewPrefix . 'Tag t, ' . $NewPrefix . 'Discussion d, `' . $OldDatabase . '`.' . $OldPrefix . 'TagDiscussion td
where t.OldID = (td.TagID) and d.OldID = (td.DiscussionID)');
/**
* Incoming tags may or may not have CategoryIDs associated with them, so we'll need to update them with a
* current CategoryID, if applicable, based on the original category ID (OldCategoryID) from the source
*/
Gdn::SQL()->Query('update ' . $NewPrefix . 'TagDiscussion td set CategoryID =
(select c.CategoryID from ' . $NewPrefix . 'Category c where c.OldID = td.OldCategoryID limit 1)
where OldCategoryID > 0');
}
}
////
// Draft - new UserIDs
// Activity - wallpost, activitycomment
// Tag - new UserID, merge on name
// TagDiscussion - new DiscussionID, TagID
// Update counters
// LastCommentID
}
