mORMot Open Source

milesyou

Member

Registered: 2015-03-04

Posts: 21

Do you Add Pagination?

no  limit in ms SQL

Last edited by milesyou (2015-03-04 04:11:55)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Do you Add Pagination?

What do you mean?

Pagination is handled by the URI method.
See http://synopse.info/forum/viewtopic.php?pid=105#p105

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

As I need the row count for the UI I'll go with a separate SQL query.
I just learn the framework, do not know how to achieve

with the URL parameters:
- select=someselect
- where=wherestatement
- sort=sortstatement
- startindex=startindexstament
- results=resultstatement
- dir=DESC will add the DESC statement

This seems to Web UI

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

Do you add funtion?

The following is paging query SQL,I want to Exe SQL by ExecuteList,Can Do?

function GetPageSQL(Tablename,KeyField:string;PageSize,PageIndex:Integer;GetFields:string='*';WhereStr:string='';OrderType:Integer=0):string;

function GetPageSQL(Tablename, KeyField:string;PageSize,PageIndex:Integer;
GetFields: string;WhereStr:string; OrderType: Integer): string;
var
OrderStr,str:string;
begin
Result:='';
if OrderType=0 then
begin
str:='>(select max';
OrderStr:=' order by '+KeyField;
end
else
begin
OrderStr:=' order by '+KeyField+' DESC';
str:=' <(select min';

end;
if PageIndex=1 then
Result:='SELECT TOP '+IntToStr(PageSize)+' '+GetFields+' FROM '+TableName+' '+WhereStr+' '+OrderStr
else
begin
Result:='SELECT TOP '+IntTostr(PageSize)+' '+GetFields+' FROM '+TableName;
if WhereStr<>'' then Result:=Result+' '+WhereStr+' and '
else Result:=Result+' where ';
Result:=Result+KeyField+' '+str+'('+KeyField+') FROM (SELECT TOP '+IntTostr((PageIndex-1)*PageSize)+' '
+KeyField+' FROM '+tableName;
if WhereStr<>'' then Result:=Result+' '+WhereStr+' '+OrderStr+') as tblTmp)'+OrderStr
else Result:=Result+' '+OrderStr+') as tblTmp)'+OrderStr;
end;
end;

Last edited by milesyou (2015-03-05 03:41:46)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Do you Add Pagination?

I see some caveats:
- This would work with MS SQL only.
- This algorithm is pretty inefficient, since nested select queries to find max/min may be pretty slow: a better implementation would use a simple comparison with an indexed field (the ID, a timestamp, or a search scoring number) corresponding to the latest page returned.
- BTW nested selects are not compatible with the "SQL adaptation" available in the framework.

You would benefit to use the SQLite3 syntax, then let our "SQL adaptation" feature perform its magic.
It would work even for MongoDB!

A working, efficient and simple pagination using ORM where clauses (and our "SQL adaptation") could be seen in our MVC sample.
See http://synopse.info/files/html/Synopse% … l#TITL_108 for a high level presentation of MVC (so that you understand how the sample is designed).

The DB requests, including pagination, is defined in https://github.com/synopse/mORMot/blob/ … wModel.pas
You could see at work, in your IDE debugger, to find out how it works.
See TBlogApplication.Default() method for pagination using ID/LastID as reference.
You have even complex and optimized pagination using FTS3/FTS4 full text search with a "MATCH" scoring function for the answers (like in Google).

This MVC sample work with SQLite3, external SQL DB (PostgreSQL or other), and... external NoSQL MongoDB.
We have tested this pagination with 100,000 blob entries, and pagination is instant, even with SQLite3 Full Text Search.
The best performance comes from the internal SQLite3 and external MongoDB, since complex queries on external SQL DB are sometimes slow down by the fact that the "SQL adaptation" is not complete, and fallback to SQLite3 virtual tables. Honnestly, I do not see any benefit of using an external SQL DB, unless you need to consume legacy data.
Of course, this sample work on Linux, with FPC or CrossKylix...

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

thanks you

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

First of all, thank the authors, I learn in your program, I would like to ask a question
Does TSQLRestServerURIPagingParameters support across the table? Like CreateAndFillPrepareJoined ?

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

I'm confused, multi table Association, I would like to have these table correlation data statistics, found a lot of trouble, I do not know the author have what good way, can fill them
For example：

TSQLRes_Users = class;

