#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,550
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,550
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,550
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

#14 2024-08-13 07:38:13

sh17
Member
Registered: 2011-08-09
Posts: 31

Re: ICU extension for SQLite

Somehow none of it works. Or it doesn't work with my extension. Does anyone know more about this? I want to download the following extension.

https://github.com/asg017/sqlite-vec/re … .2-alpha.6

Can anyone help?

Offline

#15 2024-08-13 13:41:34

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

Re: ICU extension for SQLite

What is the error? Where?
How do you load it?
What did you try?

See also the forum rules:
1) Could you create a minimal reproducible example?
2) and don't post a lot of content in the forum directly, use gist or an external link.

Offline

#16 2024-08-13 14:00:07

sh17
Member
Registered: 2011-08-09
Posts: 31

Re: ICU extension for SQLite

i have downloaded

https://github.com/asg017/sqlite-vec/re … _64.tar.gz

created a win64 vcl application

and

uses
  ...
  ,mormot.core.os,mormot.db.sql,mormot.db.raw.sqlite3,mormot.core.unicode
  ,mormot.core.base,mormot.db.raw.sqlite3.static,mormot.db.sql.sqlite3
  ,mormot.db.core,mormot.core.datetime

procedure TForm1.FormCreate(Sender: TObject);
var
  sql : TSQLDatabase;
  lLoad : Integer;
  lMsg : PUtf8Char;
begin
  sql := TSQLDatabase.Create(ExtractFilePath(Application.ExeName)+'sample.sql','');
  try
    sqlite3.db_config(sql.DB, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION, 1);
    lLoad:= sql.SQLite3Library.load_extension(sql.DB, PUtf8Char(ExtractFilePath(Application.ExeName)+'vec0.dll'), 0, lMsg);
    if not (lLoad= SQLITE_OK) then
      raise Exception.Create('Error Message');
    sql.Free;
  except
    on E:Exception do
      begin
      end;
  end;

end;

code crash at sqlite3.db_config(....

Offline

#17 2024-08-13 14:11:35

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

Re: ICU extension for SQLite

Please define "code crash".

Offline

#18 2024-08-13 14:17:20

sh17
Member
Registered: 2011-08-09
Posts: 31

Re: ICU extension for SQLite

ab wrote:

Please define "code crash".

---------------------------
GExperts Debugger Exception Notification
---------------------------
Project Project1.exe raised exception class EAccessViolation with message 'Access violation at address 0000000000D1EE5F. Read of address FFFFFFFFFFFFFFFF'.
---------------------------
[&Filter ...] [Ignore &All this Session] [&Break] [Additional &Info] [&Continue]
---------------------------
ThreadId=24592
ProcessId=1
ThreadName="Main"
ExceptionMessage="Access violation at address 0000000000D1EE5F. Read of address FFFFFFFFFFFFFFFF"
ExceptionName="EAccessViolation"
ExceptionDisplayName="$C0000005"
ExceptionAddress=00D1EE5F
FileName=<not available>
LineNumber=<not available>

---------------------------

Offline

#19 2024-08-13 14:52:07

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

Re: ICU extension for SQLite

This is not very helpful.

Which source code line? Enable detailed map generation in your project.
What is the stack trace?

Offline

#20 2024-08-14 06:13:25

sh17
Member
Registered: 2011-08-09
Posts: 31

Offline

#21 2024-08-14 06:19:17

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

Re: ICU extension for SQLite

This does not help.
This is a memory leak report, not a stack trace of the error execution.

At least, use the IDE debugger and make a little debugging by yourself.

Offline

#22 2024-09-14 06:44:44

sh17
Member
Registered: 2011-08-09
Posts: 31

Re: ICU extension for SQLite

Well, I just can't call the function sqlite3.db_config(sql.DB, SQLITE_DBCONFIG_ENABLE_LOAD_EXTENSION,1);
under Delphi Win64. There is an access violation directly in the sqlite library.
Both in the static version and in the DLL. I can't provide a more detailed analysis because I can't debug sqlite.

It works with Delphi Win32. But the extension is only Win64.

(I have tested the extension with DiSQLite3, there they works)

Last edited by sh17 (2024-09-15 09:08:31)

Offline

Board footer

Powered by FluxBB