XMPP 之Openfire的用户数据库整合(转)

来源：http://bbs.chinaunix.net/thread-3630163-1-1.html

openfire\documentation下的文档有比较详细的信息

如果手动一步步设置

删除默认，
首先停止正常运行的Openfire服务器

默认的库ofProperty的如下记录删除

provider.auth.classname

provider.user.classname

provider.group.classname

建立一个第三方的表

1. CREATE TABLE myUser (
3. username NVARCHAR(64) NOT NULL,
5. plainPassword NVARCHAR(32),
7. encryptedPassword NVARCHAR(255),
9. name NVARCHAR(100),
11. email VARCHAR(100),
13. creationDate CHAR(15) NOT NULL,
15. modificationDate CHAR(15) NOT NULL,
17. CONSTRAINT ofUser_pk PRIMARY KEY (username)
19. );
21. CREATE INDEX ofUser_cDate_idx ON myUser (creationDate ASC);
23. CREATE TABLE myGroupUser (
25. groupName NVARCHAR(50) NOT NULL,
27. username NVARCHAR(100) NOT NULL,
29. administrator INTEGER NOT NULL,
31. CONSTRAINT ofGroupUser_pk PRIMARY KEY (groupName, username, administrator)
33. );
35. CREATE TABLE myGroup (
37. groupName NVARCHAR(50) NOT NULL,
39. description NVARCHAR(255),
41. CONSTRAINT ofGroup_pk PRIMARY KEY (groupName)
43. );

复制代码

密码加密方式：

plain md5 sha1 sha256 sha512

密码采用明文：

<passwordSQL>SELECT plainPassword FROM myUser WHERE username=?</passwordSQL>

<passwordType>palin</passwordType>

密码采用HASH：

<passwordSQL>SELECT encryptedPassword FROM myUser WHERE username=?</passwordSQL>

<passwordType>sha256</passwordType>

如下是一个myUser 中admin数据[myUser 插入admin/**数据,必须有一个，否则再次启动时使用这个验证时就没法登录] admin
admin
8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918
admin
Admin@demo.com
2011-1-1      
2011-1-1      



配置文件
openfire.xml 加入如下内容

1. <!-- Use Custome Database for user/group
3.   openfire_src/documentation/docs :: db-integration-guide.html database.html database-guide.html
5. -->
7.   <jdbcProvider>
9.     <!--
11.     <driver>com.mysql.jdbc.Driver</driver>  
13.     <connectionString>jdbc:mysql://localhost:3306/of?user=root&amp;password=</connectionString>
15. -->
17.     <![CDATA[ SQL SERVER驱动 jtds.sourceforge.net/faq.html ]]>
19.     <driver>net.sourceforge.jtds.jdbc.Driver</driver>
21.     <connectionString>jdbc:jtds:sqlserver://localhost/cust;appName=cust;user=of;password=of</connectionString>
23.   </jdbcProvider>
25.   <admin/>
27.   <provider>
29.     <auth>
31.       <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
33.     </auth>
35.     <user>
37.       <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
39.     </user>
41.     <group>
43.       <className>org.jivesoftware.openfire.group.JDBCGroupProvider</className>
45.     </group>
47.   </provider>
49.   <jdbcAuthProvider>
51.     <passwordSQL>SELECT plainPassword FROM myUser WHERE username=?</passwordSQL>
53.     <passwordType>palin</passwordType>
55.   </jdbcAuthProvider>
57.   <jdbcUserProvider>
59.     <loadUserSQL>SELECT name,email FROM myUser WHERE username=?</loadUserSQL>
61.     <userCountSQL>SELECT COUNT(*) FROM myUser</userCountSQL>
63.     <allUsersSQL>SELECT username FROM myUser</allUsersSQL>
65.     <searchSQL>SELECT username FROM myUser WHERE</searchSQL>
67.     <usernameField>username</usernameField>
69.     <nameField>name</nameField>
71.     <emailField>email</emailField>
73.   </jdbcUserProvider>
75.   <jdbcGroupProvider>
77.     <groupCountSQL>SELECT count(*) FROM myGroup</groupCountSQL>
79.     <allGroupsSQL>SELECT groupName FROM myGroup</allGroupsSQL>
81.     <descriptionSQL>SELECT groupDescription FROM myGroup WHERE groupName=?</descriptionSQL>
83.     <userGroupsSQL>SELECT groupName FROM myGroupUser WHERE username=?</userGroupsSQL>
85.     <loadMembersSQL>SELECT username FROM myGroupUser WHERE groupName=? AND isAdmin='N'</loadMembersSQL>
87.     <loadAdminsSQL>SELECT username FROM myGroupUser WHERE groupName=? AND isAdmin='Y'</loadAdminsSQL>
89.   </jdbcGroupProvider>

复制代码

启动
再次启动后，使用admin可以登录进去

默认的数据库的[ofProperty]内容从配置文件导入了，openfire.xml恢复原来的形式，查看ofProperty name
propValue

   
jdbcAuthProvider.passwordSQL
SELECT plainPassword FROM myUser WHERE username=?

jdbcAuthProvider.passwordType
plain

jdbcGroupProvider.allGroupsSQL
SELECT groupName FROM myGroup

jdbcGroupProvider.descriptionSQL
SELECT groupDescription FROM myGroup WHERE groupName=?

jdbcGroupProvider.groupCountSQL
SELECT count(*) FROM myGroup

jdbcGroupProvider.loadAdminsSQL
SELECT username FROM myGroupUser WHERE groupName=? AND isAdmin='Y'

jdbcGroupProvider.loadMembersSQL
SELECT username FROM myGroupUser WHERE groupName=? AND isAdmin='N'

jdbcGroupProvider.userGroupsSQL
SELECT groupName FROM myGroupUser WHERE username=?

jdbcProvider.connectionString
jdbc:jtds:sqlserver://localhost/cust;appName=cust;user=of;password=of

jdbcProvider.driver
net.sourceforge.jtds.jdbc.Driver

jdbcUserProvider.allUsersSQL
SELECT username FROM myUser

jdbcUserProvider.emailField
email

jdbcUserProvider.loadUserSQL
SELECT name,email FROM myUser WHERE username=?

jdbcUserProvider.nameField
name

jdbcUserProvider.searchSQL
SELECT username FROM myUser WHERE

jdbcUserProvider.userCountSQL
SELECT COUNT(*) FROM myUser

jdbcUserProvider.usernameField
username

provider.auth.className
org.jivesoftware.openfire.auth.JDBCAuthProvider

provider.group.className
org.jivesoftware.openfire.group.JDBCGroupProvider

provider.user.className
org.jivesoftware.openfire.user.JDBCUserProvider