mORMot Open Source

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

SlickGrid javascript table

I've managed to link up slickgrid ( https://github.com/mleibman/SlickGrid ) using the YUI syntax for the url.

Slickgrid needs the total records returned to set the scrollbar range. To get this I appended the code below to TSQLRestServerDB.EngineList (SQLite3.pas line 947) but this seems a bit crude. Can you suggest a better way?

  i := PosEx(' FROM ', SQL)+6;
  tableName := copy(SQL, i, PosEx(' ',SQL,i)-i);
  s := 'SELECT COUNT(*) AS total FROM '+tableName+';';
  recCount := DB.LockJSON(s);
  if recCount = '' then try
    try
      Req := GetAndPrepareStatement(s);
      if Req<>nil then begin
        MS := TRawByteStringStream.Create;
        try
          try
            Req^.Execute(0,'',MS,ForceAJAX or (not NoAJAXJSON));
            recCount := MS.DataString;
          finally
            if Req=@fStaticStatement then
              Req^.Close;
          end;
        finally
          MS.Free;
        end;
      end;
    except
      on ESQLException do
        recCount := '';
    end;
  finally
    DB.UnLockJSON(recCount);
  end;
  result := copy(recCount,2,length(trim(recCount))-3)+',"'+tableName+'":'+trim(result)+'}';  

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SlickGrid javascript table

You can use the TSQLRest.TableRowCount(Table: TSQLRecordClass) method for this purpose.
Hint: try not to make your code SQLite3 dependent. There are quite always some generic methods at the higher level (like TableRowCount), which may already provide the needed information, in a database-neutral implementation.

But in order to avoid the call of a SQL statement, I've modified the framework code to return the row count during the creating of the JSON content. This is handled e.g. in both TSQLRestServerStaticInMemory() and for SQLite3 tables (see corresponding new parameter in TSQLDataBase.ExecuteJSON, LockJSON, UnLockJSON).
See http://synopse.info/fossil/info/9997c975d6

Of course, the TSQLRestServerDB.EngineList() method will use this newly available parameter.
Hope it helps.

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: SlickGrid javascript table

Thanks, using TableRowCount works fine but the new EngineList isn't quite what I was looking for.

I'm using Slickgrid in 'ajax' mode so the table data is loaded incrementally as the users scrolls, using STARTINDEX and RESULTS parameters in the url. EngineList only returns the row count of the chunk table data requested instead of the full table - could this be the full table row count instead?

It could also be useful to be able to pass a 'where' clause to TableRowCount to get a count of a query's result.

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,659

Website

Re: SlickGrid javascript table

I'm using Slickgrid in 'ajax' mode so the table data is loaded incrementally as the users scrolls, using STARTINDEX and RESULTS parameters in the url. EngineList only returns the row count of the chunk table data requested instead of the full table - could this be the full table row count instead?

I would have to implement paging at the server level.
That is, make the whole query in the Server, store the result in server cache, then publish only the given page to the client.

The current implementation is using a dedicated SQL to minimize process and memory.
But perhaps I'll have to modify this implementation in order to permit better access to native AJAX world.

OR

You may retrieve the whole table on the client (it is not so huge to download), then use the in-memory paging of the grid.
This also make sense to me.
And do not demand any framework modification, and is IMHO more compatible with a Statefull architecture.

It could also be useful to be able to pass a 'where' clause to TableRowCount to get a count of a query's result.

Good idea.

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: SlickGrid javascript table

I would have to implement paging at the server level.
That is, make the whole query in the Server, store the result in server cache, then publish only the given page to the client.

If a client makes a query that returns say 50,000 results but sorts them and is only interested the extreme results they may only need a few pages at each end.
In this case it would be fairly inefficient to run the whole query. Could the server cache act a bit like a sparse array, slowly increaseing in size as required?