Design and Implementation of a Web-based Email System

by Jin Choi (jsc@arsdigita.com)

Submitted on: 2000-01-01
Last updated: 2000-09-01

ArsDigita : ArsDigita Systems Journal : One article

---

Every program attempts to expand until it can read mail.
Those programs which cannot so expand are replaced by ones that can.
-- Third Law of Software Envelopment

Webmail is a module of the ArsDigita Community System that enables Web, POP3, and IMAP access to email messages stored in an Oracle database.

Motivation

Email handlers are among the first user-level programs written for any new operating system, and are one of the few core tools that almost anyone using a computer will use on a regular basis. Most recently, we have seen a blossoming of Web-based email systems such as Hotmail and Yahoo Mail. Why build yet another mail system?

Some of the desirable traits of a mail system are:

• Centralized storage. Users should see the same email history every time they check email, no matter which computer or email reader they happen to be using.
• Reliability. Email is important. A disk failure or a negligent sysadmin should not be a cause for losing it. The mail server should always be running, and well-connected to the internet.
• Availability. Email should be readable wherever you are.
• Completeness and correctness. An email reader should be able to receive, display, and send attachments. Any message it sends should be standards-conforming. Because many other systems are not, it should be able to handle common deviations from the standard.

The webmail module addresses the first three traits (the last is a work in progress). These requirements argue for the primary message store to remain on a well-administered server. These are the same needs addressed by the designers of IMAP. IMAP solves all these issues except for one: availability; an IMAP client isn't always installed on every computer with a net connection, whereas a Web browser almost always is. But a Web browser is a less-than-ideal interface for reading email when compared to an all-singing, all-dancing mail client. Thus, the ideal mail solution is an IMAP server with a web interface that accesses the same message store as the IMAP client.

Mail systems with this architecture already exist. Oracle provides software that does what the webmail module does, and probably more. CriticalPath is a company that provides out-sourced email with IMAP and web front ends. These may be better than webmail. CriticalPath certainly has the advantage that it requires no effort on the part of the user other than sending them a check every once in a while. However, when I used CriticalPath, it was unreachable or unuseably slow about half the time (usually due to network problems to their server). I ran out of patience attempting to install Oracle Email Server.

The downside to these other systems is lack of control. It is difficult to modify the look or extend the features of an email server without access to the source code. In the case of CriticalPath, you are stuck with what they provide, and cannot integrate it to provide web-based email as a seamless service of your web site. If you are using the ArsDigita Community System, webmail provides the core of a web-based email system that relies on proven, reliable systems to do all of the hard work and that is simple to extend. If you are not using the ACS, then perhaps studying an implementation of a working system will aid you in building something suitable for your own needs.

Design Goals

In writing webmail, I had the following goals:

Avoid writing code unnecessarily. Mail transfer agents are responsible for the reliable sending, receipt, and delivery of email in the face of network outages, full disks, and hardware failures. This is a solved problem; other people have solved it and solved it well, with MTAs that go to heroic lengths to preserve email. Similarly, parsing RFC822 and MIME messages correctly is something other people have written libraries for.

Make it easy for a human to handle large amounts of email. I receive more mail than most people. Much of it is from automated email alerts and monitors. I don't want to stop receiving them, but it is rare that I actually care to read them. Most web-based email interfaces seem to be designed with the notion that you actually care to read the email you receive. Webmail was designed to facilitate dealing with email in bulk.

Implementation

The Big Picture

Webmail is based on these tools:

