YAFLogo

guest
  • guest
  • Guest Topic Starter
14 years ago
Here's the script (in order) that I did to move everything from phpbb over to YAF. I don't know what version of phpbb I was using, because I turned it off a long time ago. I was getting spammed too much. The YAF version I did this under was 1.9.1.6. I use SQL Server 2005. I have an issue after it converts though: some of the topics aren't showing up on the page even though they're in the database. The thread for that issue can be seen here .

What I did first was to follow the install instructions in order to install YAF. In my case, I also needed it to work inside my existing .NET application, so I also followed those instructions. Then I ran this script:

First, you have to remove all of the constraints (I just removed all keys and indexes) that YAF sets up. I didn't check to see if they existed first, because I had just finished the install:

ALTER TABLE yaf_AccessMask DROP CONSTRAINT FK_yaf_AccessMask_yaf_Board
ALTER TABLE yaf_Active DROP CONSTRAINT FK_yaf_Active_yaf_Board
ALTER TABLE yaf_Active DROP CONSTRAINT FK_yaf_Active_yaf_Forum
ALTER TABLE yaf_Active DROP CONSTRAINT FK_yaf_Active_yaf_Topic
ALTER TABLE yaf_Active DROP CONSTRAINT FK_yaf_Active_yaf_User
ALTER TABLE yaf_Active DROP CONSTRAINT PK_yaf_Active
ALTER TABLE yaf_Attachment DROP CONSTRAINT FK_yaf_Attachment_yaf_Message
ALTER TABLE yaf_Attachment DROP CONSTRAINT PK_yaf_Attachment
ALTER TABLE yaf_BannedIP DROP CONSTRAINT FK_yaf_BannedIP_yaf_Board
ALTER TABLE yaf_BannedIP DROP CONSTRAINT IX_yaf_BannedIP
ALTER TABLE yaf_BannedIP DROP CONSTRAINT PK_yaf_BannedIP
ALTER TABLE yaf_Category DROP CONSTRAINT FK_yaf_Category_yaf_Board
ALTER TABLE yaf_Category DROP CONSTRAINT IX_yaf_Category
ALTER TABLE yaf_CheckEmail DROP CONSTRAINT FK_yaf_CheckEmail_yaf_User
ALTER TABLE yaf_CheckEmail DROP CONSTRAINT IX_yaf_CheckEmail
ALTER TABLE yaf_CheckEmail DROP CONSTRAINT PK_yaf_CheckEmail
ALTER TABLE yaf_Choice DROP CONSTRAINT FK_yaf_Choice_yaf_Poll
ALTER TABLE yaf_Choice DROP CONSTRAINT PK_yaf_Choice
ALTER TABLE yaf_EventLog DROP CONSTRAINT FK_yaf_EventLog_yaf_User
ALTER TABLE yaf_EventLog DROP CONSTRAINT PK_EventLog
ALTER TABLE yaf_Forum DROP CONSTRAINT FK_yaf_Forum_yaf_Category
ALTER TABLE yaf_Category DROP CONSTRAINT PK_yaf_Category
ALTER TABLE yaf_Forum DROP CONSTRAINT FK_yaf_Forum_yaf_Forum
ALTER TABLE yaf_Forum DROP CONSTRAINT FK_yaf_Forum_yaf_Message
ALTER TABLE yaf_Forum DROP CONSTRAINT FK_yaf_Forum_yaf_Topic
ALTER TABLE yaf_Forum DROP CONSTRAINT FK_yaf_Forum_yaf_User
ALTER TABLE yaf_Forum DROP CONSTRAINT IX_yaf_Forum
ALTER TABLE yaf_ForumAccess DROP CONSTRAINT FK_yaf_ForumAccess_yaf_AccessMask
ALTER TABLE yaf_ForumAccess DROP CONSTRAINT FK_yaf_ForumAccess_yaf_Forum
ALTER TABLE yaf_ForumAccess DROP CONSTRAINT FK_yaf_ForumAccess_yaf_Group
ALTER TABLE yaf_ForumAccess DROP CONSTRAINT PK_yaf_ForumAccess
ALTER TABLE yaf_Group DROP CONSTRAINT FK_yaf_Group_yaf_Board
ALTER TABLE yaf_Group DROP CONSTRAINT IX_yaf_Group
ALTER TABLE yaf_Mail DROP CONSTRAINT PK_yaf_Mail
ALTER TABLE yaf_Message DROP CONSTRAINT FK_yaf_Message_yaf_Message
ALTER TABLE yaf_Message DROP CONSTRAINT FK_yaf_Message_yaf_Topic
ALTER TABLE yaf_Message DROP CONSTRAINT FK_yaf_Message_yaf_User
ALTER TABLE yaf_NntpForum DROP CONSTRAINT FK_yaf_NntpForum_yaf_NntpServer
ALTER TABLE yaf_NntpServer DROP CONSTRAINT FK_yaf_NntpServer_yaf_Board
ALTER TABLE yaf_NntpServer DROP CONSTRAINT PK_yaf_NntpServer
ALTER TABLE yaf_NntpTopic DROP CONSTRAINT FK_yaf_NntpTopic_yaf_NntpForum
ALTER TABLE yaf_NntpForum DROP CONSTRAINT PK_yaf_NntpForum
ALTER TABLE yaf_NntpForum DROP CONSTRAINT FK_yaf_NntpForum_yaf_Forum
ALTER TABLE yaf_NntpTopic DROP CONSTRAINT FK_yaf_NntpTopic_yaf_Topic
ALTER TABLE yaf_NntpTopic DROP CONSTRAINT PK_yaf_NntpTopic
ALTER TABLE yaf_PMessage DROP CONSTRAINT FK_yaf_PMessage_yaf_User1
ALTER TABLE yaf_PollVote DROP CONSTRAINT FK_yaf_PollVote_yaf_Poll
ALTER TABLE yaf_PollVote DROP CONSTRAINT PK_yaf_PollVote
ALTER TABLE yaf_Rank DROP CONSTRAINT FK_yaf_Rank_yaf_Board
ALTER TABLE yaf_Rank DROP CONSTRAINT IX_yaf_Rank
ALTER TABLE yaf_Registry DROP CONSTRAINT FK_yaf_Registry_yaf_Board
ALTER TABLE yaf_Registry DROP CONSTRAINT PK_Registry
ALTER TABLE yaf_Replace_Words DROP CONSTRAINT PK_Replace_Words
ALTER TABLE yaf_Smiley DROP CONSTRAINT FK_yaf_Smiley_yaf_Board
ALTER TABLE yaf_Smiley DROP CONSTRAINT IX_yaf_Smiley
ALTER TABLE yaf_Smiley DROP CONSTRAINT PK_yaf_Smiley
ALTER TABLE yaf_Topic DROP CONSTRAINT FK_yaf_Topic_yaf_Forum
ALTER TABLE yaf_Topic DROP CONSTRAINT FK_yaf_Topic_yaf_Message
ALTER TABLE yaf_Message DROP CONSTRAINT PK_yaf_Message
ALTER TABLE yaf_Topic DROP CONSTRAINT FK_yaf_Topic_yaf_Poll
ALTER TABLE yaf_Poll DROP CONSTRAINT PK_yaf_Poll
ALTER TABLE yaf_Topic DROP CONSTRAINT FK_yaf_Topic_yaf_Topic
ALTER TABLE yaf_Topic DROP CONSTRAINT FK_yaf_Topic_yaf_User
ALTER TABLE yaf_Topic DROP CONSTRAINT FK_yaf_Topic_yaf_User2
ALTER TABLE yaf_User DROP CONSTRAINT FK_yaf_User_yaf_Board
ALTER TABLE yaf_Board DROP CONSTRAINT PK_yaf_Board
ALTER TABLE yaf_User DROP CONSTRAINT FK_yaf_User_yaf_Rank
ALTER TABLE yaf_User DROP CONSTRAINT IX_yaf_User
ALTER TABLE yaf_Rank DROP CONSTRAINT PK_yaf_Rank
ALTER TABLE yaf_UserForum DROP CONSTRAINT FK_yaf_UserForum_yaf_AccessMask
ALTER TABLE yaf_AccessMask DROP CONSTRAINT PK_yaf_AccessMask
ALTER TABLE yaf_UserForum DROP CONSTRAINT FK_yaf_UserForum_yaf_Forum
ALTER TABLE yaf_UserForum DROP CONSTRAINT FK_yaf_UserForum_yaf_User
ALTER TABLE yaf_UserForum DROP CONSTRAINT PK_yaf_UserForum
ALTER TABLE yaf_UserGroup DROP CONSTRAINT FK_yaf_UserGroup_yaf_Group
ALTER TABLE yaf_Group DROP CONSTRAINT PK_yaf_Group
ALTER TABLE yaf_UserGroup DROP CONSTRAINT FK_yaf_UserGroup_yaf_User
ALTER TABLE yaf_UserGroup DROP CONSTRAINT PK_yaf_UserGroup
ALTER TABLE yaf_UserPMessage DROP CONSTRAINT FK_yaf_UserPMessage_yaf_PMessage
ALTER TABLE yaf_PMessage DROP CONSTRAINT PK_yaf_PMessage
ALTER TABLE yaf_UserPMessage DROP CONSTRAINT FK_yaf_UserPMessage_yaf_User
ALTER TABLE yaf_UserPMessage DROP CONSTRAINT PK_yaf_UserPMessage
ALTER TABLE yaf_WatchForum DROP CONSTRAINT FK_yaf_WatchForum_yaf_Forum
ALTER TABLE yaf_Forum DROP CONSTRAINT PK_yaf_Forum
ALTER TABLE yaf_WatchForum DROP CONSTRAINT FK_yaf_WatchForum_yaf_User
ALTER TABLE yaf_WatchForum DROP CONSTRAINT IX_yaf_WatchForum
ALTER TABLE yaf_WatchForum DROP CONSTRAINT PK_yaf_WatchForum
ALTER TABLE yaf_WatchTopic DROP CONSTRAINT FK_yaf_WatchTopic_yaf_Topic
ALTER TABLE yaf_Topic DROP CONSTRAINT PK_yaf_Topic
ALTER TABLE yaf_WatchTopic DROP CONSTRAINT FK_yaf_WatchTopic_yaf_User
ALTER TABLE yaf_User DROP CONSTRAINT PK_yaf_yaf_User
ALTER TABLE yaf_WatchTopic DROP CONSTRAINT IX_yaf_WatchTopic
ALTER TABLE yaf_WatchTopic DROP CONSTRAINT PK_yaf_WatchTopic

