Hi,
About a year ago I asked the list about how to configure a spam setup in
dbmail, so that users could teach their spam filters by simply moving
messages to and from the spam folder. Based on the comments we've at our
company created a small patch for dbmail. We use mysql, I have no idea
if this works with other storages.
Why it's good and how it's used:
Spam mails always arrive to user's personal spam folder. When a spam
arrives to his/her Inbox, the user simply moves the offending message to
the spam mailbox and the spam filter learns this message as a spam. The
message can now be deleted by the user or by the system admin via an
automatic script.
When a false positive ends up in the spam mailbox, all the user has to
do to correct the spam filter's behavior is to move the message out of
the spam mailbox. Exceptions such as the Trash mailbox are handled
correctly or more correctly phrased: ignored.
Also note, that in this case moving messages is the same as copying
messages. Actually we're are monitoring copying. Moving is copying and
deleting the original. That's IMAP.
The patch has been working quite a OK now since midsummer. Two patch
files attached, one for dbmail 2.2.2 and one for dbmail 2.2.4, both
tested in production, although I can't and will not give you any
guarantees. In total four dbmail files had to be modified: db.c, db.h,
imapcommands.c and sort.c.
The patch requires two additional columns in the dbmail_mailboxes table
and one additional table to record the movements from and to the spam
folder.
The two columns to be added to the dbmail_mailboxes table are the following:
kred_log_actions tinyint(1) NOT NULL default '0'
kred_delete_folder_flag tinyint(1) NOT NULL default '0'
When kred_log_actions is set to "1", movements from and to this mailbox
will be logged in the kred_spamlogs table.
When kred_delete_folder_flag is set to "1", it is considered a Trash
mailbox. Movements from a mailbox with kred_log_actions = "1" to this
mailbox will NOT be logged. Otherwise if a user deletes spam with a
client that first moves the messages to a Trash mailbox, all the spam
will be relearned as ham.
And the additional table required is:
CREATE TABLE `kred_spamlogs` (
`id` int(11) unsigned NOT NULL auto_increment,
`message_idnr` bigint(21) NOT NULL,
`mailbox_idnr` bigint(21) NOT NULL,
`direction` tinyint(1) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
This should be self explanatory. Direction 1 means to and 0 means from
the monitored mailbox.
Sorry for the egoistic prefix, you can always change that.
It is now the job of an external script to monitor this table and get
the messages for retraining. With dspam, we use the dbmail_headervalue
and dbmail_headername tables to get the X-Dspam-Signature and retrain
based on that. Works like a charm.
Theoretically this can be used in other applications besides spam
relearning too.
Any bugs, improvements and questions welcome to my email address or
preferably to this thread while it's active.
Patches have the same license as dbmail, GPL version 2.
OT: The problems I have with dbmail 2.2.4 and Outlook clients are not
caused or affected by this patch. I tested without it too.
Regards,
--
Aleksander Kamenik
system administrator
+372 6659 649
[EMAIL PROTECTED]
Krediidiinfo AS
http://www.krediidiinfo.ee/
diff -Naur dbmail-2.2.2/db.c dbmail-2.2.2-kred/db.c
--- dbmail-2.2.2/db.c 2007-01-21 20:41:58.000000000 +0200
+++ dbmail-2.2.2-kred/db.c 2007-02-12 16:28:49.000000000 +0200
@@ -2427,7 +2427,7 @@
}
dbmail_message_store(message);
- result = db_copymsg(message->id, mailbox_idnr, user_idnr, msg_idnr);
+ result = db_copymsg(message->id, 0, mailbox_idnr, user_idnr, msg_idnr);
db_delete_message(message->id);
dbmail_message_free(message);
@@ -3607,7 +3607,63 @@
return size;
}
-int db_copymsg(u64_t msg_idnr, u64_t mailbox_to, u64_t user_idnr,
+int db_getmailbox_spam_copy_flag(u64_t mailbox_idnr)
+{
+ int flag = 0;
+ char query[DEF_QUERYSIZE];
+ memset(query,0,DEF_QUERYSIZE);
+
+ g_return_val_if_fail(mailbox_idnr, DM_EQUERY);
+
+ snprintf(query, DEF_QUERYSIZE, "SELECT kred_log_actions FROM %smailboxes
WHERE mailbox_idnr = %llu", DBPFX, mailbox_idnr);
+
+ if(db_query(query) == -1)
+ {
+ trace(TRACE_ERROR, "%s, %s: couldnt select mailbox", __FILE__,
__func__);
+ return DM_EQUERY;
+ }
+
+ //trace(TRACE_ERROR, "\n---------------------\ndoing spam copy flag\n");
+
+ if (db_num_rows() == 0)
+ {
+ trace(TRACE_ERROR, "%s,%s : invalid mailbox id (%lld) specified or
kred_log_actions doesnt exist", __FILE__, __func__, mailbox_idnr);
+ db_free_result();
+ return DM_EQUERY;
+ }
+ flag = db_get_result_bool(0,0);
+ return flag;
+}
+
+int db_getmailbox_trash_flag(u64_t mailbox_idnr)
+{
+ char query[DEF_QUERYSIZE];
+ memset(query,0,DEF_QUERYSIZE);
+
+ int flag = 0;
+ g_return_val_if_fail(mailbox_idnr, DM_EQUERY);
+
+ snprintf(query, DEF_QUERYSIZE, "SELECT kred_delete_folder_flag FROM
%smailboxes WHERE mailbox_idnr = %llu", DBPFX, mailbox_idnr);
+
+ if(db_query(query) == -1)
+ {
+ trace(TRACE_ERROR, "%s, %s: couldnt select mailbox", __FILE__,
__func__);
+ return DM_EQUERY;
+ }
+
+ //trace(TRACE_ERROR, "\n---------------------\ndoing spam copy flag\n");
+
+ if (db_num_rows() == 0)
+ {
+ trace(TRACE_ERROR, "%s,%s : invalid mailbox id (%lld) specified or
kred_log_actions doesnt exist", __FILE__, __func__, mailbox_idnr);
+ db_free_result();
+ return DM_EQUERY;
+ }
+ flag = db_get_result_bool(0,0);
+ return flag;
+}
+
+int db_copymsg(u64_t msg_idnr, u64_t mailbox_from, u64_t mailbox_to, u64_t
user_idnr,
u64_t * newmsg_idnr)
{
u64_t msgsize;
@@ -3615,7 +3671,6 @@
char query[DEF_QUERYSIZE];
memset(query,0,DEF_QUERYSIZE);
-
/* Get the size of the message to be copied. */
if (! (msgsize = message_get_size(msg_idnr))) {
TRACE(TRACE_ERROR, "error getting message size for "
@@ -3655,6 +3710,37 @@
/* get the id of the inserted record */
*newmsg_idnr = db_insert_result("message_idnr");
+
+ if(mailbox_from > 0)
+ {
+ //trace(TRACE_ERROR, "\nwe have mailbox_from\n");
+ if(db_getmailbox_spam_copy_flag(mailbox_from)>0)
+ {
+ if(db_getmailbox_trash_flag(mailbox_to)<1)
+ {
+ snprintf(query, DEF_QUERYSIZE, "INSERT INTO
kred_spamlogs (message_idnr, mailbox_idnr, direction) VALUES
('%llu','%llu','0')", *newmsg_idnr, mailbox_from);
+ if(db_query(query) == -1)
+ {
+ trace(TRACE_ERROR, "%s,%s: error
logging from box, couldnt insert data to spamlogs", __FILE__, __func__);
+ return DM_EQUERY;
+ }
+ //trace(TRACE_ERROR,
"\n-------------------\nmove action\n");
+ }
+ }
+ if(mailbox_to>0)
+ {
+ if(db_getmailbox_spam_copy_flag(mailbox_to)>0)
+ {
+ snprintf(query,DEF_QUERYSIZE, "INSERT
INTO kred_spamlogs (message_idnr, mailbox_idnr, direction) VALUES ('%llu',
'%llu', '1')", *newmsg_idnr, mailbox_to);
+ if(db_query(query) == -1)
+ {
+ trace(TRACE_ERROR, "%s, %s:
error logging to box, couldnt write to db", __FILE__, __func__);
+ return DM_EQUERY;
+ }
+ }
+ }
+ }
+
/* update quotum */
if (user_quotum_inc(user_idnr, msgsize) == -1) {
TRACE(TRACE_ERROR, "error setting the new quotum "
diff -Naur dbmail-2.2.2/db.h dbmail-2.2.2-kred/db.h
--- dbmail-2.2.2/db.h 2007-01-21 20:41:58.000000000 +0200
+++ dbmail-2.2.2-kred/db.h 2007-02-12 16:28:53.000000000 +0200
@@ -1102,7 +1102,7 @@
* - -1 on failure
* - 0 on success
*/
-int db_copymsg(u64_t msg_idnr, u64_t mailbox_to,
+int db_copymsg(u64_t msg_idnr, u64_t mailbox_from, u64_t mailbox_to,
u64_t user_idnr, u64_t * newmsg_idnr);
/**
* \brief get name of mailbox
diff -Naur dbmail-2.2.2/imapcommands.c dbmail-2.2.2-kred/imapcommands.c
--- dbmail-2.2.2/imapcommands.c 2006-12-10 23:36:03.000000000 +0200
+++ dbmail-2.2.2-kred/imapcommands.c 2007-02-12 16:29:51.000000000 +0200
@@ -1788,7 +1788,7 @@
imap_userdata_t *ud = (imap_userdata_t *) self->ci->userData;
u64_t i, copy_start, copy_end;
unsigned fn;
- u64_t destmboxid, thisnum;
+ u64_t destmboxid, thisnum, fromboxid;
int result;
u64_t new_msgid;
char *endptr, *lastchar = NULL;
@@ -1796,6 +1796,8 @@
bzero(&destmbox, sizeof(destmbox));
+ fromboxid = ud->mailbox.uid;
+
if (!check_state_and_args(self, "COPY", 2, 2, IMAPCS_SELECTED))
return 1; /* error, return */
@@ -1922,7 +1924,7 @@
continue;
}
- result = db_copymsg(thisnum, destmboxid, ud->userid,
&new_msgid);
+ result = db_copymsg(thisnum, fromboxid, destmboxid,
ud->userid, &new_msgid);
if (result == -1) {
dbmail_imap_session_printf(self, "* BYE
internal dbase error\r\n");
db_rollback_transaction();
diff -Naur dbmail-2.2.2/sort.c dbmail-2.2.2-kred/sort.c
--- dbmail-2.2.2/sort.c 2006-09-27 22:28:04.000000000 +0300
+++ dbmail-2.2.2-kred/sort.c 2007-02-12 16:29:12.000000000 +0200
@@ -181,7 +181,7 @@
}
// Ok, we have the ACL right, time to deliver the message.
- switch (db_copymsg(message->id, mboxidnr, useridnr, &newmsgidnr)) {
+ switch (db_copymsg(message->id, 0, mboxidnr, useridnr, &newmsgidnr)) {
case -2:
TRACE(TRACE_DEBUG, "error copying message to user [%llu],"
"maxmail exceeded", useridnr);
diff -Naur dbmail-2.2.4/db.c dbmail-2.2.4_patched/db.c
--- dbmail-2.2.4/db.c 2007-03-07 15:46:17.000000000 +0200
+++ dbmail-2.2.4_patched/db.c 2007-03-26 18:37:02.000000000 +0300
@@ -2427,7 +2427,7 @@
}
dbmail_message_store(message);
- result = db_copymsg(message->id, mailbox_idnr, user_idnr, msg_idnr);
+ result = db_copymsg(message->id, 0, mailbox_idnr, user_idnr, msg_idnr);
db_delete_message(message->id);
dbmail_message_free(message);
@@ -3597,7 +3597,63 @@
return size;
}
-int db_copymsg(u64_t msg_idnr, u64_t mailbox_to, u64_t user_idnr,
+int db_getmailbox_spam_copy_flag(u64_t mailbox_idnr)
+{
+ int flag = 0;
+ char query[DEF_QUERYSIZE];
+ memset(query,0,DEF_QUERYSIZE);
+
+ g_return_val_if_fail(mailbox_idnr, DM_EQUERY);
+
+ snprintf(query, DEF_QUERYSIZE, "SELECT kred_log_actions FROM %smailboxes
WHERE mailbox_idnr = %llu", DBPFX, mailbox_idnr);
+
+ if(db_query(query) == -1)
+ {
+ trace(TRACE_ERROR, "%s, %s: couldnt select mailbox", __FILE__,
__func__);
+ return DM_EQUERY;
+ }
+
+ //trace(TRACE_ERROR, "\n---------------------\ndoing spam copy flag\n");
+
+ if (db_num_rows() == 0)
+ {
+ trace(TRACE_ERROR, "%s,%s : invalid mailbox id (%lld) specified or
kred_log_actions doesnt exist", __FILE__, __func__, mailbox_idnr);
+ db_free_result();
+ return DM_EQUERY;
+ }
+ flag = db_get_result_bool(0,0);
+ return flag;
+}
+
+int db_getmailbox_trash_flag(u64_t mailbox_idnr)
+{
+ char query[DEF_QUERYSIZE];
+ memset(query,0,DEF_QUERYSIZE);
+
+ int flag = 0;
+ g_return_val_if_fail(mailbox_idnr, DM_EQUERY);
+
+ snprintf(query, DEF_QUERYSIZE, "SELECT kred_delete_folder_flag FROM
%smailboxes WHERE mailbox_idnr = %llu", DBPFX, mailbox_idnr);
+
+ if(db_query(query) == -1)
+ {
+ trace(TRACE_ERROR, "%s, %s: couldnt select mailbox", __FILE__,
__func__);
+ return DM_EQUERY;
+ }
+
+ //trace(TRACE_ERROR, "\n---------------------\ndoing spam copy flag\n");
+
+ if (db_num_rows() == 0)
+ {
+ trace(TRACE_ERROR, "%s,%s : invalid mailbox id (%lld) specified or
kred_log_actions doesnt exist", __FILE__, __func__, mailbox_idnr);
+ db_free_result();
+ return DM_EQUERY;
+ }
+ flag = db_get_result_bool(0,0);
+ return flag;
+}
+
+int db_copymsg(u64_t msg_idnr, u64_t mailbox_from, u64_t mailbox_to, u64_t
user_idnr,
u64_t * newmsg_idnr)
{
u64_t msgsize;
@@ -3605,7 +3661,6 @@
char query[DEF_QUERYSIZE];
memset(query,0,DEF_QUERYSIZE);
-
/* Get the size of the message to be copied. */
if (! (msgsize = message_get_size(msg_idnr))) {
TRACE(TRACE_ERROR, "error getting message size for "
@@ -3645,6 +3700,37 @@
/* get the id of the inserted record */
*newmsg_idnr = db_insert_result("message_idnr");
+
+ if(mailbox_from > 0)
+ {
+ //trace(TRACE_ERROR, "\nwe have mailbox_from\n");
+ if(db_getmailbox_spam_copy_flag(mailbox_from)>0)
+ {
+ if(db_getmailbox_trash_flag(mailbox_to)<1)
+ {
+ snprintf(query, DEF_QUERYSIZE, "INSERT INTO
kred_spamlogs (message_idnr, mailbox_idnr, direction) VALUES
('%llu','%llu','0')", *newmsg_idnr, mailbox_from);
+ if(db_query(query) == -1)
+ {
+ trace(TRACE_ERROR, "%s,%s: error
logging from box, couldnt insert data to spamlogs", __FILE__, __func__);
+ return DM_EQUERY;
+ }
+ //trace(TRACE_ERROR,
"\n-------------------\nmove action\n");
+ }
+ }
+ if(mailbox_to>0)
+ {
+ if(db_getmailbox_spam_copy_flag(mailbox_to)>0)
+ {
+ snprintf(query,DEF_QUERYSIZE, "INSERT
INTO kred_spamlogs (message_idnr, mailbox_idnr, direction) VALUES ('%llu',
'%llu', '1')", *newmsg_idnr, mailbox_to);
+ if(db_query(query) == -1)
+ {
+ trace(TRACE_ERROR, "%s, %s:
error logging to box, couldnt write to db", __FILE__, __func__);
+ return DM_EQUERY;
+ }
+ }
+ }
+ }
+
/* update quotum */
if (user_quotum_inc(user_idnr, msgsize) == -1) {
TRACE(TRACE_ERROR, "error setting the new quotum "
diff -Naur dbmail-2.2.4/db.h dbmail-2.2.4_patched/db.h
--- dbmail-2.2.4/db.h 2007-02-08 14:52:22.000000000 +0200
+++ dbmail-2.2.4_patched/db.h 2007-03-26 18:37:05.000000000 +0300
@@ -1102,7 +1102,7 @@
* - -1 on failure
* - 0 on success
*/
-int db_copymsg(u64_t msg_idnr, u64_t mailbox_to,
+int db_copymsg(u64_t msg_idnr, u64_t mailbox_from, u64_t mailbox_to,
u64_t user_idnr, u64_t * newmsg_idnr);
/**
* \brief get name of mailbox
diff -Naur dbmail-2.2.4/imapcommands.c dbmail-2.2.4_patched/imapcommands.c
--- dbmail-2.2.4/imapcommands.c 2007-03-07 15:46:17.000000000 +0200
+++ dbmail-2.2.4_patched/imapcommands.c 2007-03-26 18:37:12.000000000 +0300
@@ -1621,9 +1621,14 @@
imap_userdata_t *ud = (imap_userdata_t *) self->ci->userData;
cmd_copy_t *cmd = (cmd_copy_t *)self->cmd;
u64_t newid;
+
+ u64_t fromboxid;
+
int result;
- result = db_copymsg(*id, cmd->mailbox_id, ud->userid, &newid);
+ fromboxid = ud->mailbox.uid;
+
+ result = db_copymsg(*id, fromboxid, cmd->mailbox_id, ud->userid,
&newid);
if (result == -1) {
dbmail_imap_session_printf(self, "* BYE internal dbase
error\r\n");
db_rollback_transaction();
@@ -1648,6 +1653,8 @@
bzero(&destmbox, sizeof(destmbox));
+
+
if (!check_state_and_args(self, "COPY", 2, 2, IMAPCS_SELECTED))
return 1; /* error, return */
diff -Naur dbmail-2.2.4/sort.c dbmail-2.2.4_patched/sort.c
--- dbmail-2.2.4/sort.c 2006-09-27 22:28:04.000000000 +0300
+++ dbmail-2.2.4_patched/sort.c 2007-03-26 18:37:18.000000000 +0300
@@ -181,7 +181,7 @@
}
// Ok, we have the ACL right, time to deliver the message.
- switch (db_copymsg(message->id, mboxidnr, useridnr, &newmsgidnr)) {
+ switch (db_copymsg(message->id, 0, mboxidnr, useridnr, &newmsgidnr)) {
case -2:
TRACE(TRACE_DEBUG, "error copying message to user [%llu],"
"maxmail exceeded", useridnr);
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
