[Resolvido] Events no MySQL - Confirmação de usuários

[Andrey Knupp Vital 136]

Olá gente, tive uma ideia meio maluca aqui, mais que fica bem bacana fazer de tal modo.

vamos supor que o cadastro do seu site tem que ser validado em N dias, porem seu site

tem vários cadastros por dia, e boa parte desses cadastros não são validados, por que tem sempre

um engraçadinho que cadastra com e-mail inválido, etc .. não desejamos ficar guardando um espaço

em nosso banco de dados para esses caras que não querem nada com a vida, apenas atrapalhar

o serviço dos outros, não é ? então podemos fazer uma varredura disso sem mais nem menos.

a partir do MySQL 5.1, liberaram um recurso muito show, que nos permite criar 'eventos' internos

no banco de dados, como se fosse uma 'cron job' no mysql, isso facilita bastante, agente que não quer

ficar esperando o cara abrir a página, pra dai sim disparar uma ação no banco de dados, entre outros

meios bizzaros que existem; como podemos fazer isso para poupar nosso tempo e excluir esses indesejáveis ?

vamos a um exemplo simples, criando 3 tabelas, o código vai usar 2 events, um pra remover de uma tabela

que é usada diariamente, e outro que vai remover de uma tabela temporária esses dados do banco de dados

ou seja, tacar logo um shift del no cara ! :P

vamos lá, primeiro vamos criar as tabelas com alguns registros

