[
https://issues.apache.org/jira/browse/JAMES-880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Eric Charles closed JAMES-880.
------------------------------
Resolution: Fixed
Since there, sql queries have moved from classes to sqlResources.xml and have
been reviewed.
For postgresql virtualusertable, E'\%' is used and works correctly (tested with
postgresql 8.4.3 on linux and james current trunk) - You can check all sql
hereafter.
Later, we should go to jpa to get rid of native sql.
<sql name="selectMappings" db="derby">select
VirtualUserTable.target_address,(VirtualUserTable."user" || '@'
||VirtualUserTable.domain) from VirtualUserTable, VirtualUserTable as
VUTDomains where ((VirtualUserTable."user") like ? or (VirtualUserTable."user")
like '\%' escape '\') and (VirtualUserTable.domain like ? or
(VirtualUserTable.domain like '\%' escape '\')) order by 2 desc</sql>
<sql name="selectMappings">select VirtualUserTable.target_address from
VirtualUserTable, VirtualUserTable as VUTDomains where (VirtualUserTable.user
like ? or VirtualUserTable.user like '\%') and (VirtualUserTable.domain like ?
or (VirtualUserTable.domain like '\%')) order by
concat(VirtualUserTable.user,'@',VirtualUserTable.domain) desc limit 1</sql>
<sql name="selectMappings" db="postgresql">select
VirtualUserTable.target_address from VirtualUserTable, VirtualUserTable as
VUTDomains where (VirtualUserTable.user like ? or VirtualUserTable.user like
E'%') and (VirtualUserTable.domain like ? or (VirtualUserTable.domain like
E'%')) order by (VirtualUserTable.user || '@' || VirtualUserTable.domain) desc
limit 1</sql>
<sql name="selectUserDomainMapping" db="derby">select
VirtualUserTable.target_address from VirtualUserTable where
VirtualUserTable."user" = ? and VirtualUserTable.domain = ? </sql>
<sql name="selectUserDomainMapping">select VirtualUserTable.target_address
from VirtualUserTable where VirtualUserTable.user = ? and
VirtualUserTable.domain = ? </sql>
<sql name="deleteMapping">delete from VirtualUserTable where
VirtualUserTable."user" = ? and VirtualUserTable.domain = ? and
VirtualUserTable.target_address = ?</sql>
<sql name="updateMapping">update VirtualUserTable set
VirtualUserTable.target_address = ? where VirtualUserTable."user" = ? and
VirtualUserTable.domain = ? </sql>
<sql name="addMapping">insert into VirtualUserTable values(?,?,?) </sql>
<sql name="selectAllMappings">select * from VirtualUserTable </sql>
<sql name="selectDomains">select distinct domain from
VirtualUserTable</sql>
<sql name="selectDomain">select distinct domain from VirtualUserTable where
domain = ? </sql>
<sql name="createTable" db="postgresql">
CREATE TABLE VirtualUserTable (
"user" varchar(64) NOT NULL default '',
domain varchar(255) NOT NULL default '',
target_address varchar(255) NOT NULL default '',
PRIMARY KEY ("user",domain)
)
</sql>
> bug with sql query for postgresql jdbc driver
> ---------------------------------------------
>
> Key: JAMES-880
> URL: https://issues.apache.org/jira/browse/JAMES-880
> Project: JAMES Server
> Issue Type: Bug
> Affects Versions: 2.3.1
> Environment: Debian Etch. Java 1.6.0_10, 32bit.
> Reporter: Daniel Dettlaff
> Assignee: Eric Charles
> Priority: Blocker
> Fix For: 3.0-M1
>
> Original Estimate: 0.33h
> Remaining Estimate: 0.33h
>
> It's BLOCKER when someone needs to get server for Virtual Users (virtual mail
> accounts like in tutorial here: http://wiki.apache.org/james/VirtualUserTable
> )
> Problem:
> in file: JDBCVirtualUserTable.java
> line: 150
> should be:
> query = getInitParameter("sqlquery","select VirtualUserTable.target_address
> from VirtualUserTable, VirtualUserTable as VUTDomains where
> (VirtualUserTable.user like ? or VirtualUserTable.user like '\\\\%') and
> (VirtualUserTable.domain like ? or (VirtualUserTable.domain like '\\\\%' and
> VUTDomains.domain like ?)) order by
> concat(VirtualUserTable.user,'@',VirtualUserTable.domain) desc limit 1");
> instead of:
> query = getInitParameter("sqlquery","select VirtualUserTable.target_address
> from VirtualUserTable, VirtualUserTable as VUTDomains where
> (VirtualUserTable.user like ? or VirtualUserTable.user like '\\%') and
> (VirtualUserTable.domain like ? or (VirtualUserTable.domain like '\\%' and
> VUTDomains.domain like ?)) order by
> concat(VirtualUserTable.user,'@',VirtualUserTable.domain) desc limit 1");
> So.. there should be doubled \\ cause postgresql on select did not select
> wanted row correctly with default ".. LIKE '\%' .." and there should be "..
> LIKE '\\%' .. " statement in query.
> After adding this fix it works perfectly.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
