A thought: Is it possible to just reverse the order of the fields in the Primary Key? As in change: PRIMARY KEY (message_name, repository_name), to: PRIMARY KEY (repository_name, message_name),
In many databases, I think this allows the index (PK) to be used to optimize queries that filter by the repository_name, as well as those that use both repository_name and message_name. (With the old order, queries filtering by repository_name would not use the index). Dan -----Original Message----- From: Noel J. Bergman [mailto:[EMAIL PROTECTED]] Sent: Friday, January 10, 2003 2:08 PM To: James-User Mailing List Subject: WANTED: People familar with SQL particulars for non-MySQL servers James currently has support for several SQL database servers, using JDBC. There are specific sections for MySQL, MSSQL, Oracle, PostgreSQL, SAP, and HyperSonic. Each of these database servers has certain ... SQL subtleties. Recently we made a change for the benefit of MySQL, which I would like to reproduce for the other servers. The change effects the definitions for JDBCMailRepository operations. Specifically, the createTable operation for MySQL has a secondary index key. This looks like: <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), added --> KEY repo (repository_name) ) </sql> There are similar statements for the other database servers, but without the additional index. Rather than make a change myself for a server that I don't have available to test, I am asking if those of you who are familar with each of the others would please submit the appropriate change to me so that I can incorporate it into a future release of James. Thanks. :-) --- Noel -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]> ************************************************************************** This e-mail and any files transmitted with it may contain privileged or confidential information. It is solely for use by the individual for whom it is intended, even if addressed incorrectly. If you received this e-mail in error, please notify the sender; do not disclose, copy, distribute, or take any action in reliance on the contents of this information; and delete it from your system. Any other use of this e-mail is prohibited. Thank you for your compliance. -- To unsubscribe, e-mail: <mailto:[EMAIL PROTECTED]> For additional commands, e-mail: <mailto:[EMAIL PROTECTED]>
