mORMot Open Source

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

[Solved] Inconsistency in returned json and cache problems

Anyone else encountered problem with the ID / RowID ambiguity in the returned JSON? How to get around this?

TSQLRecord.GetAsDocVariant  and TSQLRecord.GetSimpleFieldsAsDocVariant return JSON with RowID property.

But

TSQLRestServerDB.RetrieveDocVariant return JSON with ID property.

Another problem is that when cache are enabled two situations occur ...

First, with cache enabled it is mandatory to pass the fields names.
If they are not passed always returns null. So the same call returns different results, with cache enabled or not:

Cache disabled

Server.RetrieveDocVariant(TSQLUser,'ID=?',[ID],'');

Return:
{
    "Result": {
        "ID": 1,
        "Name": "Xyz",
       ...
  }
}

Cache enabled

Serve.Cache.SetCache(TSQLUser);
Server.RetrieveDocVariant(TSQLUser,'ID=?',[ID],''); <-- empty field names as call above

Return:
{
    "Result": null
}

The second question is that with cache enabled, it returns RowID, that is, even the same method RetrieveDocVariant has ambiguity in this property.

Cache disabled

Server.RetrieveDocVariant(TSQLUser,'ID=?',[ID],'Name');

Return:
{
    "Result": {
        "ID": 1,  <-- ID
        "Name": "Xyz",
       ...
  }
}

Cache enabled

Serve.Cache.SetCache(TSQLUser);

Server.RetrieveDocVariant(TSQLUser,'ID=?',[ID],'Name'); <- Forced to pass at least one field name

Return:
{
    "Result": {
        "RowID": 1,  <-- RowID
        "Name": "Xyz",
       ...
  }
}

My Suggestion to fix this is to create a overrited method in GetAsDocVariant to allow ForceNoRowID param as suggested here:
(Topic deleted since it ended up being a duplicate)

And change RetrieveDocVariant to accept this param also, and an empty CustomFieldsCSV which will be equal to SimpleFields as already occurs in other methods.
Without this, allways return null if cache is enabled.

See:

function TSQLRest.RetrieveDocVariant(Table: TSQLRecordClass;
  const FormatSQLWhere: RawUTF8; const BoundsSQLWhere: array of const;
  const CustomFieldsCSV: RawUTF8): variant;
var T: TSQLTable;
    bits: TSQLFieldBits;
    Rec: TSQLRecord;
    ID: TID;
begin
  SetVariantNull(result);
  if (self<>nil) and (Table<>nil) then begin
    with Table.RecordProps do // optimized primary key direct access
    if Cache.IsCached(Table) and (length(BoundsSQLWhere)=1) and
       VarRecToInt64(BoundsSQLWhere[0],Int64(ID)) and
       FieldBitsFromCSV(CustomFieldsCSV,bits) then


      //*** this code is reached if cache enabled and CustomFieldsCSV = '' and return null
      if IsZero(bits) then  
        exit else
      if bits-SimpleFieldsBits[soSelect]=[] then
        if IdemPropNameU('RowID=?',FormatSQLWhere) or
           IdemPropNameU('ID=?',FormatSQLWhere) then begin
          Rec := Table.Create(self,ID);
          try


            //*** if this code is reached uses GetAsDocVariant that return RowID, instead of ID
            Rec.GetAsDocVariant(True,bits,result);  
          finally
            Rec.Free;
          end;
          exit;
        end;
    T := MultiFieldValues(Table,CustomFieldsCSV,FormatSQLWhere,BoundsSQLWhere);
    if T<>nil then
    try


      //*** if this code is reached return ID
      T.ToDocVariant(1,result)  
    finally
      T.Free;
    end;
  end;
end;

Last edited by macfly (2019-05-16 14:45:54)

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: [Solved] Inconsistency in returned json and cache problems

I had this problem ages ago with a JavaScript client and just looked for both (ID || RowID). But that doesn’t probably help here.

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: [Solved] Inconsistency in returned json and cache problems

When it's you who consume the service, okay.
But this ambiguity breaks the use of the framework to publish an API.

The biggest problem in my view is the same method, with the same parameters returning different data, depending on the cache.

Server.RetrieveDocVariant(TSQLUser,'ID=?',[ID],'');
{ "Result": {"ID": 1, "Name": "Xyz"}}
....
Serve.Cache.SetCache(TSQLUser);
Server.RetrieveDocVariant(TSQLUser,'ID=?',[ID],'');
{ "Result": null}

Even though I always pass the fields names to avoid null, then the inconsistency occurs with the RowID / ID

Server.RetrieveDocVariant(TSQLUser,'ID=?',[ID],'Name');
{ "Result": {"ID": 1, "Name": "Xyz"}}
....
Serve.Cache.SetCache(TSQLUser);
Server.RetrieveDocVariant(TSQLUser,'ID=?',[ID],'Name');
{ "Result": {"RowID": 1, "Name": "Xyz"}}

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: [Solved] Inconsistency in returned json and cache problems

I have created a pull request to add ForceNoRowID param in GetAsDocVariant.

This allows to choose whether ID must be returned or not;

TSQLRecord.GetAsDocVariant(withID: boolean;  const withFields: TSQLFieldBits; var result: variant;
  options: PDocVariantOptions; ForceNoRowID : Boolean);

...
  if withID then
    if ForceNoRowID then
      doc.Values[doc.InternalAdd('ID')] := fID else
      doc.Values[doc.InternalAdd('RowID')] := fID;
...

And a small change in RetrieveDocVariant to use this parameter, according to the presence or not of ID in FormatSQLWhere.
If I am explaining "ID=?" so I want to return as ID, right?

This way it is not necessary to change the parameters of this method

from

Rec.GetAsDocVariant(True,bits,result,nil);

to

Rec.GetAsDocVariant(True,bits,result,nil,IdemPropNameU('ID=?',FormatSQLWhere));

There is still the issue of returning null with cache enabled and CustomFieldsCSV = ''

Maybe make FieldBitsFromCSV return all simple fields to '' and all fields to '*' as is standard in other methods.
But I think this would break the code or implementations

Last edited by macfly (2019-05-15 21:18:26)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,220

Website

Re: [Solved] Inconsistency in returned json and cache problems

The consistency bug when cache was enable should be fixed by https://synopse.info/fossil/info/404ac13d8b

About returning "ID" instead of "RowID", it may break existing code, and we will face the same problem when cache is enabled... I will investigate further...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,220

Website

Re: [Solved] Inconsistency in returned json and cache problems

In fact, TSQLTable.InitFieldNames normalizes RowID field name to ID,  so TSQLTable.ToDocVariant returns "ID".

So I guess we could return "ID" in all cases.
See https://synopse.info/fossil/info/d16b8b1412

macfly

Member

From: Brasil

Registered: 2016-08-20

Posts: 374

Re: [Solved] Inconsistency in returned json and cache problems

Thanks ab!

For the attention and patience of always.

All solved!