TSQLRecordTimeStamped = class(TSQLRecord)
  private
    fCreate_Date: TCreateTime;
    fWrite_Date: TModTime;
    fWrite_uID: TSQLRes_Users;
    fCreate_uID: TSQLRes_Users;
  published
    property Create_Date: TCreateTime read fCreate_Date write fCreate_Date;
    property Write_Date: TModTime read fWrite_Date write fWrite_Date;
    property Write_uID: TSQLRes_Users read fWrite_uID write fWrite_uID;
    property Create_uID: TSQLRes_Users read fCreate_uID write fCreate_uID;
  end;

 TSQLRes_Company = class(TSQLRecordTimeStamped)
  private
    fName: RawUTF8;
     fFax: RawUTF8;
    fZip: RawUTF8;
    fStreet2: RawUTF8;
    fbic: RawUTF8;
    fPhone: RawUTF8;
    fState: RawUTF8;
    fStreet: RawUTF8;
    fActive: boolean;
    fEmail: RawUTF8;
  published
    property Name: RawUTF8 read fName write fName;
    property Fax: RawUTF8 read fFax write fFax;
    property Zip: RawUTF8 read fZip write fZip;
    property Street2: RawUTF8 read fStreet2 write fStreet2;
    property bic: RawUTF8 read fbic write fbic;
    property Phone: RawUTF8 read fPhone write fPhone;
    property State: RawUTF8 read fState write fState;
    property Street: RawUTF8 read fStreet write fStreet;
    property Active: boolean read fActive write fActive;
    property Email: RawUTF8 read fEmail write fEmail;
   end;

 TSQLRes_Users = class(TSQLRecordTimeStamped)
  private
    fActive: boolean;
    fLogin: RawUTF8;
    fPassword: RawUTF8;
    fCompany_ID: TSQLRes_Company;
    fLogin_Date: TDateTime;
    fSignature: RawUTF8;
    fPassword_Crypt: RawUTF8;
    fShare: boolean;
    fEan13: RawUTF8;

 public
    procedure SetPlainPassword(const PlainPassword: RawUTF8);
    function CheckPlainPassword(const PlainPassword: RawUTF8): boolean;

  published
    property Active: boolean read fActive write fActive;
    property Login: RawUTF8 index 32 read fLogin write fLogin;
    property Password: RawUTF8 index 64 read fPassword write fPassword;
    property Company_ID: TSQLRes_Company read fCompany_ID write fCompany_ID;
    property Login_Date: TDateTime read fLogin_Date write fLogin_Date;
    property Signature: RawUTF8 read fSignature write fSignature;
    property Password_Crypt: RawUTF8 index 32 read fPassword_Crypt
      write fPassword_Crypt;
    property Share: boolean read fShare write fShare;
    property Ean13: RawUTF8 read fEan13 write fEan13;
  end;

I Create
  ListOfMain: TObjectList<TSQLRes_Users>;

I want To Show  ListOfMain[].Company_ID.Create_uID.Login, 

What should I do?

Do you any solution to correct transaction handling with that much data?
thank

Last edited by milesyou (2015-03-06 06:14:52)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Do you Add Pagination?

You have to use TSQLRecord.CreateAndFillPrepareJoined() method and a regular loop.

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

TSQLRecord.CreateAndFillPrepareJoined() method
this olny get ListOfMain[].Company_ID.name but no get ListOfMain[].Company_ID.Create_uID.Login
Can you give Simaple? thank you

Last edited by milesyou (2015-03-06 08:33:46)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Do you Add Pagination?

It handle only a single level of JOIN.
The ORM does not support more than one JOIN level.

In fact, our ORM prefers denormalized data (i.e. data sharding), like with NoSQL databases... and real objects.
See http://synopse.info/files/html/Synopse% … ml#TITL_29
and http://en.wikipedia.org/wiki/Denormalization

The upcoming persistence feature of mORMotDDD.pas do allow such kind of denormalized data, for persistence of any level of nested objects. And in this case, plain TPersistent/TSynPersistent objects, not TSQLRecord.

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

thank your reply, Looking forward to the upcoming DDD!!!

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

Hi Arnaud,
   When do you expect to use DDD? I can't wait to use it  ^.^

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Do you Add Pagination?

You can already use it.
Persistence is working very well in mORMotDDD.pas.

Please read the comments at the beginning of the unit:

{ some mORMot conventions about DDD implementation:

* most services methods should return an enumerate:
  - Exceptions should be raised only in case of a real failure (e.g. unexpected
    execution context, service shutdown...) but most of the time an enumerate
    will be used to manage errors
  - no textual error message should be sent by the application layer: it is up
    to the end-user application to react to a given unexpected result
  - within the DDD services, CQRS methods will use a TCQRSResult enumerate
    and advanced error process, with an optional error text for debugging
  - first value should mean success, so that TInterfaceStub would let the
    test pass by returning the default 0 ordinal

* persistence ignorance is mostly implemented via CQRS repository services:
  - we implement the "Command Query Responsibility Segregation" pattern
    to potentially increase scaling ability of reading, and allow distributed
    transactions via a service-based two-phase commit, implementing the Unit Of
    Work pattern
  - no ID should be transmitted most of the time, but write commands have to
    follow a read query which would specify the corresponding item so that
    the ID would be stored during the process
  - I*Query interfaces should have some SelectBy*() methods to change the
    current selected aggregate, which could later on be retrieved by a
    Get(out aAggregate: T....) method
  - I*Command instances would be our way of implementing the Unit Of Work
  - I*Command interfaces should have standard Add/Update/Delete methods,
    expecting a previous SelectBy*() call for Update/Delete: those methods
    should prepare the corresponding data change
  - I*Command interfaces should have Commit to perform all the pending
    changes (which may be implemented by transactions or via TSQLRestBatch)
  - I*Command interfaces should abort the process if the instance is
    released without any prior call to Commit (e.g. rollback the transaction
    or free any pending TSQLRestBatch instance)

}

See for instance how dddInfraAuthRest.pas implements the CQRS persistence interface for storing the TAuthInfo aggregate, as defined in dddDomAuthInterfaces.pas.
Take a look at TDDDAuthenticationAbstract.RegressionTests() for some simple regression tests.

To generate the TSQLRecord used for actual storage of the aggregate, take a look at the TDDDRepositoryRestFactory.ComputeSQLRecord() method which is able to generate the TSQLRecord class type definition for you, directly from the aggregate type.
It will "flatten" the nested fields directly.

The function currently not fully tested, is the storage of a list of instances.
We would use T*ObjArray kind of storage, not a TList<>, in a first version.

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

thank your reply

milesyou

Member

Registered: 2015-03-04

Posts: 21

Re: Do you Add Pagination?

I Study DDD, I found that you can not use it, can show a simple CRUD of Code?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,662

Website

Re: Do you Add Pagination?

Did you take a look at TDDDAuthenticationAbstract.RegressionTests() for some simple sample code, as regression tests?