New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Migrating ActiveSocial Journal Items to core JournalMigrating ActiveSocial Journal Items to core Journal
Previous
 
Next
New Post
8/27/2013 2:53 AM
 

I've rebuilt PokerDIY from scratch on DNN 7.1.1. This involved a lot of SQL scripts to convert the old data that I wanted to keep into the new tables (8 years of changes means there was a lot of stuff I don't need anymore). The new site is a LOT faster (although it has less content)

If anyone else needs this script this is a BASE to get you going - you'll need to work out what it's doing and put in the appropriate values, but it might help someone. I also have a lot of SQL scripts to migrate users, tabs, content, Ventrian modules etc. I can share them if anyone needs them.

Basically it pulls in certain types from the old ActiveSocial Journal table and inserts them base on the new UserIDs. 

DO NOT RUN THIS WITHOUT UNDERSTANDING IT - I take no responsibility ;)

 

 


DECLARE JournalEntries Cursor FOR 

SELECT JournalID
FROM PokerDIY.dbo.activesocial_Journal J
WHERE JournalTypeID IN (5,6,11,12,14,15,201,22, 8)  --Status and post > Post
AND Summary IS NOT NULL AND Summary <> ''
ORDER BY DateCreated

OPEN JournalEntries

DECLARE @OldJournalID int
DECLARE @NewJournalID int
DECLARE @NewContentId int

DECLARE @JournalUserId int
DECLARE @JournalProfileId int

FETCH NEXT FROM JournalEntries INTO @OldJournalID
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRANSACTION

--Insert record into Journal Table
INSERT INTO [dbo].[Journal]           ([JournalTypeId]           ,[UserId]           ,[DateCreated]           ,[DateUpdated]           ,[PortalId]           ,[ProfileId]           ,[GroupId]           ,[Title]           ,[Summary]           ,[ItemData]           ,[ImageURL]           ,[ObjectKey]           ,[AccessKey]           ,[ContentItemId]           ,[IsDeleted]           ,[CommentsDisabled]           ,[CommentsHidden])
SELECT 1           ,
ISNULL((SELECT UserID FROM USERS U WHERE OldUserID = J.UserID), 163034),  --use my account if user not found
[DateCreated]           ,[DateCreated]           ,0 ,          
--if you can't find the profileID then use the UserID (means the To person was deleted)
ISNULL((SELECT UserID FROM USERS U WHERE OldUserID = J.ProfileID), ISNULL((SELECT UserID FROM USERS U WHERE OldUserID = J.UserID), 163034))
,-1           ,''           ,
[Summary]           ,NULL, NULL, NULL, '00000000-0000-0000-0000-000000000000',
NULL AS [ContentItemId]          ,0           ,0           ,0
FROM PokerDIY.dbo.activesocial_Journal J
WHERE JournalID = @OldJournalID

SELECT @NewJournalID = @@IDENTITY FROM [Journal]
SELECT @JournalUserId = (SELECT UserID FROM Journal WHERE [JournalId] = @NewJournalID)
SELECT @JournalProfileId = (SELECT ProfileID FROM Journal WHERE [JournalId] = @NewJournalID)

--Everyone
INSERT INTO [dbo].[Journal_Security]   ([JournalId]   ,[SecurityKey])
SELECT @NewJournalID, 'E' 

--The user who wrote it
INSERT INTO [dbo].[Journal_Security]   ([JournalId]   ,[SecurityKey])
SELECT @NewJournalID, 'U' + CAST(@JournalUserId AS VARCHAR)

--The user who who it was too (ProfileID) (If different)
IF (@JournalProfileId <> @JournalUserId)
BEGIN
INSERT INTO [dbo].[Journal_Security]   ([JournalId]   ,[SecurityKey])
SELECT @NewJournalID, 'P' + CAST(@JournalProfileId AS VARCHAR)
END

--insert the Content Item and get the ContentID
INSERT INTO [dbo].[ContentItems]           ([Content]           ,[ContentTypeID]           ,[TabID]           ,[ModuleID]           ,[ContentKey]           ,[Indexed]           ,[CreatedByUserID]           ,[CreatedOnDate]           ,[LastModifiedByUserID]           ,[LastModifiedOnDate]           ,[StateID])
SELECT Summary           ,4  --Status/Posts
      ,1           ,-1           ,'journalid=' + CAST(@NewJournalID AS VARCHAR)          ,0           ,UserId           ,DateCreated           ,UserId           ,DateCreated           ,NULL
FROM Journal 
WHERE [JournalId] = @NewJournalID
SELECT @NewContentId = @@IDENTITY FROM [ContentItems]

--link the Journal with the new ContentID 
UPDATE Journal
SET ContentItemID = @NewContentId
WHERE JournalID = @NewJournalID

--Do Journal Comments
INSERT INTO [dbo].[Journal_Comments]           ([JournalId]           ,[UserId]           ,[Comment]           ,[DateCreated]           ,[DateUpdated]           ,[CommentXML])
SELECT @NewJournalID           ,
ISNULL((SELECT UserID FROM USERS U WHERE OldUserID = C.UserID), 163034),           
[Comment]           ,[DateCreated]           ,[DateUpdated]           ,NULL
FROM PokerDIY.dbo.activesocial_Comments C
INNER JOIN PokerDIY.dbo.activesocial_JournalComments JC ON JC.CommentId = C.CommentID
WHERE JC.JournalID = @OldJournalID

print @OldJournalID
print @NewJournalID
print @NewContentId

COMMIT TRANSACTION
END TRY
BEGIN CATCH
PRINT 'Error with Journal: ' + CAST(@OldJournalID AS VARCHAR)
SELECT
@OldJournalID AS JournalId, 
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
IF @@Trancount > 0 ROLLBACK TRANSACTION
END CATCH
FETCH NEXT FROM JournalEntries INTO @OldJournalID
END

CLOSE JournalEntries
DEALLOCATE JournalEntries

Entrepreneur

PokerDIY Tournament Manager - PokerDIY Tournament Manager<
PokerDIY Game Finder - Mobile Apps powered by DNN
PokerDIY - Connecting Poker Players

 
New Post
8/27/2013 6:25 PM
 
Rodney,
thanks for your effort, which is very much appreciated.
It is sad that others, who have benefitted from the module before, didn't step up.

Cheers from Germany,
Sebastian Leupold

dnnWerk - The DotNetNuke Experts   German Spoken DotNetNuke User Group

Speed up your DNN Websites with TurboDNN
 
New Post
9/24/2013 8:26 PM
 

FYI:  I have added a SQL script for migrating users here:    http://www.dnnsoftware.com/forums/for...


Entrepreneur

PokerDIY Tournament Manager - PokerDIY Tournament Manager<
PokerDIY Game Finder - Mobile Apps powered by DNN
PokerDIY - Connecting Poker Players

 
Previous
 
Next
HomeHomeUsing DNN Platf...Using DNN Platf...Administration ...Administration ...Migrating ActiveSocial Journal Items to core JournalMigrating ActiveSocial Journal Items to core Journal


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.