mORMot Open Source

Great as always, ab!
Thank you!

Generally speaking also plus FTP, FTPS, and SFTP

ftp://[user[:password]@]server[:port]/path/to/remote/resource.mpeg
sftp://[user[:password]@]server[:port]/path/to/remote/resource.mpeg

From
https://ffmpeg.org/ffmpeg-protocols.html#ftp

Humm, you are rigth. Testing with "ping" works fine.
But with small test program like

 
begin
  for i := 10 to 80 do
    begin
      write(IntToStr(i) + ',');
      sleep(200);
    end;
end. 

I get this log

Start at: 07.02.2024 19:54:58
OnRedirect at: 07.02.2024 19:55:06
10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52
OnRedirect at: 07.02.2024 19:55:12
,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,
Exit Code: 0

function TFoo.OnRedirect(const text: RawByteString; pid: cardinal): boolean;
begin
  Result := False;
//  Writeln(pid);
  if text <> '' then
    begin
      Writeln('OnRedirect at: ' + DateTimeToStr(Now));
      Writeln(text);
    end;
end;

As always, thanks for the lightning-fast response, ab! ))

Add.

Is it possible to add RedirectOutput execution after a certain timeout rather than after the buffer is full?
For long processes with percentage progress, a lot of time passes until the buffer is filled out and the data is transferred to TOnRedirect.
Thanks!

Thank you Arnaud for your daily work and for sharing your knowledge and experience with us!
Great job!

Regarding your question in the blog article about the magic number 1.0594631 is the twelfth root of 2, if I'm not mistaken.
And your hint about programming music suggests that there are 12 semitones in an octave and each semitone differs from its neighbors by 1.0594631 (the twelfth root of 2).
I guessed? )))))

Yes, 48 and below working fine ))). I added the numbers for Ibx firebird 25 to my previous post.

Because, as I wrote earlier, embedded version of fbclient.dll from firebird 5 installation was used instead of connecting to 2.5 firebird server

Yes, that's right, https://www.firebirdfaq.org/faq197/

Yes, but without success.

! EXC   EIBInterBaseError {Message:"Engine Code: 335544351\r\nunsuccessful metadata update"} [Main] at 0100375a46 client/include/wloadlibrary.inc tfbclientapi.ibdatabaseerror (209)

Great, thanks for sharing your experience!

Ок, thanks. Yes, it is clear!

Yes, with my local edits MVCServerFirebirdIbx compiling successful for both (Lazarus and Delphi 12) and working as expected, except FTS.
Tomorrow I'll try to play with ..\ex\extdb-bench\PerfTestConsole.dpr for both (Lazarus and Delphi 12) with the latest ab's commit.

I suspect that the performance figures that I gave a little earlier are not entirely correct.
Most likely the embedded version of fbclient.dll server from firebird 5 installation was used instead of connecting to a particular version of firebird server.
But this is just a guess, I'll try to figure it out tomorrow.

Add.
By the way. Thanks madorin, there is a pretty good library with precise transaction controls for Firebird. Separate for reading and writing transactions, at least.
Maybe this could be a starting point for the next Firebird driver for mORMot 2
https://github.com/madorin/fibplus/
https://github.com/madorin/fibplus/commits/master

Some test results

Running tests using Synopse mORMot framework 2.2.6749, compiled with Free Pascal 3.2.2 64 bit, against SQLite 3.44.2, on Windows 8 64bit (6.2.9200), at 2024-01-19 13:28:17.

Insertion speed								|	Read speed
 			Direct	Batch	Trans	Batch Trans		| 				By one	All Virtual	All Direct
Zeos firebird 25	2265	60781	16408	65852			|	Zeos firebird 25	10884	366770		399840
Zeos firebird 4		2187	55836	6949	63136			|	Zeos firebird 4		6944	373538		412864
Zeos firebird 5		2473	65590	8084	52379			|	Zeos firebird 5		8154	403177		490196
Ibx firebird 25		2366	43955	15503	40030			|	Ibx firebird 25		3850	164076		120290
Ibx firebird 4		1296	37603	3675	49165			|	Ibx firebird 4		1798	106503		99975
Ibx firebird 5		1275	30746	4452	40146			|	Ibx firebird 5		1967	221028		214850

For some reason log shows Windows 8 64bit, but in fact Windows 11 64bit.

fbintf1.4-0 already installed on Delphi.

