Simple Machines Community Forum

Customizing SMF => SMF Coding Discussion => Topic started by: shawnb61 on October 06, 2020, 02:05:26 AM

Title: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on October 06, 2020, 02:05:26 AM
Here is a link to the latest version of the mergeSMF.php script: mergeSMF.php (https://github.com/sbulen/sjrbTools/blob/master/mergeSMF.php)

History:
Original version for SMF 1.0.x by Oldiesmann at SimpleMachines.org
Modified version for SMF 1.1RC3 by Resourcez at resourcez.biz (way back in 2006)
Modified version for SMF 2.0.6 by bfeist (fall, 2013). Let's start this new version at 2.0.
Modified version for SMF 2.0.17 by shawnb61 (Sept, 2020).  Tested with SMF 2.0.17 & php 7.3.
Modified version for SMF 2.0.17 to better duplicate member handling & logging of any member renames.  Thanks to GL700Wing, Dec, 2020

Description:
- This script will merge two SMF forums. It will take the boards, topics, members, messages, etc., from a SECONDARY forum and merge them into a PRIMARY forum.
- You need to be comfortable with PHP, and wrapping your head around databases, etc., to use this script. It works, but it will take some trial and error to get everything right.
- Given SMF's flexibility, there will be wrinkles... Anticipate issues. Backups, backups, backups...
- We are all indebted to Oldiesmann for the deep institutional SMF knowledge baked into the script & approach.
- This utility is not officially supported by the SMF team. I was asked to do a test run for my forum, so I brought this script current to do so, and am now sharing it here in case others find it useful.

Prerequisites:
1. BACKUP everything, file systems & databases, twice.  In addition, ask your host to do so.  If things go sideways, you must be able to restore everything.
2. Practice all the steps below in a test forum.  If you cannot do so, do not attempt this.
3. Primary & secondary forums must be in perfect working order.  E.g., all routine maintenance should be run, integrity checks run. A broken forum before merging = a horrifically mangled forum after merging...
4. Collations & charsets must be aligned across the forums & tables.
5. Forums must be offline and frozen.  A single update in the middle of execution can break everything...

Instructions:
1. Restore your two forums and get them working side-by-side on your site, both installations within the same database (just use a different database prefix like smf2_ for the SECONDARY one prior to the DB restore).
2. Edit mergeSMF.php in a text editor and change the prefix to match the database prefix of the PRIMARY smf board (define('PRIMARY_DB_PREFIX', 'smf_') - change smf_ to whatever you used...).
3. Also set $secondary_suffix to be the value appended to the end of member names & group names & other records in order to make them unique, in case of conflicts with the primary forum.
4. Save this modified file, put it in the directory for the SECONDARY smf installation and run it (just like you would run install.php or the converter).
5. Follow the instructions, clicking the "Continue" link as needed.
6. Pay careful attention to any errors reported.
7. When completed, copy all files in your SECONDARY attachments/to_move_to_primary directories to the primary attachments directories.
8. Copy all files in your SECONDARY custom_avatar/to_move_to_primary directory to the primary custom_avatar directory.  Be careful to confirm the name of the directory, it is different for different forums.
9. Copy all files in your SECONDARY avatars directory to the primary avatars directory.
10. Once you're done, if everything worked, login to your PRIMARY SMF board, go to Admin -> Forum Maintenance and click on "Recount all totals and statistics" - this will update everything for you.
11. Check for any members in the new (merged) PRIMARY SMF installation that end with your $secondary_suffix. If desired, these members can be merged with the members of the same name using a tool like SMF Admin Toolbox (https://github.com/Spuds/SMF-Admin-Toolbox)
12. Carefully audit all board permissions, membergroups, and subscriptions.
13. Clear your cache.

Approach:
- This script basically makes the contents of the secondary forum unique, and then copies the contents over to the primary forum.  Default & duplicate content are removed from the secondary forum.  Remaining content is made unique by incrementing the keys safely beyond values used in the primary forum (e.g., board IDs), or by appending the $secondary_suffix to the value (e.g., member names).
- Records merged: categories, boards, topics, members, messages, PMs, attachments, polls, poll_choices, log_actions, membergroups, permission_profiles, moderators, notifications, subscriptions, log_subscribed, log_reported, log_reported_comments, ban_groups and ban_items.
- Records not merged: Anything not named above... Packages, themes, logs, calendars, search tables, custom_fields.  Any tables that are new in 2.1, e.g., support for likes.
- Member names, real names & email addresses will be made unique by adding the $secondary_suffix.  Duplicate emails will have the suffix, thus will not work.
- When this script is completed, the secondary forum is unusable.

Enhancements included with this version:
- Many new record types added: Permissions, subscriptions, moderation activity, moderators, bans, & associated logs.  The idea was to bring member history along with each member.
- Category & board hierarchy & order are now preserved. The secondary forum appears to be 'appended' intact to the end of the primary forum in the board index.
- Multiple attachment directories supported.
- Avatars & custom avatars are brought over.
- Mismatches of columns between the primary & secondary forums are handled; no records dropped.
- There is no notion of 'bigger' vs 'smaller' forums. I have merged a massive forum into a smaller one & vice-versa.
- Now uses mysqli.
- Improved error reporting; improved reporting of actions taken.
- 2.1 and 2.0 support.  2.1 tested with 2.1 RC3.

Notes on Permissions:
The basic idea is to only bring over what is unique to the secondary forum:
- Profiles: Only unique profiles will be brought over.  Default & standard profiles should be defined in the primary forum.
- Membergroups: Only non-default, non-guest, membergroups will be brought over.  Default & guest behavior should be defined in the primary forum.
- Post-count-based membergroups will not be brought over - what is in the primary forum should apply.
- Assignments to post-count-based groups are not brought over.

Other Considerations:
- If you had a custom search index, it will need to be rebuilt.
- Links to other posts, including quote links, will be incorrect unless you use separate utilities to fix them.

Other Utilities that may be of help:
- smf_urls_paths.php (https://github.com/sbulen/sjrbTools/blob/master/smf_urls_paths.php) - I used this to update all the links in the old secondary posts to properly point to the new forum.
- smf_fix_log_actions.php (https://github.com/sbulen/sjrbTools/blob/master/smf_fix_log_actions.php) - I used this to clean up log_actions before running the merge. Checks & fixes all string lengths. UTF8 conversions can break string lengths in serialized strings.
- smf_quote_link_fixer.php (https://github.com/sbulen/sjrbTools/blob/master/smf_quote_link_fixer.php) - Fixes quote links. Note if it can't find the message, it strips the link. Good to run BEFORE doing the merge, so links are solid before you begin. Do not run this immediately after a merge or it will break your links irreparably, as the message numbers would be incorrect.

If you don't understand what these are doing, you probably shouldn't run them...

Revision history:
2.0 Nov 2/2013 - Initial updated script for use with 2.0.6
2.0.1 Nov 2/2013 - Added an initial step to confirm initiation of the script.
2.0.2 June 17/2014 - Fixed an error that skipped step 8.
2.0.17 October 2020 - New version, more record types.  2.1 support.
2.0.17 December 2020 - Enhanced duplicate member logic, courtesy of GL700Wing.

Edited 10/21/20: 2.1 support & multiple attachment folder support.
Edited 11/24/20: Added attachment with before & after screenshots.
Edited 1/5/21: Enhanced member processing.
Edited 10/19/22: Fixed a buncha broken links...
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Dzonny on October 06, 2020, 04:15:24 AM
Great work man, well done!
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Diego Andrés on October 06, 2020, 11:22:29 AM
Really nice, tried one of the old ones a few years ago and was quite an interesting experiment.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Bigguy on October 18, 2020, 02:59:19 PM
Very awesome. Thanks for all your work.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Doug Heffernan on October 18, 2020, 05:14:46 PM
Nice work shawn.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: GL700Wing on October 20, 2020, 09:56:16 PM
Quote11. Check for any members in the new (merged) PRIMARY SMF installation that end with your $secondary_suffix. If desired, these members can be merged with the members of the same name using a tool like SMF Admin Toolbox (http://www.simplemachines.org/community/index.php?topic=470463.0)
Looks like the SMF Admin Toolbox can no longer be downloaded from simplemachines.org - found a copy on GitHub: https://github.com/Spuds/SMF-Admin-Toolbox
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on October 21, 2020, 02:17:45 PM
Quote from: GL700Wing on October 20, 2020, 09:56:16 PM
Looks like the SMF Admin Toolbox can no longer be downloaded from simplemachines.org - found a copy on GitHub: https://github.com/Spuds/SMF-Admin-Toolbox

Thanks!  Note modified above.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on October 21, 2020, 02:26:39 PM
Also note a new version was uploaded.  Works for 2.1 forums as well now.  Also handles multiple attachment folders now.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on November 24, 2020, 04:48:21 PM
I added a document with some before & after screenshots in the original post, to provide a feel for what to expect...
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: GL700Wing on December 26, 2020, 11:33:22 PM
I've used this script to do several test runs of merging two SMF 2.0.17 forums (the secondary forum was converted from BBPress 2.6.6) and it's great.

The only issue I encountered is in relation to the renaming of member accounts.  Basically there were some member accounts in each database that had the same value for either 'member_name' (ie, the username they login with) or 'real_name' (ie, the name that is displayed for a member throughout the forum) where the accounts were for two completely different members (ie, different people in real life, different corresponding 'real_name' or 'member_name', and different 'email_address').  Ironically, and in one example I found, a member in the secondary database had the same 'real_name' as me!

By default the mergeSMF.php script renames all of the 'member_name', 'real_name' and 'email_address' values that are imported from the secondary database if any one of these values has a match in the primary database.  As a result I ended up with several imported member accounts where the member wouldn't have been able to login using their username or to recover their password via email (and they also wouldn't have had a secret question/answer set as they were a member of the forum that had been converted from BBPress).

To resolve this issue I have modified the mergeSMF.php script I'm using so that only the matching 'member_name', 'real_name' and/or 'email_address' values are renamed for imported member accounts (this can still be all three values if they all match).  I've also modified the mergeSMF.php file to copy the details of all the member renaming actions to a log file for later reference.




The changes I've made to mergeSMF.php may be found in a post below.

Finally, and to make it possible for Admins to search the member list for 'member_name' and 'real_name' values separately after forums have been merged (by default both database columns are searched for name matches), I've also updated the ManageMembers code.

In ./Sources/ManageMembers.php

Find:
'membername' => array(
'db_fields' => array('member_name', 'real_name'),
'type' => 'string'
),


Replace with:
'membername' => array(
'db_fields' => array('member_name'),
'type' => 'string'
),
'realname' => array(
'db_fields' => array('real_name'),
'type' => 'string'
),



In ./Themes/default/ManageMembers.template.php

Find:
<dt class="righttext">
<strong>', $txt['username'], ':</strong>
</dt>
<dd>
<input type="text" name="membername" value="" class="input_text" />
</dd>


Add after:
<dt class="righttext">
<strong>', $txt['display_name'], ':</strong>
</dt>
<dd>
<input type="text" name="realname" value="" class="input_text" />
</dd>


Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on December 27, 2020, 12:37:35 AM
Glad it worked out for you!

Your enhancements sound like they make a lot of sense. 

If you don't mind, I'd like to fold them into the script up on GitHub.  Or, better yet, if you could submit a PR up on GH, that would even be cleaner!
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: GL700Wing on December 27, 2020, 01:11:36 AM
Quote from: shawnb61 on December 27, 2020, 12:37:35 AM
Glad it worked out for you!

Your enhancements sound like they make a lot of sense.
Actually - I just realised that I forgot to include some very necessary code in my previous message - oops!

The correct information for the second code block I posted above for mergeSMF.php is as as follows - if you can edit my post and fix it up that'd be great.

In mergeSMF.php
Find:
if(($member_name_hits > 0) || ($real_name_hits > 0) || ($email_hits > 0))
{
echo "Member $mem[1] exists in primary. Renaming...<BR>";
$sql = "UPDATE {$db_prefix}members SET member_name = CONCAT(member_name, '{$secondary_suffix}'), real_name = CONCAT(real_name, '{$secondary_suffix}'), email_address = CONCAT(email_address, '{$secondary_suffix}') WHERE ID_MEMBER = '$mem[0]'";
$query0 = call_mysqli_query($sql, false);
}


Replace with:
if(($member_name_hits > 0) || ($real_name_hits > 0) || ($email_hits > 0))
{
$mn_suffix = ($member_name_hits > 0 ? $secondary_suffix : '');
$rn_suffix = ($real_name_hits > 0 ? $secondary_suffix : '');
$ea_suffix = ($email_hits > 0 ? $secondary_suffix : '');

$matches = ($member_name_hits > 0 ? '&nbsp;\'member_name\'&nbsp;' : '');
$matches .= ($real_name_hits > 0 ? '&nbsp;\'real_name\'&nbsp;' : '');
$matches .= ($email_hits > 0 ? '&nbsp;\'email_address\'&nbsp;' : '');

$exists = "The same <strong>$matches</strong> for the username <strong>&nbsp;'$mem[1]'&nbsp;</strong> (member ID '$mem[0]') already exists in primary.";
$renaming = "Renaming <strong>$matches</strong> in secondary ...";
echo "$exists $renaming <br />";

$now = '[' . date("d-M-Y H:i:s e") . '] ';
$exists = str_replace(array('&nbsp;', '<strong>', '</strong>'), array(' ', '', ''), $exists);
$renaming = str_replace(array('&nbsp;', '<strong>', '</strong>'), array(' ', '', ''), $renaming);
error_log(print_r($now . $exists . "  " . $renaming . "\n\n", true), 3, $renameMembersLog);

$sql = "UPDATE {$db_prefix}members SET member_name = CONCAT(member_name, '{$mn_suffix}'), real_name = CONCAT(real_name, '{$rn_suffix}'), email_address = CONCAT(email_address, '{$ea_suffix}') WHERE ID_MEMBER = '$mem[0]'";
$query0 = call_mysqli_query($sql, false);
}




QuoteIf you don't mind, I'd like to fold them into the script up on GitHub.  Or, better yet, if you could submit a PR up on GH, that would even be cleaner!
I haven't used GitHub before but I'll give it a go ...
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on January 06, 2021, 12:53:11 AM
GL700Wing - I have tested your updates & folded them info the version up on GitHub.

THANK YOU.  I like 'em. 
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on January 06, 2021, 01:02:21 AM
Quote from: GL700Wing on December 27, 2020, 01:11:36 AM
I haven't used GitHub before but I'll give it a go ...

Some instructions on how to contribute via GitHub are attached.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Shambles on January 06, 2021, 04:37:56 AM
Quote from: shawnb61 on January 06, 2021, 01:02:21 AM

Just as an FYI, there is a note on how to contribute via GitHub over here:
https://www.simplemachines.org/community/index.php?topic=576283.0

Is that in a "team only" board?

QuoteThe topic or board you are looking for appears to be either missing or off limits to you.


EDIT: Fixed the link above...
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on January 06, 2021, 06:23:37 AM
Sorry about that...  It is now an attachment above!
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Aleksi "Lex" Kilpinen on January 06, 2021, 06:23:59 AM
It's in a restricted board yes, but I see the original post was fixed already :)

EDIT: Ninja'd. :P
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: GL700Wing on January 06, 2021, 06:42:57 AM
Quote from: shawnb61 on January 06, 2021, 12:53:11 AM
GL700Wing - I have tested your updates & folded them info the version up on GitHub.

THANK YOU.  I like 'em. 

You're welcome!

Quote from: shawnb61 on January 06, 2021, 01:02:21 AM
Quote from: GL700Wing on December 27, 2020, 01:11:36 AM
I haven't used GitHub before but I'll give it a go ...

Some instructions on how to contribute via GitHub are attached.

Thanks - I've downloaded the instructions and will use them next time.

I already had a GitHub account with the same name as my SMF user name and I did create a pull request about 10 days ago but I don't know if you were able to access/use it.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on January 06, 2021, 01:16:43 PM
Quote from: GL700Wing on January 06, 2021, 06:42:57 AM
I already had a GitHub account with the same name as my SMF user name and I did create a pull request about 10 days ago but I don't know if you were able to access/use it.

It looks like all the work was done & uploaded to your repo, but the final step, submitting the PR to update my repo, was not done. 

Lots of steps using GitHub! 
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Speed King on May 18, 2021, 01:53:15 PM
I have two SMF forums at the same domain.
The old forum is larger, it's created in 2006. The new forum is smaller, it's created in 2018.
If I decide to merge two forums, I would set the new forum as primary, so the old forum to be attached to it.
But messages in old forum are older than messages in new forum, how they will be joined? Before, or after messages in the new forum?
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on May 18, 2021, 03:18:47 PM
Depends on the function.  Since all messages have dates, any functions based on dates, e.g., searching, works just fine.  Old messages look old & new messages look new.

But the 'unread posts' function will be funky unless you take some special measures.

To address, I used a bonehead utility that marked the older boards as read for newer users.  From the initial post above:
Quote- smf_mark_all_read_merge.php - I used this to mark all secondary boards read by active primary board users. In my instance, the secondary forum was a predecessor to the current forum, and the content was dated.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on October 03, 2021, 02:11:53 AM
After pondering this for about a year, we finally decided to merge our two production forums using this script.

Everything went perfectly.

With minor tweaks, we were even able to merge mod-related content.  Both forums used the Download System mod (https://www.simplemachines.org/community/index.php?topic=203471.0).  So the engine was flexible and tweakable enough to help us do so. 
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: GL700Wing on August 13, 2022, 02:23:48 AM
Quote from: GL700Wing on October 20, 2020, 09:56:16 PMLooks like the SMF Admin Toolbox can no longer be downloaded from simplemachines.org - found a copy on GitHub: https://github.com/Spuds/SMF-Admin-Toolbox
The SMF Admin Toolbox is now on SMFHacks (https://www.smfhacks.com/index.php?action=downloads;sa=view;down=190)
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: seekier on October 17, 2022, 01:35:42 AM
I edited  everything as instructed but when I hit to connect it brings out this error saying table does not exist.

The first error is the attachment bellow followed by numerous error in red colour

NB: I am using the latest version of SMF on both website.
IMG_20221017_062754.jpg 

what is it l am not doing right or is this mod not Compatible with SMF 2.1.2 or this mod does not work on two forum using the same version of SMF?
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on October 17, 2022, 02:47:07 AM
Are both forums using the same database, with different prefixes?  Are they both working?

Quote from: shawnb61 on October 06, 2020, 02:05:26 AMInstructions:
1. Restore your two forums and get them working side-by-side on your site, both installations within the same database (just use a different database prefix like smf2_ for the SECONDARY one prior to the DB restore).
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: seekier on October 17, 2022, 09:21:41 PM
Quote from: shawnb61 on October 17, 2022, 02:47:07 AMAre both forums using the same database, with different prefixes?
Sorry I missed this part. I was using different database and different prefixes.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: shawnb61 on October 20, 2022, 12:49:21 AM
I'm curious how it all turned out, @seekier !
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: seekier on October 20, 2022, 10:49:43 AM
Quote from: shawnb61 on October 20, 2022, 12:49:21 AMI'm curious how it all turned out, @seekier !
I messed up, after Successfully  merged, my forum categories, boards and topic was ok but existing Users does not have permission to do anything even the admins.

I wiped everything out and try to restore my backup but I have lost my backup file so I am starting from scratch.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Doug Heffernan on October 20, 2022, 11:00:07 AM
Quote from: seekier on October 20, 2022, 10:49:43 AMI messed up, after Successfully  merged, my forum categories, boards and topic was ok but existing Users does not have permission to do anything even the admins.

Did you check the (group) permissions after the merge?

Quote from: seekier on October 20, 2022, 10:49:43 AMI wiped everything out and try to restore my backup but I have lost my backup file so I am starting from scratch.

Can you ask your host if they have a recent backup copy of your website?
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: seekier on October 20, 2022, 12:42:58 PM
Quote from: Doug Heffernan on October 20, 2022, 11:00:07 AMDid you check the (group) permissions after the merge?
I was not able to do that because even I the admin didn't have permission into admin section.

QuoteCan you ask your host if they have a recent backup copy of your website?
I did asked them but they couldn't help because their backup was not recent.
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: Doug Heffernan on October 20, 2022, 01:26:01 PM
Quote from: seekier on October 20, 2022, 12:42:58 PMI was not able to do that because even I the admin didn't have permission into admin section.

That is strange. As an admin you must have permissions everywhere and for everything. Did you get the sandard no permissions error or some other type of error?

Quote from: seekier on October 20, 2022, 12:42:58 PMI did asked them but they couldn't help because their backup was not recent.

It is much better to use an old(er) backup rather than starting from scratch, no?
Title: Re: mergeSMF.php - Script to merge two SMF forums, v2.0.17
Post by: seekier on October 20, 2022, 03:25:21 PM
Quote from: Doug Heffernan on October 20, 2022, 01:26:01 PMIt is much better to use an old(er) backup rather than starting from scratch, no?
that's right.