I have been looking for a SQL-based way to store all my old email messages. I think I may have found it. There is an email system called DBMail that stores messages in a database (MySQL, PostgreSQL or SQLite) and exposes them (for want of a better word) through POP3 and IMAP.
At the moment I use Gmail, forwarding to Exchange 2007, Outlook 2007 and a huge PST. Searching the PST takes a while. Gmail doesn’t have everything in it. Exchange is there to push new messages to my phone (though I haven’t been using that since I’m back in NZ because data charges on prepay are too high, even though they have come down significantly in recent months). But I am getting a new phone soon, which should support IMAP IDLE push mail, which means I can get rid of the Exchange hosting.
The good thing about DBMail is that seeing it will store everything in MySQL (I’m going to use MySQL), I’ll be able to do anything I want with the database. So I’ll be able to write a web-based interface to it.
One thing I am going to have to think about is search. MySQL has full text indexing, but I’m not sure how good it will be with over 2.5GB to index. The other option I know of and have used a bit is Apache Lucene. I read an article by Jayant Kumar comparing the two. It looks like seeing I have so much data, Lucene might be the better option. I’ll try out native full text first and see if it’s fast enough, and then try out Lucene. And maybe I will post the results.
So I am now installing Ubuntu Desktop 8.04 on Virtual PC (with help from this TechRepublic article) and will give it a whirl. I have a server running Ubuntu Server in the UK, which is where this will live long term, if it works.