Sort letters by imap-folders on the mail server without using sieve and others like them

Mail in its office has grown and the presence of more than 1 workplace for more than 1 employee began to require more than the simplest implementation of a multi-domain mail server based on exim + teapop without using mysql on ordinary files. The main reason for the changes - the constant cleansing of spam and simply unnecessary letters at each of the workplaces began to get out many times, and it was decided to implement the same mail according to the famous article “Linking Exim and courier-imap ”. Most importantly, what it lacked from what was needed was a letter sorter by folders. For those who subscribe to mailing lists, it is very useful to lay out letters in the required directories in order to: a) not write sorters in each client; b) have the same folder structure when accessing mail via the web, as in the mail client.



A quick (and rather persistent) Internet research showed that sorting letters is offered only with the help of various sieve scripts. But, in my opinion, this is not a way for users (as in email clients), but for administrators who will not actually write rules for the sorter, but program filters using the appropriate scripting language:

 require ["fileinto", "reject", "regex", "subaddress", "envelope"]; #spam if header :contains "Subject" "[SPAM]" { reject "thank you"; stop; } if header :regex "Received" "from ([[:alnum:]\\-]{1,}\\.){3,}[[:alnum:]]{1,}.*" { reject "thank you"; stop; } 

I can figure it out, but why the hell, sorry, damn it? And what to do with "mere mortals" who need the same functionality, but for which the above mentioned is akin to Chinese literacy. Nowadays, when systems are honed under the finger of a housewife, the lack of the same stupid mail sorting methods that have been used for decades is at least surprising.

')

I was pushed onto my own sorting implementation by a fragment of the above article, in which the path for placing an Exim letter in the transports section forms a query from the database:

 mysql_delivery: driver = appendfile check_string = "" create_directory delivery_date_add directory = ${lookup mysql{SELECT CONCAT('/data/mail/${domain}/', `maildir`) \ FROM `mailbox` WHERE `username`='${local_part}@${domain}'}} directory_mode = 770 envelope_to_add group = mail maildir_format maildir_tag = ,S=$message_size message_prefix = "" message_suffix = "" mode = 0600 

Nothing prevents you from slightly adding a query and adding the name of the imap folder there so that stupid filters work based on the definition of the content of the substring in the sender / subject of the letter:

 mysql_delivery: driver = appendfile check_string = "" create_directory delivery_date_add directory = ${lookup mysql{SELECT CONCAT('/data/mail/${domain}/', `maildir`, \ (select dir from sorter where ( \ ( \ (locate(text,'${sender_address}')!=0 and target='sender') \ or \ (locate(text,'${sg{${extract{Subject}{${sg{${sg{$message_headers}{ }{_spAce_}}}{:_spAce_}{=}}}}}{_spAce_}{ }}')!=0 and target='subject') \ ) \ and email='${local_part}@${domain}') or priority=0 order by priority desc limit 1 \ )) \ FROM `mailbox` WHERE `username`='${local_part}@${domain}'}} directory_mode = 770 envelope_to_add group = mail maildir_format maildir_tag = ,S=$message_size message_prefix = "" message_suffix = "" mode = 0600 

After that, we write 7 kilos of primitive code in PHP ( download ) to create sorting rules for a specific user and aha - the lethal outcome of the need to sort letters is implemented by 80%. And my personal - 100%. True, it was unexpectedly discovered (or maybe he just didn’t find it?) That at this stage Exim does not have separate variables for each heading, so the hefty beard of Exex line expansions is only intended to separate the Subject heading from the multi-line field containing all the headers at once.



We create a table for storing collation rules with a mandatory zero entry, which will determine the placement of the letter in the Inbox if no rules were found:

 CREATE TABLE `sorter` ( `id` int(11) NOT NULL AUTO_INCREMENT, `priority` int(11) NOT NULL DEFAULT '0', `target` varchar(255) NOT NULL DEFAULT '', `text` varchar(255) NOT NULL DEFAULT '', `dir` varchar(255) NOT NULL DEFAULT '', `email` varchar(255) NOT NULL DEFAULT '', PRIMARY KEY (`id`) ); INSERT INTO `sorter` SET `priority`=0; 

Admin Screenshot:





Scripts take a list of folders already available from the user on the server.



The system is not ideal, and has at least three limitations:

1. works with Russian encodings only if the encoding of the letter matches the encoding of the entries in the table of rules;

2. only one rule works in order of priority (the higher, the more priority), so the rules with logic AND will not work, only OR;

3. The number of fields for processing is limited by the sender and subject.



In principle, it will be possible then to finish it for use for selections and other headings, and not only subject, maybe I will, if need be. There was also an idea to write a plugin for RoundCube, but looking at how they are written, it was decided to score on it, albeit with great pity (maybe someone who is interested in the implementation will do it: smile :). I also want to note that the sql query was tested on the latest version of the MySQL 5.5 port for FreeBSD, so it is not known whether this query will roll for older versions.