static GList *user_get_deliver_to(const char *username) { INIT_QUERY; C c; R r; S s; GList *d = NULL; snprintf(query, DEF_QUERYSIZE-1, "SELECT deliver_to FROM %saliases " "WHERE lower(alias) = lower(?) " "AND lower(alias) <> lower(deliver_to)", DBPFX); c = db_con_get(); TRY s = db_stmt_prepare(c, query); db_stmt_set_str(s, 1, username); r = db_stmt_query(s); while (db_result_next(r)) d = g_list_prepend(d, g_strdup(db_result_get(r,0))); CATCH(SQLException) LOG_SQLERROR; FINALLY db_con_close(c); END_TRY; return d; }
static void db_getmailbox_keywords(T M, Connection_T c) { ResultSet_T r; PreparedStatement_T stmt; stmt = db_stmt_prepare(c, "SELECT DISTINCT(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); db_stmt_set_u64(stmt, 1, M->id); r = db_stmt_query(stmt); while (db_result_next(r)) MailboxState_addKeyword(M, db_result_get(r, 0)); }
GList * auth_get_known_aliases(void) { GList * aliases = NULL; C c; R r; c = db_con_get(); TRY r = db_query(c,"SELECT alias FROM %saliases ORDER BY alias",DBPFX); while (db_result_next(r)) aliases = g_list_append(aliases, g_strdup(db_result_get(r,0))); CATCH(SQLException) LOG_SQLERROR; FINALLY db_con_close(c); END_TRY; return aliases; }
GList * auth_get_known_users(void) { GList * users = NULL; C c; R r; c = db_con_get(); TRY r = db_query(c, "SELECT userid FROM %susers ORDER BY userid",DBPFX); while (db_result_next(r)) users = g_list_append(users, g_strdup(db_result_get(r, 0))); CATCH(SQLException) LOG_SQLERROR; FINALLY db_con_close(c); END_TRY; return users; }
char *auth_getencryption(uint64_t user_idnr) { char *res = NULL; C c; R r; assert(user_idnr > 0); c = db_con_get(); TRY r = db_query(c, "SELECT encryption_type FROM %susers WHERE user_idnr = %" PRIu64 "",DBPFX, user_idnr); if (db_result_next(r)) res = g_strdup(db_result_get(r,0)); CATCH(SQLException) LOG_SQLERROR; FINALLY db_con_close(c); END_TRY; return res; }
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 dm_sievescript_get(uint64_t user_idnr, char **scriptname) { Connection_T c; ResultSet_T r; volatile int t = FALSE; assert(scriptname); *scriptname = NULL; c = db_con_get(); TRY r = db_query(c, "SELECT name from %ssievescripts where owner_idnr = %" PRIu64 " and active = 1", DBPFX, user_idnr); if (db_result_next(r)) *scriptname = g_strdup(db_result_get(r,0)); CATCH(SQLException) LOG_SQLERROR; t = DM_EQUERY; FINALLY db_con_close(c); END_TRY; return t; }
int dm_sievescript_list(uint64_t user_idnr, GList **scriptlist) { Connection_T c; ResultSet_T r; volatile int t = FALSE; c = db_con_get(); TRY r = db_query(c,"SELECT name,active FROM %ssievescripts WHERE owner_idnr = %" PRIu64 "", DBPFX,user_idnr); while (db_result_next(r)) { sievescript_info *info = g_new0(sievescript_info,1); strncpy(info->name, db_result_get(r,0), sizeof(info->name)-1); info->active = db_result_get_int(r,1); *(GList **)scriptlist = g_list_prepend(*(GList **)scriptlist, info); } CATCH(SQLException) LOG_SQLERROR; t = DM_EQUERY; FINALLY db_con_close(c); END_TRY; return t; }
int dm_sievescript_getbyname(uint64_t user_idnr, char *scriptname, char **script) { Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = FALSE; assert(scriptname); c = db_con_get(); TRY s = db_stmt_prepare(c, "SELECT script FROM %ssievescripts WHERE owner_idnr = ? AND name = ?", DBPFX); db_stmt_set_u64(s, 1, user_idnr); db_stmt_set_str(s, 2, scriptname); r = db_stmt_query(s); if (db_result_next(r)) *script = g_strdup(db_result_get(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; }