You are viewing a plain text version of this content. The canonical link for it is here.

Posted to user@syncope.apache.org by Adrian Gonzalez <ad...@yahoo.fr> on 2016/11/21 11:59:16 UTC

User case insensitive search

Hello,
I'd like to execute a case insensitive search in Syncope.
i.e. querying all users with an attribute givenName that matches  'martin' would return :MartinmartinmArtin
Is there a way to do this with a Fiql search ?i.e. something like UPPER(givenName) = UPPER('martin') ?
If no are there other ways to do this ?If no, I can always store givenName attribute in 2 different attributes (i.e. the real givenName attribute and a givenNameCaseInsensitive attribute to store it in lowercase), but I would like to avoid it if possible.
Thanks,Adrian

Re: User case insensitive search

Posted by Francesco Chicchiriccò <il...@apache.org>.

On 24/11/2016 10:27, Adrian Gonzalez wrote:
> Thanks Francesco !
>
> It's a pleasure to work with you !

Same feeling here :-)

> And thanks once more for the help on fiqlParser, I was getting stuck !

No problems: without the changes pushed to CXF, the job was rather 
impossible.
But hey, here's the power of intra-ASF cooperation ;-)

Regards.

> ------------------------------------------------------------------------
> *De :* Francesco Chicchiricc� <il...@apache.org>
> *� :* user@syncope.apache.org
> *Envoy� le :* Jeudi 24 novembre 2016 9h15
> *Objet :* Re: User case insensitive search
>
> Hi all,
> as you might have already seen, the changes from SYNCOPE-971were 
> successfully incorporated into:
>
> * Syncope 1.2.10-SNAPSHOT
> * Syncope 2.0.2-SNAPSHOT
> * Syncope 2.1.0-SNAPSHOT
>
> and are also already available for demo at
>
> http://syncope-vm.apache.org:9080/syncope-console/
>
> Thanks again to Adrian for taking this forward: it's a long overdue 
> enhancement, whose modifications had impact at several layers: good job!
>
> Regards.
>
> On 21/11/2016 18:27, Francesco Chicchiricc� wrote:
> On 21/11/2016 18:20, Adrian Gonzalez wrote:
>> Hi Francesco,
>>
>> I've created this PR https://github.com/apache/syncope/pull/40.
>
> Hi Adrian, thanks for your contribution!
> I have already commented there.
>
>> Be aware that :
>>
>> 1. it's a global case insentive flag (and not a fiql operator).
>>  There were 2 reasons for this:
>>  * creating a custom fiql operator isn't possible for the moment 
>> without either modifying cxf fiql parser or writing another (which 
>> would be a pity).
>>  * I had some exchanges with Sergey on case-insensitivity search . 
>> His view on this case in/sensitivity issue is that it can be resolved 
>> by configuration (I took this approach - easier :) ).
>>    After looking a bit SCIM spec, 
>> https://tools.ietf.org/html/rfc7643, there's a lot (majority) of 
>> fields with caseExact=false. So on second thought I think too that we 
>> can live with a global flag.
>> Just let me know if this limitation is ok for you.
>
> Hum, I would rather prefer not being forced to globally decide how EQ 
> and LIKE comparisons are working for all search queries.
>
>> I not, we'll need to add some more exchanges with Sergey or you'll 
>> need to show me how you could use custom fiql operators, because I 
>> don't see them.
>>
>> 2. case insensitivity is only implemented for fiql search.
>> Let me know if you think that we need also implement it in other 
>> search (I think only groupDao.findByXXX, userDao.findByXXX would need).
>
> No need for this: such methods are only used internally.
>
>> Also just let me know if you see anything in this PR that's not ok 
>> for you !
>
> Please check the comments there.
> Regards.
>
>> ------------------------------------------------------------------------
>> *De :* Francesco Chicchiricc� <il...@apache.org> 
>> <ma...@apache.org>
>> *� :* user@syncope.apache.org <ma...@syncope.apache.org>
>> *Envoy� le :* Lundi 21 novembre 2016 16h31
>> *Objet :* Re: User case insensitive search
>>
>> On 21/11/2016 16:29, Francesco Chicchiricc� wrote:
>> On 21/11/2016 14:00, Francesco Chicchiricc� wrote:
>> On 21/11/2016 13:47, Adrian Gonzalez wrote:
>>> Hi Francesco,
>>>
>>> Cool, I've just created SYNCOPE-971.
>>>
>>> > the problem is that, as far as I can tell, such clauses are 
>>> DBMS-dependent.
>>> We can solve it using jpql LOWER ou UPPER function, i.e.
>>> LOWER(givenName) = LOWER('Martin')
>>>
>>> I can try to implement it, but I don't know for now if it is 
>>> possible to introduce either :
>>>  * custom operator in FIQL (i.e. some eqi operator like : givenName 
>>> eqi Martin)
>>>  * introduce custom functions in FIQL (i.e. uc(givenName) == uc(Martin))
>>
>> I would rather go with first (we also have other custom operators).
>>
>> Essentially, I see two separate tasks here:
>>
>> 1. extend FIQL, the query builders and the logic that transforms FIQL 
>> queries into the low-level search objects
>> 2. extend JPAAnySearchDAO to take care of such new EQI comparator, 
>> similarly to what is currently done at
>>
>> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java#L782-L790
>>
>> The only non-obvious problem I see is that we will need to make the 
>> new code RDBMS provider dependent.
>>
>> Sorry, totally missed your statement above about using JPQL's LOWER() 
>> or UPPER() - smart move ;-)
>>
>> Sorry again: the problem is that queries built by JPASearchDAO are 
>> native query, not JPQL.
>>
>>
>> Regards.
>>
>>>>> ------------------------------------------------------------------------
>>>>> *De :* Francesco Chicchiricc� <il...@apache.org> 
>>>>> <ma...@apache.org>
>>>>> *� :* user@syncope.apache.org <ma...@syncope.apache.org>
>>>>> *Envoy� le :* Lundi 21 novembre 2016 13h25
>>>>> *Objet :* Re: User case insensitive search
>>>>>
>>>>>
>>>>>
>>>>> On 21/11/2016 12:59, Adrian Gonzalez wrote:
>>>>>> Hello,
>>>>>>
>>>>>> I'd like to execute a case insensitive search in Syncope.
>>>>>>
>>>>>> i.e. querying all users with an attribute givenName that matches 
>>>>>>  'martin' would return :
>>>>>> Martin
>>>>>> martin
>>>>>> mArtin
>>>>>>
>>>>>> Is there a way to do this with a Fiql search ?
>>>>>> i.e. something like UPPER(givenName) = UPPER('martin') ?
>>>>>>
>>>>>> If no are there other ways to do this ?
>>>>>> If no, I can always store givenName attribute in 2 different 
>>>>>> attributes (i.e. the real givenName attribute and a 
>>>>>> givenNameCaseInsensitive attribute to store it in lowercase), but 
>>>>>> I would like to avoid it if possible.
>>>>>
>>>>> Hi Adrian,
>>>>> I confirm that currently it is *not* possible to perform case 
>>>>> insensitive searches.
>>>>>
>>>>> Nevertheless, it would be a nice feature to add: essentially, one 
>>>>> should be adding 'lowercase()' clauses to the native SQL queries 
>>>>> manage by [1]: the problem is that, as far as I can tell, such 
>>>>> clauses are DBMS-dependent.
>>>>>
>>>>> Would you mind creating an improvement on JIRA for this?
>>>>> Naturally, contributions are welcome ;-)
>>>>>
>>>>> Regards.
>>>>>
>>>>> [1] 
>>>>> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

