Cascade deletion

macfly · 2016-09-01 19:30:15

Hi Friends,

The only way to set a cascade deletion is by TID, using a TSQLRecordXyzToBeDeletedID type?

With a TSQLRecord property is possible?

Example:

TSQLRecordChildTable = class(TSQLRecord)
   ...
end;

TSQLRecordMainTable = class(TSQLRecord)
   ...
   property child : TSQLRecordChildTable read fchild write fchild; //Delete child row if main row is deleted
end;

If only way is with TSQLRecordXyzToBeDeletedID type, i loose the CreateAndFillPrepareJoined functionality?

Last edited by macfly (2016-09-01 20:28:49)

macfly · 2016-09-01 21:03:50

Sorry guys but I'm a little confused ...

I'm testing the cascade deletion, but even with TID reference is not happening ...

My code

  TSQLtest_child = class;
  TSQLtest_childToBeDeletedID  = type TID; 
  //TSQLRecordtest_childToBeDeletedID  = type TID;  <-- Also tested with TSQLRecord*

  TSQLtest = class(TSQLRecord)
  protected
    FChildToDelete: TSQLtest_childToBeDeletedID;
    fName: RawUTF8;
    fCreatedAt: TCreateTime;
    FModifiedAt: TModTime;
  published
    property Name: RawUTF8 index 255 read fName write fName;
    property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;
    property ModifiedAt: TModTime read FModifiedAt write FModifiedAt;
    property ChildToDelete: TSQLtest_childToBeDeletedID  read FChildToDelete write FChildToDelete;
  end;

  TSQLtest_child = class(TSQLRecord)
  private
  protected
    fName: RawUTF8;
    fCreatedAt: TCreateTime;
    FModifiedAt: TModTime;
  published
    property Name: RawUTF8 index 255 read fNome write fNome;
    property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;
    property ModifiedAt: TModTime read FModifiedAt write FModifiedAt;
  end;


  //Add OK
  ATest := TSQLtest.Create;
  ATestChild := TSQLtest_child.Create;
  try
    
    ATestChild.Name := 'Child';

    ATest.ChildToDelete := TSQLtest_child(Database.Add(ATestChild, True)).ID;

    ATest.Name := Edit1.Text;


    if Database.Add(ATest, True) > 0 then
    begin
      AddItemToViewList(ListView1,ATest);
    end;


  finally
    ATestChild.Free;  
    ATest.Free;
  end;


  //DELETE
  Atest := Database.Retrieve(...); 
  Database.Delete(TSQLtest, ATest.ID);

Child row are not delete with main row.

ab · 2016-09-02 07:14:43

How do you delete the main row?
It should be via the ORM Delete method.

macfly · 2016-09-02 14:29:06

ab wrote:

How do you delete the main row?
It should be via the ORM Delete method.

Yes, i using the ORM.

Database setup:

AModel := CreateModel;
Database := TSQLRestClientDB.Create(AModel,AModel,ExtractFilePath(ParamStr(0)) + 'db.db3', TSQLRestServerDB,False);
TSQLRestClientDB(DataBase).Server.CreateMissingTables();

Then

if  Database.Retrieve(ASelectedID, ATest, False) then
  Database.Delete(TSQLtest, ATest.ID);

Main row are deleted, child row not.

ab · 2016-09-02 15:58:26

Please spend some time in the IDE/debugger to check why the cascaded deleted is not triggerred.

macfly · 2016-09-02 17:24:01

ab wrote:

Please spend some time in the IDE/debugger to check why the cascaded deleted is not triggerred.

I debugged and Database.Delete are calling TSQLRest.Delete method that not call AfterDeleteForceCoherency.

To call TSQLRestServer.Delete that call AfterDeleteForceCoherency i need to use:

TSQLRestServerDB(Database.Server).Delete(TSQLtest, ATest.ID);

This cast are need?

I have created Database in this way:

Database := TSQLRestClientDB.Create(AModel,AModel,ACompleteDBPath,TSQLRestServerDB,False);

Last edited by macfly (2016-09-02 17:24:44)

macfly · 2016-09-02 20:50:46

Also calling TSQLRestServerDB(Database.Server).Delete(TSQLtest, ATest.ID) child record are not deleted.

In AfterDeleteForceCoherency at mORMot.pas (37392) this test

if Ref^.FieldTableIndex=aTableIndex then
          PerformCascade(aID,Ref);

Are evaluted to false, because aTableIndex are 1 and Ref^.FieldTableIndex is 0.
Then PerformCascade are not executed.

PS: is not missing to set Ref value at position of I inside of "for"?
Because without this Ref always point to @Model.fRecordReferences[0];

var i: integer;
    Ref: PSQLModelRecordReference;
begin
  Ref := @Model.fRecordReferences[0];
  if Ref<>nil then begin
    for i := 1 to length(Model.fRecordReferences) do begin
      if Ref^.FieldTableIndex=-2 then // lazy initialization
        Ref^.FieldTableIndex := Model.GetTableIndexSafe(Ref^.FieldTable,false);
      case Ref^.FieldType.SQLFieldType of
      sftRecord: // TRecordReference published field
        PerformCascade(RecordReference(aTableIndex,aID),Ref);
      sftID:     // TSQLRecord published field
        if Ref^.FieldTableIndex=aTableIndex then //FieldTableIndex =0 aTableIndex =1
          PerformCascade(aID,Ref);
      sftTID:    // TTableID = type TID published field
        if Ref^.FieldTableIndex=aTableIndex then
          PerformCascade(aID,Ref);
      end;
      inc(Ref);
    end;
  end;
  result := true; // success even if no match found, or some cascade warnings
end;

Last edited by macfly (2016-09-02 21:15:48)

ab · 2016-09-02 21:51:30

Inc(ref) goes to the next entry.

macfly · 2016-09-03 02:22:13

I found my mistake, but i think that the type name induces to contrary what this feature does...

When i delete child row then the main row are deleted in cascade.

It is the opposite that i thinked.

I wanted to delete child row when main row are first deleted.

 type
   TSQLRecordClientID = type TID;
   TSQLRecordClientToBeDeletedID = type TID;
   TSQLOrder = class(TSQLRecord)
   ...
   property Client: TID
     read fClient write fClient;
   property OrderedBy: TSQLRecordClientID
     read fOrderedBy write fOrderedBy;
   property OrderedByCascade: TSQLRecordClientToBeDeletedID
     read fOrderedByCascade write fOrderedByCascade;
   ...

If i implement this scenario the Order are deleted when the Client are deleted.

But TSQLRecordClientToBeDeletedID type name, indicate that the Client are deleted.

Sorry if I'm mistaking me or forget something again.

mORMot Open Source

#1 2016-09-01 19:30:15

Cascade deletion

#2 2016-09-01 21:03:50

Re: Cascade deletion

#3 2016-09-02 07:14:43

Re: Cascade deletion

#4 2016-09-02 14:29:06

Re: Cascade deletion

#5 2016-09-02 15:58:26

Re: Cascade deletion

#6 2016-09-02 17:24:01

Re: Cascade deletion

#7 2016-09-02 20:50:46

Re: Cascade deletion

#8 2016-09-02 21:51:30

Re: Cascade deletion

#9 2016-09-03 02:22:13

Re: Cascade deletion

Board footer