For Lazarus:
MVCServerFirebirdIbx.dpr(54,17) Error: identifier idents no member "CreateDescendingPK"
MVCServerFirebirdIbx.dpr(95,1) Error: Can't open resource file "D:\Projects\Delphi\Components\Synopse\mORMot2-2.0.stable\ex\mvc-blog\mormot.win.default.manifest.res"

mormot.db.sql.ibx.pas(119):
property FirebirdLibraryPathName: RawUtf8
but
mormot.db.sql.ibx.pas(87):
fFirebirdLibraryPathName: string;

For today's commit, "2.2.6617" works fine. Thank you!
But strangely, I followed https://github.com/synopse/mORMot2/commits/master every day and did not see any changes regarding this issue.
Probably I was inattentive and missed it))))
That's why I decided to bother you today.

Yes. Same results.

Sorry. I do not have permission to publish the log of communication with the corporate AD server. Can I send the log to your email address?

Hi Ab.

The call

TLdapClient.GetGroupInfo('', vBaseDN, vLdapGroup, '', True);

Successfully returns the corresponding group (vLdapGroup) with N members.
One of the member from this group has DN returned by vLdapGroup.member[k] kind of 'CN=Super User,OU=1\+2 MEDIA,OU=Domain Users,DC=1plus2,DC=corp';

To determine sAMAccountName for this user, the next call

TLdapClient.GetUserInfo('', vLdapGroup.member[k], '', vLdapUser);

returns an error
Invalid input name: CN=Super User,OU=1\+2 MEDIA,OU=Domain Users,DC=1plus2,DC=corp

If I remove \ before +, then such a user does not exist.
The actual name of this OU is "1+2 Media" without quotes.

How to correctly call a TLdapClient.GetUserInfo with such a parameter?

In continuation of our conversation, this is not a problem of non ASCII characters, rather this is a problem of special characters for AD.

Thanks a lot.

I think it is a bad idea to set up an AutoCommit for transaction for a Rest Server that has as backend firebird database.
Below is a snippet from The Firebird Book: A Reference for Database Developers Copyright © 2004 by Helen Borrie and IBPhoenix

COMMIT with the RETAIN Option

The optional RETAIN [SNAPSHOT] extension to the COMMIT statement causes the server to retain a “snapshot” of the physical transaction’s context at the time the statement is executed and start a new transaction as a clone of the committed one. If this so-called soft commit is used on a SNAPSHOT or SNAPSHOT TABLE STABILITY transaction, the cloned transaction preserves the same snapshot of the data as the original transaction had when it started.

Although it does commit the work permanently and thus change the state of the database, COMMIT RETAIN (CommitRetaining) does not release resources. In the lifespan of a logical task that comprises many repetitions of a similar operation, cloning the context reduces some of the overhead that would be incurred by clearing resources each time with COMMIT, only to allocate the identical resources all over again when a new transaction is started. In particular, it preserves the cursors on sets selected and currently “open.”

The same TID remains active in the TSB and never appears there as “committed.” For this reason, it is often referred to as soft commit, in contrast with the “hard” commit performed by an unmodified COMMIT statement. Each soft commit is like a savepoint with no return. Any subsequent ROLLBACK reverses only the changes that have been posted since the last soft commit. The benefit of the soft commit is that it makes life easy for programmers, especially those using components that implement “scrolling dataset” behavior. It was introduced to support the data grid user interface favored by many users of the
Borland Delphi development environment. By retaining the transaction context, the application can display a seamless before-to-after transition that reduces the effort the programmer would otherwise need to invest in starting new transactions, opening new cursors, and resynchronizing them with row sets buffered on the client. Data access implementations frequently combine posting a single update, insert or delete statement with an immediate COMMIT RETAIN in a mechanism that is dubbed “Autocommit.” It is common for interface layers that implement Autocommit capability to “dumb out” explicit control of transactions by starting one invisibly in situations where the programmer-written code attempts to pass a statement without first starting a transaction itself.

Explicit transaction control is worth the extra effort, especially if you are using a connectivity product that exploits the flexible options provided by Firebird. In a busy environment, the COMMIT RETAIN option can save time and resources, but it has some serious disadvantages:

•  A snapshot transaction continues to hold the original snapshot in its view, meaning the user does not see the effects of committed changes from other
transactions that were pending at the start of the transaction.

•  As long as the same transaction continues being committed with RETAIN, resource “housekeeping” on the server is inhibited, resulting in excessive growth
of memory resources consumed by the TSB. This growth progressively slows down performance, eventually “freezing” the server and, under adverse operating system conditions, even causing it to crash.