• Qmail (http://www.qmail.org/), a robust mail transfer agent written by Dan Bernstein. It was designed to be simple, modular, secure, and reliable. It is easy to understand, and easy to integrate with other programs.
• Oracle RDBMS (http://www.oracle.com).
• JavaSoft's JavaMail (http://java.sun.com/products/javamail/) and Oracle's SQLJ (http://www.oracle.com/java/index.html?jdbc_sqlj.html) APIs, running within Oracle.
• A presentation layer implemented in Tcl using AOLserver.

Qmail handles the traditional duties of a mail transfer agent: receipt of incoming email via SMTP, bouncing of incorrectly addressed or formatted messages, notification of errors during reception, and reliable delivery of the email into the file system. Aside from being configured in a somewhat idiosyncratic fashion, qmail is used without modification to do what it was designed to do. Non-interference with qmail means we can trust it to handle its MTA duties (to the extent that it is reliable).

After delivery to the file system, the message must be moved into the database as rows in tables. This job falls to Java code running in the database, relying on the JavaMail and SQLJ APIs to parse and insert the message. The headers are parsed and stored separately to facilitate searching and filtering, and any MIME attachments are decoded and stored separately. Once this processing is complete, the message is removed from the file system. This polling process is triggered once a minute by the DBMS_JOB PL/SQL package.

Once the message is in the database, it is a simple matter to serve it up again through an IMAP server, or present it through Web pages produced by Tcl scripts running within AOLserver. In the latter case, the flexibility of SQL make it easy to implement user interfaces that allow readers to view and manipulate their email in many powerful ways.

The Web User Interface

• Index Page (screenshot)

  This is the main user page for the Web interface to webmail. At the top left, there is a count of how many total and unread messages there are in this folder. Below that is a selection widget to change folders, a filter creation widget, and a list of currently active filters.

  Filters allow the user to restrict the messages shown by various criteria. Two useful ones are shown: undeleted and last-n-days, which restrict the visible messages to those which have not been marked as deleted, and which have been received in the past two days. Other useful filters are author, subject, and body, which restrict by the sender, by the subject line, or by a full-text search over the content of the message.

  In the upper right, there is a list of numbers which link to a summary page; the numbers indicate how many days back you wish to see a summary for. Below that are links to expunge deleted messages (actually delete any messages that are marked for deletion) and send a message. Under that is a widget to delete, undelete, or refile selected messages (those messages that have the box in the first column checked).

  The "Check All" and "Clear All" links use JavaScript to select or unselect all visible messages. These links combined with the filters make it easy to bulk-select any messages matching certain criteria and delete or refile them.

  The column headers over the messages can be clicked to resort the messages on that column. The current sort column is indicated by an up or down arrow. If the current sort column is selected again, the direction of the sort is reversed. The U and D columns indicate whether that message is "unread" and "deleted". Unread messages are also displayed in a bold font. In this example, no deleted messages are shown because of the undeleted filter in place, but deleted messages are displayed in a gray font.

  The messages are listed in a striped table. After a configurable number of rows have been displayed, the table is closed and restarted, so that the browser does not have trouble attempting to display a single table with hundreds or thousands of rows. The author of a message can be selected to view that message. (This can be a problem for messages that were sent without a From header; this is rare enough that I haven't bothered to put in a separate "Read" link. Those messages can be read in the current interface by selecting the previous message and following the "Next" link from that message.)

  At the bottom of the page, the bulk-action, expunge, and send mail widgets are repeated so that they can be easily accessed from the end of a long folder of messages. If there are fewer than a configurable minimum number of messages, these widgets are not displayed.

• Summary (screenshot)

  This page gives a summary by author and recipient of all messages for the past day. The user can change the folder and number of days being summarized at the top. Selecting an author or a recipient takes the reader back to the main page with a new author or recipient filter in place. The most useful feature of this summary page is being able to delete messages by sender.

• Message Sending (screenshot)

  This is an example of a filled-out message composition page. There is currently no facility for specifying Bcc or Reply-To. The From header is limited to the addresses which the administrator has assigned to the user.

• Attachments and Confirmation (screenshot)

  This is the message confirmation screen, where you can see what your message will look like before you send it. This is also where you can attach files to your message.

• Viewing One Message (screenshot)

  At the top of the screen are links to previous and next messages, response links, and refile and delete widgets. If this message was already marked deleted, the delete button does not appear. Selecting the author or the subject will return the user to the main page with a new author or subject filter added. Images are displayed inline, while an attachment would be displayed with the text "Attachment: filename".

The Paper Path for Deliveries

See the installation section of the WebMail module documentation (http://arsdigita.com/doc/webmail.html) for a detailed description of the qmail setup.

• Mail is sent to someuser@somedomain.com.
• somedomain.com is in /var/qmail/control/rcpthosts, so qmail-smtpd accepts it.
• /var/qmail/control/virtualdomains contains

  somedomain.com:webmail-sd
  

  which routes all mail for somedomain.com to the "webmail" user, with an arbitrary suffix (in this case, "sd") appended to identify the domain it was received for. So in our example, the mail will be delivered to webmail-sd-someuser@somedomain.com.
• /var/qmail/users/assign contains

  +webmail-:oracle:101:102:/home/nsadmin/qmail/alias:-::
  

  that specifies that the "webmail" user, for the purposes of qmail, will be handled by the "oracle" Unix user, with UID 101 and GID 102, with /home/nsadmin/qmail/alias as the "home directory". The UID and GID must be set to the same values as the Unix oracle user so that the delivered mail will be owned by the oracle user, and can be read and deleted by Oracle.
• In qmail, any recipient of the form "foo-suffix" (for any arbitrary suffix) will be handled by the "foo" user (either a Unix user, or a virtual qmail user that has been assigned to a Unix user by the users/assign file). Its delivery will be controlled by the alias file "~foo/.qmail-suffix". For each user of webmail, an alias file is created with the line

  /home/nsadmin/qmail/queue/
  

  which instructs qmail to deliver to the /home/nsadmin/qmail/queue directory in maildir format. In our example, this file would be /home/nsadmin/qmail/alias/.qmail-sd-someuser.
• maildir is a mail storage format particular to qmail which was designed to be resistant to loss of mail by file corruption. A maildir has three subdirectories named cur, new, and tmp. Incoming mail is streamed to a file in tmp, and moved to new when it is complete. cur is intended to store messages for the mail reader and is not used in our case. This use of separate files for each message and atomic moves to indicate delivery completion avoids the common case of email corruption when a mail reader and the MTA don't cooperate properly in locking files.

  Java code in Oracle polls the /home/nsadmin/qmail/queue/new directory at a configurable interval (by default, one minute) and attempts to parse and store any files found there. It takes a lock so that if the processing takes longer than the polling interval, the next polling process just quits instead of attempting to parse the same files. If a file cannot be processed for any reason, an error is logged (if this is the first attempt for that file) and whatever insertions have occurred for that message are backed out. Otherwise, the message is committed into the database and the file is deleted. The message is "delivered" within Oracle based on the contents of the Delivered-To header, which is inserted by qmail. This field contains the final, expanded address of the recipient ("webmail-sd-someuser@somedomain.com"); "sd" identifies the domain, and "someuser" identifies the user. (It would appear that you could use "@somedomain.com" to identify the domain, but since multiple domains may be handled by the same qmail user, the "sd" key provides separate namespaces for different domains, so that you can have .qmail-somedomain-foo and .qmail-someotherdomain-foo and have them go to separate users.)

This somewhat roundabout delivery path is the result of the desire to have one place to look for new mail, to easily determine the intended recipient, to separate webmail's email address namespace from the Unix account namespace, and to avoid interfering with qmail's normal error-handling mechanisms in the case where the recipient could not be found. It has the disadvantage that all email to an entire domain must be handled by webmail.

The Data Model

-- Domains for which we receive email.
create table wm_domains (
	-- short text key
	short_name		varchar(100) not null primary key,
	-- fully qualified domain name
	full_domain_name	varchar(100) not null
);

The wm_domains table contains the domains for which we expect to receive mail. The short_name field stores the text key used to differentiate mail to different domains as discussed above. Qmail must be configured to handle email for these domains manually.

-- Maps email accounts to ACS users.
create table wm_email_user_map (
	email_user_name	varchar(100) not null,
	domain		references wm_domains,
	user_id		not null references users,
	primary key (email_user_name, domain, user_id)
);

wm_email_user_map assigns email addresses to ACS users. Why not just use the email column in the users table? This approach lets each ACS user receive email for more than one email address and also permits flexibility on which address is published to other registered users. As a row is inserted into this table, the appropriate .qmail alias files are created for that user.

-- Main mail message table. Stores body of the email, along
-- with a parsed text version with markers for attachments for MIME
-- messages.
create sequence wm_msg_id_sequence;
create table wm_messages (
        msg_id          integer primary key,
        body            clob,
	-- plain text portions of MIME message; empty if 
	-- entire message is of type text/*.
	mime_text	clob,
        message_id      varchar(500), -- RFC822 Message-ID field
	unique_id	integer -- for both POP3 UIDL and IMAP UID
);

create index wm_messages_by_message_id on wm_messages(message_id);

This is the primary message table. It stores the body of the message, a parsed plain-text version with markers for attachments if this is a multipart MIME message, a denormalized Message-ID field for easy reference by Message ID, and yet another ID field for IMAP bookkeeping. The message_id field is not unique, since the same message may have been received multiple times.

-- Stores attachments for MIME messages.
create table wm_attachments (
	msg_id		not null references wm_messages,
	-- File name associated with attachment.
	filename	varchar(600) not null,
	-- MIME type of attachment.
	content_type	varchar(100),
	data		blob,
	format		varchar(10) check (format in ('binary', 'text')), -- for interMedia INSO filter
	primary key (msg_id, filename)
);

This table stores MIME attachments and associated information. The format field enables the use of the interMedia INSO filters to search encoded files, such as Word or PDF documents.

-- Maps mailboxes (folders, in more common terminology) to ACS users.
create sequence wm_mailbox_id_sequence;

create table wm_mailboxes (
	mailbox_id	integer primary key,
	name		varchar(100) not null,
	creation_user	references users(user_id),
	creation_date	date,
	uid_validity	integer, -- Needed for IMAP
	unique(creation_user, name)
);

A "mailbox" is what other systems would term "folders."

-- Maps messages to mailboxes (and thus to users).
create table wm_message_mailbox_map (
	mailbox_id	integer references wm_mailboxes,
	msg_id		integer references wm_messages,
	seen_p		char(1) default 'f' check(seen_p in ('t','f')),
	answered_p	char(1) default 'f' check(answered_p in ('t','f')),
	flagged_p	char(1) default 'f' check(flagged_p in ('t','f')),
	deleted_p	char(1) default 'f' check(deleted_p in ('t','f')),
	draft_p		char(1) default 'f' check(draft_p in ('t','f')),
	recent_p	char(1) default 't' check(recent_p in ('t','f')),
	primary key (msg_id, mailbox_id)
);

Messages "belong" to mailboxes, which "belong" to users. This table exists to map messages to mailboxes and store subsidiary status information for the messages. Why aren't these just fields in the wm_messages table? Originally, there was some notion that identical messages to multiple recipients could be folded together to save on storage. This may happen at a later date, but raises reference counting issues.

-- Parsed recipients for a message; enables search by recipient.
create table wm_recipients (
        msg_id          integer not null references wm_messages,
        header          varchar(100) not null, -- to, cc, etc.
        email           varchar(300) not null,
        name            varchar(200)
);

create index wm_recipients_by_msg_id on wm_recipients(msg_id);

This table contains parsed recipient address information for each message.

-- Headers for a message.
create table wm_headers (
        msg_id          integer not null references wm_messages,
	-- field name as specified in the email
        name            varchar(100) not null,
	-- lowercase version for case insensitive searches
        lower_name      varchar(100) not null,
        value           varchar(4000),
        -- various parsed versions of the value
        time_value      date, -- date/time fields
        -- email and name, for singleton address fields like From
        email_value     varchar(300),
        name_value      varchar(200),
        -- original order of headers
        sort_order      integer not null
);

create index wm_headers_by_msg_id_name on wm_headers (msg_id, lower_name);

Headers are stored separately from the message to aid in searching. Where applicable, header values are parsed and stored in typed fields. The original ordering of the headers is maintained, both so that we can recreate the header block and because order is significant for certain fields.

-- Table for recording messages that we failed to parse for whatever reason.
create table wm_parse_errors (
	filename		varchar(255) primary key not null, -- message queue file
	error_message		varchar(4000),
	first_parse_attempt	date default sysdate not null
);

If an error occurs while attempting to parse a message, we store a record of the error in this log for the administrator to review. Only the first occurrence of an error is logged for any file, to prevent hundreds of identical error messages from clogging the log.

-- Used for storing attachments for outgoing messages.
-- Should be cleaned out periodically.

create sequence wm_outgoing_msg_id_sequence;

create table wm_outgoing_messages (
	outgoing_msg_id		integer not null primary key,
	body			clob,
	composed_message	clob,
	creation_date		date default sysdate not null,
	creation_user		not null references users
);

create table wm_outgoing_headers (
	outgoing_msg_id		integer not null references wm_outgoing_messages on delete cascade,
	name			varchar(100) not null,
	value			varchar(4000),
	sort_order		integer not null
);

create unique index wm_outgoing_headers_idx on wm_outgoing_headers (outgoing_msg_id, name);

create sequence wm_outgoing_parts_sequence;
create table wm_outgoing_message_parts (
	outgoing_msg_id		integer not null references wm_outgoing_messages on delete cascade,
	data			blob,
	filename		varchar(600) not null,
	content_type		varchar(100), -- mime type of data
	sort_order		integer not null,
	primary key (outgoing_msg_id, sort_order)
);

-- Create a job to clean up orphaned outgoing messages every day.
create or replace procedure wm_cleanup_outgoing_msgs as
begin
  delete from wm_outgoing_messages
    where creation_date < sysdate - 1;
end;
/

declare
  job number;
begin
  dbms_job.submit(job, 'wm_cleanup_outgoing_msgs;',
		  interval => 'sysdate + 1');
end;
/

When composing messages for sending, the unsent message and any attachments are stored in the database. When the message is sent, a MIME message is composed consisting of the text of the message followed by any attachments (there is currently no facility to intersperse attachments with text), and the data in these tables deleted. If a user begins to compose a message, then neglects to send it, the unsent message would remain in the database indefinitely. We could handle this either by presenting a list of unsent messages to the user and presenting the option of completing or deleting the message, or by periodically cleaning out any old unsent messages. Webmail does the latter.

Unsent outgoing attachments could as well be stored in the filesystem, but it is easier to manage them when they are all contained within the database.

-- PL/SQL bindings for Java procedures
create or replace procedure wm_process_queue (queuedir IN VARCHAR)
as language java
name 'com.arsdigita.mail.MessageParser.processQueue(java.lang.String)';
/

create or replace procedure wm_compose_message (outgoing_msg_id IN NUMBER)
as language java
name 'com.arsdigita.mail.MessageComposer.composeMimeMessage(int)';
/

-- Parse the queue every minute. Queue directory is hardcoded.
declare
  job number;
begin
  dbms_job.submit(job, 'wm_process_queue(''/home/nsadmin/qmail/queue/new'');',
		  interval => 'sysdate + 1/24/60');
end;
/

These PL/SQL bindings for Java procedures are the heart of the system. wm_process_queue attempts to parse every file in the given directory as an email message, deliver it to a webmail user, and delete the file. It is run every minute by an Oracle job.

The argument to wm_process_queue here is one of the two hardcoded paths in the webmail module. It must be changed if the webmail user's maildir is created in a different location than that shown.

-- Trigger to delete subsidiary rows when a message is deleted.
create or replace trigger wm_messages_delete_trigger
before delete on wm_messages
for each row
begin
  delete from wm_headers where msg_id = :old.msg_id;
  delete from wm_recipients where msg_id = :old.msg_id;
  delete from wm_message_mailbox_map where msg_id = :old.msg_id;
  delete from wm_attachments where msg_id = :old.msg_id;
end;
/

This trigger makes deleting messages easy; deleting from wm_messages will also delete the appropriate rows from any subsidiary tables.

-- interMedia index on body of message
create index wm_ctx_index on wm_messages (body)
indextype is ctxsys.context parameters ('memory 250M');

-- INSO filtered interMedia index for attachments.
create index wm_att_ctx_index on wm_attachments (data)
indextype is ctxsys.context parameters ('memory 250M filter ctxsys.inso_filter format column format');

-- Trigger to update format column for INSO index.
create or replace trigger wm_att_format_tr before insert on wm_attachments
for each row
declare
  content_type	varchar(100);
begin
  content_type := lower(:new.content_type);
  if content_type like 'text/%' or content_type like 'application/msword%' then
    :new.format := 'text';
  else
    :new.format := 'binary';
  end if;
end;
/

-- Resync the interMedia index every hour.
declare
  job number;
begin
  dbms_job.submit(job, 'ctx_ddl.sync_index(''wm_ctx_index'');',
		  interval => 'sysdate + 1/24');
  dbms_job.submit(job, 'ctx_ddl.sync_index(''wm_att_ctx_index'');',
		  interval => 'sysdate + 1/24');
end;
/

These indices and triggers enable full-text searches over messages. An INSO filtered index is also created to allow full-text searches over any attachments which contain text, including formatted documents.

The Java Code

MessageParser.sqlj and MessageComposer.sqlj are the two files that make up the Java portion of the webmail module. MessageParser.sqlj provides the procedures for polling the incoming message queue, parsing them, and inserting the data into the appropriate places in the database. MessageComposer.sqlj is responsible for taking text and attachments that have been placed in the database by the user from the web interface and generating a MIME message. MessageParser.sqlj is the more complex of the two; I present selected sections of it here to give an extended example of how one might use Java running in the database to do processing that would be difficult to do in PL/SQL or Tcl alone.

A Note On SQLJ SQLJ is a preprocessor-based extension to the Java language which provides type-safe embedded SQL, much like PRO*C does for C. It processes .sqlj and outputs either .java files, or runs them through the javac compiler internally to produce .class files. The loadjava program used to load Java into the database can handle SQLJ files, so there is no need to process them using the sqlj processor before loading them.

public class MessageParser {
    
    private static boolean runningOutsideOracleP = false;
    protected static Session s = null;

    // For debugging.
    public static void main (String args[]) {
	try {
	    // set the default connection to the URL, user, and password
	    // specified in your connect.properties file
	    Oracle.connect(MessageParser.class, "connect.properties");
	    System.out.println("Connected.");

	    // Set the flag to tell the system not to delete any messages
	    // after parsing.
	    MessageParser.runningOutsideOracleP = true;
	    
	    MessageParser.processQueue("/home/nsadmin/qmail/queue/new");
#sql { COMMIT };
	    // MessageParser.parseMessageFromFile("mimetest.msg");
	    System.out.println("Parse succeeded.");
	} catch (Exception e) { 
	    System.err.println("Error running the example: " + e.getMessage());
	    e.printStackTrace();
	}
	System.exit(1);
    }

It is far easier to debug Java code when it is not running within the database. This main method allows us to exercise the rest of the code from the command line. The runningOutsideOracleP flag informs methods that they are being run from the command-line (and so, presumably, in a debugging mode).

    // Process all files in a directory.
    public static void processQueue(String queueDirectory) 
	throws SQLException {
	try {

	    // Lock the wm_messages table to make sure we have exclusive access to it, and
	    // to make sure there isn't another queue processing job running.
#sql {lock table wm_messages in exclusive mode nowait};
	} catch (SQLException e) {

	    // Someone else has it; just give up for now.
	    return;
	}

Taking an exclusive lock on the wm_messages table prevents another instance of this method from coming along and attempting to process the files we are already working on. nowait says not to block if someone already has the lock. In that case, we just quit.

	// Go through all the files in the queue and attempt to parse them.
	File qdir = new File(queueDirectory);
	
	// Not compatible with 1.1
	// File[] filesToProcess = qdir.listFiles();

File.listFiles() is a Java 2 addition to the core API, and returns an Enumeration of only the files that are actually files (rather than directories, or symbolic links, or some other type of special file). Oracle 8.1.6 incorporates Java 2, but earlier versions of Oracle 8i do not, and often only Java 1.1 is available from the command line, so we will use the Java 1.1 API here.

	String[] filenamesToProcess = qdir.list();
	for (int i = 0; i < filenamesToProcess.length; i++) {
	    File currentFile = new File(qdir, filenamesToProcess[i]);
	    System.out.println("Processing " + currentFile.getAbsolutePath());
	    if (! currentFile.isFile()) {
		continue;
	    }
	    try {
		parseMessageFromFile(currentFile);
		if (! MessageParser.runningOutsideOracleP) {
#sql { COMMIT };
		    currentFile.delete();
		}

If we are not running within Oracle, then we do not delete the file that we have just parsed. This is so we can rerun tests multiple times while debugging without having to regenerate the test files.

	    } catch (Exception e) {
		// We don't want to quit for parse errors.
#sql { ROLLBACK };
		recordParseError(currentFile, e);
	    }
	}
    }

In the event of an error, the error is logged in the wm_parse_errors table, and we continue processing the rest of the directory.

    public static void parseMessageFromFile(File file) 
	throws MessagingException, SQLException, FileNotFoundException, IOException, ParseException {
	// Open the file.
	BufferedInputStream is = new BufferedInputStream(new FileInputStream(file));

	// Get the headers as an enumeration of Header objects.
	InternetHeaders ih = new InternetHeaders(is);
	Enumeration headers = ih.getAllHeaders();

The constructor for InternetHeaders takes an InputStream and parses it as an RFC822 header block, leaving the InputStream at the end of the header section.

	// Create new record in wm_messages and grab the CLOB to stuff with the body.
	CLOB body = null;
	int msgId = 0;

	// System.out.println("Inserting into wm_messages...");

#sql {select wm_msg_id_sequence.nextval into :msgId from dual};

#sql {insert into wm_messages (msg_id, body) 
	    values (:msgId, empty_clob())};

#sql {select body into :body from wm_messages where msg_id = :msgId};
	// System.out.println("done");

In theory, one should be able to say

insert into wm_messages (msg_id, body)
values (:msgId, empty_clob())
returning body into :body

but in practice, I couldn't make that work from SQLJ.

	copyInputStreamToClob(is, body);

Since the InternetHeaders constructor left the InputStream at the end of the headers, the rest of the InputStream is the body of the message. We stream it to the CLOB we inserted into wm_messages.

	// Insert the headers into wm_headers.
	insertHeaders(msgId, headers);

	// "Deliver" the message by inserting into wm_message_mailbox_map.
	deliverMessage(msgId, ih.getHeader("Delivered-To", null));

insertHeaders() inserts each of the parsed headers into wm_headers. The message is "delivered" by inserting a row into wm_message_mailbox_map mapping this message to the "INBOX" mailbox of the user indicated in the Delivered-To header. This header will always exist, as it is inserted by qmail on delivery.

	String contentType = ih.getHeader("Content-Type", null);
	if (contentType != null) {
	    contentType = contentType.toLowerCase();
	    if (contentType.indexOf("text") == -1) {
		// Reopen the file to pass to parseMIME.
		is = new BufferedInputStream(new FileInputStream(file));
		
		// If message is a MIME message and is not plain text, save
		// text to wm_messages.mime_text and save attachments to directory.
		parseMIME(msgId, is);
	    }
	}
    }

If the MIME type of this message does not contain the string "text," then parse it for attachments.

    // Utility procedure for parsing timestamps. Java date parsing
    // wayyyy sucks; this is the simplest method that seems to work
    // most of the time.
    public static Timestamp parseDate(String s)
	throws ParseException {
	// This DateFormat stuff doesn't work so great.
	// DateFormat df = DateFormat.getDateTimeInstance(DateFormat.MEDIUM,
	//					       DateFormat.FULL);
	System.out.println("Attempting to parse date: " + s);
	return new java.sql.Timestamp(Timestamp.parse(s));
    }

This utility procedure is used by insertHeaders() to parse various date fields. Unfortunately, the date parsing facilities of Java, while seemingly comprehensive, are in practice prone to throwing runtime errors and core dumping. Timestamp.parse() is a deprecated procedure, but seems fairly robust and works for most cases. Adding to the date parsing problem are systems which produce dates formatted in non-standard ways, such as Microsoft's email server which writes out time zones in full ("Eastern Standard Time" instead of "EST" or "-0500") and cause Timestamp.parse() to crash. A more robust solution is being sought.

    // Parses a MIME message, inserts text into wm_messages.mime_text, and unpacks
    // attachments into wm_attachments.
    public static void parseMIME(int msgId, InputStream is) 
	throws MessagingException, SQLException, IOException {

	// Parse the message.
	if (s == null) {
	    Properties props = new Properties();
	    s = Session.getDefaultInstance(props, null);
	}
	MimeMessage msg = new MimeMessage(s, is);
	is.close();

The constructor for MimeMessage takes care of parsing the MIME message into its components. It requires a Session object, which keeps track of state for a mail reading session when JavaMail is being used to implement a full-blown mail reader. Since we just want to use it to parse things for us, we grab and store the default Session instance into a class variable.

	// System.out.println("Message type is " + msg.getContentType());

	// Buffer we're going to store up text bits in.
	StringBuffer text = new StringBuffer();

	// Wrap partNumber in an array so we can pass by reference.
	int[] partNumber = new int[1];
	partNumber[0] = 0;

	try {
	    dumpPart(msgId, msg, text, partNumber);
	} catch (Exception e) {
	    // If dumpPart fails, then just treat the message as text.
	    return;
	}

This code takes a MIME message and builds up a text-only version of it suitable for display. dumpPart() calls itself recursively to handle multipart messages. The text version of the message contains markers which will be substituted with images or links to attachments when the message is displayed. partNumber is a one element array that contains an integer that is used to differentiate filenames that are associated with attachments if there is more than one attachment with the same filename. The array wrapper is required since it will be getting passed and updated within dumpPart() recursively, since there is no call-by-reference in Java.

	String textStr = text.toString();

	// System.out.println("Parsed MIME text is:\n" + textStr);
	
	if (textStr.length() > 0) {
	    CLOB mimeText = null;
	    StringBufferInputStream sbis = new StringBufferInputStream(textStr);
#sql { update wm_messages set mime_text = empty_clob() where msg_id = :msgId };
#sql { select mime_text into :mimeText from wm_messages where msg_id = :msgId };
	    copyInputStreamToClob(sbis, mimeText);
	}
    }
}

The text version of the message is stored in the mime_text field of wm_messages for later display.

The IMAP and POP3 Server

No matter how good a web-based mail interface can be, most users will not want to use it as their day to day mail reader. Providing an IMAP interface allows them to read their mail with the mail reader of their choice (as long as it understands IMAP), and use the web interface when they do not have access to an IMAP mail reader, or when they are travelling and do not wish to configure whatever IMAP client they may have access to.

Again, we follow the principle of writing nothing that would be hard to write. The University of Washington IMAP server (UW IMAP), available from http://www.washington.edu/imap/, is one of the oldest IMAP server implementations around, and was fortuitously designed with a modular message store implementation which makes it simple to replace the filesystem-based message store with a database-backed one. UW IMAP also handles POP3.

The work of integrating UW IMAP to webmail is being done by Greg Haverkamp (<gregh@arsdigita.com>), and is not currently available.

Future Directions

The current webmail interface is suitable for the day to day reading of email. I have been using it as my primary email reader since March 2000, and have not had cause to curse at it. This is mainly due to the ease of extending it; if I find myself doing something tediously repetitious, I will try to implement an easier way of automating that task. What I hope to see as it matures are more and better tools for automating email handling.

One possibility is the idea of incorporation filters: as email is received, a set of rule-based triggers change its status so that it can be handled more efficiently. These filters could automatically refile, delete, or mark as urgent messages meeting certain criteria. The main design issue here is how to enable the user to easily and unambiguously specify the rules and their precedence. An interesting idea is to use the interMedia gist feature to tag messages with a set of words that convey the gist of the message, a sort of automatically generated summary. This would allow automatic content-based handling of email.

One related area of work in progress is the integration of ezmlm-idx, a qmail-based mailing list manager. Ezmlm-idx already has hooks to maintain its subscriber list in a database, and I have written an Oracle driver for it, along with an ACS administration module. A full-fledged integration with webmail might make mailing list archives available as a read-only webmail folder.

---

asj-editors@arsdigita.com

Reader's Comments

I certainly understand the need to implement a Web-mail reader, especially with an IMAP interface.

However, I am curious as to why you have chose to use Oracle as the message store, surely this adds extra overhead to the process of reading/storing mail. Presumably there is some problem with filesystem storage, and integration with ACS.

I would like to propose the following tools which may also solve the Webmail/IMAP problem.

Courier-IMAP is a mail server that provides IMAP access to Maildir mailboxes.

SqWebMail is a web CGI client for sending and receiving E-mail using Maildir mailboxes.

Finally, given the requirement to maintain a similarity across multiple mailreaders/viewers, I do not see any point to provide POP access. This is a pretty dumb protocol, and only IMAP will provide the ability to create folders for filing messages within a mailreader which will then appear the same when accessed from a mailreader on another computer, or indeed via a browser.

-- Philip Jensen, June 7, 2000

I think Jin's decision to store parsed email in DB is correct. If the email is left in Maildir, it will have to be parsed everytime the user access the message which consumes a lot of cycles and disk reads.

Also, it will be hard to create index on keywords in various parts of the email if that email is sitting in Maildir in its original form.

-- Karl Tsai, June 13, 2000

When all you've got is a hammer, everything looks like a nail. And Oracle is a Very Big Hammer!

The argument for storing things in the database is that it makes adding a web interface onto it almost laughably easy. It gives you access to easy filtering by date, author, subject, searches on the body, searches on attachments, searches on binary Word attachments, easy bulk-deletion by author, etc. etc. It does add a small amount of overhead in storage, but the flexibility that it gives you is immense. I didn't just want to read my email on the web, I could have used hotmail for that. I wanted to read my email solely on the web, every day, all hundreds of messages and spam and daily robot alerts, and do it efficiently and easily. And so far, it's been not too bad.

As far as keeping things in maildir format, the docs accompanying the UW-IMAP code makes some good arguments why maildir isn't the best format for a message store.

-- Jin Choi, June 14, 2000

Jin, I am truly quite surprised that you think the M203s are appropriate, as mentioned in one of the screenshots above.

While M203s have their uses, something along the lines of the Maadi-Griffin semi auto .50 cal BMG is more useful at longer range, while still being even more devastating at closer range. (www.maadigriffin.com)

I would suggest a mix of ammo in the same clip. My personal preference is "standard" ammo for 3 rounds, followed by 1 greentip, then an HE, then tracer.

hope this helps.

-- Patrick Giagnocavo, June 27, 2000

I don't get it: why would anyone want to edit their e-mails in a textarea on the browser? Didn't someone say about a similar problem:

There are bunch of problems with this kind of system. First, Netscape Navigator is a good browser but there are better text editors than its rendering of an HTML TEXTAREA. Not only is editing a Web document in a Netscape TEXTAREA inconvenient...

OK, so the 33,000 character limitation may not be a big issue for your avarage e-mail, but still: the textarea is no text editor.

Couldn't you have found a component that would have provided a decent editing interface -- probably in Java because it runs in the browser? (Given that you are re-writing the user mail agent anyhow it shouldn't be a big issue?) I'm not asking you to re-implement Emacs in Java (unless you want to ... :-)) but something a little better than textarea.

And perhaps this component would also

• handle the various character sets (does your interface translate, say, &aring; into a proper MIME encoded message?),
• handle encryption of messages and electronic signatures,
• (of corse the trasnmission of the messages would be encrypted -- easy using https in your version but no so easy, perhaps, in the Java version) and
• provide in-line attachments.

But my real issue is with the lack of filter support: even hotmail has filters for bulk e-mail suppression. I like your idea about filters, but the first thing I would want is the ability to define and store my own filters so I can easily recall and apply them later. There doesn't seem to be support for that in your data model?

Anyhow, I have been ranting enough. It doesn't look half-bad, your system, and the storage of the documents in a RDBMS should give you some advantages if you can use it right, especially with the search facility (I can never find my e-mails again) and stability (how often does Netscape Messenger corrupt your mail files?). Good luck!

-- Allan Engelhardt, July 21, 2000

Allan, yes, all your suggestions would be brilliant additions and make life much easier. But, they are all pretty tricky and would require lots of work.

In any case, Erik Bielefeldt (erik@arsdigita.com) has picked up the ball on webmail and will be extending it in new and radical ways, so please make any suggestions on improvements to Erik :-) I expect he will be improving the filter system.

In the matter of browser text fields being a bad way to edit text, I totally agree. However, we must play the cards we are dealt. Personally, I'm looking forward to the day when I can use xemacs widgets as textareas in my browser. All the Java text editors I've seen are uselessly slow. And in the original vision, somewhat delayed, the entire web interface was to have been an alternative, on-the-road interface, with a regular IMAP client being the access method of choice.

Regarding the M203 comment above, I am sorry to say I do not own an M-16 attachable grenade launcher, nor any .50 BMG semiautomatic rifles, so I am not qualified to argue their relative merits from personal experience.

-- Jin Choi, September 17, 2000

Allan: webmail is indeed a pain. I curse whenever I'm forced to use the webmail interface I put on my mailspool, since I'm vastly more efficient with mutt and procmail. However, there are times a-plenty when HTTP is about the only option for reading mail, and given a choice between using a web email client and having no email access, I'll take the former.

The real question is not, "why use web-based email", but which is the least awful option for same.

-- Rodger Donaldson, September 18, 2000

I would shy away from the use of any kind of custom wigets in the mail webpage (in this example). After all, the idea is that you can use the browser view your mail from anywhere. If it only works if you can download a Java applet or a custom control (not necessarily possible on a kiosk, for instance) then you've drastically limited the functionality without significantly extending the usefulness.

If a standard web client and an "enhanced" client were both available, that might fulfil the goals stated. In fact, one of the joys of a well written server-side component is that multiple clients can be made available without forcing users to choose which is better in every case.

-- Richard Stanford, January 9, 2001

Why use Oracle rather than MySQL or PostgresQL? Oracle costs money AND is closed source. Check out http://www.horde.org/imp for a webmail that can interface with a variety of open-source database systems.

-- Chris Stein, February 9, 2001

I would like to hear the arguments against maildir except the fact that a few clients don't support it? I was under the impression that it was far more reliable.

Adrian

-- Adrian Byng-Clarke, June 1, 2001