DROP TABLE IF EXISTS `confirmacoes`;
CREATE TABLE IF NOT EXISTS `confirmacoes` (
 `uid` mediumint(16) unsigned NOT NULL,
 `confirmacao` varchar(32) NOT NULL,
 `enviada` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

INSERT INTO `confirmacoes` (`uid`, `confirmacao`, `enviada`) VALUES
(1, 'vjtoh@e8*szg5&a7knl+9y2!p#4mxbr3', '2011-06-19 15:41:09'),
(2, 'wz9a8%#ftçy&e5ck+bnv2g4j@d7spmo$', '2011-06-29 15:41:17'),
(3, 'u$ebrj+cg*6sh2!1@fy%4z9to&kxlp7n', '2011-07-20 15:41:25');

DROP TABLE IF EXISTS `temp`;
CREATE TABLE IF NOT EXISTS `temp` (
 `uid` mediumint(16) unsigned NOT NULL,
 `nome` varchar(15) NOT NULL,
 `sobrenome` varchar(26) NOT NULL,
 `email` varchar(72) NOT NULL,
 `confirmacao` varchar(32) NOT NULL,
 `temptime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC;

DROP TABLE IF EXISTS `usuarios`;
CREATE TABLE IF NOT EXISTS `usuarios` (
 `uid` mediumint(16) unsigned NOT NULL AUTO_INCREMENT,
 `nome` varchar(15) NOT NULL,
 `sobrenome` varchar(26) NOT NULL,
 `email` varchar(72) NOT NULL,
 `cadastro` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `confirmado` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`uid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC AUTO_INCREMENT=5 ;

INSERT INTO `usuarios` (`uid`, `nome`, `sobrenome`, `email`, `cadastro`, `confirmado`) VALUES
(1, 'Andrey', 'Knupp', 'andreykvital@gmail.com', '2011-06-19 15:11:50', 0),
(2, 'Marcos', 'Ferreira', 'marcos@hotmail.com', '2011-06-19 15:12:47', 0),
(3, 'Julio', 'Cesar', 'julio@hotmail.com', '2011-06-19 15:39:30', 0),
(4, 'Fernando', 'Oliveira', 'fernando@hotmail.com', '2011-06-19 15:39:31', 1);

percebam que eu tenho 3 usuários não confirmados, um dos eventos, vai verificar

na tabela de usuários onde o campo 'confirmado' seje '0', então vai pra outra condição

verificando na tabela de chaves, se essa chave já foi 'vencida' por assim dizer.

então remove todos os usuários que não foram confirmados no intervalo de 1 dia

obs: o tempo de execução dos eventos são de 30 minutos, segue o código abaixo

para criação dos eventos.

DELIMITER $$

DROP EVENT `deleteUnconfirmed`$$
CREATE EVENT `deleteUnconfirmed` ON SCHEDULE EVERY 30 MINUTE STARTS '2011-06-19 15:58:03' ON COMPLETION PRESERVE ENABLE DO BEGIN
 INSERT INTO `temp`( `uid`, `nome`, `sobrenome`, `email`, `confirmacao` ) 
 SELECT usuarios.uid, usuarios.nome, usuarios.sobrenome, usuarios.email, confirmacoes.confirmacao
 FROM `usuarios`  INNER JOIN `confirmacoes` ON `usuarios`.uid = `confirmacoes`.uid 
 WHERE usuarios.confirmado = 0 AND confirmacoes.enviada > CURRENT_TIMESTAMP + INTERVAL 1 DAY;

 DELETE usuarios, confirmacoes FROM `usuarios` INNER JOIN `confirmacoes` ON `usuarios`.uid = `confirmacoes`.uid
 WHERE usuarios.confirmado = 0 AND confirmacoes.enviada > CURRENT_TIMESTAMP + INTERVAL 1 DAY;
END$$

DROP EVENT `deleteTemp`$$
CREATE EVENT `deleteTemp` ON SCHEDULE EVERY 30 MINUTE STARTS '2011-06-19 16:10:59' ON COMPLETION PRESERVE ENABLE DO BEGIN
  DELETE FROM `temp` WHERE temp.temptime > CURRENT_TIMESTAMP + INTERVAL 2 DAY;
END$$

DELIMITER ;

o primeiro evento, ainda dá uma chance do usuário de fazer a sua confirmação do seu usuário porem

vocês vão selecionar da tabela temp, a confirmação juntamente com o e-mail, o segundo evento

já remove todos os usuários que não tiveram suas contas confirmadas em 2 dias, o script php

pra fazer a confirmação dos usuários nas tabelas.

<!doctype html>
<html>
   <head>
       <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
   </head>
   <body>
       <?php
            $PDO = new PDO( 'mysql:host=localhost;dbname=db', 'root', 'sua senha' );
            $PDO->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
            $chaveConfirmacao = utf8_decode( $_GET[ 'keyCode' ] );
            $email = isset( $_GET[ 'email' ] ) ? $_GET[ 'email' ] : NULL;
            if( !empty( $email ) && $_GET['comType'] == 'normal' ){
                $query = $PDO->prepare( 'DELETE FROM `confirmacoes` WHERE `confirmacao` = :chave' );
                $query->bindParam( ':chave', $chaveConfirmacao, PDO::PARAM_STR );
                if( $query->execute() == true ){
                    $query = $PDO->prepare( 'UPDATE `usuarios` SET `confirmado` = 1 WHERE `email` = :email' );
                    $query->bindParam( ':email', $email, PDO::PARAM_STR );
                    if( $query->execute() == true ){
                          echo 'Usuário confirmado com sucesso';
                    }else{
                        printf( 'Não foi possivel confirmar o e-mail <b>%s</b>', $email );
                    }
                }else{
                    printf( 'Não foi possivel encontrar uma chave de confirmação para o e-mail <b>%s</b>', $email );
                }
            }elseif( !empty( $_GET['email'] ) && $_GET['comType'] == 'temp' ){
                $query = $PDO->prepare( 'SELECT * FROM `temp` WHERE `email` = :email AND `confirmacao` = :chave' );
                $query->bindParam( ':email', $email, PDO::PARAM_STR );
                $query->bindParam( ':chave', $chaveConfirmacao, PDO::PARAM_STR );
                $query->execute();
                if( $query->rowCount() != false ){
                    $dados = $query->fetch( PDO::FETCH_ASSOC );
                    unset( $dados[ 'temptime' ], $dados[ 'uid' ], $dados[ 'confirmacao' ] );
                    foreach( $dados as $index => $data ){
                        $fields[ $index ] = '`'.$index.'`';
                        $values[ $index ] = !is_numeric( $data ) ? sprintf( "'%s'", $data ) : (integer)$data;
                    }
                    $query = $PDO->prepare( sprintf( 'INSERT INTO `usuarios`( %s, `confirmado`  ) VALUES( %s, 1 )', implode( ',', $fields ), implode( ',', $values ) ) );
                    if( $query->execute() ){
                        $query = $PDO->prepare( 'DELETE FROM `temp` WHERE `email` = :email AND `confirmacao` = :chave' );
                        $query->bindParam( ':email', $email, PDO::PARAM_STR );
                        $query->bindParam( ':chave', $chaveConfirmacao, PDO::PARAM_STR );
                        if( $query->execute() ){
                            echo 'Usuário confirmado com sucesso';
                        }
                    }else{
                        printf( 'Não foi possivel confirmar o e-mail <b>%s</b>', $email );
                    }
                }else{
                   printf( 'Não foi possivel confirmar o e-mail <b>%s</b>', $email ); 
                }
            }
       ?>
   </body>
</html>

 

;)