summaryrefslogtreecommitdiff
path: root/src/core/mail_sql.c
diff options
context:
space:
mode:
Diffstat (limited to 'src/core/mail_sql.c')
-rw-r--r--src/core/mail_sql.c655
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 */
git clone -b <branch> https://cgit.mode42.com/<repo>.git
git clone -b <branch> git://cgit.mode42.com/<repo>.git


info@mode42.com