#1 2020-12-28 07:48:42

Aknehsas
Member
Registered: 2020-08-03
Posts: 4

ICU extension for SQLite

I am trying to find out a way to load ICU extension for SQLite due to issue with 'case-sensitive LIKE for Cyrillic'. Is it possible to load and use the extension with static SynSQLite3Static? I found out that first I must enable sqlite to load extensions. But how to do it in mORMot?

Offline

#2 2020-12-28 08:55:57

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

Re: ICU extension for SQLite

You can try to download it as a dynamic extension.
But you need to recompile SQlite3 static, disabling the following define in sqlite3mc.c :

// #define SQLITE_OMIT_LOAD_EXTENSION 1
// we don't need/allow extension in an embedded engine

Another approach may be to define a custom LIKE SQLite3 function, which calls the ICU comparison available in SynFPCLinux.pas.

I will undefine SQLITE_OMIT_LOAD_EXTENSION, and define the Session Extension for the next static build.

Offline

#3 2020-12-28 09:03:56

Aknehsas
Member
Registered: 2020-08-03
Posts: 4

Re: ICU extension for SQLite

ab wrote:

You can try to download it as a dynamic extension.
But you need to recompile SQlite3 static, disabling the following define in sqlite3mc.c :

// #define SQLITE_OMIT_LOAD_EXTENSION 1
// we don't need/allow extension in an embedded engine

I will undefine SQLITE_OMIT_LOAD_EXTENSION, and define the Session Extension for the next static build.

Thanks! But how to load an extension for SQLite3 static?

Offline

#4 2020-12-28 09:09:46

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

Re: ICU extension for SQLite

You need to call the sqlite3_load_extension() function.

So perhaps it is easier to define in sqlite3mc.c and recompile it:

#define SQLITE_ENABLE_ICU

A custom LIKE function calling the ICU comparison available in SynFPCLinux.pas may be a better alternative.

Offline

#5 2020-12-28 09:21:41

Aknehsas
Member
Registered: 2020-08-03
Posts: 4

Re: ICU extension for SQLite

Thank you very much! I will try

Offline

#6 2022-07-19 08:10:55

cybexr
Member
Registered: 2016-09-14
Posts: 78

Re: ICU extension for SQLite

ab wrote:

You need to call the sqlite3_load_extension() function.

So perhaps it is easier to define in sqlite3mc.c and recompile it:

#define SQLITE_ENABLE_ICU

A custom LIKE function calling the ICU comparison available in SynFPCLinux.pas may be a better alternative.

Hi ab, just tried to load a tokenizer extension,  in mormot2  \mORMot2\ex\extdb-bench\Perftestcases , add this line:

  if not Client.DB.SQLite3Library.load_extension(Client.DB.DB, 'simple', '', Msg)= SQLITE_OK then
    raise Exception.Create('Error Message');

the load_extension returns SQLITE_OK,  but Msg contains 'not authorized' , am I missed something ?

Offline

#7 2022-07-19 08:37:10

cybexr
Member
Registered: 2016-09-14
Posts: 78

Re: ICU extension for SQLite

tried with mormot1 also ,  modify sources of SynDBExplorer(mormot 1 sample 12 )to load an external SQLite3.dll (3.39.0 , this dll can load extension, verified by SQLiteExpert tool ),

then excute SQL:  select load_extension('./simple.dll');   

got Exception:  "not authorized " too.

Offline

#8 2022-07-19 08:48:20

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

Re: ICU extension for SQLite

I never used extensions, so perhaps our build do not allow them.
We disallow them for security reasons, too.
You may need to rebuild SQLite3 from sources.

But why do you need ICU?
Since recent mORMot 2 revisions, you have the UNICODENOCASE collation which could be used, with high performance and without any need of ICU.

Offline

#9 2022-07-19 09:40:46

cybexr
Member
Registered: 2016-09-14
Posts: 78

Re: ICU extension for SQLite

in fact I wants to load a FTS5 tokenizer extension,  which mainly deals with Chinese characters.  As Chinese differs a lot with alphabetic language (English French ...) , tokenizer is very complicated ,  so some professional tokenizer extension are indispensable.

Offline

#10 2022-07-19 12:06:01

cybexr
Member
Registered: 2016-09-14
Posts: 78

Re: ICU extension for SQLite

In mormot2,  add this line before Client.DB.SQLite3Library.load_extension() will work as expected.

  sqlite3.db_config(Client.DB.DB, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 1);

according to sqlite3.h, the root cause is :

** ^Extension loading must be enabled using
** [sqlite3_enable_load_extension()] or
** [sqlite3_db_config](db,[SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION],1,NULL)
** prior to calling this API,
** otherwise an error will be returned.

and thanks to AB  for this amazing framework and lasting enthusiasm  tongue

Offline

#11 2022-07-23 18:59:32

Leslie7
Member
Registered: 2015-06-25
Posts: 248

Re: ICU extension for SQLite

cybexr,

Can you post a working example?

Last edited by Leslie7 (2022-07-24 04:01:57)

Offline

#12 2022-07-25 13:20:41

cybexr
Member
Registered: 2016-09-14
Posts: 78

Re: ICU extension for SQLite

just verify the possiblity,  my sample-exe was build with mormot static SQlite3 Engine,  place the FTS-tokenizer-extension-dll to exe folder

the sample-exe comes from : mormot2/ex/extdb-bench/PerfTestCase

  sqlite3.db_config(Client.DB.DB, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 1);
  Load:= Client.DB.SQLite3Library.load_extension(Client.DB.DB, 'simple.dll', 0, Msg);
  if not (Load= SQLITE_OK) then
    raise Exception.Create('Error Message');

then some where you can create a table with new tokenizer

  Client.DB.Execute('CREATE VIRTUAL TABLE t33 USING fts5(text, tokenize = ''simple''); ');

Offline

#13 2023-07-05 08:07:27

zhangguichao
Member
Registered: 2023-03-21
Posts: 29

Re: ICU extension for SQLite

@Aknehsas  您好,我也遇到了同样的问题,能提供一个例子吗?包含dll(如果需要的话),十分感谢。

Offline

Board footer

Powered by FluxBB