View Javadoc

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