Thread: PG Email Client

Are there any postgresql based email clients that anyone has experience with?
Is there a good technical reason why everyone is not doing this? It would seem to me to be a super
CRM/ERP enhancement.

I am toying with the idea of fully integrating email into our ERP system so that our email can be
indexed in multiple ways and can be viewable as a direct relation with the subsystem or multiple
subsystems that it is related to. It would also allow an organizational view of the communication
related to the subsystem rather then an individual view that email generally gives. For example, if
I want to look at a customer, if corporate email is integrated then I can see the entire history of
communication with that customer, including pre-sales, sales, support, shipping, etc irrelevant as
to who sent the email.

Obviously I don't want to reinvent the wheel or program my own fully featured email client, which I
would need to do if I wanted to convince users to stop using their Eudora, Thunderbird, Outlook, etc.

What I am hoping for is an email client back-end that can be loaded into a schema of an existing
database, and then the application would have access to the email data as well. The front-end would
need to have at least the commonly used components of an email client.

Sim Zacks schrieb:
> Are there any postgresql based email clients that anyone has experience
> with?
> Is there a good technical reason why everyone is not doing this? It
> would seem to me to be a super CRM/ERP enhancement.
>
> I am toying with the idea of fully integrating email into our ERP system
> so that our email can be indexed in multiple ways and can be viewable as
> a direct relation with the subsystem or multiple subsystems that it is
> related to. It would also allow an organizational view of the
> communication related to the subsystem rather then an individual view
> that email generally gives. For example, if I want to look at a
> customer, if corporate email is integrated then I can see the entire
> history of communication with that customer, including pre-sales, sales,
> support, shipping, etc irrelevant as to who sent the email.

Well "client" would be the wrong approach imho, but there
is for example dbmail which can use postgres as storage
for mail and opens a lot of the usecases above. You would
continue to use your MUA for reading mail but some of the
mails could be handled by your application directly.

Regards
Tino

DBMail is an interesting concept, but I think the real advantage would be if there were a client
that could take advantage of the power of a database backend.

For example, instead of saving a copy of an email in 1 folder, the same email could be indexed to
multiple folders. Current email clients mimic file cabinets too much in that the system considers an
email to be a physical entity that can only be stored in one place. Searching for emails can also be
done much faster with a database then with a traditional mail client approach.

However, to start out with, using dbmail imap with current email clients might be a great idea. Then
the users will not even know that anything has changed, and I can basically build the client that I
want directly into my ERP.

Sim

>
> Well "client" would be the wrong approach imho, but there
> is for example dbmail which can use postgres as storage
> for mail and opens a lot of the usecases above. You would
> continue to use your MUA for reading mail but some of the
> mails could be handled by your application directly.
>
> Regards
> Tino
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faq
>

Tino Wildenhain wrote:
> Sim Zacks schrieb:
>> Are there any postgresql based email clients that anyone has
>> experience with?

> Well "client" would be the wrong approach imho, but there
> is for example dbmail which can use postgres as storage
> for mail and opens a lot of the usecases above. You would
> continue to use your MUA for reading mail but some of the
> mails could be handled by your application directly.
>

I'll second the dbmail application.  It's POP or IMAP server based.
But it does what you are looking for.

I used to use dovecot, which is extremely fast.
But, if you gave it too much to do it would hang for hours chewing up the hard
drive and generating heat.

dbmail isn't as fast as dovecot for a single user, single event.  But not by
much.  However, it scales *much* better into the 100's and 1000's of actions.

Hello list,

Has anyone checked out
http://www.archiveopteryx.org/

looks very promising!

Cheers,
Mike

Sim Zacks schrieb:
> DBMail is an interesting concept, but I think the real advantage would
> be if there were a client that could take advantage of the power of a
> database backend.
>
> For example, instead of saving a copy of an email in 1 folder, the same
> email could be indexed to multiple folders. Current email clients mimic
> file cabinets too much in that the system considers an email to be a
> physical entity that can only be stored in one place. Searching for
> emails can also be done much faster with a database then with a
> traditional mail client approach.

Err... I do not understand "copy an email in 1 folder" - I use
IMAP which stores all the mail on the server.
(That is what dbmail does too)

