001: /****************************************************************
002: * Licensed to the Apache Software Foundation (ASF) under one *
003: * or more contributor license agreements. See the NOTICE file *
004: * distributed with this work for additional information *
005: * regarding copyright ownership. The ASF licenses this file *
006: * to you under the Apache License, Version 2.0 (the *
007: * "License"); you may not use this file except in compliance *
008: * with the License. You may obtain a copy of the License at *
009: * *
010: * http://www.apache.org/licenses/LICENSE-2.0 *
011: * *
012: * Unless required by applicable law or agreed to in writing, *
013: * software distributed under the License is distributed on an *
014: * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY *
015: * KIND, either express or implied. See the License for the *
016: * specific language governing permissions and limitations *
017: * under the License. *
018: ****************************************************************/package org.apache.james.userrepository;
019:
020: import java.io.File;
021: import java.sql.Connection;
022: import java.sql.DatabaseMetaData;
023: import java.sql.PreparedStatement;
024: import java.sql.ResultSet;
025: import java.sql.SQLException;
026: import java.util.Collection;
027: import java.util.HashMap;
028: import java.util.Iterator;
029: import java.util.ArrayList;
030: import java.util.List;
031: import java.util.Locale;
032: import java.util.Map;
033:
034: import org.apache.avalon.cornerstone.services.datasources.DataSourceSelector;
035: import org.apache.avalon.excalibur.datasource.DataSourceComponent;
036: import org.apache.avalon.framework.CascadingRuntimeException;
037: import org.apache.avalon.framework.activity.Initializable;
038: import org.apache.avalon.framework.configuration.Configurable;
039: import org.apache.avalon.framework.configuration.Configuration;
040: import org.apache.avalon.framework.configuration.ConfigurationException;
041: import org.apache.avalon.framework.context.Context;
042: import org.apache.avalon.framework.context.ContextException;
043: import org.apache.avalon.framework.context.Contextualizable;
044: import org.apache.avalon.framework.service.Serviceable;
045: import org.apache.avalon.framework.service.ServiceManager;
046: import org.apache.avalon.framework.service.ServiceException;
047: import org.apache.james.context.AvalonContextUtilities;
048: import org.apache.james.util.JDBCUtil;
049: import org.apache.james.util.SqlResources;
050: import org.apache.james.services.User;
051:
052: /**
053: * An abstract base class for creating UserRepository implementations
054: * which use a database for persistence.
055: *
056: * To implement a new UserRepository using by extending this class,
057: * you need to implement the 3 abstract methods defined below,
058: * and define the required SQL statements in an SQLResources
059: * file.
060: *
061: * The SQL statements used by this implementation are:
062: * <TABLE>
063: * <TH><TD><B>Required</B></TD></TH>
064: * <TR><TD>select</TD><TD>Select all users.</TD></TR>
065: * <TR><TD>insert</TD><TD>Insert a user.</TD></TR>
066: * <TR><TD>update</TD><TD>Update a user.</TD></TR>
067: * <TR><TD>delete</TD><TD>Delete a user by name.</TD></TR>
068: * <TR><TD>createTable</TD><TD>Create the users table.</TD></TR>
069: * <TH><TD><B>Optional</B></TD></TH>
070: * <TR><TD>selectByLowercaseName</TD><TD>Select a user by name (case-insensitive lowercase).</TD></TR>
071: * </TABLE>
072: *
073: */
074: public abstract class AbstractJdbcUsersRepository extends
075: AbstractUsersRepository implements Contextualizable,
076: Serviceable, Configurable, Initializable {
077: /**
078: * The Avalon context used by the instance
079: */
080: protected Context context;
081:
082: protected Map m_sqlParameters;
083:
084: private String m_sqlFileName;
085:
086: private String m_datasourceName;
087:
088: private DataSourceSelector m_datasources;
089:
090: private DataSourceComponent m_datasource;
091:
092: // Fetches all Users from the db.
093: private String m_getUsersSql;
094:
095: // This fetch a user by name, ensuring case-insensitive matching.
096: private String m_userByNameCaseInsensitiveSql;
097:
098: // Insert, update and delete sql statements are not guaranteed
099: // to be case-insensitive; this is handled in code.
100: private String m_insertUserSql;
101: private String m_updateUserSql;
102: private String m_deleteUserSql;
103:
104: // Creates a single table with "username" the Primary Key.
105: private String m_createUserTableSql;
106:
107: // The JDBCUtil helper class
108: private JDBCUtil theJDBCUtil;
109:
110: /**
111: * @see org.apache.avalon.framework.context.Contextualizable#contextualize(Context)
112: */
113: public void contextualize(final Context context)
114: throws ContextException {
115: this .context = context;
116: }
117:
118: /**
119: * @see org.apache.avalon.framework.service.Serviceable#compose(ServiceManager)
120: */
121: public void service(final ServiceManager componentManager)
122: throws ServiceException {
123: StringBuffer logBuffer = null;
124: if (getLogger().isDebugEnabled()) {
125: logBuffer = new StringBuffer(64).append(
126: this .getClass().getName()).append(".compose()");
127: getLogger().debug(logBuffer.toString());
128: }
129:
130: m_datasources = (DataSourceSelector) componentManager
131: .lookup(DataSourceSelector.ROLE);
132: }
133:
134: /**
135: * Configures the UserRepository for JDBC access.<br>
136: * <br>
137: * Requires a configuration element in the .conf.xml file of the form:<br>
138: * <br>
139: * <pre>
140: * <repository name="LocalUsers"
141: * class="org.apache.james.userrepository.JamesUsersJdbcRepository">
142: * <!-- Name of the datasource to use -->
143: * <data-source>MailDb</data-source>
144: * <!-- File to load the SQL definitions from -->
145: * <sqlFile>dist/conf/sqlResources.xml</sqlFile>
146: * <!-- replacement parameters for the sql file -->
147: * <sqlParameters table="JamesUsers"/>
148: * </repository>
149: * </pre>
150: */
151: public void configure(Configuration configuration)
152: throws ConfigurationException {
153: StringBuffer logBuffer = null;
154: if (getLogger().isDebugEnabled()) {
155: logBuffer = new StringBuffer(64).append(
156: this .getClass().getName()).append(".configure()");
157: getLogger().debug(logBuffer.toString());
158: }
159:
160: // Parse the DestinationURL for the name of the datasource,
161: // the table to use, and the (optional) repository Key.
162: String destUrl = configuration.getAttribute("destinationURL");
163: // normalise the destination, to simplify processing.
164: if (!destUrl.endsWith("/")) {
165: destUrl += "/";
166: }
167: // Split on "/", starting after "db://"
168: List urlParams = new ArrayList();
169: int start = 5;
170: int end = destUrl.indexOf('/', start);
171: while (end > -1) {
172: urlParams.add(destUrl.substring(start, end));
173: start = end + 1;
174: end = destUrl.indexOf('/', start);
175: }
176:
177: // Build SqlParameters and get datasource name from URL parameters
178: m_sqlParameters = new HashMap();
179: switch (urlParams.size()) {
180: case 3:
181: m_sqlParameters.put("key", urlParams.get(2));
182: case 2:
183: m_sqlParameters.put("table", urlParams.get(1));
184: case 1:
185: m_datasourceName = (String) urlParams.get(0);
186: break;
187: default:
188: throw new ConfigurationException(
189: "Malformed destinationURL - "
190: + "Must be of the format \"db://<data-source>[/<table>[/<key>]]\".");
191: }
192:
193: if (getLogger().isDebugEnabled()) {
194: logBuffer = new StringBuffer(128).append(
195: "Parsed URL: table = '").append(
196: m_sqlParameters.get("table")).append("', key = '")
197: .append(m_sqlParameters.get("key")).append("'");
198: getLogger().debug(logBuffer.toString());
199: }
200:
201: // Get the SQL file location
202: m_sqlFileName = configuration.getChild("sqlFile", true)
203: .getValue();
204: if (!m_sqlFileName.startsWith("file://")) {
205: throw new ConfigurationException(
206: "Malformed sqlFile - Must be of the format \"file://<filename>\".");
207: }
208:
209: // Get other sql parameters from the configuration object,
210: // if any.
211: Configuration sqlParamsConfig = configuration
212: .getChild("sqlParameters");
213: String[] paramNames = sqlParamsConfig.getAttributeNames();
214: for (int i = 0; i < paramNames.length; i++) {
215: String paramName = paramNames[i];
216: String paramValue = sqlParamsConfig.getAttribute(paramName);
217: m_sqlParameters.put(paramName, paramValue);
218: }
219: }
220:
221: /**
222: * <p>Initialises the JDBC repository.</p>
223: * <p>1) Tests the connection to the database.</p>
224: * <p>2) Loads SQL strings from the SQL definition file,
225: * choosing the appropriate SQL for this connection,
226: * and performing parameter substitution,</p>
227: * <p>3) Initialises the database with the required tables, if necessary.</p>
228: *
229: * @throws Exception if an error occurs
230: */
231: public void initialize() throws Exception {
232: StringBuffer logBuffer = null;
233: if (getLogger().isDebugEnabled()) {
234: logBuffer = new StringBuffer(128).append(
235: this .getClass().getName()).append(".initialize()");
236: getLogger().debug(logBuffer.toString());
237: }
238:
239: theJDBCUtil = new JDBCUtil() {
240: protected void delegatedLog(String logString) {
241: AbstractJdbcUsersRepository.this .getLogger().warn(
242: "AbstractJdbcUsersRepository: " + logString);
243: }
244: };
245:
246: // Get the data-source required.
247: m_datasource = (DataSourceComponent) m_datasources
248: .select(m_datasourceName);
249:
250: // Test the connection to the database, by getting the DatabaseMetaData.
251: Connection conn = openConnection();
252: try {
253: DatabaseMetaData dbMetaData = conn.getMetaData();
254:
255: File sqlFile = null;
256:
257: try {
258: sqlFile = AvalonContextUtilities.getFile(context,
259: m_sqlFileName);
260: } catch (Exception e) {
261: getLogger().fatalError(e.getMessage(), e);
262: throw e;
263: }
264:
265: if (getLogger().isDebugEnabled()) {
266: logBuffer = new StringBuffer(256).append(
267: "Reading SQL resources from file: ").append(
268: sqlFile.getAbsolutePath()).append(", section ")
269: .append(this .getClass().getName()).append(".");
270: getLogger().debug(logBuffer.toString());
271: }
272:
273: SqlResources sqlStatements = new SqlResources();
274: sqlStatements.init(sqlFile, this .getClass().getName(),
275: conn, m_sqlParameters);
276:
277: // Create the SQL Strings to use for this table.
278: // Fetches all Users from the db.
279: m_getUsersSql = sqlStatements.getSqlString("select", true);
280:
281: // Get a user by lowercase name. (optional)
282: // If not provided, the entire list is iterated to find a user.
283: m_userByNameCaseInsensitiveSql = sqlStatements
284: .getSqlString("selectByLowercaseName");
285:
286: // Insert, update and delete are not guaranteed to be case-insensitive
287: // Will always be called with correct case in username..
288: m_insertUserSql = sqlStatements
289: .getSqlString("insert", true);
290: m_updateUserSql = sqlStatements
291: .getSqlString("update", true);
292: m_deleteUserSql = sqlStatements
293: .getSqlString("delete", true);
294:
295: // Creates a single table with "username" the Primary Key.
296: m_createUserTableSql = sqlStatements.getSqlString(
297: "createTable", true);
298:
299: // Check if the required table exists. If not, create it.
300: // The table name is defined in the SqlResources.
301: String tableName = sqlStatements.getSqlString("tableName",
302: true);
303:
304: // Need to ask in the case that identifiers are stored, ask the DatabaseMetaInfo.
305: // NB this should work, but some drivers (eg mm MySQL)
306: // don't return the right details, hence the hackery below.
307: /*
308: String tableName = m_tableName;
309: if ( dbMetaData.storesLowerCaseIdentifiers() ) {
310: tableName = tableName.toLowerCase(Locale.US);
311: }
312: else if ( dbMetaData.storesUpperCaseIdentifiers() ) {
313: tableName = tableName.toUpperCase(Locale.US);
314: }
315: */
316:
317: // Try UPPER, lower, and MixedCase, to see if the table is there.
318: if (!theJDBCUtil.tableExists(dbMetaData, tableName)) {
319: // Users table doesn't exist - create it.
320: PreparedStatement createStatement = null;
321: try {
322: createStatement = conn
323: .prepareStatement(m_createUserTableSql);
324: createStatement.execute();
325: } finally {
326: theJDBCUtil.closeJDBCStatement(createStatement);
327: }
328:
329: logBuffer = new StringBuffer(128).append(
330: this .getClass().getName()).append(
331: ": Created table \'").append(tableName).append(
332: "\'.");
333: getLogger().info(logBuffer.toString());
334: } else {
335: if (getLogger().isDebugEnabled()) {
336: getLogger().debug("Using table: " + tableName);
337: }
338: }
339:
340: } finally {
341: theJDBCUtil.closeJDBCConnection(conn);
342: }
343: }
344:
345: /**
346: * Produces the complete list of User names, with correct case.
347: * @return a <code>List</code> of <code>String</code>s representing
348: * user names.
349: */
350: protected List listUserNames() {
351: Collection users = getAllUsers();
352: List userNames = new ArrayList(users.size());
353: for (Iterator it = users.iterator(); it.hasNext();) {
354: userNames.add(((User) it.next()).getUserName());
355: }
356: users.clear();
357: return userNames;
358: }
359:
360: //
361: // Superclass methods - overridden from AbstractUsersRepository
362: //
363: /**
364: * Returns a list populated with all of the Users in the repository.
365: * @return an <code>Iterator</code> of <code>JamesUser</code>s.
366: */
367: protected Iterator listAllUsers() {
368: return getAllUsers().iterator();
369: }
370:
371: /**
372: * Returns a list populated with all of the Users in the repository.
373: * @return a <code>Collection</code> of <code>JamesUser</code>s.
374: */
375: private Collection getAllUsers() {
376: List userList = new ArrayList(); // Build the users into this list.
377:
378: Connection conn = openConnection();
379: PreparedStatement getUsersStatement = null;
380: ResultSet rsUsers = null;
381: try {
382: // Get a ResultSet containing all users.
383: getUsersStatement = conn.prepareStatement(m_getUsersSql);
384: rsUsers = getUsersStatement.executeQuery();
385:
386: // Loop through and build a User for every row.
387: while (rsUsers.next()) {
388: User user = readUserFromResultSet(rsUsers);
389: userList.add(user);
390: }
391: } catch (SQLException sqlExc) {
392: sqlExc.printStackTrace();
393: throw new CascadingRuntimeException(
394: "Error accessing database", sqlExc);
395: } finally {
396: theJDBCUtil.closeJDBCResultSet(rsUsers);
397: theJDBCUtil.closeJDBCStatement(getUsersStatement);
398: theJDBCUtil.closeJDBCConnection(conn);
399: }
400:
401: return userList;
402: }
403:
404: /**
405: * Adds a user to the underlying Repository.
406: * The user name must not clash with an existing user.
407: *
408: * @param user the user to be added
409: */
410: protected void doAddUser(User user) {
411: Connection conn = openConnection();
412: PreparedStatement addUserStatement = null;
413:
414: // Insert into the database.
415: try {
416: // Get a PreparedStatement for the insert.
417: addUserStatement = conn.prepareStatement(m_insertUserSql);
418:
419: setUserForInsertStatement(user, addUserStatement);
420:
421: addUserStatement.execute();
422: } catch (SQLException sqlExc) {
423: sqlExc.printStackTrace();
424: throw new CascadingRuntimeException(
425: "Error accessing database", sqlExc);
426: } finally {
427: theJDBCUtil.closeJDBCStatement(addUserStatement);
428: theJDBCUtil.closeJDBCConnection(conn);
429: }
430: }
431:
432: /**
433: * Removes a user from the underlying repository.
434: * If the user doesn't exist this method doesn't throw
435: * an exception.
436: *
437: * @param user the user to be removed
438: */
439: protected void doRemoveUser(User user) {
440: String username = user.getUserName();
441:
442: Connection conn = openConnection();
443: PreparedStatement removeUserStatement = null;
444:
445: // Delete from the database.
446: try {
447: removeUserStatement = conn
448: .prepareStatement(m_deleteUserSql);
449: removeUserStatement.setString(1, username);
450: removeUserStatement.execute();
451: } catch (SQLException sqlExc) {
452: sqlExc.printStackTrace();
453: throw new CascadingRuntimeException(
454: "Error accessing database", sqlExc);
455: } finally {
456: theJDBCUtil.closeJDBCStatement(removeUserStatement);
457: theJDBCUtil.closeJDBCConnection(conn);
458: }
459: }
460:
461: /**
462: * Updates a user record to match the supplied User.
463: *
464: * @param user the updated user record
465: */
466: protected void doUpdateUser(User user) {
467: Connection conn = openConnection();
468: PreparedStatement updateUserStatement = null;
469:
470: // Update the database.
471: try {
472: updateUserStatement = conn
473: .prepareStatement(m_updateUserSql);
474: setUserForUpdateStatement(user, updateUserStatement);
475: updateUserStatement.execute();
476: } catch (SQLException sqlExc) {
477: sqlExc.printStackTrace();
478: throw new CascadingRuntimeException(
479: "Error accessing database", sqlExc);
480: } finally {
481: theJDBCUtil.closeJDBCStatement(updateUserStatement);
482: theJDBCUtil.closeJDBCConnection(conn);
483: }
484: }
485:
486: /**
487: * Gets a user by name, ignoring case if specified.
488: * If the specified SQL statement has been defined, this method
489: * overrides the basic implementation in AbstractUsersRepository
490: * to increase performance.
491: *
492: * @param name the name of the user being retrieved
493: * @param ignoreCase whether the name is regarded as case-insensitive
494: *
495: * @return the user being retrieved, null if the user doesn't exist
496: */
497: protected User getUserByName(String name, boolean ignoreCase) {
498: // See if this statement has been set, if not, use
499: // simple superclass method.
500: if (m_userByNameCaseInsensitiveSql == null) {
501: return super .getUserByName(name, ignoreCase);
502: }
503:
504: // Always get the user via case-insensitive SQL,
505: // then check case if necessary.
506: Connection conn = openConnection();
507: PreparedStatement getUsersStatement = null;
508: ResultSet rsUsers = null;
509: try {
510: // Get a ResultSet containing all users.
511: String sql = m_userByNameCaseInsensitiveSql;
512: getUsersStatement = conn.prepareStatement(sql);
513:
514: getUsersStatement.setString(1, name.toLowerCase(Locale.US));
515:
516: rsUsers = getUsersStatement.executeQuery();
517:
518: // For case-insensitive matching, the first matching user will be returned.
519: User user = null;
520: while (rsUsers.next()) {
521: User rowUser = readUserFromResultSet(rsUsers);
522: String actualName = rowUser.getUserName();
523:
524: // Check case before we assume it's the right one.
525: if (ignoreCase || actualName.equals(name)) {
526: user = rowUser;
527: break;
528: }
529: }
530: return user;
531: } catch (SQLException sqlExc) {
532: sqlExc.printStackTrace();
533: throw new CascadingRuntimeException(
534: "Error accessing database", sqlExc);
535: } finally {
536: theJDBCUtil.closeJDBCResultSet(rsUsers);
537: theJDBCUtil.closeJDBCStatement(getUsersStatement);
538: theJDBCUtil.closeJDBCConnection(conn);
539: }
540: }
541:
542: /**
543: * Reads properties for a User from an open ResultSet.
544: * Subclass implementations of this method must have knowledge of the fields
545: * presented by the "select" and "selectByLowercaseName" SQL statements.
546: * These implemenations may generate a subclass-specific User instance.
547: *
548: * @param rsUsers A ResultSet with a User record in the current row.
549: * @return A User instance
550: * @throws SQLException
551: * if an exception occurs reading from the ResultSet
552: */
553: protected abstract User readUserFromResultSet(ResultSet rsUsers)
554: throws SQLException;
555:
556: /**
557: * Set parameters of a PreparedStatement object with
558: * property values from a User instance.
559: * Implementations of this method have knowledge of the parameter
560: * ordering of the "insert" SQL statement definition.
561: *
562: * @param user a User instance, which should be an implementation class which
563: * is handled by this Repostory implementation.
564: * @param userInsert a PreparedStatement initialised with SQL taken from the "insert" SQL definition.
565: * @throws SQLException
566: * if an exception occurs while setting parameter values.
567: */
568: protected abstract void setUserForInsertStatement(User user,
569: PreparedStatement userInsert) throws SQLException;
570:
571: /**
572: * Set parameters of a PreparedStatement object with
573: * property values from a User instance.
574: * Implementations of this method have knowledge of the parameter
575: * ordering of the "update" SQL statement definition.
576: *
577: * @param user a User instance, which should be an implementation class which
578: * is handled by this Repostory implementation.
579: * @param userUpdate a PreparedStatement initialised with SQL taken from the "update" SQL definition.
580: * @throws SQLException
581: * if an exception occurs while setting parameter values.
582: */
583: protected abstract void setUserForUpdateStatement(User user,
584: PreparedStatement userUpdate) throws SQLException;
585:
586: /**
587: * Opens a connection, throwing a runtime exception if a SQLException is
588: * encountered in the process.
589: *
590: * @return the new connection
591: */
592: private Connection openConnection() {
593: try {
594: return m_datasource.getConnection();
595: } catch (SQLException sqle) {
596: throw new CascadingRuntimeException(
597: "An exception occurred getting a database connection.",
598: sqle);
599: }
600: }
601: }
|