1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20 package org.apache.james.userrepository;
21
22 import org.apache.avalon.cornerstone.services.datasources.DataSourceSelector;
23 import org.apache.avalon.excalibur.datasource.DataSourceComponent;
24 import org.apache.avalon.framework.CascadingRuntimeException;
25 import org.apache.avalon.framework.activity.Initializable;
26 import org.apache.avalon.framework.configuration.Configuration;
27 import org.apache.avalon.framework.configuration.ConfigurationException;
28 import org.apache.avalon.framework.service.ServiceException;
29 import org.apache.avalon.framework.service.ServiceManager;
30 import org.apache.avalon.framework.service.Serviceable;
31 import org.apache.james.api.user.User;
32 import org.apache.james.impl.jamesuser.AbstractUsersRepository;
33 import org.apache.james.services.FileSystem;
34 import org.apache.james.util.sql.JDBCUtil;
35 import org.apache.james.util.sql.SqlResources;
36
37 import java.io.InputStream;
38 import java.sql.Connection;
39 import java.sql.DatabaseMetaData;
40 import java.sql.PreparedStatement;
41 import java.sql.ResultSet;
42 import java.sql.SQLException;
43 import java.util.ArrayList;
44 import java.util.Collection;
45 import java.util.HashMap;
46 import java.util.Iterator;
47 import java.util.List;
48 import java.util.Locale;
49 import java.util.Map;
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73 public abstract class AbstractJdbcUsersRepository extends
74 AbstractUsersRepository implements Serviceable, Initializable {
75
76
77 protected Map m_sqlParameters;
78
79 private String m_sqlFileName;
80
81 private String m_datasourceName;
82
83 private DataSourceSelector m_datasources;
84
85 private DataSourceComponent m_datasource;
86
87
88 private String m_getUsersSql;
89
90
91 private String m_userByNameCaseInsensitiveSql;
92
93
94
95 private String m_insertUserSql;
96
97 private String m_updateUserSql;
98
99 private String m_deleteUserSql;
100
101
102 private JDBCUtil theJDBCUtil;
103
104 private FileSystem fileSystem;
105
106
107
108
109
110
111
112 public void removeUser(String userName) {
113 User user = getUserByName(userName);
114 if (user != null) {
115 doRemoveUser(user);
116 }
117 }
118
119
120
121
122
123
124
125
126
127
128
129
130 public User getUserByName(String name) {
131 return getUserByName(name, ignoreCase);
132 }
133
134
135
136
137
138
139
140
141
142
143
144
145 public User getUserByNameCaseInsensitive(String name) {
146 return getUserByName(name, true);
147 }
148
149
150
151
152
153
154
155
156
157
158 public String getRealName(String name) {
159
160 User user = getUserByName(name, ignoreCase);
161 if (user == null) {
162 return null;
163 } else {
164 return user.getUserName();
165 }
166 }
167
168
169
170
171
172
173 public boolean contains(String name) {
174 User user = getUserByName(name, ignoreCase);
175 return (user != null);
176 }
177
178
179
180
181
182
183
184 public boolean containsCaseInsensitive(String name) {
185 User user = getUserByName(name, true);
186 return (user != null);
187 }
188
189
190
191
192
193
194
195
196
197
198
199
200
201 public boolean test(String name, String password) {
202 User user = getUserByName(name, ignoreCase);
203 if (user == null) {
204 return false;
205 } else {
206 return user.verifyPassword(password);
207 }
208 }
209
210
211
212
213
214
215 public int countUsers() {
216 List usernames = listUserNames();
217 return usernames.size();
218 }
219
220
221
222
223
224
225
226 public Iterator list() {
227 return listUserNames().iterator();
228 }
229
230
231
232
233
234
235
236 void setDatasources(DataSourceSelector datasources) {
237 m_datasources = datasources;
238 }
239
240
241
242
243 public void service(final ServiceManager componentManager)
244 throws ServiceException {
245 StringBuffer logBuffer = null;
246 if (getLogger().isDebugEnabled()) {
247 logBuffer = new StringBuffer(64).append(this.getClass().getName())
248 .append(".compose()");
249 getLogger().debug(logBuffer.toString());
250 }
251
252 setDatasources((DataSourceSelector) componentManager
253 .lookup(DataSourceSelector.ROLE));
254 setFileSystem((FileSystem) componentManager.lookup(FileSystem.ROLE));
255 }
256
257
258
259
260
261
262
263 void setFileSystem(FileSystem system) {
264 this.fileSystem = system;
265 }
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287 public void configure(Configuration configuration)
288 throws ConfigurationException {
289 super.configure(configuration);
290 StringBuffer logBuffer = null;
291 if (getLogger().isDebugEnabled()) {
292 logBuffer = new StringBuffer(64).append(this.getClass().getName())
293 .append(".configure()");
294 getLogger().debug(logBuffer.toString());
295 }
296
297
298
299 String destUrl = configuration.getAttribute("destinationURL");
300
301 if (!destUrl.endsWith("/")) {
302 destUrl += "/";
303 }
304
305 List urlParams = new ArrayList();
306 int start = 5;
307 int end = destUrl.indexOf('/', start);
308 while (end > -1) {
309 urlParams.add(destUrl.substring(start, end));
310 start = end + 1;
311 end = destUrl.indexOf('/', start);
312 }
313
314
315 m_sqlParameters = new HashMap();
316 switch (urlParams.size()) {
317 case 3:
318 m_sqlParameters.put("key", urlParams.get(2));
319 case 2:
320 m_sqlParameters.put("table", urlParams.get(1));
321 case 1:
322 m_datasourceName = (String) urlParams.get(0);
323 break;
324 default:
325 throw new ConfigurationException(
326 "Malformed destinationURL - "
327 + "Must be of the format \"db://<data-source>[/<table>[/<key>]]\".");
328 }
329
330 if (getLogger().isDebugEnabled()) {
331 logBuffer = new StringBuffer(128).append("Parsed URL: table = '")
332 .append(m_sqlParameters.get("table")).append("', key = '")
333 .append(m_sqlParameters.get("key")).append("'");
334 getLogger().debug(logBuffer.toString());
335 }
336
337
338 m_sqlFileName = configuration.getChild("sqlFile", true).getValue();
339
340
341
342 Configuration sqlParamsConfig = configuration.getChild("sqlParameters");
343 String[] paramNames = sqlParamsConfig.getAttributeNames();
344 for (int i = 0; i < paramNames.length; i++) {
345 String paramName = paramNames[i];
346 String paramValue = sqlParamsConfig.getAttribute(paramName);
347 m_sqlParameters.put(paramName, paramValue);
348 }
349 }
350
351
352
353
354
355
356
357
358
359
360
361
362
363 public void initialize() throws Exception {
364 StringBuffer logBuffer = null;
365 if (getLogger().isDebugEnabled()) {
366 logBuffer = new StringBuffer(128).append(this.getClass().getName())
367 .append(".initialize()");
368 getLogger().debug(logBuffer.toString());
369 }
370
371 theJDBCUtil = new JDBCUtil() {
372 protected void delegatedLog(String logString) {
373 AbstractJdbcUsersRepository.this.getLogger().warn(
374 "AbstractJdbcUsersRepository: " + logString);
375 }
376 };
377
378
379 m_datasource = (DataSourceComponent) m_datasources
380 .select(m_datasourceName);
381
382
383 Connection conn = openConnection();
384 try {
385 DatabaseMetaData dbMetaData = conn.getMetaData();
386
387 InputStream sqlFile = null;
388
389 try {
390 sqlFile = fileSystem.getResource(m_sqlFileName);
391 } catch (Exception e) {
392 getLogger().fatalError(e.getMessage(), e);
393 throw e;
394 }
395
396 if (getLogger().isDebugEnabled()) {
397 logBuffer = new StringBuffer(256).append(
398 "Reading SQL resources from: ").append(
399 m_sqlFileName).append(", section ").append(
400 this.getClass().getName()).append(".");
401 getLogger().debug(logBuffer.toString());
402 }
403
404 SqlResources sqlStatements = new SqlResources();
405 sqlStatements.init(sqlFile, this.getClass().getName(), conn,
406 m_sqlParameters);
407
408
409
410 m_getUsersSql = sqlStatements.getSqlString("select", true);
411
412
413
414 m_userByNameCaseInsensitiveSql = sqlStatements
415 .getSqlString("selectByLowercaseName");
416
417
418
419
420 m_insertUserSql = sqlStatements.getSqlString("insert", true);
421 m_updateUserSql = sqlStatements.getSqlString("update", true);
422 m_deleteUserSql = sqlStatements.getSqlString("delete", true);
423
424
425 String createUserTableSql = sqlStatements.getSqlString(
426 "createTable", true);
427
428
429
430 String tableName = sqlStatements.getSqlString("tableName", true);
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445 if (!theJDBCUtil.tableExists(dbMetaData, tableName)) {
446
447 PreparedStatement createStatement = null;
448 try {
449 createStatement = conn.prepareStatement(createUserTableSql);
450 createStatement.execute();
451 } finally {
452 theJDBCUtil.closeJDBCStatement(createStatement);
453 }
454
455 logBuffer = new StringBuffer(128).append(
456 this.getClass().getName()).append(": Created table \'")
457 .append(tableName).append("\'.");
458 getLogger().info(logBuffer.toString());
459 } else {
460 if (getLogger().isDebugEnabled()) {
461 getLogger().debug("Using table: " + tableName);
462 }
463 }
464
465 } finally {
466 theJDBCUtil.closeJDBCConnection(conn);
467 }
468 }
469
470
471
472
473
474
475
476 protected List listUserNames() {
477 Collection users = getAllUsers();
478 List userNames = new ArrayList(users.size());
479 for (Iterator it = users.iterator(); it.hasNext();) {
480 userNames.add(((User) it.next()).getUserName());
481 }
482 users.clear();
483 return userNames;
484 }
485
486
487
488
489
490
491 protected Iterator listAllUsers() {
492 return getAllUsers().iterator();
493 }
494
495
496
497
498
499
500 private Collection getAllUsers() {
501 List userList = new ArrayList();
502
503 Connection conn = openConnection();
504 PreparedStatement getUsersStatement = null;
505 ResultSet rsUsers = null;
506 try {
507
508 getUsersStatement = conn.prepareStatement(m_getUsersSql);
509 rsUsers = getUsersStatement.executeQuery();
510
511
512 while (rsUsers.next()) {
513 User user = readUserFromResultSet(rsUsers);
514 userList.add(user);
515 }
516 } catch (SQLException sqlExc) {
517 sqlExc.printStackTrace();
518 throw new CascadingRuntimeException("Error accessing database",
519 sqlExc);
520 } finally {
521 theJDBCUtil.closeJDBCResultSet(rsUsers);
522 theJDBCUtil.closeJDBCStatement(getUsersStatement);
523 theJDBCUtil.closeJDBCConnection(conn);
524 }
525
526 return userList;
527 }
528
529
530
531
532
533
534
535
536 protected void doAddUser(User user) {
537 Connection conn = openConnection();
538 PreparedStatement addUserStatement = null;
539
540
541 try {
542
543 addUserStatement = conn.prepareStatement(m_insertUserSql);
544
545 setUserForInsertStatement(user, addUserStatement);
546
547 addUserStatement.execute();
548 } catch (SQLException sqlExc) {
549 sqlExc.printStackTrace();
550 throw new CascadingRuntimeException("Error accessing database",
551 sqlExc);
552 } finally {
553 theJDBCUtil.closeJDBCStatement(addUserStatement);
554 theJDBCUtil.closeJDBCConnection(conn);
555 }
556 }
557
558
559
560
561
562
563
564
565 protected void doRemoveUser(User user) {
566 String username = user.getUserName();
567
568 Connection conn = openConnection();
569 PreparedStatement removeUserStatement = null;
570
571
572 try {
573 removeUserStatement = conn.prepareStatement(m_deleteUserSql);
574 removeUserStatement.setString(1, username);
575 removeUserStatement.execute();
576 } catch (SQLException sqlExc) {
577 sqlExc.printStackTrace();
578 throw new CascadingRuntimeException("Error accessing database",
579 sqlExc);
580 } finally {
581 theJDBCUtil.closeJDBCStatement(removeUserStatement);
582 theJDBCUtil.closeJDBCConnection(conn);
583 }
584 }
585
586
587
588
589
590
591
592 protected void doUpdateUser(User user) {
593 Connection conn = openConnection();
594 PreparedStatement updateUserStatement = null;
595
596
597 try {
598 updateUserStatement = conn.prepareStatement(m_updateUserSql);
599 setUserForUpdateStatement(user, updateUserStatement);
600 updateUserStatement.execute();
601 } catch (SQLException sqlExc) {
602 sqlExc.printStackTrace();
603 throw new CascadingRuntimeException("Error accessing database",
604 sqlExc);
605 } finally {
606 theJDBCUtil.closeJDBCStatement(updateUserStatement);
607 theJDBCUtil.closeJDBCConnection(conn);
608 }
609 }
610
611
612
613
614
615
616
617
618
619
620
621
622
623 protected User getUserByNameIterating(String name, boolean ignoreCase) {
624
625 Iterator users = listAllUsers();
626 while (users.hasNext()) {
627 User user = (User) users.next();
628 String username = user.getUserName();
629 if ((!ignoreCase && username.equals(name))
630 || (ignoreCase && username.equalsIgnoreCase(name))) {
631 return user;
632 }
633 }
634
635 return null;
636 }
637
638
639
640
641
642
643
644
645
646
647
648
649
650 protected User getUserByName(String name, boolean ignoreCase) {
651
652
653 if (m_userByNameCaseInsensitiveSql == null) {
654 return getUserByNameIterating(name, ignoreCase);
655 }
656
657
658
659 Connection conn = openConnection();
660 PreparedStatement getUsersStatement = null;
661 ResultSet rsUsers = null;
662 try {
663
664 String sql = m_userByNameCaseInsensitiveSql;
665 getUsersStatement = conn.prepareStatement(sql);
666
667 getUsersStatement.setString(1, name.toLowerCase(Locale.US));
668
669 rsUsers = getUsersStatement.executeQuery();
670
671
672
673 User user = null;
674 while (rsUsers.next()) {
675 User rowUser = readUserFromResultSet(rsUsers);
676 String actualName = rowUser.getUserName();
677
678
679 if (ignoreCase || actualName.equals(name)) {
680 user = rowUser;
681 break;
682 }
683 }
684 return user;
685 } catch (SQLException sqlExc) {
686 sqlExc.printStackTrace();
687 throw new CascadingRuntimeException("Error accessing database",
688 sqlExc);
689 } finally {
690 theJDBCUtil.closeJDBCResultSet(rsUsers);
691 theJDBCUtil.closeJDBCStatement(getUsersStatement);
692 theJDBCUtil.closeJDBCConnection(conn);
693 }
694 }
695
696
697
698
699
700
701
702
703
704
705
706
707
708 protected abstract User readUserFromResultSet(ResultSet rsUsers)
709 throws SQLException;
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725 protected abstract void setUserForInsertStatement(User user,
726 PreparedStatement userInsert) throws SQLException;
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742 protected abstract void setUserForUpdateStatement(User user,
743 PreparedStatement userUpdate) throws SQLException;
744
745
746
747
748
749
750
751 private Connection openConnection() {
752 try {
753 return m_datasource.getConnection();
754 } catch (SQLException sqle) {
755 throw new CascadingRuntimeException(
756 "An exception occurred getting a database connection.",
757 sqle);
758 }
759 }
760 }