•  No old record versions made obsolete by operations committed by a COMMIT RETAIN transaction can be garbage collected as long as the original transaction
is never “hard committed.”

I'm sorry for that. I will take it into account.
Is 120 lines of code a huge? Can I post a tweaked example here or do I need to save it to an external resource?

Anyway, changing AutoFree () to explicit Create/Free did not solve the problem with record versions.

@AB,
do you recommend not using AutoFree () anymore? (https://synopse.info/forum/viewtopic.ph … 947#p33947)
It was a very useful thing.
Thanks!

@Michael,
@AB
Thanks alot for your help resolving this issue!
But from my point of view issue still exists.
I'm using mORMot 1.18.6286 (mORMot_and_Open_Source_friends_2021-05-06_064801_575871d143) and zeoslib-code-0-r7537-trunk
Source code of test app

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils, SynCommons, system.Classes, SynDB, SynTable, ZDbcIntfs, SynDBZeos, mORMot, mORMotDB, mORMotSQLite3,
  SynSQLite3Static, mORMotHttpServer, mORMotHttpClient;

type
  TSimpleRecord = class(TSQLRecord)
  private
    FChangeTime: TDateTime;
  published
    property ChangeTime: TDateTime read FChangeTime write FChangeTime;
  end;

var
  vCommandLine: TCommandLine;
  vMode: RawUTF8;

const
  cServerPort = '8080';
  cConnectStr = '{"Kind":"TSQLDBZEOSConnectionProperties","ServerName":"firebird://",' +
                '"DatabaseName":"d:\\Projects\\Tests\\REC_VERSION_TEST.FDB","User":"SYSDBA","Password":"HuYxjMYsqbe2"}'; //HuYxjMYsqbe2  = masterkey

procedure RunServer;
var
  vDBConnectionParams: TSQLDBZEOSConnectionProperties;
  vModel: TSQLModel;
  vRestServer: TSQLRestServerDB;
  vHttpServer: TSQLHttpServer;
  vSimpleRecord: TSimpleRecord;
begin
  Writeln('RunServer');
  TAutoFree.One(vDBConnectionParams, TSQLDBZEOSConnectionProperties.CreateFromJSON(cConnectStr) as TSQLDBZEOSConnectionProperties);
//  vDBConnectionParams.ZeosURL.Properties.Add('hard_commit=true');
  vModel := TSQLModel.Create([TSimpleRecord]);
  VirtualTableExternalRegisterAll(vModel, vDBConnectionParams, True);
  TAutoFree.One(vRestServer, TSQLRestServerDB.Create(vModel, ':memory:'));
  vRestServer.Model.Owner := vRestServer;
  vRestServer.AcquireExecutionMode[execORMGet] := amBackgroundORMSharedThread;
  vRestServer.AcquireExecutionMode[execORMWrite] := amBackgroundORMSharedThread;
  vRestServer.CreateMissingTables;
  if vRestServer.TableRowCount(TSimpleRecord) = 0 then
    begin
      TAutoFree.One(vSimpleRecord, TSimpleRecord.Create);
      vSimpleRecord.ChangeTime := Now;
      vRestServer.Add(vSimpleRecord, True);
    end;
  TAutoFree.One(vHttpServer, TSQLHttpServer.Create(cServerPort, [vRestServer], '+', useHttpApiRegisteringURI));
  vHttpServer.AccessControlAllowOrigin := '*';
  Writeln('Server is now running on http://localhost:' + cServerPort + '/root'#13#10#13#10+'Press [Enter] to quit');
  Readln;
end;

procedure RunClient;
var
  i: Integer;
  vModel: TSQLModel;
  vHttpClient: TSQLHttpClient;
  vSimpleRecord: TSimpleRecord;
begin
  Writeln('RunClient');
  vModel := TSQLModel.Create([TSimpleRecord]);
  TAutoFree.One(vHttpClient, TSQLHttpClient.Create('localhost', cServerPort, vModel));
  vHttpClient.Model.Owner := vHttpClient;
  TAutoFree.One(vSimpleRecord, TSimpleRecord.Create);
  vHttpClient.Retrieve(1, vSimpleRecord);
  i := 0;
  while true do
    begin
      inc(i);
      vSimpleRecord.FChangeTime := now;
      if vHttpClient.Update(vSimpleRecord) then
        Writeln('Udate #' + i.ToString);
      Sleep(100);
      if ConsoleKeyPressed(13) then break;
    end;
  Readln;
end;

begin
  TAutoFree.One(vCommandLine, TCommandLine.Create);
  vMode := vCommandLine.AsUTF8('mode', 'S', '');
  case vMode[1] of
    'S', 's': RunServer;
    'C', 'c': RunClient;
  end;
end.

Output of Project1.exe -mode C

....
Udate #96
Udate #97
Udate #98
Udate #99
Udate #100
Udate #101
Udate #102
Udate #103
Udate #104
Udate #105
Udate #106
Udate #107
Udate #108
Udate #109
Udate #110
Udate #111
Udate #112
Udate #113

PS>

Output of gstat -u sysdba -p masterkey -r d:\Projects\Tests\REC_VERSION_TEST.FDB

$ gstat -u sysdba -p masterkey -r d:\Projects\Tests\REC_VERSION_TEST.FDB

Database "d:\Projects\Tests\REC_VERSION_TEST.FDB"
Database header page information:
        Flags                   0
        Checksum                12345
        Generation              120
        Page size               4096
        ODS version             11.2
        Oldest transaction      1
        Oldest active           1
        Oldest snapshot         1
        Next transaction        118
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      1
        Implementation ID       16
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           May 10, 2021 23:51:00
        Attributes              force write

    Variable header data:
        *END*


Database file sequence:
File d:\Projects\Tests\REC_VERSION_TEST.FDB is the only file

Analyzing database pages ...
SIMPLERECORD (128)
    Primary pointer page: 168, Index root page: 169
    Average record length: 17.00, total records: 1
    Average version length: 9.00, total versions: 113, max versions: 113
    Data pages: 1, data page slots: 1, average fill: 73%
    Fill distribution:
         0 - 19% = 0
        20 - 39% = 0
        40 - 59% = 0
        60 - 79% = 1
        80 - 99% = 0

    Index NDXSIMPLERECORDID (1)
        Depth: 1, leaf buckets: 1, nodes: 1
        Average data length: 9.00, total dup: 0, max dup: 0
        Fill distribution:
             0 - 19% = 1
            20 - 39% = 0
            40 - 59% = 0
            60 - 79% = 0
            80 - 99% = 0

    Index RDB$PRIMARY1 (0)
        Depth: 1, leaf buckets: 1, nodes: 1
        Average data length: 9.00, total dup: 0, max dup: 0
        Fill distribution:
             0 - 19% = 1
            20 - 39% = 0
            40 - 59% = 0
            60 - 79% = 0
            80 - 99% = 0

Note for this value, please.

...
        Oldest transaction      1
        Oldest active           1
        Oldest snapshot         1
        Next transaction        118
...
SIMPLERECORD (128)
...
    Average version length: 9.00, total versions: 113, max versions: 113

Client was closed.
Server still running and hold
Oldest active            1
So, I have
total versions: 113, max versions: 113 equal to the number of updates to the record in the SIMPLERECORD table
Maybe I'm missing something?
Thanks!

I tried code like this

  while true do
    begin
      inc(i);
      vBatch := TSQLRestBatch.Create(FRestServer, TSQLRecordSample, 5);
      try
        vSQLRecordSample.LastLogin := now;
        vBatch.Update(vSQLRecordSample);
        vBatch.Update(vSQLRecordSample);
        vBatch.Update(vSQLRecordSample);
        vBatch.Update(vSQLRecordSample);
        vBatch.Update(vSQLRecordSample);
        FRestServer.BatchSend(vBatch);
      finally
        vBatch.Free;
      end;
        Writeln('Udate #' + i.ToString);
      Sleep(300);
      if ConsoleKeyPressed(13) then break;
    end;

Result remains the same. Amount of record versions is growing at each FRestServer.BatchSend

A little more details.
When the server is restarted, the first client connection and selection from this table of course started garbage collection (delete record versions).
But for a 24/7 service this is certainly not a good solution.
The question is, how to correctly and safetly close the transaction that occurs when a ThreadSafeConnection is created?
Thanks!

Addon.
Maybe this is more related to Zeos Database Objects transaction handling than ORM.
Please give your advice.
Thanks!

Hi.
Faced such a case.
There is a table with 50 records.
Each record is updated every minute from FHttpClient: TSQLHttpClient, 24/7.
As a result, a lot of record version accumulated for this table, since the ID of the oldest active transaction is equal to the ID of the first update transaction for this table at server start.

Maybe someone knows how to handle this?
Thanks!