SQLize | PHPize | SQLtest

-- ============================================= -- 1. DEFINIÇÃO DE DOMÍNIOS (TIPOS PERSONALIZADOS) -- ============================================= -- Domínio para tipos de usuário CREATE DOMAIN tipo_usuario AS ENUM('Loggado', 'Membro', 'EmArquivo', 'Anonimo', 'Host'); -- Domínio para tipos de operação CREATE DOMAIN tipo_operacao AS ENUM('CRIAR_ARQUIVO', 'DELETAR_ARQUIVO', 'EDITAR_PERFIL', 'ACESSAR_ARQUIVO', 'CONECTAR', 'SALVAR', 'DESENHAR', 'REMOVER_USUARIO'); -- Domínio para níveis de permissão CREATE DOMAIN nivel_permissao AS VARCHAR(20) CHECK (VALUE IN ('Leitura', 'Escrita', 'Administrador', 'Convidado')); -- ============================================= -- 2. TABELAS DE DIMENSÕES (STAR SCHEMA) -- ============================================= -- Tabela Fato principal (central no star schema) CREATE TABLE fato_operacoes ( operacao_id SERIAL PRIMARY KEY, usuario_id INT NOT NULL, arquivo_id VARCHAR(50) NOT NULL, data_operacao TIMESTAMP NOT NULL, tipo_operacao tipo_operacao NOT NULL, quantidade INT DEFAULT 1 ); -- Dimensão Tempo CREATE TABLE dim_tempo ( data_id DATE PRIMARY KEY, dia INT NOT NULL, mes INT NOT NULL, ano INT NOT NULL, trimestre INT NOT NULL, dia_semana VARCHAR(15) NOT NULL, feriado BOOLEAN DEFAULT FALSE ); -- Dimensão Usuário CREATE TABLE dim_usuario ( usuario_id INT PRIMARY KEY, nome VARCHAR(100) NOT NULL, email VARCHAR(100) UNIQUE NOT NULL, tipo tipo_usuario NOT NULL, nivel nivel_permissao, data_cadastro DATE NOT NULL ); -- Dimensão Arquivo CREATE TABLE dim_arquivo ( arquivo_id VARCHAR(50) PRIMARY KEY, nome VARCHAR(100) NOT NULL, tipo VARCHAR(50), tamanho_kb INT, data_criacao DATE NOT NULL, usuario_responsavel INT NOT NULL ); -- ============================================= -- 3. TABELAS OPERACIONAIS (OLTP) -- ============================================= CREATE TABLE Usuario ( id INT PRIMARY KEY AUTO_INCREMENT, tipo tipo_usuario NOT NULL, nome VARCHAR(100), email VARCHAR(100) UNIQUE, senha VARCHAR(255), id_temp INT, nivel nivel_permissao, id_arquivo VARCHAR(50), data_cadastro TIMESTAMP DEFAULT CURRENT_TIMESTAMP, ultima_atualizacao TIMESTAMP ); CREATE TABLE Arquivo ( id VARCHAR(50) PRIMARY KEY, nome VARCHAR(100), data_criacao TIMESTAMP DEFAULT CURRENT_TIMESTAMP, usuario_id INT, tamanho_kb INT, tipo VARCHAR(20), FOREIGN KEY (usuario_id) REFERENCES Usuario(id) ); CREATE TABLE Operacao ( id INT PRIMARY KEY AUTO_INCREMENT, tipo tipo_operacao NOT NULL, usuario_id INT, arquivo_id VARCHAR(50), data TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (usuario_id) REFERENCES Usuario(id), FOREIGN KEY (arquivo_id) REFERENCES Arquivo(id) ); -- ============================================= -- 4. TRIGGERS DE AUDITORIA -- ============================================= -- Tabela de log de auditoria CREATE TABLE auditoria ( id SERIAL PRIMARY KEY, tabela_afetada VARCHAR(50) NOT NULL, operacao VARCHAR(20) NOT NULL, id_registro VARCHAR(50) NOT NULL, usuario VARCHAR(100), data_hora TIMESTAMP DEFAULT CURRENT_TIMESTAMP, valores_antigos JSON, valores_novos JSON ); -- Trigger para auditoria na tabela Usuario DELIMITER // CREATE TRIGGER trg_auditoria_usuario AFTER UPDATE ON Usuario FOR EACH ROW BEGIN INSERT INTO auditoria ( tabela_afetada, operacao, id_registro, usuario, valores_antigos, valores_novos ) VALUES ( 'Usuario', 'UPDATE', OLD.id, CURRENT_USER(), JSON_OBJECT( 'nome', OLD.nome, 'email', OLD.email, 'tipo', OLD.tipo, 'nivel', OLD.nivel ), JSON_OBJECT( 'nome', NEW.nome, 'email', NEW.email, 'tipo', NEW.tipo, 'nivel', NEW.nivel ) ); END// DELIMITER ; -- Trigger para auditoria na tabela Arquivo DELIMITER // CREATE TRIGGER trg_auditoria_arquivo AFTER DELETE ON Arquivo FOR EACH ROW BEGIN INSERT INTO auditoria ( tabela_afetada, operacao, id_registro, usuario, valores_antigos ) VALUES ( 'Arquivo', 'DELETE', OLD.id, CURRENT_USER(), JSON_OBJECT( 'nome', OLD.nome, 'usuario_id', OLD.usuario_id, 'data_criacao', OLD.data_criacao ) ); END// DELIMITER ; -- ============================================= -- 5. PROCEDURES PARA CARGA DO STAR SCHEMA -- ============================================= DELIMITER // CREATE PROCEDURE carregar_fato_operacoes() BEGIN -- Limpa a tabela fato TRUNCATE TABLE fato_operacoes; -- Carrega os dados das operações INSERT INTO fato_operacoes ( usuario_id, arquivo_id, data_operacao, tipo_operacao, quantidade ) SELECT usuario_id, arquivo_id, data, tipo, 1 FROM Operacao; -- Atualiza a dimensão tempo INSERT IGNORE INTO dim_tempo ( data_id, dia, mes, ano, trimestre, dia_semana, feriado ) SELECT DATE(data_operacao), DAY(data_operacao), MONTH(data_operacao), YEAR(data_operacao), QUARTER(data_operacao), DAYNAME(data_operacao), FALSE FROM fato_operacoes WHERE DATE(data_operacao) NOT IN (SELECT data_id FROM dim_tempo); END// DELIMITER ; -- ============================================= -- 6. INSERÇÃO DE DADOS DE EXEMPLO -- ============================================= -- Inserir usuários INSERT INTO Usuario (tipo, nome, email, senha, nivel) VALUES ('Loggado', 'Ana Silva', 'ana@email.com', 'senha123', 'Escrita'), ('Host', 'Carlos Admin', 'carlos@empresa.com', 'admin456', 'Administrador'); -- Inserir arquivos INSERT INTO Arquivo (id, nome, usuario_id, tipo) VALUES ('ARQ001', 'Documento Principal', 1, 'PDF'), ('ARQ002', 'Projeto X', 2, 'DOCX'); -- Registrar operações INSERT INTO Operacao (tipo, usuario_id, arquivo_id) VALUES ('CRIAR_ARQUIVO', 1, 'ARQ001'), ('ACESSAR_ARQUIVO', 2, 'ARQ002'); -- Carregar o star schema CALL carregar_fato_operacoes(); -- ============================================= -- 7. CONSULTAS DE EXEMPLO -- ============================================= -- Consulta analítica no star schema SELECT u.nome AS usuario, a.nome AS arquivo, COUNT(f.operacao_id) AS total_operacoes, dt.ano, dt.mes FROM fato_operacoes f JOIN dim_usuario u ON f.usuario_id = u.usuario_id JOIN dim_arquivo a ON f.arquivo_id = a.arquivo_id JOIN dim_tempo dt ON DATE(f.data_operacao) = dt.data_id GROUP BY u.nome, a.nome, dt.ano, dt.mes; -- Visualizar registros de auditoria SELECT * FROM auditoria;