Reading and managing email boxes (Experimental)

The IMAP adapter is intended as an interface with email IMAP servers to perform simple queries in the web2py DAL query syntax, so email read, search and other related IMAP mail services (as those implemented by brands like Google(r), and Yahoo(r) can be managed from web2py applications.

It creates its table and field names “statically”, meaning that the developer should leave the table and field definitions to the DAL instance by calling the adapter .define_tables() method. The tables are defined with the IMAP server mailbox list information.

Connection

For a single mail account, this is the code recommended to start IMAP support at the app’s model

# Replace user, password, server and port in the connection string
# Set port as 993 for SSL support
imapdb = DAL("imap://user:password@server:port", pool_size=1)
imapdb.define_tables()

Note that <imapdb>.define_tables() returns a dictionary of strings mapping DAL tablenames to the server mailbox names with the structure {<tablename>: <server mailbox name>, ...}, so you can get the actual mailbox name in the IMAP server.

If you want to set you own tablename/mailbox configuration and skip the automatic name configuration, you can pass a custom dictionary to the adapter in this way:

imapdb.define_tables({"inbox":"MAILBOX", "trash":"SPAM"})

To handle the different native mailbox names for the user interface, the following attributes give access to the adapter auto mailbox mapped names (which native mailbox has what table name and vice versa):

The first can be useful to retrieve IMAP query sets by the native email service mailbox

# mailbox is a string containing the actual mailbox name
tablenames = dict([(v, k) for k, v in imapdb.mailboxes.items()])
myset = imapdb(imapdb[tablenames[mailbox]])

Fetching mail and updating flags

Here’s a list of IMAP commands you could use in the controller. For the examples, it’s assumed that your IMAP service has a mailbox named INBOX, which is the case for Gmail(r) accounts.

To count today’s unseen messages smaller than 6000 octets from the inbox mailbox do

q = imapdb.INBOX.seen == False
q &= imapdb.INBOX.created == request.now.date()
q &= imapdb.INBOX.size < 6000
unread = imapdb(q).count()

You can fetch the previous query messages with

rows = imapdb(q).select()

Usual query operators are implemented, including belongs

messages = imapdb(imapdb.INBOX.uid.belongs(<uid sequence>)).select()

Note: It’s strongly advised that you keep the query results below a given data size threshold to avoid jamming the server with large select commands.

To perform faster email queries, it is recommended to pass a filtered set of fields:

fields = ["INBOX.uid", "INBOX.sender", "INBOX.subject", "INBOX.created"]
rows = imapdb(q).select(*fields)

The adapter knows when to retrieve partial message payloads (fields like content, size and attachments require retrieving the complete message data)

It is possible to filter query select results with limitby and sequences of mailbox fields

# Replace the arguments with actual values
myset.select(<fields sequence>, limitby=(<int>, <int>))

Say you want to have an app action show a mailbox message. First we retrieve the message (If your IMAP service supports it, fetch messages by uid field to avoid using old sequence references).

mymessage = imapdb(imapdb.INBOX.uid == <uid>).select().first()

Otherwise, you can use the message’s id.

mymessage = imapdb.INBOX[<id>]

Note that using the message’s id as reference is not recommended, because sequence numbers can change with mailbox maintenance operations as message deletions. If you still want to record references to messages (i.e. in another database’s record field), the solution is to use the uid field as reference whenever supported, and retrieve each message with the recorded value.

Finally, add something like the following to show the message content in a view

{{=P(T("Message from"), " ", mymessage.sender)}}
{{=P(T("Received on"), " ", mymessage.created)}}
{{=H5(mymessage.subject)}}
{{for text in mymessage.content:}}
  {{=DIV(text)}}
  {{=TR()}}
{{pass}}

As expected, we can take advantage of the SQLTABLE helper to build message lists in views

{{=SQLTABLE(myset.select(), linkto=URL(...))}}

And of course, it’s possible to feed a form helper with the appropriate sequence id value

{{=SQLFORM(imapdb.INBOX, <message id>, fields=[...])}}

The current adapter supported fields available are the following:

*At the application side it is measured as the length of the RFC822 message string

WARNING: As row id’s are mapped to email sequence numbers, make sure your IMAP client web2py app does not delete messages during select or update actions, to prevent updating or deleting different messages.

Standard CRUD database operations are not supported. There’s no way of defining custom fields or tables and make inserts with different data types because updating mailboxes with IMAP services is usually reduced to posting flag updates to the server. Still, it’s possible to access those flag commands through the DAL IMAP interface

To mark last query messages as seen

seen = imapdb(q).update(seen=True)

Here we delete messages in the IMAP database that have mails from mr. Gumby

deleted = 0
for tablename in imapdb.tables():
    deleted += imapdb(imapdb[tablename].sender.contains("gumby")).delete()

It is possible also to mark messages for deletion instead of erasing them directly with

myset.update(deleted=True)