1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23 package org.apache.james.vut;
24
25 import java.io.InputStream;
26 import java.sql.Connection;
27 import java.sql.DatabaseMetaData;
28 import java.sql.PreparedStatement;
29 import java.sql.ResultSet;
30 import java.sql.SQLException;
31 import java.util.ArrayList;
32 import java.util.Collection;
33 import java.util.HashMap;
34 import java.util.List;
35 import java.util.Map;
36
37 import org.apache.avalon.cornerstone.services.datasources.DataSourceSelector;
38 import org.apache.avalon.excalibur.datasource.DataSourceComponent;
39 import org.apache.avalon.framework.activity.Initializable;
40 import org.apache.avalon.framework.configuration.Configurable;
41 import org.apache.avalon.framework.configuration.Configuration;
42 import org.apache.avalon.framework.configuration.ConfigurationException;
43 import org.apache.avalon.framework.service.ServiceException;
44 import org.apache.avalon.framework.service.ServiceManager;
45 import org.apache.avalon.framework.service.Serviceable;
46 import org.apache.james.api.vut.management.InvalidMappingException;
47 import org.apache.james.impl.vut.AbstractVirtualUserTable;
48 import org.apache.james.impl.vut.VirtualUserTableUtil;
49 import org.apache.james.services.FileSystem;
50 import org.apache.james.util.sql.JDBCUtil;
51 import org.apache.james.util.sql.SqlResources;
52
53
54
55
56 public class JDBCVirtualUserTable extends AbstractVirtualUserTable implements Configurable,Serviceable, Initializable{
57
58 private DataSourceSelector datasources = null;
59 private DataSourceComponent dataSourceComponent = null;
60 private String tableName = "VirtualUserTable";
61 private String dataSourceName = null;
62
63 private static String WILDCARD = "%";
64
65
66
67
68
69 protected SqlResources sqlQueries;
70
71
72
73
74 private String sqlFileName;
75
76 private FileSystem fileSystem;
77
78 protected String datasourceName;
79
80
81
82
83
84 public void service(ServiceManager arg0) throws ServiceException {
85 super.service(arg0);
86 datasources = (DataSourceSelector)arg0.lookup(DataSourceSelector.ROLE);
87 setFileSystem((FileSystem) arg0.lookup(FileSystem.ROLE));
88 }
89
90
91
92
93 public void configure(Configuration arg0) throws ConfigurationException {
94 super.configure(arg0);
95 String destination = arg0.getAttribute("destinationURL",null);
96
97 if (destination == null) {
98 throw new ConfigurationException("destinationURL must configured");
99 }
100
101
102 if ( ! destination.endsWith("/") ) {
103 destination += "/";
104 }
105
106
107
108 List urlParams = new ArrayList();
109 int start = 5;
110
111 int end = destination.indexOf('/', start);
112 while ( end > -1 ) {
113 urlParams.add(destination.substring(start, end));
114 start = end + 1;
115 end = destination.indexOf('/', start);
116 }
117
118
119 if (urlParams.size() == 0) {
120 StringBuffer exceptionBuffer =
121 new StringBuffer(256)
122 .append("Malformed destinationURL - Must be of the format '")
123 .append("db://<data-source>'. Was passed ")
124 .append(arg0.getAttribute("repositoryPath"));
125 throw new ConfigurationException(exceptionBuffer.toString());
126 }
127 if (urlParams.size() >= 1) {
128 dataSourceName = (String)urlParams.get(0);
129 }
130 if (urlParams.size() >= 2) {
131 tableName = (String)urlParams.get(1);
132 }
133
134
135 if (getLogger().isDebugEnabled()) {
136 StringBuffer logBuffer =
137 new StringBuffer(128)
138 .append("Parsed URL: table = '")
139 .append(tableName)
140 .append("'");
141 getLogger().debug(logBuffer.toString());
142 }
143
144 sqlFileName = arg0.getChild("sqlFile").getValue();
145
146 Configuration autoConf = arg0.getChild("autodetect");
147 if (autoConf != null) {
148 setAutoDetect(autoConf.getValueAsBoolean(true));
149 }
150
151 Configuration autoIPConf = arg0.getChild("autodetectIP");
152 if (autoConf != null) {
153 setAutoDetectIP(autoIPConf.getValueAsBoolean(true));
154 }
155 }
156
157
158
159
160 public void initialize() throws Exception {
161
162 setDataSourceComponent((DataSourceComponent) datasources.select(dataSourceName));
163
164 StringBuffer logBuffer = null;
165 if (getLogger().isDebugEnabled()) {
166 getLogger().debug(this.getClass().getName() + ".initialize()");
167 }
168
169
170 Connection conn = dataSourceComponent.getConnection();
171 PreparedStatement createStatement = null;
172
173 try {
174
175
176 InputStream sqlFile = null;
177 try {
178 sqlFile = fileSystem.getResource(sqlFileName);
179 } catch (Exception e) {
180 getLogger().fatalError(e.getMessage(), e);
181 throw e;
182 }
183
184 if (getLogger().isDebugEnabled()) {
185 logBuffer =
186 new StringBuffer(128)
187 .append("Reading SQL resources from file: ")
188 .append(sqlFileName)
189 .append(", section ")
190 .append(this.getClass().getName())
191 .append(".");
192 getLogger().debug(logBuffer.toString());
193 }
194
195
196 Map sqlParameters = new HashMap();
197 if (tableName != null) {
198 sqlParameters.put("table", tableName);
199 }
200
201 sqlQueries = new SqlResources();
202 sqlQueries.init(sqlFile, this.getClass().getName(),
203 conn, sqlParameters);
204
205
206 DatabaseMetaData dbMetaData = conn.getMetaData();
207
208
209
210 if (!(theJDBCUtil.tableExists(dbMetaData, tableName))) {
211
212
213 createStatement =
214 conn.prepareStatement(sqlQueries.getSqlString("createTable", true));
215 createStatement.execute();
216
217 if (getLogger().isInfoEnabled()) {
218 logBuffer =
219 new StringBuffer(64)
220 .append("JdbcVirtalUserTable: Created table '")
221 .append(tableName)
222 .append("'.");
223 getLogger().info(logBuffer.toString());
224 }
225 }
226
227
228 } finally {
229 theJDBCUtil.closeJDBCStatement(createStatement);
230 theJDBCUtil.closeJDBCConnection(conn);
231 }
232 }
233
234
235
236
237 private final JDBCUtil theJDBCUtil = new JDBCUtil() {
238 protected void delegatedLog(String logString) {
239 getLogger().debug("JDBCVirtualUserTable: " + logString);
240 }
241 };
242
243
244 public void setDataSourceComponent(DataSourceComponent dataSourceComponent) {
245 this.dataSourceComponent = dataSourceComponent;
246 }
247
248
249 public void setFileSystem(FileSystem fileSystem) {
250 this.fileSystem = fileSystem;
251 }
252
253
254
255
256 public String mapAddressInternal(String user, String domain) {
257 Connection conn = null;
258 PreparedStatement mappingStmt = null;
259 try {
260 conn = dataSourceComponent.getConnection();
261 mappingStmt = conn.prepareStatement(sqlQueries.getSqlString("selectMappings", true));
262
263 ResultSet mappingRS = null;
264 try {
265 mappingStmt.setString(1, user);
266 mappingStmt.setString(2, domain);
267 mappingStmt.setString(3, domain);
268 mappingRS = mappingStmt.executeQuery();
269 if (mappingRS.next()) {
270 return mappingRS.getString(1);
271 }
272 } finally {
273 theJDBCUtil.closeJDBCResultSet(mappingRS);
274 }
275
276 } catch (SQLException sqle) {
277 getLogger().error("Error accessing database", sqle);
278 } finally {
279 theJDBCUtil.closeJDBCStatement(mappingStmt);
280 theJDBCUtil.closeJDBCConnection(conn);
281 }
282 return null;
283 }
284
285
286
287
288 public boolean removeMappingInternal(String user, String domain, String mapping) throws InvalidMappingException {
289 String newUser = getUserString(user);
290 String newDomain = getDomainString(domain);
291 Collection map = getUserDomainMappings(newUser,newDomain);
292
293 if (map != null && map.size() > 1) {
294 map.remove(mapping);
295 return updateMapping(newUser,newDomain,VirtualUserTableUtil.CollectionToMapping(map));
296 } else {
297 return removeRawMapping(newUser,newDomain,mapping);
298 }
299 }
300
301
302
303
304
305 public boolean addMappingInternal(String user, String domain, String regex) throws InvalidMappingException {
306 String newUser = getUserString(user);
307 String newDomain = getDomainString(domain);
308 Collection map = getUserDomainMappings(newUser,newDomain);
309
310 if (map != null && map.size() != 0) {
311 map.add(regex);
312
313 return updateMapping(newUser,newDomain,VirtualUserTableUtil.CollectionToMapping(map));
314 }
315 return addRawMapping(newUser,newDomain,regex);
316 }
317
318
319
320
321
322
323
324
325
326 private boolean updateMapping(String user, String domain, String mapping) {
327 Connection conn = null;
328 PreparedStatement mappingStmt = null;
329
330 try {
331 conn = dataSourceComponent.getConnection();
332 mappingStmt = conn.prepareStatement(sqlQueries.getSqlString(
333 "updateMapping", true));
334
335 ResultSet mappingRS = null;
336 try {
337 mappingStmt.setString(1, mapping);
338 mappingStmt.setString(2, user);
339 mappingStmt.setString(3, domain);
340
341 if (mappingStmt.executeUpdate()> 0) {
342 return true;
343 }
344 } finally {
345 theJDBCUtil.closeJDBCResultSet(mappingRS);
346 }
347
348 } catch (SQLException sqle) {
349 getLogger().error("Error accessing database", sqle);
350 } finally {
351 theJDBCUtil.closeJDBCStatement(mappingStmt);
352 theJDBCUtil.closeJDBCConnection(conn);
353 }
354 return false;
355 }
356
357
358
359
360
361
362
363
364
365
366 private boolean removeRawMapping(String user, String domain, String mapping) {
367 Connection conn = null;
368 PreparedStatement mappingStmt = null;
369
370 try {
371 conn = dataSourceComponent.getConnection();
372 mappingStmt = conn.prepareStatement(sqlQueries.getSqlString(
373 "deleteMapping", true));
374
375 ResultSet mappingRS = null;
376 try {
377 mappingStmt.setString(1, user);
378 mappingStmt.setString(2, domain);
379 mappingStmt.setString(3, mapping);
380 if(mappingStmt.executeUpdate() > 0) {
381 return true;
382 }
383 } finally {
384 theJDBCUtil.closeJDBCResultSet(mappingRS);
385 }
386
387 } catch (SQLException sqle) {
388 getLogger().error("Error accessing database", sqle);
389 } finally {
390 theJDBCUtil.closeJDBCStatement(mappingStmt);
391 theJDBCUtil.closeJDBCConnection(conn);
392 }
393 return false;
394 }
395
396
397
398
399
400
401
402
403
404 private boolean addRawMapping(String user, String domain, String mapping) {
405 Connection conn = null;
406 PreparedStatement mappingStmt = null;
407
408 try {
409 conn = dataSourceComponent.getConnection();
410 mappingStmt = conn.prepareStatement(sqlQueries.getSqlString(
411 "addMapping", true));
412
413 ResultSet mappingRS = null;
414 try {
415 mappingStmt.setString(1, user);
416 mappingStmt.setString(2, domain);
417 mappingStmt.setString(3, mapping);
418
419 if(mappingStmt.executeUpdate() >0) {
420 return true;
421 }
422 } finally {
423 theJDBCUtil.closeJDBCResultSet(mappingRS);
424 }
425
426 } catch (SQLException sqle) {
427 getLogger().error("Error accessing database", sqle);
428 } finally {
429 theJDBCUtil.closeJDBCStatement(mappingStmt);
430 theJDBCUtil.closeJDBCConnection(conn);
431 }
432 return false;
433 }
434
435
436
437
438
439
440
441
442
443 private String getUserString(String user) throws InvalidMappingException {
444 if (user != null) {
445 if(user.equals(WILDCARD) || user.indexOf("@") < 0) {
446 return user;
447 } else {
448 throw new InvalidMappingException("Invalid user: " + user);
449 }
450 } else {
451 return WILDCARD;
452 }
453 }
454
455
456
457
458
459
460
461
462 private String getDomainString(String domain) throws InvalidMappingException {
463 if(domain != null) {
464 if (domain.equals(WILDCARD) || domain.indexOf("@") < 0) {
465 return domain;
466 } else {
467 throw new InvalidMappingException("Invalid domain: " + domain);
468 }
469 } else {
470 return WILDCARD;
471 }
472 }
473
474
475
476
477 protected Collection getUserDomainMappingsInternal(String user, String domain) {
478 Connection conn = null;
479 PreparedStatement mappingStmt = null;
480
481 try {
482 conn = dataSourceComponent.getConnection();
483 mappingStmt = conn.prepareStatement(sqlQueries.getSqlString("selectUserDomainMapping", true));
484
485 ResultSet mappingRS = null;
486 try {
487 mappingStmt.setString(1, user);
488 mappingStmt.setString(2, domain);
489 mappingRS = mappingStmt.executeQuery();
490 if (mappingRS.next()) {
491 return VirtualUserTableUtil.mappingToCollection(mappingRS.getString(1));
492 }
493 } finally {
494 theJDBCUtil.closeJDBCResultSet(mappingRS);
495 }
496
497 } catch (SQLException sqle) {
498 getLogger().error("Error accessing database", sqle);
499 } finally {
500 theJDBCUtil.closeJDBCStatement(mappingStmt);
501 theJDBCUtil.closeJDBCConnection(conn);
502 }
503 return null;
504 }
505
506
507
508
509 protected List getDomainsInternal() {
510 List domains = new ArrayList();
511 Connection conn = null;
512 PreparedStatement mappingStmt = null;
513
514 try {
515 conn = dataSourceComponent.getConnection();
516 mappingStmt = conn.prepareStatement(sqlQueries.getSqlString("selectDomains", true));
517
518 ResultSet mappingRS = null;
519 try {
520 mappingRS = mappingStmt.executeQuery();
521 while (mappingRS.next()) {
522 String domain = mappingRS.getString(1).toLowerCase();
523 if(domains.equals(WILDCARD) == false && domains.contains(domains) == false) {
524 domains.add(domain);
525 }
526 }
527 } finally {
528 theJDBCUtil.closeJDBCResultSet(mappingRS);
529 }
530
531 } catch (SQLException sqle) {
532 getLogger().error("Error accessing database", sqle);
533 } finally {
534 theJDBCUtil.closeJDBCStatement(mappingStmt);
535 theJDBCUtil.closeJDBCConnection(conn);
536 }
537 if (domains.size() == 0) {
538 return null;
539 } else {
540 return domains;
541 }
542 }
543
544
545
546
547 public boolean containsDomain(String domain) {
548 Connection conn = null;
549 PreparedStatement mappingStmt = null;
550
551 try {
552 conn = dataSourceComponent.getConnection();
553 mappingStmt = conn.prepareStatement(sqlQueries.getSqlString("selectDomain", true));
554
555 ResultSet mappingRS = null;
556 try {
557 mappingStmt.setString(1, domain);
558 mappingRS = mappingStmt.executeQuery();
559 if (mappingRS.next()) {
560 return true;
561 }
562 } finally {
563 theJDBCUtil.closeJDBCResultSet(mappingRS);
564 }
565
566 } catch (SQLException sqle) {
567 getLogger().error("Error accessing database", sqle);
568 } finally {
569 theJDBCUtil.closeJDBCStatement(mappingStmt);
570 theJDBCUtil.closeJDBCConnection(conn);
571 }
572 return false;
573 }
574
575
576
577
578 public Map getAllMappingsInternal() {
579 Connection conn = null;
580 PreparedStatement mappingStmt = null;
581 HashMap mapping = new HashMap();
582
583 try {
584 conn = dataSourceComponent.getConnection();
585 mappingStmt = conn.prepareStatement(sqlQueries.getSqlString("selectAllMappings", true));
586
587 ResultSet mappingRS = null;
588 try {
589 mappingRS = mappingStmt.executeQuery();
590 while(mappingRS.next()) {
591 String user = mappingRS.getString(1);
592 String domain = mappingRS.getString(2);
593 String map = mappingRS.getString(3);
594
595 mapping.put(user + "@" + domain,VirtualUserTableUtil.mappingToCollection(map));
596 }
597
598 if (mapping.size() > 0 ) return mapping;
599 } finally {
600 theJDBCUtil.closeJDBCResultSet(mappingRS);
601 }
602
603 } catch (SQLException sqle) {
604 getLogger().error("Error accessing database", sqle);
605 } finally {
606 theJDBCUtil.closeJDBCStatement(mappingStmt);
607 theJDBCUtil.closeJDBCConnection(conn);
608 }
609 return null;
610 }
611 }
612