The client or rather MUA queries the server via IMAP
so its complete transparent to where it saves the
mail - however - improvements to IMAP, like the sieve
protocol could be implemented in dbmail to make
use of advantage search using the database.

Regards
Tino

Might I suggest you consider using dbmail as an IMAP server.

This indexing you speak of is exactly what it will do.

But the additional advantage with IMAP is that you can have multiple
clients attached to the same folders (especially useful if shared) or be
able to attach from multiple sources (notebooks, webpages, desktops) and
have all the same messages/folders each time.

On 1/29/2007, "Sim Zacks" <sim@compulab.co.il> wrote:

>DBMail is an interesting concept, but I think the real advantage would be if there were a client
>that could take advantage of the power of a database backend.
>
>For example, instead of saving a copy of an email in 1 folder, the same email could be indexed to
>multiple folders. Current email clients mimic file cabinets too much in that the system considers an
>email to be a physical entity that can only be stored in one place. Searching for emails can also be
>done much faster with a database then with a traditional mail client approach.
>
>However, to start out with, using dbmail imap with current email clients might be a great idea. Then
>the users will not even know that anything has changed, and I can basically build the client that I
>want directly into my ERP.
>
>Sim
>
>>
>> Well "client" would be the wrong approach imho, but there
>> is for example dbmail which can use postgres as storage
>> for mail and opens a lot of the usecases above. You would
>> continue to use your MUA for reading mail but some of the
>> mails could be handled by your application directly.
>>
>> Regards
>> Tino
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 3: Have you checked our extensive FAQ?
>>
>>               http://www.postgresql.org/docs/faq
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>
>QIDX:afa47a3e4ac627067728eb058e4b191f

Sim Zacks <sim@compulab.co.il> wrote:
> DBMail is an interesting concept, but I think the real advantage would
> be if there were a client that could take advantage of the power of a
> database backend.
>
> For example, instead of saving a copy of an email in 1 folder, the
> same email could be indexed to multiple folders. Current email clients
> mimic file cabinets too much in that the system considers an email to
> be a physical entity that can only be stored in one place. Searching
> for emails can also be done much faster with a database then with a
> traditional mail client approach.

I believe that's the case with at least some of these systems; a
"mail folder" can be a query so that you could readily have various
folders that hit the same messages.

> However, to start out with, using dbmail imap with current email
> clients might be a great idea. Then the users will not even know
> that anything has changed, and I can basically build the client that
> I want directly into my ERP.

That's certainly a starting point...
--
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/
Q. What's the difference between Batman and Bill Gates?
A. When Batman fought the Penguin, he won.

  > Err... I do not understand "copy an email in 1 folder" - I use
> IMAP which stores all the mail on the server.
> (That is what dbmail does too)

In the traditional imap server the mail is stored in folders on the server that are accessed by the
client. Therefore you are limited to one indexed location per email. Lets say you want to look at
your email by customer. You would want to have a folder per customer so you can review your
correspondence with each one individually. If you want to look at correspondence per product. You
would need one folder for each product so you can review the correspondence for each product
individually.
However, if you want to have the ability to look at your email both per customer and per product,
then with the traditional imap system, you would need to copy the email so you had two physical
copies of the email. Now if you wanted to make notes on the email, it would only be tied to one of
them, etc..
I haven't checked whether dbmail can make virtual folders yet based on queries, but the normal
clients certainly don't support setting up indexes on the emails so that they could be viewed in
both folders.
A client that took advantage of a database backend would be one in which the user could
a) create virtual folders that are actually queries
b) choose indexes or put in keywords so that whether you clicked on the Customer A folder or the
Product C folder you would see the exact same e-mail.

Hi,

