mORMot Open Source

Yes for Fb4 Ibx is 20-30% faster, for Fb3 10-20% slower. Zeos have better optimized parameters setting. I try low level direct buffer setting for integer, currency and utf8 string and performance is same, but remove that code and keep fbintf param setting for safety and type conversion.

I create new pull request https://github.com/synopse/mORMot2/pull/75
With Firebird v4 batch Insert and Update use new IBatch interface. Using IBatch interface double insert performance.

Insertion speed (rows/second)
Firebird 4 using execute block for batch Insert
              Direct      Batch       Trans       Batch Trans
Ibx firebird  1745        22317       2946        22017

Firebird 4 using IBatch interface
              Direct      Batch       Trans       Batch Trans
Ibx firebird  1726        46499       6309        46763

Thanks @ab, this will solve the problem.
Can you please at line 1740 add

    if head<>'' then
      fOutput.Header := fOutput.Header + #13#10 + head;

In case when CustomOutHttpHeader := '' mvc application raise exception:
{ "errorCode":500, "errorText":"Unsafe HTTP header rejected [Content-Type: text/html; charset=UTF-8$0d$0a$0d$0aETag: \"57A6E6D8\"]" }
In same method depend of business logic I insert none, one or many response headers.

In my pull request https://github.com/synopse/mORMot2/pull/58
I convert sample 15 and results are:
Running tests using Synopse mORMot framework 2.0.1, compiled with Free Pascal 3.2 64 bit, against SQLite 3.36.0, on Manjaro Linux - Linux 5.4.148-1-MANJARO, at 2021-09-30 01:44:36.

Insertion speed (rows/second)
                              Direct      Batch       Trans       Batch Trans
Sqlite file full              304         15413       29067       82673
Sqlite file off               4027        69256       35187       90535
Sqlite file off exc           12478       82022       35143       90420
Sqlite file off exc aes       8005        73736       35028       88832
Sqlite in memory              28583       90438       35340       91195
In memory static              82611       183708      85679       183769
In memory virtual             80202       170404      84334       170010
External sqlite file full     305         7638        31593       99072
External sqlite file off      5352        67390       39135       106364
External sqlite file off exc  17633       92354       38797       105929
External sqlite in memory     32937       107940      38307       106935
Remote sqlite socket          5587        49019       5781        42242

Read speed (rows/second)
                              By one      All Virtual All Direct
Sqlite file full              24912       281563      275360
Sqlite file off               25301       289184      290528
Sqlite file off exc           42320       292620      293083
Sqlite file off exc aes       42133       296155      298169
Sqlite in memory              42353       289502      300300
In memory static              91441       551876      554692
In memory virtual             90920       211855      212539
External sqlite file full     50652       130589      286057
External sqlite file off      50972       131665      286368
External sqlite file off exc  51019       131585      286763
External sqlite in memory     49759       130938      286944
Remote sqlite socket          6518        117181      227489

I just pull request https://github.com/synopse/mORMot2/pull/57
Included conversion of sample 15 and this is results:
Running tests using Synopse mORMot framework 2.0.1, compiled with Free Pascal 3.2 64 bit, against SQLite 3.36.0, on Windows 10 64bit (6.2.9200), at 2021-09-26 21:29:48.

Insertion speed (rows/second)
                              Direct      Batch       Trans       Batch Trans
Ibx firebird                  3128        39378       22353       41027
Zeos firebird                 3035        39629       24809       38625

Read speed (rows/second)
                              By one      All Virtual All Direct
Ibx firebird                  12283       122989      179513
Zeos firebird                 30407       162522      209178

Expected result for Read speed-By one (this is read of 5000 records one by one with start/read/commit transaction in Ibx, in this test Zeos never Commit or CommitRetaining transaction)

@EgonHugeist
I test with Zeos 7.2-patches.
8.0-patches have a problem with Batch inserts. Data is corrupted in table after batch inserts, also a lot of records are missing (not 5000 records after Batch). I try with and without MORMOT2 define, same problem.
Tested with Firebird 3.0.7 in embedded mode
i7-4700MQCPU

Same test on i3-8145U
Running tests using Synopse mORMot framework 2.0.1, compiled with Free Pascal 3.2 64 bit, against SQLite 3.36.0, on Manjaro Linux - Linux 5.4.148-1-MANJARO, at 2021-09-25 19:18:03.

