XF 2.2 partial import from UBBThreads export

[BrianS]

I run an XF 2 forum (XF Forum) that has zero revenue. I've been given an SQL export file from a UBBThreads forum (UBB Forum) that also has no revenue or support and is on its last legs. Our intention is to import some of the data -- but not all of the data -- from the UBB Forum into my XF Forum.

UBBThreads is not one of the forums supported by the XF importers.

My plan has been to import the UBB Forum data into the MySQL database on the server that hosts my XF site and then create a set of migration processes that will update the XF forum with the desired info from the UBB forum. For test purposes, the XF forum has now been duplicated on a different host that's essentially invisible to the world. That's where we'll create and test whatever migration steps are needed. Once we're happy with those, we'll run the migration once against the production XF forum and, hopefully, be done.

As background, I have many years of experience creating conversions to move data from various other databases into Oracle databases, where both the target and host systems were both complex business systems. The process I'm envisioning here is a very simplified version of the processes I've used in the past. However, that does not mean that I'm not overlooking critical problems.

A couple of notes:

1. There is some overlap in the user population between the two forums. We only want to create new users for those who don't already exist in the XF forum.
2. We don't want to import all of the forums from the UBB Forum.
3. The UBB Forum did not allow users to post attachments or images so there is essentially no data beyond users, threads and posts.

Here's my plan:

1. Manually create the forum nodes needed in the XF Forum to replicate the forums that will be imported from the UBB Forum.
2. Identify users who do not currently exist in the XF Forum and create those users in the XF Forum.
4. Create threads in the XF Forum for all the threads we want to move from the UBB Forum.
5. Create posts in the XF Forum for all of the posts that are part of the UBB threads we are moving from the UBB Forum.

In the above, "create" means through the use of some combination of SQL queries to do direct table-to-table data movement or, if need be, exporting the data into temporary files, converting it as needed, and then importing those converted files into the XF Forum database.

Here are my questions:

1. Do-able?
2. Is there an easier way?
3. Is there documentation on the links between tables in the XF database (i.e., how the various user tables relate to each other)?
4. Is there some way to create new users from an external list of users (in any reasonable format)?

Thanks so much for any advice, recommendations, notes, warnings or any other comments whatsoever.

Brian

 

[briansol]

This may be quite costly, but it may be 'easier' if you purchase a vb license
https://www.vbulletin.com/docs/html/impex_module_dump as it has a ubb importer tool.
import ubb to vb
then import vb to xf

 

[BrianS]

This may be quite costly, but it may be 'easier' if you purchase a vb license
https://www.vbulletin.com/docs/html/impex_module_dump as it has a ubb importer tool.
import ubb to vb
then import vb to xf

Thanks, Brian.

Yeah, at least on the surface, that would be fairly costly. Creating an intermediate VB site would mean setting up another new host site and would get complicated pretty quickly, especially since I don't have any experience with VB.

I'm * hoping * that there's a much simpler solution here but I do very much appreciate the suggestion.

 

[duderuud]

There isn't I'm afraid. Using VB is the cheapest and easiest solutio in this case.

A custom converter is much more expensive (I know).

 

[BrianS]

Thanks.

It's possible that I'll end up going that route but, since I've got a test forum to play with at zero cost, I'll certainly experiment with the direct route first. In the next day or two, as I get a bit of time, I'll experiment with creating users programmatically. If I can accomplish that task, that will increase my confidence that I can get it all done since the only other tasks will be creating threads and posts.

In the end, it's just a relational database. It's possible that I won't be able to ferret out all of the links but it's an interesting challenge for right now. And my job is made easier by the fact that I only want to import users, threads and posts, rather than replicate an entire external forum.

I'll certainly update this thread as I progress. It's entirely possible that I'll end up throwing up my hands and going the vb approach but not until I've played with this for a bit.

 

[briansol]

it can probably mostly be done in mysql without the need to build any php or interface.

i would say,

clean the raw data first.
join on user name with the 2 data sets.
update the userid if it exists to the one on xf

bring in the users that aren't in the xf set

create any new forums to line up on xf
bring those ids via update into the old data (copy) for forumid
update posts with new thread id

import users to xf

get new userids.

populate userids into the old data (copy) for posts and threads

then you should be able to just bring in the posts table with some massaging.



I would say ignore/forget about anything else in the user profile. it's not worth bringing in their avatar or sig. they can update that easily.

 

[BrianS]

Thanks, Brian!