Then, you have to run the script that's already out there (I don't know who wrote it, but perfect job in my case!

/*
    Set your current database context to the YAF database
    If the source database does not equal phpbb,
        find and replace with the appropriate database name (dbo.[phpbb-db-name].).


    notes: handle bb code? flags? Votes: need a yaf_ChoiceUser table, cookie in YAF
*/


-- ugly, but it converts the IP address to proper format
-- functions found on internet
create function hexchar(
@b varchar(10)
) returns int
as begin
declare @n bigint
set @n = 0
declare @digits char(16)
set @digits = '0123456789ABCDEF'
set @b = substring(@b,3,8)
while len(@b) > 0 begin
set @n = 16*@n + charindex(substring(@b,1,1),@digits)-1
set @b = substring(@b,2,8)
end
return
case when @n >= 0X80000000
then @n - 0x0100000000
else @n end
end
go

create function convertHexIP(@h varchar(9)) returns varchar(15)
as begin
    declare @r varchar(15)
    select @r =cast(dbo.hexchar('0x'+substring(@h,1,2)) as varchar) + '.'+
        cast(dbo.hexchar('0x'+substring(@h,3,2)) as varchar) + '.'+
        cast(dbo.hexchar('0x'+substring(@h,5,2)) as varchar) + '.'+
        cast(dbo.hexchar('0x'+substring(@h,7,2)) as varchar)

    return @r
