Need to merge DB from phpBB. Importer tool maybe?

[Vedm]

Hi there! There are two forums that want to merge their content to become the single portal.

First one: SMF RC1 (going to be RC2)
664 users
9851 messages
1646 topics

Second one: phpBB 2.10
710 users
19088 messages

More than the half of users on these forums are the same, but we need to merge their logins.
I want to import the second forum to SMF, but I don't know how to keep safe all original content on the first one.

Anybody, please help! I don't like the idea of going back to phpBB and losing lots of posts. Has anyone merged such boards himself already, or maybe the developers can make an import converter, same to vBulletin's ImpEx tool?
(if one needs to see how vB ImpEx works just PM me)

[[Unknown]]

I'm afraid that merging is a bit difficult.

If you ONLY want to merge members, this won't be too hard.  What it will, however, require is that you install a new SMF, and convert phpBB to that SMF.  Then you'll have two tables of members you want to merge together, right?

After that, I can help you get the two merged together - again, assuming you only mean members.

I'm afraid that while I would like to make an "importer" for SMF to SMF, I don't currently have the time... because, for posts, it's quite a lot more complicated.

-[Unknown]

[Vedm]

I understand.
Okay, I would donate for that, but how long will it take to make the merging tool? BTW I don't know the cost of your time...

Also, by "merging members" I mean that some users have different nickname on the second forum, and I need to reassign their posts to the profiles of the first one quickly (or vice versa - that is more likely, because they have more messages on the second forum).

I can't afford more then a weekend of forum unavailability. Head admin of the second forum insists that phpBB is much better (oh my, LOL), and if I won't convince him by finishing migration fast... I don't like that kind of scenarios.

[packman]

If the users have different names on each forum how would you link them? If I registered on the first forum as 'chris' and the second forum as 'wonkydonkey' how do you know these are the same person. I assume that as users have re-registered for the second forum there isn't any guarantee that they have used the same email address, so you can't link them using that either.

[Vedm]

Most of the doubled users are using single email for both forums, even if they have different logins ('Altman' and 'Altman_ltd', for example). And we will try to ask 'em which account login to keep, too. If they intentionally kept different names and emails - it's their right. We will ask everybody to wipe out unused accounts few months later anyway.

[Vedm]

Waiting for [Unknown]'s answer ASAP...

[[Unknown]]

I don't have enough time to commit to ask you to pay me .

Code Select Expand

INSERT INTO database1.prefix1_members
   (ID_MEMBER, memberName, dateRegistered, posts, ID_GROUP, lngfile, lastLogin, realName,
   instantMessages, unreadMessages, im_ignore_list, passwd, emailAddress, personalText, gender, birthdate, websiteTitle, websiteUrl, location, ICQ, AIM, YIM, MSN, hideEmail, showOnline, timeFormat, signature, timeOffset, avatar, im_email_notify,
   karmaBad, karmaGood, usertitle, notifyAnnouncements, notifyOnce, memberIP, secretQuestion, secretAnswer, ID_THEME, is_activated, validation_code, ID_MSG_LAST_VISIT, additionalGroups, smileySet, ID_POST_GROUP, totalTimeLoggedIn, passwordSalt)
SELECT NULL, mem2.memberName, mem2.dateRegistered, mem2.posts, mem2.ID_GROUP, mem2.lngfile, mem2.lastLogin, mem2.realName,
   mem2.instantMessages, mem2.unreadMessages, mem2.im_ignore_list, mem2.passwd, mem2.emailAddress, mem2.personalText, mem2.gender, mem2.birthdate, mem2.websiteTitle, mem2.websiteUrl, mem2.location, mem2.ICQ, mem2.AIM, mem2.YIM, mem2.MSN,
   mem2.hideEmail, mem2.showOnline, mem2.timeFormat, mem2.signature, mem2.timeOffset, mem2.avatar, mem2.im_email_notify, mem2.karmaBad, mem2.karmaGood, mem2.usertitle, mem2.notifyAnnouncements, mem2.notifyOnce, mem2.memberIP,
   mem2.secretQuestion, mem2.secretAnswer, mem2.ID_THEME, mem2.is_activated, mem2.validation_code, mem2.ID_MSG_LAST_VISIT, mem2.additionalGroups, mem2.smileySet, mem2.ID_POST_GROUP, mem2.totalTimeLoggedIn, mem2.passwordSalt
