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