Convert/Import/Migrate bbPress to Drupal

Here's a query that I have written to convert bbPress to Drupal.

I have tested it a little and it seems to work ok. You have to enable the forum module first and import your bbPress tables into your drupal database with the prefix 'bb_'

#
# convert bbPress to Drupal. Written by Rimian Perkins: rimian.com.au
# 

--
--  setup some inital values
--
SET CHARACTER SET utf8;
SET @forumvocabularyname = 'Forums';
SET @forummodule = 'forum';
SET @teaserlength = 400;
SET @tagmodule = 'taxonomy';
SET @tagvocabularyname = 'Tags';

--
--  import users
--
INSERT INTO users (uid, name, pass, mail, created, status, init )
	SELECT ID, user_login, user_pass, user_email, UNIX_TIMESTAMP(user_registered), user_status, user_email
	FROM bb_users WHERE ID > 1 ORDER BY ID;
--
-- update last user access
--
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (uid INT UNSIGNED, access INT UNSIGNED) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO temp (uid, access) SELECT poster_id, UNIX_TIMESTAMP(post_time) FROM bb_posts GROUP BY post_time ORDER BY post_id DESC;
UPDATE users u, temp t SET u.access = t.access WHERE t.uid = u.uid;
DROP TABLE temp;

--
-- create forum vocabularies
--
INSERT INTO vocabulary (name, hierarchy, required, module) VALUES(@forumvocabularyname, 1, 1, @forummodule);

--
-- insert terms
--
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (vid INT UNSIGNED, name VARCHAR(150), description TEXT, weight TINYINT) ENGINE=MyISAM DEFAULT CHARSET=utf8;
INSERT INTO temp (name, description, weight) SELECT forum_name, forum_desc, forum_order FROM bb_forums;
UPDATE temp, vocabulary v SET temp.vid = v.vid WHERE v.name = @forumvocabularyname;
INSERT INTO term_data (vid, name, description, weight) SELECT vid, name, description, weight FROM temp;
INSERT INTO term_hierarchy (tid, parent) SELECT tid, 0 FROM term_data td INNER JOIN vocabulary v ON td.vid = v.vid WHERE v.name = @forumvocabularyname;
ALTER TABLE bb_forums ADD tid INT UNSIGNED NOT NULL;
UPDATE bb_forums bb, term_data td SET bb.tid = td.tid WHERE bb.forum_name = td.name;
DROP TABLE temp;

--
-- associate with forum content type
--
INSERT INTO vocabulary_node_types (vid, type) SELECT vid, @forummodule FROM vocabulary WHERE name = @forumvocabularyname LIMIT 1;

--
-- import forums top level
--
DROP TABLE IF EXISTS temp;
CREATE TABLE temp (nid INT UNSIGNED, uid INT UNSIGNED, title VARCHAR(128), body TEXT, created INT UNSIGNED, hostname VARCHAR(128), status TINYINT, position VARCHAR(10) ) ENGINE=MyISAM DEFAULT CHARSET=utf8;
## update bb press tables (topics and posts) with last node id
ALTER TABLE bb_topics ADD nid INT UNSIGNED NOT NULL AFTER topic_id;
ALTER TABLE bb_posts ADD nid INT UNSIGNED NOT NULL AFTER post_id;
INSERT into temp (nid) SELECT nid FROM node ORDER BY nid DESC LIMIT 1;
UPDATE bb_topics t, bb_posts p, temp SET t.nid = temp.nid, p.nid = temp.nid;
UPDATE bb_topics t, bb_posts p SET t.nid = t.nid + t.topic_id, p.nid = p.nid + p.topic_id;
DELETE FROM temp;

INSERT INTO temp (nid, uid, title, body, created, hostname, status)
  SELECT t.nid, p.poster_id, t.topic_title, p.post_text, UNIX_TIMESTAMP(p.post_time), p.poster_ip, p.post_status = 0
  FROM bb_posts p
  INNER JOIN bb_topics t ON t.topic_id = p.topic_id
  WHERE p.post_position = 1;
INSERT INTO node (nid, vid, type, title, uid, created, comment, status)
  SELECT nid, nid, 'forum', title, uid, created, 2, status FROM temp;

INSERT INTO node_revisions (nid, vid, uid, title, body, teaser, log, timestamp, format)
  SELECT nid, nid, uid, title, body, LEFT(body, 400), 'Initial import from bbPress', created, 3 FROM temp;