-- 
Francesco Chicchiricc�

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Adrian Gonzalez <ad...@yahoo.fr>.

Thanks Francesco !
It's a pleasure to work with you !And thanks once more for the help on fiqlParser, I was getting stuck !
Cheers,Adrian

De : Francesco Chicchiriccò <il...@apache.org>
À : user@syncope.apache.org
Envoyé le : Jeudi 24 novembre 2016 9h15
Objet : Re: User case insensitive search

Hi all,
as you might have already seen, the changes from SYNCOPE-971were successfully incorporated into:

* Syncope 1.2.10-SNAPSHOT
* Syncope 2.0.2-SNAPSHOT
* Syncope 2.1.0-SNAPSHOT

and are also already available for demo at

http://syncope-vm.apache.org:9080/syncope-console/

Thanks again to Adrian for taking this forward: it's a long overdue enhancement, whose modifications had impact at several layers: good job!

Regards.

On 21/11/2016 18:27, Francesco Chicchiriccò wrote:

On 21/11/2016 18:20, Adrian Gonzalez wrote:

Hi Francesco,
I've created this PR https://github.com/apache/syncope/pull/40.

Hi Adrian, thanks for your contribution!
I have already commented there.

Be aware that :
1. it's a global case insentive flag (and not a fiql operator). There were 2 reasons for this: * creating a custom fiql operator isn't possible for the moment without either modifying cxf fiql parser or writing another (which would be a pity). * I had some exchanges with Sergey on case-insensitivity search . His view on this case in/sensitivity issue is that it can be resolved by configuration (I took this approach - easier :) ). After looking a bit SCIM spec, https://tools.ietf.org/html/rfc7643, there's a lot (majority) of fields with caseExact=false. So on second thought I think too that we can live with a global flag. Just let me know if this limitation is ok for you.

Hum, I would rather prefer not being forced to globally decide how EQ and LIKE comparisons are working for all search queries.

I not, we'll need to add some more exchanges with Sergey or you'll need to show me how you could use custom fiql operators, because I don't see them.
2. case insensitivity is only implemented for fiql search. Let me know if you think that we need also implement it in other search (I think only groupDao.findByXXX, userDao.findByXXX would need).

