powered by Jive Software

Direct db changes/provisioning

I have the need to automate provisioning of many small workgroups of openfire users which have all peer contacts within their local workgroup already provisioned for them within a contact group when they first connect via Spark client; it’s an opt-out model rather than opt-in, a user must delete contacts rather than add them.

I’m using Asterisk-IM plugin and have successfully created the 1:1 mapping of user to phone within phoneDevice/phoneUser tables, and have created the ofRosterGroups entry for contact group in which I wish to place all the peers within the workgroup. What I can’t quite determine is rosterID column within ofRoster… or more generally what database changes are made when a client places a contact within a contact group.

I’ve found the database schema documentation but not how the tables/fields are used/related, is this documented anywhere?

Other than app changes that may happen as part of future openfire development what are the potential downsides of making these provisioning changes directly? actual operations issues rather than philosophical.

Perhaps I’m missing something more elegant such as openfire API in which I can do the same?? that would be awesomer

Thanks for any direction

Hey Steve,

When you add a roster entry via the Openfire code itself (and the same applies to things such as new MUC rooms etc), the Openfire code gets the next ID to use from consulting the ofID table. If you take a peek in that table for idType 18 (rosters) you’ll see that it has an ID in there. It’s really important that this value is updated if you add roster entries directly to the DB. The id in ofID for idType 18 should always be higher than the highest rosterID.

This is what we’ve found anyway from experience/looking at the source code and reading these forums.

Hi Steve,

Making changes directly to the database tables of a running instance of Openfire is generally not a good idea. Openfire uses an aggressive memory based caching mechanism to store various pieces of data, including roster data so changing the data from beneath the cache can lead to problems with data being out of sync. (Try searching the forums for “caching”, “database”, “insert” for past discussions on this issue.) Depending on your exact needs you might want to take a look at using the User Service plugin allows you to add/edit/delete users via http requests that work directly with the Openfire api.

Hope that helps,


I did find ofID and assumed that’s where the next index (rosterID) value would be derived but looked to me as if idType would be 26 matching rosterID of 18 which is next available based on current contact groups but see I have those reversed.

So it would appear that id for each idType is monotonitcally increasing value, is there no attempt to reclaim ids that have been used prior but since deleted? it is a bigint value so not likely to wrap anyway.

We are on the same page however, and glad to see I was able to figure out what was going on under the covers given my nascent exposure to mySQL

Thank you Ryan, that was what I was hoping to understand, not just the mechanics of making it work but the implications; I tried searching before posting but didn’t know the correct keywords. I have gone back to read the suggested conversations.

In looking at the UserService plugin it does not appear to provide the Contacts/Groups management that I require but will be very helpful for User mgmt, not sure how I missed it before since Asterisk-IM was so useful.

I’ll look further into using the api as designed, thanks again for your insightful feedback