A simple Swiftmailer daemon for sending emails saved to DB

Swift Mailer is a fantastic library for sending email with php. Discuss this library or ask any questions about it here.

Moderators: Chris Corbyn, General Moderators

Post Reply
EricWinchell
Forum Newbie
Posts: 5
Joined: Tue Aug 28, 2007 2:27 pm

A simple Swiftmailer daemon for sending emails saved to DB

Post by EricWinchell »

This is a daemon I've written to have Swiftmailer send emails that have been saved to a database. I'm using CakePHP so it pulls settings from config/database.php for the connection.

It's working so far but if you see any glaring problems with it, do me the favor of posting here!

Code: Select all

<?php

// This script runs like a service on the server, sending emails and marking them as sent in the database. 
require_once "vendors/Swift.php";
require_once "vendors/Swift/Connection/SMTP.php";
require_once "config/database.php";

$db = new DATABASE_CONFIG;
$dbhost = $db->default['host'];
$dbuser = $db->default['login'];
$dbpass = $db->default['password'];
$dbdb = $db->default['database'];

if(strpos($_SERVER['OS'], 'Windows') !== FALSE) {
	define('SERVER_SOFTWARE', 'windows');
	define('SMTP_HOST', 'smtp.comcast.net');
} else if(strpos($_SERVER['OS'], 'Debian') !== FALSE) {
	define('SERVER_SOFTWARE', 'linux');
	define('SMTP_HOST', 'smtp.live.net');
}

// End settings //


// Set up an infinite loop.
while(1) {
	// While there is no connection, show this message.
	do {
		sleep(10);
		echo 'Trying to connect...' . "\n";
		$mysql = mysql_connect($dbhost, $dbuser, $dbpass);
	} while (!$mysql);

	mysql_select_db($dbdb);

	// If any part of the following loop fails, echo an error and use a 'break' statement.
	// A 'break' statement causes a MySQL disconnect and reconnect... not sure whether this 
	// is necessary. 
	while(1) {
		// SELECT the next 20 emails.
		$results = mysql_query('
				SELECT emails.*, email_recipients.recipient AS recipient, email_recipients.recipient_name AS recipient_name, email_recipients.id AS recipient_id
				FROM emails, email_recipients
				WHERE emails.id = email_recipients.email_id 
				AND email_recipients.sent = 0 
				ORDER BY created ASC LIMIT 20'
		);
		if(!$results) { 
			echo date("F j, Y, g:i a") . " SELECT query failed.\n";
			break;
		}
		if(mysql_num_rows($results) == 0) {
			echo date("F j, Y, g:i a") . " No emails to send right now.\n";
			break;
		}
		
		// Loop through the emails, attempting to send each one.
		// After each send, mark the email as sent with an SQL statement.
		while($email = mysql_fetch_assoc($results)) {

			try {
				// Start Swift
				$swift =& new Swift(new Swift_Connection_SMTP(SMTP_HOST));
			} catch(Exception $e) {
				echo date("F j, Y, g:i a") . " Exception thrown creating SMTP Connection for email #$email[id]\n";
				continue;
			}

			// Create the message
			$message =& new Swift_Message($email['subject'], $email['body']);

			// Now check if Swift actually sends it
			try {
				$send = $swift->send($message, new Swift_Address($email['recipient'], $email['recipient_name']), new Swift_Address($email['sender'], $email['sender_name']));
				if($send) {
				 	echo "Sent email #" . $email['id'] . "\n";
				 	// Mark email as sent. This could be optimized for performance by creating an array of sent emails and doing
				 	// a bunch in a single query.
					$mark_as_sent = mysql_query("
							UPDATE email_recipients
							SET email_recipients.sent = 1, email_recipients.date_sent = NOW()
							WHERE email_recipients.id = $email[recipient_id]"
					);
					if($mark_as_sent == false) {
						echo date("F j, Y, g:i a") . " Problem marking email #" . $email['id'] . " as sent!\n";
					}
				} else {
					echo date("F j, Y, g:i a") . " Failed to send email #" . $email['id'] . "\n"; 
				}
				$swift->disconnect();
			} catch(Exception $e) {
				echo date("F j, Y, g:i a") . " Exception thrown for email #$email[id]\n";
				$swift->disconnect();
				continue;
			}
		}
	
		mysql_free_result($results);

	}
	
	mysql_close($mysql);
}

?>
Table DDL's:

Code: Select all

