Database email storing ...

am 05.04.2006 06:34:31 von daraburke78

Hi,

I'm writing a php app and I need to be able to store and retrieve
incoming and outgoing emails from my server in a mysql database. So
I'll need an MTA capable of storing my mail in a database. And thus be
able to access them from my app.

Can anyone give me some advice here ?
I've checked out dbmail, but I'm wondering if there are any other
solutions... ?

I'll have to use FC2 as a server ...

Thanks,

Re: Database email storing ...

am 05.04.2006 13:01:47 von Sam

Re: Database email storing ...

am 05.04.2006 23:55:01 von AK

daraburke78@gmail.com wrote:

> Hi,
>
> I'm writing a php app and I need to be able to store and retrieve
> incoming and outgoing emails from my server in a mysql database. So
> I'll need an MTA capable of storing my mail in a database. And thus be
> able to access them from my app.
>
> Can anyone give me some advice here ?
> I've checked out dbmail, but I'm wondering if there are any other
> solutions... ?
>
> I'll have to use FC2 as a server ...
>
> Thanks,
>

Sam addressed a part (major part) of your question. Make sure you have
enough space wherever the Database will reside and the row structure is
such that it can handle an email with an attachment of size unknown.

look at RT http://www.bestpractical.com/rt/.

Beyond that it all depends on what you are trying to achieve. The MTA
you use would also have some impact. DJB has a mess822
http://cr.yp.to/mess822.html package whose binaries can be used to
extract some data from the message i.e. headers, body, etc. I have not
run it with anything other then qmail, so do not know whether it will
work with other MTA's. In any case the insertion into a database will
have to be done following the local delivery to a script versus the
methode you seem to want which will have the final delivery by the mta
to insert the data into a database.

AK

Re: Database email storing ...

am 06.04.2006 01:02:18 von Sam

Re: Database email storing ...

am 06.04.2006 08:43:39 von Peter Peters

On Wed, 05 Apr 2006 18:02:18 -0500, Sam wrote:

>> Sam addressed a part (major part) of your question. Make sure you have
>> enough space wherever the Database will reside and the row structure is
>> such that it can handle an email with an attachment of size unknown.
>
>Or of arbitrary MIME complexity.
>
>Or, perhaps, the plan is to store the raw message text as a single blob, in
>which case you still have to do the MIME parsing every time you access the
>blob.

As far as I understood Exchange stores (MIME) attachments a seperate
blobs and reconstructs them when the users views them.

--
Peter Peters, senior netwerkbeheerder
Dienst Informatietechnologie, Bibliotheek en Educatie (ITBE)
Universiteit Twente, Postbus 217, 7500 AE Enschede
telefoon: 053 - 489 2301, fax: 053 - 489 2383, http://www.utwente.nl/itbe

Re: Database email storing ...

am 06.04.2006 13:00:41 von Sam

Re: Database email storing ...

am 06.04.2006 14:58:16 von Peter Peters

On Thu, 06 Apr 2006 06:00:41 -0500, Sam wrote:

>A "database" is not a technically accurate description for the mess that
>Exchange uses for storage.

Even a filesystem is a database. But you are right. In the sense of a
professional database Exchange can not be considered using a database.

But I doubt if even a real database can weed out the mess Exchange makes
of mail messages.

--
Peter Peters, senior netwerkbeheerder
Dienst Informatietechnologie, Bibliotheek en Educatie (ITBE)
Universiteit Twente, Postbus 217, 7500 AE Enschede
telefoon: 053 - 489 2301, fax: 053 - 489 2383, http://www.utwente.nl/itbe

Re: Database email storing ...

am 06.04.2006 23:16:33 von kael

Sam wrote:
> Storing E-mail in a database is an idea that occasionally someone comes
> up with, who really has no experience in messaging and networking.
>
> But anyone who actually has experience in this area will tell you that
> it's a stupid idea.

