Ejemplo de chat en Postgresql

Saludos,

No es que este reinventado la rueda pero me parecio bastante simpático crear un chat usando Postgresql para administrar los mensajes y los usuarios conectados.

Hay unas reglas básicas para la creación de la base de datos y es que los clientes no puedan acceder a las tablas, sino por medio de funciones usando la directiva "SECURITY DEFINER", es decir, la base de datos creada con el usuario postgres y otros usarios accediendo solo por medio de las funciones.

Ademas estas funciones sirven de framework para que cualquier cliente(php,java,delphi,c++) se pueda comunicar con otros clientes de distintas plataformas.

Para mejorar la eficiencia de comunicación al momento de llegar un mensaje, utilizo "LISTEN y NOTIFY" para actualizar los mensajes y los usuarios conectados.

Primero la creación de las tablas básicas.

create table estatus
(
   idestatus INTEGER NOT NULL,
   descripcion varchar(15) NOT NULL,
   primary Key(idestatus)
);

REVOKE ALL ON TABLE estatus FROM public;--denegado el acceso a todos

--Insertamos los posibles valores
INSERT INTO estatus VALUES (1,'Activo');
INSERT INTO estatus VALUES (2,'Inactivo');
INSERT INTO estatus VALUES (3,'Desconectado');

-- cada cliente es una conexión y para diferenciarlos uso
-- la dirección ip y el puerto

create table sesion
(
   idsesion serial not null,
   usuario varchar(10) not null,   
   fechahoraentrada timestamp not null DEFAULT now(),
   client_port integer NOT NULL DEFAULT inet_client_port(),
   inet_client_addr inet NOT NULL DEFAULT inet_client_addr(),
   actualizacion timestamp not null DEFAULT now(),
   idmensaje INTEGER DEFAULT NULL,
   idestatus INTEGER NOT NULL DEFAULT 1,   
   primary key(idsesion),
   FOREIGN KEY(idestatus) REFERENCES estatus(idestatus)
);


CREATE INDEX idx_port_addr_sesion ON sesion(client_port,inet_client_addr);

REVOKE ALL ON TABLE sesion FROM public;--denegamos el acceso a todos

-- todos los mensajes del chat

create table chat
(
   idmensaje serial not null,
   idsesion integer not null,
   fechahora timestamp not null default now(),   
   mensaje varchar(140) not null,
   primary key (idmensaje),
   foreign key(idsesion) references sesion(idsesion)
);

CREATE INDEX idx_fechahora_chat ON chat(fechahora);

REVOKE ALL ON TABLE chat FROM public;--denegamos el acceso a todos

--para no repetir código(php,java,delphi entre otros) uso funciones plpgsql

-- Obtiene la sesion del cliente y bloquea su registro
-- Solo para uso interno

CREATE OR REPLACE FUNCTION obtener_sesion()
  RETURNS sesion AS
$BODY$
SELECT * FROM sesion WHERE client_port = inet_client_port() AND inet_client_addr = inet_client_addr() FOR UPDATE;
$BODY$
LANGUAGE sql 
VOLATILE
  COST 100;

-- Primer paso crea la sesión, observen que tiene la directiva
--"SECURITY DEFINER"
--no permite mas de una sesión por cliente
--valida que tenga un nombre válido
--LISTEN y publica a los demas usarios que alguien se conecto.

CREATE OR REPLACE FUNCTION crear_sesion(varchar, OUT id INTEGER, OUT fechahora TIMESTAMP)
  RETURNS RECORD  AS
$BODY$
DECLARE
   idsesion INTEGER;
   rec record;
BEGIN  
   SELECT * INTO rec FROM obtener_sesion();
   IF rec.idsesion IS NOT NULL THEN
      RAISE EXCEPTION 'La sesion ya fue creada';
   END IF;

   IF LENGTH(TRIM($1)) >= 5  AND LENGTH(TRIM($1)) <= 10 THEN
      INSERT INTO sesion (usuario) VALUES ($1) RETURNING * INTO rec;
      id = rec.idsesion;
      fechahora = rec.fechahoraentrada;
      LISTEN chat;
      NOTIFY chat,'user';      
      RETURN;
   ELSE
      RAISE EXCEPTION 'Usuario incorrecto';
   END IF;
