PostgreSQL + SQLite3 + FirebirdSQL + mORMot Server/Client

ormmodels.pas

unit OrmModels;

{$I mormot.defines.inc}

interface

uses
  {$I mormot.uses.inc}
  Classes, SysUtils,
  mormot.core.os,
  mormot.core.perf,
  mormot.core.base,
  mormot.orm.core,
  mormot.orm.sql,
  mormot.orm.sqlite3,
  mormot.rest.core,
  mormot.rest.http.server,
  mormot.rest.http.client,
  mormot.rest.sqlite3,
  mormot.db.sql,
  mormot.db.sql.postgres,
  mormot.db.raw.postgres,
  mormot.db.raw.sqlite3,
  mormot.db.raw.sqlite3.static;

type
  TOrmOITbl = class(TOrm)
  private
    FValue: RawUtf8;
  published
    // I change Value to JValue for FirebirdSQL text, "Value" is reserved word in FB
    // Also add index 80 to create varchar(80) field in FB
    property JValue: RawUtf8 index 80 read FValue write FValue;
  end;

implementation

end.

PostgreSQLHttpRest.lpr

program PostgreSQLHttpRest;

{$define BatchAdd}

{$I mormot.defines.inc}

uses
 {$I mormot.uses.inc}
  sysutils,
  classes,
  MTProcs,
  mormot.core.os,
  mormot.core.perf,
  mormot.core.base,
  mormot.orm.core,
  mormot.orm.sql,
  mormot.orm.sqlite3,
  mormot.rest.core,
  mormot.rest.http.server,
  mormot.rest.http.client,
  mormot.rest.sqlite3,
  mormot.db.sql,
  mormot.db.sql.postgres,
  mormot.db.raw.postgres,
  mormot.db.raw.sqlite3,
  mormot.db.raw.sqlite3.static,
  OrmModels;

const

  InsertDirectCount = 10 * 1000;
  InsertOrmCount = InsertDirectCount;
  MultiConnection = 10;
  PerConnection = InsertDirectCount div MultiConnection;

