17 lines
870 B
SQL
17 lines
870 B
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';
|