Advertisement:

Author Topic: UBB.Classic Converter (Prototype now available)  (Read 34265 times)

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
UBB.Classic Converter (Prototype now available)
« on: April 29, 2004, 06:32:01 PM »
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.
« Last Edit: May 23, 2004, 04:51:45 PM by Mike Bobbitt »

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: UBB Converter?
« Reply #1 on: April 29, 2004, 06:42:27 PM »
What you could do for now is try converting to phpbb then to SMF.
Liverpool FC Forum with 14 million+ posts.

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB Converter -- PROTOTYPE
« Reply #2 on: May 04, 2004, 03:23:29 PM »
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

Offline Oldiesmann

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 24,868
  • Gender: Male
  • Ask me about the function DB :)
    • oldiesmann on Facebook
    • Oldiesmann on GitHub
    • https://www.linkedin.com/in/michaeleshom on LinkedIn
    • @oldiesmann on Twitter
    • Archie Comics Fan Forum
Re: UBB Converter (Prototype now available)
« Reply #3 on: May 04, 2004, 10:46:09 PM »
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
Cincy Space - now open!

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #4 on: May 05, 2004, 10:52:16 AM »
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

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #5 on: May 06, 2004, 07:32:04 PM »
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:

Code: [Select]
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

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: UBB.Classic Converter (Prototype now available)
« Reply #6 on: May 06, 2004, 09:43:16 PM »
They must start at 1... you *cannot* have a topic or board with an id of 0.

-[Unknown]

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #7 on: May 06, 2004, 10:01:14 PM »
Ok, that's good to know... What about starting at a number > 1? Also bad?

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: UBB.Classic Converter (Prototype now available)
« Reply #8 on: May 06, 2004, 10:22:23 PM »
Nope, that's fine.  Missing numbers are okay too, as long as they are all above 0.

-[Unknown]

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #9 on: May 07, 2004, 02:30:06 PM »
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:

Quote
mysql_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!

Offline Grudge

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 10,742
  • Gender: Male
  • Unofficial nuisance
Re: UBB.Classic Converter (Prototype now available)
« Reply #10 on: May 07, 2004, 03:20:30 PM »
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...

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: UBB.Classic Converter (Prototype now available)
« Reply #11 on: May 07, 2004, 03:31:57 PM »
Actually, only TRUNCATE is needed but it's not in all versions of MySQL 3.23.x.

-[Unknown]

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #12 on: May 07, 2004, 03:36:06 PM »
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!!!

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #13 on: May 21, 2004, 09:41:22 PM »
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

Offline Grudge

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 10,742
  • Gender: Male
  • Unofficial nuisance
Re: UBB.Classic Converter (Prototype now available)
« Reply #14 on: May 23, 2004, 04:15:02 PM »
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...

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #15 on: May 23, 2004, 04:48:28 PM »
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

Offline Winters

  • Jr. Member
  • **
  • Posts: 266
  • Gender: Female
    • DykesVision. Reality-based.
Re: UBB.Classic Converter (Prototype now available)
« Reply #16 on: September 28, 2004, 01:13:18 PM »
Would this converter work with a UBB 6.2.1?

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #17 on: September 28, 2004, 01:26:11 PM »
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...

Offline Winters

  • Jr. Member
  • **
  • Posts: 266
  • Gender: Female
    • DykesVision. Reality-based.
Re: UBB.Classic Converter (Prototype now available)
« Reply #18 on: September 28, 2004, 01:36:42 PM »
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...)

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: UBB.Classic Converter (Prototype now available)
« Reply #19 on: September 28, 2004, 01:38:26 PM »
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...