| <?xml version="1.0" encoding="UTF-8"?>
|
| <!--
|
| Licensed to the Apache Software Foundation (ASF) under one
|
| or more contributor license agreements. See the NOTICE file
|
| distributed with this work for additional information
|
| regarding copyright ownership. The ASF licenses this file
|
| to you under the Apache License, Version 2.0 (the
|
| "License"); you may not use this file except in compliance
|
| with the License. You may obtain a copy of the License at
|
|
|
| http://www.apache.org/licenses/LICENSE-2.0
|
|
|
| Unless required by applicable law or agreed to in writing,
|
| software distributed under the License is distributed on an
|
| "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
|
| KIND, either express or implied. See the License for the
|
| specific language governing permissions and limitations
|
| under the License.
|
| -->
|
| <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
|
| <!-- Generated by Apache Maven Doxia at Apr 12, 2012 -->
|
| <html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
|
| <head>
|
| <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
|
| <title>James Server - |
| Using JDBC</title>
|
| <style type="text/css" media="all">
|
| @import url("../css/james.css");
|
| @import url("../css/maven-base.css");
|
| @import url("../css/maven-theme.css");
|
| @import url("../css/site.css");
|
| @import url("../js/jquery/css/custom-theme/jquery-ui-1.8.5.custom.css");
|
| @import url("../js/jquery/css/print.css");
|
| @import url("../js/fancybox/jquery.fancybox-1.3.4.css");
|
| </style>
|
| <script type="text/javascript" src="../js/jquery/js/jquery-1.4.2.min.js"></script>
|
| <script type="text/javascript" src="../js/jquery/js/jquery-ui-1.8.5.custom.min.js"></script>
|
| <script type="text/javascript" src="../js/fancybox/jquery.fancybox-1.3.4.js"></script>
|
| <link rel="stylesheet" href="../css/print.css" type="text/css" media="print" />
|
| <meta name="author" content="Charles Benett" />
|
| <meta name="Date-Revision-yyyymmdd" content="20120412" />
|
| <meta http-equiv="Content-Language" content="en" />
|
|
|
| <!-- Google Analytics -->
|
| <script type="text/javascript">
|
|
|
| var _gaq = _gaq || [];
|
| _gaq.push(['_setAccount', 'UA-1384591-1']);
|
| _gaq.push(['_trackPageview']);
|
|
|
| (function() {
|
| var ga = document.createElement('script'); ga.type = 'text/javascript'; ga.async = true;
|
| ga.src = ('https:' == document.location.protocol ? 'https://ssl' : 'http://www') + '.google-analytics.com/ga.js';
|
| var s = document.getElementsByTagName('script').item(0); s.parentNode.insertBefore(ga, s);
|
| })();
|
|
|
| </script>
|
| </head>
|
| <body class="composite">
|
| <div id="banner">
|
| <a href="../index.html" id="bannerLeft" title="james-server-logo.gif">
|
|
|
|
|
| <img src="../images/logos/james-server-logo.gif" alt="James Server" />
|
| </a>
|
| <a href="http://www.apache.org/" id="bannerRight">
|
|
|
|
|
| <img src="images/logos/asf-logo-reduced.gif" alt="The Apache Software Foundation" />
|
| </a>
|
| <div class="clear">
|
| <hr/>
|
| </div>
|
| </div>
|
| <div id="breadcrumbs">
|
|
|
|
|
| <div class="xleft">
|
| <span id="publishDate">Last Published: 2012-04-12</span>
|
| | <span id="projectVersion">Version: 1.6</span>
|
| </div>
|
| <div class="xright"> <a href="../../index.html" title="Home">Home</a>
|
| |
|
| <a href="../index.html" title="Server">Server</a>
|
| |
|
| <a href="../../hupa/index.html" title="Hupa">Hupa</a>
|
| |
|
| <a href="../../protocols/index.html" title="Protocols">Protocols</a>
|
| |
|
| <a href="../../imap/index.html" title="IMAP">IMAP</a>
|
| |
|
| <a href="../../mailet/index.html" title="Mailets">Mailets</a>
|
| |
|
| <a href="../../mailbox/index.html" title="Mailbox">Mailbox</a>
|
| |
|
| <a href="../../mime4j/index.html" title="Mime4J">Mime4J</a>
|
| |
|
| <a href="../../jsieve/index.html" title="jSieve">jSieve</a>
|
| |
|
| <a href="../../jspf/index.html" title="jSPF">jSPF</a>
|
| |
|
| <a href="../../jdkim/index.html" title="jDKIM">jDKIM</a>
|
| |
|
| <a href="../../mpt/index.html" title="MPT">MPT</a>
|
| |
|
| <a href="../../postage/index.html" title="Postage">Postage</a>
|
|
|
|
|
| </div>
|
| <div class="clear">
|
| <hr/>
|
| </div>
|
| </div>
|
| <div id="leftColumn">
|
| <div id="navcolumn">
|
|
|
|
|
| <h5>James Server</h5>
|
| <ul>
|
| <li class="none">
|
| <a href="../index.html" title="Overview">Overview</a>
|
| </li>
|
| <li class="none">
|
| <a href="../design_objectives.html" title="Objectives">Objectives</a>
|
| </li>
|
| <li class="none">
|
| <a href="../FAQ.html" title="Server FAQ">Server FAQ</a>
|
| </li>
|
| <li class="none">
|
| <a href="../rfclist.html" title="Useful RFCs">Useful RFCs</a>
|
| </li>
|
| <li class="none">
|
| <a href="http://issues.apache.org/jira/browse/JAMES" title="Issue Tracker">Issue Tracker</a>
|
| </li>
|
| <li class="none">
|
| <a href="http://svn.apache.org/viewvc/james/server/" title="Source">Source</a>
|
| </li>
|
| </ul>
|
| <h5>Documentation</h5>
|
| <ul>
|
| <li class="none">
|
| <a href="../3/index.html" title="Server v3">Server v3</a>
|
| </li>
|
| <li class="none">
|
| <a href="../2/index.html" title="Server v2">Server v2</a>
|
| </li>
|
| <li class="none">
|
| <a href="../archive/document_archive.html" title="Archive">Archive</a>
|
| </li>
|
| </ul>
|
| <h5>About James</h5>
|
| <ul>
|
| <li class="none">
|
| <a href="../../index.html" title="Overview">Overview</a>
|
| </li>
|
| <li class="none">
|
| <a href="../../newsarchive.html" title="News">News</a>
|
| </li>
|
| <li class="none">
|
| <a href="../../mail.html" title="Mailing Lists">Mailing Lists</a>
|
| </li>
|
| <li class="none">
|
| <a href="../../contribute.html" title="Contributing">Contributing</a>
|
| </li>
|
| <li class="none">
|
| <a href="../../guidelines.html" title="Guidelines">Guidelines</a>
|
| </li>
|
| <li class="none">
|
| <a href="http://wiki.apache.org/james" title="Wiki">Wiki</a>
|
| </li>
|
| <li class="none">
|
| <a href="../../weare.html" title="Who We Are">Who We Are</a>
|
| </li>
|
| <li class="none">
|
| <a href="../../license.html" title="License">License</a>
|
| </li>
|
| </ul>
|
| <h5>Download</h5>
|
| <ul>
|
| <li class="none">
|
| <a href="../../download.cgi" title="Releases">Releases</a>
|
| </li>
|
| <li class="none">
|
| <a href="http://people.apache.org/builds/james/nightly/" title=""></a>
|
| </li>
|
| </ul>
|
| <h5>Apache Software Foundation</h5>
|
| <ul>
|
| <li>
|
| <strong>
|
| <a title="ASF" href="http://www.apache.org/">ASF</a>
|
| </strong>
|
| </li>
|
| <li>
|
| <a title="Get Involved" href="http://www.apache.org/foundation/getinvolved.html">Get Involved</a>
|
| </li>
|
| <li>
|
| <a title="FAQ" href="http://www.apache.org/foundation/faq.html">FAQ</a>
|
| </li>
|
| <li>
|
| <a title="License" href="http://www.apache.org/licenses/" >License</a>
|
| </li>
|
| <li>
|
| <a title="Sponsorship" href="http://www.apache.org/foundation/sponsorship.html">Sponsorship</a>
|
| </li>
|
| <li>
|
| <a title="Thanks" href="http://www.apache.org/foundation/thanks.html">Thanks</a>
|
| </li>
|
| <li>
|
| <a title="Security" href="http://www.apache.org/security/">Security</a>
|
| </li>
|
| </ul>
|
| <a href="http://maven.apache.org/" title="Built by Maven" class="poweredBy">
|
| <img class="poweredBy" alt="Built by Maven" src="../images/logos/maven-feather.png" />
|
| </a>
|
|
|
|
|
| </div>
|
| </div>
|
| <div id="bodyColumn">
|
| <div id="contentBox">
|
| <!-- Licensed to the Apache Software Foundation (ASF) under one |
| or more contributor license agreements. See the NOTICE file |
| distributed with this work for additional information |
| regarding copyright ownership. The ASF licenses this file |
| to you under the Apache License, Version 2.0 (the |
| "License"); you may not use this file except in compliance |
| with the License. You may obtain a copy of the License at |
| |
| http://www.apache.org/licenses/LICENSE-2.0 |
| |
| Unless required by applicable law or agreed to in writing, |
| software distributed under the License is distributed on an |
| "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY |
| KIND, either express or implied. See the License for the |
| specific language governing permissions and limitations |
| under the License. --> |
| |
| |
| |
| <div class="section"><h2>James 2.0 - Using JDBC<a name="James_2.0_-_Using_JDBC"></a></h2> |
| |
| <p> |
| This document explains how to enable JAMES 2.0 to use database storage via JDBC. Based on ReadMe notes by Darrell DeBoer and ??. |
| </p> |
| </div> |
| |
| <div class="section"><h2>Goals<a name="Goals"></a></h2> |
| |
| <p>Main Goals. |
| </p><ul> |
| <li>use Avalon and Cornerstone DataSource components for connection serving and pooling (done)</li> |
| <li>Remove hard-coded SQL statements from UsersJdbcRepository (done)</li> |
| <li>'SqlResources.java' - detect db product from jdbc connection and select appropriate SQL statements from SQL definition file for specific product (done)</li> |
| <li>Simpler to create database-backed UserRepository implementations for different User implementations (done)</li> |
| <li>Simplify UserRepository specification in config - make it URL:// based, like MailRepository. (done)</li> |
| <li>Consolidate existing UserRepository implementations - refactor out common functionality (TODO)</li> |
| <li>Have UserStore serve up repository implementations based on: storage, User implementation, and location. (TODO)</li> |
| </ul> |
| |
| <p>Other Goals (reuse development in JdbcMailRepository): |
| </p><ul> |
| <li>use Avalon and Cornerstone DataSource components in JdbcMailRepository (done)</li> |
| <li>Use SqlResources.java to provide db-specific SQL to JdbcMailRepository (done)</li> |
| <li>Automatic table generation for JdbcMailRepository (done)</li> |
| <li>Get rid of the separate database .properties files. (done)</li> |
| </ul> |
| |
| |
| </div> |
| |
| <div class="section"><h2>USE INSTRUCTIONS<a name="USE_INSTRUCTIONS"></a></h2> |
| |
| <p> |
| The main configuration is setting up the "database-connections" section of the |
| config file. There's an example there using MySql - I haven't yet tested on |
| other databases (although the SQL statements haven't changed much, so I |
| imagine it will still work on other platforms). |
| </p> |
| <p> |
| The only config properties you should need to set are: |
| </p><ul> |
| <li> <driver> Class name of database driver to use </driver></li> |
| <li> <dburl> the jdbc connection string for your database </dburl></li> |
| <li> <user> database user </user></li> |
| <li> <password> database password </password></li> |
| </ul> |
| |
| </div> |
| |
| <div class="section"><h2>TEST INSTRUCTIONS (FOR USER REPOSITORIES)<a name="TEST_INSTRUCTIONS_FOR_USER_REPOSITORIES"></a></h2> |
| <p> |
| </p><ul> |
| <li>Telnet to the remote manager: "telnet localhost 4555".</li> |
| <li>Do some user management - type "help" for options.</li> |
| <li>type "use list-james", to switch to the repository for this list.</li> |
| <li>list the users</li> |
| <li>send an email to "james-on@localhost"</li> |
| <li>list the users again</li> |
| </ul> |
| (note: some user management commands fail for repositories other than "LocalUsers"). |
| |
| |
| </div> |
| |
| <div class="section"><h2>MAIL REPOSITORY<a name="MAIL_REPOSITORY"></a></h2> |
| <p> |
| Mail repositories are now configured primarily by their "destinationURL" |
| property. This has the format "db://datasource/table[/repository]". Other |
| config such as the "sqlFile" (where to find sqlResources.xml, and the "filestore" |
| for mixed storage, can also be included, or can be left to defaults (see below). |
| </p> |
| <p> |
| Each repository registered in the MailStore can now take a "config" section, |
| which is the default configuration used by the MailStore when creating a repository |
| of that class. This allows us to have a configurable JDBCMailRepository, without |
| needing to specify config everywhere it's used. I've set up the SPOOL repository |
| to use mixed storage (a filestore in addition to the database), but the MAIL |
| repository to use pure db storage. |
| </p> |
| <p> |
| The new config has been tested with "inbox" and "spool" repositories, but it's not |
| yet tested with the "error", "spam" and "outgoing" repositories. |
| </p> |
| <p> |
| The statements in the SqlResources.xml file have been tested on MySQL and M$SQL. |
| Only M$ has the optimised "getMessageSize" SQL, but this is optional. |
| </p> |
| <p> |
| You no longer have to manually create the tables required - this is automatic. |
| Create Table statements are included for M$SQL and MySQL; we'll need to add others |
| for other db products. |
| </p> |
| |
| </div> |
| |
| <div class="section"><h2>USERS REPOSITORY<a name="USERS_REPOSITORY"></a></h2> |
| <p> |
| I've added an "AbstractJdbcUsersRepository", which takes care of most of the work |
| of a JdbcUsersRepository, making it pretty easy to add new ones. The abstract |
| implementation doesn't have knowledge of User implementations, this is restricted to |
| overridden methods in concrete UsersRepository implementations. |
| </p> |
| <p> |
| The AbstractJdbcUsersRepository obtains SQL statements via an "SqlResources" object, |
| which reads an sql definition file, finds the appropriate <sqlDefs> element, and |
| provides the sql strings contained. In addition, the SqlResources class handles |
| 2 other things: |
| </p><ul> |
| <li> |
| a) Parameter replacement in SQL (eg replace all occurances of ${table} within |
| an sql statement with the parameter value for "table". Currently, all |
| parameters are taken from the configuration <sqlParameters> element. It |
| is also possible to define parameters (defaults, if you like) within the |
| sql definition file itself (a <parameters> element).</li> |
| <li> b) Examines the Jdbc Connection to determine what database product is being |
| used. SQL statements specific to a db product (eg mysql) can then be used |
| automatically. (Detection is done by regexp matches on |
| Connection.getMetaData.getDatabaseProductName())</li> |
| </ul> |
| I've added 3 concrete subclasses of AbstractJdbcUserRepository: for DefaultUser, |
| DefaultJamesUser, and "ListUser" (which for now is nothing more than a name). These |
| give an example of how little work there is to implement a new repository. The |
| ListUsersJdbcRepository can store multiple lists of names in a single table. |
| |
| <p> |
| I've made a simple modification to "RemoteManagerHandler", to allow testing. The |
| "use [userRepositoryName]" command will switch the Remote manager to manage the |
| named repository. This isn't really intended for production, makes for easier testing. |
| The "james-config.xml" included in the proposal sets up 4 JDBC repositories: |
| </p><ul> |
| <li> "localUsers" - a JamesUsersJdbcRepository.</li> |
| <li> "list-james" - a ListUsersJdbcRepository, used by the ListServ mailet.</li> |
| <li> "list-test" - another ListUsersJdbcRepositor, for testing.</li> |
| <li> "default-users" - a DefaultUsersJdbcRepository, for testing.</li> |
| </ul> |
| |
| <p> |
| Note that in order for the Avalon DataSource components to work, I've included |
| an upgraded "avalon-excalibur.jar" in the proposal. |
| |
| </p> |
| |
| </div> |
| |
|
|
| </div>
|
| </div>
|
| <div class="clear">
|
| <hr/>
|
| </div>
|
| <div id="footer">
|
| <div class="xright">Copyright © 2006-2012
|
| <a href="http://www.apache.org">The Apache Software Foundation</a>.
|
| All Rights Reserved.
|
|
|
| </div>
|
| <div class="clear">
|
| <hr/>
|
| </div>
|
| </div>
|
| </body>
|
| </html>
|