/* Move message to Trash if the message is in INBOX mailbox and date less then passed date. */
int do_move_old (int days, char * mbinbox_name, char * mbtrash_name)
{
Connection_T c; ResultSet_T r; ResultSet_T r1; PreparedStatement_T s; PreparedStatement_T s1;
int skip = 1;
char expire [DEF_FRAGSIZE];
uint64_t mailbox_to;
uint64_t mailbox_from;
memset(expire,0,sizeof(expire));
snprintf(expire, DEF_FRAGSIZE-1, db_get_sql(SQL_EXPIRE), days);
c = db_con_get();
s = db_stmt_prepare(c,"SELECT msg.message_idnr, mb.owner_idnr, mb.mailbox_idnr FROM %smessages msg "
"JOIN %sphysmessage phys ON msg.physmessage_id = phys.id "
"JOIN %smailboxes mb ON msg.mailbox_idnr = mb.mailbox_idnr "
"WHERE mb.name = ? AND msg.status < %d "
"AND phys.internal_date < %s",
DBPFX, DBPFX, DBPFX, MESSAGE_STATUS_DELETE, expire);
s1 = db_stmt_prepare(c, "SELECT mailbox_idnr FROM %smailboxes WHERE owner_idnr = ? AND name = ?", DBPFX);
db_stmt_set_str(s, 1, mbinbox_name);
TRY
r = db_stmt_query(s);
while (db_result_next(r))
{
skip = 1;
uint64_t id = db_result_get_u64(r, 0);
uint64_t user_id = db_result_get_u64(r, 1);
mailbox_from = db_result_get_u64(r, 2);
db_stmt_set_u64(s1,1,user_id);
db_stmt_set_str(s1,2,mbtrash_name);
r1 = db_stmt_query(s1);
if (db_result_next(r1)) {
mailbox_to = db_result_get_u64(r1, 0);
skip = 0;
}
if (!skip) {
db_move_message(id, mailbox_to);
db_mailbox_seq_update(mailbox_to, 0);
db_mailbox_seq_update(mailbox_from, 0);
}
else {
qprintf("User(%" PRIu64 ") doesn't has mailbox(%s)\n", user_id, mbtrash_name);
}
}
CATCH(SQLException)
LOG_SQLERROR;
return -1;
FINALLY
db_con_close(c);
END_TRY;
return 0;
}
/* Delete message from mailbox if it is Trash and the message date less then passed date */
int do_erase_old(int days, char * mbtrash_name)
{
Connection_T c; PreparedStatement_T s; ResultSet_T r;
char expire [DEF_FRAGSIZE];
memset(expire,0,sizeof(expire));
snprintf(expire, DEF_FRAGSIZE-1, db_get_sql(SQL_EXPIRE), days);
c = db_con_get();
s = db_stmt_prepare(c,"SELECT msg.message_idnr FROM %smessages msg "
"JOIN %sphysmessage phys ON msg.physmessage_id = phys.id "
"JOIN %smailboxes mb ON msg.mailbox_idnr = mb.mailbox_idnr "
"WHERE mb.name = ? AND msg.status < %d "
"AND phys.internal_date < %s ",
DBPFX, DBPFX, DBPFX, MESSAGE_STATUS_DELETE, expire);
db_stmt_set_str(s, 1, mbtrash_name);
TRY
r = db_stmt_query(s);
while(db_result_next(r))
{
uint64_t id = db_result_get_u64(r, 0);
qprintf("Deleting message id(%" PRIu64 ")\n", id);
db_set_message_status(id,MESSAGE_STATUS_PURGE);
}
CATCH(SQLException)
LOG_SQLERROR;
return -1;
FINALLY
db_con_close(c);
END_TRY;
return 0;
}
static void db_getmailbox_count(T M, Connection_T c)
{
ResultSet_T r;
PreparedStatement_T stmt;
unsigned result[3];
result[0] = result[1] = result[2] = 0;
g_return_if_fail(M->id);
/* count messages */
stmt = db_stmt_prepare(c,
"SELECT "
"SUM( CASE WHEN seen_flag = 0 THEN 1 ELSE 0 END) AS unseen, "
"SUM( CASE WHEN seen_flag = 1 THEN 1 ELSE 0 END) AS seen, "
"SUM( CASE WHEN recent_flag = 1 THEN 1 ELSE 0 END) AS recent "
"FROM %smessages WHERE mailbox_idnr=? AND status IN (%d,%d)",
DBPFX, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN);
db_stmt_set_u64(stmt, 1, M->id);
r = db_stmt_query(stmt);
if (db_result_next(r)) {
result[0] = (unsigned)db_result_get_int(r,0); // unseen
result[1] = (unsigned)db_result_get_int(r,1); // seen
result[2] = (unsigned)db_result_get_int(r,2); // recent
}
M->exists = result[0] + result[1];
M->unseen = result[0];
M->recent = result[2];
TRACE(TRACE_DEBUG, "exists [%d] unseen [%d] recent [%d]", M->exists, M->unseen, M->recent);
/* now determine the next message UID
* NOTE:
* - expunged messages are selected as well in order to be able to restore them
* - the next uit MUST NOT change unless messages are added to THIS mailbox
* */
if (M->exists == 0) {
M->uidnext = 1;
return;
}
db_con_clear(c);
stmt = db_stmt_prepare(c,
"SELECT MAX(message_idnr)+1 FROM %smessages WHERE mailbox_idnr=?",
DBPFX);
db_stmt_set_u64(stmt, 1, M->id);
r = db_stmt_query(stmt);
if (db_result_next(r))
M->uidnext = db_result_get_u64(r,0);
else
M->uidnext = 1;
}
int do_migrate(int migrate_limit)
{
C c; R r;
int id = 0;
int count = 0;
DbmailMessage *m;
qprintf ("Mirgrate legacy 2.2.x messageblks to mimeparts...\n");
if (!yes_to_all) {
qprintf ("\tmigration skipped. Use -y option to perform mirgration.\n");
return 0;
}
qprintf ("Preparing to migrate %d physmessages.\n", migrate_limit);
c = db_con_get();
TRY
r = db_query(c, "SELECT DISTINCT(physmessage_id) FROM %smessageblks LIMIT %d", DBPFX, migrate_limit);
qprintf ("Migrating %d physmessages...\n", migrate_limit);
while (db_result_next(r))
{
count++;
id = db_result_get_u64(r,0);
m = dbmail_message_new();
m = dbmail_message_retrieve(m, id, DBMAIL_MESSAGE_FILTER_FULL);
if(!dm_message_store(m))
{
if(verbose) qprintf ("%d ",id);
db_update("DELETE FROM %smessageblks WHERE physmessage_id = %d", DBPFX, id);
}
else
{
if(!verbose) qprintf ("migrating physmessage_id: %d\n",id);
qprintf ("failed\n");
return -1;
}
dbmail_message_free(m);
}
CATCH(SQLException)
LOG_SQLERROR;
return -1;
FINALLY
db_con_close(c);
END_TRY;
qprintf ("Migration complete. Migrated %d physmessages.\n", count);
return 0;
}
int do_migrate(int migrate_limit)
{
Connection_T c; ResultSet_T r;
int id = 0;
volatile int count = 0;
DbmailMessage *m;
qprintf ("Migrate legacy 2.2.x messageblks to mimeparts...\n");
if (!yes_to_all) {
qprintf ("\tmigration skipped. Use -y option to perform migration.\n");
return 0;
}
qprintf ("Preparing to migrate up to %d physmessages.\n", migrate_limit);
c = db_con_get();
TRY
db_begin_transaction(c);
r = db_query(c, "SELECT DISTINCT(physmessage_id) FROM %smessageblks LIMIT %d", DBPFX, migrate_limit);
while (db_result_next(r))
{
count++;
id = db_result_get_u64(r,0);
m = dbmail_message_new(NULL);
m = dbmail_message_retrieve(m, id);
if(! dm_message_store(m)) {
if(verbose) qprintf ("%d ",id);
db_update("DELETE FROM %smessageblks WHERE physmessage_id = %d", DBPFX, id);
}
dbmail_message_free(m);
}
db_commit_transaction(c);
CATCH(SQLException)
LOG_SQLERROR;
db_rollback_transaction(c);
return -1;
FINALLY
db_con_close(c);
END_TRY;
qprintf ("Migration complete. Migrated %d physmessages.\n", count);
return 0;
}
void db_getmailbox_seq(T M, Connection_T c)
{
ResultSet_T r;
PreparedStatement_T stmt;
stmt = db_stmt_prepare(c,
"SELECT name,seq FROM %smailboxes WHERE mailbox_idnr=?",
DBPFX);
db_stmt_set_u64(stmt, 1, M->id);
r = db_stmt_query(stmt);
if (db_result_next(r)) {
if (! M->name)
M->name = p_string_new(M->pool, db_result_get(r, 0));
M->seq = db_result_get_u64(r,1);
TRACE(TRACE_DEBUG,"id: [%" PRIu64 "] name: [%s] seq [%" PRIu64 "]", M->id, p_string_str(M->name), M->seq);
} else {
TRACE(TRACE_ERR,"Aii. No such mailbox mailbox_idnr: [%" PRIu64 "]", M->id);
}
}
int auth_getclientid(uint64_t user_idnr, uint64_t * client_idnr)
{
assert(client_idnr != NULL);
*client_idnr = 0;
C c; R r; int t = TRUE;
c = db_con_get();
TRY
r = db_query(c, "SELECT client_idnr FROM %susers WHERE user_idnr = %" PRIu64 "",DBPFX, user_idnr);
if (db_result_next(r))
*client_idnr = db_result_get_u64(r,0);
CATCH(SQLException)
LOG_SQLERROR;
t = DM_EQUERY;
FINALLY
db_con_close(c);
END_TRY;
return t;
}
int auth_getmaxmailsize(uint64_t user_idnr, uint64_t * maxmail_size)
{
assert(maxmail_size != NULL);
*maxmail_size = 0;
C c; R r; int t = TRUE;
c = db_con_get();
TRY
r = db_query(c, "SELECT maxmail_size FROM %susers WHERE user_idnr = %" PRIu64 "",DBPFX, user_idnr);
if (db_result_next(r))
*maxmail_size = db_result_get_u64(r,0);
CATCH(SQLException)
LOG_SQLERROR;
t = DM_EQUERY;
FINALLY
db_con_close(c);
END_TRY;
return t;
}
static int db_count_replycache(timestring_t lasttokeep, u64_t *rows)
{
C c; R r; volatile int t = FALSE;
field_t to_date_str;
assert(rows != NULL);
*rows = 0;
char2date_str(lasttokeep, &to_date_str);
c = db_con_get();
TRY
r = db_query(c, "SELECT COUNT(*) FROM %sreplycache WHERE lastseen < %s", DBPFX, to_date_str);
if (db_result_next(r))
*rows = db_result_get_u64(r,0);
CATCH(SQLException)
LOG_SQLERROR;
t = DM_EQUERY;
FINALLY
db_con_close(c);
END_TRY;
return t;
}
static int db_count_iplog(TimeString_T lasttokeep, uint64_t *rows)
{
Connection_T c; ResultSet_T r; volatile int t = DM_SUCCESS;
Field_T to_date_str;
assert(rows != NULL);
*rows = 0;
char2date_str(lasttokeep, &to_date_str);
c = db_con_get();
TRY
r = db_query(c, "SELECT COUNT(*) FROM %spbsp WHERE since < %s", DBPFX, to_date_str);
if (db_result_next(r))
*rows = db_result_get_u64(r,0);
CATCH(SQLException)
LOG_SQLERROR;
t = DM_EQUERY;
FINALLY
db_con_close(c);
END_TRY;
return t;
}
static T state_load_messages(T M, Connection_T c)
{
unsigned nrows = 0, i = 0, j;
const char *query_result, *keyword;
MessageInfo *result;
GTree *msginfo;
uint64_t *uid, id = 0;
ResultSet_T r;
PreparedStatement_T stmt;
Field_T frag;
INIT_QUERY;
date2char_str("internal_date", &frag);
snprintf(query, DEF_QUERYSIZE-1,
"SELECT seen_flag, answered_flag, deleted_flag, flagged_flag, "
"draft_flag, recent_flag, %s, rfcsize, seq, message_idnr, status FROM %smessages m "
"LEFT JOIN %sphysmessage p ON p.id = m.physmessage_id "
"WHERE m.mailbox_idnr = ? AND m.status IN (%d,%d,%d) ORDER BY message_idnr ASC",
frag, DBPFX, DBPFX, MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN, MESSAGE_STATUS_DELETE);
msginfo = g_tree_new_full((GCompareDataFunc)ucmpdata, NULL,(GDestroyNotify)g_free,(GDestroyNotify)MessageInfo_free);
stmt = db_stmt_prepare(c, query);
db_stmt_set_u64(stmt, 1, M->id);
r = db_stmt_query(stmt);
i = 0;
while (db_result_next(r)) {
i++;
id = db_result_get_u64(r, IMAP_NFLAGS + 3);
uid = g_new0(uint64_t,1); *uid = id;
result = g_new0(MessageInfo,1);
/* id */
result->uid = id;
/* mailbox_id */
result->mailbox_id = M->id;
/* flags */
for (j = 0; j < IMAP_NFLAGS; j++)
result->flags[j] = db_result_get_bool(r,j);
/* internal date */
query_result = db_result_get(r,IMAP_NFLAGS);
strncpy(result->internaldate,
(query_result) ? query_result :
"01-Jan-1970 00:00:01 +0100",
IMAP_INTERNALDATE_LEN-1);
/* rfcsize */
result->rfcsize = db_result_get_u64(r,IMAP_NFLAGS + 1);
/* modseq */
result->seq = db_result_get_u64(r,IMAP_NFLAGS + 2);
/* status */
result->status = db_result_get_int(r, IMAP_NFLAGS + 4);
g_tree_insert(msginfo, uid, result);
}
if (! i) { // empty mailbox
MailboxState_setMsginfo(M, msginfo);
return M;
}
db_con_clear(c);
memset(query,0,sizeof(query));
snprintf(query, DEF_QUERYSIZE-1,
"SELECT k.message_idnr, keyword FROM %skeywords k "
"LEFT JOIN %smessages m ON k.message_idnr=m.message_idnr "
"LEFT JOIN %smailboxes b ON m.mailbox_idnr=b.mailbox_idnr "
"WHERE b.mailbox_idnr = ? AND m.status IN (%d,%d)",
DBPFX, DBPFX, DBPFX,
MESSAGE_STATUS_NEW, MESSAGE_STATUS_SEEN);
nrows = 0;
stmt = db_stmt_prepare(c, query);
db_stmt_set_u64(stmt, 1, M->id);
r = db_stmt_query(stmt);
while (db_result_next(r)) {
nrows++;
id = db_result_get_u64(r,0);
keyword = db_result_get(r,1);
if ((result = g_tree_lookup(msginfo, &id)) != NULL)
result->keywords = g_list_append(result->keywords, g_strdup(keyword));
}
if (! nrows) TRACE(TRACE_DEBUG, "no keywords");
MailboxState_setMsginfo(M, msginfo);
return M;
}
static void db_getmailbox_info(T M, Connection_T c)
{
/* query mailbox for LIST results */
ResultSet_T r;
char *mbxname, *name, *pattern;
struct mailbox_match *mailbox_like = NULL;
GString *fqname, *qs;
int i=0, prml;
PreparedStatement_T stmt;
stmt = db_stmt_prepare(c,
"SELECT "
"CASE WHEN user_id IS NULL THEN 0 ELSE 1 END, " // subscription
"owner_idnr, name, no_select, no_inferiors "
"FROM %smailboxes b LEFT OUTER JOIN %ssubscription s ON "
"b.mailbox_idnr = s.mailbox_id WHERE b.mailbox_idnr = ?",
DBPFX, DBPFX);
db_stmt_set_u64(stmt, 1, M->id);
r = db_stmt_query(stmt);
if (db_result_next(r)) {
/* subsciption */
M->is_subscribed = db_result_get_bool(r, i++);
/* owner_idnr */
M->owner_id = db_result_get_u64(r, i++);
/* name */
name = g_strdup(db_result_get(r,i++));
if (MATCH(name, "INBOX")) {
M->is_inbox = TRUE;
M->is_subscribed = TRUE;
}
mbxname = mailbox_add_namespace(name, M->owner_id, M->owner_id);
fqname = g_string_new(mbxname);
fqname = g_string_truncate(fqname,IMAP_MAX_MAILBOX_NAMELEN);
MailboxState_setName(M, fqname->str);
g_string_free(fqname,TRUE);
g_free(mbxname);
/* no_select */
M->no_select=db_result_get_bool(r,i++);
/* no_inferior */
M->no_inferiors=db_result_get_bool(r,i++);
/* no_children search pattern*/
pattern = g_strdup_printf("%s/%%", name);
mailbox_like = mailbox_match_new(pattern);
g_free(pattern);
g_free(name);
}
db_con_clear(c);
qs = g_string_new("");
g_string_printf(qs, "SELECT COUNT(*) AS nr_children FROM %smailboxes WHERE owner_idnr = ? ", DBPFX);
if (mailbox_like && mailbox_like->insensitive)
g_string_append_printf(qs, "AND name %s ? ", db_get_sql(SQL_INSENSITIVE_LIKE));
if (mailbox_like && mailbox_like->sensitive)
g_string_append_printf(qs, "AND name %s ? ", db_get_sql(SQL_SENSITIVE_LIKE));
stmt = db_stmt_prepare(c, qs->str);
prml = 1;
db_stmt_set_u64(stmt, prml++, M->owner_id);
if (mailbox_like && mailbox_like->insensitive)
db_stmt_set_str(stmt, prml++, mailbox_like->insensitive);
if (mailbox_like && mailbox_like->sensitive)
db_stmt_set_str(stmt, prml++, mailbox_like->sensitive);
r = db_stmt_query(stmt);
if (db_result_next(r)) {
int nr_children = db_result_get_int(r,0);
M->no_children=nr_children ? 0 : 1;
} else {
M->no_children=1;
}
mailbox_match_free(mailbox_like);
g_string_free(qs, TRUE);
}
static int db_createsession(uint64_t user_idnr, ClientSession_T * session)
{
Connection_T c;
ResultSet_T r;
volatile int t = DM_SUCCESS;
struct message *tmpmessage;
int message_counter = 0;
const char *query_result;
uint64_t mailbox_idnr;
INIT_QUERY;
if (db_find_create_mailbox("INBOX", BOX_DEFAULT, user_idnr, &mailbox_idnr) < 0) {
TRACE(TRACE_NOTICE, "find_create INBOX for user [%" PRIu64 "] failed, exiting..", user_idnr);
return DM_EQUERY;
}
g_return_val_if_fail(mailbox_idnr > 0, DM_EQUERY);
/* query is < MESSAGE_STATUS_DELETE because we don't want deleted
* messages
*/
snprintf(query, DEF_QUERYSIZE-1,
"SELECT pm.messagesize, msg.message_idnr, msg.status, "
"msg.unique_id FROM %smessages msg, %sphysmessage pm "
"WHERE msg.mailbox_idnr = %" PRIu64 " "
"AND msg.status < %d "
"AND msg.physmessage_id = pm.id "
"ORDER BY msg.message_idnr ASC",DBPFX,DBPFX,
mailbox_idnr, MESSAGE_STATUS_DELETE);
c = db_con_get();
TRY
r = db_query(c, query);
session->totalmessages = 0;
session->totalsize = 0;
/* messagecounter is total message, +1 tot end at message 1 */
message_counter = 1;
/* filling the list */
TRACE(TRACE_DEBUG, "adding items to list");
while (db_result_next(r)) {
tmpmessage = mempool_pop(session->pool, sizeof(struct message));
/* message size */
tmpmessage->msize = db_result_get_u64(r,0);
/* real message id */
tmpmessage->realmessageid = db_result_get_u64(r,1);
/* message status */
tmpmessage->messagestatus = db_result_get_u64(r,2);
/* virtual message status */
tmpmessage->virtual_messagestatus = tmpmessage->messagestatus;
/* unique id */
query_result = db_result_get(r,3);
if (query_result)
strncpy(tmpmessage->uidl, query_result, UID_SIZE-1);
session->totalmessages++;
session->totalsize += tmpmessage->msize;
tmpmessage->messageid = (uint64_t) message_counter;
session->messagelst = p_list_append(session->messagelst, tmpmessage);
message_counter++;
}
CATCH(SQLException)
LOG_SQLERROR;
t = DM_EQUERY;
FINALLY
db_con_close(c);
END_TRY;
if (t == DM_EQUERY) return t;
if (message_counter == 1) {
/* there are no messages for this user */
return DM_EGENERAL;
}
TRACE(TRACE_DEBUG, "adding succesful");
/* setting all virtual values */
session->virtual_totalmessages = session->totalmessages;
session->virtual_totalsize = session->totalsize;
return DM_EGENERAL;
}