DELETE FROM temp;
INSERT INTO temp (nid, uid, body, created, hostname, status, position )
  SELECT nid, poster_id, post_text, UNIX_TIMESTAMP(post_time), poster_ip, post_status, RIGHT(CONCAT('0', post_position, '/'), 3)
  FROM bb_posts WHERE post_position > 1;

--
-- insert comments (posts)
--
INSERT INTO comments (nid, uid, subject, comment, timestamp, hostname, status, format, thread, name, mail)
  SELECT t.nid, t.uid, '', t.body, t.created, t.hostname, t.status, 3, t.position, u.name, u.mail
  FROM temp t
  INNER JOIN users u ON t.uid = u.uid;

--
-- meta stuff
--
INSERT INTO term_node (nid, tid) SELECT t.nid, f.tid FROM bb_topics t INNER JOIN bb_forums f ON t.forum_id = f.forum_id;
INSERT INTO forum (nid, vid, tid) SELECT t.nid, t.nid, f.tid FROM bb_topics t INNER JOIN bb_forums f ON t.forum_id = f.forum_id;
INSERT INTO history (uid, nid, timestamp) SELECT n.uid, n.nid, r.timestamp FROM node n INNER JOIN node_revisions r ON n.nid = r.nid WHERE n.type = @forummodule;
INSERT INTO node_comment_statistics (nid, last_comment_timestamp, last_comment_uid) SELECT nid, timestamp, uid FROM node_revisions;# WHERE nid NOT IN (SELECT nid FROM comments);
DELETE FROM node_comment_statistics WHERE nid IN (SELECT nid FROM comments);
INSERT INTO node_comment_statistics (nid, comment_count, last_comment_timestamp, last_comment_uid) SELECT nid, COUNT(nid), MAX(timestamp), uid FROM comments GROUP BY nid;

--
-- insert tags
--
INSERT INTO vocabulary (name, tags, module) VALUES(@tagvocabularyname, 1, @tagmodule);
INSERT INTO vocabulary_node_types (vid, type) SELECT vid, @forummodule FROM vocabulary WHERE name = @tagvocabularyname LIMIT 1;
ALTER TABLE bb_tagged DROP PRIMARY KEY;
ALTER TABLE bb_tags CHANGE tag_id tag_id INT UNSIGNED NOT NULL;
ALTER TABLE bb_tags DROP PRIMARY KEY;
UPDATE bb_tags SET tag_id = tag_id + (SELECT MAX(tid) FROM term_data);
UPDATE bb_tagged SET tag_id = tag_id + (SELECT MAX(tid) FROM term_data);
UPDATE bb_tagged, bb_topics SET bb_tagged.topic_id = bb_topics.nid WHERE bb_tagged.topic_id = bb_topics.topic_id;
INSERT INTO term_data (tid, name) SELECT tag_id, raw_tag FROM bb_tags ORDER BY tag_id;
INSERT INTO term_node (tid, nid) SELECT tag_id, topic_id FROM bb_tagged ORDER BY tag_id;
UPDATE term_data SET vid = (SELECT vid FROM vocabulary WHERE name = @tagvocabularyname) WHERE vid = 0;
INSERT INTO term_hierarchy (tid) SELECT tag_id FROM bb_tags;

--
-- update sequences
--
DELETE FROM sequences WHERE name = 'vocabulary_vid';
INSERT INTO sequences (name, id) (SELECT 'vocabulary_vid', MAX(vid) FROM vocabulary);
DELETE FROM sequences WHERE name = 'users_uid';
INSERT INTO sequences (name, id) (SELECT 'users_uid', MAX(uid) FROM users);
DELETE FROM sequences WHERE name = 'term_data_tid';
INSERT INTO sequences (name, id) (SELECT 'term_data_tid', MAX(tid) FROM term_data);
DELETE FROM sequences WHERE name = 'node_revisions_vid';
INSERT INTO sequences (name, id) (SELECT 'node_revisions_vid', MAX(vid) FROM node_revisions);
DELETE FROM sequences WHERE name = 'node_nid';
INSERT INTO sequences (name, id) (SELECT 'node_nid', MAX(nid) FROM node);
DELETE FROM sequences WHERE name = 'comments_cid';
INSERT INTO sequences (name, id) (SELECT 'comments_cid', MAX(cid) FROM comments);

DROP TABLE IF EXISTS temp;

• Slashdot it!
• Digg it:
  
• Del.icio.us