Brent,
see below:
On Thu, 10 Feb 2005, Brent Baisley wrote:
> I am looking to store all incoming emails into a MySQL database. I've
> looked into dbmail, but can't get it to compile under Mac OSX (I posted
> a message on that list). I was wondering if anyone could point me in
> another direction to use MySQL as an email message store. I don't need a
> webmail interface, just a way of getting messages from a mail server to
> a MySQL database. Preferably as a direct transfer, but it could be a
> script that runs periodically. Currently it seems the best path is using
> Perl, but I would think this has been done before, just can't find it on
> google.
I use procmail to forward a copy of certain mail messages to the following
shell script which you can use as a starting point.
Cheers,
Thomas Spahni
#!/bin/sh
# This shell script is free software; all possible disclaimers apply
#
# get Mail from stdin and store into MySQL database.
#
#
DBASE=mydbase
SENDER=sendertable
ARCHIV=messagearchive
MYSQL="mysql -N"
DECODEMIME="/home/user/bin/decmime.pl"
if test "$1" == "initialize" ; then
echo "CREATE TABLE IF NOT EXISTS $SENDER (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
whencrtd DATETIME,
lastaccs TIMESTAMP,
mailaddr VARCHAR(255) NOT NULL,
actcount INT DEFAULT 0,
remarks VARCHAR(255) NOT NULL,
UNIQUE INDEX (mailaddr)) TYPE=MyISAM;" | $MYSQL $DBASE
#
echo "CREATE TABLE IF NOT EXISTS $ARCHIV (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
whencrtd DATETIME,
mailfrom VARCHAR(255) NOT NULL,
mailsubj VARCHAR(255) NOT NULL,
textbeitrag TEXT,
INDEX (mailfrom),
FULLTEXT INDEX (mailsubj,textbeitrag)) TYPE=MyISAM;" \
| $MYSQL $DBASE
#
exit 0
fi
# functions:
# code for escaping "$1" argument
# escaped string goes to stdout
TAB=' '
myesc() {
echo "$1" | sed -e 's/\\/\\\\/g' -e "s/'/\\\\'/g" -e "s/$TAB/\\\\t/g" \
| tr -d '\012\015'
}
mysqltextescape() {
echo "$1" | sed -e 's/\\/\\\\/g' -e "s/'/\\\\'/g" -e "s/$TAB/\\\\t/g" \
-e 's/$/\\n/g' -e '$ s/\\n$//' | tr -d '\012\015'
}
mysql_like_escape() {
echo "$1" | sed \
-e 's/\\/\\\\/g' \
-e "s/'/\\\\'/g" \
-e 's/%/\\%/g' \
-e 's/_/\\_/g'
}
# mail comes from stdin
TEXT="$(cat -)"
FROMADDR="$(echo "$TEXT" | formail -cx From: \
| sed -e "s/^ *//" | $DECODEMIME)"
MYFROMADDR="$(mysql_like_escape "$FROMADDR")"
FROMEXISTS="$(echo "SELECT mailaddr FROM $SENDER \
WHERE mailaddr LIKE '$MYFROMADDR';" | $MYSQL $DBASE)"
if test -z "$FROMEXISTS" ; then
# insert new entry
echo "INSERT INTO $SENDER \
VALUES(NULL, NOW(), NULL, '$(myesc "$FROMADDR")', 1, '');" \
| $MYSQL $DBASE
else
# update existing entry
echo "UPDATE $SENDER \
SET lastaccs = NULL, actcount = actcount+1 \
WHERE mailaddr LIKE '$MYFROMADDR';" | $MYSQL $DBASE
fi
# put into archiv
# Global variables for metamail
export KEYHEADS=''
export MM_NOASK=1
export MM_NOTTTY=1
export MM_QUIET=1
export MAILCAPS="/home/tsp/bin/mailcap"
# White Space, one SPACE and one TAB:
WS=' '
BODY="$(echo "$TEXT" | metamail -B -q -x 2>/dev/null | formail -I "" \
| sed -e "s/^[$WS]*$//" \
| sed -n -e "1,$ H" -e "$ g" \
-e "s/^\\n*//" -e "s/\\n*$//" \
-e "$ p")"
# beautify the subject line:
SUBJECT="$(echo "$TEXT" | formail -x Subject: | sed \
-e "s/AW: /Re: /g" \
-e "s/Aw: /Re: /g" \
-e "s/RE: /Re: /g" \
-e "s/R: /Re: /g" \
-e "s/^ *//" \
-e "s/ */ /g" \
-e "s/Re: Re: Re: Re: /Re: /" \
-e "s/Re: Re: Re: /Re: /" \
-e "s/Re: Re: /Re: /")"
MYBODY="$(mysqltextescape "$BODY")"
echo "INSERT INTO $ARCHIV \
VALUES(NULL, NOW(), '$(myesc "$FROMADDR")', \
'$(myesc "$SUBJECT")','$MYBODY');" \
| $MYSQL $DBASE
exit 0
###################################################
And this is decmime.pl
###################################################
#!/usr/bin/perl -w
use strict;
use MIME::Words qw(:all);
#my $mimewordarg;
my $decoded;
my $mimestdin;
#$mimewordarg = $ARGV[0];
$mimestdin = <STDIN>;
chomp $mimestdin;
$decoded = decode_mimewords(
$mimestdin,
);
print $decoded, "\n";
exit(0);
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
