-#!/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()

-

-

-

-