Re: Anyone uses Interbase/firebird Database for James

[simon]

Steve,

I am using the bytea type (a blob/binary data type) for message body

and using pg73b3jdbc3.jar which supports this type, I put the jbc jar in james_home/lib.
My current file has some firebird stuff in it but works ok for postgresql.

My last message had sql file attached.
Simon

Steve wrote:

Simon,

Could you please share your sqlResources file? I am trying to setup
PostgreSQL right now, but do not know enough about the
Postgres datatypes to know if I am doing it correctly.

Perhaps the developers would like to commit it to the project as well.

Thanks!

Steve B.

----- Original Message -----
From: "simon" <[EMAIL PROTECTED]>
To: "James Users List" <[EMAIL PROTECTED]>
Sent: Sunday, November 24, 2002 1:14 AM
Subject: Re: Anyone uses Interbase/firebird Database for James

Alice,

No but -

I am using PostgreSQL 7.2.3 with james and have a simple trigger on the
inbox table to count messages.

I added postgres bit to conf/sqlResources.xml because I needed to have
the types as
message_body bytea NOT NULL ,
last_updated timestamp NOT NULL,
to get it to work.

Postgres supports triggers, view and stored procedures.

So with Firebirdsql you may need to use a blob for message body and
whatever function name is needed to get the length of message_body

column..

As Noel has indicated - have a look at sqlResources.xml for queries that
james uses.


Noel - I have attached my sqlResources.xml for postgresql.



Simon

Alice K wrote:

Just wonder anyone has configured James to use Borland Interbase
(borland.com/interbase) or its Open-source version Firebird
(firebirdsql.org) for James.

Both Commercial/Open-source Interbase/Firebird is available in all
platforms with zero management with support all db features such as
view, trigger, stored procedures.

Also, JDBC Type 4 driver is available. Comments, suggestions and helps
are appreciated.

Alice


_________________________________________________________________
Add photos to your messages with MSN 8. Get 2 months FREE*.
http://join.msn.com/?page=features/featuredemail


--
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(64) NOT NULL, pwdHash VARCHAR(50), pwdAlgorithm
VARCHAR(20), useForwarding INTEGER, forwardDestination VARCHAR(255),
useAlias INTEGER, alias VARCHAR(255), PRIMARY KEY(username))</sql>

<sql name="createTable">CREATE TABLE ${table} (username VARCHAR(64)

NOT NULL, pwdHash VARCHAR(50), pwdAlgorithm VARCHAR(20), useForwarding
SMALLINT, forwardDestination VARCHAR(255), useAlias SMALLINT, alias
VARCHAR(255), PRIMARY KEY(username))</sql>

<!-- An example of a database-specific sql statement
<sql name="createTable" db="mssql">CREATE TABLE ${table} (uniqueId

UNIQUEIDENTIFIER, username VARCHAR(64) NOT NULL, pwdHash VARCHAR(50),
pwdAlgorithm VARCHAR(20), useForwarding SMALLINT, forwardDestination
VARCHAR(255), useAlias SMALLINT, alias VARCHAR(255), 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(64) NOT NULL, pwdHash VARCHAR(50), pwdAlgorithm
VARCHAR(20), PRIMARY KEY(username))</sql>

<sql name="createTable">CREATE TABLE ${table} (username VARCHAR(64)

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(64) NOT NULL, listSubscriber VARCHAR(255) NOT NULL,
PRIMARY KEY(listName, listSubscriber))</sql>

<sql name="createTable">CREATE TABLE ${table} (listName VARCHAR(64)

NOT NULL, listSubscriber VARCHAR(255) NOT NULL, PRIMARY KEY(listName,
listSubscriber))</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="postgresql">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 (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients text NOT NULL ,
remote_host varchar (255) 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="postgresql">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients text NOT NULL ,
remote_host varchar (255) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body bytea NOT NULL ,
last_updated timestamp 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 (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients varchar NOT NULL ,
remote_host varchar (255) 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] (255) NOT NULL,
[message_state] [varchar] (30) NOT NULL ,
[error_message] [varchar] (1000) NULL ,
[sender] [varchar] (255) NULL ,
[recipients] [text] NOT NULL ,
[remote_host] [varchar] (255) 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(255) NOT NULL ,
message_state varchar2(30) NOT NULL ,
error_message varchar2(200) NULL ,
sender varchar2(255) ,
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="postgresql">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 (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients varchar NOT NULL ,
remote_host varchar (255) 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="postgresql">
CREATE TABLE ${table} (
message_name varchar (200) NOT NULL,
repository_name varchar (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients text NOT NULL ,
remote_host varchar (255) NOT NULL ,
remote_addr varchar (20) NOT NULL ,
message_body bytea 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 (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients text NOT NULL ,
remote_host varchar (255) 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] (255) NOT NULL,
[message_state] [varchar] (30) NOT NULL ,
[error_message] [varchar] (1000) NULL ,
[sender] [varchar] (255) NULL ,
[recipients] [text] NOT NULL ,
[remote_host] [varchar] (255) 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(255) NOT NULL ,
message_state varchar2(30) NOT NULL ,
error_message varchar2(200) NULL ,
sender varchar2(255) NULL ,
recipients varchar2(1000) NOT NULL ,
remote_host varchar2(255) 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 (255) NOT NULL,
message_state varchar (30) NOT NULL ,
error_message varchar (200) NULL ,
sender varchar (255) NULL ,
recipients long NOT NULL ,
remote_host varchar (255) 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]>


--
To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>

--
To unsubscribe, e-mail:   <mailto:[EMAIL PROTECTED]>
For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>