Custom JSON output example 04

warleyalex · 2013-02-04 20:55:37

Hi,
I'd like to modify example "04 - HTTP Client-Server" to JSON output become like this:

{"total":25,"results":[{"ID":1,"Time":135092568090,"Name":"Warleyalex","Question":"Brasil"},

{"ID":2,"Time":135092568096,"Name":"Warleyalex","Question":"SeteLagoas"},

{"ID":3,"Time":135092568102,"Name":"Warleyalex","Question":"MinasGerais"},

{"ID":4,"Time":135092568108,"Name":"Roberto","Question":"Atletico"},

{"ID":5,"Time":135092573028,"Name":"Antonio","Question":"123"},

{"ID":6,"Time":135092573033,"Name":"AB","Question":"456789"}]}

I need to add total data to json output. How to use the method "TSQLRest.TableRowCount(Table: TSQLRecordClass): integer;"
into project 04?

Gracias.

ab · 2013-02-04 21:49:00

It is not possible directly, since the db layer creates the content without knowing about the final rows count.

Why do you need it?
Any Ajax app will be able to retrieve it.

In non expanded json format, the number is added. But not in expanded/ajax format.

You can create such a result by defining a custom service.

warleyalex · 2013-02-05 00:26:03

I would like to develop some rich interfaces with mORMot, specially with example 04. It's a datagrid with pagination (mORMot sends out data in pages). Let's suppose, I have 100 records and I want to show 20 records per page.

To perform pagination, one additional information is expected from server (mORMot) -the "totalproperty".

totalProperty = name of the property from which to retrieve the total number of records in the dataset. This is only needed if the whole dataset is not passed in one go, but is being paged from the remote server.

ExtJS grid panel paging toolbar works only with the server side data and uses the following properties to fetch the data from the server and calculate the pages:
* start
* limit
* totalProperty
* pageSize

The server must handle the start and limit and the pageSize to return the requested data and set the total number of records to the a property, which is set to totalProperty (say, 'totalRecords').

ab · 2013-02-05 05:10:14

Retrieving a "total property" will lead to a diverse SQL statement, and will be much slower, unless the whole content is retrieved and stored on the server side.
There is already an"rowCount": element for all requests which knows the rowcount, i.e. TSQLRestServerStaticInMemory.GetJSONValues, TSQLTable.GetJSONValues and in TSQLTableJSON.ParseAndConvert for about 5% faster process of huge content (mpv proposal). See http://synopse.info/forum/viewtopic.php?pid=4729#p4729

Extending it to all requests (e.g. for SQLite3 or FireBird) would be resource consuming, and a whole rewrite.

I suggest you use a dedicated method server, or interface-based server, and a TSQLTableJSON result in memory cache - returning the data using TSQLTable.GetJSONValues.

warleyalex · 2013-02-05 11:29:23

I thank you for taking time out to answer my question.

I agree with MPV member, with such format become pretty easy to use Jquery or other JS framework, most of JS frameworks expects to receive this JSON structure. If we deal with JavaScript client it's good to start property name in JSON with a small letter.

  
  {
    "total": "xxx",
    "page": "yyy",
    "records": "zzz",
    "rows" : [
      {"id" :"1", "cell" :["cell11", "cell12", "cell13"]},
      {"id" :"2", "cell":["cell21", "cell22", "cell23"]},
    ]
  }

In "Delphi Relax: http://code.marcocantu.com/p/delphirelax" there's an class interfacing the database via RlxTableModule and returning the proper JSON structure.

I would like to see an example with mORMot one day with this JSON structure.
Thank you.

warleyalex · 2013-03-18 19:31:49

I made some changes in (SynCommons.pas), procedure TJSONWriter.AddColumns, to returns this custom JSON:

{"totalCount":10,"values":[
{"ID":1,"Time":135100147338,"Name":"Name1","Question":"Message to Name1"},
{"ID":2,"Time":135100147338,"Name":"Name2","Question":"Message to Name2"},
{"ID":3,"Time":135100147338,"Name":"Name3","Question":"Message to Name3"},
{"ID":4,"Time":135100147338,"Name":"Name4","Question":"Message to Name4"},
{"ID":5,"Time":135100147338,"Name":"Name5","Question":"Message to Name5"},
{"ID":6,"Time":135100147338,"Name":"Name6","Question":"Message to Name6"},
{"ID":7,"Time":135100147338,"Name":"Name7","Question":"Message to Name7"},
{"ID":8,"Time":135100147338,"Name":"Name8","Question":"Message to Name8"},
{"ID":9,"Time":135100147338,"Name":"Name9","Question":"Message to Name9"},
{"ID":10,"Time":135100147338,"Name":"Name10","Question":"Message to Name10"}]}

