m.id,
m.uid,
m.internal_date,
msg.raw_content,
msg.size_in_bytes
SELECT * FROM mw_user WHERE username = ? AND deleted = 0;
SELECT id FROM mw_mail_message WHERE content_hash = ?;
UPDATE mw_mailbox SET uid_next = uid_next + 1 WHERE id = ? RETURNING uid_next - 1 AS next_uid;
SELECT
COALESCE(
(SELECT ARRAY_AGG(f.flag) FROM mw_mail_flag f WHERE f.mail_id = m.id),
'{}'
) as flags
FROM
mw_mail m
JOIN
mw_mail_message msg ON m.message_id = msg.id
WHERE
m.mailbox_id = ? AND m.uid = ? AND m.deleted = 0
INSERT INTO mw_mail_flag (id,mail_id, flag) VALUES (?,?,?) ON CONFLICT (mail_id, flag) DO NOTHING;
DELETE FROM mw_mail_flag WHERE mail_id = ? AND flag IN (%s);
DELETE FROM mw_mail_flag WHERE flag = '\Recent' AND mail_id IN (SELECT id FROM mw_mail WHERE mailbox_id = ?);
SELECT
m.id, m.uid, m.internal_date,
msg.raw_content, msg.size_in_bytes,
ARRAY_AGG(mf.flag) FILTER (WHERE mf.flag IS NOT NULL) as flags
FROM mw_mail m
JOIN mw_mail_message msg ON m.message_id = msg.id
LEFT JOIN mw_mail_flag mf ON m.id = mf.mail_id
WHERE m.mailbox_id = ?
AND (%s)
AND NOT EXISTS (
SELECT 1 FROM mw_mail_flag del_mf WHERE del_mf.mail_id = m.id AND del_mf.flag = '\Deleted'
)
GROUP BY m.id, msg.id
ORDER BY m.uid ASC;
WITH ranked_emails AS (
SELECT
m.id,
m.uid,
m.internal_date,
m.message_id,
ROW_NUMBER() OVER (ORDER BY m.uid ASC) as seq_num
FROM mw_mail m
WHERE m.mailbox_id = ?
AND NOT EXISTS (
SELECT 1 FROM mw_mail_flag del_mf WHERE del_mf.mail_id = m.id AND del_mf.flag = '\Deleted'
)
)
SELECT
r.id, r.uid, r.internal_date,
msg.raw_content, msg.size_in_bytes,
ARRAY_AGG(mf.flag) FILTER (WHERE mf.flag IS NOT NULL) as flags
FROM ranked_emails r
JOIN mw_mail_message msg ON r.message_id = msg.id
LEFT JOIN mw_mail_flag mf ON r.id = mf.mail_id
WHERE %s
GROUP BY r.id, r.uid, r.internal_date, msg.id
ORDER BY r.uid ASC;
SELECT MAX(uid) FROM mw_mail WHERE mailbox_id = ?;
SELECT
COUNT(*) as message_count,
COALESCE(SUM(msg.size_in_bytes), 0) as total_size
FROM mw_mail m
JOIN mw_mail_message msg ON m.message_id = msg.id
WHERE m.mailbox_id = ?
AND NOT EXISTS (
SELECT 1 FROM mw_mail_flag del_mf WHERE del_mf.mail_id = m.id AND del_mf.flag = '\Deleted'
);
WITH ranked_emails AS (
SELECT
m.id, m.uid, m.internal_date, m.message_id,
ROW_NUMBER() OVER (ORDER BY m.uid ASC) as seq_num
FROM mw_mail m
WHERE m.mailbox_id = ?
AND NOT EXISTS (
SELECT 1 FROM mw_mail_flag del_mf WHERE del_mf.mail_id = m.id AND del_mf.flag = '\Deleted'
)
)
SELECT
COALESCE(
(SELECT ARRAY_AGG(f.flag) FROM mw_mail_flag f WHERE f.mail_id = m.id),
'{}'
) as flags
FROM
mw_mail m
JOIN
mw_mail_message msg ON m.message_id = msg.id
WHERE
m.mailbox_id = ? AND m.deleted = 0
AND NOT EXISTS (
SELECT 1 FROM mw_mail_flag f WHERE f.mail_id = m.id AND f.flag = '\\Deleted'
)
ORDER BY
m.uid ASC
SELECT
r.id, r.uid, r.internal_date, r.seq_num,
msg.raw_content, msg.size_in_bytes,
ARRAY_AGG(mf.flag) FILTER (WHERE mf.flag IS NOT NULL) as flags
FROM ranked_emails r
SELECT *
FROM (
SELECT
m.id,
m.uid,
m.internal_date,
msg.raw_content,
msg.size_in_bytes,
ROW_NUMBER() OVER (ORDER BY m.uid ASC) AS seq_num,
COALESCE(
(
SELECT ARRAY_AGG(f.flag)
FROM mw_mail_flag AS f
WHERE f.mail_id = m.id
),
'{}'
) AS flags
FROM mw_mail AS m
JOIN mw_mail_message AS msg
ON m.message_id = msg.id
WHERE m.mailbox_id = ?
AND m.deleted = 0
) AS subquery
WHERE %s;
WITH ranked_emails AS (
SELECT
m.id,
ROW_NUMBER() OVER (ORDER BY m.uid ASC) AS seq_num
FROM mw_mail m
JOIN mw_mailbox b ON m.mailbox_id = b.id
JOIN mw_user u ON b.user_id = u.id
WHERE u.username = ?
AND b.name = ?
AND m.deleted = 0
)
SELECT r.seq_num
FROM ranked_emails r
WHERE EXISTS (
SELECT 1
FROM mw_mail_flag f
WHERE f.mail_id = r.id
AND f.flag = '\\Deleted'
);
UPDATE mw_mail m
SET deleted = 1
FROM mw_mailbox b, mw_user u
WHERE m.mailbox_id = b.id
AND b.user_id = u.id
AND u.username = ?
AND b.name = ?
AND EXISTS (
SELECT 1
FROM mw_mail_flag f
WHERE f.mail_id = m.id
AND f.flag = '\\Deleted'
);
WITH newuids AS (
UPDATE mw_mailbox mb
SET uid_next = uid_next + ?
WHERE mb.id = ?
RETURNING mb.uid_next - ? AS start_uid
),
moved AS (
SELECT id, ROW_NUMBER() OVER (ORDER BY uid) - 1 AS idx
FROM mw_mail
WHERE mailbox_id = ?
AND uid IN (%s)
)
UPDATE mw_mail m
SET mailbox_id = ?,
uid = nu.start_uid + mv.idx
FROM moved mv
CROSS JOIN newuids nu
WHERE m.id = mv.id
RETURNING mv.idx, m.id, m.uid;
package com.tio.mail.wing.model;
import java.time.OffsetDateTime;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* 邮件实体类 (面向数据库和真实场景设计)
* 代表一封存储在服务器上的邮件实例。
*/
@Data
@NoArgsConstructor
public class Email {
/**
* 数据库主键 (例如：自增ID)
* 这个ID主要用于数据库内部关联，不直接暴露给IMAP客户端。
*/
private Long id;
/**
* 所属用户ID
* 用于标识这封邮件属于哪个用户。
*/
private Long userId;
/**
* 所属邮箱ID (Mailbox ID)
* 用于标识这封邮件属于用户的哪个邮箱（INBOX, Sent, etc.）。
*/
private Long mailboxId;
/**
* IMAP UID (Unique Identifier)
* 这是邮件在【一个特定邮箱内】的唯一标识符。
* 在数据库中，对于同一个 mailboxId，uid 必须是唯一的。
*/
private long uid;
/**
* 邮件的完整原始内容 (MIME 格式)
* 存储在数据库中通常使用 TEXT 或 BLOB 类型。
* 为了性能，有时会将其存储在文件系统或对象存储中，数据库只存路径。
*/
private String rawContent;
/**
* 邮件大小 (单位：字节)
* 在存入时计算一次，避免重复计算。
*/
private int size;
/**
* 邮件的内部接收日期
* 服务器接收到这封邮件的时间。用于排序等。
*/
private OffsetDateTime internalDate;
// --- IMAP 状态字段 ---
/**
* IMAP 标志 (e.g., \Seen, \Answered, \Flagged, \Deleted, \Draft, \Recent)
* 在数据库中可以存为以逗号分隔的字符串，或使用关联表。
*/
private Set<String> flags = new HashSet<>();
// --- 非持久化字段 (用于业务逻辑) ---
/**
* 解析后的邮件头 (懒加载)
* 为了避免每次都解析 rawContent，可以解析一次后缓存起来。
* transient 表示这个字段不会被JPA/MyBatis等ORM框架持久化。
*/
private transient Map<String, String> headers;
/**
* The sequence number of this email within its mailbox.
* This is a transient field, calculated at query time, not stored in the database.
*/
private int sequenceNumber;
/**
* 构造函数，用于从原始MIME内容创建邮件对象。
* @param rawContent 邮件的原始内容
*/
public Email(String rawContent) {
this.rawContent = rawContent;
this.size = rawContent.getBytes(java.nio.charset.StandardCharsets.UTF_8).length;
this.internalDate = OffsetDateTime.now(); // 记录接收时间
this.flags.add("\\Recent"); // 新邮件默认为 Recent
}
/**
* 懒加载并获取解析后的邮件头。
* @return 解析后的邮件头 Map
*/
public Map<String, String> getHeaders() {
if (headers == null) {
headers = new HashMap<>();
if (rawContent != null) {
String[] lines = rawContent.split("\\r?\\n");
for (String line : lines) {
if (line.isEmpty()) {
break; // 邮件头结束
}
int colonIndex = line.indexOf(':');
if (colonIndex > 0) {
String key = line.substring(0, colonIndex).trim();
String value = line.substring(colonIndex + 1).trim();
// 只存第一个同名header，或拼接，根据需要
headers.putIfAbsent(key.toUpperCase(), value);
}
}
}
}
return headers;
}
/**
* 便捷方法，获取邮件头中的 Message-ID
* @return Message-ID 或 null
*/
public String getMessageId() {
return getHeaders().get("MESSAGE-ID");
}
// 重写 equals 和 hashCode 以确保基于数据库ID的唯一性
@Override
public boolean equals(Object o) {
if (this == o)
return true;
if (o == null || getClass() != o.getClass())
return false;
Email email = (Email) o;
return id != null && id.equals(email.id);
}
@Override
public int hashCode() {
return id != null ? id.hashCode() : 0;
}
}
package com.tio.mail.wing.service;
import java.nio.charset.StandardCharsets;
import java.sql.SQLException;
import java.time.OffsetDateTime;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Collectors;
import com.litongjava.db.activerecord.Db;
import com.litongjava.db.activerecord.Row;
import com.litongjava.jfinal.aop.Aop;
import com.litongjava.template.SqlTemplates;
import com.litongjava.tio.utils.digest.Sha256Utils;
import com.litongjava.tio.utils.hutool.StrUtil;
import com.litongjava.tio.utils.snowflake.SnowflakeIdUtils;
import com.tio.mail.wing.consts.MailBoxName;
import com.tio.mail.wing.model.Email;
import com.tio.mail.wing.model.MailRaw;
import com.tio.mail.wing.result.WhereClauseResult;
import com.tio.mail.wing.utils.MailRawUtils;
import lombok.extern.slf4j.Slf4j;
@Slf4j
public class MailboxService {
private MwUserService mwUserService = Aop.get(MwUserService.class);
* 单独的 * → 匹配所有序号
n:* → 序号 ≥ n
*:n → 序号 ≤ n
n:m → 正常范围
*:* → 匹配所有
* @param messageSet
* @param mailboxId
* @return
*/
private WhereClauseResult buildSeqWhereClause(String messageSet, long mailboxId) {
StringBuilder clause = new StringBuilder();
List<Object> params = new ArrayList<>();
String[] parts = messageSet.split(",");
for (String rawPart : parts) {
String part = rawPart.trim();
if (clause.length() > 0) {
clause.append(" OR ");
}
if (part.contains(":")) {
String[] range = part.split(":", 2);
String startStr = range[0].trim();
String endStr = range[1].trim();
boolean startIsStar = "*".equals(startStr);
boolean endIsStar = "*".equals(endStr);
if (startIsStar && endIsStar) {
clause.append("TRUE");
} else if (startIsStar) {
int end = Integer.parseInt(endStr);
clause.append("seq_num <= ?");
params.add(end);
} else if (endIsStar) {
int start = Integer.parseInt(startStr);
clause.append("seq_num >= ?");
params.add(start);
} else {
int start = Integer.parseInt(startStr);
int end = Integer.parseInt(endStr);
clause.append("seq_num BETWEEN ? AND ?");
params.add(Math.min(start, end));
params.add(Math.max(start, end));
}
} else {
if ("*".equals(part)) {
clause.append("TRUE");
} else {
clause.append("seq_num = ?");
params.add(Integer.parseInt(part));
}
}
}
return new WhereClauseResult(clause.toString(), params);
}
* 将 IMAP UID 集合字符串（如 "1,2,5:7"）解析为具体的 UID 列表。
* 支持单个 UID、逗号分隔的多段、数字范围，以及 "*" 通配符（代表最大 UID）。
*
* @param uidSet IMAP UID set，比如 "1,2,5:7,*"
* @param mailboxId 用于查询最大 UID（处理 '*' 通配符）
* @return 按顺序展开的 UID 列表
*/
public List<Long> parseUidSet(String uidSet, long mailboxId) {
long maxUid = getMaxUid(mailboxId);
List<Long> uids = new ArrayList<>();
String[] parts = uidSet.split(",");
for (String raw : parts) {
String part = raw.trim();
if (part.contains(":")) {
String[] range = part.split(":", 2);
long start = "*".equals(range[0]) ? maxUid : Long.parseLong(range[0]);
long end = "*".equals(range[1]) ? maxUid : Long.parseLong(range[1]);
long min = Math.min(start, end), max = Math.max(start, end);
for (long uid = min; uid <= max; uid++) {
uids.add(uid);
}
} else if ("*".equals(part)) {
uids.add(maxUid);
} else {
uids.add(Long.parseLong(part));
}
}
return uids;
}
public boolean saveEmail(String toUser, MailRaw mail) {
String rawContent = MailRawUtils.toRawContent(mail);
return this.saveEmail(toUser, rawContent);
}
* [兼容SMTP] 将接收到的邮件保存到指定用户的收件箱(INBOX)中。
*/
public boolean saveEmail(String username, String rawContent) {
return saveEmailInternal(username, MailBoxName.INBOX, rawContent);
}
public boolean saveEmail(String toUser, String mailBoxName, MailRaw mail) {
String rawContent = MailRawUtils.toRawContent(mail);
return this.saveEmailInternal(toUser, mailBoxName, rawContent);
}
* [兼容POP3] 获取用户收件箱(INBOX)中所有未删除的邮件。
* 注意：此方法现在性能更高，但如果邮箱巨大，仍需考虑分页。
*/
public List<Email> getActiveMessagesByUsername(String username) {
Long userId = mwUserService.getUserIdByUsername(username);
return getActiveMessages(userId, MailBoxName.INBOX);
}
public List<Email> getActiveMessagesByUserId(Long userId) {
return getActiveMessages(userId, MailBoxName.INBOX);
}
* [兼容POP3] 获取邮箱状态（邮件数，总大小），针对INBOX。
* 优化：直接在数据库中计算，避免加载所有邮件到内存。
*/
public int[] getStat(String username) {
Row user = mwUserService.getUserByUsername(username);
if (user == null) {
return new int[] { 0, 0 };
}
Row mailbox = getMailboxByName(user.getLong("id"), MailBoxName.INBOX);
if (mailbox == null)
return new int[] { 0, 0 };
String sql = SqlTemplates.get("mailbox.getStat");
Row statRow = Db.findFirst(sql, mailbox.getLong("id"));
if (statRow == null) {
return new int[] { 0, 0 };
}
return new int[] { statRow.getLong("message_count").intValue(), statRow.getBigDecimal("total_size").intValue() };
}
* [兼容POP3] 获取指定邮件内容，针对INBOX。
* 优化：使用 findEmailsBySeqSet 获取单封邮件，避免加载整个列表。
*/
public String getMessageContent(String username, int msgNumber) {
List<Email> emails = findEmailsBySeqSet(username, MailBoxName.INBOX, String.valueOf(msgNumber));
if (emails != null && !emails.isEmpty()) {
return emails.get(0).getRawContent();
}
return null;
}
* [兼容POP3] 获取邮件大小列表，用于 LIST 命令，针对INBOX。
*/
public List<Integer> listMessages(Long userId) {
return getActiveMessages(userId, MailBoxName.INBOX).stream().map(Email::getSize).collect(Collectors.toList());
}
* [兼容POP3] 获取邮件的唯一ID列表，用于 UIDL 命令，针对INBOX。
*/
public List<Long> listUids(Long userId) {
return getActiveMessages(userId, MailBoxName.INBOX).stream().map(Email::getUid).collect(Collectors.toList());
}
* 内部核心的邮件保存方法。
* 优化：使用事务和原子性UID更新。
*/
private boolean saveEmailInternal(String username, String mailboxName, String rawContent) {
try {
return Db.tx(() -> {
Row user = mwUserService.getUserByUsername(username);
if (user == null) {
log.error("User '{}' not found. Cannot save email.", username);
return false;
}
long userId = user.getLong("id");
Row mailbox = getMailboxByName(userId, mailboxName);
if (mailbox == null) {
log.error("Mailbox '{}' not found for user '{}'.", mailboxName, username);
return false;
}
long mailboxId = mailbox.getLong("id");
String contentHash = Sha256Utils.digestToHex(rawContent);
int sizeInBytes = rawContent.getBytes(StandardCharsets.UTF_8).length;
Row message = Db.findFirst(SqlTemplates.get("mailbox.message.findByHash"), contentHash);
long messageId;
long id = SnowflakeIdUtils.id();
if (message == null) {
Map<String, String> headers = parseHeaders(rawContent);
Row newMessage = Row.by("id", id).set("content_hash", contentHash)
.set("message_id_header", headers.get("Message-ID"))
.set("subject", headers.get("Subject"))
.set("from_address", headers.get("From")).set("to_address", headers.get("To"))
.set("size_in_bytes", sizeInBytes).set("raw_content", rawContent);
Db.save("mw_mail_message", "id", newMessage);
messageId = newMessage.getLong("id");
} else {
messageId = message.getLong("id");
}
String updateSql = SqlTemplates.get("mailbox.updateUidNextAndGet");
Row result = Db.findFirst(updateSql, mailboxId);
if (result == null) {
throw new SQLException("Failed to increment and retrieve uid_next for mailbox " + mailboxId);
}
long nextUid = result.getLong("next_uid");
Row mailInstance = Row.by("id", id).set("user_id", userId).set("mailbox_id", mailboxId).set("message_id", messageId).set("uid", nextUid).set("internal_date", new Date());
Db.save("mw_mail", "id", mailInstance);
long flagId = SnowflakeIdUtils.id();
Row recentFlag = Row.by("id", flagId).set("mail_id", id).set("flag", "\\Recent");
Db.save("mw_mail_flag", recentFlag);
log.info("Saved new email for {} in mailbox {} with UID {}. Mail instance ID: {}", username, mailboxName, nextUid, id);
return true;
});
} catch (Exception e) {
log.error("Error saving email for user '{}' in mailbox '{}'", username, mailboxName, e);
return false;
}
}
* [IMAP核心] 获取用户【指定邮箱】中所有未被标记为删除的邮件。
* 优化：使用单个SQL查询，将邮件、内容和标志一次性获取。
*/
public List<Email> getActiveMessages(Long userId, String mailboxName) {
Row mailbox = getMailboxByName(userId, mailboxName);
if (mailbox == null)
return Collections.emptyList();
String sql = SqlTemplates.get("mailbox.getActiveMessages");
List<Row> mailRows = Db.find(sql, mailbox.getLong("id"));
return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
}
public List<Email> getActiveMessages(Long mailboxId) {
String sql = SqlTemplates.get("mailbox.getActiveMessages");
List<Row> mailRows = Db.find(sql, mailboxId);
return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
}
* [IMAP核心] 获取邮箱的元数据。
*/
public Row getMailboxMetadata(Long userId, String mailboxName) {
Row mailbox = getMailboxByName(userId, mailboxName);
if (mailbox == null) {
return null;
}
return mailbox;
}
* [IMAP核心] 根据序号获取邮件。
* 优化：使用 findEmailsBySeqSet 避免加载全量数据。
*/
public Email getMessageByNumber(String username, String mailboxName, int msgNumber) {
List<Email> emails = findEmailsBySeqSet(username, mailboxName, String.valueOf(msgNumber));
return (emails != null && !emails.isEmpty()) ? emails.get(0) : null;
}
* [IMAP核心] 根据 UID 获取邮件。
* 优化：使用单个SQL查询，直接获取目标邮件。
*/
public Email getMessageByUid(String username, String mailboxName, long uid) {
Row user = mwUserService.getUserByUsername(username);
if (user == null)
return null;
Row mailbox = getMailboxByName(user.getLong("id"), mailboxName);
if (mailbox == null)
return null;
String sql = SqlTemplates.get("mailbox.getMessageByUid");
Row row = Db.findFirst(sql, mailbox.getLong("id"), uid);
if (row == null)
return null;
return rowToEmailWithAggregatedFlags(row);
}
* [IMAP核心] 修改邮件标志。
* 优化：使用批量、原子的SQL操作。
*/
public void storeFlags(Email email, Set<String> newFlags, boolean add) {
if (email == null || email.getId() == null || newFlags == null || newFlags.isEmpty()) {
return;
}
long mailId = email.getId();
if (add) {
String sql = SqlTemplates.get("mailbox.flags.addBatch");
for (String flag : newFlags) {
Db.updateBySql(sql, SnowflakeIdUtils.id(), mailId, flag);
}
} else {
String flagPlaceholders = String.join(",", Collections.nCopies(newFlags.size(), "?"));
String sql = String.format(SqlTemplates.get("mailbox.flags.removeBatch"), flagPlaceholders);
List<Object> params = new ArrayList<>();
params.add(mailId);
params.addAll(newFlags);
Db.updateBySql(sql, params.toArray());
}
}
* [IMAP核心] 清除指定用户邮箱的所有 \Recent 标志。
*/
public void clearRecentFlags(String username, String mailboxName) {
Row user = mwUserService.getUserByUsername(username);
if (user == null) {
return;
}
Row mailbox = getMailboxByName(user.getLong("id"), mailboxName);
if (mailbox == null) {
return;
}
String sql = SqlTemplates.get("mailbox.flags.clearRecent");
Db.updateBySql(sql, mailbox.getLong("id"));
}
* [IMAP核心] 根据UID集合获取邮件列表。
* 优化：将过滤逻辑下推到数据库，避免在内存中操作大列表。
*/
public List<Email> findEmailsByUidSet(String username, String mailboxName, String messageSet) {
Row user = mwUserService.getUserByUsername(username);
if (user == null) {
return Collections.emptyList();
}
Row mailbox = getMailboxByName(user.getLong("id"), mailboxName);
if (mailbox == null) {
return Collections.emptyList();
}
long mailboxId = mailbox.getLong("id");
return findEmailsByUidSet(mailboxId, messageSet);
}
public List<Email> findEmailsByUidSet(long mailboxId, String messageSet) {
WhereClauseResult whereClause = buildUidWhereClause(messageSet, mailboxId);
if (whereClause.getClause().isEmpty()) {
return Collections.emptyList();
}
String baseSql = SqlTemplates.get("mailbox.findEmails.baseQuery");
String finalSql = String.format(baseSql, whereClause.getClause());
List<Object> params = new ArrayList<>();
params.add(mailboxId);
params.addAll(whereClause.getParams());
List<Row> mailRows = Db.find(finalSql, params.toArray());
return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
}
* [IMAP核心] 根据序号集合获取邮件列表。
* 优化：使用窗口函数在数据库中过滤，避免内存操作。
*/
public List<Email> findEmailsBySeqSet(String username, String mailboxName, String messageSet) {
Row user = mwUserService.getUserByUsername(username);
if (user == null) {
return Collections.emptyList();
}
Row mailbox = getMailboxByName(user.getLong("id"), mailboxName);
if (mailbox == null) {
return Collections.emptyList();
}
long mailboxId = mailbox.getLong("id");
return findEmailsBySeqSet(mailboxId, messageSet);
}
public List<Email> findEmailsBySeqSet(long mailboxId, String messageSet) {
WhereClauseResult whereClause = buildSeqWhereClause(messageSet, mailboxId);
if (whereClause.getClause().isEmpty()) {
return Collections.emptyList();
}
String resolvedBaseQuery = SqlTemplates.get("mailbox.findEmails.BySeqSet");
String finalSql = String.format(resolvedBaseQuery, whereClause.getClause());
List<Object> params = new ArrayList<>();
params.add(mailboxId);
params.addAll(whereClause.getParams());
List<Row> mailRows = Db.find(finalSql, params.toArray());
return mailRows.stream().map(this::rowToEmailWithAggregatedFlags).collect(Collectors.toList());
}
private Row getMailboxByName(long userId, String mailboxName) {
String sql = "SELECT id, uid_validity, uid_next FROM mw_mailbox WHERE user_id = ? AND name = ? AND deleted = 0";
return Db.findFirst(sql, userId, mailboxName);
}
public Row getMailboxById(long userId, long mailboxId) {
String sql = "SELECT id, uid_validity, uid_next FROM mw_mailbox WHERE user_id = ? AND id = ? AND deleted = 0";
return Db.findFirst(sql, userId, mailboxId);
}
* 将数据库行（包含聚合后的标志数组）转换为 Email DTO 对象。
*/
private Email rowToEmailWithAggregatedFlags(Row row) {
Email email = new Email();
email.setId(row.getLong("id"));
email.setUid(row.getLong("uid"));
email.setRawContent(row.getStr("raw_content"));
email.setSize(row.getInt("size_in_bytes"));
if (row.get("sequence_number") != null) {
email.setSequenceNumber(row.getInt("sequence_number"));
}
OffsetDateTime internalDate = row.getOffsetDateTime("internal_date");
if (internalDate != null) {
email.setInternalDate(internalDate);
}
String[] flagsArray = row.getStringArray("flags");
if (flagsArray != null) {
Set<String> flags = new HashSet<>(flagsArray.length);
for (String string : flagsArray) {
flags.add(string);
}
email.setFlags(flags);
}
return email;
}
private WhereClauseResult buildUidWhereClause(String messageSet, long mailboxId) {
StringBuilder clause = new StringBuilder();
List<Object> params = new ArrayList<>();
Long maxUid = null;
String[] parts = messageSet.split(",");
for (String part : parts) {
part = part.trim();
if (clause.length() > 0)
clause.append(" OR ");
if (part.contains(":")) {
String[] range = part.split(":", 2);
if ("*".equals(range[0]) || "*".equals(range[1])) {
if (maxUid == null)
maxUid = getMaxUid(mailboxId);
}
long start = "*".equals(range[0]) ? maxUid : Long.parseLong(range[0]);
long end = "*".equals(range[1]) ? maxUid : Long.parseLong(range[1]);
clause.append("m.uid BETWEEN ? AND ?");
params.add(Math.min(start, end));
params.add(Math.max(start, end));
} else {
if ("*".equals(part)) {
if (maxUid == null)
maxUid = getMaxUid(mailboxId);
clause.append("m.uid = ?");
params.add(maxUid);
} else {
clause.append("m.uid = ?");
params.add(Long.parseLong(part));
}
}
}
return new WhereClauseResult(clause.toString(), params);
}
private Long getMaxUid(long mailboxId) {
Row row = Db.findFirst(SqlTemplates.get("mailbox.getMaxUid"), mailboxId);
return (row != null && row.getLong("max") != null) ? row.getLong("max") : 0L;
}
private Map<String, String> parseHeaders(String rawContent) {
Map<String, String> headers = new HashMap<>();
String[] lines = rawContent.split("\r\n");
for (String line : lines) {
if (line.isEmpty())
break;
int colonIndex = line.indexOf(':');
if (colonIndex > 0) {
String key = line.substring(0, colonIndex).trim();
String value = line.substring(colonIndex + 1).trim();
if (Arrays.asList("Message-ID", "Subject", "From", "To").contains(key)) {
headers.put(key, value);
}
}
}
return headers;
}
* 获取待 EXPUNGE 的邮件序号列表
*/
public List<Integer> getExpungeSeqNums(String username, String mailboxName) {
String sql = SqlTemplates.get("mailbox.getExpungeSeqNums");
List<Row> rows = Db.find(sql, username, mailboxName);
return rows.stream().map(r -> r.getInt("seq_num")).collect(Collectors.toList());
}
* 逻辑删除所有已标记 \Deleted 的邮件实例
*/
public void expunge(String username, String mailboxName) {
String sql = SqlTemplates.get("mailbox.expunge");
Db.updateBySql(sql, username, mailboxName);
}
* 列出指定用户的所有邮箱目录名称
*/
public List<String> listMailboxes(String username) {
Row user = mwUserService.getUserByUsername(username);
if (user == null) {
return new ArrayList<>(0);
}
Long userId = user.getLong("id");
String sql = "SELECT name FROM mw_mailbox WHERE user_id = ? AND deleted = 0";
List<Row> rows = Db.find(sql, userId);
return rows.stream().map(r -> r.getStr("name")).collect(Collectors.toList());
}
* 为指定用户创建一个新的邮箱目录
*/
public void createMailbox(String username, String mailboxName) {
if (StrUtil.isBlank(mailboxName)) {
throw new IllegalArgumentException("Mailbox name cannot be blank");
}
Row user = mwUserService.getUserByUsername(username);
if (user == null) {
throw new IllegalStateException("User not found: " + username);
}
long mailboxId = SnowflakeIdUtils.id();
long userId = user.getLong("id");
Row newMailbox = Row.by("id", mailboxId).set("user_id", userId).set("name", mailboxName)
.set("uid_validity", mailboxId).set("uid_next", 1).set("creator", "system").set("updater", "system")
.set("tenant_id", user.getLong("tenant_id"));
Db.save("mw_mailbox", "id", newMailbox);
log.info("Created mailbox '{}' (id={}) for user {}", mailboxName, mailboxId, username);
}
public void copyEmailsByUidSet(String username, String srcMailboxName, String uidSet, String destMailboxName) {
List<Email> toCopy = findEmailsByUidSet(username, srcMailboxName, uidSet);
if (toCopy.isEmpty()) {
return;
}
for (Email e : toCopy) {
saveEmailInternal(username, destMailboxName, e.getRawContent());
}
}
public boolean exitsMailBox(Long userId, String mailboxName) {
String sql = "select count(1) from mw_mailbox where user_id=? and name=?";
return Db.existsBySql(sql, userId, mailboxName);
}
public Long queryMailBoxId(Long userId, String mailboxName) {
String sql = "select id from mw_mailbox where user_id=? and name=?";
return Db.queryLong(sql, userId, mailboxName);
}
public void moveEmailsByUidSet(Long userId, String src, String uidSet, String dest) {
long srcMailboxId = getMailboxByName(userId, src).getLong("id");
long destMailboxId = getMailboxByName(userId, dest).getLong("id");
List<Long> uids = parseUidSet(uidSet, srcMailboxId);
int cnt = uids.size();
String inClause = uids.stream().map(u -> "?").collect(Collectors.joining(","));
String raw = SqlTemplates.get("mailbox.moveEmails");
String sql = String.format(raw, inClause);
List<Object> params = new ArrayList<>();
params.add(cnt);
params.add(destMailboxId);
params.add(cnt);
params.add(srcMailboxId);
params.addAll(uids);
params.add(destMailboxId);
Db.updateBySql(sql, params.toArray());
}
}
