[adressler]

I’m importing a large subscription forum from a custom-coded Lotus Notes / Domino database (*sigh*) which I converted to MySQL. The posts exported from Lotus include User’s first and last names, but no passwords, or email addresses. These old users won’t be able sign in, but their names should be displayed next to the posts they authored that were imported to the new WP site. I would like all users to re-register for the new site in order to post new topics, etc.

So, is this even possible? Are email addresses/passwords required when importing Users based on the converter /plugins/bbpress/includes/admin/converters/Example.php? I’m really trying to avoid creating an Admin user named “Archived Author” to assign all 35,000 of these old topics/replies to.

Thanks in advance.

[Robin W]

I imported from an access database (ditto conversion to sql) so know what you’re going through! see https://bbpress.org/forums/topic/documented-import-from-snitz-access-database/ for a blow by blow of how I did this and presume you’re already well into using custom importer https://codex.bbpress.org/import-forums/custom-import/

Now given that you’re going to re-register users, then we only need the topics to be assigned to a user which exists, which then when they re-register you can set up properly.

So for instance if you have a topic with the author name of “fred” – then you need a user fred to exist. This needs to be at ID level.

So in essence you’ll need to import

topic with author ID
User with same ID

Minimum info to create a user is (in theory – it may be less) ID, username, email and password.

In my case they mapped from a snitz access forum (via sql) as follows

MEMBER_ID _bbp_user_id
M_NAME user_login
M_PASSWORD _bbp_password
M_EMAIL user_email

So I would go about it this way.

What we need to end up with is a topics/replies table that has a author ID (number) against each topic/reply.
In a user table, we then need that same ID, together with name, password and email.

So first I’d go back to my lotus notes and see if topics has an author ID. If not we’ll need to create one (we’ll do that in a minute).

Then from the topics and replies lists create a set of autor names and (if it has them) ID’s

Create a user table (in my case in excel, but presume you’re familiar with creating in lotus notes) with the following headings

ID NAME PASSWORD EMAIL

If your topics/replies doesn’t have user ID’s then you just need to populate this table with unique ones, I’m pretty sure that you can just start with 1 and increment them – wordpress will assign new ID numbers as they come across starting with the next number that wordpress is not using.

For password, just put anything – and they can all be the same

Emails must be unique in theory, but don’t need to exist so abc@def.com is fine (you’ll be fixing these as they register, so doesn’t matter). But as you’re importing directly into the database, I reckon you could get away with them all being a single fictitious one. But if you have the skills, I’d create them as increments eg abc1@def.com, abc2@def.com just in case

so now you have a user table to import.

If you topics and replies don’t have author ID’s, then now go back to the topics and replies tables and run macro to populate a new column using name against the user table you’ve just created to populate with id.

Now we have topics with ID’s an da user table to import. Just check that column names match the importer, convert it all to sql, and off you go !

[Robin W]

This does presume that you’ll be doing a manual re-register – ie that the users will send you a name and email and you’ll just amend the username created in the conversion. If you want an automatic registration, then come back as that may be really complicated !

[adressler]

Thanks for the reply Robin,

Ideally, it would be really nice not to have to manually register the 600 or so active members, but it’s doable if that is the only logical route.

Long story short, the old site was built by a senior Lotus Notes dev, and he wrote so much custom code to get the site to do what it did. Whats worse is this guy passed away a couple years ago, and there is no commenting in the db. There is likely no way I can export all of the data, I consider myself lucky to have exported what I have now, which is just barely enough to get things converted to WP. This should be okay though, I can generate unique UserIDs and FKs with PHP and MySQL before importing into BBPress.