No need for this: such methods are only used internally.

Also just let me know if you see anything in this PR that's not ok for you !

Please check the comments there.
Regards.

De : Francesco Chicchiriccò <il...@apache.org>
À : user@syncope.apache.org
Envoyé le : Lundi 21 novembre 2016 16h31
Objet : Re: User case insensitive search

On 21/11/2016 16:29, Francesco Chicchiriccò wrote:

On 21/11/2016 14:00, Francesco Chicchiriccò wrote:

On 21/11/2016 13:47, Adrian Gonzalez wrote:

Hi Francesco,
Cool, I've just created SYNCOPE-971.
> the problem is that, as far as I can tell, such clauses are DBMS-dependent.
We can solve it using jpql LOWER ou UPPER function, i.e.
LOWER(givenName) = LOWER('Martin')

I can try to implement it, but I don't know for now if it is possible to introduce either : * custom operator in FIQL (i.e. some eqi operator like : givenName eqi Martin) * introduce custom functions in FIQL (i.e. uc(givenName) == uc(Martin))

I would rather go with first (we also have other custom operators).

Essentially, I see two separate tasks here:

1. extend FIQL, the query builders and the logic that transforms FIQL queries into the low-level search objects
2. extend JPAAnySearchDAO to take care of such new EQI comparator, similarly to what is currently done at

https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java#L782-L790

The only non-obvious problem I see is that we will need to make the new code RDBMS provider dependent.

Sorry, totally missed your statement above about using JPQL's LOWER() or UPPER() - smart move ;-)

Sorry again: the problem is that queries built by JPASearchDAO are native query, not JPQL.

Regards.

De : Francesco Chicchiriccò <il...@apache.org>
À : user@syncope.apache.org
Envoyé le : Lundi 21 novembre 2016 13h25
Objet : Re: User case insensitive search

On 21/11/2016 12:59, Adrian Gonzalez wrote:

Hello,
I'd like to execute a case insensitive search in Syncope.
i.e. querying all users with an attribute givenName that matches 'martin' would return : Martin martin mArtin
Is there a way to do this with a Fiql search ? i.e. something like UPPER(givenName) = UPPER('martin') ?
If no are there other ways to do this ? If no, I can always store givenName attribute in 2 different attributes (i.e. the real givenName attribute and a givenNameCaseInsensitive attribute to store it in lowercase), but I would like to avoid it if possible.

Hi Adrian,
I confirm that currently it is *not* possible to perform case insensitive searches.

Nevertheless, it would be a nice feature to add: essentially, one should be adding 'lowercase()' clauses to the native SQL queries manage by [1]: the problem is that, as far as I can tell, such clauses are DBMS-dependent.

Would you mind creating an improvement on JIRA for this?
Naturally, contributions are welcome ;-)

Regards.

[1] https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

--
Francesco Chicchiriccò

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Francesco Chicchiriccò <il...@apache.org>.

Hi all,
as you might have already seen, the changes from SYNCOPE-971were 
successfully incorporated into:

* Syncope 1.2.10-SNAPSHOT
* Syncope 2.0.2-SNAPSHOT
* Syncope 2.1.0-SNAPSHOT

and are also already available for demo at

http://syncope-vm.apache.org:9080/syncope-console/

Thanks again to Adrian for taking this forward: it's a long overdue 
enhancement, whose modifications had impact at several layers: good job!

Regards.

