mORMot Open Source

ttomas

Member

Registered: 2013-03-08

Posts: 117

FTS5 content table bug

I try TSQLRecordFTS5Unicode61 with FTS4WithoutContent.
Table and triggers are created but any insert raise exception docid column don't exists in search table from insert trigger.
Reading FTS5 documentation i make this changes. Main diffeence, FTS4 use docid, FTS5 use rowid.

diff --git "a/SQLite3/mORMot.pas" "b/SQLite3/mORMot.pas"
index 4b8f9288..7d4dfc61 100644
--- "a/SQLite3/mORMot.pas"
+++ "b/SQLite3/mORMot.pas"
@@ -31214,8 +31217,12 @@ begin
     case Props.Kind of
     rFTS3, rFTS4, rFTS5: begin
       if (Props.fFTSWithoutContentFields<>'') and (Props.fFTSWithoutContentTableIndex>=0) then
+      begin
         result := FormatUTF8('%content="%",',[result,
           aModel.Tables[Props.fFTSWithoutContentTableIndex].SQLTableName]);
+        if Props.Kind = rFTS5 then
+           result := FormatUTF8('%content_rowid="ID",',[result]);
+      end;
       for i := 0 to fields.Count-1 do
         result := result+fields.List[i].Name+',';
       tokenizer := 'simple';
@@ -49266,18 +49273,36 @@ begin
   fts := Props.Props.SQLTableName;
   ftsfields := Props.Props.SQLTableSimpleFieldsNoRowID;
   // see http://www.sqlite.org/fts3.html#*fts4content
-  Server.ExecuteFmt('CREATE TRIGGER %_bu BEFORE UPDATE ON % '+
-    'BEGIN DELETE FROM % WHERE docid=old.rowid; END;',
-    [main,main,fts]);
-  Server.ExecuteFmt('CREATE TRIGGER %_bd BEFORE DELETE ON % '+
-    'BEGIN DELETE FROM % WHERE docid=old.rowid; END;',
-    [main,main,fts]);
-  Server.ExecuteFmt('CREATE TRIGGER %_au AFTER UPDATE ON % '+
-    'BEGIN INSERT INTO %(docid,%) VALUES(new.rowid%); END;',
-    [main,main,fts,ftsfields,Props.fFTSWithoutContentFields]);
-  Server.ExecuteFmt('CREATE TRIGGER %_ai AFTER INSERT ON % '+
-    'BEGIN INSERT INTO %(docid,%) VALUES(new.rowid%); END;',
-    [main,main,fts,ftsfields,Props.fFTSWithoutContentFields]);
+  if Props.Kind=rFTS5 then
+  begin
+    Server.ExecuteFmt('CREATE TRIGGER %_bu BEFORE UPDATE ON % '+
+      'BEGIN INSERT INTO %(%,rowid,%) VALUES(''delete'',old.rowid%); END;',
+      [main,main,fts,fts,ftsfields, StringReplaceAll(Props.fFTSWithoutContentFields, 'new.', 'old.')]);
+    Server.ExecuteFmt('CREATE TRIGGER %_bd BEFORE DELETE ON % '+
+      'BEGIN INSERT INTO %(%,rowid,%) VALUES(''delete'',old.rowid%); END;',
+      [main,main,fts,fts,ftsfields, StringReplaceAll(Props.fFTSWithoutContentFields, 'new.', 'old.')]);
+    Server.ExecuteFmt('CREATE TRIGGER %_au AFTER UPDATE ON % '+
+      'BEGIN INSERT INTO %(rowid,%) VALUES(new.rowid%); END;',
+      [main,main,fts,ftsfields,Props.fFTSWithoutContentFields]);
+    Server.ExecuteFmt('CREATE TRIGGER %_ai AFTER INSERT ON % '+
+      'BEGIN INSERT INTO %(rowid,%) VALUES(new.rowid%); END;',
+      [main,main,fts,ftsfields,Props.fFTSWithoutContentFields]);
+  end
+  else
+  begin
+    Server.ExecuteFmt('CREATE TRIGGER %_bu BEFORE UPDATE ON % '+
+      'BEGIN DELETE FROM % WHERE docid=old.rowid; END;',
+      [main,main,fts]);
+    Server.ExecuteFmt('CREATE TRIGGER %_bd BEFORE DELETE ON % '+
+      'BEGIN DELETE FROM % WHERE docid=old.rowid; END;',
+      [main,main,fts]);
+    Server.ExecuteFmt('CREATE TRIGGER %_au AFTER UPDATE ON % '+
+      'BEGIN INSERT INTO %(docid,%) VALUES(new.rowid%); END;',
+      [main,main,fts,ftsfields,Props.fFTSWithoutContentFields]);
+    Server.ExecuteFmt('CREATE TRIGGER %_ai AFTER INSERT ON % '+
+      'BEGIN INSERT INTO %(docid,%) VALUES(new.rowid%); END;',
+      [main,main,fts,ftsfields,Props.fFTSWithoutContentFields]);
+  end;
 end;

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: FTS5 content table bug

Could you please make a pull request on github?

ttomas

Member

Registered: 2013-03-08

Posts: 117

Re: FTS5 content table bug

Done
https://github.com/synopse/mORMot/pull/327

Also added changes in SynDBVCL, SynDBMidas for use FPC TBufDataset. Current units don't compile in FPC.With this changes I can use SynDB, SynDBRemote to TBufDataset in FPC.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,206

Website

Re: FTS5 content table bug

Merged.

Thanks a lot!