[@scooter7978 Member 16 years, 8 months ago]

I’ve refined it a bit further so that it grabs the last post ID of each of the topics so that the ‘freshness’ links work fine for old posts…

I replaced the SQL to get the topics:

(SELECT 0) as LastPostID

to:

(SELECT MAX(CommentID) FROM LUM_Comment WHERE DiscussionID=LUM_Discussion.DiscussionID) AS LastPostID

I can’t edit my original post so if you use the above SQL, make sure you add this bit in.

[@scooter7978 Member 16 years, 8 months ago]

bb_current_time is in the functions file inside bb-includes directory. But you probably should touch that otherwise when you update bbpress, it will break again.

The SQL I used was just an interval clause…i.e. if I was going to import my current forums I would use something like:

SELECT ID, Body, DateCreated – interval ‘9 30’ hour_minute FROM current_forum_whatever

This takes the date field and drops 9.5 hours off, coincidentally, my time zone.

You could probably adapt this to an ‘update’ statement to change what’s already in your database, eg:

UPDATE bb_topics SET DateCreated=(SELECT DateCreated – interval ‘9 30’ hour_minute);

I haven’t tested it but it probably wouldn’t be too far wrong.

[@scooter7978 Member 16 years, 8 months ago]

On thinking about it a bit further, I went back to my SQL posts and kludged a statement which removes 9.5 hours from the various date fields that populate the bbpress database.

On restoring everything back, it’s ok now.

[@scooter7978 Member 16 years, 8 months ago]

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