My crazy idea that I had was to import these “old” authors as, effectively, name placeholders. Bogus/null values for user_pass and user_email, a php-generated user_login (something like “judygarland-old”, “abcdef12345-old” or similar), user_id (unique Auto Increment #), display_name (“Judy Garland”), and maybe user_nicename. These would only be used to show the Authors first + last name next to all of the old topics and replies I just imported from Lotus (they will not be logging in). When users re-register, they would create a completely new WP user (I’m using s2member to handle subscription info). I realize that “new” users will not be able to see their “old” user’s topic / reply counts, and total user counts will not be completely accurate, but that’s okay and manageable. At least they could create their new user accounts themselves, and I wouldn’t be responsible for making s2member link up properly with these old users.

I guess I will try a few approaches and see which works best, and for anybody else doing something similar I will post my results. Thanks Robin for the ideas.

Aaron

[Robin W]

No problem – I spent several weeks getting my head around importing from a flat file database, in similar circumstances !

The real issue here is whether you can get email addresses. If you can, then if you import users as per my previous, they can use “password lost” to re-do their passwords, and then it’ll all be fine.

The problem otherwise is that posts link to user-id’s not names, so yes when they register they’ll get a new ID, and the old posts won’t show as theirs. Additionally logon names need to be unique so they can’t register with any existing username !

OK so what would I do?

I’d do as previous and create a user table to import, but call each user ‘old_xxx’ – you can do this before import, effectively your placeholder.

As each user registers, you can run some code around the posts to remap them. You’ll get a registration notice, so if you cut some code, then you can just run this each time someone registers. Basically you would just enter there old username and their new one. The code would look up the old ID, the new ID, and then run through posts to remap to the new user. If you’d like help with that, just let me know and I’ll try and cut a quick loop code that you can run straight on the website (pw protected of course)

[adressler]

That sounds like the way to do it. I’m rather new to WordPress but I have a decent knowledge of PHP / MySQL. If it’s not too much of a pain, I’d really appreciate looking at some code to use as a starting point.

[Robin W]

if you’re happy with rough and ready, I’ll try and cut some next week ! should be a simple couple of loops (but they’re never as simple as that !)

[adressler]

Hi Robin,
I was able to export users from Lotus into a new users table. The table structure is:

users {
   user_id INT(11) PRIMARY KEY AUTO INCREMENT
   display_name VARCHAR(250)
   user_nicename VARCHAR(50)
   user_login VARCHAR(60)
}

Data sample:

+-------------------------------------------------------------------------------+
|  user_id  |   display_name   |   user_nicename   |  user_login                |
+-----------+------------------+-------------------+----------------------------+
|   555     |     Jane Doe     |     jane-doe      |  old_123456789abcde12345   |
+-----------+------------------+-------------------+----------------------------+

You will notice I was not able to export any email addresses, or passwords from Lotus. I linked up my custom converter and was able to import users successfully into BBPress without the need to include a bogus email or password. In the WP database, all of these “old” users have an auto-generated password, and no email address. Now, at least all of these old user display_names are showing next to the posts they authored (instead of all “anonymous”).

Is it still possible to write a loop to link these old user accounts to the newly registered users even though there’s no email addresses? I’m okay with the forum working the way it is now, but if there’s a way to link newly registered accounts with the old ones, I’d sure like to try. I’m guessing the only way to do it is to match by the old user display_name, which, from what I can tell, is unique to the users db; no two people have the same name. It would just depend on people remembering the name they used to registered on the old site (some people are registered as “John and Jane Doe”). And if someone signs up who coincidentally has the same name as an old user, there could be some confusion.

I just had a thought, what about a checkbox during registration like “Attempt to link my old DynamiteOnline account”, and when checked it would try to link up the names?

You’re right, nothing is “simple”.. lol. I really appreciate your ideas here, and thanks so much for your time.

Aaron

[Robin W]

Aaron,

Hmmm… 🙂

How many accounts are we talking about?

How are users going to re-register?

If auto the system won’t let you have two usernames the same, so you’d need to change your existing usernames to say have a character in front eg x_john smith (sql loop through user database would do that). Then brief your users to register with their existing username eg just john smith.

The as before, once they have registred (you get an email to say that each one has), you could run a process to convert.

You could do this as they register – a loop to go through wp_posts looking for old author ID and replace with new author ID, or you could automate and run daily/weekly to say go through wp_users and create an array of usernames without the x_ (that would be users who have re-registred) . Then go through that array and pick up the old id’s. You then have a conversion list 9new id vs old ID, and can run that through wp_posts.

None of this is pretty, but all quite do-able.

You could write a registration process that lets them re-use the username, and then automatically change the ID in WP-posts, but that would be kinda dangerous, as I could register as you, and get all your posts allocated to me.

The real solution if you don’t have tons of users is to go for a manual registration process, you can of course let then use the same username, as you can then generate a user with a php programme straight into the database.

Nothing pretty or easy !

But a solution is there somewhere !

[Stephen Edgar]

Nice, sad I missed this thread up until now 😉

Starting from @adressler 2nd post here are my thoughts:

@adressler wrote…
My crazy idea that I had was to import these “old” authors as, effectively, name placeholders. Bogus/null values for user_pass and user_email, a php-generated user_login (something like “judygarland-old”, “abcdef12345-old” or similar), user_id (unique Auto Increment #), display_name (“Judy Garland”), and maybe user_nicename.

I’m with you here and I’ll come back to how to do the above shortly.

@adressler wrote…
These would only be used to show the Authors first + last name next to all of the old topics and replies I just imported from Lotus (they will not be logging in). When users re-register, they would create a completely new WP user (I’m using s2member to handle subscription info).

Still with you, I think this is a good plan.

@adressler wrote…
I realize that “new” users will not be able to see their “old” user’s topic / reply counts, and total user counts will not be completely accurate, but that’s okay and manageable.

Still a good plan, we can fix the above ‘after’ they registered their new account.

@adressler wrote…
At least they could create their new user accounts themselves, and I wouldn’t be responsible for making s2member link up properly with these old users.

Correct, let the users do that themselves.

@robin-w wrote…
The real issue here is whether you can get email addresses. If you can, then if you import users as per my previous, they can use “password lost” to re-do their passwords, and then it’ll all be fine.

As per my first comment of what I will come back to shortly, we don’t need actually need an email address for this.

@robin-w wrote…
Additionally logon names need to be unique so they can’t register with any existing username !

I’m going to have to check that my solution doesn’t break because of this, it is ready to be committed and ready for bbPress v2.6 😉

@robin-w wrote…
I’d do as previous and create a user table to import, but call each user ‘old_xxx’ – you can do this before import, effectively your placeholder.

Again, I shall return and no need for the old_ prefix on the username.

@robin-w wrote…
As each user registers, you can run some code around the posts to remap them. You’ll get a registration notice, so if you cut some code, then you can just run this each time someone registers. Basically you would just enter there old username and their new one. The code would look up the old ID, the new ID, and then run through posts to remap to the new user. If you’d like help with that, just let me know and I’ll try and cut a quick loop code that you can run straight on the website (pw protected of course)

90% of this is actually built into bbPress and again I just need to test this against my solution, as I type this I am thinking maybe a custom one will be needed but shouldn’t be too hard to write a tool for it to integrate with my proposed solution as no doubt others would probably also require this. 😉

@adressler wrote…
I was able to export users from Lotus into a new users table. The table structure is:

That table looks fine, it shouldn’t matter what the old user_login name is as we will primarily use the user Display Name and/or User Nicename.

@adressler wrote…
I linked up my custom converter and was able to import users successfully into BBPress without the need to include a bogus email or password. In the WP database, all of these “old” users have an auto-generated password, and no email address. Now, at least all of these old user display_names are showing next to the posts they authored (instead of all “anonymous”).

Cool, though my plan is to purposely make them Anonymous 😉

With a couple of tweaks as we get closer to what I propose I think we can adapt your importer to use the code I have already written.

@adressler wrote…
Is it still possible to write a loop to link these old user accounts to the newly registered users even though there’s no email addresses? I’m okay with the forum working the way it is now, but if there’s a way to link newly registered accounts with the old ones, I’d sure like to try. I’m guessing the only way to do it is to match by the old user display_name, which, from what I can tell, is unique to the users db; no two people have the same name. It would just depend on people remembering the name they used to registered on the old site (some people are registered as “John and Jane Doe”). And if someone signs up who coincidentally has the same name as an old user, there could be some confusion.

As per what I wrote above, part of this is already built into bbPress and a little bit of testing in what we need to do to handle this properly then tweak the code we should be good.

@adressler wrote…
I just had a thought, what about a checkbox during registration like “Attempt to link my old DynamiteOnline account”, and when checked it would try to link up the names?

Maybe…

@robin-w wrote…
If auto the system won’t let you have two usernames the same, so you’d need to change your existing usernames to say have a character in front eg x_john smith (sql loop through user database would do that). Then brief your users to register with their existing username eg just john smith.

We don’t need to worry about this because each user would be Anonymous 😉

@robin-w wrote…
The real solution if you don’t have tons of users is to go for a manual registration process, you can of course let then use the same username, as you can then generate a user with a php programme straight into the database.

Let the users register whatever name they want as long as it is not already in use they will do the registration and sign up.

Automating this would be a pain for the high chance of abuse that could occur.

As there isn’t old email addresses for the old users verifying who’s who really should be a manual verification process in my opinion.

Nothing pretty or easy !

It took me ~7 months to come up with my solution with a few tips and advice from others who first floated the idea but I think it is relatively pretty now. 🙂

But a solution is there somewhere !

Yes it is 😉

I’ll go dig out now and have a quick read and post it shortly 🙂

[Stephen Edgar]

This started dues to an issue when importing from phpBB. If a user had depeted their own account on a phpBB forum, phpBB didn’t reattribute the post author to a different author, rather just removed the account from being able to login etc. phpBB stores the author name alongside each topic and reply so once said author has been deleted you have the option to also not delete the users topics or replies and when viewing said topic the original author’s name will still be attributed to the topic or reply.

The goal was then to bbPress support this as when you import from a phpBB if the user doesn’t exist then we wouldn’t create a new user and as seen above if the user doesn’t exist then each topic and reply is attributed to the ‘Anonymous’ user.

Over in bbPress Trac Ticket #2347 we came up with a solution.

If you take a look at the patch I currently have sitting there (Keep in mind the current ‘Example.php’ customised importer you are using)

https://bbpress.trac.wordpress.org/attachment/ticket/2347/2347.diff

The first part in the phpBB.php section I added parts to import the topics user name, the replies username and then added a flag on the user that was being imported _bbp_phpbb_user_type. That gets enough of the data that we need into the WordPress database to enable us to manipulate that once the data has been imported.

So at the moment if your Lotus DB has these details already attached to the topics and replies then adding this to your custom import would be the first step.

Firstly in regards to Anonymous users in bbPress, an anonymous user CAN have a username and/or display and this is stored in wp_postmeta table as _bbp_anonymous_name and is what my comments in my previous reply were alluding to.

Next up I added a new Repair Tool to basically post process this imported data, I create a query that joins all the topics data from wp_posts and wp_postmeta along with the topics old phpBB stored username. Once all that is in a SQL result table each topic that is by an ‘anonymous’ author is then updated to change the _bbp_anonymous_name associated with that particular topics store phpBB topic author name. Then the same process is repeated for each reply.

So now we have deleted, closed, spammed users that were deleted or deactivated in some way form the legacy phpBB forum but the topics and replies were never deleted to keep the context of the original discussions.

These are now all anonymous users in WordPress/bbPress but actually have the original display name/ user name keeping that same original context.

So that is my theory I was getting to, make each of your imported Lotus users an anonymous user using their old original user/display name to keep that context of conversation.

Now this is why you don’t need to worry about email addresses nor passwords as the accounts are not actual accounts just placeholders of the names from the original discussion.

The next part of the puzzle is when your users now sign up to reactivate and participate in the new bbPress they are free to create there account, choose their username, customise their profile, pick a password, use any email address they choose etc etc.

And the final part is now ‘Joe Blogs’ has created a new account on your site we just need a tool that will reattribute the posts currently under the anonymous user account ‘Joe Blogs’ to the now active user ‘Joe Blogs’ and the job is done. You can currently do just that with the standard WordPress User administration to delete a user and then reattribute that users posts to another user during the delete process. We just go and tweak that process to also allow/mimic/recreate that process to be able to reattribute the anonymous ‘Joe Blogs’ topics and replies to now active user ‘Joe Blogs’.

So how’s all that sound? Clear as mud?

I wrote another ‘post import’ repair tool yesterday that I had planned on integrating with the above tool today but time got away from me so hopefully I’ll have time in the morning to do this and will keep the scenario I outlined above in mind so it can be used for more than just imported phpBB forums and can be extended for other imported forums and custom imports.

[Robin W]

Awesome, very neat and clever !

[adressler]

Makes perfect sense. I will start to familiarize myself with your patch and massage it to fit my situation. Really looking forward to your new repair tool as well. Thanks so much Robin and Stephen for your time, it is very much appreciated! Thanks to you two, the end is finally in sight!

[Stephen Edgar]

I will update the patch later today with some tweaks that should make it compatible for your use, I’ll also update the Example.php importer to add the changes.