Case-insensitive search Cyrillic in SQLite3

BenTen · 2021-01-21 19:21:18

Hi.

I always use the default codepage Win-1251 for my projects.
In the Example 02, if field "Name" uses the Latin alphabet, then the search is made case-insensitive, and if the Cyrillic alphabet, then the search is only case-sensitive.
Help me please. How can I ensure that the Cyrillic alphabet is also compared case-insensitively?
Thanks.

Example 02 - Embedded SQLite3 ORM

procedure TForm1.FindButtonClick(Sender: TObject);
var
  Rec: TSQLSampleRecord;
  SearchStr: RawUTF8;
begin
  SearchString := StringToUTF8(NameEdit.Text);
  Rec := TSQLSampleRecord.Create(Database,'Name=?',[SearchString]);
  try
    if Rec.ID=0 then
      QuestionMemo.Lines.Add('Not found')
    else
    begin
      Created.Text := Rec.CreationString;
      QuestionMemo.Lines.Add(UTF8ToString(Rec.Question));
    end;
  finally
    Rec.Free;
  end;
end;

Vitaly · 2021-01-22 01:03:34

As a quick workaround, I would suggest trying to change the collation to WIN32NOCASE for the search field in your DB.

Also, this might be helpful:
https://synopse.info/files/html/Synopse … #TITLE_155
https://synopse.info/forum/viewtopic.php?id=4342

BenTen · 2021-01-22 10:32:15

Many thanks for the help.
Setting WIN32NOCASE mode solved my issue.
IMPORTANT!
It is necessary to set the Collation columns mode ONLY before creating a new database

procedure TForm1.FormCreate(Sender: TObject);
begin
  Model := CreateSampleModel;
  // It is necessary to set the Collation columns mode ONLY before creating a new database
  Model.SetCustomCollationForAll(sftUTF8Text, 'WIN32NOCASE');
  Database := TSQLRestServerDB.Create(Model,ChangeFileExt(ExeVersion.ProgramFileName,'.db3'));
  TSQLRestServerDB(Database).CreateMissingTables;
end;

ab · 2021-01-22 11:14:54

Just a warning: even if WIN32NOCASE works, there is no such thing as an universal Unicode collation.
Please read http://sqlite.1065341.n5.nabble.com/Uni … 24628.html

So if you move a DB from one system to another, depending on the local, you may break the indexes...
It won't happen for Win1251 for sure, but there is no universal comparison system, without knowing the exact locale: depending on the actual language, some Unicode codepoint may not compare the same!

I may add a Win1251 explicit collation... since there is already a Win1252 dedicated version...

Vitaly · 2021-01-22 14:01:00

ab wrote:

I may add a Win1251 explicit collation... since there is already a Win1252 dedicated version...

Personally, I'm not interested in win1251 since I'm not using it in the development, although I see questions/troubles concerning it on the forum from time to time.
Just curious, what will be the disadvantage of adding win1251 collation for other non-cyrillic developers?

ab · 2021-01-22 17:28:40

I guess we may make an internal efficient case-insensitive comparer for most simple code pages, working directly at UTF-16 level.

BenTen · 2021-01-22 18:09:45

Arnaud wrote:

I may add a Win1251 explicit collation... since there is already a Win1252 dedicated version...

Thank you, Arnaud. It will be very good.
In addition, I do not plan to move the database between systems with different locales.
I just started learning mORMot framework yesterday and I plan to transfer one my simple project implemented on Datasnap to mORMot.
If all goes well, I will use mORMot for new projects.

edwinsn · 2021-01-23 04:55:28

ab wrote:

So if you move a DB from one system to another, depending on the local, you may break the indexes...

@ab, what's the workaround to handle it after moving across locals? Delete and rebuild the indexes?

ab · 2021-01-23 10:50:04

Yes, you would need to rebuild the indexes to be sure the values would match.

This is why a hard-coded pure mORMot Unicode comparison could make sense.
It would be cross-platform and consistent over all systems, as SQlite3 should.
I will include it into mormot.core.unicode.pas from mORMot 2.

edwinsn · 2021-01-23 13:13:41

ab wrote:

This is why a hard-coded pure mORMot Unicode comparison could make sense.
It would be cross-platform and consistent over all systems, as SQlite3 should.
I will include it into mormot.core.unicode.pas from mORMot 2.

Yes! When the db is used as a document file format, the user might copy the file across different locals.
And yes, that hard-coded collation must be **unicode-ready**.
Thanks for your effort!

ab · 2021-01-23 18:48:00

I have implement in mORMot 2:

1) OS independent Unicode 10.0 process - https://github.com/synopse/mORMot2/comm … e98639793e

2) New SQlite3 UNICODECASE collation supporting full Unicode - https://github.com/synopse/mORMot2/comm … 4a1af602fb

3) At ORM level, just inherit from TOrmUnicodeNoCase class to use this new collation for RawUtf8 properties - also for our in-memory engine.

The Unicode tables hardcoded are only 20KB for a very efficient branchless comparison.

(no plan to implement in mORMot 1 yet, since SynCommons.pas is already too big)

edwinsn · 2021-01-24 06:07:16

Wow! Amazing work as usual. How can you implement it so quickly!

Although it's a pity no mORMot 1 support...

Leslie7 · 2021-01-26 16:36:38

Great news!
Not to be greedy, but the question comes kind of naturally: why v10, why not the latest v13?

ab · 2021-01-26 17:36:55

Because I started from Bero's tables, which has Unicode 10.0 support.

To be fair, I doubt Unicode 13.0 has much more to offer in practice: some EmoJi, some historical graphics...
If there is a really need, we may add them.
The main problem won't be our framework, but to find the fonts able to display those charsets, and - even more - the people able to read and write them!

Leslie7 · 2021-01-26 18:17:07

I suspected something like this. Anyhow, excellent addition to the framework.

The way you care for mORMot and it's users is amazing. Your love for your work is kind of contagious.

Last edited by Leslie7 (2021-01-26 18:20:47)

mORMot Open Source

#1 2021-01-21 19:21:18

Case-insensitive search Cyrillic in SQLite3

#2 2021-01-22 01:03:34

Re: Case-insensitive search Cyrillic in SQLite3

#3 2021-01-22 10:32:15

Re: Case-insensitive search Cyrillic in SQLite3

#4 2021-01-22 11:14:54

Re: Case-insensitive search Cyrillic in SQLite3

#5 2021-01-22 14:01:00

Re: Case-insensitive search Cyrillic in SQLite3

#6 2021-01-22 17:28:40

Re: Case-insensitive search Cyrillic in SQLite3

#7 2021-01-22 18:09:45

Re: Case-insensitive search Cyrillic in SQLite3

#8 2021-01-23 04:55:28

Re: Case-insensitive search Cyrillic in SQLite3

#9 2021-01-23 10:50:04

Re: Case-insensitive search Cyrillic in SQLite3

#10 2021-01-23 13:13:41

Re: Case-insensitive search Cyrillic in SQLite3

#11 2021-01-23 18:48:00

Re: Case-insensitive search Cyrillic in SQLite3

#12 2021-01-24 06:07:16

Re: Case-insensitive search Cyrillic in SQLite3

#13 2021-01-26 16:36:38

Re: Case-insensitive search Cyrillic in SQLite3

#14 2021-01-26 17:36:55

Re: Case-insensitive search Cyrillic in SQLite3

#15 2021-01-26 18:17:07

Re: Case-insensitive search Cyrillic in SQLite3

Board footer