mORMot my first project: mySQL + Zeos

mhmda · 2017-08-23 06:38:59

Hello mORMot,

It is really amazing framework, and I really appreciate your hard work, it's been a long that I looking for a really RESTfull web service, and had some options: tms, remobjects, datasnap but neither of them was I expected especially their price.
So I read about mORMot and I have started my first project I would like your help to review this code and tell me that this is the right way to do it so I can continue work with mORMot.

Request: web service for various clients (android app, iphone...) that return result as json (for starting no security will be needed).

Code (Server): this is working project

program mormot_1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  SysUtils,windows,messages,variants,classes,mormot,SynCommons,
  mormotsqlite3,mormotdb,syndb,syndbsqlite3,syndbdataset,syndbzeos,synsqlite3,
  synsqlite3static, dateutils, mORMotHttpServer;
const
  ROOT_NAME = 'service';
var
 Model:  TSQLModel;
 srHTTP: TSQLHttpServer;
 DB:     TSQLRestServerDB;
//------------------------------------------------------------------------------
 type
  TRecord = class(TSQLRecord)
    private
      fCname:  RawUTF8;
      fCemail: RawUTF8;
      fCcel:   RawUTF8;
      fCtel:   RawUTF8;
    published
      property Cname: RawUTF8
        read fCname write fCname;
      property Cemail: RawUTF8
        read fCemail write fCemail;
      property Ccel: RawUTF8
        read fCcel write fCcel;
      property Ctel: RawUTF8
        read fCtel write fCtel;
 end;
//------------------------------------------------------------------------------
procedure start_db(const aRawServerName, aDatabaseName, aUserID, aPassword: RawUTF8);
var
 propsClass:  TSQLDBConnectionPropertiesClass;
 props:       TSQLDBConnectionProperties;
 aServerName: RawUTF8;
 aTableName : RawUTF8;
 rec: TRecord;
 MainDBName: RawUTF8;
begin
  aTableName:='contacts';
  MainDBName:=  SQLITE_MEMORY_DATABASE_NAME;
  propsClass:=  TSQLDBZeosConnectionProperties;
  aServerName:= TSQLDBZeosConnectionProperties.URI(dMySQL,aRawServerName);
  Model:=       TSQLModel.Create([TRecord],ROOT_NAME);
  rec  :=       TRecord.Create;
  props:=propsClass.Create(aServerName,aDatabaseName,aUserID,aPassword);
  VirtualTableExternalRegister(Model,TRecord,props,'contacts');
  DB:=TSQLRestServerDB.Create(Model,':memory:',false);
  DB.CreateMissingTables;
end;
//------------------------------------------------------------------------------
procedure start_server;
begin
 try
  srHTTP := TSQLHttpServer.Create('8080', [DB], '+',HTTP_DEFAULT_MODE);
  srHTTP.AccessControlAllowOrigin := '*';
 finally
  writeln('OK.');
 end;
end;
//------------------------------------------------------------------------------
procedure stop_client;
begin
 srHTTP.Free;
 DB.Free;
 Model.Free;
end;
//------------------------------------------------------------------------------
  var s: string;
begin
  try
    //Test('localhost:3306','myphonebook','root','1234');
    writeln('starting db server...');
    start_db('localhost:3306','myphonebook','root','1234');
    writeln('starting http server...');
    start_server;
    Readln(s);
  except
    on E: Exception do
      Writeln(E.ClassName, ': ', E.Message);
  end;
end.

mhmda · 2017-08-24 06:32:15

Till now about ~50 views of this post is that mean that my code is OK.

Another question: I compare this - server response - with sample (18) after I put both project in remote server and here the result:

I test it using chrome like this: http://x.x.x.x:8080/ROOT/samplerecord/10

- sample (18): 90ms
- mySQL: 1.1sec

As you can see there is really difference between the response from server in both projects, can someone till me why? is it because I use mySQL and there is dll in middle? or there is something else with my code?

Thanks in advance

ab · 2017-08-24 07:30:52

Calling an external DB like MySQL makes it slower, because a connection is to be created for each thread.
But if you test your URI on the server more than 32 times (i.e. the thread count), the established connections will be re-used, and you should have better response time.

