Re: [sqlite] Help with custom collation
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:j...@mooseyard.com> Sent: 02 February 2017 17:32 To: SQLite mailing list<mailto:sqlite-users@mailinglists.sqlite.org> 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with custom collation
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:danielk1...@gmail.com>
Sent: 02 February 2017 08:42
To:
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
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, ) != 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:clem...@ladisch.de>
> Sent: 01 February 2017 17:32
> To:
> sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
> 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(s1),
>> *S2 = static_cast(s2);
>> return 0;
>> }
>>
>> if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8,
>> NULL, ) != 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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,
) != 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:clem...@ladisch.de>
Sent: 01 February 2017 17:32
To:
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
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(s1),
*S2 = static_cast(s2);
return 0;
}
if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL,
) != 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with custom collation
Sorry misread that you are attempting to write a custom collation.
-Ursprüngliche Nachricht-
Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von Hick Gunter
Gesendet: Donnerstag, 02. Februar 2017 09:06
An: 'SQLite mailing list' <sqlite-users@mailinglists.sqlite.org>
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:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von x
Gesendet: Mittwoch, 01. Februar 2017 17:45
An: sqlite-users@mailinglists.sqlite.org
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(s1),
*S2 = static_cast(s2);
return 0;
}
Which was registered with the following code
if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL,
) != 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,
) != 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
sqlite-users@mailinglists.sqlite.org
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: h...@scigames.at
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
sqlite-users@mailinglists.sqlite.org
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: h...@scigames.at
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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:sqlite-users-boun...@mailinglists.sqlite.org] Im
Auftrag von x
Gesendet: Mittwoch, 01. Februar 2017 17:45
An: sqlite-users@mailinglists.sqlite.org
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(s1),
*S2 = static_cast(s2);
return 0;
}
Which was registered with the following code
if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL,
) != 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,
) != 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
sqlite-users@mailinglists.sqlite.org
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: h...@scigames.at
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Help with custom collation
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, ) != SQLITE_OK)
throw Exception("Collation creation error");
I get the message “no such collation sequence: Compare” when running the query.
From: Clemens Ladisch<mailto:clem...@ladisch.de>
Sent: 01 February 2017 17:32
To:
sqlite-users@mailinglists.sqlite.org<mailto:sqlite-users@mailinglists.sqlite.org>
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(s1),
> *S2 = static_cast(s2);
> return 0;
> }
>
> if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL,
> ) != 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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(s1),
> *S2 = static_cast(s2);
> return 0;
> }
>
> if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL,
> ) != 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[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(s1),
*S2 = static_cast(s2);
return 0;
}
Which was registered with the following code
if (sqlite3_create_collation(SQLiteDB, "Compare", SQLITE_UTF8, NULL,
) != 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,
) != 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
