Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Ok. But without postgres support, I can't release 2.0.8 *with* this patch.
new patch version with postgres support is attached
it's seems to work for me on my simple postgres installation :)
--
Regards,
Eugene Prokopiev
--- ./pipe.c.orig 2005-08-19 18:30:42 +0400
+++ ./pipe.c 2005-09-20 13:04:08 +0400
@@ -574,8 +574,9 @@
trace(TRACE_DEBUG,
%s, %s: calling sort_and_deliver for useridnr [%llu],
__FILE__, __func__, useridnr);
-
- dsn_result = sort_and_deliver(tmpmsgidnr, msgsize, useridnr, delivery-mailbox);
+
+ dsn_result = sort_and_deliver(tmpmsgidnr, msgsize, useridnr,
+db_get_mailbox_from_filters(useridnr, headerfields, delivery-mailbox));
switch (dsn_result) {
case DSN_CLASS_OK:
--- ./sql/mysql/create_tables.mysql.orig 2005-08-19 18:30:42 +0400
+++ ./sql/mysql/create_tables.mysql 2005-09-20 15:16:54 +0400
@@ -61,6 +61,20 @@
UNIQUE INDEX owner_idnr_name_index (owner_idnr, name)
);
+DROP TABLE IF EXISTS dbmail_filters;
+CREATE TABLE dbmail_filters (
+ user_id bigint(21) not null default '0',
+ filter_id bigint(21) not null default '0',
+ filter_field varchar(128) NOT NULL default '',
+ filter_value varchar(255) NOT NULL default '',
+ mailbox varchar(100) NOT NULL default '',
+ index user_id_index (user_id),
+ index filter_id_index (filter_id),
+ PRIMARY KEY (user_id, filter_id),
+ FOREIGN KEY user_id_fk (user_id)
+ REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
+);
+
DROP TABLE IF EXISTS dbmail_subscription;
CREATE TABLE dbmail_subscription (
user_id bigint(21) NOT NULL,
--- ./sql/mysql/create_tables_innoDB.mysql.orig 2005-09-05 18:52:57 +0400
+++ ./sql/mysql/create_tables_innoDB.mysql 2005-09-20 15:17:16 +0400
@@ -73,6 +73,20 @@
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
) TYPE=InnoDB;
+DROP TABLE IF EXISTS dbmail_filters;
+CREATE TABLE dbmail_filters (
+ user_id bigint(21) not null default '0',
+ filter_id bigint(21) not null default '0',
+ filter_field varchar(128) NOT NULL default '',
+ filter_value varchar(255) NOT NULL default '',
+ mailbox varchar(100) NOT NULL default '',
+ index user_id_index (user_id),
+ index filter_id_index (filter_id),
+ PRIMARY KEY (user_id, filter_id),
+ FOREIGN KEY user_id_fk (user_id)
+ REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE
+) TYPE=InnoDB;
+
DROP TABLE IF EXISTS dbmail_subscription;
CREATE TABLE dbmail_subscription (
user_id bigint(21) not null default '0',
--- ./sql/postgresql/create_tables.pgsql.orig 2005-09-05 18:52:57 +0400
+++ ./sql/postgresql/create_tables.pgsql 2005-09-26 15:29:37 +0400
@@ -67,6 +67,17 @@
CREATE UNIQUE INDEX dbmail_mailboxes_owner_name_idx
ON dbmail_mailboxes(owner_idnr, name);
+CREATE TABLE dbmail_filters (
+ user_id INT8 REFERENCES dbmail_users(user_idnr) ON DELETE CASCADE ON UPDATE CASCADE,
+ filter_id INT8,
+ filter_field varchar(128) NOT NULL,
+ filter_value varchar(255) NOT NULL,
+ mailbox varchar(100) NOT NULL,
+ PRIMARY KEY (user_id, filter_id)
+);
+CREATE INDEX dbmail_user_id_idx ON dbmail_filters(user_id);
+CREATE INDEX dbmail_filter_id_idx ON dbmail_filters(filter_id);
+
CREATE TABLE dbmail_subscription (
user_id INT8 REFERENCES dbmail_users(user_idnr) ON DELETE CASCADE ON UPDATE CASCADE,
mailbox_id INT8 REFERENCES dbmail_mailboxes(mailbox_idnr)
--- ./db.c.orig 2005-08-19 18:30:42 +0400
+++ ./db.c 2005-09-21 09:43:39 +0400
@@ -524,6 +524,72 @@
return 1;
}
+char *db_get_mailbox_from_filters(u64_t useridnr, struct list *headerfields, const char *mailbox)
+{
+ trace(TRACE_MESSAGE, %s, %s: default mailbox [%s], __FILE__, __func__, mailbox);
+
+ if (mailbox == NULL)
+ {
+ unsigned i = 0;
+ unsigned num_filters = 0;
+
+ snprintf(query, DEF_QUERYSIZE,
+ SELECT filter_field, filter_value, mailbox FROM dbmail_filters WHERE user_id = '%llu' ORDER BY filter_id,
+ useridnr);
+
+ if (db_query(query) == -1) {
+ trace(TRACE_ERROR, %s,%s: error gettings filters for
+user_id [%llu], __FILE__, __func__,
+useridnr);
+return NULL;
+ }
+
+ num_filters = db_num_rows();
+ for (i = 0; i num_filters; i++) {
+
+ struct element *el = list_getstart(headerfields);
+ char *filter_field = db_get_result(i, 0);
+ char *filter_value = db_get_result(i, 1);
+ char *mailbox = db_get_result(i, 2);
+
+ trace(TRACE_MESSAGE,
+%s, %s: processing filter [%s : \%s\ = %s],
+__FILE__, __func__, filter_field, filter_value, mailbox);
+
+ while (el) {
+struct mime_record *record = (struct mime_record *) el-data;
+
+trace(TRACE_MESSAGE,
+ %s, %s: processing header [%s : \%s\],
+ __FILE__, __func__, record-field, record-value);
+
+if (!strcmp(record-field, filter_field) strstr(record-value, filter_value)) {
+
+ trace(TRACE_MESSAGE,
+ %s, %s: header [%s : \%s\] accept filter [%s : \%s\ = %s],
+ __FILE__, __func__, record-field, record-value, filter_field, filter_value, mailbox);
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Eugene Prokopiev wrote:
Ok. But without postgres support, I can't release 2.0.8 *with* this
patch.
new patch version with postgres support is attached
it's seems to work for me on my simple postgres installation :)
Did you change anything other than the postgres sql? Because for
instance this:
+ for (i = 0; i num_filters; i++) {
+
+ struct element *el = list_getstart(headerfields);
+ char *filter_field = db_get_result(i, 0);
+ char *filter_value = db_get_result(i, 1);
+ char *mailbox = db_get_result(i, 2);
is not really clean code afaik.
--- ./pipe.c.orig 2005-08-19 18:30:42 +0400
+++ ./pipe.c 2005-09-20 13:04:08 +0400
@@ -574,8 +574,9 @@
trace(TRACE_DEBUG,
%s, %s: calling sort_and_deliver for useridnr
[%llu],
__FILE__, __func__, useridnr);
-
- dsn_result = sort_and_deliver(tmpmsgidnr, msgsize,
useridnr, delivery-mailbox);
+
+ dsn_result = sort_and_deliver(tmpmsgidnr, msgsize,
useridnr,
+ db_get_mailbox_from_filters(useridnr,
headerfields, delivery-mailbox));
switch (dsn_result) {
case DSN_CLASS_OK:
--- ./sql/mysql/create_tables.mysql.orig 2005-08-19 18:30:42 +0400
+++ ./sql/mysql/create_tables.mysql 2005-09-20 15:16:54 +0400
@@ -61,6 +61,20 @@
UNIQUE INDEX owner_idnr_name_index (owner_idnr, name)
);
+DROP TABLE IF EXISTS dbmail_filters;
+CREATE TABLE dbmail_filters (
+ user_id bigint(21) not null default '0',
+ filter_id bigint(21) not null default '0',
+ filter_field varchar(128) NOT NULL default '',
+ filter_value varchar(255) NOT NULL default '',
+ mailbox varchar(100) NOT NULL default '',
+ index user_id_index (user_id),
+ index filter_id_index (filter_id),
+ PRIMARY KEY (user_id, filter_id),
+ FOREIGN KEY user_id_fk (user_id)
+ REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE
+);
+
DROP TABLE IF EXISTS dbmail_subscription;
CREATE TABLE dbmail_subscription (
user_id bigint(21) NOT NULL,
--- ./sql/mysql/create_tables_innoDB.mysql.orig 2005-09-05 18:52:57
+0400
+++ ./sql/mysql/create_tables_innoDB.mysql2005-09-20 15:17:16 +0400
@@ -73,6 +73,20 @@
REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE
) TYPE=InnoDB;
+DROP TABLE IF EXISTS dbmail_filters;
+CREATE TABLE dbmail_filters (
+ user_id bigint(21) not null default '0',
+ filter_id bigint(21) not null default '0',
+ filter_field varchar(128) NOT NULL default '',
+ filter_value varchar(255) NOT NULL default '',
+ mailbox varchar(100) NOT NULL default '',
+ index user_id_index (user_id),
+ index filter_id_index (filter_id),
+ PRIMARY KEY (user_id, filter_id),
+ FOREIGN KEY user_id_fk (user_id)
+ REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE
CASCADE
+) TYPE=InnoDB;
+
DROP TABLE IF EXISTS dbmail_subscription;
CREATE TABLE dbmail_subscription (
user_id bigint(21) not null default '0',
--- ./sql/postgresql/create_tables.pgsql.orig 2005-09-05 18:52:57 +0400
+++ ./sql/postgresql/create_tables.pgsql 2005-09-26 15:29:37 +0400
@@ -67,6 +67,17 @@
CREATE UNIQUE INDEX dbmail_mailboxes_owner_name_idx
ON dbmail_mailboxes(owner_idnr, name);
+CREATE TABLE dbmail_filters (
+ user_id INT8 REFERENCES dbmail_users(user_idnr) ON DELETE CASCADE ON
UPDATE CASCADE,
+ filter_id INT8,
+ filter_field varchar(128) NOT NULL,
+ filter_value varchar(255) NOT NULL,
+ mailbox varchar(100) NOT NULL,
+ PRIMARY KEY (user_id, filter_id)
+);
+CREATE INDEX dbmail_user_id_idx ON dbmail_filters(user_id);
+CREATE INDEX dbmail_filter_id_idx ON dbmail_filters(filter_id);
+
CREATE TABLE dbmail_subscription (
user_id INT8 REFERENCES dbmail_users(user_idnr) ON DELETE CASCADE ON
UPDATE CASCADE,
mailbox_id INT8 REFERENCES dbmail_mailboxes(mailbox_idnr)
--- ./db.c.orig 2005-08-19 18:30:42 +0400
+++ ./db.c2005-09-21 09:43:39 +0400
@@ -524,6 +524,72 @@
return 1;
}
+char *db_get_mailbox_from_filters(u64_t useridnr, struct list *headerfields,
const char *mailbox)
+{
+ trace(TRACE_MESSAGE, %s, %s: default mailbox [%s], __FILE__,
__func__, mailbox);
+
+ if (mailbox == NULL)
+ {
+ unsigned i = 0;
+ unsigned num_filters = 0;
+
+ snprintf(query, DEF_QUERYSIZE,
+ SELECT filter_field, filter_value,
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Paul J Stevens wrote:
Eugene Prokopiev wrote:
Ok. But without postgres support, I can't release 2.0.8 *with* this
patch.
new patch version with postgres support is attached
it's seems to work for me on my simple postgres installation :)
Did you change anything other than the postgres sql?
No. All works without any changes.
Because for instance this:
+ for (i = 0; i num_filters; i++) {
+
+ struct element *el = list_getstart(headerfields);
+ char *filter_field = db_get_result(i, 0);
+ char *filter_value = db_get_result(i, 1);
+ char *mailbox = db_get_result(i, 2);
is not really clean code afaik.
what are the problems with this code?
--
Regards,
Eugene Prokopiev
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Eugene Prokopiev wrote: Paul J Stevens wrote: Eugene, I've included your patch as a dpatch in the debian packages. I fixed a couple of compiler warnings in db_get_mailbox_from_filters, but other than that, things are unchanged. Did you move db_get_mailbox_from_filters function into sort_and_delivery as you wish? Nope. For that the headers struct needs to be passed into sort_and_deliver, which I ain't gonna change in the stable series. This still needs a postgres version of the table. yes, but I didn't work with it, so I can only write sql code and can't test it. Ok. But without postgres support, I can't release 2.0.8 *with* this patch. I've only done a debian/unstable upload. Stable will follow when 2.0.8 is released. Will my patch be included in dbmail-2.0.8.tar.gz archive? When 2.0.8 will be done? It'll be in the archive, because the debian code is in the archive. And 2.0.8 will be done, if and when it's done. There are still a couple of bugs that need resolving first. -- Paul Stevens mailto:[EMAIL PROTECTED] NET FACILITIES GROUP PGP: finger [EMAIL PROTECTED] The Netherlandshttp://www.nfg.nl
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Eugene, I've included your patch as a dpatch in the debian packages. I fixed a couple of compiler warnings in db_get_mailbox_from_filters, but other than that, things are unchanged. This still needs a postgres version of the table. I've only done a debian/unstable upload. Stable will follow when 2.0.8 is released. Eugene Prokopiev wrote: Steven Lynn wrote: I was able to compile it in with your patch and I added the table to the database. However, I think I am doing something wrong because cause I can not seem to get it to filter. I turned up TRACE_LEVEL (5) but not sure if I have it up high enough because I do not even see your code executing... Can you fix the example below? insert into dbmail_filters values (3, 0, 'Sender', '[EMAIL PROTECTED]', 'INBOX/Test'); Let me know if there is anything else I can give you. Use attached next patch version. My filter table is: $ mysql dbmail -u dbmail -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 83078 to server version: 4.0.20-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use dbmail; Database changed mysql select * from dbmail_filters; +-+---+--+--+---+ | user_id | filter_id | filter_field | filter_value | mailbox | +-+---+--+--+---+ | 5 | 0 | Subject | Test | MyF1/MyF3 | +-+---+--+--+---+ 1 row in set (0.04 sec) On sending mail: $ echo hello | mail -s Test Message [EMAIL PROTECTED] I have in syslog: Sep 21 09:44:51 ebr-app postfix/pickup[21858]: 23D8B3BF2: uid=500 from=john Sep 21 09:44:51 ebr-app postfix/cleanup[6030]: 23D8B3BF2: message-id=[EMAIL PROTECTED] Sep 21 09:44:51 ebr-app postfix/qmgr[11796]: 23D8B3BF2: from=[EMAIL PROTECTED], size=326, nrcpt=1 (queue active) Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: serverchild.c,PerformChildTask: incoming connection from [127.0.0.1 (localhost.localdomain)] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: default mailbox [(null)] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing filter [Subject : Test = MyF1/MyF3] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [From : [EMAIL PROTECTED]] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Date : Wed, 21 Sep 2005 09:44:51 +0400 (MSD)] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Message-Id : [EMAIL PROTECTED]] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Subject : Test Message] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: header [Subject : Test Message] accept filter [Subject : Test = MyF1/MyF3] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: sort.c, sort_and_deliver: message id=152, size=358 is inserted Sep 21 09:44:51 ebr-app postfix/lmtp[6032]: 23D8B3BF2: to=[EMAIL PROTECTED], relay=localhost[127.0.0.1], delay=0, status=sent (215 Recipient [EMAIL PROTECTED] OK) On sending mail: $ echo hello | mail -s Another Message [EMAIL PROTECTED] I have in syslog: Sep 21 09:44:57 ebr-app postfix/pickup[21858]: DC5F13BF2: uid=500 from=john Sep 21 09:44:57 ebr-app postfix/cleanup[6030]: DC5F13BF2: message-id=[EMAIL PROTECTED] Sep 21 09:44:57 ebr-app postfix/qmgr[11796]: DC5F13BF2: from=[EMAIL PROTECTED], size=329, nrcpt=1 (queue active) Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: default mailbox [(null)] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing filter [Subject : Test = MyF1/MyF3] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [From : [EMAIL PROTECTED]] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Date : Wed, 21 Sep 2005 09:44:57 +0400 (MSD)] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Message-Id : [EMAIL PROTECTED]] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Subject : Another Message] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [To : [EMAIL PROTECTED]] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Received : by ebr-app.ewsd.donpac.ru (Postfix, from userid 500)^M ^Iid DC5F13BF2; Wed, 21 Sep 2005 09:44:57 +0400 (MSD)] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c,
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Jesse Norell wrote: My filtering algorithm: 1) Read all filtering rules for current user with SELECT filter_field, filter_value, mailbox FROM dbmail_filters WHERE user_id = '%llu' ORDER BY filter_id 2) For every record read all mail headers and compare it with current record in dbmail_filters. 3) Apply destionation mailbox on founding first matching One thing that comes to mind that would be nice (if not absolutely required) is the ability to order the filters, which from a cursory look over the table setup, I don't think exists. I guess if you wanted to reorder 2 filters, you could delete the first and add it in again with a higher filter_id, but it seems a bit awkward; just have an adjustable sort priority and order by that. This idea is already implemented :) Filter records are ordered by filter_id during mail scan. You can change value of this field and so filters will be reordered. A couple other ideas that would be nice, but might complicate things, are: allow multiple entries with the same filter_id and a flag for matching all or any (might need to add a unique index on the filter_id and mailbox to make sure they're always consistent?). Also adding in a layer to pick your filtering type (eg. from dbmail.conf), so you could choose simple dbmail_filter now, and later sieve or others could be added (but that could be added later, when sieve is ready, too). Yes, this things are more complicated, so they will implemented than I need them. I hope my code will merged with mail DBMail source tree so anybody will can use my code or change it. But I haven't answer in dbmail-dev maillist about my patch. I know that adding new features in stable version is not good idea, but this feature is very needed for me and for other people which can't migrate to unstable DBMail version. Not being familiar with the code, does sort_and_deliver() get called during IMAP APPEND's too, or just during smtp/lmtp? I don't know :) I even don't know what is IMAP APPEND :) Can I do this operation in mail client? -- Regards, Eugene Prokopiev
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Eugene Prokopiev wrote: I hope my code will merged with mail DBMail source tree so anybody will can use my code or change it. But I haven't answer in dbmail-dev maillist about my patch. Sometimes I have to make a living :-) I know that adding new features in stable version is not good idea, but this feature is very needed for me and for other people which can't migrate to unstable DBMail version. If this patch works out ok, I'll add it to the debian packages as a dpatch. The main question is: how does this patch work for databases that don't have the new filter table. But I'll have to read the patch first I guess. If it degrades gracefully on existing installs, or doesn't cause any regressions, there's no real reason not to include such added features. Lets see how this works out first. Not being familiar with the code, does sort_and_deliver() get called during IMAP APPEND's too, or just during smtp/lmtp? Just during delivery (smtp/lmtp). I just rewrote the append code in 2.1 last week, so I can tell you for sure it's a different beast all together. sort_and_delivery is indeed the most obvious entry point for sorting. If that breaks stuff like _imap_append, then _imap_append is broken (duh). I don't know :) I even don't know what is IMAP APPEND :) Can I do this operation in mail client? Oh boy. What use is filtered delivery if you don't use imap??? So I assume you do use imap. Append is what happens when you copy messages from a folder in one account to another account or when your client stores a sent message to the Sent folder. -- Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlandshttp://www.nfg.nl
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
I like it. I am probably going to put it on my backup server and try it as a alternative install... Just to try it out. It would save me a lot of trouble with running Evolution and Filters. I am no dev but i think it looks good. One idea, what about using an enum on the filter_field column instead of a varchar. This might help to have to put 'subject', 'recipent', 'sender', etc. into the column... Just my thoughts... On Tue, 2005-09-20 at 15:28 +0400, Eugene Prokopiev wrote: Hi, There are no suitable way for mail filtering with DBMail. Sieve support is not stable and excluded by default, procmail will slow perfomance by invoking another process ... So, I write very simple filtering engine for DBMail. It's attached and based on this new table: CREATE TABLE dbmail_filters ( user_id bigint(21) not null default '0', filter_id bigint(21) not null default '0', filter_field varchar(128) NOT NULL default '', filter_value varchar(255) NOT NULL default '', index user_id_index (user_id), index filter_id_index (filter_id), PRIMARY KEY (user_id, filter_id), FOREIGN KEY user_id_fk (user_id) REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; If mail message field value contains value of filter_value database field it will be moved to folder referenced by mailbox database field. My implementation is not optimal and is not hight perfomance. It's seems to work with MySQL, but it's make sense to test it more. It hasn't been tested with PostgreSQL. Is my patch helpful for anybody? What can you say about including it in main source tree of DBMail? -- Regards, Eugene Prokopiev ___ Dbmail mailing list Dbmail@dbmail.org https://mailman.fastxs.nl/mailman/listinfo/dbmail signature.asc Description: This is a digitally signed message part
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Steven Lynn wrote: I was able to compile it in with your patch and I added the table to the database. However, I think I am doing something wrong because cause I can not seem to get it to filter. I turned up TRACE_LEVEL (5) but not sure if I have it up high enough because I do not even see your code executing... Can you fix the example below? insert into dbmail_filters values (3, 0, 'Sender', '[EMAIL PROTECTED]', 'INBOX/Test'); Let me know if there is anything else I can give you. Use attached next patch version. My filter table is: $ mysql dbmail -u dbmail -p Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 83078 to server version: 4.0.20-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use dbmail; Database changed mysql select * from dbmail_filters; +-+---+--+--+---+ | user_id | filter_id | filter_field | filter_value | mailbox | +-+---+--+--+---+ | 5 | 0 | Subject | Test | MyF1/MyF3 | +-+---+--+--+---+ 1 row in set (0.04 sec) On sending mail: $ echo hello | mail -s Test Message [EMAIL PROTECTED] I have in syslog: Sep 21 09:44:51 ebr-app postfix/pickup[21858]: 23D8B3BF2: uid=500 from=john Sep 21 09:44:51 ebr-app postfix/cleanup[6030]: 23D8B3BF2: message-id=[EMAIL PROTECTED] Sep 21 09:44:51 ebr-app postfix/qmgr[11796]: 23D8B3BF2: from=[EMAIL PROTECTED], size=326, nrcpt=1 (queue active) Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: serverchild.c,PerformChildTask: incoming connection from [127.0.0.1 (localhost.localdomain)] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: default mailbox [(null)] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing filter [Subject : Test = MyF1/MyF3] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [From : [EMAIL PROTECTED]] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Date : Wed, 21 Sep 2005 09:44:51 +0400 (MSD)] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Message-Id : [EMAIL PROTECTED]] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Subject : Test Message] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: header [Subject : Test Message] accept filter [Subject : Test = MyF1/MyF3] Sep 21 09:44:51 ebr-app dbmail/lmtpd[5978]: sort.c, sort_and_deliver: message id=152, size=358 is inserted Sep 21 09:44:51 ebr-app postfix/lmtp[6032]: 23D8B3BF2: to=[EMAIL PROTECTED], relay=localhost[127.0.0.1], delay=0, status=sent (215 Recipient [EMAIL PROTECTED] OK) On sending mail: $ echo hello | mail -s Another Message [EMAIL PROTECTED] I have in syslog: Sep 21 09:44:57 ebr-app postfix/pickup[21858]: DC5F13BF2: uid=500 from=john Sep 21 09:44:57 ebr-app postfix/cleanup[6030]: DC5F13BF2: message-id=[EMAIL PROTECTED] Sep 21 09:44:57 ebr-app postfix/qmgr[11796]: DC5F13BF2: from=[EMAIL PROTECTED], size=329, nrcpt=1 (queue active) Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: default mailbox [(null)] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing filter [Subject : Test = MyF1/MyF3] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [From : [EMAIL PROTECTED]] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Date : Wed, 21 Sep 2005 09:44:57 +0400 (MSD)] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Message-Id : [EMAIL PROTECTED]] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Subject : Another Message] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [To : [EMAIL PROTECTED]] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Received : by ebr-app.ewsd.donpac.ru (Postfix, from userid 500)^M ^Iid DC5F13BF2; Wed, 21 Sep 2005 09:44:57 +0400 (MSD)] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: processing header [Return-Path : [EMAIL PROTECTED]] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: db.c, db_get_mailbox_from_filters: no header accept filter [Subject : Test = MyF1/MyF3] Sep 21 09:44:58 ebr-app dbmail/lmtpd[5978]: sort.c, sort_and_deliver: message id=154, size=361 is inserted Sep 21 09:44:58 ebr-app postfix/lmtp[6032]: DC5F13BF2: to=[EMAIL PROTECTED],
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
Steven Lynn wrote: I like it. I am probably going to put it on my backup server and try it as a alternative install... Just to try it out. It would save me a lot of trouble with running Evolution and Filters. I am no dev but i think it looks good. One idea, what about using an enum on the filter_field column instead of a varchar. This might help to have to put 'subject', 'recipent', 'sender', etc. into the column... Just my thoughts... Why do you heed to put some values into one column? You can use some filtering records for one user instead of it. My filtering algorithm: 1) Read all filtering rules for current user with SELECT filter_field, filter_value, mailbox FROM dbmail_filters WHERE user_id = '%llu' ORDER BY filter_id 2) For every record read all mail headers and compare it with current record in dbmail_filters. 3) Apply destionation mailbox on founding first matching -- Regards, Eugene Prokopiev
Re: [Dbmail] Mail filtering patch for DBMail 2.0.7
My filtering algorithm: 1) Read all filtering rules for current user with SELECT filter_field, filter_value, mailbox FROM dbmail_filters WHERE user_id = '%llu' ORDER BY filter_id 2) For every record read all mail headers and compare it with current record in dbmail_filters. 3) Apply destionation mailbox on founding first matching One thing that comes to mind that would be nice (if not absolutely required) is the ability to order the filters, which from a cursory look over the table setup, I don't think exists. I guess if you wanted to reorder 2 filters, you could delete the first and add it in again with a higher filter_id, but it seems a bit awkward; just have an adjustable sort priority and order by that. A couple other ideas that would be nice, but might complicate things, are: allow multiple entries with the same filter_id and a flag for matching all or any (might need to add a unique index on the filter_id and mailbox to make sure they're always consistent?). Also adding in a layer to pick your filtering type (eg. from dbmail.conf), so you could choose simple dbmail_filter now, and later sieve or others could be added (but that could be added later, when sieve is ready, too). Not being familiar with the code, does sort_and_deliver() get called during IMAP APPEND's too, or just during smtp/lmtp? -- Jesse Norell - [EMAIL PROTECTED] Kentec Communications, Inc.
