Postnuke to Drupal Conversions: phpbb2.0 forums

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', '
'.'\\1'." ".t("wrote:").'
', $text);
$text = str_replace('[quote]', '
'.t("Quote:").'
', $text); $text = str_replace('[/quote]', '
', $text);
$text = preg_replace('#\[quote:(.*?)=(?:"|"|\')?(.*?)["\']?(?:"|"|\')?\]#si', '
'.'\\2'." ".t("wrote:").'
', $text);
$text = str_replace('[quote]', '
'.t("Quote:").'
', $text); $text = preg_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' => '
'.$quote_text.':
\\1
',
'#\[quote:(.*?)=(?:"|"|\')?(.*?)["\']?(?:"|"|\')?\](?:[\r\n])*(.*?)\[/quote(?::\w+)?\]#si' => '
'.$quote_user.':
\\2
',