CREATE TABLE `emails` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `sender` varchar(255) NOT NULL,
  `sender_name` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `body` text NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`)


CREATE TABLE `email_recipients` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `email_id` int(10) unsigned NOT NULL,
  `recipient` varchar(255) NOT NULL,
  `recipient_name` varchar(255) NOT NULL,
  `sent` tinyint(1) unsigned NOT NULL default '0',
  `date_sent` datetime NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY  (`id`)

Last edited by EricWinchell on Thu Aug 30, 2007 1:09 pm, edited 1 time in total.
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

I'm sure that will help a few users who wanted similar things, thanks :) I never actually posted my version. We both approach it with the same logic, except I think I run less queries and mine works when you run it on a farm of servers simultaneously (each server "locks" the emails it's dealing with so no two servers cause duplicates). We needed that because we run a server farm.

I never could get the SIG.. catching to work :(

Code: Select all

#!/usr/bin/php
<?php

//Uses Swift (naturally, I mean who wouldn't?)..
require_once dirname(__FILE__) . "/../conf/SysConf.php";
require_once dirname(__FILE__) . "/../classes/swift/Swift.php";

/**
 * Configuration values.
 */
class Config
{
        const DB_HOST = DATABASE_HOSTNAME;
        const DB_USER = DATABASE_USERNAME;
        const DB_PASS = DATABASE_PASSWORD;
        const DB_NAME = DATABASE_DATABASE;
        const CHECK_INTERVAL = 1;
        const MAILER_TYPE = MAILER;
        const SMTP_HOST = SMTP_HOST;
        const SMTP_PORT = SMTP_PORT;
        const SENDMAIL_COMMAND = SENDMAIL_PATH;
        const MAX_BATCH_SIZE = 50;
}

/**
 * Makes instances of Swift.
 */
class SwiftFactory
{
        /**
         * Create a new instance of Swift using the config values.
         * @return Swift
         */
        public static function getSwift($type=Config::MAILER_TYPE)
        {
                switch (strtolower($type))
                {
                        case "smtp":
                                Swift_ClassLoader::load("Swift_Connection_SMTP");
                                $conn = new Swift_Connection_SMTP(Config::SMTP_HOST, Config::SMTP_PORT);
                                break;
                        case "sendmail":
                                Swift_ClassLoader::load("Swift_Connection_Sendmail");
                                $conn = new Swift_Connection_Sendmail(Config::SENDMAIL_COMMAND);
                                break;
                        case "mail": default:
                                Swift_ClassLoader::load("Swift_Connection_NativeMail");
                                $conn = new Swift_Connection_NativeMail();
                                break;
                }
                $swift = new Swift($conn, "[192.168.1.1]");
                return $swift;
        }
}

/**
 * Database handler (ad-hoc, hack).
 */
class DB
{
        /**
         * A MySQL resource, lazy loaded
         * @var resource
         */
        private static $connection = null;

        /**
         * Get the MySQL connection.
         * @return resource
         */
        public static function getConnection()
        {
                if (self::$connection === null)
                {
                        self::$connection = mysql_connect(
                                Config::DB_HOST, Config::DB_USER, Config::DB_PASS) or die(mysql_error());
                        mysql_select_db(Config::DB_NAME, self::$connection) or die(mysql_error(self::$connection));;
                }
                return self::$connection;
        }
        /**
         * Run a query against the connection.
         * @param string Query to run.
         * @return resource
         */
        public static function executeQuery($query)
        {
                $conn = self::getConnection();
                $result = mysql_query($query, $conn) or die(mysql_error($conn));
                return $result;
        }
        /**
         * Fetch the row from the resultset as an assoc array.
         * Returns false past the end of the resultset.
         * @param resource MySQL result
         * @return array
         */
        public static function nextRow($result)
        {
                $row = mysql_fetch_assoc($result);
                return $row;
        }
        /**
         * Escape a string for insertion into SQL.
         * @param string Unfiltered input
         * @return string Filtered output
         */
        public static function escape($data)
        {
                $conn = self::getConnection();
                return mysql_real_escape_string($data);
        }
}

/**
 * The main deamon.
 */
class Daemon
{
        /**
         * The UNIX timestamp when the process began.
         * @var int
         */
        protected static $timestarted;
        /**
         * The number of emails sent since the process began.
         * @var int
         */
        protected static $emailsProcessed = 0;

        /**
         * Ctor.
         * This class can only be invoked publically using Daemon::start().
         */
        private function __construct()
        {
                self::$timestarted = time();
        }
        /**
         * Displays usage information at end of script execution.
         */
        public static function stop()
        {
                $total_time = time() - self::$timestarted;
                fwrite(STDOUT, "SIGINT caught." . PHP_EOL .
                        "Closing down. Process ran for " . $total_time . " seconds, and sent " .
                        self::$emailsProcessed . " emails." . PHP_EOL . "BYE!" . PHP_EOL);
                exit(0);
        }
        /**
         * Read from the database and try sending out a single batch.
         */
        public function runBatch()
        {
                $key = md5(microtime() . getenv("HOSTNAME"));
                $subject = null;
                $body = null;
                $sender = null;
                $recipients = new Swift_RecipientList();
                $user_mail_ids = array();

                //Grab hold of what we can!
                DB::executeQuery("
                UPDATE
                        user_spooled_mail
                SET
                        active_key = '" . DB::escape($key) . "',
                        locked = 1
                WHERE
                        active_key IS NULL
                        AND locked = 0
                        AND flag = 'U'
                LIMIT " . DB::escape(Config::MAX_BATCH_SIZE));

                //Grab all records we have a hold on, on a per-batch basis
                $query = "
                SELECT
                        a.id AS mail_id,
                        b.id AS user_mail_id,
                        a.subject,
                        a.body,
                        a.sender_email,
                        a.sender_name,
                        a.timesent,
                        b.email,
                        b.name
                FROM
                        mail_spool AS a,
                        user_spooled_mail AS b
                WHERE
                        b.mailid = a.id
                        AND b.flag = 'U'
                        AND b.active_key = '" . DB::escape($key) . "'
                ORDER BY
                        a.id,
                        b.id
                LIMIT " . DB::escape(Config::MAX_BATCH_SIZE);
                $result = DB::executeQuery($query);
                $last_id = null;
                while ($row = DB::nextRow($result))
                {
                        if ($last_id === null) $last_id = $row["mail_id"];
                        if ($last_id != $row["mail_id"]) break;

                        if ($subject === null) $subject = stripslashes($row["subject"]);
                        if ($body === null) $body = stripslashes($row["body"]);
                        if ($sender === null) $sender = new Swift_Address(stripslashes($row["sender_email"]), stripslashes($row["sender_name"]));
                        $recipients->addTo(stripslashes($row["email"]), stripslashes($row["name"]));
                        $user_mail_ids[] = $row["user_mail_id"];
                }

                if ($body && $sender)
                {
                        try {
                                //Send this batch
                                $swift = SwiftFactory::getSwift();
                                $message = new Swift_Message($subject, $body);
                                self::$emailsProcessed += $swift->batchSend($message, $recipients, $sender);
                                $swift->disconnect();
                        } catch (Exception $e) {
                                DB::executeQuery("
                                UPDATE
                                        user_spooled_mail
                                SET
                                        active_key = NULL,
                                        locked = 0
                                WHERE
                                        active_key = '" . DB::escape($key) . "'
                                LIMIT " . DB::escape(Config::MAX_BATCH_SIZE));
                                return 0;
                        }

                        //Set the status to sent
                        DB::executeQuery("
                        UPDATE
                                user_spooled_mail
                        SET
                                flag = 'S',
                                timedelivered = '" . time() . "'
                        WHERE
                                id IN (" . implode(",", $user_mail_ids) . ")");
                }

                //Release our hold on the records
                DB::executeQuery("
                UPDATE
                        user_spooled_mail
                SET
                        active_key = NULL,
                        locked = 0
                WHERE
                        active_key = '" . DB::escape($key) . "'
                LIMIT " . DB::escape(Config::MAX_BATCH_SIZE));
        }
        /**
         * Start the daemon running.
         * The daemon can only be stopped by using ^C to kill the process.
         */
        public static function start()
        {
                set_time_limit(0);
                $daemon = new self();
                while (true)
                {
                        $daemon->runBatch();
                        sleep(Config::CHECK_INTERVAL);
                }
        }
}

//Catch CTRL+C calls
//pcntl_signal(SIGINT, array("Daemon", "stop"));
//Start it running
Daemon::start();
EricWinchell
Forum Newbie
Posts: 5
Joined: Tue Aug 28, 2007 2:27 pm

Post by EricWinchell »

Thanks for posting that. Lots of good ideas there.

One question; I couldn't see a way to deduce which emails were unable to be sent with batchSend(), and so I stuck with send(). Does this make sense to you?
marcarthur
Forum Newbie
Posts: 1
Joined: Tue Sep 11, 2007 11:26 am

RE: A simple Swiftmailer daemon for sending emails saved to

Post by marcarthur »

Hi,

Subject: A simple Swiftmailer daemon for sending emails saved to DB

Is it possible to provide a version with the emails saved in a text file. I have .txt file with 160,000 emails i would to know that what is the best way to process those files without creating a DB.

Thanks in advance.
covina
Forum Newbie
Posts: 15
Joined: Tue May 22, 2007 1:55 pm

Post by covina »

Hi Chris, I tested your daemon script, it gave me t the following error:
Parse error: parse error, unexpected T_CONST, expecting T_OLD_FUNCTION or T_FUNCTION or T_VAR or '}' in /homepages/45/d48273/htdocs/mydoman.com/ASI/mailing_SWIFT.php on line 13
Is the script for PHP5 only not PHP4?
Any advice will be appreciated much.

P.S. Happy Holidays!
Post Reply