Upload new file

1 files changed, 240 insertions, 0 deletions

diff --git a/forum-merge/forum-merge.py b/forum-merge/forum-merge.py
new file mode 100644
index 0000000..0e4bf5f
--- /dev/null
+++ b/forum-merge/forum-merge.py
@@ -0,0 +1,240 @@
+#!/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
+
+
+
+
+#   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()
+
+
+
+