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