Olá galera, dando continuidade à série de validação de dados. Agora vou falar como criar a validação de CPF usando um gatilho(trigger) e uma função(function) em MySQL. Para esse post eu usei o script de validação de CPF deste artigo aqui escrito pelo Cristiano Martins Alves, no artigo do Cristiano ele mostra uma função para validação em SQL Server, eu apenas adaptei o código dele para que rode em MySQL, as mudanças foram poucas, quase todas foram em função da diferença de sintaxe(ponto e virgula e loops), da função CONVERT para CAST e da função LEN para LENGTH. Depois das alterações o código ficou da seguinte maneira.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `validarCpf`(CPF CHAR(11)) RETURNS double
BEGIN
	DECLARE INDICE INT;
	DECLARE SOMA INT;
	DECLARE DIG1 INT;
	DECLARE DIG2 INT;
	DECLARE CPF_TEMP VARCHAR(11);
	DECLARE DIGITOS_IGUAIS CHAR(1);
	DECLARE RESULTADO CHAR(1);

	SET RESULTADO = FALSE;

	/*
	Verificando se os dígitos são iguais
	A Principio CPF com todos o números iguais são Inválidos
	apesar de validar o Calculo do digito verificado
	EX: O CPF 00000000000 é inválido, mas pelo calculo
	Validaria
	*/

	SET CPF_TEMP = SUBSTRING(CPF,1,1);

	SET INDICE = 1;
	SET DIGITOS_IGUAIS = 'S';

	WHILE (INDICE <= 11) DO
		IF (SUBSTRING(CPF,INDICE,1) <> CPF_TEMP) Then
			SET DIGITOS_IGUAIS = 'N';
		END IF;
		SET INDICE = INDICE + 1;
	END WHILE;

	/*Caso os dígitos não sejam todos iguais Começo o calculo do dígitos*/
	IF (DIGITOS_IGUAIS = 'N') THEN
		/*Cálculo do 1º dígito*/
		SET SOMA = 0;
		SET INDICE = 1;
		WHILE (INDICE <= 9) DO 			SET Soma = Soma + CAST(SUBSTRING(CPF,INDICE,1) AS UNSIGNED) * (11 - INDICE); 			SET INDICE = INDICE + 1; 		END WHILE; 		SET DIG1 = 11 - (SOMA % 11); 		IF (DIG1 > 9) THEN
			SET DIG1 = 0;
		END IF;

		-- Cálculo do 2º dígito }
		SET SOMA = 0;
		SET INDICE = 1;
		WHILE (INDICE <= 10) DO 			SET Soma = Soma + CAST(SUBSTRING(CPF,INDICE,1) AS UNSIGNED) * (12 - INDICE); 			SET INDICE = INDICE + 1; 		END WHILE; 		SET DIG2 = 11 - (SOMA % 11); 		IF DIG2 > 9 THEN
			SET DIG2 = 0;
		END IF;

		-- Validando
		IF (DIG1 = SUBSTRING(CPF,LENGTH(CPF)-1,1)) AND (DIG2 = SUBSTRING(CPF,LENGTH(CPF),1)) THEN
			SET RESULTADO = TRUE;
		ELSE
			SET RESULTADO = FALSE;
		END IF;

	END IF;
	RETURN RESULTADO;
END

Executando esse código naquela nossa base de testes (download e leitura da parte 01) a função validaCpf() vai ser criada no seu banco de dados, para executar a função basta executar um SELECT normal chamando a função validaCpf(), como no exemplo abaixo.

SELECT validarCpf('02452345798') FROM dual

Nesse caso irá retornar 0(zero) que corresponde a false/falso, caso você coloque um cpf válido no teste ele irá retornar 1(um) que corresponde a true/verdadeiro. Sendo assim agora basta colocarmos na nossa trigger essa chamada para a validação.

-- Trigger DDL Statements
DELIMITER $$

