Postnuke to Drupal Conversions: phpbb2.0 forums
Book page
Written with a loving hand by kitt some time around 11:48 on 30 December 2004
Converting forums for phpbb2.0 to Drupal 4.5 forums. This is in a comment from http://drupal.org/node/12311.
# Forums # Add the phpbb forum topics #Replace XXX with the vid of the vocabularly you want to create, e.g., on a fresh drupal install, you can use "1" - otherwise, check your sequences table for the next available number INSERT INTO vocabulary VALUES (XXX, "Forum", "Topics for forums", "", 0, 1, 0, 1, "forum", -10); # add the forum head topics by forum categories #Replace YYY with the next available term data TID from your sequences table. #For a fresh install, you can just delete "+ YYY". Replace XXX with the number you used above. INSERT INTO term_data (tid, vid, name, description, weight) SELECT cat_id + YYY, XXX, cat_title, cat_title, 0 FROM phpbb_categories; #YYY same number as above or delete INSERT INTO term_hierarchy (tid, parent) SELECT cat_id + YYY, 0 FROM phpbb_categories; # add the forum specific topics. #Check your term_data table and find the highest TID number #and replace ZZZ with a higher number. #Use same XXX as above. INSERT INTO term_data (tid, vid, name, description, weight) SELECT forum_id + ZZZ, XXX, forum_name, forum_desc, 0 FROM phpbb_forums; INSERT INTO term_hierarchy (tid, parent) SELECT forum_id + ZZZ, cat_id + YYY FROM phpbb_forums; # # Create temporary tables for sorting topics and comments. # DROP TABLE IF EXISTS temp_posts; CREATE TABLE temp_posts ( post_id mediumint(8) UNSIGNED NOT NULL auto_increment, topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, forum_id smallint(5) UNSIGNED DEFAULT '0' NOT NULL, poster_id mediumint(8) DEFAULT '0' NOT NULL, post_time int(11) DEFAULT '0' NOT NULL, post_edit_time int(11), post_subject char(120), post_text text, PRIMARY KEY (post_id), KEY forum_id (forum_id), KEY topic_id (topic_id), KEY poster_id (poster_id), KEY post_time (post_time) ); DROP TABLE IF EXISTS temp_node; CREATE TABLE temp_node ( post_id mediumint(8) UNSIGNED NOT NULL auto_increment, topic_id mediumint(8) UNSIGNED DEFAULT '0' NOT NULL, PRIMARY KEY (post_id), KEY topic_id (topic_id) ); # # Copy into temporary table topics without comments # INSERT INTO temp_node (post_id,topic_id) SELECT MIN(post_id), topic_id FROM phpbb_posts GROUP BY topic_id; INSERT INTO temp_posts (post_id, topic_id,forum_id,poster_id, post_time,post_edit_time,post_subject,post_text) SELECT c.post_id, c.topic_id, a.forum_id, IF(a.poster_id='-1','0',a.poster_id), a.post_time, a.post_edit_time, REPLACE(b.post_subject, CONCAT(':',b.bbcode_uid),''), REPLACE(b.post_text, CONCAT(':',b.bbcode_uid),'') FROM phpbb_posts AS a, phpbb_posts_text AS b, temp_node AS c WHERE c.post_id=a.post_id AND c.post_id=b.post_id; # # Insert nid and tid from temp_posts into term_node # #check your node table and find the highest NID #and replace WWW with a higher number. #USe same ZZZ as above INSERT INTO term_node (nid,tid) SELECT WWW+topic_id,ZZZ+forum_id FROM temp_posts; ALTER TABLE term_node ORDER BY nid; # # Insert forum topics from temp_posts into node #USe same WWW as above INSERT INTO node (nid,type,title,uid,created,comment,body,changed) SELECT WWW+topic_id,'forum',post_subject,poster_id,post_time,'2',post_text,IF(post_edit_time<>'NULL',post_edit_time,post_time) FROM temp_posts; ALTER TABLE node ORDER BY nid; # # Insert nid into forum #Use same WWW and ZZZ as above DELETE FROM forum; INSERT INTO forum (nid,tid) SELECT WWW+topic_id,ZZZ+forum_id FROM temp_posts; # # Insert comments into comments for topics from temp_posts #Use same WWW as above INSERT INTO comments (nid,uid,subject,comment,hostname,timestamp,users) SELECT WWW+a.topic_id, CASE WHEN a.poster_id='-1' THEN '0' ELSE a.poster_id END, REPLACE(c.post_subject, CONCAT(':',c.bbcode_uid),''), REPLACE(c.post_text, CONCAT(':',c.bbcode_uid),''), CONCAT_WS('.',CONV(SUBSTRING(a.poster_ip,1,2),16,10),CONV(SUBSTRING(a.poster_ip,3,2),16,10),CONV(SUBSTRING(a.poster_ip,5,2),16,10),CONV(SUBSTRING(a.poster_ip,7,2),16,10)), a.post_time,'a:1:{i:0;i:0;}' FROM phpbb_posts AS a LEFT JOIN temp_posts AS b ON a.post_id=b.post_id,phpbb_posts_text AS c WHERE b.post_id IS NULL AND a.post_id=c.post_id; ALTER TABLE comments ORDER BY cid; UPDATE comments,node SET comments.subject=IF(comments.subject='',CONCAT('Re:',node.title),comments.subject) WHERE comments.nid=node.nid; DROP TABLE IF EXISTS temp_posts; DROP TABLE IF EXISTS temp_node; #replace UUU with number higher than your highest current UID, #or delete +UUU if this is fresh install INSERT INTO users (uid+UUU,name,pass,mail,signature,timestamp,status,init,rid) SELECT user_id,username,user_password,user_email,user_sig,IF(user_session_time='0',user_regdate,user_session_time),'1',user_email,'2' FROM phpbb_users; WHERE user_id>1 #replace WWW INSERT INTO node_comment_statistics( nid, cid, last_comment_timestamp, last_comment_name, last_comment_uid, comment_count ) SELECT t.topic_id + WWW, 0, t.topic_time, p.username, t.topic_poster, t.topic_replies FROM phpbb_topics t, users p WHERE t.topic_poster = p.pn_uid; #replace WWW UPDATE node_comment_statistics n, phpbb_topics z SET n.last_comment_timestamp = z.topic_last_post_id WHERE n.nid = z.topic_id + WWW AND z.topic_last_post_id != 0; UPDATE node_comment_statistics n, users z, phpbb_posts p SET n.last_comment_name = z.username, n.last_comment_uid = z.uid WHERE p.post_id = n.last_comment_timestamp and p.poster_id = z.uid; UPDATE node_comment_statistics n, phpbb_posts p SET n.last_comment_timestamp = p.post_time WHERE p.post_id = n.last_comment_timestamp AND n.last_comment_timestamp != 0 ; # # Update Drupal variables # This may not work and you ahve to update sequnces manually SELECT @term_data_tid:=MAX(tid) FROM term_data; SELECT @comments_cid:=MAX(cid) FROM comments; SELECT @node_nid:=MAX(nid) FROM node WHERE type = 'forum'; SELECT @users_uid:=MAX(uid) FROM users; UPDATE sequences SET id=@term_data_tid WHERE name='term_data_tid'; UPDATE sequences SET id=@comments_cid WHERE name = 'comments_cid'; UPDATE sequences SET id=@node_nid WHERE name = 'node_nid'; UPDATE sequences SET id=@users_uid WHERE name = 'users_uid'; #Now you have to install the Drupal BB code module AND the Drupal Quote Module, and you have to hack them: #In the quote module replace function _quote_filter_process($text) with this: function _quote_filter_process($text) { // Quoting with or without specifying the source (code borrowed from bbcode.module) // Thanks: function based on code from punbb.org if (strpos($text, '[quote') !== false) { $text = preg_replace('#\[quote=(?:"|"|\')?(.*?)["\']?(?:"|"|\')?\]#si', '', $text); $text = str_replace('[quote]', '', $text); $text = preg_replace('#\[quote:(.*?)=(?:"|"|\')?(.*?)["\']?(?:"|"|\')?\]#si', ' ', $text); $text = str_replace('[/quote]', '', $text); $text = str_replace('[quote]', '', $text); } return $text; } #In the BB code module, file bb-code-filter.inc, comment out the following lines: Quoting with or without specifying the source '#\[quote(?::\w+)?\](?:[\r\n])*(.*?)\[/quote(?::\w+)?\]#si' => ' ', $text); $text = preg_replace('#\[/quote:(.*?)\]#', ''.$quote_text.':', '#\[quote:(.*?)=(?:"|"|\')?(.*?)["\']?(?:"|"|\')?\](?:[\r\n])*(.*?)\[/quote(?::\w+)?\]#si' => '\\1'.$quote_user.':',\\2