#1 2020-12-21 10:19:17

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

@ab,

SQLite Release 3.34.0 On 2020-12-01 added a so-called trigram tokenizer for FTS5, and it's not integrated into `TSQLRecord.GetSQLCreate` yet in mORMot 1.

Would you add support for that? Thanks!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#2 2020-12-21 10:30:44

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

OK, I figured out I can define a `TSQLRecordFTS5Trigram= class(TSQLRecordFTS5)' class to use the new tokenizer, and `TSQLRecord.GetSQLCreate` can generate the correct SQL.

But then I got the "no such module: fts5, extended_errcode=1" error...


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#3 2020-12-21 11:49:18

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

FTS5 Trigramidx info page: https://sqlite.org/fts5.html#trigramidx


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#4 2020-12-22 06:34:16

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

@ab,

Would you advise a workaround for using hte new Trigramidx  FTS5 tokenizer? Thanks!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#5 2020-12-22 09:02:01

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 102

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

Do you link with sqlite3.dll or with SynSQLite3Static.pas?

Offline

#6 2020-12-22 12:25:30

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

@zed, I use SynSQLite3Static.pas


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#7 2020-12-24 06:25:56

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

@zed, correction: For win32 I use I use SynSQLite3Static.pas, for Win64 I use the sqlite3.dll


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#8 2020-12-24 12:09:07

zed
Member
From: Belarus
Registered: 2015-02-26
Posts: 102

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

It works for me with SynSQLite3Static and sqlite3.dll (both tested only for Win32). I used precompiled dll https://www.sqlite.org/download.html

mORMot 1.18.6192, Delphi 10.3.3 CE, test code:

program SynFTS5Test;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  SynCommons,
  SynSQLite3,
  SynSQLite3Static,
  mORMot,
  mORMotSQLite3;

type
  TSQLRecordFTS5Trigram = class(TSQLRecordFTS5);

  TSQLTextRecord = class(TSQLRecordFTS5Trigram)
  protected
    FText: RawUTF8;
  published
    property Text: RawUTF8 read FText write FText;
  end;

var
  VModel: TSQLModel;
  VClient: TSQLRestClientDB;
begin
  //sqlite3 := TSQLite3LibraryDynamic.Create;

  Writeln(SYNOPSE_FRAMEWORK_FULLVERSION);
  try
    VModel := TSQLModel.Create([TSQLTextRecord]);
    VClient := TSQLRestClientDB.Create(VModel, nil, 'fts5.db3', TSQLRestServerDB);
    try
      VClient.Server.CreateMissingTables;
      // ...
    finally
      VClient.Free;
      VModel.Free;
    end;
  except
    on E: Exception do begin
      Writeln(E.ClassName, ': ', E.Message);
    end;
  end;

  Writeln('Press ENTER to exit...');
  Readln;
end.

Offline

#9 2020-12-24 15:46:55

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

@zed,

Wow, thanks for the help and I appreciate that!

So I confirm sqlite3 dll from https://synopse.info/files/SQLite3-64.7z does NOT working with fts5 Trigram, but dll from https://www.sqlite.org/download.html works!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#10 2020-12-24 15:49:26

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

@ab,

So I'll use the sqlite3.dll downloaded from https://www.sqlite.org/download.html for the Win64 target. I assume there will be no other drawbacks? Thanks!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#11 2020-12-24 17:22:44

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,172
Website

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

You are right, FTS5 was not compiled.
I have ensured that SQlite3-64.dll will include FTS5 for the next compilation.
Check https://synopse.info/fossil/info/cc262ba058

Offline

#12 2020-12-25 05:50:25

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

@ab,

Thanks! Since  now sqlite.org officially provides both win32 and win64 versions of DLL download, why not just use their version...Wait! I remember now, mORMot includes an encryption layer for sqlite...

So in other words, if I don't use the mORMot encryption layer for SQLite, I can just use the official dll from sqlite.org, right?


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

#13 2020-12-25 10:56:59

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 13,172
Website

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

Only Delphi XE4 needs an external DLL. This is a Delphi XE4 Bug. All other Delphi Win64 versions have no problem with our static Sqlite3 .o which supports encryption.
But our dll doesn't support the encryption layer, due to technical restrictions.
So there is not much difference with the official DLL and the mORMot 64-bit DLL yet.

We will probably include some more extensions in the future (like sessions), so perhaps the mORMot dll may be of better interrest at that time.

Offline

#14 2020-12-25 12:47:50

edwinsn
Member
Registered: 2010-07-02
Posts: 1,176

Re: No support for trigram FTS5 tokenizer added in SQLit3.34.0(2020-12-01)

@ab,

Thanks for the detailed explanation!


Delphi XE4 Pro on Windows 7 64bit.
Lazarus trunk built with fpcupdelux on Windows with cross-compile for Linux 64bit.

Offline

Board footer

Powered by FluxBB