Insertion speed (rows/second)
               Direct      Batch       Trans       Batch Trans
Ibx firebird   1549        12895       3170        13066
Zeos firebird  1925        15533       2119        16242

Read speed (rows/second)
               By one      All Virtual All Direct
Ibx firebird   1663        42024       71331
Zeos firebird  1954        72547       96491

Inspired by transaction handling of ZeosLib and Firebird by this topic
  and goal to have zero started transaction on Firebird server when nobody is connected
  I create this mORMot2 database connection for FirebirdSQL.

  For low level connection I use MWA Software Firebird Pascal API package (fbintf),
  part of IBX for Lazarus.
  More details on https://www.mwasoftware.co.uk/fb-pascal-api

  - With explicit StartTransaction (Batch) all statements in connection
    is executed with this main transaction owned by connection.
  - If no explicit StartTransaction is called, all statements create internal transaction
    on prepare and COMMIT after execution or Eof or ReleseRows. This is sotware simulated auto commit.
    This internal transaction is owned by Statement.
  - TSqlDBIbxConnectionProperties.CreateDescendingPK if set to True (Default is False)
    will create only one descending PK index using statement
      PRIMARY KEY(ID) using desc index PK_TableName
    default dFirebird create two indexes on ID, one ascending, second descending
    nedded for select max(ID)
    see http://www.firebirdfaq.org/faq205

    @ab
    This feature need some change in mormot.orm.sql.pas at line 1883
    For testing in my code i now have this improvisation, I change fEngineName when set this property :-)
      dFirebird:
        // see http://www.firebirdfaq.org/faq205
        if fProperties.Engine='IBX1' then
        begin
          result := true;
          exit;
        end
        else
          Descending := true;

  - Batch implemented for insert,update,delete using execute block
  - ToDo Firebird4 API interface have new IBatch interface for insert/update
    also implemented in fbintf package.
  - You must patch FB30Statement.pas and FB25Statement.pas of fbintf, I'm waiting
    for response of MWA Software. fbintf raise exception if Execute is executed
    with different transaction then Prepare transaction.
    Just comment this lines in InternalExecute and InternalOpenCursor and rebuild package.
    more details on https://forum.lazarus.freepascal.org/in … 267.0.html

Source code mormot.db.sql.ibx.pas: https://gist.github.com/TTomas/2cb88c1c … a98f17a5b1

After boot of VM you need this commands:
sudo apt update
sudo apt upgrade
sudo apt install lxde
sudo apt install xrdp

sudo passwd ubuntu
# you will add pw for gui login to default ubuntu user

# Lazarus dependency
sudo apt install make binutils build-essential gdb subversion zip unzip libx11-dev libgtk2.0-dev libgdk-pixbuf2.0-dev libcairo2-dev libpango1.0-dev

wget fpcupdeluxe aarch64 and be happy

Yes rdp tcp port 3389 is not public I use ssh port forwarding to my PC
Linux: ssh -L 3389:localhost:3389 -i mykey ubuntu@myip
Windows: Configure Putty-Connection.SSH.Tunnels
You can publish any port in OCI config and Ubuntu FW, not recommended for rdp :-)

Better to report Issue in quickjs repo.
If you add setlocale(LC_ALL, 'C') this will impact every app using mORMot2 or can be set only for quickjs?

OK I see you edit post, SetLibcNumericLocale is nice workaround.

It's strange I know, 777 come from quickjs, after change locale test fails, but after linux restart test is OK. Try reboot.
I return locale for numbers to my country and test fail again after reboot. Look like quickjs use locale internally for some conversions!

@damiand, I have the same debugger error when try to debug MVC Blog demo with mORMot2.
In Lazarus menu Tools-Options in Debugger-General options try to change InternalStartBreak to gdsbMain.

