Collation List SQlite

TommyYommi · 2013-02-02 10:32:16

Hi...

I was seen the Database created by the ORM, and in the Database Properties -> Collation List there is a [SYSTEMNOCASE] as one of its values.

What does [SYSTEMNOCASE] means?

ab · 2013-02-02 15:24:46

You have the whole source at hand.
Just search for SYSTEMNOCASE and you will find:

In function TSQLDataBase.DBOpen: integer:

  // the SQLite3 standard NOCASE collation is used for AnsiString and is very fast
  // our custom fast UTF-8 case insensitive compare, using NormToUpper[] for all 8 bits values
  sqlite3_create_collation(DB,'SYSTEMNOCASE',SQLITE_UTF8,nil,Utf8SQLCompNoCase);

(...)

function Utf8SQLCompNoCase(CollateParam: pointer; s1Len: integer; s1: pointer;
    s2Len: integer; s2: pointer) : integer; {$ifndef USEFASTCALL}cdecl;{$endif}
begin
  if (s1Len=0) and (s2Len=0) then // see WladiD note above
    result := 0 else
    result := UTF8ILComp(s1,s2,s1Len,s2Len); // properly handles individual s?Len=0
end;

In fact, function UTF8IComp(u1, u2: PUTF8Char): PtrInt will compare not only 'a'..'z'/'A'..'Z' characters, but also with accents (i.e. 'à'='A' and 'é'='è'=E).
It will work only with latin encoding, by design.

But is was defined to be very fast - much faster than the Windows API comparison functions.

Junior/RO · 2013-02-04 13:42:21

Trying some selects in mORMot created tables, SQLite Administrator give me this error message:

SQL Error: no such collation sequence: SYSTEMNOCASE.

I think I should change my tool. Which free sqlite admin do you recommend?

Last edited by Junior/RO (2013-02-04 13:43:21)

ab · 2013-02-04 14:05:47

Our SynDBExplorer handle those collations.
See http://blog.synopse.info/tag/SynDBExplorer

You can compile it (latest version is better) from the "Samples\12 - SynDB Explorer" folder.
I recommend using the 1.18 unstable version available from http://synopse.info/fossil/wiki?name=Get+the+source

edwinsn · 2023-03-08 07:50:34

Junior/RO wrote:

Trying some selects in mORMot created tables, SQLite Administrator give me this error message:
SQL Error: no such collation sequence: SYSTEMNOCASE.
I think I should change my tool. Which free sqlite admin do you recommend?

There is a solution here: https://synopse.info/forum/viewtopic.ph … 320#p39320

mORMot Open Source

#1 2013-02-02 10:32:16

Collation List SQlite

#2 2013-02-02 15:24:46

Re: Collation List SQlite

#3 2013-02-04 13:42:21

Re: Collation List SQlite

#4 2013-02-04 14:05:47

Re: Collation List SQlite

#5 2023-03-08 07:50:34

Re: Collation List SQlite

Board footer