Openfire Custom database integration steps

Instant messaging (IM) has enjoyed phenomenal success as a person-to-person communication tool; in some instances, it has supplanted email as the preferred means of online communication. Now, developers are using this technology for application-to-person and application-to-application communication.

There are too many IM servers which work with XMPP protocol. Some of them are paid/Enterprise and some of them are OpenSource.

We are using "Openfire" as a IM server for our projects. It is free, open source and java based. Except this advantages it has too many plugins which you can use for free. Openfire is a XMPP server and it has own client application with name "Spark".
You can simply install Openfire, then go to admin console (for example if your ip is 1.2.3.4 then admin page will be available as http://1.2.3.4:9090) and configure it to use with embedded database. But in a realtime you need to use your own databases as Oracle, PostgreSQL, MySQL and etc. for storing messages, settings and etc. Additionally you may need get users from your own/already existing database.
Lets note below informations which is useful:

1. If your domain is im.handysofts.com => 1.2.3.4:5222 then all your user account id will look like myusername@im.handysofts.com (example: vasif@im.handysofts.com, admin@im.handysofts.com and etc.)
2. If you want to reset Openfire confifuration and start again then do it as below

   # /etc/init.d/openfire stop
   # vi /opt/openfire/conf/openfire.xml
    <setup>false</setup>
   # /etc/init.d/openfire start
   

3. Log location is

   /opt/openfire/logs/

   For example /opt/openfire/logs/all.log is writes all actions errors, warn and etc.
4. All system properties are stored in ofproperty table and any changes will be affected after openfire restart

Now edit openfire.xml and set setup to false and restart openfire again. During web setup select custom database and write your db host, port username and password, select connection poool settings. This database settings allows openfire create it's tables on that database. After config complete you can check your database and you'll see new tables created at your DB (with "of" prefix).
Login with default admin user (if you have specified password during config use it or default pass is admin) and start changing System Properties - add below system properties if you want to use your own/already existing user database (it means you have already registered users and you want allow them to login to your messaging server using the same username and passwords which already exists)
Note: we are using PostgreSQL 9.4 as a database

1. jdbcProvider.driver=org.postgresql.Driver
2. jdbc:postgresql://localhost:5432/mydb?useUnicode=true&characterEncoding=UTF-8&user=myuser&password=mypass
3. provider.auth.className=org.jivesoftware.openfire.auth.JDBCAuthProvider
4. provider.user.className=org.jivesoftware.openfire.user.JDBCUserProvider
5. admin.authorizedJIDs=youradminuser@im.handysofts.com,oteradminusername@im.handysofts.com
6. jdbcAuthProvider.passwordSQL=SELECT your_password_col FROM users WHERE your_username_col=?
7. jdbcAuthProvider.passwordType=plain
8. jdbcUserProvider.loadUserSQL=SELECT full_name_col, email FROM users WHERE your_username_col=?
9. jdbcUserProvider.userCountSQL=SELECT COUNT(id_col) FROM users
10. jdbcUserProvider.allUsersSQL=SELECT your_username_col FROM users
11. jdbcUserProvider.searchSQL=SELECT your_username_col FROM users WHERE
12. jdbcUserProvider.usernameField=your_username_col
13. jdbcUserProvider.nameField=full_name_col
14. jdbcUserProvider.emailField=email

Now if you logout from admin console and restart openfire (Please check openfire logs also, so that should not be any error during start time, I mean you must check is openfire able to connect to your database and get required informations as users) and login with your custom admin users youradminuser or oteradminusername :) In Users/Groups tab you should be able to see your own users (which already was existing) and it means you can connect to your own openfire server with existing users using Spark.

If there will be something wrong with system properties and you will not be able to login using admin user then you can made changes on database (ofproperty table) and then restart Openfire try to login again

GOOD LUCK :)