persist e-mail in a RDBMS

Question

I'm considering using dbmail, Archiveopteryx, manitou or James to persist javax.mail.Message objects, not exactly e-mail per se. However, I'm guessing that, in the case of dbmail specifically, it's only really useful for actual sending and receiving of messages.

In this particular case, I want to take the NNTP messages which Leafnode has and put those into a RDBMS. To that end, dbmail looks interesting if only because it's an established project which presumably has solved many e-mail --> database problems. Specifically, their schema is, I expect, reasonably designed.

I'm working with javax.mail.Message because the GNU NNTP API uses Message, which has ups and downs in this context. (It's missing some NNTP specific functionality.)

Assuming no Java driver for dbmail, and I can't see why they would have one, how would I get messages from Leafnode to dbmail? Just send myself an e-mail on localhost?

On a sidenote, how crazy/silly is this? Is there a better general approach?

Delivering mail to a database has been done many times by many people. One example is the DBMail project. Another example is the Microsoft Exchange server, which uses a relational database to store everything.

http://www.memoryhole.net/~kyle/databaseemail.html

I would be interested to know why exchange uses an RDBMS, and what their schema looks like, perhaps there are some lessons there.

Answer 1

how crazy/silly is this?

Not crazy or silly provided you're careful about your keys. Think about the headers and attachments, and break them up in tables as their cardinality requires. Then you can run queries based on date, from, to, list-id, etc. You could count attachment types by month one day, if you got curious. ;-)

I would be interested to know why exchange uses an RDBMS

IF you look carefully into how a mail message is delivered into an mbox file, you'd begin to appreciate how much better suited a DBMS is for that sort of thing. By the time Microsoft got around to writing Exchange, they already had SQL server, which they owned. Unix mail servers are older and there was no free DMBS up to the task. Cultural issues, too, but that's another story.