XF 2.2 partial import from UBBThreads export

BrianS

Member
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
 

BrianS

Member
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, your 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.

You're right: I'm importing everything from scratch. Why? Because:
  • There is no formal importer from UBB Threads to XF
  • I'm not importing the entire UBB Forum but instead only parts of it
  • I'm not importing the data into an empty XF forum but instead into a forum that has lots of data
  • There is an overlap between the users in both forums and it's important to merge the data from both forums into a consolidated whole in the XF forum.
  • A user may have different usernames on both forums and their content still needs to be merged together
I was not aware of the existence of any importer when I started this process. Even if there had been an importer, the complexity of merging the two databases may have made the use of a normal importer difficult. So I made a judgment call to do the conversion manually.

Had I not had lots of experience in the past converting complex data from one database to another, I might have not have made the same choice. Heck, I might not have been willing to begin the project at all. But my judgment was that this conversion, looked at in perspective, was really not that difficult. It mainly came down to converting users, threads and posts. Yes, there were forums and stuff to create but the bulk of the work has been on the users, threads and posts.

It's a testament to the clean design of XF that it has been really quite straightforward. The same can be said of UBB Threads and, in fact, I suspect that UBB Threads was one of the forums that inspired the design of XF. Many of the concepts and even many table and column names are the same. For my purposes, that made life easier.

I've been working on it very part-time for a couple of weeks and I'm almost done. From my perspective, as projects go, that's pretty fast. And, yes, I've made mistakes along the way but they've been easy to find and fix. There may still be some issues out there but it's looking pretty good. Figuring out how and where XF stored avatar data was actually harder than most other parts of the this conversion.

The only other way to do the job would have been to try to find an importer and then modify the importer to meet the needs of this particular project. I'd suggest kindly that process could easily have taken more work and more time than I've spent doing it by working with table-based data.

I believe you with regard to avatars. However, if I upload an avatar called 1.JPG, it works. If I upload an avatar called 1.GIF or 1.PNG, it doesn't work. For my purpose, that's good enough. I have 850 avatars to upload and about 100 were either GIFs or PNGs. It took about 10 minutes to get those all converted to JPGs. I then wrote a PHP script to get a list of the file names without the extension. I imported those into a new table in the database and added a column that would hold the corresponding new user ID. I updated that column and then downloaded a CSV containing the old file name and the new file name. One more simple PHP script and the avatars had the correct new names and could be copied to the XF forum folders.

My whole goal here was to have avatars loaded for folks who had avatars in the old forum. If folks log in and see familiar avatars and usernames in the forum, life will be less strange and more comfortable. If converting GIFs and PNGs to JPGs would make the process, I'm happy with that. :)

When I'm done, if someone else wants the scripts I've developed to convert the data from UBBT to XF, I'll be happy to share.
 

Kirby

Well-known member
There is no formal importer from UBB Threads to XF
That's obviously the reason why you have to build one yourself :)
  • I'm not importing the entire UBB Forum but instead only parts of it
  • I'm not importing the data into an empty XF forum but instead into a forum that has lots of data
  • There is an overlap between the users in both forums and it's important to merge the data from both forums into a consolidated whole in the XF forum.
  • A user may have different usernames on both forums and their content still needs to be merged together
The XenForo importer framework can handle all of this - that's what I wanted to point out.
I don't know your code, but to me it sounds like you are investing way too much time to get results.
A importer implementation for UBB.Threads using the XenForo importer franework could probably be completed in 2-3 days, not several weeks.
Furthermore this would allow to import at much higher speeds than 20K threads/90 minutes (on decent hardware).


Even if there had been an importer, the complexity of merging the two databases may have made the use of a normal importer difficult.
If you want to merge users by email (which is usually the only unique key available) - the XenForo importer framework does handle that.
Figuring out how and where XF stored avatar data was actually harder than most other parts of the this conversion.
The importer framework would have handled that for you without having to figure out where exactly avatars are being stored ;)
As you are doing this manually:
Did you resize the avatars to be exactly
  • 48x48 (s)
  • 96x96 (m)
  • 192x192 (l)
  • 384x384 (h and o)