var
  Texts: array of TOrmOITbl;

  procedure Client(Index: PtrInt; Data: Pointer; Item: TMultiThreadProcItem);
  var
    Model: TOrmModel;
    Http: TRestHttpClient;
    I: PtrInt;
    tm: TLocalPrecisionTimer;
    r: TIDDynArray;
  begin
    Model := TOrmModel.Create([TOrmOITbl]);
    Http := TRestHttpClient.Create('127.0.0.1', '9000', Model);
    writeln('Start Client: ', Index);
    tm := TLocalPrecisionTimer.CreateAndStart;
    {$ifdef BatchAdd}
    Http.BatchStart(TOrmOITbl);
    {$endif}
    for I := Index * PerConnection to (Index + 1) * PerConnection - 1 do
    begin
      {$ifdef BatchAdd}
      Http.BatchAdd(Texts[I], True);
      {$else}
      Http.Add(Texts[I], True);
      {$endif}
    end;
    {$ifdef BatchAdd}
    Http.BatchSend(r);
    writeln('End Client: ', Index , #9'Batch Records Per Second : ', tm.PerSec(PerConnection));
    {$else}
    writeln('End Client: ', Index , #9'Records Per Second : ', tm.PerSec(PerConnection));
    {$endif}
    tm.Free;
    Http.Free;
    Model.Free;
  end;

var
  I: Integer;
  timer: TLocalPrecisionTimer;
  ProcThreadPool1: TProcThreadPool;
begin
  SetLength(Texts, InsertOrmCount);
  for I := 1 to InsertOrmCount do
  begin
    Texts[I-1] := TOrmOITbl.Create();
    with Texts[I-1] do
    begin
      JValue := '{"X": ' + IntToStr(I) + '}';
    end;
  end;
  timer := TLocalPrecisionTimer.CreateAndStart;

  //  My CPU have 2 cores 4 HThreads and DoParallel start only 4 threads in a time
  //Start Client: 0
  //Start Client: 1
  //Start Client: 3
  //Start Client: 2
  //End Client: 1	Records Per Second : 906
  //Start Client: 4
  //End Client: 2	Records Per Second : 855
  //Start Client: 5
  //End Client: 3	Records Per Second : 841
  //Start Client: 6
  //End Client: 0	Records Per Second : 809
  //Start Client: 7
  //End Client: 4	Records Per Second : 881
  //Start Client: 8
  //End Client: 5	Records Per Second : 841
  //Start Client: 9
  //End Client: 7	Records Per Second : 835
  //End Client: 6	Records Per Second : 798
  //End Client: 8	Records Per Second : 1392
  //End Client: 9	Records Per Second : 1595

  ProcThreadPool1 := TProcThreadPool.Create;
  // With this setting DoParallel start all 10 threads in parallel
  ProcThreadPool1.MaxThreadCount:=MultiConnection;

  ProcThreadPool1.DoParallel(Client, 0, MultiConnection - 1, nil, MultiConnection);
  WriteLn('DB ORM :', #9'Records Per Second : ', timer.PerSec(InsertDirectCount));
  Writeln('---------------------------------------------------------');
  for I := 0 to High(Texts) do
    Texts[I].Free;
  timer.Free;
  ProcThreadPool1.Free;
end.

PostgreSQLHttpRestServer.lpr

program PostgreSQLHttpRestServer;

{.$define PQ}  // Enable PostgreSQL
{.$define FB}  // Enable FirebirdSQL Ibx
               // If not defined PQ or FB then SQLite3

{$I mormot.defines.inc}

uses
 {$I mormot.uses.inc}
  sysutils,
  classes,
  mormot.core.os,
  mormot.core.perf,
  mormot.core.base,
  mormot.orm.core,
  mormot.orm.sql,
  mormot.orm.sqlite3,
  mormot.rest.core,
  mormot.rest.http.server,
  mormot.rest.http.client,
  mormot.rest.sqlite3,
  mormot.db.sql,
  {$ifdef PQ}
  mormot.db.sql.postgres,
  mormot.db.raw.postgres,
  {$endif}
  mormot.db.raw.sqlite3,
  mormot.db.raw.sqlite3.static,
  {$ifdef FB}
  mormot.db.sql.ibx,
  {$endif}
  OrmModels;

  {$ifdef PQ}
const
  PQHost: PChar = '127.0.0.1';
  PQPort: PChar = '5432';
  PQDBName: PChar = 'mormot_postgres_test';
//  PQUserName: PChar = 'root';
//  PQPassword: PChar = '1';
  PQUserName: PChar = 'postgres';
  PQPassword: PChar = 'ttomas';
  {$endif}
  {$ifdef FB}
const
  cFirebirdServerIbx = '127.0.0.1';  // 'localhost/3033'
  cFirebirdDBFileIbx = '/tmp/test.fdb';
  cUserName = 'SYSDBA';
  cPassword = 'masterkey';
  {$endif}
var
  Props: TSqlDBConnectionPropertiesThreadSafe;
  Model: TOrmModel;
  Server: TRestServerDB;
  Http: TRestHttpServer;
begin
  {$ifdef PQ}
  Props := TSqlDBPostgresConnectionProperties.Create(PQHost, PQDBName, PQUserName, PQPassword);
  Props.ThreadingMode := tmThreadPool;
  {$endif}
  {$ifdef FB}
  Props := TSqlDBIbxConnectionProperties.Create(cFirebirdServerIbx,
    cFirebirdDBFileIbx, cUserName, cPassword);
  TSqlDBIbxConnectionProperties(Props).CreateDescendingOnlyPK := True;
  Props.ThreadingMode := tmThreadPool;
  {$endif}
  Model := TOrmModel.Create([TOrmOITbl]);
  {$if defined(PQ) or defined(FB)}
  VirtualTableExternalRegisterAll(Model, Props);
  {$ifend}
  Server := TRestServerDB.Create(Model, ':memory:');
  Http := TRestHttpServer.Create('9000', [Server], '+', useHttpAsync);
  Server.DB.Synchronous := smOff;
  Server.DB.LockingMode := lmExclusive;
  Server.CreateMissingTables();
  Http.AccessControlAllowOrigin := '*';
  ReadLn;
  Http.Free;
  Server.Free;
  Model.Free;
  Props.Free;
end.

BatchAdd PostgeSQL Output
Start Client: 3
Start Client: 1
Start Client: 7
Start Client: 6
Start Client: 9
Start Client: 2
Start Client: 4
Start Client: 0
Start Client: 8
Start Client: 5
End Client: 5 Batch Records Per Second : 66809
End Client: 1 Batch Records Per Second : 45244
End Client: 4 Batch Records Per Second : 35751
End Client: 0 Batch Records Per Second : 29143
End Client: 3 Batch Records Per Second : 24199
End Client: 2 Batch Records Per Second : 21187
End Client: 9 Batch Records Per Second : 18551
End Client: 6 Batch Records Per Second : 16409
End Client: 8 Batch Records Per Second : 14941
End Client: 7 Batch Records Per Second : 13455
DB ORM : Records Per Second : 108610

Add PostgreSQL Output
Start Client: 3
Start Client: 1
Start Client: 2
Start Client: 4
Start Client: 7
Start Client: 5
Start Client: 6
Start Client: 0
Start Client: 8
Start Client: 9
End Client: 5 Records Per Second : 150
End Client: 3 Records Per Second : 147
End Client: 4 Records Per Second : 135
End Client: 0 Records Per Second : 132
End Client: 1 Records Per Second : 131
End Client: 6 Records Per Second : 131
End Client: 8 Records Per Second : 131
End Client: 2 Records Per Second : 131
End Client: 9 Records Per Second : 131
End Client: 7 Records Per Second : 129
DB ORM : Records Per Second : 1295