1
2
3
4
5
6
7 package gov.bnl.gums;
8
9 import java.sql.*;
10 import java.util.Date;
11 import java.util.*;
12 import org.apache.commons.logging.Log;
13 import org.apache.commons.logging.LogFactory;
14
15 /*** This class hold the whole MySQL database implementation by providing factory
16 * methods for the different database interfaces. It makes use of some query
17 * features defined in mysql 4.0.18, therefore it assumes that as a minimum
18 * version.
19 * <p>
20 * @todo query depends on version of MySQL: should check it.
21 *
22 * @author Gabriele Carcassi
23 */
24 public class MySQLPersistenceFactory implements PersistenceFactory {
25 private Log log = LogFactory.getLog(MySQLPersistenceFactory.class);
26
27 private String name;
28 private Properties properties;
29
30 private List connections = Collections.synchronizedList(new LinkedList());
31 private Connection retrieveConnection() {
32 Connection conn;
33 if (connections.size() == 0) {
34 conn = createConnectionFromProperties();
35 log.trace("Connection created. Connections in the pool: " + connections.size());
36 } else {
37 conn = (Connection) connections.remove(0);
38 log.trace("Connection reused. Connections in the pool: " + connections.size());
39 }
40 return conn;
41 }
42
43 private void releaseConnection(Connection conn) {
44 connections.add(0, conn);
45 log.trace("Connection added. Connections in the pool: " + connections.size());
46 }
47
48 private Properties readDbProperties() {
49 PropertyResourceBundle prop = (PropertyResourceBundle) ResourceBundle.getBundle("db");
50 Properties prop2 = new Properties();
51 Enumeration keys = prop.getKeys();
52 while (keys.hasMoreElements()) {
53 String key = (String) keys.nextElement();
54 prop2.setProperty(key, prop.getString(key));
55 }
56 return prop2;
57 }
58
59 public void setConnectionFromDbProperties() {
60 try {
61 setProperties(readDbProperties());
62 } catch (MissingResourceException e) {
63 throw new RuntimeException("Couldn't find database configuration file (etc/db.properties)", e);
64 }
65 }
66
67 private Connection createConnectionFromProperties() {
68 try {
69 Properties prop = getProperties();
70 try {
71 Class.forName(prop.getProperty("jdbcDriver"));
72 } catch (NullPointerException e) {
73 throw new RuntimeException("The database driver wasn't specified: jdbcDriver property is missing from the configuration.", e);
74 } catch (ClassNotFoundException e) {
75 throw new RuntimeException("The database driver wasn't found", e);
76 }
77 return DriverManager.getConnection(prop.getProperty("jdbcUrl"), prop);
78 } catch (SQLException e) {
79 String url = null;
80 if (getProperties() != null) {
81 url = getProperties().getProperty("jdbcUrl");
82 }
83 log.info("Exception while connecting to the database", e);
84 throw new RuntimeException("Couldn't connect to the database '" + url + "': check gums.config or the status of the database.", e);
85 }
86 }
87
88 public Connection getConnection() {
89 return retrieveConnection();
90 }
91
92 public void setConnection(Connection conn) {
93 releaseConnection(conn);
94 }
95
96 public UserGroupDB retrieveUserGroupDB(String groupName) {
97 return new GumsDbUserGroupDB(groupName);
98 }
99
100 public ManualUserGroupDB retrieveManualUserGroupDB(String groupName) {
101 return new GumsDbUserGroupDB(groupName);
102 }
103
104 public ManualAccountMapperDB retrieveManualAccountMapperDB(String name) {
105 return new GumsDBAccountMapperDB(name);
106 }
107
108 public AccountPoolMapperDB retrieveAccountPoolMapperDB(String name) {
109
110 return new MySQLAccountPoolMapperDB(name);
111 }
112
113 private Map statementCache = new Hashtable();
114 private PreparedStatement prepareStatement(Connection conn, String sql) throws SQLException {
115 Map statementsPerConnection = (Map) statementCache.get(conn);
116 if (statementsPerConnection == null) {
117 statementsPerConnection = new Hashtable();
118 statementCache.put(conn, statementsPerConnection);
119 }
120 PreparedStatement stmt = (PreparedStatement) statementsPerConnection.get(sql);
121 if (stmt == null) {
122 stmt = conn.prepareStatement(sql);
123 statementsPerConnection.put(sql, stmt);
124 log.trace("Created newstatement for: '" + sql + "'");
125 }
126 return stmt;
127 }
128
129 private void addUser(String userDN, String groupName) {
130 if (findUserID(userDN, groupName) != null) {
131 throw new RuntimeException("Couldn't add member '" + userDN + "' to group '" + groupName +"': already in group");
132 }
133 Connection conn = getConnection();
134 try {
135
136 PreparedStatement addMemberStmt = prepareStatement(conn, "INSERT INTO User SET userDN=?, userGroup=?, registrationDate=NOW()");
137
138 addMemberStmt.setString(1, userDN);
139 addMemberStmt.setString(2, groupName);
140
141 addMemberStmt.executeUpdate();
142
143 } catch (SQLException e) {
144 log.info("Exception while executing query", e);
145 throw new RuntimeException("Couldn't add a member to group " + groupName, e);
146 } finally {
147 releaseConnection(conn);
148 }
149 }
150
151 private void addUser(String userDN, String fqan, String groupName) {
152 if (findUserID(userDN, groupName, fqan) != null) {
153 throw new RuntimeException("Couldn't add member '" + userDN + "' to group '" + groupName +"': already in group");
154 }
155 Connection conn = getConnection();
156 try {
157
158 PreparedStatement addMemberFQANStmt = prepareStatement(conn, "INSERT INTO User SET userDN=?, userFQAN=?, userGroup=?, registrationDate=NOW()");
159
160 addMemberFQANStmt.setString(1, userDN);
161 addMemberFQANStmt.setString(2, fqan);
162 addMemberFQANStmt.setString(3, groupName);
163
164 addMemberFQANStmt.executeUpdate();
165 } catch (SQLException e) {
166 log.info("Exception while executing query", e);
167 throw new RuntimeException("Couldn't add a member to group " + groupName, e);
168 } finally {
169 releaseConnection(conn);
170 }
171 }
172
173 private Integer findUserID(String DN, String groupName) {
174 Connection conn = getConnection();
175 try {
176
177 PreparedStatement retrieveUserIDStmt = prepareStatement(conn, "SELECT * FROM User " +
178 "WHERE registrationDate <= NOW() AND (removalDate > NOW() OR " +
179 "isNull(removalDate)) AND userGroup=? AND userDN=? AND isNull(userFQAN);");
180
181 retrieveUserIDStmt.setString(1, groupName);
182 retrieveUserIDStmt.setString(2, DN);
183
184 ResultSet set = retrieveUserIDStmt.executeQuery();
185 if (!set.next())
186 return null;
187 return new Integer(set.getInt("userID"));
188 } catch (SQLException e) {
189 log.info("Exception while executing query", e);
190 throw new RuntimeException("Couldn't determine whether a user was in group " + groupName, e);
191 } finally {
192 releaseConnection(conn);
193 }
194 }
195
196 private Integer findUserID(String DN, String groupName, String fqan) {
197 Connection conn = getConnection();
198 try {
199
200 PreparedStatement retrieveUserIDFqanStmt = prepareStatement(conn, "SELECT * FROM User " +
201 "WHERE registrationDate <= NOW() AND (removalDate > NOW() OR " +
202 "isNull(removalDate)) AND userGroup=? AND userDN=? AND userFQAN =?;");
203
204 retrieveUserIDFqanStmt.setString(1, groupName);
205 retrieveUserIDFqanStmt.setString(2, DN);
206 retrieveUserIDFqanStmt.setString(3, fqan);
207
208 ResultSet set = retrieveUserIDFqanStmt.executeQuery();
209 if (!set.next())
210 return null;
211 return new Integer(set.getInt("userID"));
212 } catch (SQLException e) {
213 log.info("Exception while executing query", e);
214 throw new RuntimeException("Couldn't determine whether a user was in group " + groupName, e);
215 } finally {
216 releaseConnection(conn);
217 }
218 }
219
220 private class GumsDbUserGroupDB implements UserGroupDB, ManualUserGroupDB {
221
222 private String groupName;
223
224 private List newMembers;
225 private List removedMembers;
226
227 GumsDbUserGroupDB(String groupName) {
228 this.groupName = groupName;
229 }
230
231 public boolean isMemberInGroup(GridUser user) {
232 if (user.getVoFQAN() == null) {
233 return (findUserID(user.getCertificateDN(), groupName) != null);
234 } else {
235 return (findUserID(user.getCertificateDN(), groupName, user.getVoFQAN().getFqan()) != null);
236 }
237 }
238
239 public void loadUpdatedList(List members) {
240
241
242
243
244 List currentMembers = retrieveMembers();
245 newMembers = new ArrayList(members);
246 newMembers.removeAll(currentMembers);
247 removedMembers = new ArrayList(currentMembers);
248 removedMembers.removeAll(members);
249 Iterator iter = newMembers.iterator();
250 while (iter.hasNext()) {
251 GridUser user = (GridUser) iter.next();
252 addMember(user);
253 }
254 iter = removedMembers.iterator();
255 while (iter.hasNext()) {
256 GridUser userDN = (GridUser) iter.next();
257 removeMember(userDN);
258 }
259 }
260
261 public List retrieveMembers() {
262 Connection conn = getConnection();
263 try {
264 PreparedStatement retrieveMembersStmt = prepareStatement(conn, "SELECT userDN, userFQAN FROM User " +
265 "WHERE registrationDate <= NOW() AND (removalDate > NOW() OR " +
266 "isNull(removalDate)) AND userGroup = ?");
267
268 retrieveMembersStmt.setString(1, groupName);
269
270 ResultSet set = retrieveMembersStmt.executeQuery();
271 List members = new ArrayList();
272 while (set.next()) {
273 members.add(new GridUser(set.getString("userDN"), set.getString("userFQAN")));
274 }
275
276 return members;
277 } catch (SQLException e) {
278 log.info("Exception while executing query", e);
279 throw new RuntimeException("Couldn't retrieve the member list for group " + groupName, e);
280 } finally {
281 releaseConnection(conn);
282 }
283 }
284
285 public List retrieveNewMembers() {
286 return newMembers;
287 }
288
289 public List retrieveRemovedMembers() {
290 return removedMembers;
291 }
292
293 public void addMember(GridUser user) {
294 if (user.getVoFQAN() == null) {
295 addUser(user.getCertificateDN(), groupName);
296 } else {
297 addUser(user.getCertificateDN(), user.getVoFQAN().getFqan(), groupName);
298 }
299 }
300
301 public boolean removeMember(GridUser user) {
302 Connection conn = getConnection();
303 try {
304 PreparedStatement removeMemberStmt = prepareStatement(conn, "UPDATE User SET removalDate=NOW() " +
305 "WHERE userDN = ? AND userFQAN = ? AND userGroup = ? AND isNull(removalDate)");
306
307 PreparedStatement removeMemberFqanNullStmt = prepareStatement(conn, "UPDATE User SET removalDate=NOW() " +
308 "WHERE userDN = ? AND isNull(userFQAN) AND userGroup = ? AND isNull(removalDate)");
309
310 if (user.getVoFQAN() == null) {
311 removeMemberFqanNullStmt.setString(1, user.getCertificateDN());
312 removeMemberFqanNullStmt.setString(2, groupName);
313 return (removeMemberFqanNullStmt.executeUpdate() > 0);
314 } else {
315 removeMemberStmt.setString(1, user.getCertificateDN());
316 removeMemberStmt.setString(2, user.getVoFQAN().getFqan());
317 removeMemberStmt.setString(3, groupName);
318 return (removeMemberStmt.executeUpdate() > 0);
319 }
320
321 } catch (SQLException e) {
322 log.info("Exception while executing query", e);
323 throw new RuntimeException("Couldn't add a member to group " + groupName, e);
324 } finally {
325 releaseConnection(conn);
326 }
327 }
328
329 }
330
331 public static final Byte USER_ACCOUNT = new Byte((byte)0);
332
333
334
335 protected void mapAccountToUser(String userGroup, String user, String account) {
336 Connection conn = getConnection();
337 try {
338 PreparedStatement mapAccountToUserStmt = prepareStatement(conn, "INSERT INTO UserAccountMapping SET userGroup=?, userDN=?, account=?, startDate=NOW()");
339
340 mapAccountToUserStmt.setString(1, userGroup);
341 mapAccountToUserStmt.setString(2, user);
342 mapAccountToUserStmt.setString(3, account);
343
344 mapAccountToUserStmt.executeUpdate();
345 } catch (SQLException e) {
346 log.info("Exception while executing query", e);
347 throw new RuntimeException("Couldn't map user to account", e);
348 } finally {
349 releaseConnection(conn);
350 }
351 }
352
353 public boolean removeMappings(String user, String userGroup) {
354 Connection conn = getConnection();
355 try {
356 PreparedStatement removeMappingsStmt = prepareStatement(conn, "UPDATE UserAccountMapping " +
357 "SET endDate=NOW() " +
358 "WHERE userGroup = ? " +
359 "AND userDN = ?");
360
361 removeMappingsStmt.setString(1, userGroup);
362 removeMappingsStmt.setString(2, user);
363
364 return (removeMappingsStmt.executeUpdate() > 0);
365 } catch (SQLException e) {
366 log.info("Exception while executing query", e);
367 throw new RuntimeException("Couldn't remove the account mapping", e);
368 } finally {
369 releaseConnection(conn);
370 }
371 }
372
373 public String getName() {
374 return name;
375 }
376
377 public void setName(String name) {
378 this.name = name;
379 }
380
381 public Properties getProperties() {
382 return this.properties;
383 }
384
385 public void setProperties(Properties properties) {
386 this.properties = properties;
387 }
388
389 private class GumsDBAccountMapperDB implements ManualAccountMapperDB {
390
391 String userGroup;
392
393 GumsDBAccountMapperDB(String userGroup) {
394 this.userGroup = userGroup;
395 }
396
397 public void createMapping(String userDN, String account) {
398 mapAccountToUser(userGroup, userDN, account);
399 }
400
401 public boolean removeMapping(String userDN) {
402 return removeMappings(userDN, userGroup);
403 }
404
405 public String retrieveMapping(String userDN) {
406 Connection conn = getConnection();
407 try {
408 PreparedStatement retrieveMappingStmt = prepareStatement(conn, "SELECT Account " +
409 "FROM UserAccountMapping " +
410 "WHERE userGroup = ? AND userDN = ? AND " +
411 "startDate <= NOW() AND (endDate > NOW() OR " +
412 "isNull(endDate))");
413
414 retrieveMappingStmt.setString(1, userGroup);
415 retrieveMappingStmt.setString(2, userDN);
416
417 ResultSet set = retrieveMappingStmt.executeQuery();
418 if (!set.next()) return null;
419
420 return set.getString(1);
421 } catch (SQLException e) {
422 log.info("Exception while executing query", e);
423 throw new RuntimeException("Couldn't retrieve account mapping from the account group " + userGroup, e);
424 } finally {
425 releaseConnection(conn);
426 }
427
428 }
429
430 }
431
432 private class MySQLAccountPoolMapperDB implements AccountPoolMapperDB {
433
434 String userGroup;
435
436 MySQLAccountPoolMapperDB(String userGroup) {
437 this.userGroup = userGroup;
438 }
439
440 public void addAccount(String account) {
441 if (doesAccountExists(account)) {
442 throw new IllegalArgumentException("The account is already in the pool");
443 }
444 Connection conn = getConnection();
445 try {
446 PreparedStatement addAccountStmt = prepareStatement(conn, "INSERT INTO UserAccountMapping SET userGroup=?, userDN=NULL, account=?, startDate=NULL");
447
448 addAccountStmt.setString(1, userGroup);
449 addAccountStmt.setString(2, account);
450
451 addAccountStmt.executeUpdate();
452 } catch (SQLException e) {
453 log.info("Exception while executing query", e);
454 throw new RuntimeException("Couldn't add account to pool", e);
455 } finally {
456 releaseConnection(conn);
457 }
458 }
459
460 private boolean doesAccountExists(String account) {
461 Connection conn = getConnection();
462 try {
463 PreparedStatement doesAccountExistsStmt = prepareStatement(conn, "SELECT account " +
464 "FROM UserAccountMapping " +
465 "WHERE userGroup = ? AND account=?");
466
467 doesAccountExistsStmt.setString(1, userGroup);
468 doesAccountExistsStmt.setString(2, account);
469
470 ResultSet set = doesAccountExistsStmt.executeQuery();
471 if (!set.next()) return false;
472
473 return true;
474 } catch (SQLException e) {
475 log.info("Exception while executing query", e);
476 throw new RuntimeException("Couldn't determine if account is in pool " + userGroup, e);
477 } finally {
478 releaseConnection(conn);
479 }
480 }
481
482 public String assignAccount(String userDN) {
483 String account = retrieveFreeAccount();
484 if (account == null) return null;
485 Connection conn = getConnection();
486 try {
487 PreparedStatement assignAccountStmt = prepareStatement(conn, "UPDATE UserAccountMapping " +
488 "SET startDate=?, endDate=?, userDN = ? " +
489 "WHERE userGroup = ? " +
490 "AND account = ?");
491
492 assignAccountStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
493 assignAccountStmt.setTimestamp(2, new Timestamp(System.currentTimeMillis()));
494 assignAccountStmt.setString(3, userDN);
495 assignAccountStmt.setString(4, userGroup);
496 assignAccountStmt.setString(5, account);
497
498 assignAccountStmt.executeUpdate();
499 return account;
500 } catch (SQLException e) {
501 log.info("Exception while executing query", e);
502 throw new RuntimeException("Couldn't remove the account mapping", e);
503 } finally {
504 releaseConnection(conn);
505 }
506 }
507
508 private String retrieveFreeAccount() {
509 Connection conn = getConnection();
510 try {
511 PreparedStatement retrieveFreeAccountStmt = prepareStatement(conn, "SELECT account " +
512 "FROM UserAccountMapping " +
513 "WHERE userGroup = ? AND isNull(userDN) ORDER BY account LIMIT 1");
514
515 retrieveFreeAccountStmt.setString(1, userGroup);
516
517 ResultSet set = retrieveFreeAccountStmt.executeQuery();
518 if (!set.next()) return null;
519
520 return set.getString(1);
521 } catch (SQLException e) {
522 log.info("Exception while executing query", e);
523 throw new RuntimeException("Couldn't determine if account is in pool " + userGroup, e);
524 } finally {
525 releaseConnection(conn);
526 }
527 }
528
529 public String retrieveAccount(String userDN) {
530 Connection conn = getConnection();
531 try {
532 PreparedStatement retrieveAccountStmt = prepareStatement(conn, "SELECT account " +
533 "FROM UserAccountMapping " +
534 "WHERE userGroup = ? AND userDN = ?");
535
536 retrieveAccountStmt.setString(1, userGroup);
537 retrieveAccountStmt.setString(2, userDN);
538
539 ResultSet set = retrieveAccountStmt.executeQuery();
540 if (!set.next()) return null;
541
542 String account = set.getString(1);
543
544 PreparedStatement touchAccountStmt = prepareStatement(conn, "UPDATE UserAccountMapping " +
545 "SET endDate = ? " +
546 "WHERE userGroup = ? " +
547 "AND account = ?");
548
549 touchAccountStmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
550 touchAccountStmt.setString(2, userGroup);
551 touchAccountStmt.setString(3, account);
552
553 touchAccountStmt.executeUpdate();
554
555 return account;
556 } catch (SQLException e) {
557 log.info("Exception while executing query", e);
558 throw new RuntimeException("Couldn't determine if account is in pool " + userGroup, e);
559 } finally {
560 releaseConnection(conn);
561 }
562 }
563
564 public Map retrieveAccountMap() {
565 Connection conn = getConnection();
566 try {
567 PreparedStatement retrieveAccountMapStmt = prepareStatement(conn, "SELECT account, userDN " +
568 "FROM UserAccountMapping " +
569 "WHERE userGroup = ?");
570
571 retrieveAccountMapStmt.setString(1, userGroup);
572
573 ResultSet set = retrieveAccountMapStmt.executeQuery();
574 Map map = new Hashtable();
575 while (set.next()) {
576 if (set.getString(2) != null) {
577 map.put(set.getString(2), set.getString(1));
578 }
579 }
580
581 return map;
582 } catch (SQLException e) {
583 log.info("Exception while executing query", e);
584 throw new RuntimeException("Couldn't determine if account is in pool " + userGroup, e);
585 } finally {
586 releaseConnection(conn);
587 }
588 }
589
590 public List retrieveUsersNotUsedSince(Date date) {
591 Connection conn = getConnection();
592 try {
593 PreparedStatement retrieveUsersNotUsedSinceStmt = prepareStatement(conn, "SELECT userDN " +
594 "FROM UserAccountMapping " +
595 "WHERE userGroup = ? AND endDate < ?");
596
597 retrieveUsersNotUsedSinceStmt.setString(1, userGroup);
598 retrieveUsersNotUsedSinceStmt.setTimestamp(2, new Timestamp(date.getTime()));
599
600 ResultSet set = retrieveUsersNotUsedSinceStmt.executeQuery();
601 List list = new ArrayList();
602 while (set.next()) {
603 if (set.getString(1) != null) {
604 list.add(set.getString(1));
605 }
606 }
607
608 return list;
609 } catch (SQLException e) {
610 log.info("Exception while executing query", e);
611 throw new RuntimeException("Couldn't determine if account is in pool " + userGroup, e);
612 } finally {
613 releaseConnection(conn);
614 }
615 }
616
617 public void unassignUser(String user) {
618 Connection conn = getConnection();
619 try {
620 PreparedStatement unassignUserStmt = prepareStatement(conn, "UPDATE UserAccountMapping " +
621 "SET userDN = NULL " +
622 "WHERE userGroup = ? " +
623 "AND userDN = ?");
624
625 unassignUserStmt.setString(1, userGroup);
626 unassignUserStmt.setString(2, user);
627
628 unassignUserStmt.executeUpdate();
629 } catch (SQLException e) {
630 log.info("Exception while executing query", e);
631 throw new RuntimeException("Couldn't remove the account mapping", e);
632 } finally {
633 releaseConnection(conn);
634 }
635 }
636
637 }
638 }