Subtle Lock in - liberating my data from Notes Databases Part 2!

The final part of my data migration involved migrating the site's comments, and placing them against the correct entries in the newly populated SQL Subtext database. I used the same techniques as previously to get the bulk of the SQL script created, with one minor addition.

Can you spot it?
utcDateTime := @Text(@Year(@Created)) + "-" + @Right("0" + @Text(@Month(@Created)); 2) + "-" + @Right("0" + @Text(@Day(@Created)); 2) + " " + @Right("0" + @Text(@Hour(@Created)); 2) + ":" + @Right("0" + @Text(@Minute(@Created)); 2) + ":00.000"; NewEntryID := "set @newentryid = (select top 1 ID from subtext_Content where Title = '" + @ReplaceSubstring( OriginalSubject; "'"; "''" ) + "')"; NewEntryID + @NewLine + "insert into subtext_Feedback (Title, Body, BlogId, Author, EntryId, IsBlogAuthor, Email, FeedbackType, StatusFlag, CommentAPI, FeedbackChecksumHash, DateCreated, DateModified)VALUES(" + "'" + @ReplaceSubstring( OriginalSubject; "'"; "''" ) + "', " + "'" + @ReplaceSubstring( @Implode( CommentBody; "<p>") ; "'"; "''" ) + "', 0, '" + CommentName + "', @newentryid, 0, '" + CommentEmail + "', 1, 1, 0, 'CheckSum!' ,'"+ utcDateTime + "', '" + utcDateTime + "')"

Yep, that's right, there's a little bit of extra fun in there to make sure the comment goes against the newly created EntryID. I'm assuming here that my OriginalSubjects are unique, and for the most part they are, so this works fine in my case. For production data you'd probably want to double check that first. SQL wont allow you to nest a subquery inside an insert statement, so I needed to declare a variable and assign it's value to the ID, and then use the variable in the insert. No drama, just makes the outputted SQL script a bit larger. Those who are paying attention will notice that the variable @newentry id isn't declared anywhere. I added it manually before running the SQL script against the destination database, as it only needs a single declaration.

Finally, subtext maintains counters for the Post's Feedback in it's main table, so I ran a quick update:
update subtext_Content set FeedBackCount = (select count(1) from subtext_FeedBack where subtext_FeedBack.EntryId = subtext_Content.ID)

Done! The site is cutting over next week. One site down, one to go, and then the Domino box can be decomissioned. Exciting times in data migration land.

Tags: , , , ,

Posted on Sunday, July 08, 2007 6:03 PM | ASP.NET Notes/Domino Miscellaneous

Like this? Share it!

No comments posted yet.

Post a comment
Please add 1 and 3 and type the answer here:
Remember me?
Ensure the word in this box says 'orange':