View Javadoc

1   /*
2    * Copyright [2007] [University Corporation for Advanced Internet Development, Inc.]
3    *
4    * Licensed under the Apache License, Version 2.0 (the "License");
5    * you may not use this file except in compliance with the License.
6    * You may obtain a copy of the License at
7    *
8    * http://www.apache.org/licenses/LICENSE-2.0
9    *
10   * Unless required by applicable law or agreed to in writing, software
11   * distributed under the License is distributed on an "AS IS" BASIS,
12   * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13   * See the License for the specific language governing permissions and
14   * limitations under the License.
15   */
16  
17  package edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.dataConnector;
18  
19  import java.sql.Connection;
20  import java.sql.DatabaseMetaData;
21  import java.sql.ResultSet;
22  import java.sql.ResultSetMetaData;
23  import java.sql.SQLException;
24  import java.sql.Statement;
25  import java.util.Collection;
26  import java.util.HashMap;
27  import java.util.Map;
28  
29  import javax.sql.DataSource;
30  
31  import net.sf.ehcache.Cache;
32  import net.sf.ehcache.Element;
33  
34  import org.slf4j.Logger;
35  import org.slf4j.LoggerFactory;
36  
37  import edu.internet2.middleware.shibboleth.common.attribute.BaseAttribute;
38  import edu.internet2.middleware.shibboleth.common.attribute.provider.BasicAttribute;
39  import edu.internet2.middleware.shibboleth.common.attribute.resolver.AttributeResolutionException;
40  import edu.internet2.middleware.shibboleth.common.attribute.resolver.provider.ShibbolethResolutionContext;
41  
42  /**
43   * A data connector that can retrieve information from a relational database through JDBC, version 3.
44   */
45  public class RDBMSDataConnector extends BaseDataConnector {
46  
47      /** Data types understood by this connector. */
48      public static enum DATA_TYPES {
49          BigDecimal, Boolean, Byte, ByteArray, Date, Double, Float, Integer, Long, Object, Short, String, Time, Timestamp, URL
50      };
51  
52      /** Class logger. */
53      private final Logger log = LoggerFactory.getLogger(RDBMSDataConnector.class);
54  
55      /** JDBC data source for retrieving connections. */
56      private DataSource dataSource;
57  
58      /** Template engine used to change query template into actual query. */
59      private TemplateEngine queryCreator;
60  
61      /** Name the query template is registered under with the statement creator. */
62      private String queryTemplateName;
63  
64      /** Template that produces the query to use. */
65      private String queryTemplate;
66      
67      /** SQL query timeout in seconds. */
68      private int queryTimeout;
69  
70      /** Whether the JDBC connection is read-only. */
71      private boolean readOnlyConnection;
72  
73      /** Whether queries might use stored procedures. */
74      private boolean usesStoredProcedure;
75  
76      /** Whether an empty result set is an error. */
77      private boolean noResultIsError;
78  
79      /** Set of column descriptors for managing returned data. [columnName => colmentDescriptr] */
80      private Map<String, RDBMSColumnDescriptor> columnDescriptors;
81      
82      /** Query result cache. */
83      private Cache resultsCache;
84      
85      /**
86       * Constructor.
87       * 
88       * @param source data source used to retrieve connections
89       * @param cache cache used to cache results
90       */
91      public RDBMSDataConnector(DataSource source, Cache cache) {
92          super();
93  
94          dataSource = source;
95  
96          resultsCache = cache;
97  
98          readOnlyConnection = true;
99          usesStoredProcedure = false;
100         noResultIsError = false;
101 
102         columnDescriptors = new HashMap<String, RDBMSColumnDescriptor>();
103     }
104 
105     /**
106      * This sets the underlying template engine and registers the supplied template.
107      * 
108      * @param engine template engine used to generate the query
109      * @param template template used to generate the query
110      */
111     public void registerTemplate(TemplateEngine engine, String template) {
112         if (getId() == null) {
113             throw new IllegalStateException("Template cannot be registered until plugin id has been set");
114         }
115         queryCreator = engine;
116         queryTemplate = template;
117         queryTemplateName = "shibboleth.resolver.dc." + getId();
118         queryCreator.registerTemplate(queryTemplateName, queryTemplate);
119     }
120 
121     /**
122      * Gets whether this data connector is caching results.
123      * 
124      * @return true if this data connector is caching results, false if not
125      */
126     public boolean isCachingResuts() {
127         return resultsCache != null;
128     }
129 
130     /**
131      * Gets the timeout, in seconds, of the SQL query.
132      * 
133      * @return timeout, in seconds, of the SQL query
134      */
135     public int getQueryTimeout() {
136         return queryTimeout;
137     }
138     
139     /**
140      * Sets the timeout, in seconds, of the SQL query.
141      * 
142      * @param timeout timeout, in seconds, of the SQL query
143      */
144     public void setQueryTimeout(int timeout) {
145         queryTimeout = timeout;
146     }
147     
148     /**
149      * Gets whether this data connector uses read-only connections.
150      * 
151      * @return whether this data connector uses read-only connections
152      */
153     public boolean isConnectionReadOnly() {
154         return readOnlyConnection;
155     }
156 
157     /**
158      * Sets whether this data connector uses read-only connections.
159      * 
160      * @param isReadOnly whether this data connector uses read-only connections
161      */
162     public void setConnectionReadOnly(boolean isReadOnly) {
163         readOnlyConnection = isReadOnly;
164     }
165 
166     /**
167      * Gets whether queries made use stored procedures.
168      * 
169      * @return whether queries made use stored procedures
170      */
171     public boolean getUsesStoredProcedure() {
172         return usesStoredProcedure;
173     }
174 
175     /**
176      * Sets whether queries made use stored procedures.
177      * 
178      * @param storedProcedure whether queries made use stored procedures
179      */
180     public void setUsesStoredProcedure(boolean storedProcedure) {
181         usesStoredProcedure = storedProcedure;
182     }
183 
184     /**
185      * This returns whether this connector will throw an exception if no search results are found. The default is false.
186      * 
187      * @return <code>boolean</code>
188      */
189     public boolean isNoResultIsError() {
190         return noResultIsError;
191     }
192 
193     /**
194      * This sets whether this connector will throw an exception if no search results are found.
195      * 
196      * @param isError <code>boolean</code>
197      */
198     public void setNoResultIsError(boolean isError) {
199         noResultIsError = isError;
200     }
201 
202     /**
203      * Gets the set of column descriptors used to deal with result set data. The name of the database column is the
204      * map's key. This list is unmodifiable.
205      * 
206      * @return column descriptors used to deal with result set data
207      */
208     public Map<String, RDBMSColumnDescriptor> getColumnDescriptor() {
209         return columnDescriptors;
210     }
211 
212     /** {@inheritDoc} */
213     public void validate() throws AttributeResolutionException {
214         log.debug("RDBMS data connector {} - Validating configuration.", getId());
215 
216         if (dataSource == null) {
217             log.error("RDBMS data connector {} - Datasource is null", getId());
218             throw new AttributeResolutionException("Datasource is null");
219         }
220 
221         Connection connection = null;
222         try {
223             connection = dataSource.getConnection();
224             if (connection == null) {
225                 log.error("RDBMS data connector {} - Unable to create connections", getId());
226                 throw new AttributeResolutionException("Unable to create connections for RDBMS data connector "
227                         + getId());
228             }
229 
230             DatabaseMetaData dbmd = connection.getMetaData();
231             if (!dbmd.supportsStoredProcedures() && usesStoredProcedure) {
232                 log.error("RDBMS data connector {} - Database does not support stored procedures.", getId());
233                 throw new AttributeResolutionException("Database does not support stored procedures.");
234             }
235 
236             log.debug("RDBMS data connector {} - Connector configuration is valid.", getId());
237         } catch (SQLException e) {
238             if (e.getSQLState() != null) {
239                 log.error("RDBMS data connector {} - Invalid connector configuration; SQL state: {}, SQL Code: {}",
240                         new Object[] { getId(), e.getSQLState(), e.getErrorCode() }, e);
241             } else {
242                 log.error("RDBMS data connector {} - Invalid connector configuration", new Object[] { getId() }, e);
243             }
244             throw new AttributeResolutionException("Invalid connector configuration", e);
245         } finally {
246             try {
247                 if (connection != null && !connection.isClosed()) {
248                     connection.close();
249                 }
250             } catch (SQLException e) {
251                 log.error("RDBMS data connector {} - Error closing database connection; SQL State: {}, SQL Code: {}",
252                         new Object[] { getId(), e.getSQLState(), e.getErrorCode() }, e);
253             }
254         }
255     }
256 
257     /** {@inheritDoc} */
258     public Map<String, BaseAttribute> resolve(ShibbolethResolutionContext resolutionContext)
259             throws AttributeResolutionException {
260         String query = queryCreator.createStatement(queryTemplateName, resolutionContext, getDependencyIds(), null);
261         log.debug("RDBMS data connector {} - Search Query: {}", getId(), query);
262 
263         Map<String, BaseAttribute> resolvedAttributes = null;
264         resolvedAttributes = retrieveAttributesFromCache(resolutionContext.getAttributeRequestContext()
265                 .getPrincipalName(), query);
266 
267         if (resolvedAttributes == null) {
268             resolvedAttributes = retrieveAttributesFromDatabase(query);
269         }
270 
271         cacheResult(resolutionContext.getAttributeRequestContext().getPrincipalName(), query, resolvedAttributes);
272 
273         return resolvedAttributes;
274     }
275 
276     /**
277      * Attempts to retrieve the attributes from the cache.
278      * 
279      * @param principal the principal name of the user the attributes are for
280      * @param query query used to generate the attributes
281      * 
282      * @return cached attributes
283      * 
284      * @throws AttributeResolutionException thrown if there is a problem retrieving data from the cache
285      */
286     protected Map<String, BaseAttribute> retrieveAttributesFromCache(String principal, String query)
287             throws AttributeResolutionException {
288         if (resultsCache == null) {
289             return null;
290         }
291 
292         Element cacheElement = resultsCache.get(query);
293         if (cacheElement != null && !cacheElement.isExpired()) {
294             log.debug("RDBMS data connector {} - Fetched attributes from cache for principal {}", getId(), principal);
295             return (Map<String, BaseAttribute>) cacheElement.getObjectValue();
296         }
297 
298         return null;
299     }
300 
301     /**
302      * Attempts to retrieve the attribute from the database.
303      * 
304      * @param query query used to get the attributes
305      * 
306      * @return attributes gotten from the database
307      * 
308      * @throws AttributeResolutionException thrown if there is a problem retrieving data from the database or
309      *             transforming that data into {@link BaseAttribute}s
310      */
311     protected Map<String, BaseAttribute> retrieveAttributesFromDatabase(String query)
312             throws AttributeResolutionException {
313         Map<String, BaseAttribute> resolvedAttributes;
314         Connection connection = null;
315         ResultSet queryResult = null;
316 
317         try {
318             connection = dataSource.getConnection();
319             if (readOnlyConnection) {
320                 connection.setReadOnly(true);
321             }
322             log.debug("RDBMS data connector {} - Querying database for attributes with query {}", getId(), query);
323             Statement stmt = connection.createStatement();
324             stmt.setQueryTimeout(queryTimeout);
325             queryResult = stmt.executeQuery(query);
326             resolvedAttributes = processResultSet(queryResult);
327             if (resolvedAttributes.isEmpty() && noResultIsError) {
328                 log.debug("RDBMS data connector {} - No attributes from query", getId());
329                 throw new AttributeResolutionException("No attributes returned from query");
330             }
331             log.debug("RDBMS data connector {} - Retrieved attributes: {}", getId(), resolvedAttributes.keySet());
332             return resolvedAttributes;
333         } catch (SQLException e) {
334             log.debug("RDBMS data connector {} - Unable to execute SQL query {}; SQL State: {}, SQL Code: {}",
335                     new Object[] { getId(), query, e.getSQLState(), e.getErrorCode(), }, e);
336             throw new AttributeResolutionException("Unable to execute SQL query", e);
337         } finally {
338             try {
339                 if (queryResult != null) {
340                     queryResult.close();
341                 }
342 
343                 if (connection != null && !connection.isClosed()) {
344                     connection.close();
345                 }
346             } catch (SQLException e) {
347                 log.debug("RDBMS data connector {} - Unable to close database connection; SQL State: {}, SQL Code: {}",
348                         new Object[] { getId(), e.getSQLState(), e.getErrorCode() }, e);
349             }
350         }
351     }
352 
353     /**
354      * Converts a SQL query results set into a set of {@link BaseAttribute}s.
355      * 
356      * @param resultSet the result set to convert
357      * 
358      * @return the resultant set of attributes
359      * 
360      * @throws AttributeResolutionException thrown if there is a problem converting the result set into attributes
361      */
362     protected Map<String, BaseAttribute> processResultSet(ResultSet resultSet) throws AttributeResolutionException {
363         Map<String, BaseAttribute> attributes = new HashMap<String, BaseAttribute>();
364 
365         try {
366             if (!resultSet.next()) {
367                 return attributes;
368             }
369 
370             ResultSetMetaData resultMD = resultSet.getMetaData();
371             int numOfCols = resultMD.getColumnCount();
372             String columnName;
373             RDBMSColumnDescriptor columnDescriptor;
374             String attributeId;
375             BaseAttribute attribute;
376             Collection attributeValues;
377             
378             do {
379                 for (int i = 1; i <= numOfCols; i++) {
380                     columnName = resultMD.getColumnName(i);
381                     columnDescriptor = columnDescriptors.get(columnName);
382 
383                     if (columnDescriptor == null || columnDescriptor.getAttributeID() == null) {
384                         attributeId = columnName;
385                     } else {
386                         attributeId = columnDescriptor.getAttributeID();
387                     }
388                     
389                     attribute = attributes.get(attributeId);
390                     if (attribute == null) {
391                         attribute = new BasicAttribute(attributeId);
392                     }
393 
394                     attributes.put(attribute.getId(), attribute);
395                     attributeValues = attribute.getValues();
396                     if (columnDescriptor == null || columnDescriptor.getDataType() == null) {
397                         attributeValues.add(resultSet.getObject(i));
398                     } else {
399                         addValueByType(attributeValues, columnDescriptor.getDataType(), resultSet, i);
400                     }
401                 }
402             } while (resultSet.next());
403         } catch (SQLException e) {
404             log.debug("RDBMS data connector {} - Unable to read data from query result; SQL State: {}, SQL Code: {}",
405                     new Object[] { getId(), e.getSQLState(), e.getErrorCode() }, e);
406         }
407 
408         return attributes;
409     }
410 
411     /**
412      * Adds a value extracted from the result set as a specific type into the value set.
413      * 
414      * @param values set to add values into
415      * @param type type the value should be extracted as
416      * @param resultSet result set, on the current row, to extract the value from
417      * @param columnIndex index of the column from which to extract the attribute
418      * 
419      * @throws SQLException thrown if value can not retrieved from the result set
420      */
421     protected void addValueByType(Collection values, DATA_TYPES type, ResultSet resultSet, int columnIndex)
422             throws SQLException {
423         switch (type) {
424             case BigDecimal:
425                 values.add(resultSet.getBigDecimal(columnIndex));
426                 break;
427             case Boolean:
428                 values.add(resultSet.getBoolean(columnIndex));
429                 break;
430             case Byte:
431                 values.add(resultSet.getByte(columnIndex));
432                 break;
433             case ByteArray:
434                 values.add(resultSet.getBytes(columnIndex));
435                 break;
436             case Date:
437                 values.add(resultSet.getDate(columnIndex));
438                 break;
439             case Double:
440                 values.add(resultSet.getDouble(columnIndex));
441                 break;
442             case Float:
443                 values.add(resultSet.getFloat(columnIndex));
444                 break;
445             case Integer:
446                 values.add(resultSet.getInt(columnIndex));
447                 break;
448             case Long:
449                 values.add(resultSet.getLong(columnIndex));
450                 break;
451             case Object:
452                 values.add(resultSet.getObject(columnIndex));
453                 break;
454             case Short:
455                 values.add(resultSet.getShort(columnIndex));
456                 break;
457             case Time:
458                 values.add(resultSet.getTime(columnIndex));
459                 break;
460             case Timestamp:
461                 values.add(resultSet.getTimestamp(columnIndex));
462                 break;
463             case URL:
464                 values.add(resultSet.getURL(columnIndex));
465                 break;
466             default:
467                 values.add(resultSet.getString(columnIndex));
468         }
469     }
470 
471     /**
472      * Caches the attributes resulting from a query.
473      * 
474      * @param principal the principal name of the user the attributes are for
475      * @param query the query that generated the attributes
476      * @param attributes the results of the query
477      */
478     protected void cacheResult(String principal, String query, Map<String, BaseAttribute> attributes) {
479         if (resultsCache == null) {
480             return;
481         }
482 
483         log.debug("RDBMS data connector {} - Caching attributes from for principal {}", getId(), principal);
484         Element cacheElement = new Element(query, attributes);
485         resultsCache.put(cacheElement);
486     }
487 }