Why ? Aren't databases supposed to increase search speed and to improve
data retrieval ?

What about _mairix_ http://www.rpcurnow.force9.co.uk/mairix/ and
_Decimail_ http://decimail.org/ ?

--
kael

Re: Database email storing ...

am 07.04.2006 01:03:03 von Sam

This is a MIME GnuPG-signed message. If you see this text, it means that
your E-mail or Usenet software does not support MIME signed messages.
The Internet standard for MIME PGP messages, RFC 2015, was published in 1996.
To open this message correctly you will need to install E-mail or Usenet
software that supports modern Internet standards.

--=_mimegpg-commodore.email-scan.com-18224-1144364582-0007
Content-Type: text/plain; format=flowed; charset="US-ASCII"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

kael writes:

> Sam wrote:
>> Storing E-mail in a database is an idea that occasionally someone comes
>> up with, who really has no experience in messaging and networking.
>>
>> But anyone who actually has experience in this area will tell you that
>> it's a stupid idea.
>
> Why ? Aren't databases supposed to increase search speed and to improve
> data retrieval ?

1) Unless you redefine some fundamental physical constants of our universe,
reading from a flatfile must always be faster than trying to read the same
data out of a database, which is a HELL of a lot more involved process than
just reading a flat file: you have to deal with the overhead of SQL, the
on-disk database structures, database locking, and pushing everything
through a centralized server daemon process that must multiplex itself
amongst all clients. All that overhead is not going to magically disappear.
If the database engine stores its data on a raw disk partition, it might be
technically faster to read raw disk blocks instead of reading files out of a
filesystem; however you have a long way to row before you overcome all the
additional baggage you get to deal with; and I'm highly skeptical that a
third part vendor's database engine, that mostly contains
platform-independent, generic code that manages high level database
structures, with only a small portion underneath that deals with the host
platform's idiosynchronies, will be able to outperform a native filesystem
that's been written, and tuned, for its platform.

2) Please define a relational database schema that can be used to
efficiently process arbitrary messages, of arbitrary MIME complexity and
attachment size.

Value-added from databases don't come into play until the amount of data
they handle begins to exceed the reasonable limits imposed by the native
filesystem, so until individual E-mail messages routinely begin exceeding a
few gigs in size, each, with hundreds of attachments, storing them natively
on the filesystem will always be faster than trying to pile them into a
bunch of ill-designed SQL tables. Provided, of course, that the underlying
filesystem does not suck *cough*Solaris*cough*.

> What about _mairix_ http://www.rpcurnow.force9.co.uk/mairix/ and
> _Decimail_ http://decimail.org/ ?

What about it? Do you have any benchmarks to show how well they perform
against any one of a number of servers that use the native filesystem?

--=_mimegpg-commodore.email-scan.com-18224-1144364582-0007
Content-Type: application/pgp-signature
Content-Transfer-Encoding: 7bit

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

iD8DBQBENZ4mx9p3GYHlUOIRAs7lAJ4tSYu6L2btzTRBpli2w4n6rJGnxQCf RqwQ
mFh6JOGefk77xA/9SQMVrL0=
=3K+a
-----END PGP SIGNATURE-----

--=_mimegpg-commodore.email-scan.com-18224-1144364582-0007--

Re: Database email storing ...

am 07.04.2006 11:12:13 von Peter Peters

On Thu, 06 Apr 2006 23:16:33 +0200, kael wrote:

>Sam wrote:
>> Storing E-mail in a database is an idea that occasionally someone comes
>> up with, who really has no experience in messaging and networking.
>>
>> But anyone who actually has experience in this area will tell you that
>> it's a stupid idea.
>
>Why ? Aren't databases supposed to increase search speed and to improve
>data retrieval ?

Besides what Sam mentions the only possible advantage of databases is
the fact they could store just one instance of MIME attachments and
messages if they are send to multiple people.

