Listas externas eficientes con MySQL y ejabberd

ejabberd windows

Question

Pregunta

Tenga en cuenta que la solución a esto está directamente debajo usando la idea de vista de Eugen.

Estoy escribiendo un módulo de chat para un sitio PHP / MySQL guiado por el usuario que permite a dos usuarios hacer amigos, y ha elegido eJabberd para el sistema de chat.

He configurado la autenticación externa con éxito utilizando un daemon de PHP, y ahora he logrado conseguir las amistades en eJabberd utilizando mod_roster_odbc y rosterusers manualmente la tabla rosterusers MySQL. Después de muchas excavaciones, me las arreglé para encontrar este comentario en particular muy útil para saber a qué configurar cada columna, a fin de representar esa amistad en una lista de amigos para el módulo de chat.

Mi método actual para manejar amistades es mediante la inserción de dos filas en la tabla rosterusers :

# 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'');

No estoy muy contento con esto porque se requieren dos filas para una amistad recíproca.

Entiendo que XMPP, de forma estándar, permite enlaces simples y dobles entre usuarios. Como se podría deducir por la naturaleza de mi pregunta, el sistema de amigos de mi propia aplicación usa una fila para representar una amistad.

Mis preguntas principales:

1. ¿Es posible consolidar esta amistad en una sola fila? He intentado algunas combinaciones de esta documentación no oficial pero no he tenido éxito. Estoy probando conectándome a mi servidor XMPP con el cliente Pidgin.
2. ¿Cuál es la mejor manera de mantener sincronizadas las dos bases de datos, amigos y lista de XMPP? Creo que un TRIGGER MySQL podría ser la opción más limpia por ahora.

Si no, mi otra opción es alterar la tabla rosterusers y hacer que se refiera a la fila de amigos de mi propia aplicación, para que funcione como una clave externa de la base de datos cruzada.

Código de solución

Creé una vista como sugirió Eugen. El código no es el más elegante, pero lo he probado y funciona con eJabberd 2.1 en MySQL 5.5.

Mi configuración exacta usa dos bases de datos, por lo que estoy haciendo referencia explícitamente a la base de datos de mi aplicación principal usando main_database.table_name .

El código es una unión de dos consultas: la primera lleva Usuario, Amigo y luego la segunda inserta Amigo, Usuario. Estoy usando UNION ALL para velocidad y para permitir "duplicados".

Creo que esta es una excelente manera de manejar el problema ya que no se requieren cambios en la aplicación, y se actualiza instantáneamente.

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;

Answer 1

IIUC, la tabla rosterusers es de solo lectura desde el punto de vista de su aplicación de servidor eJabberd . Esto lo haría simple, para reemplazarlo con una view , que crea la fila necesaria de 2 filas de 1 en su propia mesa de amigos.

Sin conocer la estructura de tu propia tabla de amigos, no puedo darte el código completo, pero aquí está lo que yo pensaba como 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 ...
);

y entonces

SELECT
    username, jid, subscription, ask, server, type
FROM rosterusers
WHERE jid_as_id=''[email protected]''

debería darle 2 filas, una de cada parte de la UNION en la Vista