Echo en MySQL

Echo MySQL.

0. Índice de contenidos.

• 1. Introducción.
• 2. Entorno.
• 3. Requerimientos.
• 4. Configuración.
• 5. Utilización.
• 6. Conclusiones.

1. Introducción

Cualquiera que haya trabajado con la base de datos MySQL de forma un poco más «avanzada» que realizar una simple SELECT,se habrá dado cuenta de
la inexistencia de una función del tipo ECHO. Es decir, NO EXISTE la función que nos permite la impresión de un texto en pantalla, por lo que
acciones como comprobar el correcto funcionamiento de un trigger, una función o un procedimiento almacenado se vuelven verdaderas odiseas cuando estos
no son lo suficientemente simples ;-).

Tras la realización de una función muy compleja, me dí cuenta de lo necesaria que se hacía la utilización de una función tipo ECHO para asegurar
al 100% que la función compleja «hacía lo que tenía que hacer», esta función tenía en un primer momento un error en su lógica el cual no veía..jeje.
Tras dedicar alguna hora y con mucha ayuda de la desesperación en estas cosas (que sólo los informáticos entendemos 😉 ) surgio la «idea feliz» :

¿Por qué no usar una tabla temporal de logs como pantalla en la que guardar las trazas?

2. Entorno.

El tutorial está escrito usando el siguiente entorno:

• Hardware: Portátil Asus G50Vseries (Core Duo P8600 2.4GHz, 4GB RAM, 320 GB HD).
• Sistema operativo: Windows Vista Ultimate.
• MySQL 5.1.32
• MySQL Workbench OSS for Windows version 5.1.18 (Para el diseño del modelo de datos)

3. Requerimientos.

Para la realización de este tutorial se ha utilizado la herramienta de generación de diagramas
de entidad-relación MySQL Workbench.Con ella se ha generado un modelo de datos formado por 3 tablas :

1. Clientes : Tabla que almacenará la información de los usuarios del sistema creado
2. Cuentas : Tabla que alamacenará la información asociada a la cuenta de los usuarios
3. Perfiles : Tabla que almacenarará los perfiles de las cuentas

Requisito 1 : Un cliente puede tener más de una cuenta.

Requisito 2 : Un perfil puede estar asociado a más de una cuenta.

Diagrama de Entidad Relación

Representa las tablas anteriores y la información a almacenar.

Script de creación del modelo de datos

Nota: Es necesario tener creado el esquema en el que se guardaran.

-- -----------------------------------------------------
-- Table `Clientes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Clientes` ;

