Conversion Script Discussion 9-18-2012

Developer
Sep 19, 2012 at 3:35 AM

Rather than try to discuss conversion script issues in the Issue tracker, I figured I would start this thread to talk about a few items.

First reply to aggiedan http://activeforums.codeplex.com/workitem/1583

You will need to search/replace the {databaseOwner} and {objectQualifier} tokens before you can execute this script in SQL server.

I typically replace {databaseOwner} with "dbo."

and I replace {objectQualifier} with "" 

These are settings in the web.config file, and may vary based on your installation.

Developer
Sep 19, 2012 at 3:38 AM

As for the first question, you should be able to run the entire script, it checks if the stored proc already exists, if so, it drops it. Then it adds it back, and runs the rest

Sep 19, 2012 at 6:57 AM

HTML conversion. Due to a problem with HTML in the body of post I ran this little script after conversion.
This because in the new AF forum, for old posts, I see the HTML code in clear in the post.

update activeforums_Content

set Body=cast(replace(cast(body as nvarchar(max)),'&lt;','<') as ntext)

 

 

update

activeforums_Content

set

Body=cast(replace(cast(body as nvarchar(max)),'&gt;','>') as ntext)

 

 

Developer
Sep 19, 2012 at 7:00 AM

Thanks, I hadn't got to the HTML conversion yet, still trying to track down some data issues (I can't view topic until I update the forum through the control panel). I will look at adding that to the code as well though

Developer
Sep 19, 2012 at 7:07 AM

Looks like my problem right now is that for Topics the NextTopic and PreviousTopic columns aren't populated with the script, but as soon as you "update" the forum through the control panel all the values are calculated for topics in that forum...

 

now to figure out why and see if I can call that from the script

Sep 19, 2012 at 7:41 AM

Edit the forum settings in control pannell is right. You have to define permission and freatures. Doing that correct some tables.

I notice now that the activeforums_UserProfiles is not populated. Total post and reply count for members missing in the forum untile the single member use the forum. I prepare a script to prepolupate this table

Developer
Sep 19, 2012 at 8:58 AM

I think I have the issues resolved with Topics now, so once you convert you should be able to see all topics without any errors thrown, check out my V 1.3 script attached to the work item

http://activeforums.codeplex.com/workitem/1583

I've also started putting together some instructions for use. It's late, the instructions might not make sense, I'll review them again in the morning :D

http://activeforums.codeplex.com/wikipage?title=Migrating%20from%20DNN%20Forums%205.0.3%20to%20Active%20Forums%205.0

Known issues with migration script:

  1. Still need to clean up the HTML bits that display in converted posts (that will be in v1.4

The migration script is still a work in progress, but I think it is fairly close to being stable enough to use. I plan to migrate my website over to AF over the weekend after I do some more testing this week and clean up the known issue (and others that arise)

Sep 19, 2012 at 9:44 AM

 

A fast integration for the activeforums_UserProfiles table.

Sets the basic fields for TopicCount and ReplyCount

*********************************************************************************************************
 Declare @PortalID as int
 set @PortalID=0
 
 truncate table [activeforums_UserProfiles]
  INSERT INTO [MSSql66989].[dbo].[activeforums_UserProfiles]
           ([UserId]
           ,[ModuleId]
           ,[PortalId]
           ,[TopicCount]
           ,[ReplyCount]
           ,[ViewCount]
           ,[AnswerCount]
           ,[RewardPoints]
           ,[UserCaption]
           ,[DateCreated]
           ,[DateUpdated]
           ,[DateLastActivity]
           ,[DateLastPost]
           ,[Signature]
           ,[SignatureDisabled]
           ,[TrustLevel]
           ,[AdminWatch]
           ,[AttachDisabled]
           ,[Avatar]
           ,[AvatarType]
           ,[AvatarDisabled]
           ,[PrefDefaultSort]
           ,[PrefDefaultShowReplies]
           ,[PrefJumpLastPost]
           ,[PrefTopicSubscribe]
           ,[PrefSubscriptionType]
           ,[PrefUseAjax]
           ,[PrefEmailFormat]
           ,[PrefBlockAvatars]
           ,[PrefBlockSignatures]
           ,[PrefPageSize]
           ,[Yahoo]
           ,[MSN]
           ,[ICQ]
           ,[AOL]
           ,[Occupation]
           ,[Location]
           ,[Interests]
           ,[WebSite]
           ,[Badges]
           ,[Bio])
  
  select v_members.UserID,'-1',@PortalID,isnull(TotalParentPosts,0),isnull(TotalReplyPosts,0),'0','0','0',NULL,Users.CreatedOnDate,null,getdate(),null,null
  ,'0','0','0','0',NULL,'0','0','ASC','0','0','0','0','1','0','0','0','20',null,null,null,null,null,null,null,null,null,'' from
  (
  select distinct UserID from dbo.Forum_Posts
  ) as v_members
  left join
  (
  select UserID,count(*) as TotalParentPosts from dbo.Forum_Posts
  where ParentPostID = 0 group by UserID
  ) as v_TotalParentPosts
 on v_members.UserID = v_TotalParentPosts.UserID
  left join
  (
  select UserID,count(*) as TotalReplyPosts from dbo.Forum_Posts
  where ParentPostID <> 0 group by UserID
  ) as v_TotalReplyPosts
 on v_members.UserID = v_TotalReplyPosts.UserID
  inner join dbo.Users
  on dbo.Users.UserID = v_members.UserID
where v_members.UserID not in
(select UserID from [activeforums_UserProfiles])

*********************************************************************************************************

 

 

Developer
Sep 20, 2012 at 5:35 AM

I didn't get any progress on the script tonight (had to take a night off) but will be working on it again over the next few days.

Known issues

  1. Need to clean up HTML issues
  2. Need to populate some UserProfile stuff in AF
  3. Need to create a mapping table for GroupId, ForumId and TopicId from the old DNNForum Urls to new AF Urls. 
Developer
Oct 3, 2012 at 12:35 AM

I upgraded the script (attached to the issue) http://activeforums.codeplex.com/workitem/1583 last night with some changes to build up some conversion tables for URLmapping in the future.

I still want to test/implement skiank's script, and get this all checked into source control so it is easier to manage going forward, instead of versioning the script on the issue (linked above)

Developer
Oct 3, 2012 at 7:25 AM

Found a couple of more things tonight.

 

1) I updated the code that Skiank used for cleaning the HTML to include a number of other characters (here it is, it will also be in the script)

