You are viewing a plain text version of this content. The canonical link for it is here.
Posted to server-dev@james.apache.org by "Daniel Dettlaff (JIRA)" <se...@james.apache.org> on 2008/11/17 03:44:51 UTC
[jira] Created: (JAMES-880) bug with sql query for postgresql jdbc
driver
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
Priority: Blocker
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: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Updated: (JAMES-880) bug with sql query for postgresql jdbc
driver
Posted by "Norman Maurer (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/JAMES-880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Norman Maurer updated JAMES-880:
--------------------------------
Fix Version/s: 3.0-M1
> 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: Norman Maurer
> 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: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Assigned: (JAMES-880) bug with sql query for postgresql jdbc
driver
Posted by "Eric Charles (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/JAMES-880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Eric Charles reassigned JAMES-880:
----------------------------------
Assignee: Eric Charles (was: Norman Maurer)
> 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: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Closed: (JAMES-880) bug with sql query for postgresql jdbc
driver
Posted by "Eric Charles (JIRA)" <se...@james.apache.org>.
[ 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: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org
[jira] Assigned: (JAMES-880) bug with sql query for postgresql jdbc
driver
Posted by "Norman Maurer (JIRA)" <se...@james.apache.org>.
[ https://issues.apache.org/jira/browse/JAMES-880?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Norman Maurer reassigned JAMES-880:
-----------------------------------
Assignee: Norman Maurer
> 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: Norman Maurer
> Priority: Blocker
> 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: server-dev-unsubscribe@james.apache.org
For additional commands, e-mail: server-dev-help@james.apache.org