As mentioned previously on this site I have migrated my blog from the DotNetNuke Core's Blog module to the News Articles Module provided by ventrian.com. I have seen multiple posts around DotNetNuke.com and other sites asking if there were any migration scripts away from the core blog module and well now there is! This article will provide you the sample code needed to successfully migrate all blog posts from the core blog to the News Article module.
Disclaimer
As with all other postings regarding database modifications to DotNetNuke, I am not responsible for any errors that might occur by executing the below scripts on your site. I have tested and used the below scripts however due to the vast differences in DotNetNuke installations you must not only follow my instructions but also perform sanity checks on the datasets to ensure that your setup doesn't require modifications to this process.
Overview
I have broken the process of migrating blog postings into 4 distinct steps:
- Gather required account data - Get module id, and user id of post owner
- Migrate blog posts that currently have a "summary" and a "detail"
- Migrate blog posts what do NOT currently have a "summary"
- Migrate blog comments
All of the below scripts are assuming that the blog module was only used for one instance and that you desire to move EVERY blog ever written to the new setup. If you need advanced filtering you will need to modify the queries, if you need assistance in identifying the extra filter data elements please let me know.
1. Gather Required Information
Before we can acutally migrate postings we must investigate and find two pieces of information. The first is the user id of the user that should be listed as the creator of the postings, in my case it was my administrator user account. To obtain the id of the account simply login to the account and go to your profile the ID is listed on the profile page in the display format of "(ID: 3)". In this case your user id is 3.
The second piece of information is the ModuleId for your News Article instance. You can either obtain this while looking at the URL in the browser, or you can use the below query to find the module id. If using the query, be sure to check the ModuleTitle to validate that it is the proper Id.
--Ensure that the ModuleTitle is the title of your module
select m.ModuleID, m.ModuleTitle
from ModuleDefinitions md
INNER JOIN modules m
ON (md.ModuleDefId = m.ModuleDefId)
WHERE md.Friendlyname = 'DnnForge - NewsArticles'Now that you have noted this information you can proceed to step 2.
2. Migrate blog posts that have a "summary" and "detail"
The following step will perform the first part of the migration by moving entries that currently have a summary and detail view. This will ensure that in the new setup that you will have the "summary" displayed and that users will have to actually click on the article to view the "detail". I moved these entries first as they use different data fields than the entries which are just displayed full text in the general post listings. Below is the script, please be sure to read my comments below PRIOR to executing the query on your system!
--Insertion for pages with summary!
--PART 1
INSERT INTO dnnForge_newsArticles_Article(
AuthorID,
ApproverId,
CreatedDate,
LastUpdate,
Title,
Summary,
IsApproved,
NumberOfViews,
IsDraft,
StartDate,
ModuleId,
IsFeatured,
LastUpdateID,
IsSecure,
IsNewWindow)
SELECT 3, --ID of the adding user (Obtained in step 1)
0, --0 for no approver
GETDATE(), --You are adding it now
GETDATE(), --You are updating it now
b.Title, --The actual Title
b.Description, --The description (Summary)
1, --Yes it is approved
1, --It has been viewed once
0, --No it is not a draft
b.AddedDate,--Keep the orig post date
598, --Your Module Id (Obtained in step 1)
0, --Not Featured
3, --Last updator
0, --Not a secure post
0 --Not a new window
FROM Blog_Entries b
WHERE Description != ''
AND Description IS NOT NULL
--PART 2
--RUN this after you have actually inserted the main entries
--This actually migrates the detail of the pages
INSERT INTO dnnforge_newsarticles_page(
ArticleId,
Title,
PageText,
SortOrder)
SELECT na.ArticleId, --The id of the new article
na.Title, --The title of the article
b.Entry, --The detailed entry text
0 --Sort order of 0 as this is the only page
FROM Blog_Entries b
INNER JOIN dnnForge_newsArticles_article na
ON (b.title = na.title)
WHERE b.Description != ''
AND b.Description IS NOT NULLIn the above script listing you will notice that we have two parts to this copy process. The first script inserts the general record for each blog entry that has a summary. As part of this insertion process you are adding the id of the article creator and the module id, both obtained in step 1 above. In part two of the script you are inserting the detail information to a page within the News Articles module, this creates the detail view needed. When executing these scripts be sure to execute part one then once completed executing step two!
3. Migrate blog posts that do not have a summary
The second part of the data migration is to migrate blog postings that do not have a summary. These blog entries are those that are displayed with their full content on the general blog view pages (or a portion that was then auto aummarized). Moving these entries is very similar to Part 1 of the migration shown above with minimal changes to the select query. Below you will find the single script needed to migrate these entries.
--Part 1 - Migration entries without summary information
INSERT INTO dnnForge_newsArticles_Article(
AuthorID,
ApproverId,
CreatedDate,
LastUpdate,
Title,
Summary,
IsApproved,
NumberOfViews,
IsDraft,
StartDate,
ModuleId,
IsFeatured,
LastUpdateID,
IsSecure,
IsNewWindow)
SELECT 3, --ID of the adding user (Obtained in step 1)
0, --0 for no approver
GETDATE(), --You are adding it now
GETDATE(), --You are updating it now
b.Title, --The actual Title
b.Entry, --The description (Summary)
1, --Yes it is approved
1, --It has been viewed once
0, --No it is not a draft
b.AddedDate,--Keep the orig post date
598, --Your Module Id (Obtained in step 1)
0, --Not Featured
3, --Last updator
0, --Not a secure post
0 --Not a new window
FROM Blog_Entries b
WHERE (Description IS NULL OR Description = '')As with the script for the first section you will need to be sure to modify the user id and module id to reflect the information you obtained in step 1. After you have executed this script you will have successfully migrated all blog entries to the new system. If you are looking to migrate any existing blog comments continue on to step 4 otherwise please read the conclusion for information regarding next steps!
4. Migrate blog comments
The final script I used when migrating my blog entries was the below script to migrate my blog comments. When performing this migration I simply am moving over the name, timestamp, and post information I am not actually associating the comments with individual user accounts (If they were users when posting), nor am I enabling the ability for comment notifications to be sent. This is typically not much of an issue, however, you will want to be sure to take this into consideration prior to using this script. Below is the needed script, please see the comments below the script regarding the details.
--Copy all comments!!
INSERT INTO dnnforge_newsArticles_comment(
ArticleId,
UserId,
CreatedDate,
Comment,
RemoteAddress,
[Type],
AnonymousName,
AnonymousEmail,
NotifyMe)
SELECT na.ArticleId, --Our new Article id
-1, --Do Not associate it with a user
bc.AddedDate, --The date they made the comment
bc.comment, --The actual comment
'192.168.1.1', --An Ip address I just defaulted it
0, --Comment type
bc.Author, --Entered name of the user
'[email protected]', --An e-mail for notification
0 --Do not notify user of replies...
FROM Blog_comments bc
INNER JOIN blog_entries be
ON (bc.entryId = be.entryid AND bc.Approved = 1)
INNER JOIN dnnForge_NewsArticles_Article na
ON (be.title = Na.Title)As you can see int he above example I am defaulting two pieces of information in each of these comments, IP address and email. You may choose to do this or you can dig deeper into the core blog modules storage of comments to try and obtain this information (I am not sure if it is possible). However, at a minimum by using this script you will retain all comments with their existing timestamps and can obtain a fairly seamless transition.
Conclusion and Parting Thoughts
The above information should allow you to migrate your blog data to the News Articles module via a fairly easy to follow the scripting process. I would like to simply remind everyone again that you MUST sanity check these scripts and the data that they will be inserting PRIOR to actually inserting the records into your database. As with all data operations, there are risks of damage to the database if you incorrectly insert/update data, therefore I fully recommend taking a backup of your database prior to performing any updates.
After you have completed the migration process you will need to manually insert any desired categories and other detailed settings/configurations for the blog entries. Please provide any feedback regarding this process below and feel free to use the forums for script assistance if needed!