END;    
$BODY$
LANGUAGE plpgsql 
SECURITY DEFINER 
VOLATILE
  COST 100;

-- Cierra la sesión del ciente
-- Inserta un mensaje que se ha desconectado
-- Publica la actualización de chat
-- Deja de recibir notificaciones

CREATE OR REPLACE FUNCTION cerrar_sesion()
  RETURNS VOID  AS
$BODY$
DECLARE
   rec record;
BEGIN  
   SELECT * INTO rec FROM obtener_sesion();
   IF rec.idsesion IS NOT NULL THEN
      IF rec.idestatus = 3 THEN
         RAISE EXCEPTION 'La sesion ya fue cerrada';
      END IF;
      UPDATE sesion SET idestatus=3 
      WHERE idsesion=rec.idsesion;              --Desconectado

      INSERT INTO chat (idsesion,mensaje) 
      VALUES (rec.idsesion,'se ha desconectado');
      NOTIFY chat,'chat'; 
      NOTIFY chat,'user'; 
      UNLISTEN chat;
      RETURN;
   END IF;   
END;    
$BODY$
LANGUAGE plpgsql 
SECURITY DEFINER 
VOLATILE
  COST 100;

--Función para obtener en cuanto tiempo se considera un
--usuario inactivo

CREATE OR REPLACE FUNCTION obtener_intervalo_inactividad()
RETURNS interval AS
$$
   SELECT interval '10 min';
$$
LANGUAGE sql
IMMUTABLE;

--Obtiene todos los usuarios conectados y solo los que esten activos

CREATE OR REPLACE FUNCTION obtener_usuarios(OUT idsesion INTEGER, OUT usuario varchar, OUT estatus varchar)
 RETURNS  SETOF record AS
$BODY$
DECLARE
   idse INTEGER;
   rec RECORD;
BEGIN
   SELECT * INTO rec FROM obtener_sesion();
   IF rec.idsesion IS NULL THEN
      RAISE EXCEPTION 'La sesion no ha sido creada';
   END IF;
   FOR rec IN SELECT sesion.idsesion, sesion.usuario, 
                     estatus.descripcion 
              FROM sesion LEFT JOIN estatus USING(idestatus) 
              WHERE idestatus = 1 AND now() - 
                    actualizacion < obtener_intervalo_inactividad() 
              ORDER BY fechahoraentrada LOOP
       idsesion = rec.idsesion;
       usuario= rec.usuario;
       estatus= rec.descripcion;
       RETURN NEXT;
   END LOOP;
END;    
$BODY$
LANGUAGE plpgsql 
SECURITY DEFINER 
VOLATILE
  COST 100;

--Al conectarse se debe mostrar los mensajes de las conversaciones
-- mas antiguas

CREATE OR REPLACE FUNCTION obtener_intervalo_chat()
RETURNS interval AS
$$
   SELECT interval '5 min';
$$
LANGUAGE sql
IMMUTABLE;

--Obtiene todos los mensajes no leidos
-- La primera vez que se conecta obtiene los mensajes que
-- han sido enviados obtener_intervalo_chat() minutos antes
-- Luego cada vez que envia los mensajes guarda el último idmensaje
-- para enviar despues solo aquellos que ha recibido

CREATE OR REPLACE FUNCTION obtener_mensajes(OUT idmensaje integer, OUT usuario varchar, OUT mensaje varchar)
  RETURNS SETOF RECORD AS
$BODY$
DECLARE
   idse INTEGER;
   rec RECORD;
   sql text;
   idmen INTEGER;
   fechahoraent TIMESTAMP;
