How to add the case insensitive index to a class?
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?
Comments
Property UniqueStringValue As %String(COLLATION = "SQLUPPER");Index ValueIndex On UniqueStringValue [ Unique ];
Use
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
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.
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
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.
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.
And yes: Caché creates the index with SQLUPPER collation by default for all the indexes, except idkey ones.