USE `testes_roberson`$$CREATE DEFINER=`root`@`localhost` TRIGGER trgValidacaoEmail BEFORE INSERT ON clientes
FOR EACH ROW BEGIN
    -- Declaro variável para identificar se está válido ou não
    DECLARE email DOUBLE DEFAULT FALSE;
    DECLARE cpf DOUBLE DEFAULT FALSE;

    -- Verificar se e-mail é válido
	IF (NOT EXISTS (SELECT NULL FROM Clientes WHERE  NEW.Email REGEXP '^[a-zA-Z0-9]{1}([\._a-zA-Z0-9-]+)(\.[_a-zA-Z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+){1,3}$')) THEN
        -- Caso seja inválido atribuo true para a variável email
        SET email = TRUE;
	END IF;

	IF ((SELECT validarCpf(NEW.Cpf) FROM dual) = 0 ) THEN
        -- Caso seja inválido atribuo true para a variável cpf
		SET cpf = TRUE;
	END IF;

    -- Verifico se as duas variaveis (email e cpf) estão como true, e mostro o erro
	IF (email && cpf) THEN
        SELECT 0 FROM `Não foi possível inserir os dados! E-mail e CPF inválidos`INTO @error;
    END IF;
    -- Verifico se a variável email está como true, e mostro o erro
    IF (email) THEN
        SELECT 0 FROM `Não foi possível inserir os dados! E-mail inválido`INTO @error;
    END IF;
    -- Verifico se a variável cpf está como true, e mostro o erro
    IF (cpf) THEN
        SELECT 0 FROM `Não foi possível inserir os dados! CPF inválido`INTO @error;
	END IF;
END$$

Criando essa trigger ou alterando a existente do exemplo anterior, a trigger será disparada todas as vezes que algum dado for ser inserido no banco e verificará tanto e-mail quanto CPF. Eu gostaria de ter feito esse exemplo de uma maneira um pouco diferente, queria armazenar em uma variável a string com o erro a cada verificação, e ao final concatenar elas e exibir o erro, porem não consegui que o CONCAT funciona-se dentro da clausula FROM. Caso alguem consiga algo do tipo, por favor me envie que eu atualizo aqui no script, e eu também vou continuar tentando fazer essa alteração.

O exemplo php da parte 01 continua funcionando da mesma maneira, para testarmos basta mudar no insert para um cpf inválido. Quando o E-mail e/ou CPF forem inválidos o erro será mostrado.

Espero ter contribuido com algo, sugestões, criticas ou elogios são muito bem aceitos. Espero que aproveitem e sucesso a todos.

Veja também:
Validando dados inseridos no banco MySql – Parte 01

Neste post pretendo mostrar como fazer o tratamento dos dados inseridos e atualizados no banco de dados. A idéia inicial é que o próprio mysql verifique alguns tipos de dados antes de aceitar a inserção ou alteração dos dados. Isso pode ser útil em empresas onde o DBA(Administrador de banco de dados) não é a mesma pessoa que vai programar o sistema, ou simplesmente quer garantir que todos os dados só entraram no banco se estiverem consistentes. Colocando as verificações diretamente no banco de dados, não dependemos do programa nos enviar dados já validados, não somos reféns do que vem da interface. Para o programador iremos retornar um erro “amigável” dizendo que o dado não foi inserido pois continha erro no campo X ou Y.

Para o exemplo estou disponibilizando para download aqui o script para criação e população de um banco de dados teste (Schema testes_roberson), nos outros posts relacionados a banco de dados também irei utilizar essa base, conforme eu for modificando a base vou atualizando o script de criação e publicando uma notificação de atualização aqui mesmo no blog.

A validação que vou mostrar é bem simples, mas pode dar uma idéia de como criar validações mais completas (pretendo ir colocando aos poucos outras validações mais complexas). Neste exemplo vou mostrar como criar uma Trigger para validar E-mail.

Primeiro faça o download do arquivo .zip onde você encontrará o arquivo testes_roberson.sql e o arquivo leiame.txt(algumas refências sobre o arquivo). Execute o arquivo sql em seu SGBD(Sistema Gerenciador de Banco de Dados), eu uso o MySQL Workbench Tools onde é possível administrar, interagir e modelar bases MySQL, mas pode utilizar qualquer SGBD de sua preferência.

