作者 主題: store mails into mysql --- from google  (閱讀 2433 次)

0 會員 與 1 訪客 正在閱讀本文。

asako

  • 活潑的大學生
  • ***
  • 文章數: 242
    • 檢視個人資料
store mails into mysql --- from google
« 於: 2003-10-22 01:00 »
寄件者:Matthew England (mengland@chi.mti.com)
主旨:RE: How automatically to store emails into MySQL?
View: Complete Thread (共 7 條留言)  
Original Format
網上論壇:mailing.database.mysql
日期:2000/03/31
 

The Perl and PHP examples provided by Indrek and Michael are great!  These
examples are just the things I was looking for to help get me started.

Any other email-header parsing utilities besides MIME::Parser?  Anything I
can access via PHP?

I answer Indrek's question below:

At 3/30/00 05:28 PM, indrek siitan wrote:
>um. care to explain, what you're planning to do with the e-mail
>afterwards?

I like to describe my goal as an application that mimicks an email-based
"trouble-tracking" system (eg, GNATS, a GNU email-based bug-tracking).  I
want to collect messages/info about customers, products, and events and be
able to retrieve these messages accordingly.  We've chosen to distribute
messages via email in our initial product implementation.

Some of the emails will contain specific, company-proprietary
information.  Other emails will be free form.

I absolutely need to store each message/email in it's entirety--no headers
chopped off or anything else.  This is very important for my
message-archiving requirements.

I will also want to parse the email headers so that I can store things like
date, time, "case ID", originator, etc.  I'm not yet concerned about data
redudancy (from the header info being copied into record
fields/columns).  Should I be concerned about this?

-Matt

>one thing is for sure - you can't use mysql client for that. the
>easiest way is to forward mail to a perl script, what does the job.
>the easiest script should look something like this (i'm writing
>it directly to the e-mail, so i'm not responsible for this being
>absolutely bugfree):
>
>--- 8< ---
>
>#!/usr/bin/perl
>use DBI;
>
>$dbh=DBI->connect("dbi:mysql:database", 'user', 'password');
>
>my $mailbody=''; while (<STDIN>) { $mailbody.=$_; }
>my $qmailbody=$dbh->quote($mailbody);
>
>$sth=dbh->prepare("INSERT INTO mail (body) VALUES($qmailbody)");
>$sth->execute;
>
>$dbh->disconnect;
>
>
>--- 8< ---
>
>if you want to store e-mail headers into separate fields, i
>recommend you taking a look at MIME::Parser module. this does
>a really nice job taking the mail into pieces.
>
>i have a script that does that, but it's not complete yet
>(craps out on some types of attachments) :(
>
>
>Rgds,
>   Tfr
>
>   --==< tfr@cafe.ee >==< http://tfr.cafe.ee/ >==< +1-504-4467425 >==--


--
---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread32776@lists.mysql.com

To unsubscribe, send a message to:
    <mysql-unsubscribe-mysql=freebsd.csie.nctu.edu.tw@lists.mysql.com>

asako

  • 活潑的大學生
  • ***
  • 文章數: 242
    • 檢視個人資料
store mails into mysql --- from google, part two
« 回覆 #1 於: 2003-10-22 01:02 »
寄件者:"indrek siitan" (tfr@cafe.ee)
主旨:RE: How automatically to store emails into MySQL?
 
 
View this article only
網上論壇:mailing.database.mysql
日期:2000/03/31
 


Hi,

> Any other email-header parsing utilities besides MIME::Parser?  
> Anything I can access via PHP?

sorry, I don't know about PHP - I'm an EmbPerl addict :)

so about Perl - MIME::Parser is excellent if you have to handle
e-mails with attachments. if you're dealing with just plain-text
messages, you can just parse the headers yourself. something
like that (another quick solution written directly here):

--- 8< ---

#!/usr/bin/perl
use DBI;

$dbh=DBI->connect("dbi:mysql:database", 'user', 'password');

my $ishdr=1;
my $header='';

my $mailbody='';

while (<STDIN>)
 {
  $mailbody.=$_;
  if ($_ eq '') { $ishdr=0; }
  if ($ishdr) { $header.=$_; }
 }

$header=~s/\n\s+/ /g;
%headers=(split /^(\S*?):\s*/m, $header);

$mailfrom=$dbh->quote($headers{From});
$maildate=$dbh->quote($headers{Date});
$mailsubj=$dbh->quote($headers{Subject});
$mailbody=$dbh->quote($mailbody);

$sth=dbh->prepare("INSERT INTO mail (from,date,subj,body) \
                   VALUES($mailfrom,$maildate,$mailsubj,$mailbody)");
$sth->execute;


$dbh->disconnect;


--- 8< ---

refer to RFC-822 for exact message header details.


Rgds,
  Tfr

  --==< tfr@cafe.ee >==< http://tfr.cafe.ee/ >==< +1-504-4467425 >==--

--
---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail mysql-thread32780@lists.mysql.com

To unsubscribe, send a message to:
    <mysql-unsubscribe-mysql=freebsd.csie.nctu.edu.tw@lists.mysql.com>