XMPP 之Openfire的用户数据库整合

三里屯摇滚

XMPP 之Openfire的用户数据库整合

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

如果手动一步步设置

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

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

provider.auth.classname

provider.user.classname

provider.group.classname

建立一个第三方的表

1. CREATE TABLE myUser (
   
2. 
   
3. username NVARCHAR(64) NOT NULL,
   
4. 
   
5. plainPassword NVARCHAR(32),
   
6. 
   
7. encryptedPassword NVARCHAR(255),
   
8. 
   
9. name NVARCHAR(100),
   
10. 
    
11. email VARCHAR(100),
    
12. 
    
13. creationDate CHAR(15) NOT NULL,
    
14. 
    
15. modificationDate CHAR(15) NOT NULL,
    
16. 
    
17. CONSTRAINT ofUser_pk PRIMARY KEY (username)
    
18. 
    
19. );
    
20. 
    
21. CREATE INDEX ofUser_cDate_idx ON myUser (creationDate ASC);
    
22. 
    
23. CREATE TABLE myGroupUser (
    
24. 
    
25. groupName NVARCHAR(50) NOT NULL,
    
26. 
    
27. username NVARCHAR(100) NOT NULL,
    
28. 
    
29. administrator INTEGER NOT NULL,
    
30. 
    
31. CONSTRAINT ofGroupUser_pk PRIMARY KEY (groupName, username, administrator)
    
32. 
    
33. );
    
34. 
    
35. CREATE TABLE myGroup (
    
36. 
    
37. groupName NVARCHAR(50) NOT NULL,
    
38. 
    
39. description NVARCHAR(255),
    
40. 
    
41. CONSTRAINT ofGroup_pk PRIMARY KEY (groupName)
    
42. 
    
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
   
2. 
   
3.   openfire_src/documentation/docs :: db-integration-guide.html database.html database-guide.html
   
4. 
   
5. -->
   
6. 
   
7.   <jdbcProvider>
   
8. 
   
9.     <!--
   
10. 
    
11.     <driver>com.mysql.jdbc.Driver</driver>  
    
12. 
    
13.     <connectionString>jdbc:mysql://localhost:3306/of?user=root&amp;password=</connectionString>
    
14. 
    
15. -->
    
16. 
    
17.     <![CDATA[ SQL SERVER驱动 jtds.sourceforge.net/faq.html ]]>
    
18. 
    
19.     <driver>net.sourceforge.jtds.jdbc.Driver</driver>
    
20. 
    
21.     <connectionString>jdbc:jtds:sqlserver://localhost/cust;appName=cust;user=of;password=of</connectionString>
    
22. 
    
23.   </jdbcProvider>
    
24. 
    
25.   <admin/>
    
26. 
    
27.   <provider>
    
28. 
    
29.     <auth>
    
30. 
    
31.       <className>org.jivesoftware.openfire.auth.JDBCAuthProvider</className>
    
32. 
    
33.     </auth>
    
34. 
    
35.     <user>
    
36. 
    
37.       <className>org.jivesoftware.openfire.user.JDBCUserProvider</className>
    
38. 
    
39.     </user>
    
40. 
    
41.     <group>
    
42. 
    
43.       <className>org.jivesoftware.openfire.group.JDBCGroupProvider</className>
    
44. 
    
45.     </group>
    
46. 
    
47.   </provider>
    
48. 
    
49.   <jdbcAuthProvider>
    
50. 
    
51.     <passwordSQL>SELECT plainPassword FROM myUser WHERE username=?</passwordSQL>
    
52. 
    
53.     <passwordType>palin</passwordType>
    
54. 
    
55.   </jdbcAuthProvider>
    
56. 
    
57.   <jdbcUserProvider>
    
58. 
    
59.     <loadUserSQL>SELECT name,email FROM myUser WHERE username=?</loadUserSQL>
    
60. 
    
61.     <userCountSQL>SELECT COUNT(*) FROM myUser</userCountSQL>
    
62. 
    
63.     <allUsersSQL>SELECT username FROM myUser</allUsersSQL>
    
64. 
    
65.     <searchSQL>SELECT username FROM myUser WHERE</searchSQL>
    
66. 
    
67.     <usernameField>username</usernameField>
    
68. 
    
69.     <nameField>name</nameField>
    
70. 
    
71.     <emailField>email</emailField>
    
72. 
    
73.   </jdbcUserProvider>
    
74. 
    
75.   <jdbcGroupProvider>
    
76. 
    
77.     <groupCountSQL>SELECT count(*) FROM myGroup</groupCountSQL>
    
78. 
    
79.     <allGroupsSQL>SELECT groupName FROM myGroup</allGroupsSQL>
    
80. 
    
81.     <descriptionSQL>SELECT groupDescription FROM myGroup WHERE groupName=?</descriptionSQL>
    
82. 
    
83.     <userGroupsSQL>SELECT groupName FROM myGroupUser WHERE username=?</userGroupsSQL>
    
84. 
    
85.     <loadMembersSQL>SELECT username FROM myGroupUser WHERE groupName=? AND isAdmin='N'</loadMembersSQL>
    
86. 
    
87.     <loadAdminsSQL>SELECT username FROM myGroupUser WHERE groupName=? AND isAdmin='Y'</loadAdminsSQL>
    
88. 
    
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

清风鸟儿

有爱的楼主  学习鸟