Openfire集成现有系统数据库用户

CREATE TABLE `users` (
  `uid` varchar(64) NOT NULL,
  `pwd` varchar(32) DEFAULT NULL,
  `name` varchar(100) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `groups` (
  `groupName` varchar(50) NOT NULL,
  `description` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`groupName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `groupusers` (
  `groupName` varchar(50) NOT NULL,
  `uid` varchar(100) NOT NULL,
  `admin` tinyint(4) NOT NULL,
  PRIMARY KEY (`groupName`,`uid`,`admin`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

;
;//-----------------------------------------------------------------------------------------------------------
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.auth.JDBCAuthProvider' WHERE name='provider.auth.className';
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.group.JDBCGroupProvider' WHERE name='provider.group.className';
UPDATE `openfire`.`ofProperty` SET propValue='org.jivesoftware.openfire.user.JDBCUserProvider' WHERE name='provider.user.className';

INSERT INTO `openfire`.`ofProperty` VALUES
 ('jdbcProvider.driver', 'com.mysql.jdbc.Driver'),
 ('jdbcProvider.connectionString', 'jdbc:mysql://localhost:3306/office?user=root&password=123456'),
 ('admin.authorizedJIDs', 'admin@localhost,[email protected]'),
 ('jdbcAuthProvider.passwordSQL', 'SELECT pwd FROM users WHERE uid=?'),
 ('jdbcAuthProvider.passwordType', 'plain'),
 ('jdbcUserProvider.allUsersSQL', 'SELECT uid FROM users'),
 ('jdbcUserProvider.loadUserSQL', 'SELECT name,email FROM users WHERE uid=?'),
 ('jdbcUserProvider.userCountSQL', 'SELECT COUNT(*) FROM users'),
 ('jdbcUserProvider.searchSQL', 'SELECT uid FROM users WHERE'),
 ('jdbcUserProvider.usernameField', 'uid'),
 ('jdbcUserProvider.nameField', 'name'),
 ('jdbcUserProvider.emailField', 'email');
 ('jdbcGroupProvider.allGroupsSQL', 'SELECT groupName FROM groups'),
 ('jdbcGroupProvider.descriptionSQL', 'SELECT description FROM groups WHERE groupName=?'),
 ('jdbcGroupProvider.groupCountSQL', 'SELECT COUNT(*) FROM groups'),
 ('jdbcGroupProvider.loadAdminsSQL', 'SELECT uid FROM groupusers WHERE groupName=? AND admin=1'),
 ('jdbcGroupProvider.loadMembersSQL', 'SELECT uid FROM groupusers WHERE groupName=? AND admin=0'),
 ('jdbcGroupProvider.userGroupsSQL', 'SELECT groupName from groupusers WHERE uid=?');
;//-----------------------------------------------------------------------------------------------------------