使用MySQL和ejabberd实现高效的外部排班系统

Question

问题

请注意，使用Eugen的观点即可直接解决本问题！

我正在为一个PHP/MySQL用户驱动的网站编写聊天模块，该模块允许两个用户成为好友，并选择了eJabberd作为聊天系统。

我已经成功地使用PHP守护程序设置了外部身份验证，现在我成功地将好友添加到了eJabberd中，使用mod_roster_odbc并手动填充MySQL rosterusers表。经过大量挖掘，我设法找到这个特定评论非常有帮助，可以知道要将每个列设置为什么，以便在聊天模块的好友列表中表示出这种友谊。

我目前处理好友关系的方法是在rosterusers表中插入两行：

# Relationship user1 => user2
INSERT INTO rosterusers (username, jid, subscription, ask, server, type)
VALUES ('user1', '[e-mail]', 'B', 'N', 'B', 'item');

# Relationship user2 => user1
INSERT INTO rosterusers (username, jid, subscription, ask, server, type)
VALUES ('user2', '[e-mail]', 'B', 'N', 'B', 'item');

因为互相的友谊需要两行，所以我对此不太满意。

我知道XMPP标准允许用户之间存在单项和双向链接。正如我的问题的性质所示，我自己的应用程序中的朋友系统只使用一行表示友谊。

我的主要问题：

1. 是否有可能将这个友谊合并成一行？我尝试过从这个非官方文档 中尝试了一些组合，但没有成功。我正在使用 Pidgin 客户端连接到我的XMPP服务器进行测试。
2. 保持两个数据库 - 好友和XMPP花名册 - 同步的最佳方法是什么？我认为目前最清理的选项可能是使用MySQL的 TRIGGER。

如果不行，那么我的另一个选择是修改 rosterusers 表，并使其引用我自己应用程序的好友行，以使其功能类似于跨数据库外键。

解决方案代码

我按照 Eugen 的建议创建了一个视图。代码不是最优雅的，但我已经进行了测试，并且在 MySQL 5.5 上使用 eJabberd 2.1 工作。

我的确切设置使用了两个数据库，因此我使用 main_database.table_name 显式地引用了我的主应用程序的数据库。

该代码是两个查询的联合 - 第一个获取用户、好友，然后第二个插入好友、用户。我使用 UNION ALL 以提高速度，并让“重复项”通过。

我认为这是处理问题的非常好的方法，因为不需要更改应用程序，并且可以立即更新。

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;

- Will Morgan

Answer 1

根据我的理解，从你的 eJabberd 服务器应用程序的角度来看，表 rosterusers 是只读的。这样做会使更换为视图变得简单，该视图可以从你自己的朋友表中创建所需的2行，使其成为1行。

由于我不知道你自己的友谊表的结构，因此无法给您完整的代码，但以下是我考虑到的伪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 ...
);

然后

SELECT
    username, jid, subscription, ask, server, type
FROM rosterusers
WHERE jid_as_id='[e-mail]'

应该会给你2行，一行来自视图中UNION的每个部分

- Eugen Rieck

---

是的，就所有目的而言，rosterusers 是只读的。创建一个视图是个好主意！ - Will Morgan