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
--- ./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',
--- ./db.c.orig 2005-08-19 18:30:42 +0400
+++ ./db.c 2005-09-20 14:51:26 +0400
@@ -524,6 +524,58 @@
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: filter [%s : \"%s\" => %s]",
+ __FILE__, __func__, filter_field, filter_value, mailbox);
+
+ while (el) {
+ struct mime_record *record = (struct mime_record *) el->data;
+
+ if (!strcmp(record->field, filter_field) && strstr(record->value, filter_value))
+ return mailbox;
+
+ el = el->nextnode;
+ }
+ }
+
+ db_free_result();
+
+ return NULL;
+ }
+ else
+ {
+ return NULL;
+ }
+}
+
char *db_get_deliver_from_alias(const char *alias)
{
char *escaped_alias;
--- ./db.h.orig 2005-08-19 18:30:42 +0400
+++ ./db.h 2005-09-20 13:23:02 +0400
@@ -349,6 +349,14 @@
* - deliver_to address otherwise
* \attention caller needs to free the return value
*/
+/[EMAIL PROTECTED]@*/ char *db_get_mailbox_from_filters(u64_t useridnr, struct list *headerfields, const char *mailbox);
+/**
+ * \brief get a mailbox for a user's user_idnr, default mailbox and filter table
+ * \param user_idnr idnr of user
+ * \param headerfields header fields
+ * \param mailbox default mailbox name
+ * \return mailbox name
+ */
/[EMAIL PROTECTED]@*/ char *db_get_deliver_from_alias(const char *alias);
/**
* \brief get a list of aliases associated with a user's user_idnr
@@ -360,7 +368,7 @@
* - 0 on success
* \attention aliases list needs to be empty. Method calls list_init()
* which sets list->start to NULL.
- */
+ */
int db_get_user_aliases(u64_t user_idnr, struct list *aliases);
/**
* \brief add an alias for a user
