FileDocCategorySizeDatePackage
WhiteListManager.javaAPI DocApache James 2.3.134006Fri Jan 12 12:56:30 GMT 2007org.apache.james.transport.mailets

WhiteListManager.java

/****************************************************************
 * Licensed to the Apache Software Foundation (ASF) under one   *
 * or more contributor license agreements.  See the NOTICE file *
 * distributed with this work for additional information        *
 * regarding copyright ownership.  The ASF licenses this file   *
 * to you under the Apache License, Version 2.0 (the            *
 * "License"); you may not use this file except in compliance   *
 * with the License.  You may obtain a copy of the License at   *
 *                                                              *
 *   http://www.apache.org/licenses/LICENSE-2.0                 *
 *                                                              *
 * Unless required by applicable law or agreed to in writing,   *
 * software distributed under the License is distributed on an  *
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY       *
 * KIND, either express or implied.  See the License for the    *
 * specific language governing permissions and limitations      *
 * under the License.                                           *
 ****************************************************************/

package org.apache.james.transport.mailets;

import org.apache.mailet.*;
import org.apache.mailet.dates.RFC822DateFormat;

import org.apache.avalon.cornerstone.services.datasources.*;
import org.apache.avalon.excalibur.datasource.*;
import org.apache.avalon.framework.service.*;

import org.apache.james.*;
import org.apache.james.core.*;
import org.apache.james.services.*;
import org.apache.james.util.*;

import javax.mail.*;
import javax.mail.internet.*;

import java.sql.*;
import java.util.*;
import java.text.*;
import java.io.*;

/** <P>Manages for each local user a "white list" of remote addresses whose messages
 * should never be blocked as spam.</P>
 * <P>The normal behaviour is to check, for a local sender, if a remote recipient
 * is already in the list: if not, it will be automatically inserted.
 * This is under the interpretation that if a local sender <I>X</I> sends a message to a
 * remote recipient <I>Y</I>, then later on if a message is sent by <I>Y</I> to <I>X</I> it should be
 * considered always valid and never blocked; hence <I>Y</I> should be in the white list
 * of <I>X</I>.</P>
 * <P>Another mode of operations is when a local sender sends a message to <I>whitelistManagerAddress</I>
 * with one of three specific values in the subject, to
 * (i) send back a message displaying a list of the addresses in his own list;
 * (ii) insert some new addresses in his own list;
 * (iii) remove some addresses from his own list.
 * In all this cases the message will be ghosted and the postmaster will reply
 * to the sender.</P>
 * <P> The sender name is always converted to its primary name (handling aliases).</P>
 * <P>Sample configuration:</P>
 * <PRE><CODE>
 * <mailet match="SMTPAuthSuccessful" class="WhiteListManager">
 *   <repositoryPath> db://maildb </repositoryPath>
 *   <!--
 *     If true automatically inserts the local sender to remote recipients entries in the whitelist (default is false).
 *   -->
 *   <automaticInsert>true</automaticInsert>
 *   <!--
 *     Set this to an email address of the "whitelist manager" to send commands to (default is null).
 *   -->
 *   <whitelistManagerAddress>whitelist.manager@xxx.yyy</whitelistManagerAddress>
 *   <!--
 *     Set this to a unique text that you can use (by sending a message to the "whitelist manager" above)
 *     to tell the mailet to send back the contents of the white list (default is null).
 *   -->
 *   <displayFlag>display whitelist</displayFlag>
 *   <!--
 *     Set this to a unique text that you can use (by sending a message to the "whitelist manager" above)
 *     to tell the mailet to insert some new remote recipients to the white list (default is null).
 *   -->
 *   <insertFlag>insert whitelist</insertFlag>
 *   <!--
 *     Set this to a unique text that you can use (by sending a message to the "whitelist manager" above)
 *     to tell the mailet to remove some remote recipients from the white list (default is null).
 *   -->
 *   <removeFlag>remove whitelist</removeFlag>
 * </mailet>
 * </CODE></PRE>
 *
 * @see org.apache.james.transport.matchers.IsInWhiteList
 * @version SVN $Revision: $ $Date: $
 * @since 2.3.0
 */