end
go


----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

--migrate the users

set identity_insert yaf_user on

INSERT INTO [dbo].[yaf_User]
    ([UserID], [BoardID], [Name], [Password],
     [Email], [Joined], [LastVisit], [IP],
     [NumPosts], [Location], [HomePage], [TimeZone],
     [Avatar], [Signature], [AvatarImage], [RankID],
     [Suspended], [LanguageFile], [ThemeFile], [MSN],
     [YIM], [AIM], [ICQ], [RealName],
     [Occupation], [Interests], [Gender], [Weblog], [Flags])
    SELECT user_id, 1, username, user_password,
        user_email, dateadd(ss, user_regdate, 'dec 31 1969 18:00:00'), dateadd(ss, user_lastvisit, 'dec 31 1969 18:00:00'), null,
        user_posts, left(user_from,50), left(user_website,50), user_timezone * 60,
        user_avatar, user_sig, null, 4, -- rank, 4 = member
        null, null, null, left(user_msnm,50),
        left(user_yim,30), left(user_aim,30), null, null,
        user_occ, left(user_interests,100), 1, null, 4 -- 4?
    FROM dbo.phpbb_users
    where user_id > 2 -- change when pushed ?

-- TODO: flag?

set identity_insert yaf_user off
----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------


set identity_insert yaf_category on
INSERT INTO [dbo].[yaf_Category]([CategoryID], [BoardID], [Name], [SortOrder])
    select cat_id, 1, cat_title, cat_order
    from dbo.phpbb_categories