On 21/11/2016 18:27, Francesco Chicchiricc� wrote:
> On 21/11/2016 18:20, Adrian Gonzalez wrote:
>> Hi Francesco,
>>
>> I've created this PR https://github.com/apache/syncope/pull/40.
>
> Hi Adrian, thanks for your contribution!
> I have already commented there.
>
>> Be aware that :
>>
>> 1. it's a global case insentive flag (and not a fiql operator).
>>  There were 2 reasons for this:
>>  * creating a custom fiql operator isn't possible for the moment 
>> without either modifying cxf fiql parser or writing another (which 
>> would be a pity).
>>  * I had some exchanges with Sergey on case-insensitivity search . 
>> His view on this case in/sensitivity issue is that it can be resolved 
>> by configuration (I took this approach - easier :) ).
>>    After looking a bit SCIM spec, 
>> https://tools.ietf.org/html/rfc7643, there's a lot (majority) of 
>> fields with caseExact=false. So on second thought I think too that we 
>> can live with a global flag.
>> Just let me know if this limitation is ok for you.
>
> Hum, I would rather prefer not being forced to globally decide how EQ 
> and LIKE comparisons are working for all search queries.
>
>> I not, we'll need to add some more exchanges with Sergey or you'll 
>> need to show me how you could use custom fiql operators, because I 
>> don't see them.
>>
>> 2. case insensitivity is only implemented for fiql search.
>> Let me know if you think that we need also implement it in other 
>> search (I think only groupDao.findByXXX, userDao.findByXXX would need).
>
> No need for this: such methods are only used internally.
>
>> Also just let me know if you see anything in this PR that's not ok 
>> for you !
>
> Please check the comments there.
> Regards.
>
>> ------------------------------------------------------------------------
>> *De :* Francesco Chicchiricc� <il...@apache.org>
>> *� :* user@syncope.apache.org
>> *Envoy� le :* Lundi 21 novembre 2016 16h31
>> *Objet :* Re: User case insensitive search
>>
>> On 21/11/2016 16:29, Francesco Chicchiricc� wrote:
>> On 21/11/2016 14:00, Francesco Chicchiricc� wrote:
>> On 21/11/2016 13:47, Adrian Gonzalez wrote:
>>> Hi Francesco,
>>>
>>> Cool, I've just created SYNCOPE-971.
>>>
>>> > the problem is that, as far as I can tell, such clauses are 
>>> DBMS-dependent.
>>> We can solve it using jpql LOWER ou UPPER function, i.e.
>>> LOWER(givenName) = LOWER('Martin')
>>>
>>> I can try to implement it, but I don't know for now if it is 
>>> possible to introduce either :
>>>  * custom operator in FIQL (i.e. some eqi operator like : givenName 
>>> eqi Martin)
>>>  * introduce custom functions in FIQL (i.e. uc(givenName) == uc(Martin))
>>
>> I would rather go with first (we also have other custom operators).
>>
>> Essentially, I see two separate tasks here:
>>
>> 1. extend FIQL, the query builders and the logic that transforms FIQL 
>> queries into the low-level search objects
>> 2. extend JPAAnySearchDAO to take care of such new EQI comparator, 
>> similarly to what is currently done at
>>
>> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java#L782-L790
>>
>> The only non-obvious problem I see is that we will need to make the 
>> new code RDBMS provider dependent.
>>
>> Sorry, totally missed your statement above about using JPQL's LOWER() 
>> or UPPER() - smart move ;-)
>>
>> Sorry again: the problem is that queries built by JPASearchDAO are 
>> native query, not JPQL.
>>
>>
>> Regards.
>>
>>>>> ------------------------------------------------------------------------
>>>>> *De :* Francesco Chicchiricc� <il...@apache.org> 
>>>>> <ma...@apache.org>
>>>>> *� :* user@syncope.apache.org <ma...@syncope.apache.org>
>>>>> *Envoy� le :* Lundi 21 novembre 2016 13h25
>>>>> *Objet :* Re: User case insensitive search
>>>>>
>>>>>
>>>>>
>>>>> On 21/11/2016 12:59, Adrian Gonzalez wrote:
>>>>>> Hello,
>>>>>>
>>>>>> I'd like to execute a case insensitive search in Syncope.
>>>>>>
>>>>>> i.e. querying all users with an attribute givenName that matches 
>>>>>>  'martin' would return :
>>>>>> Martin
>>>>>> martin
>>>>>> mArtin
>>>>>>
>>>>>> Is there a way to do this with a Fiql search ?
>>>>>> i.e. something like UPPER(givenName) = UPPER('martin') ?
>>>>>>
>>>>>> If no are there other ways to do this ?
>>>>>> If no, I can always store givenName attribute in 2 different 
>>>>>> attributes (i.e. the real givenName attribute and a 
>>>>>> givenNameCaseInsensitive attribute to store it in lowercase), but 
>>>>>> I would like to avoid it if possible.
>>>>>
>>>>> Hi Adrian,
>>>>> I confirm that currently it is *not* possible to perform case 
>>>>> insensitive searches.
>>>>>
>>>>> Nevertheless, it would be a nice feature to add: essentially, one 
>>>>> should be adding 'lowercase()' clauses to the native SQL queries 
>>>>> manage by [1]: the problem is that, as far as I can tell, such 
>>>>> clauses are DBMS-dependent.
>>>>>
>>>>> Would you mind creating an improvement on JIRA for this?
>>>>> Naturally, contributions are welcome ;-)
>>>>>
>>>>> Regards.
>>>>>
>>>>> [1] 
>>>>> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

-- 
Francesco Chicchiricc�

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Francesco Chicchiriccò <il...@apache.org>.

On 21/11/2016 18:20, Adrian Gonzalez wrote:
> Hi Francesco,
>
> I've created this PR https://github.com/apache/syncope/pull/40.

Hi Adrian, thanks for your contribution!
I have already commented there.

> Be aware that :
>
> 1. it's a global case insentive flag (and not a fiql operator).
>  There were 2 reasons for this:
>  * creating a custom fiql operator isn't possible for the moment 
> without either modifying cxf fiql parser or writing another (which 
> would be a pity).
>  * I had some exchanges with Sergey on case-insensitivity search . His 
> view on this case in/sensitivity issue is that it can be resolved by 
> configuration (I took this approach - easier :) ).
>    After looking a bit SCIM spec, https://tools.ietf.org/html/rfc7643, 
> there's a lot (majority) of fields with caseExact=false. So on second 
> thought I think too that we can live with a global flag.
> Just let me know if this limitation is ok for you.

