Case Insensitive search on Primary key

[Ashish Vijaywargiya-5]

Do we have some settings in OFBiz so that we could perform case
insensitive search on primary key?

To elaborate more on this:

Suppose I have a product GZ_2644.
Our target is that user should be navigated to EditProduct page
(https://localhost:8443/catalog/control/EditProduct) if user provides
any of the Product_Id shown below:
GZ_2644 or gz_2644 or Gz_2644 or gZ_2644. And then on saving it will
take GZ_2644 as the product_id and will save the values if anything is
updated on EditProduct page.

And suppose if I am adding GZ_2644 in the OrderView screen as the new
line item by typing gz_2644 then it should not give any error & will add
product with id GZ_2644 in the Order.

AFAIK these are database specific settings but I am not able to figure
solve this issue.
Anyone having experience with MySql or Postgres in this area?

Any help on this would be greatly appreciated.
Thanks !

--
Ashish

[David E. Jones-2]

These are application operations and not database settings. The  
columns in question are case-sensitive, but what you've described  
could certainly be implemented as part of the UI.

If this goes back into OFBiz make sure to keep in mind that when doing  
a case-insensitive search you may end up with multiple results and  
then one must be selected in order to look at a specific product (I  
guess that sounds obvious now that I write it, but it implies a  
different flow than when a case-sensitive ID is specified). If the UI  
is already doing a search and not a lookup by PK then it would flow  
the same, but things currently assuming an exact PK would need a  
different user flow.

-David


On Jun 20, 2009, at 9:25 AM, Ashish Vijaywargiya wrote:

> Do we have some settings in OFBiz so that we could perform case  
> insensitive search on primary key?
>
> To elaborate more on this:
>
> Suppose I have a product GZ_2644.
> Our target is that user should be navigated to EditProduct page (https://localhost:8443/catalog/control/EditProduct 
> ) if user provides any of the Product_Id shown below:
> GZ_2644 or gz_2644 or Gz_2644 or gZ_2644. And then on saving it will  
> take GZ_2644 as the product_id and will save the values if anything  
> is updated on EditProduct page.
>
> And suppose if I am adding GZ_2644 in the OrderView screen as the  
> new line item by typing gz_2644 then it should not give any error &  
> will add product with id GZ_2644 in the Order.
>
> AFAIK these are database specific settings but I am not able to  
> figure solve this issue.
> Anyone having experience with MySql or Postgres in this area?
>
> Any help on this would be greatly appreciated.
> Thanks !
>
> --
> Ashish
>

[Harmeet Bedi]

From: "Ashish Vijaywargiya" <[hidden email]>
> Anyone having experience with MySql or Postgres in this area?


Ashish, I had to address this with postgres.

Did this in EntityOperator.

    public static final EntityComparisonOperator LIKE = new EntityComparisonOperator(ID_LIKE, "LIKE") {
        public boolean compare(Comparable lhs, Object rhs) { return EntityComparisonOperator.compareLike(lhs, rhs); }
        protected void makeRHSWhereString(ModelEntity entity, List<EntityConditionParam> entityConditionParams, StringBuilder sb, ModelField field, Object rhs, DatasourceInfo datasourceInfo) {
        // HBNOTE: fix for postgres compatibility. will break other databases. Should conditionalize
        sb.append(' ').append("ILIKE").append(' ');
            makeRHSWhereStringValue(entity, entityConditionParams, sb, field, rhs, datasourceInfo);
        }
    };


basically EntityOperator is where operations like join, like etc are mapped from ofbiz entity syntax to db syntax. Needed to interpret all 'like' clause operations to postgres 'ilike' (case insesitive like), so mapped it.

It would be nice if ofbiz had a db.properties where one could switch interpretation. Some dbs like SQL server are insensitive while others like postgres are case sensitive. Search from end user perspective is typically always case insensitive, so the need to change mapping to 'ilike' equivalent.

Harmeet



----- Original Message -----
From: "Ashish Vijaywargiya" <[hidden email]>
To: "OFBiz User ML" <[hidden email]>
Sent: Saturday, June 20, 2009 11:25:35 AM GMT -05:00 US/Canada Eastern
Subject: Case Insensitive search on Primary key

Do we have some settings in OFBiz so that we could perform case
insensitive search on primary key?

To elaborate more on this:

Suppose I have a product GZ_2644.
Our target is that user should be navigated to EditProduct page
(https://localhost:8443/catalog/control/EditProduct) if user provides
any of the Product_Id shown below:
GZ_2644 or gz_2644 or Gz_2644 or gZ_2644. And then on saving it will
take GZ_2644 as the product_id and will save the values if anything is
updated on EditProduct page.

And suppose if I am adding GZ_2644 in the OrderView screen as the new
line item by typing gz_2644 then it should not give any error & will add
product with id GZ_2644 in the Order.

AFAIK these are database specific settings but I am not able to figure
solve this issue.
Anyone having experience with MySql or Postgres in this area?

Any help on this would be greatly appreciated.
Thanks !

--
Ashish

[Ashish Vijaywargiya]

Thanks Harmeet & David for your comments.
I will get back to you guys if I need more info.

--
Ashish

On Sun, Jun 21, 2009 at 2:01 AM, Harmeet Bedi <[hidden email]>wrote:

> From: "Ashish Vijaywargiya" <[hidden email]>
> > Anyone having experience with MySql or Postgres in this area?
>
>
> Ashish, I had to address this with postgres.
>
> Did this in EntityOperator.
>
>    public static final EntityComparisonOperator LIKE = new
> EntityComparisonOperator(ID_LIKE, "LIKE") {
>        public boolean compare(Comparable lhs, Object rhs) { return
> EntityComparisonOperator.compareLike(lhs, rhs); }
>        protected void makeRHSWhereString(ModelEntity entity,
> List<EntityConditionParam> entityConditionParams, StringBuilder sb,
> ModelField field, Object rhs, DatasourceInfo datasourceInfo) {
>                // HBNOTE: fix for postgres compatibility. will break other
> databases. Should conditionalize
>                sb.append(' ').append("ILIKE").append(' ');
>            makeRHSWhereStringValue(entity, entityConditionParams, sb,
> field, rhs, datasourceInfo);
>        }
>    };
>
>
> basically EntityOperator is where operations like join, like etc are mapped
> from ofbiz entity syntax to db syntax. Needed to interpret all 'like' clause
> operations to postgres 'ilike' (case insesitive like), so mapped it.
>
> It would be nice if ofbiz had a db.properties where one could switch
> interpretation. Some dbs like SQL server are insensitive while others like
> postgres are case sensitive. Search from end user perspective is typically
> always case insensitive, so the need to change mapping to 'ilike'
> equivalent.
>
> Harmeet
>
>
>
> ----- Original Message -----
> From: "Ashish Vijaywargiya" <[hidden email]>
> To: "OFBiz User ML" <[hidden email]>
> Sent: Saturday, June 20, 2009 11:25:35 AM GMT -05:00 US/Canada Eastern
> Subject: Case Insensitive search on Primary key
>
> Do we have some settings in OFBiz so that we could perform case
> insensitive search on primary key?
>
> To elaborate more on this:
>
> Suppose I have a product GZ_2644.
> Our target is that user should be navigated to EditProduct page
> (https://localhost:8443/catalog/control/EditProduct) if user provides
> any of the Product_Id shown below:
> GZ_2644 or gz_2644 or Gz_2644 or gZ_2644. And then on saving it will
> take GZ_2644 as the product_id and will save the values if anything is
> updated on EditProduct page.
>
> And suppose if I am adding GZ_2644 in the OrderView screen as the new
> line item by typing gz_2644 then it should not give any error & will add
> product with id GZ_2644 in the Order.
>
> AFAIK these are database specific settings but I am not able to figure
> solve this issue.
> Anyone having experience with MySql or Postgres in this area?
>
> Any help on this would be greatly appreciated.
> Thanks !
>
> --
> Ashish
>
>