Flutterby™! : Want... 2007-07-24 00:12:25.231423+00

Want...

2007-07-24 00:12:25.231423+00 by Dan Lyke 8 comments

Now more than ever, I want an email client that will interface with an SQL database such that as I add records to, say, keep track of what rest stop a volunteer will be working at or what sized T-shirt they wear, I have references back to the email messages that lead to that decision.

To me that's the sort of specialized thing which doesn't say "custom client" but instead says "plug-in or add-on for your existing client." It's practically the definition of a specialized task: Most users won't see the point, but for the users who do want it, it's EXTREMELY desirable.

A short-term workaround, for immediate record-keeping needs, would be to add a large-text-blob field to your table and drop the text of the email with full headers into that.

I think it could totally be an add-in for the existing client if that client used an SQL database as its backing store.

Yeah, I could copy and paste into another database, but... well... at that point I'm building enough of a GUI that I'm wondering how far it is from there to reading and sending mail.

I've always wondered why MS Exchange didn't use SQL Server for a datastore. Email is a very relational information exchange system and the investment in developing SQL Server and building fault-tolerant servers would carry-over for free. Exchange would just be the middle-tier application...

Or, the same for any other mail server. On Unix, email persists as files stored in user directories.

One possibility is to use IMAP and store an identifier for the email in the SQL database. Most SQL front-end enviroments (Perl, Access, VB, etc) also speak IMAP. You could, for instance, create a mailbox on your server called "TAMadmin" and forward all mail with such info to it. Then have your user interface to the database also connect to the TAMadmin mailbox via IMAP.

Of course, you could just have everyone email "TAMadmin" with their requests and you could make a simple mail client via IMAP that also understood your SQL database well enough to allow the user to go through these emails and update the database accordingly.

Ah.... Brain cells waking back up... Once upon a time, I worked with a group that was moving some IBM mainframe email forms to MS Outlook. This was one of the dilemmas that IBM solved but the Unix world never really got: The referential integrity of email can be very important. In the 'net/*nix world, privacy trumps referential integrity - hence the problems with SPAM and Phishing.

IBM tracked email in a way that allowed for emailable forms that could be "signed" and submitted to other departments. It was a melding of the database that tracks applications, like HR and inventory, with email. I'm sure their email actually just lived in the database (in fact, didn't O/S 400 treat everything as a database tuple? - kind of like how Unix treats everything as a file?).

The whole reason Outlook allowed .VBS emails was to provide similar functionality - a form in the email that could be modified and forwarded to the next cubicle-dweller. Of course, in Microsoft's schema, you had to have an ODBC datasource to connect to from the .VBS code and you had to really understand what the data was. And you could do things like execute local applications ;)

Ditto the IMAP comment from Eric...couldn't you just pull out and store the msg-id header (or headers) and pull them down from the server when you need to look at them?

Hmmm... Thinking maybe it's time to see if one of the Emacs mail clients understands IMAP, pick an IMAP server that backs up onto SQL, and go that direction.

I'm not sure, but could performance be one of the reasons why e-mail hasn't left a larger footprint in relational databases?

Of course, this could lead to a discussion of the pros and cons of regular expression filesystem searching vs. indexed SQL queries, and the relative flexibility and merits of each method (and others).

at least in the 90's, AOL's mail was in some big honkin' Sybase databases. Granted, "me too!" is a small message, but considering the volume of them, AOL's mail system was pretty spiffy. (AOL the company is pretty awful, but AOL the technology was actually really cool)

AOL dealt with a lot of scalability issues long before the internet became popular. They had 1M users before they even opened their gateway to the 'web.

But by being a closed environment, they could do a lot of stuff folks on the open 'net couldn't get away with. For instance, they maintained referential integrity of email messages: You knew that if the email came from 'ebradway' that it either came from me or someone logged into my account. But remember how long it took for email from the outside world to show up in an AOL email account?