I have a PolicyEntity and it is related 1:m with many ComponentEntities. There is a cascade delete so that when a Policy is deleted, all of its Components will disappear too.

Component has a number of SubTypes, so the Cascade Delete is effectively deleting the super-type of the deleting child. Off the top of my head, I don't understand how LLBLGen supports this since it normally deletes from the Sub-Type upwards I believe.

Does/Should the existing LLBLGen v4.0 support this? Does/Should the imminent LLBLGen v4.2 support this?

The reason I ask is my colleague has just found this as a possible issue. We haven't confirmed it yet but will later today but I thought I'd ask first about what LLBLGen supports/doesn't support.

(We are currently running LLBLGen 4.0.13.0730 which I know isn't the latest)

Cascade deletes are not implemented in the runtime, they're done through the DB, so the FK constraint has the CASCADE DELETE argument which means the DB will delete any rows on the FK side if the PK side is removed, in this case if the supertype is deleted, all subtypes referring to it are deleted as well (as it should).

I don't know what exactly it is you want to happen, as what you describe is what should happen, or am I missing something?

... in this case if the supertype is deleted, all subtypes referring to it are deleted as well (as it should).

But that would depend on CASCADE DELETEs being added for each subtype on the FK back to their parent supertype would it not? LLBLGen doesn't add these AFAIK.

The problem is that a CASCADE DELETE between PolicyEntity and ComponentEntity is causing ComponentEntities to be deleted automatically in the DB. But because ComponentEntity is a supertype, this fails because its subtypes still have references to it. Thus the whole transaction fails and I can't delete a Policy.

Adding additional CASCADE DELETES between ComponentEntity and all its subtypes (7 currently) will solve this as the cascade then continues down the inheritance hierarchy.

I think maybe I had assumed that LLBLGen would pickup on this and automatically add the additional CASCADE DELETE in the hierarchy. Or include Cascade Deletes as a matter of course on this type of inheritance.

So now LLBLGen supports Cascade Delete, is there any reason why it shouldn't automatically add Cascade Deletes between inherited tables if the DB supports it? Pros: - This would seem to make a more direct link between the entity and its spread-out fields - It allows deletion of an entity top-down - ie one DELETE statement to delete an entity no matter how deep the hierarchy. Currently code-side, LLBLGEN will send multiple DELETE statements from the bottom up. - It allows other database-side functions to delete an entity without even realizing it is spread over several fields Cons: - It might involve a change in LLBLGen code which could only work for DBs that support DELETE CASCADE - not sure how much of an issue this would be. On the other hand, the code could stay as it is and always delete bottom-up, there can't be that much different speedwise I suppose. - Slightly less safe in that (inherited) entities can be deleted via DB from a simple one-line script where previously they couldn't. (some might consider that a Pro I suppose)

Alternatives:- - An option on a SuperType in LLBLGen to include CASCADE DELETES automatically for its subtypes - An option on the Normal Relationship Editor where the DELETE rules is CASCADE and the FK Table is a SuperType to 'pass-on' the CASCADE DELETE to its subtypes. - Leave it to the developer to manually add CASCADE DELETES. I don't like this because it is tedious and error-prone. (Documentation pointing out why these DELETE CASCADES are necessary and how to create them via a script would help mitigate this but I understand it is a pain for you with all the database you support etc.)

Another couple of thoughts on this, maybe relevant, maybe not:-

• Target Per Entity Hierarchy (single table) wouldn't have this problem?

There may be more complication depending on where the Cascade Delete (from outside the inheritance) is mapped Imagine a three-level Target per entity structure: ComponentBase<-Component<-ComponentPension (Level 1, 2 & 3)

If my PolicyEntity was related 1:m to ComponentBase with CascadeDelete, then, providing additional Cascade Deletes are in place down the inheritance chain (manually or a new LLBLGen option) then this should work since Deleting a Policy would Delete the ComponentBase which would delete the Component which would delete the ComponentPension.

If however, PolicyEntity was related to Component (or anywhere not at the SuperType) then I don't think Cascade Deletes would be enough. Deleting a Policy would delete the Component and ComponentPension would also go but ComponentBase would be left as an orphan I think. A trigger would have to be in-place to fix up this scenario but that's a route neither of want to consider I bet

So clear guidance in the documentation about the pitfalls, limitations and workarounds of Cascade Deletes would be very helpful.

Automation of Cascade Deletes within a hierarchy (or even semi-automatic via a table flag or on a relationship to an inheritance chain) would be even more helpful since I guess that relating at the Super-Type level would be the most prevalent use? Any other scenario and you have to write a trigger.

simmotech wrote:

... in this case if the supertype is deleted, all subtypes referring to it are deleted as well (as it should).

But that would depend on CASCADE DELETEs being added for each subtype on the FK back to their parent supertype would it not? LLBLGen doesn't add these AFAIK.

For FK constraints backing inheritance relationships, it picks the defaults as with other FK constraints. Initially it was designed to always pick Cascade, however this isn't the case anymore. It was changed on July 10th 2013, because you proposed it should change: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=124408&ThreadID=22049

So they pick the cascade rule from the defaults. Which is a better option, see the thread above.

The problem is that a CASCADE DELETE between PolicyEntity and ComponentEntity is causing ComponentEntities to be deleted automatically in the DB. But because ComponentEntity is a supertype, this fails because its subtypes still have references to it. Thus the whole transaction fails and I can't delete a Policy.

Adding additional CASCADE DELETES between ComponentEntity and all its subtypes (7 currently) will solve this as the cascade then continues down the inheritance hierarchy.

I think maybe I had assumed that LLBLGen would pickup on this and automatically add the additional CASCADE DELETE in the hierarchy. Or include Cascade Deletes as a matter of course on this type of inheritance.

No, it did that, but it doesn't anymore

So now LLBLGen supports Cascade Delete, is there any reason why it shouldn't automatically add Cascade Deletes between inherited tables if the DB supports it? Pros: - This would seem to make a more direct link between the entity and its spread-out fields - It allows deletion of an entity top-down - ie one DELETE statement to delete an entity no matter how deep the hierarchy. Currently code-side, LLBLGEN will send multiple DELETE statements from the bottom up. - It allows other database-side functions to delete an entity without even realizing it is spread over several fields Cons: - It might involve a change in LLBLGen code which could only work for DBs that support DELETE CASCADE - not sure how much of an issue this would be. On the other hand, the code could stay as it is and always delete bottom-up, there can't be that much different speedwise I suppose. - Slightly less safe in that (inherited) entities can be deleted via DB from a simple one-line script where previously they couldn't. (some might consider that a Pro I suppose)

Alternatives:- - An option on a SuperType in LLBLGen to include CASCADE DELETES automatically for its subtypes - An option on the Normal Relationship Editor where the DELETE rules is CASCADE and the FK Table is a SuperType to 'pass-on' the CASCADE DELETE to its subtypes. - Leave it to the developer to manually add CASCADE DELETES. I don't like this because it is tedious and error-prone. (Documentation pointing out why these DELETE CASCADES are necessary and how to create them via a script would help mitigate this but I understand it is a pain for you with all the database you support etc.)

We initially designed to do so, but there was a drawback, see the thread above, so they're picked from the defaults.

TPE hierarchies are a bit problematic, cascades are really the only way to do it automatically, as you can't execute direct deletes, because that runs into the problem you have to filter on subtype rows to delete supertype rows but the subtype rows are already gone (as you have to delete these first )

So there's little we can do.

simmotech wrote:

Another couple of thoughts on this, maybe relevant, maybe not:-

• Target Per Entity Hierarchy (single table) wouldn't have this problem?

Correct, as all rows are in the same table, so a row of a subtype is just a row in the table, not a couple of rows in a couple of tables as with TPE hierarchies.

t was changed on July 10th 2013, because you proposed it should change: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=124408&ThreadID=22049

Don't listen to me, I'm an idiot!

OK, so sometimes these CASCADEs are wanted and sometimes not.

However, there is no place in the Designer to be able to check or change this, so how about a checkbox on the SuperType Entity form (only appears when the Inheritance Info tab is enabled) to choose whether to include them or not. No complicated logic, checked simply adds CASCADEs (both Delete and Update?) through the whole inheritance tree downwards and unchecked removes them. By default it is unchecked for new entities to match the current situation.

I think this would be a simple but very helpful feature for a complicated situation. (and a man of your calibre should be able to squeeze it into the imminent v4.2 )

simmotech wrote:

t was changed on July 10th 2013, because you proposed it should change: http://www.llblgen.com/tinyforum/GotoMessage.aspx?MessageID=124408&ThreadID=22049

Don't listen to me, I'm an idiot!

I usually ignore what you say but sometimes you make sense .

OK, so sometimes these CASCADEs are wanted and sometimes not.

However, there is no place in the Designer to be able to check or change this, so how about a checkbox on the SuperType Entity form (only appears when the Inheritance Info tab is enabled) to choose whether to include them or not.

You can change the rules in the catalog explorer if you want to, did you check that? I have the feeling it's overly complicating things though... in general people either want the rules everywhere or nowhere... The defaults set the rule values for most of the FKs, and for the ones which should be different, you can alter them at the low level of the catalog explorer.

IMHO, one should always leave 'cascade' as default for deletes and do manual updates in situations where the cascade isn't wanted, or not possible (not all situations can lead to a solid cascade delete, e.g. when multiple paths lead from A to C and A is deleted).

The rule system is complicated, and the code underneath it is too, as changes not always have to propagate to the relational model data... Therefore making changes to it isn't something I'll do lightly.

No complicated logic, checked simply adds CASCADEs (both Delete and Update?) through the whole inheritance tree downwards and unchecked removes them. By default it is unchecked for new entities to match the current situation.

But unless some edge cases, cascades for deletes in TPE are better anyway, and leaving the default as Cascade for deletes covers almost all cases.

I think this would be a simple but very helpful feature for a complicated situation. (and a man of your calibre should be able to squeeze it into the imminent v4.2 )

v4.2 is actually 100% done (installer and everything else is done, didn't have time friday to release it, but it will be live monday), but small changes can always be included. The thing with this is though that a simple checkbox here and there isn't the problem: it's what situations people will run into when using it and whether that's something one'd want. Determining these situations isn't simple, the rule system changes and the problems we've had with it since its introduction prove that... (I thought I had everything covered when we released it but it turned out it was far from OK and needed several deep changes)

I'm all for removing bottlenecks and annoyances, however it's a bit unclear to me what exactly is the real time waster / annoyance in the current system or better: what can't be done in the current system but what's needed...

You can change the rules in the catalog explorer if you want to, did you check that? I have the feeling it's overly complicating things though... in general people either want the rules everywhere or nowhere... The defaults set the rule values for most of the FKs, and for the ones which should be different, you can alter them at the low level of the catalog explorer.

Good point: As a Model-First user, I don't usually look over on 'that' side - I just assume it will be whatever the model says it should be.

Thanks.