#!/usr/bin/python import MySQLdb #written by rein of tmw/evol #credit to bruder torgen of phpbb.com. for his black magic sql statements. #https://www.phpbb.com/community/viewtopic.php?f=65&t=1917165 #hope it runs smooth as it does for me # this script has little to no error checking # it is uggly! # presented as is , working in test environment, # ready for inspction and testing in production environment. #note.. did not fully implement privmsgs #but left code for it in included file #-----------preperation------------------------------------------------------- # backup both forum dbs (in the event of failure start fresh . do not attempt salvage or a second execution on damaged db) # # db1 source # db2 target # will yeild a forum with the settings of db2 with combined content of both # # from db1 backup tables: # phpbb_users,phpbb_topics,phpbb_forums,phpbb_posts,phpbb_privmsgs,phpbb_privmsgs_to,phpbb_attachments,phpbb_poll_votes,phpbb_poll_options # # edit the backup tables # find and "replace all" add a suffix of _2 (eg. phpbb_users_2,phpbb_topics_2 etc ) to the table names # import modified tables into db2 # # db2 is now the working database for the merge #---------operation------------------------------------ #fill in the nessicary db connection info then execute with python. #this will take some luck #after sucesfull execution . #caches will need to be clered #resynch and reset everything (post count users online cache active sessions etc) #and permissions will need to be setup or the forums will be invisable to admin and user alike #ignore the example values they are from bruder torgens forum entries .used as refference global maxuser #example 133 global maxforum #example 34 global maxright #example 68 global maxattach global maxtopic #example 424 global maxpost #example 9959 global maxmsg #example 406 print"rein: hello tmw/evol" # Open database connection - user pass database print "connecting" db = MySQLdb.connect("localhost","phpBB","pass500","phpBB" ) cursor = db.cursor() #----------------users-------------------------------------------------------------------------------------- #add duplicate user colum to users2 cursor.execute("alter table phpbb_users_2 add user_id_old mediumint(8) ") #get usercount` cursor.execute("SELECT max( user_id ) FROM phpbb_users;") data = cursor.fetchone() maxuser=data print "maxuser : %s " % maxuser # remove the auto increment attribute and primary key index cursor.execute("ALTER TABLE phpbb_users_2 CHANGE `user_id` `user_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;") cursor.execute("alter table phpbb_users_2 drop primary key;") # update users in user2 with new incremented ids print "updating ids" cursor.execute("update phpbb_users_2 set user_id = user_id + %s;" % maxuser) cursor.execute("update phpbb_posts_2 set poster_id = poster_id + %s;" % maxuser) ##update related tables cursor.execute("update phpbb_topics_2 set topic_poster = topic_poster + %s;" % maxuser) cursor.execute("update phpbb_topics_2 set topic_last_poster_id = topic_last_poster_id +%s;" % maxuser) cursor.execute("update phpbb_forums_2 set forum_last_poster_id = forum_last_poster_id + %s;" % maxuser) cursor.execute("update phpbb_privmsgs_2 set author_id = author_id + %s;" % maxuser) cursor.execute("update phpbb_privmsgs_to_2 set user_id = user_id + %s;" % maxuser) cursor.execute("update phpbb_privmsgs_to_2 set author_id = author_id+ %s ;" % maxuser) cursor.execute("update phpbb_attachments_2 set poster_id = poster_id + %s;" % maxuser) cursor.execute("update phpbb_poll_votes_2 set vote_user_id = vote_user_id + %s;" % maxuser) #identify duplicates cursor.execute("UPDATE phpbb_users_2, phpbb_users SET phpbb_users_2.user_id_old = phpbb_users_2.user_id, phpbb_users_2.user_id = phpbb_users.user_id WHERE phpbb_users_2.username_clean LIKE phpbb_users.username_clean;") cursor.execute("update phpbb_posts_2, phpbb_users_2 set phpbb_posts_2.poster_id = phpbb_users_2.user_id where phpbb_posts_2.poster_id = phpbb_users_2.user_id_old;") cursor.execute("update phpbb_forums_2, phpbb_users_2 set phpbb_forums_2.forum_last_poster_id = phpbb_users_2.user_id where phpbb_forums_2.forum_last_poster_id = phpbb_users_2.user_id_old;") cursor.execute("update phpbb_topics_2, phpbb_users_2 set phpbb_topics_2.topic_poster = phpbb_users_2.user_id where phpbb_topics_2.topic_poster = phpbb_users_2.user_id_old;") cursor.execute("update phpbb_topics_2, phpbb_users_2 set phpbb_topics_2.topic_last_poster_id = phpbb_users_2.user_id where phpbb_topics_2.topic_last_poster_id = phpbb_users_2.user_id_old;") cursor.execute("update phpbb_privmsgs_2, phpbb_users_2 set phpbb_privmsgs_2.author_id = phpbb_users_2.user_id where phpbb_privmsgs_2.author_id = phpbb_users_2.user_id_old;") cursor.execute("update phpbb_privmsgs_to_2, phpbb_users_2 set phpbb_privmsgs_to_2.author_id = phpbb_users_2.user_id where phpbb_privmsgs_to_2.author_id = phpbb_users_2.user_id_old;") cursor.execute("update phpbb_privmsgs_to_2, phpbb_users_2 set phpbb_privmsgs_to_2.user_id = phpbb_users_2.user_id where phpbb_privmsgs_to_2.user_id = phpbb_users_2.user_id_old;") #deactivate added users . optionallllll and annoying dont enable lol #cursor.execute("update phpbb_users_2 set user_type = 1 where user_id_old is null;") #merge users print "merging users!" cursor.execute("INSERT INTO phpbb_users (user_id, user_type, group_id, user_permissions, user_perm_from, user_ip, user_regdate, username, username_clean, user_password, user_passchg, user_pass_convert, user_email, user_email_hash, user_birthday, user_lastvisit, user_lastmark, user_lastpost_time, user_lastpage, user_last_confirm_key, user_last_search, user_warnings, user_last_warning, user_login_attempts, user_inactive_reason, user_inactive_time, user_posts, user_lang, user_timezone, user_dst, user_dateformat, user_style, user_rank, user_colour, user_new_privmsg, user_unread_privmsg, user_last_privmsg, user_message_rules, user_full_folder, user_emailtime, user_topic_show_days, user_topic_sortby_type, user_topic_sortby_dir, user_post_show_days, user_post_sortby_type, user_post_sortby_dir, user_notify, user_notify_pm, user_notify_type, user_allow_pm, user_allow_viewonline, user_allow_viewemail, user_allow_massemail, user_options, user_avatar, user_avatar_type, user_avatar_width, user_avatar_height, user_sig, user_sig_bbcode_uid, user_sig_bbcode_bitfield, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_form_salt) SELECT user_id, user_type, group_id, user_permissions, user_perm_from, user_ip, user_regdate, username, username_clean, user_password, user_passchg, user_pass_convert, user_email, user_email_hash, user_birthday, user_lastvisit, user_lastmark, user_lastpost_time, user_lastpage, user_last_confirm_key, user_last_search, user_warnings, user_last_warning, user_login_attempts, user_inactive_reason, user_inactive_time, user_posts, user_lang, user_timezone, user_dst, user_dateformat, user_style, user_rank, user_colour, user_new_privmsg, user_unread_privmsg, user_last_privmsg, user_message_rules, user_full_folder, user_emailtime, user_topic_show_days, user_topic_sortby_type, user_topic_sortby_dir, user_post_show_days, user_post_sortby_type, user_post_sortby_dir, user_notify, user_notify_pm, user_notify_type, user_allow_pm, user_allow_viewonline, user_allow_viewemail, user_allow_massemail, user_options, user_avatar, user_avatar_type, user_avatar_width, user_avatar_height, user_sig, user_sig_bbcode_uid, user_sig_bbcode_bitfield, user_from, user_icq, user_aim, user_yim, user_msnm, user_jabber, user_website, user_occ, user_interests, user_actkey, user_newpasswd, user_form_salt from phpbb_users_2 where user_id_old is null;") #---------------------------------privmsgs----------------------------------------------------------------- #see stored routine file for instruction #----------------forums------------------------------------------------------------------------------------- #get usercount` cursor.execute("SELECT max( forum_id ) FROM phpbb_forums;") data = cursor.fetchone() maxforum=data print "maxforum : %s " % maxforum cursor.execute("SELECT max( right_id ) FROM phpbb_forums;") data = cursor.fetchone() maxright=data print "maxright : %s " % maxright # remove the auto increment attribute and primary key index againnnn cursor.execute("ALTER TABLE phpbb_forums_2 CHANGE `forum_id` `forum_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;") cursor.execute("alter table phpbb_forums_2 drop primary key;") # update users in forums2 with new incremented ids print "updating ids, again" cursor.execute("update phpbb_forums_2 set forum_id = forum_id + %s;" % maxforum) cursor.execute("update phpbb_forums_2 set right_id = right_id + %s;" % maxright) cursor.execute("update phpbb_forums_2 set left_id = left_id + %s;" % maxright) #right left whats the diff . olol #^^^^^ but really dont mess with this little section or the forums will break hard . #things turning into subtopics of other things or outright dissapearing lolol #increase non parent ids cursor.execute("update phpbb_forums_2 set parent_id = parent_id + %s where parent_id <> 0;" % maxforum) #update related tables cursor.execute("update phpbb_topics_2 set forum_id = forum_id + %s;" % maxforum) cursor.execute("update phpbb_posts_2 set forum_id = forum_id + %s;" % maxforum) #----------------topics------------------------------------------------------------------------------------- cursor.execute("SELECT max( topic_id ) FROM phpbb_topics;") data = cursor.fetchone() maxtopic=data print "maxtopic : %s " % maxtopic # remove the auto increment attribute and primary key index againnnn cursor.execute("ALTER TABLE phpbb_topics_2 CHANGE `topic_id` `topic_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;") cursor.execute("alter table phpbb_topics_2 drop primary key;") #update related tables again cursor.execute("update phpbb_topics_2 set topic_id = topic_id + %s;" % maxtopic) cursor.execute("update phpbb_posts_2 set topic_id = topic_id + %s;" % maxtopic) cursor.execute("update phpbb_attachments_2 set topic_id = topic_id + %s;" % maxtopic) cursor.execute("update phpbb_poll_options_2 set topic_id = topic_id + %s;" % maxtopic) cursor.execute("update phpbb_poll_votes_2 set topic_id = topic_id + %s;" % maxtopic) #----------------posts_------------------------------------------------------------------------------------- cursor.execute("SELECT max( post_id ) FROM phpbb_posts;") data = cursor.fetchone() maxpost=data print "maxpost : %s " % maxpost # remove the auto increment attribute and primary key index yet again cursor.execute("ALTER TABLE phpbb_posts_2 CHANGE `post_id` `post_id` MEDIUMINT( 8 ) UNSIGNED NOT NULL;") cursor.execute("alter table phpbb_posts_2 drop primary key;") #update related tables yet again cursor.execute("update phpbb_posts_2 set post_id = post_id + %s;" % maxpost) cursor.execute("update phpbb_forums_2 set forum_last_post_id = forum_last_post_id + %s;" % maxpost) cursor.execute("update phpbb_topics_2 set topic_first_post_id = topic_first_post_id +%s;" % maxpost) cursor.execute("update phpbb_topics_2 set topic_last_post_id = topic_last_post_id + %s;" % maxpost) cursor.execute("update phpbb_attachments_2 set post_msg_id= post_msg_id + %s;" % maxpost) #----------------attachments_--------------------------------------------------------------------------------- cursor.execute("SELECT max( attach_id ) FROM phpbb_attachments;") data = cursor.fetchone() maxattach=data print "maxattach : %s " % maxattach cursor.execute("update phpbb_attachments_2 set attach_id = attach_id + %s;" % maxattach) #cursor.execute("update phpbb_attachments_2 set post_msg_id= post_msg_id + %s;" % maxpost) #cursor.execute("update phpbb_attachments_2 set topic_id = topic_id + %s;" % maxtopic) #cursor.execute("update phpbb_attachments_2 set poster_id = poster_id + %s;" % maxuser) #------------------merge-------------------------------------------------------------------------------------- print "time for the real deal!" print "merging forums!" cursor.execute("INSERT INTO phpbb_forums (forum_id, parent_id, left_id, right_id, forum_parents, forum_name, forum_desc, forum_desc_bitfield, forum_desc_options, forum_desc_uid, forum_link, forum_password, forum_style, forum_image, forum_rules, forum_rules_link, forum_rules_bitfield, forum_rules_options,forum_rules_uid, forum_topics_per_page, forum_type, forum_status, forum_posts, forum_topics, forum_topics_real, forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name, forum_last_poster_colour, forum_flags, forum_options, display_subforum_list, display_on_index, enable_indexing, enable_icons, enable_prune, prune_next, prune_days, prune_viewed, prune_freq) SELECT forum_id, parent_id, left_id, right_id, forum_parents, forum_name, forum_desc, forum_desc_bitfield, forum_desc_options, forum_desc_uid, forum_link, forum_password, forum_style, forum_image, forum_rules, forum_rules_link, forum_rules_bitfield, forum_rules_options, forum_rules_uid, forum_topics_per_page, forum_type, forum_status, forum_posts, forum_topics, forum_topics_real, forum_last_post_id, forum_last_poster_id, forum_last_post_subject, forum_last_post_time, forum_last_poster_name, forum_last_poster_colour, forum_flags, forum_options, display_subforum_list, display_on_index, enable_indexing, enable_icons, enable_prune, prune_next, prune_days, prune_viewed, prune_freq from phpbb_forums_2;") #bruder torgen said there wa ssome unspecified issue with a colum here he coudlnt rememebr print "merging topics!" cursor.execute("INSERT INTO phpbb_topics (topic_id, forum_id, icon_id, topic_attachment, topic_approved, topic_reported, topic_title, topic_poster, topic_time, topic_time_limit, topic_views, topic_replies, topic_replies_real, topic_status, topic_type, topic_first_post_id, topic_first_poster_name, topic_first_poster_colour, topic_last_post_id, topic_last_poster_id, topic_last_poster_name, topic_last_poster_colour, topic_last_post_subject, topic_last_post_time, topic_last_view_time, topic_moved_id, topic_bumped, topic_bumper, poll_title, poll_start, poll_length, poll_max_options, poll_last_vote, poll_vote_change) SELECT topic_id, forum_id, icon_id, topic_attachment, topic_approved, topic_reported, topic_title, topic_poster, topic_time, topic_time_limit, topic_views, topic_replies, topic_replies_real, topic_status, topic_type, topic_first_post_id, topic_first_poster_name, topic_first_poster_colour, topic_last_post_id, topic_last_poster_id, topic_last_poster_name, topic_last_poster_colour, topic_last_post_subject, topic_last_post_time, topic_last_view_time, topic_moved_id, topic_bumped, topic_bumper, poll_title, poll_start, poll_length, poll_max_options, poll_last_vote, poll_vote_change from phpbb_topics_2;") print "merging posts!" cursor.execute("INSERT INTO phpbb_posts (post_id, topic_id, forum_id, poster_id, icon_id, poster_ip, post_time, post_approved, post_reported, enable_bbcode, enable_smilies, enable_magic_url, enable_sig, post_username, post_subject, post_text, post_checksum, post_attachment, bbcode_bitfield, bbcode_uid, post_postcount, post_edit_time, post_edit_reason, post_edit_user, post_edit_count, post_edit_locked)SELECT post_id, topic_id, forum_id, poster_id, icon_id, poster_ip, post_time, post_approved, post_reported, enable_bbcode, enable_smilies, enable_magic_url, enable_sig, post_username, post_subject, post_text, post_checksum, post_attachment, bbcode_bitfield, bbcode_uid, post_postcount, post_edit_time,post_edit_reason, post_edit_user, post_edit_count, post_edit_locked from phpbb_posts_2;") print "merging attachments!" cursor.execute("INSERT INTO phpbb_attachments (attach_id,post_msg_id,topic_id,in_message,poster_id,is_orphan,physical_filename,real_filename,download_count,attach_comment,extension,mimetype,filesize,filetime,thumbnail) SELECT attach_id,post_msg_id,topic_id,in_message,poster_id,is_orphan,physical_filename,real_filename,download_count,attach_comment,extension,mimetype,filesize,filetime,thumbnail from phpbb_attachments_2;") print "merging polls!" cursor.execute("INSERT INTO phpbb_poll_options (poll_option_id,topic_id,poll_option_text,poll_option_total) SELECT poll_option_id,topic_id,poll_option_text,poll_option_total from phpbb_poll_options_2;") cursor.execute("INSERT INTO phpbb_poll_votes (topic_id,poll_option_id,vote_user_id,vote_user_ip) SELECT topic_id,poll_option_id,vote_user_id,vote_user_ip from phpbb_poll_votes_2;") #dont forget to commit db.commit() # disconnect db.close()