BEFORE RUNNING THIS SECTION YOU MUST FIRST OPEN THE DESIGN VIEW OF THE threads TABLE AND SET THE IDENTITY VALUE OF THE threadid FIELD TO 'NO'
*********************************************************************************************************************************************************/
/********************************************************************************************************************************************************
STEP 1: BECAUSE THE NEW FORUM MERGES threads AND messages FROM THE OLD SYSTEM WE WILL NEED TO MAKE SURE THERE ARE NO MATCHING PRIMARY KEYS
TO DO THIS WE WILL UPDATE THE IDENTITIES ON THE SMALLER threads TABLE TO BE LARGER THAN THE LARGEST ID ON THE messages TABLE.
IN ORDER TO DO THIS WE WILL HAVE TO DROP THE PRIMARY KEY CONSTRAINT ON THE TABLE. ONCE WE ARE DONE WE RECREATE THE CONSTRAINT.
*********************************************************************************************************************************************************/
USE [TM]
PRINT 'START 1'
UPDATE TurfMonster_Forum..threads set threadid = (threadid + 250000)
PRINT 'END 1'
GO
/********************************************************************************************************************************************************
STEP 2: NOW THAT THE THREADID HAS BEEN CHANGES WE WILL NEED TO UPDATE ALL REFERENCES (FOREIGN KEYS) TO THAT FIELD TO MATCH
THE ONLY REFERENCES FOUND WERE IN THE messages TABLE
*********************************************************************************************************************************************************/
PRINT 'START 2'
UPDATE TurfMonster_Forum..messages set threadid = (threadid+250000)
PRINT 'END 2'
GO
/********************************************************************************************************************************************************
STEP 3: NOW TRANSFER MEMBER IMFORMATION. THE OLD SYSTEM STORED IT IN members. THE NEW SYSTEM STORES IT IN InstantASP_Users AND InstantForum_Users.
THE InstantASP_UsersRoles TABLE IS USED TO DEFINE THE PERMISSIONS FOR EACH USERS IN THE NEW SYSTEM. THIS TABLE NEEDS AN ENTRY FOR EVERY
INDIVIDUAL ROLE FOR WHICH A USERS IS AUTHORIZED. FOR EXAMPLE, AN ADMINISTRATOR WILL HAVE SIX ENTRIES IN THIS TABLE. ONE FOR EACH LEVEL.
*********************************************************************************************************************************************************/
PRINT 'START 3'
SET IDENTITY_INSERT InstantASP_users ON
INSERT InstantASP_users (userID,EmailAddress,Password, username, CreatedDate, primaryroleid)
(SELECT memberid, emailaddress,password,username,dateregistered, 2 from TurfMonster_Forum..members)
INSERT InstantForum_users (userID,PublicEmailAddress,WebAddress, ICQ, AIM, Yahoo, MSN, PostCount)
(SELECT memberid, emailaddress,website,icqnumber,aim,yahoo,msn,totalposts from TurfMonster_Forum..members)
Insert InstantForum_Folders (UserId, FolderName,InBox,SentItems,DeletedItems,SortOrder,Required)
(Select UserId, 'PMFolder_DeletedItems',0,0,1,1,1 From InstantASP_Users)
Insert InstantForum_Folders (UserId, FolderName,InBox,SentItems,DeletedItems,SortOrder,Required)
(Select UserId, 'PMFolder_SentItems',0,1,0,2,1 From InstantASP_Users)
Insert InstantForum_Folders (UserId, FolderName,InBox,SentItems,DeletedItems,SortOrder,Required)
(Select UserId, 'PMFolder_InBox',1,0,0,3,1 From InstantASP_Users)
INSERT InstantASP_UsersRoles
Select UserId, 1 From InstantASP_Users
INSERT InstantASP_UsersRoles
Select UserId, 2 From InstantASP_Users
SET IDENTITY_INSERT InstantASP_users OFF
PRINT 'END 3'
GO
/********************************************************************************************************************************************************
STEP 4: NEXT WE WILL IMPORT DATA FROM THE forums AND categories TABLES INTO THE InstantForum_Forums TABLE. THE OLD SYSTEM STORED CATEGORIES
IN THE categories TABLE AND FORUMS IN THE forums TABLE WITH A FOREIGN KEY RELATIONSHIP BACK TO THE categories TABLE. THIS RELATIONSHIP WAS
HANDLED IN THE SECOND INSERT STATEMENT WHERE WE INSERT forums.forumId INTO THE InstantForum_Forums.ParentId FIELD.
WE WILL ALSO NEED TO ADD ALL THE PERMISSIONS IN THE InstantForum_ForumsRoles TABLE OTHERWISE THE FORUMS WILL NOT BE VISIBLE TO ANYONE
*********************************************************************************************************************************************************/
PRINT 'START 4'
SET IDENTITY_INSERT InstantForum_Forums ON
/*IMPORT TurfMonster_Forum..forums*/
INSERT InstantForum_Forums (ForumID, Name, sortorder)
(SELECT categoryid, name, sortorder from TurfMonster_Forum..categories)
INSERT InstantForum_Forums (ForumID, parentId, Name, TotalPosts, TotalTopics, sortorder)
(SELECT forumid, categoryId, forumname, postcount, threadcount, sortorder from TurfMonster_Forum..forums)
SET IDENTITY_INSERT InstantForum_Forums OFF
GO
/********************************************************************************************************************************************************
THIS SETS THE PERMISSIONS ON EACH FORUM SO THAT EACH DIFFERENT TYPE OF ROLE CAN READ THE DIFFERENT THREADS
*********************************************************************************************************************************************************/
DECLARE @catid int
begin
DECLARE forum_cursor CURSOR FOR
SELECT (forumid) FROM instantforum_forums
OPEN forum_cursor
FETCH NEXT FROM forum_cursor INTO @catid
WHILE @@FETCH_STATUS = 0
BEGIN
Insert InstantForum_ForumsRoles (ForumId, RoleId) VALUES (@catId, 1)
Insert InstantForum_ForumsRoles (ForumId, RoleId) VALUES (@catId, 2)
Insert InstantForum_ForumsRoles (ForumId, RoleId) VALUES (@catId, 3)
/* Insert InstantForum_ForumsRoles (ForumId, RoleId) VALUES (@catId, 4)
Insert InstantForum_ForumsRoles (ForumId, RoleId) VALUES (@catId, 5)
Insert InstantForum_ForumsRoles (ForumId, RoleId) VALUES (@catId, 6)
*/ FETCH NEXT FROM forum_cursor INTO @catid
END
CLOSE forum_cursor
DEALLOCATE forum_cursor
END
update instantforum_forums set iscategory = 1 where forumid = (11,12,13,15,17,18,21)
PRINT 'END 4'
GO
/********************************************************************************************************************************************************
STEP 6: THIS SECTION IMPORTS THE threads AND messages TABLES INTO THE InstantForum_Topics TABLE.
*********************************************************************************************************************************************************/
PRINT 'START 5'
SET IDENTITY_INSERT InstantForum_Topics ON
INSERT InstantForum_Topics(PostId, forumid, UserId, lastposteruserid, lastposterusername, replies, views, title, datestamp, lastposterdate, ispinned,
islocked, ispoll, hasattachments)
(SELECT threadId, forumid, memberid, lastpostermemberid, lastposterguestname, totalposts, timesviewed, threadsubject, datecreated,
lastactivity, sticky, closed, haspoll, hasattachment from TurfMonster_Forum..threads)
INSERT InstantForum_Topics(PostId, forumid, TopicId, parentId, userid, messageiconname, lastposteruserid, lastposterusername, title, description, datestamp,
HasAttachments, editusername, editdatestamp, ipaddress)
(SELECT m.messageid, t.forumid, t.threadid, m.replyorder, m.memberid, m.messageicon, m.memberid, m.guestname, m.subject,cast(m.body as nvarchar(300)), m.dateposted,
m.hasattachment, m.lasteditedname, m.lastediteddate, m.hostname from TurfMonster_Forum..messages m , TurfMonster_Forum..threads t where m.threadid = t.threadid)
SET IDENTITY_INSERT InstantForum_Topics OFF
PRINT 'END 5'
GO
/********************************************************************************************************************************************************
STEP 7: THIS SECTION IMPORTS THE threads AND messages TABLES INTO THE InstantForum_Messages TABLE. ALL threads AND messages IN THE OLD SYSTEM
ARE STORED BOTH IN THE InstantForum_Topics AND THE InstantForum_Messages TABLES. THE InstantForum_Messages TABLE HOLDS THE BODY OF
ANY POST AND THE InstantForum_Topics TABLE HOLDS THE SUBJECT, DESCRIPTION AND ALL OTHER INFORMATION.
*********************************************************************************************************************************************************/
PRINT 'START 6'
INSERT InstantForum_Messages(PostId, Message, DateStamp)
(SELECT threadId, threadsubject, datecreated from TurfMonster_Forum..threads)
INSERT InstantForum_Messages(PostId, Message, DateStamp)
(SELECT messageid, body, dateposted from TurfMonster_Forum..messages)
PRINT 'END 6'
GO
/********************************************************************************************************************************************************
STEP 5: THIS SECTION HANDLES THE IMPORTATION OF POLLS. THE NEW SYSTEM MERGES TWO TABLES FROM THE OLD SYSTEM (polloptions AND pollanswers)
*********************************************************************************************************************************************************/
PRINT 'START 7'
SET IDENTITY_INSERT InstantForum_Polls ON
INSERT InstantForum_Polls (PollId, PostId, UserId, QuestionText, MultipleChoice)
(SELECT p.pollid, t.threadid, t.memberid, t.threadsubject, p.MultiVoting from TurfMonster_Forum..Polls p, TurfMonster_Forum..threads t
WHERE t.pollid = p.pollid)
SET IDENTITY_INSERT InstantForum_Polls OFF
SET IDENTITY_INSERT InstantForum_PollAnswers ON
INSERT InstantForum_PollAnswers (PollAnswerId, PollId, AnswerText)
(SELECT optionid, pollid, description from TurfMonster_Forum..Polloptions WHERE pollid in (select pollid from instantforum_polls))
SET IDENTITY_INSERT InstantForum_PollAnswers OFF
INSERT InstantForum_PollVotes (PollAnswerId, UserId)
(SELECT optionId, memberid from TurfMonster_Forum..pollvoted where optionid in (select PollAnswerId from InstantForum_PollAnswers ))
PRINT 'END 7'
GO
/********************************************************************************************************************************************************
STEP 8: NOW WE HAVE TO HANDLE THE SORT ORDER OF POSTS WITHIN A TOPIC. THE OLD SYSTEM USED A FIELD CALLED replyorder WHICH HELD THE POSITION (STARTING
AT 0 AND MOVING UP BY 1) OF THE POST. THE NEW SYSTEM STORES A ParentId WHICH IS A FOREIGN KEY RELATIONSHIP BACT TO ITSELF.
TO DO THIS WE WILL CREATE A TABLE WHICH WILL HOLD THE PARENT RECORDS ID AND THE CHILDS ID. FROM THERE WE CAN UPDATE THE ParentID FIELD
ON THE InstantForum_Topics TABLE.
*********************************************************************************************************************************************************/
PRINT 'START 8'
CREATE TABLE replyOrder (midParent int NULL , midChild int NULL )
SET NOCount on
DECLARE @threadid varchar(255),
@ct int, @parentid int, @childid int
begin
--cursor to get all the valid thread ids from the thread table
DECLARE thread_cursor CURSOR FOR
SELECT (threadid) FROM TurfMonster_Forum..threads
OPEN thread_cursor
FETCH NEXT FROM thread_cursor INTO @threadid
--while the last fetch request returned a record . . . loop
WHILE @@FETCH_STATUS = 0
/*BEGIN
Select @ct = max(replyorder) from TurfMonster_Forum..messages where threadid = @threadid
WHILE @ct > 0
*/
BEGIN
if exists (select replyorder from TurfMonster_Forum..messages where threadid = @threadid and replyorder = (@ct-1))
BEGIN
Insert ReplyOrder (midchild, midparent)
(SELECT m.messageid child, m1.messageid parent from TurfMonster_Forum..messages m, TurfMonster_Forum..messages m1
WHERE m.threadid = m1.threadid
AND m.replyorder = (m1.replyorder-1)
AND m.threadid = @threadid)
END
/* SELECT @ct=@ct-1
END
*/
FETCH NEXT FROM thread_cursor INTO @threadid
END
CLOSE thread_cursor
DEALLOCATE thread_cursor
Print 'END 8'
Print 'START 9'
DECLARE insert_cursor CURSOR FOR
SELECT * FROM REPLYORDER
OPEN insert_cursor
FETCH NEXT FROM insert_cursor INTO @parentid , @childid
--while the last fetch request returned a record . . . loop
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE InstantForum_Topics set parentId = @parentid where postid = @childid
FETCH NEXT FROM insert_cursor INTO @parentid , @childid
END
close insert_cursor
DEALLOCATE insert_cursor
END
DROP TABLE replyorder
PRINT 'END 9'
GO