Last April I offered my time to solve this issue but, unfortunately didn't
receive any responses. The time has come for me to solve this issue once and
for all. I'd really appreciate any feedback.
The problem: Transfer does not support functions, so it's not possible to do
non case sensitive searches using functions like LCASE.
Initially I solved the issue by creating views in the DB server with a LCASE
version of the columns I needed to search, and adding new objects in
Transfer. The problem of this approach is that it soon gets very messy: you
need to have duplicate versions of the same objects in Transfer; you need
to create extra views in the DB; if you decide to search in another column,
you need add more code; and most important, the DB indices are not used as
the query that the server receives does not include LCASE. (If someone needs
more details on this, please let me know)
The proposed solution: Ideally, I'd like to add support for SQL functions to
Transfer, but I'm afraid it's too big of a problem for me. The second,
simpler option is to modify Transfer's behaviour when it deals with the LIKE
function.
After checking Transfer and ANTLR source code, the easiest path seems to be
to modify the TQL>SQL translation of the LIKE operator from the current
translation:
"property LIKE expr" -> "column LIKE expr"
to
"property LIKE expr" -> "LCASE(column) LIKE LCASE(expr)"
This way, the DB server knows it should use the LCASE index.
After checking the transfer source code a bit more, I came across the file
"Tql.g", which seems to define the "grammar" for TQL. The where statements
are define as: whereStatement: WHERE^ conditionStatement; and a few lines
below: OPERATOR: ('='|'>'|'<'|'!='|'<>'|'>='|'<='|'like') {
setMode(PROPERTY_IDENTIFIER_MODE); }; and condition: ( conditionBasicClause
| conditionParen ) ( BOOLEAN_LOGIC ( conditionBasicClause | conditionParen )
)*;
So, I probably need to modify OPERATOR to exclude LIKE, add a new type of
clause to the definition of "condition" which will handle the translation
mentioned above.
Does this sound reasonable? feasible? Any ideas? comments? Unfortunately, I
don't have as much time to look into this as I'd like... is this the right
approach?
Pedro.
--
Before posting questions to the group please read:
http://groups.google.com/group/transfer-dev/web/how-to-ask-support-questions-on-transfer
Try out the new Transfer ORM Custom Google Search:
http://www.google.com/cse/home?cx=002375903941309441958:2s7wbd5ocb8
You received this message because you are subscribed to the Google Groups
"transfer-dev" group.
To post to this group, send email to transfer-dev@googlegroups.com
To unsubscribe from this group, send email to
transfer-dev+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/transfer-dev?hl=en
