James Greig wrote:
Fair point, I assumed that was the case. We are looking for a way of
triggering when e-mails are moved into a certain folder within imap,
basically for spam filter training. For example, if an email is
moved to /spam/ from /inbox/ then this triggers a spam train to train
the email as spam. However, if an email is moved to /inbox/ from
/spam/ then this triggers a spam train to train as good.
There's a patch for that. I'm attaching it, just installed and tested
for 2.2.7-rc2.
You can find discussion on it here:
http://www.nabble.com/monitoring-message-movement-between-mailboxes-tf3487027.html#a9735772
--
Aleksander Kamenik
system administrator
+372 6659 649
[EMAIL PROTECTED]
Krediidiinfo AS
http://www.krediidiinfo.ee/
diff -Naur dbmail-2.2.7-rc2/db.c dbmail-2.2.7-rc2_cplogs/db.c
--- dbmail-2.2.7-rc2/db.c 2007-09-27 17:23:04.000000000 +0300
+++ dbmail-2.2.7-rc2_cplogs/db.c 2007-10-02 16:55:00.000000000 +0300
@@ -2558,7 +2558,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);
@@ -3789,7 +3789,32 @@
return size;
}
-int db_copymsg(u64_t msg_idnr, u64_t mailbox_to, u64_t user_idnr,
+gboolean db_getmailbox_flag(u64_t mailbox_idnr, const char *flagname)
+{
+ gboolean flag = FALSE;
+
+ char query[DEF_QUERYSIZE];
+ memset(query,0,DEF_QUERYSIZE);
+
+ g_return_val_if_fail(mailbox_idnr, 0);
+
+ snprintf(query, DEF_QUERYSIZE, "SELECT %s FROM %smailboxes WHERE
mailbox_idnr = %llu", flagname, DBPFX, mailbox_idnr);
+
+ if(db_query(query) == -1)
+ return flag;
+
+ if (db_num_rows() == 0) {
+ TRACE(TRACE_ERROR, "invalid mailbox id (%lld) specified or [%s]
doesnt exist", mailbox_idnr, flagname);
+ db_free_result();
+ return flag;
+ }
+ flag = db_get_result_bool(0,0);
+ db_free_result();
+ 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;
@@ -3797,7 +3822,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 "
@@ -3836,7 +3860,7 @@
/* get the id of the inserted record */
*newmsg_idnr = db_insert_result("message_idnr");
-
+
/* update quotum */
if (user_quotum_inc(user_idnr, msgsize) == -1) {
TRACE(TRACE_ERROR, "error setting the new quotum "
@@ -3845,6 +3869,31 @@
return DM_EQUERY;
}
+ if(! (mailbox_from && mailbox_to))
+ return DM_EGENERAL;
+
+ gboolean do_src = db_getmailbox_flag(mailbox_from, "cplog_flag");
+ gboolean do_dst = db_getmailbox_flag(mailbox_to, "cplog_flag");
+ gboolean to_trash = db_getmailbox_flag(mailbox_to, "trash_flag");
+
+ if( do_src && (! to_trash) ) {
+ snprintf(query, DEF_QUERYSIZE, "INSERT INTO %scplogs "
+ "(message_idnr, mailbox_idnr, direction) "
+ "VALUES ('%llu','%llu','0')",
+ DBPFX, *newmsg_idnr, mailbox_from);
+ if(db_query(query) == -1)
+ return DM_EQUERY;
+ }
+
+ if( do_dst ) {
+ snprintf(query,DEF_QUERYSIZE, "INSERT INTO %scplogs "
+ "(message_idnr, mailbox_idnr, direction) "
+ "VALUES ('%llu', '%llu', '1')",
+ DBPFX, *newmsg_idnr, mailbox_to);
+ if(db_query(query) == -1)
+ return DM_EQUERY;
+ }
+
return DM_EGENERAL;
}
diff -Naur dbmail-2.2.7-rc2/db.h dbmail-2.2.7-rc2_cplogs/db.h
--- dbmail-2.2.7-rc2/db.h 2007-09-27 17:23:04.000000000 +0300
+++ dbmail-2.2.7-rc2_cplogs/db.h 2007-10-02 16:55:00.000000000 +0300
@@ -949,6 +949,12 @@
int db_getmailbox(mailbox_t * mb);
/**
+ * \brief get flag from mailbox by-name as boolean value
+ *
+ */
+gboolean db_getmailbox_flag(u64_t mailbox_idnr, const char *flagname);
+
+/**
* \brief find owner of a mailbox
* \param mboxid id of mailbox
* \param owner_id will hold owner of mailbox after return
@@ -1101,7 +1107,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);
/**
diff -Naur dbmail-2.2.7-rc2/dbmail-imapsession.h
dbmail-2.2.7-rc2_cplogs/dbmail-imapsession.h
--- dbmail-2.2.7-rc2/dbmail-imapsession.h 2007-09-27 17:23:04.000000000
+0300
+++ dbmail-2.2.7-rc2_cplogs/dbmail-imapsession.h 2007-10-02
16:55:00.000000000 +0300
@@ -43,7 +43,8 @@
} cmd_store_t;
typedef struct {
- u64_t mailbox_id;
+ u64_t src_box_id;
+ u64_t dst_box_id;
} cmd_copy_t;
typedef int (*IMAP_COMMAND_HANDLER) (struct ImapSession *);
diff -Naur dbmail-2.2.7-rc2/imapcommands.c
dbmail-2.2.7-rc2_cplogs/imapcommands.c
--- dbmail-2.2.7-rc2/imapcommands.c 2007-09-27 17:23:04.000000000 +0300
+++ dbmail-2.2.7-rc2_cplogs/imapcommands.c 2007-10-02 16:55:00.000000000
+0300
@@ -1691,7 +1691,7 @@
u64_t newid;
int result;
- result = db_copymsg(*id, cmd->mailbox_id, ud->userid, &newid);
+ result = db_copymsg(*id, cmd->src_box_id, cmd->dst_box_id, ud->userid,
&newid);
if (result == -1) {
dbmail_imap_session_printf(self, "* BYE internal dbase
error\r\n");
db_rollback_transaction();
@@ -1709,22 +1709,24 @@
int _ic_copy(struct ImapSession *self)
{
imap_userdata_t *ud = (imap_userdata_t *) self->ci->userData;
- u64_t destmboxid;
int result;
- mailbox_t destmbox;
+ mailbox_t dst_box;
+ u64_t dst_box_id, src_box_id;
cmd_copy_t cmd;
- memset(&destmbox, 0, sizeof(destmbox));
+ memset(&dst_box, 0, sizeof(dst_box));
+
+ src_box_id = ud->mailbox.uid;
if (!check_state_and_args(self, "COPY", 2, 2, IMAPCS_SELECTED))
return 1; /* error, return */
/* check if destination mailbox exists */
- if (db_findmailbox(self->args[self->args_idx+1], ud->userid,
&destmboxid) == -1) {
+ if (db_findmailbox(self->args[self->args_idx+1], ud->userid,
&dst_box_id) == -1) {
dbmail_imap_session_printf(self, "* BYE internal dbase
error\r\n");
return -1; /* fatal */
}
- if (destmboxid == 0) {
+ if (dst_box_id == 0) {
/* error: cannot select mailbox */
dbmail_imap_session_printf(self,
"%s NO [TRYCREATE] specified mailbox does not
exist\r\n",
@@ -1743,8 +1745,8 @@
return 1;
}
// check if user has right to COPY to destination mailbox
- destmbox.uid = destmboxid;
- result = acl_has_right(&destmbox, ud->userid, ACL_RIGHT_INSERT);
+ dst_box.uid = dst_box_id;
+ result = acl_has_right(&dst_box, ud->userid, ACL_RIGHT_INSERT);
if (result < 0) {
dbmail_imap_session_printf(self, "* BYE internal database
error\r\n");
return -1; /* fatal */
@@ -1755,7 +1757,9 @@
return 1;
}
- cmd.mailbox_id = destmboxid;
+ cmd.src_box_id = src_box_id;
+ cmd.dst_box_id = dst_box_id;
+
self->cmd = &cmd;
if (db_begin_transaction() < 0)
diff -Naur dbmail-2.2.7-rc2/sort.c dbmail-2.2.7-rc2_cplogs/sort.c
--- dbmail-2.2.7-rc2/sort.c 2007-09-27 17:23:04.000000000 +0300
+++ dbmail-2.2.7-rc2_cplogs/sort.c 2007-10-02 16:55:00.000000000 +0300
@@ -182,7 +182,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.7-rc2/sql/mysql/2_2_x-2_3_0.mysql
dbmail-2.2.7-rc2_cplogs/sql/mysql/2_2_x-2_3_0.mysql
--- dbmail-2.2.7-rc2/sql/mysql/2_2_x-2_3_0.mysql 1970-01-01
03:00:00.000000000 +0300
+++ dbmail-2.2.7-rc2_cplogs/sql/mysql/2_2_x-2_3_0.mysql 2007-10-02
16:55:00.000000000 +0300
@@ -0,0 +1,19 @@
+
+DROP TABLE IF EXISTS dbmail_cplogs;
+CREATE TABLE `dbmail_cplogs` (
+ `id` bigint(21) NOT NULL auto_increment,
+ `message_idnr` bigint(21) NOT NULL,
+ `mailbox_idnr` bigint(21) NOT NULL,
+ `direction` tinyint(1) NOT NULL,
+ PRIMARY KEY (`id`)
+ FOREIGN KEY (message_idnr)
+ REFERENCES dbmail_messages(message_idnr)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+ FOREIGN KEY (mailbox_idnr)
+ REFERENCES dbmail_mailboxes(mailbox_idnr)
+ ON UPDATE CASCADE ON DELETE CASCADE,
+) ENGINE=InnoDB;
+
+
+ALTER TABLE dbmail_messages ADD cplog_flag tinyint(1) NOT NULL default '0';
+ALTER TABLE dbmail_messages ADD trash_flag tinyint(1) NOT NULL default '0';
diff -Naur dbmail-2.2.7-rc2/sql/sqlite/2_2_x-2_3_0.sqlite
dbmail-2.2.7-rc2_cplogs/sql/sqlite/2_2_x-2_3_0.sqlite
--- dbmail-2.2.7-rc2/sql/sqlite/2_2_x-2_3_0.sqlite 1970-01-01
03:00:00.000000000 +0300
+++ dbmail-2.2.7-rc2_cplogs/sql/sqlite/2_2_x-2_3_0.sqlite 2007-10-02
16:55:00.000000000 +0300
@@ -0,0 +1,81 @@
+
+-- support faster FETCH commands by caching BODYSTRUCTURE and ENVELOPE
information
+
+CREATE TABLE dbmail_cplogs (
+ id INTEGER NOT NULL PRIMARY KEY,
+ message_idnr INTEGER NOT NULL,
+ mailbox_idnr INTEGER NOT NULL,
+ direction boolean default '0' NOT NULL
+);
+
+-- FOREIGN KEY (message_idnr)
+-- REFERENCES dbmail_messages(message_idnr)
+-- ON UPDATE CASCADE ON DELETE CASCADE,
+
+CREATE TRIGGER fk_insert_cplogs_message_idnr
+ BEFORE INSERT ON dbmail_cplogs
+ FOR EACH ROW BEGIN
+ SELECT CASE
+ WHEN (new.message_idnr IS NOT NULL)
+ AND ((SELECT message_idnr FROM dbmail_messages
WHERE message_idnr = new.message_idnr) IS NULL)
+ THEN RAISE (ABORT, 'insert on table "dbmail_cplogs"
violates foreign key constraint "fk_insert_cplogs_message_idnr"')
+ END;
+ END;
+CREATE TRIGGER fk_update1_cplogs_message_idnr
+ BEFORE UPDATE ON dbmail_cplogs
+ FOR EACH ROW BEGIN
+ SELECT CASE
+ WHEN (new.message_idnr IS NOT NULL)
+ AND ((SELECT message_idnr FROM dbmail_messages
WHERE message_idnr = new.message_idnr) IS NULL)
+ THEN RAISE (ABORT, 'update on table "dbmail_cplogs"
violates foreign key constraint "fk_update1_cplogs_message_idnr"')
+ END;
+ END;
+CREATE TRIGGER fk_update2_cplogs_message_idnr
+ AFTER UPDATE ON dbmail_messages
+ FOR EACH ROW BEGIN
+ UPDATE dbmail_cplogs SET message_idnr = new.message_idnr WHERE
message_idnr = OLD.message_idnr;
+ END;
+CREATE TRIGGER fk_delete_cplogs_message_idnr
+ BEFORE DELETE ON dbmail_messages
+ FOR EACH ROW BEGIN
+ DELETE FROM dbmail_cplogs WHERE message_idnr = OLD.message_idnr;
+ END;
+
+-- FOREIGN KEY (mailbox_idnr)
+-- REFERENCES dbmail_mailboxes(mailbox_idnr)
+-- ON UPDATE CASCADE ON DELETE CASCADE,
+
+CREATE TRIGGER fk_insert_cplogs_mailbox_idnr
+ BEFORE INSERT ON dbmail_cplogs
+ FOR EACH ROW BEGIN
+ SELECT CASE
+ WHEN (new.mailbox_idnr IS NOT NULL)
+ AND ((SELECT mailbox_idnr FROM dbmail_mailboxes
WHERE mailbox_idnr = new.mailbox_idnr) IS NULL)
+ THEN RAISE (ABORT, 'insert on table "dbmail_cplogs"
violates foreign key constraint "fk_insert_cplogs_mailbox_idnr"')
+ END;
+ END;
+CREATE TRIGGER fk_update1_cplogs_mailbox_idnr
+ BEFORE UPDATE ON dbmail_cplogs
+ FOR EACH ROW BEGIN
+ SELECT CASE
+ WHEN (new.mailbox_idnr IS NOT NULL)
+ AND ((SELECT mailbox_idnr FROM dbmail_mailboxes
WHERE mailbox_idnr = new.mailbox_idnr) IS NULL)
+ THEN RAISE (ABORT, 'update on table "dbmail_cplogs"
violates foreign key constraint "fk_update1_cplogs_mailbox_idnr"')
+ END;
+ END;
+CREATE TRIGGER fk_update2_cplogs_mailbox_idnr
+ AFTER UPDATE ON dbmail_mailboxes
+ FOR EACH ROW BEGIN
+ UPDATE dbmail_cplogs SET mailbox_idnr = new.mailbox_idnr WHERE
mailbox_idnr = OLD.mailbox_idnr;
+ END;
+CREATE TRIGGER fk_delete_cplogs_mailbox_idnr
+ BEFORE DELETE ON dbmail_mailboxes
+ FOR EACH ROW BEGIN
+ DELETE FROM dbmail_cplogs WHERE mailbox_idnr = OLD.mailbox_idnr;
+ END;
+
+
+
+ALTER TABLE dbmail_mailboxes ADD cplog_flag BOOLEAN DEFAULT '0' NOT NULL;
+ALTER TABLE dbmail_mailboxes ADD trash_flag BOOLEAN DEFAULT '0' NOT NULL;
+
_______________________________________________
DBmail mailing list
[email protected]
https://mailman.fastxs.nl/mailman/listinfo/dbmail
