Database with socket.io and node.js

Question

Database with socket.io and node.js

Navigation

#1 by (1 votes)

1

I am trying to create an application of the type messenger. I connect to the database using node.js mysql. Each person who logs on the website is initially connected to a room for example: "room juan" (only John connects to this room) said room I use to update the list of friends of this user

socket.join("room juan"); //cada usuario tiene su propio room y nadie más ingresa //en el
socket.join("juan");

when a user loguea appears with the status "connected" in the database. (The records that have repeated the "room juan" is because they are friends of Juan)

 users_online

    room      |    nickname     | status
    -------------------------------------------
    room juan |     juan        | **desconectado**
    -------------------------------------------
    room juan |     maria       | desconectado
    -------------------------------------------
    room ana  |     dario       | desconectado

Juan logueo, then set his status to connected

setStatusToConnected(juan); 


function setStatusToConnected("juan")
 {

           con.query(      
           'UPDATE users_online SET status = "connected" WHERE nickname = ?', ["juan"]);
  }

The database updates and Juan's status is now connected

    users_online

    room      |    nickname     | status
    -------------------------------------------
    room juan |     juan        | **conectado**
    -------------------------------------------
    room juan |     maria       | desconectado
    -------------------------------------------
    room ana  |     dario       | desconectado

I update users online, in this array friend_list = []; store users with status connected

updateUsersOnline(room juan, juan);


function updateUsersOnline(room juan,juan) {

friend_list = [];

           con.query(      
   'SELECT * FROM users_online WHERE status="connected"',
   [room],

  function(err,rows){
    if(err) throw err;

    for (var i in rows) {

             var obj = { room: rows[i].room, nickname: rows[i].nickname}; 

               friend_list.push(obj);

                io.sockets.in(rows[i].room).emit('view_data',      friend_list,rows[i].room); 

    }
            }
    );

I issue an event for each room that has the recently connected user, that is, if several users are connected to the room of Juan I issue an event for those rooms that have the user in question and I update the list that shows the users online

        io.sockets.in(rows[i].room).emit('view_data',      friend_list,rows[i].room); 


CLIENTE



 socket.on('view_data', function (friends,room){


                        for(var i=0; i<friends.length; i++) {
                                    if(friends[i].room==room){

     console.log(friendList[i].nickname);
    }

     }

My question is, this way of showing the friends of the user that loguea in the page, is a good practice ?, is well ?, in the long run can lead to problems ?, is the only way I came up with to update and be able to save the contacts of the users. Then I would like to store the messages of the users when they communicate with each other, for example the table would be of this style:

 tabla_mensajes

        origen    |  destinatario   | mensaje
        -------------------------------------------
        maria     |     juan        | hola juan como estas?
        -------------------------------------------
        juan      |     maria       | muy bien y vos?
        -------------------------------------------
        maria     |     juan        | tambien :)

and when the user clicks on the friend who wants to converse on the client, I create an event that communicates with the server requesting the old messages such as:

'SELECT mensaje FROM tabla_mensajes WHERE origen=? AND destinatario=?'

(Or something like that), what do you think? Am I going the right way or not?

mysql nodejs socket.io

asked by Sergio Diaz 18.02.2017 в 15:25

source

1 answer

How can I detect '#!' in a url? getline will not let me enter text

score 1 · Accepted Answer

UPDATE: The previous scheme was not going well, this one

I can think of this:

You would have:

a. Your table users
b. A table conversations that will be used to group any conversation. To this table you can add for example a column date or any other information that may be useful to you.
c. A table users_conversations that will serve as a bridge between tables a and b. In this table you will relate all the messages exchanged between two or more users in a given conversation. It could have a datetime type column if you are interested in knowing the exact date and time of any message.

It is not necessary that in the messages table (which I have called conversations) stores again the name of the user, you already have it in your user table and now you only refer to it by means of its id. In this way the tables remain optimized and are faster when it comes to obtaining information. ** A table is not the same as messages with thousands of lines in which you repeat again and again juan-> maría, maría-> john, instead of 1-> 2,2- > 1 ... ** In a chat with thousands of users your message board would grow in an incredible way and it would become very heavy if you use redundant information.

Note: Schemas are basic, to have an idea. It would have to be improved by creating the respective indexes in each table.

SQL Fiddle

MySQL 5.6 Schema Setup :

CREATE TABLE usuarios
    ('id_usuario' int, 'nombre_usuario' varchar(70))
;

INSERT INTO usuarios
    ('id_usuario', 'nombre_usuario')
VALUES
    (1, 'juan'),
    (2, 'maria'),
    (3, 'pedro')
;

CREATE TABLE conversaciones
    ('id_conversacion' int)
;

INSERT INTO conversaciones
    ('id_conversacion')
VALUES
    (1);

CREATE TABLE usuarios_conversaciones
    ('id' int, 'id_usuario_origen' int, 'id_usuario_destino' int, 'id_conversacion' int, 'mensaje' varchar(250))
;

INSERT INTO usuarios_conversaciones
    ('id', 'id_conversacion', 'id_usuario_origen', 'id_usuario_destino', 'mensaje')
VALUES
    (1,1,1, 2, 'hola maría'),
    (2,1,2, 1, 'hola juan'),
    (3,1,1, 2, '¿como estás?'),
    (4,1,2, 1, 'todo bien :)')


;

Query 1 :

SELECT
  uo.nombre_usuario u_origen, ud.nombre_usuario, uc.mensaje
FROM
  usuarios uo
LEFT JOIN usuarios_conversaciones uc
  ON uo.id_usuario = uc.id_usuario_origen 
LEFT JOIN usuarios ud
  ON ud.id_usuario = uc.id_usuario_destino 
  WHERE uc.id_conversacion=1 
 ORDER BY uc.id

Results :

| nombre_usuario | nombre_usuario |      mensaje |
|----------------|----------------|--------------|
|           juan |          maria |   hola maría |
|          maria |           juan |    hola juan |
|           juan |          maria | ¿como estás? |
|          maria |           juan | todo bien :) |