ICU extension for SQLite

Aknehsas · 2020-12-28 07:48:42

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?

ab · 2020-12-28 08:55:57

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.

Aknehsas · 2020-12-28 09:03:56

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?

ab · 2020-12-28 09:09:46

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.

Aknehsas · 2020-12-28 09:21:41

Thank you very much! I will try

cybexr · 2022-07-19 08:10:55

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 ?

cybexr · 2022-07-19 08:37:10

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.

ab · 2022-07-19 08:48:20

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.

cybexr · 2022-07-19 09:40:46

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.

cybexr · 2022-07-19 12:06:01

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

Leslie7 · 2022-07-23 18:59:32

cybexr,

Can you post a working example?

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

cybexr · 2022-07-25 13:20:41

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''); ');

zhangguichao · 2023-07-05 08:07:27

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

sh17 · 2024-08-13 07:38:13

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?

ab · 2024-08-13 13:41:34

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.

sh17 · 2024-08-13 14:00:07

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(....

ab · 2024-08-13 14:11:35

Please define "code crash".

sh17 · 2024-08-13 14:17:20

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>

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

ab · 2024-08-13 14:52:07

This is not very helpful.

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

sh17 · 2024-08-14 06:13:25

https://drive.google.com/file/d/14t2kwg … sp=sharing

ab · 2024-08-14 06:19:17

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.

sh17 · 2024-09-14 06:44:44

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)

mORMot Open Source

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

ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

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

Re: ICU extension for SQLite

Board footer