I'm writing a chat module for a PHP/MySQL user-driven site that allows two users to make friends, and have chosen eJabberd for the chat system.
I've set up external authentication successfully using a PHP daemon, and now I've successfully managed to get the friendships in to eJabberd by using mod_roster_odbc
and populating the MySQL rosterusers
table manually. After lots of digging, I managed to find this particular comment very helpful in knowing what to set each column to, in order to represent that friendship in a friends list for the chat module.
My current method for handling friendships is by inserting two rows in to the rosterusers
table:
# Relationship user1 => user2
INSERT INTO rosterusers (username, jid, subscription, ask, server, type)
VALUES ('user1', '[email protected]', 'B', 'N', 'B', 'item');
# Relationship user2 => user1
INSERT INTO rosterusers (username, jid, subscription, ask, server, type)
VALUES ('user2', '[email protected]', 'B', 'N', 'B', 'item');
I'm not too happy with this because two rows are required for a reciprocal friendship.
I understand that XMPP, by standard, allows single and dual links between users. As one might deduce by the nature of my question, my own application's friend system uses one row to represent a friendship.
My main questions:
TRIGGER
might be the cleanest option for now.If not, then my other option is to alter the rosterusers
table and get that to refer to my own application's friend row, so that it functions like a cross database foreign key.
I created a view as Eugen suggested. The code isn't the most elegant, but I've tested it and it works with eJabberd 2.1 on MySQL 5.5.
My exact setup uses two databases, so I am referencing my main application's database explicitly by using main_database.table_name
.
The code is a union of two queries - the first takes User, Friend and then the second inserts Friend, User. I'm using UNION ALL
for speed, and to let "duplicates" through.
I think this is a really great way of handling the problem as no changes in the application are required, and it updates instantly.
CREATE VIEW rosterusers AS
SELECT LCASE(ua1.Username) AS `username`, CONCAT(LCASE(ua2.Username), '@myserver.org') AS `jid`,
'B' AS `subscription`,
'N' AS `ask`,
'N' AS `server`,
'item' AS `type`,
'B' AS `subscribe`,
d1.Created AS `created_at`,
ua2.Username AS `nick`,
'' AS `askmessage`
FROM main_database.User_Friend AS `d1`
INNER JOIN main_database.User AS `ua1` ON `d1`.UserID = `ua1`.ID
INNER JOIN main_database.User AS `ua2` ON `d1`.FriendID = `ua2`.ID
WHERE d1.IsApproved = 1
UNION ALL
SELECT LCASE(ub2.Username) AS `username`, CONCAT(LCASE(ub1.Username), '@myserver.org') AS `jid`,
'B' AS `subscription`,
'N' AS `ask`,
'N' AS `server`,
'item' AS `type`,
'B' AS `subscribe`,
d2.Created AS `created_at`,
ub1.Username AS `nick`,
'' AS `askmessage`
FROM main_database.User_Friend AS `d2`
INNER JOIN main_database.User AS `ub1` ON `d2`.UserID = `ub1`.ID
INNER JOIN main_database.User AS `ub2` ON `d2`.FriendID = `ub2`.ID
WHERE d2.IsApproved = 1;
IIUC, the table rosterusers
is read-only from the POV of your eJabberd
server app. This would make it simple, to replace it with a view
, that creates the needed 2 row out of 1 row in your own friends table.
Not knowing the structure of your own friendship table, I can't give you the full code, but here is what I thought of as pseudo-SQL
CREATE VIEW rosterusers AS SELECT * FROM (
SELECT
selfuser.name AS username,
frienduser.jid AS jid,
-- ....,
selfuser.jid AS jid_as_id
FROM
users AS selfuser
INNER JOIN friendships ON ....
INNER JOIN users AS frienduser ON ...
UNION SELECT
frienduser.name AS username,
selfuser.jid AS jid,
-- ....,
frienduser.jid AS jid_as_id
FROM
users AS selfuser
INNER JOIN friendships ON ....
INNER JOIN users AS frienduser ON ...
);
and then
SELECT
username, jid, subscription, ask, server, type
FROM rosterusers
WHERE jid_as_id='[email protected]'
should give you 2 rows, one from each part of the UNION
in the View
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With