FTS problem

esmondb · 2016-01-06 11:03:35

I'm having a problem getting FTS4WithoutContent to work when updating records. It's giving this error: 'SQL logic error or missing database'.

I've put a test program below. Is the error from mormot or have I done something wrong?

program Project1;

{$APPTYPE CONSOLE}

uses
  SysUtils, mORMot, SynCommons, mORMotSQLite3, SynSQLite3Static;

type
  TSQLMy_Record = class(TSQLRecord)
  private
    fName: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
  end;

  TSQLMy_FTSRecord = class(TSQLRecordFTS4)
  private
    fName: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
  end;

function CreateModel: TSQLModel;
begin
  result := TSQLModel.Create([TSQLMy_Record, TSQLMy_FTSRecord]);
  result.Props[TSQLMy_FTSRecord].FTS4WithoutContent(TSQLMy_Record, [
    'Name']);
end;

var
  Model: TSQLModel;
  Rest: TSQLRestServerDB;
  rec: TSQLMy_Record;

begin
    Model := CreateModel;
    Rest := TSQLRestServerDB.Create(Model,'test.db3');
    Rest.CreateMissingTables();
    rec := TSQLMy_Record.Create;
    rec.Name := 'Barack Obama';
    Rest.Add(rec,True);
    rec.Name := 'President Barack Obama';
    Rest.Update(rec);
    rec.Free;
    Rest.Free;
    Model.Free;
    readln;
end.

esmondb · 2016-01-07 06:00:21

Looking at the sqlite docs http://www.sqlite.org/fts3.html#*fts4content I noticed

It is not possible to UPDATE or DELETE a row stored in a contentless FTS4 table. Attempting to do so is an error.

However, TSQLRecordFTS4.InitializeTable seems to set up DELETE and INSERT triggers on contentless FTS4 tables.

Should the table be created as an External Content FTS4 Table instead of contentless? (see https://www.sqlite.org/fts3.html#section_6_2_2)

esmondb · 2016-01-08 05:42:02

Changing the following two lines in TSQLRecord.GetSQLCreate seems to fix the problem

28690 result := result+'content="'+aModel.Tables[Props.fFTSWithoutContentTableIndex].SQLTableName+'",'+Props.fFTSWithoutContentExpression+',';
28698 else if Props.fFTSWithoutContentExpression='' then

Could this patch be applied to mormot.pas?

28690c28690
<         result := result+'content="",';
---
>         result := result+'content="'+aModel.Tables[Props.fFTSWithoutContentTableIndex].SQLTableName+'",'+Props.fFTSWithoutContentExpression+',';
28698c28698
<             [self,Name]) else
---
>             [self,Name]) else if Props.fFTSWithoutContentExpression='' then

ab · 2016-01-09 09:21:43

Should be applied now with http://synopse.info/fossil/info/8836e4b035

Thanks for the feedback!

esmondb · 2016-01-10 08:13:27

Thanks but sorry I made a mistake in TSQLRecord.GetSQLCreate. Could the new fix below be applied.

This will probably break the search feature in sample 30 - MVC Server, for non-sqlite dbs. I guess this would need contentless FTS tables as before but with only an insert trigger and some sort of utility function to periodically update the FTS index by rebuilding it.

I wasn't quite sure why the fields in the main record were being concatenated into a single field in the fts record so I made a small change to allow multiple fts fields. If there is only one field in the FTS record it behaves as before and concatenates the main record fields, otherwise the fields in the FTS record definition must be the same or a subset of the main record's fields and they will be mapped across.

28695,28696c28695
<             aModel.Tables[Props.fFTSWithoutContentTableIndex].SQLTableName+'",'+
<             Props.fFTSWithoutContentExpression+',';
---
>             aModel.Tables[Props.fFTSWithoutContentTableIndex].SQLTableName+'",';
28705,28706c28704
<           if Props.fFTSWithoutContentExpression='' then
<             result := result+Name+',';
---
>           result := result+Name+',';
30083c30081,30083
<         exp := exp+'||'' ''||new.'+ContentTableFieldNames[i];
---
>         if high(ContentTableFieldNames) > 0 then
>           exp := exp+',new.'+ ContentTableFieldNames[i] else
>           exp := exp+'||'' ''||new.'+ContentTableFieldNames[i];
44200c44200
<     fts,main,ftsmainfield: RawUTF8;
---
>     fts,main,ftsfields: RawUTF8;
44211c44211
<   ftsmainfield := Props.Props.MainFieldName(true);
---
>   ftsfields := Props.Props.SQLTableSimpleFieldsNoRowID;
44221c44221
<     [main,main,fts,ftsmainfield,Props.fFTSWithoutContentExpression]);
---
>     [main,main,fts,ftsfields,Props.fFTSWithoutContentExpression]);
44224c44224
<     [main,main,fts,ftsmainfield,Props.fFTSWithoutContentExpression]);
---
>     [main,main,fts,ftsfields,Props.fFTSWithoutContentExpression]);

ab · 2016-01-10 19:36:33

You latest patch is breaking the framework even more.
Sample MVC Blog just fails miserably now...

I've made a refactoring of the feature, according to the official "external content FTS4" documentation.
Please check http://synopse.info/fossil/info/2451afeed5

Thanks for the feedback!

esmondb · 2016-01-11 12:12:27

Many thanks . It's working fine now.

This probably isn't related but I can't get the main sample 30 (MVC Blog) to run when compiled on Delphi 2007 and win7 64bit. It's giving an AV in the ComputeMinimalData procedure in MVCViewModel.pas. Line 207:

if RestModel.BatchSend(batch,articles)=HTML_SUCCESS then begin

calls TSQLRestServerDB.MainEngineAdd in mORMotSQLite3.pas which causes an access violation at line 828:

AddInt64(TInt64DynArray(fBatchID),fBatchIDCount,result);

Having trouble finding the solution . Any help welcome!

ab · 2016-01-11 14:24:09

Perhaps the Delphi 2007 compiler has issues with TInt64DynArray(fBatchID) trans-typing.

Please try http://synopse.info/fossil/info/9a55257d23

esmondb · 2016-01-11 14:41:11

Great Thanks, it's all working now.

mORMot Open Source

#1 2016-01-06 11:03:35

FTS problem

#2 2016-01-07 06:00:21

Re: FTS problem

#3 2016-01-08 05:42:02

Re: FTS problem

#4 2016-01-09 09:21:43

Re: FTS problem

#5 2016-01-10 08:13:27

Re: FTS problem

#6 2016-01-10 19:36:33

Re: FTS problem

#7 2016-01-11 12:12:27

Re: FTS problem

#8 2016-01-11 14:24:09

Re: FTS problem

#9 2016-01-11 14:41:11

Re: FTS problem

Board footer