mORMot Open Source

function TSelect.Retrieve: Variant;
begin
    Result := FRestOrm.Orm.RetrieveDocVariantArray(TAmostra, '',
      FWhere,
      TDocVariantData(FWhereParamValues).ToArrayOfConst,
      FCustomFieldsCsv)
...
end;

FWhere =  'inner join Solicitacao s on (s.RowID = Amostra.solicitacaoID)  where (amostra.empresaID = ?) and (s.numero = ?)'
FWhereParams = [1, '4']

00000000102FB745 DB     mormot.db.sql.postgres.TSqlDBPostgresConnection(03575620) Connected to 191.101.78.31:5001 DB_SOROTRACK using...
000000001037BA78 DB     mormot.db.sql.postgres.TSqlDBPostgresStatement(0352bea0) Prepare t=1.85s c=00 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,SolicitacaoID,Exames,DataEtiqueta,RackID,Linha,Coluna,Coletado,Cancelada,ID from public.Amostra where EmpresaID=?  <<<<< this not is my sql
00000000103BBE57 SQL    mormot.db.sql.postgres.TSqlDBPostgresStatement(0352bea0) Execute t=2.11s c=00 r=25 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,SolicitacaoID,Exames,DataEtiqueta,RackID,Linha,Coluna,Coletado,Cancelada,ID from public.Amostra where EmpresaID=1 <<<< neither this
00000000103BD6F4 SQL    mormot.orm.sql.TOrmVirtualTableCursorExternal(03e3b1b0) Search select DataCriacao,UltimaAlteracao,EmpresaID,Numero,SolicitacaoID,Exames,DataEtiqueta,RackID,Linha,Coluna,Coletado,Cancelada,ID from public.Amostra where EmpresaID=?
000000001043C4A4 DB     mormot.db.sql.postgres.TSqlDBPostgresStatement(03529e20) Prepare t=516.47ms c=01 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero=? and ID=?
000000001047C973 SQL    mormot.db.sql.postgres.TSqlDBPostgresStatement(03529e20) Execute t=774.69ms c=01 r=0 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero='4' and ID=11
00000000104BBB97 SQL    mormot.db.sql.postgres.TSqlDBPostgresStatement(03529e20) Execute t=256.64ms c=01 r=0 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero='4' and ID=12
0000000014BCD30A SQL    mormot.orm.sql.TOrmVirtualTableCursorExternal(03e3b170) Search select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero=? and ID=?
0000000015AC76CC SQL    mormot.db.sql.postgres.TSqlDBPostgresStatement(03529e20) Execute t=256.49ms c=01 r=1 q=select DataCriacao,UltimaAlteracao,EmpresaID,Numero,DataSolicitacao,Paciente,PostoID,AtendenteID,ID from public.Solicitacao where Numero='4' and ID=10
0000000015AC7F3C SQL    mormot.orm.sql.TOrmVirtualTableCursorExternal(03e3b170) Search select ...

