guest
2004-04-10T08:05:44Z

Someone (think it was money-penny) mentioned something about conversion scripts a while back.  Is anyone working on one to convert from phpBB to YAF?

Sponsor
guest
2004-04-10T15:16:07Z
Not me but I think it is a great idea! 🙂
guest
2005-09-05T12:59:03Z
I wrote an initial conversion script for phpBB. The implementation in phpBB on BBCode is rather irritating, so that will still take some additional work to eliminate the extra tag it places inside each BBCode tag. I am not 100% certain about some of the flags either. But this is a start...

This is for phpBB 2.0.x


/*
	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 phpbb.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 phpbb.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 phpbb.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 phpbb.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'),
		replace(b.post_text, '\"', '"'), dbo.convertHexIP(a.poster_ip), dateadd(ss, a.post_edit_time, 'dec 31 1969 18:00:00'), 22 -- ?
	FROM phpbb.dbo.phpbb_posts a
		INNER JOIN phpbb.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 phpbb.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 phpbb.dbo.phpbb_privmsgs a
		INNER JOIN phpbb.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 phpbb.dbo.phpbb_privmsgs 

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


INSERT INTO [pointbeta].[dbo].[yaf_BannedIP]([BoardID], [Mask], [Since])
	SELECT 1, dbo.convertHexIP(ban_ip), getdate()
	FROM phpbb.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
Sirusdv
2005-11-27T13:23:37Z
Anyone try this with RC3 yet?
guest
2006-01-03T16:44:49Z
I haven't tried it with RC3 yet. However I don't think it would work with my PhpBB board as there are database inconsistencies where I have hand deleted rows from MySql. So I have written a .NET app to do the conversion, which is slightly more robust than the SQL script.

I've tested it on my board which has over 4700 users, and over 76000 posts.

It's features are:

* Copies users.

* Copies the categories, forums, topics and posts.

* Doesn't need to copy the database ID's from the PhpBB forum, as it calculates all the new ID's on the fly.

It doesn't do:

* Private messages. I just told my users that their inbox would be wiped.

* Watch topics. Don't consider it a big issue for old topics.

* Banned users. I don't have many so I didn't bother with this.

* Polls.

* Permissions. All forums are set to Member access.

To download the executable only, check here 

Unpack the zip file, and put it in a directory on it's own. Then open up the config file in Notepad or similar and fill in the connection strings for each DB. If your PhpBB board is in another DB like MySql, then use the ODBC driver for that database to export a DSN, and then use SQL Server data-transformation service to import it.

If you're interested in extending the application, you can download the source from here . It should be fairly self explanatory. I will try and give help if anyone asks for it, but I can't promise anything I'm afraid.

UserPostedImage

Paul
guest
2006-02-09T20:52:03Z
Hello,
I've downloaded your tool and have migrated my existing MySql phpBB tables into a local MS SQL db.
My empty YAF bb is online (remote).
To the best of my knowledge, the application is connecting properly to the online forum.

Beyone this point, I am pulling my hair out!!

No matter what I put in the config file for a local connection string, the application always says "Failed to connect to your PhpBB database.".
I have used the exact same connection string as my custom asp.net framework uses. I have created a new DSN for the testForum tables..., I've tried every imaginable conflagurated-mal-distorted replication of ALL example connection string options (as explained at http://www.connectionstring.com ). I've changed and swapped every variable I can think of AND I know I must be doing something painfully, simplistically wrong...:|

Do you have an example of a working connection string for the phpBB connection, connecting to localhost?

Ok, now I've even gone as far as exporting the entire phpBB forum, as MS SQL tables back online, in the exact same db as the YAF forum. Of course, now I should be able to use the exact same connection string, but no, it still fails. Now I know as a fact that this connection string is good because I have other apps that can look at these newly added tables....:shock:

Any help is appreciated.

Preston
guest
2006-02-10T09:12:24Z
Preston wrote:

Hello,
I've downloaded your tool and have migrated my existing MySql phpBB tables into a local MS SQL db.
My empty YAF bb is online (remote).
To the best of my knowledge, the application is connecting properly to the online forum.

Beyone this point, I am pulling my hair out!!

No matter what I put in the config file for a local connection string, the application always says "Failed to connect to your PhpBB database.".
I have used the exact same connection string as my custom asp.net framework uses. I have created a new DSN for the testForum tables..., I've tried every imaginable conflagurated-mal-distorted replication of ALL example connection string options (as explained at http://www.connectionstring.com ). I've changed and swapped every variable I can think of AND I know I must be doing something painfully, simplistically wrong...:|

Do you have an example of a working connection string for the phpBB connection, connecting to localhost?

Ok, now I've even gone as far as exporting the entire phpBB forum, as MS SQL tables back online, in the exact same db as the YAF forum. Of course, now I should be able to use the exact same connection string, but no, it still fails. Now I know as a fact that this connection string is good because I have other apps that can look at these newly added tables....:shock:

Any help is appreciated.

Preston



Not sure what the problem is, but there's a small chance that it may not be a connection issue. I have uploaded a newer version, which spits out a file called Exception.txt if you get the connection error-message. You may be able to get more info from it. If you don't understand it, then please post it here and I'll take a look.

Paul
guest
2006-02-11T09:57:27Z
Super, much appreciated.

I'm all off in different directions but I think I'll have a chance to work on my forum again this weekend.

Where I ended on my last exercise was I simply took your sql and ran it through query analyzer. (Both my phpBB db and YAF db tables are all in one db right now.)

After that, I started getting odd errors with YAF (I'm not surprised with everything that I've done/attempted on the db), so I'm half way through an uninstall.
I will reinstall and start with a clean slate.

I'll try your updated tool on my local install and see what I can get going. I'll report back with my results.
Oh, is the updated version available from the same place?

Thanks
Preston
guest
2006-02-14T21:37:45Z
Hello again,
Ok, here's what I've got. I've run your updated app and here's the error log output...

Source: PhpBBToYaf
Message: Failed to connect to your PhpBB database. Check the connection string in the config file
Stack Trace: at PhpBBToYaf.Logic.Helper.TestConnections()
at PhpBBToYaf.MainForm.Main()

Source: .Net SqlClient Data Provider
Message: Invalid column name 'user_newsletter'.
Stack Trace: at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.SqlClient.SqlCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
at SD.LLBLGen.Pro.ORMSupportClasses.DaoBase


Even though this starts with "Failed to connect to PhpBB database", this appears to be looking for an "Invalid column name" as well. I don't know if the invalid column name is a result of returning nothing from the db... chicken/egg?

Approaching this from the other direction, I've run your sql (as provided above).
This seems to have done 90% of what I need.
It leaves a small trail for clean-up (IE:quotes seem to be handled differently in phpBB, bold, user groups...) but I'm definately not complaining.

I'm good with doing it this way.
I'm not sure why I can't get connected with the app.

Thanks for the help
Preston
guest
2006-02-15T05:32:39Z
Ah yes, I see the problem. It's due to the fact that I have extra columns in my phpBB users table.

To make it work, you could just add some dummy columns:


column name	data type		Length		Allow Nulls

user_allowmix	smallint		2		Yes
user_mix		varchar		100		Yes
user_newsletter	smallint		2		No


The connection is fine, it's just a misnomer with the exceptionn reporting.
guest
2006-05-09T09:42:12Z
louthy wrote:

Ah yes, I see the problem. It's due to the fact that I have extra columns in my phpBB users table.

To make it work, you could just add some dummy columns:


column name	data type		Length		Allow Nulls

user_allowmix	smallint		2		Yes
user_mix		varchar		100		Yes
user_newsletter	smallint		2		No


The connection is fine, it's just a misnomer with the exceptionn reporting.



I downloaded this, and am very anxious to try it, but I have a couple of questions:

1) How easy would it be to simply convert directly from the mySQL phpBB database? That saves a big step for most users I would think.
2) Failing at that, do you know of a bug-free tool to do this migration from mySQL to a SQLServer copy of the phpBB database.
3) Is the current download copy fixed, or does it still reference those extra columns?

Any help is greatly appreciated.
guest
2006-08-16T10:51:15Z
There is a YAF to PHPBB convertor ??
guest
2006-10-28T05:48:21Z
jiffy wrote:

I wrote an initial conversion script for phpBB. The implementation in phpBB on BBCode is rather irritating, so that will still take some additional work to eliminate the extra tag it places inside each BBCode tag. I am not 100% certain about some of the flags either. But this is a start...

This is for phpBB 2.0.x



That works pretty well, I had to put where clauses on the end to tidy the database as well and used remote openrowset queries to do it directly against a mysql server. (IE - where topic_id in (select topicid from yaf_topic) and poster_id in (select userid from yaf_user)) but that worked really well. I also had to remove the REPLACE() function you used in the query to bring over the text from the posts, unfortunately it was too much data so got classed as text and replace won't work with text.

I did find I needed to do some minor extra steps (probably because of the version I was using):

--Add the users into the members group (except those users already defined)
insert into yaf_usergroup
select userid, 3
from yaf_user
where userid not in (select userid from yaf_usergroup)

--Update users to be valid members, and remove their avatars (as these were remote linking and hitting an error in the resource handler)
update yaf_user
set rankid=3, flags=2, avatar=null
where userid>2

I did try the .NET application, but unfortunately it was hard coded it to use SqlConnection rather than using IDbConnection - so I settled for the database scripts as I could use the mysql ODBC driver, plus a DSN (use ODBC manager to create this) and then use:

FROM OPENROWSET('MSDASQL', 'DSN=phpbbdsn', 'SELECT * FROM phpbb_users')

Instead of things like:

FROM phpbb.dbo.phpbb_users

Hope this helps someone in the future!
guest
2006-10-28T05:50:15Z
JRC wrote:

There is a YAF to PHPBB convertor ??



No, you could make one but it would be really annoying as PHPbb's database is a mess of bad naming conventions and extra tables compared to YAF's. You will also loose out on a significant layer of security if you switch...
guest
2007-01-30T10:51:40Z
Will this conversion app work with the DotNetNuke Module?

About Us

The YAF.NET is an open source .NET forum project. YAF.NET is supported by an team of international developers who are build community by building community software.

Powered by Resharper Donate with PayPal button

Project Twitter Updates

Copyright © YetAnotherForum.NET & Ingo Herbote. All rights reserved