You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-user@james.apache.org by Alice K <al...@hotmail.com> on 2002/11/24 03:21:49 UTC
Anyone uses Interbase/firebird Database for James
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: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Anyone uses Interbase/firebird Database for James
Posted by simon <si...@sydneybluegum.com>.
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" <si...@sydneybluegum.com>
>To: "James Users List" <ja...@jakarta.apache.org>
>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:
>>><ma...@jakarta.apache.org>
>>>For additional commands, e-mail:
>>><ma...@jakarta.apache.org>
>>>
>>>
>>>
>>>
>>
>>
>>
>
>
>----------------------------------------------------------------------------
>----
>
>
>
>
>><!-- 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:
>>
>>
><ma...@jakarta.apache.org>
>
>
>>For additional commands, e-mail:
>>
>>
><ma...@jakarta.apache.org>
>
>
>--
>To unsubscribe, e-mail: <ma...@jakarta.apache.org>
>For additional commands, e-mail: <ma...@jakarta.apache.org>
>
>
>
>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Anyone uses Interbase/firebird Database for James
Posted by Steve <sb...@velos.com>.
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" <si...@sydneybluegum.com>
To: "James Users List" <ja...@jakarta.apache.org>
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:
> > <ma...@jakarta.apache.org>
> > For additional commands, e-mail:
> > <ma...@jakarta.apache.org>
> >
> >
>
>
>
----------------------------------------------------------------------------
----
> <!-- 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:
<ma...@jakarta.apache.org>
> For additional commands, e-mail:
<ma...@jakarta.apache.org>
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>
Re: Anyone uses Interbase/firebird Database for James
Posted by simon <si...@sydneybluegum.com>.
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:
> <ma...@jakarta.apache.org>
> For additional commands, e-mail:
> <ma...@jakarta.apache.org>
>
>
RE: Anyone uses Interbase/firebird Database for James
Posted by "Noel J. Bergman" <no...@devtech.com>.
Alice,
Check sqlResources.xml to see if the generic statements are sufficient,
otherwise you'll need to add Interbase specific statements (and please
submit them for inclusion in a future release).
--- Noel
-----Original Message-----
From: Alice K [mailto:alicecanada@hotmail.com]
Sent: Saturday, November 23, 2002 21:22
To: james-user@jakarta.apache.org
Subject: Anyone uses Interbase/firebird Database for James
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
--
To unsubscribe, e-mail: <ma...@jakarta.apache.org>
For additional commands, e-mail: <ma...@jakarta.apache.org>