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

edwinsn · 2020-12-21 10:19:17

@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!

edwinsn · 2020-12-21 10:30:44

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

edwinsn · 2020-12-21 11:49:18

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

edwinsn · 2020-12-22 06:34:16

@ab,

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

zed · 2020-12-22 09:02:01

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

edwinsn · 2020-12-22 12:25:30

@zed, I use SynSQLite3Static.pas

edwinsn · 2020-12-24 06:25:56

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

zed · 2020-12-24 12:09:07

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.

edwinsn · 2020-12-24 15:46:55

@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!

edwinsn · 2020-12-24 15:49:26

@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!

ab · 2020-12-24 17:22:44

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

edwinsn · 2020-12-25 05:50:25

@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?

ab · 2020-12-25 10:56:59

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.

edwinsn · 2020-12-25 12:47:50

@ab,

Thanks for the detailed explanation!

mORMot Open Source

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Board footer