public class WhiteListManager extends GenericMailet {
    
    private boolean automaticInsert;
    private String displayFlag;
    private String insertFlag;
    private String removeFlag;
    private MailAddress whitelistManagerAddress;
    
    private String selectByPK;
    private String selectBySender;
    private String insert;
    private String deleteByPK;
    
    /** The date format object used to generate RFC 822 compliant date headers. */
    private RFC822DateFormat rfc822DateFormat = new RFC822DateFormat();

    private DataSourceComponent datasource;
    
   /** The store containing the local user repository. */
    private UsersStore usersStore;

    /** The user repository for this mail server.  Contains all the users with inboxes
     * on this server.
     */
    private UsersRepository localusers;

    /**
     * The JDBCUtil helper class
     */
    private final JDBCUtil theJDBCUtil = new JDBCUtil() {
        protected void delegatedLog(String logString) {
            log("WhiteListManager: " + logString);
        }
    };
    
    /**
     * Contains all of the sql strings for this component.
     */
    private SqlResources sqlQueries = new SqlResources();

    /**
     * Holds value of property sqlFile.
     */
    private File sqlFile;

     /**
     * Holds value of property sqlParameters.
     */
    private Map sqlParameters = new HashMap();

    /**
     * Getter for property sqlParameters.
     * @return Value of property sqlParameters.
     */
    private Map getSqlParameters() {

        return this.sqlParameters;
    }

    /**
     * Setter for property sqlParameters.
     * @param sqlParameters New value of property sqlParameters.
     */
    private void setSqlParameters(Map sqlParameters) {

        this.sqlParameters = sqlParameters;
    }

    /** Initializes the mailet.
     */
    public void init() throws MessagingException {
        automaticInsert = new Boolean(getInitParameter("automaticInsert")).booleanValue();
        log("automaticInsert: " + automaticInsert);

        displayFlag = getInitParameter("displayFlag");
        insertFlag = getInitParameter("insertFlag");
        removeFlag = getInitParameter("removeFlag");
        
        String whitelistManagerAddressString = getInitParameter("whitelistManagerAddress");
        if (whitelistManagerAddressString != null) {
            whitelistManagerAddressString = whitelistManagerAddressString.trim();
            log("whitelistManagerAddress: " + whitelistManagerAddressString);
            try {
                whitelistManagerAddress = new MailAddress(whitelistManagerAddressString);
            }
            catch (javax.mail.internet.ParseException pe) {
                throw new MessagingException("Bad whitelistManagerAddress", pe);
            }
            
            if (displayFlag != null) {
                displayFlag = displayFlag.trim();
                log("displayFlag: " + displayFlag);
            }
            else {
                log("displayFlag is null");
            }
            if (insertFlag != null) {
                insertFlag = insertFlag.trim();
                log("insertFlag: " + insertFlag);
            }
            else {
                log("insertFlag is null");
            }
            if (removeFlag != null) {
                removeFlag = removeFlag.trim();
                log("removeFlag: " + removeFlag);
            }
            else {
                log("removeFlag is null");
            }
        }
        else {
            log("whitelistManagerAddress is null; will ignore commands");
        }
        
        String repositoryPath = getInitParameter("repositoryPath");
        if (repositoryPath != null) {
            log("repositoryPath: " + repositoryPath);
        }
        else {
            throw new MessagingException("repositoryPath is null");
        }

        ServiceManager serviceManager = (ServiceManager) getMailetContext().getAttribute(Constants.AVALON_COMPONENT_MANAGER);

        try {
            // Get the DataSourceSelector block
            DataSourceSelector datasources = (DataSourceSelector) serviceManager.lookup(DataSourceSelector.ROLE);
            // Get the data-source required.
            int stindex =   repositoryPath.indexOf("://") + 3;
            String datasourceName = repositoryPath.substring(stindex);
            datasource = (DataSourceComponent) datasources.select(datasourceName);
        } catch (Exception e) {
            throw new MessagingException("Can't get datasource", e);
        }

         try {
            // Get the UsersRepository
            usersStore = (UsersStore)serviceManager.lookup(UsersStore.ROLE);
            localusers = (UsersRepository)usersStore.getRepository("LocalUsers");
        } catch (Exception e) {
            throw new MessagingException("Can't get the local users repository", e);
        }

        try {
            initSqlQueries(datasource.getConnection(), getMailetContext());
        } catch (Exception e) {
            throw new MessagingException("Exception initializing queries", e);
        }        
        
        selectByPK = sqlQueries.getSqlString("selectByPK", true);
        selectBySender = sqlQueries.getSqlString("selectBySender", true);
        insert = sqlQueries.getSqlString("insert", true);
        deleteByPK = sqlQueries.getSqlString("deleteByPK", true);
    }
    
