41cd7a4fd8
- Implemented SecurityTest to validate token generation, CSRF protection, input sanitization, and rate limiting. - Created UserModelTest to ensure correct database operations for user management, including creation, updating, banning, and fetching active users. - Developed ValidationTest to verify input validation and sanitization for user IDs, nicknames, messages, and API requests. - Introduced Security and Validation utility classes with methods for secure token generation, input sanitization, and comprehensive validation rules.
88 lines
3.3 KiB
SQL
88 lines
3.3 KiB
SQL
-- Database Migration: Initial Schema
|
|
-- This migration creates the initial database structure for the Dodgers Stream application
|
|
|
|
-- Users table for active viewers and user management
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id VARCHAR(32) UNIQUE NOT NULL,
|
|
nickname VARCHAR(50),
|
|
ip_address VARCHAR(45),
|
|
user_agent VARCHAR(500),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
is_admin BOOLEAN DEFAULT 0,
|
|
session_id VARCHAR(128)
|
|
);
|
|
|
|
-- Chat messages table
|
|
CREATE TABLE IF NOT EXISTS chat_messages (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
nickname VARCHAR(50) NOT NULL,
|
|
message TEXT NOT NULL,
|
|
is_admin BOOLEAN DEFAULT 0,
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
ip_address VARCHAR(45),
|
|
time_formatted VARCHAR(20), -- M j, H:i format
|
|
FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Banned users table
|
|
CREATE TABLE IF NOT EXISTS banned_users (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id VARCHAR(32) UNIQUE NOT NULL,
|
|
reason TEXT,
|
|
banned_by VARCHAR(32),
|
|
banned_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
expires_at DATETIME NULL,
|
|
FOREIGN KEY (banned_by) REFERENCES users(user_id) ON DELETE SET NULL
|
|
);
|
|
|
|
-- Active viewers table (for real-time tracking)
|
|
CREATE TABLE IF NOT EXISTS active_viewers (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
user_id VARCHAR(32) NOT NULL,
|
|
nickname VARCHAR(50),
|
|
ip_address VARCHAR(45),
|
|
last_seen DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
is_admin BOOLEAN DEFAULT 0,
|
|
session_id VARCHAR(128),
|
|
UNIQUE(user_id)
|
|
);
|
|
|
|
-- Admin audit log
|
|
CREATE TABLE IF NOT EXISTS admin_audit_log (
|
|
id INTEGER PRIMARY KEY AUTOINCREMENT,
|
|
admin_user_id VARCHAR(32) NOT NULL,
|
|
action VARCHAR(100) NOT NULL,
|
|
target_user_id VARCHAR(32),
|
|
details TEXT,
|
|
ip_address VARCHAR(45),
|
|
timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
FOREIGN KEY (admin_user_id) REFERENCES users(user_id) ON DELETE CASCADE
|
|
);
|
|
|
|
-- Session management
|
|
CREATE TABLE IF NOT EXISTS sessions (
|
|
session_id VARCHAR(128) PRIMARY KEY,
|
|
user_id VARCHAR(32),
|
|
user_agent VARCHAR(500),
|
|
ip_address VARCHAR(45),
|
|
created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
is_admin BOOLEAN DEFAULT 0
|
|
);
|
|
|
|
-- Create indexes for performance
|
|
CREATE INDEX IF NOT EXISTS idx_users_user_id ON users(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_users_last_seen ON users(last_seen);
|
|
CREATE INDEX IF NOT EXISTS idx_chat_messages_timestamp ON chat_messages(timestamp);
|
|
CREATE INDEX IF NOT EXISTS idx_chat_messages_user_id ON chat_messages(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_active_viewers_last_seen ON active_viewers(last_seen);
|
|
CREATE INDEX IF NOT EXISTS idx_banned_users_user_id ON banned_users(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_admin_audit_timestamp ON admin_audit_log(timestamp);
|
|
CREATE INDEX IF NOT EXISTS idx_sessions_last_activity ON sessions(last_activity);
|
|
|
|
-- Insert initial admin user (this should be done securely in application setup)
|
|
-- WARNING: This is for development only. In production, use proper admin setup.
|
|
-- DO NOT commit actual admin credentials to version control.
|