使用MySQL和ejabberd高效的外部rostering [英] Efficient external rostering with MySQL and ejabberd

查看：152
本文介绍了使用MySQL和ejabberd高效的外部rostering的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

问题



请注意，这个解决方案直接在下面使用了Eugen的观点！



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


$ b $我已经使用PHP守护进程成功地设置了外部身份验证，现在我已经成功地使用 mod_roster_odbc 来填充ejabberd的友谊，并填充手动执行MySQL rosterusers 表。经过大量的挖掘，我设法找到这个特别的评论，非常有助于了解什么为了在聊天模块的朋友列表中表示友谊，请设置每一列。



我目前处理友谊的方法是将两行插入到 rosterusers 表：

 ＃关系user1 => user2 
 INSERT INTO rosterusers（username，jid，subscription，ask，server，type）
 VALUES（'user1'，'user2@myserver.org'，'B'，'N'，'B' ，'item'）; 
 
＃关系user2 => user1 
 INSERT INTO rosterusers（username，jid，subscription，ask，server，type）
 VALUES（'user2'，'user1@myserver.org'，'B'，'N'，'B' ，'item'）;

我不太高兴，因为两行是互惠友谊所必需的。 >

我明白，按照标准，XMPP允许用户之间的单和双链接。由于我的问题本质可能推断，我自己的应用程序的朋友系统使用一行来表示友谊。



我的主要问题：


  1. 是否可以将这种友谊整合到一行？我已经尝试了这个非官方文档的一些组合，但没有成功。我正在使用Pidgin客户端连接到我的XMPP服务器进行测试。

  2. 保持两个数据库（朋友和XMPP名册）同步的最佳方法是什么？我认为MySQL TRIGGER 可能是现在最干净的选择。

如果没有，那么我的另一个选择是改变 rosterusers 表，并指定我自己的应用程序的朋友行，这样它就像一个跨数据库外键。 / p>

解决方案代码



我创建了一个Eugen建议的视图。代码不是最优雅的，但我已经测试了它，它与MySQL 5.5上的eJabberd 2.1一起使用。



我的确切设置使用两个数据库，所以我是通过使用 main_database.table_name 显式引用我的主应用程序的数据库。



代码是两个查询的并集 - 先用User，Friend然后再插入Friend，User。我正在使用 UNION ALL ，以便重复。



我认为这是一个真正很好的处理问题的方法，因为不需要更改应用程序，它会立即更新。

  CREATE VIEW rosterusers AS 
 
 SELECT LCASE（ua1.Username）AS`username`，CONCAT（LCASE（ua2.Username），'@ myserver.org'）AS`jid`，
'B'AS' `，
'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，表 rosterusers 是从 eJabberd 服务器应用程序的POV中只读的。这将使它变得简单，用视图替换它，它可以在你自己的朋友表中的1行中创建所需的2行。



不知道你自己的友谊表的结构，我不能给你完整的代码，但这里是我认为的伪SQL 

  CREATE VIEW rosterusers AS SELECT * FROM（
 SELECT 
 selfuser.name AS username，
 frienduser.jid AS jid，
  -  -  ....，
 selfuser.jid AS jid_as_id 
 FROM 
用户AS selfuser 
 INNER JOIN friendships ON .... 
 INNER JOIN users AS frienduser ON。 
 UNION SELECT 
 frienduser.name AS用户名，
 selfuser.jid AS jid，
  -  ....，
 frienduser.jid AS jid_as_id 
 FROM 
 users AS selfuser 
 INNER JOIN friendships ON .... 
 INNER JOIN用户AS frienduser ON ... 
）;

然后

 code> SELECT 
 username，jid，subscription，ask，server，type 
 FROM rosterusers 
 WHERE jid_as_id ='user1@myserver.org'

应该给你2行，一个来自 UNION 的每个部分， / p>

Question

Please note that the solution to this is directly below using Eugen's view idea!

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', 'user2@myserver.org', 'B', 'N', 'B', 'item');

# Relationship user2 => user1
INSERT INTO rosterusers (username, jid, subscription, ask, server, type)
VALUES ('user2', 'user1@myserver.org', '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:

  1. Is it possible to consolidate this friendship in to just one row? I've tried some combinations from this unofficial documentation but haven't had success. I'm testing by connecting to my XMPP server with the Pidgin client.
  2. What is the best way to keep the two databases - friends and XMPP roster - in sync? I think that a MySQL 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.

Solution Code

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='user1@myserver.org'

should give you 2 rows, one from each part of the UNION in the View

这篇关于使用MySQL和ejabberd高效的外部rostering的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
数据库最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