Help with custom collation

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

Help with custom collation

curmudgeon
The collation function used was as follows

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
{
        const UTF8String *S1 = static_cast<const UTF8String*>(s1),
        *S2 = static_cast<const UTF8String*>(s2);
        return 0;
}

Which was registered with the following code

        if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, &Compare) != SQLITE_OK)
                throw Exception("Collation creation error");

The above is merely a tester where I’ve set a breakpoint at the ‘return 0’ line. I tried it with the following stmt

‘select ID from IDTbl order by Name collate Compare’.

I’m having the following problems


  1.  When it stops at the breakpoint the debugger shows the Len1 and Len2 params with correct looking values but the S1 and S2 appear to point to NULL values. I just keep getting ‘???’ values. Am I casting wrongly or something.


  1.  If I change the select to ‘select ID from IDTbl order by ID collate Compare’ the Compare function is never entered. Is this because ID is an integer column? Is there no way to implement a custom collation on an integer column?



  1.  If I change the registration to
if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, &Compare) != SQLITE_OK)
                throw Exception("Collation creation error");
        it seems to register OK but running the select yields a “no such collation sequence: Compare” error.

Can any kind soul cast any light on this for me.

PS I’m using Embarcadero C++ builder 10.1 Berlin on windows 10 with the sqlite3.c amalgamation included in my project.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with custom collation

Clemens Ladisch
x wrote:

> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
> {
>         const UTF8String *S1 = static_cast<const UTF8String*>(s1),
>         *S2 = static_cast<const UTF8String*>(s2);
>         return 0;
> }
>
>         if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, &Compare) != SQLITE_OK)
>                 throw Exception("Collation creation error");
>
> S1 and S2 appear to point to NULL values.

What is "UTF8String"?  If it is anything different from "char", the code is wrong.

> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ the Compare function is never entered.
> Is this because ID is an integer column?

No, it is because the values in that column are integer values.

> Is there no way to implement a custom collation on an integer column?

There is no way to implement a custom collation for integer values.
Collations are used only for string values.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with custom collation

curmudgeon
Thanks Clemens. You’re right about changing the UTF8String* to char* as it now works but when trying it with a column containing Unicode characters it didn’t. I’d have liked to have tried it with windows wchar_t* type but If I try using



         if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, &Compare) != SQLITE_OK)

                 throw Exception("Collation creation error");



I get the message “no such collation sequence: Compare” when running the query.







From: Clemens Ladisch<mailto:[hidden email]>
Sent: 01 February 2017 17:32
To: [hidden email]<mailto:[hidden email]>
Subject: Re: [sqlite] Help with custom collation



x wrote:

> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
> {
>         const UTF8String *S1 = static_cast<const UTF8String*>(s1),
>         *S2 = static_cast<const UTF8String*>(s2);
>         return 0;
> }
>
>         if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, &Compare) != SQLITE_OK)
>                 throw Exception("Collation creation error");
>
> S1 and S2 appear to point to NULL values.

What is "UTF8String"?  If it is anything different from "char", the code is wrong.

> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ the Compare function is never entered.
> Is this because ID is an integer column?

No, it is because the values in that column are integer values.

> Is there no way to implement a custom collation on an integer column?

There is no way to implement a custom collation for integer values.
Collations are used only for string values.


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with custom collation

Hick Gunter
In reply to this post by curmudgeon
The interface your (simple) function must support is:

void xFunc(sqlite3_context*,int,sqlite3_value**)

with the first parameter being the sqlite3_context,
the second parameter being the number of arguments passed in, and
the third parameter being an array of pointers to unprotected sqlite3_value objects

To access the arguments, you must call one of the sqlite3_value() functions.

When implementign an aggregate function, you must provide callbacks for aggregation (xStep) and returning the value (xFinal)

See http://sqlite.org/c3ref/create_function.html

Gunter

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Mittwoch, 01. Februar 2017 17:45
An: [hidden email]
Betreff: [sqlite] Help with custom collation

The collation function used was as follows

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2) {
        const UTF8String *S1 = static_cast<const UTF8String*>(s1),
        *S2 = static_cast<const UTF8String*>(s2);
        return 0;
}

Which was registered with the following code

        if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, &Compare) != SQLITE_OK)
                throw Exception("Collation creation error");

The above is merely a tester where I’ve set a breakpoint at the ‘return 0’ line. I tried it with the following stmt

