Custom Database Integration with Openfire 3.6.4

  According to client's requirement, we need to integrate IM with our exsiting website. And it appears that openfire is a better choice after a short research. So the key point of using openfire is to integrating with our exsiting system.

  Based on the official documentation of openfire, integration with a custom database should be pretty simple which only requires a few changes in conf/openfire.xml. However, it turns out that it doesn't work at all. We tried very hard for trying to figure out why it didn't work even though we're following the documentation guide. Someone said that updating openfire.xml only works for 3.5.x not for 3.6.4. You have to configure it via admin console or directly insert the property-value pair into the table. What? Why it makes more difficult to customize?

  Unfortunately, even though we spent much time on inserting all properties into the table openfire still didn't work as we expected. But we're still lucky since openfire is open source. The last way or may be the best way is to reading the source code. We're lucky but not lucky enough because we're not JAVA developer. It's really a pain of ass to debug JAVA program(sorry, no intention to offende java developers) for a developer who is more familar with script languages.

  Ok, let's start the key part.

  We assumed that you already set up openfire correctly. Before you do any changes on configuration you should be able to sign in to the admin console by following the installation guide(default is: 127.0.0.1:9090).

  The key point of making your configuration take effective is to guarantee that all propery-value pairs in openfire.xml exactly the same as them in table 'ofProperty ' (including spaces and casesensitive).

•  Find JiveGlobals class in org.jivesoftware.util package

           comment line

              in deleteXMLProperty method(It should be easily found in Eclipse.)
   Why we need to comment the code?

   It will delete some properties you edited in conf/openfire.xml which means once you restart openfire some properties you added will no longer be there and all your changes lost. And it will result in an error like "Property 'xxxxx' as speficied in openfire.xml differs from what is stored in the database. Please make property changes in the database instead of openfire.xml" . It says so, but you still have to make changes on both.

• Changes properties both on openfire.xml and database.

          It's an example base on MySQL DB which you can found in the official documentation.

        1) openfire.xml

           <jive> ... <jdbcProvider> <driver>com.mysql.jdbc.Driver</driver> <connectionString>jdbc:mysql://localhost/dbname?user=username&password=secret</connectionString> </jdbcProvider> <provider> <auth> <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className> </auth> </provider> <jdbcAuthProvider> <passwordSQL>SELECT password FROM user_account WHERE username=?</passwordSQL> <passwordType>plain</passwordType> </jdbcAuthProvider> <jdbcUserProvider> <loadUserSQL>SELECT name,email FROM myUser WHERE username=?</loadUserSQL> <userCountSQL>SELECT COUNT(*) FROM myUser</userCountSQL> <allUsersSQL>SELECT username FROM myUser</allUsersSQL> <searchSQL>SELECT username FROM myUser WHERE</searchSQL> <usernameField>username</usernameField> <nameField>name</nameField> <emailField>email</emailField> </jdbcUserProvider> <admin> <authorizedUsernames>joe, jane</authorizedUsernames> </admin> ... </jive>

      2) And insert all propery-value pairs into database according to you settings in openfire.xml

  If you finished the step, congratulations you're almost there.

  Here are some issues you may have:

 

  1. If you still got the error even though you're pretty sure the value you set in openfire.xml is the same as the one in database: "Property 'xxxxx' as speficied in openfire.xml differs from what is stored in the database. Please make property changes in the database instead of openfire.xml"
     First, check your database CHARACTER SET . The default one is 'latin1', you have to change it to utf-8.
     Then double check the value in database, if it has an extra white space or typo there.

  2. For some reason we got an error "UnsupportedOperationException();" while we're tring to login admin console with an valid user in our database. That's because we changed  <loadUserSQL>SELECT name,email FROM myUser WHERE username=?</loadUserSQL> to

<loadUserSQL> SELECT name FROM myUser WHERE username=?</loadUserSQL> Method 'loadUser ' will be called when you login and it will read name, email based on the loadUserSQL. The exception is thrown out since it's not able to read the value of email. So please be careful if you want to change the standard statement.

   That's all for now. We've not finish all the integration with custom database. But at least we're able to login admin console with our existing users and you'll feel excited to found out that all users in myUser table will be listed on 'Users' tab on admin console.