JDBCVirtualUserTablepublic class JDBCVirtualUserTable extends AbstractVirtualUserTable | Implements a Virtual User Table for JAMES. Derived from the
JDBCAlias mailet, but whereas that mailet uses a simple map from a
source address to a destination address, this handles simple
wildcard selection, verifies that a catchall address is for a domain
in the Virtual User Table, and handles forwarding.
JDBCVirtualUserTable does not provide any administation tools.
You'll have to create the VirtualUserTable yourself. The standard
configuration is as follows:
CREATE TABLE VirtualUserTable
(
user varchar(64) NOT NULL default '',
domain varchar(255) NOT NULL default '',
target_address varchar(255) NOT NULL default '',
PRIMARY KEY (user,domain)
);
The user column specifies the username of the virtual recipient, the domain
column the domain of the virtual recipient, and the target_address column
the email address of the real recipient. The target_address column can contain
just the username in the case of a local user, and multiple recipients can be
specified in a list separated by commas, semi-colons or colons.
The standard query used with VirtualUserTable is:
select VirtualUserTable.target_address from VirtualUserTable, VirtualUserTable as VUTDomains
where (VirtualUserTable.user like ? or VirtualUserTable.user like "\%")
and (VirtualUserTable.domain like ?
or (VirtualUserTable.domain like "\%" and VUTDomains.domain like ?))
order by concat(VirtualUserTable.user,'@',VirtualUserTable.domain) desc limit 1
For a given [user, domain, domain] used with the query, this will
match as follows (in precedence order):
1. user@domain - explicit mapping for user@domain
2. user@% - catchall mapping for user anywhere
3. %@domain - catchall mapping for anyone at domain
4. null - no valid mapping
You need to set the connection. At the moment, there is a limit to
what you can change regarding the SQL Query, because there isn't a
means to specify where in the query to replace parameters. [TODO]
<mailet match="All" class="JDBCVirtualUserTable">
<table>db://maildb/VirtualUserTable</table>
<sqlquery>sqlquery</sqlquery>
</mailet> |
| Fields Summary |
|---|
| protected org.apache.avalon.excalibur.datasource.DataSourceComponent | datasource | | protected String | queryThe query used by the mailet to get the alias mapping | | private final org.apache.james.util.JDBCUtil | theJDBCUtilThe JDBCUtil helper class |
| Methods Summary |
|---|
| public java.lang.String | getMailetInfo()
return "JDBC Virtual User Table mailet";
| | public void | init()Initialize the mailet
if (getInitParameter("table") == null) {
throw new MailetException("Table location not specified for JDBCVirtualUserTable");
}
String tableURL = getInitParameter("table");
String datasourceName = tableURL.substring(5);
int pos = datasourceName.indexOf("/");
String tableName = datasourceName.substring(pos + 1);
datasourceName = datasourceName.substring(0, pos);
Connection conn = null;
try {
ServiceManager componentManager = (ServiceManager)getMailetContext().getAttribute(Constants.AVALON_COMPONENT_MANAGER);
// Get the DataSourceSelector service
DataSourceSelector datasources = (DataSourceSelector)componentManager.lookup(DataSourceSelector.ROLE);
// Get the data-source required.
datasource = (DataSourceComponent)datasources.select(datasourceName);
conn = datasource.getConnection();
// Check if the required 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 (!(theJDBCUtil.tableExists(dbMetaData, tableName))) {
StringBuffer exceptionBuffer =
new StringBuffer(128)
.append("Could not find table '")
.append(tableName)
.append("' in datasource '")
.append(datasourceName)
.append("'");
throw new MailetException(exceptionBuffer.toString());
}
//Build the query
query = getInitParameter("sqlquery","select VirtualUserTable.target_address from VirtualUserTable, VirtualUserTable as VUTDomains where (VirtualUserTable.user like ? or VirtualUserTable.user like '\\%') and (VirtualUserTable.domain like ? or (VirtualUserTable.domain like '\\%' and VUTDomains.domain like ?)) order by concat(VirtualUserTable.user,'@',VirtualUserTable.domain) desc limit 1");
} catch (MailetException me) {
throw me;
} catch (Exception e) {
throw new MessagingException("Error initializing JDBCVirtualUserTable", e);
} finally {
theJDBCUtil.closeJDBCConnection(conn);
}
| | protected void | mapRecipients(java.util.Map recipientsMap)Map any virtual recipients to real recipients using the configured
JDBC connection, table and query.
Connection conn = null;
PreparedStatement mappingStmt = null;
Collection recipients = recipientsMap.keySet();
try {
conn = datasource.getConnection();
mappingStmt = conn.prepareStatement(query);
for (Iterator i = recipients.iterator(); i.hasNext(); ) {
ResultSet mappingRS = null;
try {
MailAddress source = (MailAddress)i.next();
mappingStmt.setString(1, source.getUser());
mappingStmt.setString(2, source.getHost());
mappingStmt.setString(3, source.getHost());
mappingRS = mappingStmt.executeQuery();
if (mappingRS.next()) {
String targetString = mappingRS.getString(1);
recipientsMap.put(source, targetString);
}
} finally {
theJDBCUtil.closeJDBCResultSet(mappingRS);
}
}
} catch (SQLException sqle) {
throw new MessagingException("Error accessing database", sqle);
} finally {
theJDBCUtil.closeJDBCStatement(mappingStmt);
theJDBCUtil.closeJDBCConnection(conn);
}
|
|