set identity_insert yaf_category off

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

update yaf_topic
set lastMessageID = null

delete from yaf_message
delete from yaf_topic
delete from yaf_forumaccess
delete from yaf_forum


set identity_insert yaf_Forum on

INSERT INTO [dbo].[yaf_Forum]
    ([ForumID], [CategoryID], [ParentID], [Name],
     [Description], [SortOrder], [LastPosted], [LastTopicID],
     [LastMessageID], [LastUserID], [LastUserName], [NumTopics],
     [NumPosts], [RemoteURL], [Flags])
    SELECT forum_id, cat_id, null, left(forum_name,50),
        forum_desc, forum_order, null, null,
        null, null, null, forum_topics,
        forum_posts, null, 4 -- 4?
    FROM dbo.phpbb_forums

--TODO: figure out what flag column (4) is for

set identity_insert yaf_forum off

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

set identity_insert yaf_topic on

INSERT INTO [dbo].[yaf_Topic]
    ([TopicID], [ForumID], [UserID], [UserName],
     [Posted], [Topic], [Views], [Priority],
     [PollID], [TopicMovedID], [LastPosted], [LastMessageID],
     [LastUserID], [LastUserName], [NumPosts], [Flags])
    SELECT topic_id, forum_id, topic_poster, null,
        dateadd(ss, topic_time, 'dec 31 1969 18:00:00'), topic_title, topic_views, 0,
        null, null, null, null,
        null, null, topic_replies, 0
    FROM dbo.phpbb_topics

-- TODO: flags?

set identity_insert yaf_topic off


----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------


set identity_insert yaf_message on

INSERT INTO [dbo].[yaf_Message]
    ([MessageID], [TopicID], [ReplyTo], [Position],
     [Indent], [UserID], [UserName], [Posted],
     [Message], [IP], [Edited], [Flags])
    SELECT a.post_Id, a.topic_id, null, 0,
        0, case when a.poster_Id = -1 then 1 else a.poster_id end, null, dateadd(ss, a.post_time, 'dec 31 1969 18:00:00'),
        b.post_text, dbo.convertHexIP(a.poster_ip), dateadd(ss, a.post_edit_time, 'dec 31 1969 18:00:00'), 22 -- ?
    FROM dbo.phpbb_posts a
        INNER JOIN dbo.phpbb_posts_text b
            ON a.post_Id = b.post_Id

-- TODO: flags?
-- did not migrate subject, no column found

set identity_insert yaf_message off


----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------



INSERT INTO [dbo].[yaf_WatchTopic]([TopicID], [UserID], [Created], [LastMail])
    SELECT topic_id, user_id, getdate(), null
    FROM dbo.phpbb_topics_watch
    where notify_status = 1 --?


-- TODO: confirm notify_status value

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------


set identity_insert yaf_PMessage on

INSERT INTO [dbo].[yaf_PMessage]([PMessageID], [FromUserID], [Created], [Subject], [Body], [Flags])
    SELECT privmsgs_id, privmsgs_from_userid, dateadd(ss, a.privmsgs_date, 'dec 31 1969 18:00:00'), privmsgs_subject, privmsgs_text, 2147483646 --?flag
    FROM dbo.phpbb_privmsgs a
        INNER JOIN dbo.phpbb_privmsgs_text b
            ON a.privmsgs_id = b.privmsgs_text_id
-- TODO: flags?

set identity_insert yaf_PMessage off

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

-- setting all as read, no flag found in phpbb
insert into yaf_userpmessage (userid, pmessageid, isread)
    SELECT privmsgs_to_userid, privmsgs_id, 1
    FROM dbo.phpbb_privmsgs

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------


