SYSTEMNOCASE accent insensitive

polidados · 2018-01-12 11:07:50

Hello,

I'm having an issue with SYSTEMNOCASE collation in SQLite.
I read the documentation and I searched the forum about it. In both cases I have found this collation is accent insensitive.
Unfortunatelly it's not working for me.
For example if I have records in the database with names JOAO, JOÃO, Joao and João and if I search JOAO, it retrieves me just JOAO and Joao. The records with accented names are not retrieved. This happens in my application, in SQLiteStudio and in SynDBExplorer.
Would I have to do something special to work the SYSTEMNOCASE accent insensitive?

Thanks!

ab · 2018-01-12 12:13:25

Did you try WIN32NOCASE ?

polidados · 2018-01-12 13:05:13

Thank you for the quick answer.
Yeah, I tried right now. I created a new database with WIN32NOCASE and it didn't work too.
It also worked for case insensitive, but not for accent insensitive.

ab · 2018-01-12 14:31:21

So create your own collation function...

polidados · 2018-01-15 10:28:46

I'm sorry, but I already did it.
I created the NOACCENTS collation like SYSTEMNOCASE is.
I created a method to this collation that changes the accented characters and I set this collation for all UTF8 fields.
But it didn't work.

Filipe

ab · 2018-01-15 15:16:55

You have to specify the collation when the table is created.

noobies · 2018-01-16 08:50:42

polidados, pls share sample then u solve problem, i have too problem collation with russian letter(

Last edited by noobies (2018-01-16 08:50:54)

polidados · 2018-01-16 10:41:13

Take a look at the partial code:

We have a descendant class of TSQLRestServerDB. Its declaration is the following:

  TServerBD = class(TSQLRestServerDB)
  private
    FModel: TSQLModel;
    {...}
  public
    constructor Create(const aDatabase: String; aUseAuthentication: Boolean = True);
    {...}
  end;

Now the Create() method implementation:

constructor TServerBD.Create(const aDatabase: String;
  aUseAuthentication: Boolean);
begin
  // Create the model
  FModel := CreateModel;

  // Call the ancestor constructor
  inherited Create(FModel, aDatabase, aUseAuthentication);

  // Create the NOACCENTS collation, using our function that replaces accented characters
  // and after calls the UTF8ILComp() function
  DB.SQLite3Library.create_collation(DB.DB, 'NOACCENTS', SQLITE_UTF8, nil, Utf8SQLCompNoAccent);

  // Set the NOACCENTS collation for all RawUTF8 fields
  FModel.SetCustomCollationForAll(sftUTF8Text, 'NOACCENTS');

  // Create the missing tables in the database
  CreateMissingTables(ExeVersion.Version.Version32, [itoNoAutoCreateUsers]);

  {...}
end;

ab · 2018-01-16 11:04:50

Is the Utf8SQLCompNoAccent() function actually called?

polidados · 2018-01-16 11:50:11

I implemented the Utf8SQLCompNoAccent() function, and I passed it by parameter to the create_collation(). Just this.

I searched in mORMot code for the usage of the Utf8SQLCompNoCase() function (for SYSTEMNOCASE collation).
And it was the same. I only found its implementation and where it's passed to the create_collation().

Then, should I call the Utf8SQLCompNoAccent() function from any other place?

ab · 2018-01-16 12:45:52

This is not what I meant.

Use the debugger to find out if the Utf8SQLCompNoAccent() function is actually called during process.
Otherwise, the table was not created with the NOACCENTS collation.

Also note that you need to delete the database first, so that CreateMissingTables() will properly initialize the table and its fields collation.

polidados · 2018-01-16 13:34:04

ab wrote:

This is not what I meant.

Apologize me, now I understood.

ab wrote:

Use the debugger to find out if the Utf8SQLCompNoAccent() function is actually called during process.

I already did it. It's weird. Because it's being called in a few moments. But when I select or sort something, it's not being called.

ab wrote:

Also note that you need to delete the database first, so that CreateMissingTables() will properly initialize the table and its fields collation.

Yes, I was careful.
The fields were created with the NOACCENTS collation.

polidados · 2018-01-16 13:37:03

polidados wrote:

I already did it. It's weird. Because it's being called in a few moments. But when I select or sort something, it's not being called.

I tried now with the others collations and it's happening with any collation function.

ab · 2018-01-16 13:39:58

What is a Sqlite3 tool showing for the table collation?

polidados · 2018-01-16 13:50:54

I am using SQLiteStudio and SynDBExplorer. If I select using both of them it doesn't work, nor in my application.

But I did a test right now in my application and in SynDBExplorer. Using the "like" operator, the accent insensitive doesn't work. But using the "=" operator, it works!

ab · 2018-01-16 14:06:50

IIRC "like" in Sqlite3 doesn't use collations - see https://www.sqlite.org/c3ref/create_collation.html
and https://sqlite.org/lang_expr.html#like

You may try using the ICU extension, or override the like() function to handle your own process.

SQlite3 doc wrote:

The sqlite3_create_function() interface can be used to override the like() function

A dedicated function, not overriding LIKE, may even be the best option.

polidados · 2018-01-19 13:45:49

Hello ab!

After all, I decided to use the sqlite3.create_function, and override the like() function.
Now it's working fine.

Thank you very much!

mORMot Open Source

#1 2018-01-12 11:07:50

SYSTEMNOCASE accent insensitive

#2 2018-01-12 12:13:25

Re: SYSTEMNOCASE accent insensitive

#3 2018-01-12 13:05:13

Re: SYSTEMNOCASE accent insensitive

#4 2018-01-12 14:31:21

Re: SYSTEMNOCASE accent insensitive

#5 2018-01-15 10:28:46

Re: SYSTEMNOCASE accent insensitive

#6 2018-01-15 15:16:55

Re: SYSTEMNOCASE accent insensitive

#7 2018-01-16 08:50:42

Re: SYSTEMNOCASE accent insensitive

#8 2018-01-16 10:41:13

Re: SYSTEMNOCASE accent insensitive

#9 2018-01-16 11:04:50

Re: SYSTEMNOCASE accent insensitive

#10 2018-01-16 11:50:11

Re: SYSTEMNOCASE accent insensitive

#11 2018-01-16 12:45:52

Re: SYSTEMNOCASE accent insensitive

#12 2018-01-16 13:34:04

Re: SYSTEMNOCASE accent insensitive

#13 2018-01-16 13:37:03

Re: SYSTEMNOCASE accent insensitive

#14 2018-01-16 13:39:58

Re: SYSTEMNOCASE accent insensitive

#15 2018-01-16 13:50:54

Re: SYSTEMNOCASE accent insensitive

#16 2018-01-16 14:06:50

Re: SYSTEMNOCASE accent insensitive

#17 2018-01-19 13:45:49

Re: SYSTEMNOCASE accent insensitive

Board footer