How to best cascade deletes for a property that is a list of a persistent class?

Question

Question

Hannah Sullivan · Aug 24, 2023

I have a persistent class (GetOrgUpdatesResponse) that has a property which is a list of another persistent class (Organization).

What would be the best way to cascade deletes so that on deletion of a parent row each object in the list property is then also deleted?

I have included a sample of my classes here for clarity

Class Integration.ISIS.BO.SyncOrg.Custom.GetOrgUpdatesResponse Extends (%Persistent, Ens.Response)
{

Property Organizations As list Of Organization;
// Cascade Deletion
Property NextUpdateBatchID As %String;
Property ResultStatus As %String;
}

Where Organization is defined as the following class

Class Integration.ISIS.BO.SyncOrg.Custom.Organization Extends (%Persistent, %XML.Adaptor)
{

Property ISISID As %String;
Property Name As %String(MAXLEN = 250);
Property Type As %String;
Property IsSupported As %Boolean [ InitialExpression = 0, Required ];
Property SalesPersonGUID As %String;
Property CountryISISID As %String;
Property CountryCode As %String;
}

Discussion (11)6

Log in or sign up to continue

Ben Spead · Aug 24, 2023

@Hannah Sullivan ... does Organizations have to be a list? If it is a Parent/Child relationship then I believe the children are stored by default in the extent of the parent so deletion of the parent automatically deletes the children.

0 0

Iryna Mykhailova · Aug 24, 2023

Yep, that was my first thought - can you change Property Organizations As list Of Organization; to parent/children Relationship? In this case you will get automatic cascade delete. You'll have

Relationship Responce As GetOrgUpdatesResponse  [ Cardinality = parent, Inverse = Organizations ];
Relationship Organizations As Organization [ Cardinality = children, Inverse = Responce ];

And it suggests that each responce has its own organizations objects that don't repeat.

Otherwise, you will have to delete them manually 1 by 1 in callback method %OnDelete for example

/// This callback method is invoked by the <METHOD>%Delete</METHOD> method to 
/// provide notification that the object specified by <VAR>oid</VAR> is being deleted.
/// 
/// If this method returns an error then the object will not be deleted.
ClassMethod %OnDelete(oid As %ObjectIdentity) As %Status [ Private, ServerOnly = 1 ]
{
	Quit $$$OK
}

1 0

score 1 · Answer 1 · 2023-08-29T12:15:07-04:00

The list type of Organizations existed prior to the changes I am making and is in use within the application in other places. As such I was ideally hoping implement the cascade deletion without changing that type. However, a Parent/Child relationship as both you and @Iryna Mykhailova suggested is a great option for the automatic cascade deletion and I will definitely consider the benefits of that change. Thank you!

score 0 · Answer 2 · 2023-08-29T15:54:47-04:00

Ahh ... if they need to be used elsewhere then that is a different story.

I forget off the top of my head exactly how this would work, but you could potentially subclass your Org class and just add the parent cardinality relationship. I *think* that should force the extent of the subclass to be embedded in the parent class ( @Dan Pasco?), which should automate the cascading delete for you without impacting the Orgs stored independently.

score 0 · Answer 3 · 2023-09-13T11:24:03-04:00

I don't recommend using the "embedded extent" model. It might be possible to achieve this model but it isn't simple to do and it does produce some odd behavior. I could, reluctantly, describe how this is done. It may not apply to this case anyway. The default serialization of a LIST collection is as an embedded list and we do not support - currently - a serialized list as a child table.

This is an example of a MANY to MANY relationship. IRIS does not support this relationship type. Rather, we recommend using the Associative Entity model where there is a third class/table that maintains the relationship. The structure of the AE class/table is quite simple, consisting of two properties/columns, one referencing of the related classes and the other referencing the other related classes.

If restructuring the user class is not possible then the %OnDelete recommendation is good. Perhaps it would be better to use a FOREACH=ROW/OBJECT, ONAFTER=DELETE trigger to iterate over the collection and delete each referenced item. Triggers have the added advantage of working with both Object and SQL filing operations. Keep in mind that collections use OID format when crafting the delete.

score 1 · Answer 4 · 2023-08-24T16:21:12-04:00

Hello @Hannah Sullivan
If you need to delete the list of object while deleting the parent object. You can override the %OnDelete method and add the delete implementation refer below code logic. Once you call the ##Class(package.class).%DeleteId(id). It will trigger the %OnDelete Method and delete the list of persistent object as well.

ClassMethod %OnDelete(oid As %ObjectIdentity) As %Status [ Private, ServerOnly = 1 ]
{
    set object = ..%Open(oid,,.status)
    If $$$ISERR(status) quit status
    If $IsObject(object.Organizations) {
        set org= object.OrganizationsGetSwizzled()
        while org.GetNext(.key){
            do object.Organizations.GetAt(key).%DeleteId(key)
        }
    }
    Quit $$$OK
}

You can create these object with Parent-child relationship as well.

score 1 · Answer 5 · 2023-08-25T02:13:46-04:00

To your data storage scheme, I would like to clarify one point.
Let's say there is the following data:

Organization
ID
1
2
3

GetOrgUpdatesResponse
ID	Organizations
1	2,3
2	1,2
3	1,3

If you delete ID=1 (GetOrgUpdatesResponse), then ID=2 and ID=3 (Organization) will be automatically deleted. But in this case, the rows ID=2 and ID=3 (GetOrgUpdatesResponse) will contain incorrect data and the referential integrity will be violated. Or am I wrong?

score 0 · Answer 6 · 2023-08-25T03:17:42-04:00

Totally correct.
This is what the example describes:
and the consequence is that you have to spin through ALL GetOrgUpdatesResponse objects
A less impressive design.
So you need to add an index on the ELEMENTS of your Property Organizations
to find all affected objects.
some example of how to

score 0 · Answer 7 · 2023-08-29T11:56:08-04:00

Thank you for the clarification. For the given situation, there would never exist multiple lists that hold the same organization object. Following your example, my tables would be as such.

Organization
ID
1
2
3
4
5

GetOrgUpdatesResponse
ID	Organizations
1	2
2	1,3
3	4,5

So that there would not exist a situation where a deletion of a list in GetOrgUpdatesResponse would result in any other list containing incorrect data.

score 2 · Answer 8 · 2023-08-29T17:02:46-04:00

The solution depends on whether updates are via object, sql or both.
The %OnDelete() callback method suggested only works for objects. For SQL you can add a foreign key definition to the organisation class, referencing the GetOrgUpdatesResponse with OnDelete = cascade constraint.
See https://docs.intersystems.com/iris20231/csp/docbook/Doc.View.cls?KEY=ROB...

The solution that works for both sql and object deletes is an BEFORE DELETE trigger with Foreach = row/object parameter.

score 0 · Answer 9 · 2023-08-30T08:52:49-04:00

I almost never use the %On... methods as

they are only called via object interaction
if I want to handle the SQL interaction I have to write the code a second time

as Wolf suggests a properly defined trigger with Foreach=row/object will be called during both Object and SQL interaction which means I can write one set of code to handle both and I find for this scenario it's best to think "SQL" instead of objects.