@ab this simple test project work in mORMot1 but in mORMot2 raise exception

  aModel := CreateModel;
  try
    aServer := TRestServerDB.Create(aModel,Executable.ProgramFilePath+'MVCServer.db');
    try
      aServer.DB.Synchronous := smNormal;
      aServer.DB.LockingMode := lmExclusive;
      aServer.Server.CreateMissingTables;
      try
        Article := TOrmArticle.Create;
        try
          if aServer.Orm.Retrieve(666,Article) then begin
             writeln(article.GetJsonValues(true,true,ooSelect)); // This is OK Author: 1
             writeln(FormatUTF8('%', [ObjectToJson(Article)]));  // exception
          end;
        finally
          Article.Free;
        end;
        readln;
    finally
      aServer.Free;
    end;
  finally
    aModel.Free;
  end;

Thanks, just to notice TOrmArticle serialization this is not MVC related
  RestModel.Orm.Retrieve(ID,Article);
  writeln(FormatUTF8('%', [ObjectToJson(Article)]));   // << is broken, test in ArticleView

Is this typo in post or in your code
.MapField('TypeKW', 'Type')
vs
.MapField('Type', 'TypeKW')

Any sample of nginx conf for private relay as client, what uri/location to proxy. I already serv / and some other uri's.

Already merged in trunk https://github.com/synopse/mORMot/pull/327
This is my code how I open/create TBufDataset

function TDBConn.OpenAttOpt(aId: integer): TBufDataset;
var Stmt: ISQLDBRows;
begin
  Stmt := fProps.Execute('SELECT ID_ATT, WWVALUE, LABEL '+
     'FROM WWATT_OPT '+
     'WHERE ID_ATT=? ORDER BY ID_ATT, WWVALUE', [aID]);
  if not Assigned(fAttOptDataSet) then begin
     fAttOptDataSet := TBufDataset.Create(nil);
     fAttOptDataSet.FieldDefs.Add('ID_ATT',ftInteger);
     fAttOptDataSet.FieldDefs.Add('WWVALUE',ftString, 25);
     fAttOptDataSet.FieldDefs.Add('LABEL',ftString, 40);
     fAttOptDataSet.CreateDataSet;
     fAttOptDataSet.Fields[0].Visible:=False;
     fAttOptDataSet.Fields[1].DisplayLabel:='Value';
     fAttOptDataSet.Fields[1].DisplayWidth:=25;
     fAttOptDataSet.Fields[2].DisplayLabel:='Label';
     fAttOptDataSet.Fields[2].DisplayWidth:=40;
  end;
  if ToClientDataSet(fAttOptDataSet, Stmt.Instance, 0, cdsReplace, false) then 
     result := fAttOptDataSet
  else
     result := nil;
end; 

Note: If SQL is empty autocreated fields is Bad, I always create fields manually

@ab
I just investigate SynDBMidasVCL and find a bug for fpc to work.
{$endif FPC} in line 193 must be moved to line 226. First 3 ToClientDataSet functions are Delphi midas dependent. Also exclude add {$ifndef fpc} in implementation for first 3. Last 2 functions works fine with fpc and TBufDataset.

Thanks AOG, but this unit is not replacement for SynDBMidasVCL I need dataset from SynDB server to execute any query in existing not ORM Firebird DB.

@ab You make my day
Just tested TestSQL3
Manjaro Linux - Linux 5.4.36-1-MANJARO (cp65001)
    4 x Intel(R) Core(TM) i3-8145U CPU @ 2.10GHz (x64)
Using mORMot 1.18.5970
    TSQLite3LibraryStatic 3.31.0 with internal MM
Generated with: Free Pascal 3.2 64 bit compiler

Time elapsed for all tests: 2m36
Total assertions failed for all test suits:  0 / 43,251,504

with mormot.core.fpcx64mm.pas

Time elapsed for all tests: 1m04
Total assertions failed for all test suits:  0 / 43,253,899

I can't bеlieve this speed boost :-)
Notice total No. of test is different?

Can you change some date to day<13, all bad dates have day > 12(months) just a hint

Datetime field in insert batch is: '2019-11-02T05:55:51'
In Update batch is: '2019-11-02 06:03:28' without T

I'm thinking about Universal Front Aggregate Rest (Interface) Server with API to register/unregister backend servers.
All backend server on start will register there API to front gateway server and unregister on end.
On API update  we will just stop backend server and start new version of backend  server and new API will be registered on gateway.
Also you can start multiple instances of backend server with same API and gateway will become proxy server.
I have a dream! Can my dream become reality?