-- Active: 1717578753238@@127.0.0.1@3306@tapw
-- Vamos dar inicio à criação das TABLElas
-- partindo do MER da figura.
DROP TABLE IF EXISTS tb_utilizador;
CREATE TABLE tb_utilizador(
idUtilizador INT AUTO_INCREMENT,
dataReg TIMESTAMP,
PRIMARY KEY(idUtilizador)
);
DROP TABLE IF EXISTS tb_username;
CREATE TABLE tb_username(
idusername INT AUTO_INCREMENT,
idUtilizador INT NOT NULL,
username VARCHAR (50) NOT NULL,
dataReg TIMESTAMP,
PRIMARY KEY (idUsername),
FOREIGN KEY (idUtilizador) REFERENCES tb_utilizador (idUtilizador),
UNIQUE KEY (username)
);
DROP TABLE IF EXISTS tb_token;
CREATE TABLE tb_token(
idToken INT AUTO_INCREMENT,
idUsername INT NOT NULL,
token VARCHAR(50) NOT NULL,
dataReg TIMESTAMP,
PRIMARY KEY(idToken),
FOREIGN KEY (idUsername) REFERENCES tb_username (idUsername),
UNIQUE KEY(token)
);
DROP TABLE IF EXISTS tb_estado_utilizador;
CREATE TABLE tb_estado_utilizador(
idEstadoUtilizador INT AUTO_INCREMENT,
idUtilizador INT NOT NULL,
estado VARCHAR(50) NOT NULL,
dataReg TIMESTAMP,
PRIMARY KEY (idEstadoUtilizador),
FOREIGN KEY(idUtilizador) REFERENCES tb_utilizador (idUtilizador)
);
DROP TABLE IF EXISTS tb_estado_username;
CREATE TABLE tb_estado_username(
idEstadoUsername INT AUTO_INCREMENT,
idUsername INT NOT NULL,
estado VARCHAR(50) NOT NULL,
dataReg TIMESTAMP,
PRIMARY KEY(idEstadoUsername),
FOREIGN KEY(idUsername) REFERENCES tb_username (idUsername)
);
DROP TABLE IF EXISTS tb_senha;
CREATE TABLE tb_senha (
idSenha INT AUTO_INCREMENT,
idUtilizador INT NOT NULL,
senha VARCHAR(50) NOT NULL,
dataReg TIMESTAMP,
PRIMARY KEY(idSenha),
FOREIGN KEY(idUtilizador) REFERENCES tb_utilizador (idUtilizador),
UNIQUE index_senha_utilizador (senha, idUtilizador)
);
DROP TABLE IF EXISTS tb_perfil_utilizador;
CREATE TABLE tb_perfil_utilizador(
idPerfilUtilizador INT AUTO_INCREMENT,
idUtilizador INT NOT NULL,
perfil VARCHAR(50),
dataReg TIMESTAMP,
PRIMARY KEY(idPerfilUtilizador),
FOREIGN KEY(idUtilizador) REFERENCES tb_utilizador (idUtilizador)
);
DROP TABLE IF EXISTS tb_sessao;
CREATE TABLE tb_sessao(
idSessao INT AUTO_INCREMENT,
idUtilizador INT NOT NULL,
tipo VARCHAR(50),
dataReg TIMESTAMP,
IP VARCHAR(50) NULL,
MAC VARCHAR(50) NULL,
PRIMARY KEY(idSessao),
FOREIGN KEY(idUtilizador) REFERENCES tb_utilizador (idUtilizador)
);
DROP TABLE IF EXISTS tb_lista_negra;
CREATE TABLE tb_lista_negra(
idListaNegra INT AUTO_INCREMENT,
idUtilizador INT NOT NULL,
estado VARCHAR(50),
dataReg TIMESTAMP,
PRIMARY KEY(idListaNegra),
FOREIGN KEY(idUtilizador) REFERENCES tb_utilizador (idUtilizador)
);
-- PROCEDIMENTO de registo de utilizador
-- 1º fase porque a segunda é a ativação
DROP PROCEDURE IF EXISTS p_registar_utilizador;
DELIMITER //
CREATE PROCEDURE p_registar_utilizador (IN a TEXT)
BEGIN
DECLARE idUt INT;
DECLARE totalUtilizadores INT;
DECLARE idUser INT;
DECLARE tokenSorteado TEXT;
DECLARE totalTokens INT;
DECLARE msg TEXT;
SET totalUtilizadores = (SELECT COUNT(*) FROM tb_username WHERE username = a);
IF totalUtilizadores = 0 THEN
-- REGIsto
INSERT INTO tb_utilizador VALUES (NULL, NULL);
SET idUt = LAST_INSERT_ID(); -- idUtilizador (idUt)
INSERT INTO tb_username VALUES (NULL, idUt, a, NULL);
SET idUser = LAST_INSERT_ID();
SET totalTokens = 1;
WHILE totalTokens = 1 DO
-- sortear
SET tokenSorteado = substring(sha1(rand()),1,5);
SET totalTokens = (SELECT COUNT(*) FROM tb_token WHERE token = tokenSorteado);
END WHILE;
INSERT INTO tb_token VALUES (NULL, idUser, tokenSorteado, NULL);
INSERT INTO tb_estado_utilizador VALUES (NULL, idUt, 'Inativo', NULL);
INSERT INTO tb_estado_username VALUES (NULL, idUser, 'por validar', NULL);
SET msg = "Sucesso";
ELSE
-- não registou
SET msg ="Insucesso";
END IF;
SELECT msg AS resultado;
END //
DELIMITER ;
CALL p_registar_utilizador('neka@mail.pt');
DROP PROCEDURE IF EXISTS p_validar_utilizador;
DELIMITER //
CREATE PROCEDURE p_validar_utilizador(IN a TEXT /* token */ , b INT /* idUtilizador */ , c TEXT /* senha */)
BEGIN
DECLARE idUser INT; -- idUsername
DECLARE totalToken INT;
DECLARE msg TEXT;
DECLARE totalSenha INT;
DECLARE codigoSenha TEXT;
SET idUser = (SELECT idUsername FROM tb_username WHERE idUtilizador = b);
SET totalToken = (SELECT COUNT(*) FROM tb_token WHERE idUsername = idUser AND token = a);
SET codigoSenha = SHA1(c);
SET totalSenha = (SELECT COUNT(*) FROM tb_senha WHERE idUtilizador = b AND senha = codigoSenha);
IF (totalToken = 1 AND totalSenha=0) THEN -- Podemos avançar para a ativação
DELETE FROM tb_token WHERE token = a AND idUsername = idUser; -- pode haver aqui um problema
INSERT INTO tb_estado_username VALUES (NULL, idUser, 'valido', NULL);
INSERT INTO tb_estado_utilizador VALUES (NULL, b, 'ativo', NULL);
INSERT INTO tb_perfil_utilizador VALUES (NULL, b, 'level_1', NULL);
INSERT INTO tb_senha VALUES (NULL, b, codigoSenha, NULL);
SET msg = "Utilizador ativado e username validado com sucesso";
ELSE
IF totalSenha = 1 THEN
SET msg = "Não pode repetir uma senha já utilizada";
END IF;
IF totalToken = 0 THEN
SET msg = "Sem token";
END IF;
END IF;
SELECT msg AS resultado;
END //
DELIMITER ;
select * from tb_token;
CALL p_validar_utilizador("f8f0b", 1, "123");
DROP PROCEDURE IF EXISTS p_validar_utilizador_simplificado;
DELIMITER //
CREATE PROCEDURE p_validar_utilizador_simplificado(IN a TEXT /* token */ , b INT /* idUtilizador */ , c TEXT /* senha */)
BEGIN
DECLARE idUser INT; -- idUsername
DECLARE totalToken INT;
DECLARE msg TEXT;
DECLARE codigoSenha TEXT;
SET idUser = (SELECT idUsername FROM tb_username WHERE idUtilizador = b);
SET totalToken = (SELECT COUNT(*) FROM tb_token WHERE idUsername = idUser AND token = a);
SET codigoSenha = SHA1(c);
IF (totalToken = 1 ) THEN -- Podemos avançar para a ativação
DELETE FROM tb_token WHERE token = a AND idUsername = idUser; -- pode haver aqui um problema
INSERT INTO tb_estado_username VALUES (NULL, idUser, 'valido', NULL);
INSERT INTO tb_estado_utilizador VALUES (NULL, b, 'ativo', NULL);
INSERT INTO tb_perfil_utilizador VALUES (NULL, b, 'level_1', NULL);
INSERT INTO tb_senha VALUES (NULL, b, codigoSenha, NULL);
SET msg = "Utilizador ativado e username validado com sucesso";
ELSE
SET msg = "Sem token";
END IF;
SELECT msg AS resultado;
END //
DELIMITER ;
/*
próximos procedimentos
procedimentos
/* Registo do utilizador
1
p_registar_utilizador;
INSERT
tb_utilizador, tb_username, tb_token, tb_estado_username
*/
/* Validação do utilizador
2
p_validar_username
DELETE tb_token
INSERT tb_estado_username, tb_estado_utilizador, tb_perfil_utilizador, tb_senha
*/
/* Pedido de recuperação ou alteração de senha
3
p_recuperar_senha
INSERT tb_token
*/
/* Registo de nova senha
4
p_registar_senha
DELETE tb_token
INSERT tb_senha
*/
/* Abertura de sessão
5
p_abrir_sessao
INSERT tb_sessao
*/
/* Encerramento de sessão
6
p_fechar_sessao
INSERT tb_sessao
*/
/* erro de login
7
p_registar_lista_negra
INSERT tb_lista_negra
*/
/* Alterar perfil de utilizador
8
INSERT tb_perfil_utilizador
*/
/* Alterar estado de utilizador
9
INSERT tb_estado_utilizador
*/
-- views
DROP VIEW IF EXISTS v1_senha;
CREATE VIEW v1_senha AS
SELECT idSenha, idUtilizador, senha FROM tb_senha WHERE idSenha IN (SELECT MAX(idSenha) FROM tb_senha GROUP BY (idUtilizador));
SELECT * FROM v1_senha;
SELECT idUtilizador, username, senha
FROM tb_username JOIN
v1_senha USING(idUtilizador);
DROP VIEW IF EXISTS v1_estado_utilizador;
CREATE VIEW v1_estado_utilizador AS
SELECT idEstadoUtilizador, idUtilizador, estado FROM tb_estado_utilizador WHERE idEstadoUtilizador IN (SELECT MAX(idEstadoUtilizador) FROM tb_estado_utilizador GROUP BY (idUtilizador));
SELECT * FROM v1_estado_utilizador;
SELECT idUtilizador, username, senha
FROM tb_username JOIN
v1_senha USING(idUtilizador) JOIN
v1_estado_utilizador USING(idUtilizador)
WHERE estado = 'ativo';
SELECT * FROm tb_perfil_utilizador;
DROP VIEW IF EXISTS v1_perfil_utilizador;
CREATE VIEW v1_perfil_utilizador AS
SELECT idPerfilUtilizador, idUtilizador, perfil FROM tb_perfil_utilizador WHERE idPerfilUtilizador IN (SELECT MAX(idPerfilUtilizador) FROM tb_perfil_utilizador GROUP BY (idUtilizador));
DROP VIEW IF EXISTS v2_login;
CREATE VIEW v2_login AS
SELECT idUtilizador, username, senha, perfil
FROM tb_username JOIN
v1_senha USING(idUtilizador) JOIN
v1_estado_utilizador USING(idUtilizador)
JOIN v1_perfil_utilizador USING(idUtilizador)
WHERE estado = 'ativo';
SELECT * FROM v2_login;
-- Datas
SELECT TIMESTAMPDIFF(HOUR, NOW(), SELECT NOW());
SELECT TIMESTAMPDIFF(HOUR, '2024-07-12 07:10:49', NOW()); -- horas
SELECT TIMESTAMPDIFF(MINUTE, '2024-07-12 09:10:49', NOW()); -- minuto
SELECT * FROM tb_lista_negra;
INSERT INTO tb_lista_negra VALUES (NULL, 1, 'ativo', '2024-07-12 09:00:00');
INSERT INTO tb_lista_negra VALUES (NULL, 1, 'ativo', '2024-07-12 09:01:00');
INSERT INTO tb_lista_negra VALUES (NULL, 2, 'ativo', '2024-07-12 09:00:00');
INSERT INTO tb_lista_negra VALUES (NULL, 2, 'ativo', '2024-07-12 09:01:00');
INSERT INTO tb_lista_negra VALUES (NULL, 2, 'ativo', '2024-07-12 09:02:00');
INSERT INTO tb_lista_negra VALUES (NULL, 3, 'ativo', '2024-07-12 09:05:00');
SELECT * FROM tb_lista_negra;
DROP VIEW IF EXISTS v1_minutos_lista_negra;
CREATE VIEW v1_minutos_lista_negra AS
SELECT idListaNegra, TIMESTAMPDIFF(MINUTE, dataReg, NOW()) AS minutos FROM tb_lista_negra;
SELECT * FROM v1_minutos_lista_negra;
SELECT *
FROM tb_lista_negra
JOIN v1_minutos_lista_negra USING(idListaNegra)
WHERE minutos > 10;
SELECT * FROm tb_lista_negra;
DROP VIEW IF EXISTS v1_total_lista_negra;
CREATE VIEW v1_total_lista_negra AS
SELECT idUtilizador, COUNT(idUtilizador) AS total FROM tb_lista_negra GROUP BY (idUtilizador);
SELECT * FROM v1_total_lista_negra;
DROP VIEW IF EXISTS v2_login;
CREATE VIEW v2_login AS
SELECT idUtilizador, username, senha, perfil
FROM tb_username JOIN
v1_senha USING(idUtilizador) JOIN
v1_estado_utilizador USING(idUtilizador)
JOIN v1_perfil_utilizador USING(idUtilizador)
JOIN v1_total_lista_negra USING(idUtilizador)
WHERE estado = 'ativo' AND total < 3;
SELECT * FROM v2_login;
Sem comentários:
Enviar um comentário