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]>
