MySql Custom User Table Integration

Openfire Openfire Support
1

Hi,

I want to integrate OpenFire with my Mysql database. I have a few thousand of users and I want them to communicate over my server.

I can say documentation about this issue at the site is poor.

Do I need something else below? I have users table consists of userName,password,email,firstName,lastName columns.

openfire.xml----

2

Hi,

you still need the Openfire database, did you remove this section completely in your configuration file?

LG

3

That is all content of my xml file.

Should I just append my lines starting from <provider> ?

Or should I remove <database> tag also?

4

Hi,

the end of you openfire.xml file should look like

<connectionProvider>     <className>org.jivesoftware.database.EmbeddedConnectionProvider</className>   </connectionProvider>    <setup>true</setup>  </jive>

after the installation, depending on your connection provider. To add the user integration one should add the custom things after “” so it looks like

<connectionProvider>     <className>org.jivesoftware.database.EmbeddedConnectionProvider</className>   </connectionProvider>    <setup>true</setup>
  <jdbcProvider>     <driver>org.hsqldb.jdbcDriver</driver>      <connectionString>jdbc:hsqldb:hsql://localhost/mydb</connectionString>   </jdbcProvider>    <provider>     <auth>       <className>org.jivesoftware.wildfire.auth.JDBCAuthProvider</className>     </auth>   </provider>    <jdbcAuthProvider>     <passwordSQL>SELECT password FROM myUsers WHERE username=?</passwordSQL>      <passwordType>plain</passwordType>   </jdbcAuthProvider>  </jive>

.

LG

5

Thanks, I solved the problem.

I am posting my configuration file for other people troubled with this issue.

Database name: mydatabase

Columns of users table named ‘myuser’:

-myusername

-mypassword : stored plain

-email

-firstname

-lastname

openfire.xml:

<?xml version="1.0" encoding="UTF-8"?>
<jive> <adminConsole> <port>9090</port>  <securePort>9091</securePort> </adminConsole>  <locale>en</locale>  <connectionProvider> <className>org.jivesoftware.database.DefaultConnectionProvider</className> </connectionProvider>  <database> <defaultProvider> <driver>com.mysql.jdbc.Driver</driver>  <serverURL>jdbc:mysql://localhost:3306/mb</serverURL>  <username>root</username>  <password>passw0rd</password>  <minConnections>5</minConnections>  <maxConnections>15</maxConnections>  <connectionTimeout>1.0</connectionTimeout> </defaultProvider> </database>  <setup>true</setup>  <jdbcProvider> <driver>com.mysql.jdbc.Driver</driver>  <connectionString>jdbc:mysql://localhost/mydatabase?user=root&password=passw0rd</connectionString> </jdbcProvider>  <provider> <auth> <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className> </auth>  <user> <className>org.jivesoftware.openfire.user.JDBCUserProvider</className> </user> </provider>  <jdbcAuthProvider> <passwordSQL>SELECT mypassword FROM myuser WHERE myusername=?</passwordSQL>  <passwordType>plain</passwordType> </jdbcAuthProvider>  <jdbcUserProvider> <loadUserSQL>SELECT CONCAT(firstname,' ',lastname),email FROM myuser WHERE myusername=?</loadUserSQL>  <userCountSQL>SELECT COUNT(*) FROM myuser</userCountSQL>  <allUsersSQL>SELECT myusername FROM myuser</allUsersSQL>  <searchSQL>SELECT myusername FROM myuser WHERE</searchSQL>  <usernameField>myusername</usernameField>  <nameField>CONCAT(firstname,' ',lastname)</nameField>  <emailField>email</emailField> </jdbcUserProvider>  <admin> <authorizedUsernames>admin,testuser</authorizedUsernames>
</admin> </jive>

Message was edited by: aliok.tr