Search your email using SQL!

[Phil Endecott]

Dear PostgreSQL people,

I thought that some of you might be interested to know about Decimail, a
new IMAP mail server that I've written that uses PostgreSQL to implement
its message store.

Decimail's unique feature is that mailboxes are defined using SQL
queries. So for example you can match all messages to this list with
this query:

select msg_id from to_addrs where ad*****************@postgresql.org'

But it's better than that. You can have a table of mailing lists that
stores the list name and its address:

list_name | submit_addr
--------------------+------------------------------
PostgreSQL SQL | pg*******@postgresql.org
Wget | wg**@sunsite.dk
PostgreSQL General | pg***********@postgresql.org

Then you can create a view that generates a query like the one above for
each of these lists:

-[ RECORD 1 ] --------------------------------------------------------
mailbox | Lists/PostgreSQL SQL
query | select msg_id from to_addrs where addr='pgsql-sql@postgresql
-[ RECORD 2 ]---------------------------------------------------------
mailbox | Lists/Wget
query | select msg_id from to_addrs where addr='wg**@sunsite.dk'
-[ RECORD 3 ]---------------------------------------------------------
mailbox | Lists/PostgreSQL General
query | select msg_id from to_addrs where addr='pgsql-general@postgr

Now combine (UNION) this with similar views generated from your address
book (you keep that in a PostgreSQL table as well don't you?), for
chronological filtering, and so on. Each of the queries that you define
is visible as a mailbox within your IMAP email clients (note the level
of hierarchy defined in the mailbox names above).

Decimail is GPLed and has just been announced to the world at large in
the last few days; I'm probably still the only person who's using it.
So it's not exactly suitable for use in a "production" environment. But
if you're an experimenter and you like the idea of searching and
categorising your email using SQL, it could be just what you're looking
for. I've been using it for several months and it has never lost any
email. The hard and boring part is the IMAP server code but that's
done; the fun bit is getting the database to present clever and useful
views of the messages.

Have a look at the web page: http://decimail.org/

Thanks to all you PostgreSQL people for making this possible.

Regards,

--Phil.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend