/* $Id: db.c 2191 2006-06-30 12:57:37Z paul $ */ /* Copyright (C) 1999-2004 IC & S dbmail@ic-s.nl Modified by Mikhail Ramendik mr@ramendik.ru (MR) This program is free software; you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version. This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with this program; if not, write to the Free Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA. */ /** * \file db.c * * implement database functionality. This used to split out * between MySQL and PostgreSQL, but this is now integrated. * Only the actual calls to the database APIs are still in * place in the mysql/ and pgsql/ directories */ #include "db.h" #include "dbmail.h" #include "auth.h" #include "misc.h" #include #include #include #include #include #include #include #include static const char *db_flag_desc[] = { "seen_flag", "answered_flag", "deleted_flag", "flagged_flag", "draft_flag", "recent_flag" }; #define MAX_COLUMN_LEN 50 #define MAX_DATE_LEN 50 extern const char *TO_CHAR; extern const char *TO_DATE; /** list of tables used in dbmail */ #define DB_NTABLES 11 const char *DB_TABLENAMES[DB_NTABLES] = { "dbmail_users", "dbmail_aliases", "dbmail_mailboxes", "dbmail_messages", "dbmail_physmessage", "dbmail_messageblks", "dbmail_acl", "dbmail_subscription", "dbmail_pbsp", "dbmail_auto_notifications", "dbmail_auto_replies" }; /** can be used for making queries to db backend */ char query[DEF_QUERYSIZE]; /* size of buffer for writing messages to a client */ #define WRITE_BUFFER_SIZE 2048 /** static functions */ /** set quotum used for user user_idnr to curmail_size */ static int db_set_quotum_used(u64_t user_idnr, u64_t curmail_size); /** add to quotum used */ static int db_add_quotum_used(u64_t user_idnr, u64_t add_size); /** subtract from quotum used */ static int db_subtract_quotum_used(u64_t user_idnr, u64_t sub_size); /** check if the message will fit within or exceed the quotum */ static int db_check_quotum_used(u64_t user_idnr, u64_t msg_size); /** list all mailboxes owned by user owner_idnr */ static int db_list_mailboxes_by_regex(u64_t owner_idnr, int only_subscribed, regex_t * preg, u64_t ** mailboxes, unsigned int *nr_mailboxes); /** get size of a message */ static int db_get_message_size(u64_t message_idnr, u64_t * message_size); /** find a mailbox with a specific owner */ static int db_findmailbox_owner(const char *name, u64_t owner_idnr, u64_t * mailbox_idnr); /** get the total size of messages in a mailbox. Does not work recursively! */ static int db_get_mailbox_size(u64_t mailbox_idnr, int only_deleted, u64_t * mailbox_size); /** * constructs a string for use in queries. This is used to not be dependent * on the internal representation of a date in the database. Whenever the * date is queried for in a query, this function is used to get the right * database function for the query (TO_CHAR(date,format) for Postgres, * DATE_FORMAT(date, format) for MySQL). */ static char *date2char_str(const char *column); /** * constructs a string for use in queries. This is used to not be dependent * on the date representations a database can handle. Unfortunately, MySQL * only implements a function to handle this in version > 4.1.1. PostgreSQL * implements the TO_DATE function, which handles this very well. */ static char *char2date_str(const char *date); /** * check if the user_idnr is the same as that of the DBMAIL_DELIVERY_USERNAME * \param user_idnr user idnr to check * \return * - -1 on error * - 0 of different user * - 1 if same user (user_idnr belongs to DBMAIL_DELIVERY_USERNAME */ static int user_idnr_is_delivery_user_idnr(u64_t user_idnr); /** * set the first 5 characters of a mailbox name to "INBOX" if the name is * "inbox" or "inbox/somemailbox". This is used * when a mailbox with a name like "inbox" or "inbox/someMailbox" is used * to make sure the "inbox" part is always uppercase. * \param name name of the mailbox. strlen(name) must be bigger or equal * strlen("INBOX") */ void convert_inbox_to_uppercase(char *name); /* * check to make sure the database has been upgraded */ int db_check_version(void) { snprintf(query, DEF_QUERYSIZE, "SELECT 1=1 FROM dbmail_physmessage LIMIT 1 OFFSET 0"); if (db_query(query) == -1) { trace(TRACE_FATAL, "%s,%s: database incompatible. You may need to run the conversion script", __FILE__,__func__); return -1; } return 0; } int db_begin_transaction() { snprintf(query, DEF_QUERYSIZE, "BEGIN"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error beginning transaction", __FILE__, __func__); return -1; } return 0; } int db_commit_transaction() { snprintf(query, DEF_QUERYSIZE, "COMMIT"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error committing transaction", __FILE__, __func__); db_rollback_transaction(); return -1; } return 0; } int db_rollback_transaction() { snprintf(query, DEF_QUERYSIZE, "ROLLBACK"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error rolling back transaction", __FILE__, __func__); db_disconnect(); db_connect(); } return 0; } int db_escape_string(char **escaped, const char * const instr) { return db_escape_string_length(escaped, instr, strlen(instr)); } int db_escape_string_length(char **escaped, const char * const instr, unsigned long inlen) { /* If the arguments are invalid, problems. */ if (escaped == NULL || instr == NULL) { trace(TRACE_ERROR, "%s,%s: Got NULL arguments", __FILE__, __func__); return -1; } /* If there's no memory, problems. */ if (!(*escaped = (char *) dm_malloc(inlen * 2 + 1))) { trace(TRACE_ERROR, "%s,%s: malloc failed. Probably out of memory..", __FILE__, __func__); return -2; } /* If we get back a shorter string than we gave, problems. */ if (db_escape_direct(*escaped, instr, inlen) < inlen) { trace(TRACE_ERROR, "%s,%s: Escaped string was shorter than original.", __FILE__, __func__); dm_free(*escaped); return -3; } return 0; } int db_get_physmessage_id(u64_t message_idnr, u64_t * physmessage_id) { assert(physmessage_id != NULL); *physmessage_id = 0; snprintf(query, DEF_QUERYSIZE, "SELECT physmessage_id FROM dbmail_messages " "WHERE message_idnr = '%llu'", message_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error getting physmessage_id", __FILE__, __func__); return -1; } if (db_num_rows() < 1) { db_free_result(); return 0; } *physmessage_id = db_get_result_u64(0, 0); db_free_result(); return 1; } int db_get_quotum_used(u64_t user_idnr, u64_t * curmail_size) { assert(curmail_size != NULL); snprintf(query, DEF_QUERYSIZE, "SELECT curmail_size FROM dbmail_users " "WHERE user_idnr = '%llu'", user_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error getting used quotum for " "user [%llu]", __FILE__, __func__, user_idnr); return -1; } *curmail_size = db_get_result_u64(0, 0); db_free_result(); return 1; } /* this is a local (static) function */ int db_set_quotum_used(u64_t user_idnr, u64_t curmail_size) { snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_users SET curmail_size = '%llu' " "WHERE user_idnr = '%llu'", curmail_size, user_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error setting used quotum of " "[%llu] for user [%llu]", __FILE__, __func__, curmail_size, user_idnr); return -1; } return 0; } int db_add_quotum_used(u64_t user_idnr, u64_t add_size) { int result; trace(TRACE_DEBUG, "%s,%s: adding %llu to mailsize", __FILE__, __func__, add_size); result = user_idnr_is_delivery_user_idnr(user_idnr); if (result < 0) { trace(TRACE_ERROR, "%s,%s: call to " "user_idnr_is_delivery_user_idnr() failed", __FILE__, __func__); return -1; } /* don't do anything if this DBMAIL_DELIVERY_USERNAME's user_idnr * is given */ if (result == 1) return 0; snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_users SET curmail_size = curmail_size + '%llu' " "WHERE user_idnr = '%llu'", add_size, user_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error adding [%llu] to quotum " "of user [%llu]", __FILE__, __func__, add_size, user_idnr); return -1; } return 0; } int db_subtract_quotum_used(u64_t user_idnr, u64_t sub_size) { int result; trace(TRACE_DEBUG, "%s,%s: subtracting %llu from mailsize", __FILE__, __func__, sub_size); result = user_idnr_is_delivery_user_idnr(user_idnr); if (result < 0) { trace(TRACE_ERROR, "%s,%s: call to " "user_idnr_is_delivery_user_idnr() failed", __FILE__, __func__); return -1; } /* don't do anything if this DBMAIL_DELIVERY_USERNAME's user_idnr * is given */ if (result == 1) return 0; snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_users SET curmail_size = curmail_size - '%llu' " "WHERE user_idnr = '%llu'", sub_size, user_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error subtracting [%llu] from quotum " "of user [%llu]", __FILE__, __func__, sub_size, user_idnr); return -1; } return 0; } int db_check_quotum_used(u64_t user_idnr, u64_t msg_size) { snprintf(query, DEF_QUERYSIZE, "SELECT 1 FROM dbmail_users " "WHERE user_idnr = '%llu' " "AND (maxmail_size > 0) " "AND (curmail_size + '%llu' > maxmail_size)", user_idnr, msg_size); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error checking quotum for " "user [%llu]", __FILE__, __func__, user_idnr); return -1; } /* If there is a quotum defined, and the inequality is true, * then the message would therefore exceed the quotum, * and so the function returns non-zero. */ if (db_num_rows() > 0) { db_free_result(); return 1; } db_free_result(); return 0; } int db_calculate_quotum_all(void) { u64_t *user_idnrs; /**< will hold all user_idnr for which the quotum has to be set again */ u64_t *curmail_sizes; /**< will hold current mailsizes */ int i; int n; /**< number of records returned */ int result; /* the following query looks really weird, with its * NOT (... IS NOT NULL), but it must be like this, because * the normal query with IS NULL does not work on MySQL * for some reason. */ snprintf(query, DEF_QUERYSIZE, "SELECT usr.user_idnr, sum(pm.messagesize), usr.curmail_size " "FROM dbmail_users usr LEFT JOIN dbmail_mailboxes mbx " "ON mbx.owner_idnr = usr.user_idnr " "LEFT JOIN dbmail_messages msg " "ON msg.mailbox_idnr = mbx.mailbox_idnr " "LEFT JOIN dbmail_physmessage pm " "ON pm.id = msg.physmessage_id " "AND msg.status < '%d' " "GROUP BY usr.user_idnr, usr.curmail_size " "HAVING ((SUM(pm.messagesize) <> usr.curmail_size) OR " "(NOT (SUM(pm.messagesize) IS NOT NULL) " "AND usr.curmail_size <> 0))", MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error findng quotum used", __FILE__, __func__); return -1; } n = db_num_rows(); result = n; if (n == 0) { trace(TRACE_DEBUG, "%s,%s: quotum is already up to date", __FILE__, __func__); return 0; } if (!(user_idnrs = (u64_t *) dm_malloc(n * sizeof(u64_t)))) { trace(TRACE_ERROR, "%s,%s: malloc failed. Probably out of memory..", __FILE__, __func__); return -2; } if (!(curmail_sizes = (u64_t *) dm_malloc(n * sizeof(u64_t)))) { trace(TRACE_ERROR, "%s,%s: malloc failed: Probably out of memort..", __FILE__, __func__); dm_free(user_idnrs); return -2; } memset(user_idnrs, 0, n * sizeof(u64_t)); memset(curmail_sizes, 0, n * sizeof(u64_t)); for (i = 0; i < n; i++) { user_idnrs[i] = db_get_result_u64(i, 0); curmail_sizes[i] = db_get_result_u64(i, 1); } db_free_result(); /* now update the used quotum for all users that need to be updated */ for (i = 0; i < n; i++) { if (db_set_quotum_used(user_idnrs[i], curmail_sizes[i]) == -1) { trace(TRACE_ERROR, "%s,%s: error setting quotum used, " "trying to continue", __FILE__, __func__); result = -1; } } /* free allocated memory */ dm_free(user_idnrs); dm_free(curmail_sizes); return result; } int db_calculate_quotum_used(u64_t user_idnr) { u64_t quotum = 0; snprintf(query, DEF_QUERYSIZE, "SELECT SUM(pm.messagesize) " "FROM dbmail_physmessage pm, dbmail_messages m, dbmail_mailboxes mb " "WHERE m.physmessage_id = pm.id " "AND m.mailbox_idnr = mb.mailbox_idnr " "AND mb.owner_idnr = '%llu' " "AND m.status < '%d'", user_idnr, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -1; } if (db_num_rows() < 1) trace(TRACE_WARNING, "%s,%s: SUM did not give result, " "assuming empty mailbox", __FILE__, __func__); else { quotum = db_get_result_u64(0, 0); } db_free_result(); trace(TRACE_DEBUG, "%s, found quotum usage of [%llu] bytes", __func__, quotum); /* now insert the used quotum into the users table */ if (db_set_quotum_used(user_idnr, quotum) == -1) { if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error setting quotum for user [%llu]", __FILE__, __func__, user_idnr); return -1; } } return 0; } int db_get_users_from_clientid(u64_t client_id, u64_t ** user_ids, unsigned *num_users) { unsigned i; assert(user_ids != NULL); assert(num_users != NULL); *user_ids = NULL; *num_users = 0; snprintf(query, DEF_QUERYSIZE, "SELECT user_idnr FROM dbmail_users WHERE client_idnr = '%llu'", client_id); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error gettings users for " "client_id [%llu]", __FILE__, __func__, client_id); return -1; } *num_users = db_num_rows(); *user_ids = (u64_t *) dm_malloc(*num_users * sizeof(u64_t)); if (*user_ids == NULL) { trace(TRACE_ERROR, "%s,%s: error allocating memory, probably " "out of memory", __FILE__, __func__); db_free_result(); return -2; } memset(*user_ids, 0, *num_users * sizeof(u64_t)); for (i = 0; i < *num_users; i++) { (*user_ids)[i] = db_get_result_u64(i, 0); } db_free_result(); return 1; } char *db_get_deliver_from_alias(const char *alias) { char *escaped_alias; char *deliver = NULL; const char *query_result = NULL; if (db_escape_string(&escaped_alias, alias)) { trace(TRACE_ERROR, "%s,%s: error escaping alias.", __FILE__, __func__); return NULL; } snprintf(query, DEF_QUERYSIZE, "SELECT deliver_to FROM dbmail_aliases WHERE alias = '%s'", escaped_alias); dm_free(escaped_alias); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return NULL; } if (db_num_rows() == 0) { /* no such user */ db_free_result(); return dm_strdup(""); } query_result = db_get_result(0, 0); if (!query_result) { db_free_result(); return NULL; } deliver = dm_strdup(query_result); db_free_result(); return deliver; } int db_addalias(u64_t user_idnr, const char *alias, u64_t clientid) { char *escaped_alias; if (db_escape_string(&escaped_alias, alias)) { trace(TRACE_ERROR, "%s,%s: error escaping alias.", __FILE__, __func__); return -1; } /* check if this alias already exists */ snprintf(query, DEF_QUERYSIZE, "SELECT alias_idnr FROM dbmail_aliases " "WHERE lower(alias) = lower('%s') AND deliver_to = '%llu' " "AND client_idnr = '%llu'", escaped_alias, user_idnr, clientid); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query for searching alias failed", __FILE__, __func__); dm_free(escaped_alias); return -1; } if (db_num_rows() > 0) { trace(TRACE_INFO, "%s,%s: alias [%s] for user [%llu] already exists", __FILE__, __func__, alias, user_idnr); dm_free(escaped_alias); db_free_result(); return 1; } db_free_result(); snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_aliases (alias,deliver_to,client_idnr) " "VALUES ('%s','%llu','%llu')", escaped_alias, user_idnr, clientid); dm_free(escaped_alias); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query for adding alias failed", __FILE__, __func__); return -1; } return 0; } int db_addalias_ext(const char *alias, const char *deliver_to, u64_t clientid) { char *escaped_alias; char *escaped_deliver_to; if (db_escape_string(&escaped_alias, alias)) { trace(TRACE_ERROR, "%s,%s: error escaping alias.", __FILE__, __func__); return -1; } if (db_escape_string(&escaped_deliver_to, deliver_to)) { trace(TRACE_ERROR, "%s,%s: error escaping deliver_to.", __FILE__, __func__); dm_free(escaped_alias); return -1; } /* check if this alias already exists */ if (clientid != 0) { snprintf(query, DEF_QUERYSIZE, "SELECT alias_idnr FROM dbmail_aliases " "WHERE lower(alias) = lower('%s') AND " "lower(deliver_to) = lower('%s') " "AND client_idnr = '%llu'", escaped_alias, escaped_deliver_to, clientid); } else { snprintf(query, DEF_QUERYSIZE, "SELECT alias_idnr FROM dbmail_aliases " "WHERE lower(alias) = lower('%s') " "AND lower(deliver_to) = lower('%s') ", escaped_alias, escaped_deliver_to); } if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query for searching alias failed", __FILE__, __func__); dm_free(escaped_alias); dm_free(escaped_deliver_to); return -1; } if (db_num_rows() > 0) { trace(TRACE_INFO, "%s,%s: alias [%s] --> [%s] already exists", __FILE__, __func__, alias, deliver_to); dm_free(escaped_alias); dm_free(escaped_deliver_to); db_free_result(); return 1; } db_free_result(); snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_aliases (alias,deliver_to,client_idnr) " "VALUES ('%s','%s','%llu')", escaped_alias, escaped_deliver_to, clientid); dm_free(escaped_alias); dm_free(escaped_deliver_to); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query for adding alias failed", __FILE__, __func__); return -1; } return 0; } int db_removealias(u64_t user_idnr, const char *alias) { char *escaped_alias; if (db_escape_string(&escaped_alias, alias)) { trace(TRACE_ERROR, "%s,%s: error escaping alias.", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_aliases WHERE deliver_to='%llu' " "AND lower(alias) = lower('%s')", user_idnr, escaped_alias); dm_free(escaped_alias); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query failed", __FILE__, __func__); return -1; } return 0; } int db_removealias_ext(const char *alias, const char *deliver_to) { char *escaped_alias; char *escaped_deliver_to; if (db_escape_string(&escaped_alias, alias)) { trace(TRACE_ERROR, "%s,%s: error escaping alias.", __FILE__, __func__); return -1; } if (db_escape_string(&escaped_deliver_to, deliver_to)) { trace(TRACE_ERROR, "%s,%s: error escaping deliver_to.", __FILE__, __func__); dm_free(escaped_alias); return -1; } snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_aliases WHERE lower(deliver_to) = lower('%s') " "AND lower(alias) = lower('%s')", escaped_deliver_to, escaped_alias); dm_free(escaped_alias); dm_free(escaped_deliver_to); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query failed", __FILE__, __func__); return -1; } return 0; } int db_get_notify_address(u64_t user_idnr, char **notify_address) { const char *query_result = NULL; assert(notify_address != NULL); *notify_address = NULL; snprintf(query, DEF_QUERYSIZE, "SELECT notify_address " "FROM dbmail_auto_notifications WHERE user_idnr = %llu", user_idnr); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query failed", __FILE__, __func__); return -1; } if (db_num_rows() > 0) { query_result = db_get_result(0, 0); if (query_result && strlen(query_result) > 0) { *notify_address = dm_strdup(query_result); trace(TRACE_DEBUG, "%s,%s: found address [%s]", __FILE__, __func__, *notify_address); } } db_free_result(); return 0; } int db_get_reply_body(u64_t user_idnr, char **reply_body) { const char *query_result; *reply_body = NULL; snprintf(query, DEF_QUERYSIZE, "SELECT reply_body FROM dbmail_auto_replies " "WHERE user_idnr = %llu", user_idnr); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query failed", __FILE__, __func__); return -1; } if (db_num_rows() > 0) { query_result = db_get_result(0, 0); if (query_result && strlen(query_result) > 0) { *reply_body = dm_strdup(query_result); trace(TRACE_DEBUG, "%s,%s: found reply_body [%s]", __FILE__, __func__, *reply_body); } } db_free_result(); return 0; } u64_t db_get_mailbox_from_message(u64_t message_idnr) { u64_t mailbox_idnr; snprintf(query, DEF_QUERYSIZE, "SELECT mailbox_idnr FROM dbmail_messages " "WHERE message_idnr = '%llu'", message_idnr); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query failed", __FILE__, __func__); return -1; } if (db_num_rows() < 1) { trace(TRACE_DEBUG, "%s,%s: No mailbox found for message", __FILE__, __func__); db_free_result(); return 0; } mailbox_idnr = db_get_result_u64(0, 0); db_free_result(); return mailbox_idnr; } u64_t db_get_useridnr(u64_t message_idnr) { const char *query_result; u64_t user_idnr; snprintf(query, DEF_QUERYSIZE, "SELECT dbmail_mailboxes.owner_idnr FROM dbmail_mailboxes, dbmail_messages " "WHERE dbmail_mailboxes.mailbox_idnr = dbmail_messages.mailbox_idnr " "AND dbmail_messages.message_idnr = '%llu'", message_idnr); if (db_query(query) == -1) { /* query failed */ trace(TRACE_ERROR, "%s,%s: query failed", __FILE__, __func__); return -1; } if (db_num_rows() < 1) { trace(TRACE_DEBUG, "%s,%s: No owner found for message", __FILE__, __func__); db_free_result(); return 0; } query_result = db_get_result(0, 0); user_idnr = db_get_result_u64(0, 0); db_free_result(); return user_idnr; } int db_insert_physmessage_with_internal_date(timestring_t internal_date, u64_t * physmessage_id) { char *to_date_str; assert(physmessage_id != NULL); *physmessage_id = 0; to_date_str = char2date_str(internal_date); snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_physmessage (messagesize, internal_date) " "VALUES ('0', %s)", to_date_str); dm_free(to_date_str); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: insertion of physmessage failed", __FILE__, __func__); return -1; } *physmessage_id = db_insert_result("physmessage_id"); return 1; } int db_insert_physmessage(u64_t * physmessage_id) { assert(physmessage_id != NULL); *physmessage_id = 0; snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_physmessage (messagesize, internal_date) " "VALUES ('0', CURRENT_TIMESTAMP)"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: query failed", __FILE__, __func__); return -1; } *physmessage_id = db_insert_result("physmessage_id"); return 1; } int db_insert_message(u64_t user_idnr, const char *mailbox, int create_or_error_mailbox, const char *unique_id, u64_t * message_idnr) { u64_t mailboxid; u64_t physmessage_id; int result; assert(message_idnr); assert(unique_id); if (!mailbox) mailbox = dm_strdup("INBOX"); switch (create_or_error_mailbox) { case CREATE_IF_MBOX_NOT_FOUND: result = db_find_create_mailbox(mailbox, user_idnr, &mailboxid); break; case ERROR_IF_MBOX_NOT_FOUND: default: result = db_findmailbox(mailbox, user_idnr, &mailboxid); break; } if (result == -1) { trace(TRACE_ERROR, "%s,%s: error finding and/or creating mailbox [%s]", __FILE__, __func__, mailbox); return -1; } if (mailboxid == 0) { trace(TRACE_WARNING, "%s,%s: mailbox [%s] could not be found!", __FILE__, __func__, mailbox); return -1; } /* insert a new physmessage entry */ if (db_insert_physmessage(&physmessage_id) == -1) { trace(TRACE_ERROR, "%s,%s: error inserting physmessage", __FILE__, __func__); return -1; } /* now insert an entry into the messages table */ snprintf(query, DEF_QUERYSIZE, "INSERT INTO " "dbmail_messages(mailbox_idnr, physmessage_id, unique_id," "recent_flag, status) " "VALUES ('%llu', '%llu', '%s', '1', '%d')", mailboxid, physmessage_id, unique_id, MESSAGE_STATUS_INSERT); if (db_query(query) == -1) { trace(TRACE_STOP, "%s,%s: query failed", __FILE__, __func__); } *message_idnr = db_insert_result("message_idnr"); return 1; } int db_message_set_unique_id(u64_t message_idnr, const char *unique_id) { char *escaped_unique_id; if (db_escape_string(&escaped_unique_id, unique_id) < 0) { trace(TRACE_ERROR, "%s,%s: error escaping string.", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messages SET unique_id = '%s', status = '%d' " "WHERE message_idnr = '%llu'", unique_id, MESSAGE_STATUS_NEW, message_idnr); dm_free(escaped_unique_id); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: setting unique id for message " "[%llu] failed", __FILE__, __func__, message_idnr); return -1; } return 0; } int db_physmessage_set_sizes(u64_t physmessage_id, u64_t message_size, u64_t rfc_size) { assert(physmessage_id); assert(message_size); snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_physmessage SET " "messagesize = '%llu', rfcsize = '%llu' " "WHERE id = '%llu'", message_size, rfc_size, physmessage_id); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: error setting messagesize and " "rfcsize for physmessage [%llu]", __FILE__, __func__, physmessage_id); return -1; } return 0; } int db_update_message(u64_t message_idnr, const char *unique_id, u64_t message_size, u64_t rfc_size) { u64_t physmessage_id = 0; if (db_message_set_unique_id(message_idnr, unique_id) < 0) { trace(TRACE_STOP, "%s,%s: setting unique id failed of message " "[%llu] failed", __FILE__, __func__, message_idnr); } /* update the fields in the physmessage table */ if (db_get_physmessage_id(message_idnr, &physmessage_id) == -1) { trace(TRACE_ERROR, "%s,%s: could not find physmessage_id of message", __FILE__, __func__); return -1; } if (db_physmessage_set_sizes(physmessage_id, message_size, rfc_size) == -1) { trace(TRACE_ERROR, "%s,%s: error updating physmessage [%llu]. " "The database might be inconsistent. Run dbmail-util", __FILE__, __func__, physmessage_id); } if (db_add_quotum_used(db_get_useridnr(message_idnr), message_size) == -1) { trace(TRACE_ERROR, "%s,%s: error calculating quotum " "used for user [%llu]. Database might be " "inconsistent. run dbmail-util", __FILE__, __func__, db_get_useridnr(message_idnr)); return -1; } return 0; } int db_set_isheader(u64_t messageblk_idnr, blocktype_t is_header) { snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messageblks" " SET is_header = '%u'" " WHERE messageblk_idnr = '%llu'", is_header, messageblk_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not access messageblks table", __FILE__, __func__); return -1; } return 0; } int db_icheck_isheader(struct list *lost_list) { blocktype_t is_header; u64_t messageblk_idnr; int i, n; list_init(lost_list); snprintf(query, DEF_QUERYSIZE, "SELECT MIN(messageblk_idnr), MAX(is_header)" " FROM dbmail_messageblks" " GROUP BY physmessage_id HAVING MAX(is_header)=0"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not access messageblks table", __FILE__, __func__); return -1; } n = db_num_rows(); if (n < 1) { trace(TRACE_DEBUG, "%s,%s: no messageblocks", __FILE__, __func__); db_free_result(); return 0; } for (i = 0; i < n; i++) { messageblk_idnr = db_get_result_u64(i, 0); is_header = db_get_result_bool(i, 1); if (is_header != HEAD_BLOCK) { trace(TRACE_INFO, "%s,%s: found incorrect is_header block id [%llu]", __FILE__, __func__, messageblk_idnr); if (!list_nodeadd(lost_list, &messageblk_idnr, sizeof(u64_t))) { trace(TRACE_ERROR, "%s,%s: could not add block to list", __FILE__, __func__); list_freelist(&lost_list->start); db_free_result(); return -2; } } } db_free_result(); return 0; } int db_insert_message_block_physmessage(const char *block, u64_t block_size, u64_t physmessage_id, u64_t * messageblk_idnr, blocktype_t is_header) { char *escaped_query = NULL; unsigned maxesclen = (READ_BLOCK_SIZE + 1) * 2 + DEF_QUERYSIZE; unsigned startlen = 0; unsigned esclen = 0; assert(messageblk_idnr != NULL); *messageblk_idnr = 0; if (block == NULL) { trace(TRACE_ERROR, "%s,%s: got NULL as block. Insertion not possible", __FILE__, __func__); return -1; } if (block_size > READ_BLOCK_SIZE) { trace(TRACE_ERROR, "%s,%s: blocksize [%llu], maximum is [%ld]", __FILE__, __func__, block_size, READ_BLOCK_SIZE); return -1; } escaped_query = (char *) dm_malloc(sizeof(char) * maxesclen); if (!escaped_query) { trace(TRACE_ERROR, "%s,%s: not enough memory", __FILE__, __func__); return -1; } memset(escaped_query, '\0', maxesclen); startlen = snprintf(escaped_query, maxesclen, "INSERT INTO dbmail_messageblks " "(is_header, messageblk," " blocksize, physmessage_id)" " VALUES ('%u','", is_header); /* escape & add data */ esclen = db_escape_direct(&escaped_query[startlen], block, block_size); snprintf(&escaped_query[esclen + startlen], maxesclen - esclen - startlen, "', '%llu', '%llu')", block_size, physmessage_id); if (db_query(escaped_query) == -1) { dm_free(escaped_query); trace(TRACE_ERROR, "%s,%s: dbquery failed", __FILE__, __func__); return -1; } /* all done, clean up & exit */ dm_free(escaped_query); *messageblk_idnr = db_insert_result("messageblk_idnr"); return 1; } int db_insert_message_block(const char *block, u64_t block_size, u64_t message_idnr, u64_t * messageblk_idnr, blocktype_t is_header) { u64_t physmessage_id; assert(messageblk_idnr != NULL); *messageblk_idnr = 0; if (block == NULL) { trace(TRACE_ERROR, "%s,%s: got NULL as block, insertion not possible\n", __FILE__, __func__); return -1; } if (db_get_physmessage_id(message_idnr, &physmessage_id) < 0) { trace(TRACE_ERROR, "%s,%s: error getting physmessage_id", __FILE__, __func__); return -1; } if (db_insert_message_block_physmessage (block, block_size, physmessage_id, messageblk_idnr,is_header) < 0) { trace(TRACE_ERROR, "%s,%s: error inserting messageblks for " "physmessage [%llu]", __FILE__, __func__, physmessage_id); return -1; } return 1; } int db_log_ip(const char *ip) { u64_t id = 0; char *escaped_ip; if (db_escape_string(&escaped_ip, ip)) { trace(TRACE_ERROR, "%s,%s: error escaping ip.", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "SELECT idnr FROM dbmail_pbsp WHERE ipnumber = '%s'", escaped_ip); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not access ip-log table " "(pop/imap-before-smtp): %s", __FILE__, __func__, ip); dm_free(escaped_ip); return -1; } id = db_get_result_u64(0, 0); db_free_result(); if (id) { /* this IP is already in the table, update the 'since' field */ snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_pbsp " "SET since = CURRENT_TIMESTAMP WHERE idnr='%llu'", id); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not update ip-log " "(pop/imap-before-smtp)", __FILE__, __func__); dm_free(escaped_ip); return -1; } } else { /* IP not in table, insert row */ snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_pbsp (since, ipnumber) " "VALUES (CURRENT_TIMESTAMP, '%s')", escaped_ip); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not log IP number to dbase " "(pop/imap-before-smtp)", __FILE__, __func__); dm_free(escaped_ip); return -1; } } trace(TRACE_DEBUG, "%s,%s: ip [%s] logged\n", __FILE__, __func__, ip); dm_free(escaped_ip); return 0; } int db_count_iplog(const char *lasttokeep, u64_t *affected_rows) { char *escaped_lasttokeep; assert(affected_rows != NULL); *affected_rows = 0; if (db_escape_string(&escaped_lasttokeep, lasttokeep)) { trace(TRACE_ERROR, "%s,%s: error escaping last to keep.", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "SELECT * FROM dbmail_pbsp WHERE since < '%s'", escaped_lasttokeep); dm_free(escaped_lasttokeep); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s:%s: error executing query", __FILE__, __func__); return -1; } *affected_rows = db_get_affected_rows(); return 0; } int db_cleanup_iplog(const char *lasttokeep, u64_t *affected_rows) { char *escaped_lasttokeep; assert(affected_rows != NULL); *affected_rows = 0; if (db_escape_string(&escaped_lasttokeep, lasttokeep)) { trace(TRACE_ERROR, "%s,%s: error escaping last to keep.", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_pbsp WHERE since < '%s'", escaped_lasttokeep); dm_free(escaped_lasttokeep); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s:%s: error executing query", __FILE__, __func__); return -1; } *affected_rows = db_get_affected_rows(); return 0; } int db_cleanup(void) { return db_do_cleanup(DB_TABLENAMES, DB_NTABLES); } int db_empty_mailbox(u64_t user_idnr) { u64_t *mboxids = NULL; unsigned n, i; int result = 0; snprintf(query, DEF_QUERYSIZE, "SELECT mailbox_idnr FROM dbmail_mailboxes WHERE owner_idnr='%llu'", user_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error executing query", __FILE__, __func__); return -1; } n = db_num_rows(); if (n == 0) { db_free_result(); trace(TRACE_WARNING, "%s,%s: user [%llu] does not have any mailboxes?", __FILE__, __func__, user_idnr); return 0; } mboxids = (u64_t *) dm_malloc(n * sizeof(u64_t)); if (!mboxids) { trace(TRACE_ERROR, "%s,%s: not enough memory", __FILE__, __func__); db_free_result(); return -2; } memset(mboxids, 0, n * sizeof(u64_t)); for (i = 0; i < n; i++) { mboxids[i] = db_get_result_u64(i, 0); } db_free_result(); for (i = 0; i < n; i++) { if (db_delete_mailbox(mboxids[i], 1, 1) == -1) { trace(TRACE_ERROR, "%s,%s: error emptying mailbox [%llu]", __FILE__, __func__, mboxids[i]); result = -1; } } dm_free(mboxids); return result; } int db_icheck_messageblks(struct list *lost_list) { const char *query_result; u64_t messageblk_idnr; int i, n; list_init(lost_list); /* get all lost message blocks. Instead of doing all kinds of * nasty stuff here, we let the RDBMS handle all this. Problem * is that MySQL cannot handle subqueries. This is handled by * a left join select query. * This query will select all message block idnr that have no * associated physmessage in the physmessage table. */ snprintf(query, DEF_QUERYSIZE, "SELECT mb.messageblk_idnr FROM dbmail_messageblks mb " "LEFT JOIN dbmail_physmessage pm ON " "mb.physmessage_id = pm.id " "WHERE pm.id IS NULL"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: Could not execute query", __FILE__, __func__); return -1; } n = db_num_rows(); if (n < 1) { trace(TRACE_DEBUG, "%s,%s: no lost messageblocks", __FILE__, __func__); db_free_result(); return 0; } /* FIXME: this is actually properly designed... */ for (i = 0; i < n; i++) { query_result = db_get_result(i, 0); if (query_result) messageblk_idnr = strtoull(query_result, NULL, 10); else continue; trace(TRACE_INFO, "%s,%s: found lost block id [%llu]", __FILE__, __func__, messageblk_idnr); if (!list_nodeadd (lost_list, &messageblk_idnr, sizeof(u64_t))) { trace(TRACE_ERROR, "%s,%s: could not add block to list", __FILE__, __func__); list_freelist(&lost_list->start); db_free_result(); return -2; } } db_free_result(); return 0; } int db_icheck_messages(struct list *lost_list) { u64_t message_idnr; const char *query_result; int i, n; list_init(lost_list); snprintf(query, DEF_QUERYSIZE, "SELECT msg.message_idnr FROM dbmail_messages msg " "LEFT JOIN dbmail_mailboxes mbx ON " "msg.mailbox_idnr=mbx.mailbox_idnr " "WHERE mbx.mailbox_idnr IS NULL"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -2; } n = db_num_rows(); if (n < 1) { trace(TRACE_DEBUG, "%s,%s: no lost messages", __FILE__, __func__); db_free_result(); return 0; } /* FIXME: this is actually properly designed... */ for (i = 0; i < n; i++) { query_result = db_get_result(i, 0); if (query_result) message_idnr = strtoull(query_result, NULL, 10); else continue; trace(TRACE_INFO, "%s,%s: found lost message id [%llu]", __FILE__, __func__, message_idnr); if (!list_nodeadd(lost_list, &message_idnr, sizeof(u64_t))) { trace(TRACE_ERROR, "%s,%s: could not add message to list", __FILE__, __func__); list_freelist(&lost_list->start); db_free_result(); return -2; } } db_free_result(); return 0; } int db_icheck_mailboxes(struct list *lost_list) { u64_t mailbox_idnr; const char *query_result; int i, n; list_init(lost_list); snprintf(query, DEF_QUERYSIZE, "SELECT mbx.mailbox_idnr FROM dbmail_mailboxes mbx " "LEFT JOIN dbmail_users usr ON " "mbx.owner_idnr=usr.user_idnr " "WHERE usr.user_idnr is NULL"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -2; } n = db_num_rows(); if (n < 1) { trace(TRACE_DEBUG, "%s,%s: no lost mailboxes", __FILE__, __func__); db_free_result(); return 0; } /* FIXME: this is actually properly designed... */ for (i = 0; i < n; i++) { query_result = db_get_result(i, 0); if (query_result) mailbox_idnr = strtoull(query_result, NULL, 10); else continue; trace(TRACE_INFO, "%s,%s: found lost mailbox id [%llu]", __FILE__, __func__, mailbox_idnr); if (!list_nodeadd(lost_list, &mailbox_idnr, sizeof(u64_t))) { trace(TRACE_ERROR, "%s,%s: could not add mailbox to list", __FILE__, __func__); list_freelist(&lost_list->start); db_free_result(); return -2; } } db_free_result(); return 0; } int db_icheck_null_physmessages(struct list *lost_list) { u64_t physmessage_id; const char *result_string; unsigned i, n; list_init(lost_list); snprintf(query, DEF_QUERYSIZE, "SELECT pm.id FROM dbmail_physmessage pm " "LEFT JOIN dbmail_messageblks mbk ON " "pm.id = mbk.physmessage_id " "WHERE mbk.physmessage_id is NULL"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -1; } n = db_num_rows(); if (n < 1) { trace(TRACE_DEBUG, "%s,%s: no null physmessages", __FILE__, __func__); return 0; } /* FIXME: this is actually properly designed... */ for (i = 0; i < n; i++) { result_string = db_get_result(i, 0); if (result_string) physmessage_id = strtoull(result_string, NULL, 10); else continue; trace(TRACE_INFO, "%s,%s: found empty physmessage_id [%llu]", __FILE__, __func__, physmessage_id); if (!list_nodeadd (lost_list, &physmessage_id, sizeof(u64_t))) { trace(TRACE_ERROR, "%s,%s: could not add physmessage " "to list", __FILE__, __func__); list_freelist(&lost_list->start); db_free_result(); return -2; } } db_free_result(); return 0; } int db_icheck_null_messages(struct list *lost_list) { u64_t message_idnr; const char *query_result; int i, n; list_init(lost_list); snprintf(query, DEF_QUERYSIZE, "SELECT msg.message_idnr FROM dbmail_messages msg " "LEFT JOIN dbmail_physmessage pm ON " "msg.physmessage_id = pm.id " "WHERE pm.id is NULL"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -1; } n = db_num_rows(); if (n < 1) { trace(TRACE_DEBUG, "%s,%s: no null messages", __FILE__, __func__); db_free_result(); return 0; } /* FIXME: this is actually properly designed... */ for (i = 0; i < n; i++) { query_result = db_get_result(i, 0); if (query_result) message_idnr = strtoull(query_result, NULL, 10); else continue; trace(TRACE_INFO, "%s,%s: found empty message id [%llu]", __FILE__, __func__, message_idnr); if (!list_nodeadd(lost_list, &message_idnr, sizeof(u64_t))) { trace(TRACE_ERROR, "%s,%s: could not add message to list", __FILE__, __func__); list_freelist(&lost_list->start); db_free_result(); return -2; } } db_free_result(); return 0; } int db_set_message_status(u64_t message_idnr, MessageStatus_t status) { /** FIXME: We should check that, if a message is set from * a status < MESSAGE_STATUS_DELETE * to >= MESSAGE_STATUS_DELETE, the curmail_size is also changed */ snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messages SET status = %d " "WHERE message_idnr = '%llu'", status, message_idnr); return db_query(query); } int db_delete_messageblk(u64_t messageblk_idnr) { snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_messageblks " "WHERE messageblk_idnr = '%llu'", messageblk_idnr); return db_query(query); } int db_delete_physmessage(u64_t physmessage_id) { snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_physmessage WHERE id = '%llu'", physmessage_id); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -1; } /* if foreign keys do their work (not with MySQL ISAM tables :( ) the next query would not be necessary */ snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_messageblks WHERE physmessage_id = '%llu'", physmessage_id); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query. There " "are now messageblocks in the database that have no " "physmessage attached to them. run dbmail-util " "to fix this.", __FILE__, __func__); return -1; } return 1; } int db_delete_message(u64_t message_idnr) { u64_t physmessage_id; if (db_get_physmessage_id(message_idnr, &physmessage_id) < 0) { trace(TRACE_ERROR, "%s,%s: error getting physmessage_id", __FILE__, __func__); return -1; } /* now delete the message from the message table */ snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_messages WHERE message_idnr = '%llu'", message_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -1; } /* find if there are other messages pointing to the same physmessage entry */ snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr FROM dbmail_messages " "WHERE physmessage_id = '%llu'", physmessage_id); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -1; } if (db_num_rows() == 0) { /* there are no other messages with the same physmessage left. * the physmessage records and message blocks now need to * be removed */ db_free_result(); if (db_delete_physmessage(physmessage_id) < 0) { trace(TRACE_ERROR, "%s,%s: error deleting physmessage", __FILE__, __func__); return -1; } } else db_free_result(); return 1; } int db_delete_mailbox(u64_t mailbox_idnr, int only_empty, int update_curmail_size) { unsigned i, n; u64_t *message_idnrs; u64_t user_idnr = 0; int result; u64_t mailbox_size = 0; /* If we're updating the owner's total mail size, find the owner. */ if (update_curmail_size) { result = db_get_mailbox_owner(mailbox_idnr, &user_idnr); /* If there's an error finding the owner, bomb out. */ if (result < 0) { trace(TRACE_ERROR, "%s,%s: cannot find owner of mailbox for " "mailbox [%llu]", __FILE__, __func__, mailbox_idnr); return -1; } /* If we can't find an owner, give up. */ if (result == 0) { trace(TRACE_ERROR, "%s,%s: unable to find owner of mailbox [%llu]", __FILE__, __func__, mailbox_idnr); return 0; } /* Get the current mailbox size. */ if (db_get_mailbox_size(mailbox_idnr, 0, &mailbox_size) < 0) { trace(TRACE_ERROR, "%s,%s: error getting mailbox size " "for mailbox [%llu]", __FILE__, __func__, mailbox_idnr); return -1; } } /* If we're supposed to delete the mailbox, do it first; * if a command fails later on, the messages will be * unconnected and will be deleted by dbmail-util later. * * This also prevents delivery into the mailbox while * we are busy deleting it. */ if (!only_empty) { /* Delete the mailbox. */ snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_mailboxes WHERE mailbox_idnr = '%llu'", mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not delete mailbox [%llu]", __FILE__, __func__, mailbox_idnr); return -1; } } /* Query for all of the messages that were in the mailbox. */ snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr FROM dbmail_messages " "WHERE mailbox_idnr = '%llu'", mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not select message ID's for mailbox [%llu]", __FILE__, __func__, mailbox_idnr); return -1; } n = db_num_rows(); if (n == 0) { db_free_result(); trace(TRACE_INFO, "%s,%s: mailbox is empty", __FILE__, __func__); } if (!(message_idnrs = (u64_t *) dm_malloc(n * sizeof(u64_t)))) { trace(TRACE_ERROR, "%s,%s: error allocating memory", __FILE__, __func__); return -1; } for (i = 0; i < n; i++) message_idnrs[i] = db_get_result_u64(i, 0); db_free_result(); /* Delete messages one at a time. This means that we're * using pre-built message deletion functions, rather * than trying to cobble together a different query here. */ for (i = 0; i < n; i++) { if (db_delete_message(message_idnrs[i]) == -1) { trace(TRACE_ERROR, "%s,%s: error deleting message [%llu] " "database might be inconsistent. " "run dbmail-util", __FILE__, __func__, message_idnrs[i]); dm_free(message_idnrs); return -1; } } dm_free(message_idnrs); /* Calculate the new quotum. */ if (update_curmail_size) { if (db_subtract_quotum_used(user_idnr, mailbox_size) < 0) { trace(TRACE_ERROR, "%s,%s: error decreasing curmail_size", __FILE__, __func__); return -1; } } return 0; } int db_send_message_lines(void *fstream, u64_t message_idnr, long lines, int no_end_dot) { u64_t physmessage_id = 0; char *buffer = NULL; int buffer_pos; const char *nextpos; const char *tmppos = NULL; int block_count; u64_t rowlength; int n; const char *query_result; trace(TRACE_DEBUG, "%s,%s: request for [%ld] lines", __FILE__, __func__, lines); /* first find the physmessage_id */ snprintf(query, DEF_QUERYSIZE, "SELECT physmessage_id FROM dbmail_messages " "WHERE message_idnr = '%llu'", message_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error executing query", __FILE__, __func__); return 0; } physmessage_id = db_get_result_u64(0, 0); db_free_result(); buffer = dm_malloc(WRITE_BUFFER_SIZE * 2); if (buffer == NULL) { trace(TRACE_ERROR, "%s,%s: error allocating memory for buffer", __FILE__, __func__); return 0; } snprintf(query, DEF_QUERYSIZE, "SELECT messageblk FROM dbmail_messageblks " "WHERE physmessage_id='%llu' " "ORDER BY messageblk_idnr ASC", physmessage_id); trace(TRACE_DEBUG, "%s,%s: executing query [%s]", __FILE__, __func__, query); if (db_query(query) == -1) { dm_free(buffer); return 0; } trace(TRACE_DEBUG, "%s,%s: sending [%ld] lines from message [%llu]", __FILE__, __func__, lines, message_idnr); block_count = 0; n = db_num_rows(); /* loop over all rows in the result set, until the right amount of * lines has been read */ while ((block_count < n) && ((lines > 0) || (lines == -2) || (block_count == 0))) { query_result = db_get_result(block_count, 0); nextpos = query_result; rowlength = (u64_t) db_get_length(block_count, 0); /* reset our buffer */ memset(buffer, '\0', (WRITE_BUFFER_SIZE) * 2); buffer_pos = 0; while ((*nextpos != '\0') && (rowlength > 0) && ((lines > 0) || (lines == -2) || (block_count == 0))) { if (*nextpos == '\n') { /* first block is always the full header so this should not be counted when parsing if lines == -2 none of the lines should be counted since the whole message is requested */ if ((lines != -2) && (block_count != 0)) lines--; if (tmppos != NULL) { if (*tmppos == '\r') { buffer[buffer_pos++] = *nextpos; } else { buffer[buffer_pos++] = '\r'; buffer[buffer_pos++] = *nextpos; } } else { buffer[buffer_pos++] = '\r'; buffer[buffer_pos++] = *nextpos; } } else { if (*nextpos == '.') { if (tmppos != NULL) { if (*tmppos == '\n') { buffer [buffer_pos++] = '.'; buffer [buffer_pos++] = *nextpos; } else { buffer [buffer_pos++] = *nextpos; } } else { buffer[buffer_pos++] = *nextpos; } } else { buffer[buffer_pos++] = *nextpos; } } tmppos = nextpos; /* get the next character */ nextpos++; rowlength--; if (rowlength % WRITE_BUFFER_SIZE == 0) { /* purge buffer at every WRITE_BUFFER_SIZE bytes */ if (fwrite(buffer, sizeof(char), strlen(buffer), (FILE *)fstream) != strlen(buffer)) { trace(TRACE_ERROR, "%s,%s: error writing to " "fstream", __FILE__, __func__); db_free_result(); dm_free(buffer); return 0; } /* cleanup the buffer */ memset(buffer, '\0', (WRITE_BUFFER_SIZE * 2)); buffer_pos = 0; } } /* next block in while loop */ block_count++; trace(TRACE_DEBUG, "%s,%s: getting nextblock [%d]\n", __FILE__, __func__, block_count); /* flush our buffer */ if (fwrite(buffer, sizeof(char), strlen(buffer), (FILE *) fstream) != strlen(buffer)) { trace(TRACE_ERROR, "%s,%s: error writing to file stream", __FILE__, __func__); db_free_result(); dm_free(buffer); return 0; } } /* delimiter */ if (no_end_dot == 0) fprintf((FILE *) fstream, "\r\n.\r\n"); db_free_result(); dm_free(buffer); return 1; } int db_createsession(u64_t user_idnr, PopSession_t * session_ptr) { struct message tmpmessage; int message_counter = 0; unsigned i; const char *query_result; u64_t inbox_mailbox_idnr; list_init(&session_ptr->messagelst); if (db_findmailbox("INBOX", user_idnr, &inbox_mailbox_idnr) <= 0) { trace(TRACE_ERROR, "%s,%s: error finding mailbox_idnr of " "INBOX for user [%llu], exiting..", __FILE__, __func__, user_idnr); return -1; } /* query is <2 because we don't want deleted messages * the unique_id should not be empty, this could mean that the message is still being delivered */ snprintf(query, DEF_QUERYSIZE, "SELECT pm.messagesize, msg.message_idnr, msg.status, " "msg.unique_id FROM dbmail_messages msg, dbmail_physmessage pm " "WHERE msg.mailbox_idnr = '%llu' " "AND msg.status < '%d' " "AND msg.physmessage_id = pm.id " "order by status ASC", inbox_mailbox_idnr, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { return -1; } session_ptr->totalmessages = 0; session_ptr->totalsize = 0; message_counter = db_num_rows(); if (message_counter < 1) { /* there are no messages for this user */ db_free_result(); return 1; } /* messagecounter is total message, +1 tot end at message 1 */ message_counter++; /* filling the list */ trace(TRACE_DEBUG, "%s,%s: adding items to list", __FILE__, __func__); for (i = 0; i < db_num_rows(); i++) { /* message size */ tmpmessage.msize = db_get_result_u64(i, 0); /* real message id */ tmpmessage.realmessageid = db_get_result_u64(i, 1); /* message status */ tmpmessage.messagestatus = db_get_result_u64(i, 2); /* virtual message status */ tmpmessage.virtual_messagestatus = tmpmessage.messagestatus; /* unique id */ query_result = db_get_result(i, 3); if (query_result) strncpy(tmpmessage.uidl, query_result, UID_SIZE); session_ptr->totalmessages++; session_ptr->totalsize += tmpmessage.msize; /* descending to create inverted list */ message_counter--; tmpmessage.messageid = (u64_t) message_counter; list_nodeadd(&session_ptr->messagelst, &tmpmessage, sizeof(tmpmessage)); } trace(TRACE_DEBUG, "%s,%s: adding succesfull", __FILE__, __func__); /* setting all virtual values */ session_ptr->virtual_totalmessages = session_ptr->totalmessages; session_ptr->virtual_totalsize = session_ptr->totalsize; db_free_result(); return 1; } void db_session_cleanup(PopSession_t * session_ptr) { /* cleanups a session removes a list and all references */ session_ptr->totalsize = 0; session_ptr->virtual_totalsize = 0; session_ptr->totalmessages = 0; session_ptr->virtual_totalmessages = 0; list_freelist(&(session_ptr->messagelst.start)); } int db_update_pop(PopSession_t * session_ptr) { struct element *tmpelement; u64_t user_idnr = 0; /* get first element in list */ tmpelement = list_getstart(&session_ptr->messagelst); while (tmpelement != NULL) { /* check if they need an update in the database */ if (((struct message *) tmpelement->data)-> virtual_messagestatus != ((struct message *) tmpelement->data)->messagestatus) { /* use one message to get the user_idnr that goes with the messages */ if (user_idnr == 0) user_idnr = db_get_useridnr(((struct message *) tmpelement->data)-> realmessageid); /* yes they need an update, do the query */ snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messages set status='%d' WHERE " "message_idnr='%llu' AND status < '%d'", ((struct message *) tmpelement->data)->virtual_messagestatus, ((struct message *) tmpelement->data)-> realmessageid, MESSAGE_STATUS_DELETE); /* FIXME: a message could be deleted already if it has been accessed * by another interface and be deleted by sysop * we need a check if the query failes because it doesn't exists anymore * now it will just bailout. * ADDITION (ilja 2004-04-21) I don't think this is needed here. * The query does not fail when the message is already deleted, it * just does not do anything! */ if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not execute query", __FILE__, __func__); return -1; } } tmpelement = tmpelement->nextnode; } /* because the status of some messages might have changed (for instance * to status >= MESSAGE_STATUS_DELETE, the quotum has to be * recalculated */ if (user_idnr != 0) { if (db_calculate_quotum_used(user_idnr) == -1) { trace(TRACE_ERROR, "%s,%s: error calculating quotum used", __FILE__, __func__); return -1; } } return 0; } int db_set_deleted(u64_t * affected_rows) { assert(affected_rows != NULL); *affected_rows = 0; snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messages SET status = '%d' WHERE status = '%d'", MESSAGE_STATUS_PURGE, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: Could not execute query", __FILE__, __func__); return -1; } *affected_rows = db_get_affected_rows(); return 1; } int db_deleted_purge(u64_t * affected_rows) { unsigned i; u64_t *message_idnrs; assert(affected_rows != NULL); *affected_rows = 0; /* first we're deleting all the messageblks */ snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr FROM dbmail_messages WHERE status='%d'", MESSAGE_STATUS_PURGE); trace(TRACE_DEBUG, "%s,%s: executing query [%s]", __FILE__, __func__, query); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: Cound not fetch message ID numbers", __FILE__, __func__); return -1; } *affected_rows = db_num_rows(); if (*affected_rows == 0) { trace(TRACE_DEBUG, "%s,%s: no messages to purge", __FILE__, __func__); db_free_result(); return 0; } if (!(message_idnrs = (u64_t *) dm_malloc(*affected_rows * sizeof(u64_t)))) { trace(TRACE_ERROR, "%s,%s: error allocating memory", __FILE__, __func__); return -2; } /* delete each message */ for (i = 0; i < *affected_rows; i++) message_idnrs[i] = db_get_result_u64(i, 0); db_free_result(); for (i = 0; i < *affected_rows; i++) { if (db_delete_message(message_idnrs[i]) == -1) { trace(TRACE_ERROR, "%s,%s: error deleting message", __FILE__, __func__); dm_free(message_idnrs); return -1; } } dm_free(message_idnrs); return 1; } int db_purge_count(u64_t * affected_rows) { assert(affected_rows != NULL); *affected_rows = 0; /* first we're deleting all the messageblks */ snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr FROM dbmail_messages WHERE status='%d'", MESSAGE_STATUS_PURGE); trace(TRACE_DEBUG, "%s,%s: executing query [%s]", __FILE__, __func__, query); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: Cound not fetch message ID numbers", __FILE__, __func__); return -1; } *affected_rows = db_num_rows(); db_free_result(); return 1; } int db_deleted_count(u64_t * affected_rows) { assert(affected_rows != NULL); *affected_rows = 0; /* first we're deleting all the messageblks */ snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr FROM dbmail_messages WHERE status='%d'", MESSAGE_STATUS_DELETE); trace(TRACE_DEBUG, "%s,%s: executing query [%s]", __FILE__, __func__, query); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: Cound not fetch message ID numbers", __FILE__, __func__); return -1; } *affected_rows = db_num_rows(); db_free_result(); return 1; } int db_imap_append_msg(const char *msgdata, u64_t datalen, u64_t mailbox_idnr, u64_t user_idnr, timestring_t internal_date, u64_t * msg_idnr) { u64_t message_idnr; u64_t messageblk_idnr; u64_t physmessage_id = 0; u64_t count; char unique_id[UID_SIZE]; /* unique id */ switch (db_check_quotum_used(user_idnr, datalen)) { case -1: trace(TRACE_ERROR, "%s,%s: error checking quotum", __FILE__, __func__); return -1; case 1: trace(TRACE_INFO, "%s,%s: user [%llu] would exceed quotum", __FILE__, __func__, user_idnr); return 2; } if (strlen(internal_date) > 0) { if (db_insert_physmessage_with_internal_date(internal_date, &physmessage_id) < 0) { trace(TRACE_ERROR, "%s,%s: could not create physmessage " "with internal date [%s]", __FILE__, __func__, internal_date); return -1; } } else { if (db_insert_physmessage(&physmessage_id) < 0) { trace(TRACE_ERROR, "%s,%s: could not create physmessage", __FILE__, __func__); return -1; } } /* create a msg * according to the rfc, the recent flag has to be set to '1'. * this also means that the status will be set to '001' */ snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_messages " "(mailbox_idnr, physmessage_id, unique_id, status," "recent_flag) VALUES ('%llu', '%llu', '', '%d', '1')", mailbox_idnr, physmessage_id, MESSAGE_STATUS_SEEN); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not create message", __FILE__, __func__); return -1; } /* fetch the id of the new message */ message_idnr = db_insert_result("message_idnr"); /* ok insert blocks */ /* first the header: scan until double newline */ for (count = 1; count < datalen; count++) if (msgdata[count - 1] == '\n' && msgdata[count] == '\n') break; if (count == datalen) { trace(TRACE_INFO, "%s,%s: no double newline found [invalid msg]\n", __FILE__, __func__); if (db_delete_message(message_idnr) == -1) { trace(TRACE_ERROR, "%s,%s: could not delete invalid message" "%llu. Database could be invalid now..", __FILE__, __func__, message_idnr); } return 1; } if (count == datalen - 1) { /* msg consists of a single header */ trace(TRACE_INFO, "%s,%s: msg only contains a header", __FILE__, __func__); if (db_insert_message_block_physmessage(msgdata, datalen, physmessage_id, &messageblk_idnr,HEAD_BLOCK) == -1 || db_insert_message_block(" \n", 2, message_idnr, &messageblk_idnr,HEAD_BLOCK) == -1) { trace(TRACE_ERROR, "%s,%s: could not insert msg block\n", __FILE__, __func__); if (db_delete_message(message_idnr) == -1) { trace(TRACE_ERROR, "%s,%s: could not delete invalid message" "%llu. Database could be invalid now..", __FILE__, __func__, message_idnr); } return -1; } } else { /* * output header: * the first count bytes is the header */ count++; if (db_insert_message_block_physmessage( msgdata, count, physmessage_id, &messageblk_idnr,HEAD_BLOCK) == -1) { trace(TRACE_ERROR, "%s,%s: could not insert msg block\n", __FILE__, __func__); if (db_delete_message(message_idnr) == -1) { trace(TRACE_ERROR, "%s,%s: could not delete invalid message" "%llu. Database could be invalid now..", __FILE__, __func__, message_idnr); } return -1; } /* output message */ while ((datalen - count) > READ_BLOCK_SIZE) { if (db_insert_message_block_physmessage( &msgdata[count], READ_BLOCK_SIZE, physmessage_id, &messageblk_idnr,BODY_BLOCK) == -1) { trace(TRACE_ERROR, "%s,%s: could not insert msg block", __FILE__, __func__); if (db_delete_message(message_idnr) == -1) { trace(TRACE_ERROR, "%s,%s: could not delete invalid " "message %llu. Database could be invalid now..", __FILE__, __func__, message_idnr); } return -1; } count += READ_BLOCK_SIZE; } if (db_insert_message_block_physmessage( &msgdata[count], datalen - count, physmessage_id, &messageblk_idnr,BODY_BLOCK) == -1) { trace(TRACE_ERROR, "%s,%s: could not insert msg block\n", __FILE__, __func__); if (db_delete_message(message_idnr) == -1) { trace(TRACE_ERROR, "%s,%s: could not delete invalid " "message %llu. Database could be invalid now..", __FILE__, __func__, message_idnr); } return -1; } } /* set message size */ if (db_physmessage_set_sizes(physmessage_id, datalen, 0) < 0) { trace(TRACE_ERROR, "%s,%s: Error setting physmessages sizes", __FILE__, __func__); return -1; } /* create a unique id */ create_unique_id(unique_id, message_idnr); db_message_set_unique_id(message_idnr, unique_id); /* recalculate quotum used */ db_add_quotum_used(user_idnr, datalen); *msg_idnr = message_idnr; return 0; } int db_findmailbox(const char *fq_name, u64_t user_idnr, u64_t * mailbox_idnr) { const char *username = NULL; char *mailbox_name; char *name_str_copy; char *tempstr; size_t index; int result; u64_t owner_idnr = 0; assert(mailbox_idnr != NULL); *mailbox_idnr = 0; trace(TRACE_DEBUG, "%s,%s: looking for mailbox with FQN [%s].", __FILE__, __func__, fq_name); name_str_copy = dm_strdup(fq_name); /* see if this is a #User mailbox */ if ((strlen(NAMESPACE_USER) > 0) && (strstr(fq_name, NAMESPACE_USER) == fq_name)) { index = strcspn(name_str_copy, MAILBOX_SEPERATOR); tempstr = &name_str_copy[index + 1]; index = strcspn(tempstr, MAILBOX_SEPERATOR); username = tempstr; tempstr[index] = '\0'; mailbox_name = &tempstr[index + 1]; } else { if ((strlen(NAMESPACE_PUBLIC) > 0) && (strstr(fq_name, NAMESPACE_PUBLIC) == fq_name)) { index = strcspn(name_str_copy, MAILBOX_SEPERATOR); mailbox_name = &name_str_copy[index + 1]; username = PUBLIC_FOLDER_USER; } else { mailbox_name = name_str_copy; owner_idnr = user_idnr; } } if (username) { trace(TRACE_DEBUG, "%s,%s: finding user with name [%s].", __FILE__, __func__, username); result = auth_user_exists(username, &owner_idnr); if (result < 0) { trace(TRACE_ERROR, "%s,%s: error checking id of " "user.", __FILE__, __func__); return -1; } if (result == 0) { trace(TRACE_INFO, "%s,%s user [%s] not found.", __FILE__, __func__, username); return 0; } } result = db_findmailbox_owner(mailbox_name, owner_idnr, mailbox_idnr); if (result < 0) { trace(TRACE_ERROR, "%s,%s: error finding mailbox [%s] with " "owner [%s, %llu]", __FILE__, __func__, mailbox_name, username, owner_idnr); return -1; } dm_free(name_str_copy); return result; } int db_findmailbox_owner(const char *name, u64_t owner_idnr, u64_t * mailbox_idnr) { char *local_name; char *escaped_local_name; assert(mailbox_idnr != NULL); *mailbox_idnr = 0; local_name = dm_strdup(name); if (local_name == NULL) { trace(TRACE_ERROR, "%s,%s: error dm_strdup(name). Out of memory?", __FILE__, __func__); return -1; } convert_inbox_to_uppercase(local_name); if (db_escape_string(&escaped_local_name, local_name)) { trace(TRACE_ERROR, "%s,%s: error escaping local mailbox name.", __FILE__, __func__); dm_free(local_name); return -1; } snprintf(query, DEF_QUERYSIZE, "SELECT mailbox_idnr FROM dbmail_mailboxes " "WHERE name='%s' AND owner_idnr='%llu'", escaped_local_name, owner_idnr); dm_free(local_name); dm_free(escaped_local_name); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not select mailbox '%s'\n", __FILE__, __func__, name); db_free_result(); return -1; } if (db_num_rows() < 1) { db_free_result(); return 0; } else { *mailbox_idnr = db_get_result_u64(0, 0); db_free_result(); } if (*mailbox_idnr == 0) return 0; return 1; } int db_list_mailboxes_by_regex(u64_t user_idnr, int only_subscribed, regex_t * preg, u64_t ** mailboxes, unsigned int *nr_mailboxes) { unsigned int i; u64_t *tmp_mailboxes; u64_t *all_mailboxes; char** all_mailbox_names; u64_t *all_mailbox_owners; unsigned n_rows; assert(mailboxes != NULL); assert(nr_mailboxes != NULL); *mailboxes = NULL; *nr_mailboxes = 0; trace(TRACE_DEBUG, "%s,%s: in func", __FILE__, __func__); if (only_subscribed) snprintf(query, DEF_QUERYSIZE, "SELECT mbx.name, mbx.mailbox_idnr, mbx.owner_idnr " "FROM dbmail_mailboxes mbx " "LEFT JOIN dbmail_acl acl " "ON mbx.mailbox_idnr = acl.mailbox_id " "LEFT JOIN dbmail_users usr " "ON acl.user_id = usr.user_idnr " "LEFT JOIN dbmail_subscription sub " "ON sub.mailbox_id = mbx.mailbox_idnr " "WHERE " "sub.user_id = '%llu' AND (" "(mbx.owner_idnr = '%llu') OR " "(acl.user_id = '%llu' AND " " acl.lookup_flag = '1') OR " "(usr.userid = '%s' AND acl.lookup_flag = '1'))", user_idnr, user_idnr, user_idnr, DBMAIL_ACL_ANYONE_USER); else snprintf(query, DEF_QUERYSIZE, "SELECT mbx.name, mbx.mailbox_idnr, mbx.owner_idnr " "FROM dbmail_mailboxes mbx " "LEFT JOIN dbmail_acl acl " "ON mbx.mailbox_idnr = acl.mailbox_id " "LEFT JOIN dbmail_users usr " "ON acl.user_id = usr.user_idnr " "WHERE " "(mbx.owner_idnr = '%llu') OR " "(acl.user_id = '%llu' AND " " acl.lookup_flag = '1') OR " "(usr.userid = '%s' AND acl.lookup_flag = '1')", user_idnr, user_idnr, DBMAIL_ACL_ANYONE_USER); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error during mailbox query", __FILE__, __func__); return (-1); } n_rows = db_num_rows(); if (n_rows == 0) { /* none exist, none matched */ db_free_result(); return 0; } all_mailboxes = (u64_t *) dm_malloc(n_rows * sizeof(u64_t)); all_mailbox_names = (char **) dm_malloc(n_rows * sizeof(char*)); all_mailbox_owners = (u64_t *) dm_malloc(n_rows * sizeof(u64_t)); tmp_mailboxes = (u64_t *) dm_malloc(n_rows * sizeof(u64_t)); if (!all_mailboxes || !all_mailbox_names || !all_mailbox_owners || !tmp_mailboxes) { trace(TRACE_ERROR, "%s,%s: not enough memory\n", __FILE__, __func__); if (all_mailboxes) dm_free(all_mailboxes); if (all_mailbox_names) dm_free(all_mailbox_names); if (all_mailbox_owners) dm_free(all_mailbox_owners); if (tmp_mailboxes) dm_free(tmp_mailboxes); return (-2); } for (i = 0; i < n_rows; i++) { all_mailbox_names[i] = dm_strdup(db_get_result(i, 0)); all_mailboxes[i] = db_get_result_u64(i, 1); all_mailbox_owners[i] = db_get_result_u64(i, 2); } db_free_result(); for (i = 0; i < n_rows; i++) { char *mailbox_name; u64_t mailbox_idnr = all_mailboxes[i]; u64_t owner_idnr = all_mailbox_owners[i]; char *simple_mailbox_name = all_mailbox_names[i]; trace(TRACE_DEBUG, "%s,%s: checking mailbox: %s, nr %llu, owner = %llu", __FILE__, __func__, simple_mailbox_name, mailbox_idnr, owner_idnr); /* add possible namespace prefix to mailbox_name */ mailbox_name = mailbox_add_namespace(simple_mailbox_name, owner_idnr, user_idnr); if (mailbox_name) { trace(TRACE_DEBUG, "%s,%s: comparing mailbox [%s] to " "regular expression", __FILE__, __func__, mailbox_name); if (regexec(preg, mailbox_name, 0, NULL, 0) == 0) { tmp_mailboxes[*nr_mailboxes] = mailbox_idnr; (*nr_mailboxes)++; trace(TRACE_DEBUG, "%s,%s: regex match %s, " "mailbox_idnr = %llu", __FILE__, __func__, mailbox_name, mailbox_idnr); } } } dm_free(all_mailbox_names); dm_free(all_mailboxes); dm_free(all_mailbox_owners); trace(TRACE_DEBUG, "%s,%s: exit", __FILE__, __func__); if (*nr_mailboxes == 0) { /* none exist, none matched */ dm_free(tmp_mailboxes); return 0; } *mailboxes = tmp_mailboxes; return 1; } int db_findmailbox_by_regex(u64_t owner_idnr, const char *pattern, u64_t ** children, unsigned *nchildren, int only_subscribed) { int result; regex_t preg; *children = NULL; if ((result = regcomp(&preg, pattern, REG_ICASE | REG_NOSUB)) != 0) { trace(TRACE_ERROR, "%s,%s: error compiling regex pattern: %d\n", __FILE__, __func__, result); return 1; } /* list normal mailboxes */ if (db_list_mailboxes_by_regex(owner_idnr, only_subscribed, &preg, children, nchildren) < 0) { trace(TRACE_ERROR, "%s,%s: error listing mailboxes", __FILE__, __func__); regfree(&preg); return -1; } if (*nchildren == 0) { trace(TRACE_INFO, "%s, %s: did not find any mailboxes that " "match pattern. returning 0, nchildren = 0", __FILE__, __func__); regfree(&preg); return 0; } /* store matches */ trace(TRACE_INFO, "%s,%s: found [%d] mailboxes", __FILE__, __func__, *nchildren); regfree(&preg); return 0; } int db_getmailbox(mailbox_t * mb) { unsigned i; unsigned exists, seen, recent; /* free existing MSN list */ if (mb->seq_list) { dm_free(mb->seq_list); mb->seq_list = NULL; } mb->flags = 0; mb->exists = 0; mb->unseen = 0; mb->recent = 0; mb->msguidnext = 0; /* select mailbox */ snprintf(query, DEF_QUERYSIZE, "SELECT permission," "seen_flag," "answered_flag," "deleted_flag," "flagged_flag," "recent_flag," "draft_flag " "FROM dbmail_mailboxes WHERE mailbox_idnr = '%llu'", mb->uid); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not select mailbox\n", __FILE__, __func__); return -1; } if (db_num_rows() == 0) { trace(TRACE_ERROR, "%s,%s: invalid mailbox id specified", __FILE__, __func__); db_free_result(); return -1; } mb->permission = db_get_result_int(0, 0); if (db_get_result(0, 1)) mb->flags |= IMAPFLAG_SEEN; if (db_get_result(0, 2)) mb->flags |= IMAPFLAG_ANSWERED; if (db_get_result(0, 3)) mb->flags |= IMAPFLAG_DELETED; if (db_get_result(0, 4)) mb->flags |= IMAPFLAG_FLAGGED; if (db_get_result(0, 5)) mb->flags |= IMAPFLAG_RECENT; if (db_get_result(0, 6)) mb->flags |= IMAPFLAG_DRAFT; db_free_result(); /* count messages */ snprintf(query, DEF_QUERYSIZE, "SELECT 'a',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='%llu' " "AND (status='%d' OR status='%d') UNION " "SELECT 'b',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='%llu' " "AND (status='%d' OR status='%d') AND seen_flag=1 UNION " "SELECT 'c',COUNT(*) FROM dbmail_messages WHERE mailbox_idnr='%llu' " "AND (status='%d' OR status='%d') AND recent_flag=1", mb->uid, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN, mb->uid, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN, mb->uid, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: query error", __FILE__, __func__); return -1; } exists = (unsigned)db_get_result_int(0,1); seen = (unsigned)db_get_result_int(1,1); recent = (unsigned)db_get_result_int(2,1); mb->exists = exists; mb->unseen = exists - seen; mb->recent = recent; db_free_result(); /* get messages */ if (mb->exists) { snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr FROM dbmail_messages WHERE mailbox_idnr = '%llu' " "AND status < '%d' ORDER BY message_idnr ASC", mb->uid, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: query error [%s]", __FILE__, __func__, query); return -1; } if (! (mb->seq_list = (u64_t *) dm_malloc(sizeof(u64_t) * mb->exists))) { db_free_result(); trace(TRACE_ERROR,"%s,%s: malloc error mb->seq_list [%d]", __FILE__, __func__, mb->exists); return -1; } for (i = 0; i < db_num_rows(); i++) mb->seq_list[i] = db_get_result_u64(i, 0); } db_free_result(); /* now determine the next message UID * NOTE expunged messages are selected as well in order to be * able to restore them */ /* By suggestion of John Hansen on the mailing list, * this is instead of MAX(message_idnr) as that is not * indexable on PostgreSQL -- Feb 2 2005 */ snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr + 1 FROM dbmail_messages" " ORDER BY message_idnr DESC LIMIT 1"); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: query error", __FILE__, __func__); dm_free(mb->seq_list); mb->seq_list = NULL; return -1; } mb->msguidnext = db_get_result_u64(0, 0); db_free_result(); return 0; } int db_createmailbox(const char *name, u64_t owner_idnr, u64_t * mailbox_idnr) { const char *simple_name; char *escaped_simple_name; assert(mailbox_idnr != NULL); *mailbox_idnr = 0; /* remove namespace information from mailbox name */ if (!(simple_name = mailbox_remove_namespace(name))) { trace(TRACE_ERROR, "%s,%s: could not create simple mailbox name " "from full name", __FILE__, __func__); return -1; } if (db_escape_string(&escaped_simple_name, simple_name)) { trace(TRACE_ERROR, "%s,%s: error escaping simple mailbox name.", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_mailboxes (name, owner_idnr," "seen_flag, answered_flag, deleted_flag, flagged_flag, " "recent_flag, draft_flag, permission)" " VALUES ('%s', '%llu', 1, 1, 1, 1, 1, 1, 2)", escaped_simple_name, owner_idnr); dm_free(escaped_simple_name); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not create mailbox", __FILE__, __func__); return -1; } *mailbox_idnr = db_insert_result("mailbox_idnr"); return 0; } int db_find_create_mailbox(const char *name, u64_t owner_idnr, u64_t * mailbox_idnr) { u64_t mboxidnr; assert(mailbox_idnr != NULL); *mailbox_idnr = 0; /* Did we fail to find the mailbox? */ if (db_findmailbox_owner(name, owner_idnr, &mboxidnr) != 1) { /* Did we fail to create the mailbox? */ if (db_createmailbox(name, owner_idnr, &mboxidnr) != 0) { /* Serious failure situation! */ trace(TRACE_ERROR, "%s, %s: seriously could not create mailbox [%s]", __FILE__, __func__, name); return -1; } trace(TRACE_DEBUG, "%s, %s: mailbox [%s] created on the fly", __FILE__, __func__, name); } trace(TRACE_DEBUG, "%s, %s: mailbox [%s] found", __FILE__, __func__, name); *mailbox_idnr = mboxidnr; return 0; } int db_listmailboxchildren(u64_t mailbox_idnr, u64_t user_idnr, u64_t ** children, int *nchildren, const char *filter) { int i; char *mailbox_name = NULL; const char *tmp; char *escaped_filter; /* retrieve the name of this mailbox */ snprintf(query, DEF_QUERYSIZE, "SELECT name FROM dbmail_mailboxes WHERE " "mailbox_idnr = '%llu' AND owner_idnr = '%llu'", mailbox_idnr, user_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not retrieve mailbox name\n", __FILE__, __func__); return -1; } if (db_num_rows() == 0) { trace(TRACE_WARNING, "%s,%s: No mailbox found with mailbox_idnr " "[%llu]", __FILE__, __func__, mailbox_idnr); db_free_result(); *children = NULL; *nchildren = 0; return 0; } if ((tmp = db_get_result(0, 0))) mailbox_name = dm_strdup(tmp); db_free_result(); if (db_escape_string(&escaped_filter, filter) < 0) { trace(TRACE_ERROR, "%s,%s: error escaping filter string", __FILE__, __func__); if (mailbox_name) dm_free(mailbox_name); return -1; } if (mailbox_name) { /* FIXME: Does this need escaping? */ snprintf(query, DEF_QUERYSIZE, "SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name LIKE '%s/%s'" " AND owner_idnr = '%llu'", mailbox_name, filter, user_idnr); dm_free(mailbox_name); } else snprintf(query, DEF_QUERYSIZE, "SELECT mailbox_idnr FROM dbmail_mailboxes WHERE name LIKE '%s'" " AND owner_idnr = '%llu'", filter, user_idnr); dm_free(escaped_filter); /* now find the children */ if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not retrieve mailbox id", __FILE__, __func__); return -1; } if (db_num_rows() == 0) { /* empty set */ *children = NULL; *nchildren = 0; db_free_result(); return 0; } *nchildren = db_num_rows(); if (*nchildren == 0) { *children = NULL; db_free_result(); return 0; } *children = (u64_t *) dm_malloc(sizeof(u64_t) * (*nchildren)); if (!(*children)) { /* out of mem */ trace(TRACE_ERROR, "%s,%s: out of memory\n", __FILE__, __FILE__); db_free_result(); return -1; } for (i = 0; i < *nchildren; i++) { (*children)[i] = db_get_result_u64(i, 0); } db_free_result(); return 0; /* success */ } int db_isselectable(u64_t mailbox_idnr) { const char *query_result; long not_selectable; snprintf(query, DEF_QUERYSIZE, "SELECT no_select FROM dbmail_mailboxes WHERE mailbox_idnr = '%llu'", mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not retrieve select-flag", __FILE__, __func__); return -1; } if (db_num_rows() == 0) { db_free_result(); return 0; } query_result = db_get_result(0, 0); if (!query_result) { trace(TRACE_ERROR, "%s,%s: query result is NULL, but there is a " "result set", __FILE__, __func__); db_free_result(); return -1; } not_selectable = strtol(query_result, NULL, 10); db_free_result(); if (not_selectable == 0) return 1; else return 0; } int db_noinferiors(u64_t mailbox_idnr) { const char *query_result; long no_inferiors; snprintf(query, DEF_QUERYSIZE, "SELECT no_inferiors FROM dbmail_mailboxes WHERE mailbox_idnr = '%llu'", mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not retrieve noinferiors-flag", __FILE__, __func__); return -1; } if (db_num_rows() == 0) { db_free_result(); return 0; } query_result = db_get_result(0, 0); if (!query_result) { trace(TRACE_ERROR, "%s,%s: query result is NULL, but there is a " "result set", __FILE__, __func__); db_free_result(); return 0; } no_inferiors = strtol(query_result, NULL, 10); db_free_result(); return no_inferiors; } int db_nochildren(u64_t mailbox_idnr) { u64_t owner_idnr; char *name; char *escaped_name; int no_children; snprintf(query, DEF_QUERYSIZE, "SELECT owner_idnr, name FROM dbmail_mailboxes " "WHERE mailbox_idnr = '%llu' ", mailbox_idnr); if (db_query(query)==-1) return -1; if (db_num_rows() != 1) { db_free_result(); return -1; } owner_idnr = db_get_result_u64(0,0); name = strdup(db_get_result(0,1)); db_free_result(); if (db_escape_string(&escaped_name, name)) { trace(TRACE_ERROR, "%s,%s: error escaping mailbox name.", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "SELECT COUNT(*) AS nr_children " "FROM dbmail_mailboxes WHERE owner_idnr = '%llu' " "AND name LIKE '%s%s%%'", owner_idnr, escaped_name, MAILBOX_SEPERATOR); dm_free(escaped_name); dm_free(name); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not determine hasnochildren-flag", __FILE__, __func__); return -1; } if (db_num_rows() == 0) { db_free_result(); return -1; } no_children = db_get_result_u64(0,0)?0:1; db_free_result(); return no_children; } int db_setselectable(u64_t mailbox_idnr, int select_value) { snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_mailboxes SET no_select = %d WHERE mailbox_idnr = '%llu'", (!select_value), mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not set noselect-flag", __FILE__, __func__); return -1; } return 0; } int db_get_mailbox_size(u64_t mailbox_idnr, int only_deleted, u64_t * mailbox_size) { assert(mailbox_size != NULL); *mailbox_size = 0; if (only_deleted) snprintf(query, DEF_QUERYSIZE, "SELECT sum(pm.messagesize) FROM dbmail_messages msg, " "dbmail_physmessage pm " "WHERE msg.physmessage_id = pm.id " "AND msg.mailbox_idnr = '%llu' " "AND msg.status < '%d' " "AND msg.deleted_flag = '1'", mailbox_idnr, MESSAGE_STATUS_DELETE); else snprintf(query, DEF_QUERYSIZE, "SELECT sum(pm.messagesize) FROM dbmail_messages msg, " "dbmail_physmessage pm " "WHERE msg.physmessage_id = pm.id " "AND msg.mailbox_idnr = '%llu' " "AND msg.status < '%d'", mailbox_idnr, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not calculate size of " "mailbox [%llu]", __FILE__, __func__, mailbox_idnr); return -1; } if (db_num_rows() > 0) { *mailbox_size = db_get_result_u64(0, 0); } db_free_result(); return 0; } int db_removemsg(u64_t user_idnr, u64_t mailbox_idnr) { u64_t mailbox_size; if (db_get_mailbox_size(mailbox_idnr, 0, &mailbox_size) < 0) { trace(TRACE_ERROR, "%s,%s: error getting size for mailbox [%llu]", __FILE__, __func__, mailbox_idnr); return -1; } /* update messages belonging to this mailbox: mark as deleted (status MESSAGE_STATUS_PURGE) */ snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messages SET status='%d' WHERE mailbox_idnr = '%llu'", MESSAGE_STATUS_PURGE, mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not update messages in mailbox", __FILE__, __func__); return -1; } if (db_subtract_quotum_used(user_idnr, mailbox_size) < 0) { trace(TRACE_ERROR, "%s,%s: error subtracting mailbox size from " "used quotum for mailbox [%llu], user [%llu]. Database " "might be inconsistent. Run dbmail-util", __FILE__, __func__, mailbox_idnr, user_idnr); return -1; } return 0; /* success */ } int db_movemsg(u64_t mailbox_to, u64_t mailbox_from) { snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messages SET mailbox_idnr='%llu' WHERE" " mailbox_idnr = '%llu'", mailbox_to, mailbox_from); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not update messages in mailbox\n", __FILE__, __func__); return -1; } return 0; /* success */ } int db_get_message_size(u64_t message_idnr, u64_t * message_size) { const char *result_string; assert(message_size != NULL); snprintf(query, DEF_QUERYSIZE, "SELECT pm.messagesize FROM dbmail_physmessage pm, dbmail_messages msg " "WHERE pm.id = msg.physmessage_id " "AND message_idnr = '%llu'", message_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not fetch message size for message id " "[%llu]", __FILE__, __func__, message_idnr); return -1; } if (db_num_rows() != 1) { trace(TRACE_ERROR, "%s,%s: message [%llu] does not exist/has " "multiple entries\n", __FILE__, __func__, message_idnr); db_free_result(); return -1; } result_string = db_get_result(0, 0); if (result_string) *message_size = strtoull(result_string, NULL, 10); else { trace(TRACE_ERROR, "%s,%s: no result set after requesting msgsize " "of msg [%llu]\n", __FILE__, __func__, message_idnr); db_free_result(); return -1; } db_free_result(); return 1; } int db_copymsg(u64_t msg_idnr, u64_t mailbox_to, u64_t user_idnr, u64_t * newmsg_idnr) { u64_t msgsize; char unique_id[UID_SIZE]; /* Get the size of the message to be copied. */ if (db_get_message_size(msg_idnr, &msgsize) == -1) { trace(TRACE_ERROR, "%s,%s: error getting message size for " "message [%llu]", __FILE__, __func__, msg_idnr); return -1; } /* Check to see if the user has room for the message. */ switch (db_check_quotum_used(user_idnr, msgsize)) { case -1: trace(TRACE_ERROR, "%s,%s: error checking quotum", __FILE__, __func__); return -1; case 1: trace(TRACE_INFO, "%s,%s: user [%llu] would exceed quotum", __FILE__, __func__, user_idnr); return -2; } create_unique_id(unique_id, msg_idnr); /* Copy the message table entry of the message. */ snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_messages (mailbox_idnr," "physmessage_id, seen_flag, answered_flag, deleted_flag, " "flagged_flag, recent_flag, draft_flag, unique_id, status) " "SELECT '%llu', " "physmessage_id, seen_flag, answered_flag, deleted_flag, " "flagged_flag, recent_flag, draft_flag, '%s', status " "FROM dbmail_messages WHERE message_idnr = '%llu'", mailbox_to, unique_id, msg_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: error copying message", __FILE__, __func__); return -1; } /* get the id of the inserted record */ *newmsg_idnr = db_insert_result("message_idnr"); /* update quotum */ if (db_add_quotum_used(user_idnr, msgsize) == -1) { trace(TRACE_ERROR, "%s,%s: error setting the new quotum " "used value for user [%llu]", __FILE__, __func__, user_idnr); return -1; } return 1; } int db_getmailboxname(u64_t mailbox_idnr, u64_t user_idnr, char *name) { char *tmp_name, *tmp_fq_name; const char *query_result; int result; size_t tmp_fq_name_len; u64_t owner_idnr; result = db_get_mailbox_owner(mailbox_idnr, &owner_idnr); if (result <= 0) { trace(TRACE_ERROR, "%s,%s: error checking ownership of " "mailbox", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "SELECT name FROM dbmail_mailboxes WHERE mailbox_idnr = '%llu'", mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not retrieve name", __FILE__, __func__); return -1; } if (db_num_rows() < 1) { db_free_result(); *name = '\0'; return 0; } query_result = db_get_result(0, 0); if (!query_result) { /* empty set, mailbox does not exist */ db_free_result(); *name = '\0'; return 0; } tmp_name = dm_strdup(query_result); db_free_result(); tmp_fq_name = mailbox_add_namespace(tmp_name, owner_idnr, user_idnr); if (!tmp_fq_name) { trace(TRACE_ERROR, "%s,%s: error getting fully qualified " "mailbox name", __FILE__, __func__); return -1; } tmp_fq_name_len = strlen(tmp_fq_name); if (tmp_fq_name_len >= IMAP_MAX_MAILBOX_NAMELEN) tmp_fq_name_len = IMAP_MAX_MAILBOX_NAMELEN - 1; strncpy(name, tmp_fq_name, tmp_fq_name_len); name[tmp_fq_name_len] = '\0'; dm_free(tmp_name); dm_free(tmp_fq_name); return 0; } int db_setmailboxname(u64_t mailbox_idnr, const char *name) { char *escaped_name; if (db_escape_string(&escaped_name, name)) { trace(TRACE_ERROR, "%s,%s: error escaping mailbox name.", __FILE__, __func__); return -1; } snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_mailboxes SET name = '%s' " "WHERE mailbox_idnr = '%llu'", escaped_name, mailbox_idnr); dm_free(escaped_name); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not set name", __FILE__, __func__); return -1; } return 0; } int db_expunge(u64_t mailbox_idnr, u64_t user_idnr, u64_t ** msg_idnrs, u64_t * nmsgs) { u64_t i; u64_t mailbox_size; if (db_get_mailbox_size(mailbox_idnr, 1, &mailbox_size) < 0) { trace(TRACE_ERROR, "%s,%s: error getting mailbox size " "for mailbox [%llu]", __FILE__, __func__, mailbox_idnr); return -1; } if (nmsgs && msg_idnrs) { /* first select msg UIDs */ snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr FROM dbmail_messages WHERE " "mailbox_idnr = '%llu' AND deleted_flag='1' " "AND status < '%d' " "ORDER BY message_idnr DESC", mailbox_idnr, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not select messages in mailbox", __FILE__, __func__); return -1; } /* now alloc mem */ *nmsgs = db_num_rows(); *msg_idnrs = (u64_t *) dm_malloc(sizeof(u64_t) * (*nmsgs)); if (!(*msg_idnrs)) { /* out of mem */ *nmsgs = 0; db_free_result(); return -1; } /* save ID's in array */ for (i = 0; i < *nmsgs; i++) { (*msg_idnrs)[i] = db_get_result_u64(i, 0); } db_free_result(); } /* update messages belonging to this mailbox: * mark as expunged (status MESSAGE_STATUS_DELETE) */ snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messages SET status='%d' " "WHERE mailbox_idnr = '%llu' " "AND deleted_flag='1' AND status < '%d'", MESSAGE_STATUS_DELETE, mailbox_idnr, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not update messages in mailbox", __FILE__, __func__); if (msg_idnrs) dm_free(*msg_idnrs); if (nmsgs) *nmsgs = 0; return -1; } if (db_subtract_quotum_used(user_idnr, mailbox_size) < 0) { trace(TRACE_ERROR, "%s,%s: error decreasing used quotum for " "user [%llu]. Database might be inconsistent now", __FILE__, __func__, user_idnr); return -1; } return 0; /* success */ } u64_t db_first_unseen(u64_t mailbox_idnr) { u64_t id; snprintf(query, DEF_QUERYSIZE, "SELECT MIN(message_idnr) FROM dbmail_messages " "WHERE mailbox_idnr = '%llu' " "AND status < '%d' AND seen_flag = '0'", mailbox_idnr, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not select messages", __FILE__, __func__); return (u64_t) (-1); } id = db_get_result_u64(0, 0); db_free_result(); return id; } int db_subscribe(u64_t mailbox_idnr, u64_t user_idnr) { snprintf(query, DEF_QUERYSIZE, "SELECT * FROM dbmail_subscription " "WHERE mailbox_id = '%llu' " "AND user_id = '%llu'", mailbox_idnr, user_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not verify subscription", __FILE__, __func__); return (-1); } if (db_num_rows() > 0) { trace(TRACE_DEBUG, "%s,%s: already subscribed to mailbox " "[%llu]", __FILE__, __func__, mailbox_idnr); db_free_result(); return 0; } db_free_result(); snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_subscription (user_id, mailbox_id) " "VALUES ('%llu', '%llu')", user_idnr, mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not insert subscription", __FILE__, __func__); return -1; } return 0; } int db_unsubscribe(u64_t mailbox_idnr, u64_t user_idnr) { snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_subscription " "WHERE user_id = '%llu' AND mailbox_id = '%llu'", user_idnr, mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not update mailbox", __FILE__, __func__); return (-1); } return 0; } int db_get_msgflag(const char *flag_name, u64_t msg_idnr, u64_t mailbox_idnr) { char the_flag_name[DEF_QUERYSIZE / 2]; /* should be sufficient ;) */ int val; /* determine flag */ if (strcasecmp(flag_name, "seen") == 0) snprintf(the_flag_name, DEF_QUERYSIZE / 2, "seen_flag"); else if (strcasecmp(flag_name, "deleted") == 0) snprintf(the_flag_name, DEF_QUERYSIZE / 2, "deleted_flag"); else if (strcasecmp(flag_name, "answered") == 0) snprintf(the_flag_name, DEF_QUERYSIZE / 2, "answered_flag"); else if (strcasecmp(flag_name, "flagged") == 0) snprintf(the_flag_name, DEF_QUERYSIZE / 2, "flagged_flag"); else if (strcasecmp(flag_name, "recent") == 0) snprintf(the_flag_name, DEF_QUERYSIZE / 2, "recent_flag"); else if (strcasecmp(flag_name, "draft") == 0) snprintf(the_flag_name, DEF_QUERYSIZE / 2, "draft_flag"); else return 0; /* non-existent flag is not set */ snprintf(query, DEF_QUERYSIZE, "SELECT %s FROM dbmail_messages " "WHERE message_idnr = '%llu' AND status < '%d' " "AND mailbox_idnr = '%llu'", the_flag_name, msg_idnr, MESSAGE_STATUS_DELETE, mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not select message", __FILE__, __func__); return (-1); } val = db_get_result_int(0, 0); db_free_result(); return val; } int db_get_msgflag_all(u64_t msg_idnr, u64_t mailbox_idnr, int *flags) { int i; memset(flags, 0, sizeof(int) * IMAP_NFLAGS); snprintf(query, DEF_QUERYSIZE, "SELECT seen_flag, answered_flag, deleted_flag, " "flagged_flag, draft_flag, recent_flag FROM dbmail_messages " "WHERE message_idnr = '%llu' AND status < '%d' " "AND mailbox_idnr = '%llu'", msg_idnr, MESSAGE_STATUS_DELETE, mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not select message", __FILE__, __func__); return (-1); } if (db_num_rows() > 0) { for (i = 0; i < IMAP_NFLAGS; i++) { flags[i] = db_get_result_bool(0, i); } } db_free_result(); return 0; } int db_set_msgflag(u64_t msg_idnr, u64_t mailbox_idnr, int *flags, int action_type) { /* we're lazy.. just call db_set_msgflag_range with range * msg_idnr to msg_idnr! */ if (db_set_msgflag_range(msg_idnr, msg_idnr, mailbox_idnr, flags, action_type) == -1) { trace(TRACE_ERROR, "%s,%s: could not set message flags", __FILE__, __func__); return -1; } return 0; } int db_set_msgflag_range(u64_t msg_idnr_low, u64_t msg_idnr_high, u64_t mailbox_idnr, int *flags, int action_type) { size_t i; size_t placed = 0; size_t left; snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_messages SET "); for (i = 0; i < IMAP_NFLAGS; i++) { left = DEF_QUERYSIZE - strlen(query); switch (action_type) { case IMAPFA_ADD: if (flags[i] > 0) { strncat(query, db_flag_desc[i], left); left = DEF_QUERYSIZE - strlen(query); strncat(query, "=1,", left); placed = 1; } break; case IMAPFA_REMOVE: if (flags[i] > 0) { strncat(query, db_flag_desc[i], left); left = DEF_QUERYSIZE - strlen(query); strncat(query, "=0,", left); placed = 1; } break; case IMAPFA_REPLACE: strncat(query, db_flag_desc[i], left); left = DEF_QUERYSIZE - strlen(query); if (flags[i] == 0) strncat(query, "=0,", left); else strncat(query, "=1,", left); placed = 1; break; } db_free_result(); } if (!placed) return 0; /* nothing to update */ /* last character in string is comma, replace it --> strlen()-1 */ left = DEF_QUERYSIZE - strlen(query); snprintf(&query[strlen(query) - 1], left, " WHERE message_idnr BETWEEN '%llu' AND '%llu' AND " "status < '%d' AND mailbox_idnr = '%llu'", msg_idnr_low, msg_idnr_high, MESSAGE_STATUS_DELETE, mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not set flags", __FILE__, __func__); return (-1); } return 0; } int db_get_msgdate(u64_t mailbox_idnr, u64_t msg_idnr, char *date) { const char *query_result; char *to_char_str; to_char_str = date2char_str("pm.internal_date"); snprintf(query, DEF_QUERYSIZE, "SELECT %s FROM dbmail_physmessage pm, dbmail_messages msg " "WHERE msg.mailbox_idnr = '%llu' " "AND msg.message_idnr = '%llu' " "AND pm.id = msg.physmessage_id", to_char_str, mailbox_idnr, msg_idnr); dm_free(to_char_str); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not get message", __FILE__, __func__); return (-1); } if ((db_num_rows() > 0) && (query_result = db_get_result(0, 0))) { strncpy(date, query_result, IMAP_INTERNALDATE_LEN); date[IMAP_INTERNALDATE_LEN - 1] = '\0'; } else { /* no date ? let's say 1 jan 1970 */ strncpy(date, "1970-01-01 00:00:01", IMAP_INTERNALDATE_LEN); date[IMAP_INTERNALDATE_LEN - 1] = '\0'; } db_free_result(); return 0; } int db_set_rfcsize(u64_t rfcsize, u64_t msg_idnr, u64_t mailbox_idnr) { u64_t physmessage_id = 0; snprintf(query, DEF_QUERYSIZE, "SELECT physmessage_id FROM dbmail_messages " "WHERE message_idnr = '%llu' " "AND mailbox_idnr = '%llu'", msg_idnr, mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not get physmessage_id for " "message [%llu]", __FILE__, __func__, msg_idnr); return -1; } if (db_num_rows() == 0) { trace(TRACE_DEBUG, "%s,%s: no such message [%llu]", __FILE__, __func__, msg_idnr); db_free_result(); return 0; } physmessage_id = db_get_result_u64(0, 0); db_free_result(); snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_physmessage SET rfcsize = '%llu' " "WHERE id = '%llu'", rfcsize, physmessage_id); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not update " "message [%llu]", __FILE__, __func__, msg_idnr); return -1; } return 0; } int db_get_rfcsize(u64_t msg_idnr, u64_t mailbox_idnr, u64_t * rfc_size) { assert(rfc_size != NULL); *rfc_size = 0; snprintf(query, DEF_QUERYSIZE, "SELECT pm.rfcsize FROM dbmail_physmessage pm, dbmail_messages msg " "WHERE pm.id = msg.physmessage_id " "AND msg.message_idnr = '%llu' " "AND msg.status< '%d' " "AND msg.mailbox_idnr = '%llu'", msg_idnr, MESSAGE_STATUS_DELETE, mailbox_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not fetch RFC size from table", __FILE__, __func__); return -1; } if (db_num_rows() < 1) { trace(TRACE_ERROR, "%s,%s: message not found", __FILE__, __func__); db_free_result(); return -1; } *rfc_size = db_get_result_u64(0, 0); db_free_result(); return 1; } int db_get_msginfo_range(u64_t msg_idnr_low, u64_t msg_idnr_high, u64_t mailbox_idnr, int get_flags, int get_internaldate, int get_rfcsize, int get_msg_idnr, msginfo_t ** result, unsigned *resultsetlen) { unsigned nrows, i, j; const char *query_result; char *to_char_str; *result = 0; *resultsetlen = 0; db_free_result(); to_char_str = date2char_str("internal_date"); snprintf(query, DEF_QUERYSIZE, "SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, " "draft_flag, recent_flag, %s, rfcsize, message_idnr " "FROM dbmail_messages msg, dbmail_physmessage pm " "WHERE pm.id = msg.physmessage_id " "AND message_idnr BETWEEN '%llu' AND '%llu' " "AND mailbox_idnr = '%llu' AND status < '%d' " "ORDER BY message_idnr ASC", to_char_str, msg_idnr_low, msg_idnr_high, mailbox_idnr, MESSAGE_STATUS_DELETE); dm_free(to_char_str); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not select message", __FILE__, __func__); return (-1); } if ((nrows = db_num_rows()) == 0) { db_free_result(); return 0; } *result = (msginfo_t *) dm_malloc(nrows * sizeof(msginfo_t)); if (!(*result)) { trace(TRACE_ERROR, "%s,%s: out of memory", __FILE__, __func__); db_free_result(); return -2; } memset(*result, 0, nrows * sizeof(msginfo_t)); for (i = 0; i < nrows; i++) { if (get_flags) { for (j = 0; j < IMAP_NFLAGS; j++) { (*result)[i].flags[j] = db_get_result_bool(i, j); } } if (get_internaldate) { query_result = db_get_result(i, IMAP_NFLAGS); strncpy((*result)[i].internaldate, (query_result) ? query_result : "1970-01-01 00:00:01", IMAP_INTERNALDATE_LEN); } if (get_rfcsize) { (*result)[i].rfcsize = db_get_result_u64(i, IMAP_NFLAGS + 1); } if (get_msg_idnr) { (*result)[i].uid = db_get_result_u64(i, IMAP_NFLAGS + 2); } } db_free_result(); *resultsetlen = nrows; return 0; } int db_get_main_header(u64_t msg_idnr, struct list *hdrlist) { const char *query_result; u64_t dummy = 0, sizedummy = 0; int result; if (!hdrlist) return 0; if (hdrlist->start) list_freelist(&hdrlist->start); list_init(hdrlist); snprintf(query, DEF_QUERYSIZE, "SELECT messageblk " "FROM dbmail_messageblks blk, dbmail_messages msg " "WHERE blk.physmessage_id = msg.physmessage_id " "AND msg.message_idnr = '%llu' " "AND blk.is_header = 1", msg_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not get message header", __FILE__, __func__); return -1; } if (db_num_rows() > 0) { query_result = db_get_result(0, 0); if (!query_result) { trace(TRACE_ERROR, "%s,%s: no header for message found", __FILE__, __func__); db_free_result(); return -1; } } else { trace(TRACE_ERROR, "%s,%s: no message blocks found for message", __FILE__, __func__); db_free_result(); return -1; } result = mime_readheader(query_result, &dummy, hdrlist, &sizedummy); db_free_result(); if (result == -1) { /* parse error */ trace(TRACE_ERROR, "%s,%s: error parsing header of message [%llu]", __FILE__, __func__, msg_idnr); if (hdrlist->start) { list_freelist(&hdrlist->start); list_init(hdrlist); } return -3; } if (result == -2) { /* out of memory */ trace(TRACE_ERROR, "%s,%s: out of memory", __FILE__, __func__); if (hdrlist->start) { list_freelist(&hdrlist->start); list_init(hdrlist); } return -2; } /* success ! */ return 0; } int db_mailbox_msg_match(u64_t mailbox_idnr, u64_t msg_idnr) { int val; snprintf(query, DEF_QUERYSIZE, "SELECT message_idnr FROM dbmail_messages " "WHERE message_idnr = '%llu' " "AND mailbox_idnr = '%llu' " "AND status< '%d'", msg_idnr, mailbox_idnr, MESSAGE_STATUS_DELETE); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not get message", __FILE__, __func__); return (-1); } val = db_num_rows(); db_free_result(); return val; } int db_get_user_aliases(u64_t user_idnr, struct list *aliases) { int i, n; const char *query_result; if (!aliases) { trace(TRACE_ERROR, "%s,%s: got a NULL pointer as argument", __FILE__, __func__); return -2; } list_init(aliases); /* do a inverted (DESC) query because adding the names to the * final list inverts again */ snprintf(query, DEF_QUERYSIZE, "SELECT alias FROM dbmail_aliases WHERE deliver_to = '%llu' " "ORDER BY alias DESC", user_idnr); if (db_query(query) == -1) { trace(TRACE_ERROR, "%s,%s: could not retrieve list", __FILE__, __func__); return -1; } n = db_num_rows(); for (i = 0; i < n; i++) { query_result = db_get_result(i, 0); if (!query_result || !list_nodeadd(aliases, query_result, strlen(query_result) + 1)) { list_freelist(&aliases->start); db_free_result(); return -2; } } db_free_result(); return 0; } int db_acl_has_right(u64_t userid, u64_t mboxid, const char *right_flag) { int result; int owner_result; trace(TRACE_DEBUG, "%s,%s: checking ACL for user [%llu] on " "mailbox [%llu]", __FILE__, __func__, userid, mboxid); owner_result = db_user_is_mailbox_owner(userid, mboxid); if (owner_result < 0) { trace(TRACE_ERROR, "%s,%s: error checking mailbox ownership.", __FILE__, __func__); return -1; } if (owner_result == 1) return 1; /* FIXME: We should generate the flags here as with ACL. */ snprintf(query, DEF_QUERYSIZE, "SELECT * FROM dbmail_acl " "WHERE user_id = '%llu' " "AND mailbox_id = '%llu' " "AND %s = '1'", userid, mboxid, right_flag); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: error finding acl_right", __FILE__, __func__); return -1; } if (db_num_rows() == 0) result = 0; else result = 1; db_free_result(); return result; } static int db_acl_has_acl(u64_t userid, u64_t mboxid) { int result; snprintf(query, DEF_QUERYSIZE, "SELECT user_id, mailbox_id FROM dbmail_acl " "WHERE user_id = '%llu' AND mailbox_id = '%llu'", userid, mboxid); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: Error finding ACL entry", __FILE__, __func__); return -1; } if (db_num_rows() == 0) result = 0; else result = 1; db_free_result(); return result; } static int db_acl_create_acl(u64_t userid, u64_t mboxid) { snprintf(query, DEF_QUERYSIZE, "INSERT INTO dbmail_acl (user_id, mailbox_id) " "VALUES ('%llu', '%llu')", userid, mboxid); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: Error creating ACL entry for user " "[%llu], mailbox [%llu].", __FILE__, __func__, userid, mboxid); return -1; } return 1; } int db_acl_set_right(u64_t userid, u64_t mboxid, const char *right_flag, int set) { int owner_result; int result; assert(set == 0 || set == 1); trace(TRACE_DEBUG, "%s, %s: Setting ACL for user [%llu], mailbox " "[%llu].", __FILE__, __func__, userid, mboxid); owner_result = db_user_is_mailbox_owner(userid, mboxid); if (owner_result < 0) { trace(TRACE_ERROR, "%s,%s: error checking ownership of " "mailbox.", __FILE__, __func__); return -1; } if (owner_result == 1) return 0; // if necessary, create ACL for user, mailbox result = db_acl_has_acl(userid, mboxid); if (result == -1) { trace(TRACE_ERROR, "%s,%s: Error finding acl for user " "[%llu], mailbox [%llu]", __FILE__, __func__, userid, mboxid); return -1; } if (result == 0) { if (db_acl_create_acl(userid, mboxid) == -1) { trace(TRACE_ERROR, "%s,%s: Error creating ACL for " "user [%llu], mailbox [%llu]", __FILE__, __func__, userid, mboxid); return -1; } } /* FIXME: The right_flag should be generated locally. */ snprintf(query, DEF_QUERYSIZE, "UPDATE dbmail_acl SET %s = '%i' " "WHERE user_id = '%llu' AND mailbox_id = '%llu'", right_flag, set, userid, mboxid); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: Error updating ACL for user " "[%llu], mailbox [%llu].", __FILE__, __func__, userid, mboxid); return -1; } trace(TRACE_DEBUG, "%s,%s: Updated ACL for user [%llu], " "mailbox [%llu].", __FILE__, __func__, userid, mboxid); return 1; } int db_acl_delete_acl(u64_t userid, u64_t mboxid) { trace(TRACE_DEBUG, "%s,%s: deleting ACL for user [%llu], " "mailbox [%llu].", __FILE__, __func__, userid, mboxid); snprintf(query, DEF_QUERYSIZE, "DELETE FROM dbmail_acl " "WHERE user_id = '%llu' AND mailbox_id = '%llu'", userid, mboxid); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: error deleting ACL", __FILE__, __func__); return -1; } return 1; } int db_acl_get_identifier(u64_t mboxid, struct list *identifier_list) { unsigned i, n; const char *result_string; assert(identifier_list != NULL); list_init(identifier_list); snprintf(query, DEF_QUERYSIZE, "SELECT dbmail_users.userid FROM dbmail_users, dbmail_acl " "WHERE dbmail_acl.mailbox_id = '%llu' " "AND dbmail_users.user_idnr = dbmail_acl.user_id", mboxid); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: error getting acl identifiers " "for mailbox [%llu].", __FILE__, __func__, mboxid); return -1; } n = db_num_rows(); for (i = 0; i < n; i++) { result_string = db_get_result(i, 0); trace(TRACE_DEBUG, "%s,%s: adding %s to identifier list", __FILE__, __func__, result_string); if (!result_string || !list_nodeadd(identifier_list, result_string, strlen(result_string) + 1)) { db_free_result(); return -2; } } db_free_result(); return 1; } int db_get_mailbox_owner(u64_t mboxid, u64_t * owner_id) { assert(owner_id != NULL); snprintf(query, DEF_QUERYSIZE, "SELECT owner_idnr FROM dbmail_mailboxes " "WHERE mailbox_idnr = '%llu'", mboxid); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: error finding owner of mailbox " "[%llu]", __FILE__, __func__, mboxid); return -1; } *owner_id = db_get_result_u64(0, 0); db_free_result(); if (*owner_id == 0) return 0; else return 1; } int db_user_is_mailbox_owner(u64_t userid, u64_t mboxid) { int result; snprintf(query, DEF_QUERYSIZE, "SELECT mailbox_idnr FROM dbmail_mailboxes " "WHERE mailbox_idnr = '%llu' " "AND owner_idnr = '%llu'", mboxid, userid); if (db_query(query) < 0) { trace(TRACE_ERROR, "%s,%s: error checking if user [%llu] is " "owner of mailbox [%llu]", __FILE__, __func__, userid, mboxid); return -1; } if (db_num_rows() == 0) result = 0; else result = 1; db_free_result(); return result; } /* db_get_result_* Utility Function Annex. * These are variants of the db_get_result function that * appears in the low level database driver. Each of these * encapsulates some value checking and type conversion. */ int db_get_result_int(unsigned row, unsigned field) { const char *tmp; tmp = db_get_result(row, field); return (tmp ? atoi(tmp) : 0); } int db_get_result_bool(unsigned row, unsigned field) { const char *tmp; tmp = db_get_result(row, field); return (tmp ? (atoi(tmp) ? 1 : 0) : 0); } u64_t db_get_result_u64(unsigned row, unsigned field) { const char *tmp; tmp = db_get_result(row, field); return (tmp ? strtoull(tmp, NULL, 10) : 0); } char *date2char_str(const char *column) { unsigned len; char *s; len = strlen(TO_CHAR) + MAX_COLUMN_LEN; s = (char *) dm_malloc(len); if (!s) return NULL; snprintf(s, len, TO_CHAR, column); return s; } char *char2date_str(const char *date) { unsigned len; char *s; len = strlen(TO_CHAR) + MAX_DATE_LEN; s = (char *) dm_malloc(len); if (!s) return NULL; snprintf(s, len, TO_DATE, date); return s; } int user_idnr_is_delivery_user_idnr(u64_t user_idnr) { static int delivery_user_idnr_looked_up = 0; static u64_t delivery_user_idnr; if (delivery_user_idnr_looked_up == 0) { trace(TRACE_DEBUG, "%s.%s: looking up user_idnr for %s", __FILE__, __func__, DBMAIL_DELIVERY_USERNAME); if (auth_user_exists(DBMAIL_DELIVERY_USERNAME, &delivery_user_idnr) < 0) { trace(TRACE_ERROR, "%s,%s: error looking up " "user_idnr for DBMAIL_DELIVERY_USERNAME", __FILE__, __func__); return -1; } delivery_user_idnr_looked_up = 1; } else trace(TRACE_DEBUG, "%s.%s: no need to look up user_idnr " "for %s", __FILE__, __func__, DBMAIL_DELIVERY_USERNAME); if (delivery_user_idnr == user_idnr) return 1; else return 0; } void convert_inbox_to_uppercase(char *name) { const char *inbox = "INBOX"; if (strncasecmp(name, "INBOX", strlen("INBOX")) != 0) return; if (strlen(name) == strlen("INBOX") || strncasecmp(name, "INBOX/", strlen("INBOX/")) == 0) memcpy((void *) name, (void *) inbox, strlen(inbox)); return; }