I had about about an hour free this morning so I decided to try creating a user programmatically. After an hour of investigation and playing around, I can create a user that is sort-of but not yet correct. The system will actually accept the user's request for a password reset and send an email but the user doesn't display correctly in the admin interface. I'm pretty sure I know why but I'm out of time right now. I'll have a chance to play with that later tonight. Playing around with this project is fun but I actually do have to get some work done.

The plan that you outlined above is almost identical to the steps that I'd jotted down for myself.

Thanks again!

 

[BrianS]

I decided early on that my first task would be to see if I could create a valid XF user through MySQL. If I couldn't do that, then I couldn't do any of it since everything is so completely user-dependent. I thought it would also give me a good first look at the XF database.

It took about an hour this morning and then about another hour tonight to get to where I can create a valid XF user programmatically. I've tested it and can log in and read threads and make posts with the user I created. The next step will be to create a user without a valid password but with a valid email. I'd like folks to be able to use the forgot password functionality to set a valid password for their account. I'm not sure that will work but it's worth a try.

About 45 minutes of the time I spent tonight was spent trying to figure out the format of the time and date values in the XF database. I've used PHP in the past for some personal projects, but not recently, and I don't have a huge amount of experience with MySQL. After a few attempts to create a similar value, I decided that I was just rambling through the woods so I looked into the XF source. It took a few minutes to figure out what XF is putting there and then a few minutes more to figure out how to create that in a MySQL insert statement. Like most things in programming, the answer is easy once you know where to look.

I'll probably be able to work more on this project over the weekend. Thanks again for the replies above.

 

[rouar]

Watching.

We stood up a separate read-only "archive forum" that is a UBBThreads 7.7 instance. That's another option you may have. It is very low maintenance, since we disallow new users and disallow new posts.

This solution works for us now, but it would be nice to integrate the data.

 

[BrianS]

Another quick update: I was able to generate a valid user with a 'valid' state and a valid email but no password. I then clicked on the Forgot Password link, entered the email and followed the normal process. Once I completed the process, I was logged into the forum and the user was able to do all of the normal things.

I consider this to be really good news because it means that I can create users who can then easily set their own passwords and log in without any complication.

The UBB forum I'm inputting data from has thousands of users. Finding a neat way to create all of the users who aren't currently registered for the XF forum is a big step forward. Next step will be to play with creating threads.

 

[BrianS]

Making progress.

I can only devote so much time to this project but it's coming along. Yesterday, I was reminded of my very first computer science professor, who died many years ago. One of his first lessons to a class of college freshmen was "a computer is not always the correct tool." His point was don't use a computer to solve a problem that you can much more easily solve with a different tool -- like a pencil.

While that may be less pointed than it was 50 years ago, it still holds. After creating thousands of new users programmaticallty, I moved on to creating categories and forums. This import is not a complete import of the UBBThreads forum -- there are parts we're not going to import -- and we're importing the data into an already existing forum. That means it's not an all-into-nothing import.

I originally expected to create the nodes and forums programmatically. The data structures in the XF database are pretty clear and not all that different from the corresponding records in the UBBT database. However, it appears that XF is updating other stores of information when a new node is added to the database and it might be difficult to replicate that functionality (or even call it).

That's when my old professor's advice came to mind. Instead of programmatically creating the nodes, I was able to add them manually using XF's admin tools. The whole process took well under an hour and I'll need to replicate that one more time for the production cut-over. Far easier and more reliable than taking the time to figure out how to do it programmatically. Choose your battles.

The next step will be convert UBBT topics into XF threads. So far, I don't see any real issues with that step but bears can hide behind trees.

I'll update this thread again when I have more to report.

 

[BrianS]

I just created around 20,000 threads. It took around 90 minutes to complete the record insertions, during which I began to wonder if the process had simply died.

There are no posts connected to the new threads yet but the newly-created records do have values for first_post_id, last_post_id, etc. because I've created post_ids in the UBBT posts table for the posts that will be created next.

At first glance, the threads look correct. The real test will come next when I create the posts. There are 145 thousand of them so that might take a long time. On the other hand, it may not. My uneducated guess is that there are background tasks going on when a thread is inserted into xf_thread and those processes are the reason why the inserts took so long. (Certainly, mysql can insert 20,000 records far faster than that.) If I'm lucky, there won't be as much overhead inserting posts. Time will tell.

 

[BrianS]

I just created 145,000 posts. The data looks good, the threads look good, the thread statistics look good but when I click on a thread I get an empty window with "Oops, we ran into some problems."

Clearly, I'm missing something. I either haven't posted required records to a table or have missed a link. It feels like progress, though and I feel like I'm getting close.

 

[BrianS]

Figured it out: there were two problems:

