internal/db/meta.go (view raw)
1package db
2
3import (
4 "context"
5 "database/sql"
6 "errors"
7 "fmt"
8 "log/slog"
9
10 "github.com/emersion/go-imap/v2"
11 _ "modernc.org/sqlite"
12)
13
14const pragmas = `
15PRAGMA journal_mode = WAL;
16PRAGMA synchronous = NORMAL;
17PRAGMA foreign_keys = ON;
18PRAGMA busy_timeout = 5000;
19PRAGMA temp_store = MEMORY;
20`
21
22const schema = `
23
24CREATE TABLE IF NOT EXISTS users (
25 id INTEGER PRIMARY KEY,
26 name TEXT NOT NULL UNIQUE,
27 password_hash BLOB NOT NULL,
28 created_at INTEGER NOT NULL
29) STRICT;
30
31CREATE INDEX IF NOT EXISTS idx_users_name ON users(name);
32
33CREATE TABLE IF NOT EXISTS addresses (
34 id INTEGER PRIMARY KEY,
35 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
36 name TEXT NOT NULL UNIQUE,
37 created_at INTEGER NOT NULL
38) STRICT;
39
40CREATE INDEX IF NOT EXISTS idx_addresses_user_id ON addresses(user_id);
41CREATE INDEX IF NOT EXISTS idx_addresses_name ON addresses(name);
42
43CREATE TABLE IF NOT EXISTS gatekeepers (
44 id INTEGER PRIMARY KEY,
45 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
46 from_address TEXT NOT NULL,
47 destination TEXT NOT NULL,
48 created_at INTEGER NOT NULL,
49 UNIQUE (user_id, from_address)
50) STRICT;
51
52CREATE TABLE IF NOT EXISTS mailboxes (
53 id INTEGER PRIMARY KEY AUTOINCREMENT,
54 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
55 name TEXT NOT NULL,
56 special_use TEXT,
57 uidvalidity INTEGER NOT NULL,
58 uidnext INTEGER NOT NULL DEFAULT 1,
59 highest_modseq INTEGER NOT NULL DEFAULT 0,
60 UNIQUE (user_id, name)
61) STRICT;
62
63CREATE TABLE IF NOT EXISTS subscriptions (
64 id INTEGER PRIMARY KEY,
65 user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
66 mailbox_id INTEGER REFERENCES mailboxes(id) ON DELETE SET NULL,
67 mailbox_name TEXT, -- mailboxes can be subscribed even if mailbox does not exist, hence duplicate name
68 UNIQUE (user_id, mailbox_id)
69) STRICT;
70
71CREATE TABLE IF NOT EXISTS messages (
72 id INTEGER PRIMARY KEY,
73 blob_address TEXT NOT NULL UNIQUE,
74 size INTEGER NOT NULL,
75 subject TEXT,
76 message_id TEXT,
77 in_reply_to TEXT,
78
79 -- Envelope fields
80 date TEXT,
81 from_addr TEXT, -- JSON array of addresses
82 sender TEXT, -- JSON array of addresses
83 reply_to TEXT, -- JSON array of addresses
84 to_addr TEXT, -- JSON array of addresses
85 cc TEXT, -- JSON array of addresses
86 bcc TEXT -- JSON array of addresses
87) STRICT;
88
89CREATE TABLE IF NOT EXISTS mailbox_messages (
90 mailbox_id INTEGER NOT NULL REFERENCES mailboxes(id) ON DELETE CASCADE,
91 uid INTEGER NOT NULL,
92 message_id INTEGER NOT NULL REFERENCES messages(id),
93 modseq INTEGER NOT NULL,
94 internal_date TEXT NOT NULL,
95 expunged_modseq INTEGER,
96 PRIMARY KEY (mailbox_id, uid)
97) STRICT;
98
99CREATE TABLE IF NOT EXISTS message_flags (
100 mailbox_id INTEGER NOT NULL,
101 uid INTEGER NOT NULL,
102 flag TEXT NOT NULL,
103 PRIMARY KEY (mailbox_id, uid, flag),
104 FOREIGN KEY (mailbox_id) REFERENCES mailboxes(id) ON DELETE CASCADE,
105 FOREIGN KEY (mailbox_id, uid) REFERENCES mailbox_messages(mailbox_id, uid) ON DELETE CASCADE ON UPDATE CASCADE
106) STRICT;
107
108CREATE TABLE IF NOT EXISTS dkim (
109 id INTEGER PRIMARY KEY,
110 created_at INTEGER NOT NULL,
111 domain TEXT NOT NULL,
112 key_type TEXT NOT NULL,
113 blob_address TEXT NOT NULL,
114 selector TEXT NOT NULL,
115 enabled INTEGER NOT NULL,
116 UNIQUE (domain, selector)
117) STRICT;
118
119CREATE TABLE IF NOT EXISTS smtp_auth (
120 id INTEGER PRIMARY KEY,
121 name TEXT NOT NULL UNIQUE,
122 password_hash BLOB NOT NULL,
123 created_at INTEGER NOT NULL
124) STRICT;
125
126CREATE INDEX IF NOT EXISTS idx_mailbox_messages_modseq
127 ON mailbox_messages(mailbox_id, modseq);
128
129CREATE INDEX IF NOT EXISTS idx_mailbox_messages_message
130 ON mailbox_messages(message_id);
131
132CREATE TABLE IF NOT EXISTS keywords (
133 message_id INTEGER NOT NULL REFERENCES messages(id),
134 keyword TEXT NOT NULL,
135 PRIMARY KEY (message_id, keyword)
136) STRICT;
137
138CREATE INDEX IF NOT EXISTS idx_keywords_keyword
139 ON keywords(keyword);
140
141CREATE TABLE IF NOT EXISTS uidvalidity_counter (
142 id INTEGER PRIMARY KEY CHECK (id = 1),
143 counter INTEGER NOT NULL DEFAULT 0
144) STRICT;
145
146INSERT INTO uidvalidity_counter (id, counter)
147VALUES (1, 0)
148ON CONFLICT(id) DO NOTHING;
149`
150
151func (db *DB) applyPragmas(ctx context.Context) error {
152 for _, conn := range []*sql.DB{db.write, db.read} {
153 if _, err := conn.ExecContext(ctx, pragmas); err != nil {
154 return fmt.Errorf("applying pragmas: %w", err)
155 }
156 }
157 return nil
158}
159
160func (db *DB) applySchema(ctx context.Context) error {
161 if _, err := db.write.ExecContext(ctx, schema); err != nil {
162 return fmt.Errorf("applying schema: %w", err)
163 }
164 return nil
165}
166
167func (db *DB) Close() error {
168 werr := db.write.Close()
169 rerr := db.read.Close()
170 if werr != nil {
171 return werr
172 }
173 return rerr
174}
175
176func (db *DB) GetLastUID(ctx context.Context, userID, mailboxID int) (imap.UID, error) {
177 var uid imap.UID
178 err := db.read.QueryRowContext(ctx, `
179 SELECT COALESCE(MAX(mm.uid), 0)
180 FROM mailbox_messages mm
181 JOIN mailboxes mb ON mb.id = mm.mailbox_id
182 WHERE mm.mailbox_id = ?
183 AND mb.user_id = ?
184 AND mm.expunged_modseq IS NULL
185 `, mailboxID, userID).Scan(&uid)
186 return uid, err
187}
188
189// nextUIDValidity is db roll-back save. Even with a played-back backup, the counter will be increasing.
190// It will also be increasing if it is called more than once per second.
191func nextUIDValidity(tx *sql.Tx) (uint32, error) {
192 var v int64
193 err := tx.QueryRow(`
194 UPDATE uidvalidity_counter
195 SET counter = MAX(counter + 1, CAST(strftime('%s','now') AS INTEGER))
196 RETURNING counter
197 `).Scan(&v)
198 if err != nil {
199 return 0, err
200 }
201 return uint32(v), nil
202}
203
204func txRollback(tx *sql.Tx) {
205 err := tx.Rollback()
206 if !errors.Is(err, sql.ErrTxDone) {
207 slog.Error("failed to roll back transaction", "error", err)
208 }
209}