‘select ID from IDTbl order by Name collate Compare’.

I’m having the following problems


  1.  When it stops at the breakpoint the debugger shows the Len1 and Len2 params with correct looking values but the S1 and S2 appear to point to NULL values. I just keep getting ‘???’ values. Am I casting wrongly or something.


  1.  If I change the select to ‘select ID from IDTbl order by ID collate Compare’ the Compare function is never entered. Is this because ID is an integer column? Is there no way to implement a custom collation on an integer column?



  1.  If I change the registration to
if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, &Compare) != SQLITE_OK)
                throw Exception("Collation creation error");
        it seems to register OK but running the select yields a “no such collation sequence: Compare” error.

Can any kind soul cast any light on this for me.

PS I’m using Embarcadero C++ builder 10.1 Berlin on windows 10 with the sqlite3.c amalgamation included in my project.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with custom collation

Hick Gunter
Sorry misread that you are attempting to write a custom collation.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von Hick Gunter
Gesendet: Donnerstag, 02. Februar 2017 09:06
An: 'SQLite mailing list' <[hidden email]>
Betreff: Re: [sqlite] Help with custom collation

The interface your (simple) function must support is:

void xFunc(sqlite3_context*,int,sqlite3_value**)

with the first parameter being the sqlite3_context, the second parameter being the number of arguments passed in, and the third parameter being an array of pointers to unprotected sqlite3_value objects

To access the arguments, you must call one of the sqlite3_value() functions.

When implementign an aggregate function, you must provide callbacks for aggregation (xStep) and returning the value (xFinal)

See http://sqlite.org/c3ref/create_function.html

Gunter

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von x
Gesendet: Mittwoch, 01. Februar 2017 17:45
An: [hidden email]
Betreff: [sqlite] Help with custom collation

The collation function used was as follows

int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2) {
        const UTF8String *S1 = static_cast<const UTF8String*>(s1),
        *S2 = static_cast<const UTF8String*>(s2);
        return 0;
}

Which was registered with the following code

        if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, &Compare) != SQLITE_OK)
                throw Exception("Collation creation error");

The above is merely a tester where I’ve set a breakpoint at the ‘return 0’ line. I tried it with the following stmt

‘select ID from IDTbl order by Name collate Compare’.

I’m having the following problems


  1.  When it stops at the breakpoint the debugger shows the Len1 and Len2 params with correct looking values but the S1 and S2 appear to point to NULL values. I just keep getting ‘???’ values. Am I casting wrongly or something.


  1.  If I change the select to ‘select ID from IDTbl order by ID collate Compare’ the Compare function is never entered. Is this because ID is an integer column? Is there no way to implement a custom collation on an integer column?



  1.  If I change the registration to
if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, &Compare) != SQLITE_OK)
                throw Exception("Collation creation error");
        it seems to register OK but running the select yields a “no such collation sequence: Compare” error.

Can any kind soul cast any light on this for me.

PS I’m using Embarcadero C++ builder 10.1 Berlin on windows 10 with the sqlite3.c amalgamation included in my project.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with custom collation

Dan Kennedy-4
In reply to this post by curmudgeon
On 02/02/2017 02:08 AM, x wrote:

> Thanks Clemens. You’re right about changing the UTF8String* to char* as it now works but when trying it with a column containing Unicode characters it didn’t. I’d have liked to have tried it with windows wchar_t* type but If I try using
>
>
>
>           if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, &Compare) != SQLITE_OK)
>
>                   throw Exception("Collation creation error");
>
>
>
> I get the message “no such collation sequence: Compare” when running the query.

The second argument passed to sqlite3_create_collation16() should point
to a buffer containing a utf-16 string. Not utf-8.

Dan.



>
>
>
>
>
>
>
> From: Clemens Ladisch<mailto:[hidden email]>
> Sent: 01 February 2017 17:32
> To: [hidden email]<mailto:[hidden email]>
> Subject: Re: [sqlite] Help with custom collation
>
>
>
> x wrote:
>> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
>> {
>>          const UTF8String *S1 = static_cast<const UTF8String*>(s1),
>>          *S2 = static_cast<const UTF8String*>(s2);
>>          return 0;
>> }
>>
>>          if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, &Compare) != SQLITE_OK)
>>                  throw Exception("Collation creation error");
>>
>> S1 and S2 appear to point to NULL values.
> What is "UTF8String"?  If it is anything different from "char", the code is wrong.
>
>> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ the Compare function is never entered.
>> Is this because ID is an integer column?
> No, it is because the values in that column are integer values.
>
>> Is there no way to implement a custom collation on an integer column?
> There is no way to implement a custom collation for integer values.
> Collations are used only for string values.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with custom collation

