New post
Question
Evgeny Shvarov · Feb 25, 2017

How to add the case insensitive index to a class?

#Indexing #Object Data Model #ObjectScript #Caché

Hi!

Consider I have a class Package.Data with Property UniqueStringValue as %String.

I introduced the Index for this property:

 Index ValueIndex on UniqueStringValue [Unique];

It works well.  But if I try to check if there is an object with the certain value in code like this:

if ##class(Package.Data).ValueIndexExists(value)

this expression fails, if value="value", even if there is an instance with instance.UniqueStingValue="Value"

How can I set the index to prevent saving case sensitive values in this class?

0
0 447
Discussion (6)3
Log in or sign up to continue
Tirthankar Bachhar · Feb 27, 2017 
Property UniqueStringValue As %String(COLLATION = "SQLUPPER");
Index ValueIndex On UniqueStringValue [ Unique ];

Use COLLATION as above, to specify the manner in which property values are transformed for indexing.

Below is how data and index will look like,

ZW ^DataGlobal
^DataGlobal=1
^DataGlobal(1)=$lb("",,"Hello")
 
ZW ^IndexGlobal
^IndexGlobal("NameIDX"," HELLO",1)=""
^IndexGlobal("ValueIndex"," HELLO",1)=""

Test:

Write ##class(Package.Data).ValueIndexExists("hello") = 1
Write ##class(Package.Data).ValueIndexExists("HELLO") = 1
Write ##class(Package.Data).ValueIndexExists("heLLO") = 1
Write ##class(Package.Data).ValueIndexExists("hello") = 1
Write ##class(Package.Data).ValueIndexExists("Hello") = 1
Write ##class(Package.Data).ValueIndexExists("HelloGuys") = 0

ISC DocBook: http://docs.intersystems.com/latest/csp/docbook/DocBook.UI.Page.cls?KEY=GSQL_basics#GSQL_basics_collation

1 0
Tirthankar Bachhar · Feb 28, 2017

Agreed. Default collation for String is SQLUPPER. You can't define the collation of a index if its defined as Unique as below. Below is prohibited, but you can mention any collation on property instead of specifying in Index.

Index ValueIndex On UniqueStringValue As SQLUPPER [ Unique ];

On the other hand, I mentioned another solution to the actual problem above. Please review/comment on that.

0 0
Brendan Bannon · Feb 27, 2017

The default behavior for %String is case insensitive searching, so I would expect what Bachhar shows would be the same if he left off Collation = 'SQLUPPER".

It is possible to change the default collation for %String on a system, so maybe that is why you are seeing different results.  For example this command would make the default behavior %EXACT:


USER>w $$SetEnvironment^%apiOBJ("collation","%Library.String","EXACT")
1
USER>w $$GetEnvironment^%apiOBJ("collation","%Library.String",.current)
1
USER>w current
EXACT
USER>

This is done on the namespace level

brendan

1 0
Evgeny Shvarov · Feb 27, 2017

Thanks for the answers!

Actually there was a mistake in example. The original case in my app uses idkey:

 Index ValueIndex on UniqueStringValue [Unique, idkey];

Caché uses Exact collation for idkey indexes.

So that's why I had a problem with 

.ValueIndexExists(value)

method.

1 0
Tirthankar Bachhar · Feb 28, 2017

Yes, if you have idkey in index, that has to be Exact collation.

But I can tell you one way around it, lets see if you like it. Its tricky though.

Property UniqueStringValue As %String;
Property CopyUniqueStringValue As %String [ SqlComputeCode = { set {CopyUniqueStringValue}={UniqueStringValue}}, SqlComputed, SqlComputeOnChange = UniqueStringValue ];
Index ValueIndex On UniqueStringValue [ IdKey, Unique ];
Index ValueIndex2 On CopyUniqueStringValue As SQLUPPER;

Now, if you do ValueIndex2Exists("hello") or ValueIndex2Exists("Hello") or ValueIndex2Exists("hELLO") you will find match. This will also work on SQL as well if you do - 'Where CopyUniqueStringValue=?'.

This should solve your purpose though, but the space will be duplicated for same index though.

0 0
Evgeny Shvarov · Feb 27, 2017

And yes: Caché  creates the index with SQLUPPER collation by default for all the indexes, except idkey ones.

1 0