Load/save TSQLRestBatch from/to file?

edwinsn · 2016-11-22 16:37:27

Is it possible save the content of a TSQLRestBatch instance, in case of a network error, and later reload the content from file, so that it can be sent by TSQLRest.BatchSend() again?

Thanks.

ab · 2016-11-22 16:55:38

Not yet.

hnb · 2016-11-22 23:35:55

I didn't know that it is impossible yet and I have created solution for this.

First you need additional factory to produce batch string:

type
  TBatchAction = (baInsert, baUpdate, baDelete);

  TBatchCacheFactory = class
  public
    class function Insert(AServer: TSQLRestServerDB; ARecord: TSQLRecord; AForceID: Boolean = false;
      const CustomFields: TSQLFieldBits = []): RawUTF8; overload;
    class function Insert(AServer: TSQLRestServerDB; ARecord: TSQLRecord; AForceID: Boolean = false;
      const CustomCSVFields: RawUTF8 = '*'): RawUTF8; overload;

    class function Update(AServer: TSQLRestServerDB; ARecord: TSQLRecord; const CustomFields: TSQLFieldBits = []): RawUTF8; overload;
    class function Update(AServer: TSQLRestServerDB; ARecord: TSQLRecord; const CustomCSVFields: RawUTF8): RawUTF8; overload;
    class function Delete(AServer: TSQLRestServerDB; ARecord: TSQLRecordClass; AID: TID): RawUTF8;
  end;

implementation

{ TBatchCacheFactory }

class function TBatchCacheFactory.Insert(AServer: TSQLRestServerDB; ARecord: TSQLRecord;
  AForceID: Boolean; const CustomFields: TSQLFieldBits): RawUTF8;
var
  LBatch: TSQLRestBatch;
begin
  with TAutoFree.One(LBatch, TSQLRestBatch.Create(AServer, nil)) do
  begin
    LBatch.Add(ARecord, true, AForceID, CustomFields);
    LBatch.PrepareForSending(Result);
  end;
end;

class function TBatchCacheFactory.Update(AServer: TSQLRestServerDB; ARecord: TSQLRecord;
 const CustomFields: TSQLFieldBits): RawUTF8;
var
  LBatch: TSQLRestBatch;
begin
  with TAutoFree.One(LBatch, TSQLRestBatch.Create(AServer, nil)) do
  begin
    LBatch.Update(ARecord, CustomFields);
    LBatch.PrepareForSending(Result);
  end;
end;

class function TBatchCacheFactory.Delete(AServer: TSQLRestServerDB; ARecord: TSQLRecordClass;
  AID: TID): RawUTF8;
var
  LBatch: TSQLRestBatch;
begin
  with TAutoFree.One(LBatch, TSQLRestBatch.Create(AServer, nil)) do
  begin
    LBatch.Delete(ARecord, AID);
    LBatch.PrepareForSending(Result);
  end;
end;

class function TBatchCacheFactory.Insert(AServer: TSQLRestServerDB;
  ARecord: TSQLRecord; AForceID: Boolean;
  const CustomCSVFields: RawUTF8): RawUTF8;
begin
  Result := Insert(AServer, ARecord, AForceID, ARecord.RecordProps.FieldBitsFromCSV(CustomCSVFields));
end;

class function TBatchCacheFactory.Update(AServer: TSQLRestServerDB; ARecord: TSQLRecord;
  const CustomCSVFields: RawUTF8): RawUTF8;
begin
  Result := Update(AServer, ARecord, ARecord.RecordProps.FieldBitsFromCSV(CustomCSVFields));
end;

next you need some helpers for existing mORMot classes:

type
  TSQLRestBatchHelper = class helper for TSQLRestBatch
  public
    function Add(Value: TSQLRecord; SendData: boolean; ForceRecordClass: TSQLRecordClass; ForceID: boolean=false;
      const CustomFields: TSQLFieldBits=[]; DoNotAutoComputeFields: boolean=false): integer; overload;
  end;

  { TSQLRestHelper }

  TSQLRestHelper = class helper for TSQLRest
  public
    function BatchSend(Batch: TSQLRestBatch; const Data: RawUTF8; var Results: TIDDynArray): integer; overload;
    function BatchSend(Batch: TSQLRestBatch; const Data: RawUTF8): integer; overload;
    function Update(Value: TSQLRecord; ForceRecordClass: TSQLRecordClass;
      const CustomFields: TSQLFieldBits=[]; DoNotAutoComputeFields: boolean=false): boolean; overload;
  end;

implementation

{ TSQLRestHelper }

function TSQLRestHelper.BatchSend(Batch: TSQLRestBatch; const Data: RawUTF8;
  var Results: TIDDynArray): integer;
var
  LData: RawUTF8;
begin
  LData := Copy(Data, 1);
  try
    if Batch <> nil then
      result := EngineBatchSend(Batch.Table,LData,Results,Batch.Count)
    else
      result := EngineBatchSend(nil,LData,Results,0)
  except
    on Exception do // e.g. from TSQLRestServer.EngineBatchSend()
      result := HTTP_SERVERERROR;
  end;
