Decimail Server

The Actions Framework

1. Introduction

Decimail Server provides a powerful way to make messages stored in a relational database visible in an email client. But to make this useful communication in the other direction - from the email client to the database system - is also required. This is necessary so that new queries defining new mailboxes can be created interactively. The actions framework addresses this need.

This is best explained with an example. I might want to define a mailbox that shows all messages from a particular correspondent. If ncessary I could write an SQL query matching the correspondent's email address and add it to the system manually. This offers maximum flexibility but it is far from a user-friendly interface. Instead, with the actions framework, I can use my email client's "create mailbox" mechanism to create a new mailbox with a suitable name, and I can then copy (using drag-and-drop, for example) one example message from this correspondent to the new folder. Decimail then creates a suitable query to select all messages with the same address, and the folder is populated with all messages from that particular address, automatically.

But that's just one example. The point about the actions framework is that it is a general-purpose mechanism. In that example, particular actions suitable for per-correspondent mailboxes were triggered by "create mailbox" and "copy message to mailbox", but other actions for other applications can be added, without changing the main Decimail code.

2. The actions Table

The actions framework is centered on a database table called actions. The key for this table is a mailbox name pattern. Typically this pattern will match a portion of the mailbox name hierarchy; for example the pattern "People/%" matches all mailboxes under the People folder.

Each entry in the table defines what action should occur when the email client requests that a mailbox with a matching name is created, deleted or renamed, or when a message is copied into it. These actions are themselves SQL queries. So, when the user asks to create new new mailbox an arbitary piece of SQL can be executed.

3. Variable Substitutions

The string stored in the actions table is subjected to variable substitution prior to being executed, as follows:

Action	Variable	Description
create	$U	Username
	$MB	Mailbox name
delete	$U	Username
	$MB	Mailbox name
rename	$U	Username
	$OMB	Old mailbox name
	$NMB	New mailbox name
copy	$U	Username
	$MB	Mailbox name
	$MSG_ID	Copied message ID

4. Example: Per-Correspondent Mailboxes

The distribution includes an example application where per-correspondent mailboxes can be created under the People folder in the mailbox hierarchy.

This application is defined in actions/people.sql. This file defines the tables to record what per-correspondent mailboxes have been created, defines views so that appropriate rows appear in the main mailboxes table, and adds a row to the actions table to enable the application. The queries that are added are as follows:

Action	Query
create	insert into people (username,name) values ('$U',replace('$MB','People/',''))
delete	delete from people where username='$U' and name=replace('$MB','People/','')
rename	update people set name=replace('$NMB','People/','') where username='$U' and name=replace('$OMB','People/','')
copy	insert into people_addresses select '$U' as username, replace('$MB','People/','') as name, from_addr as email from messages where msg_id=$MSG_ID

These queries are slightly obfuscated by the need to extract the portion of the mailbox name after the "People/" prefix, and they are further obfuscated by extra quoting in the actual source file. But apart from that they are straightforward: the create, delete and rename queries just manipulate the people table, which has one row per per-correspondent mailbox, in the obvious ways. The copy query adds email addresses to the people_addresses table. Using a separate table for the addresses means that a correspondent with more than one email address have have all of their messages presented together.

Note that there is currently a bootstraping issue; it's necessary to manually define the first mailbox since otherwise the People folder is not shown at all. See the comment in create_people.sql for details.

In use, the application is straightforward. To create a folder for all messages from Joe Bloggs, just create a new mailbox under the "People" folder called "Joe Bloggs". Then copy one message from Joe Bloggs into that mailbox. (Note that it's important to copy the message rather than moving it. Moving a message is implemented by copying and then deleting the original; in Decimail, deleting a message in one view deletes it from all, which is not what you want! In Thunderbird you need to hold down Ctrl while dragging to copy rather than move.) You should then see the mailbox populate with all the messages that you've ever received from Joe.

5. Example: Labelling Messages

A common requirement is to add arbitrary labels such as "must reply soon" or "jokes" to messages. An actions framework application makes this possible under the "Labels" folder hierarchy. One table, labels, records the names of all of the labels that have been defined, and a second, labeled_messages, records which labels have been applied to which messages. The actions table entries to support this are as follows:

Action	Query
create	insert into labels (username,label) values ('$U',replace('$MB','Labels/',''))
delete	delete from labels where username='$U' and label=replace('$MB','Labels/','')
rename	update labels set label=replace('$NMB','Labels/','') where username='$U' and label=replace('$OMB','Labels/','')'
copy	insert into labeled_messages(username,label,msg_id) values ('$U', replace('$MB','Labels/',''), $MSG_ID)

In use you can simply create a new folder under "Labels/" with a suitable name, e.g. "Urgent". Then copy messages into the folder to add the label.

A misfeature is that at present there is no way to remove labels! I have not yet thought of a good way to implement this - perhaps a special mailbox called "Labels/none" is needed, and messages copied into it lose their other labels? Alternative suggestions are welcome.

6. Further Examples

The other example actions-framework applications are currently:

• Per-domain mailboxes, i.e. grouping all messages from correspondents whose email addresses are within a particular domain. The domain is taken from the name of the mailbox.
• Subject search mailboxes, which uses the Postgresql text search capability to group all messages whose subject lines contain a particular keyword or keywords.
• Mailing lists. These work as follows: first create a mailbox under Lists/ with a suitable name. Then copy one message to the list distribution address into this folder. All messages to or cc that address will be included in the folder. It will also define a custom address for outgoing messages to the list; you'll probably want to change this, see the insert rule in actions/lists.sql for details.
• Spam. You can define "black hole" spam mailboxes under Spam/ which never have any messages in them if you open them. When you copy a message into one of these mailboxes it to address is stored in the table spam_addresses. You can then use this table as the basis of some additional spam filtering; I have two spam mailboxes, one for addresses that should be filtered and another for addresses that should be entirely discarded.
• Keywords. You define mailboxes under Keywords/ whose names are tsearch2 expressions (see the tsearch2 documentation for details). These are matched against a tsearch2 index of message contents that is updated as new messages are received.

More examples may follow. Your suggestions and contributions are welcome.