INSERT INTO [dbo].[yaf_BannedIP]([BoardID], [Mask], [Since])
    SELECT 1, dbo.convertHexIP(ban_ip), getdate()
    FROM dbo.phpbb_banlist

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

-- TODO: Polls and votes!

-- TODO: DNN USERS?


----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------


-- update stats in yaf_topic table
update c
set c.lastposted = a.posted,
    c.lastmessageid = a.messageid,
    c.lastuserid = a.userid
from yaf_topic c, yaf_message a, (select topicid, max(posted) as 'dt' from yaf_message a group by topicid) b
where a.topicid = b.topicid and a.posted = b.dt
  and a.topicid = c.topicid and b.topicid = c.topicid


-- update stats in yaf_forum
update c
set    c.lastposted = a.lastposted,
    c.lastmessageid = a.lastmessageid,
    c.lastuserid = a.lastuserid,
    c.lasttopicid = a.topicid
from yaf_forum c, yaf_topic a, (select forumid, max(lastposted) as 'dt' from yaf_topic a group by forumid) b
where a.forumid = b.forumid and a.lastposted = b.dt
  and a.forumid = c.forumid and b.forumid = c.forumid

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------


------ permissions
-- admins,members
insert into yaf_forumaccess (GroupID, ForumID, AccessMaskID)
    select groupid, forumid, GroupID
    from yaf_group, yaf_forum
    where groupid in (1,3)
-- guest
insert into yaf_forumaccess (GroupID, ForumID, AccessMaskID)
    select groupid, forumid, 4
    from yaf_group, yaf_forum
    where groupid = 2

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------


drop function hexchar
drop function convertHexIp

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------

-- update the position column
DECLARE @messageID int
DECLARE @topicID int
DECLARE @position int

DECLARE CRS CURSOR LOCAL FOR
    select MessageID, TopicID
    from yaf_message

OPEN CRS

FETCH NEXT FROM CRS INTO @messageID, @topicID


WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @position = count(*)+1
    FROM yaf_message
    WHERE TopicID = @topicID
      AND MessageID < @messageID

    UPDATE yaf_message
    SET [position] = @position
    where messageid = @messageID


    FETCH NEXT FROM CRS INTO @messageID, @topicID
END

CLOSE CRS
DEALLOCATE CRS

Then, all you have to do is set up all of the indexes, keys, constraints again, which is in the .sql file included in the YAF install:

[code]

/*

** Drop old Foreign keys

*/

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Active_Forum'

AND parent_obj = Object_id('yaf_Active')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Active

DROP CONSTRAINT FK_Active_Forum

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Active_Topic'

AND parent_obj = Object_id('yaf_Active')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Active

DROP CONSTRAINT FK_Active_Topic

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Active_User'

AND parent_obj = Object_id('yaf_Active')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Active

DROP CONSTRAINT FK_Active_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_CheckEmail_User'

AND parent_obj = Object_id('yaf_CheckEmail')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_CheckEmail

DROP CONSTRAINT FK_CheckEmail_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Choice_Poll'

AND parent_obj = Object_id('yaf_Choice')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Choice

DROP CONSTRAINT FK_Choice_Poll

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Forum_Category'

