powered by Jive Software

SQL for custom database integration (instead of using openfire.xml)

Hi,

I struggled for a bit getting the custom database integration (auth and user) working with openfire 3.6.4 (Mac OS X) and a mysql database. I was receiving errors about duplicate entries in the property table and xml config file, so i decided to ignore the customer integration custom database guide and just update the appropriate table (ofProperty). I chose to run sql commands as opposed to editing through the admin interface to make my life easier (now and later).

Here are the steps I took… maybe it will help someone else out.

  1. Performed clean install of openfire using mysql database

  2. Stopped openfire service

  3. Ran the sql listed below

  4. Started the openfire service

  5. Successfully logged in using data from another database

Here’s the sql (you’ll need to tweak a few things for your set up):

/* make modifications */

update ofProperty set propValue = ‘org.jivesoftware.openfire.auth.JDBCAuthProvider’ where name = ‘provider.auth.className’;

update ofProperty set propValue = ‘org.jivesoftware.openfire.user.JDBCUserProvider’ where name = ‘provider.user.className’;

/* connection */

insert into ofProperty (name, propValue) values (‘jdbcProvider.driver’, ‘com.mysql.jdbc.Driver’);

insert into ofProperty (name, propValue) values (‘jdbcProvider.connectionString’, ‘jdbc:mysql://localhost/[db_name]?user=[user_name]&password=[password]’);

/* authentication */

insert into ofProperty (name, propValue) values (‘jdbcAuthProvider.passwordSQL’, ‘select password from user where user_name=?’);

insert into ofProperty (name, propValue) values (‘jdbcAuthProvider.passwordType’, ‘plain’);

insert into ofProperty (name, propValue) values (‘admin.authorizedJIDs’, ‘some_user@some_jid, some_other_user@some_jid’);

/* user */

insert into ofProperty (name, propValue) values (‘jdbcUserProvider.loadUserSQL’, ‘select user_name, email from user where user_name=?’);

insert into ofProperty (name, propValue) values (‘jdbcUserProvider.userCountSQL’, ‘select count(*) from user’);

insert into ofProperty (name, propValue) values (‘jdbcUserProvider.allUsersSQL’, ‘select user_name from user’);

insert into ofProperty (name, propValue) values (‘jdbcUserProvider.searchSQL’, ‘select user_name from user where’);

insert into ofProperty (name, propValue) values (‘jdbcUserProvider.usernameField’, ‘user_name’);

insert into ofProperty (name, propValue) values (‘jdbcUserProvider.nameField’, ‘user_name’);

insert into ofProperty (name, propValue) values (‘jdbcUserProvider.emailField’, ‘email’);

I hope this helps!

–Marc