mORMot Open Source

Quelltexknecht

Member

Registered: 2022-06-28

Posts: 1

How To Read Multiple 1:n and n:m Relations correct?

I am Having a Problem with CreateAndFillPrepareMany

How to Write the correct Sql to fill multiple Manys? what is the correct Loop through all data?

When i look direct in the Database it seems to be fine.

  TSchedulerIntervall = class; // forward deklarations
  TSchedulerInstanz = class;
  TSchedulerWorkInfo = class;

  TSchedulerPivot = Class(TSQLRecordMany)
  private
    FSource: TSchedulerInstanz;
    FDest: TSchedulerIntervall;
  published
    property Source: TSchedulerInstanz read FSource; // map Source column // feste namen
    property Dest: TSchedulerIntervall read FDest; // map Dest column // feste namen
  End;

  TSchedulerWorkInfoPivot = Class(TSQLRecordMany)
  private
    FSource: TSchedulerIntervall;
    FDest: TSchedulerWorkInfo;
  published
    property Source: TSchedulerIntervall read FSource; // map Source column // feste namen
    property Dest: TSchedulerWorkInfo read FDest; // map Dest column // feste namen
  End;

TSchedulerInstanz = class(TSQLRecord)
  private
    FInstanzName: RawUTF8;
    FIntervall: TSchedulerPivot;
  published
    property InstanzName: RawUTF8 read FInstanzName write FInstanzName stored AS_UNIQUE;
    property Intervalls: TSchedulerPivot read FIntervall;  // Many to many Lösung Cascading Delete über den Namen TXToBeDeleted
  end;

  TSchedulerIntervall = class(TSQLRecord)
  private
    FName: RawUTF8;
    FWorkInfos: TSchedulerWorkInfoPivot;
  published
    property Name: RawUTF8 read FName write FName stored AS_UNIQUE;
    property WorkInfos: TSchedulerWorkInfoPivot read FWorkInfos write FWorkInfos;
  end;

  TSchedulerWorkInfo = Class(TSqlRecord)
  private
    FEnde: TTime;
    FStart: TTime;
  published
    property Start: TTime read FStart write FStart;
    property Ende: TTime read FEnde write FEnde;
  End;


procedure DoWork;
var
  SQLDataInstanz: TSchedulerInstanz;
begin
  SQLDataInstanz := TSchedulerInstanz.CreateAndFillPrepareMany(GlobalVar.IService.GetDbServer
    , StringToUTF8(''), [], []);    // Fills one Level of data in the Loop. 

    ///SQLDataInstanz.Intervalls.Dest is fille. But deeper: SQLDataInstanz.Intervalls.Dest.Workinfos is not filled. How to fill SQLDataInstanz.Intervalls.Dest.Workinfos correct?


  while SQLDataInstanz.FillOne do // How to design this loop or in my case the loops?
  begin
    CallOtherFunctionsWorkingOn(SQLDataInstanz);
    GetDbServer.Update(SQLDataInstanz);
  end;
end;

I Tried several attemps to Create the Objects like SQLDataInstanz.Intervalls.Dest.WorkInfos.DestGetJoined and then SQLDataInstanz.Intervalls.Dest.Workinfos := Obj from DestGetJoined, but i always produce Errors when i free the structures ...

Thank you. (Personal Info: i dont know if its wanted but google seems to be unable to find Forum posts)

Iam trying to show off waht i fiddled over the day. But still FastMM says iam having one Object thats not freed for every run trought the loop.

procedure DoWorkWithone;
var
  SQLDataInstanz: TSchedulerInstanz;
  SQLDataIntervall: TSchedulerIntervall;
begin
  SQLDataInstanz := TSchedulerInstanz.CreateAndFillPrepareMany(GlobalVar.IService.GetDbServer
    , StringToUTF8(''), [], []);
  try
    while SQLDataInstanz.FillOne do
    begin

      SQLDataIntervall := TSchedulerIntervall.CreateAndFillPrepareMany(GlobalVar.IService.GetDbServer
        , StringToUTF8('WorkInfos.Source=?'), [], [SQLDataInstanz.Intervalls.Dest.ID]);
      try
        while SQLDataIntervall.FillOne do
        begin
          SQLDataInstanz.Intervalls.Dest.WorkInfos := SQLDataIntervall.WorkInfos;

          Do_Something_with_the_whole_Datamodell(SQLDataInstanz);

          //Save Changes
          GlobalVar.IService.GetDbServer.Update(SQLDataInstanz); 
          GlobalVar.IService.GetDbServer.Update(SQLDataInstanz.Intervalls.Dest);
          GlobalVar.IService.GetDbServer.Update(SQLDataInstanz.Intervalls.Dest.WorkInfos.Dest);

          SQLDataInstanz.Intervalls.Dest.WorkInfos := nil; // try to minimize Memory Problems...
        end;
      finally
        SQLDataIntervall.Free;
      end;
    end;
  finally
    SQLDataInstanz.Free;
  end;
end;

fastMM thinks i had one Memoryleak:
---------------------------
Service.exe: Memory Leak Detected
---------------------------
This application has leaked memory. The small block leaks are (excluding expected leaks registered by pointer):
57 - 88 bytes: UTRESTServerDataDefinition.TSchedulerWorkInfoPivot x 1
---------------------------
OK   
---------------------------

Last edited by Quelltexknecht (2022-06-28 15:19:06)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,657

Website

Re: How To Read Multiple 1:n and n:m Relations correct?

I am not sure "multiple many" are supported yet.