openfire + sqlserver ，以及现有系统的账户集成

我要说的重点就是

 

1、安装完成之后，openfire的控制台不需要进行任何配置，下面所有的配置可以通过数据库脚本直接搞定。

2、那个conf文件夹里面，也不需要进行任何修改！

 

总的来讲，如果你要集成现有账户：

只需要修改ofProperty这个表就行了，配置方面不用作其他修改！！！

 

ONLY DATABASE!!!

ONLY DATABASE!!!

ONLY DATABASE!!!    重要的事情说3遍！！

 

然后，我的操作视频在：http://v.youku.com/v_show/id_XMTg1NDgzNzc3Mg==.html

 

话不多说，直接拿源代码去吧。记得修改你自己的。

 

 

-- ///	先停止OpenFire服务器   /
BEGIN TRANSACTION
USE [JetOpenFire_D]
GO

--  首先修改三个属性(openfire提供了支持JDBC相关的Provider, 可以从其它的数据源获取用户/组数据)：
UPDATE ofProperty SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';
UPDATE ofProperty SET propValue='org.jivesoftware.openfire.group.JDBCGroupProvider' WHERE name='provider.group.className';
UPDATE ofProperty SET propValue='org.jivesoftware.openfire.user.JDBCUserProvider' WHERE name='provider.user.className';

--接着配置数据源（添加系统属性, 这里使用的是sql server数据库）：
INSERT INTO ofProperty VALUES ('jdbcProvider.driver', 'net.sourceforge.jtds.jdbc.Driver')
	DELETE ofProperty WHERE name='jdbcProvider.connectionString'
INSERT INTO ofProperty VALUES ('jdbcProvider.connectionString', 'jdbc:jtds:sqlserver://192.168.1.27:1433/JetCommon_D;user=sa;password=jetmaster;instance=sql2008')


--  

--=== 三个Auth Provider ===--
--/先删除ofProperty的相关条目
DELETE ofProperty WHERE name ='jdbcAuthProvider.passwordSQL'
DELETE ofProperty WHERE name ='jdbcAuthProvider.passwordType'
DELETE ofProperty WHERE name ='admin.authorizedJIDs'

--JDBCUserProvider 
DELETE ofProperty WHERE name ='jdbcUserProvider.allUsersSQL'
DELETE ofProperty WHERE name ='jdbcUserProvider.loadUserSQL'
DELETE ofProperty WHERE name ='jdbcUserProvider.userCountSQL'
DELETE ofProperty WHERE name ='jdbcUserProvider.searchSQL'
DELETE ofProperty WHERE name ='jdbcUserProvider.usernameField'
DELETE ofProperty WHERE name ='jdbcUserProvider.nameField'
DELETE ofProperty WHERE name ='jdbcUserProvider.emailField'

--JDBCGroupProvider
DELETE ofProperty WHERE name ='jdbcGroupProvider.allGroupsSQL'
DELETE ofProperty WHERE name ='jdbcGroupProvider.descriptionSQL'
DELETE ofProperty WHERE name ='jdbcGroupProvider.groupCountSQL'
 
DELETE ofProperty WHERE name ='jdbcGroupProvider.loadAdminsSQL'
DELETE ofProperty WHERE name ='jdbcGroupProvider.loadMembersSQL'
DELETE ofProperty WHERE name ='jdbcGroupProvider.userGroupsSQL'


--/再插入更改的新条目
--JDBCAuthProvider 
INSERT INTO ofProperty VALUES ('jdbcAuthProvider.passwordSQL', 'SELECT Password FROM UserAccount WHERE ObjectName=?')
INSERT INTO ofProperty VALUES ('jdbcAuthProvider.passwordType', 'plain')

--JDBC User Provider 
INSERT INTO ofProperty VALUES ('jdbcUserProvider.allUsersSQL', 'SELECT ObjectName FROM UserAccount')
INSERT INTO ofProperty VALUES ('jdbcUserProvider.loadUserSQL', 'SELECT ObjectName,ObjectName FROM UserAccount WHERE ObjectName=?')
INSERT INTO ofProperty VALUES ('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM UserAccount')
INSERT INTO ofProperty VALUES ('jdbcUserProvider.searchSQL', 'SELECT ObjectName FROM UserAccount WHERE')
INSERT INTO ofProperty VALUES ('jdbcUserProvider.usernameField', 'ObjectName')
INSERT INTO ofProperty VALUES ('jdbcUserProvider.nameField', 'ObjectName')
INSERT INTO ofProperty VALUES ('jdbcUserProvider.emailField', 'ObjectName')

--JDBC Group Provider
INSERT INTO ofProperty VALUES ('jdbcGroupProvider.allGroupsSQL', 'SELECT GroupName FROM EmpGroups')
INSERT INTO ofProperty VALUES ('jdbcGroupProvider.descriptionSQL', 'SELECT [description] FROM EmpGroups WHERE GroupName=?')
INSERT INTO ofProperty VALUES ('jdbcGroupProvider.groupCountSQL', 'SELECT COUNT(*) FROM EmpGroups')
 
INSERT INTO ofProperty VALUES ('jdbcGroupProvider.loadAdminsSQL', 'SELECT ObjectName FROM GroupUsers WHERE GroupName=? AND admin=1')
INSERT INTO ofProperty VALUES ('jdbcGroupProvider.loadMembersSQL', 'SELECT ObjectName FROM GroupUsers WHERE GroupName=? AND admin=0')
INSERT INTO ofProperty VALUES ('jdbcGroupProvider.userGroupsSQL', 'SELECT GroupName from GroupUsers WHERE ObjectName=?')

 
-- 插入控制台管理员账户
-- 
-- 这里要注意，是 【服务器名称】，是在管理界面看到的那个服务器名称
-- 不是admin@127.0.0.1，不是admin@localhost，不是admin@local
-- 应该就是你先安装的时候填写的服务器名称，具体可以在服务器设置里面看到。
INSERT INTO ofProperty VALUES ('admin.authorizedJIDs', 'admin@192.168.1.27')


--//对现有系统的表进行修改//

USE [JetCommon_D]
GO

--增加一个EmpGroups表.表示人员组
CREATE TABLE EmpGroups (
	GroupName NVARCHAR(50) PRIMARY KEY NOT NULL,
	[description] NVARCHAR(255),
);

--再增加一个GroupUsers表，把管理员添加进来.
CREATE TABLE GroupUsers (
	GroupName NVARCHAR(50) NOT NULL,
	[ObjectName] NVARCHAR(100) NOT NULL,
	[admin] int
);

-- 插入一个管理员用户，其实在系统初始化的时候，这个admin管理员就可能已经增加进去了.
-- 系统里面应该有一个默认部门(就是系统的根部门，ID=1)
--
--    这一步你要做，我注释掉是因为我现有数据库里面已经有了这个admin的账户了。
--
-- INSERT INTO [JetCommon_D].[dbo].[Employee]
--            ([DeptId]
--            ,[EmployeeNO]
--            ,[ObjectName]
--            ,[StatusId]
--            ,[PostId]
--            ,[PostRank]
--            ,[Sex]
--            ,[JoinTime]
--            ,[EmployeeType]
--            ,[ManageEmployeeGroupId]
--            ,[IsSync]
--            ,[SortIndex])
--      VALUES
--            (1
--            ,'admin'
--            ,'系统管理员'
--            ,1
--            ,0
--            ,0
--            ,1
--            ,'2016-08-31'
--            ,1
--            ,0
--            ,0
--            ,0)
-- GO
-- INSERT INTO [JetCommon_D].[dbo].[UserAccount]
--            ([EmployeeId]
--            ,[ObjectName]
--            ,[Password]
--            ,[LastLogin]
--            ,[LastChangePassword])
--      VALUES
--            ((SELECT Id FROM [JetCommon_D].[dbo].Employee WHERE EmployeeNO='admin')
--            ,'admin'
--            ,'888888'
--            ,GETDATE()
--            ,GETDATE()
--            )
-- GO

--插入一个管理组并将admin加入到管理组
INSERT INTO EmpGroups VALUES ('admin','administrators')
INSERT INTO GroupUsers VALUES ('admin','admin',1);

COMMIT TRANSACTION
--自动OpenFire服务器/