If you do not create all variants (except h) you will most likely run into issues running this in production.
The only other way to do the job would have been to try to find an importer and then modify the importer to meet the needs of this particular project.
No :) IMHO the best solution would have been to implement an importer module for the existing framework, that would most likely hsve taken a lot less time than doing everything manually.
I believe you with regard to avatars. However, if I upload an avatar called 1.JPG, it works. If I upload an avatar called 1.GIF or 1.PNG, it doesn't work.
What exactly does "doesn't work" mean - do you get an error message? Which one exactly.

Avatars can be any supported image format (GIF, JPEG or PNG).

The only requirement (if you are doing everything manually) is that the files hsve the correct dimensions and are named <user_id>.jpg.
 

BrianS

Member
Perhaps we are looking at this differently because of different backgrounds.

When I looked at this, I could see almost immediately what steps were needed. It was clear at the beginning that a table-to-table conversion would be pretty straightforward. I expected some hiccups and got some but nothing particularly surprising or difficult.

On the other hand, writing an importer framework for a forum that I didn't have any technical knowledge of to import data from another forum that I had no technical knowledge of did not seem at all like an easy or quick project. Given my knowledge of the amount of work I've put in on this so far, I still believe I've chosen the easier path. Nevertheless, the path I've chosen has taught me a lot about the internals of XF. I call that a benefit.

With regard to speed, who cares how long a process takes if it's measured in minutes and will run exactly twice. :)

And "doesn't work" means that a file named "1.PNG" results in a blank avatar. "1.JPG" works fine. If you mean that a file that holds a PNG file but is named "1.JPG" will work, then that's fine but I don't particularly care for file names that describe data incorrectly. If a file is named "1.JPG", I'd prefer that it hold a JPG file. :)
 
Last edited:

Kirby

Well-known member
If you mean that a file that holds a PNG file but is named "1.JPG" will work, then that's fine but I don't particularly care for file names that describe data incorrectly. If a file is named "1.JPG", I'd prefer that it hold a JPG file. :)
Well, that's how XenForo does work; every avatar filename has a jpg extension, no matter which format it is actually.
If you do not like that you would have to create an Add-on to change that.
 

BrianS

Member
Well, that's how XenForo does work; every avatar filename has a jpg extension, no matter which format it is actually.
If you do not like that you would have to create an Add-on to change that.

Or do what I did do: take a very few minutes to convert the other image files into JPGs so the file extension accurately describes the data within.
 

Kirby

Well-known member
Or do what I did do: take a very few minutes to convert the other image files into JPGs so the file extension accurately describes the data within.
Yep, that does work for existing images if they do not contain (alpha) transparency or animation.

You might loose (a bit) image quality due to JPEG lossy compression though.

Last but not least it will only last as long as users do not upload new GIF or PNG avatars; if they do the files would still be .jpg but contain the respective format.
 
Last edited:

BrianS

Member
Got the avatars loaded after dinner tonight. Pretty easy and they look good. I'll encourage folks to upload new copies of their avatar and that will fix all of the size issues but they look pretty darned good right now and seem to work well in all of the contexts that I can think of. I'm pretty happy with that.
 

BrianS

Member
Test conversion is done. It looks good. Cutover to production will probably happen next week sometime.

It happened over a few weeks because of the holidays and just being busy on other things but it turned out to be a fun and easy project. Better, from my point of view, is that it taught me a great deal about the internals of XF. That's a pretty good bonus. :)

Kirby's points about the importer frameworks are worth paying attention to -- especially if there's an existing importer for whatever forum data is being imported from. But had I used (or built) an importer, I think it's likely that I would have learned less about the internal structure of XF.

Ying and yang. It's all good. :)
 

BrianS

Member
Just to close this out: the conversion went well and the merged forum is live and everyone seems to love the result. I'm pleased.

As I noted above, the clean design of XenForo's database and structure, along with that of UBB Threads, was what made this process straightforward, easy and fun. :)
 
Top