/*
* Copyright (C) The Apache Software Foundation. All rights reserved.
*
* This software is published under the terms of the Apache Software License
* version 1.1, a copy of which has been included with this distribution in
* the LICENSE file.
*/
package org.apache.james.transport.mailets;
import org.apache.avalon.cornerstone.services.datasource.DataSourceSelector;
import org.apache.avalon.excalibur.datasource.DataSourceComponent;
import org.apache.avalon.framework.component.ComponentManager;
import org.apache.james.Constants;
import org.apache.mailet.MailAddress;
import org.apache.mailet.Mail;
import org.apache.mailet.MailetException;
import org.apache.mailet.GenericMailet;
import javax.mail.MessagingException;
import javax.mail.internet.MimeMessage;
import javax.mail.internet.MimeMultipart;
import javax.mail.internet.ParseException;
import java.sql.*;
import java.util.Collection;
import java.util.Iterator;
import java.util.Vector;
import java.io.IOException;
/**
* Rewrites recipient addresses based on a database table.
* Based on JBDCListserv this list server mailet takes both the list members and the list details from a couple of database tables
* The first table is the list data table, which contains the names of the lists, and their parameters (prefix, reply_to_list etc)
* The second table contains mappings of subscriber addresses and list addresses
*
* This works with the InJDBCListserv matcher
* EG:
*
* <mailet match="InJDBCListsListserv=db://maildb/lists" class="JDBCListsListserv"><br>
* <data_source>maildb</data_source><br>
* <listserv_table>lists</listserv_table><br>
* <members_table>listmembers</members_table><br>
* </mailet><br>
*
* @see org.apache.james.transport.matchers.InJDBCListsListserv
*
* @author Danny Angus <danny@apache.org>
* This is $Revision: 1.22 $
* Committed on $Date: 2002/03/11 21:46:32 $ by: $Author: serge $
*/
public class JDBCListsListserv extends GenericMailet {
protected DataSourceComponent datasource;
protected String listservID = null;
protected String listservTable = null;
protected String membersTable = null;
protected String peopleTable = null;
protected boolean cacheSettings = true;
//Settings for this listserv
protected Collection members = null;
protected boolean membersOnly = true;
protected boolean attachmentsAllowed = true;
protected boolean replyToList = true;
protected MailAddress listservAddress = null;
protected String subjectPrefix = null;
//Queries to DB
protected String listservQuery = null;
protected String membersQuery = null;
public void init() throws MessagingException {
if (getInitParameter("data_source") == null) {
throw new MailetException("data_source not specified for JDBCListserv");
}
if (getInitParameter("listserv_table") == null) {
throw new MailetException("listserv_table not specified for JDBCListserv");
}
if (getInitParameter("members_table") == null) {
throw new MailetException("members_table not specified for JDBCListserv");
}
if (getInitParameter("people_table") == null) {
throw new MailetException("people_table not specified for JDBCListserv");
}
String datasourceName = getInitParameter("data_source");
listservTable = getInitParameter("listserv_table");
membersTable = getInitParameter("members_table");
peopleTable = getInitParameter("people_table");
Connection conn = null;
try {
ComponentManager componentManager = (ComponentManager)getMailetContext().getAttribute(Constants.AVALON_COMPONENT_MANAGER);
// Get the DataSourceSelector block
DataSourceSelector datasources = (DataSourceSelector)componentManager.lookup(DataSourceSelector.ROLE);
// Get the data-source required.
datasource = (DataSourceComponent)datasources.select(datasourceName);
conn = datasource.getConnection();
// Check if the required listserv table exists. If not, complain.
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.
if (! ( tableExists(dbMetaData, listservTable) ||
tableExists(dbMetaData, listservTable.toUpperCase()) ||
tableExists(dbMetaData, listservTable.toLowerCase()) )) {
String create ="CREATE TABLE "+listservTable+"("+
"list_address VARCHAR (50) NOT NULL,"+
"members_only VARCHAR (10),"+
"attachments_allowed VARCHAR (10),"+
"reply_to_list VARCHAR (10),"+
"subject_prefix VARCHAR (50),"+
"PRIMARY KEY(list_address)"+
")";
PreparedStatement createStatement =
conn.prepareStatement(create);
createStatement.execute();
createStatement.close();
log("created table "+listservTable);
}
// Check if the required members table exists. If not, complain.
// 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.
if (! ( tableExists(dbMetaData, membersTable) ||
tableExists(dbMetaData, membersTable.toUpperCase()) ||
tableExists(dbMetaData, membersTable.toLowerCase()) )) {
String create ="CREATE TABLE "+membersTable+
"("+
"uid INTEGER NOT NULL AUTO_INCREMENT,"+
"list_address VARCHAR (50),"+
"member INTEGER,"+
"PRIMARY KEY(uid),"+
"FOREIGN KEY (list_address) REFERENCES lists (list_address),"+
"FOREIGN KEY (member) REFERENCES people (user_id),"+
"UNIQUE (list_address, member)"+
")";
PreparedStatement createStatement =
conn.prepareStatement(create);
createStatement.execute();
createStatement.close();
log("created table "+membersTable);
}
if (! ( tableExists(dbMetaData, peopleTable) ||
tableExists(dbMetaData, peopleTable.toUpperCase()) ||
tableExists(dbMetaData, peopleTable.toLowerCase()) )) {
String create ="CREATE TABLE "+peopleTable+
"("+
" user_id INTEGER NOT NULL AUTO_INCREMENT,"+
" email_address VARCHAR (50),"+
" )";
PreparedStatement createStatement =
conn.prepareStatement(create);
createStatement.execute();
createStatement.close();
log("created table "+peopleTable);
}
listservQuery = "SELECT members_only, attachments_allowed, reply_to_list, subject_prefix, list_address FROM "
+ listservTable + " WHERE list_address = ?";
membersQuery = "SELECT "+peopleTable+".email_address as member FROM " + membersTable +","+peopleTable+ " WHERE "+peopleTable+".user_id = "+membersTable+".member and "+membersTable+".list_address = ?";
} catch (Exception e) {
throw new MessagingException("Error initializing JDBCAlias", e);
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException sqle) {
//ignore
}
}
}
/**
* Returns a Collection of MailAddress objects of members to receive this email
*/
public Collection getMembers() throws MessagingException {
loadSettings();
return members;
}
/**
* Returns whether this list should restrict to senders only
*/
public boolean isMembersOnly() throws MessagingException {
return membersOnly;
}
/**
* Returns whether this listserv allow attachments
*/
public boolean isAttachmentsAllowed() throws MessagingException {
return attachmentsAllowed;
}
/**
* Returns whether listserv should add reply-to header
*/
public boolean isReplyToList() throws MessagingException {
return replyToList;
}
/**
* The email address that this listserv processes on. If returns null, will use the
* recipient of the message, which hopefully will be the correct email address assuming
* the matcher was properly specified.
*/
public MailAddress getListservAddress() throws MessagingException {
return listservAddress;
}
/**
* An optional subject prefix which will be surrounded by [].
*/
public String getSubjectPrefix() throws MessagingException {
return subjectPrefix;
}
protected void loadSettings() throws MessagingException {
Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
//Load members
conn = datasource.getConnection();
stmt = conn.prepareStatement(membersQuery);
stmt.setString(1, listservAddress.toString());
rs = stmt.executeQuery();
Collection tmpMembers = new Vector();
while (rs.next()) {
String address = rs.getString(1);
System.out.println(address);
try {
MailAddress mailAddress = new MailAddress(address);
tmpMembers.add(mailAddress);
} catch (ParseException pe) {
//don't stop... just log and continue
log("error parsing address '" + address + "' in listserv '" + listservAddress + "'");
}
}
members = tmpMembers;
rs.close();
stmt.close();
stmt = conn.prepareStatement(listservQuery);
stmt.setString(1, listservAddress.toString());
rs = stmt.executeQuery();
if (!rs.next()) {
throw new MailetException("Could not find listserv record for '" + listservAddress + "'");
}
membersOnly = rs.getBoolean("members_only");
attachmentsAllowed = rs.getBoolean("attachments_allowed");
replyToList = rs.getBoolean("reply_to_list");
subjectPrefix = rs.getString("subject_prefix");
String address = rs.getString("list_address");
if (address == null) {
listservAddress = null;
} else {
try {
listservAddress = new MailAddress(address);
} catch (ParseException pe) {
//log and ignore
log("invalid listserv address '" + listservAddress + "' for listserv '" + listservAddress + "'");
listservAddress = null;
}
}
rs.close();
stmt.close();
} catch (SQLException sqle) {
sqle.printStackTrace();
throw new MailetException("Problem loading settings", sqle);
} finally {
try {
if (conn != null) {
conn.close();
}
} catch (SQLException sqle) {
//ignore
}
}
}
public String getMailetInfo() {
return "JDBC listserv mailet";
}
/**
* Checks database metadata to see if a table exists.
*/
private boolean tableExists(DatabaseMetaData dbMetaData, String tableName)
throws SQLException {
ResultSet rsTables = dbMetaData.getTables(null, null, tableName, null);
boolean found = rsTables.next();
rsTables.close();
return found;
}
private MailAddress getListAddress(Mail mail){
for (Iterator i = mail.getRecipients().iterator(); i.hasNext(); ) {
MailAddress rec = (MailAddress) i.next();
if (matchRecipient(rec)) {
//yes I know.. this will prevent cross postings, is that so bad? :-)
return rec;
}
}
return null;//should never happen if it is run with its matcher..
}
public boolean matchRecipient(MailAddress recipient) {
try{
Connection conn = datasource.getConnection();
String query="Select count(*) from "+listservTable+" where list_address = '"+recipient.toString()+"'";
PreparedStatement q = conn.prepareStatement(query);
ResultSet rs = q.executeQuery();
rs.first();
int count = rs.getInt(1);
if(count >0){
return true;
}
}catch(Exception e){
log("Cant connect to list to match recipients");
e.printStackTrace();
}
return false;
}
public void service(Mail mail) throws MessagingException {
listservAddress = getListAddress(mail);
Collection members = new Vector();
members.addAll(getMembers());
//Check for members only flag....
if (isMembersOnly() && !members.contains(mail.getSender())) {
//Need to bounce the message to say they can't send to this list
getMailetContext().bounce(mail, "Only members of this listserv are allowed to send a message to this address.");
mail.setState(Mail.GHOST);
return;
}
//Check for no attachments
try{
if (!isAttachmentsAllowed() && mail.getMessage().getContent() instanceof MimeMultipart) {
getMailetContext().bounce(mail, "You cannot send attachments to this listserv.");
mail.setState(Mail.GHOST);
return;
}
}catch(Exception ioe){
ioe.printStackTrace();
throw new MailetException("Error creating listserv message", ioe);
}
MimeMessage message = mail.getMessage();
//Set the subject if set
if (getSubjectPrefix() != null) {
String prefix = "[" + getSubjectPrefix() + "]";
String subj = message.getSubject();
if (subj == null) {
subj = "";
}
//If the "prefix" is in the subject line, remove it and everything before it
int index = subj.indexOf(prefix);
if (index > -1) {
if (index == 0) {
subj = prefix + ' ' + subj.substring(index + prefix.length() + 1);
} else {
subj = prefix + ' ' + subj.substring(0, index) + subj.substring(index + prefix.length() + 1);
}
} else {
subj = prefix + ' ' + subj;
}
message.setSubject(subj);
}
if (listservAddress == null) {
//Use the recipient
listservAddress = (MailAddress)mail.getRecipients().iterator().next();
}
if (isReplyToList()) {
message.setHeader("Reply-To", listservAddress.toString());
}
//Send the message to the list members
getMailetContext().sendMail(listservAddress, members, message);
//Kill the old message
mail.setState(Mail.GHOST);
}
}