Com o banco, tabelas e dados já criados agora vamos para a análise de possíveis validações que podemos fazer.

Fig. 01 - Modelagem Data Base testes_roberson (schema)

Olhando as tabelas podemos concluir que:

1. Trata-se de um banco simples (para estudos) de um cadastro de clientes.
2. Podemos criar uma rotina para validar CPF, E-mail e talvez ainda o CEP (validar se veio somente numeros e se são 8 caracteres)
3. Podemos criar tabelas de Cidades e Estados para evitar duplicidade de dados.

Nosso objetivo neste post é criar uma trigger para validar e permitir somente e-mails válidos. Segue a baixo código para criação da trigger.

-- Trigger DDL Statements
DELIMITER $$

-- Selecionamos o database desejado e criamos a trigger
-- Usando o BEFORE validamos o dado antes de inserir na tabela e AFTER depois de inserir na tabela
-- INSERT significa que a trigger será disparada quando ocorrer um INSERT na tabela, pode ser usado também o UPDATE e DELETE
USE `testes_roberson`$$CREATE DEFINER=`root`@`localhost` TRIGGER trgValidacaoEmail BEFORE INSERT ON clientes
  FOR EACH ROW BEGIN
    -- Verificamos se o NEW.Email (e-mail que está vindo no INSERT) é válido segundo a expressão regular usado com a função REGEXP
    IF (NOT EXISTS (SELECT NULL FROM Clientes WHERE  NEW.Email REGEXP '^[a-zA-Z0-9]{1}([\._a-zA-Z0-9-]+)(\.[_a-zA-Z0-9-]+)*@[a-z0-9-]+(\.[a-z0-9-]+){1,3}$')) THEN
        -- caso entre no IF disparamos um SELECT com nome de tabela inválido descrevendo o erro que será retornado.
        SELECT 0 FROM `Erro ao inserir dados! E-mail Inválido!` INTO @error;
    -- Fechamos o IF
    END IF;
  -- Fechamos a criação da trigger
  END$$

Com isso a validação já deve estar funcionar. É possível prever uma pequena “gambiarra” (prefiro chamar de ajuste técnico) somente olhando esse script. Quando tentarmos inserir um e-mail inválido ele irá nos retornar o seguinte erro:

Error Code: 1146
Table 'testes_roberson.Erro ao inserir dados! E-mail Inválido!' doesn't exist

Este erro está nos dizendo que a tabela ‘Erro ao inserir dados! E-mail Inválido!’ não existe nessa base de dados. Isso acontece devido ao fato de o nosso querido MySQL não ter nada expecífico para tratamento de erros ou de exceção (Exception). Isso realmente é muito triste, mas pelo menos estamos notificando ao usuário/programador que o dado não foi inserido e que o erro está no e-mail. Se tivermos a possibilidade de conversar com o programador ou programar uma rotina de tratamento de erros na própria linguagem que estiver sendo usada, esse mesmo erro pode ser exibido para o usuário. Para isso basta resgatar esse erro e passar ele por uma função que retire o inicio do erro “Error Code: 1146 – Table ‘testes_roberson.Erro ao inserir dados!” e o final do erro “‘ doesn’t exist”, com isso ficaremos somente com o erro que queremos

Reconheço que o erro retornado não é a melhor coisa do mundo, mas com algumas poucas linhas podemos deixar ele mais limpos, a baixo coloco um script PHP de como poderiamos tratar esse erro e mostrar para o cliente.

//trigger_email.php
// Função para tratamento dos erros
function tratarErrosMysql($numError, $error){
	//identificamos que o erro é no nome da tabela error 1146
	if($numError == '1146'){
		//executamos a decodificação de utf8 para que o erro não apresente problemas com os acentos.
		$error = utf8_decode($error);
		//retiramos o inicio e o fim do erro.
		$error = substr($error, 23 , -15);
		//retornamos o erro que veio do banco
		return $error;
	}else{
		//caso o erro não seja no nome da tabela(retorno de nossa trigger) retornamos o erro do mysql intacto
		return $error;
	}
}
// criamos a conexão com o banco de dados no nosso caso, MySql
$conn = mysqli_connect("localhost", "root", "","testes_roberson");  