you can find a nice virtual folder implementation in the Opera-Mailclient M2.
Not sure if this also works with IMAP (don't use IMAP yet).

Virtual folders are based on regexes over various fields of a mail(Subject,From,to,Body,etc.).
Of course this is not db-based, but the feature is neat.

Just my 0,02€

Hakan



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sim Zacks
> Sent: Tuesday, January 30, 2007 6:57 AM
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] PG Email Client
>
>
>   > Err... I do not understand "copy an email in 1 folder" - I use
> > IMAP which stores all the mail on the server.
> > (That is what dbmail does too)
>
> In the traditional imap server the mail is stored in folders
> on the server that are accessed by the
> client. Therefore you are limited to one indexed location per
> email. Lets say you want to look at
> your email by customer. You would want to have a folder per
> customer so you can review your
> correspondence with each one individually. If you want to
> look at correspondence per product. You
> would need one folder for each product so you can review the
> correspondence for each product
> individually.
> However, if you want to have the ability to look at your
> email both per customer and per product,
> then with the traditional imap system, you would need to copy
> the email so you had two physical
> copies of the email. Now if you wanted to make notes on the
> email, it would only be tied to one of
> them, etc..
> I haven't checked whether dbmail can make virtual folders yet
> based on queries, but the normal
> clients certainly don't support setting up indexes on the
> emails so that they could be viewed in
> both folders.
> A client that took advantage of a database backend would be
> one in which the user could
> a) create virtual folders that are actually queries
> b) choose indexes or put in keywords so that whether you
> clicked on the Customer A folder or the
> Product C folder you would see the exact same e-mail.
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/30/07 00:32, Hakan Kocaman wrote:
> Hi,
>
> you can find a nice virtual folder implementation in the
> Opera-Mailclient M2. Not sure if this also works with IMAP (don't
> use IMAP yet).
>
> Virtual folders are based on regexes over various fields of a
> mail(Subject,From,to,Body,etc.). Of course this is not db-based,
> but the feature is neat.

And it is client-based, not host-based.

You'd have to hack the IMAP server to add virtual folders and double
links (so that deleting the base-mail will also delete the link
mails).  I'm sure it's not trivial...

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFv1G5S9HxQb37XmcRApGNAJ9akiBYJz+3KF18qVuoqLgPFc8rrwCg0lRh
g7FQlKpYe1wihYRIxAVdnDs=
=slkT
-----END PGP SIGNATURE-----

> dbmail isn't as fast as dovecot for a single user, single event.  But not
> by much.  However, it scales *much* better into the 100's and 1000's of
> actions.

<$.02>

I came within an eyelash of going live with dbmail, and then pulled back.
This was a year or so ago, I don't remember exactly. The performance I was
getting on very large IMAP folders was just awful.

So I turned on PostgreSQL query logging, and looked at the query it was
throwing at the database to select an IMAP folder. And then I looked at the
schema. I remember being horrified. It was clear that as a pure database
application, dbmail was *a mess*. The schema was just not well designed for
the queries they were actually making, and not even slightly well indexed.

They may have fixed this in the meantime. I did look at the schema for
archiveopteryx, and it looked to me to be much better thought out. I got as
far as compiling archiveopteryx on my OS of choice (OpenBSD, no flames
please) which was not a supported platform. It built, but I didn't go
further, so I don't have any results.

There's a lot of work in dbmail, and the architecture "around" the database
seems to me to be quality stuff. Now if only they've gotten the database
coding up to the same level ...

If a database as a message store cannot give good performance, then why do
it. Asking for good performance on a folder of 65K messages is not asking
for the moon. dbmail broke my heart. This was a while ago, maybe they fixed
it, or maybe I had my database tuned wrong, but I'd encourage anybody using
a database as a message store for an E-mail system to look over the schema
with a fine-tooth comb and evaluate it like you would part of your ERP
system. If the schema is not well designed for a typical IMAP query, run
the other way.

</$.02>

Please keep us posted on your archiveopteryx experience!

Thanks and Cheers.

>
> They may have fixed this in the meantime. I did look at the schema
> for archiveopteryx, and it looked to me to be much better thought
> out. I got as far as compiling archiveopteryx on my OS of choice
> (OpenBSD, no flames please) which was not a supported platform. It
> built, but I didn't go further, so I don't have any results.
>

> Please keep us posted on your archiveopteryx experience!
I just install aox on a soekris ! with openbsd 4.0 with a remote postgresl
db and setup sendmail to forward by lmtp.
Really nice. No problem to compile (just a postgresql.h in a obsd specific
location).
The installer (wich cretae the database) was not working, but I do
everything by hand to setup.

I do not have test numbers rigth now, because I need first to configure
the box for virtual hosting, then dspam.