totalCount = should be 72 ("SELECT COUNT(*) FROM SampleRecord")

How can I implement (get "total number of records" of a dataset and set to FNumRecords)?
----
procedure SetNumRecords(value: integer);
begin
FNumRecords := value;
end;

function GetNumRecords: integer;
begin
Result := FNumRecords;
end;

In (SynCommons.pas), procedure TJSONWriter.AddColumns I would like:
AddShort('{"totalCount":');
Add(GetNumRecords);
----

ab · 2013-03-18 21:22:47

It is imposible or very slow to retrieve the row count before trigerring all the data.

What is possible is to store it after the data....

From the Json point of view, this is the same. :-)

I can add it very easily, and it may also benefit to Delphi clients also.

warleyalex · 2013-03-18 22:59:54

ab wrote:

What is possible is to store it after the data....
From the Json point of view, this is the same. :-)
I can add it very easily, and it may also benefit to Delphi clients also.

Hi, thanks for the reply, I have searched through the docs for days and I'm not getting anywhere, anyone have any ideas?

IMHO, this feature "totalProperty" is very important to mORMot work properly with ExtJS and others JS frameworks.

I'm using mORMot with ExtJS for making some rich interfaces, the problem is that I have a grid in ExtJS where I need paging. Paging Toolbar supports remote paging. My backend mORMot should send totalProperty = "totalCount":72, into a json result object. If I have a pageSize set to 10, pagination it should looks like:
Page [1] of [8]
----------------
ShowMessage(IntToStr(DB.TableRowCount(TSQLSampleRecord))); --> "SELECT COUNT(*) FROM SampleRecord" ==> 72

Thank you.

ab · 2013-03-19 06:47:32

What is your concern about adding a totalCount property on the client side, in JavaScript, once all data has been retrieved from server?

Sounds not difficult to do, since you got the data.

Using TableRowCount or SELECT COUNT(*) is just IMHO too slow on the server side.

ab · 2013-03-19 10:34:32

I've added a new optional "rowCount":... field value in TSQLRestServerStaticInMemory.GetJSONValues, TSQLTable.GetJSONValues, TSQLTableJSON.ParseAndConvert, TSQLRequest.Execute and TSQLDBStatement.FetchAllToJSON methods at the end of the non expanded JSON content.

It will improve client JSON parsing performance, especially in case of huge content.

See http://synopse.info/fossil/info/760c6e358d

BUT it is working only with non expanded format, by now.
For expanded format (i.e. an array of true JSON objects), since you have the whole array at hand on client side, using JavaScript length method is enough to get the total row count.

For your specific JSON format expectation, do not change the common code, but create your own method-based service, returning the content as expected.
I can post some sample code here, if you think you are a bit lost with it.
But implementing the whole ExtJS proxy feature could be a good idea, even if it is not a trivial task.
Perhaps mpv may help here.

warleyalex · 2013-03-19 19:24:56

ab wrote:

I've added a new optional "rowCount":...

Hi ab,

indeed, to create rich interfaces with Extjs, using data paginating - mORMot should support two things:
a) the property/feature "totalProperty" = Select count(*) from resultset;
b) mORMot should read parameters passed in the HTTP requests.

