News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

UBB.Classic Converter (Prototype now available)

Started by Mike Bobbitt, April 29, 2004, 06:32:01 PM

Previous topic - Next topic

Mike Bobbitt

Hi all,

[Edited now that there's a download available]

I've written a script to convert UBB.Classic data (from the UBB exporter addd in 6.7.1) into SMF. You can download the script from:

http://perl.bobbitt.ca/ubb_to_smf/

If you give it a shot and have troubles, just let me know.

Ben_S

What you could do for now is try converting to phpbb then to SMF.
Liverpool FC Forum with 14 million+ posts.

Mike Bobbitt

Ok, I have a very rough prototype. I think it still has some issues to work out, but if anyone is willing to test it, I'd love the feedback:

http://perl.bobbitt.ca/ubb_to_smf/


Thanks

Oldiesmann

If I had access to a free copy of UBB, I'd try that out, but I'm not going to register for a "40 day trial" just to test out a convertor... One suggestion though - you should probably put a note up saying that that's for UBB Classic and not the php-based UBB Threads. This is just to clear up any confusion that people might have since there are two different versions of UBB now.
Michael Eshom
Christian Metal Fans

Mike Bobbitt

Thanks, good point. :) Yeah, I wouldn't expect anyone to build a forum just to test it out. If anyone does give it a shot, I'd be curious to hear your results. I managed to convert ~3000 users and 70,000 posts and so far all looks well. Still, lots of room for problems. :)


Cheers

Mike Bobbitt

Well, that may have been a bit premature... Everything converted ok, but the board is acting funny. Notably:

1. You can't post a new topic. You get:

You have an error in your SQL syntax near ')' at line 4
File: /home/bobbitt/www/army/forums/Sources/Post.php
Line: 1121


2. You can reply to an existing topic, but it won't show up in the forum until you check for (and fix) errors. The reply is there and can be viewed, it just won't "register" in the forum until this is done.

I suspect this is all because I'm converting topic/post IDs from UBB. They don't start at 1, and there are gaps in the numbers. Anyone know why that might be a problem, or if that's even it?


Thanks

[Unknown]

They must start at 1... you *cannot* have a topic or board with an id of 0.

-[Unknown]

Mike Bobbitt

Ok, that's good to know... What about starting at a number > 1? Also bad?

[Unknown]

Nope, that's fine.  Missing numbers are okay too, as long as they are all above 0.

-[Unknown]

Mike Bobbitt

Ok, I have more info on what's happening now... Hopefully someone can shed some light on *why* it's happening.

After a conversion, new posts are inserted into the messages table with an  ID_TOPIC of 0. This causes the next call of db_insert_id to return a value of -1693997287, which is clearly wrong.

This in turn causes the new topic not to have an entry in the topics table since the return value of db_insert_id is negative, that insert is skipped.

I found this about mysql_insert_id:

Quotemysql_insert_id() converts the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT, the value returned by mysql_insert_id()  will be incorrect. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

Since the topic ID fields are medium int, but mysql_insert_id returns int, will that cause a problem?

Or better yet, what have I done wrong! Why is the initial entry in the messages table inserting with a zero ID_TOPIC?


Thanks in advance for any help!

Grudge

You you definetly BOTH deleteing and truncating the contents of the table before you try and do any inserts? It may have messed up the next insert ID number and hence isn't giving you one...
I'm only a half geek really...

[Unknown]

Actually, only TRUNCATE is needed but it's not in all versions of MySQL 3.23.x.

-[Unknown]

Mike Bobbitt

Aha! I do a "DELETE FROM [table name]" to clean out existing entries, and since some of my early tests created bogus keys, I think that's it!

Will test and let you know.

Thanks!!!

Mike Bobbitt

Well, it looks like even my cut down topic/message IDs are too big for SMF to handle... :( For example the highest message ID in my test data turns out to be 2600970007 (seemingly valid but apparently too large...)

I've updated the script to "convert" message IDs to smaller numbers, but checking for uniqueness has caused the script to take a lot longer to run.

Anyone have a good suggestion as to how to proceed here?

(For example, a fast way to correllate UBB message IDs with smaller SMF message IDs...)


Thanks

Grudge

Personally - and I know this is going to be harder - I would just re-start the message ID's from scratch. Instead of using the old message ID's at all - just generate new ones. You'll have a much cleaner and more efficient database that way. The challange would be be inserting them in order as SMF sorts posts by ID_MSG and not time of post so they need to be in the correct order.

If it helps I've attached a possibly untested file for resorting ID_MSG's - it may help give you an idea of possible ways to do it - but I'd definetly start from scratch...

Note the file is from CVS which I hope Unknown won't mind me posting - oh well :/
I'm only a half geek really...

Mike Bobbitt

Thanks Grudge, I just finished doing that exact thing. The script now has a "preprocess" mode where it renumbers all messages and topics starting from 1. It takes a while to run (because it's not very efficient) but once it's done, you can just convert the data straight in.

And the best part: it works. :)


Thanks

Winters


Mike Bobbitt

Does 6.2.1 have the importer/exporter? The conversion script relies heavily on having exported UBB info. Not sure which UBB release that was added in...

Winters

I have no idea, it's not my forum, but they said their UBB version is 4 years old. (Of course it gets hacked and has internal server errors all the time...)

Mike Bobbitt

Hmmm. I'm pretty sure it was added more recently than 4 years ago, so there may not be much I can do to help here... Sorry...

Advertisement: