mORMot Open Source

DigDiver

Member

Registered: 2013-04-29

Posts: 137

TCollection, JScript client and JSON

In the model I have PersonalList property as TCollection.

 Type
  TAttItem = class(TCollectionItem)
...
  published
   property FileName     : RawUtf8  read FFileName     write FFileName;
   property DisplayName  : RawUtf8  read FDisplayName  write FDisplayName;
  end;

 Type
   TAttItems = Class(TCollection)
   private
    function  GetItem(index: integer): TAttItem;
    procedure SetItem(index: integer; value: TAttItem);
   public
    constructor Create;
    function Add(_FFileName: RawUtf8; _FDisplayName: RawUtf8): TAttItem;

    property Items[index: integer]: TAttItem read GetItem write SetItem; default;
   end;

Type
 TMyRecord =  class(TSQLRecord)
...
published  property PersonalList : TAttItems read FPersonalList write FPersonalList ; 

In DB PersonalList stored as:

[{"FileName":"C:\\Users\\Dig\\Desktop\\alex.jpg","DisplayName":"foto.jpg"},{"FileName":"C:\\Users\\Dig\\Desktop\\Report.pdf","DisplayName":"%%First_Name%%_report.pdf"}]

ObjectToJson:

{
"AccountList": ["15"],
"GroupList": ["86"],
"Guid": "{10A06F2A-FA70-4068-8086-8EB13703F1BA}",
"PersonalList": [ { 
                        "FileName": "C:\\Users\\Dig\\Desktop\\alex.jpg",
                         "DisplayName": "foto.jpg"
                       },
                      {
                        "FileName": "C:\\Users\\Dig\\Desktop\\Report.pdf",
                        "DisplayName": "%%First_Name%%_report.pdf"
                      }
                   ]
}

GET ModelRoot/TableName/TableID: returns PersonalList as TEXT.

procedure TSQLRestServerURIContext.ExecuteORMGet;
...
// get JSON object '{...}'
              Call.OutBody := TableEngine.EngineRetrieve(TableIndex,TableID);

"AccountList": "[\"15\"]",
"GroupList": "[\"86\"]",
"Guid": "{10A06F2A-FA70-4068-8086-8EB13703F1BA}",
"PersonalList": "[{\"FileName\":\"C:\\\\Users\\\\Dig\\\\Desktop\\\\alex.jpg\",\"DisplayName\":\"foto.jpg\"},{\"FileName\":\"C:\\\\Users\\\\Dig\\\\Desktop\\\\Report.pdf\",\"DisplayName\":\"%%First_Name%%_report.pdf\"}]"

How to return TCollection as list via GET ModelRoot/TableName/TableID?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: TCollection, JScript client and JSON

As clearly stated by the documentation, the returned content is directly retrieved from the database as a JSON strings.
This is because the database directly return the JSON, without any ORM process in-between.

You have to parse the returned string on client side.
It is very easy with a JavaScript client - using eval() or similar - or with a Delphi client - using function GetJSONField() from SynCommons.pas.

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: TCollection, JScript client and JSON

I made for myself a little hack that allows you to give the contents of fields whose names end with "List" (AccountList, GroupList, AttachList) as they are in the database. Maybe someone will be useful.

unit SynSQLite3;
...
procedure TSQLRequest.FieldsToJSON(WR: TJSONWriter; DoNotFletchBlobs: boolean);
...
  SQLITE_TEXT: begin
       if pos('List":',WR.ColNames[i]) > 2  then  // if field name end with "List" do not transfer as TEXT value
        wr.AddString(sqlite3.column_text(Request,i))
       else
        begin
         WR.Add('"');
          WR.AddJSONEscape(sqlite3.column_text(Request,i),0);
          WR.Add('"');
        end;
       end;
...

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: TCollection, JScript client and JSON

Of course it will work...
But it will be the potential source of endless issues...

We may simply check for '{..}' or '[..]' patterns in the column text.
But in some occasions, it may be an error to do so...

A better way of doing it may be an option at TJSONWriter level, to specify which columns may not be escaped...

procedure TSQLRequest.FieldsToJSON(WR: TJSONWriter; DoNotFletchBlobs: boolean);
...
  SQLITE_TEXT: begin
       if i in WR.TextColumnShouldNotBeEscaped then  // if field is a JSON object or array, do not transfer as TEXT value
        wr.AddString(sqlite3.column_text(Request,i))
       else
        begin
         WR.Add('"');
          WR.AddJSONEscape(sqlite3.column_text(Request,i),0);
          WR.Add('"');
        end;
       end;
...

The TextColumnShouldNotBeEscaped  set may be set directly from TSQLRecordProperties, at ORM level.

But this won't work with manual SQL...
Worth a ticket, may be.

DigDiver

Member

Registered: 2013-04-29

Posts: 137

Re: TCollection, JScript client and JSON

I agree, the TextColumnShouldNotBeEscaped will be a best solution.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,661

Website

Re: TCollection, JScript client and JSON

I've created a corresponding feature request.

http://synopse.info/fossil/info/842b2d155c