PG 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

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:

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

-----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.

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 //