1) The first_post_id and last_post_id columns were zero in the thread table. I'd made a mental note to update those once the posts were loaded but obviously not mental enough. I updated those, which led me to

2) The export from the UBBT posts table was truncated. That wasn't obvious before but it's obvious now because a lot of threads refer to first posts that don't exist.

To test this, I went looking for older threads from the UBBT forum. As soon as I looked at an older thread, it looked great. All of the replies were there, they were in the right order and everything seemed okay. So now I need to contact the person who provided the post export and get a new, hopefully complete export.

Progress.

 

[BrianS]

I got a new export file with all of the posts from the UBBT forum. I deleted the XF posts that I'd created earlier and created new posts and then checked the threads. Everything looks pretty good. I can go in and post new posts to those threads, update existing posts, etc. and it all seems to work okay.

At this point, the conversion is almost done. I've got a little bit of clean-up to do on the posts and threads but that's mostly cosmetic to remove HTML from of the UBBT posts and replace it with XF code for the same things. I spent a half hour on that last night and got about half of it done so there's not that much more to do there.

I still have to import avatars from the UBBT forum. That means figuring out the differences between how UBBT and XF store avatars. I looked into that last night and think I understand it but I need to confirm that. One thing that surprised me is that all of the avatars in the existing XF forum are jpg files. It's hard to believe they were all JPGs when they were uploaded by users so I'm guessing that XF is converting other file formats to JPG. That's a little hard to believe, too, so I'll test it out by loading a PNG file for an avatar in the text XF system and see what I get.

I have the avatar files from the UBBT forum but I need to rename those files. UBBT stores an actual link to an avatar file so the filename can be any legal name. I was searching for the equivalent in XF and couldn't find it. I finally figured out that XT stores avatars in files that have a filename that consists of the user ID followed by ".jpg". There are several different folders that hold the avatar files. They have cryptic folder names: "h", "l","m","o", and "s". My initial guess was that they stood for "original", "high", "medium", "low" and "small" resolutions. Each of those folders has a folder "0" under it and those folders contain the actual avatar files.

User 1 will have an avatar file called "1.jpg" in each of those "0" folders. Looking at the resolutions of those files seems to confirm my original suspicion about the folder names.

The avatar files that I have are all reasonably small. Trying to create different versions of those with different resolutions would be a fair task so I'm going to try just creating the same avatar files and plunking them in each of those "0" folders and see what I get. I will report back.

 

[BrianS]

What I get is a blank avatar. LOL When I then upload the same avatar file for the same user, I get a working avatar but the file does not appear in any of those folders. XF must be storing the data someplace else but it's not obvious at all to me where that might be. Another mystery...

 

[BrianS]

Well, I learned something new just now: XF stores avatars in folders that appear to hold 1000 files max. An avatar file called 804.jpg will be stored in the '0' folder. A file called 1234.jpg will be stored in the '1' folder and a file called 11234.jpg will be stored in the '11' folder.

Progress.

 

[BrianS]

I created the required folders and copied a couple of avatar files to them. I then updated the avatar dates for those users and left the avatar dimensions at zero. When I tested it, the avatars showed up correctly. The only hitch in this is that the avatar files I'm uploading are quite a bit bigger than what XF would use so there's a tiny performance hit.

I can solve this by asking frequent users to just upload their own avatar again. That will reset the sizes and fix the issue. For a conversion, copying the current avatar files to the corresponding files in the XF folder structure is sufficient. Now, all I need to do is write a program to read all of the existing avatar files and rename them from uuu.jpg to xxx.jpg, where uuu was their UBBT user id and xxx is their new XF user id. Once I have the renamed files, I can just copy them to the XF folder structure. I'll also need to update the avatar date for users who actually do have avatars.

I think this is the last major step in this conversion. (Those are famous last steps so I should be careful what I write!) Overall, it's been a fun and not very difficult project and my total cost so far is $0 and a bit of my own time.

 

[BrianS]

As an aside, I've confirmed that XF avatars must be JPG files. Or, at the very least, other formats don't seem to work. XF must be converting them on upload, which is interesting.

 

[Kirby]

As an aside, I've confirmed that XF avatars must be JPG files. Or, at the very least, other formats don't seem to work. XF must be converting them on upload, which is interesting.

I don't know what youe are trying to do, but to me it seems like your are trying to import everything manually from scratch.
This is possible, but a lot of work, must likely also slow and error-prone - XenForo does have an importer framework that does a lot of work for you, you just have to use it.

XenForo definitly does not require avatars to be JPEG (if this was the case animated avatars wouldn't work at all), nor does it convert them to JPEG on upload.