Anyway, here we use mostly SQLite3 as database engine (several dedicated SQLite3 instances in fact, with more than dozen GB of data), and MongoDB as remote shared centralized database (for audit/bigdata), gathered in the background. This gives awesome performance.

mhmda · 2017-08-24 07:50:00

Thank you for fast response, I understand.

mhmda · 2017-08-24 12:46:08

Another question as I continue with mORMot:

I want to retrieve the 'books' of a 'student' how I do it the ORM way.

The table 'books' has a field 'studentID' that identify the student (by id), and the student may have more than one book.

type
  TBooks = class(TSQLRecord)
    private
      fbookname:   RawUTF8;
      fbookyear:   TDate;
    published
      property bookname: RawUTF8     read fbookname write fbookname;
      property bookyear: TDate       read fbookyear write fbookyear;
 end;

And I modified the TRecord:

...
fBooks:  TBooks;
...
property Books: TBooks   read fBooks write fBooks;

How to tell mORMot to use the field 'studentID' to know the books of a student?

Thanx in advance

Last edited by mhmda (2017-08-24 12:49:28)

ab · 2017-08-24 13:58:04

Forget about relational databases: I guess you are still trying to mimics a SQL join.
Think in terms of aggregates, and objects.

For instance, you could store the book IDs as a dynamic array in the student record.
Since you won't delete a book entry when it is still owned by a student, no need to have the DB maintain the integrity between the two.
The logic will be in the objects, not the DB.

There is a lot in the documentation about this matter.
In a nutshell: the DB is there to persist values, and - sometimes - query for values in an optimized way (define proper indexes).
You should be able to switch from a SQL DB to a NoSQL DB, or even an in-memory list of objects, with no logic lost.

mhmda · 2017-08-24 14:19:41

My database is mySQL, I can learn easily from an example I'm trying to follow the third-party example of 'Megajek' but I can't understand how he did the connection between the tables, I also opened the .db3 using sqlite manager just to understand the structure of the tables and I see a table 'TTasks' that contains 'source' & 'dest' fields ? I don't know why? And even I dropped that table, I know that it will be recreated after I launch the example next time, I know the 'creatingmissingtables..' maybe doing that, but in real time app or at least the way I worked till now (sql) I just join two tables @ runtime I don't need third table to hold 'source' / 'dest'.

Please ab, be more patient with us I have read almost all the forum posts related to this issue, and I still fighting to get my project run as expected for a basic thing, please explain to use how to make the connection and how to tell mormot the index field? I also read the 'enterprise-delphi-databases-2nd' by Erick, and also his explanation is sometimes missing or cutting and jumping to another subject.

Last edited by mhmda (2017-08-24 14:20:56)

ab · 2017-08-24 15:13:48

Please check https://synopse.info/files/html/Synopse … l#TITLE_82

turrican · 2017-08-25 08:59:59

Hi mhmda,

This is my humilde aproach :

  TRecordReferenceArray = array of TID;

  TArrayHelper = class
  public
    class function IsMine(aReference: TID;
      var aReferenceArray: TRecordReferenceArray): Boolean;
    class procedure DeleteRecord(aReference: TID;
      var aReferenceArray: TRecordReferenceArray);
    class procedure AddRecord(aReference: TID;
      var aReferenceArray: TRecordReferenceArray);
  end;

  TBook = class(TSQLRecord)
  end;

  TStudent = class(TSQLRecord)
    private
      fbooks : TRecordReferenceArray;
    public
      function GetBooks(RestClient : TSQLRest) : TObjectList<TBook>;
      procedure AddBook(ID : TID);
    published
      property Books : TRecordReferenceArray read FBooks;
  end;

class procedure TArrayHelper.AddRecord(aReference: TID;
  var aReferenceArray: TRecordReferenceArray);
var
  dynarray: TDynArray;
begin
  dynarray.Init(TypeInfo(TRecordReferenceArray), aReferenceArray);
  dynarray.FindAndAddIfNotExisting(aReference);
end;

class procedure TArrayHelper.DeleteRecord(aReference: TID;
  var aReferenceArray: TRecordReferenceArray);
