Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
Clear All
new posts

    Unique validator disallows case change (when lower(oldVal)=lower(newVal))

    Hi Isomorphic,

    please see this request where I want to change the value of a field with a unique validator from upper case to lower case (using current 5.0p):

    Server log:
    Code:
    2015-05-13 22:28:18,282 [http-bio-8080-exec-8] DEBUG org.apache.catalina.authenticator.FormAuthenticator- Already authenticated 'Administrator'
    === 2015-05-13 22:28:18,283 [ec-8] INFO  RequestContext - URL: '/lms/lms/sc/IDACall', User-Agent: 'Mozilla/5.0 (Windows NT 6.3; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0': Moz (Gecko) with Accept-Encoding header
    === 2015-05-13 22:28:18,283 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: host:localhost:8080
    === 2015-05-13 22:28:18,283 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: user-agent:Mozilla/5.0 (Windows NT 6.3; WOW64; rv:26.0) Gecko/20100101 Firefox/26.0
    === 2015-05-13 22:28:18,283 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: accept:text/html,application/xhtml+xml,application/xml;q=0.9,*/*;q=0.8
    === 2015-05-13 22:28:18,283 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: accept-language:de-de,de;q=0.8,en-us;q=0.5,en;q=0.3
    === 2015-05-13 22:28:18,283 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: accept-encoding:gzip, deflate
    === 2015-05-13 22:28:18,283 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: dnt:1
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: content-type:application/x-www-form-urlencoded; charset=UTF-8
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: referer:http://localhost:8080/lms/
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: content-length:1823
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: cookie:JSESSIONID=E2C5E0F7D7448ADD6BB327E9D24BBC5F; isc_cState=ready; GLog=%7B%0D%20%20%20%20trackRPC%3Atrue%2C%20%0D%20%20%20%20pageGUID%3A%2279BA4AF7-C839-45EF-9B61-ADA5F0D40511%22%2C%20%0D%20%20%20%20priorityDefaults%3A%7B%0D%20%20%20%20%20%20%20%20sgwtInternal%3A1%0D%20%20%20%20%7D%2C%20%0D%20%20%20%20defaultPriority%3A3%2C%20%0D%20%20%20%20left%3A494%2C%20%0D%20%20%20%20top%3A299%2C%20%0D%20%20%20%20width%3A1468%2C%20%0D%20%20%20%20height%3A490%0D%7D
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: connection:keep-alive
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: pragma:no-cache
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - Header Name:Value pair: cache-control:no-cache
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - session exists: E2C5E0F7D7448ADD6BB327E9D24BBC5F
    === 2015-05-13 22:28:18,284 [ec-8] DEBUG LMSIDACall - remote user: Administrator
    === 2015-05-13 22:28:18,291 [ec-8] DEBUG RPCManager - Processing 1 requests.
    === 2015-05-13 22:28:18,292 [ec-8] DEBUG RPCManager - Request #1 (DSRequest) payload: {
        criteria:{
            ID:21
        },
        values:{
            ID:21,
            [B]SHORTNAME:"abc"[/B]
        },
        operationConfig:{
            dataSource:"T_CATEGORY1",
            repo:null,
            operationType:"update",
            textMatchStyle:"exact"
        },
        componentId:"isc_SettingVLayout_CategorySettings$4_0",
        appID:"builtinApplication",
        operation:"T_CATEGORY1_update",
        oldValues:{
            POSITION_DND:1,
            MODIFIED_AT:new Date(1431548884000),
            CREATED_BY:"1",
            NAME:"test",
            CREATED_AT:new Date(1416586020000),
            ID:21,
            [B]SHORTNAME:"ABC"[/B],
            MODIFIED_BY:"1",
            AVAILABLE:true,
            POSITION:1,
            TENANT_ID:1
        }
    }
    === 2015-05-13 22:28:18,292 [ec-8] INFO  LMSIDACall - Performing 1 operation(s)
    === 2015-05-13 22:28:18,293 [ec-8] DEBUG AppBase - [builtinApplication.T_CATEGORY1_update] No userTypes defined, allowing anyone access to all operations for this application
    === 2015-05-13 22:28:18,293 [ec-8] DEBUG AppBase - [builtinApplication.T_CATEGORY1_update] No public zero-argument method named '_T_CATEGORY1_update' found, performing generic datasource operation
    === 2015-05-13 22:28:18,294 [ec-8] DEBUG AppBase - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
    === 2015-05-13 22:28:18,294 [ec-8] DEBUG AppBase - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
    === 2015-05-13 22:28:18,294 [ec-8] INFO  SQLDataSource - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] Performing fetch operation with
    	criteria: {ID:21,TENANT_ID:1}	values: {ID:21,TENANT_ID:1}
    === 2015-05-13 22:28:18,295 [ec-8] INFO  SQLDataSource - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
    === 2015-05-13 22:28:18,296 [ec-8] INFO  SQLDataSource - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] 1563: Executing SQL query on 'Oracle': SELECT T_CATEGORY1.ID, T_CATEGORY1.TENANT_ID, T_CATEGORY1.CREATED_BY, T_CATEGORY1.CREATED_AT, T_CATEGORY1.MODIFIED_BY, T_CATEGORY1.MODIFIED_AT, T_CATEGORY1.SHORTNAME, T_CATEGORY1.NAME, T_CATEGORY1.DESCRIPTION, T_CATEGORY1.POSITION, T_CATEGORY1.POSITION AS POSITION_DND, T_CATEGORY1.AVAILABLE FROM T_CATEGORY1 WHERE (T_CATEGORY1.ID=21 AND T_CATEGORY1.TENANT_ID=1)
    === 2015-05-13 22:28:18,297 [ec-8] DEBUG SQLConnectionManager - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] Borrowed connection '392896648'
    === 2015-05-13 22:28:18,297 [ec-8] DEBUG SQLDriver - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] About to execute SQL query in 'Oracle' using connection '392896648'
    === 2015-05-13 22:28:18,297 [ec-8] INFO  SQLDriver - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] Executing SQL query on 'Oracle': SELECT T_CATEGORY1.ID, T_CATEGORY1.TENANT_ID, T_CATEGORY1.CREATED_BY, T_CATEGORY1.CREATED_AT, T_CATEGORY1.MODIFIED_BY, T_CATEGORY1.MODIFIED_AT, T_CATEGORY1.SHORTNAME, T_CATEGORY1.NAME, T_CATEGORY1.DESCRIPTION, T_CATEGORY1.POSITION, T_CATEGORY1.POSITION AS POSITION_DND, T_CATEGORY1.AVAILABLE FROM T_CATEGORY1 WHERE (T_CATEGORY1.ID=21 AND T_CATEGORY1.TENANT_ID=1)
    === 2015-05-13 22:28:18,300 [ec-8] INFO  DSResponse - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] DSResponse: List with 1 items
    === 2015-05-13 22:28:18,300 [ec-8] DEBUG SQLDriver - [builtinApplication.T_CATEGORY1_update] Freeing SQLDriver dbConnection 392896648
    === 2015-05-13 22:28:18,301 [ec-8] DEBUG SQLConnectionManager - [builtinApplication.T_CATEGORY1_update] About to close connection with hashcode "392896648"
    === 2015-05-13 22:28:18,306 [ec-8] DEBUG AppBase - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] No userTypes defined, allowing anyone access to all operations for this application
    === 2015-05-13 22:28:18,307 [ec-8] DEBUG AppBase - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] No public zero-argument method named '_null' found, performing generic datasource operation
    === 2015-05-13 22:28:18,307 [ec-8] INFO  SQLDataSource - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] Performing fetch operation with
    	outputs: [ID, SHORTNAME]	criteria: {SHORTNAME:"abc",TENANT_ID:1}	values: {SHORTNAME:"abc",TENANT_ID:1}
    === 2015-05-13 22:28:18,308 [ec-8] INFO  SQLDataSource - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] derived query: SELECT $defaultSelectClause FROM $defaultTableClause WHERE $defaultWhereClause
    [B]=== 2015-05-13 22:28:18,309 [ec-8] INFO  SQLDataSource - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] 1643: Executing SQL query on 'Oracle': SELECT T_CATEGORY1.ID, T_CATEGORY1.SHORTNAME FROM T_CATEGORY1 WHERE (LOWER(T_CATEGORY1.SHORTNAME)='abc' AND T_CATEGORY1.TENANT_ID=1)[/B]
    === 2015-05-13 22:28:18,310 [ec-8] DEBUG SQLConnectionManager - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] Borrowed connection '392896648'
    === 2015-05-13 22:28:18,310 [ec-8] DEBUG SQLTransaction - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] Started new Oracle transaction "392896648"
    === 2015-05-13 22:28:18,310 [ec-8] DEBUG SQLDriver - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] About to execute SQL query in 'Oracle' using connection '392896648'
    === 2015-05-13 22:28:18,310 [ec-8] INFO  SQLDriver - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] Executing SQL query on 'Oracle': SELECT T_CATEGORY1.ID, T_CATEGORY1.SHORTNAME FROM T_CATEGORY1 WHERE (LOWER(T_CATEGORY1.SHORTNAME)='abc' AND T_CATEGORY1.TENANT_ID=1)
    === 2015-05-13 22:28:18,312 [ec-8] INFO  DSResponse - [builtinApplication.T_CATEGORY1_update, builtinApplication.null] DSResponse: List with 1 items
    === 2015-05-13 22:28:18,314 [ec-8] DEBUG DefaultValidators - [builtinApplication.T_CATEGORY1_update] unique check: pkField: ID, old PK: 21, new PK: 21
    === 2015-05-13 22:28:18,314 [ec-8] DEBUG DefaultValidators - [builtinApplication.T_CATEGORY1_update] unique check: unique field: SHORTNAME, old unique value: ABC, new unique value: abc
    === 2015-05-13 22:28:18,315 [ec-8] DEBUG ValidationContext - [builtinApplication.T_CATEGORY1_update] Adding validation errors at path '/T_CATEGORY1/SHORTNAME/SHORTNAME': {errorMessage=Der Kurzname wird bereits verwendet!}
    === 2015-05-13 22:28:18,315 [ec-8] INFO  Validation - [builtinApplication.T_CATEGORY1_update] Validation error: [
        {
            SHORTNAME:{
                errorMessage:"Der Kurzname wird bereits verwendet!"
            }
        }
    ]
    === 2015-05-13 22:28:18,316 [ec-8] DEBUG RPCManager - Content type for RPC transaction: text/plain; charset=UTF-8
    === 2015-05-13 22:28:18,316 [ec-8] DEBUG SQLTransaction - Rolling back Oracle transaction "392896648"
    === 2015-05-13 22:28:18,317 [ec-8] DEBUG RPCManager - non-DMI response, dropExtraFields: false
    === 2015-05-13 22:28:18,317 [ec-8] DEBUG SQLTransaction - getConnection() found transactional connection for Oracle with hashcode "392896648"
    === 2015-05-13 22:28:18,317 [ec-8] DEBUG SQLTransaction - Ending Oracle transaction "392896648"
    === 2015-05-13 22:28:18,318 [ec-8] DEBUG SQLConnectionManager - About to close connection with hashcode "392896648"
    === 2015-05-13 22:28:18,319 [ec-8] INFO  Compression - /lms/lms/sc/IDACall: 236 -> 204 bytes
    Important rows are in bold.

    .ds.xml excerpt:
    Code:
    <field primaryKey="true" hidden="true" name="ID" type="sequence" />
    <field name="SHORTNAME" title="Kurzname" length="30" type="text" escapeHTML="true" required="true">
    	<validators>
    		<validator type="isUnique" errorMessage="Der Kurzname wird bereits verwendet!" />
    		<validator type="regexp" expression="/^(?!test$)/i" errorMessage="&quot;Dummy&quot; darf nicht als Kurzname verwendet werden!" />
    	</validators>
    </field>
    The problem with the uniqueness-check-SELECT statement is that it is missing a condition "AND pkField <> pkValue", here "AND id <> 21".

    This is not the same feature you added here - a case-change of the value should be possible also for a case-insensitive validator like the current 5.0p isUnique-validator.

    Currently, to change the casing, one has to change ABC -> abc1 -> abc.

    Best regards
    Blama

    #2
    This issue has now been resolved in 5.0 and later and will be available in the next nightly build.

    Regards,
    Isomorphic Software

    Comment


      #3
      Hi Isomorphic,

      this is solved for me using v10.0p_2015-05-19.

      Thank you and best regards
      Blama

      Comment


        #4
        Hi Isomorphic,

        this is not solved in every case. I'm using v10.0p_2015-08-24/PowerEdition Deployment now.
        Please see the testcase from this thread and repeat the steps. The unique-check SELECT is:
        Code:
        SELECT supplyItem.itemID, supplyItem.SKU FROM supplyItem WHERE (LOWER(supplyItem.SKU)='45300')
        (It is missing the AND supplyItem.itemID <> 1)

        Best regards
        Blama
        Last edited by Blama; 23 Sep 2015, 08:34. Reason: Made error more clear.

        Comment


          #5
          See response in the other thread addressing the unique-check query.

          Regards,
          Isomorphic Software

          Comment


            #6
            Hi Isomorphic,

            creating a testcase I found another situation where this clause is not applied.
            See the testcase (using v10.1p_2016-03-27) in this thread.

            The button "Reset Ralph Brogan via PK" works as expected and generates this SELECT:
            Code:
             SELECT   employeeTable.Name,
                employeeTable.EmployeeId
                 FROM employeeTable
                WHERE((LOWER(employeeTable.Email) = LOWER('rbrogan@server.com')
                AND employeeTable.Email          IS NOT NULL)
                AND(LOWER(employeeTable.Name)     = LOWER('ralph brogan')
                AND employeeTable.Name           IS NOT NULL)
                AND((employeeTable.EmployeeId    <> 192
                OR employeeTable.EmployeeId      IS NULL)))
            The button "Rename Ralph Brogan (Good)" does not work as expected and generates this SELECT:
            Code:
             SELECT   employeeTable.Name,
                employeeTable.EmployeeId
                 FROM employeeTable
                WHERE((LOWER(employeeTable.Email) = LOWER('foobar')
                AND employeeTable.Email          IS NOT NULL)
                AND(LOWER(employeeTable.Name)     = LOWER('foobar')
                AND employeeTable.Name           IS NOT NULL)
                [B]AND((employeeTable.EmployeeId    IS NOT NULL))) --here the "<>" is missing, also it checks for NOT NULL.[/B]
            Best regards
            Blama

            Comment


              #7
              This issue is fixed and is available in nightly builds since Apr 11 (Monday).

              Comment

              Working...
              X