Handling SYSTEMNOCASE with 3rd-party software

negativethirteen · 2013-03-05 18:20:47

I've searched the forum for a way to deal with SYSTEMNOCASE collation outside the mORMot Framework and haven't had much luck.

I'd like to be able to load/connect to the SQLite file with 3rd-party software for reporting (e.g. connecting through ODBC driver with LibreOffice Base or Microsoft Access).
Connecting works, the problem is I can't generate reports that use collation because SYSTEMNOCASE is not recognized.

Is there a way around this?

colbert · 2013-03-10 12:44:27

I have the same problem but with SQlite with other management tools.

Can we use the framework to create a Database without this Collation, can I use other collations instead?

Last edited by colbert (2013-03-10 12:46:01)

colbert · 2013-03-10 13:01:31

One way to solve this problem is to create a Loadable Extensions with this collation.

http://www.sqlite.org/cvstrac/wiki?p=LoadableExtensions

Do anyone know how can we do that with SYSTEMNOCASE?

ab · 2013-03-11 09:23:20

SynDBExplorer is able to open and work with SYSTEMNOCASE collation.

But for external use, like ODBC drivers, it won't work directly.
Loadable Extensions are not loaded with all SQLite3 software (far from it), and probably won't work with ODBC.
And I'm not even sure it will work with statically linked SQLite3 engine...

I've added a ticket for this feature request.
Implementation proposal is to allow an option at the TSQLModel level, to specify a custom collation, to be used instead of SYSTEMNOCASE.
See http://synopse.info/fossil/info/bfdc198e70

ab · 2013-03-12 15:02:50

Implemented by http://synopse.info/fossil/info/325006088d and http://synopse.info/fossil/info/4abb6deba7

Could be used as such:

type
  TSQLMyRecord = class(TSQLRecord)
  protected
    class procedure InternalRegisterCustomProperties(Props: TSQLRecordProperties); override;
  ....

class procedure TSQLMyRecord.InternalRegisterCustomProperties(Props: TSQLRecordProperties);
begin
  Props.SetCustomCollationForAllRawUTF8('NOCASE');
end;

Or the more direct TSQLModel.SetCustomCollationForAllRawUTF8(), to be called when you create the TSQLModel on both Client and Server side:

Model := TSQLModel.Create([TSQLMyRecord]);
Model.SetCustomCollationForAllRawUTF8('NOCASE');

This later method will process all TSQLRecord classes of the database model.

colbert · 2013-03-12 17:09:30

With this new feature (using

 Model.SetCustomCollationForAllRawUTF8('NOCASE')

) will have any change on the way that the database handles latin characters?

Will still be case insensitive?

Can we do it if a database that is already created or we have to create a new database?

Last edited by colbert (2013-03-12 18:49:31)

ab · 2013-03-12 22:53:04

NOCASE is the default case insensitive comparison of SQlite.
But it won't handle accentuated characters, as did SYSTEMNOCASE.

We have to create a new database, AFAIK.
May be SQLite3 is able to update the definition. But the ORM does not handle this: you have to set the modification by SQL.

negativethirteen · 2013-03-13 17:00:27

Thank you very much for this.

My interim fix was a conditional compilation flag NOCUSTOMCOLLATE that changed the definition in the wrapper (SQLFieldTypeToSQL) from SYSTEMNOCASE to NOCASE.
I needed something quick for a demo I had to do last week so I went with the "quick and dirty" method. I'm glad there's an official way to do that now.

mORMot Open Source

#1 2013-03-05 18:20:47

Handling SYSTEMNOCASE with 3rd-party software

#2 2013-03-10 12:44:27

Re: Handling SYSTEMNOCASE with 3rd-party software

#3 2013-03-10 13:01:31

Re: Handling SYSTEMNOCASE with 3rd-party software

#4 2013-03-11 09:23:20

Re: Handling SYSTEMNOCASE with 3rd-party software

#5 2013-03-12 15:02:50

Re: Handling SYSTEMNOCASE with 3rd-party software

#6 2013-03-12 17:09:30

Re: Handling SYSTEMNOCASE with 3rd-party software

#7 2013-03-12 22:53:04

Re: Handling SYSTEMNOCASE with 3rd-party software

#8 2013-03-13 17:00:27

Re: Handling SYSTEMNOCASE with 3rd-party software

Board footer