Thanks for the reply, Igor.
Understood that an index cannot be placed on a function; I wasn't
thinking of a "virtual field" as one can have in Oracle or MS-Access, or
in legacy non-1NF databases such as Revelation, for example. The flip()
function would simply be a utility that would enable me to do this:
update T set myFlippedColumn = flip(myNormalColumn)
so that if myNormalColumn contained 'abc' myFlippedColumn contains 'bca'.
Regarding your point about combining Unicode forms: no need for the
function to so anything special in the way it reverses strings
containing combining forms. That is, no need whatsoever to treat
combining forms as monads, preserving them during the flip, so that 'a
b fi c d' would become 'd c fi b a' rather than 'd c if b a'. Let
the flipped string become 'd c if b a' ! Keep the function purely
determinative as if combining-forms never existed. The flipped string
doesn't have to be *legible*. The developer would understand that the
flipped string is a raw *single-codepoint-by-single-codepoint* reversal
of the source string.
So, for example, if one wanted to find all rows where myNormalColumn
ENDS WITH 'fi c d', one could search myFlippedColumn like this:
select * from LEXICON where myFlippedColumn LIKE 'd c if%' --
allows index use
This doesn't really require combining-form intelligence on the part of
the developer's code either. As long as the search-term on the RHS gets
flipped codepoint-by-codepoint and no attempt is made to "be
intelligent" about the combining form, everything will be honky-dory.
Regards
Tim Romano
Igor Tandetnik wrote:
> Tim Romano wrote:
>
>> You can accomplish this on the front-end, of course, but it would be
>> much more convenient and efficient to have a built-in function.
>>
>
> It is difficult to define a "reverse" operation on arbitrary Unicode strings
> in a useful way. E.g., consider the string 'Á' (U+0041 Lating Capital Letter
> A + U+0301 Combining Acute Accent). Should flip() keep it as is, or reverse
> it to a meaningless sequence U+0301 U+0041 ?
>
> Or 'fi' (U+FB01 Latin Small Ligature Fi): should it be kept as is, or broken
> apart and reversed to 'if'?
>
> If your particular application deals with a subset of Unicode strings for
> which flip() would be well defined (say, ASCII strings), it should be easy to
> implement it as a custom function.
>
>
>> Given the range of possible uses for it, this flip(string) function
>> would be far more versatile than a REVERSED collation sequence.
>>
>
> Note that you can create indexes on a collation, but not on a function.
>
> Igor Tandetnik
>
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
> ------------------------------------------------------------------------
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.425 / Virus Database: 270.14.68/2507 - Release Date: 11/16/09
> 19:53:00
>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
