#2

1 files changed, 655 insertions, 0 deletions

diff --git a/src/core/mail_sql.c b/src/core/mail_sql.c
new file mode 100644
index 0000000..95b93f9
--- /dev/null
+++ b/src/core/mail_sql.c
@@ -0,0 +1,655 @@
+#include "mail_sql.h"
+#include "hybbx/service.h"
+#include "hybbx/session.h"
+#include "hybbx/traffic.h"
+#include "hybbx/util.h"
+#include "storage_private.h"
+
+#include <stdio.h>
+#include <stdlib.h>
+#include <string.h>
+#include <time.h>
+
+#ifdef HYBBX_HAVE_SQLITE
+#include <sqlite3.h>
+
+#define MAIL_FOLDER_INBOX 0
+#define MAIL_FOLDER_RECYCLE 1
+
+static int mail_entry_compare(const void *a, const void *b)
+{
+    const hybbx_mail_entry_t *ea = (const hybbx_mail_entry_t *)a;
+    const hybbx_mail_entry_t *eb = (const hybbx_mail_entry_t *)b;
+
+    if (ea->received_at > eb->received_at) {
+        return -1;
+    }
+    if (ea->received_at < eb->received_at) {
+        return 1;
+    }
+    if (ea->id > eb->id) {
+        return -1;
+    }
+    if (ea->id < eb->id) {
+        return 1;
+    }
+    return 0;
+}
+
+static hybbx_result_t mail_sql_meta_bump(sqlite3 *db, const char *key,
+                                         uint64_t *value)
+{
+    sqlite3_stmt *stmt;
+    int rc;
+
+    *value = 0;
+
+    rc = sqlite3_prepare_v2(db,
+                            "SELECT value FROM meta WHERE key=?1;",
+                            -1, &stmt, NULL);
+    if (rc == SQLITE_OK) {
+        sqlite3_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
+        if (sqlite3_step(stmt) == SQLITE_ROW) {
+            *value = (uint64_t)sqlite3_column_int64(stmt, 0);
+        }
+        sqlite3_finalize(stmt);
+    }
+
+    (*value)++;
+
+    rc = sqlite3_prepare_v2(db,
+                            "INSERT INTO meta(key,value) VALUES(?1,?2) "
+                            "ON CONFLICT(key) DO UPDATE SET value=?2;",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        return HYBBX_ERR_IO;
+    }
+
+    sqlite3_bind_text(stmt, 1, key, -1, SQLITE_STATIC);
+    sqlite3_bind_int64(stmt, 2, (sqlite3_int64)*value);
+    rc = sqlite3_step(stmt);
+    sqlite3_finalize(stmt);
+
+    return (rc == SQLITE_DONE) ? HYBBX_OK : HYBBX_ERR_IO;
+}
+
+static void mail_sql_owner_norm(char *owner, size_t len, const char *username)
+{
+    hybbx_strlcpy(owner, username, len);
+    hybbx_username_normalize(owner);
+}
+
+hybbx_result_t hybbx_mail_sql_load_inbox(hybbx_storage_t *storage,
+                                         const char *username,
+                                         hybbx_mail_entry_t *entries,
+                                         size_t max_entries,
+                                         size_t *out_count)
+{
+    sqlite3 *db;
+    sqlite3_stmt *stmt;
+    char owner[HYBBX_USER_NAME_MAX];
+    int rc;
+    size_t count = 0;
+
+    if (storage == NULL || username == NULL || entries == NULL ||
+        out_count == NULL) {
+        return HYBBX_ERR_INVALID;
+    }
+
+    db = hybbx_storage_sql_mail_db(storage);
+    if (db == NULL) {
+        return HYBBX_ERR_IO;
+    }
+
+    mail_sql_owner_norm(owner, sizeof(owner), username);
+    *out_count = 0;
+
+    rc = sqlite3_prepare_v2(db,
+                            "SELECT id,from_user,subject,received_at,read_flag "
+                            "FROM messages WHERE owner=?1 AND folder=?2 "
+                            "ORDER BY received_at DESC, id DESC;",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        return HYBBX_ERR_IO;
+    }
+
+    sqlite3_bind_text(stmt, 1, owner, -1, SQLITE_STATIC);
+    sqlite3_bind_int(stmt, 2, MAIL_FOLDER_INBOX);
+
+    while (sqlite3_step(stmt) == SQLITE_ROW && count < max_entries) {
+        hybbx_mail_entry_t *entry = &entries[count];
+
+        memset(entry, 0, sizeof(*entry));
+        entry->id = (uint64_t)sqlite3_column_int64(stmt, 0);
+        hybbx_strlcpy(entry->from, (const char *)sqlite3_column_text(stmt, 1),
+                      sizeof(entry->from));
+        hybbx_strlcpy(entry->subject,
+                      (const char *)sqlite3_column_text(stmt, 2),
+                      sizeof(entry->subject));
+        entry->received_at = (time_t)sqlite3_column_int64(stmt, 3);
+        entry->read = sqlite3_column_int(stmt, 4);
+        count++;
+    }
+
+    sqlite3_finalize(stmt);
+
+    if (count > 1) {
+        qsort(entries, count, sizeof(entries[0]), mail_entry_compare);
+    }
+
+    *out_count = count;
+    return HYBBX_OK;
+}
+
+static hybbx_result_t mail_sql_trim_inbox(sqlite3 *db, const char *owner,
+                                          unsigned max_messages)
+{
+    sqlite3_stmt *stmt;
+    int rc;
+    size_t count = 0;
+
+    rc = sqlite3_prepare_v2(db,
+                            "SELECT COUNT(*) FROM messages "
+                            "WHERE owner=?1 AND folder=?2;",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        return HYBBX_ERR_IO;
+    }
+
+    sqlite3_bind_text(stmt, 1, owner, -1, SQLITE_STATIC);
+    sqlite3_bind_int(stmt, 2, MAIL_FOLDER_INBOX);
+    if (sqlite3_step(stmt) == SQLITE_ROW) {
+        count = (size_t)sqlite3_column_int64(stmt, 0);
+    }
+    sqlite3_finalize(stmt);
+
+    if (count <= max_messages) {
+        return HYBBX_OK;
+    }
+
+    rc = sqlite3_prepare_v2(db,
+                            "DELETE FROM messages WHERE id IN ("
+                            "SELECT id FROM messages WHERE owner=?1 "
+                            "AND folder=?2 ORDER BY received_at ASC, id ASC "
+                            "LIMIT ?3);",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        return HYBBX_ERR_IO;
+    }
+
+    sqlite3_bind_text(stmt, 1, owner, -1, SQLITE_STATIC);
+    sqlite3_bind_int(stmt, 2, MAIL_FOLDER_INBOX);
+    sqlite3_bind_int64(stmt, 3, (sqlite3_int64)(count - max_messages));
+    rc = sqlite3_step(stmt);
+    sqlite3_finalize(stmt);
+
+    return (rc == SQLITE_DONE) ? HYBBX_OK : HYBBX_ERR_IO;
+}
+
+hybbx_result_t hybbx_mail_sql_deliver(hybbx_storage_t *storage,
+                                      const hybbx_mail_config_t *mail,
+                                      const char *from_user,
+                                      const char *to_user,
+                                      const char *subject,
+                                      const char *body)
+{
+    sqlite3 *db;
+    sqlite3_stmt *stmt;
+    char owner[HYBBX_USER_NAME_MAX];
+    uint64_t id;
+    int rc;
+    hybbx_result_t hres;
+    time_t now = time(NULL);
+
+    if (storage == NULL || mail == NULL || from_user == NULL ||
+        to_user == NULL) {
+        return HYBBX_ERR_INVALID;
+    }
+
+    if (subject == NULL) {
+        subject = "";
+    }
+    if (body == NULL) {
+        body = "";
+    }
+
+    db = hybbx_storage_sql_mail_db(storage);
+    if (db == NULL) {
+        return HYBBX_ERR_IO;
+    }
+
+    mail_sql_owner_norm(owner, sizeof(owner), to_user);
+
+    hres = mail_sql_meta_bump(db, "mail_next", &id);
+    if (hres != HYBBX_OK) {
+        return hres;
+    }
+
+    rc = sqlite3_prepare_v2(db,
+                            "INSERT INTO messages(id,owner,from_user,subject,"
+                            "body,received_at,read_flag,deleted_at,folder) "
+                            "VALUES(?1,?2,?3,?4,?5,?6,0,0,?7);",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        return HYBBX_ERR_IO;
+    }
+
+    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)id);
+    sqlite3_bind_text(stmt, 2, owner, -1, SQLITE_STATIC);
+    sqlite3_bind_text(stmt, 3, from_user, -1, SQLITE_STATIC);
+    sqlite3_bind_text(stmt, 4, subject, -1, SQLITE_STATIC);
+    sqlite3_bind_text(stmt, 5, body, -1, SQLITE_STATIC);
+    sqlite3_bind_int64(stmt, 6, (sqlite3_int64)now);
+    sqlite3_bind_int(stmt, 7, MAIL_FOLDER_INBOX);
+    rc = sqlite3_step(stmt);
+    sqlite3_finalize(stmt);
+    if (rc != SQLITE_DONE) {
+        return HYBBX_ERR_IO;
+    }
+
+    return mail_sql_trim_inbox(db, owner, mail->max_messages);
+}
+
+unsigned hybbx_mail_sql_purge_recycle(hybbx_storage_t *storage,
+                                      const hybbx_mail_config_t *mail,
+                                      const char *username)
+{
+    sqlite3 *db;
+    sqlite3_stmt *stmt;
+    char owner[HYBBX_USER_NAME_MAX];
+    time_t cutoff;
+    int rc;
+
+    if (storage == NULL || mail == NULL || username == NULL) {
+        return 0;
+    }
+
+    db = hybbx_storage_sql_mail_db(storage);
+    if (db == NULL) {
+        return 0;
+    }
+
+    mail_sql_owner_norm(owner, sizeof(owner), username);
+    cutoff = time(NULL) - (time_t)mail->recycle_days * 86400;
+
+    rc = sqlite3_prepare_v2(db,
+                            "DELETE FROM messages WHERE owner=?1 AND folder=?2 "
+                            "AND deleted_at > 0 AND deleted_at < ?3;",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        return 0;
+    }
+
+    sqlite3_bind_text(stmt, 1, owner, -1, SQLITE_STATIC);
+    sqlite3_bind_int(stmt, 2, MAIL_FOLDER_RECYCLE);
+    sqlite3_bind_int64(stmt, 3, (sqlite3_int64)cutoff);
+    rc = sqlite3_step(stmt);
+    sqlite3_finalize(stmt);
+
+    return (rc == SQLITE_DONE) ? (unsigned)sqlite3_changes(db) : 0;
+}
+
+static hybbx_result_t mail_sql_resolve_index(hybbx_service_t *service,
+                                             hybbx_session_t *session,
+                                             unsigned list_index,
+                                             uint64_t *out_id)
+{
+    const hybbx_mail_config_t *mail;
+    hybbx_mail_entry_t entries[HYBBX_MAIL_MAX_MESSAGES];
+    size_t count;
+    hybbx_result_t rc;
+
+    mail = hybbx_service_get_mail(service);
+    if (mail == NULL || !mail->enabled) {
+        return HYBBX_ERR_UNSUPPORTED;
+    }
+
+    rc = hybbx_mail_sql_load_inbox(hybbx_service_get_storage(service),
+                                  hybbx_session_username(session),
+                                  entries, HYBBX_MAIL_MAX_MESSAGES, &count);
+    if (rc != HYBBX_OK) {
+        return rc;
+    }
+
+    if (list_index == 0 || list_index > count) {
+        return HYBBX_ERR_NOT_FOUND;
+    }
+
+    *out_id = entries[list_index - 1].id;
+    return HYBBX_OK;
+}
+
+hybbx_result_t hybbx_mail_sql_read(hybbx_service_t *service,
+                                   hybbx_session_t *session,
+                                   unsigned list_index)
+{
+    hybbx_storage_t *storage;
+    sqlite3 *db;
+    sqlite3_stmt *stmt;
+    uint64_t id;
+    char owner[HYBBX_USER_NAME_MAX];
+    char line[HYBBX_LINE_MAX];
+    int rc;
+    hybbx_result_t hres;
+    const char *from_user;
+    const char *subject;
+    const char *body;
+
+    storage = hybbx_service_get_storage(service);
+    if (storage == NULL) {
+        return HYBBX_ERR_INVALID;
+    }
+
+    hres = mail_sql_resolve_index(service, session, list_index, &id);
+    if (hres == HYBBX_ERR_NOT_FOUND) {
+        hybbx_session_write_line(session, "No such message.");
+        return HYBBX_OK;
+    }
+    if (hres != HYBBX_OK) {
+        return hres;
+    }
+
+    db = hybbx_storage_sql_mail_db(storage);
+    if (db == NULL) {
+        return HYBBX_ERR_IO;
+    }
+
+    mail_sql_owner_norm(owner, sizeof(owner), hybbx_session_username(session));
+
+    rc = sqlite3_prepare_v2(db,
+                            "SELECT from_user,subject,body FROM messages "
+                            "WHERE id=?1 AND owner=?2 AND folder=?3;",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        return HYBBX_ERR_IO;
+    }
+
+    sqlite3_bind_int64(stmt, 1, (sqlite3_int64)id);
+    sqlite3_bind_text(stmt, 2, owner, -1, SQLITE_STATIC);
+    sqlite3_bind_int(stmt, 3, MAIL_FOLDER_INBOX);
+    rc = sqlite3_step(stmt);
+    if (rc != SQLITE_ROW) {
+        sqlite3_finalize(stmt);
+        hybbx_session_write_line(session, "Cannot open message.");
+        return HYBBX_ERR_IO;
+    }
+
+    from_user = (const char *)sqlite3_column_text(stmt, 0);
+    subject = (const char *)sqlite3_column_text(stmt, 1);
+    body = (const char *)sqlite3_column_text(stmt, 2);
+    if (from_user == NULL) {
+        from_user = "";
+    }
+    if (subject == NULL) {
+        subject = "";
+    }
+    if (body == NULL) {
+        body = "";
+    }
+
+    snprintf(line, sizeof(line), "From: %s", from_user);
+    hybbx_session_write_line(session, line);
+    snprintf(line, sizeof(line), "Subject: %s", subject);
+    hybbx_session_write_line(session, line);
+
+    {
+        const char *p = body;
+        const char *nl;
+
+        while (*p != '\0') {
+            nl = strchr(p, '\n');
+            if (nl == NULL) {
+                hybbx_session_write_line(session, p);
+                break;
+            }
+            if (nl > p) {
+                size_t chunk = (size_t)(nl - p);
+
+                if (chunk >= sizeof(line)) {
+                    chunk = sizeof(line) - 1;
+                }
+                memcpy(line, p, chunk);
+                line[chunk] = '\0';
+                hybbx_session_write_line(session, line);
+            } else {
+                hybbx_session_write_line(session, "");
+            }
+            p = nl + 1;
+        }
+    }
+
+    sqlite3_finalize(stmt);
+
+    rc = sqlite3_prepare_v2(db,
+                            "UPDATE messages SET read_flag=1 "
+                            "WHERE id=?1 AND owner=?2;",
+                            -1, &stmt, NULL);
+    if (rc == SQLITE_OK) {
+        sqlite3_bind_int64(stmt, 1, (sqlite3_int64)id);
+        sqlite3_bind_text(stmt, 2, owner, -1, SQLITE_STATIC);
+        (void)sqlite3_step(stmt);
+        sqlite3_finalize(stmt);
+    }
+
+    return HYBBX_OK;
+}
+
+hybbx_result_t hybbx_mail_sql_delete_range(hybbx_service_t *service,
+                                           hybbx_session_t *session,
+                                           unsigned from,
+                                           unsigned to)
+{
+    const hybbx_mail_config_t *mail;
+    hybbx_storage_t *storage;
+    sqlite3 *db;
+    sqlite3_stmt *stmt;
+    hybbx_mail_entry_t entries[HYBBX_MAIL_MAX_MESSAGES];
+    char owner[HYBBX_USER_NAME_MAX];
+    size_t count;
+    size_t i;
+    unsigned moved = 0;
+    time_t now = time(NULL);
+    int rc;
+    hybbx_result_t hres;
+
+    mail = hybbx_service_get_mail(service);
+    storage = hybbx_service_get_storage(service);
+    if (mail == NULL || !mail->enabled || storage == NULL) {
+        return HYBBX_ERR_UNSUPPORTED;
+    }
+
+    if (to == 0) {
+        to = from;
+    }
+
+    (void)hybbx_mail_sql_purge_recycle(storage, mail,
+                                       hybbx_session_username(session));
+
+    hres = hybbx_mail_sql_load_inbox(storage, hybbx_session_username(session),
+                                      entries, HYBBX_MAIL_MAX_MESSAGES, &count);
+    if (hres != HYBBX_OK) {
+        return hres;
+    }
+
+    if (from > count || to > count) {
+        hybbx_session_write_line(session, "No such message.");
+        return HYBBX_OK;
+    }
+
+    db = hybbx_storage_sql_mail_db(storage);
+    if (db == NULL) {
+        return HYBBX_ERR_IO;
+    }
+
+    mail_sql_owner_norm(owner, sizeof(owner), hybbx_session_username(session));
+
+    rc = sqlite3_prepare_v2(db,
+                            "UPDATE messages SET folder=?4, deleted_at=?3 "
+                            "WHERE id=?1 AND owner=?2 AND folder=?5;",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        return HYBBX_ERR_IO;
+    }
+
+    for (i = (size_t)(from - 1); i < (size_t)to; i++) {
+        sqlite3_bind_int64(stmt, 1, (sqlite3_int64)entries[i].id);
+        sqlite3_bind_text(stmt, 2, owner, -1, SQLITE_STATIC);
+        sqlite3_bind_int64(stmt, 3, (sqlite3_int64)now);
+        sqlite3_bind_int(stmt, 4, MAIL_FOLDER_RECYCLE);
+        sqlite3_bind_int(stmt, 5, MAIL_FOLDER_INBOX);
+        rc = sqlite3_step(stmt);
+        sqlite3_reset(stmt);
+        if (rc == SQLITE_DONE && sqlite3_changes(db) > 0) {
+            moved++;
+        }
+    }
+
+    sqlite3_finalize(stmt);
+
+    if (moved == 0) {
+        hybbx_session_write_line(session, "Delete failed.");
+        return HYBBX_ERR_IO;
+    }
+
+    if (moved == 1) {
+        hybbx_session_write_line(session,
+            "Message moved to recycle (auto-purge after configured days).");
+    } else {
+        char buf[64];
+
+        snprintf(buf, sizeof(buf),
+                 "%u messages moved to recycle (auto-purge after %u days).",
+                 moved, mail->recycle_days);
+        hybbx_session_write_line(session, buf);
+    }
+
+    return HYBBX_OK;
+}
+
+hybbx_result_t hybbx_mail_sql_recycle_empty(hybbx_service_t *service,
+                                            hybbx_session_t *session)
+{
+    const hybbx_mail_config_t *mail;
+    hybbx_storage_t *storage;
+    sqlite3 *db;
+    sqlite3_stmt *stmt;
+    char owner[HYBBX_USER_NAME_MAX];
+    int rc;
+    char buf[64];
+
+    mail = hybbx_service_get_mail(service);
+    storage = hybbx_service_get_storage(service);
+    if (mail == NULL || !mail->enabled || storage == NULL) {
+        return HYBBX_ERR_UNSUPPORTED;
+    }
+
+    db = hybbx_storage_sql_mail_db(storage);
+    if (db == NULL) {
+        return HYBBX_ERR_IO;
+    }
+
+    mail_sql_owner_norm(owner, sizeof(owner), hybbx_session_username(session));
+    (void)hybbx_mail_sql_purge_recycle(storage, mail, owner);
+
+    rc = sqlite3_prepare_v2(db,
+                            "DELETE FROM messages WHERE owner=?1 AND folder=?2;",
+                            -1, &stmt, NULL);
+    if (rc != SQLITE_OK) {
+        hybbx_session_write_line(session, "Cannot read recycle bin.");
+        return HYBBX_ERR_IO;
+    }
+
+    sqlite3_bind_text(stmt, 1, owner, -1, SQLITE_STATIC);
+    sqlite3_bind_int(stmt, 2, MAIL_FOLDER_RECYCLE);
+    rc = sqlite3_step(stmt);
+    sqlite3_finalize(stmt);
+
+    if (rc != SQLITE_DONE) {
+        hybbx_session_write_line(session, "Cannot read recycle bin.");
+        return HYBBX_ERR_IO;
+    }
+
+    if (sqlite3_changes(db) == 0) {
+        hybbx_session_write_line(session, "Recycle bin empty.");
+    } else {
+        snprintf(buf, sizeof(buf), "Recycle bin emptied (%d message(s)).",
+                 sqlite3_changes(db));
+        hybbx_session_write_line(session, buf);
+    }
+
+    return HYBBX_OK;
+}
+
+#else /* HYBBX_HAVE_SQLITE */
+
+hybbx_result_t hybbx_mail_sql_load_inbox(hybbx_storage_t *storage,
+                                         const char *username,
+                                         hybbx_mail_entry_t *entries,
+                                         size_t max_entries,
+                                         size_t *out_count)
+{
+    (void)storage;
+    (void)username;
+    (void)entries;
+    (void)max_entries;
+    (void)out_count;
+    return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_mail_sql_deliver(hybbx_storage_t *storage,
+                                      const hybbx_mail_config_t *mail,
+                                      const char *from_user,
+                                      const char *to_user,
+                                      const char *subject,
+                                      const char *body)
+{
+    (void)storage;
+    (void)mail;
+    (void)from_user;
+    (void)to_user;
+    (void)subject;
+    (void)body;
+    return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_mail_sql_read(hybbx_service_t *service,
+                                   hybbx_session_t *session,
+                                   unsigned list_index)
+{
+    (void)service;
+    (void)session;
+    (void)list_index;
+    return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_mail_sql_delete_range(hybbx_service_t *service,
+                                           hybbx_session_t *session,
+                                           unsigned from,
+                                           unsigned to)
+{
+    (void)service;
+    (void)session;
+    (void)from;
+    (void)to;
+    return HYBBX_ERR_UNSUPPORTED;
+}
+
+hybbx_result_t hybbx_mail_sql_recycle_empty(hybbx_service_t *service,
+                                            hybbx_session_t *session)
+{
+    (void)service;
+    (void)session;
+    return HYBBX_ERR_UNSUPPORTED;
+}
+
+unsigned hybbx_mail_sql_purge_recycle(hybbx_storage_t *storage,
+                                      const hybbx_mail_config_t *mail,
+                                      const char *username)
+{
+    (void)storage;
+    (void)mail;
+    (void)username;
+    return 0;
+}
+
+#endif /* HYBBX_HAVE_SQLITE */