[scooter7978]

I’m using Vanilla on my forums and since I like to change things every now and then, I’m going to move to BBPress in the near future. Since I’m testing at the moment, I thought I’d hand over the SQL statements for grabbing the categories, topics and posts and formatting them as best as possible for bbpress.

SQL to get the categories:

SELECT CategoryID, Name, REPLACE( LCASE( Name ) , ‘ ‘, ‘-‘ ) AS Slug, Description, Priority

FROM LUM_Category

SQL to get the topics:

SELECT DiscussionID, Name, REPLACE( Name, ‘ ‘, ‘-‘ ) AS Slug, AuthUserID,

(SELECT LUM_User.Name FROM LUM_User WHERE UserID = AuthUserID) AS PosterName,

LastUserID, (SELECT LUM_User.Name FROM LUM_User WHERE UserID = LastUserID) AS LastPosterName,

DateCreated, DateLastActive, CategoryID, Closed, Active, (SELECT 0) AS LastPostID, Sticky, CountComments, (SELECT 0) AS TagCount

FROM LUM_Discussion

SQL to get the discussions:

SELECT CommentID, LUM_Discussion.CategoryID AS ForumID, LUM_Comment.DiscussionID, LUM_Comment.AuthUserID, Body, LUM_Comment.DateCreated, RemoteIp

FROM LUM_Comment

JOIN LUM_Category ON LUM_Discussion.CategoryID = LUM_Category.CategoryID

JOIN LUM_Discussion ON LUM_Discussion.DiscussionID = LUM_Comment.DiscussionID

ORDER BY CommentID, ForumID

SQL to get the tags:

SELECT TagID, REPLACE( Tag, ‘ ‘, ‘-‘ ) , Tag, (SELECT 0) FROM LUM_DiscussionTags

From there you will need to fix up the slugs in the bb_topics table and in the bb_forums so that the web browser doesn’t have a conniption when it tries to load a forum thread but you can do that with a series of select(replace) queries like this:

update bb_topics set topic_slug = replace(topic_slug,’?’,”);

update bb_topics set topic_slug = replace(topic_slug,’.’,”);

update bb_topics set topic_slug = replace(topic_slug,’/’,”);

update bb_topics set topic_slug = replace(topic_slug,’&’,’and’);

update bb_topics set topic_slug = replace(topic_slug,’"’,”);

update bb_topics set topic_slug = replace(topic_slug,’\’,”);

update bb_topics set topic_slug = replace(topic_slug,'”‘,”);

update bb_topics set topic_slug = replace(topic_slug,’!’,”);

update bb_topics set topic_slug = replace(topic_slug,’,’,”);

update bb_topics set topic_slug = replace(topic_slug,”’,”);

update bb_topics set topic_slug = replace(topic_slug,’#’,”);

There might be a better or quicker way of doing the last bit but since it works fine on mine I am not too concerned.

I had to kludge some results in the queries like; SELECT 0 or SELECT 1 to get the answers I wanted or to pad out the columns to fit properly into the bbpress SQL layout. In most cases its just count fields or an active or open flag which is nearly always 1 or 0 anyway.

Generally this is all you need to get started, from there you can just export this table data to a CSV or something and then import it into the requisite bbpress table. If you go into the admin interface of bbpress and get the system to recount everything it should be ok from there. I only recommend doing this if you’re familiar with the inner workings of MySQL, otherwise you could bollocks things up and then noone wins.

Please remember too that this is the basics, if you’ve integrated with wordpress you’ve still got some work ahead of you to coordinate your wordpress ID’s with your forum ones. It can be done as I managed to do it, it’s just painful.

Cheers,

Scott