CREATE  TABLE IF NOT EXISTS `Clientes` (
  `idClientes` INT NOT NULL ,
  `nombre` VARCHAR(45) NULL ,
  `apellidos` VARCHAR(45) NULL ,
  `direccion` VARCHAR(45) NULL ,
  PRIMARY KEY (`idClientes`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Perfiles`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Perfiles` ;

CREATE  TABLE IF NOT EXISTS `Perfiles` (
  `idPerfiles` INT NOT NULL ,
  `descripcion` VARCHAR(45) NULL ,
  PRIMARY KEY (`idPerfiles`) )
ENGINE = InnoDB;

-- -----------------------------------------------------
-- Table `Cuentas`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `Cuentas` ;

CREATE  TABLE IF NOT EXISTS `Cuentas` (
  `idCuentas` INT NOT NULL ,
  `usuario` VARCHAR(45) NULL ,
  `contraseña` VARCHAR(45) NULL ,
  `fecha_alta` DATETIME NOT NULL ,
  `fecha_baja` DATETIME NULL ,
  `baja_logica` INT DEFAULT 0 ,
  `Clientes_idClientes` INT NOT NULL ,
  `Perfiles_idPerfiles` INT NOT NULL ,
  PRIMARY KEY (`idCuentas`) )
ENGINE = InnoDB;

CREATE INDEX `fk_Cuentas_Clientes` ON `Cuentas` (`Clientes_idClientes` ASC) ;

CREATE INDEX `fk_Cuentas_Perfiles1` ON `Cuentas` (`Perfiles_idPerfiles` ASC) ;

Script para la carga de datos

Script de carga de datos de la tabla Clientes :

insert into `clientes`(`idClientes`,`nombre`,`apellidos`,`direccion`) values (1,'Carmen','Apellidos 1','Dirección 1');
insert into `clientes`(`idClientes`,`nombre`,`apellidos`,`direccion`) values (2,'Juan Pablo','Apellidos 2','Dirección 2');
insert into `clientes`(`idClientes`,`nombre`,`apellidos`,`direccion`) values (3,'Fernando','Apellidos 3','Dirección 3');
insert into `clientes`(`idClientes`,`nombre`,`apellidos`,`direccion`) values (4,'Fran','Apellidos 4','Dirección 4');
insert into `clientes`(`idClientes`,`nombre`,`apellidos`,`direccion`) values (5,'Tamara','Apellidos 5','Dirección 5');
insert into `clientes`(`idClientes`,`nombre`,`apellidos`,`direccion`) values (6,'Marta','Apellidos 6','Dirección 6');

Script de carga de datos de la tabla Perfiles :

insert into `perfiles`(`idPerfiles`,`descripcion`) values (1,'Administrador');
insert into `perfiles`(`idPerfiles`,`descripcion`) values (2,'Gerente');
insert into `perfiles`(`idPerfiles`,`descripcion`) values (3,'Comercial');
insert into `perfiles`(`idPerfiles`,`descripcion`) values (4,'Administrativo');
insert into `perfiles`(`idPerfiles`,`descripcion`) values (5,'Usuario');

4. Configuración.

En este punto realizaremos la definición de la tabla que almacenará las trazas : la tabla echo_log.

Nota: Se ha definido una tabla de logs lo más genérica y funcional posible,de ahí que se hayan añadido
más columnas de las necesarias como «categoria» y «detalle_categoria» (de esta forma podremos filtrar las búsquedas
en esta tabla), pero estoy seguro que muchos de vosotros tendreis más propuestas para incluir en esta tabla y así dotarla
de mayor funcionalidad.

Tabla de logs para simular ECHOs

CREATE TABLE `echo_log` (
  `id_log` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `categoria` varchar(45) NOT NULL,
  `detalle_categoria` varchar(45) NOT NULL,
  `descripcion` varchar(45) NOT NULL,
  `fecha` datetime NOT NULL,
  PRIMARY KEY (`id_log`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

Detalle de las columnas :

1. id_log : Identificador del log.
2. categoria : Categoriza el tipo de log al que se refiere.
3. detalle_categoria : Detalle del tipo de log,
4. descripcion : Mensaje del log
5. fecha : Fecha en la que se produjo

5. Funcionamiento.

Para la realización del ejemplo y ver como se gerneran los logs se va proceder a crear :

1. Un Trigger que se ejecute ANTES de realizar un inserción en la tabla Cuentas.
2. Un Trigger que se ejecute DESPUES de realizar un inserción en la tabla Cuentas.
3. Un Procedimiento Almacenado utilizado para insertar un Administrador en la tabla Cuentas.
4. Una Función utilizada para insertar un Administrador en la tabla Cuentas.

Nota : Para cada ejemplo se borra previamente el contenido de la tabla Cuentas y de la tabla echo_log

Ejemplo 1 : Insertar un administrador desde la sentencia de INSERT

Código del trigger que se ejecuta ANTES de realizar un inserción en la tabla Cuentas :

Nota : Inserta únicamente una traza en la tabla de logs.

DROP TRIGGER  `beforeInsertCuentas`;

DELIMITER $$

CREATE TRIGGER `beforeInsertCuentas` BEFORE INSERT ON `cuentas` FOR EACH ROW BEGIN
    INSERT INTO echo_log (categoria,detalle_categoria,descripcion,fecha) VALUES ('TRIGGER','beforeInsertCuentas','Se va a insertar en tabla Cuentas',NOW());
END $$

DELIMITER ;

Código del trigger que se ejecuta DESPUES de realizar un inserción en la tabla Cuentas :

Nota : Inserta únicamente una traza en la tabla de logs.

DROP TRIGGER  `afterInsertCuentas`;

DELIMITER $$

CREATE TRIGGER `afterInsertCuentas` AFTER INSERT ON `cuentas` FOR EACH ROW BEGIN
		SET @usuario = NEW.usuario;
    INSERT INTO echo_log (categoria,detalle_categoria,descripcion,fecha) VALUES ('TRIGGER','afterInsertCuentas',CONCAT('Se ha insertado : ',@usuario),NOW());
END $$

DELIMITER ;

Sentencia de INSERT que introduce un administrador

insert into `cuentas`(`idCuentas`,`usuario`,`contraseña`,`fecha_alta`,`fecha_baja`,`baja_logica`,`Clientes_idClientes`,`Perfiles_idPerfiles`) 
values (1,'Tamara','Tamy',NOW(),null,'0',5,5);

Consulta que muestra las trazas de realizar un insert :

select ec.categoria, ec.detalle_categoria, ec.descripcion, ec.fecha
from echo_log ec
order by fecha;

Resultado de la consulta :

Ejemplo 2 : Insertar un administrador desde el procedimiento almacenado ‘insertarAdministradorProcedure’

Código del procedimiento almacenado para realizar una inserción en la tabla Cuentas :

Nota : Inserta únicamente una traza en la tabla de logs y ejecuta el insert. (Requiere tener el ejemplo 1 para ver las trazas de los triggers)

DROP PROCEDURE IF EXISTS `insertarAdministradorProcedure`;

DELIMITER $$

CREATE PROCEDURE `insertarAdministradorProcedure`(IN id_cuentas INT,IN usuario CHAR(45),IN password CHAR(45),IN id_cliente INT)
BEGIN
		  INSERT INTO echo_log (categoria,detalle_categoria,descripcion,fecha) VALUES ('PROCEDURE','insertarAdministrador','Se va a insertar en tabla Cuentas',NOW());
			insert into `cuentas`(`idCuentas`,`usuario`,`contraseña`,`fecha_alta`,`fecha_baja`,`baja_logica`,`Clientes_idClientes`,`Perfiles_idPerfiles`)
			values (id_cuentas,usuario,password,'2009-07-18 00:00:00',null,'0',id_cliente,5);
			INSERT INTO echo_log (categoria,detalle_categoria,descripcion,fecha) VALUES ('PROCEDURE','insertarAdministrador',CONCAT('Se ha insertado : ',usuario),NOW());
     END $$

DELIMITER ;

Sentencia de llama al procedimiento que introduce un administrador

CALL insertarAdministradorProcedure(1,'Tamara','Tamy',5);

Consulta que muestra las trazas de realizar una llamada al procedure

select ec.categoria, ec.detalle_categoria, ec.descripcion, ec.fecha
from echo_log ec
order by fecha;

Resultado de la consulta :

Ejemplo 3 : Insertar un administrador desde la función ‘insertarAdministradorFunction’

Código de la función para realizar una inserción en la tabla Cuentas :

Nota : Inserta únicamente una traza en la tabla de logs y ejecuta el insert. (Requiere tener el ejemplo 1 para ver las trazas de los triggers)

DROP FUNCTION IF EXISTS `insertarAdministradorFunction`;

DELIMITER $$

CREATE FUNCTION `insertarAdministradorFunction`(id_cuentas INT,usuario CHAR(45),password CHAR(45),id_cliente INT) RETURNS tinyint(1)
BEGIN
	INSERT INTO echo_log (categoria,detalle_categoria,descripcion,fecha) VALUES ('FUNCTION','insertarAdministradorFunction','Se va a insertar en tabla Cuentas',NOW());
	insert into `cuentas`(`idCuentas`,`usuario`,`contraseña`,`fecha_alta`,`fecha_baja`,`baja_logica`,`Clientes_idClientes`,`Perfiles_idPerfiles`)
			values (id_cuentas,usuario,password,'2009-07-18 00:00:00',null,'0',id_cliente,5);
	INSERT INTO echo_log (categoria,detalle_categoria,descripcion,fecha) VALUES ('FUNCTION','insertarAdministradorFunction',CONCAT('Se ha insertado : ',usuario),NOW());
	RETURN true;
END $$

DELIMITER ;

Sentencia de llamada a la función que introduce un Administrador

SELECT insertarAdministradorFunction(1,'Tamara','Tamy',5);

Consulta que muestra las trazas de realizar la llamada a la funcion :

select ec.categoria, ec.detalle_categoria, ec.descripcion, ec.fecha
from echo_log ec
order by fecha;

Resultado de la consulta :

Ejemplo 4 : Todo mezclado

En este ejemplo vamos una funcionalidad muy interesante de realizar la tabla echo_log de esta manera

Nota : Requiere vaciar las tablas y tener la funcionalidad de los anteriores ejemplos

Llamadas a los tres métodos

insert into `cuentas`(`idCuentas`,`usuario`,`contraseña`,`fecha_alta`,`fecha_baja`,`baja_logica`,`Clientes_idClientes`,`Perfiles_idPerfiles`) 
values (1,'Tamara','Tamy',NOW(),null,'0',5,5);

CALL insertarAdministradorProcedure(2,'Marta','Marta',6);

SELECT insertarAdministradorFunction(3,'Carmen','Carmen',1);

Consulta que muestra las trazas de realizar la llamada a la función :

select ec.categoria, ec.detalle_categoria, ec.descripcion, ec.fecha
from echo_log ec
order by fecha;

Ahora que tenemos todas las trazas podremos ser capaces de filtrar por cualquiera de las columnas
para asi obtener la información que más nos interese, por ejemplo la de todos los TRIGGER, la de una función en particular, con
un tipo de descripción,etc.

Resultado de la consulta :

6. Conclusiones.

Espero haberos ayudado con esta «idea feliz»,que os ayudara mucho a la hora de encontrar errores y sobre todo a la hora de construir diferentes elementos
de la base de datos como son : Triggers, Procedures y Functions. Para cualquier duda ya sabeis.

Un saludo.

Víctor

mailto:vjmadrid@autentia.com