--
Peter Peters, senior netwerkbeheerder
Dienst Informatietechnologie, Bibliotheek en Educatie (ITBE)
Universiteit Twente, Postbus 217, 7500 AE Enschede
telefoon: 053 - 489 2301, fax: 053 - 489 2383, http://www.utwente.nl/itbe

Re: Database email storing ...

am 07.04.2006 21:22:41 von kael

Sam wrote:
> kael writes:
>
>> What about _mairix_ http://www.rpcurnow.force9.co.uk/mairix/ and
>> _Decimail_ http://decimail.org/ ?
>
> What about it? Do you have any benchmarks to show how well they perform
> against any one of a number of servers that use the native filesystem?

You mean a benchmark that would demonstrate how Courier is a turtle
compared to these tools ? No, I haven't any. :-) (And Courier is fast as
far as I remember).

I'm just assuming that these softwares might be faster because more
recent. It'd interesting to know how fast is the IMAP SEARCH command
with that kind of back-end.

--
kael

Re: Database email storing ...

am 08.04.2006 02:58:09 von Sam

This is a MIME GnuPG-signed message. If you see this text, it means that
your E-mail or Usenet software does not support MIME signed messages.
The Internet standard for MIME PGP messages, RFC 2015, was published in 1996.
To open this message correctly you will need to install E-mail or Usenet
software that supports modern Internet standards.

--=_mimegpg-commodore.email-scan.com-3265-1144457889-0004
Content-Type: text/plain; format=flowed; charset="US-ASCII"
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

kael writes:

> Sam wrote:
>> kael writes:
>>
>>> What about _mairix_ http://www.rpcurnow.force9.co.uk/mairix/ and
>>> _Decimail_ http://decimail.org/ ?
>>
>> What about it? Do you have any benchmarks to show how well they perform
>> against any one of a number of servers that use the native filesystem?
>
> You mean a benchmark that would demonstrate how Courier is a turtle
> compared to these tools ? No, I haven't any. :-) (And Courier is fast as
> far as I remember).
>
> I'm just assuming that these softwares might be faster because more
> recent. It'd interesting to know how fast is the IMAP SEARCH command
> with that kind of back-end.

It will be absolutely dog-ass slow. IMAP SEARCH is not a straightforward
byte-for-byte match, but it must factor into account each individual MIME
section's character set, and MIME encoding (quoted-printable, and/or
base64). IMAP SEARCH must search each MIME section individually, after
transcoding the search string into the section's character set.

And before you tell me: just transcode everything to UTF-8, decode the MIME
encoding, and store all MIME text/* sections as MIME-decoded UTF-8, and
search everything in UTF-8. Two words in response to that:
multipart/signed.

Ok, so you're going to store both the original MIME content, and the
decoded/transcoded version, which is used for searching only. Ok,
double your mail store's size, and make the existing relational schema
even more obfuscated.

Maybe you're going to decode the MIME sections on demand, after the first
time the message is searched? Ok, so you're going to take a major hit on
the first SEARCH, the lusers will think you're dog-ass slow, and give up on
you.

The more you think about this, the more you will be convinced that
traditional relational database schema just DOES NOT WORK for E-mail.

Now, maybe if you designed a custom database, and a message access API
that's custom-tailored for E-mail access.

Congratulations, you've reinvented MS Exchange. Now, go and ask an Exchange
admin what happens when the Exchange DB gets corrupted, or when it grows too
big for its britches.

--=_mimegpg-commodore.email-scan.com-3265-1144457889-0004
Content-Type: application/pgp-signature
Content-Transfer-Encoding: 7bit

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

iD8DBQBENwqhx9p3GYHlUOIRAuFpAJ4lyslvpaHRo6aJVShIZVMhqJ7XLwCf Wior
zez1ALDDSD6nhzqn4WifG0k=
=RlVa
-----END PGP SIGNATURE-----

--=_mimegpg-commodore.email-scan.com-3265-1144457889-0004--