You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Hugi Thordarson <hu...@karlmenn.is> on 2019/05/29 12:34:50 UTC
Deleting a bunch of interrelated objects
Hi all.
I have a pretty large model where every to-many relationship's delete rule is "Deny" (I like being explicit about the data I delete, especially since the DB in question still doesn't have well specified FKs).
Almost every table in this DB is somehow related to a "Customer" table (sometimes through a chain of tables) that "owns" data.
I'm now implementing an operation to nuke a Customer (as in "delete everything related to that customer")—but the data "owned" by the customer is hugely interrelated, even often creating circular relations.
Do I have any nice options, given a set of DataObjects, to just tell Cayenne to nuke that darn data without any consideration for normal delete rules? As in "usually I wouldn't allow this and Ashwood is very angry at you—but since you really, really want it…".
Note that the whole operation results in a consistent database (even if there are temporary in-memory inconsistencies due to the circular relationships) since the interrelated data is all deleted within the operation.
Cheers,
- Hugi
Re: Deleting a bunch of interrelated objects
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Yeah, I'll probably end up doing something like that. Although the column name isn't always the same (data "owned" by a Customer can go Customer ->> Invoice ->> InvoiceLine ->> InvoiceLineSums ->> etc) plain SQL is probably the way to go here. I'll just write out them damn joins like our ancestors did.
Thanks,
- hugi
> On 29 May 2019, at 15:26, John Huss <jo...@gmail.com> wrote:
>
> If the column name is the same in all the tables it would be pretty easy to
> loop over all your entities and execute the same delete statement (via raw
> SQLExec). Put all the the entities with that column in a Set and loop over
> them, removing one when it succeeds until the set is empty.
>
> On Wed, May 29, 2019 at 7:35 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> Hi all.
>>
>> I have a pretty large model where every to-many relationship's delete rule
>> is "Deny" (I like being explicit about the data I delete, especially since
>> the DB in question still doesn't have well specified FKs).
>>
>> Almost every table in this DB is somehow related to a "Customer" table
>> (sometimes through a chain of tables) that "owns" data.
>> I'm now implementing an operation to nuke a Customer (as in "delete
>> everything related to that customer")—but the data "owned" by the customer
>> is hugely interrelated, even often creating circular relations.
>>
>> Do I have any nice options, given a set of DataObjects, to just tell
>> Cayenne to nuke that darn data without any consideration for normal delete
>> rules? As in "usually I wouldn't allow this and Ashwood is very angry at
>> you—but since you really, really want it…".
>>
>> Note that the whole operation results in a consistent database (even if
>> there are temporary in-memory inconsistencies due to the circular
>> relationships) since the interrelated data is all deleted within the
>> operation.
>>
>> Cheers,
>> - Hugi
Re: Deleting a bunch of interrelated objects
Posted by John Huss <jo...@gmail.com>.
If the column name is the same in all the tables it would be pretty easy to
loop over all your entities and execute the same delete statement (via raw
SQLExec). Put all the the entities with that column in a Set and loop over
them, removing one when it succeeds until the set is empty.
On Wed, May 29, 2019 at 7:35 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
> Hi all.
>
> I have a pretty large model where every to-many relationship's delete rule
> is "Deny" (I like being explicit about the data I delete, especially since
> the DB in question still doesn't have well specified FKs).
>
> Almost every table in this DB is somehow related to a "Customer" table
> (sometimes through a chain of tables) that "owns" data.
> I'm now implementing an operation to nuke a Customer (as in "delete
> everything related to that customer")—but the data "owned" by the customer
> is hugely interrelated, even often creating circular relations.
>
> Do I have any nice options, given a set of DataObjects, to just tell
> Cayenne to nuke that darn data without any consideration for normal delete
> rules? As in "usually I wouldn't allow this and Ashwood is very angry at
> you—but since you really, really want it…".
>
> Note that the whole operation results in a consistent database (even if
> there are temporary in-memory inconsistencies due to the circular
> relationships) since the interrelated data is all deleted within the
> operation.
>
> Cheers,
> - Hugi
Re: Deleting a bunch of interrelated objects
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Fortunately for the programming world, I'm currently mainly in the business of consuming APIs rather than designing them :p
> On 30 May 2019, at 14:01, Michael Gentry <bl...@gmail.com> wrote:
>
> I'm a fan of well-named things ... deleteWithoutMercy and
> horribleMutantRuntimeWithoutRelationships are fabulous!
>
>
> On Thu, May 30, 2019 at 6:44 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
>
>> Hi Jurgen,
>>
>> thanks for the suggestions :). I made a horrible hack just now, based on
>> the idea of having a different model, but instead of modifying files, I
>> remove the relationships in memory. This actually works but it would be
>> interesting to hear the point of view of others—am I potentially shooting
>> myself in the foot by firing up a copy of my ServerRuntime within the same
>> JVM, based on the same (but modified in memory) model? Any potential
>> conflicts?
>>
>> public void deleteWithoutMercy( final List<DataObject> objectsToDelete ) {
>> final ServerRuntime horribleMutantRuntimeWithoutRelationships =
>> NBCore.createServerRuntime( Props.defaultProps() );
>>
>> horribleMutantRuntimeWithoutRelationships
>> .getDataDomain()
>> .getEntityResolver()
>> .getObjEntities()
>> .forEach( objEntity -> {
>> new ArrayList<>(
>> objEntity.getRelationships() ).forEach( relationship -> {
>> objEntity.removeRelationship(
>> relationship.getName() );
>> } );
>> } );
>>
>> final ObjectContext localOC =
>> horribleMutantRuntimeWithoutRelationships.newContext();
>> final List<DataObject> localObjects = new ArrayList<>();
>>
>> for( final DataObject objectFromAnotherRuntime : objectsToDelete )
>> {
>> localObjects.add( localOC.localObject(
>> objectFromAnotherRuntime ) );
>> }
>>
>> localOC.deleteObjects( localObjects );
>> localOC.commitChanges();
>> }
>>
>>
>> *shudder*
>>
>> - hugi
>>
>>
>>> On 30 May 2019, at 07:13, Jurgen <do...@xsinet.co.za> wrote:
>>>
>>> Hi Hugi
>>>
>>> So crazy idea number one is to maybe duplicate your model and revise the
>> delete rules, then use this DeleteModel to nuke the customer. The downside
>> of this is having to maintain two models, maybe not such a good idea ?
>>>
>>> Idea number two is to add a delete method to each of the classes that
>> first deletes the children. So you have:
>>>
>>> Customer ->> Invoice ->> InvoiceLine ->> InvoiceLineSums
>>>
>>> Then add deleteLineSums() to InvoiceLine that deletes its
>> InvoiceLineSums with something like: getObjectContext().deleteObjects(
>> getLineSums() );
>>>
>>> Do the same in Invoice where deleteLines() is something like:
>>>
>>> for ( InvoiceLine line : getLines() ) line.deleteLineSums();
>>> getObjectContext().commitChanges();
>>> getObjectContext().invalidateObjects( this );
>>> getObjectContext().deleteObjects( getLines() );
>>>
>>> Then the same for Customer .... The downside of all this is that it's
>> not very efficient in terms of DB calls, but then you won't have to go all
>> caveman like and "write out damn joins like our ancestors did" :-)
>>>
>>> Cheers
>>> Jurgen
>>>
>>
>>
Re: Deleting a bunch of interrelated objects
Posted by Michael Gentry <bl...@gmail.com>.
I'm a fan of well-named things ... deleteWithoutMercy and
horribleMutantRuntimeWithoutRelationships are fabulous!
On Thu, May 30, 2019 at 6:44 AM Hugi Thordarson <hu...@karlmenn.is> wrote:
> Hi Jurgen,
>
> thanks for the suggestions :). I made a horrible hack just now, based on
> the idea of having a different model, but instead of modifying files, I
> remove the relationships in memory. This actually works but it would be
> interesting to hear the point of view of others—am I potentially shooting
> myself in the foot by firing up a copy of my ServerRuntime within the same
> JVM, based on the same (but modified in memory) model? Any potential
> conflicts?
>
> public void deleteWithoutMercy( final List<DataObject> objectsToDelete ) {
> final ServerRuntime horribleMutantRuntimeWithoutRelationships =
> NBCore.createServerRuntime( Props.defaultProps() );
>
> horribleMutantRuntimeWithoutRelationships
> .getDataDomain()
> .getEntityResolver()
> .getObjEntities()
> .forEach( objEntity -> {
> new ArrayList<>(
> objEntity.getRelationships() ).forEach( relationship -> {
> objEntity.removeRelationship(
> relationship.getName() );
> } );
> } );
>
> final ObjectContext localOC =
> horribleMutantRuntimeWithoutRelationships.newContext();
> final List<DataObject> localObjects = new ArrayList<>();
>
> for( final DataObject objectFromAnotherRuntime : objectsToDelete )
> {
> localObjects.add( localOC.localObject(
> objectFromAnotherRuntime ) );
> }
>
> localOC.deleteObjects( localObjects );
> localOC.commitChanges();
> }
>
>
> *shudder*
>
> - hugi
>
>
> > On 30 May 2019, at 07:13, Jurgen <do...@xsinet.co.za> wrote:
> >
> > Hi Hugi
> >
> > So crazy idea number one is to maybe duplicate your model and revise the
> delete rules, then use this DeleteModel to nuke the customer. The downside
> of this is having to maintain two models, maybe not such a good idea ?
> >
> > Idea number two is to add a delete method to each of the classes that
> first deletes the children. So you have:
> >
> > Customer ->> Invoice ->> InvoiceLine ->> InvoiceLineSums
> >
> > Then add deleteLineSums() to InvoiceLine that deletes its
> InvoiceLineSums with something like: getObjectContext().deleteObjects(
> getLineSums() );
> >
> > Do the same in Invoice where deleteLines() is something like:
> >
> > for ( InvoiceLine line : getLines() ) line.deleteLineSums();
> > getObjectContext().commitChanges();
> > getObjectContext().invalidateObjects( this );
> > getObjectContext().deleteObjects( getLines() );
> >
> > Then the same for Customer .... The downside of all this is that it's
> not very efficient in terms of DB calls, but then you won't have to go all
> caveman like and "write out damn joins like our ancestors did" :-)
> >
> > Cheers
> > Jurgen
> >
>
>
Re: Deleting a bunch of interrelated objects
Posted by Hugi Thordarson <hu...@karlmenn.is>.
Hi Jurgen,
thanks for the suggestions :). I made a horrible hack just now, based on the idea of having a different model, but instead of modifying files, I remove the relationships in memory. This actually works but it would be interesting to hear the point of view of others—am I potentially shooting myself in the foot by firing up a copy of my ServerRuntime within the same JVM, based on the same (but modified in memory) model? Any potential conflicts?
public void deleteWithoutMercy( final List<DataObject> objectsToDelete ) {
final ServerRuntime horribleMutantRuntimeWithoutRelationships = NBCore.createServerRuntime( Props.defaultProps() );
horribleMutantRuntimeWithoutRelationships
.getDataDomain()
.getEntityResolver()
.getObjEntities()
.forEach( objEntity -> {
new ArrayList<>( objEntity.getRelationships() ).forEach( relationship -> {
objEntity.removeRelationship( relationship.getName() );
} );
} );
final ObjectContext localOC = horribleMutantRuntimeWithoutRelationships.newContext();
final List<DataObject> localObjects = new ArrayList<>();
for( final DataObject objectFromAnotherRuntime : objectsToDelete ) {
localObjects.add( localOC.localObject( objectFromAnotherRuntime ) );
}
localOC.deleteObjects( localObjects );
localOC.commitChanges();
}
*shudder*
- hugi
> On 30 May 2019, at 07:13, Jurgen <do...@xsinet.co.za> wrote:
>
> Hi Hugi
>
> So crazy idea number one is to maybe duplicate your model and revise the delete rules, then use this DeleteModel to nuke the customer. The downside of this is having to maintain two models, maybe not such a good idea ?
>
> Idea number two is to add a delete method to each of the classes that first deletes the children. So you have:
>
> Customer ->> Invoice ->> InvoiceLine ->> InvoiceLineSums
>
> Then add deleteLineSums() to InvoiceLine that deletes its InvoiceLineSums with something like: getObjectContext().deleteObjects( getLineSums() );
>
> Do the same in Invoice where deleteLines() is something like:
>
> for ( InvoiceLine line : getLines() ) line.deleteLineSums();
> getObjectContext().commitChanges();
> getObjectContext().invalidateObjects( this );
> getObjectContext().deleteObjects( getLines() );
>
> Then the same for Customer .... The downside of all this is that it's not very efficient in terms of DB calls, but then you won't have to go all caveman like and "write out damn joins like our ancestors did" :-)
>
> Cheers
> Jurgen
>
Re: Deleting a bunch of interrelated objects
Posted by Jurgen <do...@xsinet.co.za>.
Hi Hugi
So crazy idea number one is to maybe duplicate your model and revise the
delete rules, then use this DeleteModel to nuke the customer. The downside
of this is having to maintain two models, maybe not such a good idea ?
Idea number two is to add a delete method to each of the classes that first
deletes the children. So you have:
Customer ->> Invoice ->> InvoiceLine ->> InvoiceLineSums
Then add deleteLineSums() to InvoiceLine that deletes its InvoiceLineSums
with something like: getObjectContext().deleteObjects( getLineSums() );
Do the same in Invoice where deleteLines() is something like:
for ( InvoiceLine line : getLines() ) line.deleteLineSums();
getObjectContext().commitChanges();
getObjectContext().invalidateObjects( this );
getObjectContext().deleteObjects( getLines() );
Then the same for Customer .... The downside of all this is that it's not
very efficient in terms of DB calls, but then you won't have to go all
caveman like and "write out damn joins like our ancestors did" :-)
Cheers
Jurgen