Hum, I would rather prefer not being forced to globally decide how EQ 
and LIKE comparisons are working for all search queries.

> I not, we'll need to add some more exchanges with Sergey or you'll 
> need to show me how you could use custom fiql operators, because I 
> don't see them.
>
> 2. case insensitivity is only implemented for fiql search.
> Let me know if you think that we need also implement it in other 
> search (I think only groupDao.findByXXX, userDao.findByXXX would need).

No need for this: such methods are only used internally.

> Also just let me know if you see anything in this PR that's not ok for 
> you !

Please check the comments there.
Regards.

> ------------------------------------------------------------------------
> *De :* Francesco Chicchiriccò <il...@apache.org>
> *À :* user@syncope.apache.org
> *Envoyé le :* Lundi 21 novembre 2016 16h31
> *Objet :* Re: User case insensitive search
>
> On 21/11/2016 16:29, Francesco Chicchiriccò wrote:
> On 21/11/2016 14:00, Francesco Chicchiriccò wrote:
> On 21/11/2016 13:47, Adrian Gonzalez wrote:
>> Hi Francesco,
>>
>> Cool, I've just created SYNCOPE-971.
>>
>> > the problem is that, as far as I can tell, such clauses are 
>> DBMS-dependent.
>> We can solve it using jpql LOWER ou UPPER function, i.e.
>> LOWER(givenName) = LOWER('Martin')
>>
>> I can try to implement it, but I don't know for now if it is possible 
>> to introduce either :
>>  * custom operator in FIQL (i.e. some eqi operator like : givenName 
>> eqi Martin)
>>  * introduce custom functions in FIQL (i.e. uc(givenName) == uc(Martin))
>
> I would rather go with first (we also have other custom operators).
>
> Essentially, I see two separate tasks here:
>
> 1. extend FIQL, the query builders and the logic that transforms FIQL 
> queries into the low-level search objects
> 2. extend JPAAnySearchDAO to take care of such new EQI comparator, 
> similarly to what is currently done at
>
> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java#L782-L790
>
> The only non-obvious problem I see is that we will need to make the 
> new code RDBMS provider dependent.
>
> Sorry, totally missed your statement above about using JPQL's LOWER() 
> or UPPER() - smart move ;-)
>
> Sorry again: the problem is that queries built by JPASearchDAO are 
> native query, not JPQL.
>
>
> Regards.
>
>>>> ------------------------------------------------------------------------
>>>> *De :* Francesco Chicchiriccò <il...@apache.org> 
>>>> <ma...@apache.org>
>>>> *À :* user@syncope.apache.org <ma...@syncope.apache.org>
>>>> *Envoyé le :* Lundi 21 novembre 2016 13h25
>>>> *Objet :* Re: User case insensitive search
>>>>
>>>>
>>>>
>>>> On 21/11/2016 12:59, Adrian Gonzalez wrote:
>>>>> Hello,
>>>>>
>>>>> I'd like to execute a case insensitive search in Syncope.
>>>>>
>>>>> i.e. querying all users with an attribute givenName that matches 
>>>>>  'martin' would return :
>>>>> Martin
>>>>> martin
>>>>> mArtin
>>>>>
>>>>> Is there a way to do this with a Fiql search ?
>>>>> i.e. something like UPPER(givenName) = UPPER('martin') ?
>>>>>
>>>>> If no are there other ways to do this ?
>>>>> If no, I can always store givenName attribute in 2 different 
>>>>> attributes (i.e. the real givenName attribute and a 
>>>>> givenNameCaseInsensitive attribute to store it in lowercase), but 
>>>>> I would like to avoid it if possible.
>>>>
>>>> Hi Adrian,
>>>> I confirm that currently it is *not* possible to perform case 
>>>> insensitive searches.
>>>>
>>>> Nevertheless, it would be a nice feature to add: essentially, one 
>>>> should be adding 'lowercase()' clauses to the native SQL queries 
>>>> manage by [1]: the problem is that, as far as I can tell, such 
>>>> clauses are DBMS-dependent.
>>>>
>>>> Would you mind creating an improvement on JIRA for this?
>>>> Naturally, contributions are welcome ;-)
>>>>
>>>> Regards.
>>>>
>>>> [1] 
>>>> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

-- 
Francesco Chicchiriccò

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Adrian Gonzalez <ad...@yahoo.fr>.

