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

TommyYommi
Member
Registered: 2013-01-18
Posts: 27

Collation List SQlite

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?

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,706
Website

Re: Collation List SQlite

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.

Offline

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

Junior/RO
Member
Registered: 2011-05-13
Posts: 210

Re: Collation List SQlite

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)

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,706
Website

Re: Collation List SQlite

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

Offline

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

edwinsn
Member
Registered: 2010-07-02
Posts: 1,218

Re: Collation List SQlite

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


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

Board footer

Powered by FluxBB