mORMot Open Source

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

auxiliary Rtree columns

I get errors creating rtree tables with auxillary columns. Changing line 32526 of mormot.pas from:

  if Name[1]='_' then // auxiliary columns for SQlite3 >= 3.24.0
    result := FormatUTF8('%+% %,',[result,@Name[2],Props.Props.SQLFieldTypeToSQL(i)]) else

to:

  if Name[1]='_' then // auxiliary columns for SQlite3 >= 3.24.0
    result := FormatUTF8('%+% %',[result,copy(Name,2,MaxInt),Props.Props.SQLFieldTypeToSQL(i)]) else

seems to fix it.

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: auxiliary Rtree columns

I've found another problem auxiliary columns. Because the underscore gets removed CreateMissingTables() always thinks the model has changed. This causes an sqlite error saying virtual tables can't be edited. Any suggestions on a solution?
Thanks

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: auxiliary Rtree columns

Another problem... RTREE_MAX_DIMENSION in mORMot.pas becomes invalid if auxiliary columns are used

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: auxiliary Rtree columns

Auxiliary Columns were not fully tested yet, indeed...
Sorry!

RTREE_MAX_DIMENSION is used only before the auxiliary columns IIRC.

I don't remember where the '_' is removed?

See https://synopse.info/fossil/info/c96db70ed9 about the name creation.

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: auxiliary Rtree columns

I think the '_' gets changed to a '+' by mORMot which sqlite swallows returning just the field name. I'll look more.

Thanks for https://synopse.info/fossil/info/c96db70ed9 but it still adds an extra comma at the end ('%+% %,') which sqlite seems to ignore.

Thanks for your work!

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: auxiliary Rtree columns

Finally got round to looking at this again. These changes to mORMot.pas seem to fix it:
line 33916:

       if Props.SimpleFields[i].Name[1] = '_' then    
        result := result+copy(Props.SimpleFields[i].Name,2,MaxInt)+','
      else
        result := result+Props.SimpleFields[i].Name+','; // valid simple fields

  at line 33947 the following check needs removing

    if (Props.RTreeCoordBoundaryFields<2) or
       (Props.RTreeCoordBoundaryFields>RTREE_MAX_DIMENSION*2) or
       (Props.RTreeCoordBoundaryFields and 2<>0) then
      raise EModelException.CreateUTF8('% has % fields: RTREE expects 2,4,6..% boundary columns',
        [Props.Table,Props.RTreeCoordBoundaryFields,RTREE_MAX_DIMENSION*2]);

line 51273/4

if F.Name[1] = '_' then begin
          fSQLTableSimpleFieldsNoRowID := fSQLTableSimpleFieldsNoRowID+copy(F.Name,2,MaxInt)+',';
          fSQLTableRetrieveAllFields := fSQLTableRetrieveAllFields+','+copy(F.Name,2,MaxInt);
        end else begin
          fSQLTableSimpleFieldsNoRowID := fSQLTableSimpleFieldsNoRowID+F.Name+',';
          fSQLTableRetrieveAllFields := fSQLTableRetrieveAllFields+','+F.Name;
        end;

line 51485

if Fields.List[W.Fields[i]].Name[1] = '_' then
      W.ColNames[n] := copy(Fields.List[W.Fields[i]].Name, 2, MaxInt)
    else
      W.ColNames[n] := Fields.List[W.Fields[i]].Name;

the comma in 32526 isn't needed

  result := FormatUTF8('%+% %',[result,copy(Name,2,maxInt),Props.Props.SQLFieldTypeToSQL(i)]) else

Please could you review this?

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: auxiliary Rtree columns

I've had another look at this and found a better way to remove the '_'.

@ab please could you have a look at this pull request

Thanks