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.io.Serializable;
20  import java.sql.Connection;
21  import java.sql.PreparedStatement;
22  import java.sql.ResultSet;
23  import java.sql.SQLException;
24  import java.sql.Timestamp;
25  import java.sql.Types;
26  import java.util.ArrayList;
27  import java.util.List;
28  
29  import javax.sql.DataSource;
30  
31  import org.slf4j.Logger;
32  import org.slf4j.LoggerFactory;
33  
34  /**
35   * Represents as persistent, database-backed, store of identifiers.
36   * 
37   * The DDL for the database is
38   * <tt>CREATE TABLE shibpid {localEntity VARCHAR NOT NULL, peerEntity VARCHAR NOT NULL, principalName VARCHAR NOT NULL, localId VARCHAR NOT NULL, persistentId VARCHAR NOT NULL, peerProvidedId VARCHAR, creationDate TIMESTAMP NOT NULL, deactivationDate TIMESTAMP}</tt>.
39   */
40  public class StoredIDStore {
41  
42      /** Class logger. */
43      private final Logger log = LoggerFactory.getLogger(StoredIDStore.class);
44  
45      /** JDBC data source for retrieving connections. */
46      private DataSource dataSource;
47  
48      /** Name of the database table. */
49      private final String table = "shibpid";
50  
51      /** Name of the local entity ID column. */
52      private final String localEntityColumn = "localEntity";
53  
54      /** Name of the peer entity ID name column. */
55      private final String peerEntityColumn = "peerEntity";
56  
57      /** Name of the principal name column. */
58      private final String principalNameColumn = "principalName";
59  
60      /** Name of the local ID column. */
61      private final String localIdColumn = "localId";
62  
63      /** Name of the persistent ID column. */
64      private final String persistentIdColumn = "persistentId";
65  
66      /** ID, provided by peer, associated with the persistent ID. */
67      private final String peerProvidedIdColumn = "peerProvidedId";
68  
69      /** Name of the creation time column. */
70      private final String createTimeColumn = "creationDate";
71  
72      /** Name of the deactivation time column. */
73      private final String deactivationTimeColumn = "deactivationDate";
74  
75      /** Partial select query for ID entries. */
76      private final String idEntrySelectSQL = "SELECT * FROM " + table + " WHERE ";
77  
78      /** SQL used to deactivate an ID. */
79      private final String deactivateIdSQL = "UPDATE " + table + " SET " + deactivationTimeColumn + "= ? WHERE "
80              + persistentIdColumn + "= ?";
81  
82      /**
83       * Constructor.
84       * 
85       * @param source datasource used to communicate with the database
86       */
87      public StoredIDStore(DataSource source) {
88          dataSource = source;
89      }
90  
91      /**
92       * Gets the number of persistent ID entries for a (principal, peer, local) tuple.
93       * 
94       * @param localEntity entity ID of the ID issuer
95       * @param peerEntity entity ID of the peer the ID is for
96       * @param localId local ID part of the persistent ID
97       * 
98       * @return the number of identifiers
99       * 
100      * @throws SQLException thrown if there is a problem communication with the database
101      */
102     public int getNumberOfPersistentIdEntries(String localEntity, String peerEntity, String localId)
103             throws SQLException {
104         StringBuilder sqlBuilder = new StringBuilder();
105         sqlBuilder.append("SELECT");
106         sqlBuilder.append(" count(").append(persistentIdColumn).append(")");
107         sqlBuilder.append(" FROM ").append(table).append(" WHERE ");
108         sqlBuilder.append(localEntityColumn).append(" = ?");
109         sqlBuilder.append(" AND ");
110         sqlBuilder.append(peerEntityColumn).append(" = ?");
111         sqlBuilder.append(" AND ");
112         sqlBuilder.append(localIdColumn).append(" = ?");
113 
114         String sql = sqlBuilder.toString();
115         Connection dbConn = dataSource.getConnection();
116         try {
117             log.debug("Selecting number of persistent ID entries based on prepared sql statement: {}", sql);
118             PreparedStatement statement = dbConn.prepareStatement(sql);
119 
120             log.debug("Setting prepared statement parameter {}: {}", 1, localEntity);
121             statement.setString(1, localEntity);
122             log.debug("Setting prepared statement parameter {}: {}", 2, peerEntity);
123             statement.setString(2, peerEntity);
124             log.debug("Setting prepared statement parameter {}: {}", 3, localId);
125             statement.setString(3, localId);
126 
127             ResultSet rs = statement.executeQuery();
128             rs.next();
129             return rs.getInt(1);
130         } finally {
131             try {
132                 if (dbConn != null && !dbConn.isClosed()) {
133                     dbConn.close();
134                 }
135             } catch (SQLException e) {
136                 log.error("Error closing database connection", e);
137             }
138         }
139     }
140 
141     /**
142      * Gets all the persistent ID entries for a (principal, peer, local) tuple.
143      * 
144      * @param localId local ID part of the persistent ID
145      * @param peerEntity entity ID of the peer the ID is for
146      * @param localEntity entity ID of the ID issuer
147      * 
148      * @return the active identifier
149      * 
150      * @throws SQLException thrown if there is a problem communication with the database
151      */
152     public List<PersistentIdEntry> getPersistentIdEntries(String localEntity, String peerEntity, String localId)
153             throws SQLException {
154         StringBuilder sqlBuilder = new StringBuilder(idEntrySelectSQL);
155         sqlBuilder.append(localEntityColumn).append(" = ?");
156         sqlBuilder.append(" AND ").append(peerEntityColumn).append(" = ?");
157         sqlBuilder.append(" AND ").append(localIdColumn).append(" = ?");
158         String sql = sqlBuilder.toString();
159 
160         log.debug("Selecting all persistent ID entries based on prepared sql statement: {}", sql);
161 
162         Connection dbConn = dataSource.getConnection();
163         try {
164             PreparedStatement statement = dbConn.prepareStatement(sql);
165 
166             log.debug("Setting prepared statement parameter {}: {}", 1, localEntity);
167             statement.setString(1, localEntity);
168             log.debug("Setting prepared statement parameter {}: {}", 2, peerEntity);
169             statement.setString(2, peerEntity);
170             log.debug("Setting prepared statement parameter {}: {}", 3, localId);
171             statement.setString(3, localId);
172 
173             return getIdentifierEntries(statement);
174         } finally {
175             try {
176                 if (dbConn != null && !dbConn.isClosed()) {
177                     dbConn.close();
178                 }
179             } catch (SQLException e) {
180                 log.error("Error closing database connection", e);
181             }
182         }
183     }
184 
185     /**
186      * Gets the persistent ID entry for the given ID.
187      * 
188      * @param persistentId the persistent ID
189      * 
190      * @return the ID entry for the given ID
191      * 
192      * @throws SQLException thrown if there is a problem communication with the database
193      */
194     public PersistentIdEntry getActivePersistentIdEntry(String persistentId) throws SQLException {
195         StringBuilder sqlBuilder = new StringBuilder(idEntrySelectSQL);
196         sqlBuilder.append(persistentIdColumn).append(" = ?");
197         sqlBuilder.append(" AND ").append(deactivationTimeColumn).append(" IS NULL");
198         String sql = sqlBuilder.toString();
199 
200         log.debug("Selecting active persistent ID entry based on prepared sql statement: {}", sql);
201 
202         Connection dbConn = dataSource.getConnection();
203         try {
204             PreparedStatement statement = dbConn.prepareStatement(sql);
205 
206             log.debug("Setting prepared statement parameter {}: {}", 1, persistentId);
207             statement.setString(1, persistentId);
208 
209             List<PersistentIdEntry> entries = getIdentifierEntries(statement);
210 
211             if (entries == null || entries.size() == 0) {
212                 return null;
213             }
214 
215             if (entries.size() > 1) {
216                 log.warn("More than one active identifier, only the first will be used");
217             }
218 
219             return entries.get(0);
220         } finally {
221             try {
222                 if (dbConn != null && !dbConn.isClosed()) {
223                     dbConn.close();
224                 }
225             } catch (SQLException e) {
226                 log.error("Error closing database connection", e);
227             }
228         }
229     }
230 
231     /**
232      * Gets the currently active identifier entry for a (principal, peer, local) tuple.
233      * 
234      * @param localId local ID part of the persistent ID
235      * @param peerEntity entity ID of the peer the ID is for
236      * @param localEntity entity ID of the ID issuer
237      * 
238      * @return the active identifier
239      * 
240      * @throws SQLException thrown if there is a problem communication with the database
241      */
242     public PersistentIdEntry getActivePersistentIdEntry(String localEntity, String peerEntity, String localId)
243             throws SQLException {
244         StringBuilder sqlBuilder = new StringBuilder(idEntrySelectSQL);
245         sqlBuilder.append(localEntityColumn).append(" = ?");
246         sqlBuilder.append(" AND ").append(peerEntityColumn).append(" = ?");
247         sqlBuilder.append(" AND ").append(localIdColumn).append(" = ?");
248         sqlBuilder.append(" AND ").append(deactivationTimeColumn).append(" IS NULL");
249         String sql = sqlBuilder.toString();
250 
251         log.debug("Selecting active persistent ID entry based on prepared sql statement: {}", sql);
252         Connection dbConn = dataSource.getConnection();
253         try {
254             PreparedStatement statement = dbConn.prepareStatement(sql);
255 
256             log.debug("Setting prepared statement parameter {}: {}", 1, localEntity);
257             statement.setString(1, localEntity);
258             log.debug("Setting prepared statement parameter {}: {}", 2, peerEntity);
259             statement.setString(2, peerEntity);
260             log.debug("Setting prepared statement parameter {}: {}", 3, localId);
261             statement.setString(3, localId);
262 
263             log.debug("Getting active persistent Id entries.");
264             List<PersistentIdEntry> entries = getIdentifierEntries(statement);
265 
266             if (entries == null || entries.size() == 0) {
267                 return null;
268             }
269 
270             if (entries.size() > 1) {
271                 log.warn("More than one active identifier, only the first will be used");
272             }
273 
274             return entries.get(0);
275         } finally {
276             try {
277                 if (dbConn != null && !dbConn.isClosed()) {
278                     dbConn.close();
279                 }
280             } catch (SQLException e) {
281                 log.error("Error closing database connection", e);
282             }
283         }
284     }
285 
286     /**
287      * Gets the list of deactivated IDs for a given (principal, peer, local) tuple.
288      * 
289      * @param localId local component of the Id
290      * @param peerEntity entity ID of the peer the ID is for
291      * @param localEntity entity ID of the ID issuer
292      * 
293      * @return list of deactivated identifiers
294      * 
295      * @throws SQLException thrown if there is a problem communication with the database
296      */
297     public List<PersistentIdEntry> getDeactivatedPersistentIdEntries(String localEntity, String peerEntity,
298             String localId) throws SQLException {
299         StringBuilder sqlBuilder = new StringBuilder(idEntrySelectSQL);
300         sqlBuilder.append(localEntityColumn).append(" = ?");
301         sqlBuilder.append(" AND ").append(peerEntityColumn).append(" = ?");
302         sqlBuilder.append(" AND ").append(localIdColumn).append(" = ?");
303         sqlBuilder.append(" AND ").append(deactivationTimeColumn).append(" IS NOT NULL");
304         String sql = sqlBuilder.toString();
305 
306         log.debug("Selecting deactivated persistent ID entries based on prepared sql statement: {}", sql);
307         Connection dbConn = dataSource.getConnection();
308         try {
309             PreparedStatement statement = dbConn.prepareStatement(sql);
310 
311             log.debug("Setting prepared statement parameter {}: {}", 1, localEntity);
312             statement.setString(1, localEntity);
313             log.debug("Setting prepared statement parameter {}: {}", 2, peerEntity);
314             statement.setString(2, peerEntity);
315             log.debug("Setting prepared statement parameter {}: {}", 3, localId);
316             statement.setString(3, localId);
317 
318             log.debug("Getting deactivated persistent Id entries");
319             List<PersistentIdEntry> entries = getIdentifierEntries(statement);
320 
321             if (entries == null || entries.size() == 0) {
322                 return null;
323             }
324 
325             return entries;
326         } finally {
327             try {
328                 if (dbConn != null && !dbConn.isClosed()) {
329                     dbConn.close();
330                 }
331             } catch (SQLException e) {
332                 log.error("Error closing database connection", e);
333             }
334         }
335     }
336 
337     /**
338      * Stores a persistent ID entry into the database.
339      * 
340      * @param entry entry to persist
341      * 
342      * @throws SQLException thrown is there is a problem writing to the database
343      */
344     public void storePersistentIdEntry(PersistentIdEntry entry) throws SQLException {
345 
346         StringBuilder sqlBuilder = new StringBuilder("INSERT INTO ");
347         sqlBuilder.append(table).append(" (");
348         sqlBuilder.append(localEntityColumn).append(", ");
349         sqlBuilder.append(peerEntityColumn).append(", ");
350         sqlBuilder.append(principalNameColumn).append(", ");
351         sqlBuilder.append(localIdColumn).append(", ");
352         sqlBuilder.append(persistentIdColumn).append(", ");
353         sqlBuilder.append(peerProvidedIdColumn).append(", ");
354         sqlBuilder.append(createTimeColumn);
355         sqlBuilder.append(") VALUES (?, ?, ?, ?, ?, ?, ?)");
356 
357         String sql = sqlBuilder.toString();
358 
359         Connection dbConn = dataSource.getConnection();
360         try {
361             log.debug("Storing persistent ID entry based on prepared sql statement: {}", sql);
362             PreparedStatement statement = dbConn.prepareStatement(sql);
363 
364             log.debug("Setting prepared statement parameter {}: {}", 1, entry.getLocalEntityId());
365             statement.setString(1, entry.getLocalEntityId());
366             log.debug("Setting prepared statement parameter {}: {}", 2, entry.getPeerEntityId());
367             statement.setString(2, entry.getPeerEntityId());
368             log.debug("Setting prepared statement parameter {}: {}", 3, entry.getPrincipalName());
369             statement.setString(3, entry.getPrincipalName());
370             log.debug("Setting prepared statement parameter {}: {}", 4, entry.getLocalId());
371             statement.setString(4, entry.getLocalId());
372             log.debug("Setting prepared statement parameter {}: {}", 5, entry.getPersistentId());
373             statement.setString(5, entry.getPersistentId());
374 
375             if (entry.getPeerProvidedId() == null) {
376                 log.debug("Setting prepared statement parameter {}: {}", 6, Types.NULL);
377                 statement.setNull(6, Types.NULL);
378             } else {
379                 log.debug("Setting prepared statement parameter {}: {}", 6, entry.getPeerProvidedId());
380                 statement.setString(6, entry.getPeerProvidedId());
381             }
382             Timestamp timestamp = new Timestamp(System.currentTimeMillis());
383             log.debug("Setting prepared statement parameter {}: {}", 7, timestamp.toString());
384             statement.setTimestamp(7, timestamp);
385 
386             statement.executeUpdate();
387         } finally {
388             try {
389                 if (dbConn != null && !dbConn.isClosed()) {
390                     dbConn.close();
391                 }
392             } catch (SQLException e) {
393                 log.error("Error closing database connection", e);
394             }
395         }
396     }
397 
398     /**
399      * Deactivates a given persistent ID.
400      * 
401      * @param persistentId ID to deactivate
402      * @param deactivation deactivation time, if null the current time is used
403      * 
404      * @throws SQLException thrown if there is a problem communication with the database
405      */
406     public void deactivatePersistentId(String persistentId, Timestamp deactivation) throws SQLException {
407         Timestamp deactivationTime = deactivation;
408         if (deactivationTime == null) {
409             deactivationTime = new Timestamp(System.currentTimeMillis());
410         }
411 
412         Connection dbConn = dataSource.getConnection();
413         try {
414             log.debug("Deactivating persistent id {} as of {}", persistentId, deactivationTime.toString());
415             PreparedStatement statement = dbConn.prepareStatement(deactivateIdSQL);
416             statement.setTimestamp(1, deactivationTime);
417             statement.setString(2, persistentId);
418             statement.executeUpdate();
419         } finally {
420             try {
421                 if (dbConn != null && !dbConn.isClosed()) {
422                     dbConn.close();
423                 }
424             } catch (SQLException e) {
425                 log.error("Error closing database connection", e);
426             }
427         }
428     }
429 
430     /**
431      * Gets a list of {@link PersistentIdEntry}s based on the given prepared statement.
432      * 
433      * @param statement SQL prepared statement
434      * 
435      * @return resultant list of {@link PersistentIdEntry}s
436      * 
437      * @throws SQLException thrown if there is a problem communicating with the database
438      */
439     protected List<PersistentIdEntry> getIdentifierEntries(PreparedStatement statement) throws SQLException {
440         List<PersistentIdEntry> entries;
441         Connection dbConn = dataSource.getConnection();
442         try {
443             ResultSet rs = statement.executeQuery();
444             entries = buildIdentifierEntries(rs);
445             log.debug("{} persistent ID entries retrieved", entries.size());
446             return entries;
447         } finally {
448             try {
449                 if (dbConn != null && !dbConn.isClosed()) {
450                     dbConn.close();
451                 }
452             } catch (SQLException e) {
453                 log.error("Error closing database connection", e);
454             }
455         }
456     }
457 
458     /**
459      * Builds a list of {@link PersistentIdEntry}s from a result set.
460      * 
461      * @param resultSet the result set
462      * 
463      * @return list of {@link PersistentIdEntry}s
464      * 
465      * @throws SQLException thrown if there is a problem reading the information from the database
466      */
467     protected List<PersistentIdEntry> buildIdentifierEntries(ResultSet resultSet) throws SQLException {
468         ArrayList<PersistentIdEntry> entries = new ArrayList<PersistentIdEntry>();
469 
470         PersistentIdEntry entry;
471         while (resultSet.next()) {
472             entry = new PersistentIdEntry();
473             entry.setLocalEntityId(resultSet.getString(localEntityColumn));
474             entry.setPeerEntityId(resultSet.getString(peerEntityColumn));
475             entry.setPrincipalName(resultSet.getString(principalNameColumn));
476             entry.setPersistentId(resultSet.getString(persistentIdColumn));
477             entry.setLocalId(resultSet.getString(localIdColumn));
478             entry.setPeerProvidedId(resultSet.getString(peerProvidedIdColumn));
479             entry.setCreationTime(resultSet.getTimestamp(createTimeColumn));
480             entry.setDeactivationTime(resultSet.getTimestamp(deactivationTimeColumn));
481             entries.add(entry);
482             
483             log.trace("");
484         }
485 
486         return entries;
487     }
488 
489     /** Data object representing a persistent identifier entry in the database. */
490     public class PersistentIdEntry implements Serializable {
491 
492         /** Serial version UID . */
493         private static final long serialVersionUID = -8711779466442306767L;
494 
495         /** ID of the entity that issued that identifier. */
496         private String localEntityId;
497 
498         /** ID of the entity to which the identifier was issued. */
499         private String peerEntityId;
500 
501         /** Name of the principal represented by the identifier. */
502         private String principalName;
503 
504         /** Local component portion of the persistent ID entry. */
505         private String localId;
506 
507         /** The persistent identifier. */
508         private String persistentId;
509 
510         /** ID, associated with the persistent identifier, provided by the peer. */
511         private String peerProvidedId;
512 
513         /** Time the identifier was created. */
514         private Timestamp creationTime;
515 
516         /** Time the identifier was deactivated. */
517         private Timestamp deactivationTime;
518 
519         /** Constructor. */
520         public PersistentIdEntry() {
521         }
522 
523         /**
524          * Gets the ID of the entity that issued the identifier.
525          * 
526          * @return ID of the entity that issued the identifier
527          */
528         public String getLocalEntityId() {
529             return localEntityId;
530         }
531 
532         /**
533          * Sets the ID of the entity that issued the identifier.
534          * 
535          * @param id ID of the entity that issued the identifier
536          */
537         public void setLocalEntityId(String id) {
538             localEntityId = id;
539         }
540 
541         /**
542          * Gets the ID of the entity to which the identifier was issued.
543          * 
544          * @return ID of the entity to which the identifier was issued
545          */
546         public String getPeerEntityId() {
547             return peerEntityId;
548         }
549 
550         /**
551          * Sets the ID of the entity to which the identifier was issued.
552          * 
553          * @param id ID of the entity to which the identifier was issued
554          */
555         public void setPeerEntityId(String id) {
556             peerEntityId = id;
557         }
558 
559         /**
560          * Gets the name of the principal the identifier represents.
561          * 
562          * @return name of the principal the identifier represents
563          */
564         public String getPrincipalName() {
565             return principalName;
566         }
567 
568         /**
569          * Sets the name of the principal the identifier represents.
570          * 
571          * @param name name of the principal the identifier represents
572          */
573         public void setPrincipalName(String name) {
574             principalName = name;
575         }
576 
577         /**
578          * Gets the local ID component of the persistent identifier.
579          * 
580          * @return local ID component of the persistent identifier
581          */
582         public String getLocalId() {
583             return localId;
584         }
585 
586         /**
587          * Sets the local ID component of the persistent identifier.
588          * 
589          * @param id local ID component of the persistent identifier
590          */
591         public void setLocalId(String id) {
592             localId = id;
593         }
594 
595         /**
596          * Gets the persistent identifier.
597          * 
598          * @return the persistent identifier
599          */
600         public String getPersistentId() {
601             return persistentId;
602         }
603 
604         /**
605          * Set the persistent identifier.
606          * 
607          * @param id the persistent identifier
608          */
609         public void setPersistentId(String id) {
610             persistentId = id;
611         }
612 
613         /**
614          * Gets the ID, provided by the peer, associated with this ID.
615          * 
616          * @return ID, provided by the peer, associated with this ID
617          */
618         public String getPeerProvidedId() {
619             return peerProvidedId;
620         }
621 
622         /**
623          * Sets the ID, provided by the peer, associated with this ID.
624          * 
625          * @param id ID, provided by the peer, associated with this ID
626          */
627         public void setPeerProvidedId(String id) {
628             peerProvidedId = id;
629         }
630 
631         /**
632          * Gets the time the identifier was created.
633          * 
634          * @return time the identifier was created
635          */
636         public Timestamp getCreationTime() {
637             return creationTime;
638         }
639 
640         /**
641          * Sets the time the identifier was created.
642          * 
643          * @param time time the identifier was created
644          */
645         public void setCreationTime(Timestamp time) {
646             creationTime = time;
647         }
648 
649         /**
650          * Gets the time the identifier was deactivated.
651          * 
652          * @return time the identifier was deactivated
653          */
654         public Timestamp getDeactivationTime() {
655             return deactivationTime;
656         }
657 
658         /**
659          * Sets the time the identifier was deactivated.
660          * 
661          * @param time the time the identifier was deactivated
662          */
663         public void setDeactivationTime(Timestamp time) {
664             this.deactivationTime = time;
665         }
666         
667         /** {@inheritDoc} */
668         public String toString() {
669             StringBuilder stringForm = new StringBuilder("PersistentIdEntry{");
670             stringForm.append("persistentId:").append(persistentId).append(", ");
671             stringForm.append("localEntityId:").append(localEntityId).append(", ");
672             stringForm.append("peerEntityId:").append(peerEntityId).append(", ");
673             stringForm.append("localId:").append(localId).append(", ");
674             stringForm.append("principalName:").append(principalName).append(", ");
675             stringForm.append("peerProvidedId:").append(peerProvidedId).append(", ");
676             stringForm.append("creationTime:").append(creationTime).append(", ");
677             stringForm.append("deactivationTime:").append(deactivationTime).append(", ");
678             stringForm.append("}");
679             return stringForm.toString();
680         }
681     }
682 }