Import Buddy list from an existant Mysql database

Openfire Openfire Support
1

Hello there, I’m very fond of this software, thanks to the whole team.

I have a little problem, I run a site with ~360k buddies. this table lists the friendships already made on the site.

what I’d like to give to my users is the possibility to see the list of buddies preinstalled upon login.

(that part is already fixed via openfire.xml)

is there any way to accomplish this?

id
uid_asking
uid_asked
status
question
answer
ack
date_save
delayed
delayed_answer
lckd
2
66
106
ok
false
0000-00-00 00:00:00
false
false
0

this is an exctract of the table, any ideas?

I wouldn’t like to create a view with ~360k * 2 rows as result.

thanks!

2

here is the database schema of openfire

http://www.igniterealtime.org/builds/openfire/docs/latest/documentation/database -guide.html

hope this helps.

3

INSERT INTO openfire.jiveRoster (

SELECT xx.uid AS rosterID, xx.username AS username, CONCAT( yy.username, ‘@more.cc’ ) AS jid, 3, -1, -1, yy.username AS nick
FROM users_friends
INNER JOIN users xx ON xx.uid = users_friends.uid_asking
INNER JOIN users yy ON yy.uid = users_friends.uid_asked
WHERE users_friends.status = ‘ok’
)
UNION (

SELECT yy.uid AS rosterID, yy.username AS username, CONCAT( xx.username, ‘@more.cc’ ) AS jid, 3, -1, -1, xx.username AS nick
FROM users_friends
INNER JOIN users xx ON xx.uid = users_friends.uid_asking
INNER JOIN users yy ON yy.uid = users_friends.uid_asked
WHERE users_friends.status = ‘ok’
)

this is giving me problem with the rosterID which is unique.

so, you think that this is the only way to have the buddy list precompiled?

regards.

4

this is giving me problem with the rosterID which is unique.
As far as I know, rosterID is just an continuous number, to have an clear identification for each entry. It’s only used to groups contacts in your roster. Since you seem not to have any groups in your exiting database, maybe you can simply set this column temporarily to auto_increment? (not thought long about that)

However, when SQL-commands are not sufficient for some reason, you could also write some kind of plugin for Openfire, which imports that data.

5

Yes!

that it exactly what I’ve did.

seems to be working.

I will have a “federated” table importing a view from the real database, this way buddies will always be correct.

regards!