int dm_sievescript_rename(uint64_t user_idnr, char *scriptname, char *newname) { int active = 0; Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = FALSE; assert(scriptname); /* * According to the draft RFC, a script with the same * name as an existing script should *atomically* replace it. */ c = db_con_get(); TRY db_begin_transaction(c); s = db_stmt_prepare(c,"SELECT active FROM %ssievescripts WHERE owner_idnr = ? AND name = ?", DBPFX); db_stmt_set_u64(s,1, user_idnr); db_stmt_set_str(s,2, newname); r = db_stmt_query(s); if (db_result_next(r)) { active = db_result_get_int(r,0); db_con_clear(c); s = db_stmt_prepare(c, "DELETE FROM %ssievescripts WHERE owner_idnr = ? AND name = ?", DBPFX); db_stmt_set_u64(s, 1, user_idnr); db_stmt_set_str(s, 2, newname); db_stmt_exec(s); } db_con_clear(c); s = db_stmt_prepare(c, "UPDATE %ssievescripts SET name = ?, active = ? WHERE owner_idnr = ? AND name = ?", DBPFX); db_stmt_set_str(s, 1, newname); db_stmt_set_int(s, 2, active); db_stmt_set_u64(s, 3, user_idnr); db_stmt_set_str(s, 4, scriptname); db_stmt_exec(s); t = db_commit_transaction(c); CATCH(SQLException) LOG_SQLERROR; t = DM_EQUERY; db_rollback_transaction(c); FINALLY db_con_close(c); END_TRY; return t; }
int dm_sievescript_activate(uint64_t user_idnr, char *scriptname) { Connection_T c; PreparedStatement_T s; volatile gboolean t = FALSE; assert(scriptname); c = db_con_get(); TRY db_begin_transaction(c); s = db_stmt_prepare(c,"UPDATE %ssievescripts SET active = 0 WHERE owner_idnr = ? ", DBPFX); db_stmt_set_u64(s, 1, user_idnr); db_stmt_exec(s); db_con_clear(c); s = db_stmt_prepare(c,"UPDATE %ssievescripts SET active = 1 WHERE owner_idnr = ? AND name = ?", DBPFX); db_stmt_set_u64(s, 1, user_idnr); db_stmt_set_str(s, 2, scriptname); db_stmt_exec(s); db_commit_transaction(c); t = TRUE; CATCH(SQLException) LOG_SQLERROR; db_rollback_transaction(c); FINALLY db_con_close(c); END_TRY; return t; }
int dm_sievescript_add(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 db_begin_transaction(c); s = db_stmt_prepare(c,"SELECT COUNT(*) 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)) { db_con_clear(c); s = db_stmt_prepare(c,"DELETE FROM %ssievescripts WHERE owner_idnr = ? AND name = ?", DBPFX); db_stmt_set_u64(s, 1, user_idnr); db_stmt_set_str(s, 2, scriptname); db_stmt_exec(s); } db_con_clear(c); s = db_stmt_prepare(c,"INSERT INTO %ssievescripts (owner_idnr, name, script, active) VALUES (?,?,?,1)", DBPFX); db_stmt_set_u64(s, 1, user_idnr); db_stmt_set_str(s, 2, scriptname); db_stmt_set_blob(s, 3, script, strlen(script)); db_stmt_exec(s); t = db_commit_transaction(c); CATCH(SQLException) LOG_SQLERROR; db_rollback_transaction(c); t = DM_EQUERY; FINALLY db_con_close(c); END_TRY; return t; }
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; }
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); }