end;

function TSQLRestHelper.BatchSend(Batch: TSQLRestBatch; const Data: RawUTF8
  ): integer;
var
  Results: TIDDynArray;
begin
  Result := BatchSend(Batch,Data,Results);
end;

function TSQLRestHelper.Update(Value: TSQLRecord;
  ForceRecordClass: TSQLRecordClass; const CustomFields: TSQLFieldBits;
  DoNotAutoComputeFields: boolean): boolean;
var
  DefaultClass: TSQLRecordClass;
begin
  Result := False;

  if (self=nil) or (Value=nil) or (ForceRecordClass=nil) then
    exit;

  if not PSQLRecordClass(Value)^.InheritsFrom(ForceRecordClass) then
    exit;

  DefaultClass := PSQLRecordClass(Value)^;
  PSQLRecordClass(Value)^ := ForceRecordClass;
  result := Self.Update(Value, CustomFields, DoNotAutoComputeFields);
  PSQLRecordClass(Value)^ := DefaultClass;
end;

{ TSQLRestBatchHelper }

function TSQLRestBatchHelper.Add(Value: TSQLRecord; SendData: boolean;
  ForceRecordClass: TSQLRecordClass; ForceID: boolean;
  const CustomFields: TSQLFieldBits; DoNotAutoComputeFields: boolean): integer;
var
  DefaultClass: TSQLRecordClass;
begin
  result := -1;
  if (self=nil) or (Value=nil) or (fBatch=nil) or (ForceRecordClass=nil) then
    exit;

  if not PSQLRecordClass(Value)^.InheritsFrom(ForceRecordClass) then
    exit;

  DefaultClass := PSQLRecordClass(Value)^;
  PSQLRecordClass(Value)^ := ForceRecordClass;
  result := Self.Add(Value, SendData, ForceID, CustomFields, DoNotAutoComputeFields);
  PSQLRecordClass(Value)^ := DefaultClass;
end;

now put all together:

  LBatch := TBatchCacheFactory.Update(AServer, LMyRecord, LCSV); // save batch to string
  { ... }
  { save batch to file or anywhere }
  { ... }
  FServer.BatchSend(nil, LBatch); // execute our string

done! ^^

Last edited by hnb (2016-11-22 23:42:30)

edwinsn · 2016-11-23 05:53:59

Thanks Maciej.

Does your code allow serializing multiple batch operation controlled by an instance of TSQLRestBatch? I mean something equals to:

var
  myBat, newBatch: TSQLRestBatch;
begin
  myBat.Add(...);
  myBat.Delete(...);
  myBat.Update(...);
  myBat.SaveToFile('c:\test.txt');
  
  // now reload the saved data.
  newBatch := TSQLRestBatch.Create(myDb);
  newBatch.LoadFromFile('c:\test.txt');
  myDb.Send(myBatch, ...);

end;

Last edited by edwinsn (2016-11-23 05:54:31)

ab · 2016-11-23 08:03:56

Code like

PSQLRecordClass(Value)^ := ForceRecordClass;

is just unsafe and probably not cross-platform.

hnb · 2016-11-23 08:54:54

@ab this code is not used in presented "batch save/load system" (just too much copy/paste from my helper module). Btw. It works perfectly on all of my FPC targets and is very standard for Pascal: ARM, Linux/Windows... without problems.

@edwinsn you have string per operation: Add has own string, Delete has own string, Update has own string. The best idea is to store this "batch" strings in separate files (or in single SQLite file as records):

FileFromString(TBatchCacheFactory.Add(...), 'C:\add001.txt');
FileFromString(TBatchCacheFactory.Update(...), 'C:\update001.txt');
FileFromString(TBatchCacheFactory.Delete(...), 'C:\delete001.txt');

...

myDb.BatchSend(nil, StringFromFile('C:\add001.txt'));
myDb.BatchSend(nil, StringFromFile('C:\update001.txt'));
myDb.BatchSend(nil, StringFromFile('C:\delete001.txt'));

you can also try to use somehow single text file mORMot is super elastic.

willo · 2016-11-24 05:27:36

@hnb

 TSQLRestHelper = class helper for TSQLRest

is not Delphi 6/7 safe/

mORMot Open Source

#1 2016-11-22 16:37:27

Load/save TSQLRestBatch from/to file?

#2 2016-11-22 16:55:38

Re: Load/save TSQLRestBatch from/to file?

#3 2016-11-22 23:35:55

Re: Load/save TSQLRestBatch from/to file?

#4 2016-11-23 05:53:59

Re: Load/save TSQLRestBatch from/to file?

#5 2016-11-23 08:03:56

Re: Load/save TSQLRestBatch from/to file?

#6 2016-11-23 08:54:54

Re: Load/save TSQLRestBatch from/to file?

#7 2016-11-24 05:27:36

Re: Load/save TSQLRestBatch from/to file?

Board footer