Hi Francesco,
I've created this PR https://github.com/apache/syncope/pull/40.
Be aware that :
1. it's a global case insentive flag (and not a fiql operator). There were 2 reasons for this: * creating a custom fiql operator isn't possible for the moment without either modifying cxf fiql parser or writing another (which would be a pity). * I had some exchanges with Sergey on case-insensitivity search . His view on this case in/sensitivity issue is that it can be resolved by configuration (I took this approach - easier :) ). After looking a bit SCIM spec, https://tools.ietf.org/html/rfc7643, there's a lot (majority) of fields with caseExact=false. So on second thought I think too that we can live with a global flag.Just let me know if this limitation is ok for you.
I not, we'll need to add some more exchanges with Sergey or you'll need to show me how you could use custom fiql operators, because I don't see them.
2. case insensitivity is only implemented for fiql search.Let me know if you think that we need also implement it in other search (I think only groupDao.findByXXX, userDao.findByXXX would need).

Also just let me know if you see anything in this PR that's not ok for you !
Thanks,Adrian

De : Francesco Chicchiriccò <il...@apache.org>
À : user@syncope.apache.org
Envoyé le : Lundi 21 novembre 2016 16h31
Objet : Re: User case insensitive search

On 21/11/2016 16:29, Francesco Chicchiriccò wrote:

On 21/11/2016 14:00, Francesco Chicchiriccò wrote:

On 21/11/2016 13:47, Adrian Gonzalez wrote:

I would rather go with first (we also have other custom operators).

Essentially, I see two separate tasks here:

https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java#L782-L790

The only non-obvious problem I see is that we will need to make the new code RDBMS provider dependent.

Sorry, totally missed your statement above about using JPQL's LOWER() or UPPER() - smart move ;-)

Sorry again: the problem is that queries built by JPASearchDAO are native query, not JPQL.

Regards.

De : Francesco Chicchiriccò <il...@apache.org>
À : user@syncope.apache.org
Envoyé le : Lundi 21 novembre 2016 13h25
Objet : Re: User case insensitive search

On 21/11/2016 12:59, Adrian Gonzalez wrote:

Hi Adrian,
I confirm that currently it is *not* possible to perform case insensitive searches.

Would you mind creating an improvement on JIRA for this?
Naturally, contributions are welcome ;-)

Regards.

[1] https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

--
Francesco Chicchiriccò

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Francesco Chicchiriccò <il...@apache.org>.

On 21/11/2016 16:29, Francesco Chicchiricc� wrote:
> On 21/11/2016 14:00, Francesco Chicchiricc� wrote:
>> On 21/11/2016 13:47, Adrian Gonzalez wrote:
>>> Hi Francesco,
>>>
>>> Cool, I've just created SYNCOPE-971.
>>>
>>> > the problem is that, as far as I can tell, such clauses are 
>>> DBMS-dependent.
>>> We can solve it using jpql LOWER ou UPPER function, i.e.
>>> LOWER(givenName) = LOWER('Martin')
>>>
>>> I can try to implement it, but I don't know for now if it is 
>>> possible to introduce either :
>>>  * custom operator in FIQL (i.e. some eqi operator like : givenName 
>>> eqi Martin)
>>>  * introduce custom functions in FIQL (i.e. uc(givenName) == uc(Martin))
>>
>> I would rather go with first (we also have other custom operators).
>>
>> Essentially, I see two separate tasks here:
>>
>> 1. extend FIQL, the query builders and the logic that transforms FIQL 
>> queries into the low-level search objects
>> 2. extend JPAAnySearchDAO to take care of such new EQI comparator, 
>> similarly to what is currently done at
>>
>> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java#L782-L790
>>
>> The only non-obvious problem I see is that we will need to make the 
>> new code RDBMS provider dependent.
>
> Sorry, totally missed your statement above about using JPQL's LOWER() 
> or UPPER() - smart move ;-)

Sorry again: the problem is that queries built by JPASearchDAO are 
native query, not JPQL.

Regards.

>>> ------------------------------------------------------------------------
>>> *De :* Francesco Chicchiricc� <il...@apache.org>
>>> *� :* user@syncope.apache.org
>>> *Envoy� le :* Lundi 21 novembre 2016 13h25
>>> *Objet :* Re: User case insensitive search
>>>
>>>
>>>
>>> On 21/11/2016 12:59, Adrian Gonzalez wrote:
>>>> Hello,
>>>>
>>>> I'd like to execute a case insensitive search in Syncope.
>>>>
>>>> i.e. querying all users with an attribute givenName that matches 
>>>>  'martin' would return :
>>>> Martin
>>>> martin
>>>> mArtin
>>>>
>>>> Is there a way to do this with a Fiql search ?
>>>> i.e. something like UPPER(givenName) = UPPER('martin') ?
>>>>
>>>> If no are there other ways to do this ?
>>>> If no, I can always store givenName attribute in 2 different 
>>>> attributes (i.e. the real givenName attribute and a 
>>>> givenNameCaseInsensitive attribute to store it in lowercase), but I 
>>>> would like to avoid it if possible.
>>>
>>> Hi Adrian,
>>> I confirm that currently it is *not* possible to perform case 
>>> insensitive searches.
>>>
>>> Nevertheless, it would be a nice feature to add: essentially, one 
>>> should be adding 'lowercase()' clauses to the native SQL queries 
>>> manage by [1]: the problem is that, as far as I can tell, such 
>>> clauses are DBMS-dependent.
>>>
>>> Would you mind creating an improvement on JIRA for this?
>>> Naturally, contributions are welcome ;-)
>>>
>>> Regards.
>>>
>>> [1] 
>>> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