curmudgeon
Thanks Dan. Replacing “Compare” with L”Compare” and casting to wchar_t* inside the function solved the problem.



Thanks for all the replies.





From: Dan Kennedy<mailto:[hidden email]>
Sent: 02 February 2017 08:42
To: [hidden email]<mailto:[hidden email]>
Subject: Re: [sqlite] Help with custom collation



On 02/02/2017 02:08 AM, x wrote:

> Thanks Clemens. You’re right about changing the UTF8String* to char* as it now works but when trying it with a column containing Unicode characters it didn’t. I’d have liked to have tried it with windows wchar_t* type but If I try using
>
>
>
>           if (sqlite3_create_collation16(SQLiteDB, "Compare", SQLITE_UTF16, NULL, &Compare) != SQLITE_OK)
>
>                   throw Exception("Collation creation error");
>
>
>
> I get the message “no such collation sequence: Compare” when running the query.

The second argument passed to sqlite3_create_collation16() should point
to a buffer containing a utf-16 string. Not utf-8.

Dan.



>
>
>
>
>
>
>
> From: Clemens Ladisch<mailto:[hidden email]>
> Sent: 01 February 2017 17:32
> To: [hidden email]<mailto:[hidden email]>
> Subject: Re: [sqlite] Help with custom collation
>
>
>
> x wrote:
>> int Compare(void* Data, int Len1, const void *s1, int Len2, const void *s2)
>> {
>>          const UTF8String *S1 = static_cast<const UTF8String*>(s1),
>>          *S2 = static_cast<const UTF8String*>(s2);
>>          return 0;
>> }
>>
>>          if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL, &Compare) != SQLITE_OK)
>>                  throw Exception("Collation creation error");
>>
>> S1 and S2 appear to point to NULL values.
> What is "UTF8String"?  If it is anything different from "char", the code is wrong.
>
>> If I change the select to ‘select ID from IDTbl order by ID collate Compare’ the Compare function is never entered.
>> Is this because ID is an integer column?
> No, it is because the values in that column are integer values.
>
>> Is there no way to implement a custom collation on an integer column?
> There is no way to implement a custom collation for integer values.
> Collations are used only for string values.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with custom collation

Jens Alfke-2
Perhaps off-topic, but: UTF-16 is generally not recommended, unless you need to work with legacy APIs that require it. It has the same difficulties as UTF-8 (having to handle characters broken into multi-element sequences) but uses more RAM and isn’t downward compatible with ASCII. Also, since you’re less likely to run into multi-word character sequences in normal use [if you’re non-Asian], it’s easier to miss bugs in your multi-word character handling. (Tip: put some emoji in your test strings!)

(A UTF-16 collator is also probably going to be slower than a UTF-8 one, since SQLite will need to copy/expand the UTF-8 strings from the database when it calls it. And yes, collators can easily become performance bottlenecks; I’ve seen it.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Help with custom collation

curmudgeon
Thanks for the advice Jens. My problem is the software I use (Embarcadero c++ builder) is more utf16 orientated. It does support utf8 but many of the built in functions return System.UnicodeString which is utf16.





From: Jens Alfke<mailto:[hidden email]>
Sent: 02 February 2017 17:32
To: SQLite mailing list<mailto:[hidden email]>
Subject: Re: [sqlite] Help with custom collation



Perhaps off-topic, but: UTF-16 is generally not recommended, unless you need to work with legacy APIs that require it. It has the same difficulties as UTF-8 (having to handle characters broken into multi-element sequences) but uses more RAM and isn’t downward compatible with ASCII. Also, since you’re less likely to run into multi-word character sequences in normal use [if you’re non-Asian], it’s easier to miss bugs in your multi-word character handling. (Tip: put some emoji in your test strings!)

(A UTF-16 collator is also probably going to be slower than a UTF-8 one, since SQLite will need to copy/expand the UTF-8 strings from the database when it calls it. And yes, collators can easily become performance bottlenecks; I’ve seen it.)

—Jens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users