FROM database2.prefix2_members AS mem2
   LEFT JOIN database1.prefix1_members AS mem1 ON (mem1.emailAddress = mem2.emailAddress)
WHERE mem1.ID_MEMBER IS NULL;

Backup first, may not work.  Basically, this will copy over every member who registered on one but not the other.  You'll want to do a recount afterward.

-[Unknown]

[Vedm]

As I see, this procedure will look for identical users' emails in DB, and if not found - transfer user from the second forum to the first one. Nice!

You won't get rid of me that fast, anyway.

I mean contributing for your time on boards/topics/PMs/usergroups/rights/whatever merging tool. How about that? Where, whom, how much?

Also check your PM.

[Vedm]

Please, [Unknown], tell me if and how can I buy your time for such a tool. Not only for me, but for the whole community to be able to use ever since (if such procedure isn't strictly a DB-up-close-and-personal).

(The current situation reminds me of old russian joke:
  - Doctor, please help! Everybody ignores me...
  - Who is the next one in line - come in!
)

[packman]

[Unknown] said...

I don't have enough time to commit to ask you to pay me

So I think you'll have a long wait, even though you're willing to pay. Don't forget that SMF is being developed by people with normal day jobs who spend their leisure hours developing it, so quality of life is probably just as important as money.

[Unknown] is correct in saying that it's not a trivial task to write an importer. I spent a lot of hours working on enhancing the phpBB2 converter and an importer has more complex logic to work out than a converter.

[Oldiesmann]

It is a difficult task. In order for SMF to run smoothly and quickly without overloading the server, a lot of tables have some sort of an ID value that links each entry to one or more entries in another table.

Example: ID_FIRST_MSG for each topic contains the ID_MSG value of the first message posted in that topic.

Basically, with that value, you can do something like this:

Code Select Expand

SELECT m.*, t.ID_FIRST_MSG FROM smf_messages AS m, smf_topics AS t WHERE t.ID_TOPIC = '8' && m.ID_MSG = t.ID_LAST_MSG

Without it, you'd have to do something like this:

Code Select Expand

SELECT m.* FROM smf_messages WHERE ID_TOPIC = '8' ORDER BY posterTime ASC LIMIT 1

Which would result in a lot more work on MySQL's part.

[Vedm]

[Unknown] said...

I don't have enough time to commit to ask you to pay me

So I think you'll have a long wait, even though you're willing to pay.

Whoops. Sorry, my English isn't as well as it may seem to be, maybe I misunderstood that phrase of [Unknown]'s.

[Unknown] is correct in saying that it's not a trivial task to write an importer. I spent a lot of hours working on enhancing the phpBB2 converter and an importer has more complex logic to work out than a converter.

Sad to hear this... Looks like there's no chance at all...

It is a difficult task. In order for SMF to run smoothly and quickly without overloading the server, a lot of tables have some sort of an ID value that links each entry to one or more entries in another table.

Maybe just add a 9999 before each of these IDs to make sure they won't interfere with existing in the birst board? I mean, like, if one ID was 5643 - make it 99995643. Or would this trick produce huge quantity of empty rows in DB? Don't blame me if it would, I'm not a programmer...

Edit: fixed some typos.

[[Unknown]]

Just adding 999's would not solve some of the more basic problems with appending data, and you'd still have to hunt down all the ID columns.

As I said, I do hope this can be done for SMF -> SMF at some point, but I'm afraid that right now I do not have the time to commit to it that it requires.  I have two jobs: this, and one I get paid much more handsomely for... both eat a lot of my time, and I still have to balance in enough time for myself that I stay sane.  I simply can't do it all and this, right now.... I'm sorry.

-[Unknown]

[Vedm]

Thanks, [Unknown], now I understand. I wish you having more free time combined with salary growth.

Well, there seems to be a temporary solution - install a free trialware version of vBulletin, run vB ImpEx tool, merge forums through it, and then import resulting DB to SMF.

Sounds a bit tricky... and existing legal trialware vB software versions are old, the version of ImpEx that I have just doesn't import SMF right - it loses post dates and usergroups arrangements. I don't even know if it is legal to ask someone to spare me a fresh ImpEx since all vB trial downloads are closed.

Maybe there are some bugs awaiting me furthermore... Got to crashtest it.