28 lines
1.1 KiB
MySQL
28 lines
1.1 KiB
MySQL
|
-- Criar tabela browsing_history para armazenar histórico de navegação
|
||
|
|
CREATE TABLE IF NOT EXISTS browsing_history (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
device_id VARCHAR(255) NOT NULL,
|
||
|
|
url TEXT NOT NULL,
|
||
|
|
title VARCHAR(500),
|
||
|
|
browser VARCHAR(100),
|
||
|
|
visited_at TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Criar tabela session_events para armazenar eventos de logon/logoff
|
||
|
|
CREATE TABLE IF NOT EXISTS session_events (
|
||
|
|
id SERIAL PRIMARY KEY,
|
||
|
|
device_id VARCHAR(255) NOT NULL,
|
||
|
|
event_type VARCHAR(20) NOT NULL CHECK (event_type IN ('logon', 'logoff')),
|
||
|
|
username VARCHAR(255),
|
||
|
|
event_time TIMESTAMP NOT NULL DEFAULT NOW(),
|
||
|
|
created_at TIMESTAMP NOT NULL DEFAULT NOW()
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Criar índices para melhor performance
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_browsing_history_device_id ON browsing_history(device_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_browsing_history_visited_at ON browsing_history(visited_at DESC);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_session_events_device_id ON session_events(device_id);
|
||
|
|
CREATE INDEX IF NOT EXISTS idx_session_events_event_time ON session_events(event_time DESC);
|
||
|
|
|