Manual mySQL inserts

Openfire Openfire Support
1

Hi,

I have some pretty big changes to make that would take like a month if I did it all manually… I’m writing a script to do it for me. I use a mySQL database and I figured if I just inserted into the databases it would work out.

So this is what I do. I add a user to a buddylist, and then I assign it a group to be in. That’s it.

I do like this,

insert into ofRoster (rosterID, username, jid, sub, ask, recv, nick) values (1603, ‘Amy’, ‘melissa@messenger.myserver.com’, 3, -1, 2, ‘Melissa’);

I take the last record in ofRoster and increment it by one for the rosterID there.

So, after doing that, I have to assign it a group. So I do like this:

insert into ofRosterGroups (rosterID, rank, groupName) values (1603, 0, ‘Sales’);

So now I’ve assigned melissa in Amy’s roster as being in the group ‘Sales’.

I figure this would be all I need, I looked in the other tables and I think this is it.

But when I go into the openfire admin console the changes aren’t there. So I restart openfire, still the changes aren’t there. I try to add one of them- it tells me the JID is already taken (From my script)

So what am I not updating??? What else do I need to do?

Thank you!!

2

Bumping this, because I really really need this

3

Update…

I found that in the ofID table, there’s a value that says what rosterID they’re on and they update it each time they add a new person to a roster. So now I update that too. There’s one other value that increases everytime I add someone in ofID, so I’m increasing that too. Still no dice.

I’ll keep trying… If someone could help me on this soon I would seriously appreciate it. I have managers breathing down my neck about this, it was supposed to be fixed yesterday. I’m sweating here. :’(

4

An update…

Okay so. When I update the database myself, I don’t see changes. However if I clear Openfire’s cache, I will see the changes I made.

But if I restart Openfire, Openfire deletes all the changes I’ve made in the database.

I’ve also tried doing it when openfire was offline etc… basically, whenever openfire starts, it will delete any changes I made to the SQL.

5

Just an idea. Why do you want to manipulate the behavior of your Jabber server by modifying the database? Maybe it would be easier to write a simple plug-in which does your task, or which offers a small subset of the already existing openfire api to your external application. See e.g. http://www.igniterealtime.org/community/message/147629#147629 (HTTP(S) Interface) or http://kraken.blathersource.org/node/5 (XML-RPC Interface).

6

Have you tried using the dbaccess plugin?

You will find it in the OF src tree. Read http://www.igniterealtime.org/fisheye/browse/svn-org/openfire/trunk/src/plugins/ dbaccess/readme.html?r=10807