mORMot Open Source

Thank you so much!
I'll try ASAP

Hello and thanks for your answer.

First of all, sorry for my very lame English.

I use Version field for periodical replication from my master database to slave database(s) (from master read-write server to read-only server).
I use batch because data in srcorm are came from another server(s), then i process them and store correspondent data in master orm. There is batch of data in srcorm, some data may result in an dstorm entry being added, some may result in an dstorm
entry being updated, but i need to process all of them in one transaction for many reasons.

Here is a working example with results in csv: https://gist.github.com/avavdoshin/fd1d … f8f0638691
Record versions in this example doesn't changes for updates, so this changes will not be replicated to slave database.

hello, Thomas, thank you for answer.
FPC 3.3.1, x86_64, windows 10 (codetyphon 8.0). Mormot2 github version 2.1.5479

Whole example too complicated, here is simplified part of code:

type
  TFSDBGlobalStates = class(TOrm)
  private
    { some attributes }
    FChangeTimeStamp: TUnixMSTime;
    FVersion: TRecordVersion;
  public
    class procedure InitializeTable(const Server: IRestOrmServer; const FieldNames: RawUtf8; Options: TOrmInitializeTableOptions); override;
  published
    { some properties }
    property ChangeTimeStamp: TUnixMSTime read FChangeTimeStamp write FChangeTimeStamp;
    property Version: TRecordVersion read FVersion write FVersion;
  end;

function findORM(asrcORM: TOrm; ormClass: TOrmClass):TOrm;
begin
  {some code}
  if ormClass = TFSDBGlobalStates then
    result := ormClass.CreateAndFillPrepare(LocalDBOrm.Orm, 'ObjectGUIDid = ? and SourceGUIDid = ? and StateCodeID = ?', [FindGlobalDictID(SrvId, (aSrcOrm as TFSDBLocalStates).ObjectGUIDid, false), FindGlobalDictID(SrvId, (aSrcOrm as TFSDBLocalStates).SourceGUIDid, false), FindGlobalDictID(SrvId, (aSrcOrm as TFSDBLocalStates).StateCodeID, false)])
  {some code}
  else
    result := nil;
  if assigned(result) then
    try
      result.FillOne;
    except
      FreeAndNilSafe(result);
    end;
end;

procedure CopyOrmClass(aSrcOrm: TOrm; var aDstOrm: TOrm);
begin
  { some code }
  if aSrcOrm.ClassName = 'TFSDBLocalStates' then
    begin
      {some code}
      (aDstOrm as TFSDBGlobalStates).ChangeTimeStamp := (aSrcOrm as TFSDBLocalStates).ChangeTimeStamp;
    end;
  {some code}   
end;

Procedure MainProc;
var
  srcORM, dstORM: TORM;
  SendResult: integer; 
begin
  tmpAddBatch := TRestBatch.Create(localDBOrm.Orm, TFSDBGlobalStates, 0);
  tmpUpdBatch := TRestBatch.Create(localDBOrm.Orm, TFSDBGlobalStates, 0);
  { some code where i get srcOrm  }
  try
    while srcORM.FillOne do
      begin
        dstORM := FindORM(srcORM, TFSDBGlobalStates);
        try
          if not assigned(dstORM) then
            dstOrm := TFSDBGlobalStates.Create;
          CopyOrmClass(srcOrm, dstORM);
          if dstOrm.ID > 0 then
            tmpUpdBatch.Update(dstOrm)
          else
            tmpAddBatch.Add(dstORM, true);
        finally
          FreeAndNilSafe(dstOrm);
        end;
      end;   
  finally
    FreeAndNilSafe(srcOrm);
  end;
  {some code}
  if (tmpAddBatch.Count>0) or (tmpUpdBatch.count>0) then
    begin
      {some code}
      if tmpAddBatch.Count>0 then
        sendResult := LocalDBOrm.Orm.BatchSend(tmpAddBatch)
      else
        sendResult := HTTP_SUCCESS;
      if sendResult=HTTP_SUCCESS then
        if tmpUpdBatch.Count >0 then
          sendResult := LocalDBOrm.Orm.BatchSend(tmpUpdBatch)
        else
          sendResult := HTTP_SUCCESS;
      {check for unsuccessfull send removed for this example}
      if sendresult=HTTP_SUCCESS then
        LocalDBOrm.Orm.commit;
     end;   
end;

No, there is no problem at all if the slave is SQlite3.

(i leave the same message in ISSUES on github)

First of all i delete all databases (master and slave)

Then i create new database on master (sqlite), create some orm instances (TSomeOrm, TSomeOtherOrm)
Then i create TMyBlobOrm instances with blobs (just simple strings in blob) on master, they had IDs 1,2,3,4,5,6
Then i delete TMyBlobOrm with id 4 on master
then create some other orm instances (TSomeAnotherORM) on master

Then i create SLAVE database (postgres) and synchronise from master (sqlite) to slave (postgresql) (calling RecordVersionSynchroniseSlave from slave).

In step-by-step debug i've got error
"Unexpected TSqlDBPostgresConnectionProperties.SharedTransaction (1,2)" in mormot.db.sql line 3701

Maybe it's because in prepared for sending batch i see in JSON that DELETE goes before all other lines? But this record cannot be deleted, it doesn't exist on slave server yet.

If i only create orm instances on master (with or without blobs, but do not delete any instance) - synchronise works ok

Batch prepared correct, but something happened in BatchSend, i suppose, because i've got error when step-by-step debug "TSqlDBPostgresStatement.ExecutePrepared: Invalid array type ftBlob on bound parameter #3" (it is one of my blob fields) "in mormot.db.sql.postgres.pas line 656"

Problem solved.

Arnaud, it was my mistake, so sorry to bother you.
I use data sending in complicated way: i made my own batch on client (select records from LOCAL orm sqlite database, split it to batches with 10000 record in each and fill in-memory rest storage with in-memory table, then export its contents to binary rawbytestring and call server interface method, passing this rawbytestring).

On the server side i import this binary rawbytestring back to another in-memory table in another in-memory rest storage and process it. Then i send data to SERVER orm database.

I used wrong models scheme, that's why it doesn't work as expected. Have no idea why it doesn't affect on MongoDB and SQLite backends, only to external tables created via TRestExternalDBCreate.

BTW, mormot.db.sql.postgres much faster and more stable than ZEOS postgres implimentation, thank you for recommendation.

It should search only via id, isn't it?
Db created by mormot engine via createmissingtable

I start transaction for my tormclass immediately before call batchsend and commit/rollback immediately after call batchsend. There is no another transactions for this class at all.
I saw you recommend this way of transactions handling somewhere on this forum some time ago.
This stucks does not exists neither wih sqlite nor with mongodb, only with zeos-powered databases.

I'll try suggested solutions tomorrow and post results, thank you!

Thank you, i'll try it tomorrow
But why it stucks only with updating orms? Adding orms works very fast. Maybe i misunderstood batch logic?
I call tmpbatch.add(mytorminstance, true) for new orm and tmpbatch.update(mytorminstance) for orm values change and pass whole mytorm instance in both cases (i use myorminstance:= tmyormclass.createandfillprepared, then call myorminstance.fillone and check myorminstance.id to search  do i need update (if myorminstance.id>0) or add new one.

It works now, thank you!

I closed issue.