October 11th, 2000, 11:53 PM
I am trying to set up MySQL to automatically store received email in a database. I am using RH6.2 with sendmail. sendmail will put all new mails to /var/spool/mail/userid, which is a text file. Could someone please tell me how to read this file and store the emails in mySQL....
Thanks for your help in advance!
October 13th, 2000, 05:07 AM
Depends whether you want also to keep the plain version in /var/spool/mail/<user>. If you just want the MySQL version, the best thing is to write a new delivery agent (I'd do it in perl with DBI and DBD::MySQL). Have it read each header line in turn, storing special ones in variables, such as From, Subject, To, Cc, Date, and collecting the other headers together in an "otherheaders" variable. The end of the headers is a blank line. Then store the whole body in a variable. Then connect to the MySQL database and put the special headers each into their own VARCHAR column(s), and use a TEXT or BLOB column for the otherheaders and body contents. You might want to use Time::Parsedate to interpret the Date header and convert it into a MySQL native time/date. Also bear in mind there might be multiple To and Cc addresses. Once you have it working properly from the command line, you can change the Mlocal line in sendmail.cf to call your delivery program instead of /bin/mail or /usr/bin/procmail or whatever. You will then be able to pick up the envelope recipient from @ARGV (which may be different from the To or Cc line), which is what tells the delivery agent which user to deliver to.
There's a lot to take into account - hope this helps!
October 15th, 2000, 10:07 PM
Thanks Tony, thats an aweful lot of information for a newbie like me. I guess first I have to write something that parse the email from sendmail, and then somehow feed it into mySQL. Later on I will have to show the database on a webpage, thus probably using PHP to do it. Would PHP also help in the parsing of the email in the first place? Man that's a lot to learn... I had no prior exp. in Linux/sendmail/PHP/perl/mySQL.... learning all these in a month is really something. Therefore I really appreciate and need your help. Thanks again.