    /** Services the mailet.
     */    
    public void service(Mail mail) throws MessagingException {
        
        // check if it's a local sender
        MailAddress senderMailAddress = mail.getSender();
        if (senderMailAddress == null) {
            return;
        }
        String senderUser = senderMailAddress.getUser();
        String senderHost = senderMailAddress.getHost();
        if (   !getMailetContext().isLocalServer(senderHost)
            || !getMailetContext().isLocalUser(senderUser)) {
            // not a local sender, so return
            return;
        }
        
        Collection recipients = mail.getRecipients();
        
        if (recipients.size() == 1
        && whitelistManagerAddress != null
        && whitelistManagerAddress.equals(recipients.toArray()[0])) {
            
            mail.setState(Mail.GHOST);
        
            String subject = mail.getMessage().getSubject();
            if (displayFlag != null && displayFlag.equals(subject)) {
                manageDisplayRequest(mail);
            }
            else if (insertFlag != null && insertFlag.equals(subject)) {
                manageInsertRequest(mail);
            }
            else if (removeFlag != null && removeFlag.equals(subject)) {
                manageRemoveRequest(mail);
            }
            else {
                StringWriter sout = new StringWriter();
                PrintWriter out = new PrintWriter(sout, true);
                out.println("Answering on behalf of: " + whitelistManagerAddress);
                out.println("ERROR: Unknown command in the subject line: " + subject);
                sendReplyFromPostmaster(mail, sout.toString());
            }
            return;
        }
        
        if (automaticInsert) {
            checkAndInsert(senderMailAddress, recipients);
        }
        
    }
    
    /** Returns a string describing this mailet.
     *
     * @return a string describing this mailet
     */
    public String getMailetInfo() {
        return "White List Manager mailet";
    }
    
