################################################################################## ## This MySQL-script migrates users and forums from a drupal 5.x installation to a phpbb-Forum # # Original script by Robert Fischer, tin:b Software, (robert@Robert Fischer, tin:b Software.de) # Modified by Ger Bruinsma, (http://www.bruinsma-webbased.nl) # # DISCLAIMER: This script worked for me, but you should always make sure you have BACK-UPS! # ################################################################################## # CAVEAT: Database names are hard-coded into this script. # Change these according to your own datbase names! # drupal-DB: "drupal" # phpbb-DB: "phpbb" ################################################################################## # CAVEAT: All forum names are hard-coded, too (see CALL migrateForum(...) below) # I had massive problems to create forums in phpbb from SQL. # Instead, I decided to create forums using the ACP ################################################################################## ################################################################################## # How to use # - edit this script to your needs # - make the necessary preparations with ACP (create empty forums) # - make sure that your phpbb forum is clean of posts, topics and "regular" users (with uid < 100) # (this includes removing the automatically created topic at installation of phpBB!) # - start mysql # - Copy this whole script # - Paste right into the mysql prompt. # That's it. ################################################################################## # Problems: HTML in bodies and subjects is copied over and not checked! Some contents need to be cleaned up manually # to be done in Administration Control Panel (ACP) # - Create all categories/forums with same name as in drupal # migrate all active (!) users, omit inactive users # To distinguish new users from existing, I'll add 100 to every id. phpbb has already created lots of bot users up to id=52 on my installation # u.status = 1 means "active", group_id 2 is "REGISTERED" INSERT INTO phpbb.phpbb_users( user_id, group_id, username, username_clean, user_password, user_regdate, user_posts, user_email, user_sig ) SELECT uid+100, 2, name, name, pass, created, 0, mail, signature FROM drupal.users u WHERE u.uid > 0 and u.status = 1; # Undo, if necc. # DELETE FROM phpbb.phpbb_users WHERE user_id>100; # Put users into groups table, too INSERT INTO phpbb.phpbb_user_group(group_id, user_id, user_pending) SELECT 2, u.user_id, 0 FROM phpbb.phpbb_users u WHERE u.user_id > 100; # Undo, if necc. # DELETE FROM phpbb.phpbb_user_group WHERE user_id>100; # if you need to delete all posts and topics: # DELETE FROM phpbb.phpbb_topics WHERE topic_id != 0; # ALTER TABLE phpbb.phpbb_topics AUTO_INCREMENT = 1; # DELETE FROM phpbb.phpbb_posts WHERE post_id != 0; # ALTER TABLE phpbb.phpbb_posts AUTO_INCREMENT = 1; # drop PROCEDURE, because maybe we want to edit and declare it again DROP PROCEDURE migrateForum; # delimiter is used for sql to "see" begin and end of declaration (must be reset afterward) delimiter // CREATE PROCEDURE migrateForum( forumName VARCHAR(100) ) BEGIN SET @sourceForum = forumName; SET @targetForum = @sourceForum; SELECT @sourceForumID := tid FROM drupal.forum_term_data WHERE name LIKE @sourceForum; SELECT @targetForumID := forum_id FROM phpbb.phpbb_forums WHERE forum_name LIKE @targetForum; # create all topics, yet without any content or posts within INSERT INTO phpbb_topics(topic_id, forum_id, topic_title, topic_poster, topic_time) SELECT n.nid, @targetForumID, n.title, n.uid+100, n.created FROM drupal.forum_node n, drupal.forum_forum f WHERE f.tid = @sourceForumID and f.nid = n.nid and n.type='forum'; END // # have to reset delimiter, else it would be strange to edit in mysql delimiter ; ################################################################################## # Migrate all forum topics # This is the place to enter all YOUR forum names # !! These must be (almost) identical in drupal and phpbb # The forums must be created before running this script! ################################################################################## CALL migrateForum( "GardameerForum.nl" ); CALL migrateForum( "ItalieForum.eu" ); CALL migrateForum( "VakantieForum.eu" ); # The content of the topic is to be put into a post, create new post for every topic. Generated 'post_id' is not needed later on INSERT INTO phpbb_posts(topic_id, forum_id, poster_id, post_time, post_subject, post_text) SELECT nr.nid, t.forum_id, nr.uid+100, nr.timestamp, nr.title, nr.body FROM drupal.forum_node_revisions nr, drupal.forum_node n, phpbb.phpbb_topics t WHERE n.type='forum' and nr.nid = n.nid and n.nid = t.topic_id; # Insert the posts which came form drupal comments section # This also includes the poster names from guest users INSERT INTO phpbb_posts(topic_id, forum_id, poster_id, poster_ip, post_time, post_username, post_subject, post_text) SELECT c.nid, t.forum_id, c.uid+100, c.hostname, c.timestamp, c.name, subject, comment FROM drupal.forum_comments c, phpbb.phpbb_topics t WHERE t.topic_id = c.nid; # fill in missing parts, mostly usernames as to be found through lookup in phpbb_users UPDATE phpbb_posts SET post_username = (SELECT username FROM phpbb_users WHERE user_id = poster_id) where post_username = ''; # calculate topic_first_post_id and topic_last_post_id UPDATE phpbb_topics t SET topic_first_post_id = (SELECT min(post_id) FROM phpbb_posts p WHERE t.topic_id = p.topic_id); UPDATE phpbb_topics t SET topic_last_post_id = (SELECT max(post_id) FROM phpbb_posts p WHERE t.topic_id = p.topic_id); # fill in missing parts UPDATE phpbb_topics SET topic_first_poster_name = (SELECT username FROM phpbb_users WHERE user_id = (SELECT poster_id FROM phpbb_posts WHERE post_id = topic_first_post_id)) WHERE topic_first_post_id != 0; UPDATE phpbb_topics SET topic_last_poster_name = (SELECT username FROM phpbb_users WHERE user_id = (SELECT poster_id FROM phpbb_posts WHERE post_id = topic_last_post_id)) WHERE topic_last_post_id != 0; UPDATE phpbb_topics SET topic_last_post_time = (SELECT post_time FROM phpbb_posts WHERE post_id = topic_last_post_id) WHERE topic_last_post_id != 0; UPDATE phpbb_topics SET topic_last_post_subject = (SELECT post_subject FROM phpbb_posts WHERE post_id = topic_last_post_id) WHERE topic_last_post_id != 0; # calculate number of replies (1st post is the topic itself and should not be taken into account) UPDATE phpbb_topics t SET topic_replies = (SELECT COUNT(*) FROM phpbb_posts p WHERE p.topic_id = t.topic_id); UPDATE phpbb_topics t SET topic_replies_real = (SELECT COUNT(*)-1 FROM phpbb_posts p WHERE p.topic_id = t.topic_id); # more calculations for proper display of statistics UPDATE phpbb_forums f SET forum_topics_real = (SELECT COUNT(*) FROM phpbb_topics t WHERE t.forum_id = f.forum_id); UPDATE phpbb_forums f SET forum_posts = (SELECT COUNT(*) FROM phpbb_posts p WHERE p.forum_id = f.forum_id); UPDATE phpbb_forums f SET forum_last_post_id = (SELECT MAX(post_id) FROM phpbb_posts p WHERE p.forum_id = f.forum_id); UPDATE phpbb_forums f SET forum_last_poster_id = (SELECT poster_id FROM phpbb_posts WHERE post_id = forum_last_post_id); UPDATE phpbb_forums f SET forum_last_poster_id = 0 WHERE forum_posts = 0; UPDATE phpbb_forums f SET forum_last_poster_name = (SELECT username FROM phpbb_users WHERE user_id = forum_last_poster_id) WHERE forum_posts != 0; UPDATE phpbb_forums f SET forum_last_poster_name = '' WHERE forum_posts = 0; UPDATE phpbb_forums f SET forum_last_post_subject = (SELECT post_subject FROM phpbb_posts WHERE post_id = forum_last_post_id); UPDATE phpbb_forums f SET forum_last_post_subject = '' WHERE forum_posts = 0; UPDATE phpbb_forums f SET forum_last_post_time = (SELECT post_time FROM phpbb_posts WHERE post_id = forum_last_post_id); UPDATE phpbb_forums f SET forum_last_post_time = 0 WHERE forum_posts = 0; # correct id for anonymous user UPDATE phpbb_posts SET poster_id = '1' WHERE poster_id = '100'; ################################################################################## # to be done on ACP # - Maintenance/Search Index: delete and build indexes again # - General: "Resynchronise statistics" # - Forums: Resynchronise forums ##################################################################################