var
  dynarray: TDynArray;
begin
  dynarray.Init(TypeInfo(TRecordReferenceArray), aReferenceArray);
  dynarray.Delete(dynarray.IndexOf(aReference));
end;

class function TArrayHelper.IsMine(aReference: TID;
  var aReferenceArray: TRecordReferenceArray): Boolean;
var
  dynarray: TDynArray;
begin
  dynarray.Init(TypeInfo(TRecordReferenceArray), aReferenceArray);
  Result := dynarray.IndexOf(aReference) <> -1;
end;

procedure TStudent.AddBook(ID: TID);
begin
  TArrayHelper.AddRecord(ID, fbooks);
end;

function TStudent.GetBooks(RestClient: TSQLRest): TObjectList<TBook>;
begin
  Result := (RestClient.RetrieveList<TBook>('ID in (?)', [Self.fbooks]));
end;

As Arnaud said, it's better to store the relationship ID's in a dynamic array. It's very easy to implement and respects the principles of CRUD.

Last edited by turrican (2017-08-25 09:02:48)

mhmda · 2017-08-25 11:42:02

Thanks for your help,

But:
- when the 'GetBooks' fired?
- What the purpose of the 'TArrayHelper'?

I still didn't manage to get all books for specific student, for example: http://localhost:8080/service/students/10/books

Last edited by mhmda (2017-08-25 14:12:31)

mhmda · 2017-08-25 15:04:59

I will follow all possible posts in this forum that could give me light for solving my issue.

Could any one give a straight forward example how to work with sharding/DDD.

We need a full sample app that uses 'sharding/DDD' and this is will be most valuable reference to all of us.

Last edited by mhmda (2017-08-26 09:10:23)

ab · 2017-08-26 08:49:48

If you shard data you don't join tables.
Did you get this?
Please take some time to read again my above link: most of your questions are answered here.
If you can't get one paragraph, ask here something specific to enhance the documentation.

I will write a bigger sample in the upcoming weeks, eventually.
You ask me to be patient, but i am as patient as i can be.
Then you write some hard and imho unfair words.
My guess that the patience is more needed on your side.

mhmda · 2017-08-26 09:09:32

ab, I will review the link again.

I edited my last post.

Thank you again for a great framework .

Last edited by mhmda (2017-08-26 09:11:26)

turrican · 2017-08-28 09:25:25

mhmda wrote:

Thanks for your help,
But:
- when the 'GetBooks' fired?
- What the purpose of the 'TArrayHelper'?
I still didn't manage to get all books for specific student, for example: http://localhost:8080/service/students/10/books

GetBooks is fired when you need to get the books of the student at runtime. ( StudentInstance.GetBooks ).

The purpose of TArrayHelper is a simple helper of arrays using Synopse awesome dynamic array wrapper.

mORMot Open Source

#1 2017-08-23 06:38:59

mORMot my first project: mySQL + Zeos

#2 2017-08-24 06:32:15

Re: mORMot my first project: mySQL + Zeos

#3 2017-08-24 07:30:52

Re: mORMot my first project: mySQL + Zeos

#4 2017-08-24 07:50:00

Re: mORMot my first project: mySQL + Zeos

#5 2017-08-24 12:46:08

Re: mORMot my first project: mySQL + Zeos

#6 2017-08-24 13:58:04

Re: mORMot my first project: mySQL + Zeos

#7 2017-08-24 14:19:41

Re: mORMot my first project: mySQL + Zeos

#8 2017-08-24 15:13:48

Re: mORMot my first project: mySQL + Zeos

#9 2017-08-25 08:59:59

Re: mORMot my first project: mySQL + Zeos

#10 2017-08-25 11:42:02

Re: mORMot my first project: mySQL + Zeos

#11 2017-08-25 15:04:59

Re: mORMot my first project: mySQL + Zeos

#12 2017-08-26 08:49:48

Re: mORMot my first project: mySQL + Zeos

#13 2017-08-26 09:09:32

Re: mORMot my first project: mySQL + Zeos

#14 2017-08-28 09:25:25

Re: mORMot my first project: mySQL + Zeos

Board footer