#![allow(
clippy::cast_possible_truncation,
clippy::cast_sign_loss,
clippy::cast_possible_wrap,
clippy::cast_lossless,
clippy::needless_pass_by_value
)]
use std::path::Path;
use rusqlite::{Connection, params};
use crate::store::{FlagOp, FlagSet, Message};
pub struct SqliteStore {
conn: Connection,
}
impl SqliteStore {
pub fn new<P: AsRef<Path>>(path: P) -> rusqlite::Result<Self> {
let conn = Connection::open(path)?;
Self::init_schema(&conn)?;
Ok(Self { conn })
}
#[allow(dead_code)]
pub fn in_memory() -> rusqlite::Result<Self> {
let conn = Connection::open_in_memory()?;
Self::init_schema(&conn)?;
Ok(Self { conn })
}
fn init_schema(conn: &Connection) -> rusqlite::Result<()> {
conn.execute_batch(
"
PRAGMA journal_mode=WAL;
PRAGMA synchronous=NORMAL;
PRAGMA foreign_keys=ON;
PRAGMA busy_timeout=5000;
CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY,
username TEXT UNIQUE NOT NULL
);
CREATE TABLE IF NOT EXISTS mailboxes (
id INTEGER PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
name TEXT NOT NULL,
UNIQUE(user_id, name)
);
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY,
rfc822_blob BLOB NOT NULL,
internal_date TEXT NOT NULL,
size_bytes INTEGER NOT NULL
);
CREATE TABLE IF NOT EXISTS mailbox_messages (
mailbox_id INTEGER NOT NULL REFERENCES mailboxes(id) ON DELETE CASCADE,
uid INTEGER NOT NULL,
message_id INTEGER NOT NULL REFERENCES messages(id) ON DELETE CASCADE,
flag_seen INTEGER NOT NULL DEFAULT 0,
flag_flagged INTEGER NOT NULL DEFAULT 0,
flag_deleted INTEGER NOT NULL DEFAULT 0,
flag_answered INTEGER NOT NULL DEFAULT 0,
flag_draft INTEGER NOT NULL DEFAULT 0,
PRIMARY KEY(mailbox_id, uid)
);
CREATE INDEX IF NOT EXISTS idx_mailbox_messages_message_id ON mailbox_messages(message_id);
CREATE TABLE IF NOT EXISTS uid_next (
mailbox_id INTEGER PRIMARY KEY REFERENCES mailboxes(id) ON DELETE CASCADE,
next_uid INTEGER NOT NULL DEFAULT 1
);
CREATE TABLE IF NOT EXISTS subscriptions (
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
mailbox_name TEXT NOT NULL,
PRIMARY KEY(user_id, mailbox_name)
);
",
)
}
fn ensure_user(&self, username: &str) -> rusqlite::Result<i64> {
self.conn.execute(
"INSERT OR IGNORE INTO users (username) VALUES (?1)",
params![username],
)?;
self.conn.query_row(
"SELECT id FROM users WHERE username = ?1",
params![username],
|row| row.get(0),
)
}
fn ensure_mailbox(&self, user_id: i64, mailbox: &str) -> rusqlite::Result<i64> {
self.conn.execute(
"INSERT OR IGNORE INTO mailboxes (user_id, name) VALUES (?1, ?2)",
params![user_id, mailbox],
)?;
let mailbox_id: i64 = self.conn.query_row(
"SELECT id FROM mailboxes WHERE user_id = ?1 AND name = ?2",
params![user_id, mailbox],
|row| row.get(0),
)?;
self.conn.execute(
"INSERT OR IGNORE INTO uid_next (mailbox_id, next_uid) VALUES (?1, 1)",
params![mailbox_id],
)?;
Ok(mailbox_id)
}
fn get_next_uid(&self, mailbox_id: i64) -> rusqlite::Result<u32> {
let uid: i64 = self.conn.query_row(
"SELECT next_uid FROM uid_next WHERE mailbox_id = ?1",
params![mailbox_id],
|row| row.get(0),
)?;
self.conn.execute(
"UPDATE uid_next SET next_uid = next_uid + 1 WHERE mailbox_id = ?1",
params![mailbox_id],
)?;
Ok(uid as u32)
}
pub fn ensure_mailbox_for_user(&self, user: &str, mailbox: &str) -> rusqlite::Result<()> {
let user_id = self.ensure_user(user)?;
self.ensure_mailbox(user_id, mailbox)?;
Ok(())
}
pub fn list_mailboxes(&self, user: &str) -> rusqlite::Result<Vec<String>> {
self.ensure_mailbox_for_user(user, "INBOX")?;
let user_id = self.ensure_user(user)?;
let mut stmt = self
.conn
.prepare("SELECT name FROM mailboxes WHERE user_id = ?1 ORDER BY name")?;
let names = stmt
.query_map(params![user_id], |row| row.get(0))?
.collect::<rusqlite::Result<Vec<String>>>()?;
Ok(names)
}
pub fn create_mailbox(&self, user: &str, mailbox: &str) -> rusqlite::Result<bool> {
if mailbox.eq_ignore_ascii_case("INBOX") {
return Ok(false);
}
let user_id = self.ensure_user(user)?;
let exists: bool = self.conn.query_row(
"SELECT EXISTS(SELECT 1 FROM mailboxes WHERE user_id = ?1 AND name = ?2)",
params![user_id, mailbox],
|row| row.get(0),
)?;
if exists {
return Ok(false);
}
self.ensure_mailbox(user_id, mailbox)?;
Ok(true)
}
pub fn rename_mailbox(&self, user: &str, old: &str, new: &str) -> rusqlite::Result<bool> {
let user_id = self.ensure_user(user)?;
let old_exists: bool = self.conn.query_row(
"SELECT EXISTS(SELECT 1 FROM mailboxes WHERE user_id = ?1 AND name = ?2)",
params![user_id, old],
|row| row.get(0),
)?;
let new_exists: bool = self.conn.query_row(
"SELECT EXISTS(SELECT 1 FROM mailboxes WHERE user_id = ?1 AND name = ?2)",
params![user_id, new],
|row| row.get(0),
)?;
if !old_exists || new_exists {
return Ok(false);
}
self.conn.execute(
"UPDATE mailboxes SET name = ?1 WHERE user_id = ?2 AND name = ?3",
params![new, user_id, old],
)?;
self.conn.execute(
"UPDATE subscriptions SET mailbox_name = ?1 WHERE user_id = ?2 AND mailbox_name = ?3",
params![new, user_id, old],
)?;
Ok(true)
}
pub fn delete_mailbox(&self, user: &str, mailbox: &str) -> rusqlite::Result<bool> {
if mailbox.eq_ignore_ascii_case("INBOX") {
return Ok(false);
}
let user_id = self.ensure_user(user)?;
let rows = self.conn.execute(
"DELETE FROM mailboxes WHERE user_id = ?1 AND name = ?2",
params![user_id, mailbox],
)?;
if rows > 0 {
self.conn.execute(
"DELETE FROM subscriptions WHERE user_id = ?1 AND mailbox_name = ?2",
params![user_id, mailbox],
)?;
}
Ok(rows > 0)
}
pub fn subscribe(&self, user: &str, mailbox: &str) -> rusqlite::Result<bool> {
let user_id = self.ensure_user(user)?;
let exists: bool = if mailbox.eq_ignore_ascii_case("INBOX") {
true
} else {
self.conn.query_row(
"SELECT EXISTS(SELECT 1 FROM mailboxes WHERE user_id = ?1 AND name = ?2)",
params![user_id, mailbox],
|row| row.get(0),
)?
};
if !exists {
return Ok(false);
}
self.conn.execute(
"INSERT OR IGNORE INTO subscriptions (user_id, mailbox_name) VALUES (?1, ?2)",
params![user_id, mailbox],
)?;
Ok(true)
}
pub fn unsubscribe(&self, user: &str, mailbox: &str) -> rusqlite::Result<bool> {
let user_id = self.ensure_user(user)?;
let rows = self.conn.execute(
"DELETE FROM subscriptions WHERE user_id = ?1 AND mailbox_name = ?2",
params![user_id, mailbox],
)?;
Ok(rows > 0)
}
pub fn list_subscriptions(&self, user: &str) -> rusqlite::Result<Vec<String>> {
let user_id = self.ensure_user(user)?;
let mut stmt = self.conn.prepare(
"SELECT mailbox_name FROM subscriptions WHERE user_id = ?1 ORDER BY mailbox_name",
)?;
let names = stmt
.query_map(params![user_id], |row| row.get(0))?
.collect::<rusqlite::Result<Vec<String>>>()?;
Ok(names)
}
pub fn append(
&self,
user: &str,
mailbox: &str,
data: Vec<u8>,
internal_date: String,
) -> rusqlite::Result<u32> {
let user_id = self.ensure_user(user)?;
let mailbox_id = self.ensure_mailbox(user_id, mailbox)?;
let size = data.len() as i64;
self.conn.execute(
"INSERT INTO messages (rfc822_blob, internal_date, size_bytes) VALUES (?1, ?2, ?3)",
params![data, internal_date, size],
)?;
let message_id = self.conn.last_insert_rowid();
let uid = self.get_next_uid(mailbox_id)?;
self.conn.execute(
"INSERT INTO mailbox_messages (mailbox_id, uid, message_id) VALUES (?1, ?2, ?3)",
params![mailbox_id, uid, message_id],
)?;
Ok(uid)
}
pub fn append_with_flags(
&self,
user: &str,
mailbox: &str,
data: Vec<u8>,
internal_date: String,
flags: &FlagSet,
) -> rusqlite::Result<u32> {
let user_id = self.ensure_user(user)?;
let mailbox_id = self.ensure_mailbox(user_id, mailbox)?;
let size = data.len() as i64;
self.conn.execute(
"INSERT INTO messages (rfc822_blob, internal_date, size_bytes) VALUES (?1, ?2, ?3)",
params![data, internal_date, size],
)?;
let message_id = self.conn.last_insert_rowid();
let uid = self.get_next_uid(mailbox_id)?;
self.conn.execute(
"INSERT INTO mailbox_messages (mailbox_id, uid, message_id, flag_seen, flag_flagged, flag_deleted, flag_answered, flag_draft)
VALUES (?1, ?2, ?3, ?4, ?5, ?6, ?7, ?8)",
params![
mailbox_id,
uid,
message_id,
flags.seen as i32,
flags.flagged as i32,
flags.deleted as i32,
flags.answered as i32,
flags.draft as i32
],
)?;
Ok(uid)
}
pub fn list(&self, user: &str, mailbox: &str) -> rusqlite::Result<Vec<Message>> {
self.ensure_mailbox_for_user(user, mailbox)?;
let user_id = self.ensure_user(user)?;
let mailbox_id: i64 = self.conn.query_row(
"SELECT id FROM mailboxes WHERE user_id = ?1 AND name = ?2",
params![user_id, mailbox],
|row| row.get(0),
)?;
let mut stmt = self.conn.prepare(
"SELECT mm.uid, m.rfc822_blob, m.internal_date,
mm.flag_seen, mm.flag_flagged, mm.flag_deleted, mm.flag_answered, mm.flag_draft
FROM mailbox_messages mm
JOIN messages m ON mm.message_id = m.id
WHERE mm.mailbox_id = ?1
ORDER BY mm.uid",
)?;
let messages = stmt
.query_map(params![mailbox_id], |row| {
Ok(Message {
uid: row.get::<_, i64>(0)? as u32,
data: row.get(1)?,
internal_date: row.get(2)?,
seen: row.get::<_, i32>(3)? != 0,
flagged: row.get::<_, i32>(4)? != 0,
deleted: row.get::<_, i32>(5)? != 0,
answered: row.get::<_, i32>(6)? != 0,
draft: row.get::<_, i32>(7)? != 0,
})
})?
.collect::<rusqlite::Result<Vec<Message>>>()?;
Ok(messages)
}
pub fn apply_flags_by_seq(
&self,
user: &str,
mailbox: &str,
seq: u32,
op: FlagOp,
flags: &FlagSet,
) -> rusqlite::Result<()> {
let messages = self.list(user, mailbox)?;
let index = seq.saturating_sub(1) as usize;
if let Some(message) = messages.get(index) {
self.apply_flags_by_uid(user, mailbox, message.uid, op, flags)?;
}
Ok(())
}
pub fn apply_flags_by_uid(
&self,
user: &str,
mailbox: &str,
uid: u32,
op: FlagOp,
flags: &FlagSet,
) -> rusqlite::Result<()> {
let user_id = self.ensure_user(user)?;
let mailbox_id: i64 = self.conn.query_row(
"SELECT id FROM mailboxes WHERE user_id = ?1 AND name = ?2",
params![user_id, mailbox],
|row| row.get(0),
)?;
match op {
FlagOp::Add => {
if flags.seen {
self.conn.execute(
"UPDATE mailbox_messages SET flag_seen = 1 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
if flags.flagged {
self.conn.execute(
"UPDATE mailbox_messages SET flag_flagged = 1 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
if flags.deleted {
self.conn.execute(
"UPDATE mailbox_messages SET flag_deleted = 1 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
if flags.answered {
self.conn.execute(
"UPDATE mailbox_messages SET flag_answered = 1 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
if flags.draft {
self.conn.execute(
"UPDATE mailbox_messages SET flag_draft = 1 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
}
FlagOp::Remove => {
if flags.seen {
self.conn.execute(
"UPDATE mailbox_messages SET flag_seen = 0 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
if flags.flagged {
self.conn.execute(
"UPDATE mailbox_messages SET flag_flagged = 0 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
if flags.deleted {
self.conn.execute(
"UPDATE mailbox_messages SET flag_deleted = 0 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
if flags.answered {
self.conn.execute(
"UPDATE mailbox_messages SET flag_answered = 0 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
if flags.draft {
self.conn.execute(
"UPDATE mailbox_messages SET flag_draft = 0 WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
}
}
FlagOp::Replace => {
self.conn.execute(
"UPDATE mailbox_messages SET flag_seen = ?1, flag_flagged = ?2, flag_deleted = ?3, flag_answered = ?4, flag_draft = ?5 WHERE mailbox_id = ?6 AND uid = ?7",
params![
flags.seen as i32,
flags.flagged as i32,
flags.deleted as i32,
flags.answered as i32,
flags.draft as i32,
mailbox_id,
uid
],
)?;
}
}
Ok(())
}
pub fn expunge_deleted(&self, user: &str, mailbox: &str) -> rusqlite::Result<Vec<u32>> {
let messages = self.list(user, mailbox)?;
let mut expunged = Vec::new();
let user_id = self.ensure_user(user)?;
let mailbox_id: i64 = self.conn.query_row(
"SELECT id FROM mailboxes WHERE user_id = ?1 AND name = ?2",
params![user_id, mailbox],
|row| row.get(0),
)?;
for (i, msg) in messages.iter().enumerate() {
if msg.deleted {
if let Ok(seq) = u32::try_from(i + 1) {
expunged.push(seq);
}
let message_id: i64 = self.conn.query_row(
"SELECT message_id FROM mailbox_messages WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, msg.uid],
|row| row.get(0),
)?;
self.conn.execute(
"DELETE FROM mailbox_messages WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, msg.uid],
)?;
self.conn
.execute("DELETE FROM messages WHERE id = ?1", params![message_id])?;
}
}
Ok(expunged)
}
pub fn expunge_deleted_by_uid(
&self,
user: &str,
mailbox: &str,
uids: &[u32],
) -> rusqlite::Result<Vec<u32>> {
let messages = self.list(user, mailbox)?;
let mut expunged = Vec::new();
let user_id = self.ensure_user(user)?;
let mailbox_id: i64 = self.conn.query_row(
"SELECT id FROM mailboxes WHERE user_id = ?1 AND name = ?2",
params![user_id, mailbox],
|row| row.get(0),
)?;
for (i, msg) in messages.iter().enumerate() {
if msg.deleted && uids.contains(&msg.uid) {
if let Ok(seq) = u32::try_from(i + 1) {
expunged.push(seq);
}
let message_id: i64 = self.conn.query_row(
"SELECT message_id FROM mailbox_messages WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, msg.uid],
|row| row.get(0),
)?;
self.conn.execute(
"DELETE FROM mailbox_messages WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, msg.uid],
)?;
self.conn
.execute("DELETE FROM messages WHERE id = ?1", params![message_id])?;
}
}
Ok(expunged)
}
pub fn copy_by_seq_set(
&self,
user: &str,
src: &str,
seqs: &[u32],
dest: &str,
) -> rusqlite::Result<usize> {
self.ensure_mailbox_for_user(user, dest)?;
let source_messages = self.list(user, src)?;
let user_id = self.ensure_user(user)?;
let dest_mailbox_id = self.ensure_mailbox(user_id, dest)?;
let mut copied = 0;
for seq in seqs {
let index = seq.saturating_sub(1) as usize;
if let Some(message) = source_messages.get(index) {
let size = message.data.len() as i64;
self.conn.execute(
"INSERT INTO messages (rfc822_blob, internal_date, size_bytes) VALUES (?1, ?2, ?3)",
params![message.data, message.internal_date, size],
)?;
let message_id = self.conn.last_insert_rowid();
let uid = self.get_next_uid(dest_mailbox_id)?;
self.conn.execute(
"INSERT INTO mailbox_messages (mailbox_id, uid, message_id, flag_seen, flag_flagged, flag_deleted, flag_answered, flag_draft)
VALUES (?1, ?2, ?3, ?4, ?5, 0, ?6, ?7)",
params![
dest_mailbox_id,
uid,
message_id,
message.seen as i32,
message.flagged as i32,
message.answered as i32,
message.draft as i32
],
)?;
copied += 1;
}
}
Ok(copied)
}
pub fn move_by_seq_set(
&self,
user: &str,
src: &str,
seqs: &[u32],
dest: &str,
) -> rusqlite::Result<Vec<u32>> {
self.copy_by_seq_set(user, src, seqs, dest)?;
let messages = self.list(user, src)?;
let user_id = self.ensure_user(user)?;
let src_mailbox_id: i64 = self.conn.query_row(
"SELECT id FROM mailboxes WHERE user_id = ?1 AND name = ?2",
params![user_id, src],
|row| row.get(0),
)?;
let mut expunged = Vec::new();
let mut indices: Vec<(usize, u32)> = seqs
.iter()
.filter_map(|seq| seq.checked_sub(1).map(|v| (v as usize, *seq)))
.collect();
indices.sort_by(|a, b| b.0.cmp(&a.0));
for (idx, seq) in indices {
if let Some(msg) = messages.get(idx) {
let message_id: i64 = self.conn.query_row(
"SELECT message_id FROM mailbox_messages WHERE mailbox_id = ?1 AND uid = ?2",
params![src_mailbox_id, msg.uid],
|row| row.get(0),
)?;
self.conn.execute(
"DELETE FROM mailbox_messages WHERE mailbox_id = ?1 AND uid = ?2",
params![src_mailbox_id, msg.uid],
)?;
self.conn
.execute("DELETE FROM messages WHERE id = ?1", params![message_id])?;
expunged.push(seq);
}
}
Ok(expunged)
}
pub fn seqs_from_uids(
&self,
user: &str,
mailbox: &str,
uids: &[u32],
) -> rusqlite::Result<Vec<u32>> {
let messages = self.list(user, mailbox)?;
let mut seqs = Vec::new();
for uid in uids {
if let Some(pos) = messages.iter().position(|m| m.uid == *uid) {
if let Ok(seq) = u32::try_from(pos + 1) {
seqs.push(seq);
}
}
}
Ok(seqs)
}
pub fn reset(&self) -> rusqlite::Result<()> {
self.conn.execute_batch(
"
DELETE FROM mailbox_messages;
DELETE FROM messages;
DELETE FROM uid_next;
DELETE FROM subscriptions;
DELETE FROM mailboxes;
DELETE FROM users;
",
)
}
pub fn list_users(&self) -> rusqlite::Result<Vec<String>> {
let mut stmt = self.conn.prepare("SELECT username FROM users")?;
let users = stmt
.query_map([], |row| row.get(0))?
.filter_map(Result::ok)
.collect();
Ok(users)
}
pub fn delete_by_uid(&self, user: &str, mailbox: &str, uid: u32) -> rusqlite::Result<()> {
let user_id = self.ensure_user(user)?;
let mailbox_id: i64 = self.conn.query_row(
"SELECT id FROM mailboxes WHERE user_id = ?1 AND name = ?2",
params![user_id, mailbox],
|row| row.get(0),
)?;
let message_id: Option<i64> = self
.conn
.query_row(
"SELECT message_id FROM mailbox_messages WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
|row| row.get(0),
)
.ok();
self.conn.execute(
"DELETE FROM mailbox_messages WHERE mailbox_id = ?1 AND uid = ?2",
params![mailbox_id, uid],
)?;
if let Some(msg_id) = message_id {
self.conn
.execute("DELETE FROM messages WHERE id = ?1", params![msg_id])?;
}
Ok(())
}
}