//executamos nosso comando sql
$sql = "INSERT INTO Clientes SET Cpf = '05332754906', Nome='Teste', Sobrenome='Teste', Email='isso não é um email válido',sexo='M';"; // query (comando sql)
$result = mysqli_query($conn, $sql);
//verificamos se ouve algum erro
if(mysqli_error($conn)){
	// apresentamos o erro para o cliente;
	echo tratarErrosMysql(mysqli_errno($conn),mysqli_error($conn));
}

Assim teremos o erro tratado, assim teremos uma base de dados bem mais consistente e livre de falhas de verificação na aplicação e erros dos usuários. E não esqueçam de criar uma trigger que valide o dado quando houver uma atualização BEFORE UPDATE.

Em próximos posts pretendo mostrar a validação de CPF usando trigger e stored procedure.

Em muitos momentos no desenvolvimento de um sistema necessitamos trocar informações com outros sistemas (integração entre sistemas). Existem várias formas de conseguirmos essa troca de dados, sendo uma delas a troca de arquivos XML. Nesse post pretendo mostrar uma forma simples de transformar os dados do banco de dados (MySql) em arquivos xml.

Vamos supor uma estrutura de banco de dados assim:

--Criamos a base de dados (schema)
CREATE DATABASE IF NOT EXISTS mysql_xml;

--Selecionamos a base para usar
USE mysql_xml;

--Criamos a tabela, nesse exemplo para guardar dados de usuários
CREATE TABLE 'mysql_xml'.'usuarios' (
        'idUsuario' INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
        'login' VARCHAR(45) NOT NULL,
        'senha' VARCHAR(45) NOT NULL,
        PRIMARY KEY ('idUsuario')
)ENGINE = InnoDB;

--Inserimos alguns dados na tabela
INSERT INTO usuarios VALUES ('', 'usuario1','senha1');
INSERT INTO usuarios VALUES ('', 'usuario2','senha2');
INSERT INTO usuarios VALUES ('', 'usuario3','senha3');
INSERT INTO usuarios VALUES ('', 'usuario4','senha4');
INSERT INTO usuarios VALUES ('', 'usuario5','senha5');

Com o banco/tabela populado podemos ir para o nosso script PHP.
Abaixo vou demonstrar como o php pode pegar esses dados e utilizando a lib SimpleXML do php, podemos transformar os dados em um arquivo xml.

//mysql_xml.php
// criamos a conexao com o banco de dados no nosso caso, MySql
$conn = mysqli_connect("localhost", "root", "","mysql_xml");

//exacutamos nosso comando sql
$sql = "select * from usuarios"; // query (comando sql)
$result = mysqli_query($conn, $sql);

// criamos um objeto do tipo SimpleXMLElement
$xml = new SimpleXMLElement("<resultset/>");

// criamos um loop para recuperar todos os dados da tabela usuarios
// os dados sao retornados como um objeto
while ($dados = mysqli_fetch_object($result))
{
        // criamos um um novo node para abrigar as linhas
        $line = $xml->addChild("line");

        //No proximo loop adicionamos os nodes para cada um dos registros de cada linha
        foreach ( $dados as $colName => $value )
                $line->addChild($colName, $value);
}

// finalmente criamos o xml com todos os dados que estavam na tabela.
echo $xml->asXML();

Executando o arquivo acima temos o seguinte resultado.

		1
		usuario1
		senha1
	

		2
		usuario2
		senha2
	

		3
		usuario3
		senha3
	

		4
		usuario4
		senha4
	

		5
		usuario5
		senha5
	

E é isso galera, simples assim, esses scripts foram baseados em um tutorial que li há algum tempo atrás, desde então venho utilizando essa maneira de gerar xml para exportação, integração, etc, em meus sistemas. Infelizmente não lembro e não encontrei a referência para o artigo que li, caso alguem ache ele, por favor me informe que atualizo aqui.