My Migration Story to Graffiti
I had to write this sooner but couldn't stay up and went to bed!
My migration process to Graffiti from Community Server 2007 wasn't as simple as others! Almost all the current bloggers who have done this migration, have used Jayme Davis' migrator tool (can be found in his comments).
First I tried to import posts to Graffiti using this tool even though I was aware of the fact that it just imports some main data and isn't tested by many users. For my blog that had large amount of data and many posts, this tool didn't work very well. I tried to solve the issues around this tool for a day but finally ended up. This tool, as is, would be a great tool for small blogs but don't seem to work for many posts.
I don't like to invest the wheel for second time but this time there was no other choice. I could wait for a short while to see what others will end up or could do something myself! I also was going to implement a BlogML converter but left it to Telligent developers per Scott's comment for me.
As an addicted guy, I began my own work on a new migrator to import my posts from Community Server database to Graffiti database. I implemented this as a Windows Application to be simpler to use and faster.
If we can't write such things then who can?! I finally wrote the tool that was able to migrate all my posts and feedbacks from Community Server 2007 to Graffiti Beta 1. It uses SQL Server 2005 or SQL Express and is able to migrate trackbacks as well. However, I didn't work on categories or tags because didn't want them.
Even though, Rich Mercer has a helpful redirector to redirect Community Server posts to Graffiti but I also included a simple part that gets two file paths for a Google SiteMap file and a destination text file to generate text values for my Ionic's ISAPI Rewrite Filter. Initially I couldn't predict that what Rich has built can work well for all my posts so wrote this as well.
However, after some tests, I could import all my old posts to Graffiti database and after some database manipulations I could make it work! Now I have a clean and simple database with small IDs!!
The code doesn't seem very hard to follow. I publish my tool here but with no guarantees. First I set user inputs in my variables and call GetCSSectionID and LoadCSPosts to get the section ID for the Application Key that user has provided and save all the posts for that user in that section in a DataSet. After this I call Import method to insert data to Graffiti database and finally call the GenerateRedirectCodes to generate codes for Ionic ISAPI Rewrite.
private void btnImport_Click(object sender, EventArgs e)
{
this.strCSDB = txtCSConnectionString.Text;
this.strGraffitiDB = txtGraffitiConnectionString.Text;
this.strAppKey = txtAppKey.Text;
this.strUsername = txtUserName.Text;
this.strSiteMapPath = txtSiteMap.Text;
this.strRewriter = txtRewriter.Text;
this.intSectionID = GetCSSectionID();
LoadCSPosts();
Import();
GenerateRedirectCodes();
MessageBox.Show("Import Finished!");
}
Here is the code for GetCSSectionID and LoadCSPosts methods:
private int GetCSSectionID()
{
int sectionID = 0;
using (SqlConnection connection = new SqlConnection(this.strCSDB))
{
connection.Open();
string strCommand = string.Format
("SELECT SectionID FROM cs_Sections WHERE ApplicationKey = '{0}'",
this.strAppKey);
SqlCommand command = new SqlCommand(strCommand, connection);
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
sectionID = int.Parse(reader["SectionID"].ToString());
}
}
return sectionID;
}
private void LoadCSPosts()
{
using (SqlConnection connection = new SqlConnection(this.strCSDB))
{
connection.Open();
string strCommand = string.Format
("SELECT * FROM cs_Posts WHERE (SectionID = '{0}' AND ApplicationPostType = 1" +
" AND PostAuthor = '{1}')",
this.intSectionID.ToString(), this.strUsername);
SqlCommand command = new SqlCommand(strCommand, connection);
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(this.posts);
}
}
Here is the code for Import method that iterates through all Community Server posts in DataSet and loads their comments and trackbacks to a DataSet then inserts posts and their feedbacks into Graffiti database.
private void Import()
{
foreach (DataRow post in this.posts.Tables[0].Rows)
{
// Load comments and trackbacks
DataSet feedbacks = new DataSet();
using (SqlConnection connection = new SqlConnection(this.strCSDB))
{
string strCommand = string.Format
("SELECT * FROM cs_Posts WHERE (SectionID = '{0}' AND ApplicationPostType <> 1"
+ " AND ParentID = {1})",
this.intSectionID.ToString(), post["PostID"]);
SqlCommand selectFeedbacks = new SqlCommand(strCommand, connection);
SqlDataAdapter adapter = new SqlDataAdapter(selectFeedbacks);
adapter.Fill(feedbacks);
}
// Insert items into database
using (SqlConnection connection2 = new SqlConnection(this.strGraffitiDB))
{
// Insert posts
SqlCommand insert = new SqlCommand("InsertPost", connection2);
insert.CommandType = CommandType.StoredProcedure;
insert.Parameters.AddWithValue("Title", post["Subject"].ToString());
insert.Parameters.AddWithValue("PostBody", post["Body"].ToString());
insert.Parameters.AddWithValue("CreatedOn", post["PostDate"].ToString());
insert.Parameters.AddWithValue("Name",
post["PostName"].ToString().Replace(" ", "-"));
insert.Parameters.AddWithValue
("CommentCount", feedbacks.Tables[0].Rows.Count);
int views = int.Parse(post["TotalViews"].ToString())
+ int.Parse(post["AggViews"].ToString());
insert.Parameters.AddWithValue("Views", views);
connection2.Open();
insert.ExecuteNonQuery();
connection2.Close();
// Inserting feedbacks
connection2.Open();
foreach (DataRow feedback in feedbacks.Tables[0].Rows)
{
SqlCommand insertComment = new SqlCommand("InsertFeedback", connection2);
insertComment.CommandType = CommandType.StoredProcedure;
insertComment.Parameters.AddWithValue("Body",
feedback["Body"].ToString());
insertComment.Parameters.AddWithValue("Published",
feedback["PostDate"].ToString());
insertComment.Parameters.AddWithValue("AuthorName",
GetAuthorName(
feedback["PostAuthor"].ToString(),
feedback["PropertyNames"].ToString(),
feedback["PropertyValues"].ToString(),
(feedback["ApplicationPostType"].ToString() == "8") ? true : false));
insertComment.Parameters.AddWithValue("WebSite",
GetAuthorUrl
(feedback["PropertyNames"].ToString(),
feedback["PropertyValues"].ToString()));
insertComment.Parameters.AddWithValue("IPAddress",
feedback["IPAddress"].ToString());
if (feedback["PostAuthor"].ToString() == "Keyvan Nayyeri")
insertComment.Parameters.AddWithValue("UName", "admin");
else
insertComment.Parameters.AddWithValue("UName", DBNull.Value);
insertComment.Parameters.AddWithValue("IsTrackback",
(feedback["ApplicationPostType"].ToString() == "8") ? true : false);
insertComment.ExecuteNonQuery();
}
connection2.Close();
}
}
}
Note that for easier coding and better performance I used to stored procedures to insert posts and comments into Graffiti database. The code for these two stored procedures is here:
-- For posts
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[InsertPost]
@Title nvarchar(255),
@PostBody ntext,
@CreatedOn datetime,
@Name nvarchar(255),
@CommentCount int,
@Views int
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO graffiti_Posts
([Title],[PostBody],[CreatedOn],
[ModifiedOn],[Status],[Content_Type],
[Name],[Comment_Count],[CategoryId],
[Version],[ModifiedBy],[CreatedBy],
[IsDeleted],[Published],[Pending_Comment_Count],
[Views],[UniqueId],[EnableComments],
[UserName],[IsPublished],[SortOrder])
VALUES
(@Title,@PostBody,@CreatedOn,
@CreatedOn,1,'text/html',@Name,@CommentCount,2,0,
'admin','admin',0,@CreatedOn,0,@Views,
NEWID(),1,'admin',1,0)
END
-- For comments
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
CREATE PROCEDURE [dbo].[InsertFeedback]
@Body ntext,
@Published datetime,
@AuthorName nvarchar(128),
@WebSite nvarchar(512),
@IPAddress nvarchar(64),
@UName nvarchar(128),
@IsTrackback bit
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ID int
SET @ID = IDENT_CURRENT('graffiti_Posts')
INSERT INTO graffiti_Comments
([PostId] ,[Body] ,[Published] ,[Name]
,[IsPublished] ,[Version] ,[WebSite] ,[SpamScore]
,[IPAddress] ,[ModifiedOn] ,[IsDeleted],[UniqueId]
,[UserName],[IsTrackback])
VALUES
(@ID ,@Body ,@Published ,@AuthorName
,1 ,0 ,@WebSite ,0 ,@IPAddress ,@Published
,0 ,NEWID() ,@UName ,@IsTrackback)
END
I also used two methods to get the author name and author URL for comments and trackbacks from Community Server data.
private string GetAuthorName(string authorName, string keys,
string values, bool isTrackback)
{
if (authorName == "Keyvan Nayyeri")
return "Keyvan Nayyeri";
else
{
MatchCollection matches = Regex.Matches(keys,
@"SubmittedUserName:S:(\d{1,3}):(\d{1,4}):");
if (isTrackback)
matches = Regex.Matches(keys,
@"trackbackName:S:(\d{1,3}):(\d{1,4}):");
if (matches.Count == 1)
{
string[] Params = matches[0].Value.Split(':');
return values.Substring(Convert.ToInt32(Params[2]),
Convert.ToInt32(Params[3]));
}
}
return "Anonymous";
}
private string GetAuthorUrl(string keys, string values)
{
MatchCollection matches = Regex.Matches(keys,
@"TitleUrl:S:(\d{1,3}):(\d{1,4}):");
if (matches.Count == 1)
{
string[] Params = matches[0].Value.Split(':');
return values.Substring(Convert.ToInt32(Params[2]),
Convert.ToInt32(Params[3]));
}
return "http://nayyeri.net";
}
The last piece of code is for GenerateRedirectCodes method that uses some XML and string manipulations to generate text values for my ISAPI filter.
private void GenerateRedirectCodes()
{
StringBuilder finalText = new StringBuilder();
XmlReader reader = XmlReader.Create
(new MemoryStream(File.ReadAllBytes(this.strSiteMapPath)));
XPathDocument xpathDoc = new XPathDocument(reader);
XPathNavigator navigator = xpathDoc.CreateNavigator();
foreach (XPathItem item in navigator.Select("/urlset/url/loc"))
{
string strOld = string.Format("{0}", item.Value);
int slashIndex = strOld.LastIndexOf('/');
string strTemp = strOld.Remove(0, (strOld.Length - slashIndex));
int dotIndex = strTemp.LastIndexOf('.');
string strNew = string.Format("http://nayyeri.net/blog/{0}/",
strTemp.Remove(dotIndex, (strTemp.Length - dotIndex)));
finalText.AppendLine(string.Format("RewriteRule {0} {1} [I,RP]",
strOld, strNew));
}
File.WriteAllText(this.strRewriter, finalText.ToString());
}
This tool is available for download from here but again don't forget that you should use it on your own risk. Obviously this code needs some modifications to work for others but I thought it's worthwhile to share it with others in the case that someone has gotten in same troubles as me. There is no help file but feel free to ask your questions in comments and I try to help you there.
Although I finished my migration but there are still some issues on my site that I have to fix. Not all my posts have a page because Graffiti was unable to generate pages for them. Also I need to test all my old URLs to make sure they're redirected to new location.
Enjoy Graffiti!
[advertisement] Axosoft OnTime 2008 is four developer tools in one: bug tracking, project wiki, feature management, and help desk. It manages your development process so developers can focus on coding. Installed or Hosted – Free Single-user license -- Free 30-day team trial.
2 Comments : 12.07.07
Feedbacks
Ryan,
I think you're able to find everything via search feature. For instance, if you want to find CS Dev Guide posts then just search for "CS Dev Guide".
As long as my blog is getting older it's hard to manage categories and tags and I think you can understand this.
#1
Ryan
01.05.2008 @ 3:09 PM
You said you didn't want categories or tags, but it's a lot harder to find things in your archives now as a result. If I hadn't saved my bookmark to your site as "CS Dev Guide", I would have never been able to know what to search for to find what I needed. Just thought you should know.