Since REST supports parameters for filtering (that's not the problem here!), I have discovered that before any request, mORMot should read parameters passed in the HTTP request.

IMHO, the property rowCount is useless here using mORMot with ExtJS. For example, If I add/delete a record in a grid, this should affect "totalProperty" value to paginate data properly. Before HTTP request, mORMot should calc "totalProperty" on-the-fly.

Any idea?

Regards,

Last edited by warleyalex (2013-03-26 22:22:14)

ab · 2013-03-19 20:43:03

This will be slow, to handle the pagination as such.

Or maybe retrieve all IDs, then store it in cache before pagination.

Perhaps not worth it.

My proposal:
1. Current parameters names should be customizable.
2. An optional totalCount field shall be available.

But I think using extjs proxy to handle the paging on client side is a better solution.

ab · 2013-03-20 10:59:51

I've added TSQLRestServer.URIPagingParameters property, to support alternate URI parameters sets for request paging (in addition to YUI syntax).
See

What are the parameters expected by the ExtJS grid?
http://synopse.info/fossil/info/466ecb966d

Still need to implement the optional totalCount field.

ab · 2013-03-21 16:45:09

The new "totalCount" feature is now available.

TSQLRestServer.URIPagingParameters property now supports an optional "total":... field format to be added within the JSON result.

Please note that it will call "SELECT count()" on the un-paged request, so it may be slow, especially on external databases (where OFFSET is not handled yet, so it will rely on virtual table mechanism which may be slow).
The WHERE clause will be processed two times: one for the "totalCount" value, then another time with OFFSET/LIMIT feature.

BTW, internal TSQLRestServerStaticInMemory fast in-memory engine has been optimized to support such paging - if needed.

See http://synopse.info/fossil/info/25426a6933

warleyalex · 2013-03-22 11:51:22

*** Before changes at procedure mormot.TSQLRestServer.URI(var Call: TSQLRestServerURIParams)
----------
When I send a simple HTTP REQUEST to Server like:
http://localhost:8080/root/SampleRecord/?SELECT=id&START=10&ROWS=10
===> JSON object: {"fieldCount":1,"values":["ID",11,12,13,14,15,16,17,18,19,20],"rowCount":10}

*** After changes at procedure mormot.TSQLRestServer.URI(var Call: TSQLRestServerURIParams);
-----------------
I add this in project
DB.URIPagingParameters.StartIndex := 'START=';
DB.URIPagingParameters.Results := 'ROWS=';

...but when I type
http://localhost:8080/root/SampleRecord/?SELECT=id&START=10&ROWS=10 it returns
===> {"ErrorCode":400,"ErrorText":"Bad Request"}

Project raised exception class ESQLite3Exception with message 'near "LIMIT": syntax error'
..............................................................................
Date: 2013-03-21
mORMot and Open Source friends-25426a69331b822c.zip

Do I have to add another URI.parameter?

ab · 2013-03-22 15:20:55

what are the executed SQL statements?

warleyalex · 2013-03-22 15:42:48

Before changes at "procedure mormot.TSQLRestServer.URI":

I've started the Project04Server.exe program (Table SampleRecords has: 72 records)
If I typed into the address bar:
http://localhost:8080/root/SampleRecord/?SELECT=id&STARTINDEX=10&RESULTS=10

--> the database reply to my request with this expect encoded JSON:
{"fieldCount":1,"values":["ID",11,12,13,14,15,16,17,18,19,20]}

Now, I see an error message: Server Error 400.

ab · 2013-03-22 15:59:40

Yes I understand it.

But what are the executed SQL statements?

warleyalex · 2013-03-22 20:32:33

AB, thank you for replying.

in mormot.pas, line 22878 - procedure TSQLRestServer.URI

SQL := Model.TableProps[URI.TableIndex].SQLFromSelectWhere(SQLSelect,SQLWhere);
I changed to
SQL := Model.TableProps[URI.TableIndex].SQLFromSelectWhere(SQLSelect,trim(SQLWhere));

"Just a word" and it solved the problem!

ab · 2013-03-23 09:03:43

I was not able to find the root cause by now (do not have time this morning).

But if http://synopse.info/fossil/info/3489a1a0c2 fixes it, it could be OK.

mORMot Open Source

#1 2013-02-04 20:55:37

Custom JSON output example 04

#2 2013-02-04 21:49:00

Re: Custom JSON output example 04

#3 2013-02-05 00:26:03

Re: Custom JSON output example 04

#4 2013-02-05 05:10:14

Re: Custom JSON output example 04

#5 2013-02-05 11:29:23

Re: Custom JSON output example 04

#6 2013-03-18 19:31:49

Re: Custom JSON output example 04

#7 2013-03-18 21:22:47

Re: Custom JSON output example 04

#8 2013-03-18 22:59:54

Re: Custom JSON output example 04

#9 2013-03-19 06:47:32

Re: Custom JSON output example 04

#10 2013-03-19 10:34:32

Re: Custom JSON output example 04

#11 2013-03-19 19:24:56

Re: Custom JSON output example 04

#12 2013-03-19 20:43:03

Re: Custom JSON output example 04

#13 2013-03-20 10:59:51

Re: Custom JSON output example 04

#14 2013-03-21 16:45:09

Re: Custom JSON output example 04

#15 2013-03-22 11:51:22

Re: Custom JSON output example 04

#16 2013-03-22 15:20:55

Re: Custom JSON output example 04

#17 2013-03-22 15:42:48

Re: Custom JSON output example 04

#18 2013-03-22 15:59:40

Re: Custom JSON output example 04

#19 2013-03-22 20:32:33

Re: Custom JSON output example 04

#20 2013-03-23 09:03:43

Re: Custom JSON output example 04

Board footer