BEGIN  
   SELECT * INTO rec FROM obtener_sesion();
   IF rec.idsesion IS NULL THEN
      RAISE EXCEPTION 'La sesion no ha sido creada';
   END IF;
   idse = rec.idsesion;
 
   sql = 'SELECT * FROM chat LEFT join sesion using(idsesion)';
   IF rec.idmensaje IS NULL THEN
      sql = sql || 'WHERE fechahora >= (' || quote_literal(rec.fechahoraentrada) || '::timestamp - obtener_intervalo_chat())';
   ELSE 
      sql = sql || 'WHERE chat.idmensaje > ' || rec.idmensaje;
   END IF;
   sql = sql || ' ORDER BY fechahora';
   
   FOR rec IN EXECUTE sql LOOP
       idmensaje = rec.idmensaje;
       idmen = rec.idmensaje;
       usuario = rec.usuario;
       mensaje = rec.mensaje;
       RETURN NEXT;
   END LOOP;
   IF idmen IS NOT NULL THEN
      UPDATE sesion SET actualizacion=now(), idmensaje=idmen WHERE idsesion = idse;   
   END IF;

END;    
$BODY$
LANGUAGE plpgsql 
SECURITY DEFINER 
VOLATILE
  COST 100;

-- Enviar mensaje inserta en la tabla chat
-- Notifica a todos los usarios que llego un mensaje.

CREATE OR REPLACE FUNCTION enviar_mensaje(IN men varchar)
  RETURNS VOID AS
$BODY$
DECLARE
   rec RECORD;
BEGIN  
   SELECT * INTO rec FROM obtener_sesion();
   IF rec.idsesion IS NULL THEN
      RAISE EXCEPTION 'La sesion no ha sido creada';
   END IF;

   INSERT INTO chat (idsesion,mensaje) VALUES (rec.idsesion,$1);

   NOTIFY chat,'chat'; -- cuando alguien envia un mensaje notifica a todos que lo reciban.
END;    
$BODY$
LANGUAGE plpgsql 
SECURITY DEFINER 
VOLATILE
  COST 100;

Para probarlo podemos usar dos psql llamando las funciones y usando un usuario distinto a postgres

Cliente Nro 1

select * from crear_sesion ('Pedro');  --paso 1
select * from obtener_usuarios();      --paso 3
select * from enviar_mensaje('Hola');  --Paso 5
select * from obtener_mensajes();      --Paso 8
select * from cerrar_sesion();         --paso 9

Cliente nro 2

select * from crear_sesion ('Maria');  --paso 2
select * from obtener_usuarios();      --Paso 4
select * from obtener_mensajes();      --Paso 6
select * from enviar_mensaje('Hola como estan?');  -- paso 7
select * from obtener_usuarios();      --Paso 10
select * from obtener_mensajes();      --Paso 11
select * from cerrar_sesion();         --paso 12

Si su lenguaje de programación no posee mecanismos de recepción asincrona LISTEN NOTIFY, pueden crear un hilo y cada cierto tiempo llamar a las funciones obtener_mensajes() y obtener_usuarios()

Me gustaria agregarle otra funcionalidad como enviar mensajes privados, pero sera en otro momento. Por lo menos sirve como ejemplo de usos de funciones y manejo de datos en Postgresql.

Comentarios

Opciones de visualización de comentarios

Seleccione la forma que prefiera para mostrar los comentarios y haga clic en «Guardar las opciones» para activar los cambios.

utilizacion del listen y notify

buenos dias, se que esta publicacion ya tiene bastante tiempo, pero quisiera saber si tienes algun fragmento de codigo en algun lenguaje donde utilices estas funciones para ver como es el funcionamiento del LISTEN y NOTIFY dentro de algun lenguaje, si haces algun tipo de llamado especial o alguna funcion en el lenguaje utilizado para que te retorne solo las notificaciones, ya que mencionas mecanismos de recepción asincrona, me gustaria saber como son esos en algun lenguajes que donimes.