AriWeb-Suite/modules/arimail/migrations/0002_imap.sql

-- IMAP: 메일함별 UID(전역 단조 증가)와 영구 플래그.
CREATE TABLE imap_uids (
 id INTEGER PRIMARY KEY AUTOINCREMENT,
 message_id TEXT NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
 folder TEXT NOT NULL,
 UNIQUE(message_id, folder)
);
CREATE TABLE imap_flags (
 message_id TEXT PRIMARY KEY REFERENCES messages(id) ON DELETE CASCADE,
 flags TEXT NOT NULL DEFAULT '[]',
 updated_at TEXT NOT NULL
);
INSERT INTO settings(key,value,updated_at) VALUES('imap_uidvalidity', CAST(strftime('%s','now') AS TEXT), strftime('%Y-%m-%dT%H:%M:%SZ','now'));
INSERT INTO imap_uids(message_id, folder) SELECT id, folder FROM messages ORDER BY received_at, id;
-- 보낸 메일·초안은 읽음 상태로 둡니다. IMAP 의 \Seen 은 read_at 으로 표현합니다.
UPDATE messages SET read_at = COALESCE(read_at, sent_at, received_at) WHERE direction = 'outbound';