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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

FTS problem

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.

Offline

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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

Re: FTS problem

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)

Offline

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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

Re: FTS problem

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,131
Website

Re: FTS problem

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

Thanks for the feedback!

Online

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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

Re: FTS problem

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

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,131
Website

Re: FTS problem

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

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

Thanks for the feedback!

Online

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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

Re: FTS problem

Many thanks smile. 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 sad. Any help welcome!

Offline

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

ab
Administrator
From: France
Registered: 2010-06-21
Posts: 14,131
Website

Re: FTS problem

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

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

Online

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

esmondb
Member
From: London
Registered: 2010-07-20
Posts: 299

Re: FTS problem

Great smile Thanks, it's all working now.

Offline

Board footer

Powered by FluxBB