update  dbo.activeforums_Contentset  Body=cast(replace(cast(body as nvarchar(max)),'&lt;','<') as ntext)
update dbo.activeforums_Contentset Body=cast(replace(cast(body as nvarchar(max)),'&gt;','>') as ntext)
update dbo.activeforums_Contentset  Body=cast(replace(cast(body as nvarchar(max)),'&nbsp;',' ') as ntext), Summary=cast(replace(cast(Summary as nvarchar(max)),'&nbsp;',' ') as ntext)
update dbo.activeforums_Contentset  Body=cast(replace(cast(body as nvarchar(max)),'&amp;nbsp;',' ') as ntext), Summary=cast(replace(cast(Summary as nvarchar(max)),'&amp;nbsp;',' ') as ntext)
update dbo.activeforums_Contentset  Body=cast(replace(cast(body as nvarchar(max)),'&quot;','"') as ntext), Summary=cast(replace(cast(Summary as nvarchar(max)),'&quot;','"') as ntext)
update dbo.activeforums_Contentset  Body=cast(replace(cast(body as nvarchar(max)),'&#39;','''') as ntext), Summary=cast(replace(cast(Summary as nvarchar(max)),'&#39;','''') as ntext) GO 

2) It appears with the current iterations of the scripts that you can't actually go back and edit REPLIES, though you can edit TOPICs. This is due to replytoid being NULL on the replies. I have created two new cursors that run after replies are imported to do the populate of that. I am not positive all of this works just yet, so I am not uploading v1.5 to the linked issue, but I am running the script now to see how it goes.

I am heading to bed, so I'll see if I can't get a new script posted tomorrow, assuming this all works :D 

Developer
Oct 3, 2012 at 7:59 AM
Edited Oct 3, 2012 at 8:05 AM

Who needs sleep. V1.5 of the script is attached to the issue.

http://activeforums.codeplex.com/workitem/1583

Found another issue, the last reply info for a forum doesn't appear to be correct, this self corrects once a reply is made ;) but if someone wants to spend some time tracking this down, go for it, I will try later this week if someone else doesn't find it first.

Oct 13, 2012 at 8:07 PM

Need more attention the table activeforums_Topics because some fields cannot be NULL.
The search function give error.

I've integrate with:

  
update activeforums_Topics
set TopicIcon = '',
Announcestart='1900-01-01 00:00:00.000',
Announceend='1900-01-01 00:00:00.000',
url='',
topicdata=''

 

 

Developer
Oct 14, 2012 at 1:26 AM

I'll make another pass through the conversion script this week and add this as well as anything else I come across.

Developer
Oct 16, 2012 at 5:54 AM

Does anyone have a Search that works? All I get are timeouts right now when trying to search (all forums)

Oct 16, 2012 at 8:25 AM

My search work properly and fast.

5 Forums
8640 Topics
97374 Replies
259 active members