0000000018F01559 res    mormot.db.raw.sqlite3.TSqlDatabase(035750a0) [{"rowid":1,"DataCriacao":"2024-10-10T14:21:43","UltimaAlteracao":"2024-10-24T10:04:18","EmpresaID":1,"Numero":"123","SolicitacaoID":10,"Exames":"he","DataEtiqueta":"2024-10-
...
10T18:20:00","RackID":1,"Linha":1,"Coluna":2,"Coletado":1,"Cancelada":0,"numerosolicitacao":"4","Paciente":"ana","DataSolicitacao":"2024-10-10T08:39:00"},
{"ID":9,"DataCriacao":"2024-10-10T14:21:43","Ultim... (truncated) length=7692

0000000018F35FCB  -    99.053.647
0000000019A38E4D  +    mormot.rest.memserver.TRestServerFullMemory(0349b260).URI POST v1/amostraService/buscar in=232 B
0000000019C92853 SQL    mormot.db.raw.sqlite3.TSqlDatabase(035750a0) from cache  SELECT Amostra.RowID, Amostra.*, s.numero numerosolicitacao, s.paciente, s.datasolicitacao FROM Amostra  inner join Solicitacao s on (s.RowID = Amostra.solicitacaoID)  where (amostra.empresaID = :(1):) and (s.numero = :('4'):)
0000000019F70618 ret    Infra.Authentication.JWT.TRestServerURIContext_JWT(036e9b40) [{"ID":1,"DataCriacao":"2024-10-10T14:21:43","UltimaAlteracao":"2024-10-24T10:04:18","EmpresaID":1,"Numero":"123","SolicitacaoID":10,"Exames":"he","DataEtiqueta":"2024-10-10T18:20:00","RackID":1,"Linha":1,"Coluna":2,"Coletado":true,"Cancelada":false,"numerosolicitacao":"4","Paciente":"ana","DataSolicitacao":"2024-10-10T08:39:00"},{"ID":5,"DataCriacao":"2024-10-10T14:21:43","UltimaAlteracao":"2024-10-24T10:06:58","EmpresaID":1,"Numero":"125","SolicitacaoID":10,"Exames":"ad","DataEtiqueta":"2024-10-10T18:20:33","RackID":1,"Linha":1,"Coluna":1,"Coletado":true,"Cancelada":false,"numerosolicitacao":"4","Paciente":"ana","DataSolicitacao":"2024-10-10T08:39:00"},...
{"ID":8,"DataCriacao":"2024-10-10T14:21:43","UltimaAlteracao":"2024-10-31T08:18:58","EmpresaID":1,"Numero":"128","SolicitacaoID":10,"Exames":"ijiji","DataEtiqueta":"2024-10-10T18:20:00","RackID":0,"Linha":0,"Coluna":0,"Coletado":true,"Cancelada":false,"numerosolicitacao":"4","Paciente":"ana","DataSolicitacao":"2024-10-10T08:39:00"},{"ID":9,"DataCriacao":"2024-10-10T14:21:43","Ultim... (truncated) length=7692
0000000019F71039  -    05.472.748

function TAmostraService.InsertMany(pAmostras: variant): TServiceCustomAnswer;
var
  vListaAmostras: IDocList;
  vItemAmostra: variant;
  vAmostra: TAmostra;
begin
  vAmostra := TAmostra.Create;
  try
    // delphi compiles, fpc no (Sorotrack.AmostraService.pas(339,23) Error: Can't determine which overloaded function to call)
    vListaAmostras := DocListFrom(pAmostras.amostras);
    for vItemAmostra in vListaAmostras do
    begin
      // delphi compiles, fpc no. what do do? Sorotrack.AmostraService.pas(343,38) Error: Incompatible types: got "OleVariant" expected "UTF8String"
      vAmostra.Numero := vItemAmostra.Numero;
      // delphi compiles, fpc no. what do do?
      // Sorotrack.AmostraService.pas(347,31) Error: Incompatible types: got "Pointer" expected "TSolicitacao"
      vAmostra.Solicitacao := Pointer(PtrInt(vItemAmostra.Solicitacao));
      vAmostra.Exames := vItemAmostra.Exames;
      vAmostra.DataEtiqueta := vItemAmostra.DataEtiqueta;
      vAmostra.Empresa := Pointer(PtrInt(CurrentUserCompany));
      Result := Add(vAmostra);
    end;
  finally
   vAmostra.Free;
  end;
end;  

function TRepository.ConnectionClass(pDBConnection: TSqlDBConnectionPropertiesClass): IRepository;
begin
  Result := Self;
  FConfigDB := pDBConnection.Create(FHost, FDatabase, FUser, FPassword);
  VirtualTableExternalRegisterAll(FOrmModel, FConfigDB);
  FOrmModel.Props[TAmostra].ExternalDB.MapField('Solicitacao', 'IdSolicitacao');
  FRestOrm := TRestServerDB.Create(FOrmModel, SQLITE_MEMORY_DATABASE_NAME);
  FRestOrm.DB.Synchronous := smOff;
  FRestOrm.DB.LockingMode := lmExclusive;
  FRestOrm.Server.CreateMissingTables;
  FRestOrm.Server.CreateSqlMultiIndex(TAmostra, ['empresa', 'numero'], true, 'sk_amostra_01');
  FRestOrm.Server.CreateSqlMultiIndex(TSolicitacao, ['empresa', 'numero'], true, 'sk_solicitacao_01');
  FRestOrm.Server.TrackChanges([TEmpresa, TUsuario, TAmostra,
    TRack, TEvento], TMyHistory, 100, 10, 65536);
end;

function TRestServerUriContext.Authenticate: boolean;
...
begin
...
    if (rsoSessionInConnectionOpaque in Server.Options) and
       (Call^.LowLevelConnectionOpaque <> nil) then
    begin
      // TAuthSession instance may have been stored at connection level
      // to avoid signature parsing and session lookup
      s := pointer(Call^.LowLevelConnectionOpaque^.ValueInternal);
      if s <> nil then
        if s.InheritsFrom(Server.fSessionClass) then
        begin
          SessionAssign(s);  <<<<<<<<<<<<<<<<<<<<< here does
          exit;
        end
        else
          Call^.LowLevelConnectionOpaque^.ValueInternal := 0; // paranoid
    end;
    // parse signature to retrieve the associated session
    Server.fSessions.Safe.ReadOnlyLock; // allow concurrent authentication
    try
      a := pointer(Server.fSessionAuthentication);
      if a <> nil then
      begin
        n := PDALen(PAnsiChar(a) - _DALEN)^ + _DAOFF;
        repeat
          s := a^.RetrieveSession(self); // retrieve from URI or cookie
          if s <> nil then
          begin
            if (Log <> nil) and
               (s.RemoteIP <> '') and
               (s.RemoteIP <> '127.0.0.1') then
              Log.Log(sllUserAuth, '%/% %',
                [s.User.LogonName, s.ID, s.RemoteIP], self);

             SessionAssign(s) <<<<<< are not missing this here????

            exit;
          end;
          inc(a);
          dec(n);
        until n = 0;
      end;
...
end;

  TContatos = class(TOrmMany)
  private
    FSource: TPessoa;
    FDest: TContato;
  published
    property Source: TPessoa read FSource;
    property Dest: TContato read FDest;
  end;
  
  TContato = class(TOrm)
  private
    FTelefone: RawUTF8;
    FEmail: RawUTF8;
  published
    property Telefone: RawUTF8 read FTelefone write FTelefone;
    property Email: RawUTF8 read FEmail write FEmail;
  end;
  
  TRacas = (rPoodle, rViraLata, rPitbull, rShawShaw);

  TPet = class(TOrm)
  private
    FRaca: TRacas;
    FNome: RawUTF8;
    FDono: TPessoa;
  published
    property Raca: TRacas read FRaca write FRaca;
    property Nome: RawUTF8 read FNome write FNome;
    property Dono: TPessoa read FDono write FDono;
  end;
  
  TCidade = class(TOrm)
  private
    FNome: RawUTF8;
  published
    property Nome: RawUTF8 read FNome write FNome;
  end;

  TPessoa = class(TOrm)
  private
    FNome: RawUTF8;
    FIdade: Integer;
    FCidade: TCidade;
    FContatos: TContatos;
    FPets: TObjectList;
  public
    function NewContato(const pEmail, pFone: RawUtf8): TContato;
    function NewPet(const pRaca: TRacas; const pNome: RawUTF8): TPet;
    property Pets: TObjectList read FPets write FPets;
    destructor Destroy; override;
  published
    property Nome: RawUTF8 read FNome write FNome;
    property Idade: Integer read FIdade write FIdade;
    property Cidade: TCidade read FCidade write FCidade;
    property Contatos: TContatos read FContatos write FContatos;
  end;

var
  vPessoas: TPessoa;
  vCidade: TCidade;
  vContato: TContato;
  vOrm: IRestOrm;
  vIdPessoa: TID;
  vI: Integer;
begin
  vPessoas := TPessoa.CreateAndFillPrepareJoined(FRestOrm.Orm, '', [], []);
  try
	  Memo2.Lines.Add(Format('Quantidade de pessoas: %d', [vPessoas.FillTable.RowCount]));  // ok return 2 pessoa
	  while vPessoas.FillOne do
	  begin
      Memo2.Lines.Add(Format('Id: %d', [vPessoas.ID]));
      Memo2.Lines.Add(Format('Nome: %s', [vPessoas.Nome]));
      Memo2.Lines.Add(Format('Idade: %d', [vPessoas.Idade]));
      Memo2.Lines.Add(Format('Cidade: %s', [vPessoas.Cidade.Nome]));

      vPessoas.Contatos.FillPrepareMany(FRestOrm.Orm, 'RowId>=?', [], [vPessoas.ID]);
      Memo2.Lines.Add(Format('Quantidade de Contatos: %d', [vPessoas.Contatos.FillTable.RowCount])); // ERROR HERE. return 0 should be 2 (pedro and joao) <<<<<<
      while vPessoas.Contatos.Dest.FillOne do  // fillone return false
      begin
        Memo2.Lines.Add(Format('  Contato: %s - %s', [vI, vPessoas.Contatos.Dest.Email, vPessoas.Contatos.Dest.Telefone])); //is not retrieving email and telefone value
      end;
      vPessoas.FillClose;

      // pets is nil on this moment then i do this, but i don´t know if is the better aproach.  <<<<<<

      vPessoas.Pets := FRestOrm.RetrieveList(TPet, 'dono=?', [vPessoas.Id]);
      Memo2.Lines.Add(Format('Quantidade de Pets: %d', [vPessoas.Pets.Count]));  ok 3 from first pessoa and 1 to second
      for vI := 0 to vPessoas.Pets.Count-1 do
        Memo2.Lines.Add(Format('  Pets %d: %s', [vI, TPet(vPessoas.Pets[vI]).Nome]));

      Memo2.Lines.Add('-----------------------');

      vPessoas.Pets.Clear;
      vPessoas.Pets.Free;
      vPessoas.Pets := nil;
	  end;
  finally
	  vPessoas.Free;
  end;