-- 
Francesco Chicchiricc�

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Francesco Chicchiriccò <il...@apache.org>.

On 21/11/2016 14:00, Francesco Chicchiricc� wrote:
> On 21/11/2016 13:47, Adrian Gonzalez wrote:
>> Hi Francesco,
>>
>> Cool, I've just created SYNCOPE-971.
>>
>> > the problem is that, as far as I can tell, such clauses are 
>> DBMS-dependent.
>> We can solve it using jpql LOWER ou UPPER function, i.e.
>> LOWER(givenName) = LOWER('Martin')
>>
>> I can try to implement it, but I don't know for now if it is possible 
>> to introduce either :
>>  * custom operator in FIQL (i.e. some eqi operator like : givenName 
>> eqi Martin)
>>  * introduce custom functions in FIQL (i.e. uc(givenName) == uc(Martin))
>
> I would rather go with first (we also have other custom operators).
>
> Essentially, I see two separate tasks here:
>
> 1. extend FIQL, the query builders and the logic that transforms FIQL 
> queries into the low-level search objects
> 2. extend JPAAnySearchDAO to take care of such new EQI comparator, 
> similarly to what is currently done at
>
> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java#L782-L790
>
> The only non-obvious problem I see is that we will need to make the 
> new code RDBMS provider dependent.

Sorry, totally missed your statement above abut using JPQL's LOWER() or 
UPPER() - smart move ;-)

Regards.

>> ------------------------------------------------------------------------
>> *De :* Francesco Chicchiricc� <il...@apache.org>
>> *� :* user@syncope.apache.org
>> *Envoy� le :* Lundi 21 novembre 2016 13h25
>> *Objet :* Re: User case insensitive search
>>
>>
>>
>> On 21/11/2016 12:59, Adrian Gonzalez wrote:
>>> Hello,
>>>
>>> I'd like to execute a case insensitive search in Syncope.
>>>
>>> i.e. querying all users with an attribute givenName that matches 
>>>  'martin' would return :
>>> Martin
>>> martin
>>> mArtin
>>>
>>> Is there a way to do this with a Fiql search ?
>>> i.e. something like UPPER(givenName) = UPPER('martin') ?
>>>
>>> If no are there other ways to do this ?
>>> If no, I can always store givenName attribute in 2 different 
>>> attributes (i.e. the real givenName attribute and a 
>>> givenNameCaseInsensitive attribute to store it in lowercase), but I 
>>> would like to avoid it if possible.
>>
>> Hi Adrian,
>> I confirm that currently it is *not* possible to perform case 
>> insensitive searches.
>>
>> Nevertheless, it would be a nice feature to add: essentially, one 
>> should be adding 'lowercase()' clauses to the native SQL queries 
>> manage by [1]: the problem is that, as far as I can tell, such 
>> clauses are DBMS-dependent.
>>
>> Would you mind creating an improvement on JIRA for this?
>> Naturally, contributions are welcome ;-)
>>
>> Regards.
>>
>> [1] 
>> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

-- 
Francesco Chicchiricc�

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Francesco Chicchiriccò <il...@apache.org>.

On 21/11/2016 13:47, Adrian Gonzalez wrote:
> Hi Francesco,
>
> Cool, I've just created SYNCOPE-971.
>
> > the problem is that, as far as I can tell, such clauses are 
> DBMS-dependent.
> We can solve it using jpql LOWER ou UPPER function, i.e.
> LOWER(givenName) = LOWER('Martin')
>
> I can try to implement it, but I don't know for now if it is possible 
> to introduce either :
>  * custom operator in FIQL (i.e. some eqi operator like : givenName 
> eqi Martin)
>  * introduce custom functions in FIQL (i.e. uc(givenName) == uc(Martin))

I would rather go with first (we also have other custom operators).

Essentially, I see two separate tasks here:

1. extend FIQL, the query builders and the logic that transforms FIQL 
queries into the low-level search objects
2. extend JPAAnySearchDAO to take care of such new EQI comparator, 
similarly to what is currently done at

https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java#L782-L790

The only non-obvious problem I see is that we will need to make the new 
code RDBMS provider dependent.

Regards.

