|
|
-- ============================================
|
|
|
-- 邮件服务器完整数据库初始化脚本
|
|
|
-- ============================================
|
|
|
-- 创建数据库（如果不存在）
|
|
|
CREATE DATABASE IF NOT EXISTS mail_server
|
|
|
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
USE mail_server;
|
|
|
|
|
|
-- 创建用户并授权（关键步骤！）
|
|
|
CREATE USER IF NOT EXISTS 'mail_user'@'%' IDENTIFIED BY 'user123';
|
|
|
GRANT ALL PRIVILEGES ON mail_server.* TO 'mail_user'@'%';
|
|
|
FLUSH PRIVILEGES;
|
|
|
|
|
|
-- ============================================
|
|
|
-- 核心功能表
|
|
|
-- ============================================
|
|
|
|
|
|
-- 用户表
|
|
|
CREATE TABLE IF NOT EXISTS users (
|
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
username VARCHAR(100) UNIQUE NOT NULL,
|
|
|
password_hash VARCHAR(255) NOT NULL,
|
|
|
is_admin TINYINT(1) DEFAULT 0,
|
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
-- 邮件表（兼容两种存储方式：SimpleSmtpServer用sender/recipient，SmtpHandler用sender_id/recipient_id）
|
|
|
CREATE TABLE IF NOT EXISTS emails (
|
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
sender VARCHAR(100),
|
|
|
recipient VARCHAR(100),
|
|
|
sender_id INT,
|
|
|
recipient_id INT,
|
|
|
subject VARCHAR(200),
|
|
|
body TEXT,
|
|
|
headers TEXT,
|
|
|
size_bytes INT DEFAULT 0,
|
|
|
is_deleted TINYINT(1) DEFAULT 0,
|
|
|
is_read TINYINT(1) DEFAULT 0,
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE SET NULL,
|
|
|
FOREIGN KEY (recipient_id) REFERENCES users(id) ON DELETE SET NULL
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
|
|
|
|
|
|
-- 系统日志表
|
|
|
CREATE TABLE IF NOT EXISTS server_logs (
|
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
log_type VARCHAR(50),
|
|
|
message TEXT,
|
|
|
client_ip VARCHAR(45),
|
|
|
user_id INT,
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
|
|
|
|
|
|
-- ============================================
|
|
|
-- 管理功能表
|
|
|
-- ============================================
|
|
|
|
|
|
-- 系统设置表
|
|
|
CREATE TABLE IF NOT EXISTS system_settings (
|
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
setting_key VARCHAR(100) UNIQUE NOT NULL,
|
|
|
setting_value TEXT,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
|
|
|
|
|
|
-- 过滤规则表
|
|
|
CREATE TABLE IF NOT EXISTS filter_rules (
|
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
rule_type ENUM('email', 'ip') NOT NULL,
|
|
|
rule_value VARCHAR(255) NOT NULL,
|
|
|
action ENUM('block', 'allow') DEFAULT 'block',
|
|
|
description TEXT,
|
|
|
is_active TINYINT(1) DEFAULT 1,
|
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
|
UNIQUE KEY unique_rule (rule_type, rule_value)
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
|
|
|
|
|
|
-- 服务状态表
|
|
|
CREATE TABLE IF NOT EXISTS service_status (
|
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
service_name VARCHAR(50) UNIQUE NOT NULL,
|
|
|
is_running TINYINT(1) DEFAULT 0,
|
|
|
pid INT,
|
|
|
last_started_at TIMESTAMP NULL,
|
|
|
last_stopped_at TIMESTAMP NULL,
|
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
|
|
|
|
|
|
-- 用户邮箱大小限制表
|
|
|
CREATE TABLE IF NOT EXISTS user_mailbox_limits (
|
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
|
|
user_id INT NOT NULL,
|
|
|
size_limit_bytes BIGINT DEFAULT 104857600, -- 100MB
|
|
|
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
|
|
|
UNIQUE KEY unique_user (user_id)
|
|
|
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ENGINE=InnoDB;
|
|
|
|
|
|
-- ============================================
|
|
|
-- 插入默认数据
|
|
|
-- ============================================
|
|
|
|
|
|
-- 插入测试用户（密码都是：123456）
|
|
|
INSERT INTO users (username, password_hash, is_admin, is_active) VALUES
|
|
|
('admin@test.com', '$2y$10$jB21V61k9aLAyp5.5qBpV.L70Aq6.XrtJrvlNI28bOXeJboLBJwoq', 1, 1),
|
|
|
('user1@test.com', '$2y$10$jB21V61k9aLAyp5.5qBpV.L70Aq6.XrtJrvlNI28bOXeJboLBJwoq', 0, 1)
|
|
|
ON DUPLICATE KEY UPDATE username = VALUES(username);
|
|
|
|
|
|
-- 插入测试邮件
|
|
|
INSERT INTO emails (sender, recipient, subject, body) VALUES
|
|
|
('admin@test.com', 'user1@test.com', '欢迎使用邮件系统', '这是第一封测试邮件'),
|
|
|
('user1@test.com', 'admin@test.com', '回复测试', '我收到了，谢谢！')
|
|
|
ON DUPLICATE KEY UPDATE id = id;
|
|
|
|
|
|
-- 插入系统默认设置
|
|
|
INSERT INTO system_settings (setting_key, setting_value) VALUES
|
|
|
('smtp_port', '25'),
|
|
|
('pop3_port', '110'),
|
|
|
('domain', 'test.com'),
|
|
|
('mailbox_size_limit', '104857600'), -- 100MB
|
|
|
('smtp_enabled', '1'),
|
|
|
('pop3_enabled', '1'),
|
|
|
('log_path', '/var/log/mailserver'),
|
|
|
('log_max_size', '10485760') -- 10MB
|
|
|
ON DUPLICATE KEY UPDATE setting_value = VALUES(setting_value);
|
|
|
|
|
|
-- 插入服务状态初始记录
|
|
|
INSERT INTO service_status (service_name, is_running) VALUES
|
|
|
('smtp', 0),
|
|
|
('pop3', 0)
|
|
|
ON DUPLICATE KEY UPDATE service_name = VALUES(service_name);
|
|
|
|
