Hi All,
Just a small contribution for those who are interested in …
I’m running a small openfire server and it’s really anoying to explain new registred users how to add a contact or how to find/configure group chatroom.
So, I’ve written a small trigger on mysql to do the job :
-
It fill the ‘ofRoster’ table ( for contacts ),
-
Also fill the ‘ofPrivate’ ( for group chatroom ).
Please be indulgent, it’s my very very first trigger but any suggestion is welcome to improve this trigger.
========================================================================
DELIMITER $$
CREATE TRIGGER test BEFORE INSERT ON ofUser
FOR EACH ROW
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE nextId integer;
DECLARE myUserName CHAR(64);
DECLARE myFullName CHAR(100);
DECLARE cur1 CURSOR FOR SELECT username, name FROM ofUser;
DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000’ SET done = 1;
SELECT MAX(rosterID) INTO nextId FROM ofRoster;
SET nextId=nextId+1;
OPEN cur1;
REPEAT
FETCH cur1 INTO myUserName, myFullName;
IF NOT done THEN
INSERT INTO ofRoster VALUES (nextId, NEW.username, CONCAT(myUserName, ‘@example.com’), 3, -1, -1, CONCAT(UPPER(LEFT(myUserName, 1)), LOWER(SUBSTRING(myUserName, 2))) );
SET nextId=nextId+1;
INSERT INTO ofRoster VALUES (nextId, myUserName, CONCAT(NEW.username, ‘@example.com’), 3, -1, -1, CONCAT(UPPER(LEFT(NEW.username, 1)), LOWER(SUBSTRING(NEW.username, 2))) );
SET nextId=nextId+1;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
INSERT INTO ofPrivate VALUES (NEW.username, ‘storage’, ‘storage:bookmarks’, ‘’);
UPDATE ofID SET id=nextId WHERE idType=18;
END$$
DELIMITER ;
========================================================================
The only issue I have is that new contacts don’t display on existing users contact list until I restart the OpenFire daemon or I clear the roster cache.
Let me know if it’s usefull …
Regards,
KlinT.