| <?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 2021-11-12 --> |
| <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>Apache James Project – 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="20211112" /> |
| <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-logo.png"> |
| |
| |
| <img src="../../images/logos/james-logo.png" alt="James Project" /> |
| </a> |
| <a href="https://www.apache.org/index.html" id="bannerRight"> |
| |
| |
| <img src="images/logos/asf_logo_small.png" alt="The Apache Software Foundation" /> |
| </a> |
| <div class="clear"> |
| <hr/> |
| </div> |
| </div> |
| <div id="breadcrumbs"> |
| |
| |
| <div class="xleft"> |
| <span id="publishDate">Last Published: 2021-11-12</span> |
| </div> |
| <div class="xright"> <a href="../../index.html" title="Home">Home</a> |
| | |
| <a href="../../documentation.html" title="James">James</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> |
| |
| |
| </div> |
| <div class="clear"> |
| <hr/> |
| </div> |
| </div> |
| <div id="leftColumn"> |
| <div id="navcolumn"> |
| |
| |
| <h5>James components</h5> |
| <ul> |
| <li class="collapsed"> |
| <a href="../../documentation.html" title="About James">About James</a> |
| </li> |
| <li class="collapsed"> |
| <a href="../../server/index.html" title="Server">Server</a> |
| </li> |
| <li class="collapsed"> |
| <a href="../../mailet/index.html" title="Mailets">Mailets</a> |
| </li> |
| <li class="collapsed"> |
| <a href="../../mailbox/index.html" title="Mailbox">Mailbox</a> |
| </li> |
| <li class="collapsed"> |
| <a href="../../protocols/index.html" title="Protocols">Protocols</a> |
| </li> |
| <li class="collapsed"> |
| <a href="../../mpt/index.html" title="MPT">MPT</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"> |
| |
| |
| |
| |
| <section> |
| <h2><a name="James_2.0_-_Using_JDBC"></a>James 2.0 - Using JDBC</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> |
| </section> |
| |
| <section> |
| <h2><a name="Goals"></a>Goals</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> |
| |
| |
| </section> |
| |
| <section> |
| <h2><a name="USE_INSTRUCTIONS"></a>USE INSTRUCTIONS</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> |
| |
| </section> |
| |
| <section> |
| <h2><a name="TEST_INSTRUCTIONS_.28FOR_USER_REPOSITORIES.29"></a>TEST INSTRUCTIONS (FOR USER REPOSITORIES)</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"). |
| |
| |
| </section> |
| |
| <section> |
| <h2><a name="MAIL_REPOSITORY"></a>MAIL REPOSITORY</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> |
| |
| </section> |
| |
| <section> |
| <h2><a name="USERS_REPOSITORY"></a>USERS REPOSITORY</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> |
| |
| </section> |
| |
| |
| </div> |
| </div> |
| <div class="clear"> |
| <hr/> |
| </div> |
| <div id="footer"> |
| <div class="xright">Copyright © 2006-2021 |
| <a href="https://www.apache.org/">The Apache Software Foundation</a>. |
| All Rights Reserved. |
| |
| </div> |
| <div class="clear"> |
| <hr/> |
| </div> |
| </div> |
| </body> |
| </html> |