The table structure is really nice. Normalize all the field. Each
multipart mime attachements are handle separatly. It is really esay to use
rules to map config and data to an other soft.
My dream will be to setup a webdav script to access all the attachement in
a dynamic FS. Then I mount the webdav to my mac or my pc.

In dbmail it was very complicated to get each attachement independently
because the message is stored by block of 500Kb and not already parsed by
attachement.

Cordialement,
Jean-Gérard Pailloncy

Jean-Gérard Pailloncy wrote:
> I do not have test numbers rigth now, because I need first to configure
> the box for virtual hosting, then dspam.

It would be really wonderful if someone managed to combine this with
dspams own database. Maybe it'd be possible to pull dspam's algorithms
as a lib into the database?

I've been wanting an IMAP spam drop-box like forever, this seems like a
possibility to achieve this. Combine a spam/not-spam trigger to specific
IMAP boxes and make those update dspam's statistics - seems possible :)

All I'd need to do to mark something spam is drop the mail in the right
box from my client. The quarantine box could work similarly.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

> Jean-Gérard Pailloncy wrote:
>> I do not have test numbers rigth now, because I need first to configure
>> the box for virtual hosting, then dspam.
>
> It would be really wonderful if someone managed to combine this with
> dspams own database. Maybe it'd be possible to pull dspam's algorithms
> as a lib into the database?
Right now, you can do without dspam lib.
Here my planned setup:
sendmail --lmtp--> dpsam --lmtp--> aox
with:
- sendmail + sm-pgsql http://blue-labs.org/software/sm-pgsql/
- dpsam + pg_driver + clamav + daemon
- aox (pg db)

So all the configuration and data will be in pg.
With few rules, everything should rock!

For sendmail config, I am testing
- local-host-names with only mail.domain.com
- accessdb with pg_map and a view to aox user db.
I plan to use no virtual domain in local-host-names and RELAY in access_db
because I want sendmail to be able to test which email adress is OK at
RCPT time. I do no want to spool the file, then send to lmtp send bounce
in case of error.

I will add a virtualuser/aliases to manage .forward-like with pg.

Right now, sendmail+aox works.
I am on the road to sendmail+pg+virtual_domain+oax.

> I've been wanting an IMAP spam drop-box like forever, this seems like a
> possibility to achieve this. Combine a spam/not-spam trigger to specific
> IMAP boxes and make those update dspam's statistics - seems possible :)
On solution I seen on the web (but do not remember the URL) is to use 3
specials folders in INBOX: quarantine, ham, spam.
- HAM go to INBOX
- SPAM go to INBOX.qarantine
- false-HAM is moved by user to INBOX.spam
- false-SPAM is moved by user to INBOX.ham
- a cron script retrain dspam with messages in INBOX.ham and then move
messages to INBOX
- a cron script retrain dspam with messages in INBOX.spam and then move
messages to INBOX.quarantine
- a cron script delete old messages (<14 days) from INBOX.qarantine

May be a trigger that call some external program and tell dspam to
retrain. I need more time to study that.

Cordialement,
Jean-Gérard Pailloncy

Alban Hertroys wrote:
> Jean-Gérard Pailloncy wrote:
>> I do not have test numbers rigth now, because I need first to configure
>> the box for virtual hosting, then dspam.
>
> It would be really wonderful if someone managed to combine this with
> dspams own database. Maybe it'd be possible to pull dspam's algorithms
> as a lib into the database?
>
> I've been wanting an IMAP spam drop-box like forever, this seems like a
> possibility to achieve this. Combine a spam/not-spam trigger to specific
> IMAP boxes and make those update dspam's statistics - seems possible :)
>
> All I'd need to do to mark something spam is drop the mail in the right
> box from my client. The quarantine box could work similarly.
>

I would be careful about using dspam.

I have documented cases where it lost email and even with posting all the debug
and other logs on the mailing lists I've never received any indication that they
recognize this as a dspam problem.

dspam will lose your mail.

I wouldn't spend a second considering trying to merge a spam filtering
application into an imap server.  It's just plain dumb.  You have lose all
modularity of code or function and now you exponentially increase the
probability that you will have defects and they will be exponentially more
difficult to identify and fix.

Leave dbmail to do what it does and invent something else to do what other clear
functionality you want available.  That's the only sane way to do this kind of
development.

