James Server
  1. James Server
  2. JAMES-880

bug with sql query for postgresql jdbc driver

    Details

    • Type: Bug Bug
    • Status: Closed
    • Priority: Blocker Blocker
    • Resolution: Fixed
    • Affects Version/s: 2.3.1
    • Fix Version/s: 3.0-M1
    • Component/s: None
    • Labels:
      None
    • Environment:
      Debian Etch. Java 1.6.0_10, 32bit.

      Description

      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.

        Activity

        Mark Thomas made changes -
        Workflow Default workflow, editable Closed status [ 12566155 ] jira [ 12581540 ]
        Mark Thomas made changes -
        Workflow jira [ 12446416 ] Default workflow, editable Closed status [ 12566155 ]
        Eric Charles made changes -
        Status Open [ 1 ] Closed [ 6 ]
        Resolution Fixed [ 1 ]
        Hide
        Eric Charles added a comment -

        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>

        Show
        Eric Charles added a comment - 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>
        Eric Charles made changes -
        Assignee Norman Maurer [ norman ] Eric Charles [ eric@apache.org ]
        Norman Maurer made changes -
        Fix Version/s 3.0-M1 [ 12314294 ]
        Norman Maurer made changes -
        Field Original Value New Value
        Assignee Norman Maurer [ norman ]
        Daniel Dettlaff created issue -

          People

          • Assignee:
            Eric Charles
            Reporter:
            Daniel Dettlaff
          • Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Time Tracking

              Estimated:
              Original Estimate - 20m
              20m
              Remaining:
              Remaining Estimate - 20m
              20m
              Logged:
              Time Spent - Not Specified
              Not Specified

                Development