DROP TABLE IF EXISTS tb_username;
CREATE TABLE
tb_username(
id int AUTO_INCREMENT PRIMARY KEY,
username varchar(50) UNIQUE,
dataReg TIMESTAMP
);
DROP TABLE IF EXISTS tb_estado_username;
CREATE TABLE
tb_estado_username(
id int AUTO_INCREMENT PRIMARY KEY,
idUsername int,
estado char(1) DEFAULT 0,
idAdmin int DEFAULT 1,
dataReg timestamp
);
DROP TABLE IF EXISTS tb_senha;
CREATE TABLE
tb_senha(
id int AUTO_INCREMENT PRIMARY KEY,
idUsername int,
senha varchar(100),
UNIQUE KEY (idUsername, senha),
dataReg TIMESTAMP
);
DROP TABLE IF EXISTS tb_user_privilegio;
CREATE TABLE
tb_user_privilegio(
id int AUTO_INCREMENT PRIMARY KEY,
idUsername int,
idPrivilegio int,
idAdmin int,
dataReg TIMESTAMP
);
DROP TABLE IF EXISTS tb_privilegio;
CREATE TABLE
tb_privilegio(
id int AUTO_INCREMENT PRIMARY KEY,
privilegio varchar(20) UNIQUE
);
-- --------------------------------------------
-- --------------------------------------------
-- ------------------- VIEWS ------------------
-- --------------------------------------------
-- --------------------------------------------
/*
SELECT * FROM tb_estado_username WHERE estado <> 0;
SELECT MAX(id) FROM tb_estado_username WHERE estado <> 0 GROUP BY idUSername;
SELECT * FROM tb_estado_username WHERE id IN(2);
SELECT idUsername FROM tb_estado_username WHERE id IN(SELECT MAX(id) FROM tb_estado_username WHERE estado <> 0 GROUP BY idUSername);
*/
DROP VIEW IF EXISTS v_max_username;
CREATE VIEW v_max_username AS
SELECT *
FROM tb_estado_username
WHERE id IN(
SELECT MAX(id)
FROM
tb_estado_username
GROUP BY idUSername
);
DROP VIEW IF EXISTS v_max_username_ativo;
CREATE VIEW v_max_username_ativo AS
SELECT idUsername, username
FROM tb_username
JOIN v_max_username ON tb_username.id = v_max_username.idUsername
WHERE estado <> 0;
/*
SELECT * FROM tb_username JOIN v_max_username ON tb_username.id = v_max_username.idUsername;
SELECT idUsername, username FROM v_max_username_ativo;
*/
-- --------------------------------------------
-- --------------------------------------------
-- ----------------- FUNÇÕES ------------------
-- --------------------------------------------
-- --------------------------------------------
DROP FUNCTION IF EXISTS F_TESTAR_USERNAME;
CREATE FUNCTION F_TESTAR_USERNAME(A TEXT
/* USERNAME */
) RETURNS INT BEGIN
DECLARE resultado, total int;
SET total = (SELECT COUNT(id) FROM tb_username WHERE username=a);
IF total = 1 THEN -- Existe username
SET resultado = 1;
ELSE -- Não existe username
SET resultado = 0;
END IF;
RETURN resultado;
END;
DROP FUNCTION IF EXISTS F_TESTAR_TOKEN;
CREATE FUNCTION F_TESTAR_TOKEN(A INT, B TEXT /* TOKEN */)
RETURNS INT BEGIN
DECLARE resultado, total int;
SET total = (
SELECT COUNT(id)
FROM tb_token
WHERE
idUsername = a
AND token = b
);
IF total = 1 THEN -- Existe username
SET resultado = 1;
ELSE -- Não existe username
SET resultado = 0;
END IF;
RETURN resultado;
END;
DROP FUNCTION IF EXISTS F_TESTAR_SENHA;
CREATE FUNCTION F_TESTAR_SENHA(A INT, B TEXT)
RETURNS INT BEGIN
DECLARE resultado, total int;
SET total = (
SELECT COUNT(id)
FROM tb_senha
WHERE
idUsername = a
AND senha = sha1(b)
);
IF total = 1 THEN SET resultado = 1;
ELSE SET resultado = 0;
END IF;
RETURN resultado;
END;
DROP FUNCTION IF EXISTS F_REG_USER;
CREATE FUNCTION F_REG_USER(A TEXT)
RETURNS INT BEGIN
DECLARE idUsername int;
INSERT INTO tb_username (username) VALUES (a);
SET idUsername = LAST_INSERT_ID();
RETURN idUsername;
END;
-- testar se o username não está por validar
-- criamos esta função para ser usada também no pedido de alteração de senha
DROP FUNCTION IF EXISTS F_TESTAR_VALIDADE_USERNAME;
CREATE FUNCTION F_TESTAR_VALIDADE_USERNAME(A TEXT)
RETURNS INT BEGIN
DECLARE total int;
SET total = (
SELECT idUsername
FROM
v_max_username_ativo
WHERE username = a
);
IF total = 1 THEN SET total=1;
ELSE SET total=0;
END IF;
RETURN total;
END;
/*
SELECT F_TESTAR_VALIDADE_USERNAME('admin'); */
-- --------------------------------------------
-- --------------------------------------------
-- -------------- PROCEDURES ------------------
-- --------------------------------------------
-- --------------------------------------------
-- remover token antigos
DROP PROCEDURE IF EXISTS P_REMOVER_TOKEN_INVALIDO;
CREATE PROCEDURE P_REMOVER_TOKEN_INVALIDO()
BEGIN
DECLARE hora, idU, total int;
SET hora = 48;
SET idU = (
SELECT idUsername
FROM tb_token
WHERE (
HOUR(
TIMEDIFF(CURRENT_TIMESTAMP, dataReg)
)
) > hora
);
SET total = (
SELECT
COUNT(idUsername)
FROM tb_token
WHERE (
HOUR(
TIMEDIFF(CURRENT_TIMESTAMP, dataReg)
)
) > hora
);
IF total=0 THEN SET @msg = 0;
ELSE DELETE FROM tb_token WHERE idUsername = idU;
SET @msg = 1;
END IF;
SELECT @msg AS resultado;
END;
DROP PROCEDURE IF EXISTS P_REMOVER_USERNAME_INVALIDO;
CREATE PROCEDURE P_REMOVER_USERNAME_INVALIDO()
BEGIN
DECLARE hora, idU, total int;
SET hora = 48;
SET idU = (
SELECT idUsername
FROM v_max_username
WHERE
estado = 0
AND (
HOUR(
TIMEDIFF(CURRENT_TIMESTAMP, dataReg)
)
) > hora
);
SET total = (
SELECT
COUNT(idUsername)
FROM v_max_username
WHERE
estado = 0
AND (
HOUR(
TIMEDIFF(CURRENT_TIMESTAMP, dataReg)
)
) > hora
);
IF total=0 THEN SET @msg = 0;
ELSE DELETE FROM tb_token WHERE idUsername = idU;
DELETE FROM tb_username WHERE id = idU;
DELETE FROM tb_estado_username WHERE id = idU;
SET @msg = 1;
END IF;
SELECT @msg AS resultado;
END;
DROP PROCEDURE IF EXISTS P_CARREGAR_PRIVILEGIO;
CREATE PROCEDURE P_CARREGAR_PRIVILEGIO(IN A INT, B TEXT)
BEGIN
DECLARE total, va, contador int;
IF a < 0 THEN SET va = -a;
ELSE SET va = a;
END IF;
SET contador = 1;
WHILE contador <= va
DO
INSERT INTO
tb_privilegio (privilegio)
VALUES (CONCAT(b, ' ', contador));
SET contador = contador + 1;
END WHILE;
END;
/*
CALL P_CARREGAR_PRIVILEGIO(5,'Access level'); */
DROP PROCEDURE IF EXISTS P_TRUNCATE_TABLES;
CREATE PROCEDURE P_TRUNCATE_TABLES()
BEGIN
TRUNCATE tb_estado_username ;
TRUNCATE tb_senha ;
TRUNCATE tb_token;
TRUNCATE tb_username;
TRUNCATE tb_user_privilegio ;
END;
DROP PROCEDURE IF EXISTS P_REGISTAR_TOKEN;
CREATE PROCEDURE P_REGISTAR_TOKEN(IN A INT)
BEGIN
INSERT INTO tb_token (idUsername) VALUES (a);
END;
DROP PROCEDURE IF EXISTS P_ESTADO_USER_REGISTO;
CREATE PROCEDURE P_ESTADO_USER_REGISTO(IN A INT)
BEGIN
INSERT INTO tb_estado_username (idUsername, estado, idAdmin) VALUES (a, 0, 1);
END;
DROP PROCEDURE IF EXISTS P_REGISTAR_USERNAME;
CREATE PROCEDURE P_REGISTAR_USERNAME(IN A TEXT)
BEGIN
DECLARE teste, idUsername int;
SET teste = F_TESTAR_USERNAME(a);
IF teste = 0 THEN SET idUsername = F_REG_USER(a);
CALL P_REGISTAR_TOKEN(idUsername);
CALL P_ESTADO_USER_REGISTO(idUsername);
END IF;
END;
DROP PROCEDURE IF EXISTS P_APAGAR_TOKEN;
CREATE PROCEDURE P_APAGAR_TOKEN(IN A INT, B TEXT)
BEGIN
DELETE FROM tb_token WHERE idUsername = a AND token = b;
END;
DROP PROCEDURE IF EXISTS P_ESTADO_USER_ATIVACAO;
CREATE PROCEDURE P_ESTADO_USER_ATIVACAO(IN A INT)
BEGIN
INSERT INTO tb_estado_username (idUsername, estado, idAdmin) VALUES (a, 1, 1);
END;
DROP PROCEDURE IF EXISTS P_PRIVILEGIO_USER_REGISTO;
CREATE PROCEDURE P_PRIVILEGIO_USER_REGISTO(IN A INT)
BEGIN
INSERT INTO tb_user_privilegio (idUsername, idPrivilegio, idAdmin) VALUES (a, 1, 1);
END;
DROP PROCEDURE IF EXISTS P_REGISTAR_SENHA;
CREATE PROCEDURE P_REGISTAR_SENHA(IN A INT, B TEXT)
BEGIN
INSERT INTO tb_senha (idUsername, senha) VALUES (a, SHA1(b));
SELECT 1 AS resultado;
END;
DROP PROCEDURE IF EXISTS P_ATIVAR_USERNAME;
CREATE PROCEDURE P_ATIVAR_USERNAME(IN a int /* idUsername */, b text /* token*/, c text /* senha */)
BEGIN
DECLARE testeToken, testeSenha int;
SET testeToken = F_TESTAR_TOKEN(a,b);
SET testeSenha = F_TESTAR_SENHA(a,c);
IF (testeToken = 1 AND testeSenha = 0) THEN
CALL P_APAGAR_TOKEN(a,b);
CALL P_ESTADO_USER_ATIVACAO(a);
CALL P_PRIVILEGIO_USER_REGISTO(a);
CALL P_REGISTAR_SENHA(a,c);
SET @msg = 1;
ELSE
SET @msg = 0;
END IF;
SELECT @msg AS resultado;
END;
DROP PROCEDURE IF EXISTS P_PEDIR_RECUPERACAO_SENHA;
CREATE PROCEDURE P_PEDIR_RECUPERACAO_SENHA(IN a text /* username */)
BEGIN
-- Testar se existe username e se está válido: estado diferente de zero
DECLARE idU int;
SET idU = F_TESTAR_VALIDADE_USERNAME(a);
IF idU > 0 THEN
CALL P_REGISTAR_TOKEN(idU);
SET @msg=1;
ELSE
SET @msg=0;
END IF;
SELECT @msg AS resultado;
END;
DROP PROCEDURE IF EXISTS P_REGISTAR_NOVA_SENHA;
CREATE PROCEDURE P_REGISTAR_NOVA_SENHA(IN a int /* idUsername */, b text /* token*/, c text /* senha */)
BEGIN
DECLARE testeToken, testeSenha int;
SET testeToken = F_TESTAR_TOKEN(a,b);
SET testeSenha = F_TESTAR_SENHA(a,c);
IF (testeToken = 1 AND testeSenha = 0) THEN
CALL P_APAGAR_TOKEN(a,b);
CALL P_REGISTAR_SENHA(a,c);
SET @msg = 1;
ELSE
SET @msg = 0;
END IF;
SELECT @msg AS resultado;
END;
CALL P_REGISTAR_USERNAME('manuel');
CALL P_ATIVAR_USERNAME(1, '2beb72d5cc92b52a5ac10d24108577', '123');
CALL P_PEDIR_RECUPERACAO_SENHA('admin');
CALL P_REGISTAR_NOVA_SENHA(1, 'a6b3a1778d0fec42c71c22c32df2b6', '321');
CALL P_REMOVER_USERNAME_INVALIDO;
CALL P_REMOVER_TOKEN_INVALIDO;
Sem comentários:
Enviar um comentário