AND parent_obj = Object_id('yaf_Forum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Forum

DROP CONSTRAINT FK_Forum_Category

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Forum_Message'

AND parent_obj = Object_id('yaf_Forum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Forum

DROP CONSTRAINT FK_Forum_Message

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Forum_Topic'

AND parent_obj = Object_id('yaf_Forum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Forum

DROP CONSTRAINT FK_Forum_Topic

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Forum_User'

AND parent_obj = Object_id('yaf_Forum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Forum

DROP CONSTRAINT FK_Forum_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_ForumAccess_Forum'

AND parent_obj = Object_id('yaf_ForumAccess')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_ForumAccess

DROP CONSTRAINT FK_ForumAccess_Forum

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_ForumAccess_Group'

AND parent_obj = Object_id('yaf_ForumAccess')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_ForumAccess

DROP CONSTRAINT FK_ForumAccess_Group

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Message_Topic'

AND parent_obj = Object_id('yaf_Message')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Message

DROP CONSTRAINT FK_Message_Topic

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Message_User'

AND parent_obj = Object_id('yaf_Message')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Message

DROP CONSTRAINT FK_Message_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_PMessage_User1'

AND parent_obj = Object_id('yaf_PMessage')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_PMessage

DROP CONSTRAINT FK_PMessage_User1

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Topic_Forum'

AND parent_obj = Object_id('yaf_Topic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Topic

DROP CONSTRAINT FK_Topic_Forum

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Topic_Message'

AND parent_obj = Object_id('yaf_Topic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Topic

DROP CONSTRAINT FK_Topic_Message

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Topic_Poll'

AND parent_obj = Object_id('yaf_Topic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Topic

DROP CONSTRAINT FK_Topic_Poll

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Topic_Topic'

AND parent_obj = Object_id('yaf_Topic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Topic

DROP CONSTRAINT FK_Topic_Topic

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Topic_User'

AND parent_obj = Object_id('yaf_Topic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Topic

DROP CONSTRAINT FK_Topic_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Topic_User2'

AND parent_obj = Object_id('yaf_Topic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Topic

DROP CONSTRAINT FK_Topic_User2

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_WatchForum_Forum'

AND parent_obj = Object_id('yaf_WatchForum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_WatchForum

DROP CONSTRAINT FK_WatchForum_Forum

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_WatchForum_User'

AND parent_obj = Object_id('yaf_WatchForum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_WatchForum

DROP CONSTRAINT FK_WatchForum_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_WatchTopic_Topic'

AND parent_obj = Object_id('yaf_WatchTopic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_WatchTopic

DROP CONSTRAINT FK_WatchTopic_Topic

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_WatchTopic_User'

AND parent_obj = Object_id('yaf_WatchTopic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_WatchTopic

DROP CONSTRAINT FK_WatchTopic_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Active_Forum'

AND parent_obj = Object_id('yaf_Active')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Attachment

DROP CONSTRAINT FK_Attachment_Message

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_UserGroup_User'

AND parent_obj = Object_id('yaf_UserGroup')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_UserGroup

DROP CONSTRAINT FK_UserGroup_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_UserGroup_Group'

AND parent_obj = Object_id('yaf_UserGroup')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_UserGroup

DROP CONSTRAINT FK_UserGroup_Group

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Attachment_Message'

AND parent_obj = Object_id('yaf_Attachment')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Attachment

DROP CONSTRAINT FK_Attachment_Message

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_NntpForum_NntpServer'

AND parent_obj = Object_id('yaf_NntpForum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_NntpForum

DROP CONSTRAINT FK_NntpForum_NntpServer

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_NntpForum_Forum'

AND parent_obj = Object_id('yaf_NntpForum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_NntpForum

DROP CONSTRAINT FK_NntpForum_Forum

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_NntpTopic_NntpForum'

AND parent_obj = Object_id('yaf_NntpTopic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_NntpTopic

DROP CONSTRAINT FK_NntpTopic_NntpForum

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_NntpTopic_Topic'

AND parent_obj = Object_id('yaf_NntpTopic')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_NntpTopic

DROP CONSTRAINT FK_NntpTopic_Topic

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_ForumAccess_AccessMask'

AND parent_obj = Object_id('yaf_ForumAccess')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_ForumAccess

DROP CONSTRAINT FK_ForumAccess_AccessMask

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_UserForum_User'

AND parent_obj = Object_id('yaf_UserForum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_UserForum

DROP CONSTRAINT FK_UserForum_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_UserForum_Forum'

AND parent_obj = Object_id('yaf_UserForum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_UserForum

DROP CONSTRAINT FK_UserForum_Forum

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_UserForum_AccessMask'

AND parent_obj = Object_id('yaf_UserForum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_UserForum

DROP CONSTRAINT FK_UserForum_AccessMask

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Category_Board'

AND parent_obj = Object_id('yaf_Category')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Category

DROP CONSTRAINT FK_Category_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_AccessMask_Board'

AND parent_obj = Object_id('yaf_AccessMask')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_AccessMask

DROP CONSTRAINT FK_AccessMask_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Active_Board'

AND parent_obj = Object_id('yaf_Active')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Active

DROP CONSTRAINT FK_Active_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_BannedIP_Board'

AND parent_obj = Object_id('yaf_BannedIP')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_BannedIP

DROP CONSTRAINT FK_BannedIP_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Group_Board'

AND parent_obj = Object_id('yaf_Group')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Group

DROP CONSTRAINT FK_Group_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_NntpServer_Board'

AND parent_obj = Object_id('yaf_NntpServer')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_NntpServer

DROP CONSTRAINT FK_NntpServer_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Rank_Board'

AND parent_obj = Object_id('yaf_Rank')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Rank

DROP CONSTRAINT FK_Rank_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Smiley_Board'

AND parent_obj = Object_id('yaf_Smiley')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Smiley

DROP CONSTRAINT FK_Smiley_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_User_Rank'

AND parent_obj = Object_id('yaf_User')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_User

DROP CONSTRAINT FK_User_Rank

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_User_Board'

AND parent_obj = Object_id('yaf_User')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_User

DROP CONSTRAINT FK_User_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Forum_Forum'

AND parent_obj = Object_id('yaf_Forum')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Forum

DROP CONSTRAINT FK_Forum_Forum

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Message_Message'

AND parent_obj = Object_id('yaf_Message')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Message

DROP CONSTRAINT FK_Message_Message

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_UserPMessage_User'

AND parent_obj = Object_id('yaf_UserPMessage')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_UserPMessage

DROP CONSTRAINT FK_UserPMessage_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_UserPMessage_PMessage'

AND parent_obj = Object_id('yaf_UserPMessage')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_UserPMessage

DROP CONSTRAINT FK_UserPMessage_PMessage

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_Registry_Board'

AND parent_obj = Object_id('yaf_Registry')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_Registry

DROP CONSTRAINT FK_Registry_Board

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_EventLog_User'

AND parent_obj = Object_id('yaf_EventLog')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_EventLog

DROP CONSTRAINT FK_EventLog_User

GO

IF EXISTS (SELECT 1

FROM sysobjects

WHERE name = 'FK_yaf_PollVote_yaf_Poll'

AND parent_obj = Object_id('yaf_PollVote')

AND Objectproperty(id,N'IsForeignKey') = 1)

ALTER TABLE dbo.yaf_PollVote

DROP CONSTRAINT FK_yaf_PollVote_yaf_Poll

GO

/* Drop old primary keys */

IF EXISTS (SELECT 1

FROM sysindexes

WHERE id = Object_id('yaf_BannedIP')

AND name = 'PK_BannedIP')

ALTER TABLE dbo.yaf_BannedIP

DROP CONSTRAINT PK_BannedIP

GO

IF EXISTS (SELECT 1

FROM sysindexes

WHERE id = Object_id('yaf_Category')

AND name = 'PK_Category')

ALTER TABLE dbo.yaf_Category

DROP CONSTRAINT PK_Category

GO

IF EXISTS (SELECT 1

FROM sysindexes

WHERE id = Object_id('yaf_CheckEmail')

AND name = 'PK_CheckEmail')

ALTER TABLE...

Sponsor
guest
  • guest
  • Guest Topic Starter
14 years ago
Ederon found an issue with this. After running all of that, run this:

update yaf_Topic
set
    NumPosts = (select count(1) from yaf_Message b where b.TopicID = yaf_Topic.TopicID and (b.Flags & 😎 = 0)
kacke
  • kacke
  • 53.6% (Neutral)
  • YAF Forumling
14 years ago
Can I run this scripts on my phpBB at a mySQL to YAF at a MS-SQL?
guest
  • guest
  • Guest Topic Starter
14 years ago
Maybe, but not exactly as I had it here. You will have to change all of the source db names to point to a linked server that you set up. If you set up a linked server successfully and give me the alias name you give it, I can maybe give you an example of what you'd need.
agibby5
14 years ago
I'm using this script to migrate from a forum called "ASP-DEv XM". It's old, buggy and I dont like it.

Anyway, I'm having an issue that I'd like to bring up. The way the database is structured in Asp-dev, there's a topic table which also holds the first post. Therefore, I need to import the topics (post information) into the messages table AND import the topic subject/post date, etc into the topic table on the yafnet database. If I import the topics into the message table without bringing over any "messageid" and then import the messages (aka "replies" on the asp-dev forum) into the messages table without importing any "messageid" will I be ok? Or am I missing some nuance here?

Thanks

Ederon
  • Ederon
  • 100% (Exalted)
  • YAF Developer
14 years ago
You just need to make sure MessageID's created as identity values are linked back to yaf_Topic table (in case of starting/first posts of a topic). Also, other replies must get valid TopicID they belong to.
When I post FP:Ederon in a topic, I'm leaving my footprint there so I can track it once I get into coding/supporting.
agibby5
14 years ago
I believe I got it down using this code:

/* Grab the topics and inser them into the topics table... we need to do 
	this because in the devforum the topics had the first message as well
	as the topic information */
SET IDENTITY_INSERT yaf_topic ON
INSERT INTO [dbo].[yaf_Topic]
    ([TopicID], [ForumID], [UserID], [UserName],
     [Posted], [Topic], [Views], [Priority],
     [PollID], [TopicMovedID], [LastPosted], [LastMessageID],
     [LastUserID], [LastUserName], [NumPosts], [Flags])
    SELECT 
		a.topicid, a.tforumid, a.tpostedby, b.muname,
        a.tposteddate, a.tsubject, a.tread, 0,
        null, null, a.tlastpostdate, a.tlastpostid,
        a.tlastpostby, c.muname, (a.treplys + 1), 0
		/* reply count is -1 from what it should be, yaf_topic.numposts also 
			considers the first post in the count however aspxm_topics.treplys doesn't */
    FROM [DevForum].[devforum_user].[ASPXM_topics] as a
	INNER JOIN [DevForum].[devforum_user].[ASPXM_members] as b
			on b.memberid = a.tpostedby
	INNER JOIN [DevForum].[devforum_user].[ASPXM_members] as c
			on c.memberid = a.tlastpostby
SET IDENTITY_INSERT yaf_topic OFF

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
/* Grab the first post from the topics table on devforum, 
	insert as message on new forum */
SET IDENTITY_INSERT yaf_message OFF
INSERT INTO [dbo].[yaf_Message]
    ( [TopicID], [ReplyTo], [Position], --[MessageID], [TopicID], [ReplyTo], [Position],
     [Indent], [UserID], [UserName], [Posted],
     [Message], [IP], [Edited], [Flags])
SELECT 
		a.topicid, null, 0,	--messageid, a.topicid, null, 0,
        0, a.tpostedby, b.muname, a.tposteddate,
        a.tmessage, a.tip, a.tlastpostdate, 22
    FROM [DevForum].[devforum_user].[ASPXM_topics] as a
	INNER JOIN [DevForum].[devforum_user].[ASPXM_members] as b
		on b.memberid = a.tpostedby
SET IDENTITY_INSERT yaf_message OFF

----------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------
--Grab the rest of the messages (replies) from the devforum, 
--insert them as messages on the new forum.
SET IDENTITY_INSERT yaf_message OFF
INSERT INTO [dbo].[yaf_Message]
    ( [TopicID], [ReplyTo], [Position],--[MessageID], [TopicID], [ReplyTo], [Position],
     [Indent], [UserID], [UserName], [Posted],
     [Message], [IP], [Edited], [Flags])
    SELECT 
		a.rtopicid, null, 0, --a.[replyID], a.rtopicid, null, 0,
        0, a.rpostby, b.muname, a.rdate,
		a.rmessage, a.rip, a.reditdate, 22
	FROM devforum.devforum_user.[ASPXM_replys] as a
		INNER JOIN [DevForum].[devforum_user].[ASPXM_members] as b
		on b.memberid = a.rpostby
SET IDENTITY_INSERT yaf_message OFF

However, I didn't try to re-enable the constraints yet. I didn't quite understand what you meant by:

Quote:

make sure MessageID's created as identity values

One other question, the old forum stored quotes and ampersand's as " &. How would you recommend going through each message and replacing them with the proper values? Should I directly replace them with " and &, respectively?

Thanks so much for your help 🙂

fenrir1
  • fenrir1
  • 53% (Neutral)
  • YAF Forumling
14 years ago
@suprphrk: Did i understand correctly, that your old phpBB database was just a saved database-file? So you uploaded something like phpBB.db to your IIS and ran the scripts there?

The point is: I am planning to move an existing phpBB-board to a new install of YAF and i don't want to have to copy the posts manually.

I don't care for themes, extension and so on. I don't even have a problem with manually adding the users again. I don't even need the PMs any more. If i can just copy the existing posts, i am a happy person. More so if the members and their passwords can be transfered as well, but that is strictly optional.

What i have is a single file backup from the original database.

korben
  • korben
  • 51.8% (Neutral)
  • YAF Forumling
13 years ago
hi !

Thank's for this script,

but I don't anderstand where you found "dbo.phpBB_..." ?

Where did you find the phpBB database for ms sql ?

is there a tool or soft for this ?