SourceForge Email Changes

*** Disclaimer ***

Use at your own risk. Absolutely no warranty with the following procedure or supplied script and code.

I wrote a perl script to do this on sf.net. But there is a problem. My email account got blocked after a few emails on shell.sf.net. Looks like you are restricted to only a few emails per day per user on shell.sf.net. What a joke.

SMTP error from remote mailer after RCPT TO:: host sc8-pr-sshgate-b.sourceforge.net [10.5.1.1]: 550-You have sent too much email today. Please try again\n550 tomorrow.

But it does work when you are within their set quata.

Anyway:

Step 1) Create email table in SF drupal database

Create a DB table in your drupal database using mysql command:

Hint: Cut and paste the SQL statement into the window after executing the "cat and pipe and mysq"l command. Control-D to finish.

<I>Shell> cat | mysql -h _host_ -u _user_ --password=_password_ _sf_net_databaseName_</I>
CREATE TABLE email (
  id int(11) NOT NULL auto_increment,
  receiver varchar(64) NOT NULL default '',
  `subject` varchar(64) NOT NULL default '',
  body text NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=MyISAM;

Step 2) Modify user.module file

Add DB query statement to user.module to insert copy of the outbound email in our table.

• a) Locate the method "user_mail()" in user.module. In my installation that was around line number 1820 in the text file.

• b) Add the db_query command right after the first "else" statement:
  

<I>Shell> vi modules/user.module</I>
db_query("INSERT INTO email VALUES (null, '%s', '%s', '%s')", $mail, $subject, $message);

Step 3) Cut/Paste this perl script

Cut and paste the Perl script that checks the "email" database table, extracts messages and cleans up all records from the "email" table. In my case, I keep downloaded themes and modules in ext/ directory so that when I upgrade I know what I've added, so I also keep the sendmail.pl script in the same directory.

Another words, $DRUPAL_HOME/ext/sendmail.pl

Hint: after executing the cat command with stdout redirection to file, cut and paste the perl script into the same window and hit Control-D to close stdin and tell cat command we're finished writting.

<I>Shell> cat > ext/sendmail.pl </I>
#!/usr/bin/perl

use Mail::Sendmail;

############################################################
# Modify the following 4 variable to match your environment
############################################################
$dbHost = "";         # <B><I>your SF mysql db hostname goes here, i.e. mysql4-j</I></B>
$dbUser = "";         # <B><I>your rw or admin db user name goes here</I></B>
$dbUserPassword = ""; # <B><I>your password goes here</I></B>
$dbDatabase = "";     # <B><I>your mysql database name goes here. i.e. j89169_drupal</I></B>

#
# Should not have to modify any of the blow code or variables
#

$dbTable = "email";

$mysql = "/usr/bin/mysql";

$mysqlCmd = "$mysql ";
$mysqlCmd .= "-h $dbHost ";
$mysqlCmd .= "-u $dbUser ";
$mysqlCmd .= "--password=$dbUserPassword ";
$mysqlCmd .= "$dbDatabase";

# Get a list of messages

$cmd = qq#echo "SELECT id FROM $dbTable" | $mysqlCmd#;
chop(@messageList = `$cmd`);

# Get rid of the first entry, its column header
shift(@messageList);

#
# Iterate through all the message indexes
#
foreach(@messageList) {

        %mail = ( to      => query($_, "receiver"),
                  subject => query($_, "subject"),
                  Message => query($_, "body")
                );

        sendmail(%mail);
}

#
# Now remove all sent message from table so we don't try to
# send them again on the next run.
#
$cmd = qq#echo "DELETE from email" | $mysqlCmd#;
`$cmd`;

sub query {
        my($id, $col) = @_;

        $cmd = qq#echo "SELECT $col FROM $dbTable WHERE id=$id" | $mysqlCmd#;
        chop(my @r = `$cmd`);

        my $body = $r[1];
        $body =~ s/\\n/\n/g;

        return $body;
}

Step 4)

Add a new line to your crontab file on shell.sf.net using the "crontab -e" command:

<I>Shell> crontab -e</I>
* * * * * /home/groups/j/jn/jnetstream/htdocs/ext/sendmail.pl

All * indicate to execute this command every 1 minute interval. You can change it to 10 minute if you'd like. Check man pages, "man 5 crontab" for proper syntax of this file if you are confused by all the "*" there.