Example #1
0
/* 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;

}
Example #2
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;
}
Example #3
0
int MailboxState_getAcl(T M, uint64_t userid, struct ACLMap *map)
{
	int i;
	volatile int t = DM_SUCCESS;
	gboolean gotrow = FALSE;
	uint64_t anyone;
	Connection_T c; ResultSet_T r; PreparedStatement_T s;

	g_return_val_if_fail(MailboxState_getId(M),DM_EGENERAL); 

	if (! (auth_user_exists(DBMAIL_ACL_ANYONE_USER, &anyone)))
		return DM_EQUERY;

	c = db_con_get();
	TRY
		s = db_stmt_prepare(c, "SELECT lookup_flag,read_flag,seen_flag,"
			"write_flag,insert_flag,post_flag,"
			"create_flag,delete_flag,deleted_flag,expunge_flag,administer_flag "
			"FROM %sacl "
			"WHERE mailbox_id = ? AND user_id = ?",DBPFX);
		db_stmt_set_u64(s, 1, MailboxState_getId(M));
		db_stmt_set_u64(s, 2, userid);
		r = db_stmt_query(s);
		if (! db_result_next(r)) {
			/* else check the 'anyone' user */
			db_stmt_set_u64(s, 2, anyone);
			r = db_stmt_query(s);
			if (db_result_next(r))
				gotrow = TRUE;
		} else {
			gotrow = TRUE;
		}

		if (gotrow) {
			i = 0;
			map->lookup_flag	= db_result_get_bool(r,i++);
			map->read_flag		= db_result_get_bool(r,i++);
			map->seen_flag		= db_result_get_bool(r,i++);
			map->write_flag		= db_result_get_bool(r,i++);
			map->insert_flag	= db_result_get_bool(r,i++);
			map->post_flag		= db_result_get_bool(r,i++);
			map->create_flag	= db_result_get_bool(r,i++);
			map->delete_flag	= db_result_get_bool(r,i++);
			map->deleted_flag	= db_result_get_bool(r,i++);
			map->expunge_flag	= db_result_get_bool(r,i++);
			map->administer_flag	= db_result_get_bool(r,i++);
		}
	CATCH(SQLException)
		LOG_SQLERROR;
		t = DM_EQUERY;
	FINALLY
		db_con_close(c);
	END_TRY;

	return t;
}
Example #4
0
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;
}
Example #5
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;
}
Example #6
0
int dm_sievescript_isactive_byname(uint64_t user_idnr, const char *scriptname)
{
	Connection_T c; ResultSet_T r; PreparedStatement_T s; volatile int t = TRUE;

	c = db_con_get();
	TRY
		if (scriptname) {
			s = db_stmt_prepare(c, "SELECT name FROM %ssievescripts WHERE owner_idnr = ? AND active = 1 AND name = ?", DBPFX);
			db_stmt_set_u64(s, 1, user_idnr);
			db_stmt_set_str(s, 2, scriptname);
		} else {
			s = db_stmt_prepare(c, "SELECT name FROM %ssievescripts WHERE owner_idnr = ? AND active = 1", DBPFX);
			db_stmt_set_u64(s, 1, user_idnr);
		}

		r = db_stmt_query(s);
		if (! db_result_next(r)) t = FALSE;

	CATCH(SQLException)
		LOG_SQLERROR;
		t = DM_EQUERY;
	FINALLY
		db_con_close(c);
	END_TRY;

	return t;
}
Example #7
0
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;
}
Example #8
0
void db_getmailbox_permission(T M, Connection_T c)
{
	ResultSet_T r;
	PreparedStatement_T stmt;
	g_return_if_fail(M->id);

	stmt = db_stmt_prepare(c,
			"SELECT permission FROM %smailboxes WHERE mailbox_idnr = ?",
			DBPFX);
	db_stmt_set_u64(stmt, 1, M->id);
	r = db_stmt_query(stmt);

	if (db_result_next(r))
		M->permission = db_result_get_int(r, 0);
}
Example #9
0
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;
}
Example #10
0
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));
}
Example #11
0
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;
}
Example #12
0
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;
}
Example #13
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;
}
Example #14
0
static int db_count_deleted(u64_t * rows)
{
	C c; R r; volatile int t = TRUE;
	assert(rows != NULL); *rows = 0;

	c = db_con_get();
	TRY
		r = db_query(c, "SELECT COUNT(*) FROM %smessages WHERE status = %d", DBPFX, MESSAGE_STATUS_DELETE);
		if (db_result_next(r))
			*rows = db_result_get_int(r,0);
	CATCH(SQLException)
		LOG_SQLERROR;
		t = DM_EQUERY;
	FINALLY
		db_con_close(c);
	END_TRY;

	return t;
}
Example #15
0
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;
}
Example #16
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;
}
Example #17
0
static int db_deleted_count(u64_t * rows)
{
	C c; R r; volatile int t = FALSE;
	assert(rows); *rows = 0;

	c = db_con_get();
	r = db_query(c, "SELECT COUNT(*) FROM %smessages WHERE status=%d", DBPFX, MESSAGE_STATUS_PURGE);
	TRY
		if (db_result_next(r)) {
			*rows = db_result_get_int(r,0);
			t = TRUE;
		}
	CATCH(SQLException)
		LOG_SQLERROR;
	FINALLY
		db_con_close(c);
	END_TRY;

	return t;
}
Example #18
0
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;
}
Example #19
0
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;
}
Example #20
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);
	}
}
Example #21
0
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;
}
Example #22
0
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;
}
Example #23
0
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;
}
Example #24
0
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;
}
Example #25
0
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;
}
Example #26
0
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);
}
Example #27
0
File: pop3.c Project: skeyby/dbmail
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;
}
Example #28
0
int MailboxState_hasPermission(T M, uint64_t userid, const char *right_flag)
{
	PreparedStatement_T stmt;
	Connection_T c;
       	ResultSet_T r;
	volatile int result = FALSE;
	volatile bool owner_acl = false;
	uint64_t owner_id, mboxid;

	mboxid = MailboxState_getId(M);

	TRACE(TRACE_DEBUG, "checking ACL [%s] for user [%" PRIu64 "] on mailbox [%" PRIu64 "]",
			right_flag, userid, mboxid);

	/* If we don't know who owns the mailbox, look it up. */
	owner_id = MailboxState_getOwner(M);
	if (! owner_id) {
		result = db_get_mailbox_owner(mboxid, &owner_id);
		MailboxState_setOwner(M, owner_id);
		if (! (result > 0))
			return result;
	}

	if (owner_id == userid) {
		c = db_con_get();
		TRY
			stmt = db_stmt_prepare(c,
					"SELECT * FROM %sacl WHERE "
					"user_id = ? AND mailbox_id = ?", 
					DBPFX);
			db_stmt_set_u64(stmt, 1, userid);
			db_stmt_set_u64(stmt, 2, mboxid);
			r = db_stmt_query(stmt);

			if (db_result_next(r))
				owner_acl = true;
		CATCH(SQLException)
			LOG_SQLERROR;
			result = DM_EQUERY;
		FINALLY	
			db_con_close(c);
		END_TRY;

		if (! owner_acl) {
			TRACE(TRACE_DEBUG, "mailbox [%" PRIu64 "] is owned by user [%" PRIu64 "]"
					"and no ACL in place. Giving all rights",
					mboxid, userid);
			return 1;
		} else {
			TRACE(TRACE_DEBUG, "mailbox [%" PRIu64 "] is owned by user [%" PRIu64 "]"
					"but ACL in place. Restricted access for owner.",
					mboxid, userid);
		}

	}

	result = FALSE;
	c = db_con_get();
	TRY
		stmt = db_stmt_prepare(c,
			       	"SELECT * FROM %sacl WHERE "
				"user_id = ? AND mailbox_id = ? AND %s = 1", 
				DBPFX, right_flag);
		db_stmt_set_u64(stmt, 1, userid);
		db_stmt_set_u64(stmt, 2, mboxid);
		r = db_stmt_query(stmt);

		if (db_result_next(r))
			result = TRUE;
	CATCH(SQLException)
		LOG_SQLERROR;
		result = DM_EQUERY;
	FINALLY	
		db_con_close(c);
	END_TRY;

	return result;
}
Example #29
0
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;
}