Posts by hieroly

hieroly · mORMot 2

Hello AB and TDO, thank you very much!

Weighing the development efficiency, I ultimately decided to implement it using SQL's join. Actually, at the beginning, I also planned to study methods like TOrmMany and FillMany, but didn't understand them.

function TEquipResourceDB.GetCheckResults(out aJsonResult: RawUtf8): integer;
var
  vSQL: TStringList;
begin
  Result := 0;
  aJsonResult := '[]';
  vSQL := TStringList.Create;
  try
    with vSql do
    begin
      add('SELECT t1.ID,');
      add('t1.IODEVICE,');
      add('t1.PrimaryIPAddress,');
      add('t1.PrimaryEquipmentAddress,');
      add('t1.CircuitName,');
      add('t1.CheckPoint,');
      add('t2.TestPassed,');
      add('t2.TestCount,');
      add('t2.Health');
      add('FROM EquipBase t1 JOIN EquipHealth t2 ON t1.ID = t2.EquipBaseID');
      add('LIMIT 2048');
    end;
    // FOrmServer: IRestOrm, saved when building TRestServerDB as FOrmServer := FRestServer.Server;
    aJsonResult := FOrmServer.ExecuteJson([TEquipBase, TEquipHealth], vSql.Text, False, @Result);
  finally
    vSql.Free;
  end;
end;

Considering the retrieval efficiency, the "InitializeTable" method is used to index the relevant fields.

class procedure TEquipHealth.InitializeTable(const Server :IRestOrmServer; const FieldName :RawUtf8;
  Options :TOrmInitializeTableOptions);
begin
  inherited InitializeTable(Server, FieldName, Options);

  if (FieldName = '') or (FieldName = 'EquipBaseID') then
    Server.CreateSQLIndex(TEquipHealth, 'EquipBaseID', False);
end;

Regarding the question, why does the first parameter of `ExecuteJson` need to be associated with an ORM array like `[TEquipBase, TEquipHealth]`? Although I haven't figured this out yet, I still included the associated ORM in it.

hieroly · mORMot 2

hi AB！

type
  TTest = record
    id: Integer;
    name: string;
  end;

  TArrTest= array of TTest;
  TTests = record
    msg: string;
    t: TArrTest;
  end;

Found a strange problem using mormot.core.json RecordSaveJson(aRec, TypeInfo(T)), Convert the record to JSON serialization, using the code mormot.core.json.RecordLoadJson(result, json, TypeInfo(T));
It can also achieve deserialization, whether in Delhi or lazaarus, both directions are reversible!
But it is strange that the serialized text in Delphi is displayed normally, that is, it can be seen as a standard json format.
delphi —— {"msg":"测试","t":[{"id":1,"name":"名字"}]}
lazarus —— "￰Bua1i+ivlQAOAQAAAAABAAAAAAAAAAblkI3lrZc="
However, when the serialized text is base64 under lazrus, it is not universally applicable. It is clearly inappropriate to directly use the system's DeBase64 for the serialized result.
Upon reviewing the code, I discovered a comment that states "will use default Base64 encoding". This is not what I had hoped for,
Is there a way to directly control the "default Base64 encoding" without writing custom code.

Additionally: mormot.core.json ObjectLoadJson(aObj, json) For Lazarus, it would be nice if $M+ could be used to define serialization for public member variables of classes. However, it was found that this only works for published properties of classes.

hieroly · mORMot 2

Hello AB,

I've read many responses regarding whether to use SQL JOIN. You suggested using ORM concepts as much as possible and minimizing the use of relational database concepts. I've been striving to work in this manner in my code.

However, I've found that it's unavoidable to end up with one-to-many or one-to-one relationships, and there's no escaping the need to use reporting components like Fastreport, which require associating a Datasource or Dataset.

After reading numerous posts, I feel that using virtual tables could solve this issue, as they allow for the implementation of ORM while also providing the contiguous nature of databases.

constructor TEquipResourceDB.Create(const aDBFileName: TFileName);
var
  uriStr: string;
begin
  inherited Create;
  FOrmModel := CreateModel;

  uriStr := TSqlDBZeosConnectionProperties.URI('sqlite:', '', 'SQLite3.dll');
  FConnProps := TSqlDBZeosConnectionProperties.Create(uriStr, aDBFileName, '', '');

  VirtualTableExternalRegisterAll(FOrmModel, FConnProps, [regMapAutoKeywordFields]);
  FRestServer := TRestServerDB.Create(FOrmModel, SQLITE_MEMORY_DATABASE_NAME);

  // FRestServer := TRestServerDB.Create(FOrmModel, aDBFileName); //***

  FRestServer.Model.Owner := FRestServer;
  FRestServer.DB.Synchronous := smFull;

  FRestServer.DB.LockingMode := lmExclusive;
  FRestServer.Server.CreateMissingTables(0, [itoNoAutoCreateGroups, itoNoAutoCreateUsers]);
  FOrmServer := FRestServer.Server;  // IRestOrmServer
  // FHttpServer := TSQLHttpServer.Create('8080', [FOrmServer], '+', HTTP_DEFAULT_MODE, 5);
  FHttpServer := TSQLHttpServer.Create('8080', FRestServer);
  FHttpServer.AccessControlAllowOrigin := '*';

  FLoadEquipEvent := TSynBackgroundThreadEvent.Create(LoadEquipEvent, nil, 'Background_LoadEquipEvent');
end;

But based on your replies in the posts, it seems that this approach may not yield better performance.

Therefore, it would be ideal if using `TRestServerDB.Create(FOrmModel, aDBFileName)` directly could provide the connection properties of `TSqlDBConnectionProperties`. Unfortunately, in the ORM context, it equals nil if the table is internal to SQLite3.

How to efficiently obtain 'dataset' using internal SQLite3 through TRestServerDB‘？

Is there a possibility to achieve both efficiency and the capability to provide a Dataset?

Or, when a Dataset is required, is the only option to synthesize the necessary JSON through ORM and then temporarily convert it using `JsonToDataSet`?

mORMot Open Source

#1 Re: mORMot 2 » How to efficiently obtain 'dataset' using internal SQLite3 » 2024-09-15 06:58:17

#2 mORMot 2 » Can mormot control default BASE64 encoding for record serialization? » 2024-09-15 05:26:55

#3 mORMot 2 » How to efficiently obtain 'dataset' using internal SQLite3 » 2024-09-08 01:17:56

Board footer