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;
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;
}
/**
* Initializes the sql query environment from the SqlResources file. Will look for
* conf/sqlResources.xml.
*
* @param conn The connection for accessing the database
* @param sqlFileUrl The url which we use to get the sql file
* @throws Exception If any error occurs
*/
private void initSqlQueries(Connection conn, String sqlFileUrl) throws Exception {
try {
File sqlFile;
try {
sqlFile = fileSystem.getFile(sqlFileUrl);
sqlFileUrl = null;
} catch (Exception e) {
serviceLog.error(e.getMessage(), e);
throw e;
}
sqlQueries.init(sqlFile.getCanonicalFile(), "GreyList", conn, sqlParameters);
selectQuery = sqlQueries.getSqlString("selectQuery", true);
insertQuery = sqlQueries.getSqlString("insertQuery", true);
deleteQuery = sqlQueries.getSqlString("deleteQuery", true);
deleteAutoWhiteListQuery = sqlQueries.getSqlString("deleteAutoWhitelistQuery", true);
updateQuery = sqlQueries.getSqlString("updateQuery", true);
} finally {
theJDBCUtil.closeJDBCConnection(conn);
}
}
/** Manages a display request. */
private void manageDisplayRequest(Mail mail) throws MessagingException {
MailAddress senderMailAddress = mail.getSender();
String senderUser = senderMailAddress.getLocalPart().toLowerCase(Locale.US);
String senderHost = senderMailAddress.getDomain().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);
}
}
/**
* 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
*/
private void initSqlQueries(Connection conn, org.apache.mailet.MailetContext mailetContext)
throws Exception {
try {
if (conn.getAutoCommit()) {
conn.setAutoCommit(false);
}
/*
Holds value of property sqlFile.
*/
File sqlFile =
new File((String) mailetContext.getAttribute("confDir"), "sqlResources.xml")
.getCanonicalFile();
sqlQueries.init(sqlFile, "WhiteList", conn, getSqlParameters());
checkTables(conn);
} finally {
theJDBCUtil.closeJDBCConnection(conn);
}
}
/** Manages a remove request. */
private void manageRemoveRequest(Mail mail) throws MessagingException {
MailAddress senderMailAddress = mail.getSender();
String senderUser = senderMailAddress.getLocalPart().toLowerCase(Locale.US);
String senderHost = senderMailAddress.getDomain().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.getLocalPart().toLowerCase(Locale.US);
String recipientHost = recipientMailAddress.getDomain().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);
}
}
/**
* 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<MailAddress> recipients)
throws MessagingException {
String senderUser = senderMailAddress.getLocalPart().toLowerCase(Locale.US);
String senderHost = senderMailAddress.getDomain().toLowerCase(Locale.US);
senderUser = getPrimaryName(senderUser);
Connection conn = null;
PreparedStatement selectStmt = null;
PreparedStatement insertStmt = null;
boolean dbUpdated = false;
try {
for (MailAddress recipient : recipients) {
ResultSet selectRS = null;
try {
String recipientUser = recipient.getLocalPart().toLowerCase(Locale.US);
String recipientHost = recipient.getDomain().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);
}
}