For example -- spam filtering in itself can require a very large box with
extensive amounts of resources in terms of memory, cpu, disk IO....  If you have
that combined with a large dbmail installation and a postgres database what you
end up with is wanting multiple boxes to disperse the loads around.
What you are describing here is the opposite.  Make one box do everything in the
entire environment of email.

There are other things you can do to use imap folders and spam training that
would make more sense.

The *one* feature I did like about dspam is they quarantine spam and you can
sort it out later.  That can be done easily enough and won't interfere with
dbmail if you don't want it to.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 02/03/07 07:17, Tom Allison wrote:
> Alban Hertroys wrote:
>> Jean-Gérard Pailloncy wrote:
[snip]
> The *one* feature I did like about dspam is they quarantine spam and you
> can sort it out later.  That can be done easily enough and won't
> interfere with dbmail if you don't want it to.

This can also be done with server-side filtering.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFxKv3S9HxQb37XmcRAsaaAJ9HvzOKWivAik9VTWxTu/YKf2pwAwCfScHT
15viYSFVVyyTLhy7ukzq0Z0=
=6n70
-----END PGP SIGNATURE-----

> I would be careful about using dspam.
>
> I have documented cases where it lost email and even with posting all
> the debug and other logs on the mailing lists I've never received any
> indication that they recognize this as a dspam problem.
>
> dspam will lose your mail.

*ahem* I have several dspam installations and have never had a problem
with it loosing email.

Sincerely,

Joshua D. Drake




--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/

Tom Allison wrote:
> I would be careful about using dspam.
>
> I have documented cases where it lost email and even with posting all
> the debug and other logs on the mailing lists I've never received any
> indication that they recognize this as a dspam problem.
>
> dspam will lose your mail.

This is getting off topic, but I can't just let this hang in the air.

I know there have been cases - particularly in old versions (> 1 year
old IIRC) where dspam would fail to deliver a message w/o returning an
error code.

I'm sure there are combinations of MTA - dspam - delivery agent that
break. Dspam can be used in so many ways, it's very difficult to cover
every possible scenario it can be deployed in.

However, anything remotely recent (I still use an old 3.6.6) returns an
error code on failure. For the rest, it is up to your MTA to respond to
those error codes, crashes of dspam, etc. I use postfix, and it does
check things like that.

As a matter of fact, I've had trouble in the past where postfix decided
dspam wasn't going to finish processing mail and delivered the message
regardless (that was when my mail/spam machine was a P233 w/ only 64MB
RAM - it simply couldn't cope).

I know there are bugs in dspam, but I doubt it's losing mail.

That said, anything beyond the scope of its performance on postgres is
off topic and should be taken off list.

--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //

Hello Sim,

Am 2007-01-29 14:29:27, schrieb Sim Zacks:
> For example, instead of saving a copy of an email in 1 folder, the same
> email could be indexed to multiple folders. Current email clients mimic
> file cabinets too much in that the system considers an email to be a
> physical entity that can only be stored in one place. Searching for emails
> can also be done much faster with a database then with a traditional mail
> client approach.

Since some time I am coding an IMAP-Client which heavy search functionality
but Server based.  My base is Mutt (with GTK2 Interface), Courier-Imap,
PostgreSQL and Sieve.

The thing is currently in PreAlpha state (it works for me) with realy
limited functions,since I have excluded most thinks to reduce errors
while coding my stuff.

It is only a private Research Project but maybe some times...

Greetings
    Michelle Konzack
    Systemadministrator
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSM LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)

Am 2007-01-30 07:56:32, schrieb Sim Zacks:
> In the traditional imap server the mail is stored in folders on the server
> that are accessed by the client. Therefore you are limited to one indexed
> location per email. Lets say you want to look at your email by customer.
> You would want to have a folder per customer so you can review your
> correspondence with each one individually. If you want to look at

Not needed!

I have a Table per $USER and folders are only virtuell.  So if you have
the message in your INBOX and copy it to INBOX.pgsql the the message in
the table become two references for the TWO folders.

<snip>

Note:  Currently I have 7.41 million messages in my table
       for testing plus the originals on Courier-Imap.

Greetings
    Michelle Konzack
    Systemadministrator
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSM LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)