    /** Loops through each address in the recipient list, checks if in the senders
     * list and inserts in it otherwise.
     */    
    private void checkAndInsert(MailAddress senderMailAddress, Collection recipients) throws MessagingException {
        String senderUser = senderMailAddress.getUser().toLowerCase(Locale.US);
        String senderHost = senderMailAddress.getHost().toLowerCase(Locale.US);
        
        senderUser = getPrimaryName(senderUser);
        
        Connection conn = null;
        PreparedStatement selectStmt = null;
        PreparedStatement insertStmt = null;
        boolean dbUpdated = false;
        
        try {
            
            for (Iterator i = recipients.iterator(); i.hasNext(); ) {
                ResultSet selectRS = null;
                try {
                    MailAddress recipientMailAddress = (MailAddress)i.next();
                    String recipientUser = recipientMailAddress.getUser().toLowerCase(Locale.US);
                    String recipientHost = recipientMailAddress.getHost().toLowerCase(Locale.US);
                    
                    if (getMailetContext().isLocalServer(recipientHost)) {
                        // not a remote recipient, so skip
                        continue;
                    }
                    
                    if (conn == null) {
                        conn = datasource.getConnection();
                    }
                    
                    if (selectStmt == null) {
                        selectStmt = conn.prepareStatement(selectByPK);
                    }
                    selectStmt.setString(1, senderUser);
                    selectStmt.setString(2, senderHost);
                    selectStmt.setString(3, recipientUser);
                    selectStmt.setString(4, recipientHost);
                    selectRS = selectStmt.executeQuery();
                    if (selectRS.next()) {
                        //This address was already in the list
                        continue;
                    }
                    
                    if (insertStmt == null) {
                        insertStmt = conn.prepareStatement(insert);
                    }
                    insertStmt.setString(1, senderUser);
                    insertStmt.setString(2, senderHost);
                    insertStmt.setString(3, recipientUser);
                    insertStmt.setString(4, recipientHost);
                    insertStmt.executeUpdate();
                    dbUpdated = true;
                    
                } finally {
                    theJDBCUtil.closeJDBCResultSet(selectRS);
                }
                
                //Commit our changes if necessary.
                if (conn != null && dbUpdated && !conn.getAutoCommit()) {
                    conn.commit();
                    dbUpdated = false;
                }
            }
        } catch (SQLException sqle) {
            log("Error accessing database", sqle);
            throw new MessagingException("Exception thrown", sqle);
        } finally {
            theJDBCUtil.closeJDBCStatement(selectStmt);
            theJDBCUtil.closeJDBCStatement(insertStmt);
            //Rollback our changes if necessary.
            try {
                if (conn != null && dbUpdated && !conn.getAutoCommit()) {
                    conn.rollback();
                    dbUpdated = false;
                }
            }
            catch (Exception e) {}
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }
    
    /** Manages a display request.
     */    
    private void manageDisplayRequest(Mail mail)
    throws MessagingException {
        MailAddress senderMailAddress = mail.getSender();
        String senderUser = senderMailAddress.getUser().toLowerCase(Locale.US);
        String senderHost = senderMailAddress.getHost().toLowerCase(Locale.US);
        
        senderUser = getPrimaryName(senderUser);
        
        Connection conn = null;
        PreparedStatement selectStmt = null;
        ResultSet selectRS = null;
        
        StringWriter sout = new StringWriter();
        PrintWriter out = new PrintWriter(sout, true);
        
        try {
            out.println("Answering on behalf of: " + whitelistManagerAddress);
            out.println("Displaying white list of " + (new MailAddress(senderUser, senderHost)) + ":");
            out.println();
            
            conn = datasource.getConnection();
            selectStmt = conn.prepareStatement(selectBySender);
            selectStmt.setString(1, senderUser);
            selectStmt.setString(2, senderHost);
            selectRS = selectStmt.executeQuery();
            while (selectRS.next()) {
                MailAddress mailAddress =
                    new MailAddress(selectRS.getString(1), selectRS.getString(2));
                out.println(mailAddress.toInternetAddress().toString());
            }
            
            out.println();
            out.println("Finished");
            
            sendReplyFromPostmaster(mail, sout.toString());
                        
        } catch (SQLException sqle) {
            out.println("Error accessing the database");
            sendReplyFromPostmaster(mail, sout.toString());
            throw new MessagingException("Error accessing database", sqle);
        } finally {
            theJDBCUtil.closeJDBCResultSet(selectRS);
            theJDBCUtil.closeJDBCStatement(selectStmt);
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }
    
    /** Manages an insert request.
     */    
    private void manageInsertRequest(Mail mail)
    throws MessagingException {
        MailAddress senderMailAddress = mail.getSender();
        String senderUser = senderMailAddress.getUser().toLowerCase(Locale.US);
        String senderHost = senderMailAddress.getHost().toLowerCase(Locale.US);
        
        senderUser = getPrimaryName(senderUser);
        
        Connection conn = null;
        PreparedStatement selectStmt = null;
        PreparedStatement insertStmt = null;
        boolean dbUpdated = false;
        
        StringWriter sout = new StringWriter();
        PrintWriter out = new PrintWriter(sout, true);
        
        try {
            out.println("Answering on behalf of: " + whitelistManagerAddress);
            out.println("Inserting in the white list of " + (new MailAddress(senderUser, senderHost)) + " ...");
            out.println();
            
            MimeMessage message = mail.getMessage() ;
            
            Object content= message.getContent();
            
            if (message.getContentType().startsWith("text/plain")
            && content instanceof String) {
                StringTokenizer st = new StringTokenizer((String) content, " \t\n\r\f,;:<>");
                while (st.hasMoreTokens()) {
                    ResultSet selectRS = null;
                    try {
                        MailAddress recipientMailAddress;
                        try {
                            recipientMailAddress = new MailAddress(st.nextToken());
                        }
                        catch (javax.mail.internet.ParseException pe) {
                            continue;
                        }
                        String recipientUser = recipientMailAddress.getUser().toLowerCase(Locale.US);
                        String recipientHost = recipientMailAddress.getHost().toLowerCase(Locale.US);
                        
                        if (getMailetContext().isLocalServer(recipientHost)) {
                            // not a remote recipient, so skip
                            continue;
                        }
                        
                        if (conn == null) {
                            conn = datasource.getConnection();
                        }
                        
                        if (selectStmt == null) {
                            selectStmt = conn.prepareStatement(selectByPK);
                        }
                        selectStmt.setString(1, senderUser);
                        selectStmt.setString(2, senderHost);
                        selectStmt.setString(3, recipientUser);
                        selectStmt.setString(4, recipientHost);
                        selectRS = selectStmt.executeQuery();
                        if (selectRS.next()) {
                            //This address was already in the list
                            out.println("Skipped:  " + recipientMailAddress);
                            continue;
                        }
                        
                        if (insertStmt == null) {
                            insertStmt = conn.prepareStatement(insert);
                        }
                        insertStmt.setString(1, senderUser);
                        insertStmt.setString(2, senderHost);
                        insertStmt.setString(3, recipientUser);
                        insertStmt.setString(4, recipientHost);
                        insertStmt.executeUpdate();
                        dbUpdated = true;
                        out.println("Inserted: " + recipientMailAddress);
                        
                    } finally {
                        theJDBCUtil.closeJDBCResultSet(selectRS);
                    }
                }
                
                if (dbUpdated) {
                    log("Insertion request issued by " + senderMailAddress);
                }
                //Commit our changes if necessary.
                if (conn != null && dbUpdated && !conn.getAutoCommit()) {
                    conn.commit() ;
                    dbUpdated = false;
                }
            }
            else {
                out.println("The message must be plain - no action");
            }
            
            out.println();
            out.println("Finished");
            
            sendReplyFromPostmaster(mail, sout.toString());
            
        } catch (SQLException sqle) {
            out.println("Error accessing the database");
            sendReplyFromPostmaster(mail, sout.toString());
            throw new MessagingException("Error accessing the database", sqle);
        } catch (IOException ioe) {
            out.println("Error getting message content");
            sendReplyFromPostmaster(mail, sout.toString());
            throw new MessagingException("Error getting message content", ioe);
        } finally {
            theJDBCUtil.closeJDBCStatement(selectStmt);
            theJDBCUtil.closeJDBCStatement(insertStmt);
            //Rollback our changes if necessary.
            try {
                if (conn != null && dbUpdated && !conn.getAutoCommit()) {
                    conn.rollback() ;
                    dbUpdated = false;
                }
            }
            catch (Exception e) {}
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }
    
    /** Manages a remove request.
     */    
    private void manageRemoveRequest(Mail mail)
    throws MessagingException {
        MailAddress senderMailAddress = mail.getSender();
        String senderUser = senderMailAddress.getUser().toLowerCase(Locale.US);
        String senderHost = senderMailAddress.getHost().toLowerCase(Locale.US);
        
        senderUser = getPrimaryName(senderUser);
        
        Connection conn = null;
        PreparedStatement selectStmt = null;
        PreparedStatement deleteStmt = null;
        boolean dbUpdated = false;
        
        StringWriter sout = new StringWriter();
        PrintWriter out = new PrintWriter(sout, true);
        
        try {
            out.println("Answering on behalf of: " + whitelistManagerAddress);
            out.println("Removing from the white list of " + (new MailAddress(senderUser, senderHost)) + " ...");
            out.println();
            
            MimeMessage message = mail.getMessage() ;
            
            Object content= message.getContent();
            
            if (message.getContentType().startsWith("text/plain")
            && content instanceof String) {
                StringTokenizer st = new StringTokenizer((String) content, " \t\n\r\f,;:<>");
                while (st.hasMoreTokens()) {
                    ResultSet selectRS = null;
                    try {
                        MailAddress recipientMailAddress;
                        try {
                            recipientMailAddress = new MailAddress(st.nextToken());
                        }
                        catch (javax.mail.internet.ParseException pe) {
                            continue;
                        }
                        String recipientUser = recipientMailAddress.getUser().toLowerCase(Locale.US);
                        String recipientHost = recipientMailAddress.getHost().toLowerCase(Locale.US);
                        
                        if (getMailetContext().isLocalServer(recipientHost)) {
                            // not a remote recipient, so skip
                            continue;
                        }
                        
                        if (conn == null) {
                            conn = datasource.getConnection();
                        }
                        
                        if (selectStmt == null) {
                            selectStmt = conn.prepareStatement(selectByPK);
                        }
                        selectStmt.setString(1, senderUser);
                        selectStmt.setString(2, senderHost);
                        selectStmt.setString(3, recipientUser);
                        selectStmt.setString(4, recipientHost);
                        selectRS = selectStmt.executeQuery();
                        if (!selectRS.next()) {
                            //This address was not in the list
                            out.println("Skipped: " + recipientMailAddress);
                            continue;
                        }
                        
                        if (deleteStmt == null) {
                            deleteStmt = conn.prepareStatement(deleteByPK);
                        }
                        deleteStmt.setString(1, senderUser);
                        deleteStmt.setString(2, senderHost);
                        deleteStmt.setString(3, recipientUser);
                        deleteStmt.setString(4, recipientHost);
                        deleteStmt.executeUpdate();
                        dbUpdated = true;
                        out.println("Removed: " + recipientMailAddress);
                        
                    } finally {
                        theJDBCUtil.closeJDBCResultSet(selectRS);
                    }
                }

                if (dbUpdated) {
                    log("Removal request issued by " + senderMailAddress);
                }
                //Commit our changes if necessary.
                if (conn != null && dbUpdated && !conn.getAutoCommit()) {
                    conn.commit() ;
                    dbUpdated = false;
                }
            }
            else {
                out.println("The message must be plain - no action");
            }
            
            out.println();
            out.println("Finished");
            
            sendReplyFromPostmaster(mail, sout.toString());
            
        } catch (SQLException sqle) {
            out.println("Error accessing the database");
            sendReplyFromPostmaster(mail, sout.toString());
            throw new MessagingException("Error accessing the database", sqle);
        } catch (IOException ioe) {
            out.println("Error getting message content");
            sendReplyFromPostmaster(mail, sout.toString());
            throw new MessagingException("Error getting message content", ioe);
        } finally {
            theJDBCUtil.closeJDBCStatement(selectStmt);
            theJDBCUtil.closeJDBCStatement(deleteStmt);
            //Rollback our changes if necessary.
            try {
                if (conn != null && dbUpdated && !conn.getAutoCommit()) {
                    conn.rollback() ;
                    dbUpdated = false;
                }
            }
            catch (Exception e) {}
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }
    
    private void sendReplyFromPostmaster(Mail mail, String stringContent) throws MessagingException {
        try {
            MailAddress notifier = getMailetContext().getPostmaster();
            
            MailAddress senderMailAddress = mail.getSender();
            
            MimeMessage message = mail.getMessage();
            //Create the reply message
            MimeMessage reply = new MimeMessage(Session.getDefaultInstance(System.getProperties(), null));
            
            //Create the list of recipients in the Address[] format
            InternetAddress[] rcptAddr = new InternetAddress[1];
            rcptAddr[0] = senderMailAddress.toInternetAddress();
            reply.setRecipients(Message.RecipientType.TO, rcptAddr);
            
            //Set the sender...
            reply.setFrom(notifier.toInternetAddress());
            
            //Create the message body
            MimeMultipart multipart = new MimeMultipart();
            //Add message as the first mime body part
            MimeBodyPart part = new MimeBodyPart();
            part.setContent(stringContent, "text/plain");
            part.setHeader(RFC2822Headers.CONTENT_TYPE, "text/plain");
            multipart.addBodyPart(part);
            
            reply.setContent(multipart);
            reply.setHeader(RFC2822Headers.CONTENT_TYPE, multipart.getContentType());
            
            //Create the list of recipients in our MailAddress format
            Set recipients = new HashSet();
            recipients.add(senderMailAddress);
            
            //Set additional headers
            if (reply.getHeader(RFC2822Headers.DATE)==null){
                reply.setHeader(RFC2822Headers.DATE, rfc822DateFormat.format(new java.util.Date()));
            }
            String subject = message.getSubject();
            if (subject == null) {
                subject = "";
            }
            if (subject.indexOf("Re:") == 0){
                reply.setSubject(subject);
            } else {
                reply.setSubject("Re:" + subject);
            }
            reply.setHeader(RFC2822Headers.IN_REPLY_TO, message.getMessageID());
            
            //Send it off...
            getMailetContext().sendMail(notifier, recipients, reply);
        }
        catch (Exception e) {
            log("Exception found sending reply", e);
        }
    }
    
    /** Gets the main name of a local customer, handling alias */
    private String getPrimaryName(String originalUsername) {
        String username;
        try {
            username = localusers.getRealName(originalUsername);
            JamesUser user = (JamesUser) localusers.getUserByName(username);
            if (user.getAliasing()) {
                username = user.getAlias();
            }
        }
        catch (Exception e) {
            username = originalUsername;
        }
        return username;
    }
    
    /**
     * Initializes the sql query environment from the SqlResources file.
     * Will look for conf/sqlResources.xml.
     * @param conn The connection for accessing the database
     * @param mailetContext The current mailet context,
     * for finding the conf/sqlResources.xml file
     * @throws Exception If any error occurs
     */
    public void initSqlQueries(Connection conn, org.apache.mailet.MailetContext mailetContext) throws Exception {
        try {
            if (conn.getAutoCommit()) {
                conn.setAutoCommit(false);
            }
            
            this.sqlFile = new File((String) mailetContext.getAttribute("confDir"), "sqlResources.xml").getCanonicalFile();
            sqlQueries.init(this.sqlFile, "WhiteList" , conn, getSqlParameters());
            
            checkTables(conn);
        } finally {
            theJDBCUtil.closeJDBCConnection(conn);
        }
    }
    
    private void checkTables(Connection conn) throws SQLException {
        DatabaseMetaData dbMetaData = conn.getMetaData();
        // Need to ask in the case that identifiers are stored, ask the DatabaseMetaInfo.
        // Try UPPER, lower, and MixedCase, to see if the table is there.
        
        boolean dbUpdated = false;
        
        dbUpdated = createTable(conn, "whiteListTableName", "createWhiteListTable");
        
        //Commit our changes if necessary.
        if (conn != null && dbUpdated && !conn.getAutoCommit()) {
            conn.commit();
            dbUpdated = false;
        }
            
    }
    
    private boolean createTable(Connection conn, String tableNameSqlStringName, String createSqlStringName) throws SQLException {
        String tableName = sqlQueries.getSqlString(tableNameSqlStringName, true);
        
        DatabaseMetaData dbMetaData = conn.getMetaData();

        // Try UPPER, lower, and MixedCase, to see if the table is there.
        if (theJDBCUtil.tableExists(dbMetaData, tableName)) {
            return false;
        }
        
        PreparedStatement createStatement = null;
        
        try {
            createStatement =
                    conn.prepareStatement(sqlQueries.getSqlString(createSqlStringName, true));
            createStatement.execute();
            
            StringBuffer logBuffer = null;
            logBuffer =
                    new StringBuffer(64)
                    .append("Created table '")
                    .append(tableName)
                    .append("' using sqlResources string '")
                    .append(createSqlStringName)
                    .append("'.");
            log(logBuffer.toString());
            
        } finally {
            theJDBCUtil.closeJDBCStatement(createStatement);
        }
        
        return true;
    }
    
}