From 1605f171418842df3135d9331d607203e5899c99 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Rapha=C3=ABl=20Barrois?= Date: Mon, 22 Jun 2009 10:56:20 +0200 Subject: [PATCH] Improve mailings to subsets MIME-Version: 1.0 Content-Type: text/plain; charset=utf8 Content-Transfer-Encoding: 8bit * Store the list of email addresses in the DB and convert when sending * Don't use a "axletter_subsets" table any more * Use idsFromMails function : convert emails to uids Signed-off-by: Raphaël Barrois --- include/emails.inc.php | 86 +++++++++++++++++++++++++++++++++++++++ modules/axletter.php | 20 ++------- modules/axletter/axletter.inc.php | 18 ++++++-- upgrade/0.10.1/02_axletter.sql | 10 +---- 4 files changed, 104 insertions(+), 30 deletions(-) diff --git a/include/emails.inc.php b/include/emails.inc.php index 73d539a..4b40e0d 100644 --- a/include/emails.inc.php +++ b/include/emails.inc.php @@ -74,6 +74,92 @@ function isvalid_email_redirection($email) !preg_match("/@(polytechnique\.(org|edu)|melix\.(org|net)|m4x\.org)$/", $email); } +// function idsFromMails() {{{1 +/** Converts an array of emails to an array of email => uid + * @param $emails : array of emails + * @return array of ($email => $uid) + */ +function idsFromMails($emails) +{ + global $globals; + $domain_mails = array(); + $alias_mails = array(); + $other_mails = array(); + /* Find type of email address */ + foreach ($emails as $email) { + if (strpos($email, '@') === false) { + $user = $email; + $domain = $globals->mail->domain2; + } else { + list($user, $domain) = explode('@', $email); + } + if ($domain == $globals->mail->alias_dom || $domain == $globals->mail->alias_dom2) { + list($user) = explode('+', $user); + list($user) = explode('_', $user); + $alias_mails[$user] = $email; + } elseif ($domain == $globals->mail->domain || $domain == $globals->mail->domain2) { + list($user) = explode('+', $user); + list($user) = explode('_', $user); + $domain_mails[$user] = $email; + } else { + $other_mails[] = $email; + } + } + $uids = array(); + /* domain users */ + if (count($domain_mails)) { + $domain_users = array(); + foreach (array_keys($domain_mails) as $user) { + $domain_users[] = XDB::escape($user); + } + $list = implode(',', $domain_users); + $res = XDB::query("SELECT alias, id + FROM aliases + WHERE alias IN ($list)"); + foreach ($res->fetchAllRow() as $row) { + list ($alias, $id) = $row; + $uids[$domain_mails[$alias]] = $id; + } + } + + /* Alias users */ + if (count($alias_mails)) { + $alias_users = array(); + foreach (array_keys($alias_mails) as $user) { + $alias_users[] = XDB::escape($user."@".$globals->mail->alias_dom); + } + $list = implode(',', $alias_users); + $res = XDB::query("SELECT v.alias, a.id + FROM virtual AS v + INNER JOIN virtual_redirect AS r USING(vid) + INNER JOIN aliases AS a ON (a.type = 'a_vie' + AND r.redirect = CONCAT(a.alias, '@{$globals->mail->domain2}')) + WHERE v.alias IN ($list)"); + foreach ($res->fetchAllRow() as $row) { + list ($alias, $id) = $row; + $uids[$alias_mails[$alias]] = $id; + } + } + + /* Other mails */ + if (count($other_mails)) { + $other_users = array(); + foreach (array_keys($other_mails) as $user) { + $other_users[] = XDB::escape($user); + } + $list = implode(',', $other_users); + $res = XDB::query("SELECT email, uid + FROM emails + WHERE email IN ($list)"); + foreach ($res->fetchAllRow() as $row) { + list ($email, $uid) = $row; + $uids[$other_mails[$email]] = $uid; + } + } + + return $uids; +} + // class Bogo {{{1 // The Bogo class represents a spam filtering level in plat/al architecture. class Bogo diff --git a/modules/axletter.php b/modules/axletter.php index fe160b2..f1bd6e6 100644 --- a/modules/axletter.php +++ b/modules/axletter.php @@ -102,10 +102,8 @@ class AXLetterModule extends PLModule $res = XDB::query("SELECT * FROM axletter WHERE FIND_IN_SET('new', bits)"); if ($res->numRows()) { extract($res->fetchOneAssoc(), EXTR_OVERWRITE); - if ($subset) { - $res = XDB::query('SELECT email FROM axletter_subsets WHERE letter_id = {?}', $id); - $subset_to = $res->fetchColumn(); - } + $subset_to = preg_split("/\n/", $subset); + $subset = (count($subset_to > 0)); $saved = true; } else { XDB::execute("INSERT INTO axletter SET id = NULL"); @@ -165,19 +163,7 @@ class AXLetterModule extends PLModule XDB::execute("REPLACE INTO axletter SET id = {?}, short_name = {?}, subject = {?}, title = {?}, body = {?}, signature = {?}, promo_min = {?}, promo_max = {?}, echeance = {?}, subset = {?}", - $id, $short_name, $subject, $title, $body, $signature, $promo_min, $promo_max, $echeance, $subset); - if ($subset) { - XDB::execute('DELETE FROM axletter_subsets - WHERE letter_id = {?}', $id); - foreach ($subset_to as $email) { - $uid = $this->idFromMail(array('email' => $email)); - if ($uid) { - XDB::execute('INSERT INTO axletter_subsets - SET letter_id = {?}, user_id = {?}, email = {?}', - $id, $uid, $email); - } - } - } + $id, $short_name, $subject, $title, $body, $signature, $promo_min, $promo_max, $echeance, $subset ? implode("\n", $subset_to): null); if (!$saved) { global $globals; $mailer = new PlMailer(); diff --git a/modules/axletter/axletter.inc.php b/modules/axletter/axletter.inc.php index 8b591f6..68c64a9 100644 --- a/modules/axletter/axletter.inc.php +++ b/modules/axletter/axletter.inc.php @@ -28,6 +28,7 @@ class AXLetter extends MassMailer public $_promo_min; public $_promo_max; public $_subset; + public $_subset_to; public $_echeance; public $_date; public $_bits; @@ -55,10 +56,12 @@ class AXLetter extends MassMailer } list($this->_id, $this->_shortname, $this->_title_mail, $this->_title, $this->_body, $this->_signature, $this->_promo_min, $this->_promo_max, - $this->_subset, $this->_echeance, $this->_date, $this->_bits) = $id; + $this->_subset_to, $this->_echeance, $this->_date, $this->_bits) = $id; if ($this->_date == '0000-00-00') { $this->_date = 0; } + $this->_subset_to = preg_split("/\n/", $this->_subset_to); + $this->_subset = (count($this->_subset_to) > 0); } protected function assignData(&$smarty) @@ -108,7 +111,6 @@ class AXLetter extends MassMailer IF(ni.user_id = 0, 'html', q.core_mail_fmt) AS pref, IF(ni.user_id = 0, ni.hash, 0) AS hash FROM axletter_ins AS ni - {$this->subsetJoin()} LEFT JOIN auth_user_md5 AS u USING(user_id) LEFT JOIN auth_user_quick AS q ON(q.user_id = u.user_id) LEFT JOIN aliases AS a ON(u.user_id=a.id AND FIND_IN_SET('bestalias',a.flags)) @@ -193,12 +195,11 @@ class AXLetter extends MassMailer return "INNER JOIN axletter_subsets AS c ON (c.letter_id = ".XDB::escape($this->_id)." AND ni.user_id = c.uid)"; } return ''; - // TODO : force use of the adresses given by AX, not "canonical" ones } protected function subscriptionWhere() { - if (!$this->_promo_min && !$this->_promo_max) { + if (!$this->_promo_min && !$this->_promo_max && !$this->_subset) { return '1'; } $where = array(); @@ -208,6 +209,15 @@ class AXLetter extends MassMailer if ($this->_promo_max) { $where[] = "((ni.user_id = 0 AND ni.promo <= {$this->_promo_max}) OR (ni.user_id != 0 AND u.promo <= {$this->_promo_max}))"; } + if ($this->_subset) { + require_once("emails.inc.php"); + print_r($this->_subset_to); + $ids = idsFromMails($this->_subset_to); + print_r($ids); + $ids_list = implode(',', $ids); + $where[] = "ni.user_id IN ($ids_list)"; + // TODO : force use of the adresses given by AX, not "canonical" ones ? + } return implode(' AND ', $where); } diff --git a/upgrade/0.10.1/02_axletter.sql b/upgrade/0.10.1/02_axletter.sql index a460fed..d675b54 100644 --- a/upgrade/0.10.1/02_axletter.sql +++ b/upgrade/0.10.1/02_axletter.sql @@ -1,12 +1,4 @@ -DROP TABLE IF EXISTS axletter_subsets; - -CREATE TABLE IF NOT EXISTS axletter_subsets ( - letter_id INT(11) UNSIGNED NOT NULL, - uid INT(11) NOT NULL, - email VARCHAR(255) NOT NULL -) - -ALTER TABLE axletter ADD subset smallint(1) NOT NULL DEFAULT 0 AFTER promo_max +ALTER TABLE axletter ADD subset TEXT CHARACTER SET ASCII COLLATE ascii_general_ci NULL DEFAULT NULL AFTER promo_max -- vim:set syntax=mysql: -- 2.1.4