> ------------------------------------------------------------------------
> *De :* Francesco Chicchiriccò <il...@apache.org>
> *À :* user@syncope.apache.org
> *Envoyé le :* Lundi 21 novembre 2016 13h25
> *Objet :* Re: User case insensitive search
>
>
>
> On 21/11/2016 12:59, Adrian Gonzalez wrote:
>> Hello,
>>
>> I'd like to execute a case insensitive search in Syncope.
>>
>> i.e. querying all users with an attribute givenName that matches 
>>  'martin' would return :
>> Martin
>> martin
>> mArtin
>>
>> Is there a way to do this with a Fiql search ?
>> i.e. something like UPPER(givenName) = UPPER('martin') ?
>>
>> If no are there other ways to do this ?
>> If no, I can always store givenName attribute in 2 different 
>> attributes (i.e. the real givenName attribute and a 
>> givenNameCaseInsensitive attribute to store it in lowercase), but I 
>> would like to avoid it if possible.
>
> Hi Adrian,
> I confirm that currently it is *not* possible to perform case 
> insensitive searches.
>
> Nevertheless, it would be a nice feature to add: essentially, one 
> should be adding 'lowercase()' clauses to the native SQL queries 
> manage by [1]: the problem is that, as far as I can tell, such clauses 
> are DBMS-dependent.
>
> Would you mind creating an improvement on JIRA for this?
> Naturally, contributions are welcome ;-)
>
> Regards.
>
> [1] 
> https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

-- 
Francesco Chicchiriccò

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Adrian Gonzalez <ad...@yahoo.fr>.

Hi Francesco,
Cool, I've just created SYNCOPE-971.
> the problem is that, as far as I can tell, such clauses are DBMS-dependent.
We can solve it using jpql LOWER ou UPPER function, i.e. 
LOWER(givenName) = LOWER('Martin')

I can try to implement it, but I don't know for now if it is possible to introduce either : * custom operator in FIQL (i.e. some eqi operator like : givenName eqi Martin) * introduce custom functions in FIQL (i.e. uc(givenName) == uc(Martin))
Cheers,Adrian

      De : Francesco Chicchiriccò <il...@apache.org>
 À : user@syncope.apache.org 
 Envoyé le : Lundi 21 novembre 2016 13h25
 Objet : Re: User case insensitive search
  


 On 21/11/2016 12:59, Adrian Gonzalez wrote:
  
  Hello, 
  I'd like to execute a case insensitive search in Syncope. 
  i.e. querying all users with an attribute givenName that matches  'martin' would return : Martin martin mArtin 
  Is there a way to do this with a Fiql search ? i.e. something like UPPER(givenName) = UPPER('martin') ? 
  If no are there other ways to do this ? If no, I can always store givenName attribute in 2 different attributes (i.e. the real givenName attribute and a givenNameCaseInsensitive attribute to store it in lowercase), but I would like to avoid it if possible.  
 
 Hi Adrian,
 I confirm that currently it is *not* possible to perform case insensitive searches.
 
 Nevertheless, it would be a nice feature to add: essentially, one should be adding 'lowercase()' clauses to the native SQL queries manage by [1]: the problem is that, as far as I can tell, such clauses are DBMS-dependent.
 
 Would you mind creating an improvement on JIRA for this?
 Naturally, contributions are welcome ;-)
 
 Regards.
 
 [1]https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java
 -- 
Francesco Chicchiriccò

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/

Re: User case insensitive search

Posted by Francesco Chicchiriccò <il...@apache.org>.

On 21/11/2016 12:59, Adrian Gonzalez wrote:
> Hello,
>
> I'd like to execute a case insensitive search in Syncope.
>
> i.e. querying all users with an attribute givenName that matches 
>  'martin' would return :
> Martin
> martin
> mArtin
>
> Is there a way to do this with a Fiql search ?
> i.e. something like UPPER(givenName) = UPPER('martin') ?
>
> If no are there other ways to do this ?
> If no, I can always store givenName attribute in 2 different 
> attributes (i.e. the real givenName attribute and a 
> givenNameCaseInsensitive attribute to store it in lowercase), but I 
> would like to avoid it if possible.

Hi Adrian,
I confirm that currently it is *not* possible to perform case 
insensitive searches.

Nevertheless, it would be a nice feature to add: essentially, one should 
be adding 'lowercase()' clauses to the native SQL queries manage by [1]: 
the problem is that, as far as I can tell, such clauses are DBMS-dependent.

Would you mind creating an improvement on JIRA for this?
Naturally, contributions are welcome ;-)

Regards.

[1] 
https://github.com/apache/syncope/blob/2_0_X/core/persistence-jpa/src/main/java/org/apache/syncope/core/persistence/jpa/dao/JPAAnySearchDAO.java

-- 
Francesco Chicchiriccò

Tirasa - Open Source Excellence
http://www.tirasa.net/

Member at The Apache Software Foundation
Syncope, Cocoon, Olingo, CXF, OpenJPA, PonyMail
http://home.apache.org/~ilgrosso/