54 lines
2.1 KiB
SQL
54 lines
2.1 KiB
SQL
|
-- kiri-mail-server 数据库初始化脚本
|
||
|
|
-- 运行: mysql -u root -p < migrations/001_init.sql
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS mail_domains (
|
||
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
domain VARCHAR(255) NOT NULL UNIQUE,
|
||
|
|
description VARCHAR(500) DEFAULT '',
|
||
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
|
|
max_users INT NOT NULL DEFAULT 100,
|
||
|
|
max_quota BIGINT NOT NULL DEFAULT 0 COMMENT '0=unlimited, bytes',
|
||
|
|
created_at INT NOT NULL,
|
||
|
|
updated_at INT NOT NULL,
|
||
|
|
INDEX idx_domain (domain),
|
||
|
|
INDEX idx_active (is_active)
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS mail_users (
|
||
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
email VARCHAR(320) NOT NULL UNIQUE,
|
||
|
|
domain_id INT NOT NULL,
|
||
|
|
local_part VARCHAR(128) NOT NULL,
|
||
|
|
password VARCHAR(255) NOT NULL COMMENT 'bcrypt/argon2 hashed',
|
||
|
|
display_name VARCHAR(255) DEFAULT '',
|
||
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
|
|
quota BIGINT NOT NULL DEFAULT 0 COMMENT '0=unlimited, bytes',
|
||
|
|
created_at INT NOT NULL,
|
||
|
|
updated_at INT NOT NULL,
|
||
|
|
INDEX idx_email (email),
|
||
|
|
INDEX idx_domain_id (domain_id),
|
||
|
|
FOREIGN KEY (domain_id) REFERENCES mail_domains(id) ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS mail_aliases (
|
||
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
source_email VARCHAR(320) NOT NULL,
|
||
|
|
destination_email VARCHAR(320) NOT NULL,
|
||
|
|
domain_id INT NOT NULL,
|
||
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
||
|
|
created_at INT NOT NULL,
|
||
|
|
updated_at INT NOT NULL,
|
||
|
|
INDEX idx_source (source_email),
|
||
|
|
INDEX idx_domain (domain_id),
|
||
|
|
FOREIGN KEY (domain_id) REFERENCES mail_domains(id) ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|
||
|
|
|
||
|
|
CREATE TABLE IF NOT EXISTS mail_quotas (
|
||
|
|
id INT AUTO_INCREMENT PRIMARY KEY,
|
||
|
|
user_id INT NOT NULL UNIQUE,
|
||
|
|
used_bytes BIGINT NOT NULL DEFAULT 0,
|
||
|
|
message_count INT NOT NULL DEFAULT 0,
|
||
|
|
updated_at INT NOT NULL,
|
||
|
|
FOREIGN KEY (user_id) REFERENCES mail_users(id) ON DELETE CASCADE
|
||
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
|