Criando auditoria do seu aplicativo e melhorando o desempenho com Stored Procedures no MYSQL

E ai pessoal,
Nesse post veremos como criar uma auditoria de todas as inclusões, alterações e exclusões que os usuários fazem em sua base de Dados MySQL. Faremos isso com a ajuda de Stored Procedures que, além de nos auxiliar na auditoria, já é comprovado que elas aumentam o desempenho quando vamos trabalhar com nossas tabelas de base de dados e além disso, não ficamos dependendo de qual linguagem será implementado o sistema desde que a linguagem consiga se comunicar com a base e chamar as SPs do servidor Mysql.


ENTENDENDO O USO DE STORED PROCEDURE

Primeiramente veremos como trabalha as Stored Procedures no Mysql.
A criação de SPs estão disponíveis apenas na versão 5.x do MySQL, o que foi um marco do SGBD OpenSource. Ainda faltam alguns recursos no MYSQL como vemos em outros SGBD, como a criação de Exceptions e outras funcionalidades.

Uma Stored Procedure trabalha parecido como Functions, Procedures e Métodos das linguagens de programação, onde você cria o nome e passa os paramêtros para a rotina.
Nós temos 3 tipos diferentes de métodos em SP. Esses métodos podem ser de Entrada, de Saída ou de Entrada e Saida ao mesmo tempo.

IN: este é um parâmetro de entrada, ou seja, um parâmetro cujo seu valor será utilizado no interior do procedimento para produzir algum resultado;

OUT: esté parâmetro retorna algo de dentro do procedimento para o lado externo, colocando os valores manipulados disponíveis na memória ou no conjunto de resultados;

INOUT: faz os dois trabalhos ao mesmo tempo!


CRIANDO STORED PROCEDURES

Agora veremos como criar Stored Procedures.

Exemplo de criação de SP no MYSQL:

//Criamos um delimitador, para que possamos criar várias "linhas de código" em um único script
DELIMITER $

/*Apagamos a procedure chamada sp_pessoas caso ela já exista.*/
DROP PROCEDURE IF EXISTS sp_pessoas$

/*Criamos uma procedure chamada sp_pessoas que recebe 3 parâmetros de entrada*/
CREATE PROCEDURE sp_pessoas(IN sNome_pessoa VARCHAR(35), IN sEndereco_pessoa VARCHAR(30), IN sUf CHAR(1))
BEGIN

/*Inserimos na entidade pessoas, valores passados ao chamar a Stored Procedure*/
INSERT INTO pessoas(nome_pessoa, endereco_pessoa, uf) VALUES (sNome_pessoa, sEndereco_pessoa, sUF);

END$


Para podermos chamarmos essa StoredProcedure diretamente pelo Mysql, utilizamos a função CALL.
Exemplo:
CALL sp_pessoas('Mauricio', 'Rua sem saida', 'SP');


CRIANDO A AUDITORIA

Para criarmos a auditoria, iremos criar uma tabela chamada usabilidade, na qual irá conter dados do usuário que esta efetuando o processo no sistema e um pequeno histórico sobre o processo.
Campos da tabela usabilidade :
idoperador INT(11) - (AI, PK)
data_uso TIMESTAMP - (Default value: CURRENT_TIMESTAMP)
menu VARCHAR(35)
acao VARCHAR(35)

Agora criaremos uma SP para Adicionar uma nova ou Atualizar uma pessoa já existente na entidade.
Para isso, vamos inserir algumas linhas no código do exemplo acima. Ele ficará assim:

/*Criamos um delimitador, para que possamos criar várias "linhas de código" em um único script*/
DELIMITER $

DROP PROCEDURE IF EXISTS sp_pessoas_IU$

CREATE PROCEDURE sp_pessoas_IU(sIdpessoa INT(11), sNome_pessoa VARCHAR(35), sEndereco_pessoa VARCHAR(30), sUf CHAR(1), sIdoperador INT(11))
BEGIN

/*O que faremos aqui, é verificar pelo id da pessoa, se ela já existe. Caso nao exista (seja igual a 0), insere no banco. Caso contrário, atualiza os dados*/
IF NOT EXISTS(SELECT idpessoa FROM pessoas WHERE idpessoa = sIdpessoa) THEN
INSERT INTO pessoas(nome_pessoa, endereco_pessoa, uf) VALUES (sNome_pessoa, sEndereco_pessoa, sUF);
/*Inserimos na entidade usabilidade, o historico do que o usuário processou.*/
INSERT INTO usabilidade(idoperador, menu, acao) VALUES (sIdoperador, 'CADASTROS - PESSOAS', CONCAT('O USUÁRIO INSERIU A SEGUINTE PESSOA: ', LAST_INSERT_ID()));

ELSE

UPDATE pessoas SET nome_pessoa = sNome_pessoa, sEndereco_pessoa = sNome_pessoa, uf = sUF WHERE idpessoa = sIdpessoa;
/*Inserimos na entidade usabilidade, o historico do que o usuário processou.*/
INSERT INTO usabilidade(idoperador, menu, acao) VALUES (sIdoperador, 'CADASTROS - PESSOAS', CONCAT('O USUÁRIO ATUALIZOU A SEGUINTE PESSOA: ', sIdpessoa));

END IF;


END$


Agora, criaremos a StoredProcedure para Apagar um usuário da entidade:

DROP PROCEDURE IF EXISTS sp_pessoas_D$

CREATE PROCEDURE sp_pessoas_D(sIdpessoa INT(11), sIdoperador INT(11))
BEGIN
/*Apaga a pessoa da base de dados*/
DELETE FROM pessoas WHERE idpessoa = sIdPessoa;
/*Inserimos na entidade usabilidade, o historico do que o usuário processou.*/
INSERT INTO usabilidade(idoperador, menu, acao) VALUES (sIdoperador, 'CADASTROS - PESSOAS', CONCAT('O USUÁRIO DELETOU A SEGUINTE PESSOA: ', sIdpessoa));

END$

CHAMANDO AS STORED PROCEDURES

Stored procedure de inserção:
/*Chamamos a StoredProcedure, inserindo uma nova pessoa. Nota que o IdPessoa passado no parametro da nossa SP, tem o valor 0 (ZERO). Fazendo isso, forçamos a inserção na nossa base de dados, pois nunca existirá um usuário com o Id 0(Zero) em nossa base de dados*/

/*Insere um usuario*/
CALL sp_pessoas_IU(0, 'Mauricio', 'Rua Bahia', 'SP', 1);

/*Atualiza o usuário*/
CALL sp_pessoas_IU(1, 'Mauricio', 'Rua Bahia', 'BA', 1);

/*Deletando um usuário*/
CALL sp_pessoas_D(1, 1);


Blz pessoal?
Qualquer dúvida, enviem comentários.
Terei prazer de responder ;)