[yunqa.de] Re: Custom Collations

  • From: Delphi Inspiration <delphi@xxxxxxxx>
  • To: yunqa@xxxxxxxxxxxxx
  • Date: Wed, 17 Sep 2008 11:58:50 +0200

Clyde England wrote:

>I am trying to add some custom collations into my application.
>I have been looking at the demo examples, and finally got them to work in my 
>application (I had to pass SQLITE_UTF16 when doing the creation instead of the 
>examples which use SQLITE_UTF8)

With sqlite3_create_collation(), the SQLITE_UTF8 or SQLITE_UTF16 parameter 
value must match the collation callback function implementation. With 
SQLITE_UTF8, DISQLite3 is converts all text to the UTF-8 encoding before 
passing it to the callback.

However, UTF-8 encoding is not usually supported by Windows CompareString... 
functions. Therefore does indeed make more sense to use SQLITE_UTF16, which 
corresponds to Delphi's WideString and the Windows default Unicode encoding.

>However, there is one example that has me puzzled and I would like 
>DiSQLite3Collations.pas in the demo folder has SQLite3_Compare_System_Ansi and 
>a corresponding SQLite3_Compare_System_NoCase_Ansi
>I have implemented both these in my application
>However, when I order using these collations the sort sequence is exactly the 
>same - case INsenstive.
>Case appears to be respected in System_Ansi when used in a "Where" clause, but 
>when used in the "Order" clause case appears to be ignored.

Did you specify the collation with the ORDER BY clause as shown here:

  select * from t order by t collate system;

If you do not specify a collation, DISQLite3 uses the column's default 
collation. The standard collation is "BINARY", but you can override this 
default for individual columns in CREATE TABLE:

  create table t (t text collate systemnocase);

>I notice you also appear to support both these collations in your SQLiteSpy 
>By trial and error I believe these collations in SQLiteSpy are named SYSTEM 
>and SYSTEMNOCASE respectively

Correct. The examples given in this message refer to the SQLiteSpy collation 

>Tesing in SQLiteSpy also shows the same behaviour when using ORDER
>Can you please explain to me why both these collations ORDER in exactly the 
>same way, yet WHERE respects case in the former?

You did not show your WHERE clause, but the following SQL shows that ORDER BY 
does indeed show different results with different collations:

  create table t (t text);

  insert into t values ('A');
  insert into t values ('a');
  insert into t values ('B');
  insert into t values ('b');

  select * from t order by t;

Returns: A B a b

  select * from t order by t collate system;

Returns: a A b B

  select * from t order by t collate systemnocase;

Returns: A a B b


Delphi Inspiration mailing list

Other related posts: