Is there a way to define a custon collation for SQL table properties?
I would linke to have the functionalities provided by the Table Field/Property Collation, but applying a transformation which differs from the provided ones.
Does anyone know if there is a way do accomplish that?
Comments
I'm not aware of a "customized" collation.
But this might be an appropriate workaround:
- for your property, you define an additional calculated property that results out of your customized collation
- for this new calculated property, you define COLLATION = EXACT to avoid default surprises (SQLUPPER !!!)
If you index it, you should get what you expected without impact to the rest of your table
Thank you for your answer Robert.
This was my initial approach as well. The problem is that you need to use the calculated property to filter and ordering, but the original one to get the value. This will be confusing for the development, and may lead to some issues in the future.
This is why I thought about COLLATION, because it provides the desired behavior, I just need a custom transformation :)
The default collation for strings is SQLUPPER, which allows non-case-sensitive searches. So assuming the following table, named foo:
| ID | bar |
| 1 | Hello |
| 2 | World |
All following sql queries will return exactly the same value in myBar, which will be exactly Hello.
SELECT bar As myBar FROM foo where bar = 'HELLO'
SELECT bar As myBar FROM foo where bar = 'hello'
SELECT bar As myBar FROM foo where bar = 'heLLo'
What I need is something very similar but ignoring leading zeroes when searching.
| ID | bar |
| 1 | 00123AB |
| 2 | 12345AB |
So the following two queries shall return 00123AB as myBar.
SELECT bar As myBar FROM foo where bar = '00123AB'
SELECT bar As myBar FROM foo where bar = '123AB'
As you can see, the desired behavior is the same, just different transformation. Whether there are few to none use cases where applying this transformation makes sense is another discussion.
Normally I would implement this behavior in the business logic, not in the data model. But this is a change that we need to implement in a really legacy part with a lot of existing business logic. So having the possibility of implementing it that way would save a LOT of effort.
I don't see how I can get this behavior by overriding the methods DisplayToLogical/LogicalToDisplay and/or LogicalToStorage/StorageToLogical.
I think you need to override not the COLLATION, and methods DisplayToLogical/LogicalToDisplay and/or LogicalToStorage/StorageToLogical.
See Defining Data Type ClassesUse %MVR
For example:Class dc.test Extends %Persistent
{
</FONT><FONT COLOR="#000080">Index </FONT><FONT COLOR="#000000">ibar On bar;
</FONT><FONT COLOR="#000080">Property </FONT><FONT COLOR="#000000">bar </FONT><FONT COLOR="#000080">As %String</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#000080">COLLATION </FONT><FONT COLOR="#000000">= </FONT><FONT COLOR="#800080">"MVR"</FONT><FONT COLOR="#000000">);
</FONT><FONT COLOR="#000080">ClassMethod </FONT><FONT COLOR="#000000">Test()
{
</FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000000">..</FONT><FONT COLOR="#0000ff">%KillExtent</FONT><FONT COLOR="#000000">()
</FONT><FONT COLOR="#800080">&sql(</FONT><FONT COLOR="#0000ff">insert </FONT><FONT COLOR="#000080">into </FONT><FONT COLOR="#008000">dc</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#008000">test</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008000">bar</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'00123AB' </FONT><FONT COLOR="#000080">union
</FONT><FONT COLOR="#0000ff">select </FONT><FONT COLOR="#008080">'12345AB'</FONT><FONT COLOR="#800080">)
</FONT><FONT COLOR="#0000ff">d $system</FONT><FONT COLOR="#008080">.SQL</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">TuneTable</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#0000ff">$$$YES</FONT><FONT COLOR="#000000">), </FONT><FONT COLOR="#0000ff">$system</FONT><FONT COLOR="#008080">.OBJ</FONT><FONT COLOR="#000000">.</FONT><FONT COLOR="#0000ff">Compile</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#0000ff">$classname</FONT><FONT COLOR="#000000">(),</FONT><FONT COLOR="#008000">"cu-d"</FONT><FONT COLOR="#000000">)
</FONT><FONT COLOR="#0000ff">f </FONT><FONT COLOR="#800000">args</FONT><FONT COLOR="#000000">=</FONT><FONT COLOR="#008000">"00123AB"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#008000">"123AB" </FONT><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">%SQL.Statement</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%ExecDirect</FONT><FONT COLOR="#000000">(,</FONT><FONT COLOR="#008000">"select * from dc.test where bar=?"</FONT><FONT COLOR="#000000">,</FONT><FONT COLOR="#800000">args</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">%Display</FONT><FONT COLOR="#000000">() </FONT><FONT COLOR="#0000ff">w </FONT><FONT COLOR="#000000">!!
}
}</FONT>
Result:USER><FONT COLOR="#0000ff">d </FONT><FONT COLOR="#000080">##class</FONT><FONT COLOR="#000000">(</FONT><FONT COLOR="#008080">dc.test</FONT><FONT COLOR="#000000">).</FONT><FONT COLOR="#0000ff">Test</FONT><FONT COLOR="#000000">()</FONT> ID bar 1 00123AB1 Rows(s) Affected
ID bar 1 00123AB
1 Rows(s) Affected