(I am running james-2.1a1 binary dist, jdk1.3, WindowsXP running james as a
service)
Kudos to the developers! The sqlResources and config.xml implementation mad
things as simple as they could be.
I hope it is ok to send attachments to this mail list. Here is the
sqlResources.xml I created for 2.1a1. The following instructions will setup
James to use HypersonicSQL in "stand-alone" mode.
1) replace the sqlResources.xml in the /conf directory with the one attached
to this email.
2) Add the following to the <database-connections> nest. (Note that the url
will need to be modified to an existing pathname for your installation.):
<data-source name="maildb"
class="org.apache.james.util.mordred.JdbcDataSource">
<driver>org.hsql.jdbcDriver</driver>
<dburl>jdbc:HypersonicSQL:<path-to-james>/apps/james/var/james.db</dburl>
<user>sa</user>
<password />
<max>10</max>
</data-source>
3) There are many areas in the config.xml which have values for the file
repository, but also have commented-out equivalents for the database
repository; comment-out the file xml, and uncomment out the db xml. (Hint:
do a text search for "file:". Careful, some of these are the path to the
sqlResources.xml which should not be changed.)
4) Put the HypersonicSQL jar file (hsql.jar) into the <james-home>/lib
folder. You can download it from SourceForge (although IMHO it would be nice
to distribute with James).
Start-up James and test it out. You will see that the first time you run,
the database files will automatically be created by HypersonicSQL in the
directory you indicated in the <dburl>. Note that this is in stand-alone
mode, so if you would like to interact with the db directly, you will need
to stop james to un-lock the data files.
HypersonicSQL includes its own interface. chdir to the james/lib folder ant
type:
java -cp hsql.jar org.hsql.util.DatabaseManager
I just set this up last night. I sent and received a couple test messages -
in other words, this is not well tested. If you have any problems related to
this setup, let me know. If I can recreate the problem, I can try to fix it.
Steve B.
PS - If I get a chance, I will try to download the latest CVS today and
apply the changes there as well.
----- Original Message -----
From: "Noel J. Bergman" <[EMAIL PROTECTED]>
To: "James Users List" <[EMAIL PROTECTED]>
Sent: Wednesday, August 14, 2002 1:41 AM
Subject: RE: Is this an error? Trying to create a hypersonic db store
> Steve,
>
> Please submit that information. A lot of users have asked about using
> HypersonicSQL with James.
>
> Please update to the CVS version. We've made a number of changes
> specifically in the area you were exercising.
>
> If I recall correctly, there is a race condition in the spooler scheme. A
> message is acquired from the spool by a worker thread. The worker thread
> then asks if it can own the message. If another thread has the message
> locked (in a map), then the worker can't have it, and it goes back to the
> spool for another. However, if a thread actively working on the message
> should happen finish, delete and unlock the message between the time when
> the new worker thread tentatively acquires the message from the spool to
> work on and when it sees if someone else has locked it, then you'll see
that
> exception.
>
> We've recently done a lot of work in that part of the code, and have
> significantly reduced the possibility of that exception. When it does
> happen, it is harmless. It simply means that a message was in the queue,
> was pulled from the queue, but was already in use by another thread that
> finished with it and deleted it because there is nothing left to do.
>
> --- Noel
>
> -----Original Message-----
> From: Steve [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, August 14, 2002 2:36
> To: James Users List
> Subject: Is this an error? Trying to create a hypersonic db store
>
>
> Hello all.
>
> I am trying to setup James using HypersonicSQL as my db stores. I just
> downloaded james-2.1a1 in an attempt to start with the latest version
(short
> of CVS).
>
> I have
> - modified my SAR-INF/config.xml so that all (I hope) mailets and stores
use
> the db option.
> - I have added hypersonic connection parameters to the
> <database-connections> section.
> - I have updated the sqlResources.xml
> added dbMatcher for Hypersonic
> added sql's for hypersonic
>
> James now starts up without reporting any errors.
> I can use the telnet client to add and list users.
> I can send emails to James, from james, and within james.
>
> Once, when I was sending an email to myself, I noted the following in the
> run window:
>
> C:\james-2.1a1\bin>run
> Using PHOENIX_HOME: C:\james-2.1a1
> Using PHOENIX_TMPDIR: C:\james-2.1a1\temp
> Using JAVA_HOME: c:\jdk1.3
>
> Phoenix 4.0a4
>
> James 2.1a1
> Started POP3 Server plain:110
> Started SMTP Server plain:25
> Started NNTP Server plain:119
> java.lang.RuntimeException: Did not find a record Mail1029306580132-2 in
> spool
> at
> org.apache.james.mailrepository.JDBCMailRepository.retrieve(JDBCMailR
> epository.java:454)
> at
> org.apache.james.transport.JamesSpoolManager.run(JamesSpoolManager.ja
> va:205)
> at
> org.apache.avalon.excalibur.thread.impl.ExecutableRunnable.execute(Ex
> ecutableRunnable.java:47)
> at
> org.apache.avalon.excalibur.thread.impl.WorkerThread.run(WorkerThread
> .java:80)
> java.lang.RuntimeException: Exception while retrieving mail: Did not find
a
> reco
> rd Mail1029306580132-2 in spool
> at
> org.apache.james.mailrepository.JDBCMailRepository.retrieve(JDBCMailR
> epository.java:494)
> at
> org.apache.james.transport.JamesSpoolManager.run(JamesSpoolManager.ja
> va:205)
> at
> org.apache.avalon.excalibur.thread.impl.ExecutableRunnable.execute(Ex
> ecutableRunnable.java:47)
> at
> org.apache.avalon.excalibur.thread.impl.WorkerThread.run(WorkerThread
> .java:80)
>
> However, I did receive the email. Is this a problem? In general, I want to
> test the setup. What tests would you suggest, and what logs should I look
> for?
>
> Thanks!
>
> Steve B.
>
> PS - the JDBC How-To makes it sound like you have only to add info to the
> database-connections section. This may be a bit misleading as there are
many
> places in the config.xml that need to be commented and uncommented. It may
> be worth listing them as when I eventually realized there were additional
> changes to be made, it took me a couple tries to find them all...just a
> suggestion.
>
>
> --
> To unsubscribe, e-mail:
<mailto:[EMAIL PROTECTED]>
> For additional commands, e-mail:
<mailto:[EMAIL PROTECTED]>
<!-- SQL Statements used by James for database access. -->
<sqlResources>
<!--
This section provided configuration to determine the determine the
database product which is being used for storage. Different database
products may require different SQL syntax.
The jdbc database connection is examined to see if it matches with the
regular expressions specified in any of the defined matchers. The matchers
are processed in the over provided here, with the first successful match
defining the "db" value for this connection.
This value is then used to choose between different definitions for various
named sql statements, defined below. If no match is found,
the default sql statements are used.
-->
<dbMatchers>
<dbMatcher db="mssql" databaseProductName="microsoft sql server"/>
<dbMatcher db="oracle" databaseProductName="oracle.*"/>
<dbMatcher db="mysql" databaseProductName="my.*"/>
<dbMatcher db="postgresql" databaseProductName="postgres.*"/>
<dbMatcher db="sapdb" databaseProductName="sap.*"/>
<dbMatcher db="hypersonic" databaseProductName="HypersonicSQL"/>
</dbMatchers>
<!-- SQL statements to use for various components.
Parameter definitions ${param} are replaced with parameter values
from the configuration file.
If a named statement has a definition defined for the current database product,
then that statement is used. Otherwise the default statement is used.
-->
<!-- SQL statements for the JamesUsersJdbcRepository -->
<sqlDefs name="org.apache.james.userrepository.JamesUsersJdbcRepository">
<sql name="tableName">${table}</sql>
<sql name="select">SELECT username, pwdHash, pwdAlgorithm, useForwarding,
forwardDestination, useAlias, alias
FROM ${table}
ORDER BY username
</sql>
<sql name="selectByLowercaseName">
SELECT username, pwdHash, pwdAlgorithm, useForwarding,
forwardDestination, useAlias, alias
FROM ${table}
WHERE lower(username) = ?
</sql>
<sql name="insert">INSERT INTO ${table}
(username, pwdHash, pwdAlgorithm, useForwarding, forwardDestination, useAlias, alias)
VALUES (?,?,?,?,?,?,?)
</sql>
<sql name="update">UPDATE ${table} SET
pwdHash = ?, pwdAlgorithm = ?, useForwarding = ?, forwardDestination = ?, useAlias = ?, alias = ?
WHERE username = ?
</sql>
<sql name="delete">DELETE FROM ${table} WHERE username = ?</sql>
<sql name="createTable" db="hypersonic">CREATE CACHED TABLE ${table} (username VARCHAR(50) NOT NULL, pwdHash VARCHAR(50), pwdAlgorithm VARCHAR(20), useForwarding SMALLINT, forwardDestination VARCHAR(250), useAlias INTEGER, alias VARCHAR(250), PRIMARY KEY(username))</sql>
<sql name="createTable">CREATE TABLE ${table} (username VARCHAR(50) NOT NULL, pwdHash VARCHAR(50), pwdAlgorithm VARCHAR(20), useForwarding SMALLINT, forwardDestination VARCHAR(250), useAlias SMALLINT, alias VARCHAR(250), PRIMARY KEY(username))</sql>
<!-- An example of a database-specific sql statement
<sql name="createTable" db="mssql">CREATE TABLE ${table} (uniqueId UNIQUEIDENTIFIER, username VARCHAR(50) NOT NULL, pwdHash VARCHAR(50), pwdAlgorithm VARCHAR(20), useForwarding SMALLINT, forwardDestination VARCHAR(250), useAlias SMALLINT, alias VARCHAR(250), PRIMARY KEY(username))</sql>
-->
</sqlDefs>
<!-- SQL statements for the DefaultUsersJdbcRepository -->
<sqlDefs name="org.apache.james.userrepository.DefaultUsersJdbcRepository">
<sql name="tableName">${table}</sql>
<sql name="select">SELECT username, pwdHash, pwdAlgorithm
FROM ${table}
</sql>
<sql name="insert">INSERT INTO ${table}
(username, pwdHash, pwdAlgorithm)
VALUES (?,?,?)
</sql>
<sql name="update">UPDATE ${table} SET
pwdHash = ?, pwdAlgorithm = ?
WHERE username = ?
</sql>
<sql name="delete">DELETE FROM ${table} WHERE username = ?</sql>
<sql name="createTable" db="hypersonic">CREATE CACHED TABLE ${table} (username VARCHAR(50) NOT NULL, pwdHash VARCHAR(50), pwdAlgorithm VARCHAR(20), PRIMARY KEY(username))</sql>
<sql name="createTable">CREATE TABLE ${table} (username VARCHAR(50) NOT NULL, pwdHash VARCHAR(50), pwdAlgorithm VARCHAR(20), PRIMARY KEY(username))</sql>
</sqlDefs>
<!-- SQL statements for the ListUsersJdbcRepository
(allowing multiple lists in one table) -->
<sqlDefs name="org.apache.james.userrepository.ListUsersJdbcRepository">
<!-- An example of defining "default" parameters within the definition file.
This is used if no key parameter is specified in config. -->
<parameters key="unknownList"/>
<sql name="tableName">${table}</sql>
<sql name="select">SELECT listSubscriber
FROM ${table}
WHERE listName = '${key}'
</sql>
<sql name="insert">INSERT INTO ${table}
(listSubscriber, listName)
VALUES (?, '${key}')
</sql>
<sql name="update">UPDATE ${table} SET
listSubscriber = ?
WHERE listSubscriber = ? AND listName = '${key}'
</sql>
<sql name="delete">DELETE FROM ${table}
WHERE listSubscriber = ? AND listName = '${key}'
</sql>
<sql name="createTable" db="hypersonic">CREATE CACHED TABLE ${table} (listName VARCHAR(50) NOT NULL, listSubscriber VARCHAR(50) NOT NULL)</sql>
<sql name="createTable">CREATE TABLE ${table} (listName VARCHAR(50) NOT NULL, listSubscriber VARCHAR(50) NOT NULL)</sql>
</sqlDefs>
<!-- SQL statements for the JdbcMailRepository -->
<sqlDefs name="org.apache.james.mailrepository.JDBCMailRepository">
<sql name="checkMessageExistsSQL">SELECT count(*) FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="updateMessageSQL">UPDATE ${table} SET message_state = ?, error_message = ?, sender = ?, recipients = ?, remote_host = ?, remote_addr = ?, last_updated = ? WHERE message_name = ? AND repository_name = ?</sql>
<sql name="updateMessageBodySQL">UPDATE ${table} SET message_body = ? WHERE message_name = ? AND repository_name = ?</sql>
<sql name="insertMessageSQL">INSERT INTO ${table} (message_name, repository_name, message_state, error_message, sender, recipients, remote_host, remote_addr, last_updated, message_body) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)</sql>
<sql name="retrieveMessageSQL">SELECT message_state, error_message, sender, recipients, remote_host, remote_addr, last_updated FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="retrieveMessageBodySQL">SELECT message_body FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="retrieveMessageBodySizeSQL" db="mssql">SELECT datalength(message_body) FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="retrieveMessageBodySizeSQL" db="mysql">SELECT length(message_body) FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="retrieveMessageBodySizeSQL" db="hypersonic">SELECT length(message_body) FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="removeMessageSQL">DELETE FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="listMessagesSQL">SELECT message_name, message_state, last_updated FROM ${table} WHERE repository_name = ? ORDER BY last_updated ASC</sql>
<sql name="createTable" db="mysql">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (200) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (200) NULL ,
recipients text NOT NULL ,
remote_host varchar (100) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body longblob NOT NULL ,
last_updated datetime NOT NULL,
PRIMARY KEY (message_name, repository_name)
)
</sql>
<sql name="createTable" db="hypersonic">
CREATE CACHED TABLE ${table} (
message_name varchar(200) NOT NULL,
repository_name varchar(200) NOT NULL,
message_state varchar(30) NOT NULL ,
error_message varchar(200) NULL ,
sender varchar(200) NULL ,
recipients varchar NOT NULL ,
remote_host varchar(100) NOT NULL ,
remote_addr varchar(20) NOT NULL ,
message_body varchar NOT NULL ,
last_updated timestamp NOT NULL,
PRIMARY KEY (message_name, repository_name)
)
</sql>
<sql name="createTable" db="mssql">
CREATE TABLE [${table}] (
[message_name] [varchar] (200) NOT NULL,
[repository_name] [varchar] (200) NOT NULL,
[message_state] [varchar] (30) NOT NULL ,
[error_message] [varchar] (1000) NULL ,
[sender] [varchar] (200) NULL ,
[recipients] [text] NOT NULL ,
[remote_host] [varchar] (100) NOT NULL ,
[remote_addr] [varchar] (20) NOT NULL ,
[message_body] [image] NOT NULL ,
[last_updated] [datetime] NOT NULL
)
ALTER TABLE [${table}] WITH NOCHECK ADD
CONSTRAINT [PK_${table}] PRIMARY KEY
(
[message_name],
[repository_name]
)
</sql>
<sql name="createTable" db="oracle">
CREATE TABLE ${table} (
message_name varchar2(200) NOT NULL ,
repository_name varchar2(200) NOT NULL ,
message_state varchar2(30) NOT NULL ,
error_message varchar2(200) NULL ,
sender varchar2(200) ,
recipients varchar2(1000) NOT NULL ,
remote_host varchar2(100) NOT NULL ,
remote_addr varchar2(20) NOT NULL ,
message_body long raw NOT NULL ,
last_updated date NOT NULL ,
PRIMARY KEY (message_name, repository_name)
)
TABLESPACE system
</sql>
<sql name="createTable" db="sapdb">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (200) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (200) NULL ,
recipients long NOT NULL ,
remote_host varchar (100) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body long byte NOT NULL ,
last_updated date NOT NULL,
PRIMARY KEY (message_name, repository_name)
)
</sql>
</sqlDefs>
<sqlDefs name="org.apache.james.mailrepository.JDBCSpoolRepository">
<sql name="checkMessageExistsSQL">SELECT count(*) FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="updateMessageSQL">UPDATE ${table} SET message_state = ?, error_message = ?, sender = ?, recipients = ?, remote_host = ?, remote_addr = ?, last_updated = ? WHERE message_name = ? AND repository_name = ?</sql>
<sql name="updateMessageBodySQL">UPDATE ${table} SET message_body = ? WHERE message_name = ? AND repository_name = ?</sql>
<sql name="insertMessageSQL">INSERT INTO ${table} (message_name, repository_name, message_state, error_message, sender, recipients, remote_host, remote_addr, last_updated, message_body) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)</sql>
<sql name="retrieveMessageSQL">SELECT message_state, error_message, sender, recipients, remote_host, remote_addr, last_updated FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="retrieveMessageBodySQL">SELECT message_body FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="retrieveMessageBodySizeSQL" db="mssql">SELECT datalength(message_body) FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="retrieveMessageBodySizeSQL" db="mysql">SELECT length(message_body) FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="retrieveMessageBodySizeSQL" db="hypersonic">SELECT length(message_body) FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="removeMessageSQL">DELETE FROM ${table} WHERE message_name = ? AND repository_name = ?</sql>
<sql name="listMessagesSQL">SELECT message_name, message_state, last_updated FROM ${table} WHERE repository_name = ? ORDER BY last_updated ASC</sql>
<sql name="createTable" db="hypersonic">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (200) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (200) NULL ,
recipients varchar NOT NULL ,
remote_host varchar (100) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body varchar NOT NULL ,
last_updated timestamp NOT NULL,
PRIMARY KEY (message_name, repository_name)
)
</sql>
<sql name="createTable" db="mysql">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (200) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (200) NULL ,
recipients text NOT NULL ,
remote_host varchar (100) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body longblob NOT NULL ,
last_updated datetime NOT NULL,
PRIMARY KEY (message_name, repository_name)
)
</sql>
<sql name="createTable" db="mssql">
CREATE TABLE [${table}] (
[message_name] [varchar] (200) NOT NULL,
[repository_name] [varchar] (200) NOT NULL,
[message_state] [varchar] (30) NOT NULL ,
[error_message] [varchar] (1000) NULL ,
[sender] [varchar] (200) NULL ,
[recipients] [text] NOT NULL ,
[remote_host] [varchar] (100) NOT NULL ,
[remote_addr] [varchar] (20) NOT NULL ,
[message_body] [image] NOT NULL ,
[last_updated] [datetime] NOT NULL
)
ALTER TABLE [${table}] WITH NOCHECK ADD
CONSTRAINT [PK_${table}] PRIMARY KEY
(
[message_name],
[repository_name]
)
</sql>
<sql name="createTable" db="oracle">
CREATE TABLE ${table} (
message_name varchar2(200) NOT NULL ,
repository_name varchar2(200) NOT NULL ,
message_state varchar2(30) NOT NULL ,
error_message varchar2(200) NULL ,
sender varchar2(200) NULL ,
recipients varchar2(1000) NOT NULL ,
remote_host varchar2(100) NOT NULL ,
remote_addr varchar2(20) NOT NULL ,
message_body long raw NOT NULL ,
last_updated date NOT NULL ,
PRIMARY KEY (message_name, repository_name)
)
</sql>
<sql name="createTable" db="sapdb">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (200) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (200) NULL ,
recipients long NOT NULL ,
remote_host varchar (100) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body long byte NOT NULL ,
last_updated date NOT NULL,
PRIMARY KEY (message_name, repository_name)
)
</sql>
</sqlDefs>
</sqlResources>
--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
