I am using Sogo on SME8b7. I wanted to create a Spamassassin whitelist of all the email addresses in the contacts list. With my old SME server I used OpenXchange, so this was a LDIF search. Sogo keeps the contacts in MySQL, so here is my solution.
Create /var/lib/spamassassin/allemails.sql
connect sogo;
SELECT CONCAT(
'SELECT * FROM (SELECT c_mail FROM ',
GROUP_CONCAT(tb SEPARATOR ' UNION SELECT c_mail FROM '),
') AS emails ORDER BY c_mail'
)
INTO @mailquery FROM
(
SELECT TABLE_NAME tb
FROM INFORMATION_SCHEMA.COLUMNS
WHERE column_name LIKE 'c_mail'
AND TABLE_SCHEMA='sogo'
) AS tbls;
PREPARE stmt FROM @mailquery;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
This dumps the contents of the c_mail columns from all the sogo*_quick tables.
Create /etc/cron.daily/sogo_whitelist and
#!/bin/bash
export HOME=/var/lib/spamassassin
cd $HOME
mysql --defaults-file=/root/.my.cnf -u root < /var/lib/spamassassin/allemails.sql > /tmp/sogo_emails.txt
sed "/c_mail/d" /tmp/sogo_emails.txt > /tmp/sogo_emails2.txt
rm -f /tmp/sogo_emails.txt
sed "/^$/d" /tmp/sogo_emails2.txt > /tmp/sogo_emails3.txt
rm -f /tmp/sogo_emails2.txt
sed "s/^/whitelist_from /" /tmp/sogo_emails3.txt > /etc/mail/spamassassin/sogo_whitelist
rm -f /tmp/sogo_emails3.txt
service spamassassin condrestart > /dev/null
chmod a+x /etc/cron.daily/sogo_whitelist
This tidies up the whitelist and adds the whitelist_from entry to each line, then reloads SA
Finally create the custom template
/etc/e-smith/templates/custom/etc/mail/spamassassin/local.cf/80sogo_whitelist
include sogo_whitelist
then
expand-template /etc/mail/spamassassin/local.cf
/etc/cron.daily/sogo_whitelist
service spamassassin restart