Announcement

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

    Enhancement suggestion / bug: caseSensitive attibute for hasRelatedRecord-validator

    Hi Isomorphic,

    like here (and similar here) I have an enhancement for 12.0p hasRelatedRecord-validator, which is actually more of a bug.

    hasRelatedRecord-validator does not support a caseSensitive="true"-attribute.
    Queries issued for it are always like this: LOWER(T_SETTINGS_IMPORT_FOA_MAP.TRANSLATION)='xyz') (you do a Java String.toLowerCase() on the 'xyz').

    There was a similar issue at isUnique here, so the change might actually be pretty easy.

    The problem is that as you write in the docs: "The primary purpose of declaring this validation explicitly is to provide clear, friendly error messages for use cases such as BatchUploader, where values aren't individually chosen by the user.".
    Most of the time, this foreignKey-relation, that the validator should check before the insert and output a user friendly error message for, is also secured by a foreignKey-constraint in the DB.
    As long as involved columns are integer, a lower (which by the way does not happen here) would not hurt, but with case-insensitivity, the SELECT might succeed, no error is displayed and then the following insert fails, as the DB of course does not find a matching key in the parent table, effectively making BatchUploader rollback and fail.

    Additionally, such a column will normally be indexed on the parent if the developer knows that there is a lookup coming. Having lower() will force a table scan instead. Depending on table size, this might be a real issue (imagine a real world supplyitem.SKU, which is type="text").

    I'm not sure if the default should already be caseSensitive="true" (perhaps not because of backwards compatibility), but the attribute should definitely exist.

    Best regards
    Blama

    #2
    Hi Isomorphic,

    actually, this also applies for the BatchUploader-lookup query, which also uses LOWER(). This should be configurable as well, for similar reasons as above.
    Case-insensitivity might be a convenience in some cases, but a problem in other cases.
    Also, the index-lookup vs full-table-scan issue is present here as well.

    Best regards
    Blama

    Comment


      #3
      These are implemented, see docs for hasRelatedRecord validator and new batchUploadCaseSensitive DSField attribute.

      Comment


        #4
        Hi Isomorphic,

        thanks a lot, these will help.
        Short docs remark: batchUploadCaseSensitive lists a default in the text (false) and a different one below (null).

        Thank you & Best regards
        Blama

        Comment


          #5
          Hi Isomorphic,

          using v12.0p_2019-07-03 this is working as expected.

          Thanks a lot,
          Blama

          Comment

          Working...
          X